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;
private SystemIconTable systemIconTable;
private final ApplicationLogger logger;
private Connection conn;
+ private Connection indexConn;
+ private Connection resourceConn;
+ int throttle;
int id;
- public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
+ public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String userid, String password, String cypherPassword, int throttle) {
logger = l;
- dbSetup(url, userid, password, cypherPassword);
+ this.throttle = throttle;
+ dbSetup(url, iurl, rurl, userid, password, cypherPassword);
}
private void setupTables() {
}
// Initialize the database connection
- public void dbSetup(String url,String userid, String userPassword, String cypherPassword) {
+ public void dbSetup(String url,String indexUrl, String resourceUrl, String userid, String userPassword, String cypherPassword) {
logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
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();
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);
// conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
} catch (SQLException e) {
e.printStackTrace();
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'");
+ }
+
+ // Drop the note resource table & re-connect it in case it was recently encrypted.
+/* NSqlQuery query = new NSqlQuery(conn);
+ query.exec("drop table NoteResources;");
+ String linkcmd = "create linked table NoteResources "
+ +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
+ query.exec(linkcmd);
+ System.err.println(query.lastError());
+*/
logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
}
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 RESOURCES_GUID_INDEX on noteresources (noteGuid, 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=''");
+ "publishingPublicDescription='', linked=false");
+ executeSql("update tag set linked=false, realname='', notebookguid=''");
sharedNotebookTable.createTable();
linkedNotebookTable.createTable();
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);");
+ }
+
+
}
public void executeSql(String sql) {
if (!Global.getDatabaseVersion().equals("0.95")) {
upgradeDb(Global.getDatabaseVersion());
}
+ if (!Global.getDatabaseVersion().equals("0.97")) {
+ upgradeDb(Global.getDatabaseVersion());
+ }
}
searchTable.createTable();
watchFolderTable.createTable();
invalidXMLTable.createTable();
- wordsTable.createTable();
syncTable.createTable();
}
+ public void createIndexTables() {
+ wordsTable.createTable();
+ }
+
+ public void createResourceTables() {
+ noteTable.noteResourceTable.createTable();
+ }
+
public Connection getConnection() {
return conn;
}
+ public Connection getIndexConnection() {
+ return indexConn;
+ }
+ public Connection getResourceConnection() {
+ return resourceConn;
+ }
//***************************************************************
//* Table get methods
public InkImagesTable getInkImagesTable() {
return inkImagesTable;
}
+
+ //****************************************************************
+ //* 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;
+ }
}