public void createTable() {\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
logger.log(logger.HIGH, "Creating table LinkedNotebook...");\r
- if (!query.exec("Create table LinkedNotebook (id long primary key, " +\r
+ if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " +\r
"shareName VarChar, " +\r
"username VarChar, "+\r
"shardID VarChar, " +\r
"shareKey VarChar, " +\r
- "url VarChar, " +\r
- "icon blob, " +\r
+ "uri VarChar, " +\r
+ "updateSequenceNumber Long," +\r
+ "lastSequenceNumber Integer," +\r
+ "lastSequenceDate Long," +\r
+ "notebookGuid VarChar," +\r
"isDirty boolean)")) \r
logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!"); \r
}\r
query.exec("Drop table LinkedNotebook");\r
}\r
// Save an individual notebook\r
- public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
+ public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
boolean check;\r
\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
- check = query.prepare("Insert Into LinkedNotebook (id, shareName, username, "\r
- +"shardId, shareKey, url, isDirty) " \r
+ check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username, "\r
+ +"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, "\r
+ + "lastSequenceDate, notebookGuid) " \r
+ " Values("\r
- +":id, :shareName, :username, "\r
- +":shardId, :shareKey, :url, :isDirty)");\r
- query.bindValue(":id", tempNotebook.getId());\r
+ +":guid, :shareName, :username, "\r
+ +":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)");\r
+ query.bindValue(":guid", tempNotebook.getGuid());\r
query.bindValue(":shareName", tempNotebook.getShareName());\r
query.bindValue(":username", tempNotebook.getUsername());\r
query.bindValue(":shardId", tempNotebook.getShardId());\r
query.bindValue(":shareKey", tempNotebook.getShareKey());\r
- query.bindValue(":url", tempNotebook.getUri());\r
+ query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
+ query.bindValue(":uri", tempNotebook.getUri());\r
+ query.bindValue(":notebookGuid", "");\r
\r
if (isDirty)\r
query.bindValue(":isDirty", true);\r
if (!check) {\r
logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed.");\r
logger.log(logger.MEDIUM, query.lastError().toString());\r
+ return;\r
}\r
}\r
// Delete the notebook based on a guid\r
- public void expungeNotebook(long id, boolean needsSync) {\r
+ public void expungeNotebook(String id, boolean needsSync) {\r
boolean check;\r
+ \r
+ // First, delete any tags associated with this notebook\r
+ String notebookGuid = getNotebookGuid(id);\r
+ db.getNotebookTable().deleteLinkedTags(notebookGuid);\r
+ \r
+ // Now, delete any notes associated with this notebook\r
+ List<String> notes = db.getNoteTable().getNotesByNotebook(notebookGuid);\r
+ for (int i=0; i<notes.size(); i++) {\r
+ db.getNoteTable().expungeNote(notes.get(i), true, needsSync);\r
+ }\r
+ \r
+ // Delete the notebook record\r
+ db.getNotebookTable().expungeNotebook(notebookGuid, needsSync);\r
+ \r
+ // Finally, delete the linked notebook object itself\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
-\r
check = query.prepare("delete from LinkedNotebook "\r
- +"where id=:id");\r
+ +"where guid=:guid");\r
if (!check) {\r
logger.log(logger.EXTREME, "LinkedNotebook SQL delete prepare has failed.");\r
logger.log(logger.EXTREME, query.lastError().toString());\r
}\r
- query.bindValue(":id", id);\r
+ query.bindValue(":guid", id);\r
check = query.exec();\r
if (!check) \r
logger.log(logger.MEDIUM, "LinkedNotebook delete failed.");\r
\r
- // Signal the parent that work needs to be done\r
if (needsSync) {\r
DeletedTable deletedTable = new DeletedTable(logger, db);\r
deletedTable.addDeletedItem(new Long(id).toString(), "LinkedNotebook");\r
}\r
}\r
// Check if a notebook exists\r
- public boolean exists(long id) {\r
+ public boolean exists(String id) {\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
- boolean check = query.prepare("Select id from linkednotebook where id=:id");\r
- query.bindValue(":id", id);\r
+ boolean check = query.prepare("Select guid from linkednotebook where guid=:guid");\r
+ query.bindValue(":guid", id);\r
check = query.exec();\r
if (!check) {\r
logger.log(logger.MEDIUM, "LinkedNotebook Table exists check failed.");\r
// Update a notebook\r
public void updateNotebook(LinkedNotebook tempNotebook, boolean isDirty) {\r
boolean check;\r
- if (!exists(tempNotebook.getId())) {\r
+ if (!exists(tempNotebook.getGuid())) {\r
addNotebook(tempNotebook, isDirty);\r
return;\r
}\r
\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
- check = query.prepare("Update LinkedNotebook set id=:id, shareName=:shareName, " +\r
- "username=:username, shardID=:shardID, shareKey=:shareKey, url=:url, isDirty=:isDirty");\r
- query.bindValue(":id", tempNotebook.getId());\r
+ check = query.prepare("Update LinkedNotebook set guid=:guid, shareName=:shareName, " +\r
+ "username=:username, shardID=:shardID, shareKey=:shareKey, uri=:uri, updateSequenceNumber=:usn, isDirty=:isDirty "+\r
+ "where guid=:keyGuid");\r
+ query.bindValue(":guid", tempNotebook.getGuid());\r
+ query.bindValue(":keyGuid", tempNotebook.getGuid());\r
query.bindValue(":shareName", tempNotebook.getShareName());\r
query.bindValue(":username", tempNotebook.getUsername());\r
query.bindValue(":shardID", tempNotebook.getShardId());\r
query.bindValue(":shareKey", tempNotebook.getShareKey());\r
- query.bindValue(":url", tempNotebook.getUri());\r
+ query.bindValue(":uri", tempNotebook.getUri());\r
+ query.bindValue(":usn", tempNotebook.getUpdateSequenceNum());\r
\r
query.bindValue(":isDirty", isDirty);\r
\r
\r
NSqlQuery query = new NSqlQuery(db.getConnection());\r
\r
- check = query.exec("Select id, shareName, username, shardID, shareKey url, " +\r
+ check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " +\r
" from LinkedNotebook");\r
if (!check)\r
logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
while (query.next()) {\r
tempNotebook = new LinkedNotebook();\r
- tempNotebook.setId(query.valueLong(0));\r
- tempNotebook.setUsername(query.valueString(1));\r
- tempNotebook.setShardId(query.valueString(2));\r
- tempNotebook.setShareKey(query.valueString(3));\r
- tempNotebook.setUri(query.valueString(4));\r
+ tempNotebook.setGuid(query.valueString(0));\r
+ tempNotebook.setShareName(query.valueString(1));\r
+ tempNotebook.setUsername(query.valueString(2));\r
+ tempNotebook.setShardId(query.valueString(3));\r
+ tempNotebook.setShareKey(query.valueString(4));\r
+ tempNotebook.setUri(query.valueString(5));\r
\r
index.add(tempNotebook); \r
} \r
return index;\r
} \r
+ // Load notebooks from the database\r
+ public LinkedNotebook getNotebook(String guid) {\r
+ LinkedNotebook tempNotebook;\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
+ " from LinkedNotebook where guid=:guid");\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
+ query.bindValue(":guid", guid);\r
+ query.exec();\r
+ while (query.next()) {\r
+ tempNotebook = new LinkedNotebook();\r
+ tempNotebook.setGuid(query.valueString(0));\r
+ tempNotebook.setShareName(query.valueString(1));\r
+ tempNotebook.setUsername(query.valueString(2));\r
+ tempNotebook.setShardId(query.valueString(3));\r
+ tempNotebook.setShareKey(query.valueString(4));\r
+ tempNotebook.setUri(query.valueString(5));\r
+ return tempNotebook;\r
+ } \r
+ return null;\r
+ } \r
+ // Load notebooks from the database\r
+ public LinkedNotebook getByNotebookGuid(String guid) {\r
+ LinkedNotebook tempNotebook;\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " +\r
+ " from LinkedNotebook where notebookguid=:guid");\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed.");\r
+ query.bindValue(":guid", guid);\r
+ query.exec();\r
+ while (query.next()) {\r
+ tempNotebook = new LinkedNotebook();\r
+ tempNotebook.setGuid(query.valueString(0));\r
+ tempNotebook.setShareName(query.valueString(1));\r
+ tempNotebook.setUsername(query.valueString(2));\r
+ tempNotebook.setShardId(query.valueString(3));\r
+ tempNotebook.setShareKey(query.valueString(4));\r
+ tempNotebook.setUri(query.valueString(5));\r
+ return tempNotebook;\r
+ } \r
+ return null;\r
+ }\r
+ // Get last sequence date for the notebook\r
+ public long getLastSequenceDate(String guid) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select LastSequenceDate " \r
+ +"from LinkedNotebook where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve last sequence date has failed.");\r
+ if (query.next()) {\r
+ return query.valueLong(0);\r
+ } \r
+ return 0;\r
+ } \r
+ // Get a guid by uri\r
+ public String getNotebookGuid(String guid) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select notebookGuid " \r
+ +"from LinkedNotebook where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve of notebookguid by guidhas failed.");\r
+ if (query.next()) {\r
+ return query.valueString(0);\r
+ } \r
+ return null;\r
+ } \r
+ // get last sequence numeber\r
+ public int getLastSequenceNumber(String guid) {\r
+ boolean check;\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.prepare("Select LastSequenceNumber " \r
+ +"from LinkedNotebook where guid=:guid");\r
+ query.bindValue(":guid", guid);\r
+ check = query.exec();\r
+ if (!check)\r
+ logger.log(logger.EXTREME, "Notebook SQL retrieve has failed.");\r
+ while (query.next()) {\r
+ return query.valueInteger(0);\r
+ } \r
+ return 0;\r
+ } \r
\r
// does a record exist?\r
public String findNotebookByShareName(String name) {\r
return val;\r
}\r
\r
+ // does a record exist?\r
+ public String setNotebookGuid(String shareKey, String notebookGuid) {\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ query.prepare("Update LinkedNotebook set notebookGuid=:notebookGuid where shareKey=:shareKey");\r
+ query.bindValue(":notebookGuid", notebookGuid);\r
+ query.bindValue(":shareKey", shareKey);\r
+ if (!query.exec())\r
+ logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
+ String val = null;\r
+ if (query.next())\r
+ val = query.valueString(0);\r
+ return val;\r
+ }\r
+ // set sync date\r
+ public String setLastSequenceDate(String guid, long date) {\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ query.prepare("Update LinkedNotebook set lastsequencedate=:date where guid=:guid");\r
+ query.bindValue(":date", date);\r
+ query.bindValue(":guid", guid);\r
+ if (!query.exec())\r
+ logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
+ String val = null;\r
+ if (query.next())\r
+ val = query.valueString(0);\r
+ return val;\r
+ }\r
+ // set sync number\r
+ public String setLastSequenceNumber(String guid, int number) {\r
+ \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ query.prepare("Update LinkedNotebook set lastsequencenumber=:number where guid=:guid");\r
+ query.bindValue(":number", number);\r
+ query.bindValue(":guid", guid);\r
+ if (!query.exec())\r
+ logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed.");\r
+ String val = null;\r
+ if (query.next())\r
+ val = query.valueString(0);\r
+ return val;\r
+ }\r
+ \r
+ // Get a list of linked notebooks that need to be updated\r
+ public List<String> getDirtyGuids() {\r
+ List<String> index = new ArrayList<String>();\r
+ boolean check; \r
+ NSqlQuery query = new NSqlQuery(db.getConnection());\r
+ \r
+ check = query.exec("Select guid from LinkedNotebook where isDirty = true");\r
+ if (!check) \r
+ logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve has failed in getdirtyIds.");\r
+ while (query.next()) {\r
+ index.add(query.valueString(0));\r
+ } \r
+ return index; \r
+ }\r
\r
}\r
\r