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.Notebook;\r
  31 import com.trolltech.qt.core.QBuffer;\r
  32 import com.trolltech.qt.core.QByteArray;\r
  33 import com.trolltech.qt.core.QIODevice;\r
  34 import com.trolltech.qt.gui.QIcon;\r
  35 import com.trolltech.qt.gui.QImage;\r
  36 import com.trolltech.qt.gui.QPixmap;\r
  37 \r
  38 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  39 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  40 import cx.fbn.nevernote.utilities.Pair;\r
  41 \r
  42 public class NotebookTable {\r
  43         \r
  44         private final ApplicationLogger                 logger;\r
  45         DatabaseConnection                                                      db;\r
  46         \r
  47         // Constructor\r
  48         public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
  49                 logger = l;\r
  50                 db = d;\r
  51         }\r
  52         // Create the table\r
  53         public void createTable() {\r
  54                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  55         logger.log(logger.HIGH, "Creating table Notebook...");\r
  56         if (!query.exec("Create table Notebook (guid varchar primary key, " +\r
  57                         "sequence integer, " +\r
  58                         "name varchar, "+\r
  59                         "defaultNotebook varchar, "+\r
  60                         "serviceCreated timestamp, " +\r
  61                         "serviceUpdated timestamp, "+\r
  62                         "published boolean, "+\r
  63                         "isDirty boolean, "+\r
  64                         "autoEncrypt boolean, "+\r
  65                         "local boolean, "+\r
  66                         "archived boolean)"))                           \r
  67                 logger.log(logger.HIGH, "Table Notebook creation FAILED!!!");   \r
  68         Notebook newnote = new Notebook();\r
  69         newnote.setDefaultNotebook(true);\r
  70         newnote.setName("My Notebook");\r
  71         newnote.setPublished(false);\r
  72         newnote.setGuid("1");\r
  73         addNotebook(newnote, true, false);\r
  74                 \r
  75         }\r
  76         // Drop the table\r
  77         public void dropTable() {\r
  78                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  79                 query.exec("Drop table Notebook");\r
  80         }\r
  81         // Save an individual notebook\r
  82         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
  83                 boolean check;\r
  84                 \r
  85                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
  86         NSqlQuery query = new NSqlQuery(db.getConnection());\r
  87                 check = query.prepare("Insert Into Notebook (guid, sequence, name, defaultNotebook, "\r
  88                                 +"serviceCreated, serviceUpdated, published, "   \r
  89                                 + "isDirty, autoEncrypt, stack" \r
  90                                 + "local, archived) Values("\r
  91                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
  92                                 +":serviceCreated, :serviceUpdated, :published, "\r
  93                                 +":isDirty, :autoEncrypt, "\r
  94                                 +":local, false, :stack)");\r
  95                 query.bindValue(":guid", tempNotebook.getGuid());\r
  96                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
  97                 query.bindValue(":name", tempNotebook.getName());\r
  98                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
  99                 \r
 100                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 101                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
 102                 if (serviceUpdated.toString() == null)\r
 103                         serviceUpdated = serviceCreated;\r
 104                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 105                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
 106                 query.bindValue(":published",tempNotebook.isPublished());\r
 107                 \r
 108                 if (isDirty)\r
 109                         query.bindValue(":isDirty", true);\r
 110                 else\r
 111                         query.bindValue(":isDirty", false);\r
 112                 query.bindValue(":autoEncrypt", false);\r
 113                 query.bindValue(":local", local);\r
 114                 query.bindValue(":stack", tempNotebook.getStack());\r
 115 \r
 116                 check = query.exec();\r
 117                 if (!check) {\r
 118                         logger.log(logger.MEDIUM, "Notebook Table insert failed.");\r
 119                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 120                 }\r
 121         }\r
 122         // Delete the notebook based on a guid\r
 123         public void expungeNotebook(String guid, boolean needsSync) {\r
 124                 boolean check;\r
 125         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 126 \r
 127         check = query.prepare("delete from Notebook "\r
 128                                 +"where guid=:guid");\r
 129                 if (!check) {\r
 130                         logger.log(logger.EXTREME, "Notebook SQL delete prepare has failed.");\r
 131                         logger.log(logger.EXTREME, query.lastError().toString());\r
 132                 }\r
 133                 query.bindValue(":guid", guid);\r
 134                 check = query.exec();\r
 135                 if (!check) \r
 136                         logger.log(logger.MEDIUM, "Notebook delete failed.");\r
 137                 \r
 138                 // Signal the parent that work needs to be done\r
 139                 if  (needsSync) {\r
 140                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 141                         deletedTable.addDeletedItem(guid, "Notebook");\r
 142                 }\r
 143         }\r
 144         // Update a notebook\r
 145         public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
 146                 boolean check;\r
 147                 \r
 148                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 149                 \r
 150         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 151         check = query.prepare("Update Notebook set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
 152                         "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
 153                                 "published=:published, isDirty=:isDirty where guid=:guid ");\r
 154                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
 155                 query.bindValue(":name", tempNotebook.getName());\r
 156                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
 157 \r
 158                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
 159                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
 160                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
 161                 query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
 162                 \r
 163                 query.bindValue(":published", tempNotebook.isPublished());\r
 164                 query.bindValue(":isDirty", isDirty);\r
 165                 query.bindValue(":guid", tempNotebook.getGuid());\r
 166                 \r
 167                 check = query.exec();\r
 168                 if (!check) {\r
 169                         logger.log(logger.MEDIUM, "Notebook Table update failed.");\r
 170                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 171                 }\r
 172         }\r
 173         // Load notebooks from the database\r
 174         public List<Notebook> getAll() {\r
 175                 Notebook tempNotebook;\r
 176                 List<Notebook> index = new ArrayList<Notebook>();\r
 177                 boolean check;\r
 178                                         \r
 179         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 180                                         \r
 181                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 182                                 "serviceCreated, "+\r
 183                                 "serviceUpdated, "+\r
 184                                 "published, defaultNotebook, stack from Notebook order by name");\r
 185                 if (!check)\r
 186                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 187                 while (query.next()) {\r
 188                         tempNotebook = new Notebook();\r
 189                         tempNotebook.setGuid(query.valueString(0));\r
 190                         int sequence = new Integer(query.valueString(1)).intValue();\r
 191                         tempNotebook.setUpdateSequenceNum(sequence);\r
 192                         tempNotebook.setName(query.valueString(2));\r
 193                         DateFormat indfm = null;\r
 194                         try {\r
 195                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 196 //                              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 197                         } catch (Exception e) { }\r
 198                         try {\r
 199                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 200                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 201                         } catch (ParseException e) {\r
 202                                 e.printStackTrace();\r
 203                         }\r
 204                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 205                         tempNotebook.setDefaultNotebook(new Boolean(query.valueString(7)));\r
 206                         tempNotebook.setStack(query.valueString(8));\r
 207                         index.add(tempNotebook); \r
 208                 }       \r
 209                 return index;\r
 210         }       \r
 211         public List<Notebook> getAllLocal() {\r
 212                 Notebook tempNotebook;\r
 213                 List<Notebook> index = new ArrayList<Notebook>();\r
 214                 boolean check;\r
 215 \r
 216         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 217                                         \r
 218                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 219                                 "serviceCreated, serviceUpdated, published, stack from Notebook where local=true order by name");\r
 220                 if (!check)\r
 221                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 222                 while (query.next()) {\r
 223                         tempNotebook = new Notebook();\r
 224                         tempNotebook.setGuid(query.valueString(0));\r
 225                         int sequence = new Integer(query.valueString(1)).intValue();\r
 226                         tempNotebook.setUpdateSequenceNum(sequence);\r
 227                         tempNotebook.setName(query.valueString(2));\r
 228                         \r
 229                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 230 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 231                         try {\r
 232                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 233                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 234                         } catch (ParseException e) {\r
 235                                 e.printStackTrace();\r
 236                         }\r
 237                         tempNotebook.setStack(query.valueString(7));\r
 238                         index.add(tempNotebook); \r
 239                 }       \r
 240                 return index;\r
 241         }\r
 242         // Archive or un-archive a notebook\r
 243         public void setArchived(String guid, boolean val) {\r
 244                 boolean check;                  \r
 245         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
 246                 check = query.prepare("Update notebook set archived=:archived where guid=:guid");\r
 247                 if (!check)\r
 248                         logger.log(logger.EXTREME, "Notebook SQL archive update has failed.");\r
 249                 query.bindValue(":guid", guid);\r
 250                 query.bindValue(":archived", val);\r
 251                 query.exec();\r
 252         }\r
 253         // Load non-archived notebooks from the database\r
 254         public List<Notebook> getAllArchived() {\r
 255                 Notebook tempNotebook;\r
 256                 List<Notebook> index = new ArrayList<Notebook>();\r
 257                 boolean check;\r
 258                                                 \r
 259         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 260                                         \r
 261                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 262                                 "serviceCreated, serviceUpdated, published, stack from Notebook where archived=true order by name");\r
 263                 if (!check)\r
 264                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 265                 while (query.next()) {\r
 266                         tempNotebook = new Notebook();\r
 267                         tempNotebook.setGuid(query.valueString(0));\r
 268                         int sequence = new Integer(query.valueString(1)).intValue();\r
 269                         tempNotebook.setUpdateSequenceNum(sequence);\r
 270                         tempNotebook.setName(query.valueString(2));\r
 271                         \r
 272                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 273 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 274                         try {\r
 275                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 276                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 277                         } catch (ParseException e) {\r
 278                                 e.printStackTrace();\r
 279                         }\r
 280                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 281                         tempNotebook.setStack(query.valueString(7));\r
 282                         index.add(tempNotebook); \r
 283                 }       \r
 284                 return index;\r
 285         }       \r
 286         // Check for a local/remote notebook\r
 287         public boolean isNotebookLocal(String guid) {\r
 288         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 289                 \r
 290                 query.prepare("Select local from Notebook where guid=:guid");\r
 291                 query.bindValue(":guid", guid);\r
 292                 query.exec();\r
 293                 if (!query.next()) {\r
 294                         return false;\r
 295                 }\r
 296                 boolean returnValue = query.valueBoolean(0, false);\r
 297                 return returnValue;\r
 298         }\r
 299         // Update a notebook sequence number\r
 300         public void updateNotebookSequence(String guid, int sequence) {\r
 301                 boolean check;\r
 302         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 303                 check = query.prepare("Update Notebook set sequence=:sequence where guid=:guid");\r
 304                 query.bindValue(":guid", guid);\r
 305                 query.bindValue(":sequence", sequence);\r
 306                 query.exec();\r
 307                 if (!check) {\r
 308                         logger.log(logger.MEDIUM, "Notebook sequence update failed.");\r
 309                         logger.log(logger.MEDIUM, query.lastError());\r
 310                 } \r
 311         }\r
 312         // Update a notebook GUID number\r
 313         public void updateNotebookGuid(String oldGuid, String newGuid) {\r
 314         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 315                 query.prepare("Update Notebook set guid=:newGuid where guid=:oldGuid");\r
 316                 query.bindValue(":oldGuid", oldGuid);\r
 317                 query.bindValue(":newGuid", newGuid);\r
 318                 if (!query.exec()) {\r
 319                         logger.log(logger.MEDIUM, "Notebook guid update failed.");\r
 320                         logger.log(logger.MEDIUM, query.lastError());\r
 321                 } \r
 322                 \r
 323                 // Update any notes containing the notebook guid\r
 324                 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
 325                 query.bindValue(":oldGuid", oldGuid);\r
 326                 query.bindValue(":newGuid", newGuid);\r
 327                 if (!query.exec()) {\r
 328                         logger.log(logger.MEDIUM, "Notebook guid update for note failed.");\r
 329                         logger.log(logger.MEDIUM, query.lastError());\r
 330                 } \r
 331                 \r
 332                 // Update any watch folders with the new guid\r
 333                 query = new NSqlQuery(db.getConnection());\r
 334                 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
 335                 query.bindValue(":oldGuid", oldGuid);\r
 336                 query.bindValue(":newGuid", newGuid);\r
 337                 if (!query.exec()) {\r
 338                         logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
 339                         logger.log(logger.MEDIUM, query.lastError().toString());\r
 340                 }               \r
 341         }\r
 342         // Get a list of notes that need to be updated\r
 343         public List <Notebook> getDirty() {\r
 344                 Notebook tempNotebook;\r
 345                 List<Notebook> index = new ArrayList<Notebook>();\r
 346                 boolean check;\r
 347                                                 \r
 348                 \r
 349         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 350                                         \r
 351                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
 352                                 "serviceCreated, serviceUpdated, published from Notebook where isDirty = true and local=false");\r
 353                 if (!check) \r
 354                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
 355                 while (query.next()) {\r
 356                         tempNotebook = new Notebook();\r
 357                         tempNotebook.setGuid(query.valueString(0));\r
 358                         int sequence = new Integer(query.valueString(1)).intValue();\r
 359                         tempNotebook.setUpdateSequenceNum(sequence);\r
 360                         tempNotebook.setName(query.valueString(2));\r
 361                         \r
 362                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 363 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 364                         try {\r
 365                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
 366                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
 367                         } catch (ParseException e) {\r
 368                                 e.printStackTrace();\r
 369                         }\r
 370                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
 371                         index.add(tempNotebook); \r
 372                 }       \r
 373                 return index;   \r
 374         }\r
 375         // This is a convience method to check if a tag exists & update/create based upon it\r
 376         public void syncNotebook(Notebook notebook, boolean isDirty) {\r
 377                 if (!exists(notebook.getGuid())) {\r
 378                         addNotebook(notebook, isDirty, isDirty);\r
 379                         return;\r
 380                 }\r
 381                 updateNotebook(notebook, isDirty);\r
 382         }\r
 383         // does a record exist?\r
 384         private boolean exists(String guid) {\r
 385                 \r
 386                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 387                 \r
 388                 query.prepare("Select guid from notebook where guid=:guid");\r
 389                 query.bindValue(":guid", guid);\r
 390                 if (!query.exec())\r
 391                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
 392                 boolean retval = query.next();\r
 393                 return retval;\r
 394         }\r
 395         // Reset the dirty flag.  Typically done after a sync.\r
 396         public void  resetDirtyFlag(String guid) {\r
 397                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 398                 \r
 399                 query.prepare("Update notebook set isdirty='false' where guid=:guid");\r
 400                 query.bindValue(":guid", guid);\r
 401                 if (!query.exec())\r
 402                         logger.log(logger.EXTREME, "Error resetting notebook dirty field.");\r
 403         }\r
 404         // Set the default notebook\r
 405         public void setDefaultNotebook(String guid) {\r
 406                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 407                 \r
 408                 query.prepare("Update notebook set defaultNotebook=false");\r
 409                 if (!query.exec())\r
 410                         logger.log(logger.EXTREME, "Error removing default notebook.");\r
 411                 query.prepare("Update notebook set defaultNotebook=true where guid = :guid");\r
 412                 query.bindValue(":guid", guid);\r
 413                 if (!query.exec())\r
 414                         logger.log(logger.EXTREME, "Error setting default notebook.");\r
 415         }\r
 416         \r
 417         // Get a list of all icons\r
 418         public HashMap<String, QIcon> getAllIcons() {\r
 419                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
 420                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 421         \r
 422                 if (!query.exec("SELECT guid, icon from notebook where ARCHIVED  != true"))\r
 423                         logger.log(logger.EXTREME, "Error executing notebook getAllIcons select.");\r
 424                 while (query.next()) {\r
 425                         if (query.getBlob(1) != null) {\r
 426                                 String guid = query.valueString(0);\r
 427                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
 428                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 429                                 values.put(guid, icon);\r
 430                         }\r
 431                 }\r
 432                 return values;\r
 433         }\r
 434         \r
 435         // Get the notebooks custom icon\r
 436         public QIcon getIcon(String guid) {\r
 437                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 438                 \r
 439                 if (!query.prepare("Select icon from notebook where guid=:guid"))\r
 440                         logger.log(logger.EXTREME, "Error preparing notebook icon select.");\r
 441                 query.bindValue(":guid", guid);\r
 442                 if (!query.exec())\r
 443                         logger.log(logger.EXTREME, "Error finding notebook icon.");\r
 444                 if (!query.next() || query.getBlob(0) == null)\r
 445                         return null;\r
 446                 \r
 447                 QByteArray blob = new QByteArray(query.getBlob(0));\r
 448                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
 449                 return icon;\r
 450         }\r
 451         // Set the notebooks custom icon\r
 452         public void setIcon(String guid, QIcon icon, String type) {\r
 453                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 454                 if (icon == null) {\r
 455                         if (!query.prepare("update notebook set icon=null where guid=:guid"))\r
 456                                 logger.log(logger.EXTREME, "Error preparing notebook icon select.");\r
 457                 } else {\r
 458                         if (!query.prepare("update notebook set icon=:icon where guid=:guid"))\r
 459                                 logger.log(logger.EXTREME, "Error preparing notebook icon select.");\r
 460                         QBuffer buffer = new QBuffer();\r
 461                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
 462                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
 463                         return;\r
 464                 }\r
 465                 QPixmap p = icon.pixmap(32, 32);\r
 466                 QImage i = p.toImage();\r
 467                 i.save(buffer, type.toUpperCase());\r
 468                 buffer.close();\r
 469                 QByteArray b = new QByteArray(buffer.buffer());\r
 470                 if (!b.isNull() && !b.isEmpty())\r
 471                         query.bindValue(":icon", b.toByteArray());\r
 472                 else\r
 473                         return;\r
 474                 }\r
 475                 query.bindValue(":guid", guid);\r
 476                 if (!query.exec()) \r
 477                         logger.log(logger.LOW, "Error setting notebook icon. " +query.lastError());\r
 478         }\r
 479 \r
 480         // does a record exist?\r
 481         public String findNotebookByName(String newname) {\r
 482                 \r
 483                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 484                 \r
 485                 query.prepare("Select guid from notebook where name=:newname");\r
 486                 query.bindValue(":newname", newname);\r
 487                 if (!query.exec())\r
 488                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
 489                 String val = null;\r
 490                 if (query.next())\r
 491                         val = query.valueString(0);\r
 492                 return val;\r
 493         }\r
 494         // Get a note tag counts\r
 495         public List<Pair<String,Integer>> getNotebookCounts() {\r
 496                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
 497                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 498                 if (!query.exec("select notebookGuid, count(guid) from note where active=1 group by notebookguid;")) {\r
 499                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
 500                         logger.log(logger.MEDIUM, query.lastError());\r
 501                         return null;\r
 502                 }\r
 503                 while (query.next()) {\r
 504                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
 505                         newCount.setFirst(query.valueString(0));\r
 506                         newCount.setSecond(query.valueInteger(1));\r
 507                         counts.add(newCount);\r
 508                 }       \r
 509                 return counts;\r
 510         }\r
 511 \r
 512 }\r
 513 \r