src/cx/fbn/nevernote/sql/NoteTable.java

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