OSDN Git Service

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