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

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