OSDN Git Service

8eed13404fdcee6ef216aceb9a02b6e84fabcafb
[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 tags for a specific notebook\r
64         // get all tags\r
65         public List<Tag> getTagsForNotebook(String notebookGuid) {\r
66                 \r
67                 Tag tempTag;\r
68                 List<Tag> index = new ArrayList<Tag>();\r
69                 boolean check;\r
70                                                 \r
71         NSqlQuery query = new NSqlQuery(db.getConnection());\r
72                                                 \r
73                 check = query.prepare("Select guid, parentGuid, sequence, name"\r
74                                 +" from Tag where notebookGuid=:notebookGuid");\r
75                 if (!check) {\r
76                         logger.log(logger.EXTREME, "Tag SQL prepare getTagsForNotebook has failed.");\r
77                         logger.log(logger.EXTREME, query.lastError());\r
78                 }\r
79                 query.bindValue(":notebookGuid", notebookGuid);\r
80                 query.exec();\r
81                 while (query.next()) {\r
82                         tempTag = new Tag();\r
83                         tempTag.setGuid(query.valueString(0));\r
84                         if (query.valueString(1) != null)\r
85                                 tempTag.setParentGuid(query.valueString(1));\r
86                         else\r
87                                 tempTag.setParentGuid(null);\r
88                         int sequence = new Integer(query.valueString(2)).intValue();\r
89                         tempTag.setUpdateSequenceNum(sequence);\r
90                         tempTag.setName(query.valueString(3));\r
91                         index.add(tempTag); \r
92                 }       \r
93                 \r
94                 return index;\r
95         }\r
96         // get all tags\r
97         public List<Tag> getAll() {\r
98                 \r
99                 Tag tempTag;\r
100                 List<Tag> index = new ArrayList<Tag>();\r
101                 boolean check;\r
102                                                 \r
103         NSqlQuery query = new NSqlQuery(db.getConnection());\r
104                                                 \r
105                 check = query.exec("Select guid, parentGuid, sequence, name"\r
106                                 +" from Tag where notebookguid not in (select guid from notebook where archived=true)");\r
107                 if (!check) {\r
108                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
109                         logger.log(logger.EXTREME, query.lastError());\r
110                 }\r
111                 while (query.next()) {\r
112                         tempTag = new Tag();\r
113                         tempTag.setGuid(query.valueString(0));\r
114                         if (query.valueString(1) != null)\r
115                                 tempTag.setParentGuid(query.valueString(1));\r
116                         else\r
117                                 tempTag.setParentGuid(null);\r
118                         int sequence = new Integer(query.valueString(2)).intValue();\r
119                         tempTag.setUpdateSequenceNum(sequence);\r
120                         tempTag.setName(query.valueString(3));\r
121                         index.add(tempTag); \r
122                 }       \r
123                 \r
124                 return index;\r
125         }\r
126         public Tag getTag(String guid) {\r
127                 Tag tempTag = new Tag();                \r
128                 \r
129         NSqlQuery query = new NSqlQuery(db.getConnection());\r
130                                                         \r
131                 if (!query.prepare("Select guid, parentGuid, sequence, name"\r
132                                 +" from Tag where guid=:guid"))\r
133                         logger.log(logger.EXTREME, "Tag select by guid SQL prepare has failed.");\r
134 \r
135                 query.bindValue(":guid", guid);\r
136                 if (!query.exec())\r
137                         logger.log(logger.EXTREME, "Tag select by guid SQL exec has failed.");\r
138                 \r
139                 if (!query.next())  {\r
140                         return tempTag;\r
141                 }\r
142                 tempTag.setGuid(query.valueString(0));\r
143                 tempTag.setParentGuid(query.valueString(1));\r
144                 int sequence = new Integer(query.valueString(2)).intValue();\r
145                 tempTag.setUpdateSequenceNum(sequence);\r
146                 tempTag.setName(query.valueString(3));\r
147                 return tempTag;\r
148         }\r
149         // Update a tag\r
150         public void updateTag(Tag tempTag, boolean isDirty) {\r
151                 updateTag(tempTag, isDirty, "");\r
152         }\r
153         // Update a tag\r
154         public void updateTag(Tag tempTag, boolean isDirty, String realName) {\r
155                 boolean check;\r
156                 \r
157         NSqlQuery query = new NSqlQuery(db.getConnection());\r
158                 check = query.prepare("Update Tag set parentGuid=:parentGuid, sequence=:sequence, "+\r
159                         "hashCode=:hashCode, name=:name, isDirty=:isDirty "\r
160                         +"where guid=:guid");\r
161       \r
162                 if (!check) {\r
163                         logger.log(logger.EXTREME, "Tag SQL update prepare has failed.");\r
164                         logger.log(logger.EXTREME, query.lastError());\r
165                 }\r
166                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
167                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
168                 query.bindValue(":hashCode", tempTag.hashCode());\r
169                 query.bindValue(":name", tempTag.getName());\r
170                 query.bindValue(":isDirty", isDirty);\r
171                 query.bindValue(":guid", tempTag.getGuid());\r
172                 \r
173                 check = query.exec();\r
174                 if (!check)\r
175                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
176                 \r
177         }\r
178         // Delete a tag\r
179         public void expungeTag(String guid, boolean needsSync) {\r
180                 boolean check;\r
181                 \r
182                 \r
183         NSqlQuery query = new NSqlQuery(db.getConnection());\r
184 \r
185         check = query.prepare("delete from Tag "\r
186                                 +"where guid=:guid");\r
187                 if (!check) {\r
188                         logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");\r
189                         logger.log(logger.EXTREME, query.lastError());\r
190                 }\r
191                 query.bindValue(":guid", guid);\r
192                 check = query.exec();\r
193                 if (!check)\r
194                         logger.log(logger.MEDIUM, "Tag delete failed.");\r
195                 \r
196         check = query.prepare("delete from NoteTags "\r
197                                 +"where tagGuid=:guid");\r
198                 if (!check) {\r
199                         logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");\r
200                         logger.log(logger.EXTREME, query.lastError());\r
201                 }\r
202                 \r
203                 query.bindValue(":guid", guid);\r
204                 check = query.exec();\r
205                 if (!check)\r
206                         logger.log(logger.MEDIUM, "NoteTags delete failed.");\r
207                 \r
208                 // Add the work to the parent queue\r
209                 if (needsSync) {\r
210                         DeletedTable del = new DeletedTable(logger, db);\r
211                         del.addDeletedItem(guid, "Tag");\r
212                 }\r
213         }\r
214         // Save a tag\r
215         public void addTag(Tag tempTag, boolean isDirty) {\r
216                 addTag(tempTag, isDirty, false, "", "");\r
217         }\r
218         // Save a tag\r
219         public void addTag(Tag tempTag, boolean isDirty, boolean isLinked, String realName, String notebookGuid) {\r
220                 boolean check;\r
221                 \r
222         NSqlQuery query = new NSqlQuery(db.getConnection());\r
223                 check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty, linked, realName, notebookGuid)"\r
224                                 +" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty, :linked, :realName, :notebookGuid)");\r
225                 if (!check) {\r
226                         logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");\r
227                         logger.log(logger.EXTREME, query.lastError());\r
228                 }\r
229                 query.bindValue(":guid", tempTag.getGuid());\r
230                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
231                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
232                 query.bindValue(":hashCode", tempTag.hashCode());\r
233                 query.bindValue(":name", tempTag.getName());\r
234                 query.bindValue(":isDirty", isDirty);\r
235                 query.bindValue(":linked", isLinked);\r
236                 query.bindValue(":realName", realName);\r
237                 query.bindValue(":notebookGuid", notebookGuid);\r
238                 \r
239                 check = query.exec();\r
240                 if (!check) {\r
241                         logger.log(logger.MEDIUM, "Tag Table insert failed.");\r
242                         logger.log(logger.MEDIUM, query.lastError());\r
243                 }\r
244         }\r
245         // Update a tag's parent\r
246         public void updateTagParent(String guid, String parentGuid) {\r
247                 boolean check;\r
248                 \r
249         NSqlQuery query = new NSqlQuery(db.getConnection());\r
250                 check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");\r
251                 if (!check) {\r
252                         logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");\r
253                         logger.log(logger.EXTREME, query.lastError());\r
254                 }\r
255 \r
256                 query.bindValue(":parentGuid", parentGuid);\r
257                 query.bindValue(":guid", guid);\r
258                 \r
259                 check = query.exec();\r
260                 if (!check) {\r
261                         logger.log(logger.MEDIUM, "Tag parent update failed.");\r
262                         logger.log(logger.MEDIUM, query.lastError());\r
263                 }\r
264         }\r
265         //Save tags from Evernote\r
266         public void saveTags(List<Tag> tags) {\r
267                 Tag tempTag;\r
268                 for (int i=0; i<tags.size(); i++) {\r
269                         tempTag = tags.get(i);\r
270                         addTag(tempTag, false);\r
271                 }               \r
272         }\r
273         // Update a tag sequence number\r
274         public void updateTagSequence(String guid, int sequence) {\r
275                 boolean check;\r
276         NSqlQuery query = new NSqlQuery(db.getConnection());\r
277                 check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");\r
278                 query.bindValue(":sequence", sequence);\r
279                 query.bindValue(":guid", guid);\r
280                 \r
281                 query.exec();\r
282                 if (!check) {\r
283                         logger.log(logger.MEDIUM, "Tag sequence update failed.");\r
284                         logger.log(logger.MEDIUM, query.lastError());\r
285                 }\r
286                 \r
287         }\r
288         // Update a tag sequence number\r
289         public void updateTagGuid(String oldGuid, String newGuid) {\r
290                 boolean check;\r
291                 \r
292         NSqlQuery query = new NSqlQuery(db.getConnection());\r
293                 check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");\r
294                 query.bindValue(":newGuid", newGuid);\r
295                 query.bindValue(":oldGuid", oldGuid);\r
296                 query.exec();\r
297                 if (!check) {\r
298                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
299                         logger.log(logger.MEDIUM, query.lastError());\r
300                 }\r
301                 \r
302                 check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");\r
303                 query.bindValue(":newGuid", newGuid);\r
304                 query.bindValue(":oldGuid", oldGuid);\r
305                 query.exec();\r
306                 if (!check) {\r
307                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
308                         logger.log(logger.MEDIUM, query.lastError());\r
309                 }\r
310                 \r
311                 check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");\r
312                 query.bindValue(":newGuid", newGuid);\r
313                 query.bindValue(":oldGuid", oldGuid);\r
314                 query.exec();\r
315                 if (!check) {\r
316                         logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");\r
317                         logger.log(logger.MEDIUM, query.lastError());\r
318                 }\r
319                 \r
320         }\r
321         // Get dirty tags\r
322         public List<Tag> getDirty() {\r
323                 Tag tempTag;\r
324                 List<Tag> index = new ArrayList<Tag>();\r
325                 boolean check;\r
326                                                 \r
327                 \r
328         NSqlQuery query = new NSqlQuery(db.getConnection());\r
329                                                 \r
330                 check = query.exec("Select guid, parentGuid, sequence, name"\r
331                                 +" from Tag where isDirty = true");\r
332                 if (!check)\r
333                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
334                 while (query.next()) {\r
335                         tempTag = new Tag();\r
336                         tempTag.setGuid(query.valueString(0));\r
337                         tempTag.setParentGuid(query.valueString(1));\r
338                         int sequence = new Integer(query.valueString(2)).intValue();\r
339                         tempTag.setUpdateSequenceNum(sequence);\r
340                         tempTag.setName(query.valueString(3));\r
341                         if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))\r
342                                 tempTag.setParentGuid(null);\r
343                         index.add(tempTag); \r
344                 }\r
345                 return index;\r
346         }\r
347         // Find a guid based upon the name\r
348         public String findTagByName(String name) {\r
349                 \r
350                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
351                 \r
352                 query.prepare("Select guid from tag where name=:name");\r
353                 query.bindValue(":name", name);\r
354                 if (!query.exec())\r
355                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
356                 String val = null;\r
357                 if (query.next())\r
358                         val = query.valueString(0);\r
359                 return val;\r
360         }\r
361         // Get the linked notebook guid for this tag\r
362         public String getNotebookGuid(String guid) {\r
363                 \r
364                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
365                 \r
366                 query.prepare("Select notebookguid from tag where guid=:guid");\r
367                 query.bindValue(":guid", guid);\r
368                 if (!query.exec())\r
369                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
370                 String val = null;\r
371                 if (query.next())\r
372                         val = query.valueString(0);\r
373                 return val;\r
374         }\r
375         // given a guid, does the tag exist\r
376         public boolean exists(String guid) {\r
377                 \r
378                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
379                 \r
380                 query.prepare("Select guid from tag where guid=:guid");\r
381                 query.bindValue(":guid", guid);\r
382                 if (!query.exec())\r
383                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
384                 boolean retval = query.next();\r
385                 return retval;\r
386         }\r
387         // This is a convience method to check if a tag exists & update/create based upon it\r
388         public void syncLinkedTag(Tag tag, String notebookGuid, boolean isDirty) {\r
389                 if (exists(tag.getGuid())) {\r
390                         Tag t = getTag(tag.getGuid());\r
391                         String realName = tag.getName();\r
392                         tag.setName(t.getName());\r
393                         updateTag(tag, isDirty, realName);\r
394                 }\r
395                 else\r
396                         addTag(tag, isDirty, true, tag.getName(), notebookGuid);\r
397         }\r
398 \r
399         // This is a convience method to check if a tag exists & update/create based upon it\r
400         public void syncTag(Tag tag, boolean isDirty) {\r
401                 if (exists(tag.getGuid()))\r
402                         updateTag(tag, isDirty);\r
403                 else\r
404                         addTag(tag, isDirty);\r
405         }\r
406         public void  resetDirtyFlag(String guid) {\r
407                 \r
408                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
409                 \r
410                 query.prepare("Update tag set isdirty=false where guid=:guid");\r
411                 query.bindValue(":guid", guid);\r
412                 if (!query.exec())\r
413                         logger.log(logger.EXTREME, "Error resetting tag dirty field.");\r
414         }\r
415         \r
416         \r
417         // Get the custom icon\r
418         public QIcon getIcon(String guid) {\r
419                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
420                 \r
421                 if (!query.prepare("Select icon from tag where guid=:guid"))\r
422                         logger.log(logger.EXTREME, "Error preparing tag icon select.");\r
423                 query.bindValue(":guid", guid);\r
424                 if (!query.exec())\r
425                         logger.log(logger.EXTREME, "Error finding tag icon.");\r
426                 if (!query.next() || query.getBlob(0) == null)\r
427                         return null;\r
428                 \r
429                 QByteArray blob = new QByteArray(query.getBlob(0));\r
430                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
431                 return icon;\r
432         }\r
433         // Set the custom icon\r
434         public void setIcon(String guid, QIcon icon, String type) {\r
435                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
436                 if (icon == null) {\r
437                         if (!query.prepare("update tag set icon=null where guid=:guid"))\r
438                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
439                 } else {\r
440                         if (!query.prepare("update tag set icon=:icon where guid=:guid"))\r
441                                 logger.log(logger.EXTREME, "Error preparing tag icon update.");\r
442                         QBuffer buffer = new QBuffer();\r
443                 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {\r
444                         logger.log(logger.EXTREME, "Failure to open buffer.  Aborting.");\r
445                         return;\r
446                 }\r
447                 QPixmap p = icon.pixmap(32, 32);\r
448                 QImage i = p.toImage();\r
449                 i.save(buffer, type.toUpperCase());\r
450                 buffer.close();\r
451                 QByteArray b = new QByteArray(buffer.buffer());\r
452                 if (!b.isNull() && !b.isEmpty())\r
453                         query.bindValue(":icon", b.toByteArray());\r
454                 else\r
455                         return;\r
456                 }\r
457                 query.bindValue(":guid", guid);\r
458                 if (!query.exec()) \r
459                         logger.log(logger.LOW, "Error setting tag icon. " +query.lastError());\r
460         }\r
461 \r
462         // Get a list of all icons\r
463         public HashMap<String, QIcon> getAllIcons() {\r
464                 HashMap<String, QIcon> values = new HashMap<String, QIcon>();\r
465                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
466         \r
467                 if (!query.exec("SELECT guid, icon from tag"))\r
468                         logger.log(logger.EXTREME, "Error executing SavedSearch getAllIcons select.");\r
469                 while (query.next()) {\r
470                         if (query.getBlob(1) != null) {\r
471                                 String guid = query.valueString(0);\r
472                                 QByteArray blob = new QByteArray(query.getBlob(1));\r
473                                 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));\r
474                                 values.put(guid, icon);\r
475                         }\r
476                 }\r
477                 return values;\r
478         }\r
479 \r
480         // Remove unused tags that are linked tags\r
481         public void removeUnusedLinkedTags() {\r
482                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
483                 \r
484                 query.exec("Delete from tag where linked=true and guid not in (select distinct tagguid from notetags);");\r
485         }\r
486         \r
487         public void cleanupTags() {\r
488                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
489                 \r
490                 query.exec("Update tag set parentguid=null where parentguid not in (select distinct guid from tag);");  \r
491         }\r
492 }\r