OSDN Git Service

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