2 * This file is part of NeverNote
3 * Copyright 2009 Randy Baumgarte
5 * This file may be licensed under the terms of of the
6 * GNU General Public License Version 2 (the ``GPL'').
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.
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.
19 package cx.fbn.nevernote.sql;
22 import java.sql.Connection;
23 import java.sql.DriverManager;
24 import java.sql.SQLException;
26 import cx.fbn.nevernote.Global;
27 import cx.fbn.nevernote.sql.driver.NSqlQuery;
28 import cx.fbn.nevernote.utilities.ApplicationLogger;
31 public class DatabaseConnection {
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;
54 public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String userid, String password, String cypherPassword, int throttle) {
56 this.throttle = throttle;
57 dbSetup(url, iurl, rurl, userid, password, cypherPassword);
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);
77 // Compact the database
78 public void compactDatabase() {
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);
88 Class.forName("org.h2.Driver");
89 } catch (ClassNotFoundException e1) {
94 // QJdbc.initialize();
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();
105 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
107 String passwordString = null;
110 if (cypherPassword==null || cypherPassword.trim().equals(""))
111 passwordString = userPassword;
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);
118 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
120 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
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) {
130 // If it doesn't exist and we are the main thread, then we need to create stuff.
133 Global.setAutomaticLogin(false);
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')";
143 query.exec("insert into noteresources (select * from oldnoteresources)");
144 query.exec("Drop table oldnoteresources;");
145 query.exec("Update noteresources set indexneeded='true'");
149 if (!indexDbExists) {
151 executeSql("Update note set indexneeded='true'");
154 // Drop the note resource table & re-connect it in case it was recently encrypted.
155 /* NSqlQuery query = new NSqlQuery(conn);
156 query.exec("drop table NoteResources;");
157 String linkcmd = "create linked table NoteResources "
158 +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
160 System.err.println(query.lastError());
163 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
167 public void dbShutdown() {
168 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
171 } catch (SQLException e) {
174 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
177 public void upgradeDb(String version) {
178 if (version.equals("0.85")) {
179 executeSql("alter table note add column titleColor integer");
180 executeSql("alter table note add column thumbnail blob");
181 executeSql("alter table note add column thumbnailneeded boolean");
182 executeSql("Update note set thumbnailneeded = true;");
183 executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
184 executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
186 Global.setDatabaseVersion(version);
188 if (version.equals("0.86")) {
190 executeSql("alter table notebook add column publishingUri VarChar");
191 executeSql("alter table notebook add column publishingOrder Integer");
192 executeSql("alter table notebook add column publishingAscending Boolean");
193 executeSql("alter table notebook add column publishingPublicDescription varchar");
194 executeSql("alter table notebook add column stack varchar");
195 executeSql("alter table notebook add column icon blob");
196 executeSql("alter table notebook add column readOnly boolean");
197 executeSql("alter table notebook add column linked boolean");
199 executeSql("alter table tag add column realname varchar");
200 executeSql("alter table tag add column linked boolean");
201 executeSql("alter table tag add column icon blob");
202 executeSql("alter table tag add column notebookguid varchar");
203 executeSql("alter table SavedSearch add column icon blob");
205 executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
206 executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
207 executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
208 executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
210 executeSql("update note set thumbnailneeded=true, thumbnail=null;");
211 executeSql("update notebook set publishingUri='', " +
212 "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
213 "publishingPublicDescription='', linked=false");
214 executeSql("update tag set linked=false, realname='', notebookguid=''");
216 sharedNotebookTable.createTable();
217 linkedNotebookTable.createTable();
218 systemIconTable.createTable();
219 inkImagesTable.createTable();
222 executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
223 executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
224 executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
225 executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
226 Global.setDatabaseVersion(version);
228 if (version.equals("0.95")) {
229 if (dbTableExists("words"))
230 executeSql("Drop table words;");
231 if (dbTableExists("NoteResources"))
232 executeSql("Drop table NoteResources;");
234 if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
235 executeSql("alter table note add column ORIGINAL_GUID VarChar");
236 executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
242 public void executeSql(String sql) {
243 NSqlQuery query = new NSqlQuery(conn);
247 public void checkDatabaseVersion() {
248 if (!Global.getDatabaseVersion().equals("0.86")) {
249 upgradeDb(Global.getDatabaseVersion());
251 if (!Global.getDatabaseVersion().equals("0.95")) {
252 upgradeDb(Global.getDatabaseVersion());
254 if (!Global.getDatabaseVersion().equals("0.97")) {
255 upgradeDb(Global.getDatabaseVersion());
260 public void backupDatabase(int highSequence, long date) {
265 public void createTables() {
266 Global.setDatabaseVersion("0.85");
267 Global.setAutomaticLogin(false);
268 Global.saveCurrentNoteGuid("");
269 Global.saveUploadAmount(0);
271 getTagTable().createTable();
272 notebookTable.createTable(true);
273 noteTable.createTable();
274 deletedTable.createTable();
275 searchTable.createTable();
276 watchFolderTable.createTable();
277 invalidXMLTable.createTable();
278 syncTable.createTable();
281 public void createIndexTables() {
282 wordsTable.createTable();
285 public void createResourceTables() {
286 noteTable.noteResourceTable.createTable();
289 public Connection getConnection() {
292 public Connection getIndexConnection() {
295 public Connection getResourceConnection() {
299 //***************************************************************
300 //* Table get methods
301 //***************************************************************
302 public DeletedTable getDeletedTable() {
305 public TagTable getTagTable() {
308 public NoteTable getNoteTable() {
311 public NotebookTable getNotebookTable() {
312 return notebookTable;
314 public SavedSearchTable getSavedSearchTable() {
317 public WatchFolderTable getWatchFolderTable() {
318 return watchFolderTable;
320 public WordsTable getWordsTable() {
323 public InvalidXMLTable getInvalidXMLTable() {
324 return invalidXMLTable;
326 public SyncTable getSyncTable() {
329 public LinkedNotebookTable getLinkedNotebookTable() {
330 return linkedNotebookTable;
332 public SharedNotebookTable getSharedNotebookTable() {
333 return sharedNotebookTable;
335 public SystemIconTable getSystemIconTable() {
336 return systemIconTable;
338 public InkImagesTable getInkImagesTable() {
339 return inkImagesTable;
342 //****************************************************************
343 //* Begin/End transactions
344 //****************************************************************
345 public void beginTransaction() {
347 NSqlQuery query = new NSqlQuery(getConnection());
348 if (!query.exec("Begin Transaction"))
349 logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
352 public void commitTransaction() {
353 NSqlQuery query = new NSqlQuery(getConnection());
355 if (!query.exec("Commit"))
356 logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
359 //****************************************************************
360 //* Check if a table exists
361 //****************************************************************
362 public boolean dbTableExists(String name) {
363 NSqlQuery query = new NSqlQuery(getConnection());
364 query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
365 query.bindValue(":name", name.toUpperCase());
373 //****************************************************************
374 //* Check if a row in a table exists
375 //****************************************************************
376 public boolean dbTableColumnExists(String tableName, String columnName) {
377 NSqlQuery query = new NSqlQuery(getConnection());
378 query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
379 query.bindValue(":name", tableName.toUpperCase());
380 query.bindValue(":column", columnName);