2 * This file is part of NixNote
\r
3 * Copyright 2009 Randy Baumgarte
\r
5 * This file may be licensed under the terms of of the
\r
6 * GNU General Public License Version 2 (the ``GPL'').
\r
8 * Software distributed under the License is distributed
\r
9 * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either
\r
10 * express or implied. See the GPL for the specific language
\r
11 * governing rights and limitations.
\r
13 * You should have received a copy of the GPL along with this
\r
14 * program. If not, go to http://www.gnu.org/licenses/gpl.html
\r
15 * or write to the Free Software Foundation, Inc.,
\r
16 * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
\r
21 package cx.fbn.nevernote.sql;
\r
23 import java.text.DateFormat;
\r
24 import java.text.ParseException;
\r
25 import java.text.SimpleDateFormat;
\r
26 import java.util.ArrayList;
\r
27 import java.util.HashMap;
\r
28 import java.util.List;
\r
30 import com.evernote.edam.type.NoteSortOrder;
\r
31 import com.evernote.edam.type.Notebook;
\r
32 import com.evernote.edam.type.Publishing;
\r
33 import com.trolltech.qt.core.QBuffer;
\r
34 import com.trolltech.qt.core.QByteArray;
\r
35 import com.trolltech.qt.core.QIODevice;
\r
36 import com.trolltech.qt.gui.QIcon;
\r
37 import com.trolltech.qt.gui.QImage;
\r
38 import com.trolltech.qt.gui.QPixmap;
\r
40 import cx.fbn.nevernote.Global;
\r
41 import cx.fbn.nevernote.sql.driver.NSqlQuery;
\r
42 import cx.fbn.nevernote.utilities.ApplicationLogger;
\r
43 import cx.fbn.nevernote.utilities.Pair;
\r
45 public class NotebookTable {
\r
47 private final ApplicationLogger logger;
\r
48 DatabaseConnection db;
\r
49 private final String dbName;
\r
50 NSqlQuery notebookCountQuery;
\r
53 public NotebookTable(ApplicationLogger l, DatabaseConnection d) {
\r
56 dbName = "Notebook";
\r
59 public NotebookTable(ApplicationLogger l, DatabaseConnection d, String name) {
\r
66 public void createTable(boolean addDefaulte) {
\r
67 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
68 logger.log(logger.HIGH, "Creating table "+dbName+"...");
\r
69 if (!query.exec("Create table "+dbName+" (guid varchar primary key, " +
\r
70 "sequence integer, " +
\r
72 "defaultNotebook varchar, "+
\r
73 "serviceCreated timestamp, " +
\r
74 "serviceUpdated timestamp, "+
\r
75 "published boolean, "+
\r
76 "isDirty boolean, "+
\r
77 "autoEncrypt boolean, "+
\r
79 "archived boolean)"))
\r
80 logger.log(logger.HIGH, "Table "+dbName+" creation FAILED!!!");
\r
81 Notebook newnote = new Notebook();
\r
82 newnote.setDefaultNotebook(true);
\r
83 newnote.setName("My Notebook");
\r
84 newnote.setPublished(false);
\r
85 newnote.setGuid("1");
\r
87 // Setup an initial notebook
\r
88 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
89 query = new NSqlQuery(db.getConnection());
\r
90 query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "
\r
91 +"serviceCreated, serviceUpdated, published, "
\r
92 + "isDirty, autoEncrypt, "
\r
93 + "local, archived) Values("
\r
94 +":guid, :sequence, :name, :defaultNotebook, "
\r
95 +":serviceCreated, :serviceUpdated, :published, "
\r
96 +":isDirty, :autoEncrypt, "
\r
98 query.bindValue(":guid", newnote.getGuid());
\r
99 query.bindValue(":sequence", newnote.getUpdateSequenceNum());
\r
100 query.bindValue(":name", newnote.getName());
\r
101 query.bindValue(":defaultNotebook", newnote.isDefaultNotebook());
\r
103 StringBuilder serviceCreated = new StringBuilder(simple.format(newnote.getServiceCreated()));
\r
104 StringBuilder serviceUpdated = new StringBuilder(simple.format(newnote.getServiceUpdated()));
\r
105 if (serviceUpdated.toString() == null)
\r
106 serviceUpdated = serviceCreated;
\r
107 query.bindValue(":serviceCreated", serviceCreated.toString());
\r
108 query.bindValue(":serviceUpdated", serviceCreated.toString());
\r
109 query.bindValue(":published",newnote.isPublished());
\r
111 query.bindValue(":isDirty", true);
\r
112 query.bindValue(":autoEncrypt", false);
\r
113 query.bindValue(":local", false);
\r
115 boolean check = query.exec();
\r
117 logger.log(logger.MEDIUM, "Initial "+dbName+" Table insert failed.");
\r
118 logger.log(logger.MEDIUM, query.lastError().toString());
\r
124 public void dropTable() {
\r
125 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
126 query.exec("Drop table "+dbName);
\r
128 // Save an individual notebook
\r
129 public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {
\r
130 addNotebook(tempNotebook, isDirty, local, false, false);
\r
132 // Save an individual notebook
\r
133 public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local, boolean linked, boolean readOnly) {
\r
136 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
137 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
138 check = query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "
\r
139 +"serviceCreated, serviceUpdated, published, "
\r
140 + "publishingUri, publishingOrder, publishingAscending, publishingPublicDescription, "
\r
141 + "isDirty, autoEncrypt, stack, "
\r
142 + "local, archived, readOnly, linked) Values("
\r
143 +":guid, :sequence, :name, :defaultNotebook, "
\r
144 +":serviceCreated, :serviceUpdated, :published, "
\r
145 +":publishingUri, :publishingOrder, :publishingAscending, :publishingPublicDescription, "
\r
146 +":isDirty, :autoEncrypt, "
\r
147 +":stack, :local, false, :readOnly, :linked)");
\r
148 query.bindValue(":guid", tempNotebook.getGuid());
\r
149 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());
\r
150 query.bindValue(":name", tempNotebook.getName());
\r
151 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());
\r
153 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));
\r
154 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));
\r
155 if (serviceUpdated.toString() == null)
\r
156 serviceUpdated = serviceCreated;
\r
157 query.bindValue(":serviceCreated", serviceCreated.toString());
\r
158 query.bindValue(":serviceUpdated", serviceCreated.toString());
\r
159 query.bindValue(":published",tempNotebook.isPublished());
\r
160 query.bindValue(":linked", linked);
\r
161 query.bindValue(":readOnly", readOnly);
\r
163 if (tempNotebook.isPublished() && tempNotebook.getPublishing() != null) {
\r
164 Publishing p = tempNotebook.getPublishing();
\r
165 query.bindValue(":publishingUri", p.getUri());
\r
166 query.bindValue(":publishingOrder", p.getOrder().getValue());
\r
167 query.bindValue(":publishingAscending", p.isAscending());
\r
168 query.bindValue(":publishingPublicDescription", p.getPublicDescription());
\r
170 query.bindValue(":publishingUri", "");
\r
171 query.bindValue(":publishingOrder", 1);
\r
172 query.bindValue(":publishingAscending", 1);
\r
173 query.bindValue(":publishingPublicDescription", "");
\r
177 query.bindValue(":isDirty", true);
\r
179 query.bindValue(":isDirty", false);
\r
180 query.bindValue(":autoEncrypt", false);
\r
181 query.bindValue(":local", local);
\r
182 query.bindValue(":stack", tempNotebook.getStack());
\r
184 check = query.exec();
\r
186 logger.log(logger.MEDIUM, ""+dbName+" Table insert failed.");
\r
187 logger.log(logger.MEDIUM, query.lastError().toString());
\r
190 // Delete the notebook based on a guid
\r
191 public void expungeNotebook(String guid, boolean needsSync) {
\r
194 n = getNotebook(guid);
\r
195 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
197 check = query.prepare("delete from "+dbName+" where guid=:guid");
\r
199 logger.log(logger.EXTREME, dbName+" SQL delete prepare has failed.");
\r
200 logger.log(logger.EXTREME, query.lastError().toString());
\r
202 query.bindValue(":guid", guid);
\r
203 check = query.exec();
\r
205 logger.log(logger.MEDIUM, dbName+" delete failed.");
\r
207 // Signal the parent that work needs to be done
\r
208 if (needsSync && n!=null && n.getUpdateSequenceNum() > 0) {
\r
209 DeletedTable deletedTable = new DeletedTable(logger, db);
\r
210 deletedTable.addDeletedItem(guid, dbName);
\r
213 // Update a notebook
\r
214 public void updateNotebook(Notebook tempNotebook, boolean isDirty) {
\r
217 SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
219 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
220 check = query.prepare("Update "+dbName+" set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +
\r
221 "serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+
\r
222 "published=:published, isDirty=:isDirty, publishinguri=:uri, "+
\r
223 "publishingOrder=:order, " +
\r
224 "publishingAscending=:ascending, " +
\r
225 "publishingPublicDescription=:desc, " +
\r
227 "where guid=:guid ");
\r
229 query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());
\r
230 query.bindValue(":name", tempNotebook.getName());
\r
231 query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());
\r
233 StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));
\r
234 StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));
\r
235 query.bindValue(":serviceCreated", serviceCreated.toString());
\r
236 query.bindValue(":serviceUpdated", serviceUpdated.toString());
\r
238 query.bindValue(":published", tempNotebook.isPublished());
\r
239 query.bindValue(":isDirty", isDirty);
\r
241 if (tempNotebook.isPublished()) {
\r
242 query.bindValue(":uri", tempNotebook.getPublishing().getUri());
\r
243 query.bindValue(":order", tempNotebook.getPublishing().getOrder().getValue());
\r
244 query.bindValue(":ascending", tempNotebook.getPublishing().isAscending());
\r
245 query.bindValue(":desc", tempNotebook.getPublishing().getPublicDescription());
\r
247 query.bindValue(":uri", "");
\r
248 query.bindValue(":order", NoteSortOrder.CREATED.getValue());
\r
249 query.bindValue(":ascending", false);
\r
250 query.bindValue(":desc", "");
\r
253 query.bindValue(":guid", tempNotebook.getGuid());
\r
254 query.bindValue(":stack", tempNotebook.getStack());
\r
256 check = query.exec();
\r
258 logger.log(logger.MEDIUM, dbName+" Table update failed.");
\r
259 logger.log(logger.MEDIUM, query.lastError().toString());
\r
262 // Load notebooks from the database
\r
263 public List<Notebook> getAll() {
\r
264 Notebook tempNotebook;
\r
265 List<Notebook> index = new ArrayList<Notebook>();
\r
268 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
270 check = query.exec("Select guid, sequence, name, defaultNotebook, " +
\r
271 "serviceCreated, "+
\r
272 "serviceUpdated, "+
\r
273 "published, stack, publishinguri, publishingascending, publishingPublicDescription, "+
\r
274 "publishingOrder from "+dbName+" order by name");
\r
276 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
277 while (query.next()) {
\r
278 tempNotebook = new Notebook();
\r
279 tempNotebook.setGuid(query.valueString(0));
\r
280 int sequence = new Integer(query.valueString(1)).intValue();
\r
281 tempNotebook.setUpdateSequenceNum(sequence);
\r
282 tempNotebook.setName(query.valueString(2));
\r
283 tempNotebook.setDefaultNotebook(query.valueBoolean(3, false));
\r
284 DateFormat indfm = null;
\r
286 indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
287 // indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
\r
288 } catch (Exception e) { }
\r
290 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
\r
291 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
\r
292 } catch (ParseException e) {
\r
293 e.printStackTrace();
\r
295 tempNotebook.setPublished(new Boolean(query.valueString(6)));
\r
296 tempNotebook.setStack(query.valueString(7));
\r
297 if (tempNotebook.isPublished()) {
\r
298 Publishing p = new Publishing();
\r
299 p.setUri(query.valueString(8));
\r
300 p.setAscending(query.valueBoolean(9, false));
\r
301 p.setPublicDescription(query.valueString(10));
\r
302 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
\r
303 tempNotebook.setPublishing(p);
\r
305 index.add(tempNotebook);
\r
309 public List<Notebook> getAllLocal() {
\r
310 Notebook tempNotebook;
\r
311 List<Notebook> index = new ArrayList<Notebook>();
\r
314 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
316 check = query.exec("Select guid, sequence, name, defaultNotebook, " +
\r
317 "serviceCreated, serviceUpdated, published, stack from "+dbName+" where local=true order by name");
\r
319 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
320 while (query.next()) {
\r
321 tempNotebook = new Notebook();
\r
322 tempNotebook.setGuid(query.valueString(0));
\r
323 int sequence = new Integer(query.valueString(1)).intValue();
\r
324 tempNotebook.setUpdateSequenceNum(sequence);
\r
325 tempNotebook.setName(query.valueString(2));
\r
327 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
328 // indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
\r
330 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
\r
331 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
\r
332 } catch (ParseException e) {
\r
333 e.printStackTrace();
\r
335 tempNotebook.setStack(query.valueString(7));
\r
336 index.add(tempNotebook);
\r
340 // Archive or un-archive a notebook
\r
341 public void setArchived(String guid, boolean val) {
\r
343 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
344 check = query.prepare("Update "+dbName+" set archived=:archived where guid=:guid");
\r
346 logger.log(logger.EXTREME, dbName+" SQL archive update has failed.");
\r
347 query.bindValue(":guid", guid);
\r
348 query.bindValue(":archived", val);
\r
351 // Load non-archived notebooks from the database
\r
352 public List<Notebook> getAllArchived() {
\r
353 Notebook tempNotebook;
\r
354 List<Notebook> index = new ArrayList<Notebook>();
\r
357 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
359 check = query.exec("Select guid, sequence, name, defaultNotebook, " +
\r
360 "serviceCreated, serviceUpdated, published, stack, "+
\r
361 "publishinguri, publishingascending, publishingPublicDescription, "+
\r
362 "publishingOrder " +
\r
363 "from "+dbName+" where archived=true order by name");
\r
365 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
366 while (query.next()) {
\r
367 tempNotebook = new Notebook();
\r
368 tempNotebook.setGuid(query.valueString(0));
\r
369 int sequence = new Integer(query.valueString(1)).intValue();
\r
370 tempNotebook.setUpdateSequenceNum(sequence);
\r
371 tempNotebook.setName(query.valueString(2));
\r
373 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
374 // indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
\r
376 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
\r
377 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
\r
378 } catch (ParseException e) {
\r
379 e.printStackTrace();
\r
381 tempNotebook.setPublished(new Boolean(query.valueString(6)));
\r
382 tempNotebook.setStack(query.valueString(7));
\r
384 if (tempNotebook.isPublished()) {
\r
385 Publishing p = new Publishing();
\r
386 p.setUri(query.valueString(8));
\r
387 p.setAscending(query.valueBoolean(9, false));
\r
388 p.setPublicDescription(query.valueString(10));
\r
389 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
\r
390 tempNotebook.setPublishing(p);
\r
393 index.add(tempNotebook);
\r
397 // Check for a local/remote notebook
\r
398 public boolean isNotebookLocal(String guid) {
\r
399 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
401 query.prepare("Select local from "+dbName+" where guid=:guid");
\r
402 query.bindValue(":guid", guid);
\r
404 if (!query.next()) {
\r
407 boolean returnValue = query.valueBoolean(0, false);
\r
408 return returnValue;
\r
410 // Check for a local/remote notebook
\r
411 public boolean isNotebookLinked(String guid) {
\r
412 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
414 query.prepare("Select linked from "+dbName+" where guid=:guid");
\r
415 query.bindValue(":guid", guid);
\r
417 if (!query.next()) {
\r
420 boolean returnValue = query.valueBoolean(0, false);
\r
421 return returnValue;
\r
423 public boolean isReadOnly(String guid) {
\r
424 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
426 query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly=true");
\r
427 query.bindValue(":guid", guid);
\r
429 if (!query.next()) {
\r
432 boolean returnValue = query.valueBoolean(0, false);
\r
433 return returnValue;
\r
435 // Update a notebook sequence number
\r
436 public void updateNotebookSequence(String guid, int sequence) {
\r
438 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
439 check = query.prepare("Update "+dbName+" set sequence=:sequence where guid=:guid");
\r
440 query.bindValue(":guid", guid);
\r
441 query.bindValue(":sequence", sequence);
\r
444 logger.log(logger.MEDIUM, dbName+" sequence update failed.");
\r
445 logger.log(logger.MEDIUM, query.lastError());
\r
448 // Update a notebook GUID number
\r
449 public void updateNotebookGuid(String oldGuid, String newGuid) {
\r
450 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
451 query.prepare("Update "+dbName+" set guid=:newGuid where guid=:oldGuid");
\r
452 query.bindValue(":oldGuid", oldGuid);
\r
453 query.bindValue(":newGuid", newGuid);
\r
454 if (!query.exec()) {
\r
455 logger.log(logger.MEDIUM, dbName+" guid update failed.");
\r
456 logger.log(logger.MEDIUM, query.lastError());
\r
459 // Update any notes containing the notebook guid
\r
460 query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");
\r
461 query.bindValue(":oldGuid", oldGuid);
\r
462 query.bindValue(":newGuid", newGuid);
\r
463 if (!query.exec()) {
\r
464 logger.log(logger.MEDIUM, dbName+" guid update for note failed.");
\r
465 logger.log(logger.MEDIUM, query.lastError());
\r
468 // Update any watch folders with the new guid
\r
469 query = new NSqlQuery(db.getConnection());
\r
470 query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");
\r
471 query.bindValue(":oldGuid", oldGuid);
\r
472 query.bindValue(":newGuid", newGuid);
\r
473 if (!query.exec()) {
\r
474 logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");
\r
475 logger.log(logger.MEDIUM, query.lastError().toString());
\r
478 // Get a list of notes that need to be updated
\r
479 public List <Notebook> getDirty() {
\r
480 Notebook tempNotebook;
\r
481 List<Notebook> index = new ArrayList<Notebook>();
\r
484 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
486 check = query.exec("Select guid, sequence, name, defaultNotebook, " +
\r
487 "serviceCreated, serviceUpdated, published, stack, "+
\r
488 "publishinguri, publishingascending, publishingPublicDescription, "+
\r
489 "publishingOrder " +
\r
490 "from "+dbName+" where isDirty=true and local=false and linked=false");
\r
492 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
493 while (query.next()) {
\r
494 tempNotebook = new Notebook();
\r
495 tempNotebook.setGuid(query.valueString(0));
\r
496 int sequence = new Integer(query.valueString(1)).intValue();
\r
497 tempNotebook.setUpdateSequenceNum(sequence);
\r
498 tempNotebook.setName(query.valueString(2));
\r
500 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
502 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
\r
503 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
\r
504 } catch (ParseException e) {
\r
505 e.printStackTrace();
\r
507 tempNotebook.setPublished(new Boolean(query.valueString(6)));
\r
508 tempNotebook.setStack(query.valueString(7));
\r
510 if (tempNotebook.isPublished()) {
\r
511 Publishing p = new Publishing();
\r
512 p.setUri(query.valueString(8));
\r
513 p.setAscending(query.valueBoolean(9, false));
\r
514 p.setPublicDescription(query.valueString(10));
\r
515 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
\r
516 if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))
\r
517 p.setPublicDescription(null);
\r
518 tempNotebook.setPublishing(p);
\r
521 index.add(tempNotebook);
\r
525 // Get a list of notes that need to be updated
\r
526 public Notebook getNotebook(String guid) {
\r
527 Notebook tempNotebook;
\r
530 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
532 query.prepare("Select guid, sequence, name, defaultNotebook, " +
\r
533 "serviceCreated, serviceUpdated, published, stack, "+
\r
534 "publishinguri, publishingascending, publishingPublicDescription, "+
\r
535 "publishingOrder " +
\r
536 "from "+dbName+" where guid=:guid");
\r
537 query.bindValue(":guid", guid);
\r
538 check = query.exec();
\r
540 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
541 while (query.next()) {
\r
542 tempNotebook = new Notebook();
\r
543 tempNotebook.setGuid(query.valueString(0));
\r
544 int sequence = new Integer(query.valueString(1)).intValue();
\r
545 tempNotebook.setUpdateSequenceNum(sequence);
\r
546 tempNotebook.setName(query.valueString(2));
\r
548 DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
\r
550 tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
\r
551 tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
\r
552 } catch (ParseException e) {
\r
553 e.printStackTrace();
\r
555 tempNotebook.setPublished(new Boolean(query.valueString(6)));
\r
556 tempNotebook.setStack(query.valueString(7));
\r
558 if (tempNotebook.isPublished()) {
\r
559 Publishing p = new Publishing();
\r
560 p.setUri(query.valueString(8));
\r
561 p.setAscending(query.valueBoolean(9, false));
\r
562 p.setPublicDescription(query.valueString(10));
\r
563 p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
\r
564 if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))
\r
565 p.setPublicDescription(null);
\r
566 tempNotebook.setPublishing(p);
\r
569 return tempNotebook;
\r
573 // This is a convience method to check if a tag exists & update/create based upon it
\r
574 public void syncNotebook(Notebook notebook, boolean isDirty) {
\r
575 if (!exists(notebook.getGuid())) {
\r
576 addNotebook(notebook, isDirty, isDirty);
\r
579 updateNotebook(notebook, isDirty);
\r
581 // This is a convience method to check if a tag exists & update/create based upon it
\r
582 public void syncLinkedNotebook(Notebook notebook, boolean isDirty, boolean readOnly) {
\r
583 if (!exists(notebook.getGuid())) {
\r
584 addNotebook(notebook, isDirty, false, true, readOnly);
\r
587 updateNotebook(notebook, isDirty);
\r
589 // does a record exist?
\r
590 private boolean exists(String guid) {
\r
592 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
594 query.prepare("Select guid from "+dbName+" where guid=:guid");
\r
595 query.bindValue(":guid", guid);
\r
597 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
598 boolean retval = query.next();
\r
601 // Reset the dirty flag. Typically done after a sync.
\r
602 public void resetDirtyFlag(String guid) {
\r
603 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
605 query.prepare("Update "+dbName+" set isdirty='false' where guid=:guid");
\r
606 query.bindValue(":guid", guid);
\r
608 logger.log(logger.EXTREME, "Error resetting "+dbName+" dirty field.");
\r
610 // Set the default notebook
\r
611 public void setDefaultNotebook(String guid) {
\r
612 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
614 query.prepare("Update "+dbName+" set defaultNotebook=false, isDirty=true where linked=false and defaultNotebook=true");
\r
616 logger.log(logger.EXTREME, "Error removing default "+dbName+".");
\r
617 query.prepare("Update "+dbName+" set defaultNotebook=true, isDirty=true where guid=:guid and linked=false");
\r
618 query.bindValue(":guid", guid);
\r
620 logger.log(logger.EXTREME, "Error setting default "+dbName+".");
\r
623 // Get a list of all icons
\r
624 public HashMap<String, QIcon> getAllIcons() {
\r
625 HashMap<String, QIcon> values = new HashMap<String, QIcon>();
\r
626 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
628 if (!query.exec("SELECT guid, icon from "+dbName+" where ARCHIVED != true"))
\r
629 logger.log(logger.EXTREME, "Error executing "+dbName+" getAllIcons select.");
\r
630 while (query.next()) {
\r
631 if (query.getBlob(1) != null) {
\r
632 String guid = query.valueString(0);
\r
633 QByteArray blob = new QByteArray(query.getBlob(1));
\r
634 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));
\r
635 values.put(guid, icon);
\r
641 // Get the notebooks custom icon
\r
642 public QIcon getIcon(String guid) {
\r
643 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
645 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))
\r
646 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
\r
647 query.bindValue(":guid", guid);
\r
649 logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");
\r
650 if (!query.next() || query.getBlob(0) == null)
\r
653 QByteArray blob = new QByteArray(query.getBlob(0));
\r
654 QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));
\r
657 // Get the notebooks custom icon
\r
658 public QByteArray getIconAsByteArray(String guid) {
\r
659 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
661 if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))
\r
662 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
\r
663 query.bindValue(":guid", guid);
\r
665 logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");
\r
666 if (!query.next() || query.getBlob(0) == null)
\r
669 QByteArray blob = new QByteArray(query.getBlob(0));
\r
672 // Set the notebooks custom icon
\r
673 public void setIcon(String guid, QIcon icon, String type) {
\r
674 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
675 if (icon == null) {
\r
676 if (!query.prepare("update "+dbName+" set icon=null where guid=:guid"))
\r
677 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
\r
679 if (!query.prepare("update "+dbName+" set icon=:icon where guid=:guid"))
\r
680 logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
\r
681 QBuffer buffer = new QBuffer();
\r
682 if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {
\r
683 logger.log(logger.EXTREME, "Failure to open buffer. Aborting.");
\r
686 QPixmap p = icon.pixmap(32, 32);
\r
687 QImage i = p.toImage();
\r
688 i.save(buffer, type.toUpperCase());
\r
690 QByteArray b = new QByteArray(buffer.buffer());
\r
691 if (!b.isNull() && !b.isEmpty())
\r
692 query.bindValue(":icon", b.toByteArray());
\r
696 query.bindValue(":guid", guid);
\r
697 if (!query.exec())
\r
698 logger.log(logger.LOW, "Error setting "+dbName+" icon. " +query.lastError());
\r
700 // Set the notebooks custom icon
\r
701 public void setReadOnly(String guid, boolean readOnly) {
\r
702 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
703 if (!query.prepare("update "+dbName+" set readOnly=:readOnly where guid=:guid"))
\r
704 logger.log(logger.EXTREME, "Error preparing "+dbName+" read only.");
\r
705 query.bindValue(":guid", guid);
\r
706 query.bindValue(":readOnly", readOnly);
\r
707 if (!query.exec())
\r
708 logger.log(logger.LOW, "Error setting "+dbName+" read only. " +query.lastError());
\r
711 // does a record exist?
\r
712 public String findNotebookByName(String newname) {
\r
714 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
716 query.prepare("Select guid from "+dbName+" where name=:newname");
\r
717 query.bindValue(":newname", newname);
\r
719 logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
\r
722 val = query.valueString(0);
\r
725 // Get a note tag counts
\r
726 public List<Pair<String,Integer>> getNotebookCounts() {
\r
727 List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();
\r
728 if (notebookCountQuery == null) {
\r
729 notebookCountQuery = new NSqlQuery(db.getConnection());
\r
730 notebookCountQuery.prepare("select notebookGuid, count(guid) from note where active=1 group by notebookguid;");
\r
732 if (!notebookCountQuery.exec()) {
\r
733 logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");
\r
734 logger.log(logger.MEDIUM, notebookCountQuery.lastError());
\r
737 while (notebookCountQuery.next()) {
\r
738 Pair<String,Integer> newCount = new Pair<String,Integer>();
\r
739 newCount.setFirst(notebookCountQuery.valueString(0));
\r
740 newCount.setSecond(notebookCountQuery.valueInteger(1));
\r
741 counts.add(newCount);
\r
747 public void clearStack(String guid) {
\r
748 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
750 query.prepare("Update "+dbName+" set stack='' where guid=:guid");
\r
751 query.bindValue(":guid", guid);
\r
753 logger.log(logger.EXTREME, "Error clearing "+dbName+" stack.");
\r
756 public void setStack(String guid, String stack) {
\r
757 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
759 query.prepare("Update "+dbName+" set stack=:stack, isDirty=true where guid=:guid");
\r
760 query.bindValue(":guid", guid);
\r
761 query.bindValue(":stack", stack);
\r
763 logger.log(logger.EXTREME, "Error setting notebook stack.");
\r
765 // Get all stack names
\r
766 public List<String> getAllStackNames() {
\r
767 List<String> stacks = new ArrayList<String>();
\r
768 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
770 if (!query.exec("Select distinct stack from "+dbName)) {
\r
771 logger.log(logger.EXTREME, "Error getting all stack names.");
\r
775 while (query.next()) {
\r
776 if (query.valueString(0) != null && !query.valueString(0).trim().equals(""))
\r
777 stacks.add(query.valueString(0));
\r
782 public void renameStacks(String oldName, String newName) {
\r
783 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
785 if (!query.prepare("update "+dbName+" set stack=:newName where stack=:oldName")) {
\r
786 logger.log(logger.EXTREME, "Error preparing in renameStacks.");
\r
789 query.bindValue(":oldName", oldName);
\r
790 query.bindValue(":newName", newName);
\r
791 if (!query.exec()) {
\r
792 logger.log(logger.EXTREME, "Error updating stack names");
\r
796 if (!query.prepare("update SystemIcon set name=:newName where name=:oldName and type='STACK'")) {
\r
797 logger.log(logger.EXTREME, "Error preparing icon rename in renameStacks.");
\r
800 query.bindValue(":oldName", oldName);
\r
801 query.bindValue(":newName", newName);
\r
802 if (!query.exec()) {
\r
803 logger.log(logger.EXTREME, "Error updating stack names for SystemIcon");
\r
809 public boolean stackExists(String stack) {
\r
810 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
812 query.prepare("Select guid from "+dbName+" where stack=:stack limit 1");
\r
813 query.bindValue(":stack", stack);
\r
815 logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");
\r
822 public void setPublishing(String guid, boolean published, Publishing p) {
\r
823 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
826 query.prepare("Update "+dbName+" set publishingPublicDescription=:publishingPublicDescription, " +
\r
827 "publishingUri=:publishingUri, publishingOrder=:publishingOrder, published=:published, "+
\r
828 "publishingAscending=:publishingAscending, isdirty=true where "+
\r
830 query.bindValue(":publishingPublicDescription", p.getPublicDescription());
\r
831 query.bindValue(":publishingUri", p.getUri());
\r
832 query.bindValue(":publishingOrder", p.getOrder().getValue());
\r
833 query.bindValue(":publishingAscending", p.isAscending());
\r
834 query.bindValue(":publishingPublicDescription", p.getPublicDescription());
\r
835 query.bindValue(":published", published);
\r
836 query.bindValue(":guid", guid);
\r
838 logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");
\r
840 // Get a notebook by uri
\r
841 public String getNotebookByUri(String uri) {
\r
844 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
846 check = query.prepare("Select guid "
\r
847 +"from "+dbName+" where publishingUri=:uri");
\r
848 query.bindValue(":uri", uri);
\r
849 check = query.exec();
\r
851 logger.log(logger.EXTREME, "Notebook SQL retrieve guid by uri has failed.");
\r
852 if (query.next()) {
\r
853 return query.valueString(0);
\r
857 // Get a notebook's sort order
\r
858 public int getSortColumn(String guid) {
\r
861 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
863 if (Global.getSortOrder() != Global.View_List_Wide)
\r
864 check = query.prepare("Select wide_sort_column "
\r
865 +"from "+dbName+" where guid=:guid");
\r
867 check = query.prepare("Select narrow_sort_column "
\r
868 +"from "+dbName+" where guid=:guid");
\r
869 query.bindValue(":guid", guid);
\r
870 check = query.exec();
\r
872 logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");
\r
875 if (query.next()) {
\r
876 return query.valueInteger(0);
\r
881 // Get a notebook's sort order
\r
882 public int getSortOrder(String guid) {
\r
885 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
887 if (Global.getSortOrder() != Global.View_List_Wide)
\r
888 check = query.prepare("Select wide_sort_order "
\r
889 +"from "+dbName+" where guid=:guid");
\r
891 check = query.prepare("Select narrow_sort_order "
\r
892 +"from "+dbName+" where guid=:guid");
\r
893 query.bindValue(":guid", guid);
\r
894 check = query.exec();
\r
896 logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");
\r
899 if (query.next()) {
\r
900 return query.valueInteger(0);
\r
904 // Get a notebook's sort order
\r
905 public void setSortOrder(String guid, int column, int order) {
\r
908 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
910 if (Global.getSortOrder() != Global.View_List_Wide)
\r
911 check = query.prepare("Update "+dbName+" set wide_sort_order=:order, wide_sort_column=:column where guid=:guid");
\r
913 check = query.prepare("Update "+dbName+" set narrow_sort_order=:order, narrow_sort_column=:column where guid=:guid");
\r
915 query.bindValue(":guid", guid);
\r
916 query.bindValue(":order", order);
\r
917 query.bindValue(":column", column);
\r
918 check = query.exec();
\r
920 logger.log(logger.EXTREME, "Notebook SQL set sort order has failed.");
\r
922 // Is a notebook a linked notebook?
\r
923 public boolean isLinked(String guid) {
\r
926 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
928 check = query.prepare("Select guid "
\r
929 +"from "+dbName+" where guid=:guid and linked=true");
\r
930 query.bindValue(":guid", guid);
\r
931 check = query.exec();
\r
933 logger.log(logger.EXTREME, "Notebook SQL isLinked failed.");
\r
934 if (query.next()) {
\r
940 // Given a notebook, what tags are valid for it?
\r
941 public List<String> getValidLinkedTags(String guid) {
\r
943 List<String> tags = new ArrayList<String>();
\r
945 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
946 check = query.prepare("select distinct tagGuid from noteTags " +
\r
947 "where noteGuid in " +
\r
948 "(SELECT guid from note where notebookguid=:guid)");
\r
949 query.bindValue(":guid", guid);
\r
950 check = query.exec();
\r
952 logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");
\r
953 while (query.next()) {
\r
954 tags.add(query.valueString(0));
\r
960 // Given a notebook, what tags are valid for it?
\r
961 public void deleteLinkedTags(String guid) {
\r
963 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
964 query.prepare("select distinct tagguid from noteTags " +
\r
965 "where noteGuid in " +
\r
966 "(SELECT guid from note where notebookguid=:guid)");
\r
967 query.bindValue(":guid", guid);
\r
968 boolean check = query.exec();
\r
970 logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");
\r
971 while(query.next()) {
\r
972 db.getTagTable().expungeTag(query.valueString(0), false);
\r
976 query.prepare("delete from note " +
\r
977 "where notebookguid=:guid");
\r
978 query.bindValue(":guid", guid);
\r
979 check = query.exec();
\r
981 logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");
\r
989 // Given a notebook, what tags are valid for it?
\r
990 public void convertFromSharedNotebook(String guid, boolean local) {
\r
992 NSqlQuery query = new NSqlQuery(db.getConnection());
\r
994 query.prepare("Update Notebook set sequence=0, published=false, isdirty=true, local=:local, publishinguri=''"
\r
995 +" where guid=:guid");
\r
996 query.bindValue(":guid", guid);
\r
998 query.bindValue(":local", true);
\r
1000 query.bindValue(":local", false);
\r
1002 if (!query.exec())
\r
1003 logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook error.");
\r
1005 query.prepare("Update Note set updatesequencenumber=0, isdirty=true"
\r
1006 +" where notebookguid=:guid");
\r
1007 query.bindValue(":guid", guid);
\r
1008 if (!query.exec())
\r
1009 logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook #2 error.");
\r