2 * This file is part of NixNote/NeighborNote
\r
3 * Copyright 2009 Randy Baumgarte
\r
5 * This file may be licensed under the terms of of the
\r
6 * GNU General Public License Version 2 (the ``GPL'').
\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
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
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
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
31 import cx.fbn.nevernote.sql.driver.NSqlQuery;
\r
32 import cx.fbn.nevernote.utilities.ApplicationLogger;
\r
36 public class NoteResourceTable {
\r
40 // private static final long serialVersionUID = 1L;
\r
41 private final ApplicationLogger logger;
\r
42 private final DatabaseConnection db;
\r
45 public NoteResourceTable(ApplicationLogger l, DatabaseConnection d) {
\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
73 public void dropTable() {
\r
74 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
75 query.exec("Drop table NoteResources");
\r
77 // Reset the dirty flag
\r
78 public void resetDirtyFlag(String guid) {
\r
79 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
81 query.prepare("Update noteresources set isdirty=false where guid=:guid");
\r
82 query.bindValue(":guid", guid);
\r
84 logger.log(logger.EXTREME, "Error resetting noteresource dirty field. " +query.lastError());
\r
86 query.exec("commit");
\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
95 logger.log(logger.EXTREME, "Error setting noteresource indexneeded field: " +query.lastError());
\r
97 query.exec("commit");
\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
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
107 // Get a list of the notes
\r
109 while (query.next()) {
\r
110 guid = new String();
\r
111 guid = query.valueString(0);
\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
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
124 // Get a list of the notes
\r
126 while (query.next()) {
\r
127 guid = new String();
\r
128 guid = query.valueString(0);
\r
134 public List<String> getAll() {
\r
135 List<String> guids = new ArrayList<String>();
\r
136 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
138 query.prepare("Select guid from noteresources;");
\r
140 logger.log(logger.EXTREME, "Error getting all note resource guids. " +query.lastError());
\r
142 while (query.next()) {
\r
143 guids.add(query.valueString(0));
\r
148 public List<String> findInkNotes() {
\r
149 List<String> guids = new ArrayList<String>();
\r
150 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
152 query.prepare("Select guid from noteresources where mime='application/vnd.evernote.ink'");
\r
154 logger.log(logger.EXTREME, "Error searching for ink notes. " +query.lastError());
\r
156 while (query.next()) {
\r
157 guids.add(query.valueString(0));
\r
162 public void saveNoteResource(Resource r, boolean isDirty) {
\r
163 logger.log(logger.HIGH, "Entering saveNoteResources: isDirty " +isDirty);
\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
170 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
186 query.bindValue(":guid", r.getGuid());
\r
187 query.bindValue(":noteGuid", r.getNoteGuid());
\r
188 if (r.getData() != null) {
\r
189 query.bindValue(":dataHash", byteArrayToHexString(r.getData().getBodyHash()));
\r
190 // query.bindValue(":dataHash", "c0369123fe9871d675ae456fd056ba33");
\r
191 query.bindValue(":dataSize", r.getData().getSize());
\r
192 query.bindBlob(":dataBody", r.getData().getBody());
\r
194 query.bindValue(":updateSequenceNumber", r.getUpdateSequenceNum());
\r
195 query.bindValue(":mime", r.getMime());
\r
196 query.bindValue(":width", new Integer(r.getWidth()));
\r
197 query.bindValue(":height", new Integer(r.getHeight()));
\r
198 query.bindValue(":duration", new Integer(r.getDuration()));
\r
199 query.bindValue(":active", r.isActive());
\r
200 if (r.getRecognition() != null) {
\r
201 query.bindValue(":recognitionHash", r.getRecognition().getBodyHash());
\r
202 query.bindValue(":recognitionSize", r.getRecognition().getSize());
\r
203 if (r.getRecognition().getBody() != null)
\r
204 query.bindValue(":recognitionBody", new String(r.getRecognition().getBody()));
\r
206 query.bindValue(":recognitionBody", "");
\r
208 query.bindValue(":recognitionHash", "");
\r
209 query.bindValue(":recognitionSize", 0);
\r
210 query.bindValue(":recognitionBody", "");
\r
212 if (r.getAttributes() != null) {
\r
213 query.bindValue(":attributeSourceUrl", r.getAttributes().getSourceURL());
\r
214 StringBuilder ts = new StringBuilder(simple.format(r.getAttributes().getTimestamp()));
\r
215 query.bindValue(":attributeTimestamp", ts.toString());
\r
216 query.bindValue(":attributeLatitude", r.getAttributes().getLatitude());
\r
217 query.bindValue(":attributeLongitude", r.getAttributes().getLongitude());
\r
218 query.bindValue(":attributeAltitude", r.getAttributes().getAltitude());
\r
219 query.bindValue(":attributeCameraMake", r.getAttributes().getCameraMake());
\r
220 query.bindValue(":attributeCameraModel", r.getAttributes().getCameraModel());
\r
221 query.bindValue(":attributeClientWillIndex", r.getAttributes().isClientWillIndex());
\r
222 query.bindValue(":attributeRecoType", r.getAttributes().getRecoType());
\r
223 query.bindValue(":attributeFileName", r.getAttributes().getFileName());
\r
224 query.bindValue(":attributeAttachment", r.getAttributes().isAttachment());
\r
226 query.bindValue(":isDirty", isDirty);
\r
228 check = query.exec();
\r
230 logger.log(logger.MEDIUM, "*** NoteResource Table insert failed.");
\r
231 logger.log(logger.MEDIUM, query.lastError());
\r
233 query.exec("commit");
\r
236 logger.log(logger.HIGH, "Leaving DBRunner.saveNoteResources");
\r
238 // delete an old resource
\r
239 public void expungeNoteResource(String guid) {
\r
240 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
241 query.prepare("delete from NoteResources where guid=:guid");
\r
242 query.bindValue(":guid", guid);
\r
244 query.exec("commit");
\r
246 NSqlQuery query2 = new NSqlQuery(db.getConnection());
\r
247 query2.prepare("Delete from InkImages where guid=:guid");
\r
248 query2.bindValue(":guid", guid);
\r
250 query2.exec("commit");
\r
255 // Get a note resource from the database by it's hash value
\r
256 public String getNoteResourceGuidByHashHex(String noteGuid, String hash) {
\r
257 logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceGuidByHashHex");
\r
260 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
262 check = query.prepare("Select guid from NoteResources " +
\r
263 "where noteGuid=:noteGuid and dataHash=:hash");
\r
265 logger.log(logger.EXTREME, "NoteResource SQL select prepare was successful.");
\r
267 logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");
\r
268 logger.log(logger.MEDIUM, query.lastError());
\r
270 query.bindValue(":noteGuid", noteGuid);
\r
271 query.bindValue(":hash", hash);
\r
273 check = query.exec();
\r
275 logger.log(logger.MEDIUM, "dbRunner.getNoteResourceGuidByHashHex Select failed." +
\r
276 "Note Guid:" +noteGuid+
\r
277 "Data Body Hash:" +hash);
\r
278 logger.log(logger.MEDIUM, query.lastError());
\r
280 if (!query.next()) {
\r
281 logger.log(logger.MEDIUM, "Note Resource not found.");
\r
284 return query.valueString(0);
\r
287 // Get a note resource from the database by it's hash value
\r
288 public Resource getNoteResourceDataBodyByHashHex(String noteGuid, String hash) {
\r
289 logger.log(logger.HIGH, "Entering DBRunner.getNoteResourceDataBodyByHash");
\r
292 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
294 check = query.prepare("Select guid, mime, from NoteResources " +
\r
295 "where noteGuid=:noteGuid and dataHash=:hash");
\r
297 logger.log(logger.EXTREME, "NoteResource SQL select prepare has failed.");
\r
298 logger.log(logger.MEDIUM, query.lastError());
\r
300 query.bindValue(":noteGuid", noteGuid);
\r
301 query.bindValue(":hash", hash);
\r
303 if (!query.exec()) {
\r
304 logger.log(logger.MEDIUM, "NoteResource Select failed." +
\r
305 "Note Guid:" +noteGuid+
\r
306 "Data Body Hash:" +hash);
\r
307 logger.log(logger.MEDIUM, query.lastError());
\r
309 if (!query.next()) {
\r
310 logger.log(logger.MEDIUM, "Note Resource not found.");
\r
314 Resource r = new Resource();
\r
315 r.setGuid(query.valueString(0));
\r
316 r.setMime(query.valueString(1));
\r
318 NSqlQuery binary = new NSqlQuery(db.getResourceConnection());
\r
319 if (!binary.prepare("Select databinary from NoteResources " +
\r
320 "where guid=:guid")) {
\r
321 logger.log(logger.MEDIUM, "Prepare for NoteResources Binary failed");
\r
322 logger.log(logger.MEDIUM, binary.lastError());
\r
325 if (!binary.exec()) {
\r
326 logger.log(logger.MEDIUM, "NoteResources Binary Select failed." +
\r
327 "Note Guid:" +noteGuid+
\r
328 "Data Body Hash:" +hash);
\r
329 logger.log(logger.MEDIUM, binary.lastError());
\r
331 if (!binary.next()) {
\r
332 logger.log(logger.MEDIUM, "Note Resource Binary not found.");
\r
336 Data d = new Data();
\r
338 d.setBody(binary.valueString(0).getBytes());
\r
339 logger.log(logger.HIGH, "Leaving DBRunner.getNoteResourceDataBodyByHash");
\r
344 // Get a note's resourcesby Guid
\r
345 public Resource getNoteResource(String guid, boolean withBinary) {
\r
349 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
350 String queryString;
\r
351 queryString = new String("Select guid, noteGuid, mime, width, height, duration, "
\r
352 +"active, updateSequenceNumber, dataHash, dataSize, "
\r
353 +"recognitionHash, recognitionSize, "
\r
354 +"attributeLatitude, attributeLongitude, attributeAltitude, "
\r
355 +"attributeCameraMake, attributeCameraModel, attributeClientWillIndex, "
\r
356 +"attributeRecoType, attributeFileName, attributeAttachment, attributeSourceUrl "
\r
357 +" from NoteResources where guid=:guid");
\r
360 query.prepare(queryString);
\r
362 query.bindValue(":guid", guid);
\r
363 if (!query.exec()) {
\r
364 logger.log(logger.EXTREME, "NoteResources SQL select has failed.");
\r
365 logger.log(logger.MEDIUM, query.lastError());
\r
369 if (query.next()) {
\r
371 r = new Resource();
\r
372 r.setGuid(query.valueString(0)); // Resource Guid
\r
373 r.setNoteGuid(query.valueString(1)); // note Guid
\r
374 r.setMime(query.valueString(2)); // Mime Type
\r
375 r.setWidth(new Short(query.valueString(3))); // Width
\r
376 r.setHeight(new Short(query.valueString(4))); // Height
\r
377 r.setDuration(new Short(query.valueString(5))); // Duration
\r
378 r.setActive(new Boolean(query.valueString(6))); // active
\r
379 r.setUpdateSequenceNum(new Integer(query.valueString(7))); // update sequence number
\r
381 Data d = new Data();
\r
382 byte[] h = query.valueString(8).getBytes(); // data hash
\r
383 QByteArray hData = new QByteArray(h);
\r
384 QByteArray bData = new QByteArray(QByteArray.fromHex(hData));
\r
385 d.setBodyHash(bData.toByteArray());
\r
386 d.setSize(new Integer(query.valueString(9)));
\r
389 Data rec = new Data();
\r
390 if (query.valueObject(10) != null)
\r
391 rec.setBodyHash(query.valueString(10).getBytes()); // Recognition Hash
\r
392 if (query.valueObject(11) != null)
\r
393 rec.setSize(new Integer(query.valueString(11)));
\r
396 r.setRecognition(rec);
\r
398 ResourceAttributes a = new ResourceAttributes();
\r
399 if (!query.valueString(12).equals("")) // Latitude
\r
400 a.setLatitude(new Float(query.valueString(12)));
\r
401 if (!query.valueString(13).equals("")) // Longitude
\r
402 a.setLongitude(new Float(query.valueString(13)));
\r
403 if (!query.valueString(14).equals("")) // Altitude
\r
404 a.setAltitude(new Float(query.valueString(14)));
\r
405 a.setCameraMake(stringValue(query.valueString(15))); // Camera Make
\r
406 a.setCameraModel(stringValue(query.valueString(16)));
\r
407 a.setClientWillIndex(booleanValue(query.valueString(17).toString(),false)); // Camera Model
\r
408 a.setRecoType(stringValue(query.valueString(18))); // Recognition Type
\r
409 a.setFileName(stringValue(query.valueString(19))); // File Name
\r
410 a.setAttachment(booleanValue(query.valueString(20).toString(),false));
\r
411 a.setSourceURL(query.valueString(21));
\r
412 r.setAttributes(a);
\r
416 query.prepare("Select dataBinary from NoteResources where guid=:guid");
\r
417 query.bindValue(":guid", r.getGuid());
\r
419 if (query.next()) {
\r
420 byte[] b = query.getBlob(0);
\r
421 r.getData().setBody(b);
\r
429 // Get a note's resourcesby Guid
\r
430 public List<Resource> getNoteResources(String noteGuid, boolean withBinary) {
\r
431 if (noteGuid == null)
\r
433 List<Resource> res = new ArrayList<Resource>();
\r
435 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
436 query.prepare("Select guid"
\r
437 +" from NoteResources where noteGuid = :noteGuid");
\r
438 query.bindValue(":noteGuid", noteGuid);
\r
439 if (!query.exec()) {
\r
440 logger.log(logger.EXTREME, "NoteResources SQL select has failed.");
\r
441 logger.log(logger.MEDIUM, query.lastError());
\r
444 while (query.next()) {
\r
445 String guid = (query.valueString(0));
\r
446 res.add(getNoteResource(guid, withBinary));
\r
450 // Get all of a note's recognition data by the note guid
\r
451 public List<Resource> getNoteResourcesRecognition(String noteGuid) {
\r
452 if (noteGuid == null)
\r
455 List<Resource> res = new ArrayList<Resource>();
\r
456 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
457 check = query.prepare("Select "
\r
458 +"recognitionHash, recognitionSize, recognitionBinary "
\r
459 +" from NoteResources where noteGuid=:guid");
\r
461 logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");
\r
462 logger.log(logger.MEDIUM, query.lastError());
\r
465 query.bindValue(":guid", noteGuid);
\r
467 logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");
\r
468 logger.log(logger.MEDIUM, query.lastError());
\r
471 while (query.next()) {
\r
472 Resource r = new Resource();
\r
474 Data rec = new Data();
\r
475 rec.setBodyHash(query.valueString(0).getBytes());
\r
476 String x = new String(query.valueString(1));
\r
477 if (!x.equals("")) {
\r
478 rec.setSize(new Integer(x));
\r
479 rec.setBody(query.valueString(2).getBytes());
\r
482 r.setRecognition(rec);
\r
488 // Get a note's recognition data by it's guid.
\r
489 public Resource getNoteResourceRecognition(String guid) {
\r
493 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
494 check = query.prepare("Select "
\r
495 +"recognitionHash, recognitionSize, recognitionBinary, noteGuid "
\r
496 +" from NoteResources where guid=:guid");
\r
498 logger.log(logger.EXTREME, "NoteTable.getNoteRecognition SQL prepare has failed.");
\r
499 logger.log(logger.MEDIUM, query.lastError());
\r
502 query.bindValue(":guid", guid);
\r
505 logger.log(logger.EXTREME, "NoteTable.getNoteRecognition exec has failed.");
\r
506 logger.log(logger.MEDIUM, query.lastError());
\r
510 while (query.next()) {
\r
512 r = new Resource();
\r
513 Data rec = new Data();
\r
514 rec.setBodyHash(query.valueString(0).getBytes());
\r
515 String x = new String(query.valueString(1));
\r
516 if (!x.equals("")) {
\r
517 rec.setSize(new Integer(x));
\r
518 rec.setBody(query.valueString(2).getBytes());
\r
521 r.setRecognition(rec);
\r
522 r.setNoteGuid(query.valueString(3));
\r
527 // Save Note Resource
\r
528 public void updateNoteResource(Resource r, boolean isDirty) {
\r
529 logger.log(logger.HIGH, "Entering ListManager.updateNoteResource");
\r
530 expungeNoteResource(r.getGuid());
\r
531 saveNoteResource(r, isDirty);
\r
532 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResource");
\r
534 // Update note resource GUID
\r
535 public void updateNoteResourceGuid(String oldGuid, String newGuid, boolean isDirty) {
\r
536 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");
\r
537 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
538 query.prepare("update NoteResources set guid=:newGuid, isDirty=:isDirty where guid=:oldGuid");
\r
539 query.bindValue(":newGuid", newGuid);
\r
540 query.bindValue(":isDirty", isDirty);
\r
541 query.bindValue(":oldGuid", oldGuid);
\r
543 query.exec("commit");
\r
544 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");
\r
546 // Update note resource GUID
\r
547 public void resetUpdateSequenceNumber(String guid, boolean isDirty) {
\r
548 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteResourceGuid");
\r
549 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
550 query.prepare("update NoteResources set updateSequenceNumber=0, isDirty=:isDirty where guid=:guid");
\r
551 query.bindValue(":isDirty", isDirty);
\r
552 query.bindValue(":guid", guid);
\r
554 query.exec("commit");
\r
555 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteResourceGuid");
\r
559 public void reindexAll() {
\r
560 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
561 query.exec("Update NoteResources set indexneeded=true");
\r
562 query.exec("commit");
\r
564 // Count attachments
\r
565 public int getResourceCount() {
\r
566 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
567 query.exec("select count(*) from noteresources");
\r
569 int returnValue = new Integer(query.valueString(0));
\r
570 return returnValue;
\r
573 // Count unindexed notes
\r
574 public int getUnindexedCount() {
\r
575 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
576 query.exec("select count(*) from noteresources where indexneeded=true");
\r
578 int returnValue = new Integer(query.valueString(0));
\r
579 return returnValue;
\r
582 //********************************************
\r
583 //** Utility Functions
\r
584 //********************************************
\r
585 // Convert a byte array to a hex string
\r
586 private static String byteArrayToHexString(byte data[]) {
\r
587 StringBuffer buf = new StringBuffer();
\r
588 for (byte element : data) {
\r
589 int halfbyte = (element >>> 4) & 0x0F;
\r
592 if ((0 <= halfbyte) && (halfbyte <= 9))
\r
593 buf.append((char) ('0' + halfbyte));
\r
595 buf.append((char) ('a' + (halfbyte - 10)));
\r
596 halfbyte = element & 0x0F;
\r
597 } while(two_halfs++ < 1);
\r
599 return buf.toString();
\r
603 private String stringValue(Object value) {
\r
604 if (value != null && value.toString() != null)
\r
605 return value.toString();
\r
610 private boolean booleanValue(Object value, boolean unknown) {
\r
611 if (value != null && value.toString() != null) {
\r
613 if ((Integer)value > 0)
\r
617 } catch (java.lang.ClassCastException e) {
\r
619 String stringValue = (String)value;
\r
620 if (stringValue.equalsIgnoreCase("true"))
\r
624 } catch (java.lang.ClassCastException e1) {
\r
633 // Update note source url.
\r
634 public void updateNoteSourceUrl(String guid, String url, boolean isDirty) {
\r
635 logger.log(logger.HIGH, "Entering RNoteResourceTable.updateNoteSourceUrl()");
\r
636 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
637 query.prepare("update NoteResources set attributesourceurl=:url, isDirty=:isDirty where guid=:guid");
\r
638 query.bindValue(":guid", guid);
\r
639 query.bindValue(":isDirty", isDirty);
\r
640 query.bindValue(":url", url);
\r
642 query.exec("commit");
\r
643 logger.log(logger.HIGH, "Leaving RNoteResourceTable.updateNoteSourceUrl()");
\r
647 public String getNoteSourceUrl(String guid) {
\r
648 logger.log(logger.HIGH, "Entering RNoteResourceTable.getNoteSourceUrl()");
\r
649 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
650 query.prepare("Select attributesourceurl from noteresources where guid=:guid");
\r
651 query.bindValue(":guid", guid);
\r
653 if (query.next()) {
\r
654 logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl()");
\r
655 return query.valueString(0);
\r
657 logger.log(logger.HIGH, "Leaving RNoteResourceTable.getNoteSourceUrl() - no value found");
\r
662 public List<String> getDistinctNoteGuids() {
\r
663 logger.log(logger.HIGH, "Entering NoteResourceTable.getDistinctNoteGuids()");
\r
664 List<String> guids = new ArrayList<String>();
\r
665 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
666 query.exec("select distinct noteguid from noteresources");
\r
667 if (query.next()) {
\r
668 guids.add(query.valueString(0));
\r
670 logger.log(logger.HIGH, "Leaving NoteResourceTable.getDistinctNoteGuids()");
\r
674 public void resetAllDirty() {
\r
675 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
676 query.exec("update noteresources set isdirty=false");
\r
680 public void updateResourceText(String guid, String text) {
\r
681 logger.log(logger.HIGH, "Entering NoteResourceTable.updateResourceText");
\r
682 NSqlQuery query = new NSqlQuery(db.getResourceConnection());
\r
683 boolean check = query.prepare("Update noteResources set resourceText=:resourceText where guid=:guid");
\r
685 logger.log(logger.EXTREME, "Update resourceText sql prepare has failed.");
\r
686 logger.log(logger.MEDIUM, query.lastError());
\r
689 query.bindValue(":resourceText", text);
\r
690 query.bindValue(":guid", guid);
\r
692 check = query.exec();
\r
694 logger.log(logger.EXTREME, "Update resourceText has failed.");
\r
695 logger.log(logger.MEDIUM, query.lastError());
\r
697 logger.log(logger.HIGH, "Leaving NoteResourceTable.updateResourceText");
\r