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 com.trolltech.qt.sql.QJdbc;
  27
  28 import cx.fbn.nevernote.Global;
  29 import cx.fbn.nevernote.sql.driver.NSqlQuery;
  30 import cx.fbn.nevernote.utilities.ApplicationLogger;
  31
  32
  33 public class DatabaseConnection {
  34         // Table helpers
  35         private WordsTable                                      wordsTable;
  36         private TagTable                                        tagTable;
  37         private NotebookTable                           notebookTable;
  38         private NoteTable                                       noteTable;
  39         private DeletedTable                            deletedTable;
  40         private SavedSearchTable                        searchTable;
  41         private WatchFolderTable                        watchFolderTable;
  42         private InvalidXMLTable                         invalidXMLTable;
  43         private LinkedNotebookTable                     linkedNotebookTable;
  44         private SharedNotebookTable                     sharedNotebookTable;
  45         private SyncTable                                       syncTable;
  46         private final ApplicationLogger         logger;
  47         private Connection                                      conn;
  48         int id;
  49
  50
  51         public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
  52                 logger = l;
  53                 dbSetup(url, userid, password, cypherPassword);
  54         }
  55
  56         private void setupTables() {
  57                 tagTable = new TagTable(logger, this);
  58                 notebookTable = new NotebookTable(logger, this);
  59                 noteTable = new NoteTable(logger, this);
  60                 deletedTable = new DeletedTable(logger, this);
  61                 searchTable = new SavedSearchTable(logger, this);
  62                 watchFolderTable = new WatchFolderTable(logger, this);
  63                 invalidXMLTable = new InvalidXMLTable(logger, this);
  64                 wordsTable = new WordsTable(logger, this);
  65                 syncTable = new SyncTable(logger, this);
  66                 linkedNotebookTable = new LinkedNotebookTable(logger, this);
  67                 sharedNotebookTable = new SharedNotebookTable(logger, this);
  68         }
  69
  70
  71         // Compact the database
  72         public void compactDatabase() {
  73
  74         }
  75
  76         // Initialize the database connection
  77         public void dbSetup(String url,String userid, String userPassword, String cypherPassword) {
  78                 logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
  79
  80
  81                 try {
  82                         Class.forName("org.h2.Driver");
  83                 } catch (ClassNotFoundException e1) {
  84                         e1.printStackTrace();
  85                         System.exit(16);
  86                 }
  87
  88                 QJdbc.initialize();
  89
  90                 setupTables();
  91
  92                 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
  93                 boolean dbExists = f.exists();
  94
  95                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
  96
  97
  98                 try {
  99                         String passwordString = null;
 100                         if (cypherPassword==null || cypherPassword.trim().equals(""))
 101                                 passwordString = userPassword;
 102                         else
 103                                 passwordString = cypherPassword+" "+userPassword;
 104                         conn = DriverManager.getConnection(url,userid,passwordString);
 105 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
 106                 } catch (SQLException e) {
 107                         e.printStackTrace();
 108                         return;
 109                 }
 110
 111                 // If it doesn't exist and we are the main thread, then we need to create stuff.
 112                 if (!dbExists)  {
 113                         createTables();
 114                         Global.setAutomaticLogin(false);
 115                 }
 116
 117                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
 118         }
 119
 120
 121         public void dbShutdown() {
 122                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
 123                 try {
 124                         conn.close();
 125                 } catch (SQLException e) {
 126                         e.printStackTrace();
 127                 }
 128                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
 129         }
 130
 131         public void upgradeDb(String version) {
 132                 if (version.equals("0.85")) {
 133                         executeSql("alter table note add column titleColor integer");
 134                         executeSql("alter table note add column thumbnail blob");
 135                         executeSql("alter table note add column thumbnailneeded boolean");
 136                         executeSql("Update note set thumbnailneeded = true;");
 137                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
 138                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
 139                         version = "0.86";
 140                         Global.setDatabaseVersion(version);
 141                 }
 142                 if (version.equals("0.86")) {
 143
 144                         executeSql("alter table notebook add column publishingUri VarChar");
 145                         executeSql("alter table notebook add column publishingOrder Integer");
 146                         executeSql("alter table notebook add column publishingAscending VarChar");
 147                         executeSql("alter table notebook add column publishingPublicDescription varchar");
 148                         executeSql("alter table notebook add column stack varchar");
 149                         executeSql("alter table notebook add column icon blob");
 150                         executeSql("alter table tag add column icon blob");
 151                         executeSql("alter table SavedSearch add column icon blob");
 152
 153                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
 154                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
 155                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
 156                         executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
 157                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
 158                         executeSql("update notebook set publishingUri='', " +
 159                                         "publishingAscending='', stack='', publishingOrder=1, " +
 160                                         "publishingPublicDescription=''");
 161
 162                         sharedNotebookTable.createTable();
 163                         linkedNotebookTable.createTable();
 164
 165                         version = "0.95";
 166                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
 167                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
 168                         Global.setDatabaseVersion(version);
 169                 }
 170         }
 171
 172         public void executeSql(String sql) {
 173                 NSqlQuery query = new NSqlQuery(conn);
 174                 query.exec(sql);
 175         }
 176
 177         public void checkDatabaseVersion() {
 178                 if (!Global.getDatabaseVersion().equals("0.86")) {
 179                         upgradeDb(Global.getDatabaseVersion());
 180                 }
 181                 if (!Global.getDatabaseVersion().equals("0.95")) {
 182                         upgradeDb(Global.getDatabaseVersion());
 183                 }
 184         }
 185
 186
 187         public void backupDatabase(int highSequence, long date) {
 188
 189         }
 190
 191
 192         public void createTables() {
 193                 Global.setDatabaseVersion("0.85");
 194                 Global.setAutomaticLogin(false);
 195                 Global.saveCurrentNoteGuid("");
 196                 Global.saveUploadAmount(0);
 197
 198                 getTagTable().createTable();
 199                 notebookTable.createTable();
 200                 noteTable.createTable();
 201                 deletedTable.createTable();
 202                 searchTable.createTable();
 203                 watchFolderTable.createTable();
 204                 invalidXMLTable.createTable();
 205                 wordsTable.createTable();
 206                 syncTable.createTable();
 207         }
 208
 209         public Connection getConnection() {
 210                 return conn;
 211         }
 212
 213         //***************************************************************
 214         //* Table get methods
 215         //***************************************************************
 216         public DeletedTable getDeletedTable() {
 217                 return deletedTable;
 218         }
 219         public TagTable getTagTable() {
 220                 return tagTable;
 221         }
 222         public NoteTable getNoteTable() {
 223                 return noteTable;
 224         }
 225         public NotebookTable getNotebookTable() {
 226                 return notebookTable;
 227         }
 228         public SavedSearchTable getSavedSearchTable() {
 229                 return searchTable;
 230         }
 231         public WatchFolderTable getWatchFolderTable() {
 232                 return watchFolderTable;
 233         }
 234         public WordsTable getWordsTable() {
 235                 return wordsTable;
 236         }
 237         public InvalidXMLTable getInvalidXMLTable() {
 238                 return invalidXMLTable;
 239         }
 240         public SyncTable getSyncTable() {
 241                 return syncTable;
 242         }
 243         public LinkedNotebookTable getLinkedNotebookTable() {
 244                 return linkedNotebookTable;
 245         }
 246         public SharedNotebookTable getSharedNotebookTable() {
 247                 return sharedNotebookTable;
 248         }
 249 }