X-Git-Url: http://git.sourceforge.jp/view?p=neighbornote%2FNeighborNote.git;a=blobdiff_plain;f=src%2Fcx%2Ffbn%2Fnevernote%2Fsql%2FDatabaseConnection.java;h=72caf26797eb61caeacfb7b2c1cbe6fcf430529b;hp=44c0cc6ee55d77436f4d74744d6a7d50bc29d296;hb=4f64dbba0a56c86271272d9809a5df97bd955815;hpb=a0f63546a4a60e5b22d869cc194092bc25fc1aa6 diff --git a/src/cx/fbn/nevernote/sql/DatabaseConnection.java b/src/cx/fbn/nevernote/sql/DatabaseConnection.java index 44c0cc6..72caf26 100644 --- a/src/cx/fbn/nevernote/sql/DatabaseConnection.java +++ b/src/cx/fbn/nevernote/sql/DatabaseConnection.java @@ -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,60 +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")) { -/* 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"); + 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) { @@ -197,6 +248,9 @@ public class DatabaseConnection { if (!Global.getDatabaseVersion().equals("0.95")) { upgradeDb(Global.getDatabaseVersion()); } + if (!Global.getDatabaseVersion().equals("0.97")) { + upgradeDb(Global.getDatabaseVersion()); + } } @@ -207,26 +261,57 @@ public class DatabaseConnection { public void createTables() { Global.setDatabaseVersion("0.85"); -// Global.setDatabaseVersion("0.95"); Global.setAutomaticLogin(false); Global.saveCurrentNoteGuid(""); Global.saveUploadAmount(0); getTagTable().createTable(); - notebookTable.createTable(); + notebookTable.createTable(true); noteTable.createTable(); deletedTable.createTable(); searchTable.createTable(); watchFolderTable.createTable(); invalidXMLTable.createTable(); - wordsTable.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; + } //*************************************************************** //* Table get methods @@ -264,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; + } }