OSDN Git Service

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