OSDN Git Service

755c73e238e859a6db6926a62f32df83aab383a2
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / SharedNotebookTable.java
1 /*\r
2  * This file is part of NeverNote \r
3  * Copyright 2009 Randy Baumgarte\r
4  * \r
5  * This file may be licensed under the terms of of the\r
6  * GNU General Public License Version 2 (the ``GPL'').\r
7  *\r
8  * Software distributed under the License is distributed\r
9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
10  * express or implied. See the GPL for the specific language\r
11  * governing rights and limitations.\r
12  *\r
13  * You should have received a copy of the GPL along with this\r
14  * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
15  * or write to the Free Software Foundation, Inc.,\r
16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
17  *\r
18 */\r
19 \r
20 \r
21 package cx.fbn.nevernote.sql;\r
22 \r
23 import java.text.DateFormat;\r
24 import java.text.ParseException;\r
25 import java.text.SimpleDateFormat;\r
26 import java.util.ArrayList;\r
27 import java.util.List;\r
28 \r
29 import com.evernote.edam.type.SharedNotebook;\r
30 \r
31 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
32 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
33 \r
34 public class SharedNotebookTable {\r
35         \r
36         private final ApplicationLogger                 logger;\r
37         DatabaseConnection                                                      db;\r
38         \r
39         // Constructor\r
40         public SharedNotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
41                 logger = l;\r
42                 db = d;\r
43         }\r
44         // Create the table\r
45         public void createTable() {\r
46                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
47         logger.log(logger.HIGH, "Creating table SharedNotebook...");\r
48         if (!query.exec("Create table SharedNotebook (id long primary key, " +\r
49                         "userid Integer, " +\r
50                         "notebookGuid VarChar, "+\r
51                         "email VarChar, "+\r
52                         "notebookModifiable boolean, " +\r
53                         "requireLogin boolean, "+\r
54                         "serviceCreated timestamp, "+\r
55                         "shareKey VarChar,  "+\r
56                         "username VarChar, "+\r
57                         "icon blob, " +\r
58                         "isDirty boolean)"))                            \r
59                 logger.log(logger.HIGH, "Table SharedNotebook creation FAILED!!!");   \r
60         }\r
61         // Drop the table\r
62         public void dropTable() {\r
63                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
64                 query.exec("Drop table SharedNotebook");\r
65         }\r
66         // Save an individual notebook\r
67         public void addNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
68                 boolean check;\r
69                 \r
70                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
71         NSqlQuery query = new NSqlQuery(db.getConnection());\r
72                 check = query.prepare("Insert Into SharedNotebook (id, userid, notebookGuid, email,  "\r
73 <<<<<<< HEAD
74                                 +"notebookModifiable, requireLogin, serviceCreated, shareKey, username, isDirty) "   \r
75                                 + " Values("\r
76                                 +":id, :userid, :notebookGuid, :email, "\r
77                                 +":notebookModifiable, :requireLogin, :serviceCreated, "\r
78                                 +":shareKey, :username, :isDirty)");\r
79 =======
80                                 +"notebookModifiable, requireLogin, serviceCreated, shareKey, username) "   \r
81                                 + " Values("\r
82                                 +":id, :userid, :notebookGuid, :email, "\r
83                                 +":notebookModifiable, :requireLogin, :serviceCreated, "\r
84                                 +":shareKey, :username)");\r
85 >>>>>>> Add linked & shared notebook tables
86                 query.bindValue(":id", tempNotebook.getId());\r
87                 query.bindValue(":userid", tempNotebook.getUserId());\r
88                 query.bindValue(":notebookGuid", tempNotebook.getNotebookGuid());\r
89                 query.bindValue(":email", tempNotebook.getEmail());\r
90                 query.bindValue(":notebookModifiable", tempNotebook.isNotebookModifiable());\r
91                 query.bindValue(":requireLogin", tempNotebook.isRequireLogin());\r
92 \r
93                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                      \r
94                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
95                 \r
96                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
97                 query.bindValue(":username", tempNotebook.getUsername());\r
98                 \r
99                 if (isDirty)\r
100                         query.bindValue(":isDirty", true);\r
101                 else\r
102                         query.bindValue(":isDirty", false);\r
103 \r
104                 check = query.exec();\r
105                 if (!check) {\r
106                         logger.log(logger.MEDIUM, "SharedNotebook Table insert failed.");\r
107                         logger.log(logger.MEDIUM, query.lastError().toString());\r
108                 }\r
109         }\r
110 <<<<<<< HEAD
111         // Check if a notebook exists\r
112         public boolean exists(long id) {\r
113         NSqlQuery query = new NSqlQuery(db.getConnection());\r
114         boolean check = query.prepare("Select id from sharednotebook where id=:id");\r
115         query.bindValue(":id", id);\r
116                 check = query.exec();\r
117                 if (!check) {\r
118                         logger.log(logger.MEDIUM, "SharedNotebook Table exists check failed.");\r
119                         logger.log(logger.MEDIUM, query.lastError().toString());\r
120                 }\r
121                 if (query.next())\r
122                         return true;\r
123                 return false;\r
124         }\r
125         // Delete the notebook based on a id\r
126 =======
127         // Delete the notebook based on a guid\r
128 >>>>>>> Add linked & shared notebook tables
129         public void expungeNotebook(long id, boolean needsSync) {\r
130                 boolean check;\r
131         NSqlQuery query = new NSqlQuery(db.getConnection());\r
132 \r
133         check = query.prepare("delete from SharedNotebook "\r
134                                 +"where id=:id");\r
135                 if (!check) {\r
136                         logger.log(logger.EXTREME, "SharedNotebook SQL delete prepare has failed.");\r
137                         logger.log(logger.EXTREME, query.lastError().toString());\r
138                 }\r
139                 query.bindValue(":id", id);\r
140                 check = query.exec();\r
141                 if (!check) \r
142                         logger.log(logger.MEDIUM, "SharedNotebook delete failed.");\r
143                 \r
144                 // Signal the parent that work needs to be done\r
145                 if  (needsSync) {\r
146                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
147                         deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook");\r
148                 }\r
149         }\r
150 <<<<<<< HEAD
151         // Delete the notebook based on a id\r
152         public void expungeNotebookByGuid(String id, boolean needsSync) {\r
153                 boolean check;\r
154         NSqlQuery query = new NSqlQuery(db.getConnection());\r
155 \r
156         check = query.prepare("delete from SharedNotebook "\r
157                                 +"where guid=:id");\r
158                 if (!check) {\r
159                         logger.log(logger.EXTREME, "SharedNotebook SQL delete by notebook guid prepare has failed.");\r
160                         logger.log(logger.EXTREME, query.lastError().toString());\r
161                 }\r
162                 query.bindValue(":id", id);\r
163                 check = query.exec();\r
164                 if (!check) \r
165                         logger.log(logger.MEDIUM, "SharedNotebook delete by notebook guid failed.");\r
166                 \r
167                 // Signal the parent that work needs to be done\r
168                 if  (needsSync) {\r
169                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
170                         deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook");\r
171                 }\r
172         }\r
173 \r
174         \r
175         // Update a notebook\r
176         public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
177                 boolean check;\r
178                 if (!exists(tempNotebook.getId())) {\r
179                         addNotebook(tempNotebook, isDirty);\r
180                         return;\r
181                 }\r
182                 \r
183                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
184                 \r
185                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                                              \r
186         NSqlQuery query = new NSqlQuery(db.getConnection());\r
187         check = query.prepare("Update SharedNotebook set id=:id, userid=:userid, notebookGuid=:notebook, "\r
188                         + "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:serviceCreated, "\r
189 =======
190         // Update a notebook\r
191         public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) {\r
192                 boolean check;\r
193                 \r
194                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
195                 \r
196         NSqlQuery query = new NSqlQuery(db.getConnection());\r
197         check = query.prepare("Update SharedNotebook set id=:id, userid=:userid, notebookGuid=:notebook, "\r
198                         + "email=:email, notebookModifiable=:mod, requireLogin=:rlogin, serviceCreated=:created "\r
199 >>>>>>> Add linked & shared notebook tables
200                         + "shareKey=:shareKey, username=:username, isDirty=:isdirty");\r
201                 query.bindValue(":id", tempNotebook.getId());\r
202                 query.bindValue(":userid", tempNotebook.getUserId());\r
203                 query.bindValue(":notebook", tempNotebook.getNotebookGuid());\r
204                 query.bindValue(":email", tempNotebook.getEmail());\r
205                 query.bindValue(":mod", tempNotebook.isNotebookModifiable());\r
206                 query.bindValue(":rlogin", tempNotebook.isRequireLogin());\r
207 <<<<<<< HEAD
208                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
209                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
210                 query.bindValue(":username", tempNotebook.getUsername());\r
211 =======
212                 query.bindValue(":shareKey", tempNotebook.getShareKey());\r
213                 query.bindValue(":username", tempNotebook.getUsername());\r
214 \r
215                 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));                                              \r
216                 query.bindValue(":serviceCreated", serviceCreated.toString());\r
217 >>>>>>> Add linked & shared notebook tables
218                 \r
219                 query.bindValue(":isDirty", isDirty);\r
220                 \r
221                 check = query.exec();\r
222                 if (!check) {\r
223                         logger.log(logger.MEDIUM, "SharedNotebook Table update failed.");\r
224                         logger.log(logger.MEDIUM, query.lastError().toString());\r
225                 }\r
226         }\r
227         // Load notebooks from the database\r
228         public List<SharedNotebook> getAll() {\r
229                 SharedNotebook tempNotebook;\r
230                 List<SharedNotebook> index = new ArrayList<SharedNotebook>();\r
231                 boolean check;\r
232                                         \r
233         NSqlQuery query = new NSqlQuery(db.getConnection());\r
234                                         \r
235                 check = query.exec("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " +\r
236                                 "serviceCreated, "+\r
237                                 "shareKey, username from SharedNotebook");\r
238                 if (!check)\r
239                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
240                 while (query.next()) {\r
241                         tempNotebook = new SharedNotebook();\r
242                         tempNotebook.setId(query.valueLong(0));\r
243                         tempNotebook.setUserId(query.valueInteger(1));\r
244                         tempNotebook.setNotebookGuid(query.valueString(2));\r
245                         tempNotebook.setEmail(query.valueString(3));\r
246                         tempNotebook.setNotebookModifiable(query.valueBoolean(4,false));\r
247                         tempNotebook.setRequireLogin(query.valueBoolean(5,true));\r
248                         DateFormat indfm = null;\r
249                         try {\r
250                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
251                         } catch (Exception e) { }\r
252                         try {\r
253                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime());\r
254                         } catch (ParseException e) {\r
255                                 e.printStackTrace();\r
256                         }\r
257                         tempNotebook.setShareKey(query.valueString(7));\r
258                         tempNotebook.setUsername(query.valueString(8));\r
259 \r
260                         index.add(tempNotebook); \r
261                 }       \r
262                 return index;\r
263         }                       \r
264 \r
265 <<<<<<< HEAD
266         // Load notebooks from the database\r
267         public List<SharedNotebook> getForNotebook(String guid) {\r
268                 SharedNotebook tempNotebook;\r
269                 List<SharedNotebook> index = new ArrayList<SharedNotebook>();\r
270                 boolean check;\r
271                                         \r
272         NSqlQuery query = new NSqlQuery(db.getConnection());\r
273                                         \r
274                 check = query.prepare("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " +\r
275                                 "serviceCreated, "+\r
276                                 "shareKey, username from SharedNotebook where notebookGuid=:notebookGuid ");\r
277                 if (!check)\r
278                         logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL prepare has failed.");\r
279                 query.bindValue(":notebookGuid", guid);\r
280                 check = query.exec();\r
281                 if (!check)\r
282                         logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL exec has failed.");\r
283                 \r
284                 while (query.next()) {\r
285                         tempNotebook = new SharedNotebook();\r
286                         tempNotebook.setId(query.valueLong(0));\r
287                         tempNotebook.setUserId(query.valueInteger(1));\r
288                         tempNotebook.setNotebookGuid(query.valueString(2));\r
289                         tempNotebook.setEmail(query.valueString(3));\r
290                         tempNotebook.setNotebookModifiable(query.valueBoolean(4,false));\r
291                         tempNotebook.setRequireLogin(query.valueBoolean(5,true));\r
292                         DateFormat indfm = null;\r
293                         try {\r
294                                 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
295                         } catch (Exception e) { }\r
296                         try {\r
297                                 tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime());\r
298                         } catch (ParseException e) {\r
299                                 e.printStackTrace();\r
300                         }\r
301                         tempNotebook.setShareKey(query.valueString(7));\r
302                         tempNotebook.setUsername(query.valueString(8));\r
303 \r
304                         index.add(tempNotebook); \r
305                 }       \r
306                 return index;\r
307         }                       \r
308 \r
309         \r
310         // Get a list of shared notebooks that need to be updated\r
311         public List <Long> getDirtyIds() {\r
312                 List<Long> index = new ArrayList<Long>();\r
313                 boolean check;  \r
314         NSqlQuery query = new NSqlQuery(db.getConnection());\r
315                                         \r
316                 check = query.exec("Select id from SharedNotebook where isDirty = true");\r
317                 if (!check) \r
318                         logger.log(logger.EXTREME, "SharedNotebook SQL retrieve has failed in getdirtyIds.");\r
319                 while (query.next()) {\r
320                         index.add(query.valueLong(0));\r
321                 }       \r
322                 return index;   \r
323         }\r
324 \r
325 =======
326         // does a record exist?\r
327         public String findNotebookByName(String newname) {\r
328                 \r
329                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
330                 \r
331                 query.prepare("Select guid from sharednotebook where name=:newname");\r
332                 query.bindValue(":newname", newname);\r
333                 if (!query.exec())\r
334                         logger.log(logger.EXTREME, "notebook SQL retrieve has failed.");\r
335                 String val = null;\r
336                 if (query.next())\r
337                         val = query.valueString(0);\r
338                 return val;\r
339         }\r
340 \r
341 \r
342 >>>>>>> Add linked & shared notebook tables
343 }\r
344 \r