OSDN Git Service

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