OSDN Git Service

Cleanup compiler warning messages and alter backup & restore to handle new database...
[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.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