OSDN Git Service

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