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