OSDN Git Service

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