OSDN Git Service

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