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

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