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         NSqlQuery                                                               notebookCountQuery;\r
  50         \r
  51         // Constructor\r
  52         public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
  53                 logger = l;\r
  54                 db = d;\r
  55                 dbName = "Notebook";\r
  56         }\r
  57         // Constructor\r
  58         public NotebookTable(ApplicationLogger l, DatabaseConnection d, String name) {\r
  59                 logger = l;\r
  60                 db = d;\r
  61                 dbName = name;\r
  62         }\r
  63 \r
  64         // Create the table\r
  65         public void createTable(boolean addDefaulte) {\r
  66                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  67         logger.log(logger.HIGH, "Creating table "+dbName+"...");\r
  68         if (!query.exec("Create table "+dbName+" (guid varchar primary key, " +\r
  69                         "sequence integer, " +\r
  70                         "name varchar, "+\r
  71                         "defaultNotebook varchar, "+\r
  72                         "serviceCreated timestamp, " +\r
  73                         "serviceUpdated timestamp, "+\r
  74                         "published boolean, "+\r
  75                         "isDirty boolean, "+\r
  76                         "autoEncrypt boolean, "+\r
  77                         "local boolean, "+\r
  78                         "archived boolean)"))                           \r
  79                 logger.log(logger.HIGH, "Table "+dbName+" creation FAILED!!!");   \r
  80         Notebook newnote = new Notebook();\r
  81         newnote.setDefaultNotebook(true);\r
  82         newnote.setName("My Notebook");\r
  83         newnote.setPublished(false);\r
  84         newnote.setGuid("1");\r
  85         \r
  86         // Setup an initial notebook\r
  87                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
  88         query = new NSqlQuery(db.getConnection());\r
  89                 query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "\r
  90                                 +"serviceCreated, serviceUpdated, published, "   \r
  91                                 + "isDirty, autoEncrypt, " \r
  92                                 + "local, archived) Values("\r
  93                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
  94                                 +":serviceCreated, :serviceUpdated, :published, "\r
  95                                 +":isDirty, :autoEncrypt, "\r
  96                                 +":local, false)");\r
  97                 query.bindValue(":guid", newnote.getGuid());\r
  98                 query.bindValue(":sequence", newnote.getUpdateSequenceNum());\r
  99                 query.bindValue(":name", newnote.getName());\r
 100                 query.bindValue(":defaultNotebook", newnote.isDefaultNotebook());\r
 101                 \r
 102                 StringBuilder serviceCreated = new StringBuilder(simple.format(newnote.getServiceCreated()));                   \r
 103                 StringBuilder serviceUpdated = new StringBuilder(simple.format(newnote.getServiceUpdated()));\r
 104                 if (serviceUpdated.toString() == null)\r
 105                         serviceUpdated = serviceCreated;\r
 106                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 107                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 108                 query.bindValue(":published",newnote.isPublished());\r
 109                 \r
 110                 query.bindValue(":isDirty", true);\r
 111                 query.bindValue(":autoEncrypt", false);\r
 112                 query.bindValue(":local", false);\r
 113 \r
 114                 boolean check = query.exec();\r
 115                 if (!check) {\r
 116                         logger.log(logger.MEDIUM, "Initial "+dbName+" Table insert failed.");\r
 117                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 118                 }\r
 119 \r
 120                 \r
 121         }\r
 122         // Drop the table\r
 123         public void dropTable() {\r
 124                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 125                 query.exec("Drop table "+dbName);\r
 126         }\r
 127         // Save an individual notebook\r
 128         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
 129                 addNotebook(tempNotebook, isDirty, local, false, false);\r
 130         }\r
 131         // Save an individual notebook\r
 132         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local, boolean linked, boolean readOnly) {\r
 133                 boolean check;\r
 134                 \r
 135                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 136         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 137                 check = query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "\r
 138                                 +"serviceCreated, serviceUpdated, published, "   \r
 139                                 + "publishingUri, publishingOrder, publishingAscending, publishingPublicDescription, "\r
 140                                 + "isDirty, autoEncrypt, stack, " \r
 141                                 + "local, archived, readOnly, linked) Values("\r
 142                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
 143                                 +":serviceCreated, :serviceUpdated, :published, "\r
 144                                 +":publishingUri, :publishingOrder, :publishingAscending, :publishingPublicDescription, "\r
 145                                 +":isDirty, :autoEncrypt, "\r
 146                                 +":stack, :local, false, :readOnly, :linked)");\r
 147                 query.bindValue(":guid", tempNotebook.getGuid());\r
 148                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 149                 query.bindValue(":name", tempNotebook.getName());\r
 150                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 151                 \r
 152                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 153                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
 154                 if (serviceUpdated.toString() == null)\r
 155                         serviceUpdated = serviceCreated;\r
 156                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 157                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 158                 query.bindValue(":published",tempNotebook.isPublished());\r
 159                 query.bindValue(":linked", linked);\r
 160                 query.bindValue(":readOnly", readOnly);\r
 161                 \r
 162                 if (tempNotebook.isPublished() && tempNotebook.getPublishing() != null) {\r
 163                         Publishing p = tempNotebook.getPublishing();\r
 164                         query.bindValue(":publishingUri", p.getUri());\r
 165                         query.bindValue(":publishingOrder", p.getOrder().getValue());\r
 166                         query.bindValue(":publishingAscending", p.isAscending());\r
 167                         query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 168                 } else {\r
 169                         query.bindValue(":publishingUri", "");\r
 170                         query.bindValue(":publishingOrder", 1);\r
 171                         query.bindValue(":publishingAscending", 1);\r
 172                         query.bindValue(":publishingPublicDescription", "");\r
 173                 }\r
 174                 \r
 175                 if (isDirty)\r
 176                         query.bindValue(":isDirty", true);\r
 177                 else\r
 178                         query.bindValue(":isDirty", false);\r
 179                 query.bindValue(":autoEncrypt", false);\r
 180                 query.bindValue(":local", local);\r
 181                 query.bindValue(":stack", tempNotebook.getStack());\r
 182 \r
 183                 check = query.exec();\r
 184                 if (!check) {\r
 185                         logger.log(logger.MEDIUM, ""+dbName+" Table insert failed.");\r
 186                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 187                 }\r
 188         }\r
 189         // Delete the notebook based on a guid\r
 190         public void expungeNotebook(String guid, boolean needsSync) {\r
 191                 boolean check;\r
 192         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 193 \r
 194         check = query.prepare("delete from "+dbName+" where guid=:guid");\r
 195                 if (!check) {\r
 196                         logger.log(logger.EXTREME, dbName+" SQL delete prepare has failed.");\r
 197                         logger.log(logger.EXTREME, query.lastError().toString());\r
 198                 }\r
 199                 query.bindValue(":guid", guid);\r
 200                 check = query.exec();\r
 201                 if (!check) \r
 202                         logger.log(logger.MEDIUM, dbName+" delete failed.");\r
 203                 \r
 204                 // Signal the parent that work needs to be done\r
 205                 if  (needsSync) {\r
 206                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 207                         deletedTable.addDeletedItem(guid, dbName);\r
 208                 }\r
 209         }\r
 210         // Update a notebook\r
 211         public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
 212                 boolean check;\r
 213                 \r
 214                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 215                 \r
 216         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 217         check = query.prepare("Update "+dbName+" set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
 218                         "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
 219                                 "published=:published, isDirty=:isDirty, publishinguri=:uri, "+\r
 220                                 "publishingOrder=:order, " + \r
 221                                 "publishingAscending=:ascending, " +\r
 222                                 "publishingPublicDescription=:desc, " +\r
 223                                 "stack=:stack " +\r
 224                                 "where guid=:guid ");\r
 225         \r
 226                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 227                 query.bindValue(":name", tempNotebook.getName());\r
 228                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 229 \r
 230                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 231                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
 232                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 233                 query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
 234                 \r
 235                 query.bindValue(":published", tempNotebook.isPublished());\r
 236                 query.bindValue(":isDirty", isDirty);\r
 237                 \r
 238                 if (tempNotebook.isPublished()) {\r
 239                         query.bindValue(":uri", tempNotebook.getPublishing().getUri());\r
 240                         query.bindValue(":order", tempNotebook.getPublishing().getOrder().getValue());\r
 241                         query.bindValue(":ascending", tempNotebook.getPublishing().isAscending());\r
 242                         query.bindValue(":desc", tempNotebook.getPublishing().getPublicDescription());\r
 243                 } else {\r
 244                         query.bindValue(":uri", "");\r
 245                         query.bindValue(":order", NoteSortOrder.CREATED.getValue());\r
 246                         query.bindValue(":ascending", false);\r
 247                         query.bindValue(":desc", "");\r
 248                 }\r
 249                 \r
 250                 query.bindValue(":guid", tempNotebook.getGuid());\r
 251                 query.bindValue(":stack", tempNotebook.getStack());\r
 252                 \r
 253                 check = query.exec();\r
 254                 if (!check) {\r
 255                         logger.log(logger.MEDIUM, dbName+" Table update failed.");\r
 256                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 257                 }\r
 258         }\r
 259         // Load notebooks from the database\r
 260         public List<Notebook> getAll() {\r
 261                 Notebook tempNotebook;\r
 262                 List<Notebook> index = new ArrayList<Notebook>();\r
 263                 boolean check;\r
 264                                         \r
 265         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 266                                         \r
 267                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 268                                 "serviceCreated, "+\r
 269                                 "serviceUpdated, "+\r
 270                                 "published, stack, publishinguri, publishingascending, publishingPublicDescription, "+\r
 271                                 "publishingOrder from "+dbName+" order by name");\r
 272                 if (!check)\r
 273                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 274                 while (query.next()) {\r
 275                         tempNotebook = new Notebook();\r
 276                         tempNotebook.setGuid(query.valueString(0));\r
 277                         int sequence = new Integer(query.valueString(1)).intValue();\r
 278                         tempNotebook.setUpdateSequenceNum(sequence);\r
 279                         tempNotebook.setName(query.valueString(2));\r
 280                         tempNotebook.setDefaultNotebook(query.valueBoolean(3, false));\r
 281                         DateFormat indfm = null;\r
 282                         try {\r
 283                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 284 //                              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 285                         } catch (Exception e) { }\r
 286                         try {\r
 287                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 288                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 289                         } catch (ParseException e) {\r
 290                                 e.printStackTrace();\r
 291                         }\r
 292                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 293                         tempNotebook.setStack(query.valueString(7));\r
 294                         if (tempNotebook.isPublished()) {\r
 295                                 Publishing p = new Publishing();\r
 296                                 p.setUri(query.valueString(8));\r
 297                                 p.setAscending(query.valueBoolean(9, false));\r
 298                                 p.setPublicDescription(query.valueString(10));\r
 299                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 300                                 tempNotebook.setPublishing(p);\r
 301                         }\r
 302                         index.add(tempNotebook); \r
 303                 }       \r
 304                 return index;\r
 305         }       \r
 306         public List<Notebook> getAllLocal() {\r
 307                 Notebook tempNotebook;\r
 308                 List<Notebook> index = new ArrayList<Notebook>();\r
 309                 boolean check;\r
 310 \r
 311         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 312                                         \r
 313                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 314                                 "serviceCreated, serviceUpdated, published, stack from "+dbName+" where local=true order by name");\r
 315                 if (!check)\r
 316                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 317                 while (query.next()) {\r
 318                         tempNotebook = new Notebook();\r
 319                         tempNotebook.setGuid(query.valueString(0));\r
 320                         int sequence = new Integer(query.valueString(1)).intValue();\r
 321                         tempNotebook.setUpdateSequenceNum(sequence);\r
 322                         tempNotebook.setName(query.valueString(2));\r
 323                         \r
 324                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 325 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 326                         try {\r
 327                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 328                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 329                         } catch (ParseException e) {\r
 330                                 e.printStackTrace();\r
 331                         }\r
 332                         tempNotebook.setStack(query.valueString(7));\r
 333                         index.add(tempNotebook); \r
 334                 }       \r
 335                 return index;\r
 336         }\r
 337         // Archive or un-archive a notebook\r
 338         public void setArchived(String guid, boolean val) {\r
 339                 boolean check;                  \r
 340         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 341                 check = query.prepare("Update "+dbName+" set archived=:archived where guid=:guid");\r
 342                 if (!check)\r
 343                         logger.log(logger.EXTREME, dbName+" SQL archive update has failed.");\r
 344                 query.bindValue(":guid", guid);\r
 345                 query.bindValue(":archived", val);\r
 346                 query.exec();\r
 347         }\r
 348         // Load non-archived notebooks from the database\r
 349         public List<Notebook> getAllArchived() {\r
 350                 Notebook tempNotebook;\r
 351                 List<Notebook> index = new ArrayList<Notebook>();\r
 352                 boolean check;\r
 353                                                 \r
 354         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 355                                         \r
 356                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 357                                 "serviceCreated, serviceUpdated, published, stack, "+\r
 358                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 359                                 "publishingOrder " +\r
 360                                 "from "+dbName+" where archived=true order by name");\r
 361                 if (!check)\r
 362                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 363                 while (query.next()) {\r
 364                         tempNotebook = new Notebook();\r
 365                         tempNotebook.setGuid(query.valueString(0));\r
 366                         int sequence = new Integer(query.valueString(1)).intValue();\r
 367                         tempNotebook.setUpdateSequenceNum(sequence);\r
 368                         tempNotebook.setName(query.valueString(2));\r
 369                         \r
 370                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 371 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 372                         try {\r
 373                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 374                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 375                         } catch (ParseException e) {\r
 376                                 e.printStackTrace();\r
 377                         }\r
 378                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 379                         tempNotebook.setStack(query.valueString(7));\r
 380                         \r
 381                         if (tempNotebook.isPublished()) {\r
 382                                 Publishing p = new Publishing();\r
 383                                 p.setUri(query.valueString(8));\r
 384                                 p.setAscending(query.valueBoolean(9, false));\r
 385                                 p.setPublicDescription(query.valueString(10));\r
 386                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 387                                 tempNotebook.setPublishing(p);\r
 388                         }\r
 389                         \r
 390                         index.add(tempNotebook); \r
 391                 }       \r
 392                 return index;\r
 393         }       \r
 394         // Check for a local/remote notebook\r
 395         public boolean isNotebookLocal(String guid) {\r
 396         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 397                 \r
 398                 query.prepare("Select local from "+dbName+" where guid=:guid");\r
 399                 query.bindValue(":guid", guid);\r
 400                 query.exec();\r
 401                 if (!query.next()) {\r
 402                         return false;\r
 403                 }\r
 404                 boolean returnValue = query.valueBoolean(0, false);\r
 405                 return returnValue;\r
 406         }\r
 407         // Check for a local/remote notebook\r
 408         public boolean isNotebookLinked(String guid) {\r
 409         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 410                 \r
 411                 query.prepare("Select linked from "+dbName+" where guid=:guid");\r
 412                 query.bindValue(":guid", guid);\r
 413                 query.exec();\r
 414                 if (!query.next()) {\r
 415                         return false;\r
 416                 }\r
 417                 boolean returnValue = query.valueBoolean(0, false);\r
 418                 return returnValue;\r
 419         }\r
 420         public boolean isReadOnly(String guid) {\r
 421         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 422                 \r
 423                 query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly=true");\r
 424                 query.bindValue(":guid", guid);\r
 425                 query.exec();\r
 426                 if (!query.next()) {\r
 427                         return false;\r
 428                 }\r
 429                 boolean returnValue = query.valueBoolean(0, false);\r
 430                 return returnValue;\r
 431         }\r
 432         // Update a notebook sequence number\r
 433         public void updateNotebookSequence(String guid, int sequence) {\r
 434                 boolean check;\r
 435         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 436                 check = query.prepare("Update "+dbName+" set sequence=:sequence where guid=:guid");\r
 437                 query.bindValue(":guid", guid);\r
 438                 query.bindValue(":sequence", sequence);\r
 439                 query.exec();\r
 440                 if (!check) {\r
 441                         logger.log(logger.MEDIUM, dbName+" sequence update failed.");\r
 442                         logger.log(logger.MEDIUM, query.lastError());\r
 443                 } \r
 444         }\r
 445         // Update a notebook GUID number\r
 446         public void updateNotebookGuid(String oldGuid, String newGuid) {\r
 447         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 448                 query.prepare("Update "+dbName+" set guid=:newGuid where guid=:oldGuid");\r
 449                 query.bindValue(":oldGuid", oldGuid);\r
 450                 query.bindValue(":newGuid", newGuid);\r
 451                 if (!query.exec()) {\r
 452                         logger.log(logger.MEDIUM, dbName+" guid update failed.");\r
 453                         logger.log(logger.MEDIUM, query.lastError());\r
 454                 } \r
 455                 \r
 456                 // Update any notes containing the notebook guid\r
 457                 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
 458                 query.bindValue(":oldGuid", oldGuid);\r
 459                 query.bindValue(":newGuid", newGuid);\r
 460                 if (!query.exec()) {\r
 461                         logger.log(logger.MEDIUM, dbName+" guid update for note failed.");\r
 462                         logger.log(logger.MEDIUM, query.lastError());\r
 463                 } \r
 464                 \r
 465                 // Update any watch folders with the new guid\r
 466                 query = new NSqlQuery(db.getConnection());\r
 467                 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
 468                 query.bindValue(":oldGuid", oldGuid);\r
 469                 query.bindValue(":newGuid", newGuid);\r
 470                 if (!query.exec()) {\r
 471                         logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
 472                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 473                 }\r
 474         }\r
 475         // Get a list of notes that need to be updated\r
 476         public List <Notebook> getDirty() {\r
 477                 Notebook tempNotebook;\r
 478                 List<Notebook> index = new ArrayList<Notebook>();\r
 479                 boolean check;\r
 480                                                 \r
 481         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 482                                         \r
 483                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 484                                 "serviceCreated, serviceUpdated, published, stack, "+\r
 485                                 "publishinguri, publishingascending, publishingPublicDescription, "+\r
 486                                 "publishingOrder " +\r
 487                                 "from "+dbName+" where isDirty=true and local=false and linked=false");\r
 488                 if (!check) \r
 489                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 490                 while (query.next()) {\r
 491                         tempNotebook = new Notebook();\r
 492                         tempNotebook.setGuid(query.valueString(0));\r
 493                         int sequence = new Integer(query.valueString(1)).intValue();\r
 494                         tempNotebook.setUpdateSequenceNum(sequence);\r
 495                         tempNotebook.setName(query.valueString(2));\r
 496                         \r
 497                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 498                         try {\r
 499                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 500                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 501                         } catch (ParseException e) {\r
 502                                 e.printStackTrace();\r
 503                         }\r
 504                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 505                         tempNotebook.setStack(query.valueString(7));\r
 506                         \r
 507                         if (tempNotebook.isPublished()) {\r
 508                                 Publishing p = new Publishing();\r
 509                                 p.setUri(query.valueString(8));\r
 510                                 p.setAscending(query.valueBoolean(9, false));\r
 511                                 p.setPublicDescription(query.valueString(10));\r
 512                                 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
 513                                 if (p.getPublicDescription().trim().equalsIgnoreCase(""))\r
 514                                         p.setPublicDescription(null);\r
 515                                 tempNotebook.setPublishing(p);\r
 516                         }\r
 517                         \r
 518                         index.add(tempNotebook);\r
 519                 }       \r
 520                 return index;   \r
 521         }\r
 522         // This is a convience method to check if a tag exists & update/create based upon it\r
 523         public void syncNotebook(Notebook notebook, boolean isDirty) {\r
 524                 if (!exists(notebook.getGuid())) {\r
 525                         addNotebook(notebook, isDirty, isDirty);\r
 526                         return;\r
 527                 }\r
 528                 updateNotebook(notebook, isDirty);\r
 529         }\r
 530         // This is a convience method to check if a tag exists & update/create based upon it\r
 531         public void syncLinkedNotebook(Notebook notebook, boolean isDirty, boolean readOnly) {\r
 532                 if (!exists(notebook.getGuid())) {\r
 533                         addNotebook(notebook, isDirty, false, true, readOnly);\r
 534                         return;\r
 535                 }\r
 536                 updateNotebook(notebook, isDirty);\r
 537         }\r
 538         // does a record exist?\r
 539         private boolean exists(String guid) {\r
 540                 \r
 541                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 542                 \r
 543                 query.prepare("Select guid from "+dbName+" where guid=:guid");\r
 544                 query.bindValue(":guid", guid);\r
 545                 if (!query.exec())\r
 546                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 547                 boolean retval = query.next();\r
 548                 return retval;\r
 549         }\r
 550         // Reset the dirty flag.  Typically done after a sync.\r
 551         public void  resetDirtyFlag(String guid) {\r
 552                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 553                 \r
 554                 query.prepare("Update "+dbName+" set isdirty='false' where guid=:guid");\r
 555                 query.bindValue(":guid", guid);\r
 556                 if (!query.exec())\r
 557                         logger.log(logger.EXTREME, "Error resetting "+dbName+" dirty field.");\r
 558         }\r
 559         // Set the default notebook\r
 560         public void setDefaultNotebook(String guid) {\r
 561                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 562                 \r
 563                 query.prepare("Update "+dbName+" set defaultNotebook=false where linked=false");\r
 564                 if (!query.exec())\r
 565                         logger.log(logger.EXTREME, "Error removing default "+dbName+".");\r
 566                 query.prepare("Update "+dbName+" set defaultNotebook=true where guid=:guid where linked=false");\r
 567                 query.bindValue(":guid", guid);\r
 568                 if (!query.exec())\r
 569                         logger.log(logger.EXTREME, "Error setting default "+dbName+".");\r
 570         }\r
 571         \r
 572         // Get a list of all icons\r
 573         public HashMap<String, QIcon> getAllIcons() {\r
 574                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 575                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 576         \r
 577                 if (!query.exec("SELECT guid, icon from "+dbName+" where ARCHIVED  != true"))\r
 578                         logger.log(logger.EXTREME, "Error executing "+dbName+" getAllIcons select.");\r
 579                 while (query.next()) {\r
 580                         if (query.getBlob(1) != null) {\r
 581                                 String guid = query.valueString(0);\r
 582                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 583                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 584                                 values.put(guid, icon);\r
 585                         }\r
 586                 }\r
 587                 return values;\r
 588         }\r
 589         \r
 590         // Get the notebooks custom icon\r
 591         public QIcon getIcon(String guid) {\r
 592                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 593                 \r
 594                 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))\r
 595                         logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 596                 query.bindValue(":guid", guid);\r
 597                 if (!query.exec())\r
 598                         logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");\r
 599                 if (!query.next() || query.getBlob(0) == null)\r
 600                         return null;\r
 601                 \r
 602                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 603                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 604                 return icon;\r
 605         }\r
 606         // Get the notebooks custom icon\r
 607         public QByteArray getIconAsByteArray(String guid) {\r
 608                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 609                 \r
 610                 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))\r
 611                         logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 612                 query.bindValue(":guid", guid);\r
 613                 if (!query.exec())\r
 614                         logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");\r
 615                 if (!query.next() || query.getBlob(0) == null)\r
 616                         return null;\r
 617                 \r
 618                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 619                 return blob;\r
 620         }\r
 621         // Set the notebooks custom icon\r
 622         public void setIcon(String guid, QIcon icon, String type) {\r
 623                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 624                 if (icon == null) {\r
 625                         if (!query.prepare("update "+dbName+" set icon=null where guid=:guid"))\r
 626                                 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 627                 } else {\r
 628                         if (!query.prepare("update "+dbName+" set icon=:icon where guid=:guid"))\r
 629                                 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");\r
 630                         QBuffer buffer = new QBuffer();\r
 631                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 632                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 633                         return;\r
 634                 }\r
 635                 QPixmap p = icon.pixmap(32, 32);\r
 636                 QImage i = p.toImage();\r
 637                 i.save(buffer, type.toUpperCase());\r
 638                 buffer.close();\r
 639                 QByteArray b = new QByteArray(buffer.buffer());\r
 640                 if (!b.isNull() && !b.isEmpty())\r
 641                         query.bindValue(":icon", b.toByteArray());\r
 642                 else\r
 643                         return;\r
 644                 }\r
 645                 query.bindValue(":guid", guid);\r
 646                 if (!query.exec()) \r
 647                         logger.log(logger.LOW, "Error setting "+dbName+" icon. " +query.lastError());\r
 648         }\r
 649         // Set the notebooks custom icon\r
 650         public void setReadOnly(String guid, boolean readOnly) {\r
 651                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 652                 if (!query.prepare("update "+dbName+" set readOnly=:readOnly where guid=:guid"))\r
 653                         logger.log(logger.EXTREME, "Error preparing "+dbName+" read only.");\r
 654                 query.bindValue(":guid", guid);\r
 655                 query.bindValue(":readOnly", readOnly);\r
 656                 if (!query.exec()) \r
 657                         logger.log(logger.LOW, "Error setting "+dbName+" read only. " +query.lastError());\r
 658         }\r
 659 \r
 660         // does a record exist?\r
 661         public String findNotebookByName(String newname) {\r
 662                 \r
 663                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 664                 \r
 665                 query.prepare("Select guid from "+dbName+" where name=:newname");\r
 666                 query.bindValue(":newname", newname);\r
 667                 if (!query.exec())\r
 668                         logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
 669                 String val = null;\r
 670                 if (query.next())\r
 671                         val = query.valueString(0);\r
 672                 return val;\r
 673         }\r
 674         // Get a note tag counts\r
 675         public List<Pair<String,Integer>> getNotebookCounts() {\r
 676                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
 677                 if (notebookCountQuery == null) {\r
 678                         notebookCountQuery = new NSqlQuery(db.getConnection());\r
 679                         notebookCountQuery.prepare("select notebookGuid, count(guid) from note where active=1 group by notebookguid;");\r
 680                 }\r
 681                 if (!notebookCountQuery.exec()) {\r
 682                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
 683                         logger.log(logger.MEDIUM, notebookCountQuery.lastError());\r
 684                         return null;\r
 685                 }\r
 686                 while (notebookCountQuery.next()) {\r
 687                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
 688                         newCount.setFirst(notebookCountQuery.valueString(0));\r
 689                         newCount.setSecond(notebookCountQuery.valueInteger(1));\r
 690                         counts.add(newCount);\r
 691                 }       \r
 692                 return counts;\r
 693         }\r
 694 \r
 695         // Get/Set stacks\r
 696         public void clearStack(String guid) {\r
 697                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 698                 \r
 699                 query.prepare("Update "+dbName+" set stack='' where guid=:guid");\r
 700                 query.bindValue(":guid", guid);\r
 701                 if (!query.exec())\r
 702                         logger.log(logger.EXTREME, "Error clearing "+dbName+" stack.");\r
 703         }\r
 704         // Get/Set stacks\r
 705         public void setStack(String guid, String stack) {\r
 706                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 707                 \r
 708                 query.prepare("Update "+dbName+" set stack=:stack, isDirty=true where guid=:guid");\r
 709                 query.bindValue(":guid", guid);\r
 710                 query.bindValue(":stack", stack);\r
 711                 if (!query.exec())\r
 712                         logger.log(logger.EXTREME, "Error setting notebook stack.");\r
 713         }\r
 714         // Get all stack names\r
 715         public List<String> getAllStackNames() {\r
 716                 List<String> stacks = new ArrayList<String>();\r
 717                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 718                 \r
 719                 if (!query.exec("Select distinct stack from "+dbName)) {\r
 720                         logger.log(logger.EXTREME, "Error getting all stack names.");\r
 721                         return null;\r
 722                 }\r
 723                 \r
 724                 while (query.next()) {\r
 725                         if (query.valueString(0) != null && !query.valueString(0).trim().equals(""))\r
 726                                 stacks.add(query.valueString(0));\r
 727                 }\r
 728                 return stacks;\r
 729         }\r
 730         // Rename a stack\r
 731         public void renameStacks(String oldName, String newName) {\r
 732                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 733                 \r
 734                 if (!query.prepare("update "+dbName+" set stack=:newName where stack=:oldName")) {\r
 735                         logger.log(logger.EXTREME, "Error preparing in renameStacks.");\r
 736                         return;\r
 737                 }\r
 738                 query.bindValue(":oldName", oldName);\r
 739                 query.bindValue(":newName", newName);\r
 740                 if (!query.exec()) {\r
 741                         logger.log(logger.EXTREME, "Error updating stack names");\r
 742                         return;\r
 743                 }\r
 744                 \r
 745                 if (!query.prepare("update SystemIcon set name=:newName where name=:oldName and type='STACK'")) {\r
 746                         logger.log(logger.EXTREME, "Error preparing icon rename in renameStacks.");\r
 747                         return;\r
 748                 }\r
 749                 query.bindValue(":oldName", oldName);\r
 750                 query.bindValue(":newName", newName);\r
 751                 if (!query.exec()) {\r
 752                         logger.log(logger.EXTREME, "Error updating stack names for SystemIcon");\r
 753                         return;\r
 754                 }\r
 755 \r
 756         }\r
 757         // Get/Set stacks\r
 758         public boolean stackExists(String stack) {\r
 759                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 760                 \r
 761                 query.prepare("Select guid from "+dbName+" where stack=:stack limit 1");\r
 762                 query.bindValue(":stack", stack);\r
 763                 if (!query.exec())\r
 764                         logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
 765                 if (query.next())\r
 766                         return true;\r
 767                 else\r
 768                         return false;\r
 769         }\r
 770         // Set Publishing\r
 771         public void setPublishing(String guid, boolean published, Publishing p) {\r
 772                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 773                 \r
 774                 \r
 775                 query.prepare("Update "+dbName+" set publishingPublicDescription=:publishingPublicDescription, " +\r
 776                                 "publishingUri=:publishingUri, publishingOrder=:publishingOrder, published=:published, "+\r
 777                                 "publishingAscending=:publishingAscending, isdirty=true where "+\r
 778                                 "guid=:guid");\r
 779                 query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 780                 query.bindValue(":publishingUri", p.getUri());\r
 781                                 query.bindValue(":publishingOrder", p.getOrder().getValue());\r
 782                 query.bindValue(":publishingAscending", p.isAscending());\r
 783                 query.bindValue(":publishingPublicDescription", p.getPublicDescription());\r
 784                 query.bindValue(":published", published);\r
 785                 query.bindValue(":guid", guid);\r
 786                 if (!query.exec())\r
 787                         logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
 788         }\r
 789         // Get a notebook by uri\r
 790         public String getNotebookByUri(String uri) {\r
 791                 boolean check;\r
 792                                         \r
 793         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 794                                         \r
 795                 check = query.prepare("Select guid " \r
 796                                 +"from "+dbName+" where publishingUri=:uri");\r
 797                 query.bindValue(":uri", uri);\r
 798                 check = query.exec();\r
 799                 if (!check)\r
 800                         logger.log(logger.EXTREME, "Notebook SQL retrieve guid by uri has failed.");\r
 801                 if (query.next()) {\r
 802                         return query.valueString(0);\r
 803                 }       \r
 804                 return null;\r
 805         }       \r
 806         // Is a notebook a linked notebook?\r
 807         public boolean isLinked(String guid) {\r
 808                 boolean check;\r
 809                 \r
 810         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 811                                         \r
 812                 check = query.prepare("Select guid " \r
 813                                 +"from "+dbName+" where guid=:guid and linked=true");\r
 814                 query.bindValue(":guid", guid);\r
 815                 check = query.exec();\r
 816                 if (!check)\r
 817                         logger.log(logger.EXTREME, "Notebook SQL isLinked failed.");\r
 818                 if (query.next()) {\r
 819                         return true;\r
 820                 }       \r
 821                 return false;\r
 822         }\r
 823 \r
 824         // Given a notebook, what tags are valid for it?\r
 825         public List<String> getValidLinkedTags(String guid) {\r
 826                 boolean check;\r
 827                 List<String> tags = new ArrayList<String>();\r
 828                 \r
 829         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 830                 check = query.prepare("select distinct tagGuid from noteTags " +\r
 831                                 "where noteGuid in " +\r
 832                                 "(SELECT guid from note where notebookguid=:guid)");\r
 833                 query.bindValue(":guid", guid);\r
 834                 check = query.exec();\r
 835                 if (!check)\r
 836                         logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
 837                 while (query.next()) {\r
 838                         tags.add(query.valueString(0));\r
 839                 }       \r
 840                 return tags;\r
 841                 \r
 842                 \r
 843         }\r
 844         // Given a notebook, what tags are valid for it?\r
 845         public void deleteLinkedTags(String guid) {\r
 846                 \r
 847         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 848                 query.prepare("select distinct tagguid from noteTags " +\r
 849                                 "where noteGuid in " +\r
 850                                 "(SELECT guid from note where notebookguid=:guid)");\r
 851                 query.bindValue(":guid", guid);\r
 852                 boolean check = query.exec();\r
 853                 if (!check)\r
 854                         logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
 855                 while(query.next()) {\r
 856                         db.getTagTable().expungeTag(query.valueString(0), false);\r
 857                 }\r
 858                 \r
 859                 \r
 860                 query.prepare("delete from note " +\r
 861                                 "where notebookguid=:guid");\r
 862                 query.bindValue(":guid", guid);\r
 863                 check = query.exec();\r
 864                 if (!check)\r
 865                         logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
 866 \r
 867                 \r
 868                 return;\r
 869                 \r
 870                 \r
 871         }\r
 872 }\r
 873 \r