2 * This file is part of NeverNote
\r
3 * Copyright 2009 Randy Baumgarte
\r
5 * This file may be licensed under the terms of of the
\r
6 * GNU General Public License Version 2 (the ``GPL'').
\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
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
21 package cx.fbn.nevernote.sql;
\r
25 import java.text.SimpleDateFormat;
\r
26 >>>>>>> Add linked & shared notebook tables
27 import java.util.ArrayList;
\r
28 import java.util.List;
\r
30 import com.evernote.edam.type.LinkedNotebook;
\r
32 import cx.fbn.nevernote.sql.driver.NSqlQuery;
\r
33 import cx.fbn.nevernote.utilities.ApplicationLogger;
\r
35 public class LinkedNotebookTable {
\r
37 private final ApplicationLogger logger;
\r
38 DatabaseConnection db;
\r
41 public LinkedNotebookTable(ApplicationLogger l, DatabaseConnection d) {
\r
46 public void createTable() {
\r
47 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
48 logger.log(logger.HIGH, "Creating table LinkedNotebook...");
\r
50 if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " +
\r
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
60 "updateSequenceNumber Long," +
\r
61 "lastSequenceNumber Integer," +
\r
62 "lastSequenceDate Long," +
\r
63 "notebookGuid VarChar," +
\r
67 >>>>>>> Add linked & shared notebook tables
68 "isDirty boolean)"))
\r
69 logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");
\r
72 public void dropTable() {
\r
73 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
74 query.exec("Drop table LinkedNotebook");
\r
76 // Save an individual notebook
\r
78 public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {
\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
86 +":guid, :shareName, :username, "
\r
87 +":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)");
\r
88 query.bindValue(":guid", tempNotebook.getGuid());
\r
90 public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {
\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
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
107 query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());
\r
108 query.bindValue(":uri", tempNotebook.getUri());
\r
109 query.bindValue(":notebookGuid", "");
\r
111 query.bindValue(":url", tempNotebook.getUri());
\r
112 >>>>>>> Add linked & shared notebook tables
115 query.bindValue(":isDirty", true);
\r
117 query.bindValue(":isDirty", false);
\r
119 check = query.exec();
\r
121 logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");
\r
122 logger.log(logger.MEDIUM, query.lastError().toString());
\r
127 // Delete the notebook based on a guid
\r
128 public void expungeNotebook(String id, boolean needsSync) {
\r
131 // First, delete any tags associated with this notebook
\r
132 String notebookGuid = getNotebookGuid(id);
\r
133 db.getNotebookTable().deleteLinkedTags(notebookGuid);
\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
141 // Delete the notebook record
\r
142 db.getNotebookTable().expungeNotebook(notebookGuid, needsSync);
\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
151 // Delete the notebook based on a guid
\r
152 public void expungeNotebook(long id, boolean needsSync) {
\r
154 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
156 check = query.prepare("delete from LinkedNotebook "
\r
158 >>>>>>> Add linked & shared notebook tables
160 logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");
\r
161 logger.log(logger.EXTREME, query.lastError().toString());
\r
164 query.bindValue(":guid", id);
\r
166 query.bindValue(":id", id);
\r
167 >>>>>>> Add linked & shared notebook tables
168 check = query.exec();
\r
170 logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");
\r
174 // Signal the parent that work needs to be done
\r
175 >>>>>>> Add linked & shared notebook tables
177 DeletedTable deletedTable = new DeletedTable(logger, db);
\r
178 deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");
\r
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
189 logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");
\r
190 logger.log(logger.MEDIUM, query.lastError().toString());
\r
196 // Update a notebook
\r
197 public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {
\r
199 if (!exists(tempNotebook.getGuid())) {
\r
200 addNotebook(tempNotebook, isDirty);
\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
211 // Update a notebook
\r
212 public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {
\r
215 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\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
227 query.bindValue(":uri", tempNotebook.getUri());
\r
228 query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());
\r
230 query.bindValue(":url", tempNotebook.getUri());
\r
231 >>>>>>> Add linked & shared notebook tables
233 query.bindValue(":isDirty", isDirty);
\r
235 check = query.exec();
\r
237 logger.log(logger.MEDIUM, "LinkedNotebook Table update failed.");
\r
238 logger.log(logger.MEDIUM, query.lastError().toString());
\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
247 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
250 check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " +
\r
252 check = query.exec("Select id, shareName, username, shardID, shareKey url, " +
\r
253 >>>>>>> Add linked & shared notebook tables
254 " from LinkedNotebook");
\r
256 logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");
\r
257 while (query.next()) {
\r
258 tempNotebook = new LinkedNotebook();
\r
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
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
274 index.add(tempNotebook);
\r
279 // Load notebooks from the database
\r
280 public LinkedNotebook getNotebook(String guid) {
\r
281 LinkedNotebook tempNotebook;
\r
284 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
286 check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +
\r
287 " from LinkedNotebook where guid=:guid");
\r
289 logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");
\r
290 query.bindValue(":guid", guid);
\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
304 // Load notebooks from the database
\r
305 public LinkedNotebook getByNotebookGuid(String guid) {
\r
306 LinkedNotebook tempNotebook;
\r
309 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
311 check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +
\r
312 " from LinkedNotebook where notebookguid=:guid");
\r
314 logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");
\r
315 query.bindValue(":guid", guid);
\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
329 // Get last sequence date for the notebook
\r
330 public long getLastSequenceDate(String guid) {
\r
333 NSqlQuery query = new NSqlQuery(db.getConnection());
\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
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
346 // Get a guid by uri
\r
347 public String getNotebookGuid(String guid) {
\r
350 NSqlQuery query = new NSqlQuery(db.getConnection());
\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
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
363 // get last sequence numeber
\r
364 public int getLastSequenceNumber(String guid) {
\r
367 NSqlQuery query = new NSqlQuery(db.getConnection());
\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
374 logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");
\r
375 while (query.next()) {
\r
376 return query.valueInteger(0);
\r
381 >>>>>>> Add linked & shared notebook tables
383 // does a record exist?
\r
384 public String findNotebookByShareName(String name) {
\r
386 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
388 query.prepare("Select guid from Linkednotebook where shareName=:name");
\r
389 query.bindValue(":name", name);
\r
391 logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
\r
394 val = query.valueString(0);
\r
399 // does a record exist?
\r
400 public String setNotebookGuid(String shareKey, String notebookGuid) {
\r
402 NSqlQuery query = new NSqlQuery(db.getConnection());
\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
408 logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
\r
411 val = query.valueString(0);
\r
415 public String setLastSequenceDate(String guid, long date) {
\r
417 NSqlQuery query = new NSqlQuery(db.getConnection());
\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
423 logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
\r
426 val = query.valueString(0);
\r
430 public String setLastSequenceNumber(String guid, int number) {
\r
432 NSqlQuery query = new NSqlQuery(db.getConnection());
\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
438 logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");
\r
441 val = query.valueString(0);
\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
449 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
451 check = query.exec("Select guid from LinkedNotebook where isDirty = true");
\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
460 >>>>>>> Add linked & shared notebook tables