OSDN Git Service

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