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