OSDN Git Service

Add logic to display stacks in notebook tree
[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.List;\r
25 \r
26 import com.evernote.edam.type.Tag;\r
27 \r
28 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
29 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
30 \r
31 public class TagTable {\r
32         private final ApplicationLogger logger;\r
33         DatabaseConnection db;\r
34 \r
35         public TagTable (ApplicationLogger l, DatabaseConnection d) {\r
36                 logger = l;\r
37                 db = d;\r
38         }\r
39         // Create the table\r
40         public void createTable() {\r
41                 \r
42                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
43         logger.log(logger.HIGH, "Creating table Tag...");\r
44         if (!query.exec("Create table Tag (guid varchar primary key, " +\r
45                         "parentGuid varchar, sequence integer, hashCode integer, name varchar, isDirty boolean)"))\r
46                 logger.log(logger.HIGH, "Table TAG creation FAILED!!!");  \r
47                 \r
48         }\r
49         // Drop the table\r
50         public void dropTable() {\r
51                 \r
52                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
53                 query.exec("Drop table Tag");\r
54                 \r
55         }\r
56         // get all tags\r
57         public List<Tag> getAll() {\r
58                 \r
59                 Tag tempTag;\r
60                 List<Tag> index = new ArrayList<Tag>();\r
61                 boolean check;\r
62                                                 \r
63         NSqlQuery query = new NSqlQuery(db.getConnection());\r
64                                                 \r
65                 check = query.exec("Select guid, parentGuid, sequence, name"\r
66                                 +" from Tag");\r
67                 if (!check) {\r
68                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
69                         logger.log(logger.EXTREME, query.lastError());\r
70                 }\r
71                 while (query.next()) {\r
72                         tempTag = new Tag();\r
73                         tempTag.setGuid(query.valueString(0));\r
74                         if (query.valueString(1) != null)\r
75                                 tempTag.setParentGuid(query.valueString(1));\r
76                         else\r
77                                 tempTag.setParentGuid(null);\r
78                         int sequence = new Integer(query.valueString(2)).intValue();\r
79                         tempTag.setUpdateSequenceNum(sequence);\r
80                         tempTag.setName(query.valueString(3));\r
81                         index.add(tempTag); \r
82                 }       \r
83                 \r
84                 return index;\r
85         }\r
86         public Tag getTag(String guid) {\r
87                 Tag tempTag = new Tag();                \r
88                 \r
89         NSqlQuery query = new NSqlQuery(db.getConnection());\r
90                                                         \r
91                 if (!query.prepare("Select guid, parentGuid, sequence, name"\r
92                                 +" from Tag where guid=:guid"))\r
93                         logger.log(logger.EXTREME, "Tag select by guid SQL prepare has failed.");\r
94 \r
95                 query.bindValue(":guid", guid);\r
96                 if (!query.exec())\r
97                         logger.log(logger.EXTREME, "Tag select by guid SQL exec has failed.");\r
98                 \r
99                 if (!query.next())  {\r
100                         return tempTag;\r
101                 }\r
102                 tempTag.setGuid(query.valueString(0));\r
103                 tempTag.setParentGuid(query.valueString(1));\r
104                 int sequence = new Integer(query.valueString(2)).intValue();\r
105                 tempTag.setUpdateSequenceNum(sequence);\r
106                 tempTag.setName(query.valueString(3));\r
107                 return tempTag;\r
108         }\r
109         // Update a tag\r
110         public void updateTag(Tag tempTag, boolean isDirty) {\r
111                 boolean check;\r
112                 \r
113         NSqlQuery query = new NSqlQuery(db.getConnection());\r
114                 check = query.prepare("Update Tag set parentGuid=:parentGuid, sequence=:sequence, "+\r
115                         "hashCode=:hashCode, name=:name, isDirty=:isDirty "\r
116                         +"where guid=:guid");\r
117       \r
118                 if (!check) {\r
119                         logger.log(logger.EXTREME, "Tag SQL update prepare has failed.");\r
120                         logger.log(logger.EXTREME, query.lastError());\r
121                 }\r
122                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
123                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
124                 query.bindValue(":hashCode", tempTag.hashCode());\r
125                 query.bindValue(":name", tempTag.getName());\r
126                 query.bindValue(":isDirty", isDirty);\r
127                 query.bindValue(":guid", tempTag.getGuid());\r
128                 \r
129                 check = query.exec();\r
130                 if (!check)\r
131                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
132                 \r
133         }\r
134         // Delete a tag\r
135         public void expungeTag(String guid, boolean needsSync) {\r
136                 boolean check;\r
137                 \r
138                 \r
139         NSqlQuery query = new NSqlQuery(db.getConnection());\r
140 \r
141         check = query.prepare("delete from Tag "\r
142                                 +"where guid=:guid");\r
143                 if (!check) {\r
144                         logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");\r
145                         logger.log(logger.EXTREME, query.lastError());\r
146                 }\r
147                 query.bindValue(":guid", guid);\r
148                 check = query.exec();\r
149                 if (!check)\r
150                         logger.log(logger.MEDIUM, "Tag delete failed.");\r
151                 \r
152         check = query.prepare("delete from NoteTags "\r
153                                 +"where tagGuid=:guid");\r
154                 if (!check) {\r
155                         logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");\r
156                         logger.log(logger.EXTREME, query.lastError());\r
157                 }\r
158                 \r
159                 query.bindValue(":guid", guid);\r
160                 check = query.exec();\r
161                 if (!check)\r
162                         logger.log(logger.MEDIUM, "NoteTags delete failed.");\r
163                 \r
164                 // Add the work to the parent queue\r
165                 if (needsSync) {\r
166                         DeletedTable del = new DeletedTable(logger, db);\r
167                         del.addDeletedItem(guid, "Tag");\r
168                 }\r
169         }\r
170         // Save a tag\r
171         public void addTag(Tag tempTag, boolean isDirty) {\r
172                 boolean check;\r
173                 \r
174         NSqlQuery query = new NSqlQuery(db.getConnection());\r
175                 check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty)"\r
176                                 +" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty)");\r
177                 if (!check) {\r
178                         logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");\r
179                         logger.log(logger.EXTREME, query.lastError());\r
180                 }\r
181                 query.bindValue(":guid", tempTag.getGuid());\r
182                 query.bindValue(":parentGuid", tempTag.getParentGuid());\r
183                 query.bindValue(":sequence", tempTag.getUpdateSequenceNum());\r
184                 query.bindValue(":hashCode", tempTag.hashCode());\r
185                 query.bindValue(":name", tempTag.getName());\r
186                 query.bindValue(":isDirty", isDirty);\r
187                 \r
188                 check = query.exec();\r
189                 if (!check) {\r
190                         logger.log(logger.MEDIUM, "Tag Table insert failed.");\r
191                         logger.log(logger.MEDIUM, query.lastError());\r
192                 }\r
193         }\r
194         // Update a tag's parent\r
195         public void updateTagParent(String guid, String parentGuid) {\r
196                 boolean check;\r
197                 \r
198         NSqlQuery query = new NSqlQuery(db.getConnection());\r
199                 check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");\r
200                 if (!check) {\r
201                         logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");\r
202                         logger.log(logger.EXTREME, query.lastError());\r
203                 }\r
204 \r
205                 query.bindValue(":parentGuid", parentGuid);\r
206                 query.bindValue(":guid", guid);\r
207                 \r
208                 check = query.exec();\r
209                 if (!check) {\r
210                         logger.log(logger.MEDIUM, "Tag parent update failed.");\r
211                         logger.log(logger.MEDIUM, query.lastError());\r
212                 }\r
213         }\r
214         //Save tags from Evernote\r
215         public void saveTags(List<Tag> tags) {\r
216                 Tag tempTag;\r
217                 for (int i=0; i<tags.size(); i++) {\r
218                         tempTag = tags.get(i);\r
219                         addTag(tempTag, false);\r
220                 }               \r
221         }\r
222         // Update a tag sequence number\r
223         public void updateTagSequence(String guid, int sequence) {\r
224                 boolean check;\r
225         NSqlQuery query = new NSqlQuery(db.getConnection());\r
226                 check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");\r
227                 query.bindValue(":sequence", sequence);\r
228                 query.bindValue(":guid", guid);\r
229                 \r
230                 query.exec();\r
231                 if (!check) {\r
232                         logger.log(logger.MEDIUM, "Tag sequence update failed.");\r
233                         logger.log(logger.MEDIUM, query.lastError());\r
234                 }\r
235                 \r
236         }\r
237         // Update a tag sequence number\r
238         public void updateTagGuid(String oldGuid, String newGuid) {\r
239                 boolean check;\r
240                 \r
241         NSqlQuery query = new NSqlQuery(db.getConnection());\r
242                 check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");\r
243                 query.bindValue(":newGuid", newGuid);\r
244                 query.bindValue(":oldGuid", oldGuid);\r
245                 query.exec();\r
246                 if (!check) {\r
247                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
248                         logger.log(logger.MEDIUM, query.lastError());\r
249                 }\r
250                 \r
251                 check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");\r
252                 query.bindValue(":newGuid", newGuid);\r
253                 query.bindValue(":oldGuid", oldGuid);\r
254                 query.exec();\r
255                 if (!check) {\r
256                         logger.log(logger.MEDIUM, "Tag guid update failed.");\r
257                         logger.log(logger.MEDIUM, query.lastError());\r
258                 }\r
259                 \r
260                 check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");\r
261                 query.bindValue(":newGuid", newGuid);\r
262                 query.bindValue(":oldGuid", oldGuid);\r
263                 query.exec();\r
264                 if (!check) {\r
265                         logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");\r
266                         logger.log(logger.MEDIUM, query.lastError());\r
267                 }\r
268                 \r
269         }\r
270         // Get dirty tags\r
271         public List<Tag> getDirty() {\r
272                 Tag tempTag;\r
273                 List<Tag> index = new ArrayList<Tag>();\r
274                 boolean check;\r
275                                                 \r
276                 \r
277         NSqlQuery query = new NSqlQuery(db.getConnection());\r
278                                                 \r
279                 check = query.exec("Select guid, parentGuid, sequence, name"\r
280                                 +" from Tag where isDirty = true");\r
281                 if (!check)\r
282                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
283                 while (query.next()) {\r
284                         tempTag = new Tag();\r
285                         tempTag.setGuid(query.valueString(0));\r
286                         tempTag.setParentGuid(query.valueString(1));\r
287                         int sequence = new Integer(query.valueString(2)).intValue();\r
288                         tempTag.setUpdateSequenceNum(sequence);\r
289                         tempTag.setName(query.valueString(3));\r
290                         if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))\r
291                                 tempTag.setParentGuid(null);\r
292                         index.add(tempTag); \r
293                 }\r
294                 return index;\r
295         }\r
296         // Find a guid based upon the name\r
297         public String findTagByName(String name) {\r
298                 \r
299                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
300                 \r
301                 query.prepare("Select guid from tag where name=:name");\r
302                 query.bindValue(":name", name);\r
303                 if (!query.exec())\r
304                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
305                 String val = null;\r
306                 if (query.next())\r
307                         val = query.valueString(0);\r
308                 return val;\r
309         }\r
310         // given a guid, does the tag exist\r
311         public boolean exists(String guid) {\r
312                 \r
313                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
314                 \r
315                 query.prepare("Select guid from tag where guid=:guid");\r
316                 query.bindValue(":guid", guid);\r
317                 if (!query.exec())\r
318                         logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");\r
319                 boolean retval = query.next();\r
320                 return retval;\r
321         }\r
322         // This is a convience method to check if a tag exists & update/create based upon it\r
323         public void syncTag(Tag tag, boolean isDirty) {\r
324                 if (exists(tag.getGuid()))\r
325                         updateTag(tag, isDirty);\r
326                 else\r
327                         addTag(tag, isDirty);\r
328         }\r
329         public void  resetDirtyFlag(String guid) {\r
330                 \r
331                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
332                 \r
333                 query.prepare("Update tag set isdirty=false where guid=:guid");\r
334                 query.bindValue(":guid", guid);\r
335                 if (!query.exec())\r
336                         logger.log(logger.EXTREME, "Error resetting tag dirty field.");\r
337         }\r
338 }\r