OSDN Git Service

Resync Linked & shared notebook tables.
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / LinkedNotebookTable.java
index b5a32bb..937dca2 100644 (file)
@@ -49,9 +49,9 @@ public class LinkedNotebookTable {
                        "shareKey VarChar, " +\r
                        "uri VarChar, " +\r
                        "updateSequenceNumber Long," +\r
-                       "lastSequenceNumber Long," +\r
-                       "lastSequenceDate timestamp," +\r
-                       "icon blob, " +\r
+                       "lastSequenceNumber Integer," +\r
+                       "lastSequenceDate Long," +\r
+                       "notebookGuid VarChar," +\r
                        "isDirty boolean)"))                            \r
                logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!");   \r
        }\r
@@ -61,16 +61,16 @@ public class LinkedNotebookTable {
                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
                check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username,  "\r
                                +"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, "\r
-                               + "lastSequenceDate) "   \r
+                               + "lastSequenceDate, notebookGuid) "   \r
                                + " Values("\r
                                +":guid, :shareName, :username, "\r
-                               +":shardId, :shareKey, :uri,:usn, :isDirty, 0, '1969-12-31 19.00.00')");\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
@@ -78,6 +78,7 @@ public class LinkedNotebookTable {
                query.bindValue(":shareKey", tempNotebook.getShareKey());\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
@@ -88,13 +89,28 @@ public class LinkedNotebookTable {
                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
        public void expungeNotebook(String id, boolean needsSync) {\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
-\r
                check = query.prepare("delete from LinkedNotebook "\r
                                +"where guid=:guid");\r
                if (!check) {\r
@@ -106,7 +122,6 @@ public class LinkedNotebookTable {
                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
@@ -136,8 +151,10 @@ public class LinkedNotebookTable {
                \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\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
+                               "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
@@ -161,22 +178,124 @@ public class LinkedNotebookTable {
                                        \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                                        \r
-               check = query.exec("Select guid, shareName, username, shardID, shareKey uri, " +\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
                        tempNotebook.setGuid(query.valueString(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.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
+       // 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
@@ -193,6 +312,51 @@ public class LinkedNotebookTable {
                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
@@ -200,7 +364,7 @@ public class LinkedNotebookTable {
                boolean check;  \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                                        \r
-               check = query.exec("Select id from LinkedNotebook where isDirty = true");\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