/*
- * 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;
+ private HistoryTable historyTable;
+ private ExcludedTable excludedTable;
+ private StaredTable staredTable;
+
+ private final ApplicationLogger logger;
+ private Connection conn;
+ private Connection indexConn;
+ private Connection resourceConn;
+ private Connection behaviorConn;
+
+ int throttle;
int id;
+ 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;
+ 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);
+ 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");
+ 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();
+ 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);
+ 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'");
}
+
+ // 操作履歴テーブルと除外ノートテーブルとスター付きノートテーブルを作る
+ 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);
+ 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 Boolean");
+ 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 notebook add column readOnly boolean");
+ executeSql("alter table notebook add column linked boolean");
+
+ executeSql("alter table tag add column realname varchar");
+ executeSql("alter table tag add column linked boolean");
+ executeSql("alter table tag add column icon blob");
+ executeSql("alter table tag add column notebookguid varchar");
+ 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 TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
+
+ executeSql("update note set thumbnailneeded=true, thumbnail=null;");
+ executeSql("update notebook set publishingUri='', " +
+ "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
+ "publishingPublicDescription='', linked=false");
+ executeSql("update tag set linked=false, realname='', notebookguid=''");
+
+ sharedNotebookTable.createTable();
+ linkedNotebookTable.createTable();
+ systemIconTable.createTable();
+ inkImagesTable.createTable();
+
+ version = "0.95";
+ executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
+ executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
+ executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
+ executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
+ Global.setDatabaseVersion(version);
+ }
+ if (version.equals("0.95")) {
+ if (dbTableExists("words"))
+ executeSql("Drop table words;");
+ if (dbTableExists("NoteResources"))
+ executeSql("Drop table NoteResources;");
+ }
+ 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 = ''");
+ }
+
+ // Evernoteサムネイルカラムを追加
+ if (!dbTableColumnExists("NOTE", "ENTHUMBNAIL")) {
+ executeSql("alter table note add column enThumbNail Blob");
+ }
+
+ // Apache Luceneを使った日本語検索のためのプレーンテキストノートコンテンツカラムを準備
+ if (!dbTableColumnExists("NOTE", "CONTENTTEXT")) {
+ executeSql("alter table note add column contentText VarChar");
+ executeSql("update note set contentText = ''");
+ NSqlQuery query = new NSqlQuery(conn);
+ query.exec("Select guid, content from Note where contentText = ''");
+ while (query.next()) {
+ String guid = query.valueString(0);
+ String content = query.valueString(1);
+ String contentText = Global.extractPlainText(content);
+ NSqlQuery query2 = new NSqlQuery(conn);
+ query2.prepare("update note set contentText=:contentText where guid=:guid");
+ query2.bindValue(":contentText", contentText);
+ query2.bindValue(":guid", guid);
+ query2.exec();
+ }
+
+ // Apache Luceneを使った全文検索のための準備
+ query.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
+ query.exec("CALL FTL_INIT()");
+
+ Global.rebuildFullTextNoteTarget(this);
+ }
+
+ // Apache Luceneを使った日本語検索のためのプレーンテキストノートリソースカラムを準備
+ NSqlQuery rQuery = new NSqlQuery(resourceConn);
+ rQuery.exec("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='NOTERESOURCES' and COLUMN_NAME='RESOURCETEXT'");
+ if (!rQuery.next()) {
+ rQuery.exec("alter table noteResources add column resourceText VarChar");
+ rQuery.exec("update noteResources set resourceText = ''");
+
+ // Apache Luceneを使った全文検索のための準備
+ rQuery.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
+ rQuery.exec("CALL FTL_INIT()");
+
+ Global.rebuildFullTextResourceTarget(this);
+ }
+
+ // 注意：ここから先でnoteテーブルとnoteResourcesテーブルの構造を変更するな。全文検索ができなくなる。
+ }
+
+ 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();
+ }
+
+ public void createHistoryTables() {
+ historyTable.createTable();
+ }
+
+ public void createExcludedTables() {
+ excludedTable.createTable();
+ }
+
+ public void createStaredTables() {
+ staredTable.createTable();
+ }
+
+ public Connection getConnection() {
+ return conn;
+ }
+ public Connection getIndexConnection() {
+ return indexConn;
+ }
+ public Connection getResourceConnection() {
+ return resourceConn;
+ }
+
+ public Connection getBehaviorConnection() {
+ return behaviorConn;
}
//***************************************************************
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;
+ }
+
+ public HistoryTable getHistoryTable() {
+ return historyTable;
+ }
+
+ public ExcludedTable getExcludedTable() {
+ return excludedTable;
+ }
+
+ 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;
+ }
}
OSDN Git Service
