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