OSDN Git Service

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