Added LaTex image insert & edit

[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / NoteResourceTable.java

diff --git a/src/cx/fbn/nevernote/sql/NoteResourceTable.java b/src/cx/fbn/nevernote/sql/NoteResourceTable.java
index 6200444..b3a0806 100644 (file)
--- a/src/cx/fbn/nevernote/sql/NoteResourceTable.java
+++ b/src/cx/fbn/nevernote/sql/NoteResourceTable.java
@@ -48,7 +48,7 @@ public class NoteResourceTable  {
        }\r
        // Create the table\r
        public void createTable() {\r
        }\r
        // Create the table\r
        public void createTable() {\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

         // Create the NoteResource table\r
         logger.log(logger.HIGH, "Creating table NoteResource...");\r
         if (!query.exec("Create table NoteResources (guid varchar primary key, " +\r
         // Create the NoteResource table\r
         logger.log(logger.HIGH, "Creating table NoteResource...");\r
         if (!query.exec("Create table NoteResources (guid varchar primary key, " +\r


@@ -66,35 +66,40 @@ public class NoteResourceTable  {
                logger.log(logger.HIGH, "Noteresources unindexed_resources index creation FAILED!!!");\r
         if (!query.exec("CREATE INDEX resources_dataheshhex on noteresources (datahash, guid);"))\r
                logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r
                logger.log(logger.HIGH, "Noteresources unindexed_resources index creation FAILED!!!");\r
         if (!query.exec("CREATE INDEX resources_dataheshhex on noteresources (datahash, guid);"))\r
                logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r

-        \r
+        if (!query.exec("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);"))\r
+               logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r

        }\r
        // Drop the table\r
        public void dropTable() {               \r
        }\r
        // Drop the table\r
        public void dropTable() {               \r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.exec("Drop table NoteResources");\r
        }\r
        // Reset the dirty flag\r
        public void  resetDirtyFlag(String guid) {\r
                query.exec("Drop table NoteResources");\r
        }\r
        // Reset the dirty flag\r
        public void  resetDirtyFlag(String guid) {\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                \r
                query.prepare("Update noteresources set isdirty=false where guid=:guid");\r
                query.bindValue(":guid", guid);\r
                if (!query.exec())\r
                        logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError());\r
                \r
                query.prepare("Update noteresources set isdirty=false where guid=:guid");\r
                query.bindValue(":guid", guid);\r
                if (!query.exec())\r
                        logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError());\r

+               else\r
+                       query.exec("commit");\r

        }\r
        // Set if the resource should be indexed\r
        public void  setIndexNeeded(String guid, Boolean indexNeeded) {\r
        }\r
        // Set if the resource should be indexed\r
        public void  setIndexNeeded(String guid, Boolean indexNeeded) {\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());            \r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());            \r

                query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid");\r
                query.bindValue(":needed", indexNeeded);\r
                query.bindValue(":guid", guid);\r
                if (!query.exec())\r
                        logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError());\r
                query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid");\r
                query.bindValue(":needed", indexNeeded);\r
                query.bindValue(":guid", guid);\r
                if (!query.exec())\r
                        logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError());\r

+               else\r
+                       query.exec("commit");\r

        }\r
        // get any unindexed resource\r
        public List<String> getNextUnindexed(int limit) {\r
                List<String> guids = new ArrayList<String>();\r
        }\r
        // get any unindexed resource\r
        public List<String> getNextUnindexed(int limit) {\r
                List<String> guids = new ArrayList<String>();\r

-        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                                        \r
                if (!query.exec("Select guid from NoteResources where indexNeeded = true limit " +limit))\r
                        logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getNextUnindexed(): " +query.lastError());\r
                                        \r
                if (!query.exec("Select guid from NoteResources where indexNeeded = true limit " +limit))\r
                        logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getNextUnindexed(): " +query.lastError());\r


@@ -111,7 +116,7 @@ public class NoteResourceTable  {
        // get any unindexed resource\r
        public List<String> getUnindexed() {\r
                List<String> guids = new ArrayList<String>();\r
        // get any unindexed resource\r
        public List<String> getUnindexed() {\r
                List<String> guids = new ArrayList<String>();\r

-        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                                        \r
                if (!query.exec("Select guid from NoteResources where indexNeeded = true"))\r
                        logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getUnindexed(): " +query.lastError());\r
                                        \r
                if (!query.exec("Select guid from NoteResources where indexNeeded = true"))\r
                        logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getUnindexed(): " +query.lastError());\r


@@ -126,9 +131,23 @@ public class NoteResourceTable  {
                return guids;   \r
        }\r
 \r
                return guids;   \r
        }\r
 \r

+       public List<String> getAll() {\r
+               List<String> guids = new ArrayList<String>();\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               \r
+               query.prepare("Select guid from noteresources;");\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Error getting all note resource guids. " +query.lastError());\r
+               \r
+               while (query.next()) {\r
+                       guids.add(query.valueString(0));\r
+               }\r
+               return guids;\r
+       }\r
+       \r

        public List<String> findInkNotes() {\r
                List<String> guids = new ArrayList<String>();\r
        public List<String> findInkNotes() {\r
                List<String> guids = new ArrayList<String>();\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                \r
                query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'");\r
                if (!query.exec())\r
                \r
                query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'");\r
                if (!query.exec())\r


@@ -143,7 +162,7 @@ public class NoteResourceTable  {
        public void saveNoteResource(Resource r, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering DBRunner.saveNoteResources");\r
                boolean check;\r
        public void saveNoteResource(Resource r, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering DBRunner.saveNoteResources");\r
                boolean check;\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
                \r
                check = query.prepare("Insert Into NoteResources ("\r
                SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
                \r
                check = query.prepare("Insert Into NoteResources ("\r


@@ -213,21 +232,25 @@ public class NoteResourceTable  {
                        if (!check) {\r
                                logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");             \r
                                logger.log(logger.MEDIUM, query.lastError());\r
                        if (!check) {\r
                                logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");             \r
                                logger.log(logger.MEDIUM, query.lastError());\r

-                       }\r
+                       } else\r
+                               query.exec("commit");\r

                        \r
                                                \r
                        logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources");\r
        }\r
        // delete an old resource\r
        public void expungeNoteResource(String guid) {\r
                        \r
                                                \r
                        logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources");\r
        }\r
        // delete an old resource\r
        public void expungeNoteResource(String guid) {\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.prepare("delete from NoteResources where guid=:guid");\r
                query.bindValue(":guid", guid);\r
                query.exec();\r
                query.prepare("delete from NoteResources where guid=:guid");\r
                query.bindValue(":guid", guid);\r
                query.exec();\r

-\r
-               query.prepare("delete from InkImages where guid=:guid");\r
-               query.bindValue(":guid", guid);\r
-               query.exec();\r
+               query.exec("commit");\r
+               \r
+               NSqlQuery query2 = new NSqlQuery(db.getConnection());\r
+               query2.prepare("Delete from InkImages where guid=:guid");\r
+               query2.bindValue(":guid", guid);\r
+               query2.exec();\r
+               query2.exec("commit");\r

 \r
        }\r
 \r
 \r
        }\r
 \r


@@ -237,7 +260,7 @@ public class NoteResourceTable  {
                logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
 \r
                boolean check;\r
                logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
 \r
                boolean check;\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                \r
                check = query.prepare("Select guid from NoteResources " +\r
                                        "where noteGuid=:noteGuid and dataHash=:hash");\r
                \r
                check = query.prepare("Select guid from NoteResources " +\r
                                        "where noteGuid=:noteGuid and dataHash=:hash");\r


@@ -269,7 +292,7 @@ public class NoteResourceTable  {
                logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
 \r
                boolean check;\r
                logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
 \r
                boolean check;\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                \r
                check = query.prepare("Select guid, mime, from NoteResources " +\r
                                        "where noteGuid=:noteGuid and dataHash=:hash");\r
                \r
                check = query.prepare("Select guid, mime, from NoteResources " +\r
                                        "where noteGuid=:noteGuid and dataHash=:hash");\r


@@ -295,7 +318,7 @@ public class NoteResourceTable  {
                r.setGuid(query.valueString(0));\r
                r.setMime(query.valueString(1));\r
                \r
                r.setGuid(query.valueString(0));\r
                r.setMime(query.valueString(1));\r
                \r

-               NSqlQuery binary = new NSqlQuery(db.getConnection());\r
+               NSqlQuery binary = new NSqlQuery(db.getResourceConnection());\r

                if (!binary.prepare("Select databinary from NoteResources " +\r
                                        "where guid=:guid")) {\r
                        logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");\r
                if (!binary.prepare("Select databinary from NoteResources " +\r
                                        "where guid=:guid")) {\r
                        logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");\r


@@ -326,14 +349,14 @@ public class NoteResourceTable  {
                if (guid == null)\r
                        return null;\r
                \r
                if (guid == null)\r
                        return null;\r
                \r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                String queryString;\r
                queryString = new String("Select guid, noteGuid, mime, width, height, duration, "\r
                                +"active, updateSequenceNumber, dataHash, dataSize, "\r
                                +"recognitionHash, recognitionSize, "\r
                                +"attributeLatitude, attributeLongitude, attributeAltitude, "\r
                                +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, "\r
                String queryString;\r
                queryString = new String("Select guid, noteGuid, mime, width, height, duration, "\r
                                +"active, updateSequenceNumber, dataHash, dataSize, "\r
                                +"recognitionHash, recognitionSize, "\r
                                +"attributeLatitude, attributeLongitude, attributeAltitude, "\r
                                +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, "\r

-                               +"attributeRecoType, attributeFileName, attributeAttachment, recognitionBinary "\r
+                               +"attributeRecoType, attributeFileName, attributeAttachment, attributeSourceUrl "\r

                                +" from NoteResources where guid=:guid");\r
 \r
                \r
                                +" from NoteResources where guid=:guid");\r
 \r
                \r


@@ -388,6 +411,7 @@ public class NoteResourceTable  {
                        a.setRecoType(stringValue(query.valueString(18)));                 // Recognition Type\r
                        a.setFileName(stringValue(query.valueString(19)));                  // File Name\r
                        a.setAttachment(booleanValue(query.valueString(20).toString(),false));\r
                        a.setRecoType(stringValue(query.valueString(18)));                 // Recognition Type\r
                        a.setFileName(stringValue(query.valueString(19)));                  // File Name\r
                        a.setAttachment(booleanValue(query.valueString(20).toString(),false));\r

+                       a.setSourceURL(query.valueString(21));\r

                        r.setAttributes(a);\r
                \r
                        if (withBinary) {\r
                        r.setAttributes(a);\r
                \r
                        if (withBinary) {\r


@@ -411,7 +435,7 @@ public class NoteResourceTable  {
                        return null;\r
                List<Resource> res = new ArrayList<Resource>();\r
                \r
                        return null;\r
                List<Resource> res = new ArrayList<Resource>();\r
                \r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.prepare("Select guid"\r
                                +" from NoteResources where noteGuid = :noteGuid");\r
                query.bindValue(":noteGuid", noteGuid);\r
                query.prepare("Select guid"\r
                                +" from NoteResources where noteGuid = :noteGuid");\r
                query.bindValue(":noteGuid", noteGuid);\r


@@ -432,7 +456,7 @@ public class NoteResourceTable  {
                        return null;\r
                boolean check;\r
                List<Resource> res = new ArrayList<Resource>();\r
                        return null;\r
                boolean check;\r
                List<Resource> res = new ArrayList<Resource>();\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                check = query.prepare("Select "\r
                                +"recognitionHash, recognitionSize, recognitionBinary "\r
                                +" from NoteResources where noteGuid=:guid");\r
                check = query.prepare("Select "\r
                                +"recognitionHash, recognitionSize, recognitionBinary "\r
                                +" from NoteResources where noteGuid=:guid");\r


@@ -469,7 +493,7 @@ public class NoteResourceTable  {
                if (guid == null)\r
                        return null;\r
                boolean check;\r
                if (guid == null)\r
                        return null;\r
                boolean check;\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                check = query.prepare("Select "\r
                                +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
                                +" from NoteResources where guid=:guid");\r
                check = query.prepare("Select "\r
                                +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
                                +" from NoteResources where guid=:guid");\r


@@ -506,44 +530,43 @@ public class NoteResourceTable  {
        // Save Note Resource\r
        public void updateNoteResource(Resource r, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r
        // Save Note Resource\r
        public void updateNoteResource(Resource r, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
-               query.prepare("delete from NoteResources where guid=:recGuid");\r
-               query.bindValue(":recGuid", r.getGuid());\r
-               query.exec();\r
+               expungeNoteResource(r.getGuid());\r

                saveNoteResource(r, isDirty);\r
                saveNoteResource(r, isDirty);\r

-               query = null;\r

                logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource");\r
        }\r
        // Update note resource GUID\r
        public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
                logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource");\r
        }\r
        // Update note resource GUID\r
        public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");\r
                query.bindValue(":newGuid", newGuid);\r
                query.bindValue(":isDirty", isDirty);\r
                query.bindValue(":oldGuid", oldGuid);\r
                query.exec();\r
                query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");\r
                query.bindValue(":newGuid", newGuid);\r
                query.bindValue(":isDirty", isDirty);\r
                query.bindValue(":oldGuid", oldGuid);\r
                query.exec();\r

+               query.exec("commit");\r

                logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
        }\r
        // Update note resource GUID\r
        public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
                logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
        }\r
        // Update note resource GUID\r
        public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
                logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");\r
                query.bindValue(":isDirty", isDirty);\r
                query.bindValue(":guid", guid);\r
                query.exec();\r
                query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");\r
                query.bindValue(":isDirty", isDirty);\r
                query.bindValue(":guid", guid);\r
                query.exec();\r

+               query.exec("commit");\r

                logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
        }\r
        \r
        // Drop the table\r
        public void reindexAll() {              \r
                logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
        }\r
        \r
        // Drop the table\r
        public void reindexAll() {              \r

-               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.exec("Update NoteResources set indexneeded=true");\r
                query.exec("Update NoteResources set indexneeded=true");\r

+               query.exec("commit");\r

        }\r
        // Count attachments\r
        public int getResourceCount() {\r
        }\r
        // Count attachments\r
        public int getResourceCount() {\r

-        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.exec("select count(*) from noteresources");\r
                query.next(); \r
                int returnValue = new Integer(query.valueString(0));\r
                query.exec("select count(*) from noteresources");\r
                query.next(); \r
                int returnValue = new Integer(query.valueString(0));\r


@@ -552,7 +575,7 @@ public class NoteResourceTable  {
        //\r
        // Count unindexed notes\r
        public int getUnindexedCount() {\r
        //\r
        // Count unindexed notes\r
        public int getUnindexedCount() {\r

-        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+        NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r

                query.exec("select count(*) from noteresources where indexneeded=true");\r
                query.next(); \r
                int returnValue = new Integer(query.valueString(0));\r
                query.exec("select count(*) from noteresources where indexneeded=true");\r
                query.next(); \r
                int returnValue = new Integer(query.valueString(0));\r


@@ -610,4 +633,31 @@ public class NoteResourceTable  {
                        return unknown;\r
        }\r
 \r
                        return unknown;\r
        }\r
 \r

+       // Update note source url. \r
+       public void updateNoteSourceUrl(String guid, String url, boolean isDirty) {\r
+               logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteSourceUrl()");\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               query.prepare("update NoteResources set attributesourceurl=:url, isDirty=:isDirty where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.bindValue(":isDirty", isDirty);\r
+               query.bindValue(":url", url);\r
+               query.exec();\r
+               query.exec("commit");\r
+               logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteSourceUrl()");\r
+       }\r
+       \r
+       // Get note source\r
+       public String getNoteSourceUrl(String guid) {\r
+               logger.log(logger.HIGH, "Entering RNoteResourceTable.getNoteSourceUrl()");\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               query.prepare("Select attributesourceurl from noteresources where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               if (query.next()) {\r
+                       logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl()");\r
+                       return query.valueString(0);\r
+               }\r
+               logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl() - no value found");\r
+               return null;\r
+       }\r

 }\r
 }\r