X-Git-Url: http://git.sourceforge.jp/view?p=neighbornote%2FNeighborNote.git;a=blobdiff_plain;f=src%2Fcx%2Ffbn%2Fnevernote%2Fsql%2FNoteResourceTable.java;h=b3a080699b8b4deeacaee2a3e5992d19b18c2a41;hp=6acc35764a54797a17f59bab8b41d583881423e3;hb=adf1dae4e586ae493c99a71f6892eb5797a5111a;hpb=30a7c4065569aee9c3bc0c82c01b5f1f137a8c7e diff --git a/src/cx/fbn/nevernote/sql/NoteResourceTable.java b/src/cx/fbn/nevernote/sql/NoteResourceTable.java index 6acc357..b3a0806 100644 --- a/src/cx/fbn/nevernote/sql/NoteResourceTable.java +++ b/src/cx/fbn/nevernote/sql/NoteResourceTable.java @@ -48,7 +48,7 @@ public class NoteResourceTable { } // Create the table public void createTable() { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); // Create the NoteResource table logger.log(logger.HIGH, "Creating table NoteResource..."); if (!query.exec("Create table NoteResources (guid varchar primary key, " + @@ -66,35 +66,40 @@ public class NoteResourceTable { logger.log(logger.HIGH, "Noteresources unindexed_resources index creation FAILED!!!"); if (!query.exec("CREATE INDEX resources_dataheshhex on noteresources (datahash, guid);")) logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!"); - + if (!query.exec("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);")) + logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!"); } // Drop the table public void dropTable() { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.exec("Drop table NoteResources"); } // Reset the dirty flag public void resetDirtyFlag(String guid) { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("Update noteresources set isdirty=false where guid=:guid"); query.bindValue(":guid", guid); if (!query.exec()) logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError()); + else + query.exec("commit"); } // Set if the resource should be indexed public void setIndexNeeded(String guid, Boolean indexNeeded) { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid"); query.bindValue(":needed", indexNeeded); query.bindValue(":guid", guid); if (!query.exec()) logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError()); + else + query.exec("commit"); } // get any unindexed resource public List getNextUnindexed(int limit) { List guids = new ArrayList(); - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); if (!query.exec("Select guid from NoteResources where indexNeeded = true limit " +limit)) logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getNextUnindexed(): " +query.lastError()); @@ -108,10 +113,41 @@ public class NoteResourceTable { } return guids; } + // get any unindexed resource + public List getUnindexed() { + List guids = new ArrayList(); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); + + if (!query.exec("Select guid from NoteResources where indexNeeded = true")) + logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getUnindexed(): " +query.lastError()); + + // Get a list of the notes + String guid; + while (query.next()) { + guid = new String(); + guid = query.valueString(0); + guids.add(guid); + } + return guids; + } + public List getAll() { + List guids = new ArrayList(); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); + + query.prepare("Select guid from noteresources;"); + if (!query.exec()) + logger.log(logger.EXTREME, "Error getting all note resource guids. " +query.lastError()); + + while (query.next()) { + guids.add(query.valueString(0)); + } + return guids; + } + public List findInkNotes() { List guids = new ArrayList(); - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'"); if (!query.exec()) @@ -126,7 +162,7 @@ public class NoteResourceTable { public void saveNoteResource(Resource r, boolean isDirty) { logger.log(logger.HIGH, "Entering DBRunner.saveNoteResources"); boolean check; - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); check = query.prepare("Insert Into NoteResources (" @@ -196,21 +232,25 @@ public class NoteResourceTable { if (!check) { logger.log(logger.MEDIUM, "*** NoteResource Table insert failed."); logger.log(logger.MEDIUM, query.lastError()); - } + } else + query.exec("commit"); logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources"); } // delete an old resource public void expungeNoteResource(String guid) { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("delete from NoteResources where guid=:guid"); query.bindValue(":guid", guid); query.exec(); - - query.prepare("delete from InkImages where guid=:guid"); - query.bindValue(":guid", guid); - query.exec(); + query.exec("commit"); + + NSqlQuery query2 = new NSqlQuery(db.getConnection()); + query2.prepare("Delete from InkImages where guid=:guid"); + query2.bindValue(":guid", guid); + query2.exec(); + query2.exec("commit"); } @@ -220,7 +260,7 @@ public class NoteResourceTable { logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex"); boolean check; - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); check = query.prepare("Select guid from NoteResources " + "where noteGuid=:noteGuid and dataHash=:hash"); @@ -252,7 +292,7 @@ public class NoteResourceTable { logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash"); boolean check; - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); check = query.prepare("Select guid, mime, from NoteResources " + "where noteGuid=:noteGuid and dataHash=:hash"); @@ -278,7 +318,7 @@ public class NoteResourceTable { r.setGuid(query.valueString(0)); r.setMime(query.valueString(1)); - NSqlQuery binary = new NSqlQuery(db.getConnection()); + NSqlQuery binary = new NSqlQuery(db.getResourceConnection()); if (!binary.prepare("Select databinary from NoteResources " + "where guid=:guid")) { logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed"); @@ -309,14 +349,14 @@ public class NoteResourceTable { if (guid == null) return null; - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); String queryString; queryString = new String("Select guid, noteGuid, mime, width, height, duration, " +"active, updateSequenceNumber, dataHash, dataSize, " +"recognitionHash, recognitionSize, " +"attributeLatitude, attributeLongitude, attributeAltitude, " +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, " - +"attributeRecoType, attributeFileName, attributeAttachment, recognitionBinary " + +"attributeRecoType, attributeFileName, attributeAttachment, attributeSourceUrl " +" from NoteResources where guid=:guid"); @@ -371,6 +411,7 @@ public class NoteResourceTable { a.setRecoType(stringValue(query.valueString(18))); // Recognition Type a.setFileName(stringValue(query.valueString(19))); // File Name a.setAttachment(booleanValue(query.valueString(20).toString(),false)); + a.setSourceURL(query.valueString(21)); r.setAttributes(a); if (withBinary) { @@ -394,7 +435,7 @@ public class NoteResourceTable { return null; List res = new ArrayList(); - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("Select guid" +" from NoteResources where noteGuid = :noteGuid"); query.bindValue(":noteGuid", noteGuid); @@ -415,7 +456,7 @@ public class NoteResourceTable { return null; boolean check; List res = new ArrayList(); - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); check = query.prepare("Select " +"recognitionHash, recognitionSize, recognitionBinary " +" from NoteResources where noteGuid=:guid"); @@ -452,7 +493,7 @@ public class NoteResourceTable { if (guid == null) return null; boolean check; - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); check = query.prepare("Select " +"recognitionHash, recognitionSize, recognitionBinary, noteGuid " +" from NoteResources where guid=:guid"); @@ -489,43 +530,43 @@ public class NoteResourceTable { // Save Note Resource public void updateNoteResource(Resource r, boolean isDirty) { logger.log(logger.HIGH, "Entering ListManager.updateNoteResource"); - NSqlQuery query = new NSqlQuery(db.getConnection()); - query.prepare("delete from NoteResources where guid=:recGuid"); - query.bindValue(":recGuid", r.getGuid()); - query.exec(); + expungeNoteResource(r.getGuid()); saveNoteResource(r, isDirty); logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource"); } // Update note resource GUID public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) { logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid"); - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid"); query.bindValue(":newGuid", newGuid); query.bindValue(":isDirty", isDirty); query.bindValue(":oldGuid", oldGuid); query.exec(); + query.exec("commit"); logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid"); } // Update note resource GUID public void resetUpdateSequenceNumber(String guid, boolean isDirty) { logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid"); - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid"); query.bindValue(":isDirty", isDirty); query.bindValue(":guid", guid); query.exec(); + query.exec("commit"); logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid"); } // Drop the table public void reindexAll() { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.exec("Update NoteResources set indexneeded=true"); + query.exec("commit"); } // Count attachments public int getResourceCount() { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.exec("select count(*) from noteresources"); query.next(); int returnValue = new Integer(query.valueString(0)); @@ -534,7 +575,7 @@ public class NoteResourceTable { // // Count unindexed notes public int getUnindexedCount() { - NSqlQuery query = new NSqlQuery(db.getConnection()); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); query.exec("select count(*) from noteresources where indexneeded=true"); query.next(); int returnValue = new Integer(query.valueString(0)); @@ -592,4 +633,31 @@ public class NoteResourceTable { return unknown; } + // Update note source url. + public void updateNoteSourceUrl(String guid, String url, boolean isDirty) { + logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteSourceUrl()"); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); + query.prepare("update NoteResources set attributesourceurl=:url, isDirty=:isDirty where guid=:guid"); + query.bindValue(":guid", guid); + query.bindValue(":isDirty", isDirty); + query.bindValue(":url", url); + query.exec(); + query.exec("commit"); + logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteSourceUrl()"); + } + + // Get note source + public String getNoteSourceUrl(String guid) { + logger.log(logger.HIGH, "Entering RNoteResourceTable.getNoteSourceUrl()"); + NSqlQuery query = new NSqlQuery(db.getResourceConnection()); + query.prepare("Select attributesourceurl from noteresources where guid=:guid"); + query.bindValue(":guid", guid); + query.exec(); + if (query.next()) { + logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl()"); + return query.valueString(0); + } + logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl() - no value found"); + return null; + } }