OSDN Git Service

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