X-Git-Url: http://git.sourceforge.jp/view?p=neighbornote%2FNeighborNote.git;a=blobdiff_plain;f=src%2Fcx%2Ffbn%2Fnevernote%2Fsql%2FDatabaseConnection.java;h=deefdc8257cd2af1fa9f5bb40f03bd6ebec572d4;hp=7f3ebdc807cd8d7bbc3526420b423c51a76fb917;hb=8f801d9014deed810710af8a380852cbc04209cd;hpb=fc96c451c806b5070f15dabbf767c79c49be2401

diff --git a/src/cx/fbn/nevernote/sql/DatabaseConnection.java b/src/cx/fbn/nevernote/sql/DatabaseConnection.java
index 7f3ebdc..deefdc8 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;
@@ -45,16 +44,27 @@ public class DatabaseConnection {
 	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;
-	int throttle=0;
+	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, int throttle) {
+	// 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;
-		dbSetup(url, userid, password, cypherPassword);
+		// ICHANGED burlを追加
+		dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
 	}
 	
 	private void setupTables() {
@@ -71,6 +81,11 @@ public class DatabaseConnection {
 		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);
+		
 	}
 	
 	
@@ -80,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);
 
 		
@@ -91,18 +107,25 @@ 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
@@ -110,10 +133,16 @@ public class DatabaseConnection {
 //			conn = DriverManager.getConnection(url,userid,passwordString);
 //			conn = DriverManager.getConnection(url,userid,passwordString);
 //			conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
-			if (throttle == 0)
+			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);
+			} 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();
@@ -125,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);
 	}
@@ -171,7 +239,6 @@ public class DatabaseConnection {
 			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("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
 			
 			executeSql("update note set thumbnailneeded=true, thumbnail=null;");
@@ -190,10 +257,67 @@ public class DatabaseConnection {
 			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')");
-			executeSql("Update note set indexneeded='true'");
-			executeSql("Update noteresources set indexneeded='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 = ''");
+		}
+		
+		// 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();
+			}
+			
+			// 全文検索のための準備
+			query.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLucene.init\"");
+			query.exec("CALL FTL_INIT()");
+			if (Global.indexNoteBody()) {
+				query.exec("CALL FTL_CREATE_INDEX('PUBLIC', 'NOTE', 'CONTENTTEXT');");
+			}
+			if (Global.indexNoteTitle()) {
+				query.exec("CALL FTL_CREATE_INDEX('PUBLIC', 'NOTE', 'TITLE');");
+			}
+		}
 	}
 	
 	public void executeSql(String sql) {
@@ -208,6 +332,9 @@ public class DatabaseConnection {
 		if (!Global.getDatabaseVersion().equals("0.95")) {
 			upgradeDb(Global.getDatabaseVersion());
 		}
+		if (!Global.getDatabaseVersion().equals("0.97")) {
+			upgradeDb(Global.getDatabaseVersion());
+		}
 	}
 	
 
@@ -229,13 +356,46 @@ public class DatabaseConnection {
 		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
@@ -279,13 +439,28 @@ public class DatabaseConnection {
 	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() {
-        NSqlQuery query = new NSqlQuery(getConnection());
-	        				        
+		commitTransaction();
+        NSqlQuery query = new NSqlQuery(getConnection());	        				        
 		if (!query.exec("Begin Transaction"))
 			logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
 
@@ -296,4 +471,33 @@ public class DatabaseConnection {
 		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;
+	}
 }