OSDN Git Service

1f24b686a669101621dc478764247076c2869a05
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / NoteResourceTable.java
1 /*\r
2  * This file is part of NixNote \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 import java.text.SimpleDateFormat;\r
23 import java.util.ArrayList;\r
24 import java.util.List;\r
25 \r
26 import com.evernote.edam.type.Data;\r
27 import com.evernote.edam.type.Resource;\r
28 import com.evernote.edam.type.ResourceAttributes;\r
29 import com.trolltech.qt.core.QByteArray;\r
30 \r
31 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
32 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
33 \r
34 \r
35 \r
36 public class NoteResourceTable  {\r
37         /**\r
38          * \r
39          */\r
40 //      private static final long serialVersionUID = 1L;\r
41         private final ApplicationLogger                 logger;\r
42         private final DatabaseConnection                db;     \r
43         \r
44         // Constructor\r
45         public NoteResourceTable(ApplicationLogger l, DatabaseConnection d) {\r
46                 logger = l;\r
47                 db = d;\r
48         }\r
49         // Create the table\r
50         public void createTable() {\r
51                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
52         // Create the NoteResource table\r
53         logger.log(logger.HIGH, "Creating table NoteResource...");\r
54         if (!query.exec("Create table NoteResources (guid varchar primary key, " +\r
55                         "noteGuid varchar, updateSequenceNumber integer, dataHash varchar, "+\r
56                         "dataSize integer, dataBinary blob, "+\r
57                         "mime varchar, width integer, height integer, duration integer, "+\r
58                         "active integer, recognitionHash varchar, recognitionSize integer, " +\r
59                         "recognitionBinary varchar, attributeSourceUrl varchar, attributeTimestamp timestamp, " +\r
60                         "attributeLatitude double, attributeLongitude double, "+\r
61                         "attributeAltitude double, attributeCameraMake varchar, attributeCameraModel varchar, "\r
62                         +"attributeClientWillIndex varchar, attributeRecoType varchar, attributeFileName varchar,"+\r
63                         "attributeAttachment boolean, isDirty boolean, indexNeeded boolean)"))\r
64                 logger.log(logger.HIGH, "Table NoteResource creation FAILED!!!"); \r
65         if (!query.exec("CREATE INDEX unindexed_resources on noteresources (indexneeded desc, guid);"))\r
66                 logger.log(logger.HIGH, "Noteresources unindexed_resources index creation FAILED!!!");\r
67         if (!query.exec("CREATE INDEX resources_dataheshhex on noteresources (datahash, guid);"))\r
68                 logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r
69         if (!query.exec("create index RESOURCES_GUID_INDEX on noteresources (noteGuid, guid);"))\r
70                 logger.log(logger.HIGH, "Noteresources resources_datahash index creation FAILED!!!");  \r
71         }\r
72         // Drop the table\r
73         public void dropTable() {               \r
74                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
75                 query.exec("Drop table NoteResources");\r
76         }\r
77         // Reset the dirty flag\r
78         public void  resetDirtyFlag(String guid) {\r
79                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
80                 \r
81                 query.prepare("Update noteresources set isdirty=false where guid=:guid");\r
82                 query.bindValue(":guid", guid);\r
83                 if (!query.exec())\r
84                         logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError());\r
85                 else\r
86                         query.exec("commit");\r
87         }\r
88         // Set if the resource should be indexed\r
89         public void  setIndexNeeded(String guid, Boolean indexNeeded) {\r
90                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());            \r
91                 query.prepare("Update noteresources set indexNeeded=:needed where guid=:guid");\r
92                 query.bindValue(":needed", indexNeeded);\r
93                 query.bindValue(":guid", guid);\r
94                 if (!query.exec())\r
95                         logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError());\r
96                 else\r
97                         query.exec("commit");\r
98         }\r
99         // get any unindexed resource\r
100         public List<String> getNextUnindexed(int limit) {\r
101                 List<String> guids = new ArrayList<String>();\r
102         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
103                                         \r
104                 if (!query.exec("Select guid from NoteResources where indexNeeded = true limit " +limit))\r
105                         logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getNextUnindexed(): " +query.lastError());\r
106 \r
107                 // Get a list of the notes\r
108                 String guid;\r
109                 while (query.next()) {\r
110                         guid = new String();\r
111                         guid = query.valueString(0);\r
112                         guids.add(guid);\r
113                 }       \r
114                 return guids;   \r
115         }\r
116         // get any unindexed resource\r
117         public List<String> getUnindexed() {\r
118                 List<String> guids = new ArrayList<String>();\r
119         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
120                                         \r
121                 if (!query.exec("Select guid from NoteResources where indexNeeded = true"))\r
122                         logger.log(logger.EXTREME, "NoteResources SQL retrieve has failed on getUnindexed(): " +query.lastError());\r
123 \r
124                 // Get a list of the notes\r
125                 String guid;\r
126                 while (query.next()) {\r
127                         guid = new String();\r
128                         guid = query.valueString(0);\r
129                         guids.add(guid);\r
130                 }       \r
131                 return guids;   \r
132         }\r
133 \r
134         public List<String> getAll() {\r
135                 List<String> guids = new ArrayList<String>();\r
136                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
137                 \r
138                 query.prepare("Select guid from noteresources;");\r
139                 if (!query.exec())\r
140                         logger.log(logger.EXTREME, "Error getting all note resource guids. " +query.lastError());\r
141                 \r
142                 while (query.next()) {\r
143                         guids.add(query.valueString(0));\r
144                 }\r
145                 return guids;\r
146         }\r
147         \r
148         public List<String> findInkNotes() {\r
149                 List<String> guids = new ArrayList<String>();\r
150                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
151                 \r
152                 query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'");\r
153                 if (!query.exec())\r
154                         logger.log(logger.EXTREME, "Error searching for ink notes. " +query.lastError());\r
155                 \r
156                 while (query.next()) {\r
157                         guids.add(query.valueString(0));\r
158                 }\r
159                 return guids;\r
160         }\r
161         \r
162         public void saveNoteResource(Resource r, boolean isDirty) {\r
163                 logger.log(logger.HIGH, "Entering saveNoteResources: isDirty " +isDirty);\r
164                 boolean check;\r
165                 logger.log(logger.HIGH, "Note: " +r.getNoteGuid());\r
166                 logger.log(logger.HIGH, "Resource: " +r.getGuid());\r
167                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
168                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
169                 \r
170                 check = query.prepare("Insert Into NoteResources ("\r
171                                 +"guid, noteGuid, dataHash, dataSize, dataBinary, updateSequenceNumber, "\r
172                                 +"mime, width, height, duration, active, recognitionHash, "                             \r
173                                 +"recognitionSize, recognitionBinary, attributeSourceUrl, attributeTimestamp, "\r
174                                 +"attributeLatitude, attributeLongitude, attributeAltitude, attributeCameraMake, "\r
175                                 +"attributeCameraModel, "\r
176                                 +"attributeClientWillIndex, attributeRecoType, attributeFileName, attributeAttachment, isDirty, "\r
177                                 +"indexNeeded) Values("\r
178                                 +":guid, :noteGuid, :dataHash,:dataSize, :dataBody, :updateSequenceNumber, "\r
179                                 +":mime, :width, :height, :duration, :active, :recognitionHash, "                               \r
180                                 +":recognitionSize, :recognitionBody, :attributeSourceUrl, :attributeTimestamp, "\r
181                                 +":attributeLatitude, :attributeLongitude, :attributeAltitude, :attributeCameraMake, "\r
182                                 +":attributeCameraModel, "\r
183                                 +":attributeClientWillIndex, :attributeRecoType, :attributeFileName, :attributeAttachment, "\r
184                                 +":isDirty, true)");\r
185                 if (!check) {\r
186                         logger.log(logger.EXTREME, "NoteResource SQL insert prepare has failed.");\r
187                         logger.log(logger.MEDIUM, query.lastError());\r
188                 }\r
189         \r
190                         query.bindValue(":guid", r.getGuid());\r
191                         query.bindValue(":noteGuid", r.getNoteGuid());\r
192                         if (r.getData() != null) {\r
193                                 query.bindValue(":dataHash", byteArrayToHexString(r.getData().getBodyHash()));\r
194 //                              query.bindValue(":dataHash", "c0369123fe9871d675ae456fd056ba33");\r
195                                 query.bindValue(":dataSize", r.getData().getSize());\r
196                                 query.bindBlob(":dataBody", r.getData().getBody());\r
197                         }\r
198                         query.bindValue(":updateSequenceNumber", r.getUpdateSequenceNum());\r
199                         query.bindValue(":mime", r.getMime());\r
200                         query.bindValue(":width", new Integer(r.getWidth()));\r
201                         query.bindValue(":height", new Integer(r.getHeight()));\r
202                         query.bindValue(":duration", new Integer(r.getDuration()));\r
203                         query.bindValue(":active", r.isActive());\r
204                         if (r.getRecognition() != null) {\r
205                                 query.bindValue(":recognitionHash", r.getRecognition().getBodyHash());\r
206                                 query.bindValue(":recognitionSize", r.getRecognition().getSize());\r
207                                 if (r.getRecognition().getBody() != null)\r
208                                         query.bindValue(":recognitionBody", new String(r.getRecognition().getBody()));\r
209                                 else\r
210                                         query.bindValue(":recognitionBody", "");\r
211                         } else {\r
212                                 query.bindValue(":recognitionHash", "");\r
213                                 query.bindValue(":recognitionSize", 0);\r
214                                 query.bindValue(":recognitionBody", "");\r
215                         }\r
216                         if (r.getAttributes() != null) {\r
217                                 query.bindValue(":attributeSourceUrl", r.getAttributes().getSourceURL());\r
218                                 StringBuilder ts = new StringBuilder(simple.format(r.getAttributes().getTimestamp()));\r
219                                 query.bindValue(":attributeTimestamp", ts.toString());\r
220                                 query.bindValue(":attributeLatitude", r.getAttributes().getLatitude());\r
221                                 query.bindValue(":attributeLongitude", r.getAttributes().getLongitude());\r
222                                 query.bindValue(":attributeAltitude", r.getAttributes().getAltitude());\r
223                                 query.bindValue(":attributeCameraMake", r.getAttributes().getCameraMake());\r
224                                 query.bindValue(":attributeCameraModel", r.getAttributes().getCameraModel());\r
225                                 query.bindValue(":attributeClientWillIndex", r.getAttributes().isClientWillIndex());\r
226                                 query.bindValue(":attributeRecoType", r.getAttributes().getRecoType());\r
227                                 query.bindValue(":attributeFileName", r.getAttributes().getFileName());\r
228                                 query.bindValue(":attributeAttachment", r.getAttributes().isAttachment());                      \r
229                         }\r
230                         query.bindValue(":isDirty", isDirty);\r
231                                                 \r
232                         check = query.exec();\r
233                         if (!check) {\r
234                                 logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");             \r
235                                 logger.log(logger.MEDIUM, query.lastError());\r
236                         } else\r
237                                 query.exec("commit");\r
238                         \r
239                                                 \r
240                         logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources");\r
241         }\r
242         // delete an old resource\r
243         public void expungeNoteResource(String guid) {\r
244                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
245                 query.prepare("delete from NoteResources where guid=:guid");\r
246                 query.bindValue(":guid", guid);\r
247                 query.exec();\r
248                 query.exec("commit");\r
249                 \r
250                 NSqlQuery query2 = new NSqlQuery(db.getConnection());\r
251                 query2.prepare("Delete from InkImages where guid=:guid");\r
252                 query2.bindValue(":guid", guid);\r
253                 query2.exec();\r
254                 query2.exec("commit");\r
255 \r
256         }\r
257 \r
258         \r
259         // Get a note resource from the database by it's hash value\r
260         public String getNoteResourceGuidByHashHex(String noteGuid, String hash) {\r
261                 logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");\r
262 \r
263                 boolean check;\r
264                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
265                 \r
266                 check = query.prepare("Select guid from NoteResources " +\r
267                                         "where noteGuid=:noteGuid and dataHash=:hash");\r
268                 if (check)\r
269                         logger.log(logger.EXTREME, "NoteResource SQL select prepare was successful.");\r
270                 else {\r
271                         logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
272                         logger.log(logger.MEDIUM, query.lastError());\r
273                 }\r
274                 query.bindValue(":noteGuid", noteGuid);\r
275                 query.bindValue(":hash", hash);\r
276         \r
277                 check = query.exec();\r
278                 if (!check)      {\r
279                         logger.log(logger.MEDIUM, "dbRunner.getNoteResourceGuidByHashHex Select failed." +\r
280                                         "Note Guid:" +noteGuid+\r
281                                         "Data Body Hash:" +hash);               \r
282                         logger.log(logger.MEDIUM, query.lastError());\r
283                 }\r
284                 if (!query.next()) {\r
285                         logger.log(logger.MEDIUM, "Note Resource not found.");\r
286                         return null;\r
287                 }\r
288                 return query.valueString(0);\r
289         }\r
290 \r
291         // Get a note resource from the database by it's hash value\r
292         public Resource getNoteResourceDataBodyByHashHex(String noteGuid, String hash) {\r
293                 logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");\r
294 \r
295                 boolean check;\r
296                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
297                 \r
298                 check = query.prepare("Select guid, mime, from NoteResources " +\r
299                                         "where noteGuid=:noteGuid and dataHash=:hash");\r
300                 if (!check) {\r
301                         logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");\r
302                         logger.log(logger.MEDIUM, query.lastError());\r
303                 }\r
304                 query.bindValue(":noteGuid", noteGuid);\r
305                 query.bindValue(":hash", hash);\r
306         \r
307                 if (!query.exec()) {\r
308                         logger.log(logger.MEDIUM, "NoteResource Select failed." +\r
309                                         "Note Guid:" +noteGuid+\r
310                                         "Data Body Hash:" +hash);               \r
311                         logger.log(logger.MEDIUM, query.lastError());\r
312                 }\r
313                 if (!query.next()) {\r
314                         logger.log(logger.MEDIUM, "Note Resource not found.");\r
315                         return null;\r
316                 }\r
317                 \r
318                 Resource r = new Resource();\r
319                 r.setGuid(query.valueString(0));\r
320                 r.setMime(query.valueString(1));\r
321                 \r
322                 NSqlQuery binary = new NSqlQuery(db.getResourceConnection());\r
323                 if (!binary.prepare("Select databinary from NoteResources " +\r
324                                         "where guid=:guid")) {\r
325                         logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");\r
326                         logger.log(logger.MEDIUM, binary.lastError());\r
327                 }\r
328                 \r
329                 if (!binary.exec()) {\r
330                         logger.log(logger.MEDIUM, "NoteResources Binary Select failed." +\r
331                                         "Note Guid:" +noteGuid+\r
332                                         "Data Body Hash:" +hash);               \r
333                         logger.log(logger.MEDIUM, binary.lastError());\r
334                 }\r
335                 if (!binary.next()) {\r
336                         logger.log(logger.MEDIUM, "Note Resource Binary not found.");\r
337                         return null;\r
338                 }\r
339                 \r
340                 Data d = new Data();\r
341                 r.setData(d);\r
342                 d.setBody(binary.valueString(0).getBytes());\r
343                 logger.log(logger.HIGH, "Leaving DBRunner.getNoteResourceDataBodyByHash");\r
344                 return r;\r
345         }\r
346 \r
347         \r
348         // Get a note's resourcesby Guid\r
349         public Resource getNoteResource(String guid, boolean withBinary) {\r
350                 if (guid == null)\r
351                         return null;\r
352                 \r
353                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
354                 String queryString;\r
355                 queryString = new String("Select guid, noteGuid, mime, width, height, duration, "\r
356                                 +"active, updateSequenceNumber, dataHash, dataSize, "\r
357                                 +"recognitionHash, recognitionSize, "\r
358                                 +"attributeLatitude, attributeLongitude, attributeAltitude, "\r
359                                 +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, "\r
360                                 +"attributeRecoType, attributeFileName, attributeAttachment, attributeSourceUrl "\r
361                                 +" from NoteResources where guid=:guid");\r
362 \r
363                 \r
364                 query.prepare(queryString);\r
365                 \r
366                 query.bindValue(":guid", guid);\r
367                 if (!query.exec()) {\r
368                         logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
369                         logger.log(logger.MEDIUM, query.lastError());\r
370                         return null;\r
371                 }\r
372                 Resource r = null;\r
373                 if (query.next()) {\r
374                                                                         \r
375                         r = new Resource();\r
376                         r.setGuid(query.valueString(0));        // Resource Guid\r
377                         r.setNoteGuid(query.valueString(1));   // note Guid\r
378                         r.setMime(query.valueString(2));       // Mime Type\r
379                         r.setWidth(new Short(query.valueString(3)));  // Width\r
380                         r.setHeight(new Short(query.valueString(4)));  // Height\r
381                         r.setDuration(new Short(query.valueString(5)));  // Duration\r
382                         r.setActive(new Boolean(query.valueString(6)));  // active\r
383                         r.setUpdateSequenceNum(new Integer(query.valueString(7)));  // update sequence number\r
384                         \r
385                         Data d = new Data();\r
386                         byte[] h = query.valueString(8).getBytes();    // data hash\r
387                         QByteArray hData = new QByteArray(h);\r
388                         QByteArray bData = new QByteArray(QByteArray.fromHex(hData));\r
389                         d.setBodyHash(bData.toByteArray());\r
390                         d.setSize(new Integer(query.valueString(9)));\r
391                         r.setData(d);\r
392                         \r
393                         Data rec = new Data();\r
394                         if (query.valueObject(10) != null)\r
395                                 rec.setBodyHash(query.valueString(10).getBytes());   // Recognition Hash\r
396                         if (query.valueObject(11) != null)\r
397                                 rec.setSize(new Integer(query.valueString(11)));\r
398                         else\r
399                                 rec.setSize(0);\r
400                         r.setRecognition(rec);\r
401 \r
402                         ResourceAttributes a = new ResourceAttributes();\r
403                         if (!query.valueString(12).equals(""))              // Latitude\r
404                                 a.setLatitude(new Float(query.valueString(12)));\r
405                         if (!query.valueString(13).equals(""))              // Longitude\r
406                                 a.setLongitude(new Float(query.valueString(13)));\r
407                         if (!query.valueString(14).equals(""))              // Altitude\r
408                                 a.setAltitude(new Float(query.valueString(14)));\r
409                         a.setCameraMake(stringValue(query.valueString(15)));              // Camera Make\r
410                         a.setCameraModel(stringValue(query.valueString(16)));\r
411                         a.setClientWillIndex(booleanValue(query.valueString(17).toString(),false));  // Camera Model\r
412                         a.setRecoType(stringValue(query.valueString(18)));                 // Recognition Type\r
413                         a.setFileName(stringValue(query.valueString(19)));                  // File Name\r
414                         a.setAttachment(booleanValue(query.valueString(20).toString(),false));\r
415                         a.setSourceURL(query.valueString(21));\r
416                         r.setAttributes(a);\r
417                 \r
418                         if (withBinary) {\r
419                             \r
420                                 query.prepare("Select dataBinary from NoteResources where guid=:guid");\r
421                                 query.bindValue(":guid", r.getGuid());\r
422                                 query.exec();\r
423                                 if (query.next()) {\r
424                                         byte[] b = query.getBlob(0);\r
425                                         r.getData().setBody(b);\r
426                                 }\r
427                         } \r
428                 }\r
429                 return r;\r
430         }\r
431         \r
432         \r
433         // Get a note's resourcesby Guid\r
434         public List<Resource> getNoteResources(String noteGuid, boolean withBinary) {\r
435                 if (noteGuid == null)\r
436                         return null;\r
437                 List<Resource> res = new ArrayList<Resource>();\r
438                 \r
439                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
440                 query.prepare("Select guid"\r
441                                 +" from NoteResources where noteGuid = :noteGuid");\r
442                 query.bindValue(":noteGuid", noteGuid);\r
443                 if (!query.exec()) {\r
444                         logger.log(logger.EXTREME, "NoteResources SQL select has failed.");\r
445                         logger.log(logger.MEDIUM, query.lastError());\r
446                         return null;\r
447                 }\r
448                 while (query.next()) {\r
449                         String guid = (query.valueString(0));\r
450                         res.add(getNoteResource(guid, withBinary));\r
451                 }       \r
452                 return res;\r
453         }\r
454         // Get all of a note's recognition data by the note guid\r
455         public List<Resource> getNoteResourcesRecognition(String noteGuid) {\r
456                 if (noteGuid == null)\r
457                         return null;\r
458                 boolean check;\r
459                 List<Resource> res = new ArrayList<Resource>();\r
460                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
461                 check = query.prepare("Select "\r
462                                 +"recognitionHash, recognitionSize, recognitionBinary "\r
463                                 +" from NoteResources where noteGuid=:guid");\r
464                 if (!check) {\r
465                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
466                         logger.log(logger.MEDIUM, query.lastError());\r
467                         return null;\r
468                 }\r
469                 query.bindValue(":guid", noteGuid);\r
470                 if (!check) {\r
471                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
472                         logger.log(logger.MEDIUM, query.lastError());\r
473                         return null;\r
474                 }\r
475                 while (query.next()) {  \r
476                         Resource r = new Resource();            \r
477                         res.add(r);\r
478                         Data rec = new Data();\r
479                         rec.setBodyHash(query.valueString(0).getBytes());\r
480                         String x = new String(query.valueString(1));\r
481                         if (!x.equals("")) {\r
482                                 rec.setSize(new Integer(x));\r
483                                 rec.setBody(query.valueString(2).getBytes());\r
484                         } else\r
485                                 rec.setSize(0);\r
486                         r.setRecognition(rec);\r
487                 }       \r
488                 return res;\r
489         }\r
490         \r
491         \r
492         // Get a note's recognition data by it's guid.\r
493         public Resource getNoteResourceRecognition(String guid) {\r
494                 if (guid == null)\r
495                         return null;\r
496                 boolean check;\r
497                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
498                 check = query.prepare("Select "\r
499                                 +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "\r
500                                 +" from NoteResources where guid=:guid");\r
501                 if (!check) {\r
502                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");\r
503                         logger.log(logger.MEDIUM, query.lastError());\r
504                         return null;\r
505                 }\r
506                 query.bindValue(":guid", guid);\r
507                 query.exec();\r
508                 if (!check) {\r
509                         logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");\r
510                         logger.log(logger.MEDIUM, query.lastError());\r
511                         return null;\r
512                 }\r
513                 Resource r = null;\r
514                 while (query.next()) {\r
515                                                                         \r
516                         r = new Resource();             \r
517                         Data rec = new Data();\r
518                         rec.setBodyHash(query.valueString(0).getBytes());\r
519                         String x = new String(query.valueString(1));\r
520                         if (!x.equals("")) {\r
521                                 rec.setSize(new Integer(x));\r
522                                 rec.setBody(query.valueString(2).getBytes());\r
523                         } else\r
524                                 rec.setSize(0);\r
525                         r.setRecognition(rec);\r
526                         r.setNoteGuid(query.valueString(3));\r
527                 }       \r
528                 return r;\r
529         }\r
530 \r
531         // Save Note Resource\r
532         public void updateNoteResource(Resource r, boolean isDirty) {\r
533                 logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");\r
534                 expungeNoteResource(r.getGuid());\r
535                 saveNoteResource(r, isDirty);\r
536                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource");\r
537         }\r
538         // Update note resource GUID\r
539         public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {\r
540                 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
541                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
542                 query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");\r
543                 query.bindValue(":newGuid", newGuid);\r
544                 query.bindValue(":isDirty", isDirty);\r
545                 query.bindValue(":oldGuid", oldGuid);\r
546                 query.exec();\r
547                 query.exec("commit");\r
548                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
549         }\r
550         // Update note resource GUID\r
551         public void resetUpdateSequenceNumber(String guid, boolean isDirty) {\r
552                 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");\r
553                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
554                 query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");\r
555                 query.bindValue(":isDirty", isDirty);\r
556                 query.bindValue(":guid", guid);\r
557                 query.exec();\r
558                 query.exec("commit");\r
559                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");\r
560         }\r
561         \r
562         // Drop the table\r
563         public void reindexAll() {              \r
564                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
565                 query.exec("Update NoteResources set indexneeded=true");\r
566                 query.exec("commit");\r
567         }\r
568         // Count attachments\r
569         public int getResourceCount() {\r
570         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
571                 query.exec("select count(*) from noteresources");\r
572                 query.next(); \r
573                 int returnValue = new Integer(query.valueString(0));\r
574                 return returnValue;\r
575         }\r
576         //\r
577         // Count unindexed notes\r
578         public int getUnindexedCount() {\r
579         NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
580                 query.exec("select count(*) from noteresources where indexneeded=true");\r
581                 query.next(); \r
582                 int returnValue = new Integer(query.valueString(0));\r
583                 return returnValue;\r
584         }\r
585         \r
586         //********************************************\r
587         //** Utility Functions\r
588         //********************************************\r
589         // Convert a byte array to a hex string\r
590         private static String byteArrayToHexString(byte data[]) {\r
591                 StringBuffer buf = new StringBuffer();\r
592             for (byte element : data) {\r
593                 int halfbyte = (element >>> 4) & 0x0F;\r
594                 int two_halfs = 0;\r
595                 do {\r
596                         if ((0 <= halfbyte) && (halfbyte <= 9))\r
597                                buf.append((char) ('0' + halfbyte));\r
598                            else\r
599                                 buf.append((char) ('a' + (halfbyte - 10)));\r
600                         halfbyte = element & 0x0F;\r
601                 } while(two_halfs++ < 1);\r
602             }\r
603             return buf.toString();              \r
604         }\r
605 \r
606         \r
607         private String stringValue(Object value) {\r
608                 if (value != null && value.toString() != null) \r
609                         return value.toString();\r
610                 else\r
611                         return null;\r
612         }\r
613         \r
614         private  boolean booleanValue(Object value, boolean unknown) {\r
615                 if (value != null && value.toString() != null) {\r
616                         try {\r
617                                 if ((Integer)value > 0)\r
618                                         return true;\r
619                                 else\r
620                                         return false;\r
621                         } catch (java.lang.ClassCastException e) {\r
622                                 try {\r
623                                         String stringValue = (String)value;\r
624                                         if (stringValue.equalsIgnoreCase("true"))\r
625                                                 return true;\r
626                                         else \r
627                                                 return false;\r
628                                 } catch (java.lang.ClassCastException e1) { \r
629                                         return unknown;\r
630                                 }\r
631                         }\r
632                 }\r
633                 else\r
634                         return unknown;\r
635         }\r
636 \r
637         // Update note source url. \r
638         public void updateNoteSourceUrl(String guid, String url, boolean isDirty) {\r
639                 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteSourceUrl()");\r
640                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
641                 query.prepare("update NoteResources set attributesourceurl=:url, isDirty=:isDirty where guid=:guid");\r
642                 query.bindValue(":guid", guid);\r
643                 query.bindValue(":isDirty", isDirty);\r
644                 query.bindValue(":url", url);\r
645                 query.exec();\r
646                 query.exec("commit");\r
647                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteSourceUrl()");\r
648         }\r
649         \r
650         // Get note source\r
651         public String getNoteSourceUrl(String guid) {\r
652                 logger.log(logger.HIGH, "Entering RNoteResourceTable.getNoteSourceUrl()");\r
653                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
654                 query.prepare("Select attributesourceurl from noteresources where guid=:guid");\r
655                 query.bindValue(":guid", guid);\r
656                 query.exec();\r
657                 if (query.next()) {\r
658                         logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl()");\r
659                         return query.valueString(0);\r
660                 }\r
661                 logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl() - no value found");\r
662                 return null;\r
663         }\r
664         \r
665         // Get note source\r
666         public List<String> getDistinctNoteGuids() {\r
667                 logger.log(logger.HIGH, "Entering NoteResourceTable.getDistinctNoteGuids()");\r
668                 List<String> guids = new ArrayList<String>();\r
669                 NSqlQuery query = new NSqlQuery(db.getResourceConnection());\r
670                 query.exec("select distinct noteguid from noteresources");\r
671                 if (query.next()) {\r
672                         guids.add(query.valueString(0));\r
673                 }\r
674                 logger.log(logger.HIGH, "Leaving NoteResourceTable.getDistinctNoteGuids()");\r
675                 return guids;\r
676         }\r
677 }\r