OSDN Git Service

NixNoteのUpgradeDbメソッドを不要部分を削除。
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / DatabaseConnection.java
index 47a6113..72caf26 100644 (file)
@@ -1,6 +1,7 @@
 /*
- * This file is part of NeverNote 
+ * This file is part of NixNote/NeighborNote 
  * Copyright 2009 Randy Baumgarte
+ * Copyright 2013 Yuki Takahashi
  * 
  * This file may be licensed under the terms of of the
  * GNU General Public License Version 2 (the ``GPL'').
@@ -23,8 +24,6 @@ import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 
-import com.trolltech.qt.sql.QJdbc;
-
 import cx.fbn.nevernote.Global;
 import cx.fbn.nevernote.sql.driver.NSqlQuery;
 import cx.fbn.nevernote.utilities.ApplicationLogger;
@@ -42,15 +41,30 @@ public class DatabaseConnection {
        private InvalidXMLTable                         invalidXMLTable;
        private LinkedNotebookTable                     linkedNotebookTable;
        private SharedNotebookTable                     sharedNotebookTable;
+       private InkImagesTable                          inkImagesTable;
        private SyncTable                                       syncTable;
+       private SystemIconTable                         systemIconTable;
+       // ICHANGED
+       private HistoryTable historyTable;
+       private ExcludedTable excludedTable;
+       private StaredTable staredTable;
+       
        private final ApplicationLogger         logger;
        private Connection                                      conn;
+       private Connection                                      indexConn;
+       private Connection                                      resourceConn;
+       // ICHANGED
+       private Connection behaviorConn;
+       
+       int throttle;
        int id;
 
-       
-       public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
+       // ICHANGED String burlを追加
+       public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String burl, String userid, String password, String cypherPassword, int throttle) {
                logger = l;
-               dbSetup(url, userid, password, cypherPassword);
+               this.throttle = throttle;
+               // ICHANGED burlを追加
+               dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
        }
        
        private void setupTables() {
@@ -65,6 +79,13 @@ public class DatabaseConnection {
                syncTable = new SyncTable(logger, this);
                linkedNotebookTable = new LinkedNotebookTable(logger, this);
                sharedNotebookTable = new SharedNotebookTable(logger, this);
+               systemIconTable = new SystemIconTable(logger, this);
+               inkImagesTable = new InkImagesTable(logger, this);
+               // ICHANGED
+               historyTable = new HistoryTable(logger, this);
+               excludedTable = new ExcludedTable(logger, this);
+               staredTable = new StaredTable(logger, this);
+               
        }
        
        
@@ -74,7 +95,8 @@ public class DatabaseConnection {
        }
        
        // Initialize the database connection
-       public void dbSetup(String url,String userid, String userPassword, String cypherPassword) {
+       // ICHANGED String behaviorUrlを追加
+       public void dbSetup(String url,String indexUrl, String resourceUrl, String behaviorUrl, String userid, String userPassword, String cypherPassword) {
                logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
 
                
@@ -85,23 +107,42 @@ public class DatabaseConnection {
                        System.exit(16);
                }
                
-               QJdbc.initialize();
+//             QJdbc.initialize();
                
                setupTables();
                
                File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
                boolean dbExists = f.exists(); 
+               f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
+               boolean indexDbExists = f.exists(); 
+               f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
+               boolean resourceDbExists = f.exists();
+               // ICHANGED
+               f = Global.getFileManager().getDbDirFile(Global.behaviorDatabaseName + ".h2.db");
+               boolean behaviorDbExists = f.exists();
                
                logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
                
-
+               String passwordString = null;
                try {
-                       String passwordString = null;
+                       
                        if (cypherPassword==null || cypherPassword.trim().equals(""))
                                passwordString = userPassword;
                        else
                                passwordString = cypherPassword+" "+userPassword;
-                       conn = DriverManager.getConnection(url,userid,passwordString);
+//                     conn = DriverManager.getConnection(url,userid,passwordString);
+//                     conn = DriverManager.getConnection(url,userid,passwordString);
+//                     conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
+                       if (throttle == 0) {
+                               conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
+                       } else {
+                               conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
+                       }
+                       indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
+                       resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
+                       // ICHANGED
+                       behaviorConn = DriverManager.getConnection(behaviorUrl, userid, passwordString);
+                       
 //                     conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
                } catch (SQLException e) {
                        e.printStackTrace();
@@ -113,6 +154,45 @@ public class DatabaseConnection {
                        createTables();
                        Global.setAutomaticLogin(false);
                }               
+               if (!resourceDbExists) {
+                       createResourceTables();
+                       if (dbTableExists("NoteResources")) {
+                               // Begin migration of database
+                               NSqlQuery query = new NSqlQuery(resourceConn);
+                               String linkcmd = "create linked table oldnoteresources "+
+                                               "('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
+                               query.exec(linkcmd);
+                               query.exec("insert into noteresources (select * from oldnoteresources)");
+                               query.exec("Drop table oldnoteresources;");
+                               query.exec("Update noteresources set indexneeded='true'");
+                               
+                       }
+               }
+               if (!indexDbExists)  {
+                       createIndexTables();
+                       executeSql("Update note set indexneeded='true'");
+               }
+               
+               // ICHANGED
+               // 操作履歴テーブルと除外ノートテーブルとスター付きノートテーブルを作る
+               if (!behaviorDbExists) {
+                       createHistoryTables();
+                       createExcludedTables();
+                       createStaredTables();
+               }
+               
+               // If we encrypted/decrypted it the last time, we need to reconnect the tables.
+//             if (Global.relinkTables) {
+//                     NSqlQuery query = new NSqlQuery(conn);
+//                     query.exec("Drop table NoteResources;");
+//                     String linkcmd = "create linked table NoteResources "
+//                             +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
+//                     System.out.println(linkcmd);
+//                     query.exec(linkcmd);
+//                     System.err.println(query.lastError());
+//                     Global.relinkTables = false;
+//             }
+               
                
                logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
        }
@@ -129,44 +209,31 @@ public class DatabaseConnection {
        }
        
        public void upgradeDb(String version) {
-               if (version.equals("0.85")) {
-                       executeSql("alter table note add column titleColor integer");
-                       executeSql("alter table note add column thumbnail blob");
-                       executeSql("alter table note add column thumbnailneeded boolean");
-                       executeSql("Update note set thumbnailneeded = true;");
-                       executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
-                       executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
-                       version = "0.86";
-                       Global.setDatabaseVersion(version);
-               } 
-               if (version.equals("0.86")) {
-       
-                       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");
+               if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
+                       executeSql("alter table note add column ORIGINAL_GUID VarChar");
+                       executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
+               }
+               if (!dbTableColumnExists("NOTEBOOK", "NARROW_SORT_ORDER")) {
+                       executeSql("alter table notebook add column NARROW_SORT_ORDER integer");
+                       executeSql("update notebook set NARROW_SORT_ORDER = -1");
 
-                       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;");
-                       executeSql("update notebook set publishingUri='', " +
-                                       "publishingAscending='', stack='', publishingOrder=1, " +
-                                       "publishingPublicDescription=''");
+                       executeSql("alter table notebook add column WIDE_SORT_ORDER integer");
+                       executeSql("update notebook set WIDE_SORT_ORDER = -1");
                        
-                       sharedNotebookTable.createTable();
-                       linkedNotebookTable.createTable();
+                       executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
+                       executeSql("update notebook set WIDE_SORT_COLUMN = -1");
                        
-                       version = "0.95";
-                       executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
-                       executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
-                       Global.setDatabaseVersion(version);
-               } 
+                       executeSql("alter table notebook add column NARROW_SORT_COLUMN integer");
+                       executeSql("update notebook set NARROW_SORT_COLUMN = -1");
+               }
+               if (!dbTableColumnExists("NOTE", "PINNED")) {
+                       executeSql("alter table note add column pinned integer");
+                       executeSql("update note set pinned = 0");
+               }
+               if (!dbTableColumnExists("NOTE", "ATTRIBUTECONTENTCLASS")) {
+                       executeSql("alter table note add column attributeContentClass VarChar");
+                       executeSql("update note set attributeContentClass = ''");
+               }
        }
        
        public void executeSql(String sql) {
@@ -181,6 +248,9 @@ public class DatabaseConnection {
                if (!Global.getDatabaseVersion().equals("0.95")) {
                        upgradeDb(Global.getDatabaseVersion());
                }
+               if (!Global.getDatabaseVersion().equals("0.97")) {
+                       upgradeDb(Global.getDatabaseVersion());
+               }
        }
        
 
@@ -196,19 +266,52 @@ public class DatabaseConnection {
                Global.saveUploadAmount(0);
                
                getTagTable().createTable();
-               notebookTable.createTable();
+               notebookTable.createTable(true);
                noteTable.createTable();
                deletedTable.createTable();             
                searchTable.createTable();
                watchFolderTable.createTable();
                invalidXMLTable.createTable();
+               syncTable.createTable();
+       }
+       
+       public void createIndexTables() {
                wordsTable.createTable();
-               syncTable.createTable();                
+       }
+       
+       public void createResourceTables() {
+               noteTable.noteResourceTable.createTable();
+       }
+       
+       // ICHANGED
+       public void createHistoryTables() {
+               historyTable.createTable();
+       }
+       
+       // ICHANGED
+       public void createExcludedTables() {
+               excludedTable.createTable();
+       }
+       
+       // ICHANGED
+       public void createStaredTables() {
+               staredTable.createTable();
        }
        
        public Connection getConnection() {
                return conn;
        }
+       public Connection getIndexConnection() {
+               return  indexConn;
+       }
+       public Connection getResourceConnection() {
+               return resourceConn;
+       }
+       
+       // ICHANGED
+       public Connection getBehaviorConnection() {
+               return behaviorConn;
+       }
        
        //***************************************************************
        //* Table get methods
@@ -246,4 +349,71 @@ public class DatabaseConnection {
        public SharedNotebookTable getSharedNotebookTable() {
                return sharedNotebookTable;
        }
+       public SystemIconTable getSystemIconTable() {
+               return systemIconTable;
+       }
+       public InkImagesTable getInkImagesTable() {
+               return inkImagesTable;
+       }
+       
+       // ICHANGED
+       public HistoryTable getHistoryTable() {
+               return historyTable;
+       }
+       
+       // ICHANGED
+       public ExcludedTable getExcludedTable() {
+               return excludedTable;
+       }
+       
+       // ICHANGED
+       public StaredTable getStaredTable() {
+               return staredTable;
+       }
+
+       //****************************************************************
+       //* Begin/End transactions
+       //****************************************************************
+       public void beginTransaction() {
+               commitTransaction();
+        NSqlQuery query = new NSqlQuery(getConnection());                                                      
+               if (!query.exec("Begin Transaction"))
+                       logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
+
+       }
+       public void commitTransaction() {
+        NSqlQuery query = new NSqlQuery(getConnection());
+                                                       
+               if (!query.exec("Commit"))
+                       logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
+       }
+
+       //****************************************************************
+       //* Check if a table exists
+       //****************************************************************
+       public boolean dbTableExists(String name) {
+        NSqlQuery query = new NSqlQuery(getConnection());
+        query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
+        query.bindValue(":name", name.toUpperCase());
+        query.exec();
+        if (query.next())
+               return true;
+        else
+               return false;
+       }
+       
+       //****************************************************************
+       //* Check if a row in a table exists
+       //****************************************************************
+       public boolean dbTableColumnExists(String tableName, String columnName) {
+        NSqlQuery query = new NSqlQuery(getConnection());
+        query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
+        query.bindValue(":name", tableName.toUpperCase());
+        query.bindValue(":column", columnName);
+        query.exec();
+        if (query.next())
+               return true;
+        else
+               return false;
+       }
 }