OSDN Git Service

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