OSDN Git Service

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