OSDN Git Service

Cleanup for 0.95 shell problems
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / LinkedNotebookTable.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 <<<<<<< HEAD
24 =======
25 import java.text.SimpleDateFormat;\r
26 >>>>>>> Add linked & shared notebook tables
27 import java.util.ArrayList;\r
28 import java.util.List;\r
29 \r
30 import com.evernote.edam.type.LinkedNotebook;\r
31 \r
32 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
33 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
34 \r
35 public class LinkedNotebookTable {\r
36         \r
37         private final ApplicationLogger                 logger;\r
38         DatabaseConnection                                                      db;\r
39         \r
40         // Constructor\r
41         public LinkedNotebookTable(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 LinkedNotebook...");\r
49 <<<<<<< HEAD
50         if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " +\r
51 =======
52         if (!query.exec("Create table LinkedNotebook (id long primary key, " +\r
53 >>>>>>> Add linked & shared notebook tables
54                         "shareName VarChar, " +\r
55                         "username VarChar, "+\r
56                         "shardID VarChar, " +\r
57                         "shareKey VarChar, " +\r
58 <<<<<<< HEAD
59                         "uri VarChar, " +\r
60                         "updateSequenceNumber Long," +\r
61                         "lastSequenceNumber Integer," +\r
62                         "lastSequenceDate Long," +\r
63                         "notebookGuid VarChar," +\r
64 =======
65                         "url VarChar, " +\r
66                         "icon blob, " +\r
67 >>>>>>> Add linked & shared notebook tables
68                         "isDirty boolean)"))                            \r
69                 logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");   \r
70         }\r
71         // Drop the table\r
72         public void dropTable() {\r
73                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
74                 query.exec("Drop table LinkedNotebook");\r
75         }\r
76         // Save an individual notebook\r
77 <<<<<<< HEAD
78         public void addNotebook(LinkedNotebook tempNotebook,  boolean isDirty) {\r
79                 boolean check;\r
80                 \r
81         NSqlQuery query = new NSqlQuery(db.getConnection());\r
82                 check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username,  "\r
83                                 +"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, "\r
84                                 + "lastSequenceDate, notebookGuid) "   \r
85                                 + " Values("\r
86                                 +":guid, :shareName, :username, "\r
87                                 +":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)");\r
88                 query.bindValue(":guid", tempNotebook.getGuid());\r
89 =======
90         public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
91                 boolean check;\r
92                 \r
93                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
94         NSqlQuery query = new NSqlQuery(db.getConnection());\r
95                 check = query.prepare("Insert Into LinkedNotebook (id, shareName, username,  "\r
96                                 +"shardId, shareKey, url, isDirty) "   \r
97                                 + " Values("\r
98                                 +":id, :shareName, :username, "\r
99                                 +":shardId, :shareKey, :url, :isDirty)");\r
100                 query.bindValue(":id", tempNotebook.getId());\r
101 >>>>>>> Add linked & shared notebook tables
102                 query.bindValue(":shareName", tempNotebook.getShareName());\r
103                 query.bindValue(":username", tempNotebook.getUsername());\r
104                 query.bindValue(":shardId", tempNotebook.getShardId());\r
105                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
106 <<<<<<< HEAD
107                 query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
108                 query.bindValue(":uri", tempNotebook.getUri());\r
109                 query.bindValue(":notebookGuid", "");\r
110 =======
111                 query.bindValue(":url", tempNotebook.getUri());\r
112 >>>>>>> Add linked & shared notebook tables
113                 \r
114                 if (isDirty)\r
115                         query.bindValue(":isDirty", true);\r
116                 else\r
117                         query.bindValue(":isDirty", false);\r
118 \r
119                 check = query.exec();\r
120                 if (!check) {\r
121                         logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");\r
122                         logger.log(logger.MEDIUM, query.lastError().toString());\r
123 <<<<<<< HEAD
124                         return;\r
125                 }\r
126         }\r
127         // Delete the notebook based on a guid\r
128         public void expungeNotebook(String id, boolean needsSync) {\r
129                 boolean check;\r
130                 \r
131                 // First, delete any tags associated with this notebook\r
132                 String notebookGuid = getNotebookGuid(id);\r
133                 db.getNotebookTable().deleteLinkedTags(notebookGuid);\r
134                 \r
135                 // Now, delete any notes associated with this notebook\r
136                 List<String> notes = db.getNoteTable().getNotesByNotebook(notebookGuid);\r
137                 for (int i=0; i<notes.size(); i++) {\r
138                         db.getNoteTable().expungeNote(notes.get(i), true, needsSync);\r
139                 }\r
140                 \r
141                 // Delete the notebook record\r
142                 db.getNotebookTable().expungeNotebook(notebookGuid, needsSync);\r
143                 \r
144                 // Finally, delete the linked notebook object itself\r
145         NSqlQuery query = new NSqlQuery(db.getConnection());\r
146         check = query.prepare("delete from LinkedNotebook "\r
147                                 +"where guid=:guid");\r
148 =======
149                 }\r
150         }\r
151         // Delete the notebook based on a guid\r
152         public void expungeNotebook(long id, boolean needsSync) {\r
153                 boolean check;\r
154         NSqlQuery query = new NSqlQuery(db.getConnection());\r
155 \r
156         check = query.prepare("delete from LinkedNotebook "\r
157                                 +"where id=:id");\r
158 >>>>>>> Add linked & shared notebook tables
159                 if (!check) {\r
160                         logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");\r
161                         logger.log(logger.EXTREME, query.lastError().toString());\r
162                 }\r
163 <<<<<<< HEAD
164                 query.bindValue(":guid", id);\r
165 =======
166                 query.bindValue(":id", id);\r
167 >>>>>>> Add linked & shared notebook tables
168                 check = query.exec();\r
169                 if (!check) \r
170                         logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");\r
171                 \r
172 <<<<<<< HEAD
173 =======
174                 // Signal the parent that work needs to be done\r
175 >>>>>>> Add linked & shared notebook tables
176                 if  (needsSync) {\r
177                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
178                         deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");\r
179                 }\r
180         }\r
181 <<<<<<< HEAD
182         // Check if a notebook exists\r
183         public boolean exists(String id) {\r
184         NSqlQuery query = new NSqlQuery(db.getConnection());\r
185         boolean check = query.prepare("Select guid from linkednotebook where guid=:guid");\r
186         query.bindValue(":guid", id);\r
187                 check = query.exec();\r
188                 if (!check) {\r
189                         logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");\r
190                         logger.log(logger.MEDIUM, query.lastError().toString());\r
191                 }\r
192                 if (query.next())\r
193                         return true;\r
194                 return false;\r
195         }\r
196         // Update a notebook\r
197         public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
198                 boolean check;\r
199                 if (!exists(tempNotebook.getGuid())) {\r
200                         addNotebook(tempNotebook, isDirty);\r
201                         return;\r
202                 }\r
203                 \r
204         NSqlQuery query = new NSqlQuery(db.getConnection());\r
205         check = query.prepare("Update LinkedNotebook set guid=:guid, shareName=:shareName, " +\r
206                         "username=:username, shardID=:shardID, shareKey=:shareKey, uri=:uri, updateSequenceNumber=:usn, isDirty=:isDirty "+\r
207                         "where guid=:keyGuid");\r
208                 query.bindValue(":guid", tempNotebook.getGuid());\r
209                 query.bindValue(":keyGuid", tempNotebook.getGuid());\r
210 =======
211         // Update a notebook\r
212         public void updateNotebook(LinkedNotebook 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 LinkedNotebook set id=:id, shareName=:shareName, " +\r
219                         "username=:username, shardID=:shardID, shareKey=:shareKey, url=:url, isDirty=:isDirty");\r
220                 query.bindValue(":id", tempNotebook.getId());\r
221 >>>>>>> Add linked & shared notebook tables
222                 query.bindValue(":shareName", tempNotebook.getShareName());\r
223                 query.bindValue(":username", tempNotebook.getUsername());\r
224                 query.bindValue(":shardID", tempNotebook.getShardId());\r
225                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
226 <<<<<<< HEAD
227                 query.bindValue(":uri", tempNotebook.getUri());\r
228                 query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
229 =======
230                 query.bindValue(":url", tempNotebook.getUri());\r
231 >>>>>>> Add linked & shared notebook tables
232 \r
233                 query.bindValue(":isDirty", isDirty);\r
234                 \r
235                 check = query.exec();\r
236                 if (!check) {\r
237                         logger.log(logger.MEDIUM, "LinkedNotebook Table update failed.");\r
238                         logger.log(logger.MEDIUM, query.lastError().toString());\r
239                 }\r
240         }\r
241         // Load notebooks from the database\r
242         public List<LinkedNotebook> getAll() {\r
243                 LinkedNotebook tempNotebook;\r
244                 List<LinkedNotebook> index = new ArrayList<LinkedNotebook>();\r
245                 boolean check;\r
246                                         \r
247         NSqlQuery query = new NSqlQuery(db.getConnection());\r
248                                         \r
249 <<<<<<< HEAD
250                 check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " +\r
251 =======
252                 check = query.exec("Select id, shareName, username, shardID, shareKey url, " +\r
253 >>>>>>> Add linked & shared notebook tables
254                                 " from LinkedNotebook");\r
255                 if (!check)\r
256                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
257                 while (query.next()) {\r
258                         tempNotebook = new LinkedNotebook();\r
259 <<<<<<< HEAD
260                         tempNotebook.setGuid(query.valueString(0));\r
261                         tempNotebook.setShareName(query.valueString(1));\r
262                         tempNotebook.setUsername(query.valueString(2));\r
263                         tempNotebook.setShardId(query.valueString(3));\r
264                         tempNotebook.setShareKey(query.valueString(4));\r
265                         tempNotebook.setUri(query.valueString(5));\r
266 =======
267                         tempNotebook.setId(query.valueLong(0));\r
268                         tempNotebook.setUsername(query.valueString(1));\r
269                         tempNotebook.setShardId(query.valueString(2));\r
270                         tempNotebook.setShareKey(query.valueString(3));\r
271                         tempNotebook.setUri(query.valueString(4));\r
272 >>>>>>> Add linked & shared notebook tables
273 \r
274                         index.add(tempNotebook); \r
275                 }       \r
276                 return index;\r
277         }                       \r
278 <<<<<<< HEAD
279         // Load notebooks from the database\r
280         public LinkedNotebook getNotebook(String guid) {\r
281                 LinkedNotebook tempNotebook;\r
282                 boolean check;\r
283                                         \r
284         NSqlQuery query = new NSqlQuery(db.getConnection());\r
285                                         \r
286                 check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
287                                 " from LinkedNotebook where guid=:guid");\r
288                 if (!check)\r
289                         logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
290                 query.bindValue(":guid", guid);\r
291                 query.exec();\r
292                 while (query.next()) {\r
293                         tempNotebook = new LinkedNotebook();\r
294                         tempNotebook.setGuid(query.valueString(0));\r
295                         tempNotebook.setShareName(query.valueString(1));\r
296                         tempNotebook.setUsername(query.valueString(2));\r
297                         tempNotebook.setShardId(query.valueString(3));\r
298                         tempNotebook.setShareKey(query.valueString(4));\r
299                         tempNotebook.setUri(query.valueString(5));\r
300                         return tempNotebook;\r
301                 }       \r
302                 return null;\r
303         }       \r
304         // Load notebooks from the database\r
305         public LinkedNotebook getByNotebookGuid(String guid) {\r
306                 LinkedNotebook tempNotebook;\r
307                 boolean check;\r
308                                         \r
309         NSqlQuery query = new NSqlQuery(db.getConnection());\r
310                                         \r
311                 check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
312                                 " from LinkedNotebook where notebookguid=:guid");\r
313                 if (!check)\r
314                         logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
315                 query.bindValue(":guid", guid);\r
316                 query.exec();\r
317                 while (query.next()) {\r
318                         tempNotebook = new LinkedNotebook();\r
319                         tempNotebook.setGuid(query.valueString(0));\r
320                         tempNotebook.setShareName(query.valueString(1));\r
321                         tempNotebook.setUsername(query.valueString(2));\r
322                         tempNotebook.setShardId(query.valueString(3));\r
323                         tempNotebook.setShareKey(query.valueString(4));\r
324                         tempNotebook.setUri(query.valueString(5));\r
325                         return tempNotebook;\r
326                 }       \r
327                 return null;\r
328         }\r
329         // Get last sequence date for the notebook\r
330         public long getLastSequenceDate(String guid) {\r
331                 boolean check;\r
332                                         \r
333         NSqlQuery query = new NSqlQuery(db.getConnection());\r
334                                         \r
335                 check = query.prepare("Select LastSequenceDate " \r
336                                 +"from LinkedNotebook where guid=:guid");\r
337                 query.bindValue(":guid", guid);\r
338                 check = query.exec();\r
339                 if (!check)\r
340                         logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve last sequence date has failed.");\r
341                 if (query.next()) {\r
342                         return query.valueLong(0);\r
343                 }       \r
344                 return 0;\r
345         }                       \r
346         // Get a guid by uri\r
347         public String getNotebookGuid(String guid) {\r
348                 boolean check;\r
349                                         \r
350         NSqlQuery query = new NSqlQuery(db.getConnection());\r
351                                         \r
352                 check = query.prepare("Select notebookGuid " \r
353                                 +"from LinkedNotebook where guid=:guid");\r
354                 query.bindValue(":guid", guid);\r
355                 check = query.exec();\r
356                 if (!check)\r
357                         logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve of notebookguid by guidhas failed.");\r
358                 if (query.next()) {\r
359                         return query.valueString(0);\r
360                 }       \r
361                 return null;\r
362         }       \r
363         // get last sequence numeber\r
364         public int getLastSequenceNumber(String guid) {\r
365                 boolean check;\r
366                                         \r
367         NSqlQuery query = new NSqlQuery(db.getConnection());\r
368                                         \r
369                 check = query.prepare("Select LastSequenceNumber " \r
370                                 +"from LinkedNotebook where guid=:guid");\r
371                 query.bindValue(":guid", guid);\r
372                 check = query.exec();\r
373                 if (!check)\r
374                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
375                 while (query.next()) {\r
376                         return query.valueInteger(0);\r
377                 }       \r
378                 return 0;\r
379         }                       \r
380 =======
381 >>>>>>> Add linked & shared notebook tables
382 \r
383         // does a record exist?\r
384         public String findNotebookByShareName(String name) {\r
385                 \r
386                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
387                 \r
388                 query.prepare("Select guid from Linkednotebook where shareName=:name");\r
389                 query.bindValue(":name", name);\r
390                 if (!query.exec())\r
391                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
392                 String val = null;\r
393                 if (query.next())\r
394                         val = query.valueString(0);\r
395                 return val;\r
396         }\r
397 \r
398 <<<<<<< HEAD
399         // does a record exist?\r
400         public String setNotebookGuid(String shareKey, String notebookGuid) {\r
401                 \r
402                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
403                 \r
404                 query.prepare("Update LinkedNotebook set notebookGuid=:notebookGuid where shareKey=:shareKey");\r
405                 query.bindValue(":notebookGuid", notebookGuid);\r
406                 query.bindValue(":shareKey", shareKey);\r
407                 if (!query.exec())\r
408                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
409                 String val = null;\r
410                 if (query.next())\r
411                         val = query.valueString(0);\r
412                 return val;\r
413         }\r
414         // set sync date\r
415         public String setLastSequenceDate(String guid, long date) {\r
416                 \r
417                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
418                 \r
419                 query.prepare("Update LinkedNotebook set lastsequencedate=:date where guid=:guid");\r
420                 query.bindValue(":date", date);\r
421                 query.bindValue(":guid", guid);\r
422                 if (!query.exec())\r
423                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
424                 String val = null;\r
425                 if (query.next())\r
426                         val = query.valueString(0);\r
427                 return val;\r
428         }\r
429         // set sync number\r
430         public String setLastSequenceNumber(String guid, int number) {\r
431                 \r
432                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
433                 \r
434                 query.prepare("Update LinkedNotebook set lastsequencenumber=:number where guid=:guid");\r
435                 query.bindValue(":number", number);\r
436                 query.bindValue(":guid", guid);\r
437                 if (!query.exec())\r
438                         logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
439                 String val = null;\r
440                 if (query.next())\r
441                         val = query.valueString(0);\r
442                 return val;\r
443         }\r
444         \r
445         // Get a list of linked notebooks that need to be updated\r
446         public List<String> getDirtyGuids() {\r
447                 List<String> index = new ArrayList<String>();\r
448                 boolean check;  \r
449         NSqlQuery query = new NSqlQuery(db.getConnection());\r
450                                         \r
451                 check = query.exec("Select guid from LinkedNotebook where isDirty = true");\r
452                 if (!check) \r
453                         logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve has failed in getdirtyIds.");\r
454                 while (query.next()) {\r
455                         index.add(query.valueString(0));\r
456                 }       \r
457                 return index;   \r
458         }\r
459 =======
460 >>>>>>> Add linked & shared notebook tables
461 \r
462 }\r
463 \r