OSDN Git Service

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