OSDN Git Service

- Correct issue with importing when not a premium member.
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / NoteResourceTable.java
index a0461e7..1f24b68 100644 (file)
@@ -1,5 +1,5 @@
 /*\r
- * This file is part of NeverNote \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
 \r
 \r
 package cx.fbn.nevernote.sql;\r
+import java.text.SimpleDateFormat;\r
+import java.util.ArrayList;\r
 import java.util.List;\r
 \r
+import com.evernote.edam.type.Data;\r
 import com.evernote.edam.type.Resource;\r
+import com.evernote.edam.type.ResourceAttributes;\r
+import com.trolltech.qt.core.QByteArray;\r
 \r
-import cx.fbn.nevernote.Global;\r
-import cx.fbn.nevernote.sql.requests.ResourceRequest;\r
+import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
+import cx.fbn.nevernote.utilities.ApplicationLogger;\r
 \r
 \r
-public class NoteResourceTable {\r
-       private final int id;\r
+\r
+public class NoteResourceTable  {\r
+       /**\r
+        * \r
+        */\r
+//     private static final long serialVersionUID = 1L;\r
+       private final ApplicationLogger                 logger;\r
+       private final DatabaseConnection                db;     \r
        \r
        // Constructor\r
-       public NoteResourceTable(int i) {\r
-               id = i;\r
+       public NoteResourceTable(ApplicationLogger l, DatabaseConnection d) {\r
+               logger = l;\r
+               db = d;\r
        }\r
        // Create the table\r
        public void createTable() {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Create_Table;\r
-               Global.dbRunner.addWork(request);\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
+                       "noteGuid varchar, updateSequenceNumber integer, dataHash varchar, "+\r
+                       "dataSize integer, dataBinary blob, "+\r
+                       "mime varchar, width integer, height integer, duration integer, "+\r
+                       "active integer, recognitionHash varchar, recognitionSize integer, " +\r
+                       "recognitionBinary varchar, attributeSourceUrl varchar, attributeTimestamp timestamp, " +\r
+                       "attributeLatitude double, attributeLongitude double, "+\r
+                       "attributeAltitude double, attributeCameraMake varchar, attributeCameraModel varchar, "\r
+                       +"attributeClientWillIndex varchar, attributeRecoType varchar, attributeFileName varchar,"+\r
+                       "attributeAttachment boolean, isDirty boolean, indexNeeded boolean)"))\r
+               logger.log(logger.HIGH, "Table NoteResource creation FAILED!!!"); \r
+        if (!query.exec("CREATE INDEX unindexed_resources on noteresources (indexneeded desc, guid);"))\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
+        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
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Drop_Table;\r
-               Global.dbRunner.addWork(request);\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
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Reset_Dirty_Flag;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\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
+               else\r
+                       query.exec("commit");\r
        }\r
        // Set if the resource should be indexed\r
        public void  setIndexNeeded(String guid, Boolean indexNeeded) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Set_Index_Needed;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = indexNeeded;\r
-               Global.dbRunner.addWork(request);\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
+               else\r
+                       query.exec("commit");\r
        }\r
        // get any unindexed resource\r
        public List<String> getNextUnindexed(int limit) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Next_Unindexed;\r
-               request.int1 = limit;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseStrings;\r
+               List<String> guids = new ArrayList<String>();\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
+               // 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
+       // get any unindexed resource\r
+       public List<String> getUnindexed() {\r
+               List<String> guids = new ArrayList<String>();\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
+               // 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
+       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
+               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
+                       logger.log(logger.EXTREME, "Error searching for ink notes. " +query.lastError());\r
+               \r
+               while (query.next()) {\r
+                       guids.add(query.valueString(0));\r
+               }\r
+               return guids;\r
        }\r
+       \r
        public void saveNoteResource(Resource r, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Save_Note_Resource;\r
-               request.resource = r.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
-//             Global.dbClientWait(id);\r
+               logger.log(logger.HIGH, "Entering saveNoteResources: isDirty " +isDirty);\r
+               boolean check;\r
+               logger.log(logger.HIGH, "Note: " +r.getNoteGuid());\r
+               logger.log(logger.HIGH, "Resource: " +r.getGuid());\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
+                               +"guid, noteGuid, dataHash, dataSize, dataBinary, updateSequenceNumber, "\r
+                               +"mime, width, height, duration, active, recognitionHash, "                             \r
+                               +"recognitionSize, recognitionBinary, attributeSourceUrl, attributeTimestamp, "\r
+                               +"attributeLatitude, attributeLongitude, attributeAltitude, attributeCameraMake, "\r
+                               +"attributeCameraModel, "\r
+                               +"attributeClientWillIndex, attributeRecoType, attributeFileName, attributeAttachment, isDirty, "\r
+                               +"indexNeeded) Values("\r
+                               +":guid, :noteGuid, :dataHash,:dataSize, :dataBody, :updateSequenceNumber, "\r
+                               +":mime, :width, :height, :duration, :active, :recognitionHash, "                               \r
+                               +":recognitionSize, :recognitionBody, :attributeSourceUrl, :attributeTimestamp, "\r
+                               +":attributeLatitude, :attributeLongitude, :attributeAltitude, :attributeCameraMake, "\r
+                               +":attributeCameraModel, "\r
+                               +":attributeClientWillIndex, :attributeRecoType, :attributeFileName, :attributeAttachment, "\r
+                               +":isDirty, true)");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteResource SQL insert prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       \r
+                       query.bindValue(":guid", r.getGuid());\r
+                       query.bindValue(":noteGuid", r.getNoteGuid());\r
+                       if (r.getData() != null) {\r
+                               query.bindValue(":dataHash", byteArrayToHexString(r.getData().getBodyHash()));\r
+//                             query.bindValue(":dataHash", "c0369123fe9871d675ae456fd056ba33");\r
+                               query.bindValue(":dataSize", r.getData().getSize());\r
+                               query.bindBlob(":dataBody", r.getData().getBody());\r
+                       }\r
+                       query.bindValue(":updateSequenceNumber", r.getUpdateSequenceNum());\r
+                       query.bindValue(":mime", r.getMime());\r
+                       query.bindValue(":width", new Integer(r.getWidth()));\r
+                       query.bindValue(":height", new Integer(r.getHeight()));\r
+                       query.bindValue(":duration", new Integer(r.getDuration()));\r
+                       query.bindValue(":active", r.isActive());\r
+                       if (r.getRecognition() != null) {\r
+                               query.bindValue(":recognitionHash", r.getRecognition().getBodyHash());\r
+                               query.bindValue(":recognitionSize", r.getRecognition().getSize());\r
+                               if (r.getRecognition().getBody() != null)\r
+                                       query.bindValue(":recognitionBody", new String(r.getRecognition().getBody()));\r
+                               else\r
+                                       query.bindValue(":recognitionBody", "");\r
+                       } else {\r
+                               query.bindValue(":recognitionHash", "");\r
+                               query.bindValue(":recognitionSize", 0);\r
+                               query.bindValue(":recognitionBody", "");\r
+                       }\r
+                       if (r.getAttributes() != null) {\r
+                               query.bindValue(":attributeSourceUrl", r.getAttributes().getSourceURL());\r
+                               StringBuilder ts = new StringBuilder(simple.format(r.getAttributes().getTimestamp()));\r
+                               query.bindValue(":attributeTimestamp", ts.toString());\r
+                               query.bindValue(":attributeLatitude", r.getAttributes().getLatitude());\r
+                               query.bindValue(":attributeLongitude", r.getAttributes().getLongitude());\r
+                               query.bindValue(":attributeAltitude", r.getAttributes().getAltitude());\r
+                               query.bindValue(":attributeCameraMake", r.getAttributes().getCameraMake());\r
+                               query.bindValue(":attributeCameraModel", r.getAttributes().getCameraModel());\r
+                               query.bindValue(":attributeClientWillIndex", r.getAttributes().isClientWillIndex());\r
+                               query.bindValue(":attributeRecoType", r.getAttributes().getRecoType());\r
+                               query.bindValue(":attributeFileName", r.getAttributes().getFileName());\r
+                               query.bindValue(":attributeAttachment", r.getAttributes().isAttachment());                      \r
+                       }\r
+                       query.bindValue(":isDirty", isDirty);\r
+                                               \r
+                       check = query.exec();\r
+                       if (!check) {\r
+                               logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");             \r
+                               logger.log(logger.MEDIUM, query.lastError());\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
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Expunge_Note_Resource;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);\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.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
        // Get a note resource from the database by it's hash value\r
        public String getNoteResourceGuidByHashHex(String noteGuid, String hash) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource_Guid_By_Hash_Hex;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(hash);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseString;      \r
+               logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
+\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               \r
+               check = query.prepare("Select guid from NoteResources " +\r
+                                       "where noteGuid=:noteGuid and dataHash=:hash");\r
+               if (check)\r
+                       logger.log(logger.EXTREME, "NoteResource SQL select prepare was successful.");\r
+               else {\r
+                       logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":hash", hash);\r
+       \r
+               check = query.exec();\r
+               if (!check)      {\r
+                       logger.log(logger.MEDIUM, "dbRunner.getNoteResourceGuidByHashHex Select failed." +\r
+                                       "Note Guid:" +noteGuid+\r
+                                       "Data Body Hash:" +hash);               \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               if (!query.next()) {\r
+                       logger.log(logger.MEDIUM, "Note Resource not found.");\r
+                       return null;\r
+               }\r
+               return query.valueString(0);\r
        }\r
+\r
        // Get a note resource from the database by it's hash value\r
        public Resource getNoteResourceDataBodyByHashHex(String noteGuid, String hash) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource_Data_Body_By_Hash_Hex;\r
-               request.string1 = new String(noteGuid);\r
-               request.string2 = new String(hash);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResource;\r
-       }\r
-\r
-       // Update a note resource guid\r
-       public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Update_Note_Resource_Guid;\r
-               request.string1 = new String(oldGuid);\r
-               request.string2 = new String(newGuid);\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
-//             Global.dbClientWait(id);\r
-       }\r
-       \r
+               logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
 \r
-       // Reset update sequence number to zero\r
-       public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Reset_Update_Sequence_Number;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               boolean check;\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
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               query.bindValue(":hash", hash);\r
+       \r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "NoteResource Select failed." +\r
+                                       "Note Guid:" +noteGuid+\r
+                                       "Data Body Hash:" +hash);               \r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+               if (!query.next()) {\r
+                       logger.log(logger.MEDIUM, "Note Resource not found.");\r
+                       return null;\r
+               }\r
+               \r
+               Resource r = new Resource();\r
+               r.setGuid(query.valueString(0));\r
+               r.setMime(query.valueString(1));\r
+               \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
+                       logger.log(logger.MEDIUM, binary.lastError());\r
+               }\r
+               \r
+               if (!binary.exec()) {\r
+                       logger.log(logger.MEDIUM, "NoteResources Binary Select failed." +\r
+                                       "Note Guid:" +noteGuid+\r
+                                       "Data Body Hash:" +hash);               \r
+                       logger.log(logger.MEDIUM, binary.lastError());\r
+               }\r
+               if (!binary.next()) {\r
+                       logger.log(logger.MEDIUM, "Note Resource Binary not found.");\r
+                       return null;\r
+               }\r
+               \r
+               Data d = new Data();\r
+               r.setData(d);\r
+               d.setBody(binary.valueString(0).getBytes());\r
+               logger.log(logger.HIGH, "Leaving DBRunner.getNoteResourceDataBodyByHash");\r
+               return r;\r
        }\r
+\r
        \r
        // Get a note's resourcesby Guid\r
        public Resource getNoteResource(String guid, boolean withBinary) {\r
-               if (guid == null) \r
+               if (guid == null)\r
+                       return null;\r
+               \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
+                               +"attributeRecoType, attributeFileName, attributeAttachment, attributeSourceUrl "\r
+                               +" from NoteResources where guid=:guid");\r
+\r
+               \r
+               query.prepare(queryString);\r
+               \r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
                        return null;\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource;\r
-               request.string1 = new String(guid);\r
-               request.bool1 = withBinary;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResource;\r
+               }\r
+               Resource r = null;\r
+               if (query.next()) {\r
+                                                                       \r
+                       r = new Resource();\r
+                       r.setGuid(query.valueString(0));        // Resource Guid\r
+                       r.setNoteGuid(query.valueString(1));   // note Guid\r
+                       r.setMime(query.valueString(2));       // Mime Type\r
+                       r.setWidth(new Short(query.valueString(3)));  // Width\r
+                       r.setHeight(new Short(query.valueString(4)));  // Height\r
+                       r.setDuration(new Short(query.valueString(5)));  // Duration\r
+                       r.setActive(new Boolean(query.valueString(6)));  // active\r
+                       r.setUpdateSequenceNum(new Integer(query.valueString(7)));  // update sequence number\r
+                       \r
+                       Data d = new Data();\r
+                       byte[] h = query.valueString(8).getBytes();    // data hash\r
+                       QByteArray hData = new QByteArray(h);\r
+                       QByteArray bData = new QByteArray(QByteArray.fromHex(hData));\r
+                       d.setBodyHash(bData.toByteArray());\r
+                       d.setSize(new Integer(query.valueString(9)));\r
+                       r.setData(d);\r
+                       \r
+                       Data rec = new Data();\r
+                       if (query.valueObject(10) != null)\r
+                               rec.setBodyHash(query.valueString(10).getBytes());   // Recognition Hash\r
+                       if (query.valueObject(11) != null)\r
+                               rec.setSize(new Integer(query.valueString(11)));\r
+                       else\r
+                               rec.setSize(0);\r
+                       r.setRecognition(rec);\r
+\r
+                       ResourceAttributes a = new ResourceAttributes();\r
+                       if (!query.valueString(12).equals(""))              // Latitude\r
+                               a.setLatitude(new Float(query.valueString(12)));\r
+                       if (!query.valueString(13).equals(""))              // Longitude\r
+                               a.setLongitude(new Float(query.valueString(13)));\r
+                       if (!query.valueString(14).equals(""))              // Altitude\r
+                               a.setAltitude(new Float(query.valueString(14)));\r
+                       a.setCameraMake(stringValue(query.valueString(15)));              // Camera Make\r
+                       a.setCameraModel(stringValue(query.valueString(16)));\r
+                       a.setClientWillIndex(booleanValue(query.valueString(17).toString(),false));  // Camera Model\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
+                               query.prepare("Select dataBinary from NoteResources where guid=:guid");\r
+                               query.bindValue(":guid", r.getGuid());\r
+                               query.exec();\r
+                               if (query.next()) {\r
+                                       byte[] b = query.getBlob(0);\r
+                                       r.getData().setBody(b);\r
+                               }\r
+                       } \r
+               }\r
+               return r;\r
        }\r
        \r
        \r
        // Get a note's resourcesby Guid\r
        public List<Resource> getNoteResources(String noteGuid, boolean withBinary) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resources;\r
-               request.string1 = new String(noteGuid);\r
-               request.bool1 = withBinary;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResources;\r
+               if (noteGuid == null)\r
+                       return null;\r
+               List<Resource> res = new ArrayList<Resource>();\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               query.prepare("Select guid"\r
+                               +" from NoteResources where noteGuid = :noteGuid");\r
+               query.bindValue(":noteGuid", noteGuid);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {\r
+                       String guid = (query.valueString(0));\r
+                       res.add(getNoteResource(guid, withBinary));\r
+               }       \r
+               return res;\r
        }\r
-       \r
-       \r
        // Get all of a note's recognition data by the note guid\r
        public List<Resource> getNoteResourcesRecognition(String noteGuid) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resources_Recognition;\r
-               request.string1 = new String(noteGuid);\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResources;\r
+               if (noteGuid == null)\r
+                       return null;\r
+               boolean check;\r
+               List<Resource> res = new ArrayList<Resource>();\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               check = query.prepare("Select "\r
+                               +"recognitionHash, recognitionSize, recognitionBinary "\r
+                               +" from NoteResources where noteGuid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               query.bindValue(":guid", noteGuid);\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               while (query.next()) {  \r
+                       Resource r = new Resource();            \r
+                       res.add(r);\r
+                       Data rec = new Data();\r
+                       rec.setBodyHash(query.valueString(0).getBytes());\r
+                       String x = new String(query.valueString(1));\r
+                       if (!x.equals("")) {\r
+                               rec.setSize(new Integer(x));\r
+                               rec.setBody(query.valueString(2).getBytes());\r
+                       } else\r
+                               rec.setSize(0);\r
+                       r.setRecognition(rec);\r
+               }       \r
+               return res;\r
        }\r
        \r
+       \r
        // Get a note's recognition data by it's guid.\r
        public Resource getNoteResourceRecognition(String guid) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Note_Resource_Recognition;\r
-               request.string1 = new String(guid);\r
-               Global.dbRunner.addWork(request);       \r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseResource;\r
+               if (guid == null)\r
+                       return null;\r
+               boolean check;\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               check = query.prepare("Select "\r
+                               +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
+                               +" from NoteResources where guid=:guid");\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+                       return null;\r
+               }\r
+               Resource r = null;\r
+               while (query.next()) {\r
+                                                                       \r
+                       r = new Resource();             \r
+                       Data rec = new Data();\r
+                       rec.setBodyHash(query.valueString(0).getBytes());\r
+                       String x = new String(query.valueString(1));\r
+                       if (!x.equals("")) {\r
+                               rec.setSize(new Integer(x));\r
+                               rec.setBody(query.valueString(2).getBytes());\r
+                       } else\r
+                               rec.setSize(0);\r
+                       r.setRecognition(rec);\r
+                       r.setNoteGuid(query.valueString(3));\r
+               }       \r
+               return r;\r
        }\r
+\r
        // Save Note Resource\r
        public void updateNoteResource(Resource r, boolean isDirty) {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Update_Note_Resource;\r
-               request.resource = r.deepCopy();\r
-               request.bool1 = isDirty;\r
-               Global.dbRunner.addWork(request);\r
+               logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r
+               expungeNoteResource(r.getGuid());\r
+               saveNoteResource(r, isDirty);\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.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.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
+               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.exec("commit");\r
+               logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
        }\r
        \r
        // Drop the table\r
        public void reindexAll() {              \r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Reindex_All;\r
-               Global.dbRunner.addWork(request);\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               query.exec("Update NoteResources set indexneeded=true");\r
+               query.exec("commit");\r
        }\r
-       // Count unindexed notes\r
+       // Count attachments\r
        public int getResourceCount() {\r
-               ResourceRequest request = new ResourceRequest();\r
-               request.requestor_id = id;\r
-               request.type = ResourceRequest.Get_Resource_Count;\r
-               Global.dbRunner.addWork(request);\r
-               Global.dbClientWait(id);\r
-               ResourceRequest req = Global.dbRunner.resourceResponse.get(id).copy();\r
-               return req.responseInteger;\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
+               return returnValue;\r
+       }\r
+       //\r
+       // Count unindexed notes\r
+       public int getUnindexedCount() {\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
+               return returnValue;\r
+       }\r
+       \r
+       //********************************************\r
+       //** Utility Functions\r
+       //********************************************\r
+       // Convert a byte array to a hex string\r
+       private static String byteArrayToHexString(byte data[]) {\r
+               StringBuffer buf = new StringBuffer();\r
+           for (byte element : data) {\r
+               int halfbyte = (element >>> 4) & 0x0F;\r
+               int two_halfs = 0;\r
+               do {\r
+                       if ((0 <= halfbyte) && (halfbyte <= 9))\r
+                              buf.append((char) ('0' + halfbyte));\r
+                          else\r
+                               buf.append((char) ('a' + (halfbyte - 10)));\r
+                       halfbyte = element & 0x0F;\r
+               } while(two_halfs++ < 1);\r
+           }\r
+           return buf.toString();              \r
+       }\r
+\r
+       \r
+       private String stringValue(Object value) {\r
+               if (value != null && value.toString() != null) \r
+                       return value.toString();\r
+               else\r
+                       return null;\r
+       }\r
+       \r
+       private  boolean booleanValue(Object value, boolean unknown) {\r
+               if (value != null && value.toString() != null) {\r
+                       try {\r
+                               if ((Integer)value > 0)\r
+                                       return true;\r
+                               else\r
+                                       return false;\r
+                       } catch (java.lang.ClassCastException e) {\r
+                               try {\r
+                                       String stringValue = (String)value;\r
+                                       if (stringValue.equalsIgnoreCase("true"))\r
+                                               return true;\r
+                                       else \r
+                                               return false;\r
+                               } catch (java.lang.ClassCastException e1) { \r
+                                       return unknown;\r
+                               }\r
+                       }\r
+               }\r
+               else\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
+       // Get note source\r
+       public List<String> getDistinctNoteGuids() {\r
+               logger.log(logger.HIGH, "Entering NoteResourceTable.getDistinctNoteGuids()");\r
+               List<String> guids = new ArrayList<String>();\r
+               NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
+               query.exec("select distinct noteguid from noteresources");\r
+               if (query.next()) {\r
+                       guids.add(query.valueString(0));\r
+               }\r
+               logger.log(logger.HIGH, "Leaving NoteResourceTable.getDistinctNoteGuids()");\r
+               return guids;\r
        }\r
 }\r