OSDN Git Service

Add the ability to pin notes.
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / DatabaseConnection.java
1 /*
2  * This file is part of NixNote 
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 cx.fbn.nevernote.Global;
27 import cx.fbn.nevernote.sql.driver.NSqlQuery;
28 import cx.fbn.nevernote.utilities.ApplicationLogger;
29
30
31 public class DatabaseConnection {
32         // Table helpers
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;
50         int throttle;
51         int id;
52
53         
54         public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String userid, String password, String cypherPassword, int throttle) {
55                 logger = l;
56                 this.throttle = throttle;
57                 dbSetup(url, iurl, rurl, userid, password, cypherPassword);
58         }
59         
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);
74         }
75         
76         
77         // Compact the database
78         public void compactDatabase() {
79                 
80         }
81         
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);
85
86                 
87                 try {
88                         Class.forName("org.h2.Driver");
89                 } catch (ClassNotFoundException e1) {
90                         e1.printStackTrace();
91                         System.exit(16);
92                 }
93                 
94 //              QJdbc.initialize();
95                 
96                 setupTables();
97                 
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();
104                 
105                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
106                 
107                 String passwordString = null;
108                 try {
109                         
110                         if (cypherPassword==null || cypherPassword.trim().equals(""))
111                                 passwordString = userPassword;
112                         else
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);
117                         if (throttle == 0) {
118                                 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
119                         } else {
120                                 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
121                         }
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) {
126                         e.printStackTrace();
127                         return;
128                 }
129                 
130                 // If it doesn't exist and we are the main thread, then we need to create stuff.
131                 if (!dbExists)  {
132                         createTables();
133                         Global.setAutomaticLogin(false);
134                 }               
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')";
142                                 query.exec(linkcmd);
143                                 query.exec("insert into noteresources (select * from oldnoteresources)");
144                                 query.exec("Drop table oldnoteresources;");
145                                 query.exec("Update noteresources set indexneeded='true'");
146                                 
147                         }
148                 }
149                 if (!indexDbExists)  {
150                         createIndexTables();
151                         executeSql("Update note set indexneeded='true'");
152                 }
153                 
154                 // If we encrypted/decrypted it the last time, we need to reconnect the tables.
155 //              if (Global.relinkTables) {
156 //                      NSqlQuery query = new NSqlQuery(conn);
157 //                      query.exec("Drop table NoteResources;");
158 //                      String linkcmd = "create linked table NoteResources "
159 //                              +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
160 //                      System.out.println(linkcmd);
161 //                      query.exec(linkcmd);
162 //                      System.err.println(query.lastError());
163 //                      Global.relinkTables = false;
164 //              }
165                 
166                 
167                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
168         }
169         
170         
171         public void dbShutdown() {
172                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
173                 try {
174                         conn.close();
175                 } catch (SQLException e) {
176                         e.printStackTrace();
177                 }
178                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
179         }
180         
181         public void upgradeDb(String version) {
182                 if (version.equals("0.85")) {
183                         executeSql("alter table note add column titleColor integer");
184                         executeSql("alter table note add column thumbnail blob");
185                         executeSql("alter table note add column thumbnailneeded boolean");
186                         executeSql("Update note set thumbnailneeded = true;");
187                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
188                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
189                         version = "0.86";
190                         Global.setDatabaseVersion(version);
191                 } 
192                 if (version.equals("0.86")) {
193         
194                         executeSql("alter table notebook add column publishingUri VarChar");
195                         executeSql("alter table notebook add column publishingOrder Integer");
196                         executeSql("alter table notebook add column publishingAscending Boolean");
197                         executeSql("alter table notebook add column publishingPublicDescription varchar");
198                         executeSql("alter table notebook add column stack varchar");
199                         executeSql("alter table notebook add column icon blob");
200                         executeSql("alter table notebook add column readOnly boolean");
201                         executeSql("alter table notebook add column linked boolean");
202                         
203                         executeSql("alter table tag add column realname varchar");
204                         executeSql("alter table tag add column linked boolean");
205                         executeSql("alter table tag add column icon blob");
206                         executeSql("alter table tag add column notebookguid varchar");
207                         executeSql("alter table SavedSearch add column icon blob");
208
209                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
210                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
211                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
212                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
213                         
214                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
215                         executeSql("update notebook set publishingUri='', " +
216                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
217                                         "publishingPublicDescription='', linked=false");
218                         executeSql("update tag set linked=false, realname='', notebookguid=''");
219                         
220                         sharedNotebookTable.createTable();
221                         linkedNotebookTable.createTable();
222                         systemIconTable.createTable();
223                         inkImagesTable.createTable();
224                         
225                         version = "0.95";
226                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
227                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
228                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
229                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
230                         Global.setDatabaseVersion(version);
231                 } 
232                 if (version.equals("0.95")) {
233                         if (dbTableExists("words"))
234                                 executeSql("Drop table words;");
235                         if (dbTableExists("NoteResources"))
236                                 executeSql("Drop table NoteResources;");
237                 }
238                 if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
239                         executeSql("alter table note add column ORIGINAL_GUID VarChar");
240                         executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
241                 }
242                 if (!dbTableColumnExists("NOTEBOOK", "NARROW_SORT_ORDER")) {
243                         executeSql("alter table notebook add column NARROW_SORT_ORDER integer");
244                         executeSql("update notebook set NARROW_SORT_ORDER = -1");
245
246                         executeSql("alter table notebook add column WIDE_SORT_ORDER integer");
247                         executeSql("update notebook set WIDE_SORT_ORDER = -1");
248                         
249                         executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
250                         executeSql("update notebook set WIDE_SORT_COLUMN = -1");
251                         
252                         executeSql("alter table notebook add column NARROW_SORT_COLUMN integer");
253                         executeSql("update notebook set NARROW_SORT_COLUMN = -1");
254                 }
255                 if (!dbTableColumnExists("NOTE", "PINNED")) {
256                         executeSql("alter table note add column pinned integer");
257                         executeSql("update note set pinned = 0");
258                 }
259         }
260         
261         public void executeSql(String sql) {
262                 NSqlQuery query = new NSqlQuery(conn);
263                 query.exec(sql);        
264         }
265         
266         public void checkDatabaseVersion() {
267                 if (!Global.getDatabaseVersion().equals("0.86")) {
268                         upgradeDb(Global.getDatabaseVersion());
269                 }
270                 if (!Global.getDatabaseVersion().equals("0.95")) {
271                         upgradeDb(Global.getDatabaseVersion());
272                 }
273                 if (!Global.getDatabaseVersion().equals("0.97")) {
274                         upgradeDb(Global.getDatabaseVersion());
275                 }
276         }
277         
278
279         public void backupDatabase(int highSequence, long date) {
280                 
281         }
282         
283         
284         public void createTables() {
285                 Global.setDatabaseVersion("0.85");
286                 Global.setAutomaticLogin(false);
287                 Global.saveCurrentNoteGuid("");
288                 Global.saveUploadAmount(0);
289                 
290                 getTagTable().createTable();
291                 notebookTable.createTable(true);
292                 noteTable.createTable();
293                 deletedTable.createTable();             
294                 searchTable.createTable();
295                 watchFolderTable.createTable();
296                 invalidXMLTable.createTable();
297                 syncTable.createTable();
298         }
299         
300         public void createIndexTables() {
301                 wordsTable.createTable();
302         }
303         
304         public void createResourceTables() {
305                 noteTable.noteResourceTable.createTable();
306         }
307         
308         public Connection getConnection() {
309                 return conn;
310         }
311         public Connection getIndexConnection() {
312                 return  indexConn;
313         }
314         public Connection getResourceConnection() {
315                 return resourceConn;
316         }
317         
318         //***************************************************************
319         //* Table get methods
320         //***************************************************************
321         public DeletedTable getDeletedTable() {
322                 return deletedTable;
323         }
324         public TagTable getTagTable() {
325                 return tagTable;
326         }
327         public NoteTable getNoteTable() {
328                 return noteTable;
329         }
330         public NotebookTable getNotebookTable() {
331                 return notebookTable;
332         }
333         public SavedSearchTable getSavedSearchTable() {
334                 return searchTable;
335         }
336         public WatchFolderTable getWatchFolderTable() {
337                 return watchFolderTable;
338         }
339         public WordsTable getWordsTable() {
340                 return wordsTable;
341         }
342         public InvalidXMLTable getInvalidXMLTable() {
343                 return invalidXMLTable;
344         }
345         public SyncTable getSyncTable() {
346                 return syncTable;
347         }
348         public LinkedNotebookTable getLinkedNotebookTable() {
349                 return linkedNotebookTable;
350         }
351         public SharedNotebookTable getSharedNotebookTable() {
352                 return sharedNotebookTable;
353         }
354         public SystemIconTable getSystemIconTable() {
355                 return systemIconTable;
356         }
357         public InkImagesTable getInkImagesTable() {
358                 return inkImagesTable;
359         }
360
361         //****************************************************************
362         //* Begin/End transactions
363         //****************************************************************
364         public void beginTransaction() {
365                 commitTransaction();
366         NSqlQuery query = new NSqlQuery(getConnection());                                                       
367                 if (!query.exec("Begin Transaction"))
368                         logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
369
370         }
371         public void commitTransaction() {
372         NSqlQuery query = new NSqlQuery(getConnection());
373                                                         
374                 if (!query.exec("Commit"))
375                         logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
376         }
377
378         //****************************************************************
379         //* Check if a table exists
380         //****************************************************************
381         public boolean dbTableExists(String name) {
382         NSqlQuery query = new NSqlQuery(getConnection());
383         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
384         query.bindValue(":name", name.toUpperCase());
385         query.exec();
386         if (query.next())
387                 return true;
388         else
389                 return false;
390         }
391         
392         //****************************************************************
393         //* Check if a row in a table exists
394         //****************************************************************
395         public boolean dbTableColumnExists(String tableName, String columnName) {
396         NSqlQuery query = new NSqlQuery(getConnection());
397         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
398         query.bindValue(":name", tableName.toUpperCase());
399         query.bindValue(":column", columnName);
400         query.exec();
401         if (query.next())
402                 return true;
403         else
404                 return false;
405         }
406 }