OSDN Git Service

fafd2eb323a0b7b90d310954c20f12d893a13fdb
[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.List;\r
28 \r
29 import com.evernote.edam.type.Notebook;\r
30 \r
31 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
32 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
33 import cx.fbn.nevernote.utilities.Pair;\r
34 \r
35 public class NotebookTable {\r
36         \r
37         private final ApplicationLogger                 logger;\r
38         DatabaseConnection                                                      db;\r
39         \r
40         // Constructor\r
41         public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
42                 logger = l;\r
43                 db = d;\r
44         }\r
45         // Create the table\r
46         public void createTable() {\r
47                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
48         logger.log(logger.HIGH, "Creating table Notebook...");\r
49         if (!query.exec("Create table Notebook (guid varchar primary key, " +\r
50                         "sequence integer, " +\r
51                         "name varchar, "+\r
52                         "defaultNotebook varchar, "+\r
53                         "serviceCreated timestamp, " +\r
54                         "serviceUpdated timestamp, "+\r
55                         "published boolean, "+\r
56                         "isDirty boolean, "+\r
57                         "autoEncrypt boolean, "+\r
58                         "local boolean, "+\r
59                         "archived boolean)"))                           \r
60                 logger.log(logger.HIGH, "Table Notebook creation FAILED!!!");   \r
61         Notebook newnote = new Notebook();\r
62         newnote.setDefaultNotebook(true);\r
63         newnote.setName("My Notebook");\r
64         newnote.setPublished(false);\r
65         newnote.setGuid("1");\r
66         addNotebook(newnote, true, false);\r
67                 \r
68         }\r
69         // Drop the table\r
70         public void dropTable() {\r
71                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
72                 query.exec("Drop table Notebook");\r
73         }\r
74         // Save an individual notebook\r
75         public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
76                 boolean check;\r
77                 \r
78                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
79         NSqlQuery query = new NSqlQuery(db.getConnection());\r
80                 check = query.prepare("Insert Into Notebook (guid, sequence, name, defaultNotebook, "\r
81                                 +"serviceCreated, serviceUpdated, published, "   \r
82                                 + "isDirty, autoEncrypt," \r
83                                 + "local, archived) Values("\r
84                                 +":guid, :sequence, :name, :defaultNotebook,  "\r
85                                 +":serviceCreated, :serviceUpdated, :published, "\r
86                                 +":isDirty, :autoEncrypt, "\r
87                                 +":local, false)");\r
88                 query.bindValue(":guid", tempNotebook.getGuid());\r
89                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
90                 query.bindValue(":name", tempNotebook.getName());\r
91                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
92                 \r
93                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
94                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));\r
95                 if (serviceUpdated.toString() == null)\r
96                         serviceUpdated = serviceCreated;\r
97                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
98                 query.bindValue(":serviceUpdated", serviceCreated.toString());\r
99                 query.bindValue(":published",tempNotebook.isPublished());\r
100                 \r
101                 if (isDirty)\r
102                         query.bindValue(":isDirty", true);\r
103                 else\r
104                         query.bindValue(":isDirty", false);\r
105                 query.bindValue(":autoEncrypt", false);\r
106                 query.bindValue(":local", local);\r
107 \r
108                 check = query.exec();\r
109                 if (!check) {\r
110                         logger.log(logger.MEDIUM, "Notebook Table insert failed.");\r
111                         logger.log(logger.MEDIUM, query.lastError().toString());\r
112                 }\r
113         }\r
114         // Delete the notebook based on a guid\r
115         public void expungeNotebook(String guid, boolean needsSync) {\r
116                 boolean check;\r
117         NSqlQuery query = new NSqlQuery(db.getConnection());\r
118 \r
119         check = query.prepare("delete from Notebook "\r
120                                 +"where guid=:guid");\r
121                 if (!check) {\r
122                         logger.log(logger.EXTREME, "Notebook SQL delete prepare has failed.");\r
123                         logger.log(logger.EXTREME, query.lastError().toString());\r
124                 }\r
125                 query.bindValue(":guid", guid);\r
126                 check = query.exec();\r
127                 if (!check) \r
128                         logger.log(logger.MEDIUM, "Notebook delete failed.");\r
129                 \r
130                 // Signal the parent that work needs to be done\r
131                 if  (needsSync) {\r
132                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
133                         deletedTable.addDeletedItem(guid, "Notebook");\r
134                 }\r
135         }\r
136         // Update a notebook\r
137         public void updateNotebook(Notebook tempNotebook, boolean isDirty) {\r
138                 boolean check;\r
139                 \r
140                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
141                 \r
142         NSqlQuery query = new NSqlQuery(db.getConnection());\r
143         check = query.prepare("Update Notebook set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +\r
144                         "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+\r
145                                 "published=:published, isDirty=:isDirty where guid=:guid ");\r
146                 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
147                 query.bindValue(":name", tempNotebook.getName());\r
148                 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());\r
149 \r
150                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
151                 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));                      \r
152                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
153                 query.bindValue(":serviceUpdated", serviceUpdated.toString());\r
154                 \r
155                 query.bindValue(":published", tempNotebook.isPublished());\r
156                 query.bindValue(":isDirty", isDirty);\r
157                 query.bindValue(":guid", tempNotebook.getGuid());\r
158                 \r
159                 check = query.exec();\r
160                 if (!check) {\r
161                         logger.log(logger.MEDIUM, "Notebook Table update failed.");\r
162                         logger.log(logger.MEDIUM, query.lastError().toString());\r
163                 }\r
164         }\r
165         // Load notebooks from the database\r
166         public List<Notebook> getAll() {\r
167                 Notebook tempNotebook;\r
168                 List<Notebook> index = new ArrayList<Notebook>();\r
169                 boolean check;\r
170                                         \r
171         NSqlQuery query = new NSqlQuery(db.getConnection());\r
172                                         \r
173                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
174                                 "serviceCreated, "+\r
175                                 "serviceUpdated, "+\r
176                                 "published, defaultNotebook from Notebook order by name");\r
177                 if (!check)\r
178                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
179                 while (query.next()) {\r
180                         tempNotebook = new Notebook();\r
181                         tempNotebook.setGuid(query.valueString(0));\r
182                         int sequence = new Integer(query.valueString(1)).intValue();\r
183                         tempNotebook.setUpdateSequenceNum(sequence);\r
184                         tempNotebook.setName(query.valueString(2));\r
185                         DateFormat indfm = null;\r
186                         try {\r
187                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
188 //                              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
189                         } catch (Exception e) { }\r
190                         try {\r
191                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
192                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
193                         } catch (ParseException e) {\r
194                                 e.printStackTrace();\r
195                         }\r
196                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
197                         tempNotebook.setDefaultNotebook(new Boolean(query.valueString(7)));\r
198                         index.add(tempNotebook); \r
199                 }       \r
200                 return index;\r
201         }       \r
202         public List<Notebook> getAllLocal() {\r
203                 Notebook tempNotebook;\r
204                 List<Notebook> index = new ArrayList<Notebook>();\r
205                 boolean check;\r
206 \r
207         NSqlQuery query = new NSqlQuery(db.getConnection());\r
208                                         \r
209                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
210                                 "serviceCreated, serviceUpdated, published from Notebook where local=true order by name");\r
211                 if (!check)\r
212                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
213                 while (query.next()) {\r
214                         tempNotebook = new Notebook();\r
215                         tempNotebook.setGuid(query.valueString(0));\r
216                         int sequence = new Integer(query.valueString(1)).intValue();\r
217                         tempNotebook.setUpdateSequenceNum(sequence);\r
218                         tempNotebook.setName(query.valueString(2));\r
219                         \r
220                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
221 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
222                         try {\r
223                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
224                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
225                         } catch (ParseException e) {\r
226                                 e.printStackTrace();\r
227                         }\r
228                         index.add(tempNotebook); \r
229                 }       \r
230                 return index;\r
231         }\r
232         // Archive or un-archive a notebook\r
233         public void setArchived(String guid, boolean val) {\r
234                 boolean check;                  \r
235         NSqlQuery query = new NSqlQuery(db.getConnection());                                    \r
236                 check = query.prepare("Update notebook set archived=:archived where guid=:guid");\r
237                 if (!check)\r
238                         logger.log(logger.EXTREME, "Notebook SQL archive update has failed.");\r
239                 query.bindValue(":guid", guid);\r
240                 query.bindValue(":archived", val);\r
241                 query.exec();\r
242         }\r
243         // Load non-archived notebooks from the database\r
244         public List<Notebook> getAllArchived() {\r
245                 Notebook tempNotebook;\r
246                 List<Notebook> index = new ArrayList<Notebook>();\r
247                 boolean check;\r
248                                                 \r
249         NSqlQuery query = new NSqlQuery(db.getConnection());\r
250                                         \r
251                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
252                                 "serviceCreated, serviceUpdated, published from Notebook where archived=true order by name");\r
253                 if (!check)\r
254                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
255                 while (query.next()) {\r
256                         tempNotebook = new Notebook();\r
257                         tempNotebook.setGuid(query.valueString(0));\r
258                         int sequence = new Integer(query.valueString(1)).intValue();\r
259                         tempNotebook.setUpdateSequenceNum(sequence);\r
260                         tempNotebook.setName(query.valueString(2));\r
261                         \r
262                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
263 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
264                         try {\r
265                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
266                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
267                         } catch (ParseException e) {\r
268                                 e.printStackTrace();\r
269                         }\r
270                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
271                         index.add(tempNotebook); \r
272                 }       \r
273                 return index;\r
274         }       \r
275         // Check for a local/remote notebook\r
276         public boolean isNotebookLocal(String guid) {\r
277         NSqlQuery query = new NSqlQuery(db.getConnection());\r
278                 \r
279                 query.prepare("Select local from Notebook where guid=:guid");\r
280                 query.bindValue(":guid", guid);\r
281                 query.exec();\r
282                 if (!query.next()) {\r
283                         return false;\r
284                 }\r
285                 boolean returnValue = query.valueBoolean(0, false);\r
286                 return returnValue;\r
287         }\r
288         // Update a notebook sequence number\r
289         public void updateNotebookSequence(String guid, int sequence) {\r
290                 boolean check;\r
291         NSqlQuery query = new NSqlQuery(db.getConnection());\r
292                 check = query.prepare("Update Notebook set sequence=:sequence where guid=:guid");\r
293                 query.bindValue(":guid", guid);\r
294                 query.bindValue(":sequence", sequence);\r
295                 query.exec();\r
296                 if (!check) {\r
297                         logger.log(logger.MEDIUM, "Notebook sequence update failed.");\r
298                         logger.log(logger.MEDIUM, query.lastError());\r
299                 } \r
300         }\r
301         // Update a notebook GUID number\r
302         public void updateNotebookGuid(String oldGuid, String newGuid) {\r
303         NSqlQuery query = new NSqlQuery(db.getConnection());\r
304                 query.prepare("Update Notebook set guid=:newGuid where guid=:oldGuid");\r
305                 query.bindValue(":oldGuid", oldGuid);\r
306                 query.bindValue(":newGuid", newGuid);\r
307                 if (!query.exec()) {\r
308                         logger.log(logger.MEDIUM, "Notebook guid update failed.");\r
309                         logger.log(logger.MEDIUM, query.lastError());\r
310                 } \r
311                 \r
312                 // Update any notes containing the notebook guid\r
313                 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");\r
314                 query.bindValue(":oldGuid", oldGuid);\r
315                 query.bindValue(":newGuid", newGuid);\r
316                 if (!query.exec()) {\r
317                         logger.log(logger.MEDIUM, "Notebook guid update for note failed.");\r
318                         logger.log(logger.MEDIUM, query.lastError());\r
319                 } \r
320                 \r
321                 // Update any watch folders with the new guid\r
322                 query = new NSqlQuery(db.getConnection());\r
323                 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");\r
324                 query.bindValue(":oldGuid", oldGuid);\r
325                 query.bindValue(":newGuid", newGuid);\r
326                 if (!query.exec()) {\r
327                         logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
328                         logger.log(logger.MEDIUM, query.lastError().toString());\r
329                 }               \r
330         }\r
331         // Get a list of notes that need to be updated\r
332         public List <Notebook> getDirty() {\r
333                 Notebook tempNotebook;\r
334                 List<Notebook> index = new ArrayList<Notebook>();\r
335                 boolean check;\r
336                                                 \r
337                 \r
338         NSqlQuery query = new NSqlQuery(db.getConnection());\r
339                                         \r
340                 check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
341                                 "serviceCreated, serviceUpdated, published from Notebook where isDirty = true and local=false");\r
342                 if (!check) \r
343                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
344                 while (query.next()) {\r
345                         tempNotebook = new Notebook();\r
346                         tempNotebook.setGuid(query.valueString(0));\r
347                         int sequence = new Integer(query.valueString(1)).intValue();\r
348                         tempNotebook.setUpdateSequenceNum(sequence);\r
349                         tempNotebook.setName(query.valueString(2));\r
350                         \r
351                         DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
352 //                      indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
353                         try {\r
354                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
355                                 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
356                         } catch (ParseException e) {\r
357                                 e.printStackTrace();\r
358                         }\r
359                         tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
360                         index.add(tempNotebook); \r
361                 }       \r
362                 return index;   \r
363         }\r
364         // This is a convience method to check if a tag exists & update/create based upon it\r
365         public void syncNotebook(Notebook notebook, boolean isDirty) {\r
366                 if (!exists(notebook.getGuid())) {\r
367                         addNotebook(notebook, isDirty, isDirty);\r
368                         return;\r
369                 }\r
370                 updateNotebook(notebook, isDirty);\r
371         }\r
372         // does a record exist?\r
373         private boolean exists(String guid) {\r
374                 \r
375                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
376                 \r
377                 query.prepare("Select guid from notebook where guid=:guid");\r
378                 query.bindValue(":guid", guid);\r
379                 if (!query.exec())\r
380                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
381                 boolean retval = query.next();\r
382                 return retval;\r
383         }\r
384         // Reset the dirty flag.  Typically done after a sync.\r
385         public void  resetDirtyFlag(String guid) {\r
386                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
387                 \r
388                 query.prepare("Update notebook set isdirty='false' where guid=:guid");\r
389                 query.bindValue(":guid", guid);\r
390                 if (!query.exec())\r
391                         logger.log(logger.EXTREME, "Error resetting notebook dirty field.");\r
392         }\r
393         // Set the default notebook\r
394         public void setDefaultNotebook(String guid) {\r
395                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
396                 \r
397                 query.prepare("Update notebook set defaultNotebook=false");\r
398                 if (!query.exec())\r
399                         logger.log(logger.EXTREME, "Error removing default notebook.");\r
400                 query.prepare("Update notebook set defaultNotebook=true where guid = :guid");\r
401                 query.bindValue(":guid", guid);\r
402                 if (!query.exec())\r
403                         logger.log(logger.EXTREME, "Error setting default notebook.");\r
404         }\r
405         \r
406         \r
407 \r
408         // does a record exist?\r
409         public String findNotebookByName(String newname) {\r
410                 \r
411                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
412                 \r
413                 query.prepare("Select guid from notebook where name=:newname");\r
414                 query.bindValue(":newname", newname);\r
415                 if (!query.exec())\r
416                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
417                 String val = null;\r
418                 if (query.next())\r
419                         val = query.valueString(0);\r
420                 return val;\r
421         }\r
422         // Get a note tag counts\r
423         public List<Pair<String,Integer>> getNotebookCounts() {\r
424                 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();              \r
425                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
426                 if (!query.exec("select notebookGuid, count(guid) from note where active=1 group by notebookguid;")) {\r
427                         logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
428                         logger.log(logger.MEDIUM, query.lastError());\r
429                         return null;\r
430                 }\r
431                 while (query.next()) {\r
432                         Pair<String,Integer> newCount = new Pair<String,Integer>();\r
433                         newCount.setFirst(query.valueString(0));\r
434                         newCount.setSecond(query.valueInteger(1));\r
435                         counts.add(newCount);\r
436                 }       \r
437                 return counts;\r
438         }\r
439 \r
440 }\r
441 \r