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.List;\r
  28 \r
  29 import com.evernote.edam.type.Note;\r
  30 import com.evernote.edam.type.NoteAttributes;\r
  31 import com.evernote.edam.type.Resource;\r
  32 import com.evernote.edam.type.Tag;\r
  33 import com.trolltech.qt.core.QByteArray;\r
  34 import com.trolltech.qt.core.QDateTime;\r
  35 import com.trolltech.qt.core.QTextCodec;\r
  36 \r
  37 import cx.fbn.nevernote.Global;\r
  38 import cx.fbn.nevernote.evernote.EnmlConverter;\r
  39 import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
  40 import cx.fbn.nevernote.utilities.ApplicationLogger;\r
  41 import cx.fbn.nevernote.utilities.Pair;\r
  42 \r
  43 public class NoteTable {\r
  44         private final ApplicationLogger                 logger;\r
  45         public final NoteTagsTable                              noteTagsTable;\r
  46         public NoteResourceTable                                noteResourceTable;\r
  47         private final DatabaseConnection                db;\r
  48         int id;\r
  49 \r
  50         // Prepared Queries to improve speed\r
  51         private NSqlQuery                                               getQueryWithContent;\r
  52         private NSqlQuery                                               getQueryWithoutContent;\r
  53         private NSqlQuery                                               getAllQueryWithoutContent;\r
  54         \r
  55         // Constructor\r
  56         public NoteTable(ApplicationLogger l, DatabaseConnection d) {\r
  57                 logger = l;\r
  58                 db = d;\r
  59                 id = 0;\r
  60                 noteResourceTable = new NoteResourceTable(logger, db);\r
  61                 noteTagsTable = new NoteTagsTable(logger, db);\r
  62                 getQueryWithContent = null;\r
  63                 getQueryWithoutContent = null;\r
  64                 \r
  65         }\r
  66         // Create the table\r
  67         public void createTable() {\r
  68                 getQueryWithContent = new NSqlQuery(db.getConnection());\r
  69                 getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
  70                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  71         logger.log(logger.HIGH, "Creating table Note...");\r
  72         if (!query.exec("Create table Note (guid varchar primary key, " +\r
  73                         "updateSequenceNumber integer, title varchar, content varchar, contentHash varchar, "+\r
  74                         "contentLength integer, created timestamp, updated timestamp, deleted timestamp, " \r
  75                         +"active integer, notebookGuid varchar, attributeSubjectDate timestamp, "+\r
  76                         "attributeLatitude double, attributeLongitude double, attributeAltitude double,"+\r
  77                         "attributeAuthor varchar, attributeSource varchar, attributeSourceUrl varchar, "+\r
  78                         "attributeSourceApplication varchar, indexNeeded boolean, isExpunged boolean, " +\r
  79                         "isDirty boolean)"))                    \r
  80                 logger.log(logger.HIGH, "Table Note creation FAILED!!!");    \r
  81         if (!query.exec("CREATE INDEX unindexed_notess on note (indexneeded desc, guid);"))\r
  82                 logger.log(logger.HIGH, "Note unindexed_notes index creation FAILED!!!");\r
  83         if (!query.exec("CREATE INDEX unsynchronized_notes on note (isDirty desc, guid);"))\r
  84                 logger.log(logger.HIGH, "note unsynchronized_notes index creation FAILED!!!");  \r
  85         noteTagsTable.createTable();\r
  86         noteResourceTable.createTable();     \r
  87         }\r
  88         // Drop the table\r
  89         public void dropTable() {\r
  90                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
  91                 query.exec("Drop table Note");\r
  92                 noteTagsTable.dropTable();\r
  93                 noteResourceTable.dropTable();\r
  94         }\r
  95         // Save Note List from Evernote \r
  96         public void addNote(Note n, boolean isDirty) {\r
  97                 logger.log(logger.EXTREME, "Inside addNote");\r
  98                 if (n == null)\r
  99                         return;\r
 100                 \r
 101                 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");\r
 102 \r
 103                 NSqlQuery query = new NSqlQuery(db.getConnection());                    \r
 104                 query.prepare("Insert Into Note ("\r
 105                                 +"guid, updateSequenceNumber, title, content, "\r
 106                                 +"contentHash, contentLength, created, updated, deleted, active, notebookGuid, "\r
 107                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
 108                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
 109                                 +"indexNeeded, isExpunged, isDirty, titlecolor, thumbnailneeded" \r
 110                                 +") Values("\r
 111                                 +":guid, :updateSequenceNumber, :title, :content, "\r
 112                                 +":contentHash, :contentLength, :created, :updated, :deleted, :active, :notebookGuid, "\r
 113                                 +":attributeSubjectDate, :attributeLatitude, :attributeLongitude, :attributeAltitude, "\r
 114                                 +":attributeAuthor, :attributeSource, :attributeSourceUrl, :attributeSourceApplication, "\r
 115                                 +":indexNeeded, :isExpunged, :isDirty, -1, true) ");\r
 116 \r
 117                 StringBuilder created = new StringBuilder(simple.format(n.getCreated()));                       \r
 118                 StringBuilder updated = new StringBuilder(simple.format(n.getUpdated()));                       \r
 119                 StringBuilder deleted = new StringBuilder(simple.format(n.getDeleted()));\r
 120 \r
 121                 EnmlConverter enml = new EnmlConverter(logger);\r
 122                 \r
 123                 query.bindValue(":guid", n.getGuid());\r
 124                 query.bindValue(":updateSequenceNumber", n.getUpdateSequenceNum());\r
 125                 query.bindValue(":title", n.getTitle());\r
 126                 query.bindValue(":content", enml.fixEnXMLCrap(enml.fixEnMediaCrap(n.getContent())));\r
 127                 query.bindValue(":contentHash", n.getContentHash());\r
 128                 query.bindValue(":contentLength", n.getContentLength());\r
 129                 query.bindValue(":created", created.toString());\r
 130                 query.bindValue(":updated", updated.toString());\r
 131                 query.bindValue(":deleted", deleted.toString());\r
 132                 query.bindValue(":active", n.isActive());\r
 133                 query.bindValue(":notebookGuid", n.getNotebookGuid());\r
 134                 \r
 135                 if (n.getAttributes() != null) {\r
 136                         created = new StringBuilder(simple.format(n.getAttributes().getSubjectDate()));\r
 137                         query.bindValue(":attributeSubjectDate", created.toString());\r
 138                         query.bindValue(":attributeLatitude", n.getAttributes().getLatitude());\r
 139                         query.bindValue(":attributeLongitude", n.getAttributes().getLongitude());\r
 140                         query.bindValue(":attributeAltitude", n.getAttributes().getAltitude());\r
 141                         query.bindValue(":attributeAuthor", n.getAttributes().getAuthor());\r
 142                         query.bindValue(":attributeSource", n.getAttributes().getSource());\r
 143                         query.bindValue(":attributeSourceUrl", n.getAttributes().getSourceURL());\r
 144                         query.bindValue(":attributeSourceApplication", n.getAttributes().getSourceApplication());\r
 145                 }\r
 146                 query.bindValue(":indexNeeded", true);\r
 147                 query.bindValue(":isExpunged", false);\r
 148                 query.bindValue(":isDirty", isDirty);\r
 149 \r
 150                 \r
 151                 if (!query.exec())\r
 152                         logger.log(logger.MEDIUM, query.lastError());\r
 153                 \r
 154                 // Save the note tags\r
 155                 if (n.getTagGuids() != null) {\r
 156                         for (int i=0; i<n.getTagGuids().size(); i++) \r
 157                                 noteTagsTable.saveNoteTag(n.getGuid(), n.getTagGuids().get(i));\r
 158                 }\r
 159                 logger.log(logger.EXTREME, "Leaving addNote");\r
 160         } \r
 161         // Setup queries for get to save time later\r
 162         private void prepareQueries() {\r
 163                 getQueryWithContent = new NSqlQuery(db.getConnection());\r
 164                 getQueryWithoutContent = new NSqlQuery(db.getConnection());\r
 165                 getAllQueryWithoutContent = new NSqlQuery(db.getConnection());\r
 166                 \r
 167                 if (!getQueryWithContent.prepare("Select "\r
 168                                 +"guid, updateSequenceNumber, title, "\r
 169                                 +"created, updated, deleted, active, notebookGuid, "\r
 170                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
 171                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication, "\r
 172                                 +"content, contentHash, contentLength"\r
 173                                 +" from Note where guid=:guid and isExpunged=false")) {\r
 174                                         logger.log(logger.EXTREME, "Note SQL select prepare with content has failed.");\r
 175                                         logger.log(logger.MEDIUM, getQueryWithContent.lastError());\r
 176                 }\r
 177                 \r
 178                 if (!getQueryWithoutContent.prepare("Select "\r
 179                                 +"guid, updateSequenceNumber, title, "\r
 180                                 +"created, updated, deleted, active, notebookGuid, "\r
 181                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
 182                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
 183                                 +" from Note where guid=:guid and isExpunged=false")) {\r
 184                                         logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
 185                                         logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
 186                 }\r
 187                 if (!getAllQueryWithoutContent.prepare("Select "\r
 188                                 +"guid, updateSequenceNumber, title, "\r
 189                                 +"created, updated, deleted, active, notebookGuid, "\r
 190                                 +"attributeSubjectDate, attributeLatitude, attributeLongitude, attributeAltitude, "\r
 191                                 +"attributeAuthor, attributeSource, attributeSourceUrl, attributeSourceApplication "\r
 192                                 +" from Note where isExpunged = false")) {\r
 193                                         logger.log(logger.EXTREME, "Note SQL select prepare without content has failed.");\r
 194                                         logger.log(logger.MEDIUM, getQueryWithoutContent.lastError());\r
 195                 }\r
 196         }\r
 197 \r
 198         // Get a note's content in raw, binary format for the sync.\r
 199         public String getNoteContentBinary(String guid) {\r
 200                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 201                 query.prepare("Select content from note where guid=:guid");\r
 202                 query.bindValue(":guid", guid);\r
 203                 query.exec();           \r
 204                 query.next();\r
 205                 return query.valueString(0);\r
 206         }\r
 207         // Get a note by Guid\r
 208         public Note getNote(String noteGuid, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
 209                 if (noteGuid == null)\r
 210                         return null;\r
 211                 if (noteGuid.trim().equals(""))\r
 212                         return null;\r
 213 \r
 214                 prepareQueries();\r
 215                 NSqlQuery query;\r
 216                 if (loadContent) {\r
 217                         query = getQueryWithContent;\r
 218                 } else {\r
 219                         query = getQueryWithoutContent;\r
 220                 }\r
 221                 \r
 222                 query.bindValue(":guid", noteGuid);\r
 223                 if (!query.exec()) {\r
 224                         logger.log(logger.EXTREME, "Note SQL select exec has failed.");\r
 225                         logger.log(logger.MEDIUM, query.lastError());\r
 226                         return null;\r
 227                 }\r
 228                 if (!query.next()) {\r
 229                         logger.log(logger.EXTREME, "SQL Retrieve failed for note guid " +noteGuid + " in getNote()");\r
 230                         logger.log(logger.EXTREME, " -> " +query.lastError().toString());\r
 231                         logger.log(logger.EXTREME, " -> " +query.lastError());\r
 232                         return null;\r
 233                 }\r
 234                 Note n = mapNoteFromQuery(query, loadContent, loadResources, loadRecognition, loadBinary, loadTags);\r
 235                 n.setContent(fixCarriageReturn(n.getContent()));\r
 236                 return n;\r
 237         }\r
 238         // Get a note by Guid\r
 239         public Note mapNoteFromQuery(NSqlQuery query, boolean loadContent, boolean loadResources, boolean loadRecognition, boolean loadBinary, boolean loadTags) {\r
 240                 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
 241 //              indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
 242 \r
 243                 \r
 244                 Note n = new Note();\r
 245                 NoteAttributes na = new NoteAttributes();\r
 246                 n.setAttributes(na);\r
 247                 \r
 248                 n.setGuid(query.valueString(0));\r
 249                 n.setUpdateSequenceNum(new Integer(query.valueString(1)));\r
 250                 n.setTitle(query.valueString(2));\r
 251 \r
 252                 try {\r
 253                         n.setCreated(indfm.parse(query.valueString(3)).getTime());\r
 254                         n.setUpdated(indfm.parse(query.valueString(4)).getTime());\r
 255                         n.setDeleted(indfm.parse(query.valueString(5)).getTime());\r
 256                 } catch (ParseException e) {\r
 257                         e.printStackTrace();\r
 258                 }\r
 259 \r
 260                 n.setActive(query.valueBoolean(6,true));\r
 261                 n.setNotebookGuid(query.valueString(7));\r
 262                 \r
 263                 try {\r
 264                         String attributeSubjectDate = query.valueString(8);\r
 265                         if (!attributeSubjectDate.equals(""))\r
 266                                 na.setSubjectDate(indfm.parse(attributeSubjectDate).getTime());\r
 267                 } catch (ParseException e) {\r
 268                         e.printStackTrace();\r
 269                 }\r
 270                 na.setLatitude(new Float(query.valueString(9)));\r
 271                 na.setLongitude(new Float(query.valueString(10)));\r
 272                 na.setAltitude(new Float(query.valueString(11)));\r
 273                 na.setAuthor(query.valueString(12));\r
 274                 na.setSource(query.valueString(13));\r
 275                 na.setSourceURL(query.valueString(14));\r
 276                 na.setSourceApplication(query.valueString(15));\r
 277                 \r
 278                 if (loadTags) {\r
 279                         n.setTagGuids(noteTagsTable.getNoteTags(n.getGuid()));\r
 280                         List<String> tagNames = new ArrayList<String>();\r
 281                         TagTable tagTable = new TagTable(logger, db);\r
 282                         for (int i=0; i<n.getTagGuids().size(); i++) {\r
 283                                 String currentGuid = n.getTagGuids().get(i);\r
 284                                 Tag tag = tagTable.getTag(currentGuid);\r
 285                                 tagNames.add(tag.getName());\r
 286                         }\r
 287                         n.setTagNames(tagNames);\r
 288                 }\r
 289                 \r
 290                 if (loadContent) {\r
 291                                                 \r
 292                         QTextCodec codec = QTextCodec.codecForLocale();\r
 293                         codec = QTextCodec.codecForName("UTF-8");\r
 294                 String unicode =  codec.fromUnicode(query.valueString(16)).toString();\r
 295                         n.setContent(unicode);\r
 296 //                      n.setContent(query.valueString(16).toString());\r
 297                         \r
 298                         String contentHash = query.valueString(17);\r
 299                         if (contentHash != null)\r
 300                                 n.setContentHash(contentHash.getBytes());\r
 301                         n.setContentLength(new Integer(query.valueString(18)));\r
 302                 }\r
 303                 if (loadResources)\r
 304                         n.setResources(noteResourceTable.getNoteResources(n.getGuid(), loadBinary));\r
 305                 if (loadRecognition) {\r
 306                         if (n.getResources() == null) {\r
 307                                 List<Resource> resources = noteResourceTable.getNoteResourcesRecognition(n.getGuid());\r
 308                                 n.setResources(resources);\r
 309                         } else {\r
 310                                 // We need to merge the recognition resources with the note resources retrieved earlier\r
 311                                 for (int i=0; i<n.getResources().size(); i++) {\r
 312                                         Resource r = noteResourceTable.getNoteResourceRecognition(n.getResources().get(i).getGuid());\r
 313                                         n.getResources().get(i).setRecognition(r.getRecognition());\r
 314                                 }\r
 315                         }\r
 316                 }\r
 317                 n.setContent(fixCarriageReturn(n.getContent()));\r
 318                 return n;\r
 319         }\r
 320         // Update a note's title\r
 321         public void updateNoteTitle(String guid, String title) {\r
 322                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 323                 boolean check = query.prepare("Update Note set title=:title, isDirty=true where guid=:guid");\r
 324                 if (!check) {\r
 325                         logger.log(logger.EXTREME, "Update note title sql prepare has failed.");\r
 326                         logger.log(logger.MEDIUM, query.lastError());\r
 327                 }\r
 328                 query.bindValue(":title", title);\r
 329                 query.bindValue(":guid", guid);\r
 330                 check = query.exec();\r
 331                 if (!check) {\r
 332                         logger.log(logger.EXTREME, "Update note title has failed.");\r
 333                         logger.log(logger.MEDIUM, query.lastError());\r
 334                 }\r
 335         }\r
 336         // Update a note's creation date\r
 337         public void updateNoteCreatedDate(String guid, QDateTime date) {\r
 338                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 339                 boolean check = query.prepare("Update Note set created=:created, isDirty=true where guid=:guid");\r
 340                 if (!check) {\r
 341                         logger.log(logger.EXTREME, "Update note creation update sql prepare has failed.");\r
 342                         logger.log(logger.MEDIUM, query.lastError());\r
 343                 }\r
 344                 \r
 345                 query.bindValue(":created", date.toString("yyyy-MM-dd HH:mm:ss"));\r
 346                 query.bindValue(":guid", guid);\r
 347                 \r
 348                 check = query.exec();\r
 349                 if (!check) {\r
 350                         logger.log(logger.EXTREME, "Update note creation date has failed.");\r
 351                         logger.log(logger.MEDIUM, query.lastError());\r
 352                 }\r
 353         }\r
 354         // Update a note's creation date\r
 355         public void updateNoteAlteredDate(String guid, QDateTime date) {\r
 356                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 357                 boolean check = query.prepare("Update Note set updated=:altered, isDirty=true where guid=:guid");\r
 358                 if (!check) {\r
 359                         logger.log(logger.EXTREME, "Update note altered sql prepare has failed.");\r
 360                         logger.log(logger.MEDIUM, query.lastError());\r
 361                 }\r
 362                 \r
 363                 query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
 364                 query.bindValue(":guid", guid);\r
 365                 \r
 366                 check = query.exec();\r
 367                 if (!check) {\r
 368                         logger.log(logger.EXTREME, "Update note altered date has failed.");\r
 369                         logger.log(logger.MEDIUM, query.lastError());\r
 370                 }\r
 371         }\r
 372         // Update a note's creation date\r
 373         public void updateNoteSubjectDate(String guid, QDateTime date) {\r
 374                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 375                 boolean check = query.prepare("Update Note set attributeSubjectDate=:altered, isDirty=true where guid=:guid");\r
 376                 if (!check) {\r
 377                         logger.log(logger.EXTREME, "Update note subject date sql prepare has failed.");\r
 378                         logger.log(logger.MEDIUM, query.lastError());\r
 379                 }\r
 380         \r
 381                 query.bindValue(":altered", date.toString("yyyy-MM-dd HH:mm:ss"));\r
 382                 query.bindValue(":guid", guid);\r
 383                 \r
 384                 check = query.exec();\r
 385                 if (!check) {\r
 386                         logger.log(logger.EXTREME, "Update note subject date date has failed.");\r
 387                         logger.log(logger.MEDIUM, query.lastError());\r
 388                 }\r
 389         }\r
 390         // Update a note's creation date\r
 391         public void updateNoteAuthor(String guid, String author) {\r
 392                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 393                 boolean check = query.prepare("Update Note set attributeAuthor=:author, isDirty=true where guid=:guid");\r
 394                 if (!check) {\r
 395                         logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
 396                         logger.log(logger.MEDIUM, query.lastError());\r
 397                 }\r
 398 \r
 399                 query.bindValue(":author", author);\r
 400                 query.bindValue(":guid", guid);\r
 401 \r
 402                 check = query.exec();\r
 403                 if (!check) {\r
 404                         logger.log(logger.EXTREME, "Update note author has failed.");\r
 405                         logger.log(logger.MEDIUM, query.lastError());\r
 406                 }\r
 407                 \r
 408         }\r
 409         // Update a note's geo tags\r
 410         public void updateNoteGeoTags(String guid, Double lon, Double lat, Double alt) {\r
 411                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 412                 boolean check = query.prepare("Update Note set attributeLongitude=:longitude, "+\r
 413                                 "attributeLatitude=:latitude, attributeAltitude=:altitude, isDirty=true where guid=:guid");\r
 414                 if (!check) {\r
 415                         logger.log(logger.EXTREME, "Update note author sql prepare has failed.");\r
 416                         logger.log(logger.MEDIUM, query.lastError());\r
 417                 }\r
 418 \r
 419                 query.bindValue(":longitude", lon);\r
 420                 query.bindValue(":latitude", lat);\r
 421                 query.bindValue(":altitude", alt);\r
 422                 query.bindValue(":guid", guid);\r
 423 \r
 424                 check = query.exec();\r
 425                 if (!check) {\r
 426                         logger.log(logger.EXTREME, "Update note geo tag has failed.");\r
 427                         logger.log(logger.MEDIUM, query.lastError());\r
 428                 }\r
 429                 \r
 430         }\r
 431         // Update a note's creation date\r
 432         public void updateNoteSourceUrl(String guid, String url) {\r
 433                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 434                 boolean check = query.prepare("Update Note set attributeSourceUrl=:url, isDirty=true where guid=:guid");\r
 435                 if (!check) {\r
 436                         logger.log(logger.EXTREME, "Update note url sql prepare has failed.");\r
 437                         logger.log(logger.MEDIUM, query.lastError());\r
 438                 }\r
 439                 \r
 440                 query.bindValue(":url", url);\r
 441                 query.bindValue(":guid", guid);\r
 442 \r
 443                 check = query.exec();\r
 444                 if (!check) {\r
 445                         logger.log(logger.EXTREME, "Update note url has failed.");\r
 446                         logger.log(logger.MEDIUM, query.lastError());\r
 447                 }\r
 448                 \r
 449         }\r
 450         // Update the notebook that a note is assigned to\r
 451         public void updateNoteNotebook(String guid, String notebookGuid, boolean expungeFromRemote) {\r
 452                 String currentNotebookGuid = new String("");\r
 453                 \r
 454                 \r
 455                 // If we are going from a synchronized notebook to a local notebook, we\r
 456                 // need to tell Evernote to purge the note online.  However, if this is  \r
 457                 // conflicting change we move it to the local notebook without deleting it \r
 458                 // or it would then delete the copy on the remote server.\r
 459                 NotebookTable notebookTable = new NotebookTable(logger, db);\r
 460                 DeletedTable deletedTable = new DeletedTable(logger, db);\r
 461                 if (expungeFromRemote) {\r
 462                         if (!notebookTable.isNotebookLocal(currentNotebookGuid) & notebookTable.isNotebookLocal(notebookGuid)) {\r
 463                                 deletedTable.addDeletedItem(guid, "NOTE");\r
 464                         }\r
 465                 }\r
 466                 \r
 467                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 468                 boolean check = query.prepare("Update Note set notebookGuid=:notebook, isDirty=true where guid=:guid");\r
 469                 if (!check) {\r
 470                         logger.log(logger.EXTREME, "Update note notebook sql prepare has failed.");\r
 471                         logger.log(logger.MEDIUM, query.lastError());\r
 472                 }\r
 473                 query.bindValue(":notebook", notebookGuid);\r
 474                 query.bindValue(":guid", guid);\r
 475                 \r
 476                 check = query.exec();\r
 477                 if (!check) {\r
 478                         logger.log(logger.EXTREME, "Update note notebook has failed.");\r
 479                         logger.log(logger.MEDIUM, query.lastError());\r
 480                 };\r
 481         }\r
 482         // Update a note's title\r
 483         public void updateNoteContent(String guid, String content) {\r
 484                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 485                 boolean check = query.prepare("Update Note set content=:content, updated=CURRENT_TIMESTAMP(), isDirty=true, indexNeeded=true " +\r
 486                                 " where guid=:guid");\r
 487                 if (!check) {\r
 488                         logger.log(logger.EXTREME, "Update note content sql prepare has failed.");\r
 489                         logger.log(logger.MEDIUM, query.lastError());\r
 490                 }\r
 491                 \r
 492                 query.bindValue(":content", content);\r
 493                 query.bindValue(":guid", guid);\r
 494 \r
 495                 check = query.exec();\r
 496                 if (!check) {\r
 497                         logger.log(logger.EXTREME, "Update note content has failed.");\r
 498                         logger.log(logger.MEDIUM, query.lastError());\r
 499                 }\r
 500         }\r
 501 \r
 502         // Delete a note\r
 503         public void deleteNote(String guid) {\r
 504         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 505         query.prepare("Update Note set deleted=CURRENT_TIMESTAMP(), active=false, isDirty=true where guid=:guid");\r
 506                 query.bindValue(":guid", guid);\r
 507                 if (!query.exec()) {\r
 508                         logger.log(logger.MEDIUM, "Note delete failed.");\r
 509                         logger.log(logger.MEDIUM, query.lastError());\r
 510                 }\r
 511         }\r
 512         public void restoreNote(String guid) {\r
 513         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 514                 query.prepare("Update Note set deleted='1969-12-31 19.00.00', active=true, isDirty=true where guid=:guid");\r
 515 //              query.prepare("Update Note set deleted=0, active=true, isDirty=true where guid=:guid");\r
 516                 query.bindValue(":guid", guid);\r
 517                 if (!query.exec()) {\r
 518                         logger.log(logger.MEDIUM, "Note restore failed.");\r
 519                         logger.log(logger.MEDIUM, query.lastError());\r
 520                 }\r
 521         }\r
 522         // Purge a note (actually delete it instead of just marking it deleted)\r
 523         public void expungeNote(String guid, boolean permanentExpunge, boolean needsSync) {\r
 524                 \r
 525                 if (!permanentExpunge) {\r
 526                         hideExpungedNote(guid, needsSync);\r
 527                         return;\r
 528                 }\r
 529                 \r
 530                 \r
 531         NSqlQuery note = new NSqlQuery(db.getConnection());\r
 532         NSqlQuery resources = new NSqlQuery(db.getConnection());\r
 533         NSqlQuery tags = new NSqlQuery(db.getConnection());\r
 534         NSqlQuery words = new NSqlQuery(db.getConnection());\r
 535         \r
 536         note.prepare("Delete from Note where guid=:guid");\r
 537                 resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
 538                 tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
 539                 words.prepare("Delete from words where guid=:guid");\r
 540 \r
 541                 note.bindValue(":guid", guid);\r
 542                 resources.bindValue(":guid", guid);\r
 543                 tags.bindValue(":guid", guid);\r
 544                 words.bindValue(":guid", guid);\r
 545         \r
 546                 // Start purging notes.\r
 547                 if (!note.exec()) {\r
 548                         logger.log(logger.MEDIUM, "Purge from note failed.");\r
 549                         logger.log(logger.MEDIUM, note.lastError());\r
 550                 }\r
 551                 if (!resources.exec()) {\r
 552                                 logger.log(logger.MEDIUM, "Purge from resources failed.");\r
 553                         logger.log(logger.MEDIUM, resources.lastError());\r
 554                 }\r
 555                 if (!tags.exec()) {\r
 556                         logger.log(logger.MEDIUM, "Note tags delete failed.");\r
 557                         logger.log(logger.MEDIUM, tags.lastError());\r
 558                 }\r
 559                 if (!words.exec()) {\r
 560                         logger.log(logger.MEDIUM, "Word delete failed.");\r
 561                         logger.log(logger.MEDIUM, words.lastError());\r
 562                 }\r
 563                 if (needsSync) {\r
 564                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 565                         deletedTable.addDeletedItem(guid, "Note");\r
 566                 }\r
 567 \r
 568         }\r
 569         // Purge a note (actually delete it instead of just marking it deleted)\r
 570         public void hideExpungedNote(String guid, boolean needsSync) {\r
 571         NSqlQuery note = new NSqlQuery(db.getConnection());\r
 572         NSqlQuery resources = new NSqlQuery(db.getConnection());\r
 573         NSqlQuery tags = new NSqlQuery(db.getConnection());\r
 574         NSqlQuery words = new NSqlQuery(db.getConnection());\r
 575         \r
 576         note.prepare("Update Note set isExpunged=true where guid=:guid");\r
 577                 resources.prepare("Delete from NoteResources where noteGuid=:guid");\r
 578                 tags.prepare("Delete from NoteTags where noteGuid=:guid");\r
 579                 words.prepare("Delete from words where guid=:guid");\r
 580 \r
 581                 note.bindValue(":guid", guid);\r
 582                 resources.bindValue(":guid", guid);\r
 583                 tags.bindValue(":guid", guid);\r
 584                 words.bindValue(":guid", guid);\r
 585 \r
 586                 // Start purging notes.\r
 587                 if (!note.exec()) {\r
 588                         logger.log(logger.MEDIUM, "Purge from note failed.");\r
 589                         logger.log(logger.MEDIUM, note.lastError());\r
 590                 }\r
 591                 if (!resources.exec()) {\r
 592                                 logger.log(logger.MEDIUM, "Purge from resources failed.");\r
 593                         logger.log(logger.MEDIUM, resources.lastError());\r
 594                 }\r
 595                 if (!tags.exec()) {\r
 596                         logger.log(logger.MEDIUM, "Note tags delete failed.");\r
 597                         logger.log(logger.MEDIUM, tags.lastError());\r
 598                 }\r
 599                 if (!words.exec()) {\r
 600                         logger.log(logger.MEDIUM, "Word delete failed.");\r
 601                         logger.log(logger.MEDIUM, words.lastError());\r
 602                 }\r
 603                 if (needsSync) {\r
 604                         DeletedTable deletedTable = new DeletedTable(logger, db);\r
 605                         deletedTable.addDeletedItem(guid, "Note");\r
 606                 }\r
 607         }\r
 608 \r
 609                 \r
 610         // Purge all deleted notes;\r
 611         public void expungeAllDeletedNotes() {\r
 612                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 613                 query.exec("select guid, updateSequenceNumber from note where active = false");\r
 614                 while (query.next()) {\r
 615                         String guid = query.valueString(0);\r
 616                         Integer usn = new Integer(query.valueString(1));\r
 617                         if (usn == 0)\r
 618                                 expungeNote(guid, true, false);\r
 619                         else\r
 620                                 expungeNote(guid, false, true);\r
 621                 }\r
 622         }\r
 623         // Update the note sequence number\r
 624         public void updateNoteSequence(String guid, int sequence) {\r
 625                 boolean check;\r
 626         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 627                 check = query.prepare("Update Note set updateSequenceNumber=:sequence where guid=:guid");\r
 628 \r
 629                 query.bindValue(":sequence", sequence);\r
 630                 query.bindValue(":guid", guid);\r
 631                 \r
 632                 query.exec();\r
 633                 if (!check) {\r
 634                         logger.log(logger.MEDIUM, "Note sequence update failed.");\r
 635                         logger.log(logger.MEDIUM, query.lastError());\r
 636                 } \r
 637         }\r
 638         // Update the note Guid\r
 639         public void updateNoteGuid(String oldGuid, String newGuid) {\r
 640                 boolean check;\r
 641         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 642                 query.prepare("Update Note set guid=:newGuid where guid=:oldGuid");\r
 643 \r
 644                 query.bindValue(":newGuid", newGuid);\r
 645                 query.bindValue(":oldGuid", oldGuid);\r
 646 \r
 647                 check = query.exec();\r
 648                 if (!check) {\r
 649                         logger.log(logger.MEDIUM, "Note Guid update failed.");\r
 650                         logger.log(logger.MEDIUM, query.lastError());\r
 651                 } \r
 652                 \r
 653                 query.prepare("Update NoteTags set noteGuid=:newGuid where noteGuid=:oldGuid");\r
 654                 query.bindValue(":newGuid", newGuid);\r
 655                 query.bindValue(":oldGuid", oldGuid);\r
 656                 check = query.exec();\r
 657                 if (!check) {\r
 658                         logger.log(logger.MEDIUM, "Note guid update failed for NoteTags.");\r
 659                         logger.log(logger.MEDIUM, query.lastError());\r
 660                 }\r
 661                 \r
 662                 query.prepare("Update words set guid=:newGuid where guid=:oldGuid");\r
 663                 query.bindValue(":newGuid", newGuid);\r
 664                 query.bindValue(":oldGuid", oldGuid);\r
 665                 query.exec();\r
 666                 if (!check) {\r
 667                         logger.log(logger.MEDIUM, "Note guid update failed for Words.");\r
 668                         logger.log(logger.MEDIUM, query.lastError());\r
 669                 }\r
 670                 query.prepare("Update noteresources set noteguid=:newGuid where noteguid=:oldGuid");\r
 671                 query.bindValue(":newGuid", newGuid);\r
 672                 query.bindValue(":oldGuid", oldGuid);\r
 673                 query.exec();\r
 674                 if (!check) {\r
 675                         logger.log(logger.MEDIUM, "Note guid update failed for noteresources.");\r
 676                         logger.log(logger.MEDIUM, query.lastError());\r
 677                 }\r
 678         }\r
 679         // Update a note\r
 680         public void updateNote(Note n, boolean isNew) {\r
 681                 boolean isExpunged = isNoteExpunged(n.getGuid());\r
 682                 int titleColor = getNoteTitleColor(n.getGuid());\r
 683                 expungeNote(n.getGuid(), !isExpunged, false);\r
 684                 addNote(n, false);\r
 685                 if (titleColor != -1)\r
 686                         setNoteTitleColor(n.getGuid(), titleColor);\r
 687         }\r
 688         // Does a note exist?\r
 689         public boolean exists(String guid) {\r
 690                 if (guid == null)\r
 691                         return false;\r
 692                 if (guid.trim().equals(""))\r
 693                         return false;\r
 694                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 695                 query.prepare("Select guid from note where guid=:guid");\r
 696                 query.bindValue(":guid", guid);\r
 697                 if (!query.exec())\r
 698                         logger.log(logger.EXTREME, "note.exists SQL retrieve has failed.");\r
 699                 boolean retVal = query.next();\r
 700                 return retVal;\r
 701         }\r
 702         // Does a note exist?\r
 703         public boolean isNoteExpunged(String guid) {\r
 704                 if (guid == null)\r
 705                         return false;\r
 706                 if (guid.trim().equals(""))\r
 707                         return false;\r
 708                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 709                 query.prepare("Select isExpunged from note where guid=:guid and isExpunged = true");\r
 710                 query.bindValue(":guid", guid);\r
 711                 if (!query.exec())\r
 712                         logger.log(logger.EXTREME, "note.isNoteExpunged SQL retrieve has failed.");\r
 713                 boolean retVal = query.next();\r
 714                 return retVal;\r
 715         }\r
 716         // This is a convience method to check if a tag exists & update/create based upon it\r
 717         public void syncNote(Note tag, boolean isDirty) {\r
 718                 if (exists(tag.getGuid()))\r
 719                         updateNote(tag, isDirty);\r
 720                 else\r
 721                         addNote(tag, isDirty);\r
 722         }\r
 723         // Get a list of notes that need to be updated\r
 724         public List <Note> getDirty() {\r
 725                 String guid;\r
 726                 Note tempNote;\r
 727                 List<Note> notes = new ArrayList<Note>();\r
 728                 List<String> index = new ArrayList<String>();\r
 729                 \r
 730                 boolean check;                  \r
 731         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 732                                         \r
 733                 check = query.exec("Select guid from Note where isDirty = true and isExpunged = false and notebookGuid not in (select guid from notebook where local = true)");\r
 734                 if (!check) \r
 735                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
 736                 \r
 737                 // Get a list of the notes\r
 738                 while (query.next()) {\r
 739                         guid = new String();\r
 740                         guid = query.valueString(0);\r
 741                         index.add(guid); \r
 742                 }       \r
 743                 \r
 744                 // Start getting notes\r
 745                 for (int i=0; i<index.size(); i++) {\r
 746                         tempNote = getNote(index.get(i), true,true,false,true,true);\r
 747                         notes.add(tempNote);\r
 748                 }\r
 749                 return notes;   \r
 750         }\r
 751         // Get a list of notes that need to be updated\r
 752         public boolean isNoteDirty(String guid) {\r
 753                 \r
 754                 boolean check;                  \r
 755         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 756                                         \r
 757                 check = query.prepare("Select guid from Note where isDirty = true and guid=:guid");\r
 758                 query.bindValue(":guid", guid);\r
 759                 check = query.exec();\r
 760                 if (!check) \r
 761                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
 762                 \r
 763                 boolean returnValue;\r
 764                 // Get a list of the notes\r
 765                 if (query.next()) \r
 766                         returnValue = true; \r
 767                 else\r
 768                         returnValue = false;\r
 769 \r
 770                 return returnValue;     \r
 771         }\r
 772         // Get a list of notes that need to be updated\r
 773         public List <String> getUnsynchronizedGUIDs() {\r
 774                 String guid;\r
 775                 List<String> index = new ArrayList<String>();\r
 776                 \r
 777                 boolean check;                  \r
 778         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 779                                         \r
 780                 check = query.exec("Select guid from Note where isDirty = true");\r
 781                 if (!check) \r
 782                         logger.log(logger.EXTREME, "Note SQL retrieve has failed: " +query.lastError().toString());\r
 783                 \r
 784                 // Get a list of the notes\r
 785                 while (query.next()) {\r
 786                         guid = new String();\r
 787                         guid = query.valueString(0);\r
 788                         index.add(guid); \r
 789                 }       \r
 790                 return index;   \r
 791         }\r
 792         // Reset the dirty bit\r
 793         public void  resetDirtyFlag(String guid) {\r
 794                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 795                 \r
 796                 query.prepare("Update note set isdirty=false where guid=:guid");\r
 797                 query.bindValue(":guid", guid);\r
 798                 if (!query.exec())\r
 799                         logger.log(logger.EXTREME, "Error resetting note dirty field.");\r
 800         }\r
 801         // Get all notes\r
 802         public List<String> getAllGuids() {\r
 803                 List<String> notes = new ArrayList<String>();\r
 804                 \r
 805                 boolean check;                                  \r
 806         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 807                                         \r
 808                 check = query.exec("Select guid from Note");\r
 809                 if (!check)\r
 810                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
 811 \r
 812                 // Get a list of the notes\r
 813                 while (query.next()) {\r
 814                         notes.add(new String(query.valueString(0))); \r
 815                 }\r
 816                 return notes;\r
 817         }\r
 818         // Get all notes\r
 819         public List<Note> getAllNotes() {\r
 820                 List<Note> notes = new ArrayList<Note>();\r
 821                 prepareQueries();\r
 822                 boolean check;                                  \r
 823         NSqlQuery query = getAllQueryWithoutContent;\r
 824                 check = query.exec();\r
 825                 if (!check)\r
 826                         logger.log(logger.EXTREME, "Notebook SQL retrieve has failed: "+query.lastError());\r
 827                 // Get a list of the notes\r
 828                 while (query.next()) {\r
 829                         notes.add(mapNoteFromQuery(query, false, false, false, false, true));\r
 830                 }\r
 831                 return notes;\r
 832         }\r
 833         // Count unindexed notes\r
 834         public int getUnindexedCount() {\r
 835         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 836                 query.exec("select count(*) from note where indexneeded=true and isExpunged = false");\r
 837                 query.next(); \r
 838                 int returnValue = new Integer(query.valueString(0));\r
 839                 return returnValue;\r
 840         }\r
 841         // Count unsynchronized notes\r
 842         public int getDirtyCount() {\r
 843         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 844                 query.exec("select count(*) from note where isDirty=true and isExpunged = false");\r
 845                 query.next(); \r
 846                 int returnValue = new Integer(query.valueString(0));\r
 847                 return returnValue;\r
 848         }\r
 849         // Count notes\r
 850         public int getNoteCount() {\r
 851         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 852                 query.exec("select count(*) from note where isExpunged = false");\r
 853                 query.next(); \r
 854                 int returnValue = new Integer(query.valueString(0));\r
 855                 return returnValue;\r
 856         }\r
 857         // Count deleted notes\r
 858         public int getDeletedCount() {\r
 859         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 860                 query.exec("select count(*) from note where isExpunged = false and active = false");\r
 861                 if (!query.next()) \r
 862                         return 0;\r
 863                 int returnValue = new Integer(query.valueString(0));\r
 864                 return returnValue;\r
 865         }\r
 866         // Reset a note sequence number to zero.  This is useful for moving conflicting notes\r
 867         public void resetNoteSequence(String guid) {\r
 868                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 869                 boolean check = query.prepare("Update Note set updateSequenceNumber=0, isDirty=true where guid=:guid");\r
 870                 if (!check) {\r
 871                         logger.log(logger.EXTREME, "Update note ResetSequence sql prepare has failed.");\r
 872                         logger.log(logger.MEDIUM, query.lastError());\r
 873                 }\r
 874                 query.bindValue(":guid", guid);\r
 875                 check = query.exec();\r
 876                 if (!check) {\r
 877                         logger.log(logger.EXTREME, "Update note sequence number has failed.");\r
 878                         logger.log(logger.MEDIUM, query.lastError());\r
 879                 }\r
 880         }\r
 881         \r
 882         \r
 883         // Update a note resource by the hash\r
 884         public void updateNoteResourceGuidbyHash(String noteGuid, String resGuid, String hash) {\r
 885                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 886 /*              query.prepare("Select guid from NoteResources where noteGuid=:noteGuid and datahash=:hex");\r
 887                 query.bindValue(":noteGuid", noteGuid);\r
 888                 query.bindValue(":hex", hash);\r
 889                 query.exec();\r
 890                 if (!query.next()) {\r
 891                         logger.log(logger.LOW, "Error finding note resource in RNoteTable.updateNoteResourceGuidbyHash.  GUID="+noteGuid +" resGuid="+ resGuid+" hash="+hash);\r
 892                         return;\r
 893                 }\r
 894                 String guid = query.valueString(0);\r
 895 */              \r
 896                 query.prepare("update noteresources set guid=:guid where noteGuid=:noteGuid and datahash=:hex");\r
 897                 query.bindValue(":guid", resGuid);\r
 898                 query.bindValue(":noteGuid", noteGuid);\r
 899                 query.bindValue(":hex", hash);\r
 900                 if (!query.exec()) {\r
 901                         logger.log(logger.EXTREME, "Note Resource Update by Hash failed");\r
 902                         logger.log(logger.EXTREME, query.lastError().toString());\r
 903                 }\r
 904         }\r
 905 \r
 906         // Fix CRLF problem that is on some notes\r
 907         private String fixCarriageReturn(String note) {\r
 908                 if (note == null || !Global.enableCarriageReturnFix)\r
 909                         return note;\r
 910                 QByteArray a0Hex = new QByteArray("a0");\r
 911                 String a0 = QByteArray.fromHex(a0Hex).toString();\r
 912                 note = note.replace("<div>"+a0+"</div>", "<div>&nbsp;</div>");\r
 913                 return note.replace("<div/>", "<div>&nbsp;</div>");\r
 914         }\r
 915         \r
 916         \r
 917         \r
 918         //********************************************************************************\r
 919         //********************************************************************************\r
 920         //* Indexing Functions\r
 921         //********************************************************************************\r
 922         //********************************************************************************\r
 923         // set/unset a note to be reindexed\r
 924         public void setIndexNeeded(String guid, Boolean flag) {\r
 925                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 926                 query.prepare("Update Note set indexNeeded=:flag where guid=:guid");\r
 927 \r
 928                 if (flag)\r
 929                         query.bindValue(":flag", 1);\r
 930                 else\r
 931                         query.bindValue(":flag", 0);\r
 932                 query.bindValue(":guid", guid);\r
 933                 if (!query.exec()) {\r
 934                         logger.log(logger.MEDIUM, "Note indexNeeded update failed.");\r
 935                         logger.log(logger.MEDIUM, query.lastError());\r
 936                 } \r
 937         }\r
 938         // Set all notes to be reindexed\r
 939         public void reindexAllNotes() {\r
 940                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
 941                 if (!query.exec("Update Note set indexNeeded=true")) {\r
 942                         logger.log(logger.MEDIUM, "Note reindexAllNotes update failed.");\r
 943                         logger.log(logger.MEDIUM, query.lastError());\r
 944                 } \r
 945         }\r
 946 \r
 947         // Get all unindexed notes\r
 948         public List <String> getUnindexed() {\r
 949                 String guid;\r
 950                 List<String> index = new ArrayList<String>();\r
 951         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 952                                         \r
 953                 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',updated,CURRENT_TIMESTAMP)>5"))\r
 954                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
 955 \r
 956                 // Get a list of the notes\r
 957                 while (query.next()) {\r
 958                         guid = new String();\r
 959                         guid = query.valueString(0);\r
 960                         index.add(guid); \r
 961                 }       \r
 962                 return index;   \r
 963         }\r
 964         public List<String> getNextUnindexed(int limit) {\r
 965                 List<String> guids = new ArrayList<String>();\r
 966                         \r
 967         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 968                                         \r
 969                 if (!query.exec("Select guid from Note where isExpunged = false and indexNeeded = true and DATEDIFF('MINUTE',Updated,CURRENT_TIMESTAMP)>5 limit " +limit))\r
 970                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
 971                 \r
 972                 // Get a list of the notes\r
 973                 String guid;\r
 974                 while (query.next()) {\r
 975                         guid = new String();\r
 976                         guid = query.valueString(0);\r
 977                         guids.add(guid);\r
 978                 }       \r
 979                 return guids;   \r
 980         }\r
 981         \r
 982         \r
 983         //**********************************************************************************\r
 984         //* Title color functions\r
 985         //**********************************************************************************\r
 986         // Get the title color of all notes\r
 987         public List<Pair<String, Integer>> getNoteTitleColors() {\r
 988                 List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
 989         NSqlQuery query = new NSqlQuery(db.getConnection());\r
 990                 \r
 991                 if (!query.exec("Select guid,titleColor from Note where titleColor != -1"))\r
 992                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getUnindexed().");\r
 993 \r
 994                 String guid;\r
 995                 Integer color;\r
 996                 \r
 997                 // Get a list of the notes\r
 998                 while (query.next()) {\r
 999                         Pair<String, Integer> pair = new Pair<String,Integer>();\r
1000                         guid = query.valueString(0);\r
1001                         color = query.valueInteger(1);\r
1002                         pair.setFirst(guid);\r
1003                         pair.setSecond(color);\r
1004                         returnValue.add(pair); \r
1005                 }       \r
1006 \r
1007                 return returnValue;\r
1008         }\r
1009         // Set a title color\r
1010         public void  setNoteTitleColor(String guid, int color) {\r
1011                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1012                 \r
1013                 query.prepare("Update note set titlecolor=:color where guid=:guid");\r
1014                 query.bindValue(":guid", guid);\r
1015                 query.bindValue(":color", color);\r
1016                 if (!query.exec())\r
1017                         logger.log(logger.EXTREME, "Error updating title color.");\r
1018         }\r
1019         // Get in individual note's title color\r
1020         // Get the title color of all notes\r
1021         public Integer getNoteTitleColor(String guid) {\r
1022                 List<Pair<String,Integer>> returnValue = new ArrayList<Pair<String,Integer>>();\r
1023         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1024                 \r
1025                 if (!query.exec("Select titleColor from Note where titleColor != -1 and guid=:guid"))\r
1026                         logger.log(logger.EXTREME, "Note SQL retrieve has failed on getNoteTitleColor(guid).");\r
1027 \r
1028                 Integer color = -1;\r
1029                 \r
1030                 // Get a list of the notes\r
1031                 while (query.next()) {\r
1032                         Pair<String, Integer> pair = new Pair<String,Integer>();\r
1033                         guid = query.valueString(0);\r
1034                         color = query.valueInteger(1);\r
1035                         pair.setFirst(guid);\r
1036                         pair.setSecond(color);\r
1037                         returnValue.add(pair); \r
1038                 }       \r
1039 \r
1040                 \r
1041                 return color;\r
1042         }\r
1043         \r
1044         \r
1045         //**********************************************************************************\r
1046         //* Thumbnail functions\r
1047         //**********************************************************************************\r
1048         // Set if a new thumbnail is needed\r
1049         public void setThumbnailNeeded(String guid, boolean needed) {\r
1050                 \r
1051                 boolean check;                  \r
1052         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1053                                         \r
1054                 check = query.prepare("Update note set thumbnailneeded = :needed where guid=:guid");\r
1055                 query.bindValue(":guid", guid);\r
1056                 query.bindValue(":needed", needed);\r
1057                 check = query.exec();\r
1058                 if (!check) \r
1059                         logger.log(logger.EXTREME, "Note SQL set thumbail needed failed: " +query.lastError().toString());\r
1060 \r
1061         }\r
1062         // Is a thumbail needed for this guid?\r
1063         public boolean isThumbnailNeeded(String guid) {\r
1064                 \r
1065                 boolean check;                  \r
1066         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1067                                         \r
1068                 check = query.prepare("select thumbnailneeded from note where guid=:guid");\r
1069                 query.bindValue(":guid", guid);\r
1070                 check = query.exec();\r
1071                 if (!check) \r
1072                         logger.log(logger.EXTREME, "Note SQL isThumbnailNeeded query failed: " +query.lastError().toString());\r
1073                 \r
1074                 boolean returnValue;\r
1075                 // Get a list of the notes\r
1076                 if (query.next()) \r
1077                         returnValue = query.valueBoolean(0, false); \r
1078                 else\r
1079                         returnValue = false;\r
1080 \r
1081                 return returnValue;     \r
1082         }\r
1083         // Set if a new thumbnail is needed\r
1084         public void setThumbnail(String guid, QByteArray thumbnail) {\r
1085                 \r
1086                 boolean check;                  \r
1087         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1088                                         \r
1089                 check = query.prepare("Update note set thumbnail = :thumbnail where guid=:guid");\r
1090                 query.bindValue(":guid", guid);\r
1091                 query.bindValue(":thumbnail", thumbnail.toByteArray());\r
1092                 check = query.exec();\r
1093                 if (!check) \r
1094                         logger.log(logger.EXTREME, "Note SQL set thumbail failed: " +query.lastError().toString());\r
1095 \r
1096         }\r
1097         // Set if a new thumbnail is needed\r
1098         public QByteArray getThumbnail(String guid) {\r
1099                 \r
1100                 boolean check;                  \r
1101         NSqlQuery query = new NSqlQuery(db.getConnection());\r
1102                                         \r
1103                 check = query.prepare("Select thumbnail from note where guid=:guid");\r
1104                 query.bindValue(":guid", guid);\r
1105                 check = query.exec();\r
1106                 if (!check) \r
1107                         logger.log(logger.EXTREME, "Note SQL get thumbail failed: " +query.lastError().toString());\r
1108                 // Get a list of the notes\r
1109                 if (query.next()) \r
1110                         if (query.getBlob(0) != null)\r
1111                                 return new QByteArray(query.getBlob(0)); \r
1112                 return null;\r
1113         }\r
1114         \r
1115         \r
1116         // Update a note content's hash.  This happens if a resource is edited outside of NN\r
1117         public void updateResourceContentHash(String guid, String oldHash, String newHash) {\r
1118                 Note n = getNote(guid, true, false, false, false,false);\r
1119                 int position = n.getContent().indexOf("<en-media");\r
1120                 int endPos;\r
1121                 for (;position>-1;) {\r
1122                         endPos = n.getContent().indexOf(">", position+1);\r
1123                         String oldSegment = n.getContent().substring(position,endPos);\r
1124                         int hashPos = oldSegment.indexOf("hash=\"");\r
1125                         int hashEnd = oldSegment.indexOf("\"", hashPos+7);\r
1126                         String hash = oldSegment.substring(hashPos+6, hashEnd);\r
1127                         if (hash.equalsIgnoreCase(oldHash)) {\r
1128                                 String newSegment = oldSegment.replace(oldHash, newHash);\r
1129                                 String content = n.getContent().substring(0,position) +\r
1130                                                  newSegment +\r
1131                                                  n.getContent().substring(endPos);\r
1132                                 NSqlQuery query = new NSqlQuery(db.getConnection());\r
1133                                 query.prepare("update note set isdirty=true, content=:content where guid=:guid");\r
1134                                 query.bindValue(":content", content);\r
1135                                 query.bindValue(":guid", n.getGuid());\r
1136                                 query.exec();\r
1137                         }\r
1138                         \r
1139                         position = n.getContent().indexOf("<en-media", position+1);\r
1140                 }\r
1141         }\r
1142 }       \r