OSDN Git Service

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