OSDN Git Service

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