OSDN Git Service

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