OSDN Git Service

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