Resync Linked & shared notebook tables.

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

diff --git a/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java b/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java
index 7b21a17..937dca2 100644 (file)
--- a/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java
+++ b/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java
@@ -42,13 +42,16 @@ public class LinkedNotebookTable {
        public void createTable() {\r
                NSqlQuery query = new NSqlQuery(db.getConnection());\r
         logger.log(logger.HIGH, "Creating table LinkedNotebook...");\r
        public void createTable() {\r
                NSqlQuery query = new NSqlQuery(db.getConnection());\r
         logger.log(logger.HIGH, "Creating table LinkedNotebook...");\r

-        if (!query.exec("Create table LinkedNotebook (id long primary key, " +\r
+        if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " +\r

                        "shareName VarChar, " +\r
                        "username VarChar, "+\r
                        "shardID VarChar, " +\r
                        "shareKey VarChar, " +\r
                        "shareName VarChar, " +\r
                        "username VarChar, "+\r
                        "shardID VarChar, " +\r
                        "shareKey VarChar, " +\r

-                       "url VarChar, " +\r
-                       "icon blob, " +\r
+                       "uri VarChar, " +\r
+                       "updateSequenceNumber Long," +\r
+                       "lastSequenceNumber Integer," +\r
+                       "lastSequenceDate Long," +\r
+                       "notebookGuid VarChar," +\r

                        "isDirty boolean)"))                            \r
                logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");   \r
        }\r
                        "isDirty boolean)"))                            \r
                logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");   \r
        }\r


@@ -58,21 +61,24 @@ public class LinkedNotebookTable {
                query.exec("Drop table LinkedNotebook");\r
        }\r
        // Save an individual notebook\r
                query.exec("Drop table LinkedNotebook");\r
        }\r
        // Save an individual notebook\r

-       public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
+       public void addNotebook(LinkedNotebook tempNotebook,  boolean isDirty) {\r

                boolean check;\r
                \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                boolean check;\r
                \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r

-               check = query.prepare("Insert Into LinkedNotebook (id, shareName, username,  "\r
-                               +"shardId, shareKey, url, isDirty) "   \r
+               check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username,  "\r
+                               +"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, "\r
+                               + "lastSequenceDate, notebookGuid) "   \r

                                + " Values("\r
                                + " Values("\r

-                               +":id, :shareName, :username, "\r
-                               +":shardId, :shareKey, :url, :isDirty)");\r
-               query.bindValue(":id", tempNotebook.getId());\r
+                               +":guid, :shareName, :username, "\r
+                               +":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)");\r
+               query.bindValue(":guid", tempNotebook.getGuid());\r

                query.bindValue(":shareName", tempNotebook.getShareName());\r
                query.bindValue(":username", tempNotebook.getUsername());\r
                query.bindValue(":shardId", tempNotebook.getShardId());\r
                query.bindValue(":shareKey", tempNotebook.getShareKey());\r
                query.bindValue(":shareName", tempNotebook.getShareName());\r
                query.bindValue(":username", tempNotebook.getUsername());\r
                query.bindValue(":shardId", tempNotebook.getShardId());\r
                query.bindValue(":shareKey", tempNotebook.getShareKey());\r

-               query.bindValue(":url", tempNotebook.getUri());\r
+               query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
+               query.bindValue(":uri", tempNotebook.getUri());\r
+               query.bindValue(":notebookGuid", "");\r

                \r
                if (isDirty)\r
                        query.bindValue(":isDirty", true);\r
                \r
                if (isDirty)\r
                        query.bindValue(":isDirty", true);\r


@@ -83,35 +89,49 @@ public class LinkedNotebookTable {
                if (!check) {\r
                        logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");\r
                        logger.log(logger.MEDIUM, query.lastError().toString());\r
                if (!check) {\r
                        logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");\r
                        logger.log(logger.MEDIUM, query.lastError().toString());\r

+                       return;\r

                }\r
        }\r
        // Delete the notebook based on a guid\r
                }\r
        }\r
        // Delete the notebook based on a guid\r

-       public void expungeNotebook(long id, boolean needsSync) {\r
+       public void expungeNotebook(String id, boolean needsSync) {\r

                boolean check;\r
                boolean check;\r

+               \r
+               // First, delete any tags associated with this notebook\r
+               String notebookGuid = getNotebookGuid(id);\r
+               db.getNotebookTable().deleteLinkedTags(notebookGuid);\r
+               \r
+               // Now, delete any notes associated with this notebook\r
+               List<String> notes = db.getNoteTable().getNotesByNotebook(notebookGuid);\r
+               for (int i=0; i<notes.size(); i++) {\r
+                       db.getNoteTable().expungeNote(notes.get(i), true, needsSync);\r
+               }\r
+               \r
+               // Delete the notebook record\r
+               db.getNotebookTable().expungeNotebook(notebookGuid, needsSync);\r
+               \r
+               // Finally, delete the linked notebook object itself\r

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

-\r

                check = query.prepare("delete from LinkedNotebook "\r
                check = query.prepare("delete from LinkedNotebook "\r

-                               +"where id=:id");\r
+                               +"where guid=:guid");\r

                if (!check) {\r
                        logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");\r
                        logger.log(logger.EXTREME, query.lastError().toString());\r
                }\r
                if (!check) {\r
                        logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");\r
                        logger.log(logger.EXTREME, query.lastError().toString());\r
                }\r

-               query.bindValue(":id", id);\r
+               query.bindValue(":guid", id);\r

                check = query.exec();\r
                if (!check) \r
                        logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");\r
                \r
                check = query.exec();\r
                if (!check) \r
                        logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");\r
                \r

-               // Signal the parent that work needs to be done\r

                if  (needsSync) {\r
                        DeletedTable deletedTable = new DeletedTable(logger, db);\r
                        deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");\r
                }\r
        }\r
        // Check if a notebook exists\r
                if  (needsSync) {\r
                        DeletedTable deletedTable = new DeletedTable(logger, db);\r
                        deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");\r
                }\r
        }\r
        // Check if a notebook exists\r

-       public boolean exists(long id) {\r
+       public boolean exists(String id) {\r

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

-               boolean check = query.prepare("Select id from linkednotebook where id=:id");\r
-               query.bindValue(":id", id);\r
+               boolean check = query.prepare("Select guid from linkednotebook where guid=:guid");\r
+               query.bindValue(":guid", id);\r

                check = query.exec();\r
                if (!check) {\r
                        logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");\r
                check = query.exec();\r
                if (!check) {\r
                        logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");\r


@@ -124,20 +144,23 @@ public class LinkedNotebookTable {
        // Update a notebook\r
        public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
                boolean check;\r
        // Update a notebook\r
        public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
                boolean check;\r

-               if (!exists(tempNotebook.getId())) {\r
+               if (!exists(tempNotebook.getGuid())) {\r

                        addNotebook(tempNotebook, isDirty);\r
                        return;\r
                }\r
                \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                        addNotebook(tempNotebook, isDirty);\r
                        return;\r
                }\r
                \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r

-               check = query.prepare("Update LinkedNotebook set id=:id, shareName=:shareName, " +\r
-                               "username=:username, shardID=:shardID, shareKey=:shareKey, url=:url, isDirty=:isDirty");\r
-               query.bindValue(":id", tempNotebook.getId());\r
+               check = query.prepare("Update LinkedNotebook set guid=:guid, shareName=:shareName, " +\r
+                               "username=:username, shardID=:shardID, shareKey=:shareKey, uri=:uri, updateSequenceNumber=:usn, isDirty=:isDirty "+\r
+                               "where guid=:keyGuid");\r
+               query.bindValue(":guid", tempNotebook.getGuid());\r
+               query.bindValue(":keyGuid", tempNotebook.getGuid());\r

                query.bindValue(":shareName", tempNotebook.getShareName());\r
                query.bindValue(":username", tempNotebook.getUsername());\r
                query.bindValue(":shardID", tempNotebook.getShardId());\r
                query.bindValue(":shareKey", tempNotebook.getShareKey());\r
                query.bindValue(":shareName", tempNotebook.getShareName());\r
                query.bindValue(":username", tempNotebook.getUsername());\r
                query.bindValue(":shardID", tempNotebook.getShardId());\r
                query.bindValue(":shareKey", tempNotebook.getShareKey());\r

-               query.bindValue(":url", tempNotebook.getUri());\r
+               query.bindValue(":uri", tempNotebook.getUri());\r
+               query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r

 \r
                query.bindValue(":isDirty", isDirty);\r
                \r
 \r
                query.bindValue(":isDirty", isDirty);\r
                \r


@@ -155,22 +178,124 @@ public class LinkedNotebookTable {
                                        \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                                        \r
                                        \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                                        \r

-               check = query.exec("Select id, shareName, username, shardID, shareKey url, " +\r
+               check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " +\r

                                " from LinkedNotebook");\r
                if (!check)\r
                        logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
                while (query.next()) {\r
                        tempNotebook = new LinkedNotebook();\r
                                " from LinkedNotebook");\r
                if (!check)\r
                        logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
                while (query.next()) {\r
                        tempNotebook = new LinkedNotebook();\r

-                       tempNotebook.setId(query.valueLong(0));\r
-                       tempNotebook.setUsername(query.valueString(1));\r
-                       tempNotebook.setShardId(query.valueString(2));\r
-                       tempNotebook.setShareKey(query.valueString(3));\r
-                       tempNotebook.setUri(query.valueString(4));\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       tempNotebook.setShareName(query.valueString(1));\r
+                       tempNotebook.setUsername(query.valueString(2));\r
+                       tempNotebook.setShardId(query.valueString(3));\r
+                       tempNotebook.setShareKey(query.valueString(4));\r
+                       tempNotebook.setUri(query.valueString(5));\r

 \r
                        index.add(tempNotebook); \r
                }       \r
                return index;\r
        }                       \r
 \r
                        index.add(tempNotebook); \r
                }       \r
                return index;\r
        }                       \r

+       // Load notebooks from the database\r
+       public LinkedNotebook getNotebook(String guid) {\r
+               LinkedNotebook tempNotebook;\r
+               boolean check;\r
+                                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
+                               " from LinkedNotebook where guid=:guid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               while (query.next()) {\r
+                       tempNotebook = new LinkedNotebook();\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       tempNotebook.setShareName(query.valueString(1));\r
+                       tempNotebook.setUsername(query.valueString(2));\r
+                       tempNotebook.setShardId(query.valueString(3));\r
+                       tempNotebook.setShareKey(query.valueString(4));\r
+                       tempNotebook.setUri(query.valueString(5));\r
+                       return tempNotebook;\r
+               }       \r
+               return null;\r
+       }       \r
+       // Load notebooks from the database\r
+       public LinkedNotebook getByNotebookGuid(String guid) {\r
+               LinkedNotebook tempNotebook;\r
+               boolean check;\r
+                                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
+                               " from LinkedNotebook where notebookguid=:guid");\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
+               query.bindValue(":guid", guid);\r
+               query.exec();\r
+               while (query.next()) {\r
+                       tempNotebook = new LinkedNotebook();\r
+                       tempNotebook.setGuid(query.valueString(0));\r
+                       tempNotebook.setShareName(query.valueString(1));\r
+                       tempNotebook.setUsername(query.valueString(2));\r
+                       tempNotebook.setShardId(query.valueString(3));\r
+                       tempNotebook.setShareKey(query.valueString(4));\r
+                       tempNotebook.setUri(query.valueString(5));\r
+                       return tempNotebook;\r
+               }       \r
+               return null;\r
+       }\r
+       // Get last sequence date for the notebook\r
+       public long getLastSequenceDate(String guid) {\r
+               boolean check;\r
+                                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select LastSequenceDate " \r
+                               +"from LinkedNotebook where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve last sequence date has failed.");\r
+               if (query.next()) {\r
+                       return query.valueLong(0);\r
+               }       \r
+               return 0;\r
+       }                       \r
+       // Get a guid by uri\r
+       public String getNotebookGuid(String guid) {\r
+               boolean check;\r
+                                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select notebookGuid " \r
+                               +"from LinkedNotebook where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve of notebookguid by guidhas failed.");\r
+               if (query.next()) {\r
+                       return query.valueString(0);\r
+               }       \r
+               return null;\r
+       }       \r
+       // get last sequence numeber\r
+       public int getLastSequenceNumber(String guid) {\r
+               boolean check;\r
+                                       \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.prepare("Select LastSequenceNumber " \r
+                               +"from LinkedNotebook where guid=:guid");\r
+               query.bindValue(":guid", guid);\r
+               check = query.exec();\r
+               if (!check)\r
+                       logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
+               while (query.next()) {\r
+                       return query.valueInteger(0);\r
+               }       \r
+               return 0;\r
+       }                       \r

 \r
        // does a record exist?\r
        public String findNotebookByShareName(String name) {\r
 \r
        // does a record exist?\r
        public String findNotebookByShareName(String name) {\r


@@ -187,6 +312,66 @@ public class LinkedNotebookTable {
                return val;\r
        }\r
 \r
                return val;\r
        }\r
 \r

+       // does a record exist?\r
+       public String setNotebookGuid(String shareKey, String notebookGuid) {\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update LinkedNotebook set notebookGuid=:notebookGuid where shareKey=:shareKey");\r
+               query.bindValue(":notebookGuid", notebookGuid);\r
+               query.bindValue(":shareKey", shareKey);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
+               String val = null;\r
+               if (query.next())\r
+                       val = query.valueString(0);\r
+               return val;\r
+       }\r
+       // set sync date\r
+       public String setLastSequenceDate(String guid, long date) {\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update LinkedNotebook set lastsequencedate=:date where guid=:guid");\r
+               query.bindValue(":date", date);\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
+               String val = null;\r
+               if (query.next())\r
+                       val = query.valueString(0);\r
+               return val;\r
+       }\r
+       // set sync number\r
+       public String setLastSequenceNumber(String guid, int number) {\r
+               \r
+               NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               \r
+               query.prepare("Update LinkedNotebook set lastsequencenumber=:number where guid=:guid");\r
+               query.bindValue(":number", number);\r
+               query.bindValue(":guid", guid);\r
+               if (!query.exec())\r
+                       logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
+               String val = null;\r
+               if (query.next())\r
+                       val = query.valueString(0);\r
+               return val;\r
+       }\r
+       \r
+       // Get a list of linked notebooks that need to be updated\r
+       public List<String> getDirtyGuids() {\r
+               List<String> index = new ArrayList<String>();\r
+               boolean check;  \r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+                                       \r
+               check = query.exec("Select guid from LinkedNotebook where isDirty = true");\r
+               if (!check) \r
+                       logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve has failed in getdirtyIds.");\r
+               while (query.next()) {\r
+                       index.add(query.valueString(0));\r
+               }       \r
+               return index;   \r
+       }\r

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