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 com.trolltech.qt.sql.QJdbc;
28 import cx.fbn.nevernote.Global;
29 import cx.fbn.nevernote.sql.driver.NSqlQuery;
30 import cx.fbn.nevernote.utilities.ApplicationLogger;
33 public class DatabaseConnection {
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;
51 public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
53 dbSetup(url, userid, password, cypherPassword);
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);
71 // Compact the database
72 public void compactDatabase() {
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);
82 Class.forName("org.h2.Driver");
83 } catch (ClassNotFoundException e1) {
92 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
93 boolean dbExists = f.exists();
95 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
99 String passwordString = null;
100 if (cypherPassword==null || cypherPassword.trim().equals(""))
101 passwordString = userPassword;
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) {
111 // If it doesn't exist and we are the main thread, then we need to create stuff.
114 Global.setAutomaticLogin(false);
117 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
121 public void dbShutdown() {
122 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
125 } catch (SQLException e) {
128 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
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);");
140 Global.setDatabaseVersion(version);
142 if (version.equals("0.86")) {
143 /* sharedNotebookTable.dropTable();
144 linkedNotebookTable.dropTable();
146 executeSql("alter table notebook drop column publishingUri");
147 executeSql("alter table notebook drop column publishingOrder");
148 executeSql("alter table notebook drop column publishingAscending");
149 executeSql("alter table notebook drop column publishingPublicDescription");
150 executeSql("alter table notebook drop column stack");
151 executeSql("alter table notebook drop column icon");
152 executeSql("alter table tag drop column icon");
153 executeSql("alter table SavedSearch drop column icon");
155 executeSql("drop index NOTE_THUMBNAIL_INDEX;");
156 executeSql("drop index NOTE_EXPUNGED_INDEX;");
157 executeSql("drop index NOTE_DUEDATE_INDEX;");
158 executeSql("drop index RESOURCES_GUID_INDEX;");
160 executeSql("alter table notebook add column publishingUri VarChar");
161 executeSql("alter table notebook add column publishingOrder Integer");
162 executeSql("alter table notebook add column publishingAscending VarChar");
163 executeSql("alter table notebook add column publishingPublicDescription varchar");
164 executeSql("alter table notebook add column stack varchar");
165 executeSql("alter table notebook add column icon blob");
166 executeSql("alter table tag add column icon blob");
167 executeSql("alter table SavedSearch add column icon blob");
169 executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
170 executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
171 executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
172 executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
173 executeSql("update note set thumbnailneeded=true, thumbnail=null;");
175 sharedNotebookTable.createTable();
176 linkedNotebookTable.createTable();
179 executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
180 executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
181 Global.setDatabaseVersion(version);
185 public void executeSql(String sql) {
186 NSqlQuery query = new NSqlQuery(conn);
190 public void checkDatabaseVersion() {
191 if (!Global.getDatabaseVersion().equals("0.86")) {
192 upgradeDb(Global.getDatabaseVersion());
194 if (!Global.getDatabaseVersion().equals("0.95")) {
195 upgradeDb(Global.getDatabaseVersion());
200 public void backupDatabase(int highSequence, long date) {
205 public void createTables() {
206 Global.setDatabaseVersion("0.85");
207 // Global.setDatabaseVersion("0.95");
208 Global.setAutomaticLogin(false);
209 Global.saveCurrentNoteGuid("");
210 Global.saveUploadAmount(0);
212 getTagTable().createTable();
213 notebookTable.createTable();
214 noteTable.createTable();
215 deletedTable.createTable();
216 searchTable.createTable();
217 watchFolderTable.createTable();
218 invalidXMLTable.createTable();
219 wordsTable.createTable();
220 syncTable.createTable();
224 public Connection getConnection() {
228 //***************************************************************
229 //* Table get methods
230 //***************************************************************
231 public DeletedTable getDeletedTable() {
234 public TagTable getTagTable() {
237 public NoteTable getNoteTable() {
240 public NotebookTable getNotebookTable() {
241 return notebookTable;
243 public SavedSearchTable getSavedSearchTable() {
246 public WatchFolderTable getWatchFolderTable() {
247 return watchFolderTable;
249 public WordsTable getWordsTable() {
252 public InvalidXMLTable getInvalidXMLTable() {
253 return invalidXMLTable;
255 public SyncTable getSyncTable() {
258 public LinkedNotebookTable getLinkedNotebookTable() {
259 return linkedNotebookTable;
261 public SharedNotebookTable getSharedNotebookTable() {
262 return sharedNotebookTable;