OSDN Git Service

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