src/cx/fbn/nevernote/sql/DatabaseConnection.java

   1 /*
   2  * This file is part of NeverNote
   3  * Copyright 2009 Randy Baumgarte
   4  *
   5  * This file may be licensed under the terms of of the
   6  * GNU General Public License Version 2 (the ``GPL'').
   7  *
   8  * Software distributed under the License is distributed
   9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
  10  * express or implied. See the GPL for the specific language
  11  * governing rights and limitations.
  12  *
  13  * You should have received a copy of the GPL along with this
  14  * program. If not, go to http://www.gnu.org/licenses/gpl.html
  15  * or write to the Free Software Foundation, Inc.,
  16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
  17  *
  18 */
  19 package cx.fbn.nevernote.sql;
  20
  21 import java.io.File;
  22 import java.sql.Connection;
  23 import java.sql.DriverManager;
  24 import java.sql.SQLException;
  25
  26 import cx.fbn.nevernote.Global;
  27 import cx.fbn.nevernote.sql.driver.NSqlQuery;
  28 import cx.fbn.nevernote.utilities.ApplicationLogger;
  29
  30
  31 public class DatabaseConnection {
  32         // Table helpers
  33         private WordsTable                                      wordsTable;
  34         private TagTable                                        tagTable;
  35         private NotebookTable                           notebookTable;
  36         private NoteTable                                       noteTable;
  37         private DeletedTable                            deletedTable;
  38         private SavedSearchTable                        searchTable;
  39         private WatchFolderTable                        watchFolderTable;
  40         private InvalidXMLTable                         invalidXMLTable;
  41         private LinkedNotebookTable                     linkedNotebookTable;
  42         private SharedNotebookTable                     sharedNotebookTable;
  43         private InkImagesTable                          inkImagesTable;
  44         private SyncTable                                       syncTable;
  45         private SystemIconTable                         systemIconTable;
  46         private final ApplicationLogger         logger;
  47         private Connection                                      conn;
  48         private Connection                                      indexConn;
  49         private Connection                                      resourceConn;
  50         int throttle=0;
  51         int id;
  52
  53
  54         public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String userid, String password, String cypherPassword, int throttle) {
  55                 logger = l;
  56                 this.throttle = throttle;
  57                 dbSetup(url, iurl, rurl, userid, password, cypherPassword);
  58         }
  59
  60         private void setupTables() {
  61                 tagTable = new TagTable(logger, this);
  62                 notebookTable = new NotebookTable(logger, this);
  63                 noteTable = new NoteTable(logger, this);
  64                 deletedTable = new DeletedTable(logger, this);
  65                 searchTable = new SavedSearchTable(logger, this);
  66                 watchFolderTable = new WatchFolderTable(logger, this);
  67                 invalidXMLTable = new InvalidXMLTable(logger, this);
  68                 wordsTable = new WordsTable(logger, this);
  69                 syncTable = new SyncTable(logger, this);
  70                 linkedNotebookTable = new LinkedNotebookTable(logger, this);
  71                 sharedNotebookTable = new SharedNotebookTable(logger, this);
  72                 systemIconTable = new SystemIconTable(logger, this);
  73                 inkImagesTable = new InkImagesTable(logger, this);
  74         }
  75
  76
  77         // Compact the database
  78         public void compactDatabase() {
  79
  80         }
  81
  82         // Initialize the database connection
  83         public void dbSetup(String url,String indexUrl, String resourceUrl, String userid, String userPassword, String cypherPassword) {
  84                 logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
  85
  86
  87                 try {
  88                         Class.forName("org.h2.Driver");
  89                 } catch (ClassNotFoundException e1) {
  90                         e1.printStackTrace();
  91                         System.exit(16);
  92                 }
  93
  94 //              QJdbc.initialize();
  95
  96                 setupTables();
  97
  98                 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
  99                 boolean dbExists = f.exists();
 100                 f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
 101                 boolean indexDbExists = f.exists();
 102                 f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
 103                 boolean resourceDbExists = f.exists();
 104
 105                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
 106
 107                 String passwordString = null;
 108                 try {
 109
 110                         if (cypherPassword==null || cypherPassword.trim().equals(""))
 111                                 passwordString = userPassword;
 112                         else
 113                                 passwordString = cypherPassword+" "+userPassword;
 114 //                      conn = DriverManager.getConnection(url,userid,passwordString);
 115 //                      conn = DriverManager.getConnection(url,userid,passwordString);
 116 //                      conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
 117                         if (throttle == 0) {
 118                                 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
 119                         } else {
 120                                 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
 121                         }
 122                         indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
 123                         resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
 124 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
 125                 } catch (SQLException e) {
 126                         e.printStackTrace();
 127                         return;
 128                 }
 129
 130                 // If it doesn't exist and we are the main thread, then we need to create stuff.
 131                 if (!dbExists)  {
 132                         createTables();
 133                         Global.setAutomaticLogin(false);
 134                 }
 135                 if (!resourceDbExists) {
 136                         createResourceTables();
 137                         if (dbTableExists("NoteResources")) {
 138                                 // Begin migration of database
 139                                 NSqlQuery query = new NSqlQuery(resourceConn);
 140                                 String linkcmd = "create linked table oldnoteresources "+
 141                                                 "('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
 142                                 query.exec(linkcmd);
 143                                 query.exec("insert into noteresources (select * from oldnoteresources)");
 144                                 query.exec("Drop table oldnoteresources;");
 145                                 query.exec("Update noteresources set indexneeded='true'");
 146
 147                         }
 148                 }
 149                 if (!indexDbExists)  {
 150                         createIndexTables();
 151                         executeSql("Update note set indexneeded='true'");
 152                 }
 153
 154                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
 155         }
 156
 157
 158         public void dbShutdown() {
 159                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
 160                 try {
 161                         conn.close();
 162                 } catch (SQLException e) {
 163                         e.printStackTrace();
 164                 }
 165                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
 166         }
 167
 168         public void upgradeDb(String version) {
 169                 if (version.equals("0.85")) {
 170                         executeSql("alter table note add column titleColor integer");
 171                         executeSql("alter table note add column thumbnail blob");
 172                         executeSql("alter table note add column thumbnailneeded boolean");
 173                         executeSql("Update note set thumbnailneeded = true;");
 174                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
 175                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
 176                         version = "0.86";
 177                         Global.setDatabaseVersion(version);
 178                 }
 179                 if (version.equals("0.86")) {
 180
 181                         executeSql("alter table notebook add column publishingUri VarChar");
 182                         executeSql("alter table notebook add column publishingOrder Integer");
 183                         executeSql("alter table notebook add column publishingAscending Boolean");
 184                         executeSql("alter table notebook add column publishingPublicDescription varchar");
 185                         executeSql("alter table notebook add column stack varchar");
 186                         executeSql("alter table notebook add column icon blob");
 187                         executeSql("alter table notebook add column readOnly boolean");
 188                         executeSql("alter table notebook add column linked boolean");
 189
 190                         executeSql("alter table tag add column realname varchar");
 191                         executeSql("alter table tag add column linked boolean");
 192                         executeSql("alter table tag add column icon blob");
 193                         executeSql("alter table tag add column notebookguid varchar");
 194                         executeSql("alter table SavedSearch add column icon blob");
 195
 196                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
 197                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
 198                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
 199                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
 200
 201                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
 202                         executeSql("update notebook set publishingUri='', " +
 203                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
 204                                         "publishingPublicDescription='', linked=false");
 205                         executeSql("update tag set linked=false, realname='', notebookguid=''");
 206
 207                         sharedNotebookTable.createTable();
 208                         linkedNotebookTable.createTable();
 209                         systemIconTable.createTable();
 210                         inkImagesTable.createTable();
 211
 212                         version = "0.95";
 213                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
 214                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
 215                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
 216                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
 217                         Global.setDatabaseVersion(version);
 218                 }
 219                 if (version.equals("0.95")) {
 220                         if (dbTableExists("words"))
 221                                 executeSql("Drop table words;");
 222                         if (dbTableExists("NoteResources"))
 223                                 executeSql("Drop table NoteResources;");
 224                 }
 225
 226
 227         }
 228
 229         public void executeSql(String sql) {
 230                 NSqlQuery query = new NSqlQuery(conn);
 231                 query.exec(sql);
 232         }
 233
 234         public void checkDatabaseVersion() {
 235                 if (!Global.getDatabaseVersion().equals("0.86")) {
 236                         upgradeDb(Global.getDatabaseVersion());
 237                 }
 238                 if (!Global.getDatabaseVersion().equals("0.95")) {
 239                         upgradeDb(Global.getDatabaseVersion());
 240                 }
 241                 if (!Global.getDatabaseVersion().equals("0.97")) {
 242                         upgradeDb(Global.getDatabaseVersion());
 243                 }
 244         }
 245
 246
 247         public void backupDatabase(int highSequence, long date) {
 248
 249         }
 250
 251
 252         public void createTables() {
 253                 Global.setDatabaseVersion("0.85");
 254                 Global.setAutomaticLogin(false);
 255                 Global.saveCurrentNoteGuid("");
 256                 Global.saveUploadAmount(0);
 257
 258                 getTagTable().createTable();
 259                 notebookTable.createTable(true);
 260                 noteTable.createTable();
 261                 deletedTable.createTable();
 262                 searchTable.createTable();
 263                 watchFolderTable.createTable();
 264                 invalidXMLTable.createTable();
 265                 syncTable.createTable();
 266         }
 267
 268         public void createIndexTables() {
 269                 wordsTable.createTable();
 270         }
 271
 272         public void createResourceTables() {
 273                 noteTable.noteResourceTable.createTable();
 274         }
 275
 276         public Connection getConnection() {
 277                 return conn;
 278         }
 279         public Connection getIndexConnection() {
 280                 return  indexConn;
 281         }
 282         public Connection getResourceConnection() {
 283                 return resourceConn;
 284         }
 285
 286         //***************************************************************
 287         //* Table get methods
 288         //***************************************************************
 289         public DeletedTable getDeletedTable() {
 290                 return deletedTable;
 291         }
 292         public TagTable getTagTable() {
 293                 return tagTable;
 294         }
 295         public NoteTable getNoteTable() {
 296                 return noteTable;
 297         }
 298         public NotebookTable getNotebookTable() {
 299                 return notebookTable;
 300         }
 301         public SavedSearchTable getSavedSearchTable() {
 302                 return searchTable;
 303         }
 304         public WatchFolderTable getWatchFolderTable() {
 305                 return watchFolderTable;
 306         }
 307         public WordsTable getWordsTable() {
 308                 return wordsTable;
 309         }
 310         public InvalidXMLTable getInvalidXMLTable() {
 311                 return invalidXMLTable;
 312         }
 313         public SyncTable getSyncTable() {
 314                 return syncTable;
 315         }
 316         public LinkedNotebookTable getLinkedNotebookTable() {
 317                 return linkedNotebookTable;
 318         }
 319         public SharedNotebookTable getSharedNotebookTable() {
 320                 return sharedNotebookTable;
 321         }
 322         public SystemIconTable getSystemIconTable() {
 323                 return systemIconTable;
 324         }
 325         public InkImagesTable getInkImagesTable() {
 326                 return inkImagesTable;
 327         }
 328
 329         //****************************************************************
 330         //* Begin/End transactions
 331         //****************************************************************
 332         public void beginTransaction() {
 333                 commitTransaction();
 334         NSqlQuery query = new NSqlQuery(getConnection());
 335                 if (!query.exec("Begin Transaction"))
 336                         logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
 337
 338         }
 339         public void commitTransaction() {
 340         NSqlQuery query = new NSqlQuery(getConnection());
 341
 342                 if (!query.exec("Commit"))
 343                         logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
 344         }
 345
 346         //****************************************************************
 347         //* Check if a table exists
 348         //****************************************************************
 349         public boolean dbTableExists(String name) {
 350         NSqlQuery query = new NSqlQuery(getConnection());
 351         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
 352         query.bindValue(":name", name.toUpperCase());
 353         query.exec();
 354         if (query.next())
 355                 return true;
 356         else
 357                 return false;
 358         }
 359 }