OSDN Git Service

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