OSDN Git Service

Cleanup compiler warning messages and alter backup & restore to handle new database...
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / TagTable.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.util.ArrayList;\r
24 import java.util.HashMap;\r
25 import java.util.List;\r
26 \r
27 import com.evernote.edam.type.Tag;\r
28 import com.trolltech.qt.core.QBuffer;\r
29 import com.trolltech.qt.core.QByteArray;\r
30 import com.trolltech.qt.core.QIODevice;\r
31 import com.trolltech.qt.gui.QIcon;\r
32 import com.trolltech.qt.gui.QImage;\r
33 import com.trolltech.qt.gui.QPixmap;\r
34 \r
35 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
36 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
37 \r
38 public class TagTable {\r
39         private final ApplicationLogger logger;\r
40         DatabaseConnection db;\r
41 \r
42         public TagTable (ApplicationLogger l, DatabaseConnection d) {\r
43                 logger = l;\r
44                 db = d;\r
45         }\r
46         // Create the table\r
47         public void createTable() {\r
48                 \r
49                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
50         logger.log(logger.HIGH, "Creating table Tag...");\r
51         if (!query.exec("Create table Tag (guid varchar primary key, " +\r
52                         "parentGuid varchar, sequence integer, hashCode integer, name varchar, isDirty boolean)"))\r
53                 logger.log(logger.HIGH, "Table TAG creation FAILED!!!");  \r
54                 \r
55         }\r
56         // Drop the table\r
57         public void dropTable() {\r
58                 \r
59                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
60                 query.exec("Drop table Tag");\r
61                 \r
62         }\r
63         // get all tags\r
64         public List<Tag> getAll() {\r
65                 \r
66                 Tag tempTag;\r
67                 List<Tag> index = new ArrayList<Tag>();\r
68                 boolean check;\r
69                                                 \r
70         NSqlQuery query = new NSqlQuery(db.getConnection());\r
71                                                 \r
72                 check = query.exec("Select guid, parentGuid, sequence, name"\r
73                                 +" from Tag");\r
74                 if (!check) {\r
75                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
76                         logger.log(logger.EXTREME, query.lastError());\r
77                 }\r
78                 while (query.next()) {\r
79                         tempTag = new Tag();\r
80                         tempTag.setGuid(query.valueString(0));\r
81                         if (query.valueString(1) != null)\r
82                                 tempTag.setParentGuid(query.valueString(1));\r
83                         else\r
84                                 tempTag.setParentGuid(null);\r
85                         int sequence = new Integer(query.valueString(2)).intValue();\r
86                         tempTag.setUpdateSequenceNum(sequence);\r
87                         tempTag.setName(query.valueString(3));\r
88                         index.add(tempTag); \r
89                 }       \r
90                 \r
91                 return index;\r
92         }\r
93         public Tag getTag(String guid) {\r
94                 Tag tempTag = new Tag();                \r
95                 \r
96         NSqlQuery query = new NSqlQuery(db.getConnection());\r
97                                                         \r
98                 if (!query.prepare("Select guid, parentGuid, sequence, name"\r
99                                 +" from Tag where guid=:guid"))\r
100                         logger.log(logger.EXTREME, "Tag select by guid SQL prepare has failed.");\r
101 \r
102                 query.bindValue(":guid", guid);\r
103                 if (!query.exec())\r
104                         logger.log(logger.EXTREME, "Tag select by guid SQL exec has failed.");\r
105                 \r
106                 if (!query.next())  {\r
107                         return tempTag;\r
108                 }\r
109                 tempTag.setGuid(query.valueString(0));\r
110                 tempTag.setParentGuid(query.valueString(1));\r
111                 int sequence = new Integer(query.valueString(2)).intValue();\r
112                 tempTag.setUpdateSequenceNum(sequence);\r
113                 tempTag.setName(query.valueString(3));\r
114                 return tempTag;\r
115         }\r
116         // Update a tag\r
117         public void updateTag(Tag tempTag, boolean isDirty) {\r
118                 boolean check;\r
119                 \r
120         NSqlQuery query = new NSqlQuery(db.getConnection());\r
121                 check = query.prepare("Update Tag set parentGuid=:parentGuid, sequence=:sequence, "+\r
122                         "hashCode=:hashCode, name=:name, isDirty=:isDirty "\r
123                         +"where guid=:guid");\r
124       \r
125                 if (!check) {\r
126                         logger.log(logger.EXTREME, "Tag SQL update prepare has failed.");\r
127                         logger.log(logger.EXTREME, query.lastError());\r
128                 }\r
129                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
130                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
131                 query.bindValue(":hashCode", tempTag.hashCode());\r
132                 query.bindValue(":name", tempTag.getName());\r
133                 query.bindValue(":isDirty", isDirty);\r
134                 query.bindValue(":guid", tempTag.getGuid());\r
135                 \r
136                 check = query.exec();\r
137                 if (!check)\r
138                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
139                 \r
140         }\r
141         // Delete a tag\r
142         public void expungeTag(String guid, boolean needsSync) {\r
143                 boolean check;\r
144                 \r
145                 \r
146         NSqlQuery query = new NSqlQuery(db.getConnection());\r
147 \r
148         check = query.prepare("delete from Tag "\r
149                                 +"where guid=:guid");\r
150                 if (!check) {\r
151                         logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");\r
152                         logger.log(logger.EXTREME, query.lastError());\r
153                 }\r
154                 query.bindValue(":guid", guid);\r
155                 check = query.exec();\r
156                 if (!check)\r
157                         logger.log(logger.MEDIUM, "Tag delete failed.");\r
158                 \r
159         check = query.prepare("delete from NoteTags "\r
160                                 +"where tagGuid=:guid");\r
161                 if (!check) {\r
162                         logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");\r
163                         logger.log(logger.EXTREME, query.lastError());\r
164                 }\r
165                 \r
166                 query.bindValue(":guid", guid);\r
167                 check = query.exec();\r
168                 if (!check)\r
169                         logger.log(logger.MEDIUM, "NoteTags delete failed.");\r
170                 \r
171                 // Add the work to the parent queue\r
172                 if (needsSync) {\r
173                         DeletedTable del = new DeletedTable(logger, db);\r
174                         del.addDeletedItem(guid, "Tag");\r
175                 }\r
176         }\r
177         // Save a tag\r
178         public void addTag(Tag tempTag, boolean isDirty) {\r
179                 boolean check;\r
180                 \r
181         NSqlQuery query = new NSqlQuery(db.getConnection());\r
182                 check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty)"\r
183                                 +" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty)");\r
184                 if (!check) {\r
185                         logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");\r
186                         logger.log(logger.EXTREME, query.lastError());\r
187                 }\r
188                 query.bindValue(":guid", tempTag.getGuid());\r
189                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
190                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
191                 query.bindValue(":hashCode", tempTag.hashCode());\r
192                 query.bindValue(":name", tempTag.getName());\r
193                 query.bindValue(":isDirty", isDirty);\r
194                 \r
195                 check = query.exec();\r
196                 if (!check) {\r
197                         logger.log(logger.MEDIUM, "Tag Table insert failed.");\r
198                         logger.log(logger.MEDIUM, query.lastError());\r
199                 }\r
200         }\r
201         // Update a tag's parent\r
202         public void updateTagParent(String guid, String parentGuid) {\r
203                 boolean check;\r
204                 \r
205         NSqlQuery query = new NSqlQuery(db.getConnection());\r
206                 check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");\r
207                 if (!check) {\r
208                         logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");\r
209                         logger.log(logger.EXTREME, query.lastError());\r
210                 }\r
211 \r
212                 query.bindValue(":parentGuid", parentGuid);\r
213                 query.bindValue(":guid", guid);\r
214                 \r
215                 check = query.exec();\r
216                 if (!check) {\r
217                         logger.log(logger.MEDIUM, "Tag parent update failed.");\r
218                         logger.log(logger.MEDIUM, query.lastError());\r
219                 }\r
220         }\r
221         //Save tags from Evernote\r
222         public void saveTags(List<Tag> tags) {\r
223                 Tag tempTag;\r
224                 for (int i=0; i<tags.size(); i++) {\r
225                         tempTag = tags.get(i);\r
226                         addTag(tempTag, false);\r
227                 }               \r
228         }\r
229         // Update a tag sequence number\r
230         public void updateTagSequence(String guid, int sequence) {\r
231                 boolean check;\r
232         NSqlQuery query = new NSqlQuery(db.getConnection());\r
233                 check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");\r
234                 query.bindValue(":sequence", sequence);\r
235                 query.bindValue(":guid", guid);\r
236                 \r
237                 query.exec();\r
238                 if (!check) {\r
239                         logger.log(logger.MEDIUM, "Tag sequence update failed.");\r
240                         logger.log(logger.MEDIUM, query.lastError());\r
241                 }\r
242                 \r
243         }\r
244         // Update a tag sequence number\r
245         public void updateTagGuid(String oldGuid, String newGuid) {\r
246                 boolean check;\r
247                 \r
248         NSqlQuery query = new NSqlQuery(db.getConnection());\r
249                 check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");\r
250                 query.bindValue(":newGuid", newGuid);\r
251                 query.bindValue(":oldGuid", oldGuid);\r
252                 query.exec();\r
253                 if (!check) {\r
254                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
255                         logger.log(logger.MEDIUM, query.lastError());\r
256                 }\r
257                 \r
258                 check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");\r
259                 query.bindValue(":newGuid", newGuid);\r
260                 query.bindValue(":oldGuid", oldGuid);\r
261                 query.exec();\r
262                 if (!check) {\r
263                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
264                         logger.log(logger.MEDIUM, query.lastError());\r
265                 }\r
266                 \r
267                 check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");\r
268                 query.bindValue(":newGuid", newGuid);\r
269                 query.bindValue(":oldGuid", oldGuid);\r
270                 query.exec();\r
271                 if (!check) {\r
272                         logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");\r
273                         logger.log(logger.MEDIUM, query.lastError());\r
274                 }\r
275                 \r
276         }\r
277         // Get dirty tags\r
278         public List<Tag> getDirty() {\r
279                 Tag tempTag;\r
280                 List<Tag> index = new ArrayList<Tag>();\r
281                 boolean check;\r
282                                                 \r
283                 \r
284         NSqlQuery query = new NSqlQuery(db.getConnection());\r
285                                                 \r
286                 check = query.exec("Select guid, parentGuid, sequence, name"\r
287                                 +" from Tag where isDirty = true");\r
288                 if (!check)\r
289                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
290                 while (query.next()) {\r
291                         tempTag = new Tag();\r
292                         tempTag.setGuid(query.valueString(0));\r
293                         tempTag.setParentGuid(query.valueString(1));\r
294                         int sequence = new Integer(query.valueString(2)).intValue();\r
295                         tempTag.setUpdateSequenceNum(sequence);\r
296                         tempTag.setName(query.valueString(3));\r
297                         if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))\r
298                                 tempTag.setParentGuid(null);\r
299                         index.add(tempTag); \r
300                 }\r
301                 return index;\r
302         }\r
303         // Find a guid based upon the name\r
304         public String findTagByName(String name) {\r
305                 \r
306                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
307                 \r
308                 query.prepare("Select guid from tag where name=:name");\r
309                 query.bindValue(":name", name);\r
310                 if (!query.exec())\r
311                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
312                 String val = null;\r
313                 if (query.next())\r
314                         val = query.valueString(0);\r
315                 return val;\r
316         }\r
317         // given a guid, does the tag exist\r
318         public boolean exists(String guid) {\r
319                 \r
320                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
321                 \r
322                 query.prepare("Select guid from tag where guid=:guid");\r
323                 query.bindValue(":guid", guid);\r
324                 if (!query.exec())\r
325                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
326                 boolean retval = query.next();\r
327                 return retval;\r
328         }\r
329         // This is a convience method to check if a tag exists & update/create based upon it\r
330         public void syncTag(Tag tag, boolean isDirty) {\r
331                 if (exists(tag.getGuid()))\r
332                         updateTag(tag, isDirty);\r
333                 else\r
334                         addTag(tag, isDirty);\r
335         }\r
336         public void  resetDirtyFlag(String guid) {\r
337                 \r
338                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
339                 \r
340                 query.prepare("Update tag set isdirty=false where guid=:guid");\r
341                 query.bindValue(":guid", guid);\r
342                 if (!query.exec())\r
343                         logger.log(logger.EXTREME, "Error resetting tag dirty field.");\r
344         }\r
345         \r
346         \r
347         // Get the custom icon\r
348         public QIcon getIcon(String guid) {\r
349                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
350                 \r
351                 if (!query.prepare("Select icon from tag where guid=:guid"))\r
352                         logger.log(logger.EXTREME, "Error preparing tag icon select.");\r
353                 query.bindValue(":guid", guid);\r
354                 if (!query.exec())\r
355                         logger.log(logger.EXTREME, "Error finding tag icon.");\r
356                 if (!query.next() || query.getBlob(0) == null)\r
357                         return null;\r
358                 \r
359                 QByteArray blob = new QByteArray(query.getBlob(0));\r
360                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
361                 return icon;\r
362         }\r
363         // Set the custom icon\r
364         public void setIcon(String guid, QIcon icon, String type) {\r
365                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
366                 if (icon == null) {\r
367                         if (!query.prepare("update tag set icon=null where guid=:guid"))\r
368                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
369                 } else {\r
370                         if (!query.prepare("update tag set icon=:icon where guid=:guid"))\r
371                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
372                         QBuffer buffer = new QBuffer();\r
373                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
374                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
375                         return;\r
376                 }\r
377                 QPixmap p = icon.pixmap(32, 32);\r
378                 QImage i = p.toImage();\r
379                 i.save(buffer, type.toUpperCase());\r
380                 buffer.close();\r
381                 QByteArray b = new QByteArray(buffer.buffer());\r
382                 if (!b.isNull() && !b.isEmpty())\r
383                         query.bindValue(":icon", b.toByteArray());\r
384                 else\r
385                         return;\r
386                 }\r
387                 query.bindValue(":guid", guid);\r
388                 if (!query.exec()) \r
389                         logger.log(logger.LOW, "Error setting tag icon. " +query.lastError());\r
390         }\r
391 \r
392         // Get a list of all icons\r
393         public HashMap<String, QIcon> getAllIcons() {\r
394                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
395                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
396         \r
397                 if (!query.exec("SELECT guid, icon from tag"))\r
398                         logger.log(logger.EXTREME, "Error executing SavedSearch getAllIcons select.");\r
399                 while (query.next()) {\r
400                         if (query.getBlob(1) != null) {\r
401                                 String guid = query.valueString(0);\r
402                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
403                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
404                                 values.put(guid, icon);\r
405                         }\r
406                 }\r
407                 return values;\r
408         }\r
409 \r
410 }\r