/*\r
- * This file is part of NeverNote \r
+ * This file is part of NixNote \r
* Copyright 2009 Randy Baumgarte\r
* \r
* This file may be licensed under the terms of of the\r
import com.trolltech.qt.gui.QImage;\r
import com.trolltech.qt.gui.QPixmap;\r
\r
+import cx.fbn.nevernote.Global;\r
import cx.fbn.nevernote.sql.driver.NSqlQuery;\r
import cx.fbn.nevernote.utilities.ApplicationLogger;\r
import cx.fbn.nevernote.utilities.Pair;\r
private final ApplicationLogger logger;\r
DatabaseConnection db;\r
private final String dbName;\r
+ NSqlQuery notebookCountQuery;\r
\r
// Constructor\r
public NotebookTable(ApplicationLogger l, DatabaseConnection d) {\r
db = d;\r
dbName = "Notebook";\r
}\r
+ // Constructor\r
+ public NotebookTable(ApplicationLogger l, DatabaseConnection d, String name) {\r
+ logger = l;\r
+ db = d;\r
+ dbName = name;\r
+ }\r
+\r
// Create the table\r
- public void createTable(boolean addDefault) {\r
+ public void createTable(boolean addDefaulte) {\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
logger.log(logger.HIGH, "Creating table "+dbName+"...");\r
if (!query.exec("Create table "+dbName+" (guid varchar primary key, " +\r
newnote.setPublished(false);\r
newnote.setGuid("1");\r
\r
- if (!addDefault)\r
- return;\r
- \r
// Setup an initial notebook\r
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
query = new NSqlQuery(db.getConnection());\r
}\r
// Save an individual notebook\r
public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {\r
+ addNotebook(tempNotebook, isDirty, local, false, false);\r
+ }\r
+ // Save an individual notebook\r
+ public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local, boolean linked, boolean readOnly) {\r
boolean check;\r
\r
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+"serviceCreated, serviceUpdated, published, " \r
+ "publishingUri, publishingOrder, publishingAscending, publishingPublicDescription, "\r
+ "isDirty, autoEncrypt, stack, " \r
- + "local, archived, readOnly) Values("\r
+ + "local, archived, readOnly, linked) Values("\r
+":guid, :sequence, :name, :defaultNotebook, "\r
+":serviceCreated, :serviceUpdated, :published, "\r
+":publishingUri, :publishingOrder, :publishingAscending, :publishingPublicDescription, "\r
+":isDirty, :autoEncrypt, "\r
- +":stack, :local, false, false)");\r
+ +":stack, :local, false, :readOnly, :linked)");\r
query.bindValue(":guid", tempNotebook.getGuid());\r
query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
query.bindValue(":name", tempNotebook.getName());\r
query.bindValue(":serviceCreated", serviceCreated.toString());\r
query.bindValue(":serviceUpdated", serviceCreated.toString());\r
query.bindValue(":published",tempNotebook.isPublished());\r
+ query.bindValue(":linked", linked);\r
+ query.bindValue(":readOnly", readOnly);\r
\r
if (tempNotebook.isPublished() && tempNotebook.getPublishing() != null) {\r
Publishing p = tempNotebook.getPublishing();\r
// Delete the notebook based on a guid\r
public void expungeNotebook(String guid, boolean needsSync) {\r
boolean check;\r
+ Notebook n;\r
+ n = getNotebook(guid);\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
\r
check = query.prepare("delete from "+dbName+" where guid=:guid");\r
logger.log(logger.MEDIUM, dbName+" delete failed.");\r
\r
// Signal the parent that work needs to be done\r
- if (needsSync) {\r
+ if (needsSync && n!=null && n.getUpdateSequenceNum() > 0) {\r
DeletedTable deletedTable = new DeletedTable(logger, db);\r
deletedTable.addDeletedItem(guid, dbName);\r
}\r
"published=:published, isDirty=:isDirty, publishinguri=:uri, "+\r
"publishingOrder=:order, " + \r
"publishingAscending=:ascending, " +\r
- "publishingPublicDescription=:desc " +\r
+ "publishingPublicDescription=:desc, " +\r
+ "stack=:stack " +\r
"where guid=:guid ");\r
\r
query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());\r
}\r
\r
query.bindValue(":guid", tempNotebook.getGuid());\r
+ query.bindValue(":stack", tempNotebook.getStack());\r
\r
check = query.exec();\r
if (!check) {\r
} catch (ParseException e) {\r
e.printStackTrace();\r
}\r
- tempNotebook.setStack(query.valueString(7));\r
+ if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
+ tempNotebook.setStack(query.valueString(7));\r
index.add(tempNotebook); \r
} \r
return index;\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
\r
check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
- "serviceCreated, serviceUpdated, published, stack "+\r
+ "serviceCreated, serviceUpdated, published, stack, "+\r
"publishinguri, publishingascending, publishingPublicDescription, "+\r
"publishingOrder " +\r
"from "+dbName+" where archived=true order by name");\r
e.printStackTrace();\r
}\r
tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
- tempNotebook.setStack(query.valueString(7));\r
+ if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
+ tempNotebook.setStack(query.valueString(7));\r
\r
if (tempNotebook.isPublished()) {\r
Publishing p = new Publishing();\r
boolean returnValue = query.valueBoolean(0, false);\r
return returnValue;\r
}\r
+ // Check for a local/remote notebook\r
+ public boolean isNotebookLinked(String guid) {\r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ query.prepare("Select linked from "+dbName+" where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ query.exec();\r
+ if (!query.next()) {\r
+ return false;\r
+ }\r
+ boolean returnValue = query.valueBoolean(0, false);\r
+ return returnValue;\r
+ }\r
public boolean isReadOnly(String guid) {\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
\r
- query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly='true'");\r
+ query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly=true");\r
query.bindValue(":guid", guid);\r
query.exec();\r
if (!query.next()) {\r
if (!query.exec()) {\r
logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");\r
logger.log(logger.MEDIUM, query.lastError().toString());\r
- } \r
+ }\r
}\r
// Get a list of notes that need to be updated\r
public List <Notebook> getDirty() {\r
List<Notebook> index = new ArrayList<Notebook>();\r
boolean check;\r
\r
- \r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
\r
check = query.exec("Select guid, sequence, name, defaultNotebook, " +\r
"serviceCreated, serviceUpdated, published, stack, "+\r
"publishinguri, publishingascending, publishingPublicDescription, "+\r
"publishingOrder " +\r
- "from "+dbName+" where isDirty = true and local=false");\r
+ "from "+dbName+" where isDirty=true and local=false and linked=false");\r
if (!check) \r
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
while (query.next()) {\r
tempNotebook.setName(query.valueString(2));\r
\r
DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
-// indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");\r
try {\r
tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
e.printStackTrace();\r
}\r
tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
- tempNotebook.setStack(query.valueString(7));\r
+ if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
+ tempNotebook.setStack(query.valueString(7));\r
\r
if (tempNotebook.isPublished()) {\r
Publishing p = new Publishing();\r
p.setAscending(query.valueBoolean(9, false));\r
p.setPublicDescription(query.valueString(10));\r
p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
- if (p.getPublicDescription().trim().equalsIgnoreCase(""))\r
+ if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))\r
p.setPublicDescription(null);\r
tempNotebook.setPublishing(p);\r
}\r
} \r
return index; \r
}\r
+ // Get a list of notes that need to be updated\r
+ public Notebook getNotebook(String guid) {\r
+ Notebook tempNotebook;\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ query.prepare("Select guid, sequence, name, defaultNotebook, " +\r
+ "serviceCreated, serviceUpdated, published, stack, "+\r
+ "publishinguri, publishingascending, publishingPublicDescription, "+\r
+ "publishingOrder " +\r
+ "from "+dbName+" where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check) \r
+ logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");\r
+ while (query.next()) {\r
+ tempNotebook = new Notebook();\r
+ tempNotebook.setGuid(query.valueString(0));\r
+ int sequence = new Integer(query.valueString(1)).intValue();\r
+ tempNotebook.setUpdateSequenceNum(sequence);\r
+ tempNotebook.setName(query.valueString(2));\r
+ \r
+ DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");\r
+ try {\r
+ tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());\r
+ tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());\r
+ } catch (ParseException e) {\r
+ e.printStackTrace();\r
+ }\r
+ tempNotebook.setPublished(new Boolean(query.valueString(6)));\r
+ if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))\r
+ tempNotebook.setStack(query.valueString(7));\r
+ \r
+ if (tempNotebook.isPublished()) {\r
+ Publishing p = new Publishing();\r
+ p.setUri(query.valueString(8));\r
+ p.setAscending(query.valueBoolean(9, false));\r
+ p.setPublicDescription(query.valueString(10));\r
+ p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));\r
+ if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))\r
+ p.setPublicDescription(null);\r
+ tempNotebook.setPublishing(p);\r
+ }\r
+ \r
+ return tempNotebook;\r
+ } \r
+ return null; \r
+ }\r
// This is a convience method to check if a tag exists & update/create based upon it\r
public void syncNotebook(Notebook notebook, boolean isDirty) {\r
if (!exists(notebook.getGuid())) {\r
}\r
updateNotebook(notebook, isDirty);\r
}\r
+ // This is a convience method to check if a tag exists & update/create based upon it\r
+ public void syncLinkedNotebook(Notebook notebook, boolean isDirty, boolean readOnly) {\r
+ if (!exists(notebook.getGuid())) {\r
+ addNotebook(notebook, isDirty, false, true, readOnly);\r
+ return;\r
+ }\r
+ updateNotebook(notebook, isDirty);\r
+ }\r
// does a record exist?\r
private boolean exists(String guid) {\r
\r
public void setDefaultNotebook(String guid) {\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
\r
- query.prepare("Update "+dbName+" set defaultNotebook=false");\r
+ query.prepare("Update "+dbName+" set defaultNotebook=false, isDirty=true where linked=false and defaultNotebook=true");\r
if (!query.exec())\r
logger.log(logger.EXTREME, "Error removing default "+dbName+".");\r
- query.prepare("Update "+dbName+" set defaultNotebook=true where guid = :guid");\r
+ query.prepare("Update "+dbName+" set defaultNotebook=true, isDirty=true where guid=:guid and linked=false");\r
query.bindValue(":guid", guid);\r
if (!query.exec())\r
logger.log(logger.EXTREME, "Error setting default "+dbName+".");\r
// Get a note tag counts\r
public List<Pair<String,Integer>> getNotebookCounts() {\r
List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>(); \r
- NSqlQuery query = new NSqlQuery(db.getConnection());\r
- if (!query.exec("select notebookGuid, count(guid) from "+dbName+" where active=1 group by notebookguid;")) {\r
+ if (notebookCountQuery == null) {\r
+ notebookCountQuery = new NSqlQuery(db.getConnection());\r
+ notebookCountQuery.prepare("select notebookGuid, count(guid) from note where active=1 group by notebookguid;");\r
+ }\r
+ if (!notebookCountQuery.exec()) {\r
logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");\r
- logger.log(logger.MEDIUM, query.lastError());\r
+ logger.log(logger.MEDIUM, notebookCountQuery.lastError());\r
return null;\r
}\r
- while (query.next()) {\r
+ while (notebookCountQuery.next()) {\r
Pair<String,Integer> newCount = new Pair<String,Integer>();\r
- newCount.setFirst(query.valueString(0));\r
- newCount.setSecond(query.valueInteger(1));\r
+ newCount.setFirst(notebookCountQuery.valueString(0));\r
+ newCount.setSecond(notebookCountQuery.valueInteger(1));\r
counts.add(newCount);\r
} \r
return counts;\r
return;\r
}\r
\r
- if (!query.prepare("update SystemIcon set stack=:newName where stack=:oldName and type='STACK'")) {\r
+ if (!query.prepare("update SystemIcon set name=:newName where name=:oldName and type='STACK'")) {\r
logger.log(logger.EXTREME, "Error preparing icon rename in renameStacks.");\r
return;\r
}\r
if (!query.exec())\r
logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");\r
}\r
+ // Get a notebook by uri\r
+ public String getNotebookByUri(String uri) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select guid " \r
+ +"from "+dbName+" where publishingUri=:uri");\r
+ query.bindValue(":uri", uri);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL retrieve guid by uri has failed.");\r
+ if (query.next()) {\r
+ return query.valueString(0);\r
+ } \r
+ return null;\r
+ } \r
+ // Get a notebook's sort order\r
+ public int getSortColumn(String guid) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ if (Global.getSortOrder() != Global.View_List_Wide)\r
+ check = query.prepare("Select wide_sort_column " \r
+ +"from "+dbName+" where guid=:guid");\r
+ else\r
+ check = query.prepare("Select narrow_sort_column " \r
+ +"from "+dbName+" where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check) {\r
+ logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");\r
+ return -1;\r
+ }\r
+ if (query.next()) {\r
+ return query.valueInteger(0);\r
+ } \r
+ return -1;\r
+ } \r
+\r
+ // Get a notebook's sort order\r
+ public int getSortOrder(String guid) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ if (Global.getSortOrder() != Global.View_List_Wide)\r
+ check = query.prepare("Select wide_sort_order " \r
+ +"from "+dbName+" where guid=:guid");\r
+ else\r
+ check = query.prepare("Select narrow_sort_order " \r
+ +"from "+dbName+" where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check) {\r
+ logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");\r
+ return -1;\r
+ }\r
+ if (query.next()) {\r
+ return query.valueInteger(0);\r
+ } \r
+ return -1;\r
+ } \r
+ // Get a notebook's sort order\r
+ public void setSortOrder(String guid, int column, int order) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ if (Global.getSortOrder() != Global.View_List_Wide)\r
+ check = query.prepare("Update "+dbName+" set wide_sort_order=:order, wide_sort_column=:column where guid=:guid");\r
+ else\r
+ check = query.prepare("Update "+dbName+" set narrow_sort_order=:order, narrow_sort_column=:column where guid=:guid");\r
+\r
+ query.bindValue(":guid", guid);\r
+ query.bindValue(":order", order);\r
+ query.bindValue(":column", column);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL set sort order has failed.");\r
+ } \r
+ // Is a notebook a linked notebook?\r
+ public boolean isLinked(String guid) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select guid " \r
+ +"from "+dbName+" where guid=:guid and linked=true");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL isLinked failed.");\r
+ if (query.next()) {\r
+ return true;\r
+ } \r
+ return false;\r
+ }\r
+\r
+ // Given a notebook, what tags are valid for it?\r
+ public List<String> getValidLinkedTags(String guid) {\r
+ boolean check;\r
+ List<String> tags = new ArrayList<String>();\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection()); \r
+ check = query.prepare("select distinct tagGuid from noteTags " +\r
+ "where noteGuid in " +\r
+ "(SELECT guid from note where notebookguid=:guid)");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
+ while (query.next()) {\r
+ tags.add(query.valueString(0));\r
+ } \r
+ return tags;\r
+ \r
+ \r
+ }\r
+ // Given a notebook, what tags are valid for it?\r
+ public void deleteLinkedTags(String guid) {\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection()); \r
+ query.prepare("select distinct tagguid from noteTags " +\r
+ "where noteGuid in " +\r
+ "(SELECT guid from note where notebookguid=:guid)");\r
+ query.bindValue(":guid", guid);\r
+ boolean check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
+ while(query.next()) {\r
+ db.getTagTable().expungeTag(query.valueString(0), false);\r
+ }\r
+ \r
+ \r
+ query.prepare("delete from note " +\r
+ "where notebookguid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");\r
+\r
+ \r
+ return;\r
+ \r
+ \r
+ }\r
+ \r
+ // Given a notebook, what tags are valid for it?\r
+ public void convertFromSharedNotebook(String guid, boolean local) {\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection()); \r
+ \r
+ query.prepare("Update Notebook set sequence=0, published=false, isdirty=true, local=:local, publishinguri=''"\r
+ +" where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ if (local)\r
+ query.bindValue(":local", true);\r
+ else\r
+ query.bindValue(":local", false);\r
+ \r
+ if (!query.exec())\r
+ logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook error.");\r
+ \r
+ query.prepare("Update Note set updatesequencenumber=0, isdirty=true"\r
+ +" where notebookguid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ if (!query.exec())\r
+ logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook #2 error.");\r
+ \r
+ return;\r
+ \r
+ \r
+ }\r
}\r
\r