OSDN Git Service

NixNoteのUpgradeDbメソッドを不要部分を削除。
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / DatabaseConnection.java
index b754828..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'').
 package cx.fbn.nevernote.sql;
 
 import java.io.File;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
 
 import cx.fbn.nevernote.Global;
-import cx.fbn.nevernote.sql.requests.DatabaseRequest;
+import cx.fbn.nevernote.sql.driver.NSqlQuery;
+import cx.fbn.nevernote.utilities.ApplicationLogger;
 
 
 public class DatabaseConnection {
        // Table helpers
-       private final WordsTable                                wordsTable;
-       private final TagTable                                  tagTable;
-       private final NotebookTable                             notebookTable;
-       private final NoteTable                                 noteTable;
-       private final DeletedTable                              deletedTable;
-       private final SavedSearchTable                  searchTable;
-       private final WatchFolderTable                  watchFolderTable;
-       private final InvalidXMLTable                   invalidXMLTable;
-       private final SyncTable                                 syncTable;
+       private WordsTable                                      wordsTable;
+       private TagTable                                        tagTable;
+       private NotebookTable                           notebookTable;
+       private NoteTable                                       noteTable;
+       private DeletedTable                            deletedTable;
+       private SavedSearchTable                        searchTable;
+       private WatchFolderTable                        watchFolderTable;
+       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;
 
+       // 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;
+               this.throttle = throttle;
+               // ICHANGED burlを追加
+               dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
+       }
        
-       public DatabaseConnection(int i) {
-               id = i;
-               tagTable = new TagTable(id);
-               notebookTable = new NotebookTable(id);
-               noteTable = new NoteTable(id);
-               deletedTable = new DeletedTable(id);
-               searchTable = new SavedSearchTable(id);
-               watchFolderTable = new WatchFolderTable(id);
-               wordsTable = new WordsTable(id);
-               invalidXMLTable = new InvalidXMLTable(id);
-               syncTable = new SyncTable(id);
+       private void setupTables() {
+               tagTable = new TagTable(logger, this);
+               notebookTable = new NotebookTable(logger, this);
+               noteTable = new NoteTable(logger, this);
+               deletedTable = new DeletedTable(logger, this);
+               searchTable = new SavedSearchTable(logger, this);       
+               watchFolderTable = new WatchFolderTable(logger, this);
+               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);
+               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);
+               
        }
        
        
+       // Compact the database
+       public void compactDatabase() {
+               
+       }
+       
        // Initialize the database connection
-       public void dbSetup() {
-               // NFC FIXME: should be parameterized with databaseName like in RDatabaseConnection?
-               File f = Global.getFileManager().getDbDirFile("NeverNote.h2.db");
+       // 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);
+
+               
+               try {
+                       Class.forName("org.h2.Driver");
+               } catch (ClassNotFoundException e1) {
+                       e1.printStackTrace();
+                       System.exit(16);
+               }
+               
+//             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 {
+                       
+                       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+";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();
+                       return;
+               }
                
                // If it doesn't exist and we are the main thread, then we need to create stuff.
-               if (!dbExists && id  == 0)  {
+               if (!dbExists)  {
                        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);
        }
        
        
        public void dbShutdown() {
-               DatabaseRequest req = new DatabaseRequest();
-               req.type = DatabaseRequest.Shutdown;
-               Global.dbRunner.addWork(req);
+               logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
+               try {
+                       conn.close();
+               } catch (SQLException e) {
+                       e.printStackTrace();
+               }
+               logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
        }
        
        public void upgradeDb(String version) {
-               if (version.equals("0.85")) {
-                       DatabaseRequest req = new DatabaseRequest();
-                       req.type = DatabaseRequest.Execute_Sql;
-                       req.string1 = new String("alter table note add column titleColor integer");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.type = DatabaseRequest.Execute_Sql;
-                       req.string1 = new String("update note set titlecolor=-1");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.type = DatabaseRequest.Execute_Sql;
-                       req.string1 = new String("alter table note add column thumbnail blob");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("alter table note add column thumbnailneeded boolean");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("Update note set thumbnailneeded = true;");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       req.string1 = new String("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
-                       Global.dbRunner.addWork(req);
-                       Global.dbClientWait(id);
-                       version = "0.86";
-                       Global.setDatabaseVersion(version);
-               } 
+               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("alter table notebook add column WIDE_SORT_ORDER integer");
+                       executeSql("update notebook set WIDE_SORT_ORDER = -1");
+                       
+                       executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
+                       executeSql("update notebook set WIDE_SORT_COLUMN = -1");
+                       
+                       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) {
+               NSqlQuery query = new NSqlQuery(conn);
+               query.exec(sql);        
        }
        
        public void checkDatabaseVersion() {
-               // NFC FIXME: this needs to read the existing version number from a table in the DB
                if (!Global.getDatabaseVersion().equals("0.86")) {
                        upgradeDb(Global.getDatabaseVersion());
                }
+               if (!Global.getDatabaseVersion().equals("0.95")) {
+                       upgradeDb(Global.getDatabaseVersion());
+               }
+               if (!Global.getDatabaseVersion().equals("0.97")) {
+                       upgradeDb(Global.getDatabaseVersion());
+               }
        }
        
-       public void compactDatabase() {
-               DatabaseRequest request = new DatabaseRequest();
-               request.requestor_id = id;
-               request.type = DatabaseRequest.Compact;
-               Global.dbRunner.addWork(request);
-               Global.dbClientWait(id);
-       }
 
        public void backupDatabase(int highSequence, long date) {
-               DatabaseRequest request = new DatabaseRequest();
-               request.requestor_id = id;
-               request.int1 = highSequence;
-               request.long1 = date;
-               request.type = DatabaseRequest.Backup_Database;
-               Global.dbRunner.addWork(request);
-               Global.dbClientWait(id);
+               
        }
        
        
-       private void createTables() {
+       public void createTables() {
                Global.setDatabaseVersion("0.85");
-//             Global.setUpdateSequenceNumber(0);
                Global.setAutomaticLogin(false);
                Global.saveCurrentNoteGuid("");
                Global.saveUploadAmount(0);
                
+               getTagTable().createTable();
+               notebookTable.createTable(true);
+               noteTable.createTable();
+               deletedTable.createTable();             
+               searchTable.createTable();
+               watchFolderTable.createTable();
+               invalidXMLTable.createTable();
+               syncTable.createTable();
+       }
+       
+       public void createIndexTables() {
+               wordsTable.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;
        }
        
        //***************************************************************
@@ -169,4 +343,77 @@ public class DatabaseConnection {
        public SyncTable getSyncTable() {
                return syncTable;
        }
+       public LinkedNotebookTable getLinkedNotebookTable() {
+               return linkedNotebookTable;
+       }
+       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;
+       }
 }