OSDN Git Service

Rework some database logic to try and reduce the overhead with indexing & thumbnail...
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / DatabaseConnection.java
1 /*
2  * This file is part of NeverNote 
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 com.trolltech.qt.sql.QJdbc;
27
28 import cx.fbn.nevernote.Global;
29 import cx.fbn.nevernote.sql.driver.NSqlQuery;
30 import cx.fbn.nevernote.utilities.ApplicationLogger;
31
32
33 public class DatabaseConnection {
34         // Table helpers
35         private WordsTable                                      wordsTable;
36         private TagTable                                        tagTable;
37         private NotebookTable                           notebookTable;
38         private NoteTable                                       noteTable;
39         private DeletedTable                            deletedTable;
40         private SavedSearchTable                        searchTable;
41         private WatchFolderTable                        watchFolderTable;
42         private InvalidXMLTable                         invalidXMLTable;
43         private LinkedNotebookTable                     linkedNotebookTable;
44         private SharedNotebookTable                     sharedNotebookTable;
45         private InkImagesTable                          inkImagesTable;
46         private SyncTable                                       syncTable;
47         private SystemIconTable                         systemIconTable;
48         private final ApplicationLogger         logger;
49         private Connection                                      conn;
50         int throttle=0;
51         int id;
52
53         
54         public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword, int throttle) {
55                 logger = l;
56                 this.throttle = throttle;
57                 dbSetup(url, 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 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                 
101                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
102                 
103
104                 try {
105                         String passwordString = null;
106                         if (cypherPassword==null || cypherPassword.trim().equals(""))
107                                 passwordString = userPassword;
108                         else
109                                 passwordString = cypherPassword+" "+userPassword;
110 //                      conn = DriverManager.getConnection(url,userid,passwordString);
111 //                      conn = DriverManager.getConnection(url,userid,passwordString);
112 //                      conn = DriverManager.getConnection(url+";CACHE_SIZE=4096",userid,passwordString);
113                         if (throttle == 0)
114                                 conn = DriverManager.getConnection(url+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
115                         else
116                                 conn = DriverManager.getConnection(url+";THROTTLE=" +new Integer(throttle).toString()+";CACHE_SIZE="+Global.databaseCache,userid,passwordString);
117 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
118                 } catch (SQLException e) {
119                         e.printStackTrace();
120                         return;
121                 }
122                 
123                 // If it doesn't exist and we are the main thread, then we need to create stuff.
124                 if (!dbExists)  {
125                         createTables();
126                         Global.setAutomaticLogin(false);
127                 }               
128                 
129                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
130         }
131         
132         
133         public void dbShutdown() {
134                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
135                 try {
136                         conn.close();
137                 } catch (SQLException e) {
138                         e.printStackTrace();
139                 }
140                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
141         }
142         
143         public void upgradeDb(String version) {
144                 if (version.equals("0.85")) {
145                         executeSql("alter table note add column titleColor integer");
146                         executeSql("alter table note add column thumbnail blob");
147                         executeSql("alter table note add column thumbnailneeded boolean");
148                         executeSql("Update note set thumbnailneeded = true;");
149                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
150                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
151                         version = "0.86";
152                         Global.setDatabaseVersion(version);
153                 } 
154                 if (version.equals("0.86")) {
155         
156                         executeSql("alter table notebook add column publishingUri VarChar");
157                         executeSql("alter table notebook add column publishingOrder Integer");
158                         executeSql("alter table notebook add column publishingAscending Boolean");
159                         executeSql("alter table notebook add column publishingPublicDescription varchar");
160                         executeSql("alter table notebook add column stack varchar");
161                         executeSql("alter table notebook add column icon blob");
162                         executeSql("alter table notebook add column readOnly boolean");
163                         executeSql("alter table notebook add column linked boolean");
164                         
165                         executeSql("alter table tag add column realname varchar");
166                         executeSql("alter table tag add column linked boolean");
167                         executeSql("alter table tag add column icon blob");
168                         executeSql("alter table tag add column notebookguid varchar");
169                         executeSql("alter table SavedSearch add column icon blob");
170
171                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
172                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
173                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
174                         executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
175                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
176                         
177                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
178                         executeSql("update notebook set publishingUri='', " +
179                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
180                                         "publishingPublicDescription='', linked=false");
181                         executeSql("update tag set linked=false, realname='', notebookguid=''");
182                         
183                         sharedNotebookTable.createTable();
184                         linkedNotebookTable.createTable();
185                         systemIconTable.createTable();
186                         inkImagesTable.createTable();
187                         
188                         version = "0.95";
189                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
190                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
191                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
192                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
193                         executeSql("Update note set indexneeded='true'");
194                         executeSql("Update noteresources set indexneeded='true'");
195                         Global.setDatabaseVersion(version);
196                 } 
197         }
198         
199         public void executeSql(String sql) {
200                 NSqlQuery query = new NSqlQuery(conn);
201                 query.exec(sql);        
202         }
203         
204         public void checkDatabaseVersion() {
205                 if (!Global.getDatabaseVersion().equals("0.86")) {
206                         upgradeDb(Global.getDatabaseVersion());
207                 }
208                 if (!Global.getDatabaseVersion().equals("0.95")) {
209                         upgradeDb(Global.getDatabaseVersion());
210                 }
211         }
212         
213
214         public void backupDatabase(int highSequence, long date) {
215                 
216         }
217         
218         
219         public void createTables() {
220                 Global.setDatabaseVersion("0.85");
221                 Global.setAutomaticLogin(false);
222                 Global.saveCurrentNoteGuid("");
223                 Global.saveUploadAmount(0);
224                 
225                 getTagTable().createTable();
226                 notebookTable.createTable(true);
227                 noteTable.createTable();
228                 deletedTable.createTable();             
229                 searchTable.createTable();
230                 watchFolderTable.createTable();
231                 invalidXMLTable.createTable();
232                 wordsTable.createTable();
233                 syncTable.createTable();
234         }
235         
236         public Connection getConnection() {
237                 return conn;
238         }
239         
240         //***************************************************************
241         //* Table get methods
242         //***************************************************************
243         public DeletedTable getDeletedTable() {
244                 return deletedTable;
245         }
246         public TagTable getTagTable() {
247                 return tagTable;
248         }
249         public NoteTable getNoteTable() {
250                 return noteTable;
251         }
252         public NotebookTable getNotebookTable() {
253                 return notebookTable;
254         }
255         public SavedSearchTable getSavedSearchTable() {
256                 return searchTable;
257         }
258         public WatchFolderTable getWatchFolderTable() {
259                 return watchFolderTable;
260         }
261         public WordsTable getWordsTable() {
262                 return wordsTable;
263         }
264         public InvalidXMLTable getInvalidXMLTable() {
265                 return invalidXMLTable;
266         }
267         public SyncTable getSyncTable() {
268                 return syncTable;
269         }
270         public LinkedNotebookTable getLinkedNotebookTable() {
271                 return linkedNotebookTable;
272         }
273         public SharedNotebookTable getSharedNotebookTable() {
274                 return sharedNotebookTable;
275         }
276         public SystemIconTable getSystemIconTable() {
277                 return systemIconTable;
278         }
279         public InkImagesTable getInkImagesTable() {
280                 return inkImagesTable;
281         }
282
283         //****************************************************************
284         //* Begin/End transactions
285         //****************************************************************
286         public void beginTransaction() {
287         NSqlQuery query = new NSqlQuery(getConnection());
288                                                         
289                 if (!query.exec("Begin Transaction"))
290                         logger.log(logger.EXTREME, "Begin transaction has failed: " +query.lastError());
291
292         }
293         public void commitTransaction() {
294         NSqlQuery query = new NSqlQuery(getConnection());
295                                                         
296                 if (!query.exec("Commit"))
297                         logger.log(logger.EXTREME, "Transaction commit has failed: " +query.lastError());
298         }
299 }