src/cx/fbn/nevernote/sql/NotebookTable.java

   1 /*\r
   2  * This file is part of NeverNote \r
   3  * Copyright 2009 Randy Baumgarte\r
   4  * \r
   5  * This file may be licensed under the terms of of the\r
   6  * GNU General Public License Version 2 (the ``GPL'').\r
   7  *\r
   8  * Software distributed under the License is distributed\r
   9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
  10  * express or implied. See the GPL for the specific language\r
  11  * governing rights and limitations.\r
  12  *\r
  13  * You should have received a copy of the GPL along with this\r
  14  * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
  15  * or write to the Free Software Foundation, Inc.,\r
  16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
  17  *\r
  18 */\r
  19 \r
  20 \r
  21 package cx.fbn.nevernote.sql;\r
  22 \r
  23 import java.text.DateFormat;\r
  24 import java.text.ParseException;\r
  25 import java.text.SimpleDateFormat;\r
  26 import java.util.ArrayList;\r
  27 import java.util.HashMap;\r
  28 import java.util.List;\r
  29 \r
  30 import com.evernote.edam.type.NoteSortOrder;\r
  31 import com.evernote.edam.type.Notebook;\r
  32 import com.evernote.edam.type.Publishing;\r
  33 import com.trolltech.qt.core.QBuffer;\r
  34 import com.trolltech.qt.core.QByteArray;\r
  35 import com.trolltech.qt.core.QIODevice;\r
  36 import com.trolltech.qt.gui.QIcon;\r
  37 import com.trolltech.qt.gui.QImage;\r
  38 import com.trolltech.qt.gui.QPixmap;\r
  39 \r
  40 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  41 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  42 import cx.fbn.nevernote.utilities.Pair;\r
  43 \r
  44 public class NotebookTable {\r
  45         \r
  46         private final ApplicationLogger                 logger;\r
  47         DatabaseConnection                                              db;\r
  48         private final String                                    dbName;\r
  49         \r
  50         // Constructor\r
  51         public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
  52                 logger = l;\r
  53                 db = d;\r
  54                 dbName = "Notebook";\r
  55         }\r
  56         // Create the table\r
  57         public void createTable(boolean addDefaulte) {\r
  58                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  59         logger.log(logger.HIGH, "Creating table "+dbName+"...");\r
  60         if (!query.exec("Create table "+dbName+" (guid varchar primary key, " +\r
  61                         "sequence integer, " +\r
  62                         "name varchar, "+\r
  63                         "defaultNotebook varchar, "+\r
  64                         "serviceCreated timestamp, " +\r
  65                         "serviceUpdated timestamp, "+\r
  66                         "published boolean, "+\r
  67                         "isDirty boolean, "+\r
  68                         "autoEncrypt boolean, "+\r
  69                         "local boolean, "+\r
  70                         "archived boolean)"))                           \r
  71                 logger.log(logger.HIGH, "Table "+dbName+" creation FAILED!!!");   \r
  72         Notebook newnote = new Notebook();\r
  73         newnote.setDefaultNotebook(true);\r
  74         newnote.setName("My Notebook");\r
  75         newnote.setPublished(false);\r
  76         newnote.setGuid("1");\r
  77         \r
  78         // Setup an initial notebook\r
  79                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
  80         query = new NSqlQuery(db.getConnection());\r
  81                 query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "\r
  82                                 +"serviceCreated, serviceUpdated, published, "   \r
  83                                 + "isDirty, autoEncrypt, " \r
  84                                 + "local, archived) Values("\r
  85                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
  86                                 +":serviceCreated, :serviceUpdated, :published, "\r
  87                                 +":isDirty, :autoEncrypt, "\r
  88                                 +":local, false)");\r
  89                 query.bindValue(":guid", newnote.getGuid());\r
  90                 query.bindValue(":sequence", newnote.getUpdateSequenceNum());\r
  91                 query.bindValue(":name", newnote.getName());\r
  92                 query.bindValue(":defaultNotebook", newnote.isDefaultNotebook());\r
  93                 \r
  94                 StringBuilder serviceCreated = new StringBuilder(simple.format(newnote.getServiceCreated()));                   \r
  95                 StringBuilder serviceUpdated = new StringBuilder(simple.format(newnote.getServiceUpdated()));\r
  96                 if (serviceUpdated.toString() == null)\r
  97                         serviceUpdated = serviceCreated;\r
  98                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
  99                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 100                 query.bindValue(":published",newnote.isPublished());\r
 101                 \r
 102                 query.bindValue(":isDirty", true);\r
 103                 query.bindValue(":autoEncrypt", false);\r
 104                 query.bindValue(":local", false);\r
 105 \r
 106                 boolean check = query.exec();\r
 107                 if (!check) {\r
 108                         logger.log(logger.MEDIUM, "Initial "+dbName+" Table insert failed.");\r
 109                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 110                 }\r
 111 \r
 112                 \r
 113         }\r
 114         // Drop the table\r
 115         public void dropTable() {\r
 116                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 117                 query.exec("Drop table "+dbName);\r
 118         }\r
 119         // Save an individual notebook\r
 120         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
 121                 boolean check;\r
 122                 \r
 123                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 124         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 125                 check = query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "\r
 126                                 +"serviceCreated, serviceUpdated, published, "   \r
 127                                 + "publishingUri, publishingOrder, publishingAscending, publishingPublicDescription, "\r
 128                                 + "isDirty, autoEncrypt, stack, " \r
 129                                 + "local, archived, readOnly) Values("\r
 130                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
 131                                 +":serviceCreated, :serviceUpdated, :published, "\r
 132                                 +":publishingUri, :publishingOrder, :publishingAscending, :publishingPublicDescription, "\r
 133                                 +":isDirty, :autoEncrypt, "\r
 134                                 +":stack, :local, false, false)");\r
 135                 query.bindValue(":guid", tempNotebook.getGuid());\r
 136                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 137                 query.bindValue(":name", tempNotebook.getName());\r
 138                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 139                 \r
 140                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 141                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
 142                 if (serviceUpdated.toString() == null)\r
 143                         serviceUpdated = serviceCreated;\r
 144                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 145                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 146                 query.bindValue(":published",tempNotebook.isPublished());\r
 147                 \r
 148                 if (tempNotebook.isPublished() && tempNotebook.getPublishing() != null) {\r
 149                         Publishing p = tempNotebook.getPublishing();\r
 150                         query.bindValue(":publishingUri", p.getUri());\r
 151                         query.bindValue(":publishingOrder", p.getOrder().getValue());\r
 152                         query.bindValue(":publishingAscending", p.isAscending());\r
 153                         query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 154                 } else {\r
 155                         query.bindValue(":publishingUri", "");\r
 156                         query.bindValue(":publishingOrder", 1);\r
 157                         query.bindValue(":publishingAscending", 1);\r
 158                         query.bindValue(":publishingPublicDescription", "");\r
 159                 }\r
 160                 \r
 161                 if (isDirty)\r
 162                         query.bindValue(":isDirty", true);\r
 163                 else\r
 164                         query.bindValue(":isDirty", false);\r
 165                 query.bindValue(":autoEncrypt", false);\r
 166                 query.bindValue(":local", local);\r
 167                 query.bindValue(":stack", tempNotebook.getStack());\r
 168 \r
 169                 check = query.exec();\r
 170                 if (!check) {\r
 171                         logger.log(logger.MEDIUM, ""+dbName+" Table insert failed.");\r
 172                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 173                 }\r
 174         }\r
 175         // Delete the notebook based on a guid\r
 176         public void expungeNotebook(String guid, boolean needsSync) {\r
 177                 boolean check;\r
 178         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 179 \r
 180         check = query.prepare("delete from "+dbName+" where guid=:guid");\r
 181                 if (!check) {\r
 182                         logger.log(logger.EXTREME, dbName+" SQL delete prepare has failed.");\r
 183                         logger.log(logger.EXTREME, query.lastError().toString());\r
 184                 }\r
 185                 query.bindValue(":guid", guid);\r
 186                 check = query.exec();\r
 187                 if (!check) \r
 188                         logger.log(logger.MEDIUM, dbName+" delete failed.");\r
 189                 \r
 190                 // Signal the parent that work needs to be done\r
 191                 if  (needsSync) {\r
 192                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 193                         deletedTable.addDeletedItem(guid, dbName);\r
 194                 }\r
 195         }\r
 196         // Update a notebook\r
 197         public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
 198                 boolean check;\r
 199                 \r
 200                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 201                 \r
 202         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 203         check = query.prepare("Update "+dbName+" set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
 204                         "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
 205                                 "published=:published, isDirty=:isDirty, publishinguri=:uri, "+\r
 206                                 "publishingOrder=:order, " + \r
 207                                 "publishingAscending=:ascending, " +\r
 208                                 "publishingPublicDescription=:desc " +\r
 209                                 "where guid=:guid ");\r
 210         \r
 211                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 212                 query.bindValue(":name", tempNotebook.getName());\r
 213                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 214 \r
 215                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 216                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
 217                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 218                 query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
 219                 \r
 220                 query.bindValue(":published", tempNotebook.isPublished());\r
 221                 query.bindValue(":isDirty", isDirty);\r
 222                 \r
 223                 if (tempNotebook.isPublished()) {\r
 224                         query.bindValue(":uri", tempNotebook.getPublishing().getUri());\r
 225                         query.bindValue(":order", tempNotebook.getPublishing().getOrder().getValue());\r
 226                         query.bindValue(":ascending", tempNotebook.getPublishing().isAscending());\r
 227                         query.bindValue(":desc", tempNotebook.getPublishing().getPublicDescription());\r
 228                 } else {\r
 229                         query.bindValue(":uri", "");\r
 230                         query.bindValue(":order", NoteSortOrder.CREATED.getValue());\r
 231                         query.bindValue(":ascending", false);\r
 232                         query.bindValue(":desc", "");\r
 233                 }\r
 234                 \r
 235                 query.bindValue(":guid", tempNotebook.getGuid());\r
 236                 \r
 237                 check = query.exec();\r
 238                 if (!check) {\r
 239                         logger.log(logger.MEDIUM, dbName+" Table update failed.");\r
 240                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 241                 }\r
 242         }\r
 243         // Load notebooks from the database\r
 244         public List<Notebook> getAll() {\r
 245                 Notebook tempNotebook;\r
 246                 List<Notebook> index = new ArrayList<Notebook>();\r
 247                 boolean check;\r
 248                                         \r
 249         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 250                                         \r
 251                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 252                                 "serviceCreated, "+\r
 253                                 "serviceUpdated, "+\r
 254                                 "published, stack, publishinguri, publishingascending, publishingPublicDescription, "+\r
 255                                 "publishingOrder from "+dbName+" order by name");\r
 256                 if (!check)\r
 257                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 258                 while (query.next()) {\r
 259                         tempNotebook = new Notebook();\r
 260                         tempNotebook.setGuid(query.valueString(0));\r
 261                         int sequence = new Integer(query.valueString(1)).intValue();\r
 262                         tempNotebook.setUpdateSequenceNum(sequence);\r
 263                         tempNotebook.setName(query.valueString(2));\r
 264                         tempNotebook.setDefaultNotebook(query.valueBoolean(3, false));\r
 265                         DateFormat indfm = null;\r
 266                         try {\r
 267                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 268 //                              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 269                         } catch (Exception e) { }\r
 270                         try {\r
 271                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 272                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 273                         } catch (ParseException e) {\r
 274                                 e.printStackTrace();\r
 275                         }\r
 276                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 277                         tempNotebook.setStack(query.valueString(7));\r
 278                         if (tempNotebook.isPublished()) {\r
 279                                 Publishing p = new Publishing();\r
 280                                 p.setUri(query.valueString(8));\r
 281                                 p.setAscending(query.valueBoolean(9, false));\r
 282                                 p.setPublicDescription(query.valueString(10));\r
 283                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 284                                 tempNotebook.setPublishing(p);\r
 285                         }\r
 286                         index.add(tempNotebook); \r
 287                 }       \r
 288                 return index;\r
 289         }       \r
 290         public List<Notebook> getAllLocal() {\r
 291                 Notebook tempNotebook;\r
 292                 List<Notebook> index = new ArrayList<Notebook>();\r
 293                 boolean check;\r
 294 \r
 295         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 296                                         \r
 297                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 298                                 "serviceCreated, serviceUpdated, published, stack from "+dbName+" where local=true order by name");\r
 299                 if (!check)\r
 300                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 301                 while (query.next()) {\r
 302                         tempNotebook = new Notebook();\r
 303                         tempNotebook.setGuid(query.valueString(0));\r
 304                         int sequence = new Integer(query.valueString(1)).intValue();\r
 305                         tempNotebook.setUpdateSequenceNum(sequence);\r
 306                         tempNotebook.setName(query.valueString(2));\r
 307                         \r
 308                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 309 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 310                         try {\r
 311                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 312                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 313                         } catch (ParseException e) {\r
 314                                 e.printStackTrace();\r
 315                         }\r
 316                         tempNotebook.setStack(query.valueString(7));\r
 317                         index.add(tempNotebook); \r
 318                 }       \r
 319                 return index;\r
 320         }\r
 321         // Archive or un-archive a notebook\r
 322         public void setArchived(String guid, boolean val) {\r
 323                 boolean check;                  \r
 324         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 325                 check = query.prepare("Update "+dbName+" set archived=:archived where guid=:guid");\r
 326                 if (!check)\r
 327                         logger.log(logger.EXTREME, dbName+" SQL archive update has failed.");\r
 328                 query.bindValue(":guid", guid);\r
 329                 query.bindValue(":archived", val);\r
 330                 query.exec();\r
 331         }\r
 332         // Load non-archived notebooks from the database\r
 333         public List<Notebook> getAllArchived() {\r
 334                 Notebook tempNotebook;\r
 335                 List<Notebook> index = new ArrayList<Notebook>();\r
 336                 boolean check;\r
 337                                                 \r
 338         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 339                                         \r
 340                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 341                                 "serviceCreated, serviceUpdated, published, stack "+\r
 342                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 343                                 "publishingOrder " +\r
 344                                 "from "+dbName+" where archived=true order by name");\r
 345                 if (!check)\r
 346                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 347                 while (query.next()) {\r
 348                         tempNotebook = new Notebook();\r
 349                         tempNotebook.setGuid(query.valueString(0));\r
 350                         int sequence = new Integer(query.valueString(1)).intValue();\r
 351                         tempNotebook.setUpdateSequenceNum(sequence);\r
 352                         tempNotebook.setName(query.valueString(2));\r
 353                         \r
 354                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 355 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 356                         try {\r
 357                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 358                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 359                         } catch (ParseException e) {\r
 360                                 e.printStackTrace();\r
 361                         }\r
 362                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 363                         tempNotebook.setStack(query.valueString(7));\r
 364                         \r
 365                         if (tempNotebook.isPublished()) {\r
 366                                 Publishing p = new Publishing();\r
 367                                 p.setUri(query.valueString(8));\r
 368                                 p.setAscending(query.valueBoolean(9, false));\r
 369                                 p.setPublicDescription(query.valueString(10));\r
 370                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 371                                 tempNotebook.setPublishing(p);\r
 372                         }\r
 373                         \r
 374                         index.add(tempNotebook); \r
 375                 }       \r
 376                 return index;\r
 377         }       \r
 378         // Check for a local/remote notebook\r
 379         public boolean isNotebookLocal(String guid) {\r
 380         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 381                 \r
 382                 query.prepare("Select local from "+dbName+" where guid=:guid");\r
 383                 query.bindValue(":guid", guid);\r
 384                 query.exec();\r
 385                 if (!query.next()) {\r
 386                         return false;\r
 387                 }\r
 388                 boolean returnValue = query.valueBoolean(0, false);\r
 389                 return returnValue;\r
 390         }\r
 391         public boolean isReadOnly(String guid) {\r
 392         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 393                 \r
 394                 query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly='true'");\r
 395                 query.bindValue(":guid", guid);\r
 396                 query.exec();\r
 397                 if (!query.next()) {\r
 398                         return false;\r
 399                 }\r
 400                 boolean returnValue = query.valueBoolean(0, false);\r
 401                 return returnValue;\r
 402         }\r
 403         // Update a notebook sequence number\r
 404         public void updateNotebookSequence(String guid, int sequence) {\r
 405                 boolean check;\r
 406         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 407                 check = query.prepare("Update "+dbName+" set sequence=:sequence where guid=:guid");\r
 408                 query.bindValue(":guid", guid);\r
 409                 query.bindValue(":sequence", sequence);\r
 410                 query.exec();\r
 411                 if (!check) {\r
 412                         logger.log(logger.MEDIUM, dbName+" sequence update failed.");\r
 413                         logger.log(logger.MEDIUM, query.lastError());\r
 414                 } \r
 415         }\r
 416         // Update a notebook GUID number\r
 417         public void updateNotebookGuid(String oldGuid, String newGuid) {\r
 418         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 419                 query.prepare("Update "+dbName+" set guid=:newGuid where guid=:oldGuid");\r
 420                 query.bindValue(":oldGuid", oldGuid);\r
 421                 query.bindValue(":newGuid", newGuid);\r
 422                 if (!query.exec()) {\r
 423                         logger.log(logger.MEDIUM, dbName+" guid update failed.");\r
 424                         logger.log(logger.MEDIUM, query.lastError());\r
 425                 } \r
 426                 \r
 427                 // Update any notes containing the notebook guid\r
 428                 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
 429                 query.bindValue(":oldGuid", oldGuid);\r
 430                 query.bindValue(":newGuid", newGuid);\r
 431                 if (!query.exec()) {\r
 432                         logger.log(logger.MEDIUM, dbName+" guid update for note failed.");\r
 433                         logger.log(logger.MEDIUM, query.lastError());\r
 434                 } \r
 435                 \r
 436                 // Update any watch folders with the new guid\r
 437                 query = new NSqlQuery(db.getConnection());\r
 438                 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
 439                 query.bindValue(":oldGuid", oldGuid);\r
 440                 query.bindValue(":newGuid", newGuid);\r
 441                 if (!query.exec()) {\r
 442                         logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
 443                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 444                 }               \r
 445         }\r
 446         // Get a list of notes that need to be updated\r
 447         public List <Notebook> getDirty() {\r
 448                 Notebook tempNotebook;\r
 449                 List<Notebook> index = new ArrayList<Notebook>();\r
 450                 boolean check;\r
 451                                                 \r
 452                 \r
 453         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 454                                         \r
 455                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 456                                 "serviceCreated, serviceUpdated, published, stack, "+\r
 457                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 458                                 "publishingOrder " +\r
 459                                 "from "+dbName+" where isDirty = true and local=false");\r
 460                 if (!check) \r
 461                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 462                 while (query.next()) {\r
 463                         tempNotebook = new Notebook();\r
 464                         tempNotebook.setGuid(query.valueString(0));\r
 465                         int sequence = new Integer(query.valueString(1)).intValue();\r
 466                         tempNotebook.setUpdateSequenceNum(sequence);\r
 467                         tempNotebook.setName(query.valueString(2));\r
 468                         \r
 469                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 470 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 471                         try {\r
 472                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 473                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 474                         } catch (ParseException e) {\r
 475                                 e.printStackTrace();\r
 476                         }\r
 477                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 478                         tempNotebook.setStack(query.valueString(7));\r
 479                         \r
 480                         if (tempNotebook.isPublished()) {\r
 481                                 Publishing p = new Publishing();\r
 482                                 p.setUri(query.valueString(8));\r
 483                                 p.setAscending(query.valueBoolean(9, false));\r
 484                                 p.setPublicDescription(query.valueString(10));\r
 485                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 486                                 if (p.getPublicDescription().trim().equalsIgnoreCase(""))\r
 487                                         p.setPublicDescription(null);\r
 488                                 tempNotebook.setPublishing(p);\r
 489                         }\r
 490                         \r
 491                         index.add(tempNotebook);\r
 492                 }       \r
 493                 return index;   \r
 494         }\r
 495         // This is a convience method to check if a tag exists & update/create based upon it\r
 496         public void syncNotebook(Notebook notebook, boolean isDirty) {\r
 497                 if (!exists(notebook.getGuid())) {\r
 498                         addNotebook(notebook, isDirty, isDirty);\r
 499                         return;\r
 500                 }\r
 501                 updateNotebook(notebook, isDirty);\r
 502         }\r
 503         // does a record exist?\r
 504         private boolean exists(String guid) {\r
 505                 \r
 506                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 507                 \r
 508                 query.prepare("Select guid from "+dbName+" where guid=:guid");\r
 509                 query.bindValue(":guid", guid);\r
 510                 if (!query.exec())\r
 511                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 512                 boolean retval = query.next();\r
 513                 return retval;\r
 514         }\r
 515         // Reset the dirty flag.  Typically done after a sync.\r
 516         public void  resetDirtyFlag(String guid) {\r
 517                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 518                 \r
 519                 query.prepare("Update "+dbName+" set isdirty='false' where guid=:guid");\r
 520                 query.bindValue(":guid", guid);\r
 521                 if (!query.exec())\r
 522                         logger.log(logger.EXTREME, "Error resetting "+dbName+" dirty field.");\r
 523         }\r
 524         // Set the default notebook\r
 525         public void setDefaultNotebook(String guid) {\r
 526                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 527                 \r
 528                 query.prepare("Update "+dbName+" set defaultNotebook=false");\r
 529                 if (!query.exec())\r
 530                         logger.log(logger.EXTREME, "Error removing default "+dbName+".");\r
 531                 query.prepare("Update "+dbName+" set defaultNotebook=true where guid = :guid");\r
 532                 query.bindValue(":guid", guid);\r
 533                 if (!query.exec())\r
 534                         logger.log(logger.EXTREME, "Error setting default "+dbName+".");\r
 535         }\r
 536         \r
 537         // Get a list of all icons\r
 538         public HashMap<String, QIcon> getAllIcons() {\r
 539                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 540                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 541         \r
 542                 if (!query.exec("SELECT guid, icon from "+dbName+" where ARCHIVED  != true"))\r
 543                         logger.log(logger.EXTREME, "Error executing "+dbName+" getAllIcons select.");\r
 544                 while (query.next()) {\r
 545                         if (query.getBlob(1) != null) {\r
 546                                 String guid = query.valueString(0);\r
 547                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 548                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 549                                 values.put(guid, icon);\r
 550                         }\r
 551                 }\r
 552                 return values;\r
 553         }\r
 554         \r
 555         // Get the notebooks custom icon\r
 556         public QIcon getIcon(String guid) {\r
 557                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 558                 \r
 559                 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))\r
 560                         logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 561                 query.bindValue(":guid", guid);\r
 562                 if (!query.exec())\r
 563                         logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");\r
 564                 if (!query.next() || query.getBlob(0) == null)\r
 565                         return null;\r
 566                 \r
 567                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 568                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 569                 return icon;\r
 570         }\r
 571         // Get the notebooks custom icon\r
 572         public QByteArray getIconAsByteArray(String guid) {\r
 573                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 574                 \r
 575                 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))\r
 576                         logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 577                 query.bindValue(":guid", guid);\r
 578                 if (!query.exec())\r
 579                         logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");\r
 580                 if (!query.next() || query.getBlob(0) == null)\r
 581                         return null;\r
 582                 \r
 583                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 584                 return blob;\r
 585         }\r
 586         // Set the notebooks custom icon\r
 587         public void setIcon(String guid, QIcon icon, String type) {\r
 588                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 589                 if (icon == null) {\r
 590                         if (!query.prepare("update "+dbName+" set icon=null where guid=:guid"))\r
 591                                 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 592                 } else {\r
 593                         if (!query.prepare("update "+dbName+" set icon=:icon where guid=:guid"))\r
 594                                 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 595                         QBuffer buffer = new QBuffer();\r
 596                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 597                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 598                         return;\r
 599                 }\r
 600                 QPixmap p = icon.pixmap(32, 32);\r
 601                 QImage i = p.toImage();\r
 602                 i.save(buffer, type.toUpperCase());\r
 603                 buffer.close();\r
 604                 QByteArray b = new QByteArray(buffer.buffer());\r
 605                 if (!b.isNull() && !b.isEmpty())\r
 606                         query.bindValue(":icon", b.toByteArray());\r
 607                 else\r
 608                         return;\r
 609                 }\r
 610                 query.bindValue(":guid", guid);\r
 611                 if (!query.exec()) \r
 612                         logger.log(logger.LOW, "Error setting "+dbName+" icon. " +query.lastError());\r
 613         }\r
 614         // Set the notebooks custom icon\r
 615         public void setReadOnly(String guid, boolean readOnly) {\r
 616                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 617                 if (!query.prepare("update "+dbName+" set readOnly=:readOnly where guid=:guid"))\r
 618                         logger.log(logger.EXTREME, "Error preparing "+dbName+" read only.");\r
 619                 query.bindValue(":guid", guid);\r
 620                 query.bindValue(":readOnly", readOnly);\r
 621                 if (!query.exec()) \r
 622                         logger.log(logger.LOW, "Error setting "+dbName+" read only. " +query.lastError());\r
 623         }\r
 624 \r
 625         // does a record exist?\r
 626         public String findNotebookByName(String newname) {\r
 627                 \r
 628                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 629                 \r
 630                 query.prepare("Select guid from "+dbName+" where name=:newname");\r
 631                 query.bindValue(":newname", newname);\r
 632                 if (!query.exec())\r
 633                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 634                 String val = null;\r
 635                 if (query.next())\r
 636                         val = query.valueString(0);\r
 637                 return val;\r
 638         }\r
 639         // Get a note tag counts\r
 640         public List<Pair<String,Integer>> getNotebookCounts() {\r
 641                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
 642                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 643                 if (!query.exec("select notebookGuid, count(guid) from note where active=1 group by notebookguid;")) {\r
 644                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
 645                         logger.log(logger.MEDIUM, query.lastError());\r
 646                         return null;\r
 647                 }\r
 648                 while (query.next()) {\r
 649                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
 650                         newCount.setFirst(query.valueString(0));\r
 651                         newCount.setSecond(query.valueInteger(1));\r
 652                         counts.add(newCount);\r
 653                 }       \r
 654                 return counts;\r
 655         }\r
 656 \r
 657         // Get/Set stacks\r
 658         public void clearStack(String guid) {\r
 659                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 660                 \r
 661                 query.prepare("Update "+dbName+" set stack='' where guid=:guid");\r
 662                 query.bindValue(":guid", guid);\r
 663                 if (!query.exec())\r
 664                         logger.log(logger.EXTREME, "Error clearing "+dbName+" stack.");\r
 665         }\r
 666         // Get/Set stacks\r
 667         public void setStack(String guid, String stack) {\r
 668                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 669                 \r
 670                 query.prepare("Update "+dbName+" set stack=:stack, isDirty=true where guid=:guid");\r
 671                 query.bindValue(":guid", guid);\r
 672                 query.bindValue(":stack", stack);\r
 673                 if (!query.exec())\r
 674                         logger.log(logger.EXTREME, "Error setting notebook stack.");\r
 675         }\r
 676         // Get all stack names\r
 677         public List<String> getAllStackNames() {\r
 678                 List<String> stacks = new ArrayList<String>();\r
 679                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 680                 \r
 681                 if (!query.exec("Select distinct stack from "+dbName)) {\r
 682                         logger.log(logger.EXTREME, "Error getting all stack names.");\r
 683                         return null;\r
 684                 }\r
 685                 \r
 686                 while (query.next()) {\r
 687                         if (query.valueString(0) != null && !query.valueString(0).trim().equals(""))\r
 688                                 stacks.add(query.valueString(0));\r
 689                 }\r
 690                 return stacks;\r
 691         }\r
 692         // Rename a stack\r
 693         public void renameStacks(String oldName, String newName) {\r
 694                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 695                 \r
 696                 if (!query.prepare("update "+dbName+" set stack=:newName where stack=:oldName")) {\r
 697                         logger.log(logger.EXTREME, "Error preparing in renameStacks.");\r
 698                         return;\r
 699                 }\r
 700                 query.bindValue(":oldName", oldName);\r
 701                 query.bindValue(":newName", newName);\r
 702                 if (!query.exec()) {\r
 703                         logger.log(logger.EXTREME, "Error updating stack names");\r
 704                         return;\r
 705                 }\r
 706                 \r
 707                 if (!query.prepare("update SystemIcon set stack=:newName where stack=:oldName and type='STACK'")) {\r
 708                         logger.log(logger.EXTREME, "Error preparing icon rename in renameStacks.");\r
 709                         return;\r
 710                 }\r
 711                 query.bindValue(":oldName", oldName);\r
 712                 query.bindValue(":newName", newName);\r
 713                 if (!query.exec()) {\r
 714                         logger.log(logger.EXTREME, "Error updating stack names for SystemIcon");\r
 715                         return;\r
 716                 }\r
 717 \r
 718         }\r
 719         // Get/Set stacks\r
 720         public boolean stackExists(String stack) {\r
 721                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 722                 \r
 723                 query.prepare("Select guid from "+dbName+" where stack=:stack limit 1");\r
 724                 query.bindValue(":stack", stack);\r
 725                 if (!query.exec())\r
 726                         logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
 727                 if (query.next())\r
 728                         return true;\r
 729                 else\r
 730                         return false;\r
 731         }\r
 732         // Set Publishing\r
 733         public void setPublishing(String guid, boolean published, Publishing p) {\r
 734                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 735                 \r
 736                 \r
 737                 query.prepare("Update "+dbName+" set publishingPublicDescription=:publishingPublicDescription, " +\r
 738                                 "publishingUri=:publishingUri, publishingOrder=:publishingOrder, published=:published, "+\r
 739                                 "publishingAscending=:publishingAscending, isdirty=true where "+\r
 740                                 "guid=:guid");\r
 741                 query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 742                 query.bindValue(":publishingUri", p.getUri());\r
 743                                 query.bindValue(":publishingOrder", p.getOrder().getValue());\r
 744                 query.bindValue(":publishingAscending", p.isAscending());\r
 745                 query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 746                 query.bindValue(":published", published);\r
 747                 query.bindValue(":guid", guid);\r
 748                 if (!query.exec())\r
 749                         logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
 750         }\r
 751 }\r
 752 \r