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

   1 /*
   2  * This file is part of NixNote/NeighborNote
   3  * Copyright 2009 Randy Baumgarte
   4  * Copyright 2013 Yuki Takahashi
   5  *
   6  * This file may be licensed under the terms of of the
   7  * GNU General Public License Version 2 (the ``GPL'').
   8  *
   9  * Software distributed under the License is distributed
  10  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
  11  * express or implied. See the GPL for the specific language
  12  * governing rights and limitations.
  13  *
  14  * You should have received a copy of the GPL along with this
  15  * program. If not, go to http://www.gnu.org/licenses/gpl.html
  16  * or write to the Free Software Foundation, Inc.,
  17  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
  18  *
  19 */
  20 package cx.fbn.nevernote.sql;
  21
  22 import java.io.File;
  23 import java.sql.Connection;
  24 import java.sql.DriverManager;
  25 import java.sql.SQLException;
  26
  27 import cx.fbn.nevernote.Global;
  28 import cx.fbn.nevernote.sql.driver.NSqlQuery;
  29 import cx.fbn.nevernote.utilities.ApplicationLogger;
  30
  31
  32 public class DatabaseConnection {
  33         // Table helpers
  34         private WordsTable                                      wordsTable;
  35         private TagTable                                        tagTable;
  36         private NotebookTable                           notebookTable;
  37         private NoteTable                                       noteTable;
  38         private DeletedTable                            deletedTable;
  39         private SavedSearchTable                        searchTable;
  40         private WatchFolderTable                        watchFolderTable;
  41         private InvalidXMLTable                         invalidXMLTable;
  42         private LinkedNotebookTable                     linkedNotebookTable;
  43         private SharedNotebookTable                     sharedNotebookTable;
  44         private InkImagesTable                          inkImagesTable;
  45         private SyncTable                                       syncTable;
  46         private SystemIconTable                         systemIconTable;
  47         private HistoryTable historyTable;
  48         private ExcludedTable excludedTable;
  49         private StaredTable staredTable;
  50
  51         private final ApplicationLogger         logger;
  52         private Connection                                      conn;
  53         private Connection                                      indexConn;
  54         private Connection                                      resourceConn;
  55         private Connection behaviorConn;
  56
  57         int throttle;
  58         int id;
  59
  60         public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String burl, String userid, String password, String cypherPassword, int throttle) {
  61                 logger = l;
  62                 this.throttle = throttle;
  63                 dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
  64         }
  65
  66         private void setupTables() {
  67                 tagTable = new TagTable(logger, this);
  68                 notebookTable = new NotebookTable(logger, this);
  69                 noteTable = new NoteTable(logger, this);
  70                 deletedTable = new DeletedTable(logger, this);
  71                 searchTable = new SavedSearchTable(logger, this);
  72                 watchFolderTable = new WatchFolderTable(logger, this);
  73                 invalidXMLTable = new InvalidXMLTable(logger, this);
  74                 wordsTable = new WordsTable(logger, this);
  75                 syncTable = new SyncTable(logger, this);
  76                 linkedNotebookTable = new LinkedNotebookTable(logger, this);
  77                 sharedNotebookTable = new SharedNotebookTable(logger, this);
  78                 systemIconTable = new SystemIconTable(logger, this);
  79                 inkImagesTable = new InkImagesTable(logger, this);
  80                 historyTable = new HistoryTable(logger, this);
  81                 excludedTable = new ExcludedTable(logger, this);
  82                 staredTable = new StaredTable(logger, this);
  83
  84         }
  85
  86
  87         // Compact the database
  88         public void compactDatabase() {
  89
  90         }
  91
  92         // Initialize the database connection
  93         public void dbSetup(String url,String indexUrl, String resourceUrl, String behaviorUrl, String userid, String userPassword, String cypherPassword) {
  94                 logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
  95
  96
  97                 try {
  98                         Class.forName("org.h2.Driver");
  99                 } catch (ClassNotFoundException e1) {
 100                         e1.printStackTrace();
 101                         System.exit(16);
 102                 }
 103
 104 //              QJdbc.initialize();
 105
 106                 setupTables();
 107
 108                 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
 109                 boolean dbExists = f.exists();
 110                 f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
 111                 boolean indexDbExists = f.exists();
 112                 f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
 113                 boolean resourceDbExists = f.exists();
 114                 f = Global.getFileManager().getDbDirFile(Global.behaviorDatabaseName + ".h2.db");
 115                 boolean behaviorDbExists = f.exists();
 116
 117                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
 118
 119                 String passwordString = null;
 120                 try {
 121
 122                         if (cypherPassword==null || cypherPassword.trim().equals(""))
 123                                 passwordString = userPassword;
 124                         else
 125                                 passwordString = cypherPassword+" "+userPassword;
 126 //                      conn = DriverManager.getConnection(url,userid,passwordString);
 127 //                      conn = DriverManager.getConnection(url,userid,passwordString);
 128 //                      conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
 129                         if (throttle == 0) {
 130                                 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
 131                         } else {
 132                                 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
 133                         }
 134                         indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
 135                         resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
 136                         behaviorConn = DriverManager.getConnection(behaviorUrl, userid, passwordString);
 137
 138 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
 139                 } catch (SQLException e) {
 140                         e.printStackTrace();
 141                         return;
 142                 }
 143
 144                 // If it doesn't exist and we are the main thread, then we need to create stuff.
 145                 if (!dbExists)  {
 146                         createTables();
 147                         Global.setAutomaticLogin(false);
 148                 }
 149                 if (!resourceDbExists) {
 150                         createResourceTables();
 151                         if (dbTableExists("NoteResources")) {
 152                                 // Begin migration of database
 153                                 NSqlQuery query = new NSqlQuery(resourceConn);
 154                                 String linkcmd = "create linked table oldnoteresources "+
 155                                                 "('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
 156                                 query.exec(linkcmd);
 157                                 query.exec("insert into noteresources (select * from oldnoteresources)");
 158                                 query.exec("Drop table oldnoteresources;");
 159                                 query.exec("Update noteresources set indexneeded='true'");
 160
 161                         }
 162                 }
 163                 if (!indexDbExists)  {
 164                         createIndexTables();
 165                         executeSql("Update note set indexneeded='true'");
 166                 }
 167
 168                 // 操作履歴テーブルと除外ノートテーブルとスター付きノートテーブルを作る
 169                 if (!behaviorDbExists) {
 170                         createHistoryTables();
 171                         createExcludedTables();
 172                         createStaredTables();
 173                 }
 174
 175                 // If we encrypted/decrypted it the last time, we need to reconnect the tables.
 176 //              if (Global.relinkTables) {
 177 //                      NSqlQuery query = new NSqlQuery(conn);
 178 //                      query.exec("Drop table NoteResources;");
 179 //                      String linkcmd = "create linked table NoteResources "
 180 //                              +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
 181 //                      System.out.println(linkcmd);
 182 //                      query.exec(linkcmd);
 183 //                      System.err.println(query.lastError());
 184 //                      Global.relinkTables = false;
 185 //              }
 186
 187
 188                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
 189         }
 190
 191
 192         public void dbShutdown() {
 193                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
 194                 try {
 195                         conn.close();
 196                 } catch (SQLException e) {
 197                         e.printStackTrace();
 198                 }
 199                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
 200         }
 201
 202         public void upgradeDb(String version) {
 203                 if (version.equals("0.85")) {
 204                         executeSql("alter table note add column titleColor integer");
 205                         executeSql("alter table note add column thumbnail blob");
 206                         executeSql("alter table note add column thumbnailneeded boolean");
 207                         executeSql("Update note set thumbnailneeded = true;");
 208                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
 209                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
 210                         version = "0.86";
 211                         Global.setDatabaseVersion(version);
 212                 }
 213                 if (version.equals("0.86")) {
 214
 215                         executeSql("alter table notebook add column publishingUri VarChar");
 216                         executeSql("alter table notebook add column publishingOrder Integer");
 217                         executeSql("alter table notebook add column publishingAscending Boolean");
 218                         executeSql("alter table notebook add column publishingPublicDescription varchar");
 219                         executeSql("alter table notebook add column stack varchar");
 220                         executeSql("alter table notebook add column icon blob");
 221                         executeSql("alter table notebook add column readOnly boolean");
 222                         executeSql("alter table notebook add column linked boolean");
 223
 224                         executeSql("alter table tag add column realname varchar");
 225                         executeSql("alter table tag add column linked boolean");
 226                         executeSql("alter table tag add column icon blob");
 227                         executeSql("alter table tag add column notebookguid varchar");
 228                         executeSql("alter table SavedSearch add column icon blob");
 229
 230                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
 231                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
 232                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
 233                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
 234
 235                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
 236                         executeSql("update notebook set publishingUri='', " +
 237                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
 238                                         "publishingPublicDescription='', linked=false");
 239                         executeSql("update tag set linked=false, realname='', notebookguid=''");
 240
 241                         sharedNotebookTable.createTable();
 242                         linkedNotebookTable.createTable();
 243                         systemIconTable.createTable();
 244                         inkImagesTable.createTable();
 245
 246                         version = "0.95";
 247                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
 248                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
 249                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
 250                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
 251                         Global.setDatabaseVersion(version);
 252                 }
 253                 if (version.equals("0.95")) {
 254                         if (dbTableExists("words"))
 255                                 executeSql("Drop table words;");
 256                         if (dbTableExists("NoteResources"))
 257                                 executeSql("Drop table NoteResources;");
 258
 259                         version = "0.97";
 260                         Global.setDatabaseVersion(version);
 261                 }
 262                 if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
 263                         executeSql("alter table note add column ORIGINAL_GUID VarChar");
 264                         executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
 265                 }
 266                 if (!dbTableColumnExists("NOTEBOOK", "NARROW_SORT_ORDER")) {
 267                         executeSql("alter table notebook add column NARROW_SORT_ORDER integer");
 268                         executeSql("update notebook set NARROW_SORT_ORDER = -1");
 269
 270                         executeSql("alter table notebook add column WIDE_SORT_ORDER integer");
 271                         executeSql("update notebook set WIDE_SORT_ORDER = -1");
 272
 273                         executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
 274                         executeSql("update notebook set WIDE_SORT_COLUMN = -1");
 275
 276                         executeSql("alter table notebook add column NARROW_SORT_COLUMN integer");
 277                         executeSql("update notebook set NARROW_SORT_COLUMN = -1");
 278                 }
 279                 if (!dbTableColumnExists("NOTE", "PINNED")) {
 280                         executeSql("alter table note add column pinned integer");
 281                         executeSql("update note set pinned = 0");
 282                 }
 283                 if (!dbTableColumnExists("NOTE", "ATTRIBUTECONTENTCLASS")) {
 284                         executeSql("alter table note add column attributeContentClass VarChar");
 285                         executeSql("update note set attributeContentClass = ''");
 286                 }
 287
 288                 // Evernoteサムネイルカラムを追加
 289                 if (!dbTableColumnExists("NOTE", "ENTHUMBNAIL")) {
 290                         executeSql("alter table note add column enThumbNail Blob");
 291                 }
 292
 293                 // Apache Luceneを使った日本語検索のためのプレーンテキストノートコンテンツカラムを準備
 294                 NSqlQuery query = new NSqlQuery(conn);
 295                 if (!dbTableColumnExists("NOTE", "CONTENTTEXT")) {
 296                         executeSql("alter table note add column contentText VarChar");
 297                         executeSql("update note set contentText = ''");
 298                         query.exec("Select guid, content from Note where contentText = ''");
 299                         while (query.next()) {
 300                                 String guid = query.valueString(0);
 301                                 String content = query.valueString(1);
 302                                 String contentText = Global.extractPlainText(content);
 303                                 NSqlQuery query2 = new NSqlQuery(conn);
 304                                 query2.prepare("update note set contentText=:contentText where guid=:guid");
 305                                 query2.bindValue(":contentText", contentText);
 306                                 query2.bindValue(":guid", guid);
 307                                 query2.exec();
 308                         }
 309
 310                         // Apache Luceneを使った全文検索のための準備
 311                         query.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
 312                         query.exec("CALL FTL_INIT()");
 313                 }
 314
 315                 // Apache Luceneを使った日本語検索のためのプレーンテキストノートリソースカラムを準備
 316                 NSqlQuery rQuery = new NSqlQuery(resourceConn);
 317                 rQuery.exec("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='NOTERESOURCES' and COLUMN_NAME='RESOURCETEXT'");
 318                 if (!rQuery.next()) {
 319                         rQuery.exec("alter table noteResources add column resourceText VarChar");
 320                         rQuery.exec("update noteResources set resourceText = ''");
 321
 322                         // Apache Luceneを使った全文検索のための準備
 323                         rQuery.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
 324                         rQuery.exec("CALL FTL_INIT()");
 325                 }
 326
 327                 // 注意：ここから先でnoteテーブルとnoteResourcesテーブルの構造を変更するな。全文検索ができなくなる。
 328
 329                 // v0.4.1以前では起動時に全文検索が正しく設定されない問題があったのでLuceneを再構築
 330                 if (version.equals("0.97")) {
 331                         query.exec("CALL FTL_DROP_ALL();");
 332                         rQuery.exec("CALL FTL_DROP_ALL();");
 333                         Global.rebuildFullTextNoteTarget(this);
 334                         Global.rebuildFullTextResourceTarget(this);
 335
 336                         version = "0.98";
 337                         Global.setDatabaseVersion(version);
 338                 }
 339         }
 340
 341         public void executeSql(String sql) {
 342                 NSqlQuery query = new NSqlQuery(conn);
 343                 query.exec(sql);
 344         }
 345
 346         public void checkDatabaseVersion() {
 347 //              if (!Global.getDatabaseVersion().equals("0.86")) {
 348 //                      upgradeDb(Global.getDatabaseVersion());
 349 //              }
 350 //              if (!Global.getDatabaseVersion().equals("0.95")) {
 351 //                      upgradeDb(Global.getDatabaseVersion());
 352 //              }
 353                 if (!Global.getDatabaseVersion().equals("0.98")) {
 354                         upgradeDb(Global.getDatabaseVersion());
 355                 }
 356         }
 357
 358
 359         public void backupDatabase(int highSequence, long date) {
 360
 361         }
 362
 363
 364         public void createTables() {
 365                 Global.setDatabaseVersion("0.85");
 366                 Global.setAutomaticLogin(false);
 367                 Global.saveCurrentNoteGuid("");
 368                 Global.saveUploadAmount(0);
 369
 370                 getTagTable().createTable();
 371                 notebookTable.createTable(true);
 372                 noteTable.createTable();
 373                 deletedTable.createTable();
 374                 searchTable.createTable();
 375                 watchFolderTable.createTable();
 376                 invalidXMLTable.createTable();
 377                 syncTable.createTable();
 378         }
 379
 380         public void createIndexTables() {
 381                 wordsTable.createTable();
 382         }
 383
 384         public void createResourceTables() {
 385                 noteTable.noteResourceTable.createTable();
 386         }
 387
 388         public void createHistoryTables() {
 389                 historyTable.createTable();
 390         }
 391
 392         public void createExcludedTables() {
 393                 excludedTable.createTable();
 394         }
 395
 396         public void createStaredTables() {
 397                 staredTable.createTable();
 398         }
 399
 400         public Connection getConnection() {
 401                 return conn;
 402         }
 403         public Connection getIndexConnection() {
 404                 return  indexConn;
 405         }
 406         public Connection getResourceConnection() {
 407                 return resourceConn;
 408         }
 409
 410         public Connection getBehaviorConnection() {
 411                 return behaviorConn;
 412         }
 413
 414         //***************************************************************
 415         //* Table get methods
 416         //***************************************************************
 417         public DeletedTable getDeletedTable() {
 418                 return deletedTable;
 419         }
 420         public TagTable getTagTable() {
 421                 return tagTable;
 422         }
 423         public NoteTable getNoteTable() {
 424                 return noteTable;
 425         }
 426         public NotebookTable getNotebookTable() {
 427                 return notebookTable;
 428         }
 429         public SavedSearchTable getSavedSearchTable() {
 430                 return searchTable;
 431         }
 432         public WatchFolderTable getWatchFolderTable() {
 433                 return watchFolderTable;
 434         }
 435         public WordsTable getWordsTable() {
 436                 return wordsTable;
 437         }
 438         public InvalidXMLTable getInvalidXMLTable() {
 439                 return invalidXMLTable;
 440         }
 441         public SyncTable getSyncTable() {
 442                 return syncTable;
 443         }
 444         public LinkedNotebookTable getLinkedNotebookTable() {
 445                 return linkedNotebookTable;
 446         }
 447         public SharedNotebookTable getSharedNotebookTable() {
 448                 return sharedNotebookTable;
 449         }
 450         public SystemIconTable getSystemIconTable() {
 451                 return systemIconTable;
 452         }
 453         public InkImagesTable getInkImagesTable() {
 454                 return inkImagesTable;
 455         }
 456
 457         public HistoryTable getHistoryTable() {
 458                 return historyTable;
 459         }
 460
 461         public ExcludedTable getExcludedTable() {
 462                 return excludedTable;
 463         }
 464
 465         public StaredTable getStaredTable() {
 466                 return staredTable;
 467         }
 468
 469         //****************************************************************
 470         //* Begin/End transactions
 471         //****************************************************************
 472         public void beginTransaction() {
 473                 commitTransaction();
 474         NSqlQuery query = new NSqlQuery(getConnection());
 475                 if (!query.exec("Begin Transaction"))
 476                         logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
 477
 478         }
 479         public void commitTransaction() {
 480         NSqlQuery query = new NSqlQuery(getConnection());
 481
 482                 if (!query.exec("Commit"))
 483                         logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
 484         }
 485
 486         //****************************************************************
 487         //* Check if a table exists
 488         //****************************************************************
 489         public boolean dbTableExists(String name) {
 490         NSqlQuery query = new NSqlQuery(getConnection());
 491         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
 492         query.bindValue(":name", name.toUpperCase());
 493         query.exec();
 494         if (query.next())
 495                 return true;
 496         else
 497                 return false;
 498         }
 499
 500         //****************************************************************
 501         //* Check if a row in a table exists
 502         //****************************************************************
 503         public boolean dbTableColumnExists(String tableName, String columnName) {
 504         NSqlQuery query = new NSqlQuery(getConnection());
 505         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
 506         query.bindValue(":name", tableName.toUpperCase());
 507         query.bindValue(":column", columnName);
 508         query.exec();
 509         if (query.next())
 510                 return true;
 511         else
 512                 return false;
 513         }
 514 }