-/*\r
- * This file is part of NixNote \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;\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.HashMap;\r
-import java.util.List;\r
-\r
-import org.apache.commons.lang3.StringEscapeUtils;\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
-import com.trolltech.qt.gui.QPixmap;\r
-\r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.evernote.EnmlConverter;\r
-import cx.fbn.nevernote.evernote.NoteMetadata;\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 NoteTable {\r
- private final ApplicationLogger logger;\r
- public final NoteTagsTable noteTagsTable;\r
- public NoteResourceTable noteResourceTable;\r
- private final DatabaseConnection 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 NoteTable(ApplicationLogger l, DatabaseConnection d) {\r
- logger = l;\r
- db = d;\r
- id = 0;\r
- noteResourceTable = new NoteResourceTable(logger, db);\r
- noteTagsTable = new NoteTagsTable(logger, db);\r
- getQueryWithContent = null;\r
- getQueryWithoutContent = null;\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
- \r
- \r
- query.bindValue(":guid", n.getGuid());\r
- query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());\r
- query.bindValue(":title", n.getTitle());\r
- if (isDirty) {\r
- EnmlConverter enml = new EnmlConverter(logger);\r
- query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));\r
- }\r
- else\r
- query.bindValue(":content", 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
- if (getQueryWithContent == null) {\r
- getQueryWithContent = new NSqlQuery(db.getConnection());\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
- \r
- if (getQueryWithoutContent == null) {\r
- getQueryWithoutContent = new NSqlQuery(db.getConnection());\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
- }\r
- \r
- if (getAllQueryWithoutContent == null) {\r
- getAllQueryWithoutContent = new NSqlQuery(db.getConnection());\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
-\r
-\r
- // Get a note's content in blob format for index.\r
- public String getNoteContentNoUTFConversion(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
- 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
- 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
- TagTable tagTable = db.getTagTable();\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
- QTextCodec codec = QTextCodec.codecForLocale();\r
- codec = QTextCodec.codecForName("UTF-8");\r
- String unicode = codec.fromUnicode(query.valueString(16)).toString();\r
-\r
- // This is a hack. Basically I need to convert HTML Entities to "normal" text, but if I\r
- // convert the < character to < it will mess up the XML parsing. So, to get around this\r
- // I am "bit stuffing" the < to &< so StringEscapeUtils doesn't unescape it. After\r
- // I'm done I convert it back.\r
- StringBuffer buffer = new StringBuffer(unicode);\r
- if (Global.enableHTMLEntitiesFix && unicode.indexOf("&#") > 0) {\r
- unicode = query.valueString(16);\r
- //System.out.println(unicode);\r
- //unicode = unicode.replace("<", "&_lt;");\r
- //unicode = codec.fromUnicode(StringEscapeUtils.unescapeHtml(unicode)).toString();\r
- //unicode = unicode.replace("&_lt;", "<");\r
- //System.out.println("************************");\r
- int j=1;\r
- for (int i=buffer.indexOf("&#"); i != -1 && buffer.indexOf("&#", i)>0; i=buffer.indexOf("&#",i+1)) {\r
- j = buffer.indexOf(";",i)+1;\r
- if (i<j) {\r
- String entity = buffer.substring(i,j).toString();\r
- int len = entity.length()-1;\r
- String tempEntity = entity.substring(2, len);\r
- try {\r
- Integer.parseInt(tempEntity);\r
- entity = codec.fromUnicode(StringEscapeUtils.unescapeHtml4(entity)).toString();\r
- buffer.delete(i, j);\r
- buffer.insert(i, entity);\r
- } catch (Exception e){ }\r
- \r
- }\r
- } \r
- } \r
- \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 geo tags\r
- public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+\r
- "attributeLatitude=:latitude, attributeAltitude=:altitude, 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(":longitude", lon);\r
- query.bindValue(":latitude", lat);\r
- query.bindValue(":altitude", alt);\r
- query.bindValue(":guid", guid);\r
-\r
- check = query.exec();\r
- if (!check) {\r
- logger.log(logger.EXTREME, "Update note geo tag 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
- NotebookTable notebookTable = new NotebookTable(logger, db);\r
- DeletedTable deletedTable = new DeletedTable(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
- " thumbnailneeded=true 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
-// QTextCodec codec = QTextCodec.codecForLocale();\r
-// codec = QTextCodec.codecForName("UTF-8");\r
-// query.bindValue(":content", codec.fromUnicode(content).toString());\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
- // 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.getResourceConnection());\r
- NSqlQuery tags = new NSqlQuery(db.getConnection());\r
- NSqlQuery words = new NSqlQuery(db.getIndexConnection());\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
-\r
- if (!words.exec()) {\r
- logger.log(logger.MEDIUM, "Word delete failed.");\r
- logger.log(logger.MEDIUM, words.lastError());\r
- }\r
- if (needsSync) {\r
- DeletedTable deletedTable = new DeletedTable(logger, db);\r
- deletedTable.addDeletedItem(guid, "Note");\r
- }\r
-\r
- }\r
- // Purge a bunch of notes based upon the notebook\r
- public void expungeNotesByNotebook(String notebookGuid, boolean permanentExpunge, boolean needsSync) {\r
- List<String> notes = getNotesByNotebook(notebookGuid);\r
- for (int i=0; i<notes.size(); i++) {\r
- expungeNote(notes.get(i), permanentExpunge, needsSync);\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.getResourceConnection());\r
- NSqlQuery tags = new NSqlQuery(db.getConnection());\r
- NSqlQuery words = new NSqlQuery(db.getIndexConnection());\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
-// System.out.println("Hiding Note: Deleting words");\r
-// if (!words.exec()) {\r
-// logger.log(logger.MEDIUM, "Word delete failed.");\r
-// logger.log(logger.MEDIUM, words.lastError());\r
-// }\r
- if (needsSync) {\r
- DeletedTable deletedTable = new DeletedTable(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
- List<String> guids = new ArrayList<String>();\r
- List<Integer> usns = new ArrayList<Integer>();\r
- while (query.next()) {\r
- guids.add(query.valueString(0));\r
- Integer usn = new Integer(query.valueString(1));\r
- usns.add(usn);\r
- }\r
- \r
- for (int i=0; i<guids.size(); i++) {\r
- Integer usn = usns.get(i);\r
- String guid = guids.get(i);\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
- NSqlQuery resQuery = new NSqlQuery(db.getResourceConnection());\r
- NSqlQuery wordQuery = new NSqlQuery(db.getIndexConnection());\r
- query.prepare("Update Note set guid=:newGuid, original_guid=:original_guid where guid=:oldGuid");\r
-\r
- query.bindValue(":original_guid", oldGuid);\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
- wordQuery.prepare("Update words set guid=:newGuid where guid=:oldGuid");\r
- wordQuery.bindValue(":newGuid", newGuid);\r
- wordQuery.bindValue(":oldGuid", oldGuid);\r
- wordQuery.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note guid update failed for Words.");\r
- logger.log(logger.MEDIUM, wordQuery.lastError());\r
- }\r
- resQuery.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");\r
- resQuery.bindValue(":newGuid", newGuid);\r
- resQuery.bindValue(":oldGuid", oldGuid);\r
- resQuery.exec();\r
- if (!check) {\r
- logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");\r
- logger.log(logger.MEDIUM, resQuery.lastError());\r
- }\r
- }\r
- // Update a note\r
- public void updateNote(Note n, boolean isNew) {\r
- int titleColor = getNoteTitleColor(n.getGuid());\r
- String originalGuid = findAlternateGuid(n.getGuid());\r
- expungeNote(n.getGuid(), true, false);\r
- addNote(n, false);\r
- if (titleColor != -1)\r
- setNoteTitleColor(n.getGuid(), titleColor);\r
- if (originalGuid != null) {\r
- updateNoteGuid(n.getGuid(), originalGuid);\r
- updateNoteGuid(originalGuid, n.getGuid());\r
- }\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 isExpunged = false and notebookGuid not in (select guid from notebook where local = true or linked = 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 List <Note> getDirtyLinkedNotes() {\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 isExpunged = false and notebookGuid in (select guid from notebook where linked = 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 List <Note> getDirtyLinked(String notebookGuid) {\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
- query.prepare("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid=:notebookGuid");\r
- query.bindValue(":notebookGuid", notebookGuid);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed getting dirty linked notes: " +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 List <String> getNotesByNotebook(String notebookGuid) {\r
- List<String> notes = new ArrayList<String>();\r
- List<String> index = new ArrayList<String>();\r
- \r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("Select guid from Note where notebookguid=:notebookguid");\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
- query.bindValue(":notebookguid", notebookGuid);\r
- query. exec();\r
- \r
- // Get a list of the notes\r
- while (query.next()) {\r
- index.add(query.valueString(0)); \r
- } \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
-\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
- if (getAllQueryWithoutContent == null) \r
- prepareQueries();\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(guid) 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.getResourceConnection());\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
- // Expunge notes that we don't want to synchronize\r
- public List<String> expungeIgnoreSynchronizedNotes(List<String> notebooks, List<String>tags, List<String> linked) {\r
- \r
- List<String> noteGuids = new ArrayList<String>();\r
- for (int i=0; i<notebooks.size(); i++) {\r
- List<String> notes = findNotesByNotebook(notebooks.get(i));\r
- for (int j=0; j<notes.size(); j++) {\r
- if (!isNoteDirty(notes.get(j))) {\r
- expungeNote(notes.get(j), true, false);\r
- noteGuids.add(notes.get(j));\r
- }\r
- }\r
- }\r
- \r
- for (int i=0; i<tags.size(); i++) {\r
- List<String> notes = findNotesByTag(tags.get(i));\r
- for (int j=0; j<notes.size(); j++) {\r
- if (!isNoteDirty(notes.get(j))) {\r
- expungeNote(notes.get(j), true, false);\r
- noteGuids.add(notes.get(j));\r
- }\r
- }\r
- }\r
- \r
- for (int i=0; i<linked.size(); i++) {\r
- String notebookGuid = db.getLinkedNotebookTable().getNotebookGuid(linked.get(i));\r
- if (notebookGuid != null && !notebookGuid.trim().equals("")) {\r
- List<Tag> linkedTags = db.getTagTable().getTagsForNotebook(notebookGuid);\r
- for (int j=0; j<linkedTags.size(); j++)\r
- db.getTagTable().expungeTag(linkedTags.get(j).getGuid(), false);\r
- \r
- List<String> notes = findNotesByNotebook(notebookGuid);\r
- for (int j=0; j<notes.size(); j++) {\r
- if (!isNoteDirty(notes.get(j))) {\r
- expungeNote(notes.get(j), true, false);\r
- noteGuids.add(notes.get(j));\r
- }\r
- }\r
- }\r
- }\r
- return noteGuids;\r
- }\r
- \r
- // Find a note by its notebook\r
- // Expunge notes that we don't want to synchronize\r
- public List<String> findNotesByNotebook(String notebook) {\r
- List<String> values = new ArrayList<String>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Select guid from note where notebookguid=:notebook");\r
-\r
- query.bindValue(":notebook", notebook);\r
- query.exec();\r
- while (query.next()) {\r
- values.add(query.valueString(0));\r
- }\r
- return values;\r
- }\r
- \r
- public List<String> findNotesByTag(String tag) {\r
- List<String> values = new ArrayList<String>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- query.prepare("Select distinct noteguid from notetags where tagguid=:tag");\r
-\r
- query.bindValue(":tag", tag);\r
- query.exec();\r
- while (query.next()) {\r
- values.add(query.valueString(0));\r
- }\r
- return values;\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
- List<Resource> r = noteResourceTable.getNoteResources(guid, false);\r
- for (int i=0; r!= null && i<r.size(); i++) {\r
- noteResourceTable.setIndexNeeded(r.get(i).getGuid(), true);\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
- return returnValue;\r
- }\r
- */\r
- \r
- // Get note meta information\r
- public void updateNoteMetadata(NoteMetadata meta) {\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- if (!query.prepare("Update Note set titleColor=:color, pinned=:pinned where guid=:guid"))\r
- logger.log(logger.EXTREME, "Note SQL prepare has failed on updateNoteMetadata.");\r
- query.bindValue(":color", meta.getColor());\r
- query.bindValue(":pinned", meta.isPinned());\r
- query.bindValue(":guid", meta.getGuid());\r
- query.exec();\r
- return;\r
- }\r
- \r
- // Get note meta information\r
- public HashMap<String, NoteMetadata> getNoteMetaInformation() {\r
- HashMap<String, NoteMetadata> returnValue = new HashMap<String, NoteMetadata>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- if (!query.exec("Select guid,titleColor, isDirty, pinned from Note"))\r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteMetaInformation.");\r
-\r
- // Get a list of the notes\r
- while (query.next()) {\r
- NoteMetadata note = new NoteMetadata();\r
- note.setGuid(query.valueString(0));\r
- note.setColor(query.valueInteger(1));\r
- note.setDirty(query.valueBoolean(2, false));\r
- int pinned = query.valueInteger(3);\r
- if (pinned > 0) \r
- note.setPinned(true);\r
- returnValue.put(note.getGuid(), note); \r
- } \r
-\r
- return returnValue;\r
- }\r
- // Set a title color\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
- // Get in individual note's title color\r
- // Get the title color of all notes\r
- public Integer getNoteTitleColor(String guid) {\r
- List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- query.prepare("Select titleColor from Note where titleColor != -1 and guid=:guid");\r
- query.bindValue(":guid", guid);\r
- if (!query.exec())\r
- logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteTitleColor(guid).");\r
-\r
- Integer color = -1;\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
- return color;\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
- try {\r
- if (query.getBlob(0) != null) {\r
- return new QByteArray(query.getBlob(0)); \r
- }\r
- } catch (java.lang.IllegalArgumentException e) {\r
- return null;\r
- }\r
- }\r
- return null;\r
- }\r
- // Get all thumbnails\r
- public HashMap<String, QPixmap> getThumbnails() {\r
- boolean check; \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- HashMap<String, QPixmap> map = new HashMap<String,QPixmap>();\r
- \r
- check = query.prepare("Select guid,thumbnail from note where thumbnailneeded=false and isExpunged=false");\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
- while (query.next()) {\r
- try {\r
- if (query.getBlob(1) != null) {\r
- QByteArray data = new QByteArray(query.getBlob(1));\r
- QPixmap img = new QPixmap();\r
- if (img.loadFromData(data)) {\r
- img = img.scaled(Global.largeThumbnailSize);\r
- map.put(query.valueString(0), img);\r
- }\r
- } \r
- } catch (java.lang.IllegalArgumentException e) {\r
- logger.log(logger.HIGH, "Error retrieving thumbnail " +e.getMessage());\r
- }\r
- }\r
- return map;\r
- }\r
- // Get a list of notes that need thumbnails\r
- public List<String> findThumbnailsNeeded() {\r
- \r
- boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("select guid from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 5");\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL findThumbnailsNeeded query failed: " +query.lastError().toString());\r
- \r
-\r
- // Get a list of the notes\r
- List<String> values = new ArrayList<String>();\r
- while (query.next()) {\r
- values.add(query.valueString(0)); \r
- }\r
-\r
- return values; \r
- }\r
- // Get a count of thumbnails needed\r
- public int getThumbnailNeededCount() {\r
- \r
- boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("select count(guid) from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 2");\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL findThumbnailNeededCount query failed: " +query.lastError().toString());\r
- \r
- if (query.next()) {\r
- return query.valueInteger(0); \r
- }\r
-\r
- return 0; \r
- }\r
-\r
- //***********************************************************************************\r
- public String findAlternateGuid(String guid) {\r
- boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("select guid from note where original_guid=:guid");\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL findAlternateguid query failed: " +query.lastError().toString());\r
- \r
- if (query.next()) {\r
- return query.valueString(0); \r
- }\r
-\r
- return null; \r
- }\r
- \r
- //* Check if a note guid exists\r
- public boolean guidExists(String guid) {\r
- boolean check;\r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- \r
- check = query.prepare("select guid from note where guid=:guid");\r
- query.bindValue(":guid", guid);\r
- check = query.exec();\r
- if (!check) \r
- logger.log(logger.EXTREME, "Note SQL guidExists query failed: " +query.lastError().toString());\r
- \r
- if (query.next()) {\r
- return true; \r
- }\r
-\r
- return false; \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, thumbnailneeded=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
-} \r
-\r
-\r
-\r
-\r
+/*
+ * This file is part of NixNote/NeighborNote
+ * Copyright 2009 Randy Baumgarte
+ * Copyright 2013 Yuki Takahashi
+ *
+ * This file may be licensed under the terms of of the
+ * GNU General Public License Version 2 (the ``GPL'').
+ *
+ * Software distributed under the License is distributed
+ * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
+ * express or implied. See the GPL for the specific language
+ * governing rights and limitations.
+ *
+ * You should have received a copy of the GPL along with this
+ * program. If not, go to http://www.gnu.org/licenses/gpl.html
+ * or write to the Free Software Foundation, Inc.,
+ * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
+ *
+*/
+
+
+package cx.fbn.nevernote.sql;
+
+import java.text.DateFormat;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+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;
+import com.evernote.edam.type.Tag;
+import com.trolltech.qt.core.QByteArray;
+import com.trolltech.qt.core.QDateTime;
+import com.trolltech.qt.core.QTextCodec;
+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;
+
+public class NoteTable {
+ private final ApplicationLogger logger;
+ public final NoteTagsTable noteTagsTable;
+ public NoteResourceTable noteResourceTable;
+ private final DatabaseConnection db;
+ int id;
+
+ // Prepared Queries to improve speed
+ private NSqlQuery getQueryWithContent;
+ private NSqlQuery getQueryWithoutContent;
+ private NSqlQuery getAllQueryWithoutContent;
+
+ // Constructor
+ public NoteTable(ApplicationLogger l, DatabaseConnection d) {
+ logger = l;
+ db = d;
+ id = 0;
+ noteResourceTable = new NoteResourceTable(logger, db);
+ 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());
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ logger.log(logger.HIGH, "Creating table Note...");
+ if (!query.exec("Create table Note (guid varchar primary key, " +
+ "updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+
+ "contentLength integer, created timestamp, updated timestamp, deleted timestamp, "
+ +"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+
+ "attributeLatitude double, attributeLongitude double, attributeAltitude double,"+
+ "attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+
+ "attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +
+ "isDirty boolean)"))
+ logger.log(logger.HIGH, "Table Note creation FAILED!!!");
+ if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))
+ logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");
+ 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();
+ }
+ // Drop the table
+ public void dropTable() {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.exec("Drop table Note");
+ noteTagsTable.dropTable();
+ noteResourceTable.dropTable();
+ }
+ // Save Note List from Evernote
+ public void addNote(Note n, boolean isDirty) {
+ logger.log(logger.EXTREME, "Inside addNote");
+ if (n == null)
+ return;
+
+ SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Insert Into Note ("
+ +"guid, updateSequenceNumber, title, content, "
+ +"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "
+ +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "
+ +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "
+ +"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded, contentText"
+ +") Values("
+ +":guid, :updateSequenceNumber, :title, :content, "
+ +":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "
+ +":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "
+ +":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "
+ +":indexNeeded, :isExpunged, :isDirty, -1, true, :contentText) ");
+
+ StringBuilder created = new StringBuilder(simple.format(n.getCreated()));
+ StringBuilder updated = new StringBuilder(simple.format(n.getUpdated()));
+ StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));
+
+
+
+ query.bindValue(":guid", n.getGuid());
+ query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());
+ query.bindValue(":title", n.getTitle());
+ if (isDirty) {
+ EnmlConverter enml = new EnmlConverter(logger);
+ String contentText = Global.extractPlainText(enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));
+
+ query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));
+ query.bindValue(":contentText", contentText);
+ }
+ else {
+ String contentText = Global.extractPlainText(n.getContent());
+
+ query.bindValue(":content", n.getContent());
+ query.bindValue(":contentText", contentText);
+
+ }
+ query.bindValue(":contentHash", n.getContentHash());
+ query.bindValue(":contentLength", n.getContentLength());
+ query.bindValue(":created", created.toString());
+ query.bindValue(":updated", updated.toString());
+ query.bindValue(":deleted", deleted.toString());
+ query.bindValue(":active", n.isActive());
+ query.bindValue(":notebookGuid", n.getNotebookGuid());
+
+ if (n.getAttributes() != null) {
+ created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));
+ query.bindValue(":attributeSubjectDate", created.toString());
+ query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());
+ query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());
+ query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());
+ query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());
+ 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);
+ query.bindValue(":isDirty", isDirty);
+
+
+ if (!query.exec())
+ logger.log(logger.MEDIUM, query.lastError());
+
+ // Save the note tags
+ if (n.getTagGuids() != null) {
+ for (int i=0; i<n.getTagGuids().size(); i++)
+ noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i), isDirty);\r
+ }
+ logger.log(logger.EXTREME, "Leaving addNote");
+ }
+ // Setup queries for get to save time later
+ private void prepareQueries() {
+ 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 == 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 == 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, "
+ +"attributeContentClass "
+ +" from Note where isExpunged = false")) {
+ logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");
+ logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());
+ }
+ }
+ }
+
+
+ // Get a note's content in blob format for index.
+ public String getNoteContentNoUTFConversion(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 by Guid
+ public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {
+
+ // ICHANGED 自分のキーに変更
+// extractMetadata("otherKey:{values};kimaira792:{titleColor=fff;pinned=true;};finalKey:{values1);");
+ if (noteGuid == null)
+ return null;
+ if (noteGuid.trim().equals(""))
+ return null;
+
+ prepareQueries();
+ NSqlQuery query;
+ if (loadContent) {
+ query = getQueryWithContent;
+ } else {
+ query = getQueryWithoutContent;
+ }
+
+ query.bindValue(":guid", noteGuid);
+ if (!query.exec()) {
+ logger.log(logger.EXTREME, "Note SQL select exec has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ return null;
+ }
+ if (!query.next()) {
+ logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");
+ logger.log(logger.EXTREME, " -> " +query.lastError().toString());
+ logger.log(logger.EXTREME, " -> " +query.lastError());
+ return null;
+ }
+ 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
+ public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {
+ 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);
+
+ n.setGuid(query.valueString(0));
+ n.setUpdateSequenceNum(new Integer(query.valueString(1)));
+ n.setTitle(query.valueString(2));
+
+ try {
+ n.setCreated(indfm.parse(query.valueString(3)).getTime());
+ n.setUpdated(indfm.parse(query.valueString(4)).getTime());
+ n.setDeleted(indfm.parse(query.valueString(5)).getTime());
+ } catch (ParseException e) {
+ e.printStackTrace();
+ }
+
+ n.setActive(query.valueBoolean(6,true));
+ n.setNotebookGuid(query.valueString(7));
+
+ try {
+ String attributeSubjectDate = query.valueString(8);
+ if (!attributeSubjectDate.equals(""))
+ na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());
+ } catch (ParseException e) {
+ e.printStackTrace();
+ }
+ na.setLatitude(new Float(query.valueString(9)));
+ na.setLongitude(new Float(query.valueString(10)));
+ na.setAltitude(new Float(query.valueString(11)));
+ na.setAuthor(query.valueString(12));
+ na.setSource(query.valueString(13));
+ na.setSourceURL(query.valueString(14));
+ na.setSourceApplication(query.valueString(15));
+ na.setContentClass(query.valueString(16));
+
+ if (loadTags) {
+ List<String> tagGuids = noteTagsTable.getNoteTags(n.getGuid());
+ List<String> tagNames = new ArrayList<String>();
+ TagTable tagTable = db.getTagTable();
+ for (int i=0; i<tagGuids.size(); i++) {
+ String currentGuid = tagGuids.get(i);
+ Tag tag = tagTable.getTag(currentGuid);
+ if (tag.getName() != null)
+ tagNames.add(tag.getName());
+ else
+ tagNames.add("");
+ }
+
+ n.setTagNames(tagNames);
+ n.setTagGuids(tagGuids);
+ }
+
+ if (loadContent) {
+ QTextCodec codec = QTextCodec.codecForLocale();
+ codec = QTextCodec.codecForName("UTF-8");
+ String unicode = codec.fromUnicode(query.valueString(17)).toString();
+
+ // This is a hack. Basically I need to convert HTML Entities to "normal" text, but if I
+ // convert the < character to < it will mess up the XML parsing. So, to get around this
+ // I am "bit stuffing" the < to &< so StringEscapeUtils doesn't unescape it. After
+ // I'm done I convert it back.
+ StringBuffer buffer = new StringBuffer(unicode);
+ if (Global.enableHTMLEntitiesFix && unicode.indexOf("&#") > 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<j) {
+ String entity = buffer.substring(i,j).toString();
+ int len = entity.length()-1;
+ String tempEntity = entity.substring(2, len);
+ try {
+ Integer.parseInt(tempEntity);
+ entity = codec.fromUnicode(StringEscapeUtils.unescapeHtml4(entity)).toString();
+ buffer.delete(i, j);
+ buffer.insert(i, entity);
+ } catch (Exception e){ }
+
+ }
+ }
+ }
+
+ n.setContent(unicode);
+// n.setContent(query.valueString(16).toString());
+
+ String contentHash = query.valueString(18);
+ if (contentHash != null)
+ n.setContentHash(contentHash.getBytes());
+ n.setContentLength(new Integer(query.valueString(19)));
+ }
+ if (loadResources)
+ n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));
+ if (loadRecognition) {
+ if (n.getResources() == null) {
+ List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());
+ n.setResources(resources);
+ } else {
+ // We need to merge the recognition resources with the note resources retrieved earlier
+ for (int i=0; i<n.getResources().size(); i++) {
+ Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());
+ n.getResources().get(i).setRecognition(r.getRecognition());
+ }
+ }
+ }
+ n.setContent(fixCarriageReturn(n.getContent()));
+ return n;
+ }
+ // Update a note's title
+ public void updateNoteTitle(String guid, String title) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteTitle");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note title sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ query.bindValue(":title", title);
+ query.bindValue(":guid", guid);
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note title has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteTitle");
+ }
+ // Update a note's creation date
+ public void updateNoteCreatedDate(String guid, QDateTime date) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteCreatedDate");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note creation date has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteCreatedDate");
+ }
+ // Update a note's creation date
+ public void updateNoteAlteredDate(String guid, QDateTime date) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteAlteredDate");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note altered date has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteAlteredDate");
+ }
+ // Update a note's creation date
+ public void updateNoteSubjectDate(String guid, QDateTime date) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteSubjectDate");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note subject date date has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSubjectDate");
+ }
+ // Update a note's creation date
+ public void updateNoteAuthor(String guid, String author) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteSubject");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note author sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.bindValue(":author", author);
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note author has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSubject");
+ }
+ // Update a note's geo tags
+ public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteGeoTags");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+
+ "attributeLatitude=:latitude, attributeAltitude=:altitude, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note author sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.bindValue(":longitude", lon);
+ query.bindValue(":latitude", lat);
+ query.bindValue(":altitude", alt);
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note geo tag has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteGeoTags");
+
+ }
+ // Update a note's creation date
+ public void updateNoteSourceUrl(String guid, String url) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteSourceUrl");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note url sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.bindValue(":url", url);
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note url has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSourceUrl");
+ }
+ // Update the notebook that a note is assigned to
+ public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteNotebook");
+ String currentNotebookGuid = new String("");
+
+
+ // If we are going from a synchronized notebook to a local notebook, we
+ // need to tell Evernote to purge the note online. However, if this is
+ // conflicting change we move it to the local notebook without deleting it
+ // or it would then delete the copy on the remote server.
+ NotebookTable notebookTable = new NotebookTable(logger, db);
+ DeletedTable deletedTable = new DeletedTable(logger, db);
+ if (expungeFromRemote) {
+ if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {
+ deletedTable.addDeletedItem(guid, "NOTE");
+ }
+ }
+
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ query.bindValue(":notebook", notebookGuid);
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note notebook has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteNotebook");
+ }
+ // Update a note's title
+ public void updateNoteContent(String guid, String content) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteContent");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set content=:content, contentText=:contentText, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true, " +
+ " thumbnailneeded=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note content sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+// QTextCodec codec = QTextCodec.codecForLocale();
+// codec = QTextCodec.codecForName("UTF-8");
+// query.bindValue(":content", codec.fromUnicode(content).toString());
+ query.bindValue(":content", content);
+ query.bindValue(":contentText", Global.extractPlainText(content));
+ query.bindValue(":guid", guid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note content has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteContent");
+ }
+
+ // Delete a note
+ public void deleteNote(String guid) {
+ logger.log(logger.HIGH, "Entering NoteTable.deleteNote");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");
+ query.bindValue(":guid", guid);
+ if (!query.exec()) {
+ logger.log(logger.MEDIUM, "Note delete failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.deleteNote");
+ }
+ public void restoreNote(String guid) {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Update Note set deleted=:reset, active=true, isDirty=true where guid=:guid");
+// query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");
+ query.bindValue(":guid", guid);
+ query.bindValue(":reset", "1969-12-31 19:00:00");
+ if (!query.exec()) {
+ logger.log(logger.MEDIUM, "Note restore failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ }
+ // Purge a note (actually delete it instead of just marking it deleted)
+ public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {
+ logger.log(logger.HIGH, "Entering NoteTable.expungeNote");
+
+ if (!permanentExpunge) {
+ hideExpungedNote(guid, needsSync);
+ return;
+ }
+
+
+ NSqlQuery note = new NSqlQuery(db.getConnection());
+ NSqlQuery resources = new NSqlQuery(db.getResourceConnection());
+ NSqlQuery tags = new NSqlQuery(db.getConnection());
+ NSqlQuery words = new NSqlQuery(db.getIndexConnection());
+
+ note.prepare("Delete from Note where guid=:guid");
+ resources.prepare("Delete from NoteResources where noteGuid=:guid");
+ tags.prepare("Delete from NoteTags where noteGuid=:guid");
+ words.prepare("Delete from words where guid=:guid");
+
+ note.bindValue(":guid", guid);
+ resources.bindValue(":guid", guid);
+ tags.bindValue(":guid", guid);
+ words.bindValue(":guid", guid);
+
+ // Start purging notes.
+ if (!note.exec()) {
+ logger.log(logger.MEDIUM, "Purge from note failed.");
+ logger.log(logger.MEDIUM, note.lastError());
+ }
+ if (!resources.exec()) {
+ logger.log(logger.MEDIUM, "Purge from resources failed.");
+ logger.log(logger.MEDIUM, resources.lastError());
+ }
+ if (!tags.exec()) {
+ logger.log(logger.MEDIUM, "Note tags delete failed.");
+ logger.log(logger.MEDIUM, tags.lastError());
+ }
+
+ if (!words.exec()) {
+ logger.log(logger.MEDIUM, "Word delete failed.");
+ logger.log(logger.MEDIUM, words.lastError());
+ }
+ if (needsSync) {
+ DeletedTable deletedTable = new DeletedTable(logger, db);
+ deletedTable.addDeletedItem(guid, "Note");
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.expungeNote");
+ }
+ // Purge a bunch of notes based upon the notebook
+ public void expungeNotesByNotebook(String notebookGuid, boolean permanentExpunge, boolean needsSync) {
+ List<String> notes = getNotesByNotebook(notebookGuid);
+ for (int i=0; i<notes.size(); i++) {
+ expungeNote(notes.get(i), permanentExpunge, needsSync);
+ }
+ }
+
+ // Purge a note (actually delete it instead of just marking it deleted)
+ public void hideExpungedNote(String guid, boolean needsSync) {
+ NSqlQuery note = new NSqlQuery(db.getConnection());
+ NSqlQuery resources = new NSqlQuery(db.getResourceConnection());
+ NSqlQuery tags = new NSqlQuery(db.getConnection());
+ NSqlQuery words = new NSqlQuery(db.getIndexConnection());
+
+ note.prepare("Update Note set isExpunged=true where guid=:guid");
+ resources.prepare("Delete from NoteResources where noteGuid=:guid");
+ tags.prepare("Delete from NoteTags where noteGuid=:guid");
+// words.prepare("Delete from words where guid=:guid");
+
+ note.bindValue(":guid", guid);
+ resources.bindValue(":guid", guid);
+ tags.bindValue(":guid", guid);
+ words.bindValue(":guid", guid);
+
+ // Start purging notes.
+ if (!note.exec()) {
+ logger.log(logger.MEDIUM, "Purge from note failed.");
+ logger.log(logger.MEDIUM, note.lastError());
+ }
+ if (!resources.exec()) {
+ logger.log(logger.MEDIUM, "Purge from resources failed.");
+ logger.log(logger.MEDIUM, resources.lastError());
+ }
+ if (!tags.exec()) {
+ logger.log(logger.MEDIUM, "Note tags delete failed.");
+ logger.log(logger.MEDIUM, tags.lastError());
+ }
+// System.out.println("Hiding Note: Deleting words");
+// if (!words.exec()) {
+// logger.log(logger.MEDIUM, "Word delete failed.");
+// logger.log(logger.MEDIUM, words.lastError());
+// }
+ if (needsSync) {
+ DeletedTable deletedTable = new DeletedTable(logger, db);
+ deletedTable.addDeletedItem(guid, "Note");
+ }
+ }
+
+
+ // Purge all deleted notes;
+ public void expungeAllDeletedNotes() {
+ logger.log(logger.HIGH, "Entering NoteTable.expungeAllDeletedNotes");
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.exec("select guid, updateSequenceNumber from note where active = false");
+ List<String> guids = new ArrayList<String>();
+ List<Integer> usns = new ArrayList<Integer>();
+ while (query.next()) {
+ guids.add(query.valueString(0));
+ Integer usn = new Integer(query.valueString(1));
+ usns.add(usn);
+ }
+
+ for (int i=0; i<guids.size(); i++) {
+ Integer usn = usns.get(i);
+ String guid = guids.get(i);
+ if (usn == 0)
+ expungeNote(guid, true, false);
+ else
+ expungeNote(guid, false, true);
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.expungeAllDeletedNotes");
+ }
+ // Update the note sequence number
+ public void updateNoteSequence(String guid, int sequence) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteSequence");
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");
+
+ query.bindValue(":sequence", sequence);
+ query.bindValue(":guid", guid);
+
+ query.exec();
+ if (!check) {
+ logger.log(logger.MEDIUM, "Note sequence update failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNoteSequence");
+ }
+ // Update the note Guid
+ public void updateNoteGuid(String oldGuid, String newGuid) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNoteGuid");
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ NSqlQuery resQuery = new NSqlQuery(db.getResourceConnection());
+ NSqlQuery wordQuery = new NSqlQuery(db.getIndexConnection());
+
+ query.prepare("Update Note set guid=:newGuid, original_guid=:original_guid where guid=:oldGuid");
+
+ query.bindValue(":original_guid", oldGuid);
+ query.bindValue(":newGuid", newGuid);
+ query.bindValue(":oldGuid", oldGuid);
+
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.MEDIUM, "Note Guid update failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");
+ query.bindValue(":newGuid", newGuid);
+ query.bindValue(":oldGuid", oldGuid);
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+
+ wordQuery.prepare("Update words set guid=:newGuid where guid=:oldGuid");
+ wordQuery.bindValue(":newGuid", newGuid);
+ wordQuery.bindValue(":oldGuid", oldGuid);
+ // IFIXED check = が抜けていたので修正
+ check = wordQuery.exec();
+ if (!check) {
+ logger.log(logger.MEDIUM, "Note guid update failed for Words.");
+ logger.log(logger.MEDIUM, wordQuery.lastError());
+ }
+ resQuery.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");
+ resQuery.bindValue(":newGuid", newGuid);
+ resQuery.bindValue(":oldGuid", oldGuid);
+ // IFIXED check = が抜けていたので修正
+ check = resQuery.exec();
+ if (!check) {
+ logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");
+ logger.log(logger.MEDIUM, resQuery.lastError());
+ }
+
+ // ICHANGED 操作履歴テーブルのguidを更新
+ db.getHistoryTable().updateHistoryGuid(newGuid, oldGuid);
+
+ // ICHANGED 除外ノートテーブルのguidを更新
+ db.getExcludedTable().updateExcludedNoteGuid(newGuid, oldGuid);
+
+ // ICHANGED スター付きノートテーブルのguidを更新
+ db.getStaredTable().updateStaredNoteGuid(newGuid, oldGuid);
+ }
+ // Update a note
+ public void updateNote(Note n) {
+ logger.log(logger.HIGH, "Entering NoteTable.updateNote");
+ NoteMetadata meta = getNoteMetaInformation(n.getGuid());
+ String originalGuid = findAlternateGuid(n.getGuid());
+ expungeNote(n.getGuid(), true, false);
+ addNote(n, false);
+ if (n!=null) {
+ updateNoteMetadata(meta);
+ }
+ if (originalGuid != null) {
+ updateNoteGuid(n.getGuid(), originalGuid);
+ updateNoteGuid(originalGuid, n.getGuid());
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.updateNote");
+ }
+ // Does a note exist?
+ public boolean exists(String guid) {
+ if (guid == null)
+ return false;
+ if (guid.trim().equals(""))
+ return false;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Select guid from note where guid=:guid");
+ query.bindValue(":guid", guid);
+ if (!query.exec())
+ logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");
+ boolean retVal = query.next();
+ return retVal;
+ }
+ // Does a note exist?
+ public boolean isNoteExpunged(String guid) {
+ if (guid == null)
+ return false;
+ if (guid.trim().equals(""))
+ return false;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");
+ query.bindValue(":guid", guid);
+ if (!query.exec())
+ logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");
+ boolean retVal = query.next();
+ return retVal;
+ }
+ // This is a convenience method to check if a note exists & update/create based upon it
+ public void syncNote(Note note) {
+ logger.log(logger.HIGH, "Entering NoteTable.syncNote");
+ // If we got the note from Evernote we use its
+ // metadata instead of the local copy.
+ NoteMetadata meta = null;
+ if (note.getAttributes() != null && note.getAttributes().getSourceApplication() != null) {
+ meta = extractMetadata(note.getAttributes().getSourceApplication());
+ } else
+ meta = getNoteMetaInformation(note.getGuid());
+
+ // Now, if the note exists we simply update it. Otherwise we
+ // add a new note.
+ if (exists(note.getGuid())) {
+ updateNote(note);
+ }
+ else
+ addNote(note, false);
+
+ // If we have metadata, we write it out.
+ if (meta != null) {
+ meta.setGuid(note.getGuid());
+ updateNoteMetadata(meta);
+ }
+ logger.log(logger.HIGH, "Leaving NoteTable.syncNote");
+ }
+ // Get a list of notes that need to be updated
+ public List <Note> getDirty() {
+ String guid;
+ Note tempNote;
+ List<Note> notes = new ArrayList<Note>();
+ List<String> index = new ArrayList<String>();
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true or 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<index.size(); i++) {
+ 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 <Note> getDirtyLinkedNotes() {
+ String guid;
+ Note tempNote;
+ List<Note> notes = new ArrayList<Note>();
+ List<String> index = new ArrayList<String>();
+
+ 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<index.size(); i++) {
+ tempNote = getNote(index.get(i), true,true,false,true,true);
+ notes.add(tempNote);
+ }
+ logger.log(logger.LOW, "Dirty linked local notes: " +new Integer(notes.size()).toString());
+ return notes;
+ }
+ // Get a list of notes that need to be updated
+ public List <Note> getDirtyLinked(String notebookGuid) {
+ String guid;
+ Note tempNote;
+ List<Note> notes = new ArrayList<Note>();
+ List<String> index = new ArrayList<String>();
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ query.prepare("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid=:notebookGuid");
+ query.bindValue(":notebookGuid", notebookGuid);
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL retrieve has failed getting dirty linked notes: " +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<index.size(); i++) {
+ tempNote = getNote(index.get(i), true,true,false,true,true);
+ notes.add(tempNote);
+ }
+ logger.log(logger.LOW, "Dirty local notes for notebook " +notebookGuid +": " +new Integer(notes.size()).toString());
+ return notes;
+ }
+ // Get a list of notes that need to be updated
+ // IFIXED バグで空のリストを返すだけのメソッドになっていたのを修正
+ public List <String> getNotesByNotebook(String notebookGuid) {
+ List<String> notes = new ArrayList<String>();
+ // IFIXED List<String> index = new ArrayList<String>();
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("Select guid from Note where notebookguid=:notebookguid");
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
+ query.bindValue(":notebookguid", notebookGuid);
+ query. exec();
+
+ // Get a list of the notes
+ while (query.next()) {
+ // IFIXED index.add(query.valueString(0));
+ notes.add(query.valueString(0));
+ }
+
+ return notes;
+ }
+ // Get a list of notes that need to be updated
+ public boolean isNoteDirty(String guid) {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");
+ query.bindValue(":guid", guid);
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());
+
+ boolean returnValue;
+ // Get a list of the notes
+ if (query.next())
+ returnValue = true;
+ else
+ returnValue = false;
+
+ return returnValue;
+ }
+
+ // 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");
+ query.bindValue(":guid", guid);
+ if (!query.exec())
+ logger.log(logger.EXTREME, "Error resetting note dirty field.");
+ }
+ // Get all notes
+ public List<String> getAllGuids() {
+ List<String> notes = new ArrayList<String>();
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.exec("Select guid from Note");
+ if (!check)
+ logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());
+
+ // Get a list of the notes
+ while (query.next()) {
+ notes.add(new String(query.valueString(0)));
+ }
+ return notes;
+ }
+ // Get all notes
+ public List<Note> getAllNotes() {
+ List<Note> notes = new ArrayList<Note>();
+ prepareQueries();
+ boolean check;
+ if (getAllQueryWithoutContent == null)
+ prepareQueries();
+ NSqlQuery query = getAllQueryWithoutContent;
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());
+ // Get a list of the notes
+ while (query.next()) {
+ notes.add(mapNoteFromQuery(query, false, false, false, false, true));
+ }
+ return notes;
+ }
+ // Count unindexed notes
+ public int getUnindexedCount() {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.exec("select count(*) from note where indexneeded=true and isExpunged = false");
+ query.next();
+ int returnValue = new Integer(query.valueString(0));
+ return returnValue;
+ }
+ // Count unsynchronized notes
+ public int getDirtyCount() {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ 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<Note> recs = getDirty();
+ //for (int i=0; i<recs.size(); i++) {
+ //Note n = getNote(recs.get(i).getGuid(), true, true, true, false, true);
+ //logger.log(logger.LOW, "-- Begin Record ---");
+ //logger.log(logger.LOW, "Guid: " +n.getGuid());
+ //logger.log(logger.LOW, "Title: " +n.getTitle());
+ //logger.log(logger.LOW, "Active: " +n.isActive());
+ //logger.log(logger.LOW, "USN: " +n.getUpdateSequenceNum());
+ //logger.log(logger.LOW, "Date Created: " +n.getCreated());
+ //logger.log(logger.LOW, "Date Updated: " +n.getUpdated());
+ //logger.log(logger.LOW, "Date Deleted: " +n.getDeleted());
+ //logger.log(logger.LOW, "Resource Count: " +n.getResourcesSize());
+ //for (int j=0; j<n.getResourcesSize(); j++) {
+ //Resource r = n.getResources().get(j);
+ //logger.log(logger.LOW, "Resource " +j +": " +r.getGuid());
+ //logger.log(logger.LOW, "Active: " +r.isActive());
+ //logger.log(logger.LOW, "USN: " +r.getUpdateSequenceNum());
+ //}
+ //logger.log(logger.LOW, "-- End Record ---");
+ //}
+ //logger.log(logger.LOW, "*****************************");
+ //logger.log(logger.LOW, "*** End DIRTY RECORD DUMP ***");
+ //logger.log(logger.LOW, "*****************************");
+ return returnValue;
+ }
+ // Count notes
+ public int getNoteCount() {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.exec("select count(*) from note where isExpunged = false");
+ query.next();
+ int returnValue = new Integer(query.valueString(0));
+ return returnValue;
+ }
+ // Count deleted notes
+ public int getDeletedCount() {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.exec("select count(*) from note where isExpunged = false and active = false");
+ if (!query.next())
+ return 0;
+ int returnValue = new Integer(query.valueString(0));
+ return returnValue;
+ }
+ // Reset a note sequence number to zero. This is useful for moving conflicting notes
+ public void resetNoteSequence(String guid) {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ query.bindValue(":guid", guid);
+ check = query.exec();
+ if (!check) {
+ logger.log(logger.EXTREME, "Update note sequence number has failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ }
+
+
+ // Update a note resource by the hash
+ public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {
+ NSqlQuery query = new NSqlQuery(db.getResourceConnection());
+/* query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");
+ query.bindValue(":noteGuid", noteGuid);
+ query.bindValue(":hex", hash);
+ query.exec();
+ if (!query.next()) {
+ logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash. GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);
+ return;
+ }
+ String guid = query.valueString(0);
+*/
+ query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");
+ query.bindValue(":guid", resGuid);
+ query.bindValue(":noteGuid", noteGuid);
+ query.bindValue(":hex", hash);
+ if (!query.exec()) {
+ logger.log(logger.EXTREME, "Note Resource Update by Hash failed");
+ logger.log(logger.EXTREME, query.lastError().toString());
+ }
+ }
+
+ // Fix CRLF problem that is on some notes
+ private String fixCarriageReturn(String note) {
+ if (note == null || !Global.enableCarriageReturnFix)
+ return note;
+ QByteArray a0Hex = new QByteArray("a0");
+ String a0 = QByteArray.fromHex(a0Hex).toString();
+ note = note.replace("<div>"+a0+"</div>", "<div> </div>");
+ return note.replace("<div/>", "<div> </div>");
+ }
+
+ // Expunge notes that we don't want to synchronize
+ public List<String> expungeIgnoreSynchronizedNotes(List<String> notebooks, List<String>tags, List<String> linked) {
+
+ List<String> noteGuids = new ArrayList<String>();
+ for (int i=0; i<notebooks.size(); i++) {
+ List<String> notes = findNotesByNotebook(notebooks.get(i));
+ for (int j=0; j<notes.size(); j++) {
+ if (!isNoteDirty(notes.get(j))) {
+ expungeNote(notes.get(j), true, false);
+ noteGuids.add(notes.get(j));
+ }
+ }
+ }
+
+ for (int i=0; i<tags.size(); i++) {
+ List<String> notes = findNotesByTag(tags.get(i));
+ for (int j=0; j<notes.size(); j++) {
+ if (!isNoteDirty(notes.get(j))) {
+ expungeNote(notes.get(j), true, false);
+ noteGuids.add(notes.get(j));
+ }
+ }
+ }
+
+ for (int i=0; i<linked.size(); i++) {
+ String notebookGuid = db.getLinkedNotebookTable().getNotebookGuid(linked.get(i));
+ if (notebookGuid != null && !notebookGuid.trim().equals("")) {
+ List<Tag> linkedTags = db.getTagTable().getTagsForNotebook(notebookGuid);
+ for (int j=0; j<linkedTags.size(); j++)
+ db.getTagTable().expungeTag(linkedTags.get(j).getGuid(), false);
+
+ List<String> notes = findNotesByNotebook(notebookGuid);
+ for (int j=0; j<notes.size(); j++) {
+ if (!isNoteDirty(notes.get(j))) {
+ expungeNote(notes.get(j), true, false);
+ noteGuids.add(notes.get(j));
+ }
+ }
+ }
+ }
+ return noteGuids;
+ }
+
+ // Find a note by its notebook
+ // Expunge notes that we don't want to synchronize
+ public List<String> findNotesByNotebook(String notebook) {
+ List<String> values = new ArrayList<String>();
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Select guid from note where notebookguid=:notebook");
+
+ query.bindValue(":notebook", notebook);
+ query.exec();
+ while (query.next()) {
+ values.add(query.valueString(0));
+ }
+ return values;
+ }
+
+ public List<String> findNotesByTag(String tag) {
+ List<String> values = new ArrayList<String>();
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Select distinct noteguid from notetags where tagguid=:tag");
+
+ query.bindValue(":tag", tag);
+ query.exec();
+ while (query.next()) {
+ values.add(query.valueString(0));
+ }
+ return values;
+ }
+
+ // Find a note based upon its title.
+ public List<Pair<String,String>> findNotesByTitle(String text) {
+ List<Pair<String,String>> results = new ArrayList<Pair<String,String>>();
+ 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<String,String> p = new Pair<String,String>();
+ p.setFirst(query.valueString(0));
+ p.setSecond(query.valueString(1));
+ results.add(p);
+ }
+ return results;
+ }
+
+
+
+ //********************************************************************************
+ //********************************************************************************
+ //* Indexing Functions
+ //********************************************************************************
+ //********************************************************************************
+ // set/unset a note to be reindexed
+ public void setIndexNeeded(String guid, Boolean flag) {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Update Note set indexNeeded=:flag where guid=:guid");
+
+ if (flag)
+ query.bindValue(":flag", 1);
+ else
+ query.bindValue(":flag", 0);
+ query.bindValue(":guid", guid);
+ if (!query.exec()) {
+ logger.log(logger.MEDIUM, "Note indexNeeded update failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ List<Resource> r = noteResourceTable.getNoteResources(guid, false);
+ for (int i=0; r!= null && i<r.size(); i++) {
+ noteResourceTable.setIndexNeeded(r.get(i).getGuid(), true);
+ }
+ }
+ // Set all notes to be reindexed
+ public void reindexAllNotes() {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ if (!query.exec("Update Note set indexNeeded=true")) {
+ logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");
+ logger.log(logger.MEDIUM, query.lastError());
+ }
+ }
+
+ // Get all unindexed notes
+ public List <String> getUnindexed() {
+ String guid;
+ List<String> index = new ArrayList<String>();
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))
+ logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");
+
+ // Get a list of the notes
+ while (query.next()) {
+ guid = new String();
+ guid = query.valueString(0);
+ index.add(guid);
+ }
+ return index;
+ }
+ public List<String> getNextUnindexed(int limit) {
+ List<String> guids = new ArrayList<String>();
+
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))
+ logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");
+
+ // Get a list of the notes
+ String guid;
+ while (query.next()) {
+ guid = new String();
+ guid = query.valueString(0);
+ guids.add(guid);
+ }
+ 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, isDirty=true 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;
+ }
+
+ // Get all note meta information
+ public HashMap<String, NoteMetadata> getNotesMetaInformation() {
+ HashMap<String, NoteMetadata> returnValue = new HashMap<String, NoteMetadata>();
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ if (!query.exec("Select guid,titleColor, isDirty, pinned from Note"))
+ logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteMetaInformation.");
+
+ // Get a list of the notes
+ while (query.next()) {
+ 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;
+ }
+ // Get note meta information
+ public NoteMetadata getNoteMetaInformation(String guid) {
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ 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();
+
+ // Get a list of the notes
+ while (query.next()) {
+ 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 null;
+ }
+
+
+ //**********************************************************************************
+ //* Thumbnail functions
+ //**********************************************************************************
+ // Set if a new thumbnail is needed
+ public void setThumbnailNeeded(String guid, boolean needed) {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");
+ query.bindValue(":guid", guid);
+ query.bindValue(":needed", needed);
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());
+
+ }
+ // Is a thumbail needed for this guid?
+ public boolean isThumbnailNeeded(String guid) {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("select thumbnailneeded from note where guid=:guid");
+ query.bindValue(":guid", guid);
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());
+
+ boolean returnValue;
+ // Get a list of the notes
+ if (query.next())
+ returnValue = query.valueBoolean(0, false);
+ else
+ returnValue = false;
+
+ return returnValue;
+ }
+ // Set if a new thumbnail is needed
+ public void setThumbnail(String guid, QByteArray thumbnail) {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");
+ query.bindValue(":guid", guid);
+ query.bindValue(":thumbnail", thumbnail.toByteArray());
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());
+
+ }
+ // Set if a new thumbnail is needed
+ public QByteArray getThumbnail(String guid) {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("Select thumbnail from note where guid=:guid");
+ query.bindValue(":guid", guid);
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());
+ // Get a list of the notes
+ if (query.next()) {
+ try {
+ if (query.getBlob(0) != null) {
+ return new QByteArray(query.getBlob(0));
+ }
+ } catch (java.lang.IllegalArgumentException e) {
+ return null;
+ }
+ }
+ return null;
+ }
+ // Get all thumbnails
+ public HashMap<String, QPixmap> getThumbnails() {
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ HashMap<String, QPixmap> map = new HashMap<String,QPixmap>();
+
+ check = query.prepare("Select guid,thumbnail from note where thumbnailneeded=false and isExpunged=false");
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());
+ // Get a list of the notes
+ while (query.next()) {
+ try {
+ if (query.getBlob(1) != null) {
+ QByteArray data = new QByteArray(query.getBlob(1));
+ QPixmap img = new QPixmap();
+ if (img.loadFromData(data)) {
+ img = img.scaled(Global.largeThumbnailSize);
+ map.put(query.valueString(0), img);
+ }
+ }
+ } catch (java.lang.IllegalArgumentException e) {
+ logger.log(logger.HIGH, "Error retrieving thumbnail " +e.getMessage());
+ }
+ }
+ return map;
+ }
+ // Get a list of notes that need thumbnails
+ public List<String> findThumbnailsNeeded() {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("select guid from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 5");
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL findThumbnailsNeeded query failed: " +query.lastError().toString());
+
+
+ // Get a list of the notes
+ List<String> values = new ArrayList<String>();
+ while (query.next()) {
+ values.add(query.valueString(0));
+ }
+
+ return values;
+ }
+ // Get a count of thumbnails needed
+ public int getThumbnailNeededCount() {
+
+ boolean check;
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+
+ check = query.prepare("select count(guid) from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 2");
+ check = query.exec();
+ if (!check)
+ logger.log(logger.EXTREME, "Note SQL findThumbnailNeededCount query failed: " +query.lastError().toString());
+
+ if (query.next()) {
+ return query.valueInteger(0);
+ }
+
+ 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) {
+ Note n = getNote(guid, true, false, false, false,false);
+ int position = n.getContent().indexOf("<en-media");
+ int endPos;
+ for (;position>-1;) {
+ endPos = n.getContent().indexOf(">", position+1);
+ String oldSegment = n.getContent().substring(position,endPos);
+ int hashPos = oldSegment.indexOf("hash=\"");
+ int hashEnd = oldSegment.indexOf("\"", hashPos+7);
+ String hash = oldSegment.substring(hashPos+6, hashEnd);
+ if (hash.equalsIgnoreCase(oldHash)) {
+ String newSegment = oldSegment.replace(oldHash, newHash);
+ String content = n.getContent().substring(0,position) +
+ newSegment +
+ n.getContent().substring(endPos);
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("update note set isdirty=true, thumbnailneeded=true, content=:content, contentText=:contentText where guid=:guid");
+ query.bindValue(":content", content);
+ query.bindValue(":contentText", Global.extractPlainText(content));
+ query.bindValue(":guid", n.getGuid());
+ query.exec();
+ }
+
+ position = n.getContent().indexOf("<en-media", position+1);
+ }
+ }
+
+ // Extract metadata from a note's Note.attributes.sourceApplication
+ private NoteMetadata extractMetadata(String sourceApplication) {
+ // ICHANGED 自分のキーに変更
+ String consumerKey = "kimaira792:{";
+
+ int startPos = sourceApplication.indexOf(consumerKey);
+ if (startPos < 0 )
+ return null;
+
+ NoteMetadata meta = new NoteMetadata();
+ startPos = startPos+consumerKey.length();
+
+// String startString = sourceApplication.substring(0,startPos);
+ String metaString = sourceApplication.substring(startPos);
+// String endString = metaString.substring(metaString.indexOf("};"));
+ int endPos = metaString.indexOf("};");
+ if (endPos > 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("; ");
+
+ // ICHANGED 自分のキーに変更
+ value.append("kimaira792:{");
+ 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 "";
+
+ // ICHANGED 自分のキーに変更
+ String consumerKey = "kimaira792:{";
+ 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;
+ }
+
+ public void dumpDirtyNotes() {\r
+ logger.log(logger.LOW, "Dirty Notes: ");\r
+ List<Note> noteList = this.getDirty();\r
+ for (int i=0; i<noteList.size();i++) {\r
+ logger.log(logger.LOW, i +" : " +noteList.get(i).getGuid() + " : " +noteList.get(i).getTitle() );\r
+ }\r
+ }
+
+ // ICHANGED
+ // guidからノートのタイトルをゲット
+ public String getNoteTitle(String noteGuid) {
+
+ if (noteGuid == null)
+ return null;
+ if (noteGuid.trim().equals(""))
+ return null;
+
+ NSqlQuery query = new NSqlQuery(db.getConnection());
+ query.prepare("Select title from Note where guid=:guid and isExpunged=false");
+ query.bindValue(":guid", noteGuid);
+ if (!query.exec()) {
+ logger.log(logger.MEDIUM, "Noteテーブルからタイトルの取得失敗");
+ logger.log(logger.MEDIUM, query.lastError());
+ return null;
+ }
+ if (!query.next()) {
+ logger.log(logger.EXTREME, "SQL Retrieve failed for note guid "
+ + noteGuid + " in getNoteTitle()");
+ logger.log(logger.EXTREME, " -> " + query.lastError().toString());
+ logger.log(logger.EXTREME, " -> " + query.lastError());
+ return null;
+ }
+
+ String noteTitle = query.valueString(0);
+
+ return noteTitle;
+ }
+
+ /*
+ * // ICHANGED // ノートがアクティブかどうか調べる public boolean isNoteActive(String guid){
+ * if(guid == null) return false; if(guid.trim().equals("")) return false;
+ *
+ * NSqlQuery query = new NSqlQuery(db.getConnection());
+ * query.prepare("Select active from Note where guid=:guid");
+ * query.bindValue(":guid", guid); if(!query.exec()){
+ * logger.log(logger.EXTREME, "note.isNoteActive SQL retrieve has failed.");
+ * return false; } if(!query.next()){ logger.log(logger.EXTREME,
+ * "SQL Retrieve failed for note guid " +guid + " in isNoteActive()");
+ * return false; }
+ *
+ * boolean retVal = query.valueBoolean(0, false); return retVal; }
+ */
+
+}
+
+
+
+
+
OSDN Git Service
