Added 0.95 database structures

diff --git a/src/cx/fbn/nevernote/sql/DatabaseConnection.java b/src/cx/fbn/nevernote/sql/DatabaseConnection.java
index 2c32abd..249b41e 100644 (file)
--- a/src/cx/fbn/nevernote/sql/DatabaseConnection.java
+++ b/src/cx/fbn/nevernote/sql/DatabaseConnection.java
@@ -40,6 +40,8 @@ public class DatabaseConnection {
        private SavedSearchTable                        searchTable;
        private WatchFolderTable                        watchFolderTable;
        private InvalidXMLTable                         invalidXMLTable;
+       private LinkedNotebookTable                     linkedNotebookTable;
+       private SharedNotebookTable                     sharedNotebookTable;
        private SyncTable                                       syncTable;
        private final ApplicationLogger         logger;
        private Connection                                      conn;
@@ -61,6 +63,8 @@ public class DatabaseConnection {
                invalidXMLTable = new InvalidXMLTable(logger, this);
                wordsTable = new WordsTable(logger, this);
                syncTable = new SyncTable(logger, this);
+               linkedNotebookTable = new LinkedNotebookTable(logger, this);
+               sharedNotebookTable = new SharedNotebookTable(logger, this);
        }
        
        
@@ -135,6 +139,47 @@ public class DatabaseConnection {
                        version = "0.86";
                        Global.setDatabaseVersion(version);
                } 
+               if (version.equals("0.86")) {
+/*                     sharedNotebookTable.dropTable();
+                       linkedNotebookTable.dropTable();
+                       
+                       executeSql("alter table notebook drop column publishingUri");
+                       executeSql("alter table notebook drop column publishingOrder");
+                       executeSql("alter table notebook drop column publishingAscending");
+                       executeSql("alter table notebook drop column publishingPublicDescription");
+                       executeSql("alter table notebook drop column stack");
+                       executeSql("alter table notebook drop column icon");
+                       executeSql("alter table tag drop column icon");
+                       executeSql("alter table SavedSearch drop column icon");
+                       
+                       executeSql("drop index NOTE_THUMBNAIL_INDEX;");
+                       executeSql("drop index NOTE_EXPUNGED_INDEX;");
+                       executeSql("drop index NOTE_DUEDATE_INDEX;");
+                       executeSql("drop index RESOURCES_GUID_INDEX;");
+*/             
+                       executeSql("alter table notebook add column publishingUri VarChar");
+                       executeSql("alter table notebook add column publishingOrder Integer");
+                       executeSql("alter table notebook add column publishingAscending VarChar");
+                       executeSql("alter table notebook add column publishingPublicDescription varchar");
+                       executeSql("alter table notebook add column stack varchar");
+                       executeSql("alter table notebook add column icon blob");
+                       executeSql("alter table tag add column icon blob");
+                       executeSql("alter table SavedSearch add column icon blob");
+
+                       executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
+                       executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
+                       executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
+                       executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
+//                     executeSql("update note set thumbnailneeded=true, thumbnail=null;");
+                       
+                       sharedNotebookTable.createTable();
+                       linkedNotebookTable.createTable();
+                       
+                       version = "0.95";
+                       executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
+                       executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
+                       Global.setDatabaseVersion(version);
+               } 
        }
        
        public void executeSql(String sql) {
@@ -146,6 +191,9 @@ public class DatabaseConnection {
                if (!Global.getDatabaseVersion().equals("0.86")) {
                        upgradeDb(Global.getDatabaseVersion());
                }
+               if (!Global.getDatabaseVersion().equals("0.95")) {
+                       upgradeDb(Global.getDatabaseVersion());
+               }
        }
        
 
@@ -156,6 +204,7 @@ public class DatabaseConnection {
        
        public void createTables() {
                Global.setDatabaseVersion("0.85");
+//             Global.setDatabaseVersion("0.95");
                Global.setAutomaticLogin(false);
                Global.saveCurrentNoteGuid("");
                Global.saveUploadAmount(0);
@@ -206,4 +255,10 @@ public class DatabaseConnection {
        public SyncTable getSyncTable() {
                return syncTable;
        }
+       public LinkedNotebookTable getLinkedNotebookTable() {
+               return linkedNotebookTable;
+       }
+       public SharedNotebookTable getSharedNotebookTable() {
+               return sharedNotebookTable;
+       }
 }

diff --git a/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java b/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java
index d82d138..7b21a17 100644 (file)
--- a/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java
+++ b/src/cx/fbn/nevernote/sql/LinkedNotebookTable.java
@@ -20,7 +20,6 @@
 \r
 package cx.fbn.nevernote.sql;\r
 \r
-import java.text.SimpleDateFormat;\r
 import java.util.ArrayList;\r
 import java.util.List;\r
 \r
@@ -62,7 +61,6 @@ public class LinkedNotebookTable {
        public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
                boolean check;\r
                \r
-               SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                check = query.prepare("Insert Into LinkedNotebook (id, shareName, username,  "\r
                                +"shardId, shareKey, url, isDirty) "   \r
@@ -109,11 +107,27 @@ public class LinkedNotebookTable {
                        deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");\r
                }\r
        }\r
+       // Check if a notebook exists\r
+       public boolean exists(long id) {\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
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError().toString());\r
+               }\r
+               if (query.next())\r
+                       return true;\r
+               return false;\r
+       }\r
        // Update a notebook\r
        public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
                boolean check;\r
-               \r
-               SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+               if (!exists(tempNotebook.getId())) {\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

diff --git a/src/cx/fbn/nevernote/sql/SharedNotebookTable.java b/src/cx/fbn/nevernote/sql/SharedNotebookTable.java
index 34691f7..e6aab91 100644 (file)
--- a/src/cx/fbn/nevernote/sql/SharedNotebookTable.java
+++ b/src/cx/fbn/nevernote/sql/SharedNotebookTable.java
@@ -70,11 +70,11 @@ public class SharedNotebookTable {
                SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                check = query.prepare("Insert Into SharedNotebook (id, userid, notebookGuid, email,  "\r
-                               +"notebookModifiable, requireLogin, serviceCreated, shareKey, username) "   \r
+                               +"notebookModifiable, requireLogin, serviceCreated, shareKey, username, isDirty) "   \r
                                + " Values("\r
                                +":id, :userid, :notebookGuid, :email, "\r
                                +":notebookModifiable, :requireLogin, :serviceCreated, "\r
-                               +":shareKey, :username)");\r
+                               +":shareKey, :username, :isDirty)");\r
                query.bindValue(":id", tempNotebook.getId());\r
                query.bindValue(":userid", tempNotebook.getUserId());\r
                query.bindValue(":notebookGuid", tempNotebook.getNotebookGuid());\r
@@ -99,7 +99,21 @@ public class SharedNotebookTable {
                        logger.log(logger.MEDIUM, query.lastError().toString());\r
                }\r
        }\r
-       // Delete the notebook based on a guid\r
+       // Check if a notebook exists\r
+       public boolean exists(long id) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               boolean check = query.prepare("Select id from sharednotebook where id=:id");\r
+               query.bindValue(":id", id);\r
+               check = query.exec();\r
+               if (!check) {\r
+                       logger.log(logger.MEDIUM, "SharedNotebook Table exists check failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError().toString());\r
+               }\r
+               if (query.next())\r
+                       return true;\r
+               return false;\r
+       }\r
+       // Delete the notebook based on a id\r
        public void expungeNotebook(long id, boolean needsSync) {\r
                boolean check;\r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
@@ -124,12 +138,16 @@ public class SharedNotebookTable {
        // Update a notebook\r
        public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
                boolean check;\r
+               if (!exists(tempNotebook.getId())) {\r
+                       addNotebook(tempNotebook, isDirty);\r
+                       return;\r
+               }\r
                \r
                SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
                \r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r
                check = query.prepare("Update SharedNotebook set id=:id, userid=:userid, notebookGuid=:notebook, "\r
-                               + "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:created "\r
+                               + "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:created, "\r
                                + "shareKey=:shareKey, username=:username, isDirty=:isdirty");\r
                query.bindValue(":id", tempNotebook.getId());\r
                query.bindValue(":userid", tempNotebook.getUserId());\r

diff --git a/src/cx/fbn/nevernote/sql/SyncTable.java b/src/cx/fbn/nevernote/sql/SyncTable.java
index 7812054..092609b 100644 (file)
--- a/src/cx/fbn/nevernote/sql/SyncTable.java
+++ b/src/cx/fbn/nevernote/sql/SyncTable.java
@@ -60,6 +60,16 @@ public class SyncTable {
                        logger.log(logger.MEDIUM, query.lastError());\r
                }\r
        }\r
+       // Add an item to the table\r
+       public void deleteRecord(String key) {\r
+        NSqlQuery query = new NSqlQuery(db.getConnection());\r
+               query.prepare("Delete From Sync where key=:key");\r
+               query.bindValue(":key", key);\r
+               if (!query.exec()) {\r
+                       logger.log(logger.MEDIUM, "Delete from Sync failed.");\r
+                       logger.log(logger.MEDIUM, query.lastError());\r
+               }\r
+       }\r
        // Set a key field\r
        public String getRecord(String key) {\r
         NSqlQuery query = new NSqlQuery(db.getConnection());\r