OSDN Git Service

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