/*
* This file is part of NixNote
* Copyright 2009 Randy Baumgarte
*
* This file may be licensed under the terms of of the
* GNU General Public License Version 2 (the ``GPL'').
*
* Software distributed under the License is distributed
* on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
* express or implied. See the GPL for the specific language
* governing rights and limitations.
*
* You should have received a copy of the GPL along with this
* program. If not, go to http://www.gnu.org/licenses/gpl.html
* or write to the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*
*/
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.driver.NSqlQuery;
import cx.fbn.nevernote.utilities.ApplicationLogger;
public class DatabaseConnection {
// Table helpers
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 final ApplicationLogger logger;
private Connection conn;
private Connection indexConn;
private Connection resourceConn;
int throttle;
int id;
public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String userid, String password, String cypherPassword, int throttle) {
logger = l;
this.throttle = throttle;
dbSetup(url, iurl, rurl, userid, password, cypherPassword);
}
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);
}
// Compact the database
public void compactDatabase() {
}
// Initialize the database connection
public void dbSetup(String url,String indexUrl, String resourceUrl, 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();
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);
// 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) {
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 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() {
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")) {
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 = ''");
}
}
public void executeSql(String sql) {
NSqlQuery query = new NSqlQuery(conn);
query.exec(sql);
}
public void checkDatabaseVersion() {
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 backupDatabase(int highSequence, long date) {
}
public void createTables() {
Global.setDatabaseVersion("0.85");
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 Connection getConnection() {
return conn;
}
public Connection getIndexConnection() {
return indexConn;
}
public Connection getResourceConnection() {
return resourceConn;
}
//***************************************************************
//* Table get methods
//***************************************************************
public DeletedTable getDeletedTable() {
return deletedTable;
}
public TagTable getTagTable() {
return tagTable;
}
public NoteTable getNoteTable() {
return noteTable;
}
public NotebookTable getNotebookTable() {
return notebookTable;
}
public SavedSearchTable getSavedSearchTable() {
return searchTable;
}
public WatchFolderTable getWatchFolderTable() {
return watchFolderTable;
}
public WordsTable getWordsTable() {
return wordsTable;
}
public InvalidXMLTable getInvalidXMLTable() {
return invalidXMLTable;
}
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;
}
//****************************************************************
//* 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;
}
}