OSDN Git Service

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