-/*\r
- * This file is part of NeverNote \r
- * Copyright 2009 Randy Baumgarte\r
- * \r
- * This file may be licensed under the terms of of the\r
- * GNU General Public License Version 2 (the ``GPL'').\r
- *\r
- * Software distributed under the License is distributed\r
- * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
- * express or implied. See the GPL for the specific language\r
- * governing rights and limitations.\r
- *\r
- * You should have received a copy of the GPL along with this\r
- * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
- * or write to the Free Software Foundation, Inc.,\r
- * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
- *\r
-*/\r
-\r
-\r
-package cx.fbn.nevernote.sql.runners;\r
-\r
-import java.text.DateFormat;\r
-import java.text.ParseException;\r
-import java.text.SimpleDateFormat;\r
-import java.util.ArrayList;\r
-import java.util.List;\r
-\r
-import com.evernote.edam.type.Note;\r
-import com.evernote.edam.type.NoteAttributes;\r
-import com.evernote.edam.type.Resource;\r
-import com.evernote.edam.type.Tag;\r
-import com.trolltech.qt.core.QByteArray;\r
-import com.trolltech.qt.core.QDateTime;\r
-import com.trolltech.qt.core.QTextCodec;\r
-\r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.evernote.EnmlConverter;\r
-import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
-import cx.fbn.nevernote.utilities.ApplicationLogger;\r
-import cx.fbn.nevernote.utilities.Pair;\r
-\r
-public class RNoteTable {\r
- private final ApplicationLogger logger;\r
- public final RNoteTagsTable noteTagsTable;\r
- public RNoteResourceTable noteResourceTable;\r
- private final RDatabaseConnection db;\r
- int id;\r
-\r
- // Prepared Queries to improve speed\r
- private NSqlQuery getQueryWithContent;\r
- private NSqlQuery getQueryWithoutContent;\r
- private NSqlQuery getAllQueryWithoutContent;\r
- \r
- // Constructor\r
- public RNoteTable(ApplicationLogger l, RDatabaseConnection d) {\r
- logger = l;\r
- db = d;\r
- id = 0;\r
- noteResourceTable = new RNoteResourceTable(logger, db);\r
- noteTagsTable = new RNoteTagsTable(logger, db);\r
- getQueryWithContent = null;\r
- getQueryWithoutContent = null;\r
- \r
- }\r
- // Create the table\r
- public void createTable() {\r
- getQueryWithContent = new NSqlQuery(db.getConnection());\r
- getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- logger.log(logger.HIGH, "Creating table Note...");\r
- if (!query.exec("Create table Note (guid varchar primary key, " +\r
- "updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+\r
- "contentLength integer, created timestamp, updated timestamp, deleted timestamp, " \r
- +"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+\r
- "attributeLatitude double, attributeLongitude double, attributeAltitude double,"+\r
- "attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+\r
- "attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +\r
- "isDirty boolean)")) \r
- logger.log(logger.HIGH, "Table Note creation FAILED!!!"); \r
- if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))\r
- logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");\r
- if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);"))\r
- logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!"); \r
- noteTagsTable.createTable();\r
- noteResourceTable.createTable(); \r
- }\r
- // Drop the table\r
- public void dropTable() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.exec("Drop table Note");\r
- noteTagsTable.dropTable();\r
- noteResourceTable.dropTable();\r
- }\r
- // Save Note List from Evernote \r
- public void addNote(Note n, boolean isDirty) {\r
- logger.log(logger.EXTREME, "Inside addNote");\r
- if (n == null)\r
- return;\r
- \r
- SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
-\r
- NSqlQuery query = new NSqlQuery(db.getConnection()); \r
- query.prepare("Insert Into Note ("\r
- +"guid, updateSequenceNumber, title, content, "\r
- +"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "\r
- +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
- +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
- +"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded" \r
- +") Values("\r
- +":guid, :updateSequenceNumber, :title, :content, "\r
- +":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "\r
- +":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "\r
- +":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "\r
- +":indexNeeded, :isExpunged, :isDirty, -1, true) ");\r
-\r
- StringBuilder created = new StringBuilder(simple.format(n.getCreated())); \r
- StringBuilder updated = new StringBuilder(simple.format(n.getUpdated())); \r
- StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));\r
-\r
- EnmlConverter enml = new EnmlConverter(logger);\r
- \r
- query.bindValue(":guid", n.getGuid());\r
- query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());\r
- query.bindValue(":title", n.getTitle());\r
- query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));\r
- query.bindValue(":contentHash", n.getContentHash());\r
- query.bindValue(":contentLength", n.getContentLength());\r
- query.bindValue(":created", created.toString());\r
- query.bindValue(":updated", updated.toString());\r
- query.bindValue(":deleted", deleted.toString());\r
- query.bindValue(":active", n.isActive());\r
- query.bindValue(":notebookGuid", n.getNotebookGuid());\r
- \r
- if (n.getAttributes() != null) {\r
- created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));\r
- query.bindValue(":attributeSubjectDate", created.toString());\r
- query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());\r
- query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());\r
- query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());\r
- query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());\r
- query.bindValue(":attributeSource", n.getAttributes().getSource());\r
- query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL());\r
- query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication());\r
- }\r
- query.bindValue(":indexNeeded", true);\r
- query.bindValue(":isExpunged", false);\r
- query.bindValue(":isDirty", isDirty);\r
-\r
- \r
- if (!query.exec())\r
- logger.log(logger.MEDIUM, query.lastError());\r
- \r
- // Save the note tags\r
- if (n.getTagGuids() != null) {\r
- for (int i=0; i<n.getTagGuids().size(); i++) \r
- noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i));\r
- }\r
- logger.log(logger.EXTREME, "Leaving addNote");\r
- } \r
- // Setup queries for get to save time later\r
- private void prepareQueries() {\r
- getQueryWithContent = new NSqlQuery(db.getConnection());\r
- getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
- getAllQueryWithoutContent = new NSqlQuery(db.getConnection());\r
- \r
- if (!getQueryWithContent.prepare("Select "\r
- +"guid, updateSequenceNumber, title, "\r
- +"created, updated, deleted, active, notebookGuid, "\r
- +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
- +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
- +"content, contentHash, contentLength"\r
- +" from Note where guid=:guid and isExpunged=false")) {\r
- logger.log(logger.EXTREME, "Note SQL select prepare with content has failed.");\r
- logger.log(logger.MEDIUM, getQueryWithContent.lastError());\r
- }\r
- \r
- if (!getQueryWithoutContent.prepare("Select "\r
- +"guid, updateSequenceNumber, title, "\r
- +"created, updated, deleted, active, notebookGuid, "\r
- +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
- +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
- +" from Note where guid=:guid and isExpunged=false")) {\r
- logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
- logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
- }\r
- if (!getAllQueryWithoutContent.prepare("Select "\r
- +"guid, updateSequenceNumber, title, "\r
- +"created, updated, deleted, active, notebookGuid, "\r
- +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
- +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
- +" from Note where isExpunged = false")) {\r
- logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
- logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
- }\r
- }\r
-\r
- // Get a note's content in raw, binary format for the sync.\r
- public String getNoteContentBinary(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Select content from note where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- query.exec();\r
- \r
- query.next();\r
- return query.valueString(0);\r
- }\r
- // Get a note by Guid\r
- public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
- if (noteGuid == null)\r
- return null;\r
- if (noteGuid.trim().equals(""))\r
- return null;\r
-\r
- prepareQueries();\r
- NSqlQuery query;\r
- if (loadContent) {\r
- query = getQueryWithContent;\r
- } else {\r
- query = getQueryWithoutContent;\r
- }\r
- \r
- query.bindValue(":guid", noteGuid);\r
- if (!query.exec()) {\r
- logger.log(logger.EXTREME, "Note SQL select exec has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- return null;\r
- }\r
- if (!query.next()) {\r
- logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");\r
- logger.log(logger.EXTREME, " -> " +query.lastError().toString());\r
- logger.log(logger.EXTREME, " -> " +query.lastError());\r
- return null;\r
- }\r
- Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags);\r
- n.setContent(fixCarriageReturn(n.getContent()));\r
- return n;\r
- }\r
- // Get a note by Guid\r
- public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
- DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
-// indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
-\r
- \r
- Note n = new Note();\r
- NoteAttributes na = new NoteAttributes();\r
- n.setAttributes(na);\r
- \r
- n.setGuid(query.valueString(0));\r
- n.setUpdateSequenceNum(new Integer(query.valueString(1)));\r
- n.setTitle(query.valueString(2));\r
-\r
- try {\r
- n.setCreated(indfm.parse(query.valueString(3)).getTime());\r
- n.setUpdated(indfm.parse(query.valueString(4)).getTime());\r
- n.setDeleted(indfm.parse(query.valueString(5)).getTime());\r
- } catch (ParseException e) {\r
- e.printStackTrace();\r
- }\r
-\r
- n.setActive(query.valueBoolean(6,true));\r
- n.setNotebookGuid(query.valueString(7));\r
- \r
- try {\r
- String attributeSubjectDate = query.valueString(8);\r
- if (!attributeSubjectDate.equals(""))\r
- na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());\r
- } catch (ParseException e) {\r
- e.printStackTrace();\r
- }\r
- na.setLatitude(new Float(query.valueString(9)));\r
- na.setLongitude(new Float(query.valueString(10)));\r
- na.setAltitude(new Float(query.valueString(11)));\r
- na.setAuthor(query.valueString(12));\r
- na.setSource(query.valueString(13));\r
- na.setSourceURL(query.valueString(14));\r
- na.setSourceApplication(query.valueString(15));\r
- \r
- if (loadTags) {\r
- n.setTagGuids(noteTagsTable.getNoteTags(n.getGuid()));\r
- List<String> tagNames = new ArrayList<String>();\r
- RTagTable tagTable = new RTagTable(logger, db);\r
- for (int i=0; i<n.getTagGuids().size(); i++) {\r
- String currentGuid = n.getTagGuids().get(i);\r
- Tag tag = tagTable.getTag(currentGuid);\r
- tagNames.add(tag.getName());\r
- }\r
- n.setTagNames(tagNames);\r
- }\r
- \r
- if (loadContent) {\r
- \r
- QTextCodec codec = QTextCodec.codecForLocale();\r
- codec = QTextCodec.codecForName("UTF-8");\r
- String unicode = codec.fromUnicode(query.valueString(16)).toString();\r
- n.setContent(unicode);\r
-// n.setContent(query.valueString(16).toString());\r
- \r
- String contentHash = query.valueString(17);\r
- if (contentHash != null)\r
- n.setContentHash(contentHash.getBytes());\r
- n.setContentLength(new Integer(query.valueString(18)));\r
- }\r
- if (loadResources)\r
- n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));\r
- if (loadRecognition) {\r
- if (n.getResources() == null) {\r
- List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());\r
- n.setResources(resources);\r
- } else {\r
- // We need to merge the recognition resources with the note resources retrieved earlier\r
- for (int i=0; i<n.getResources().size(); i++) {\r
- Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());\r
- n.getResources().get(i).setRecognition(r.getRecognition());\r
- }\r
- }\r
- }\r
- n.setContent(fixCarriageReturn(n.getContent()));\r
- return n;\r
- }\r
- // Update a note's title\r
- public void updateNoteTitle(String guid, String title) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note title sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- query.bindValue(":title", title);\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note title has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- // Update a note's creation date\r
- public void updateNoteCreatedDate(String guid, QDateTime date) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));\r
- query.bindValue(":guid", guid);\r
- \r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note creation date has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- // Update a note's creation date\r
- public void updateNoteAlteredDate(String guid, QDateTime date) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
- query.bindValue(":guid", guid);\r
- \r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note altered date has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- // Update a note's creation date\r
- public void updateNoteSubjectDate(String guid, QDateTime date) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
- query.bindValue(":guid", guid);\r
- \r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note subject date date has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- // Update a note's creation date\r
- public void updateNoteAuthor(String guid, String author) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
-\r
- query.bindValue(":author", author);\r
- query.bindValue(":guid", guid);\r
-\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note author has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- }\r
- // Update a note's creation date\r
- public void updateNoteSourceUrl(String guid, String url) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note url sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- query.bindValue(":url", url);\r
- query.bindValue(":guid", guid);\r
-\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note url has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- }\r
- // Update the notebook that a note is assigned to\r
- public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {\r
- String currentNotebookGuid = new String("");\r
- \r
- \r
- // If we are going from a synchronized notebook to a local notebook, we\r
- // need to tell Evernote to purge the note online. However, if this is \r
- // conflicting change we move it to the local notebook without deleting it \r
- // or it would then delete the copy on the remote server.\r
- RNotebookTable notebookTable = new RNotebookTable(logger, db);\r
- RDeletedTable deletedTable = new RDeletedTable(logger, db);\r
- if (expungeFromRemote) {\r
- if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {\r
- deletedTable.addDeletedItem(guid, "NOTE");\r
- }\r
- }\r
- \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- query.bindValue(":notebook", notebookGuid);\r
- query.bindValue(":guid", guid);\r
- \r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note notebook has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- };\r
- }\r
- // Update a note's title\r
- public void updateNoteContent(String guid, String content) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set content=:content, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true " +\r
- " where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note content sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- query.bindValue(":content", content);\r
- query.bindValue(":guid", guid);\r
-\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note content has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
-\r
- \r
- // Check a note to see if it passes the attribute selection criteria\r
- public boolean checkAttributeSelection(Note n) {\r
- if (Global.createdSinceFilter.check(n) &&\r
- Global.createdBeforeFilter.check(n) && \r
- Global.changedSinceFilter.check(n) &&\r
- Global.changedBeforeFilter.check(n) )\r
- return true;\r
- \r
- return false;\r
- }\r
- // Delete a note\r
- public void deleteNote(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- if (!query.exec()) {\r
- logger.log(logger.MEDIUM, "Note delete failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- public void restoreNote(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Update Note set deleted='1969-12-31 19.00.00', active=true, isDirty=true where guid=:guid");\r
-// query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- if (!query.exec()) {\r
- logger.log(logger.MEDIUM, "Note restore failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- // Purge a note (actually delete it instead of just marking it deleted)\r
- public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {\r
- \r
- if (!permanentExpunge) {\r
- hideExpungedNote(guid, needsSync);\r
- return;\r
- }\r
- \r
- \r
- NSqlQuery note = new NSqlQuery(db.getConnection());\r
- NSqlQuery resources = new NSqlQuery(db.getConnection());\r
- NSqlQuery tags = new NSqlQuery(db.getConnection());\r
- NSqlQuery words = new NSqlQuery(db.getConnection());\r
- \r
- note.prepare("Delete from Note where guid=:guid");\r
- resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
- tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
- words.prepare("Delete from words where guid=:guid");\r
-\r
- note.bindValue(":guid", guid);\r
- resources.bindValue(":guid", guid);\r
- tags.bindValue(":guid", guid);\r
- words.bindValue(":guid", guid);\r
- \r
- // Start purging notes.\r
- if (!note.exec()) {\r
- logger.log(logger.MEDIUM, "Purge from note failed.");\r
- logger.log(logger.MEDIUM, note.lastError());\r
- }\r
- if (!resources.exec()) {\r
- logger.log(logger.MEDIUM, "Purge from resources failed.");\r
- logger.log(logger.MEDIUM, resources.lastError());\r
- }\r
- if (!tags.exec()) {\r
- logger.log(logger.MEDIUM, "Note tags delete failed.");\r
- logger.log(logger.MEDIUM, tags.lastError());\r
- }\r
- if (!words.exec()) {\r
- logger.log(logger.MEDIUM, "Word delete failed.");\r
- logger.log(logger.MEDIUM, words.lastError());\r
- }\r
- if (needsSync) {\r
- RDeletedTable deletedTable = new RDeletedTable(logger, db);\r
- deletedTable.addDeletedItem(guid, "Note");\r
- }\r
-\r
- }\r
- // Purge a note (actually delete it instead of just marking it deleted)\r
- public void hideExpungedNote(String guid, boolean needsSync) {\r
- NSqlQuery note = new NSqlQuery(db.getConnection());\r
- NSqlQuery resources = new NSqlQuery(db.getConnection());\r
- NSqlQuery tags = new NSqlQuery(db.getConnection());\r
- NSqlQuery words = new NSqlQuery(db.getConnection());\r
- \r
- note.prepare("Update Note set isExpunged=true where guid=:guid");\r
- resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
- tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
- words.prepare("Delete from words where guid=:guid");\r
-\r
- note.bindValue(":guid", guid);\r
- resources.bindValue(":guid", guid);\r
- tags.bindValue(":guid", guid);\r
- words.bindValue(":guid", guid);\r
-\r
- // Start purging notes.\r
- if (!note.exec()) {\r
- logger.log(logger.MEDIUM, "Purge from note failed.");\r
- logger.log(logger.MEDIUM, note.lastError());\r
- }\r
- if (!resources.exec()) {\r
- logger.log(logger.MEDIUM, "Purge from resources failed.");\r
- logger.log(logger.MEDIUM, resources.lastError());\r
- }\r
- if (!tags.exec()) {\r
- logger.log(logger.MEDIUM, "Note tags delete failed.");\r
- logger.log(logger.MEDIUM, tags.lastError());\r
- }\r
- if (!words.exec()) {\r
- logger.log(logger.MEDIUM, "Word delete failed.");\r
- logger.log(logger.MEDIUM, words.lastError());\r
- }\r
- if (needsSync) {\r
- RDeletedTable deletedTable = new RDeletedTable(logger, db);\r
- deletedTable.addDeletedItem(guid, "Note");\r
- }\r
- }\r
-\r
- \r
- // Purge all deleted notes;\r
- public void expungeAllDeletedNotes() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.exec("select guid, updateSequenceNumber from note where active = false");\r
- while (query.next()) {\r
- String guid = query.valueString(0);\r
- Integer usn = new Integer(query.valueString(1));\r
- if (usn == 0)\r
- expungeNote(guid, true, false);\r
- else\r
- expungeNote(guid, false, true);\r
- }\r
- }\r
- // Update the note sequence number\r
- public void updateNoteSequence(String guid, int sequence) {\r
- boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");\r
-\r
- query.bindValue(":sequence", sequence);\r
- query.bindValue(":guid", guid);\r
- \r
- query.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note sequence update failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- } \r
- }\r
- // Update the note Guid\r
- public void updateNoteGuid(String oldGuid, String newGuid) {\r
- boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Update Note set guid=:newGuid where guid=:oldGuid");\r
-\r
- query.bindValue(":newGuid", newGuid);\r
- query.bindValue(":oldGuid", oldGuid);\r
-\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note Guid update failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- } \r
- \r
- query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");\r
- query.bindValue(":newGuid", newGuid);\r
- query.bindValue(":oldGuid", oldGuid);\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- \r
- query.prepare("Update words set guid=:newGuid where guid=:oldGuid");\r
- query.bindValue(":newGuid", newGuid);\r
- query.bindValue(":oldGuid", oldGuid);\r
- query.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note guid update failed for Words.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- query.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");\r
- query.bindValue(":newGuid", newGuid);\r
- query.bindValue(":oldGuid", oldGuid);\r
- query.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- // Update a note\r
- public void updateNote(Note n, boolean isNew) {\r
- boolean isExpunged = isNoteExpunged(n.getGuid());\r
- \r
- expungeNote(n.getGuid(), !isExpunged, false);\r
- addNote(n, false);\r
- }\r
- // Does a note exist?\r
- public boolean exists(String guid) {\r
- if (guid == null)\r
- return false;\r
- if (guid.trim().equals(""))\r
- return false;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Select guid from note where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- if (!query.exec())\r
- logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");\r
- boolean retVal = query.next();\r
- return retVal;\r
- }\r
- // Does a note exist?\r
- public boolean isNoteExpunged(String guid) {\r
- if (guid == null)\r
- return false;\r
- if (guid.trim().equals(""))\r
- return false;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");\r
- query.bindValue(":guid", guid);\r
- if (!query.exec())\r
- logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");\r
- boolean retVal = query.next();\r
- return retVal;\r
- }\r
- // This is a convience method to check if a tag exists & update/create based upon it\r
- public void syncNote(Note tag, boolean isDirty) {\r
- if (exists(tag.getGuid()))\r
- updateNote(tag, isDirty);\r
- else\r
- addNote(tag, isDirty);\r
- }\r
- // Get a list of notes that need to be updated\r
- public List <Note> getDirty() {\r
- String guid;\r
- Note tempNote;\r
- List<Note> notes = new ArrayList<Note>();\r
- List<String> index = new ArrayList<String>();\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.exec("Select guid from Note where isDirty = true and notebookGuid not in (select guid from notebook where local = true)");\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
- \r
- // Get a list of the notes\r
- while (query.next()) {\r
- guid = new String();\r
- guid = query.valueString(0);\r
- index.add(guid); \r
- } \r
- \r
- // Start getting notes\r
- for (int i=0; i<index.size(); i++) {\r
- tempNote = getNote(index.get(i), true,true,false,true,true);\r
- notes.add(tempNote);\r
- }\r
- return notes; \r
- }\r
- // Get a list of notes that need to be updated\r
- public boolean isNoteDirty(String guid) {\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
- \r
- boolean returnValue;\r
- // Get a list of the notes\r
- if (query.next()) \r
- returnValue = true; \r
- else\r
- returnValue = false;\r
-\r
- return returnValue; \r
- }\r
- // Get a list of notes that need to be updated\r
- public List <String> getUnsynchronizedGUIDs() {\r
- String guid;\r
- List<String> index = new ArrayList<String>();\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.exec("Select guid from Note where isDirty = true");\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
- \r
- // Get a list of the notes\r
- while (query.next()) {\r
- guid = new String();\r
- guid = query.valueString(0);\r
- index.add(guid); \r
- } \r
- return index; \r
- }\r
- // Reset the dirty bit\r
- public void resetDirtyFlag(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- query.prepare("Update note set isdirty=false where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- if (!query.exec())\r
- logger.log(logger.EXTREME, "Error resetting note dirty field.");\r
- }\r
- // Get all notes\r
- public List<String> getAllGuids() {\r
- List<String> notes = new ArrayList<String>();\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.exec("Select guid from Note");\r
- if (!check)\r
- logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
-\r
- // Get a list of the notes\r
- while (query.next()) {\r
- notes.add(new String(query.valueString(0))); \r
- }\r
- return notes;\r
- }\r
- // Get all notes\r
- public List<Note> getAllNotes() {\r
- List<Note> notes = new ArrayList<Note>();\r
- prepareQueries();\r
- boolean check; \r
- NSqlQuery query = getAllQueryWithoutContent;\r
- check = query.exec();\r
- if (!check)\r
- logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
- // Get a list of the notes\r
- while (query.next()) {\r
- notes.add(mapNoteFromQuery(query, false, false, false, false, true));\r
- }\r
- return notes;\r
- }\r
- // Count unindexed notes\r
- public int getUnindexedCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.exec("select count(*) from note where indexneeded=true and isExpunged = false");\r
- query.next(); \r
- int returnValue = new Integer(query.valueString(0));\r
- return returnValue;\r
- }\r
- // Count unsynchronized notes\r
- public int getDirtyCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.exec("select count(*) from note where isDirty=true and isExpunged = false");\r
- query.next(); \r
- int returnValue = new Integer(query.valueString(0));\r
- return returnValue;\r
- }\r
- // Count notes\r
- public int getNoteCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.exec("select count(*) from note where isExpunged = false");\r
- query.next(); \r
- int returnValue = new Integer(query.valueString(0));\r
- return returnValue;\r
- }\r
- // Count deleted notes\r
- public int getDeletedCount() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.exec("select count(*) from note where isExpunged = false and active = false");\r
- if (!query.next()) \r
- return 0;\r
- int returnValue = new Integer(query.valueString(0));\r
- return returnValue;\r
- }\r
- // Reset a note sequence number to zero. This is useful for moving conflicting notes\r
- public void resetNoteSequence(String guid) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note sequence number has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- }\r
- }\r
- \r
- \r
- // Update a note resource by the hash\r
- public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
-/* query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");\r
- query.bindValue(":noteGuid", noteGuid);\r
- query.bindValue(":hex", hash);\r
- query.exec();\r
- if (!query.next()) {\r
- logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash. GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);\r
- return;\r
- }\r
- String guid = query.valueString(0);\r
-*/ \r
- query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");\r
- query.bindValue(":guid", resGuid);\r
- query.bindValue(":noteGuid", noteGuid);\r
- query.bindValue(":hex", hash);\r
- if (!query.exec()) {\r
- logger.log(logger.EXTREME, "Note Resource Update by Hash failed");\r
- logger.log(logger.EXTREME, query.lastError().toString());\r
- }\r
- }\r
-\r
- // Fix CRLF problem that is on some notes\r
- private String fixCarriageReturn(String note) {\r
- if (note == null || !Global.enableCarriageReturnFix)\r
- return note;\r
- QByteArray a0Hex = new QByteArray("a0");\r
- String a0 = QByteArray.fromHex(a0Hex).toString();\r
- note = note.replace("<div>"+a0+"</div>", "<div> </div>");\r
- return note.replace("<div/>", "<div> </div>");\r
- }\r
- \r
- \r
- \r
- //********************************************************************************\r
- //********************************************************************************\r
- //* Indexing Functions\r
- //********************************************************************************\r
- //********************************************************************************\r
- // set/unset a note to be reindexed\r
- public void setIndexNeeded(String guid, Boolean flag) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Update Note set indexNeeded=:flag where guid=:guid");\r
-\r
- if (flag)\r
- query.bindValue(":flag", 1);\r
- else\r
- query.bindValue(":flag", 0);\r
- query.bindValue(":guid", guid);\r
- if (!query.exec()) {\r
- logger.log(logger.MEDIUM, "Note indexNeeded update failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- } \r
- }\r
- // Set all notes to be reindexed\r
- public void reindexAllNotes() {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- if (!query.exec("Update Note set indexNeeded=true")) {\r
- logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
- } \r
- }\r
-\r
- // Get all unindexed notes\r
- public List <String> getUnindexed() {\r
- String guid;\r
- List<String> index = new ArrayList<String>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))\r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
-\r
- // Get a list of the notes\r
- while (query.next()) {\r
- guid = new String();\r
- guid = query.valueString(0);\r
- index.add(guid); \r
- } \r
- return index; \r
- }\r
- public List<String> getNextUnindexed(int limit) {\r
- List<String> guids = new ArrayList<String>();\r
- \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))\r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
- \r
- // Get a list of the notes\r
- String guid;\r
- while (query.next()) {\r
- guid = new String();\r
- guid = query.valueString(0);\r
- guids.add(guid);\r
- } \r
- return guids; \r
- }\r
- \r
- \r
- //**********************************************************************************\r
- //* Title color functions\r
- //**********************************************************************************\r
- // Get the title color of all notes\r
- public List<Pair<String, Integer>> getNoteTitleColors() {\r
- List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- if (!query.exec("Select guid,titleColor from Note where titleColor != -1"))\r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
-\r
- String guid;\r
- Integer color;\r
- \r
- // Get a list of the notes\r
- while (query.next()) {\r
- Pair<String, Integer> pair = new Pair<String,Integer>();\r
- guid = query.valueString(0);\r
- color = query.valueInteger(1);\r
- pair.setFirst(guid);\r
- pair.setSecond(color);\r
- returnValue.add(pair); \r
- } \r
-\r
- \r
- \r
- return returnValue;\r
- }\r
- // Set a title color\r
- // Reset the dirty bit\r
- public void setNoteTitleColor(String guid, int color) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- query.prepare("Update note set titlecolor=:color where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- query.bindValue(":color", color);\r
- if (!query.exec())\r
- logger.log(logger.EXTREME, "Error updating title color.");\r
- }\r
-\r
- \r
- \r
- //**********************************************************************************\r
- //* Thumbnail functions\r
- //**********************************************************************************\r
- // Set if a new thumbnail is needed\r
- public void setThumbnailNeeded(String guid, boolean needed) {\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- query.bindValue(":needed", needed);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());\r
-\r
- }\r
- // Is a thumbail needed for this guid?\r
- public boolean isThumbnailNeeded(String guid) {\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("select thumbnailneeded from note where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());\r
- \r
- boolean returnValue;\r
- // Get a list of the notes\r
- if (query.next()) \r
- returnValue = query.valueBoolean(0, false); \r
- else\r
- returnValue = false;\r
-\r
- return returnValue; \r
- }\r
- // Set if a new thumbnail is needed\r
- public void setThumbnail(String guid, QByteArray thumbnail) {\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- query.bindValue(":thumbnail", thumbnail.toByteArray());\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());\r
-\r
- }\r
- // Set if a new thumbnail is needed\r
- public QByteArray getThumbnail(String guid) {\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("Select thumbnail from note where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());\r
- // Get a list of the notes\r
- if (query.next()) \r
- if (query.getBlob(0) != null)\r
- return new QByteArray(query.getBlob(0)); \r
- return null;\r
- }\r
- \r
- \r
- // Update a note content's hash. This happens if a resource is edited outside of NN\r
- public void updateResourceContentHash(String guid, String oldHash, String newHash) {\r
- Note n = getNote(guid, true, false, false, false,false);\r
- int position = n.getContent().indexOf("<en-media");\r
- int endPos;\r
- for (;position>-1;) {\r
- endPos = n.getContent().indexOf(">", position+1);\r
- String oldSegment = n.getContent().substring(position,endPos);\r
- int hashPos = oldSegment.indexOf("hash=\"");\r
- int hashEnd = oldSegment.indexOf("\"", hashPos+7);\r
- String hash = oldSegment.substring(hashPos+6, hashEnd);\r
- if (hash.equalsIgnoreCase(oldHash)) {\r
- String newSegment = oldSegment.replace(oldHash, newHash);\r
- String content = n.getContent().substring(0,position) +\r
- newSegment +\r
- n.getContent().substring(endPos);\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("update note set isdirty=true, content=:content where guid=:guid");\r
- query.bindValue(":content", content);\r
- query.bindValue(":guid", n.getGuid());\r
- query.exec();\r
- }\r
- \r
- position = n.getContent().indexOf("<en-media", position+1);\r
- }\r
- }\r
-} \r
OSDN Git Service
