OSDN Git Service

There are multiple changes with this commit.
[neighbornote/NeighborNote.git] / src / cx / fbn / nevernote / sql / NoteTable.java
1 /*\r
2  * This file is part of NeverNote \r
3  * Copyright 2009 Randy Baumgarte\r
4  * \r
5  * This file may be licensed under the terms of of the\r
6  * GNU General Public License Version 2 (the ``GPL'').\r
7  *\r
8  * Software distributed under the License is distributed\r
9  * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either\r
10  * express or implied. See the GPL for the specific language\r
11  * governing rights and limitations.\r
12  *\r
13  * You should have received a copy of the GPL along with this\r
14  * program. If not, go to http://www.gnu.org/licenses/gpl.html\r
15  * or write to the Free Software Foundation, Inc.,\r
16  * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.\r
17  *\r
18 */\r
19 \r
20 \r
21 package cx.fbn.nevernote.sql;\r
22 \r
23 import java.text.DateFormat;\r
24 import java.text.ParseException;\r
25 import java.text.SimpleDateFormat;\r
26 import java.util.ArrayList;\r
27 import java.util.List;\r
28 \r
29 import com.evernote.edam.type.Note;\r
30 import com.evernote.edam.type.NoteAttributes;\r
31 import com.evernote.edam.type.Resource;\r
32 import com.evernote.edam.type.Tag;\r
33 import com.trolltech.qt.core.QByteArray;\r
34 import com.trolltech.qt.core.QDateTime;\r
35 import com.trolltech.qt.core.QTextCodec;\r
36 \r
37 import cx.fbn.nevernote.Global;\r
38 import cx.fbn.nevernote.evernote.EnmlConverter;\r
39 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
40 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
41 import cx.fbn.nevernote.utilities.Pair;\r
42 \r
43 public class NoteTable {\r
44         private final ApplicationLogger                 logger;\r
45         public final NoteTagsTable                              noteTagsTable;\r
46         public NoteResourceTable                                noteResourceTable;\r
47         private final DatabaseConnection                db;\r
48         int id;\r
49 \r
50         // Prepared Queries to improve speed\r
51         private NSqlQuery                                               getQueryWithContent;\r
52         private NSqlQuery                                               getQueryWithoutContent;\r
53         private NSqlQuery                                               getAllQueryWithoutContent;\r
54         \r
55         // Constructor\r
56         public NoteTable(ApplicationLogger l, DatabaseConnection d) {\r
57                 logger = l;\r
58                 db = d;\r
59                 id = 0;\r
60                 noteResourceTable = new NoteResourceTable(logger, db);\r
61                 noteTagsTable = new NoteTagsTable(logger, db);\r
62                 getQueryWithContent = null;\r
63                 getQueryWithoutContent = null;\r
64                 \r
65         }\r
66         // Create the table\r
67         public void createTable() {\r
68                 getQueryWithContent = new NSqlQuery(db.getConnection());\r
69                 getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
70                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
71         logger.log(logger.HIGH, "Creating table Note...");\r
72         if (!query.exec("Create table Note (guid varchar primary key, " +\r
73                         "updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+\r
74                         "contentLength integer, created timestamp, updated timestamp, deleted timestamp, " \r
75                         +"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+\r
76                         "attributeLatitude double, attributeLongitude double, attributeAltitude double,"+\r
77                         "attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+\r
78                         "attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +\r
79                         "isDirty boolean)"))                    \r
80                 logger.log(logger.HIGH, "Table Note creation FAILED!!!");    \r
81         if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))\r
82                 logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");\r
83         if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);"))\r
84                 logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!");  \r
85         noteTagsTable.createTable();\r
86         noteResourceTable.createTable();     \r
87         }\r
88         // Drop the table\r
89         public void dropTable() {\r
90                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
91                 query.exec("Drop table Note");\r
92                 noteTagsTable.dropTable();\r
93                 noteResourceTable.dropTable();\r
94         }\r
95         // Save Note List from Evernote \r
96         public void addNote(Note n, boolean isDirty) {\r
97                 logger.log(logger.EXTREME, "Inside addNote");\r
98                 if (n == null)\r
99                         return;\r
100                 \r
101                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
102 \r
103                 NSqlQuery query = new NSqlQuery(db.getConnection());                    \r
104                 query.prepare("Insert Into Note ("\r
105                                 +"guid, updateSequenceNumber, title, content, "\r
106                                 +"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "\r
107                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
108                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
109                                 +"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded" \r
110                                 +") Values("\r
111                                 +":guid, :updateSequenceNumber, :title, :content, "\r
112                                 +":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "\r
113                                 +":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "\r
114                                 +":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "\r
115                                 +":indexNeeded, :isExpunged, :isDirty, -1, true) ");\r
116 \r
117                 StringBuilder created = new StringBuilder(simple.format(n.getCreated()));                       \r
118                 StringBuilder updated = new StringBuilder(simple.format(n.getUpdated()));                       \r
119                 StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));\r
120 \r
121                 EnmlConverter enml = new EnmlConverter(logger);\r
122                 \r
123                 query.bindValue(":guid", n.getGuid());\r
124                 query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());\r
125                 query.bindValue(":title", n.getTitle());\r
126                 query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));\r
127                 query.bindValue(":contentHash", n.getContentHash());\r
128                 query.bindValue(":contentLength", n.getContentLength());\r
129                 query.bindValue(":created", created.toString());\r
130                 query.bindValue(":updated", updated.toString());\r
131                 query.bindValue(":deleted", deleted.toString());\r
132                 query.bindValue(":active", n.isActive());\r
133                 query.bindValue(":notebookGuid", n.getNotebookGuid());\r
134                 \r
135                 if (n.getAttributes() != null) {\r
136                         created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));\r
137                         query.bindValue(":attributeSubjectDate", created.toString());\r
138                         query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());\r
139                         query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());\r
140                         query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());\r
141                         query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());\r
142                         query.bindValue(":attributeSource", n.getAttributes().getSource());\r
143                         query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL());\r
144                         query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication());\r
145                 }\r
146                 query.bindValue(":indexNeeded", true);\r
147                 query.bindValue(":isExpunged", false);\r
148                 query.bindValue(":isDirty", isDirty);\r
149 \r
150                 \r
151                 if (!query.exec())\r
152                         logger.log(logger.MEDIUM, query.lastError());\r
153                 \r
154                 // Save the note tags\r
155                 if (n.getTagGuids() != null) {\r
156                         for (int i=0; i<n.getTagGuids().size(); i++) \r
157                                 noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i));\r
158                 }\r
159                 logger.log(logger.EXTREME, "Leaving addNote");\r
160         } \r
161         // Setup queries for get to save time later\r
162         private void prepareQueries() {\r
163                 getQueryWithContent = new NSqlQuery(db.getConnection());\r
164                 getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
165                 getAllQueryWithoutContent = new NSqlQuery(db.getConnection());\r
166                 \r
167                 if (!getQueryWithContent.prepare("Select "\r
168                                 +"guid, updateSequenceNumber, title, "\r
169                                 +"created, updated, deleted, active, notebookGuid, "\r
170                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
171                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
172                                 +"content, contentHash, contentLength"\r
173                                 +" from Note where guid=:guid and isExpunged=false")) {\r
174                                         logger.log(logger.EXTREME, "Note SQL select prepare with content has failed.");\r
175                                         logger.log(logger.MEDIUM, getQueryWithContent.lastError());\r
176                 }\r
177                 \r
178                 if (!getQueryWithoutContent.prepare("Select "\r
179                                 +"guid, updateSequenceNumber, title, "\r
180                                 +"created, updated, deleted, active, notebookGuid, "\r
181                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
182                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
183                                 +" from Note where guid=:guid and isExpunged=false")) {\r
184                                         logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
185                                         logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
186                 }\r
187                 if (!getAllQueryWithoutContent.prepare("Select "\r
188                                 +"guid, updateSequenceNumber, title, "\r
189                                 +"created, updated, deleted, active, notebookGuid, "\r
190                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
191                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
192                                 +" from Note where isExpunged = false")) {\r
193                                         logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
194                                         logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
195                 }\r
196         }\r
197 \r
198         // Get a note's content in raw, binary format for the sync.\r
199         public String getNoteContentBinary(String guid) {\r
200                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
201                 query.prepare("Select content from note where guid=:guid");\r
202                 query.bindValue(":guid", guid);\r
203                 query.exec();           \r
204                 query.next();\r
205                 return query.valueString(0);\r
206         }\r
207         // Get a note by Guid\r
208         public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
209                 if (noteGuid == null)\r
210                         return null;\r
211                 if (noteGuid.trim().equals(""))\r
212                         return null;\r
213 \r
214                 prepareQueries();\r
215                 NSqlQuery query;\r
216                 if (loadContent) {\r
217                         query = getQueryWithContent;\r
218                 } else {\r
219                         query = getQueryWithoutContent;\r
220                 }\r
221                 \r
222                 query.bindValue(":guid", noteGuid);\r
223                 if (!query.exec()) {\r
224                         logger.log(logger.EXTREME, "Note SQL select exec has failed.");\r
225                         logger.log(logger.MEDIUM, query.lastError());\r
226                         return null;\r
227                 }\r
228                 if (!query.next()) {\r
229                         logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");\r
230                         logger.log(logger.EXTREME, " -> " +query.lastError().toString());\r
231                         logger.log(logger.EXTREME, " -> " +query.lastError());\r
232                         return null;\r
233                 }\r
234                 Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags);\r
235                 n.setContent(fixCarriageReturn(n.getContent()));\r
236                 return n;\r
237         }\r
238         // Get a note by Guid\r
239         public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
240                 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
241 //              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
242 \r
243                 \r
244                 Note n = new Note();\r
245                 NoteAttributes na = new NoteAttributes();\r
246                 n.setAttributes(na);\r
247                 \r
248                 n.setGuid(query.valueString(0));\r
249                 n.setUpdateSequenceNum(new Integer(query.valueString(1)));\r
250                 n.setTitle(query.valueString(2));\r
251 \r
252                 try {\r
253                         n.setCreated(indfm.parse(query.valueString(3)).getTime());\r
254                         n.setUpdated(indfm.parse(query.valueString(4)).getTime());\r
255                         n.setDeleted(indfm.parse(query.valueString(5)).getTime());\r
256                 } catch (ParseException e) {\r
257                         e.printStackTrace();\r
258                 }\r
259 \r
260                 n.setActive(query.valueBoolean(6,true));\r
261                 n.setNotebookGuid(query.valueString(7));\r
262                 \r
263                 try {\r
264                         String attributeSubjectDate = query.valueString(8);\r
265                         if (!attributeSubjectDate.equals(""))\r
266                                 na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());\r
267                 } catch (ParseException e) {\r
268                         e.printStackTrace();\r
269                 }\r
270                 na.setLatitude(new Float(query.valueString(9)));\r
271                 na.setLongitude(new Float(query.valueString(10)));\r
272                 na.setAltitude(new Float(query.valueString(11)));\r
273                 na.setAuthor(query.valueString(12));\r
274                 na.setSource(query.valueString(13));\r
275                 na.setSourceURL(query.valueString(14));\r
276                 na.setSourceApplication(query.valueString(15));\r
277                 \r
278                 if (loadTags) {\r
279                         n.setTagGuids(noteTagsTable.getNoteTags(n.getGuid()));\r
280                         List<String> tagNames = new ArrayList<String>();\r
281                         TagTable tagTable = new TagTable(logger, db);\r
282                         for (int i=0; i<n.getTagGuids().size(); i++) {\r
283                                 String currentGuid = n.getTagGuids().get(i);\r
284                                 Tag tag = tagTable.getTag(currentGuid);\r
285                                 tagNames.add(tag.getName());\r
286                         }\r
287                         n.setTagNames(tagNames);\r
288                 }\r
289                 \r
290                 if (loadContent) {\r
291                                                 \r
292                         QTextCodec codec = QTextCodec.codecForLocale();\r
293                         codec = QTextCodec.codecForName("UTF-8");\r
294                 String unicode =  codec.fromUnicode(query.valueString(16)).toString();\r
295                         n.setContent(unicode);\r
296 //                      n.setContent(query.valueString(16).toString());\r
297                         \r
298                         String contentHash = query.valueString(17);\r
299                         if (contentHash != null)\r
300                                 n.setContentHash(contentHash.getBytes());\r
301                         n.setContentLength(new Integer(query.valueString(18)));\r
302                 }\r
303                 if (loadResources)\r
304                         n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));\r
305                 if (loadRecognition) {\r
306                         if (n.getResources() == null) {\r
307                                 List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());\r
308                                 n.setResources(resources);\r
309                         } else {\r
310                                 // We need to merge the recognition resources with the note resources retrieved earlier\r
311                                 for (int i=0; i<n.getResources().size(); i++) {\r
312                                         Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());\r
313                                         n.getResources().get(i).setRecognition(r.getRecognition());\r
314                                 }\r
315                         }\r
316                 }\r
317                 n.setContent(fixCarriageReturn(n.getContent()));\r
318                 return n;\r
319         }\r
320         // Update a note's title\r
321         public void updateNoteTitle(String guid, String title) {\r
322                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
323                 boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");\r
324                 if (!check) {\r
325                         logger.log(logger.EXTREME, "Update note title sql prepare has failed.");\r
326                         logger.log(logger.MEDIUM, query.lastError());\r
327                 }\r
328                 query.bindValue(":title", title);\r
329                 query.bindValue(":guid", guid);\r
330                 check = query.exec();\r
331                 if (!check) {\r
332                         logger.log(logger.EXTREME, "Update note title has failed.");\r
333                         logger.log(logger.MEDIUM, query.lastError());\r
334                 }\r
335         }\r
336         // Update a note's creation date\r
337         public void updateNoteCreatedDate(String guid, QDateTime date) {\r
338                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
339                 boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");\r
340                 if (!check) {\r
341                         logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");\r
342                         logger.log(logger.MEDIUM, query.lastError());\r
343                 }\r
344                 \r
345                 query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));\r
346                 query.bindValue(":guid", guid);\r
347                 \r
348                 check = query.exec();\r
349                 if (!check) {\r
350                         logger.log(logger.EXTREME, "Update note creation date has failed.");\r
351                         logger.log(logger.MEDIUM, query.lastError());\r
352                 }\r
353         }\r
354         // Update a note's creation date\r
355         public void updateNoteAlteredDate(String guid, QDateTime date) {\r
356                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
357                 boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");\r
358                 if (!check) {\r
359                         logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");\r
360                         logger.log(logger.MEDIUM, query.lastError());\r
361                 }\r
362                 \r
363                 query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
364                 query.bindValue(":guid", guid);\r
365                 \r
366                 check = query.exec();\r
367                 if (!check) {\r
368                         logger.log(logger.EXTREME, "Update note altered date has failed.");\r
369                         logger.log(logger.MEDIUM, query.lastError());\r
370                 }\r
371         }\r
372         // Update a note's creation date\r
373         public void updateNoteSubjectDate(String guid, QDateTime date) {\r
374                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
375                 boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");\r
376                 if (!check) {\r
377                         logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");\r
378                         logger.log(logger.MEDIUM, query.lastError());\r
379                 }\r
380         \r
381                 query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
382                 query.bindValue(":guid", guid);\r
383                 \r
384                 check = query.exec();\r
385                 if (!check) {\r
386                         logger.log(logger.EXTREME, "Update note subject date date has failed.");\r
387                         logger.log(logger.MEDIUM, query.lastError());\r
388                 }\r
389         }\r
390         // Update a note's creation date\r
391         public void updateNoteAuthor(String guid, String author) {\r
392                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
393                 boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");\r
394                 if (!check) {\r
395                         logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
396                         logger.log(logger.MEDIUM, query.lastError());\r
397                 }\r
398 \r
399                 query.bindValue(":author", author);\r
400                 query.bindValue(":guid", guid);\r
401 \r
402                 check = query.exec();\r
403                 if (!check) {\r
404                         logger.log(logger.EXTREME, "Update note author has failed.");\r
405                         logger.log(logger.MEDIUM, query.lastError());\r
406                 }\r
407                 \r
408         }\r
409         // Update a note's geo tags\r
410         public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {\r
411                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
412                 boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+\r
413                                 "attributeLatitude=:latitude, attributeAltitude=:altitude, isDirty=true where guid=:guid");\r
414                 if (!check) {\r
415                         logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
416                         logger.log(logger.MEDIUM, query.lastError());\r
417                 }\r
418 \r
419                 query.bindValue(":longitude", lon);\r
420                 query.bindValue(":latitude", lat);\r
421                 query.bindValue(":altitude", alt);\r
422                 query.bindValue(":guid", guid);\r
423 \r
424                 check = query.exec();\r
425                 if (!check) {\r
426                         logger.log(logger.EXTREME, "Update note geo tag has failed.");\r
427                         logger.log(logger.MEDIUM, query.lastError());\r
428                 }\r
429                 \r
430         }\r
431         // Update a note's creation date\r
432         public void updateNoteSourceUrl(String guid, String url) {\r
433                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
434                 boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");\r
435                 if (!check) {\r
436                         logger.log(logger.EXTREME, "Update note url sql prepare has failed.");\r
437                         logger.log(logger.MEDIUM, query.lastError());\r
438                 }\r
439                 \r
440                 query.bindValue(":url", url);\r
441                 query.bindValue(":guid", guid);\r
442 \r
443                 check = query.exec();\r
444                 if (!check) {\r
445                         logger.log(logger.EXTREME, "Update note url has failed.");\r
446                         logger.log(logger.MEDIUM, query.lastError());\r
447                 }\r
448                 \r
449         }\r
450         // Update the notebook that a note is assigned to\r
451         public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {\r
452                 String currentNotebookGuid = new String("");\r
453                 \r
454                 \r
455                 // If we are going from a synchronized notebook to a local notebook, we\r
456                 // need to tell Evernote to purge the note online.  However, if this is  \r
457                 // conflicting change we move it to the local notebook without deleting it \r
458                 // or it would then delete the copy on the remote server.\r
459                 NotebookTable notebookTable = new NotebookTable(logger, db);\r
460                 DeletedTable deletedTable = new DeletedTable(logger, db);\r
461                 if (expungeFromRemote) {\r
462                         if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {\r
463                                 deletedTable.addDeletedItem(guid, "NOTE");\r
464                         }\r
465                 }\r
466                 \r
467                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
468                 boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");\r
469                 if (!check) {\r
470                         logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");\r
471                         logger.log(logger.MEDIUM, query.lastError());\r
472                 }\r
473                 query.bindValue(":notebook", notebookGuid);\r
474                 query.bindValue(":guid", guid);\r
475                 \r
476                 check = query.exec();\r
477                 if (!check) {\r
478                         logger.log(logger.EXTREME, "Update note notebook has failed.");\r
479                         logger.log(logger.MEDIUM, query.lastError());\r
480                 };\r
481         }\r
482         // Update a note's title\r
483         public void updateNoteContent(String guid, String content) {\r
484                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
485                 boolean check = query.prepare("Update Note set content=:content, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true " +\r
486                                 " where guid=:guid");\r
487                 if (!check) {\r
488                         logger.log(logger.EXTREME, "Update note content sql prepare has failed.");\r
489                         logger.log(logger.MEDIUM, query.lastError());\r
490                 }\r
491                 \r
492                 query.bindValue(":content", content);\r
493                 query.bindValue(":guid", guid);\r
494 \r
495                 check = query.exec();\r
496                 if (!check) {\r
497                         logger.log(logger.EXTREME, "Update note content has failed.");\r
498                         logger.log(logger.MEDIUM, query.lastError());\r
499                 }\r
500         }\r
501 \r
502         \r
503         // Check a note to see if it passes the attribute selection criteria\r
504         public boolean checkAttributeSelection(Note n) {\r
505                 if (Global.createdSinceFilter.check(n) &&\r
506                         Global.createdBeforeFilter.check(n) && \r
507                         Global.changedSinceFilter.check(n) &&\r
508                         Global.changedBeforeFilter.check(n) )\r
509                                 return true;\r
510                 \r
511                 return false;\r
512         }\r
513         // Delete a note\r
514         public void deleteNote(String guid) {\r
515         NSqlQuery query = new NSqlQuery(db.getConnection());\r
516         query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");\r
517                 query.bindValue(":guid", guid);\r
518                 if (!query.exec()) {\r
519                         logger.log(logger.MEDIUM, "Note delete failed.");\r
520                         logger.log(logger.MEDIUM, query.lastError());\r
521                 }\r
522         }\r
523         public void restoreNote(String guid) {\r
524         NSqlQuery query = new NSqlQuery(db.getConnection());\r
525                 query.prepare("Update Note set deleted='1969-12-31 19.00.00', active=true, isDirty=true where guid=:guid");\r
526 //              query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");\r
527                 query.bindValue(":guid", guid);\r
528                 if (!query.exec()) {\r
529                         logger.log(logger.MEDIUM, "Note restore failed.");\r
530                         logger.log(logger.MEDIUM, query.lastError());\r
531                 }\r
532         }\r
533         // Purge a note (actually delete it instead of just marking it deleted)\r
534         public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {\r
535                 \r
536                 if (!permanentExpunge) {\r
537                         hideExpungedNote(guid, needsSync);\r
538                         return;\r
539                 }\r
540                 \r
541                 \r
542         NSqlQuery note = new NSqlQuery(db.getConnection());\r
543         NSqlQuery resources = new NSqlQuery(db.getConnection());\r
544         NSqlQuery tags = new NSqlQuery(db.getConnection());\r
545         NSqlQuery words = new NSqlQuery(db.getConnection());\r
546         \r
547         note.prepare("Delete from Note where guid=:guid");\r
548                 resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
549                 tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
550                 words.prepare("Delete from words where guid=:guid");\r
551 \r
552                 note.bindValue(":guid", guid);\r
553                 resources.bindValue(":guid", guid);\r
554                 tags.bindValue(":guid", guid);\r
555                 words.bindValue(":guid", guid);\r
556         \r
557                 // Start purging notes.\r
558                 if (!note.exec()) {\r
559                         logger.log(logger.MEDIUM, "Purge from note failed.");\r
560                         logger.log(logger.MEDIUM, note.lastError());\r
561                 }\r
562                 if (!resources.exec()) {\r
563                                 logger.log(logger.MEDIUM, "Purge from resources failed.");\r
564                         logger.log(logger.MEDIUM, resources.lastError());\r
565                 }\r
566                 if (!tags.exec()) {\r
567                         logger.log(logger.MEDIUM, "Note tags delete failed.");\r
568                         logger.log(logger.MEDIUM, tags.lastError());\r
569                 }\r
570                 if (!words.exec()) {\r
571                         logger.log(logger.MEDIUM, "Word delete failed.");\r
572                         logger.log(logger.MEDIUM, words.lastError());\r
573                 }\r
574                 if (needsSync) {\r
575                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
576                         deletedTable.addDeletedItem(guid, "Note");\r
577                 }\r
578 \r
579         }\r
580         // Purge a note (actually delete it instead of just marking it deleted)\r
581         public void hideExpungedNote(String guid, boolean needsSync) {\r
582         NSqlQuery note = new NSqlQuery(db.getConnection());\r
583         NSqlQuery resources = new NSqlQuery(db.getConnection());\r
584         NSqlQuery tags = new NSqlQuery(db.getConnection());\r
585         NSqlQuery words = new NSqlQuery(db.getConnection());\r
586         \r
587         note.prepare("Update Note set isExpunged=true where guid=:guid");\r
588                 resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
589                 tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
590                 words.prepare("Delete from words where guid=:guid");\r
591 \r
592                 note.bindValue(":guid", guid);\r
593                 resources.bindValue(":guid", guid);\r
594                 tags.bindValue(":guid", guid);\r
595                 words.bindValue(":guid", guid);\r
596 \r
597                 // Start purging notes.\r
598                 if (!note.exec()) {\r
599                         logger.log(logger.MEDIUM, "Purge from note failed.");\r
600                         logger.log(logger.MEDIUM, note.lastError());\r
601                 }\r
602                 if (!resources.exec()) {\r
603                                 logger.log(logger.MEDIUM, "Purge from resources failed.");\r
604                         logger.log(logger.MEDIUM, resources.lastError());\r
605                 }\r
606                 if (!tags.exec()) {\r
607                         logger.log(logger.MEDIUM, "Note tags delete failed.");\r
608                         logger.log(logger.MEDIUM, tags.lastError());\r
609                 }\r
610                 if (!words.exec()) {\r
611                         logger.log(logger.MEDIUM, "Word delete failed.");\r
612                         logger.log(logger.MEDIUM, words.lastError());\r
613                 }\r
614                 if (needsSync) {\r
615                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
616                         deletedTable.addDeletedItem(guid, "Note");\r
617                 }\r
618         }\r
619 \r
620                 \r
621         // Purge all deleted notes;\r
622         public void expungeAllDeletedNotes() {\r
623                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
624                 query.exec("select guid, updateSequenceNumber from note where active = false");\r
625                 while (query.next()) {\r
626                         String guid = query.valueString(0);\r
627                         Integer usn = new Integer(query.valueString(1));\r
628                         if (usn == 0)\r
629                                 expungeNote(guid, true, false);\r
630                         else\r
631                                 expungeNote(guid, false, true);\r
632                 }\r
633         }\r
634         // Update the note sequence number\r
635         public void updateNoteSequence(String guid, int sequence) {\r
636                 boolean check;\r
637         NSqlQuery query = new NSqlQuery(db.getConnection());\r
638                 check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");\r
639 \r
640                 query.bindValue(":sequence", sequence);\r
641                 query.bindValue(":guid", guid);\r
642                 \r
643                 query.exec();\r
644                 if (!check) {\r
645                         logger.log(logger.MEDIUM, "Note sequence update failed.");\r
646                         logger.log(logger.MEDIUM, query.lastError());\r
647                 } \r
648         }\r
649         // Update the note Guid\r
650         public void updateNoteGuid(String oldGuid, String newGuid) {\r
651                 boolean check;\r
652         NSqlQuery query = new NSqlQuery(db.getConnection());\r
653                 query.prepare("Update Note set guid=:newGuid where guid=:oldGuid");\r
654 \r
655                 query.bindValue(":newGuid", newGuid);\r
656                 query.bindValue(":oldGuid", oldGuid);\r
657 \r
658                 check = query.exec();\r
659                 if (!check) {\r
660                         logger.log(logger.MEDIUM, "Note Guid update failed.");\r
661                         logger.log(logger.MEDIUM, query.lastError());\r
662                 } \r
663                 \r
664                 query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");\r
665                 query.bindValue(":newGuid", newGuid);\r
666                 query.bindValue(":oldGuid", oldGuid);\r
667                 check = query.exec();\r
668                 if (!check) {\r
669                         logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");\r
670                         logger.log(logger.MEDIUM, query.lastError());\r
671                 }\r
672                 \r
673                 query.prepare("Update words set guid=:newGuid where guid=:oldGuid");\r
674                 query.bindValue(":newGuid", newGuid);\r
675                 query.bindValue(":oldGuid", oldGuid);\r
676                 query.exec();\r
677                 if (!check) {\r
678                         logger.log(logger.MEDIUM, "Note guid update failed for Words.");\r
679                         logger.log(logger.MEDIUM, query.lastError());\r
680                 }\r
681                 query.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");\r
682                 query.bindValue(":newGuid", newGuid);\r
683                 query.bindValue(":oldGuid", oldGuid);\r
684                 query.exec();\r
685                 if (!check) {\r
686                         logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");\r
687                         logger.log(logger.MEDIUM, query.lastError());\r
688                 }\r
689         }\r
690         // Update a note\r
691         public void updateNote(Note n, boolean isNew) {\r
692                 boolean isExpunged = isNoteExpunged(n.getGuid());\r
693                 \r
694                 expungeNote(n.getGuid(), !isExpunged, false);\r
695                 addNote(n, false);\r
696         }\r
697         // Does a note exist?\r
698         public boolean exists(String guid) {\r
699                 if (guid == null)\r
700                         return false;\r
701                 if (guid.trim().equals(""))\r
702                         return false;\r
703                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
704                 query.prepare("Select guid from note where guid=:guid");\r
705                 query.bindValue(":guid", guid);\r
706                 if (!query.exec())\r
707                         logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");\r
708                 boolean retVal = query.next();\r
709                 return retVal;\r
710         }\r
711         // Does a note exist?\r
712         public boolean isNoteExpunged(String guid) {\r
713                 if (guid == null)\r
714                         return false;\r
715                 if (guid.trim().equals(""))\r
716                         return false;\r
717                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
718                 query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");\r
719                 query.bindValue(":guid", guid);\r
720                 if (!query.exec())\r
721                         logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");\r
722                 boolean retVal = query.next();\r
723                 return retVal;\r
724         }\r
725         // This is a convience method to check if a tag exists & update/create based upon it\r
726         public void syncNote(Note tag, boolean isDirty) {\r
727                 if (exists(tag.getGuid()))\r
728                         updateNote(tag, isDirty);\r
729                 else\r
730                         addNote(tag, isDirty);\r
731         }\r
732         // Get a list of notes that need to be updated\r
733         public List <Note> getDirty() {\r
734                 String guid;\r
735                 Note tempNote;\r
736                 List<Note> notes = new ArrayList<Note>();\r
737                 List<String> index = new ArrayList<String>();\r
738                 \r
739                 boolean check;                  \r
740         NSqlQuery query = new NSqlQuery(db.getConnection());\r
741                                         \r
742                 check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true)");\r
743                 if (!check) \r
744                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
745                 \r
746                 // Get a list of the notes\r
747                 while (query.next()) {\r
748                         guid = new String();\r
749                         guid = query.valueString(0);\r
750                         index.add(guid); \r
751                 }       \r
752                 \r
753                 // Start getting notes\r
754                 for (int i=0; i<index.size(); i++) {\r
755                         tempNote = getNote(index.get(i), true,true,false,true,true);\r
756                         notes.add(tempNote);\r
757                 }\r
758                 return notes;   \r
759         }\r
760         // Get a list of notes that need to be updated\r
761         public boolean isNoteDirty(String guid) {\r
762                 \r
763                 boolean check;                  \r
764         NSqlQuery query = new NSqlQuery(db.getConnection());\r
765                                         \r
766                 check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");\r
767                 query.bindValue(":guid", guid);\r
768                 check = query.exec();\r
769                 if (!check) \r
770                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
771                 \r
772                 boolean returnValue;\r
773                 // Get a list of the notes\r
774                 if (query.next()) \r
775                         returnValue = true; \r
776                 else\r
777                         returnValue = false;\r
778 \r
779                 return returnValue;     \r
780         }\r
781         // Get a list of notes that need to be updated\r
782         public List <String> getUnsynchronizedGUIDs() {\r
783                 String guid;\r
784                 List<String> index = new ArrayList<String>();\r
785                 \r
786                 boolean check;                  \r
787         NSqlQuery query = new NSqlQuery(db.getConnection());\r
788                                         \r
789                 check = query.exec("Select guid from Note where isDirty = true");\r
790                 if (!check) \r
791                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
792                 \r
793                 // Get a list of the notes\r
794                 while (query.next()) {\r
795                         guid = new String();\r
796                         guid = query.valueString(0);\r
797                         index.add(guid); \r
798                 }       \r
799                 return index;   \r
800         }\r
801         // Reset the dirty bit\r
802         public void  resetDirtyFlag(String guid) {\r
803                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
804                 \r
805                 query.prepare("Update note set isdirty=false where guid=:guid");\r
806                 query.bindValue(":guid", guid);\r
807                 if (!query.exec())\r
808                         logger.log(logger.EXTREME, "Error resetting note dirty field.");\r
809         }\r
810         // Get all notes\r
811         public List<String> getAllGuids() {\r
812                 List<String> notes = new ArrayList<String>();\r
813                 \r
814                 boolean check;                                  \r
815         NSqlQuery query = new NSqlQuery(db.getConnection());\r
816                                         \r
817                 check = query.exec("Select guid from Note");\r
818                 if (!check)\r
819                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
820 \r
821                 // Get a list of the notes\r
822                 while (query.next()) {\r
823                         notes.add(new String(query.valueString(0))); \r
824                 }\r
825                 return notes;\r
826         }\r
827         // Get all notes\r
828         public List<Note> getAllNotes() {\r
829                 List<Note> notes = new ArrayList<Note>();\r
830                 prepareQueries();\r
831                 boolean check;                                  \r
832         NSqlQuery query = getAllQueryWithoutContent;\r
833                 check = query.exec();\r
834                 if (!check)\r
835                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
836                 // Get a list of the notes\r
837                 while (query.next()) {\r
838                         notes.add(mapNoteFromQuery(query, false, false, false, false, true));\r
839                 }\r
840                 return notes;\r
841         }\r
842         // Count unindexed notes\r
843         public int getUnindexedCount() {\r
844         NSqlQuery query = new NSqlQuery(db.getConnection());\r
845                 query.exec("select count(*) from note where indexneeded=true and isExpunged = false");\r
846                 query.next(); \r
847                 int returnValue = new Integer(query.valueString(0));\r
848                 return returnValue;\r
849         }\r
850         // Count unsynchronized notes\r
851         public int getDirtyCount() {\r
852         NSqlQuery query = new NSqlQuery(db.getConnection());\r
853                 query.exec("select count(*) from note where isDirty=true and isExpunged = false");\r
854                 query.next(); \r
855                 int returnValue = new Integer(query.valueString(0));\r
856                 return returnValue;\r
857         }\r
858         // Count notes\r
859         public int getNoteCount() {\r
860         NSqlQuery query = new NSqlQuery(db.getConnection());\r
861                 query.exec("select count(*) from note where isExpunged = false");\r
862                 query.next(); \r
863                 int returnValue = new Integer(query.valueString(0));\r
864                 return returnValue;\r
865         }\r
866         // Count deleted notes\r
867         public int getDeletedCount() {\r
868         NSqlQuery query = new NSqlQuery(db.getConnection());\r
869                 query.exec("select count(*) from note where isExpunged = false and active = false");\r
870                 if (!query.next()) \r
871                         return 0;\r
872                 int returnValue = new Integer(query.valueString(0));\r
873                 return returnValue;\r
874         }\r
875         // Reset a note sequence number to zero.  This is useful for moving conflicting notes\r
876         public void resetNoteSequence(String guid) {\r
877                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
878                 boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");\r
879                 if (!check) {\r
880                         logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");\r
881                         logger.log(logger.MEDIUM, query.lastError());\r
882                 }\r
883                 query.bindValue(":guid", guid);\r
884                 check = query.exec();\r
885                 if (!check) {\r
886                         logger.log(logger.EXTREME, "Update note sequence number has failed.");\r
887                         logger.log(logger.MEDIUM, query.lastError());\r
888                 }\r
889         }\r
890         \r
891         \r
892         // Update a note resource by the hash\r
893         public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {\r
894                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
895 /*              query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");\r
896                 query.bindValue(":noteGuid", noteGuid);\r
897                 query.bindValue(":hex", hash);\r
898                 query.exec();\r
899                 if (!query.next()) {\r
900                         logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash.  GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);\r
901                         return;\r
902                 }\r
903                 String guid = query.valueString(0);\r
904 */              \r
905                 query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");\r
906                 query.bindValue(":guid", resGuid);\r
907                 query.bindValue(":noteGuid", noteGuid);\r
908                 query.bindValue(":hex", hash);\r
909                 if (!query.exec()) {\r
910                         logger.log(logger.EXTREME, "Note Resource Update by Hash failed");\r
911                         logger.log(logger.EXTREME, query.lastError().toString());\r
912                 }\r
913         }\r
914 \r
915         // Fix CRLF problem that is on some notes\r
916         private String fixCarriageReturn(String note) {\r
917                 if (note == null || !Global.enableCarriageReturnFix)\r
918                         return note;\r
919                 QByteArray a0Hex = new QByteArray("a0");\r
920                 String a0 = QByteArray.fromHex(a0Hex).toString();\r
921                 note = note.replace("<div>"+a0+"</div>", "<div>&nbsp;</div>");\r
922                 return note.replace("<div/>", "<div>&nbsp;</div>");\r
923         }\r
924         \r
925         \r
926         \r
927         //********************************************************************************\r
928         //********************************************************************************\r
929         //* Indexing Functions\r
930         //********************************************************************************\r
931         //********************************************************************************\r
932         // set/unset a note to be reindexed\r
933         public void setIndexNeeded(String guid, Boolean flag) {\r
934                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
935                 query.prepare("Update Note set indexNeeded=:flag where guid=:guid");\r
936 \r
937                 if (flag)\r
938                         query.bindValue(":flag", 1);\r
939                 else\r
940                         query.bindValue(":flag", 0);\r
941                 query.bindValue(":guid", guid);\r
942                 if (!query.exec()) {\r
943                         logger.log(logger.MEDIUM, "Note indexNeeded update failed.");\r
944                         logger.log(logger.MEDIUM, query.lastError());\r
945                 } \r
946         }\r
947         // Set all notes to be reindexed\r
948         public void reindexAllNotes() {\r
949                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
950                 if (!query.exec("Update Note set indexNeeded=true")) {\r
951                         logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");\r
952                         logger.log(logger.MEDIUM, query.lastError());\r
953                 } \r
954         }\r
955 \r
956         // Get all unindexed notes\r
957         public List <String> getUnindexed() {\r
958                 String guid;\r
959                 List<String> index = new ArrayList<String>();\r
960         NSqlQuery query = new NSqlQuery(db.getConnection());\r
961                                         \r
962                 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))\r
963                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
964 \r
965                 // Get a list of the notes\r
966                 while (query.next()) {\r
967                         guid = new String();\r
968                         guid = query.valueString(0);\r
969                         index.add(guid); \r
970                 }       \r
971                 return index;   \r
972         }\r
973         public List<String> getNextUnindexed(int limit) {\r
974                 List<String> guids = new ArrayList<String>();\r
975                         \r
976         NSqlQuery query = new NSqlQuery(db.getConnection());\r
977                                         \r
978                 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))\r
979                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
980                 \r
981                 // Get a list of the notes\r
982                 String guid;\r
983                 while (query.next()) {\r
984                         guid = new String();\r
985                         guid = query.valueString(0);\r
986                         guids.add(guid);\r
987                 }       \r
988                 return guids;   \r
989         }\r
990         \r
991         \r
992         //**********************************************************************************\r
993         //* Title color functions\r
994         //**********************************************************************************\r
995         // Get the title color of all notes\r
996         public List<Pair<String, Integer>> getNoteTitleColors() {\r
997                 List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
998         NSqlQuery query = new NSqlQuery(db.getConnection());\r
999                 \r
1000                 if (!query.exec("Select guid,titleColor from Note where titleColor != -1"))\r
1001                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
1002 \r
1003                 String guid;\r
1004                 Integer color;\r
1005                 \r
1006                 // Get a list of the notes\r
1007                 while (query.next()) {\r
1008                         Pair<String, Integer> pair = new Pair<String,Integer>();\r
1009                         guid = query.valueString(0);\r
1010                         color = query.valueInteger(1);\r
1011                         pair.setFirst(guid);\r
1012                         pair.setSecond(color);\r
1013                         returnValue.add(pair); \r
1014                 }       \r
1015 \r
1016                 \r
1017                 \r
1018                 return returnValue;\r
1019         }\r
1020         // Set a title color\r
1021         // Reset the dirty bit\r
1022         public void  setNoteTitleColor(String guid, int color) {\r
1023                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1024                 \r
1025                 query.prepare("Update note set titlecolor=:color where guid=:guid");\r
1026                 query.bindValue(":guid", guid);\r
1027                 query.bindValue(":color", color);\r
1028                 if (!query.exec())\r
1029                         logger.log(logger.EXTREME, "Error updating title color.");\r
1030         }\r
1031 \r
1032         \r
1033         \r
1034         //**********************************************************************************\r
1035         //* Thumbnail functions\r
1036         //**********************************************************************************\r
1037         // Set if a new thumbnail is needed\r
1038         public void setThumbnailNeeded(String guid, boolean needed) {\r
1039                 \r
1040                 boolean check;                  \r
1041         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1042                                         \r
1043                 check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");\r
1044                 query.bindValue(":guid", guid);\r
1045                 query.bindValue(":needed", needed);\r
1046                 check = query.exec();\r
1047                 if (!check) \r
1048                         logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());\r
1049 \r
1050         }\r
1051         // Is a thumbail needed for this guid?\r
1052         public boolean isThumbnailNeeded(String guid) {\r
1053                 \r
1054                 boolean check;                  \r
1055         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1056                                         \r
1057                 check = query.prepare("select thumbnailneeded from note where guid=:guid");\r
1058                 query.bindValue(":guid", guid);\r
1059                 check = query.exec();\r
1060                 if (!check) \r
1061                         logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());\r
1062                 \r
1063                 boolean returnValue;\r
1064                 // Get a list of the notes\r
1065                 if (query.next()) \r
1066                         returnValue = query.valueBoolean(0, false); \r
1067                 else\r
1068                         returnValue = false;\r
1069 \r
1070                 return returnValue;     \r
1071         }\r
1072         // Set if a new thumbnail is needed\r
1073         public void setThumbnail(String guid, QByteArray thumbnail) {\r
1074                 \r
1075                 boolean check;                  \r
1076         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1077                                         \r
1078                 check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");\r
1079                 query.bindValue(":guid", guid);\r
1080                 query.bindValue(":thumbnail", thumbnail.toByteArray());\r
1081                 check = query.exec();\r
1082                 if (!check) \r
1083                         logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());\r
1084 \r
1085         }\r
1086         // Set if a new thumbnail is needed\r
1087         public QByteArray getThumbnail(String guid) {\r
1088                 \r
1089                 boolean check;                  \r
1090         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1091                                         \r
1092                 check = query.prepare("Select thumbnail from note where guid=:guid");\r
1093                 query.bindValue(":guid", guid);\r
1094                 check = query.exec();\r
1095                 if (!check) \r
1096                         logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());\r
1097                 // Get a list of the notes\r
1098                 if (query.next()) \r
1099                         if (query.getBlob(0) != null)\r
1100                                 return new QByteArray(query.getBlob(0)); \r
1101                 return null;\r
1102         }\r
1103         \r
1104         \r
1105         // Update a note content's hash.  This happens if a resource is edited outside of NN\r
1106         public void updateResourceContentHash(String guid, String oldHash, String newHash) {\r
1107                 Note n = getNote(guid, true, false, false, false,false);\r
1108                 int position = n.getContent().indexOf("<en-media");\r
1109                 int endPos;\r
1110                 for (;position>-1;) {\r
1111                         endPos = n.getContent().indexOf(">", position+1);\r
1112                         String oldSegment = n.getContent().substring(position,endPos);\r
1113                         int hashPos = oldSegment.indexOf("hash=\"");\r
1114                         int hashEnd = oldSegment.indexOf("\"", hashPos+7);\r
1115                         String hash = oldSegment.substring(hashPos+6, hashEnd);\r
1116                         if (hash.equalsIgnoreCase(oldHash)) {\r
1117                                 String newSegment = oldSegment.replace(oldHash, newHash);\r
1118                                 String content = n.getContent().substring(0,position) +\r
1119                                                  newSegment +\r
1120                                                  n.getContent().substring(endPos);\r
1121                                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1122                                 query.prepare("update note set isdirty=true, content=:content where guid=:guid");\r
1123                                 query.bindValue(":content", content);\r
1124                                 query.bindValue(":guid", n.getGuid());\r
1125                                 query.exec();\r
1126                         }\r
1127                         \r
1128                         position = n.getContent().indexOf("<en-media", position+1);\r
1129                 }\r
1130         }\r
1131 }       \r