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
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
29 import com.evernote.edam.type.Note;
\r
30 import com.evernote.edam.type.NoteAttributes;
\r
31 import com.evernote.edam.type.Resource;
\r
32 import com.evernote.edam.type.Tag;
\r
33 import com.trolltech.qt.core.QByteArray;
\r
34 import com.trolltech.qt.core.QDateTime;
\r
35 import com.trolltech.qt.core.QTextCodec;
\r
37 import cx.fbn.nevernote.Global;
\r
38 import cx.fbn.nevernote.evernote.EnmlConverter;
\r
39 import cx.fbn.nevernote.sql.driver.NSqlQuery;
\r
40 import cx.fbn.nevernote.utilities.ApplicationLogger;
\r
41 import cx.fbn.nevernote.utilities.Pair;
\r
43 public class NoteTable {
\r
44 private final ApplicationLogger logger;
\r
45 public final NoteTagsTable noteTagsTable;
\r
46 public NoteResourceTable noteResourceTable;
\r
47 private final DatabaseConnection db;
\r
50 // Prepared Queries to improve speed
\r
51 private NSqlQuery getQueryWithContent;
\r
52 private NSqlQuery getQueryWithoutContent;
\r
53 private NSqlQuery getAllQueryWithoutContent;
\r
56 public NoteTable(ApplicationLogger l, DatabaseConnection d) {
\r
60 noteResourceTable = new NoteResourceTable(logger, db);
\r
61 noteTagsTable = new NoteTagsTable(logger, db);
\r
62 getQueryWithContent = null;
\r
63 getQueryWithoutContent = null;
\r
67 public void createTable() {
\r
68 getQueryWithContent = new NSqlQuery(db.getConnection());
\r
69 getQueryWithoutContent = new NSqlQuery(db.getConnection());
\r
70 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
71 logger.log(logger.HIGH, "Creating table Note...");
\r
72 if (!query.exec("Create table Note (guid varchar primary key, " +
\r
73 "updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+
\r
74 "contentLength integer, created timestamp, updated timestamp, deleted timestamp, "
\r
75 +"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+
\r
76 "attributeLatitude double, attributeLongitude double, attributeAltitude double,"+
\r
77 "attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+
\r
78 "attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +
\r
79 "isDirty boolean)"))
\r
80 logger.log(logger.HIGH, "Table Note creation FAILED!!!");
\r
81 if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))
\r
82 logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");
\r
83 if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);"))
\r
84 logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!");
\r
85 noteTagsTable.createTable();
\r
86 noteResourceTable.createTable();
\r
89 public void dropTable() {
\r
90 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
91 query.exec("Drop table Note");
\r
92 noteTagsTable.dropTable();
\r
93 noteResourceTable.dropTable();
\r
95 // Save Note List from Evernote
\r
96 public void addNote(Note n, boolean isDirty) {
\r
97 logger.log(logger.EXTREME, "Inside addNote");
\r
101 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
\r
103 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
104 query.prepare("Insert Into Note ("
\r
105 +"guid, updateSequenceNumber, title, content, "
\r
106 +"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "
\r
107 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
\r
108 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
\r
109 +"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded"
\r
111 +":guid, :updateSequenceNumber, :title, :content, "
\r
112 +":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "
\r
113 +":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "
\r
114 +":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "
\r
115 +":indexNeeded, :isExpunged, :isDirty, -1, true) ");
\r
117 StringBuilder created = new StringBuilder(simple.format(n.getCreated()));
\r
118 StringBuilder updated = new StringBuilder(simple.format(n.getUpdated()));
\r
119 StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));
\r
121 EnmlConverter enml = new EnmlConverter(logger);
\r
123 query.bindValue(":guid", n.getGuid());
\r
124 query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());
\r
125 query.bindValue(":title", n.getTitle());
\r
126 query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));
\r
127 query.bindValue(":contentHash", n.getContentHash());
\r
128 query.bindValue(":contentLength", n.getContentLength());
\r
129 query.bindValue(":created", created.toString());
\r
130 query.bindValue(":updated", updated.toString());
\r
131 query.bindValue(":deleted", deleted.toString());
\r
132 query.bindValue(":active", n.isActive());
\r
133 query.bindValue(":notebookGuid", n.getNotebookGuid());
\r
135 if (n.getAttributes() != null) {
\r
136 created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));
\r
137 query.bindValue(":attributeSubjectDate", created.toString());
\r
138 query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());
\r
139 query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());
\r
140 query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());
\r
141 query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());
\r
142 query.bindValue(":attributeSource", n.getAttributes().getSource());
\r
143 query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL());
\r
144 query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication());
\r
146 query.bindValue(":indexNeeded", true);
\r
147 query.bindValue(":isExpunged", false);
\r
148 query.bindValue(":isDirty", isDirty);
\r
152 logger.log(logger.MEDIUM, query.lastError());
\r
154 // Save the note tags
\r
155 if (n.getTagGuids() != null) {
\r
156 for (int i=0; i<n.getTagGuids().size(); i++)
\r
157 noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i));
\r
159 logger.log(logger.EXTREME, "Leaving addNote");
\r
161 // Setup queries for get to save time later
\r
162 private void prepareQueries() {
\r
163 getQueryWithContent = new NSqlQuery(db.getConnection());
\r
164 getQueryWithoutContent = new NSqlQuery(db.getConnection());
\r
165 getAllQueryWithoutContent = new NSqlQuery(db.getConnection());
\r
167 if (!getQueryWithContent.prepare("Select "
\r
168 +"guid, updateSequenceNumber, title, "
\r
169 +"created, updated, deleted, active, notebookGuid, "
\r
170 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
\r
171 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
\r
172 +"content, contentHash, contentLength"
\r
173 +" from Note where guid=:guid and isExpunged=false")) {
\r
174 logger.log(logger.EXTREME, "Note SQL select prepare with content has failed.");
\r
175 logger.log(logger.MEDIUM, getQueryWithContent.lastError());
\r
178 if (!getQueryWithoutContent.prepare("Select "
\r
179 +"guid, updateSequenceNumber, title, "
\r
180 +"created, updated, deleted, active, notebookGuid, "
\r
181 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
\r
182 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "
\r
183 +" from Note where guid=:guid and isExpunged=false")) {
\r
184 logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");
\r
185 logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());
\r
187 if (!getAllQueryWithoutContent.prepare("Select "
\r
188 +"guid, updateSequenceNumber, title, "
\r
189 +"created, updated, deleted, active, notebookGuid, "
\r
190 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
\r
191 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "
\r
192 +" from Note where isExpunged = false")) {
\r
193 logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");
\r
194 logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());
\r
198 // Get a note's content in raw, binary format for the sync.
\r
199 public String getNoteContentBinary(String guid) {
\r
200 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
201 query.prepare("Select content from note where guid=:guid");
\r
202 query.bindValue(":guid", guid);
\r
205 return query.valueString(0);
\r
207 // Get a note by Guid
\r
208 public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {
\r
209 if (noteGuid == null)
\r
211 if (noteGuid.trim().equals(""))
\r
217 query = getQueryWithContent;
\r
219 query = getQueryWithoutContent;
\r
222 query.bindValue(":guid", noteGuid);
\r
223 if (!query.exec()) {
\r
224 logger.log(logger.EXTREME, "Note SQL select exec has failed.");
\r
225 logger.log(logger.MEDIUM, query.lastError());
\r
228 if (!query.next()) {
\r
229 logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");
\r
230 logger.log(logger.EXTREME, " -> " +query.lastError().toString());
\r
231 logger.log(logger.EXTREME, " -> " +query.lastError());
\r
234 Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags);
\r
235 n.setContent(fixCarriageReturn(n.getContent()));
\r
238 // Get a note by Guid
\r
239 public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {
\r
240 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
241 // indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
\r
244 Note n = new Note();
\r
245 NoteAttributes na = new NoteAttributes();
\r
246 n.setAttributes(na);
\r
248 n.setGuid(query.valueString(0));
\r
249 n.setUpdateSequenceNum(new Integer(query.valueString(1)));
\r
250 n.setTitle(query.valueString(2));
\r
253 n.setCreated(indfm.parse(query.valueString(3)).getTime());
\r
254 n.setUpdated(indfm.parse(query.valueString(4)).getTime());
\r
255 n.setDeleted(indfm.parse(query.valueString(5)).getTime());
\r
256 } catch (ParseException e) {
\r
257 e.printStackTrace();
\r
260 n.setActive(query.valueBoolean(6,true));
\r
261 n.setNotebookGuid(query.valueString(7));
\r
264 String attributeSubjectDate = query.valueString(8);
\r
265 if (!attributeSubjectDate.equals(""))
\r
266 na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());
\r
267 } catch (ParseException e) {
\r
268 e.printStackTrace();
\r
270 na.setLatitude(new Float(query.valueString(9)));
\r
271 na.setLongitude(new Float(query.valueString(10)));
\r
272 na.setAltitude(new Float(query.valueString(11)));
\r
273 na.setAuthor(query.valueString(12));
\r
274 na.setSource(query.valueString(13));
\r
275 na.setSourceURL(query.valueString(14));
\r
276 na.setSourceApplication(query.valueString(15));
\r
279 n.setTagGuids(noteTagsTable.getNoteTags(n.getGuid()));
\r
280 List<String> tagNames = new ArrayList<String>();
\r
281 TagTable tagTable = new TagTable(logger, db);
\r
282 for (int i=0; i<n.getTagGuids().size(); i++) {
\r
283 String currentGuid = n.getTagGuids().get(i);
\r
284 Tag tag = tagTable.getTag(currentGuid);
\r
285 tagNames.add(tag.getName());
\r
287 n.setTagNames(tagNames);
\r
292 QTextCodec codec = QTextCodec.codecForLocale();
\r
293 codec = QTextCodec.codecForName("UTF-8");
\r
294 String unicode = codec.fromUnicode(query.valueString(16)).toString();
\r
295 n.setContent(unicode);
\r
296 // n.setContent(query.valueString(16).toString());
\r
298 String contentHash = query.valueString(17);
\r
299 if (contentHash != null)
\r
300 n.setContentHash(contentHash.getBytes());
\r
301 n.setContentLength(new Integer(query.valueString(18)));
\r
304 n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));
\r
305 if (loadRecognition) {
\r
306 if (n.getResources() == null) {
\r
307 List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());
\r
308 n.setResources(resources);
\r
310 // We need to merge the recognition resources with the note resources retrieved earlier
\r
311 for (int i=0; i<n.getResources().size(); i++) {
\r
312 Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());
\r
313 n.getResources().get(i).setRecognition(r.getRecognition());
\r
317 n.setContent(fixCarriageReturn(n.getContent()));
\r
320 // Update a note's title
\r
321 public void updateNoteTitle(String guid, String title) {
\r
322 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
323 boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");
\r
325 logger.log(logger.EXTREME, "Update note title sql prepare has failed.");
\r
326 logger.log(logger.MEDIUM, query.lastError());
\r
328 query.bindValue(":title", title);
\r
329 query.bindValue(":guid", guid);
\r
330 check = query.exec();
\r
332 logger.log(logger.EXTREME, "Update note title has failed.");
\r
333 logger.log(logger.MEDIUM, query.lastError());
\r
336 // Update a note's creation date
\r
337 public void updateNoteCreatedDate(String guid, QDateTime date) {
\r
338 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
339 boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");
\r
341 logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");
\r
342 logger.log(logger.MEDIUM, query.lastError());
\r
345 query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));
\r
346 query.bindValue(":guid", guid);
\r
348 check = query.exec();
\r
350 logger.log(logger.EXTREME, "Update note creation date has failed.");
\r
351 logger.log(logger.MEDIUM, query.lastError());
\r
354 // Update a note's creation date
\r
355 public void updateNoteAlteredDate(String guid, QDateTime date) {
\r
356 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
357 boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");
\r
359 logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");
\r
360 logger.log(logger.MEDIUM, query.lastError());
\r
363 query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));
\r
364 query.bindValue(":guid", guid);
\r
366 check = query.exec();
\r
368 logger.log(logger.EXTREME, "Update note altered date has failed.");
\r
369 logger.log(logger.MEDIUM, query.lastError());
\r
372 // Update a note's creation date
\r
373 public void updateNoteSubjectDate(String guid, QDateTime date) {
\r
374 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
375 boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");
\r
377 logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");
\r
378 logger.log(logger.MEDIUM, query.lastError());
\r
381 query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));
\r
382 query.bindValue(":guid", guid);
\r
384 check = query.exec();
\r
386 logger.log(logger.EXTREME, "Update note subject date date has failed.");
\r
387 logger.log(logger.MEDIUM, query.lastError());
\r
390 // Update a note's creation date
\r
391 public void updateNoteAuthor(String guid, String author) {
\r
392 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
393 boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");
\r
395 logger.log(logger.EXTREME, "Update note author sql prepare has failed.");
\r
396 logger.log(logger.MEDIUM, query.lastError());
\r
399 query.bindValue(":author", author);
\r
400 query.bindValue(":guid", guid);
\r
402 check = query.exec();
\r
404 logger.log(logger.EXTREME, "Update note author has failed.");
\r
405 logger.log(logger.MEDIUM, query.lastError());
\r
409 // Update a note's geo tags
\r
410 public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {
\r
411 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
412 boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+
\r
413 "attributeLatitude=:latitude, attributeAltitude=:altitude, isDirty=true where guid=:guid");
\r
415 logger.log(logger.EXTREME, "Update note author sql prepare has failed.");
\r
416 logger.log(logger.MEDIUM, query.lastError());
\r
419 query.bindValue(":longitude", lon);
\r
420 query.bindValue(":latitude", lat);
\r
421 query.bindValue(":altitude", alt);
\r
422 query.bindValue(":guid", guid);
\r
424 check = query.exec();
\r
426 logger.log(logger.EXTREME, "Update note geo tag has failed.");
\r
427 logger.log(logger.MEDIUM, query.lastError());
\r
431 // Update a note's creation date
\r
432 public void updateNoteSourceUrl(String guid, String url) {
\r
433 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
434 boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");
\r
436 logger.log(logger.EXTREME, "Update note url sql prepare has failed.");
\r
437 logger.log(logger.MEDIUM, query.lastError());
\r
440 query.bindValue(":url", url);
\r
441 query.bindValue(":guid", guid);
\r
443 check = query.exec();
\r
445 logger.log(logger.EXTREME, "Update note url has failed.");
\r
446 logger.log(logger.MEDIUM, query.lastError());
\r
450 // Update the notebook that a note is assigned to
\r
451 public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {
\r
452 String currentNotebookGuid = new String("");
\r
455 // If we are going from a synchronized notebook to a local notebook, we
\r
456 // need to tell Evernote to purge the note online. However, if this is
\r
457 // conflicting change we move it to the local notebook without deleting it
\r
458 // or it would then delete the copy on the remote server.
\r
459 NotebookTable notebookTable = new NotebookTable(logger, db);
\r
460 DeletedTable deletedTable = new DeletedTable(logger, db);
\r
461 if (expungeFromRemote) {
\r
462 if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {
\r
463 deletedTable.addDeletedItem(guid, "NOTE");
\r
467 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
468 boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");
\r
470 logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");
\r
471 logger.log(logger.MEDIUM, query.lastError());
\r
473 query.bindValue(":notebook", notebookGuid);
\r
474 query.bindValue(":guid", guid);
\r
476 check = query.exec();
\r
478 logger.log(logger.EXTREME, "Update note notebook has failed.");
\r
479 logger.log(logger.MEDIUM, query.lastError());
\r
482 // Update a note's title
\r
483 public void updateNoteContent(String guid, String content) {
\r
484 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
485 boolean check = query.prepare("Update Note set content=:content, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true " +
\r
486 " where guid=:guid");
\r
488 logger.log(logger.EXTREME, "Update note content sql prepare has failed.");
\r
489 logger.log(logger.MEDIUM, query.lastError());
\r
492 query.bindValue(":content", content);
\r
493 query.bindValue(":guid", guid);
\r
495 check = query.exec();
\r
497 logger.log(logger.EXTREME, "Update note content has failed.");
\r
498 logger.log(logger.MEDIUM, query.lastError());
\r
503 // Check a note to see if it passes the attribute selection criteria
\r
504 public boolean checkAttributeSelection(Note n) {
\r
505 if (Global.createdSinceFilter.check(n) &&
\r
506 Global.createdBeforeFilter.check(n) &&
\r
507 Global.changedSinceFilter.check(n) &&
\r
508 Global.changedBeforeFilter.check(n) &&
\r
509 Global.containsFilter.check(this, n))
\r
515 public void deleteNote(String guid) {
\r
516 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
517 query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");
\r
518 query.bindValue(":guid", guid);
\r
519 if (!query.exec()) {
\r
520 logger.log(logger.MEDIUM, "Note delete failed.");
\r
521 logger.log(logger.MEDIUM, query.lastError());
\r
524 public void restoreNote(String guid) {
\r
525 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
526 query.prepare("Update Note set deleted='1969-12-31 19.00.00', active=true, isDirty=true where guid=:guid");
\r
527 // query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");
\r
528 query.bindValue(":guid", guid);
\r
529 if (!query.exec()) {
\r
530 logger.log(logger.MEDIUM, "Note restore failed.");
\r
531 logger.log(logger.MEDIUM, query.lastError());
\r
534 // Purge a note (actually delete it instead of just marking it deleted)
\r
535 public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {
\r
537 if (!permanentExpunge) {
\r
538 hideExpungedNote(guid, needsSync);
\r
543 NSqlQuery note = new NSqlQuery(db.getConnection());
\r
544 NSqlQuery resources = new NSqlQuery(db.getConnection());
\r
545 NSqlQuery tags = new NSqlQuery(db.getConnection());
\r
546 NSqlQuery words = new NSqlQuery(db.getConnection());
\r
548 note.prepare("Delete from Note where guid=:guid");
\r
549 resources.prepare("Delete from NoteResources where noteGuid=:guid");
\r
550 tags.prepare("Delete from NoteTags where noteGuid=:guid");
\r
551 words.prepare("Delete from words where guid=:guid");
\r
553 note.bindValue(":guid", guid);
\r
554 resources.bindValue(":guid", guid);
\r
555 tags.bindValue(":guid", guid);
\r
556 words.bindValue(":guid", guid);
\r
558 // Start purging notes.
\r
559 if (!note.exec()) {
\r
560 logger.log(logger.MEDIUM, "Purge from note failed.");
\r
561 logger.log(logger.MEDIUM, note.lastError());
\r
563 if (!resources.exec()) {
\r
564 logger.log(logger.MEDIUM, "Purge from resources failed.");
\r
565 logger.log(logger.MEDIUM, resources.lastError());
\r
567 if (!tags.exec()) {
\r
568 logger.log(logger.MEDIUM, "Note tags delete failed.");
\r
569 logger.log(logger.MEDIUM, tags.lastError());
\r
571 if (!words.exec()) {
\r
572 logger.log(logger.MEDIUM, "Word delete failed.");
\r
573 logger.log(logger.MEDIUM, words.lastError());
\r
576 DeletedTable deletedTable = new DeletedTable(logger, db);
\r
577 deletedTable.addDeletedItem(guid, "Note");
\r
581 // Purge a note (actually delete it instead of just marking it deleted)
\r
582 public void hideExpungedNote(String guid, boolean needsSync) {
\r
583 NSqlQuery note = new NSqlQuery(db.getConnection());
\r
584 NSqlQuery resources = new NSqlQuery(db.getConnection());
\r
585 NSqlQuery tags = new NSqlQuery(db.getConnection());
\r
586 NSqlQuery words = new NSqlQuery(db.getConnection());
\r
588 note.prepare("Update Note set isExpunged=true where guid=:guid");
\r
589 resources.prepare("Delete from NoteResources where noteGuid=:guid");
\r
590 tags.prepare("Delete from NoteTags where noteGuid=:guid");
\r
591 words.prepare("Delete from words where guid=:guid");
\r
593 note.bindValue(":guid", guid);
\r
594 resources.bindValue(":guid", guid);
\r
595 tags.bindValue(":guid", guid);
\r
596 words.bindValue(":guid", guid);
\r
598 // Start purging notes.
\r
599 if (!note.exec()) {
\r
600 logger.log(logger.MEDIUM, "Purge from note failed.");
\r
601 logger.log(logger.MEDIUM, note.lastError());
\r
603 if (!resources.exec()) {
\r
604 logger.log(logger.MEDIUM, "Purge from resources failed.");
\r
605 logger.log(logger.MEDIUM, resources.lastError());
\r
607 if (!tags.exec()) {
\r
608 logger.log(logger.MEDIUM, "Note tags delete failed.");
\r
609 logger.log(logger.MEDIUM, tags.lastError());
\r
611 if (!words.exec()) {
\r
612 logger.log(logger.MEDIUM, "Word delete failed.");
\r
613 logger.log(logger.MEDIUM, words.lastError());
\r
616 DeletedTable deletedTable = new DeletedTable(logger, db);
\r
617 deletedTable.addDeletedItem(guid, "Note");
\r
622 // Purge all deleted notes;
\r
623 public void expungeAllDeletedNotes() {
\r
624 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
625 query.exec("select guid, updateSequenceNumber from note where active = false");
\r
626 while (query.next()) {
\r
627 String guid = query.valueString(0);
\r
628 Integer usn = new Integer(query.valueString(1));
\r
630 expungeNote(guid, true, false);
\r
632 expungeNote(guid, false, true);
\r
635 // Update the note sequence number
\r
636 public void updateNoteSequence(String guid, int sequence) {
\r
638 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
639 check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");
\r
641 query.bindValue(":sequence", sequence);
\r
642 query.bindValue(":guid", guid);
\r
646 logger.log(logger.MEDIUM, "Note sequence update failed.");
\r
647 logger.log(logger.MEDIUM, query.lastError());
\r
650 // Update the note Guid
\r
651 public void updateNoteGuid(String oldGuid, String newGuid) {
\r
653 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
654 query.prepare("Update Note set guid=:newGuid where guid=:oldGuid");
\r
656 query.bindValue(":newGuid", newGuid);
\r
657 query.bindValue(":oldGuid", oldGuid);
\r
659 check = query.exec();
\r
661 logger.log(logger.MEDIUM, "Note Guid update failed.");
\r
662 logger.log(logger.MEDIUM, query.lastError());
\r
665 query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");
\r
666 query.bindValue(":newGuid", newGuid);
\r
667 query.bindValue(":oldGuid", oldGuid);
\r
668 check = query.exec();
\r
670 logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");
\r
671 logger.log(logger.MEDIUM, query.lastError());
\r
674 query.prepare("Update words set guid=:newGuid where guid=:oldGuid");
\r
675 query.bindValue(":newGuid", newGuid);
\r
676 query.bindValue(":oldGuid", oldGuid);
\r
679 logger.log(logger.MEDIUM, "Note guid update failed for Words.");
\r
680 logger.log(logger.MEDIUM, query.lastError());
\r
682 query.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");
\r
683 query.bindValue(":newGuid", newGuid);
\r
684 query.bindValue(":oldGuid", oldGuid);
\r
687 logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");
\r
688 logger.log(logger.MEDIUM, query.lastError());
\r
692 public void updateNote(Note n, boolean isNew) {
\r
693 boolean isExpunged = isNoteExpunged(n.getGuid());
\r
695 expungeNote(n.getGuid(), !isExpunged, false);
\r
698 // Does a note exist?
\r
699 public boolean exists(String guid) {
\r
702 if (guid.trim().equals(""))
\r
704 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
705 query.prepare("Select guid from note where guid=:guid");
\r
706 query.bindValue(":guid", guid);
\r
708 logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");
\r
709 boolean retVal = query.next();
\r
712 // Does a note exist?
\r
713 public boolean isNoteExpunged(String guid) {
\r
716 if (guid.trim().equals(""))
\r
718 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
719 query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");
\r
720 query.bindValue(":guid", guid);
\r
722 logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");
\r
723 boolean retVal = query.next();
\r
726 // This is a convience method to check if a tag exists & update/create based upon it
\r
727 public void syncNote(Note tag, boolean isDirty) {
\r
728 if (exists(tag.getGuid()))
\r
729 updateNote(tag, isDirty);
\r
731 addNote(tag, isDirty);
\r
733 // Get a list of notes that need to be updated
\r
734 public List <Note> getDirty() {
\r
737 List<Note> notes = new ArrayList<Note>();
\r
738 List<String> index = new ArrayList<String>();
\r
741 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
743 check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true)");
\r
745 logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
\r
747 // Get a list of the notes
\r
748 while (query.next()) {
\r
749 guid = new String();
\r
750 guid = query.valueString(0);
\r
754 // Start getting notes
\r
755 for (int i=0; i<index.size(); i++) {
\r
756 tempNote = getNote(index.get(i), true,true,false,true,true);
\r
757 notes.add(tempNote);
\r
761 // Get a list of notes that need to be updated
\r
762 public boolean isNoteDirty(String guid) {
\r
765 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
767 check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");
\r
768 query.bindValue(":guid", guid);
\r
769 check = query.exec();
\r
771 logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
\r
773 boolean returnValue;
\r
774 // Get a list of the notes
\r
776 returnValue = true;
\r
778 returnValue = false;
\r
780 return returnValue;
\r
782 // Get a list of notes that need to be updated
\r
783 public List <String> getUnsynchronizedGUIDs() {
\r
785 List<String> index = new ArrayList<String>();
\r
788 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
790 check = query.exec("Select guid from Note where isDirty = true");
\r
792 logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
\r
794 // Get a list of the notes
\r
795 while (query.next()) {
\r
796 guid = new String();
\r
797 guid = query.valueString(0);
\r
802 // Reset the dirty bit
\r
803 public void resetDirtyFlag(String guid) {
\r
804 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
806 query.prepare("Update note set isdirty=false where guid=:guid");
\r
807 query.bindValue(":guid", guid);
\r
809 logger.log(logger.EXTREME, "Error resetting note dirty field.");
\r
812 public List<String> getAllGuids() {
\r
813 List<String> notes = new ArrayList<String>();
\r
816 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
818 check = query.exec("Select guid from Note");
\r
820 logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());
\r
822 // Get a list of the notes
\r
823 while (query.next()) {
\r
824 notes.add(new String(query.valueString(0)));
\r
829 public List<Note> getAllNotes() {
\r
830 List<Note> notes = new ArrayList<Note>();
\r
833 NSqlQuery query = getAllQueryWithoutContent;
\r
834 check = query.exec();
\r
836 logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());
\r
837 // Get a list of the notes
\r
838 while (query.next()) {
\r
839 notes.add(mapNoteFromQuery(query, false, false, false, false, true));
\r
843 // Count unindexed notes
\r
844 public int getUnindexedCount() {
\r
845 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
846 query.exec("select count(*) from note where indexneeded=true and isExpunged = false");
\r
848 int returnValue = new Integer(query.valueString(0));
\r
849 return returnValue;
\r
851 // Count unsynchronized notes
\r
852 public int getDirtyCount() {
\r
853 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
854 query.exec("select count(*) from note where isDirty=true and isExpunged = false");
\r
856 int returnValue = new Integer(query.valueString(0));
\r
857 return returnValue;
\r
860 public int getNoteCount() {
\r
861 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
862 query.exec("select count(*) from note where isExpunged = false");
\r
864 int returnValue = new Integer(query.valueString(0));
\r
865 return returnValue;
\r
867 // Count deleted notes
\r
868 public int getDeletedCount() {
\r
869 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
870 query.exec("select count(*) from note where isExpunged = false and active = false");
\r
871 if (!query.next())
\r
873 int returnValue = new Integer(query.valueString(0));
\r
874 return returnValue;
\r
876 // Reset a note sequence number to zero. This is useful for moving conflicting notes
\r
877 public void resetNoteSequence(String guid) {
\r
878 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
879 boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");
\r
881 logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");
\r
882 logger.log(logger.MEDIUM, query.lastError());
\r
884 query.bindValue(":guid", guid);
\r
885 check = query.exec();
\r
887 logger.log(logger.EXTREME, "Update note sequence number has failed.");
\r
888 logger.log(logger.MEDIUM, query.lastError());
\r
893 // Update a note resource by the hash
\r
894 public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {
\r
895 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
896 /* query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");
\r
897 query.bindValue(":noteGuid", noteGuid);
\r
898 query.bindValue(":hex", hash);
\r
900 if (!query.next()) {
\r
901 logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash. GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);
\r
904 String guid = query.valueString(0);
\r
906 query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");
\r
907 query.bindValue(":guid", resGuid);
\r
908 query.bindValue(":noteGuid", noteGuid);
\r
909 query.bindValue(":hex", hash);
\r
910 if (!query.exec()) {
\r
911 logger.log(logger.EXTREME, "Note Resource Update by Hash failed");
\r
912 logger.log(logger.EXTREME, query.lastError().toString());
\r
916 // Fix CRLF problem that is on some notes
\r
917 private String fixCarriageReturn(String note) {
\r
918 if (note == null || !Global.enableCarriageReturnFix)
\r
920 QByteArray a0Hex = new QByteArray("a0");
\r
921 String a0 = QByteArray.fromHex(a0Hex).toString();
\r
922 note = note.replace("<div>"+a0+"</div>", "<div> </div>");
\r
923 return note.replace("<div/>", "<div> </div>");
\r
928 //********************************************************************************
\r
929 //********************************************************************************
\r
930 //* Indexing Functions
\r
931 //********************************************************************************
\r
932 //********************************************************************************
\r
933 // set/unset a note to be reindexed
\r
934 public void setIndexNeeded(String guid, Boolean flag) {
\r
935 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
936 query.prepare("Update Note set indexNeeded=:flag where guid=:guid");
\r
939 query.bindValue(":flag", 1);
\r
941 query.bindValue(":flag", 0);
\r
942 query.bindValue(":guid", guid);
\r
943 if (!query.exec()) {
\r
944 logger.log(logger.MEDIUM, "Note indexNeeded update failed.");
\r
945 logger.log(logger.MEDIUM, query.lastError());
\r
948 // Set all notes to be reindexed
\r
949 public void reindexAllNotes() {
\r
950 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
951 if (!query.exec("Update Note set indexNeeded=true")) {
\r
952 logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");
\r
953 logger.log(logger.MEDIUM, query.lastError());
\r
957 // Get all unindexed notes
\r
958 public List <String> getUnindexed() {
\r
960 List<String> index = new ArrayList<String>();
\r
961 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
963 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))
\r
964 logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");
\r
966 // Get a list of the notes
\r
967 while (query.next()) {
\r
968 guid = new String();
\r
969 guid = query.valueString(0);
\r
974 public List<String> getNextUnindexed(int limit) {
\r
975 List<String> guids = new ArrayList<String>();
\r
977 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
979 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))
\r
980 logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");
\r
982 // Get a list of the notes
\r
984 while (query.next()) {
\r
985 guid = new String();
\r
986 guid = query.valueString(0);
\r
993 //**********************************************************************************
\r
994 //* Title color functions
\r
995 //**********************************************************************************
\r
996 // Get the title color of all notes
\r
997 public List<Pair<String, Integer>> getNoteTitleColors() {
\r
998 List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();
\r
999 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1001 if (!query.exec("Select guid,titleColor from Note where titleColor != -1"))
\r
1002 logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");
\r
1007 // Get a list of the notes
\r
1008 while (query.next()) {
\r
1009 Pair<String, Integer> pair = new Pair<String,Integer>();
\r
1010 guid = query.valueString(0);
\r
1011 color = query.valueInteger(1);
\r
1012 pair.setFirst(guid);
\r
1013 pair.setSecond(color);
\r
1014 returnValue.add(pair);
\r
1019 return returnValue;
\r
1021 // Set a title color
\r
1022 // Reset the dirty bit
\r
1023 public void setNoteTitleColor(String guid, int color) {
\r
1024 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1026 query.prepare("Update note set titlecolor=:color where guid=:guid");
\r
1027 query.bindValue(":guid", guid);
\r
1028 query.bindValue(":color", color);
\r
1029 if (!query.exec())
\r
1030 logger.log(logger.EXTREME, "Error updating title color.");
\r
1035 //**********************************************************************************
\r
1036 //* Thumbnail functions
\r
1037 //**********************************************************************************
\r
1038 // Set if a new thumbnail is needed
\r
1039 public void setThumbnailNeeded(String guid, boolean needed) {
\r
1042 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1044 check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");
\r
1045 query.bindValue(":guid", guid);
\r
1046 query.bindValue(":needed", needed);
\r
1047 check = query.exec();
\r
1049 logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());
\r
1052 // Is a thumbail needed for this guid?
\r
1053 public boolean isThumbnailNeeded(String guid) {
\r
1056 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1058 check = query.prepare("select thumbnailneeded from note where guid=:guid");
\r
1059 query.bindValue(":guid", guid);
\r
1060 check = query.exec();
\r
1062 logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());
\r
1064 boolean returnValue;
\r
1065 // Get a list of the notes
\r
1066 if (query.next())
\r
1067 returnValue = query.valueBoolean(0, false);
\r
1069 returnValue = false;
\r
1071 return returnValue;
\r
1073 // Set if a new thumbnail is needed
\r
1074 public void setThumbnail(String guid, QByteArray thumbnail) {
\r
1077 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1079 check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");
\r
1080 query.bindValue(":guid", guid);
\r
1081 query.bindValue(":thumbnail", thumbnail.toByteArray());
\r
1082 check = query.exec();
\r
1084 logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());
\r
1087 // Set if a new thumbnail is needed
\r
1088 public QByteArray getThumbnail(String guid) {
\r
1091 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1093 check = query.prepare("Select thumbnail from note where guid=:guid");
\r
1094 query.bindValue(":guid", guid);
\r
1095 check = query.exec();
\r
1097 logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());
\r
1098 // Get a list of the notes
\r
1099 if (query.next())
\r
1100 if (query.getBlob(0) != null)
\r
1101 return new QByteArray(query.getBlob(0));
\r
1106 // Update a note content's hash. This happens if a resource is edited outside of NN
\r
1107 public void updateResourceContentHash(String guid, String oldHash, String newHash) {
\r
1108 Note n = getNote(guid, true, false, false, false,false);
\r
1109 int position = n.getContent().indexOf("<en-media");
\r
1111 for (;position>-1;) {
\r
1112 endPos = n.getContent().indexOf(">", position+1);
\r
1113 String oldSegment = n.getContent().substring(position,endPos);
\r
1114 int hashPos = oldSegment.indexOf("hash=\"");
\r
1115 int hashEnd = oldSegment.indexOf("\"", hashPos+7);
\r
1116 String hash = oldSegment.substring(hashPos+6, hashEnd);
\r
1117 if (hash.equalsIgnoreCase(oldHash)) {
\r
1118 String newSegment = oldSegment.replace(oldHash, newHash);
\r
1119 String content = n.getContent().substring(0,position) +
\r
1121 n.getContent().substring(endPos);
\r
1122 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
1123 query.prepare("update note set isdirty=true, content=:content where guid=:guid");
\r
1124 query.bindValue(":content", content);
\r
1125 query.bindValue(":guid", n.getGuid());
\r
1129 position = n.getContent().indexOf("<en-media", position+1);
\r