OSDN Git Service

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