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> getDirtyLinked(String notebookGuid) {\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                 query.prepare("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid=:notebookGuid");\r
 773                 query.bindValue(":notebookGuid", notebookGuid);\r
 774                 check = query.exec();\r
 775                 if (!check) \r
 776                         logger.log(logger.EXTREME, "Note SQL retrieve has failed getting dirty linked notes: " +query.lastError().toString());\r
 777                 \r
 778                 // Get a list of the notes\r
 779                 while (query.next()) {\r
 780                         guid = new String();\r
 781                         guid = query.valueString(0);\r
 782                         index.add(guid); \r
 783                 }       \r
 784                 \r
 785                 // Start getting notes\r
 786                 for (int i=0; i<index.size(); i++) {\r
 787                         tempNote = getNote(index.get(i), true,true,false,true,true);\r
 788                         notes.add(tempNote);\r
 789                 }\r
 790                 return notes;   \r
 791         }\r
 792         // Get a list of notes that need to be updated\r
 793         public List <String> getNotesByNotebook(String notebookGuid) {\r
 794                 List<String> notes = new ArrayList<String>();\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                 check = query.prepare("Select guid from Note where notebookguid=:notebookguid");\r
 801                 if (!check) \r
 802                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
 803                 query.bindValue(":notebookguid", notebookGuid);\r
 804                 query. exec();\r
 805                 \r
 806                 // Get a list of the notes\r
 807                 while (query.next()) {\r
 808                         index.add(query.valueString(0)); \r
 809                 }       \r
 810                 \r
 811                 return notes;   \r
 812         }\r
 813         // Get a list of notes that need to be updated\r
 814         public boolean isNoteDirty(String guid) {\r
 815                 \r
 816                 boolean check;                  \r
 817         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 818                                         \r
 819                 check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");\r
 820                 query.bindValue(":guid", guid);\r
 821                 check = query.exec();\r
 822                 if (!check) \r
 823                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
 824                 \r
 825                 boolean returnValue;\r
 826                 // Get a list of the notes\r
 827                 if (query.next()) \r
 828                         returnValue = true; \r
 829                 else\r
 830                         returnValue = false;\r
 831 \r
 832                 return returnValue;     \r
 833         }\r
 834         // Get a list of notes that need to be updated\r
 835         public List <String> getUnsynchronizedGUIDs() {\r
 836                 String guid;\r
 837                 List<String> index = new ArrayList<String>();\r
 838                 \r
 839                 boolean check;                  \r
 840         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 841                                         \r
 842                 check = query.exec("Select guid from Note where isDirty=true");\r
 843                 if (!check) \r
 844                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
 845                 \r
 846                 // Get a list of the notes\r
 847                 while (query.next()) {\r
 848                         guid = new String();\r
 849                         guid = query.valueString(0);\r
 850                         index.add(guid); \r
 851                 }       \r
 852                 return index;   \r
 853         }\r
 854         // Reset the dirty bit\r
 855         public void  resetDirtyFlag(String guid) {\r
 856                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 857                 \r
 858                 query.prepare("Update note set isdirty=false where guid=:guid");\r
 859                 query.bindValue(":guid", guid);\r
 860                 if (!query.exec())\r
 861                         logger.log(logger.EXTREME, "Error resetting note dirty field.");\r
 862         }\r
 863         // Get all notes\r
 864         public List<String> getAllGuids() {\r
 865                 List<String> notes = 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");\r
 871                 if (!check)\r
 872                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
 873 \r
 874                 // Get a list of the notes\r
 875                 while (query.next()) {\r
 876                         notes.add(new String(query.valueString(0))); \r
 877                 }\r
 878                 return notes;\r
 879         }\r
 880         // Get all notes\r
 881         public List<Note> getAllNotes() {\r
 882                 List<Note> notes = new ArrayList<Note>();\r
 883                 prepareQueries();\r
 884                 boolean check;                                  \r
 885         NSqlQuery query = getAllQueryWithoutContent;\r
 886                 check = query.exec();\r
 887                 if (!check)\r
 888                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
 889                 // Get a list of the notes\r
 890                 while (query.next()) {\r
 891                         notes.add(mapNoteFromQuery(query, false, false, false, false, true));\r
 892                 }\r
 893                 return notes;\r
 894         }\r
 895         // Count unindexed notes\r
 896         public int getUnindexedCount() {\r
 897         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 898                 query.exec("select count(*) from note where indexneeded=true and isExpunged = false");\r
 899                 query.next(); \r
 900                 int returnValue = new Integer(query.valueString(0));\r
 901                 return returnValue;\r
 902         }\r
 903         // Count unsynchronized notes\r
 904         public int getDirtyCount() {\r
 905         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 906                 query.exec("select count(guid) from note where isDirty=true and isExpunged = false");\r
 907                 query.next(); \r
 908                 int returnValue = new Integer(query.valueString(0));\r
 909                 return returnValue;\r
 910         }\r
 911         // Count notes\r
 912         public int getNoteCount() {\r
 913         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 914                 query.exec("select count(*) from note where isExpunged = false");\r
 915                 query.next(); \r
 916                 int returnValue = new Integer(query.valueString(0));\r
 917                 return returnValue;\r
 918         }\r
 919         // Count deleted notes\r
 920         public int getDeletedCount() {\r
 921         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 922                 query.exec("select count(*) from note where isExpunged = false and active = false");\r
 923                 if (!query.next()) \r
 924                         return 0;\r
 925                 int returnValue = new Integer(query.valueString(0));\r
 926                 return returnValue;\r
 927         }\r
 928         // Reset a note sequence number to zero.  This is useful for moving conflicting notes\r
 929         public void resetNoteSequence(String guid) {\r
 930                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 931                 boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");\r
 932                 if (!check) {\r
 933                         logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");\r
 934                         logger.log(logger.MEDIUM, query.lastError());\r
 935                 }\r
 936                 query.bindValue(":guid", guid);\r
 937                 check = query.exec();\r
 938                 if (!check) {\r
 939                         logger.log(logger.EXTREME, "Update note sequence number has failed.");\r
 940                         logger.log(logger.MEDIUM, query.lastError());\r
 941                 }\r
 942         }\r
 943         \r
 944         \r
 945         // Update a note resource by the hash\r
 946         public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {\r
 947                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 948 /*              query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");\r
 949                 query.bindValue(":noteGuid", noteGuid);\r
 950                 query.bindValue(":hex", hash);\r
 951                 query.exec();\r
 952                 if (!query.next()) {\r
 953                         logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash.  GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);\r
 954                         return;\r
 955                 }\r
 956                 String guid = query.valueString(0);\r
 957 */              \r
 958                 query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");\r
 959                 query.bindValue(":guid", resGuid);\r
 960                 query.bindValue(":noteGuid", noteGuid);\r
 961                 query.bindValue(":hex", hash);\r
 962                 if (!query.exec()) {\r
 963                         logger.log(logger.EXTREME, "Note Resource Update by Hash failed");\r
 964                         logger.log(logger.EXTREME, query.lastError().toString());\r
 965                 }\r
 966         }\r
 967 \r
 968         // Fix CRLF problem that is on some notes\r
 969         private String fixCarriageReturn(String note) {\r
 970                 if (note == null || !Global.enableCarriageReturnFix)\r
 971                         return note;\r
 972                 QByteArray a0Hex = new QByteArray("a0");\r
 973                 String a0 = QByteArray.fromHex(a0Hex).toString();\r
 974                 note = note.replace("<div>"+a0+"</div>", "<div>&nbsp;</div>");\r
 975                 return note.replace("<div/>", "<div>&nbsp;</div>");\r
 976         }\r
 977         \r
 978         // Expunge notes that we don't want to synchronize\r
 979         public List<String> expungeIgnoreSynchronizedNotes(List<String> notebooks, List<String>tags, List<String> linked) {\r
 980                 \r
 981                 List<String> noteGuids = new ArrayList<String>();\r
 982                 for (int i=0; i<notebooks.size(); i++) {\r
 983                         List<String> notes = findNotesByNotebook(notebooks.get(i));\r
 984                         for (int j=0; j<notes.size(); j++) {\r
 985                                 if (!isNoteDirty(notes.get(j))) {\r
 986                                         expungeNote(notes.get(j), true, false);\r
 987                                         noteGuids.add(notes.get(j));\r
 988                                 }\r
 989                         }\r
 990                 }\r
 991                 \r
 992                 for (int i=0; i<tags.size(); i++) {\r
 993                         List<String> notes = findNotesByTag(tags.get(i));\r
 994                         for (int j=0; j<notes.size(); j++) {\r
 995                                 if (!isNoteDirty(notes.get(j))) {\r
 996                                         expungeNote(notes.get(j), true, false);\r
 997                                         noteGuids.add(notes.get(j));\r
 998                                 }\r
 999                         }\r
1000                 }\r
1001                 \r
1002                 for (int i=0; i<linked.size(); i++) {\r
1003                         String notebookGuid = db.getLinkedNotebookTable().getNotebookGuid(linked.get(i));\r
1004                         if (notebookGuid != null && !notebookGuid.trim().equals("")) {\r
1005                                 List<Tag> linkedTags = db.getTagTable().getTagsForNotebook(notebookGuid);\r
1006                                 for (int j=0; j<linkedTags.size(); j++)\r
1007                                         db.getTagTable().expungeTag(linkedTags.get(j).getGuid(), false);\r
1008                                 \r
1009                                 List<String> notes = findNotesByNotebook(notebookGuid);\r
1010                                 for (int j=0; j<notes.size(); j++) {\r
1011                                         if (!isNoteDirty(notes.get(j))) {\r
1012                                                 expungeNote(notes.get(j), true, false);\r
1013                                                 noteGuids.add(notes.get(j));\r
1014                                         }\r
1015                                 }\r
1016                         }\r
1017                 }\r
1018                 return noteGuids;\r
1019         }\r
1020         \r
1021         // Find a note by its notebook\r
1022         // Expunge notes that we don't want to synchronize\r
1023         public List<String> findNotesByNotebook(String notebook) {\r
1024                 List<String> values = new ArrayList<String>();\r
1025                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1026                 query.prepare("Select guid from note where notebookguid=:notebook");\r
1027 \r
1028                 query.bindValue(":notebook", notebook);\r
1029                 query.exec();\r
1030                 while (query.next()) {\r
1031                         values.add(query.valueString(0));\r
1032                 }\r
1033                 return values;\r
1034         }\r
1035         \r
1036         public List<String> findNotesByTag(String tag) {\r
1037                 List<String> values = new ArrayList<String>();\r
1038                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1039                 query.prepare("Select distinct noteguid from notetags where tagguid=:tag");\r
1040 \r
1041                 query.bindValue(":tag", tag);\r
1042                 query.exec();\r
1043                 while (query.next()) {\r
1044                         values.add(query.valueString(0));\r
1045                 }\r
1046                 return values;\r
1047         }\r
1048         \r
1049         \r
1050         \r
1051         //********************************************************************************\r
1052         //********************************************************************************\r
1053         //* Indexing Functions\r
1054         //********************************************************************************\r
1055         //********************************************************************************\r
1056         // set/unset a note to be reindexed\r
1057         public void setIndexNeeded(String guid, Boolean flag) {\r
1058                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1059                 query.prepare("Update Note set indexNeeded=:flag where guid=:guid");\r
1060 \r
1061                 if (flag)\r
1062                         query.bindValue(":flag", 1);\r
1063                 else\r
1064                         query.bindValue(":flag", 0);\r
1065                 query.bindValue(":guid", guid);\r
1066                 if (!query.exec()) {\r
1067                         logger.log(logger.MEDIUM, "Note indexNeeded update failed.");\r
1068                         logger.log(logger.MEDIUM, query.lastError());\r
1069                 } \r
1070                 List<Resource> r = noteResourceTable.getNoteResources(guid, false);\r
1071                 for (int i=0; r!= null && i<r.size(); i++) {\r
1072                         noteResourceTable.setIndexNeeded(r.get(i).getGuid(), true);\r
1073                 }\r
1074         }\r
1075         // Set all notes to be reindexed\r
1076         public void reindexAllNotes() {\r
1077                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1078                 if (!query.exec("Update Note set indexNeeded=true")) {\r
1079                         logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");\r
1080                         logger.log(logger.MEDIUM, query.lastError());\r
1081                 } \r
1082         }\r
1083 \r
1084         // Get all unindexed notes\r
1085         public List <String> getUnindexed() {\r
1086                 String guid;\r
1087                 List<String> index = new ArrayList<String>();\r
1088         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1089                                         \r
1090                 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))\r
1091                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
1092 \r
1093                 // Get a list of the notes\r
1094                 while (query.next()) {\r
1095                         guid = new String();\r
1096                         guid = query.valueString(0);\r
1097                         index.add(guid); \r
1098                 }       \r
1099                 return index;   \r
1100         }\r
1101         public List<String> getNextUnindexed(int limit) {\r
1102                 List<String> guids = new ArrayList<String>();\r
1103                         \r
1104         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1105                                         \r
1106                 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))\r
1107                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
1108                 \r
1109                 // Get a list of the notes\r
1110                 String guid;\r
1111                 while (query.next()) {\r
1112                         guid = new String();\r
1113                         guid = query.valueString(0);\r
1114                         guids.add(guid);\r
1115                 }       \r
1116                 return guids;   \r
1117         }\r
1118         \r
1119         \r
1120         //**********************************************************************************\r
1121         //* Title color functions\r
1122         //**********************************************************************************\r
1123         // Get the title color of all notes\r
1124         public List<Pair<String, Integer>> getNoteTitleColors() {\r
1125                 List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
1126         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1127                 \r
1128                 if (!query.exec("Select guid,titleColor from Note where titleColor != -1"))\r
1129                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
1130 \r
1131                 String guid;\r
1132                 Integer color;\r
1133                 \r
1134                 // Get a list of the notes\r
1135                 while (query.next()) {\r
1136                         Pair<String, Integer> pair = new Pair<String,Integer>();\r
1137                         guid = query.valueString(0);\r
1138                         color = query.valueInteger(1);\r
1139                         pair.setFirst(guid);\r
1140                         pair.setSecond(color);\r
1141                         returnValue.add(pair); \r
1142                 }       \r
1143 \r
1144                 return returnValue;\r
1145         }\r
1146         // Set a title color\r
1147         public void  setNoteTitleColor(String guid, int color) {\r
1148                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1149                 \r
1150                 query.prepare("Update note set titlecolor=:color where guid=:guid");\r
1151                 query.bindValue(":guid", guid);\r
1152                 query.bindValue(":color", color);\r
1153                 if (!query.exec())\r
1154                         logger.log(logger.EXTREME, "Error updating title color.");\r
1155         }\r
1156         // Get in individual note's title color\r
1157         // Get the title color of all notes\r
1158         public Integer getNoteTitleColor(String guid) {\r
1159                 List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
1160         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1161                 \r
1162         query.prepare("Select titleColor from Note where titleColor != -1 and guid=:guid");\r
1163         query.bindValue(":guid", guid);\r
1164                 if (!query.exec())\r
1165                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteTitleColor(guid).");\r
1166 \r
1167                 Integer color = -1;\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                 \r
1180                 return color;\r
1181         }\r
1182         \r
1183         \r
1184         //**********************************************************************************\r
1185         //* Thumbnail functions\r
1186         //**********************************************************************************\r
1187         // Set if a new thumbnail is needed\r
1188         public void setThumbnailNeeded(String guid, boolean needed) {\r
1189                 \r
1190                 boolean check;                  \r
1191         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1192                                         \r
1193                 check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");\r
1194                 query.bindValue(":guid", guid);\r
1195                 query.bindValue(":needed", needed);\r
1196                 check = query.exec();\r
1197                 if (!check) \r
1198                         logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());\r
1199 \r
1200         }\r
1201         // Is a thumbail needed for this guid?\r
1202         public boolean isThumbnailNeeded(String guid) {\r
1203                 \r
1204                 boolean check;                  \r
1205         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1206                                         \r
1207                 check = query.prepare("select thumbnailneeded from note where guid=:guid");\r
1208                 query.bindValue(":guid", guid);\r
1209                 check = query.exec();\r
1210                 if (!check) \r
1211                         logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());\r
1212                 \r
1213                 boolean returnValue;\r
1214                 // Get a list of the notes\r
1215                 if (query.next()) \r
1216                         returnValue = query.valueBoolean(0, false); \r
1217                 else\r
1218                         returnValue = false;\r
1219 \r
1220                 return returnValue;     \r
1221         }\r
1222         // Set if a new thumbnail is needed\r
1223         public void setThumbnail(String guid, QByteArray thumbnail) {\r
1224                 \r
1225                 boolean check;                  \r
1226         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1227                                         \r
1228                 check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");\r
1229                 query.bindValue(":guid", guid);\r
1230                 query.bindValue(":thumbnail", thumbnail.toByteArray());\r
1231                 check = query.exec();\r
1232                 if (!check) \r
1233                         logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());\r
1234 \r
1235         }\r
1236         // Set if a new thumbnail is needed\r
1237         public QByteArray getThumbnail(String guid) {\r
1238                 \r
1239                 boolean check;                  \r
1240         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1241                                         \r
1242                 check = query.prepare("Select thumbnail 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 get thumbail failed: " +query.lastError().toString());\r
1247                 // Get a list of the notes\r
1248                 if (query.next())  {\r
1249                         try {\r
1250                                 if (query.getBlob(0) != null) {\r
1251                                         return new QByteArray(query.getBlob(0)); \r
1252                                 }\r
1253                         } catch (java.lang.IllegalArgumentException e) {\r
1254                                 return null;\r
1255                         }\r
1256                 }\r
1257                 return null;\r
1258         }\r
1259         // Get all thumbnails\r
1260         public HashMap<String, QPixmap> getThumbnails() {\r
1261                 boolean check;                  \r
1262         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1263         HashMap<String, QPixmap> map = new HashMap<String,QPixmap>();\r
1264                                         \r
1265                 check = query.prepare("Select guid,thumbnail from note where thumbnailneeded=false and isExpunged=false");\r
1266                 check = query.exec();\r
1267                 if (!check) \r
1268                         logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());\r
1269                 // Get a list of the notes\r
1270                 while (query.next())  {\r
1271                         try {\r
1272                                 if (query.getBlob(1) != null) {\r
1273                                         QByteArray data = new QByteArray(query.getBlob(1));\r
1274                                         QPixmap img = new QPixmap();\r
1275                                         if (img.loadFromData(data)) {\r
1276                                                 img = img.scaled(Global.largeThumbnailSize);\r
1277                                                 map.put(query.valueString(0), img);\r
1278                                         }\r
1279                                 }       \r
1280                         } catch (java.lang.IllegalArgumentException e) {\r
1281                                 logger.log(logger.HIGH, "Error retrieving thumbnail " +e.getMessage());\r
1282                         }\r
1283                 }\r
1284                 return map;\r
1285         }\r
1286         // Get a list of notes that need thumbnails\r
1287         public List<String> findThumbnailsNeeded() {\r
1288                 \r
1289                 boolean check;\r
1290         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1291                                         \r
1292                 check = query.prepare("select guid from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 5");\r
1293                 check = query.exec();\r
1294                 if (!check) \r
1295                         logger.log(logger.EXTREME, "Note SQL findThumbnailsNeeded query failed: " +query.lastError().toString());\r
1296                 \r
1297 \r
1298                 // Get a list of the notes\r
1299                 List<String> values = new ArrayList<String>();\r
1300                 while (query.next()) {\r
1301                         values.add(query.valueString(0)); \r
1302                 }\r
1303 \r
1304                 return values;  \r
1305         }\r
1306         // Get a count of thumbnails needed\r
1307         public int getThumbnailNeededCount() {\r
1308                 \r
1309                 boolean check;\r
1310         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1311                                         \r
1312                 check = query.prepare("select count(guid) from note where thumbnailneeded=true and isExpunged=false and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5 limit 2");\r
1313                 check = query.exec();\r
1314                 if (!check) \r
1315                         logger.log(logger.EXTREME, "Note SQL findThumbnailNeededCount query failed: " +query.lastError().toString());\r
1316                 \r
1317                 if (query.next()) {\r
1318                         return query.valueInteger(0); \r
1319                 }\r
1320 \r
1321                 return 0;       \r
1322         }\r
1323 \r
1324         \r
1325         // Update a note content's hash.  This happens if a resource is edited outside of NN\r
1326         public void updateResourceContentHash(String guid, String oldHash, String newHash) {\r
1327                 Note n = getNote(guid, true, false, false, false,false);\r
1328                 int position = n.getContent().indexOf("<en-media");\r
1329                 int endPos;\r
1330                 for (;position>-1;) {\r
1331                         endPos = n.getContent().indexOf(">", position+1);\r
1332                         String oldSegment = n.getContent().substring(position,endPos);\r
1333                         int hashPos = oldSegment.indexOf("hash=\"");\r
1334                         int hashEnd = oldSegment.indexOf("\"", hashPos+7);\r
1335                         String hash = oldSegment.substring(hashPos+6, hashEnd);\r
1336                         if (hash.equalsIgnoreCase(oldHash)) {\r
1337                                 String newSegment = oldSegment.replace(oldHash, newHash);\r
1338                                 String content = n.getContent().substring(0,position) +\r
1339                                                  newSegment +\r
1340                                                  n.getContent().substring(endPos);\r
1341                                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1342                                 query.prepare("update note set isdirty=true, thumbnailneeded=true, content=:content where guid=:guid");\r
1343                                 query.bindValue(":content", content);\r
1344                                 query.bindValue(":guid", n.getGuid());\r
1345                                 query.exec();\r
1346                         }\r
1347                         \r
1348                         position = n.getContent().indexOf("<en-media", position+1);\r
1349                 }\r
1350         }\r
1351 }       \r