X-Git-Url: http://git.sourceforge.jp/view?p=neighbornote%2FNeighborNote.git;a=blobdiff_plain;f=src%2Fcx%2Ffbn%2Fnevernote%2Fsql%2FNoteTable.java;h=f19deee50f295fae8fe6eaf31b11313e3d25ddaa;hp=97c0b15e09eb931a9acff23887a71c39461ae1d8;hb=e565a6dfd9ace9638a7afcfb66f7a2f34c6a82ce;hpb=8a07f5da9560792e75e4f85dd6bb1d096519b975 diff --git a/src/cx/fbn/nevernote/sql/NoteTable.java b/src/cx/fbn/nevernote/sql/NoteTable.java index 97c0b15..f19deee 100644 --- a/src/cx/fbn/nevernote/sql/NoteTable.java +++ b/src/cx/fbn/nevernote/sql/NoteTable.java @@ -1,5 +1,5 @@ /* - * This file is part of NeverNote + * This file is part of NixNote * Copyright 2009 Randy Baumgarte * * This file may be licensed under the terms of of the @@ -27,6 +27,8 @@ import java.util.ArrayList; import java.util.HashMap; import java.util.List; +import org.apache.commons.lang3.StringEscapeUtils; + import com.evernote.edam.type.Note; import com.evernote.edam.type.NoteAttributes; import com.evernote.edam.type.Resource; @@ -38,6 +40,7 @@ import com.trolltech.qt.gui.QPixmap; import cx.fbn.nevernote.Global; import cx.fbn.nevernote.evernote.EnmlConverter; +import cx.fbn.nevernote.evernote.NoteMetadata; import cx.fbn.nevernote.sql.driver.NSqlQuery; import cx.fbn.nevernote.utilities.ApplicationLogger; import cx.fbn.nevernote.utilities.Pair; @@ -63,12 +66,11 @@ public class NoteTable { noteTagsTable = new NoteTagsTable(logger, db); getQueryWithContent = null; getQueryWithoutContent = null; - } // Create the table public void createTable() { - getQueryWithContent = new NSqlQuery(db.getConnection()); - getQueryWithoutContent = new NSqlQuery(db.getConnection()); + //getQueryWithContent = new NSqlQuery(db.getConnection()); + //getQueryWithoutContent = new NSqlQuery(db.getConnection()); NSqlQuery query = new NSqlQuery(db.getConnection()); logger.log(logger.HIGH, "Creating table Note..."); if (!query.exec("Create table Note (guid varchar primary key, " + @@ -85,7 +87,7 @@ public class NoteTable { if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);")) logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!"); noteTagsTable.createTable(); - noteResourceTable.createTable(); +// noteResourceTable.createTable(); } // Drop the table public void dropTable() { @@ -120,12 +122,17 @@ public class NoteTable { StringBuilder updated = new StringBuilder(simple.format(n.getUpdated())); StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted())); - EnmlConverter enml = new EnmlConverter(logger); + query.bindValue(":guid", n.getGuid()); query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum()); query.bindValue(":title", n.getTitle()); - query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent()))); + if (isDirty) { + EnmlConverter enml = new EnmlConverter(logger); + query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent()))); + } + else + query.bindValue(":content", n.getContent()); query.bindValue(":contentHash", n.getContentHash()); query.bindValue(":contentLength", n.getContentLength()); query.bindValue(":created", created.toString()); @@ -144,6 +151,16 @@ public class NoteTable { query.bindValue(":attributeSource", n.getAttributes().getSource()); query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL()); query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication()); + } else { + created = new StringBuilder(simple.format(n.getCreated())); + query.bindValue(":attributeSubjectDate", created.toString()); + query.bindValue(":attributeLatitude", 0.0); + query.bindValue(":attributeLongitude", 0.0); + query.bindValue(":attributeAltitude", 0.0); + query.bindValue(":attributeAuthor", ""); + query.bindValue(":attributeSource", ""); + query.bindValue(":attributeSourceUrl", ""); + query.bindValue(":attributeSourceApplication", ""); } query.bindValue(":indexNeeded", true); query.bindValue(":isExpunged", false); @@ -162,50 +179,52 @@ public class NoteTable { } // Setup queries for get to save time later private void prepareQueries() { - getQueryWithContent = new NSqlQuery(db.getConnection()); - getQueryWithoutContent = new NSqlQuery(db.getConnection()); - getAllQueryWithoutContent = new NSqlQuery(db.getConnection()); - - if (!getQueryWithContent.prepare("Select " - +"guid, updateSequenceNumber, title, " - +"created, updated, deleted, active, notebookGuid, " - +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, " - +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, " - +"content, contentHash, contentLength" - +" from Note where guid=:guid and isExpunged=false")) { - logger.log(logger.EXTREME, "Note SQL select prepare with content has failed."); - logger.log(logger.MEDIUM, getQueryWithContent.lastError()); + if (getQueryWithContent == null) { + getQueryWithContent = new NSqlQuery(db.getConnection()); + if (!getQueryWithContent.prepare("Select " + +"guid, updateSequenceNumber, title, " + +"created, updated, deleted, active, notebookGuid, " + +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, " + +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, " + +"attributeContentClass, " + +"content, contentHash, contentLength" + +" from Note where guid=:guid and isExpunged=false")) { + logger.log(logger.EXTREME, "Note SQL select prepare with content has failed."); + logger.log(logger.MEDIUM, getQueryWithContent.lastError()); + } } - if (!getQueryWithoutContent.prepare("Select " - +"guid, updateSequenceNumber, title, " - +"created, updated, deleted, active, notebookGuid, " - +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, " - +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication " - +" from Note where guid=:guid and isExpunged=false")) { - logger.log(logger.EXTREME, "Note SQL select prepare without content has failed."); - logger.log(logger.MEDIUM, getQueryWithoutContent.lastError()); + if (getQueryWithoutContent == null) { + getQueryWithoutContent = new NSqlQuery(db.getConnection()); + if (!getQueryWithoutContent.prepare("Select " + +"guid, updateSequenceNumber, title, " + +"created, updated, deleted, active, notebookGuid, " + +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, " + +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, " + +"attributeContentClass" + +" from Note where guid=:guid and isExpunged=false")) { + logger.log(logger.EXTREME, "Note SQL select prepare without content has failed."); + logger.log(logger.MEDIUM, getQueryWithoutContent.lastError()); + } } - if (!getAllQueryWithoutContent.prepare("Select " + + if (getAllQueryWithoutContent == null) { + getAllQueryWithoutContent = new NSqlQuery(db.getConnection()); + + if (!getAllQueryWithoutContent.prepare("Select " +"guid, updateSequenceNumber, title, " +"created, updated, deleted, active, notebookGuid, " +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, " - +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication " + +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, " + +"attributeContentClass " +" from Note where isExpunged = false")) { - logger.log(logger.EXTREME, "Note SQL select prepare without content has failed."); + logger.log(logger.EXTREME, "Note SQL select prepare without content has failed."); logger.log(logger.MEDIUM, getQueryWithoutContent.lastError()); + } } } - // Get a note's content in raw, binary format for the sync. - public String getNoteContentBinary(String guid) { - NSqlQuery query = new NSqlQuery(db.getConnection()); - query.prepare("Select content from note where guid=:guid"); - query.bindValue(":guid", guid); - query.exec(); - query.next(); - return query.valueString(0); - } + // Get a note's content in blob format for index. public String getNoteContentNoUTFConversion(String guid) { NSqlQuery query = new NSqlQuery(db.getConnection()); @@ -217,6 +236,8 @@ public class NoteTable { } // Get a note by Guid public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) { + +// extractMetadata("otherKey:{values};baumgarte:{titleColor=fff;pinned=true;};finalKey:{values1);"); if (noteGuid == null) return null; if (noteGuid.trim().equals("")) @@ -244,6 +265,7 @@ public class NoteTable { } Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags); n.setContent(fixCarriageReturn(n.getContent())); + n.getAttributes().setContentClassIsSet(false); return n; } // Get a note by Guid @@ -251,7 +273,6 @@ public class NoteTable { DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S"); // indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy"); - Note n = new Note(); NoteAttributes na = new NoteAttributes(); n.setAttributes(na); @@ -285,31 +306,67 @@ public class NoteTable { na.setSource(query.valueString(13)); na.setSourceURL(query.valueString(14)); na.setSourceApplication(query.valueString(15)); + na.setContentClass(query.valueString(16)); if (loadTags) { - n.setTagGuids(noteTagsTable.getNoteTags(n.getGuid())); + List tagGuids = noteTagsTable.getNoteTags(n.getGuid()); List tagNames = new ArrayList(); - TagTable tagTable = new TagTable(logger, db); - for (int i=0; i 0) { + unicode = query.valueString(17); + //System.out.println(unicode); + //unicode = unicode.replace("<", "&_lt;"); + //unicode = codec.fromUnicode(StringEscapeUtils.unescapeHtml(unicode)).toString(); + //unicode = unicode.replace("&_lt;", "<"); + //System.out.println("************************"); + int j=1; + for (int i=buffer.indexOf("&#"); i != -1 && buffer.indexOf("&#", i)>0; i=buffer.indexOf("&#",i+1)) { + j = buffer.indexOf(";",i)+1; + if (i notes = getNotesByNotebook(notebookGuid); + for (int i=0; i guids = new ArrayList(); + List usns = new ArrayList(); while (query.next()) { - String guid = query.valueString(0); + guids.add(query.valueString(0)); Integer usn = new Integer(query.valueString(1)); + usns.add(usn); + } + + for (int i=0; i getDirty() { @@ -757,6 +890,36 @@ public class NoteTable { tempNote = getNote(index.get(i), true,true,false,true,true); notes.add(tempNote); } + logger.log(logger.LOW, "Dirty local notes: " +new Integer(notes.size()).toString()); + return notes; + } + // Get a list of notes that need to be updated + public List getDirtyLinkedNotes() { + String guid; + Note tempNote; + List notes = new ArrayList(); + List index = new ArrayList(); + + boolean check; + NSqlQuery query = new NSqlQuery(db.getConnection()); + + check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid in (select guid from notebook where linked = true)"); + if (!check) + logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString()); + + // Get a list of the notes + while (query.next()) { + guid = new String(); + guid = query.valueString(0); + index.add(guid); + } + + // Start getting notes + for (int i=0; i getUnsynchronizedGUIDs() { - String guid; - List index = new ArrayList(); - - boolean check; - NSqlQuery query = new NSqlQuery(db.getConnection()); - - check = query.exec("Select guid from Note where isDirty=true"); - if (!check) - logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString()); - - // Get a list of the notes - while (query.next()) { - guid = new String(); - guid = query.valueString(0); - index.add(guid); - } - return index; - } + // Reset the dirty bit public void resetDirtyFlag(String guid) { + logger.log(logger.LOW, "Resetting dirty flag for " +guid); NSqlQuery query = new NSqlQuery(db.getConnection()); query.prepare("Update note set isdirty=false where guid=:guid"); @@ -881,7 +1027,9 @@ public class NoteTable { public List getAllNotes() { List notes = new ArrayList(); prepareQueries(); - boolean check; + boolean check; + if (getAllQueryWithoutContent == null) + prepareQueries(); NSqlQuery query = getAllQueryWithoutContent; check = query.exec(); if (!check) @@ -906,6 +1054,42 @@ public class NoteTable { query.exec("select count(guid) from note where isDirty=true and isExpunged = false"); query.next(); int returnValue = new Integer(query.valueString(0)); + logger.log(logger.LOW, "dirty count: " +returnValue); + //query.exec("select count(guid) from note where isDirty=true and Active = 0 and isExpunged = false"); + //query.next(); + //logger.log(logger.LOW, "dirty count (active only): " +query.valueString(0)); + //query.exec("Select count(guid) from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true or linked = true)"); + //query.next(); + //logger.log(logger.LOW, "dirty count (no l&l notebooks): " +query.valueString(0)); + //logger.log(logger.LOW, "Beginning stack trace"); + //logger.log(logger.LOW, Thread.currentThread().getStackTrace()); + + //logger.log(logger.LOW, "*************************"); + //logger.log(logger.LOW, "*** DIRTY RECORD DUMP ***"); + //logger.log(logger.LOW, "*************************"); + //List recs = getDirty(); + //for (int i=0; i expungeIgnoreSynchronizedNotes(List notebooks, Listtags) { + public List expungeIgnoreSynchronizedNotes(List notebooks, Listtags, List linked) { List noteGuids = new ArrayList(); for (int i=0; i linkedTags = db.getTagTable().getTagsForNotebook(notebookGuid); + for (int j=0; j notes = findNotesByNotebook(notebookGuid); + for (int j=0; j> findNotesByTitle(String text) { + List> results = new ArrayList>(); + boolean check; + NSqlQuery query = new NSqlQuery(db.getConnection()); + + check = query.prepare("Select guid,title from Note where lower(title) like :title"); + if (!check) + logger.log(logger.EXTREME, "Note SQL prepare for search by title has failed: " +query.lastError().toString()); + + query.bindValue(":title", "%"+text.toLowerCase()+"%"); + query.exec(); + // Get a list of the notes + while (query.next()) { + Pair p = new Pair(); + p.setFirst(query.valueString(0)); + p.setSecond(query.valueString(1)); + results.add(p); + } + return results; + } + //******************************************************************************** @@ -1098,69 +1321,68 @@ public class NoteTable { } return guids; } + + // Get note meta information + public void updateNoteMetadata(NoteMetadata meta) { + NSqlQuery query = new NSqlQuery(db.getConnection()); + if (!query.prepare("Update Note set titleColor=:color, pinned=:pinned, attributeSourceApplication=:metaString where guid=:guid")) + logger.log(logger.EXTREME, "Note SQL prepare has failed on updateNoteMetadata."); + query.bindValue(":color", meta.getColor()); + query.bindValue(":pinned", meta.isPinned()); + query.bindValue(":guid", meta.getGuid()); + query.bindValue(":metaString", buildMetadataString(meta)); + if (!query.exec()) + logger.log(logger.EXTREME, "Note SQL exec has failed on updateNoteMetadata."); + return; + } - //********************************************************************************** - //* Title color functions - //********************************************************************************** - // Get the title color of all notes - public List> getNoteTitleColors() { - List> returnValue = new ArrayList>(); + // Get all note meta information + public HashMap getNotesMetaInformation() { + HashMap returnValue = new HashMap(); NSqlQuery query = new NSqlQuery(db.getConnection()); - if (!query.exec("Select guid,titleColor from Note where titleColor != -1")) - logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed()."); + if (!query.exec("Select guid,titleColor, isDirty, pinned from Note")) + logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteMetaInformation."); - String guid; - Integer color; - // Get a list of the notes while (query.next()) { - Pair pair = new Pair(); - guid = query.valueString(0); - color = query.valueInteger(1); - pair.setFirst(guid); - pair.setSecond(color); - returnValue.add(pair); + NoteMetadata note = new NoteMetadata(); + note.setGuid(query.valueString(0)); + note.setColor(query.valueInteger(1)); + note.setDirty(query.valueBoolean(2, false)); + int pinned = query.valueInteger(3); + if (pinned > 0) + note.setPinned(true); + returnValue.put(note.getGuid(), note); } return returnValue; } - // Set a title color - public void setNoteTitleColor(String guid, int color) { - NSqlQuery query = new NSqlQuery(db.getConnection()); - - query.prepare("Update note set titlecolor=:color where guid=:guid"); - query.bindValue(":guid", guid); - query.bindValue(":color", color); - if (!query.exec()) - logger.log(logger.EXTREME, "Error updating title color."); - } - // Get in individual note's title color - // Get the title color of all notes - public Integer getNoteTitleColor(String guid) { - List> returnValue = new ArrayList>(); + // Get note meta information + public NoteMetadata getNoteMetaInformation(String guid) { NSqlQuery query = new NSqlQuery(db.getConnection()); - query.prepare("Select titleColor from Note where titleColor != -1 and guid=:guid"); - query.bindValue(":guid", guid); - if (!query.exec()) - logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteTitleColor(guid)."); + if (!query.prepare("Select guid,titleColor, isDirty, pinned from Note where guid=:guid")) { + logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteMetaInformation."); + return null; + } + query.bindValue(":guid", guid); + query.exec(); - Integer color = -1; - // Get a list of the notes while (query.next()) { - Pair pair = new Pair(); - guid = query.valueString(0); - color = query.valueInteger(1); - pair.setFirst(guid); - pair.setSecond(color); - returnValue.add(pair); + NoteMetadata note = new NoteMetadata(); + note.setGuid(query.valueString(0)); + note.setColor(query.valueInteger(1)); + note.setDirty(query.valueBoolean(2, false)); + int pinned = query.valueInteger(3); + if (pinned > 0) + note.setPinned(true); + return note; } - - return color; + return null; } @@ -1304,6 +1526,41 @@ public class NoteTable { return 0; } + //*********************************************************************************** + public String findAlternateGuid(String guid) { + boolean check; + NSqlQuery query = new NSqlQuery(db.getConnection()); + + check = query.prepare("select guid from note where original_guid=:guid"); + query.bindValue(":guid", guid); + check = query.exec(); + if (!check) + logger.log(logger.EXTREME, "Note SQL findAlternateguid query failed: " +query.lastError().toString()); + + if (query.next()) { + return query.valueString(0); + } + + return null; + } + + //* Check if a note guid exists + public boolean guidExists(String guid) { + boolean check; + NSqlQuery query = new NSqlQuery(db.getConnection()); + + check = query.prepare("select guid from note where guid=:guid"); + query.bindValue(":guid", guid); + check = query.exec(); + if (!check) + logger.log(logger.EXTREME, "Note SQL guidExists query failed: " +query.lastError().toString()); + + if (query.next()) { + return true; + } + + return false; + } // Update a note content's hash. This happens if a resource is edited outside of NN public void updateResourceContentHash(String guid, String oldHash, String newHash) { @@ -1331,4 +1588,109 @@ public class NoteTable { position = n.getContent().indexOf(" 0) + metaString = metaString.substring(0,endPos); + + String value = parseMetaString(metaString, "titleColor"); + if (value != null) + meta.setColor(Integer.parseInt(value)); + + value = parseMetaString(metaString, "pinned"); + if (value != null && value.equals(true)) + meta.setPinned(true); + + return meta; + } + + // Given a metadata string from attribute.sourceApplication, we + // extract the information for a given key. + private String parseMetaString(String metaString, String key) { + int startPos = metaString.indexOf(key); + if (startPos < 0) + return null; + + String value = metaString.substring(startPos+key.length()+1); + int endPos = value.indexOf(";"); + if (endPos > 0) + value = value.substring(0,endPos); + + return value; + } + + // Given a set of metadata, we build a string that can be inserted + // into the attribute.sourceApplication string. + private String buildMetadataString(NoteMetadata meta) { + StringBuffer value = new StringBuffer(removeExistingMetaString(meta.getGuid())); + StringBuffer metaString = new StringBuffer(); + + if (meta.isPinned()) { + metaString.append("pinned=true;"); + } + if (meta.getColor() != -1) { + metaString.append("titleColor=" +new Integer(meta.getColor()).toString()+";"); + } + if (metaString.length()>0) { + + // Adda any missing ";" or " " at the end of the existing + // string. + if (value.length()>1 && (!value.toString().trim().endsWith(";") || !value.toString().trim().endsWith(";"))) + value.append("; "); + + value.append("baumgarte:{"); + value.append(metaString); + value.append("};"); + return value.toString(); + } + return null; + } + + // This will remove the existing metadata string from the attribute.sourceApplication string. + private String removeExistingMetaString(String guid) { + NSqlQuery query = new NSqlQuery(db.getConnection()); + + if (!query.prepare("Select attributeSourceApplication from Note where guid=:guid")) { + logger.log(logger.EXTREME, "Note SQL retrieve has failed in removeExistingMetaString."); + return null; + } + query.bindValue(":guid", guid); + query.exec(); + + // Get the application source string + String sourceApplication = null; + while (query.next()) { + sourceApplication = query.valueString(0); + } + if (sourceApplication == null) + return ""; + + String consumerKey = "baumgarte:{"; + int startPos = sourceApplication.indexOf(consumerKey); + if (startPos < 0 ) + return sourceApplication; + String startString = sourceApplication.substring(0,startPos); + String metaString = sourceApplication.substring(startPos); + String endString = metaString.substring(metaString.indexOf("};")+2); + + return startString+endString; + } + } + + + +