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 InkImagesTable inkImagesTable;
46 private SyncTable syncTable;
47 private SystemIconTable systemIconTable;
48 private final ApplicationLogger logger;
49 private Connection conn;
53 public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
55 dbSetup(url, userid, password, cypherPassword);
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);
75 // Compact the database
76 public void compactDatabase() {
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);
86 Class.forName("org.h2.Driver");
87 } catch (ClassNotFoundException e1) {
96 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
97 boolean dbExists = f.exists();
99 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
103 String passwordString = null;
104 if (cypherPassword==null || cypherPassword.trim().equals(""))
105 passwordString = userPassword;
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) {
115 // If it doesn't exist and we are the main thread, then we need to create stuff.
118 Global.setAutomaticLogin(false);
121 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
125 public void dbShutdown() {
126 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
129 } catch (SQLException e) {
132 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
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);");
144 Global.setDatabaseVersion(version);
146 if (version.equals("0.86")) {
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 notebook add column linked boolean");
157 executeSql("alter table tag add column realname varchar");
158 executeSql("alter table tag add column linked boolean");
159 executeSql("alter table tag add column icon blob");
160 executeSql("alter table tag add column notebookguid varchar");
161 executeSql("alter table SavedSearch add column icon blob");
163 executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
164 executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
165 executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
166 executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
167 executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
169 executeSql("update note set thumbnailneeded=true, thumbnail=null;");
170 executeSql("update notebook set publishingUri='', " +
171 "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
172 "publishingPublicDescription='', linked=false");
173 executeSql("update tag set linked=false, realname='', notebookguid=''");
175 sharedNotebookTable.createTable();
176 linkedNotebookTable.createTable();
177 systemIconTable.createTable();
178 inkImagesTable.createTable();
181 executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
182 executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
183 executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
184 executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
185 executeSql("Update note set indexneeded='true'");
186 executeSql("Update noteresources set indexneeded='true'");
187 Global.setDatabaseVersion(version);
191 public void executeSql(String sql) {
192 NSqlQuery query = new NSqlQuery(conn);
196 public void checkDatabaseVersion() {
197 if (!Global.getDatabaseVersion().equals("0.86")) {
198 upgradeDb(Global.getDatabaseVersion());
200 if (!Global.getDatabaseVersion().equals("0.95")) {
201 upgradeDb(Global.getDatabaseVersion());
206 public void backupDatabase(int highSequence, long date) {
211 public void createTables() {
212 Global.setDatabaseVersion("0.85");
213 Global.setAutomaticLogin(false);
214 Global.saveCurrentNoteGuid("");
215 Global.saveUploadAmount(0);
217 getTagTable().createTable();
218 notebookTable.createTable(true);
219 noteTable.createTable();
220 deletedTable.createTable();
221 searchTable.createTable();
222 watchFolderTable.createTable();
223 invalidXMLTable.createTable();
224 wordsTable.createTable();
225 syncTable.createTable();
228 public Connection getConnection() {
232 //***************************************************************
233 //* Table get methods
234 //***************************************************************
235 public DeletedTable getDeletedTable() {
238 public TagTable getTagTable() {
241 public NoteTable getNoteTable() {
244 public NotebookTable getNotebookTable() {
245 return notebookTable;
247 public SavedSearchTable getSavedSearchTable() {
250 public WatchFolderTable getWatchFolderTable() {
251 return watchFolderTable;
253 public WordsTable getWordsTable() {
256 public InvalidXMLTable getInvalidXMLTable() {
257 return invalidXMLTable;
259 public SyncTable getSyncTable() {
262 public LinkedNotebookTable getLinkedNotebookTable() {
263 return linkedNotebookTable;
265 public SharedNotebookTable getSharedNotebookTable() {
266 return sharedNotebookTable;
268 public SystemIconTable getSystemIconTable() {
269 return systemIconTable;
271 public InkImagesTable getInkImagesTable() {
272 return inkImagesTable;