OSDN Git Service

Add logic to display stacks in notebook tree
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / SavedSearchTable.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.QueryFormat;\r
27 import com.evernote.edam.type.SavedSearch;\r
28 \r
29 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
30 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
31 \r
32 public class SavedSearchTable {\r
33         private final ApplicationLogger                 logger;\r
34         private final DatabaseConnection                db;\r
35 \r
36         \r
37         // Constructor\r
38         public SavedSearchTable(ApplicationLogger l, DatabaseConnection d) {\r
39                 logger = l;\r
40                 db = d;\r
41         }\r
42         // Create the table\r
43         public void createTable() {\r
44                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
45         logger.log(logger.HIGH, "Creating table SavedSearch...");\r
46         if (!query.exec("Create table SavedSearch (guid varchar primary key, " +\r
47                         "name varchar, query varchar, format integer, sequence integer, isDirty boolean)"))\r
48                 logger.log(logger.HIGH, "Table SavedSearch creation FAILED!!!"); \r
49         }\r
50         // Drop the table\r
51         public void dropTable() {\r
52                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
53                 query.exec("Drop table SavedSearch");\r
54         }\r
55         // get all tags\r
56         public List<SavedSearch> getAll() {\r
57                 SavedSearch tempSearch;\r
58                 List<SavedSearch> index = new ArrayList<SavedSearch>();\r
59                 boolean check;\r
60                                                 \r
61         NSqlQuery query = new NSqlQuery(db.getConnection());\r
62                                                 \r
63                 check = query.exec("Select guid, name, query, format, sequence"\r
64                                 +" from SavedSearch");\r
65                 if (!check)\r
66                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in getAll().");\r
67                 while (query.next()) {\r
68                         tempSearch = new SavedSearch();\r
69                         tempSearch.setGuid(query.valueString(0));\r
70                         tempSearch.setName(query.valueString(1));\r
71                         tempSearch.setQuery(query.valueString(2));\r
72                         int fmt = new Integer(query.valueString(3));\r
73                         if (fmt == 1)\r
74                                 tempSearch.setFormat(QueryFormat.USER);\r
75                         else\r
76                                 tempSearch.setFormat(QueryFormat.SEXP);\r
77                         int sequence = new Integer(query.valueString(4)).intValue();\r
78                         tempSearch.setUpdateSequenceNum(sequence);\r
79                         index.add(tempSearch); \r
80                 }       \r
81                 return index;\r
82         }\r
83         public SavedSearch getSavedSearch(String guid) {\r
84                 SavedSearch tempSearch = null;\r
85                 boolean check;\r
86                         \r
87         NSqlQuery query = new NSqlQuery(db.getConnection());\r
88                                                 \r
89                 check = query.prepare("Select guid, name, query, format, sequence"\r
90                                 +" from SavedSearch where guid=:guid");\r
91                 if (!check)\r
92                         logger.log(logger.EXTREME, "SavedSearch SQL prepare has failed in getSavedSearch.");\r
93                 query.bindValue(":guid", guid);\r
94                 query.exec();\r
95                 if (!check)\r
96                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in getSavedSearch.");\r
97                 if (query.next()) {\r
98                         tempSearch = new SavedSearch();\r
99                         tempSearch.setGuid(query.valueString(0));\r
100                         tempSearch.setName(query.valueString(1));\r
101                         tempSearch.setQuery(query.valueString(2));\r
102                         int fmt = new Integer(query.valueString(3));\r
103                         if (fmt == 1)\r
104                                 tempSearch.setFormat(QueryFormat.USER);\r
105                         else\r
106                                 tempSearch.setFormat(QueryFormat.SEXP);\r
107                         int sequence = new Integer(query.valueString(4)).intValue();\r
108                         tempSearch.setUpdateSequenceNum(sequence);\r
109                 }\r
110                 return tempSearch;\r
111         }\r
112         // Update a tag\r
113         public void updateSavedSearch(SavedSearch search, boolean isDirty) {\r
114                 boolean check;\r
115         NSqlQuery query = new NSqlQuery(db.getConnection());\r
116                 check = query.prepare("Update SavedSearch set sequence=:sequence, "+\r
117                         "name=:name, isDirty=:isDirty, query=:query, format=:format "\r
118                         +"where guid=:guid");\r
119        \r
120                 if (!check) {\r
121                         logger.log(logger.EXTREME, "SavedSearch SQL update prepare has failed.");\r
122                         logger.log(logger.EXTREME, query.lastError().toString());\r
123                 }\r
124                 query.bindValue(":sequence", search.getUpdateSequenceNum());\r
125                 query.bindValue(":name", search.getName());\r
126                 query.bindValue(":isDirty", isDirty);\r
127                 query.bindValue(":query", search.getQuery());\r
128                 if (search.getFormat() == QueryFormat.USER)\r
129                         query.bindValue(":format", 1);\r
130                 else\r
131                         query.bindValue(":format", 2);\r
132                 \r
133                 query.bindValue(":guid", search.getGuid());\r
134                 \r
135                 check = query.exec();\r
136                 if (!check) {\r
137                         logger.log(logger.MEDIUM, "Tag Table update failed.");\r
138                         logger.log(logger.EXTREME, query.lastError().toString());\r
139                 }\r
140         }\r
141         // Delete a tag\r
142         public void expungeSavedSearch(String guid, boolean needsSync) {\r
143                 boolean check;\r
144         NSqlQuery query = new NSqlQuery(db.getConnection());\r
145 \r
146         check = query.prepare("delete from SavedSearch "\r
147                                 +"where guid=:guid");\r
148                 if (!check) {\r
149                         logger.log(logger.EXTREME, "SavedSearch SQL delete prepare has failed.");\r
150                         logger.log(logger.EXTREME, query.lastError().toString());\r
151                 }\r
152                 query.bindValue(":guid", guid);\r
153                 check = query.exec();\r
154                 if (!check) {\r
155                         logger.log(logger.MEDIUM, "Saved Search delete failed.");\r
156                         logger.log(logger.EXTREME, query.lastError().toString());\r
157                 }\r
158 \r
159                 // Add the work to the parent queue\r
160                 if (needsSync) {\r
161                         DeletedTable del = new DeletedTable(logger, db);\r
162                         del.addDeletedItem(guid, "SavedSearch");\r
163                 }\r
164         }\r
165         // Save a tag\r
166         public void addSavedSearch(SavedSearch search, boolean isDirty) {\r
167                 boolean check;\r
168         NSqlQuery query = new NSqlQuery(db.getConnection());\r
169                 check = query.prepare("Insert Into SavedSearch (guid, query, sequence, format, name, isDirty)"\r
170                                 +" Values(:guid, :query, :sequence, :format, :name, :isDirty)");\r
171                 if (!check) {\r
172                         logger.log(logger.EXTREME, "Search SQL insert prepare has failed.");\r
173                         logger.log(logger.EXTREME, query.lastError().toString());\r
174                 }\r
175                 query.bindValue(":guid", search.getGuid());\r
176                 query.bindValue(":query", search.getQuery());\r
177                 query.bindValue(":sequence", search.getUpdateSequenceNum());\r
178                 if (search.getFormat() == QueryFormat.USER)\r
179                         query.bindValue(":format", 1);\r
180                 else\r
181                         query.bindValue(":format", 2);\r
182                 query.bindValue(":name", search.getName());\r
183                 query.bindValue(":isDirty", isDirty);\r
184         \r
185                 check = query.exec();\r
186                 if  (!check) {\r
187                         logger.log(logger.MEDIUM, "Search Table insert failed.");\r
188                         logger.log(logger.MEDIUM, query.lastError().toString());\r
189                 }\r
190         }\r
191         // Update a tag sequence number\r
192         public void updateSavedSearchSequence(String guid, int sequence) {\r
193                 boolean check;\r
194                 ;\r
195         NSqlQuery query = new NSqlQuery(db.getConnection());\r
196                 check = query.prepare("Update SavedSearch set sequence=:sequence where guid=:guid");\r
197                 query.bindValue(":sequence", sequence);\r
198                 query.bindValue(":guid", guid);\r
199                 query.exec();\r
200                 if (!check) {\r
201                         logger.log(logger.MEDIUM, "SavedSearch sequence update failed.");\r
202                         logger.log(logger.MEDIUM, query.lastError());\r
203                 }\r
204         }\r
205         // Update a tag sequence number\r
206         public void updateSavedSearchGuid(String oldGuid, String newGuid) {\r
207                 boolean check;\r
208         NSqlQuery query = new NSqlQuery(db.getConnection());\r
209                 check = query.prepare("Update SavedSearch set guid=:newGuid where guid=:oldGuid");\r
210                 query.bindValue(":newGuid", newGuid);\r
211                 query.bindValue(":oldGuid", oldGuid);\r
212                 query.exec();\r
213                 if (!check) {\r
214                         logger.log(logger.MEDIUM, "SavedSearch guid update failed.");\r
215                         logger.log(logger.MEDIUM, query.lastError());\r
216                 }\r
217         }\r
218         // Get dirty tags\r
219         public List<SavedSearch> getDirty() {\r
220                 SavedSearch search;\r
221                 List<SavedSearch> index = new ArrayList<SavedSearch>();\r
222                 boolean check;\r
223                                                 \r
224         NSqlQuery query = new NSqlQuery(db.getConnection());\r
225                                                 \r
226                 check = query.exec("Select guid, query, sequence, name, format"\r
227                                 +" from SavedSearch where isDirty = true");\r
228                 if (!check)\r
229                         logger.log(logger.EXTREME, "SavedSearch getDirty prepare has failed.");\r
230                 while (query.next()) {\r
231                         search = new SavedSearch();\r
232                         search.setGuid(query.valueString(0));\r
233                         search.setQuery(query.valueString(1));\r
234                         int sequence = new Integer(query.valueString(2)).intValue();\r
235                         search.setUpdateSequenceNum(sequence);\r
236                         search.setName(query.valueString(3));\r
237                         int fmt = new Integer(query.valueString(4)).intValue();\r
238                         if (fmt == 1)\r
239                                 search.setFormat(QueryFormat.USER);\r
240                         else\r
241                                 search.setFormat(QueryFormat.SEXP);\r
242                         index.add(search); \r
243                 }       \r
244                 return index;\r
245         }\r
246         // Find a guid based upon the name\r
247         public String findSavedSearchByName(String name) {\r
248                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
249                 \r
250                 query.prepare("Select guid from SavedSearch where name=:name");\r
251                 query.bindValue(":name", name);\r
252                 if (!query.exec())\r
253                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in findSavedSearchByName().");\r
254                 String val = null;\r
255                 if (query.next())\r
256                         val = query.valueString(0);\r
257                 return val;\r
258         }\r
259         // given a guid, does the tag exist\r
260         public boolean exists(String guid) {\r
261                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
262                 query.prepare("Select guid from SavedSearch where guid=:guid");\r
263                 query.bindValue(":guid", guid);\r
264                 if (!query.exec())\r
265                         logger.log(logger.EXTREME, "SavedSearch SQL retrieve has failed in exists().");\r
266                 boolean retval = query.next();\r
267                 return retval;\r
268         }\r
269         // This is a convience method to check if a tag exists & update/create based upon it\r
270         public void syncSavedSearch(SavedSearch search, boolean isDirty) {\r
271                 if (exists(search.getGuid()))\r
272                         updateSavedSearch(search, isDirty);\r
273                 else\r
274                         addSavedSearch(search, isDirty);\r
275         }\r
276         public void  resetDirtyFlag(String guid) {\r
277                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
278                 \r
279                 query.prepare("Update SavedSearch set isdirty=false where guid=:guid");\r
280                 query.bindValue(":guid", guid);\r
281                 if (!query.exec())\r
282                         logger.log(logger.EXTREME, "Error resetting SavedSearch dirty field in resetDirtyFlag().");\r
283         }\r
284 }\r