OSDN Git Service

Increase database cache size to improve performance.
[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 id;
51
52         
53         public DatabaseConnection(ApplicationLogger l, String url, String userid, String password, String cypherPassword) {
54                 logger = l;
55                 dbSetup(url, userid, password, cypherPassword);
56         }
57         
58         private void setupTables() {
59                 tagTable = new TagTable(logger, this);
60                 notebookTable = new NotebookTable(logger, this);
61                 noteTable = new NoteTable(logger, this);
62                 deletedTable = new DeletedTable(logger, this);
63                 searchTable = new SavedSearchTable(logger, this);       
64                 watchFolderTable = new WatchFolderTable(logger, this);
65                 invalidXMLTable = new InvalidXMLTable(logger, this);
66                 wordsTable = new WordsTable(logger, this);
67                 syncTable = new SyncTable(logger, this);
68                 linkedNotebookTable = new LinkedNotebookTable(logger, this);
69                 sharedNotebookTable = new SharedNotebookTable(logger, this);
70                 systemIconTable = new SystemIconTable(logger, this);
71                 inkImagesTable = new InkImagesTable(logger, this);
72         }
73         
74         
75         // Compact the database
76         public void compactDatabase() {
77                 
78         }
79         
80         // Initialize the database connection
81         public void dbSetup(String url,String userid, String userPassword, String cypherPassword) {
82                 logger.log(logger.HIGH, "Entering DatabaseConnection.dbSetup " +id);
83
84                 
85                 try {
86                         Class.forName("org.h2.Driver");
87                 } catch (ClassNotFoundException e1) {
88                         e1.printStackTrace();
89                         System.exit(16);
90                 }
91                 
92                 QJdbc.initialize();
93                 
94                 setupTables();
95                 
96                 File f = Global.getFileManager().getDbDirFile(Global.databaseName + ".h2.db");
97                 boolean dbExists = f.exists(); 
98                 
99                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbSetup");
100                 
101
102                 try {
103                         String passwordString = null;
104                         if (cypherPassword==null || cypherPassword.trim().equals(""))
105                                 passwordString = userPassword;
106                         else
107                                 passwordString = cypherPassword+" "+userPassword;
108                         conn = DriverManager.getConnection(url+";CACHE_SIZE=65536",userid,passwordString);
109 //                      conn = DriverManager.getConnection(url+";AUTO_SERVER=TRUE",userid,passwordString);
110                 } catch (SQLException e) {
111                         e.printStackTrace();
112                         return;
113                 }
114                 
115                 // If it doesn't exist and we are the main thread, then we need to create stuff.
116                 if (!dbExists)  {
117                         createTables();
118                         Global.setAutomaticLogin(false);
119                 }               
120                 
121                 logger.log(logger.HIGH, "Leaving DatabaseConnection.dbSetup" +id);
122         }
123         
124         
125         public void dbShutdown() {
126                 logger.log(logger.HIGH, "Entering RDatabaseConnection.dbShutdown");
127                 try {
128                         conn.close();
129                 } catch (SQLException e) {
130                         e.printStackTrace();
131                 }
132                 logger.log(logger.HIGH, "Leaving RDatabaseConnection.dbShutdown");
133         }
134         
135         public void upgradeDb(String version) {
136                 if (version.equals("0.85")) {
137                         executeSql("alter table note add column titleColor integer");
138                         executeSql("alter table note add column thumbnail blob");
139                         executeSql("alter table note add column thumbnailneeded boolean");
140                         executeSql("Update note set thumbnailneeded = true;");
141                         executeSql("create index NOTE_NOTEBOOK_INDEX on note (notebookguid, guid);");
142                         executeSql("create index NOTETAGS_TAG_INDEX on notetags (tagguid, noteguid);");
143                         version = "0.86";
144                         Global.setDatabaseVersion(version);
145                 } 
146                 if (version.equals("0.86")) {
147         
148                         executeSql("alter table notebook add column publishingUri VarChar");
149                         executeSql("alter table notebook add column publishingOrder Integer");
150                         executeSql("alter table notebook add column publishingAscending Boolean");
151                         executeSql("alter table notebook add column publishingPublicDescription varchar");
152                         executeSql("alter table notebook add column stack varchar");
153                         executeSql("alter table notebook add column icon blob");
154                         executeSql("alter table notebook add column readOnly boolean");
155                         executeSql("alter table notebook add column linked boolean");
156                         
157                         executeSql("alter table tag add column realname varchar");
158                         executeSql("alter table tag add column linked boolean");
159                         executeSql("alter table tag add column icon blob");
160                         executeSql("alter table tag add column notebookguid varchar");
161                         executeSql("alter table SavedSearch add column icon blob");
162
163                         executeSql("create index NOTE_THUMBNAIL_INDEX on note (thumbnailneeded, guid);");
164                         executeSql("create index NOTE_EXPUNGED_INDEX on note (isExpunged, guid);");
165                         executeSql("create index NOTE_DUEDATE_INDEX on note (attributeSubjectDate, guid);");
166                         executeSql("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);");
167                         executeSql("create index TAG_NOTEBOOK_INDEX on tag (notebookGuid);");
168                         
169                         executeSql("update note set thumbnailneeded=true, thumbnail=null;");
170                         executeSql("update notebook set publishingUri='', " +
171                                         "publishingAscending=false, stack='', readonly=false, publishingOrder=1, " +
172                                         "publishingPublicDescription='', linked=false");
173                         executeSql("update tag set linked=false, realname='', notebookguid=''");
174                         
175                         sharedNotebookTable.createTable();
176                         linkedNotebookTable.createTable();
177                         systemIconTable.createTable();
178                         inkImagesTable.createTable();
179                         
180                         version = "0.95";
181                         executeSql("Insert into Sync (key, value) values ('FullNotebookSync', 'true')");
182                         executeSql("Insert into Sync (key, value) values ('FullLinkedNotebookSync', 'true')");
183                         executeSql("Insert into Sync (key, value) values ('FullSharedNotebookSync', 'true')");
184                         executeSql("Insert into Sync (key, value) values ('FullInkNoteImageSync', 'true')");
185                         executeSql("Update note set indexneeded='true'");
186                         executeSql("Update noteresources set indexneeded='true'");
187                         Global.setDatabaseVersion(version);
188                 } 
189         }
190         
191         public void executeSql(String sql) {
192                 NSqlQuery query = new NSqlQuery(conn);
193                 query.exec(sql);        
194         }
195         
196         public void checkDatabaseVersion() {
197                 if (!Global.getDatabaseVersion().equals("0.86")) {
198                         upgradeDb(Global.getDatabaseVersion());
199                 }
200                 if (!Global.getDatabaseVersion().equals("0.95")) {
201                         upgradeDb(Global.getDatabaseVersion());
202                 }
203         }
204         
205
206         public void backupDatabase(int highSequence, long date) {
207                 
208         }
209         
210         
211         public void createTables() {
212                 Global.setDatabaseVersion("0.85");
213                 Global.setAutomaticLogin(false);
214                 Global.saveCurrentNoteGuid("");
215                 Global.saveUploadAmount(0);
216                 
217                 getTagTable().createTable();
218                 notebookTable.createTable(true);
219                 noteTable.createTable();
220                 deletedTable.createTable();             
221                 searchTable.createTable();
222                 watchFolderTable.createTable();
223                 invalidXMLTable.createTable();
224                 wordsTable.createTable();
225                 syncTable.createTable();
226         }
227         
228         public Connection getConnection() {
229                 return conn;
230         }
231         
232         //***************************************************************
233         //* Table get methods
234         //***************************************************************
235         public DeletedTable getDeletedTable() {
236                 return deletedTable;
237         }
238         public TagTable getTagTable() {
239                 return tagTable;
240         }
241         public NoteTable getNoteTable() {
242                 return noteTable;
243         }
244         public NotebookTable getNotebookTable() {
245                 return notebookTable;
246         }
247         public SavedSearchTable getSavedSearchTable() {
248                 return searchTable;
249         }
250         public WatchFolderTable getWatchFolderTable() {
251                 return watchFolderTable;
252         }
253         public WordsTable getWordsTable() {
254                 return wordsTable;
255         }
256         public InvalidXMLTable getInvalidXMLTable() {
257                 return invalidXMLTable;
258         }
259         public SyncTable getSyncTable() {
260                 return syncTable;
261         }
262         public LinkedNotebookTable getLinkedNotebookTable() {
263                 return linkedNotebookTable;
264         }
265         public SharedNotebookTable getSharedNotebookTable() {
266                 return sharedNotebookTable;
267         }
268         public SystemIconTable getSystemIconTable() {
269                 return systemIconTable;
270         }
271         public InkImagesTable getInkImagesTable() {
272                 return inkImagesTable;
273         }
274
275 }