OSDN Git Service

GitHub最初のコミット(SourceForge.jp 128fa38 2013-02-28 15:28:57 と同じ内容)
[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         // ICHANGED
47         private HistoryTable historyTable;
48         private ExcludedTable excludedTable;
49         private StaredTable staredTable;
50         
51         private final ApplicationLogger         logger;
52         private Connection                                      conn;
53         private Connection                                      indexConn;
54         private Connection                                      resourceConn;
55         // ICHANGED
56         private Connection behaviorConn;
57         
58         int throttle;
59         int id;
60
61         // ICHANGED String burlを追加
62         public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String burl, String userid, String password, String cypherPassword, int throttle) {
63                 logger = l;
64                 this.throttle = throttle;
65                 // ICHANGED burlを追加
66                 dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
67         }
68         
69         private void setupTables() {
70                 tagTable = new TagTable(logger, this);
71                 notebookTable = new NotebookTable(logger, this);
72                 noteTable = new NoteTable(logger, this);
73                 deletedTable = new DeletedTable(logger, this);
74                 searchTable = new SavedSearchTable(logger, this);       
75                 watchFolderTable = new WatchFolderTable(logger, this);
76                 invalidXMLTable = new InvalidXMLTable(logger, this);
77                 wordsTable = new WordsTable(logger, this);
78                 syncTable = new SyncTable(logger, this);
79                 linkedNotebookTable = new LinkedNotebookTable(logger, this);
80                 sharedNotebookTable = new SharedNotebookTable(logger, this);
81                 systemIconTable = new SystemIconTable(logger, this);
82                 inkImagesTable = new InkImagesTable(logger, this);
83                 // ICHANGED
84                 historyTable = new HistoryTable(logger, this);
85                 excludedTable = new ExcludedTable(logger, this);
86                 staredTable = new StaredTable(logger, this);
87                 
88         }
89         
90         
91         // Compact the database
92         public void compactDatabase() {
93                 
94         }
95         
96         // Initialize the database connection
97         // ICHANGED String behaviorUrlを追加
98         public void dbSetup(String url,String indexUrl, String resourceUrl, String behaviorUrl, String userid, String userPassword, String cypherPassword) {
99                 logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
100
101                 
102                 try {
103                         Class.forName("org.h2.Driver");
104                 } catch (ClassNotFoundException e1) {
105                         e1.printStackTrace();
106                         System.exit(16);
107                 }
108                 
109 //              QJdbc.initialize();
110                 
111                 setupTables();
112                 
113                 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
114                 boolean dbExists = f.exists(); 
115                 f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
116                 boolean indexDbExists = f.exists(); 
117                 f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
118                 boolean resourceDbExists = f.exists();
119                 // ICHANGED
120                 f = Global.getFileManager().getDbDirFile(Global.behaviorDatabaseName + ".h2.db");
121                 boolean behaviorDbExists = f.exists();
122                 
123                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
124                 
125                 String passwordString = null;
126                 try {
127                         
128                         if (cypherPassword==null || cypherPassword.trim().equals(""))
129                                 passwordString = userPassword;
130                         else
131                                 passwordString = cypherPassword+" "+userPassword;
132 //                      conn = DriverManager.getConnection(url,userid,passwordString);
133 //                      conn = DriverManager.getConnection(url,userid,passwordString);
134 //                      conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
135                         if (throttle == 0) {
136                                 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
137                         } else {
138                                 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
139                         }
140                         indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
141                         resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
142                         // ICHANGED
143                         behaviorConn = DriverManager.getConnection(behaviorUrl, userid, passwordString);
144                         
145 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
146                 } catch (SQLException e) {
147                         e.printStackTrace();
148                         return;
149                 }
150                 
151                 // If it doesn't exist and we are the main thread, then we need to create stuff.
152                 if (!dbExists)  {
153                         createTables();
154                         Global.setAutomaticLogin(false);
155                 }               
156                 if (!resourceDbExists) {
157                         createResourceTables();
158                         if (dbTableExists("NoteResources")) {
159                                 // Begin migration of database
160                                 NSqlQuery query = new NSqlQuery(resourceConn);
161                                 String linkcmd = "create linked table oldnoteresources "+
162                                                 "('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
163                                 query.exec(linkcmd);
164                                 query.exec("insert into noteresources (select * from oldnoteresources)");
165                                 query.exec("Drop table oldnoteresources;");
166                                 query.exec("Update noteresources set indexneeded='true'");
167                                 
168                         }
169                 }
170                 if (!indexDbExists)  {
171                         createIndexTables();
172                         executeSql("Update note set indexneeded='true'");
173                 }
174                 
175                 // ICHANGED
176                 // 操作履歴テーブルと除外ノートテーブルとスター付きノートテーブルを作る
177                 if (!behaviorDbExists) {
178                         createHistoryTables();
179                         createExcludedTables();
180                         createStaredTables();
181                 }
182                 
183                 // If we encrypted/decrypted it the last time, we need to reconnect the tables.
184 //              if (Global.relinkTables) {
185 //                      NSqlQuery query = new NSqlQuery(conn);
186 //                      query.exec("Drop table NoteResources;");
187 //                      String linkcmd = "create linked table NoteResources "
188 //                              +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
189 //                      System.out.println(linkcmd);
190 //                      query.exec(linkcmd);
191 //                      System.err.println(query.lastError());
192 //                      Global.relinkTables = false;
193 //              }
194                 
195                 
196                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
197         }
198         
199         
200         public void dbShutdown() {
201                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
202                 try {
203                         conn.close();
204                 } catch (SQLException e) {
205                         e.printStackTrace();
206                 }
207                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
208         }
209         
210         public void upgradeDb(String version) {
211                 if (version.equals("0.85")) {
212                         executeSql("alter table note add column titleColor integer");
213                         executeSql("alter table note add column thumbnail blob");
214                         executeSql("alter table note add column thumbnailneeded boolean");
215                         executeSql("Update note set thumbnailneeded = true;");
216                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
217                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
218                         version = "0.86";
219                         Global.setDatabaseVersion(version);
220                 } 
221                 if (version.equals("0.86")) {
222         
223                         executeSql("alter table notebook add column publishingUri VarChar");
224                         executeSql("alter table notebook add column publishingOrder Integer");
225                         executeSql("alter table notebook add column publishingAscending Boolean");
226                         executeSql("alter table notebook add column publishingPublicDescription varchar");
227                         executeSql("alter table notebook add column stack varchar");
228                         executeSql("alter table notebook add column icon blob");
229                         executeSql("alter table notebook add column readOnly boolean");
230                         executeSql("alter table notebook add column linked boolean");
231                         
232                         executeSql("alter table tag add column realname varchar");
233                         executeSql("alter table tag add column linked boolean");
234                         executeSql("alter table tag add column icon blob");
235                         executeSql("alter table tag add column notebookguid varchar");
236                         executeSql("alter table SavedSearch add column icon blob");
237
238                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
239                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
240                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
241                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
242                         
243                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
244                         executeSql("update notebook set publishingUri='', " +
245                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
246                                         "publishingPublicDescription='', linked=false");
247                         executeSql("update tag set linked=false, realname='', notebookguid=''");
248                         
249                         sharedNotebookTable.createTable();
250                         linkedNotebookTable.createTable();
251                         systemIconTable.createTable();
252                         inkImagesTable.createTable();
253                         
254                         version = "0.95";
255                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
256                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
257                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
258                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
259                         Global.setDatabaseVersion(version);
260                 } 
261                 if (version.equals("0.95")) {
262                         if (dbTableExists("words"))
263                                 executeSql("Drop table words;");
264                         if (dbTableExists("NoteResources"))
265                                 executeSql("Drop table NoteResources;");
266                 }
267                 if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
268                         executeSql("alter table note add column ORIGINAL_GUID VarChar");
269                         executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
270                 }
271                 if (!dbTableColumnExists("NOTEBOOK", "NARROW_SORT_ORDER")) {
272                         executeSql("alter table notebook add column NARROW_SORT_ORDER integer");
273                         executeSql("update notebook set NARROW_SORT_ORDER = -1");
274
275                         executeSql("alter table notebook add column WIDE_SORT_ORDER integer");
276                         executeSql("update notebook set WIDE_SORT_ORDER = -1");
277                         
278                         executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
279                         executeSql("update notebook set WIDE_SORT_COLUMN = -1");
280                         
281                         executeSql("alter table notebook add column NARROW_SORT_COLUMN integer");
282                         executeSql("update notebook set NARROW_SORT_COLUMN = -1");
283                 }
284                 if (!dbTableColumnExists("NOTE", "PINNED")) {
285                         executeSql("alter table note add column pinned integer");
286                         executeSql("update note set pinned = 0");
287                 }
288                 if (!dbTableColumnExists("NOTE", "ATTRIBUTECONTENTCLASS")) {
289                         executeSql("alter table note add column attributeContentClass VarChar");
290                         executeSql("update note set attributeContentClass = ''");
291                 }
292         }
293         
294         public void executeSql(String sql) {
295                 NSqlQuery query = new NSqlQuery(conn);
296                 query.exec(sql);        
297         }
298         
299         public void checkDatabaseVersion() {
300                 if (!Global.getDatabaseVersion().equals("0.86")) {
301                         upgradeDb(Global.getDatabaseVersion());
302                 }
303                 if (!Global.getDatabaseVersion().equals("0.95")) {
304                         upgradeDb(Global.getDatabaseVersion());
305                 }
306                 if (!Global.getDatabaseVersion().equals("0.97")) {
307                         upgradeDb(Global.getDatabaseVersion());
308                 }
309         }
310         
311
312         public void backupDatabase(int highSequence, long date) {
313                 
314         }
315         
316         
317         public void createTables() {
318                 Global.setDatabaseVersion("0.85");
319                 Global.setAutomaticLogin(false);
320                 Global.saveCurrentNoteGuid("");
321                 Global.saveUploadAmount(0);
322                 
323                 getTagTable().createTable();
324                 notebookTable.createTable(true);
325                 noteTable.createTable();
326                 deletedTable.createTable();             
327                 searchTable.createTable();
328                 watchFolderTable.createTable();
329                 invalidXMLTable.createTable();
330                 syncTable.createTable();
331         }
332         
333         public void createIndexTables() {
334                 wordsTable.createTable();
335         }
336         
337         public void createResourceTables() {
338                 noteTable.noteResourceTable.createTable();
339         }
340         
341         // ICHANGED
342         public void createHistoryTables() {
343                 historyTable.createTable();
344         }
345         
346         // ICHANGED
347         public void createExcludedTables() {
348                 excludedTable.createTable();
349         }
350         
351         // ICHANGED
352         public void createStaredTables() {
353                 staredTable.createTable();
354         }
355         
356         public Connection getConnection() {
357                 return conn;
358         }
359         public Connection getIndexConnection() {
360                 return  indexConn;
361         }
362         public Connection getResourceConnection() {
363                 return resourceConn;
364         }
365         
366         // ICHANGED
367         public Connection getBehaviorConnection() {
368                 return behaviorConn;
369         }
370         
371         //***************************************************************
372         //* Table get methods
373         //***************************************************************
374         public DeletedTable getDeletedTable() {
375                 return deletedTable;
376         }
377         public TagTable getTagTable() {
378                 return tagTable;
379         }
380         public NoteTable getNoteTable() {
381                 return noteTable;
382         }
383         public NotebookTable getNotebookTable() {
384                 return notebookTable;
385         }
386         public SavedSearchTable getSavedSearchTable() {
387                 return searchTable;
388         }
389         public WatchFolderTable getWatchFolderTable() {
390                 return watchFolderTable;
391         }
392         public WordsTable getWordsTable() {
393                 return wordsTable;
394         }
395         public InvalidXMLTable getInvalidXMLTable() {
396                 return invalidXMLTable;
397         }
398         public SyncTable getSyncTable() {
399                 return syncTable;
400         }
401         public LinkedNotebookTable getLinkedNotebookTable() {
402                 return linkedNotebookTable;
403         }
404         public SharedNotebookTable getSharedNotebookTable() {
405                 return sharedNotebookTable;
406         }
407         public SystemIconTable getSystemIconTable() {
408                 return systemIconTable;
409         }
410         public InkImagesTable getInkImagesTable() {
411                 return inkImagesTable;
412         }
413         
414         // ICHANGED
415         public HistoryTable getHistoryTable() {
416                 return historyTable;
417         }
418         
419         // ICHANGED
420         public ExcludedTable getExcludedTable() {
421                 return excludedTable;
422         }
423         
424         // ICHANGED
425         public StaredTable getStaredTable() {
426                 return staredTable;
427         }
428
429         //****************************************************************
430         //* Begin/End transactions
431         //****************************************************************
432         public void beginTransaction() {
433                 commitTransaction();
434         NSqlQuery query = new NSqlQuery(getConnection());                                                       
435                 if (!query.exec("Begin Transaction"))
436                         logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
437
438         }
439         public void commitTransaction() {
440         NSqlQuery query = new NSqlQuery(getConnection());
441                                                         
442                 if (!query.exec("Commit"))
443                         logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
444         }
445
446         //****************************************************************
447         //* Check if a table exists
448         //****************************************************************
449         public boolean dbTableExists(String name) {
450         NSqlQuery query = new NSqlQuery(getConnection());
451         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
452         query.bindValue(":name", name.toUpperCase());
453         query.exec();
454         if (query.next())
455                 return true;
456         else
457                 return false;
458         }
459         
460         //****************************************************************
461         //* Check if a row in a table exists
462         //****************************************************************
463         public boolean dbTableColumnExists(String tableName, String columnName) {
464         NSqlQuery query = new NSqlQuery(getConnection());
465         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
466         query.bindValue(":name", tableName.toUpperCase());
467         query.bindValue(":column", columnName);
468         query.exec();
469         if (query.next())
470                 return true;
471         else
472                 return false;
473         }
474 }