OSDN Git Service

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