OSDN Git Service

初期状態のままだと全文検索が機能していなかった問題を修正した
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / DatabaseConnection.java
1 /*
2  * This file is part of NixNote/NeighborNote 
3  * Copyright 2009 Randy Baumgarte
4  * Copyright 2013 Yuki Takahashi
5  * 
6  * This file may be licensed under the terms of of the
7  * GNU General Public License Version 2 (the ``GPL'').
8  *
9  * Software distributed under the License is distributed
10  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
11  * express or implied. See the GPL for the specific language
12  * governing rights and limitations.
13  *
14  * You should have received a copy of the GPL along with this
15  * program. If not, go to http://www.gnu.org/licenses/gpl.html
16  * or write to the Free Software Foundation, Inc.,
17  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18  *
19 */
20 package cx.fbn.nevernote.sql;
21
22 import java.io.File;
23 import java.sql.Connection;
24 import java.sql.DriverManager;
25 import java.sql.SQLException;
26
27 import cx.fbn.nevernote.Global;
28 import cx.fbn.nevernote.sql.driver.NSqlQuery;
29 import cx.fbn.nevernote.utilities.ApplicationLogger;
30
31
32 public class DatabaseConnection {
33         // Table helpers
34         private WordsTable                                      wordsTable;
35         private TagTable                                        tagTable;
36         private NotebookTable                           notebookTable;
37         private NoteTable                                       noteTable;
38         private DeletedTable                            deletedTable;
39         private SavedSearchTable                        searchTable;
40         private WatchFolderTable                        watchFolderTable;
41         private InvalidXMLTable                         invalidXMLTable;
42         private LinkedNotebookTable                     linkedNotebookTable;
43         private SharedNotebookTable                     sharedNotebookTable;
44         private InkImagesTable                          inkImagesTable;
45         private SyncTable                                       syncTable;
46         private SystemIconTable                         systemIconTable;
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         private Connection behaviorConn;
56         
57         int throttle;
58         int id;
59
60         public DatabaseConnection(ApplicationLogger l, String url, String iurl, String rurl, String burl, String userid, String password, String cypherPassword, int throttle) {
61                 logger = l;
62                 this.throttle = throttle;
63                 dbSetup(url, iurl, rurl, burl, userid, password, cypherPassword);
64         }
65         
66         private void setupTables() {
67                 tagTable = new TagTable(logger, this);
68                 notebookTable = new NotebookTable(logger, this);
69                 noteTable = new NoteTable(logger, this);
70                 deletedTable = new DeletedTable(logger, this);
71                 searchTable = new SavedSearchTable(logger, this);       
72                 watchFolderTable = new WatchFolderTable(logger, this);
73                 invalidXMLTable = new InvalidXMLTable(logger, this);
74                 wordsTable = new WordsTable(logger, this);
75                 syncTable = new SyncTable(logger, this);
76                 linkedNotebookTable = new LinkedNotebookTable(logger, this);
77                 sharedNotebookTable = new SharedNotebookTable(logger, this);
78                 systemIconTable = new SystemIconTable(logger, this);
79                 inkImagesTable = new InkImagesTable(logger, this);
80                 historyTable = new HistoryTable(logger, this);
81                 excludedTable = new ExcludedTable(logger, this);
82                 staredTable = new StaredTable(logger, this);
83                 
84         }
85         
86         
87         // Compact the database
88         public void compactDatabase() {
89                 
90         }
91         
92         // Initialize the database connection
93         public void dbSetup(String url,String indexUrl, String resourceUrl, String behaviorUrl, String userid, String userPassword, String cypherPassword) {
94                 logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
95
96                 
97                 try {
98                         Class.forName("org.h2.Driver");
99                 } catch (ClassNotFoundException e1) {
100                         e1.printStackTrace();
101                         System.exit(16);
102                 }
103                 
104 //              QJdbc.initialize();
105                 
106                 setupTables();
107                 
108                 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
109                 boolean dbExists = f.exists(); 
110                 f = Global.getFileManager().getDbDirFile(Global.indexDatabaseName + ".h2.db");
111                 boolean indexDbExists = f.exists(); 
112                 f = Global.getFileManager().getDbDirFile(Global.resourceDatabaseName + ".h2.db");
113                 boolean resourceDbExists = f.exists();
114                 f = Global.getFileManager().getDbDirFile(Global.behaviorDatabaseName + ".h2.db");
115                 boolean behaviorDbExists = f.exists();
116                 
117                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
118                 
119                 String passwordString = null;
120                 try {
121                         
122                         if (cypherPassword==null || cypherPassword.trim().equals(""))
123                                 passwordString = userPassword;
124                         else
125                                 passwordString = cypherPassword+" "+userPassword;
126 //                      conn = DriverManager.getConnection(url,userid,passwordString);
127 //                      conn = DriverManager.getConnection(url,userid,passwordString);
128 //                      conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
129                         if (throttle == 0) {
130                                 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
131                         } else {
132                                 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
133                         }
134                         indexConn = DriverManager.getConnection(indexUrl,userid,passwordString);
135                         resourceConn = DriverManager.getConnection(resourceUrl,userid,passwordString);
136                         behaviorConn = DriverManager.getConnection(behaviorUrl, userid, passwordString);
137                         
138 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
139                 } catch (SQLException e) {
140                         e.printStackTrace();
141                         return;
142                 }
143                 
144                 // If it doesn't exist and we are the main thread, then we need to create stuff.
145                 if (!dbExists)  {
146                         createTables();
147                         Global.setAutomaticLogin(false);
148                 }               
149                 if (!resourceDbExists) {
150                         createResourceTables();
151                         if (dbTableExists("NoteResources")) {
152                                 // Begin migration of database
153                                 NSqlQuery query = new NSqlQuery(resourceConn);
154                                 String linkcmd = "create linked table oldnoteresources "+
155                                                 "('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+"', 'NoteResources')";
156                                 query.exec(linkcmd);
157                                 query.exec("insert into noteresources (select * from oldnoteresources)");
158                                 query.exec("Drop table oldnoteresources;");
159                                 query.exec("Update noteresources set indexneeded='true'");
160                                 
161                         }
162                 }
163                 if (!indexDbExists)  {
164                         createIndexTables();
165                         executeSql("Update note set indexneeded='true'");
166                 }
167                 
168                 // 操作履歴テーブルと除外ノートテーブルとスター付きノートテーブルを作る
169                 if (!behaviorDbExists) {
170                         createHistoryTables();
171                         createExcludedTables();
172                         createStaredTables();
173                 }
174                 
175                 // If we encrypted/decrypted it the last time, we need to reconnect the tables.
176 //              if (Global.relinkTables) {
177 //                      NSqlQuery query = new NSqlQuery(conn);
178 //                      query.exec("Drop table NoteResources;");
179 //                      String linkcmd = "create linked table NoteResources "
180 //                              +"('org.h2.Driver', '"+url+"', '"+userid+"', '"+passwordString+ "', 'NoteResources')";
181 //                      System.out.println(linkcmd);
182 //                      query.exec(linkcmd);
183 //                      System.err.println(query.lastError());
184 //                      Global.relinkTables = false;
185 //              }
186                 
187                 
188                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
189         }
190         
191         
192         public void dbShutdown() {
193                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
194                 try {
195                         conn.close();
196                 } catch (SQLException e) {
197                         e.printStackTrace();
198                 }
199                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
200         }
201         
202         public void upgradeDb(String version) {
203                 if (version.equals("0.85")) {
204                         executeSql("alter table note add column titleColor integer");
205                         executeSql("alter table note add column thumbnail blob");
206                         executeSql("alter table note add column thumbnailneeded boolean");
207                         executeSql("Update note set thumbnailneeded = true;");
208                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
209                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
210                         version = "0.86";
211                         Global.setDatabaseVersion(version);
212                 } 
213                 if (version.equals("0.86")) {
214         
215                         executeSql("alter table notebook add column publishingUri VarChar");
216                         executeSql("alter table notebook add column publishingOrder Integer");
217                         executeSql("alter table notebook add column publishingAscending Boolean");
218                         executeSql("alter table notebook add column publishingPublicDescription varchar");
219                         executeSql("alter table notebook add column stack varchar");
220                         executeSql("alter table notebook add column icon blob");
221                         executeSql("alter table notebook add column readOnly boolean");
222                         executeSql("alter table notebook add column linked boolean");
223                         
224                         executeSql("alter table tag add column realname varchar");
225                         executeSql("alter table tag add column linked boolean");
226                         executeSql("alter table tag add column icon blob");
227                         executeSql("alter table tag add column notebookguid varchar");
228                         executeSql("alter table SavedSearch add column icon blob");
229
230                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
231                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
232                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
233                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
234                         
235                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
236                         executeSql("update notebook set publishingUri='', " +
237                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
238                                         "publishingPublicDescription='', linked=false");
239                         executeSql("update tag set linked=false, realname='', notebookguid=''");
240                         
241                         sharedNotebookTable.createTable();
242                         linkedNotebookTable.createTable();
243                         systemIconTable.createTable();
244                         inkImagesTable.createTable();
245                         
246                         version = "0.95";
247                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
248                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
249                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
250                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
251                         Global.setDatabaseVersion(version);
252                 } 
253                 if (version.equals("0.95")) {
254                         if (dbTableExists("words"))
255                                 executeSql("Drop table words;");
256                         if (dbTableExists("NoteResources"))
257                                 executeSql("Drop table NoteResources;");
258                 }
259                 if (!dbTableColumnExists("NOTE", "ORIGINAL_GUID")) {
260                         executeSql("alter table note add column ORIGINAL_GUID VarChar");
261                         executeSql("create index NOTE_ORIGINAL_GUID_INDEX on note (original_guid, guid);");
262                 }
263                 if (!dbTableColumnExists("NOTEBOOK", "NARROW_SORT_ORDER")) {
264                         executeSql("alter table notebook add column NARROW_SORT_ORDER integer");
265                         executeSql("update notebook set NARROW_SORT_ORDER = -1");
266
267                         executeSql("alter table notebook add column WIDE_SORT_ORDER integer");
268                         executeSql("update notebook set WIDE_SORT_ORDER = -1");
269                         
270                         executeSql("alter table notebook add column WIDE_SORT_COLUMN integer");
271                         executeSql("update notebook set WIDE_SORT_COLUMN = -1");
272                         
273                         executeSql("alter table notebook add column NARROW_SORT_COLUMN integer");
274                         executeSql("update notebook set NARROW_SORT_COLUMN = -1");
275                 }
276                 if (!dbTableColumnExists("NOTE", "PINNED")) {
277                         executeSql("alter table note add column pinned integer");
278                         executeSql("update note set pinned = 0");
279                 }
280                 if (!dbTableColumnExists("NOTE", "ATTRIBUTECONTENTCLASS")) {
281                         executeSql("alter table note add column attributeContentClass VarChar");
282                         executeSql("update note set attributeContentClass = ''");
283                 }
284                 
285                 // Evernoteサムネイルカラムを追加
286                 if (!dbTableColumnExists("NOTE", "ENTHUMBNAIL")) {
287                         executeSql("alter table note add column enThumbNail Blob");
288                 }
289                 
290                 // Apache Luceneを使った日本語検索のためのプレーンテキストノートコンテンツカラムを準備
291                 if (!dbTableColumnExists("NOTE", "CONTENTTEXT")) {
292                         executeSql("alter table note add column contentText VarChar");
293                         executeSql("update note set contentText = ''");
294                         NSqlQuery query = new NSqlQuery(conn);
295                         query.exec("Select guid, content from Note where contentText = ''");
296                         while (query.next()) {
297                                 String guid = query.valueString(0);
298                                 String content = query.valueString(1);
299                                 String contentText = Global.extractPlainText(content);
300                                 NSqlQuery query2 = new NSqlQuery(conn);
301                                 query2.prepare("update note set contentText=:contentText where guid=:guid");
302                                 query2.bindValue(":contentText", contentText);
303                                 query2.bindValue(":guid", guid);
304                                 query2.exec();
305                         }
306                         
307                         // Apache Luceneを使った全文検索のための準備
308                         query.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
309                         query.exec("CALL FTL_INIT()");
310                         
311                         Global.rebuildFullTextNoteTarget(this);
312                 }
313                 
314                 // Apache Luceneを使った日本語検索のためのプレーンテキストノートリソースカラムを準備
315                 NSqlQuery rQuery = new NSqlQuery(resourceConn);
316                 rQuery.exec("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='NOTERESOURCES' and COLUMN_NAME='RESOURCETEXT'");
317                 if (!rQuery.next()) {
318                         rQuery.exec("alter table noteResources add column resourceText VarChar");
319                         rQuery.exec("update noteResources set resourceText = ''");
320                         
321                         // Apache Luceneを使った全文検索のための準備
322                         rQuery.exec("CREATE ALIAS IF NOT EXISTS FTL_INIT FOR \"org.h2.fulltext.FullTextLuceneEx.init\"");
323                         rQuery.exec("CALL FTL_INIT()");
324                         
325                         Global.rebuildFullTextResourceTarget(this);
326                 }
327                 
328                 // 注意:ここから先でnoteテーブルとnoteResourcesテーブルの構造を変更するな。全文検索ができなくなる。
329         }
330         
331         public void executeSql(String sql) {
332                 NSqlQuery query = new NSqlQuery(conn);
333                 query.exec(sql);        
334         }
335         
336         public void checkDatabaseVersion() {
337                 if (!Global.getDatabaseVersion().equals("0.86")) {
338                         upgradeDb(Global.getDatabaseVersion());
339                 }
340                 if (!Global.getDatabaseVersion().equals("0.95")) {
341                         upgradeDb(Global.getDatabaseVersion());
342                 }
343                 if (!Global.getDatabaseVersion().equals("0.97")) {
344                         upgradeDb(Global.getDatabaseVersion());
345                 }
346         }
347         
348
349         public void backupDatabase(int highSequence, long date) {
350                 
351         }
352         
353         
354         public void createTables() {
355                 Global.setDatabaseVersion("0.85");
356                 Global.setAutomaticLogin(false);
357                 Global.saveCurrentNoteGuid("");
358                 Global.saveUploadAmount(0);
359                 
360                 getTagTable().createTable();
361                 notebookTable.createTable(true);
362                 noteTable.createTable();
363                 deletedTable.createTable();             
364                 searchTable.createTable();
365                 watchFolderTable.createTable();
366                 invalidXMLTable.createTable();
367                 syncTable.createTable();
368         }
369         
370         public void createIndexTables() {
371                 wordsTable.createTable();
372         }
373         
374         public void createResourceTables() {
375                 noteTable.noteResourceTable.createTable();
376         }
377         
378         public void createHistoryTables() {
379                 historyTable.createTable();
380         }
381         
382         public void createExcludedTables() {
383                 excludedTable.createTable();
384         }
385         
386         public void createStaredTables() {
387                 staredTable.createTable();
388         }
389         
390         public Connection getConnection() {
391                 return conn;
392         }
393         public Connection getIndexConnection() {
394                 return  indexConn;
395         }
396         public Connection getResourceConnection() {
397                 return resourceConn;
398         }
399         
400         public Connection getBehaviorConnection() {
401                 return behaviorConn;
402         }
403         
404         //***************************************************************
405         //* Table get methods
406         //***************************************************************
407         public DeletedTable getDeletedTable() {
408                 return deletedTable;
409         }
410         public TagTable getTagTable() {
411                 return tagTable;
412         }
413         public NoteTable getNoteTable() {
414                 return noteTable;
415         }
416         public NotebookTable getNotebookTable() {
417                 return notebookTable;
418         }
419         public SavedSearchTable getSavedSearchTable() {
420                 return searchTable;
421         }
422         public WatchFolderTable getWatchFolderTable() {
423                 return watchFolderTable;
424         }
425         public WordsTable getWordsTable() {
426                 return wordsTable;
427         }
428         public InvalidXMLTable getInvalidXMLTable() {
429                 return invalidXMLTable;
430         }
431         public SyncTable getSyncTable() {
432                 return syncTable;
433         }
434         public LinkedNotebookTable getLinkedNotebookTable() {
435                 return linkedNotebookTable;
436         }
437         public SharedNotebookTable getSharedNotebookTable() {
438                 return sharedNotebookTable;
439         }
440         public SystemIconTable getSystemIconTable() {
441                 return systemIconTable;
442         }
443         public InkImagesTable getInkImagesTable() {
444                 return inkImagesTable;
445         }
446         
447         public HistoryTable getHistoryTable() {
448                 return historyTable;
449         }
450         
451         public ExcludedTable getExcludedTable() {
452                 return excludedTable;
453         }
454         
455         public StaredTable getStaredTable() {
456                 return staredTable;
457         }
458
459         //****************************************************************
460         //* Begin/End transactions
461         //****************************************************************
462         public void beginTransaction() {
463                 commitTransaction();
464         NSqlQuery query = new NSqlQuery(getConnection());                                                       
465                 if (!query.exec("Begin Transaction"))
466                         logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
467
468         }
469         public void commitTransaction() {
470         NSqlQuery query = new NSqlQuery(getConnection());
471                                                         
472                 if (!query.exec("Commit"))
473                         logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
474         }
475
476         //****************************************************************
477         //* Check if a table exists
478         //****************************************************************
479         public boolean dbTableExists(String name) {
480         NSqlQuery query = new NSqlQuery(getConnection());
481         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME=:name");
482         query.bindValue(":name", name.toUpperCase());
483         query.exec();
484         if (query.next())
485                 return true;
486         else
487                 return false;
488         }
489         
490         //****************************************************************
491         //* Check if a row in a table exists
492         //****************************************************************
493         public boolean dbTableColumnExists(String tableName, String columnName) {
494         NSqlQuery query = new NSqlQuery(getConnection());
495         query.prepare("select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=:name and COLUMN_NAME=:column");
496         query.bindValue(":name", tableName.toUpperCase());
497         query.bindValue(":column", columnName);
498         query.exec();
499         if (query.next())
500                 return true;
501         else
502                 return false;
503         }
504 }