/* * This file is part of NixNote * Copyright 2009 Randy Baumgarte * * This file may be licensed under the terms of of the * GNU General Public License Version 2 (the ``GPL''). * * Software distributed under the License is distributed * on an ``AS IS'' basis, WITHOUT WARRANTY OF ANY KIND, either * express or implied. See the GPL for the specific language * governing rights and limitations. * * You should have received a copy of the GPL along with this * program. If not, go to http://www.gnu.org/licenses/gpl.html * or write to the Free Software Foundation, Inc., * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * */ package cx.fbn.nevernote.sql; import java.util.ArrayList; import java.util.List; import com.evernote.edam.type.LinkedNotebook; import cx.fbn.nevernote.sql.driver.NSqlQuery; import cx.fbn.nevernote.utilities.ApplicationLogger; public class LinkedNotebookTable { private final ApplicationLogger logger; DatabaseConnection db; // Constructor public LinkedNotebookTable(ApplicationLogger l, DatabaseConnection d) { logger = l; db = d; } // Create the table public void createTable() { NSqlQuery query = new NSqlQuery(db.getConnection()); logger.log(logger.HIGH, "Creating table LinkedNotebook..."); if (!query.exec("Create table LinkedNotebook (guid VarChar primary key, " + "shareName VarChar, " + "username VarChar, "+ "shardID VarChar, " + "shareKey VarChar, " + "uri VarChar, " + "updateSequenceNumber Long," + "lastSequenceNumber Integer," + "lastSequenceDate Long," + "notebookGuid VarChar," + "isDirty boolean)")) logger.log(logger.HIGH, "Table LinkedNotebook creation FAILED!!!"); } // Drop the table public void dropTable() { NSqlQuery query = new NSqlQuery(db.getConnection()); query.exec("Drop table LinkedNotebook"); } // Save an individual notebook public void addNotebook(LinkedNotebook tempNotebook, boolean isDirty) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Insert Into LinkedNotebook (guid, shareName, username, " +"shardId, shareKey, uri, updateSequenceNumber, isDirty, lastSequenceNumber, " + "lastSequenceDate, notebookGuid) " + " Values(" +":guid, :shareName, :username, " +":shardId, :shareKey, :uri,:usn, :isDirty, 0, 0, :notebookGuid)"); query.bindValue(":guid", tempNotebook.getGuid()); query.bindValue(":shareName", tempNotebook.getShareName()); query.bindValue(":username", tempNotebook.getUsername()); query.bindValue(":shardId", tempNotebook.getShardId()); query.bindValue(":shareKey", tempNotebook.getShareKey()); query.bindValue(":usn", tempNotebook.getUpdateSequenceNum()); query.bindValue(":uri", tempNotebook.getUri()); query.bindValue(":notebookGuid", ""); if (isDirty) query.bindValue(":isDirty", true); else query.bindValue(":isDirty", false); check = query.exec(); if (!check) { logger.log(logger.MEDIUM, "LinkedNotebook Table insert failed."); logger.log(logger.MEDIUM, query.lastError().toString()); return; } } // Delete the notebook based on a guid public void expungeNotebook(String id, boolean needsSync) { boolean check; // First, delete any tags associated with this notebook String notebookGuid = getNotebookGuid(id); db.getNotebookTable().deleteLinkedTags(notebookGuid); // Now, delete any notes associated with this notebook List notes = db.getNoteTable().getNotesByNotebook(notebookGuid); for (int i=0; i getAll() { LinkedNotebook tempNotebook; List index = new ArrayList(); boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.exec("Select guid, shareName, username, shardID, shareKey, uri " + " from LinkedNotebook"); if (!check) logger.log(logger.EXTREME, "Notebook SQL retrieve has failed."); while (query.next()) { tempNotebook = new LinkedNotebook(); tempNotebook.setGuid(query.valueString(0)); tempNotebook.setShareName(query.valueString(1)); tempNotebook.setUsername(query.valueString(2)); tempNotebook.setShardId(query.valueString(3)); tempNotebook.setShareKey(query.valueString(4)); tempNotebook.setUri(query.valueString(5)); index.add(tempNotebook); } return index; } // Load notebooks from the database public LinkedNotebook getNotebook(String guid) { LinkedNotebook tempNotebook; boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " + " from LinkedNotebook where guid=:guid"); if (!check) logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed."); query.bindValue(":guid", guid); query.exec(); while (query.next()) { tempNotebook = new LinkedNotebook(); tempNotebook.setGuid(query.valueString(0)); tempNotebook.setShareName(query.valueString(1)); tempNotebook.setUsername(query.valueString(2)); tempNotebook.setShardId(query.valueString(3)); tempNotebook.setShareKey(query.valueString(4)); tempNotebook.setUri(query.valueString(5)); return tempNotebook; } return null; } // Load notebooks from the database public LinkedNotebook getByNotebookGuid(String guid) { LinkedNotebook tempNotebook; boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select guid, shareName, username, shardID, shareKey, uri " + " from LinkedNotebook where notebookguid=:guid"); if (!check) logger.log(logger.EXTREME, "Notebook SQL retrieve notebook prepare has failed."); query.bindValue(":guid", guid); query.exec(); while (query.next()) { tempNotebook = new LinkedNotebook(); tempNotebook.setGuid(query.valueString(0)); tempNotebook.setShareName(query.valueString(1)); tempNotebook.setUsername(query.valueString(2)); tempNotebook.setShardId(query.valueString(3)); tempNotebook.setShareKey(query.valueString(4)); tempNotebook.setUri(query.valueString(5)); return tempNotebook; } return null; } // Get last sequence date for the notebook public long getLastSequenceDate(String guid) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select LastSequenceDate " +"from LinkedNotebook where guid=:guid"); query.bindValue(":guid", guid); check = query.exec(); if (!check) logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve last sequence date has failed."); if (query.next()) { return query.valueLong(0); } return 0; } // Get a guid by uri public String getNotebookGuid(String guid) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select notebookGuid " +"from LinkedNotebook where guid=:guid"); query.bindValue(":guid", guid); check = query.exec(); if (!check) logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve of notebookguid by guidhas failed."); if (query.next()) { return query.valueString(0); } return null; } // get last sequence numeber public int getLastSequenceNumber(String guid) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select LastSequenceNumber " +"from LinkedNotebook where guid=:guid"); query.bindValue(":guid", guid); check = query.exec(); if (!check) logger.log(logger.EXTREME, "Notebook SQL retrieve has failed."); while (query.next()) { return query.valueInteger(0); } return 0; } // get the "true" notebook guid and not the shared notebook guid public String getLocalNotebookGuid(String guid) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select notebookGuid " +"from LinkedNotebook where guid=:guid"); query.bindValue(":guid", guid); check = query.exec(); if (!check) logger.log(logger.EXTREME, "Notebook SQL retrieve has failed."); while (query.next()) { return query.valueString(0); } return null; } // does a record exist? public String findNotebookByShareName(String name) { NSqlQuery query = new NSqlQuery(db.getConnection()); query.prepare("Select guid from Linkednotebook where shareName=:name"); query.bindValue(":name", name); if (!query.exec()) logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed."); String val = null; if (query.next()) val = query.valueString(0); return val; } // does a record exist? public String setNotebookGuid(String shareKey, String notebookGuid) { NSqlQuery query = new NSqlQuery(db.getConnection()); query.prepare("Update LinkedNotebook set notebookGuid=:notebookGuid where shareKey=:shareKey"); query.bindValue(":notebookGuid", notebookGuid); query.bindValue(":shareKey", shareKey); if (!query.exec()) logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed."); String val = null; if (query.next()) val = query.valueString(0); return val; } // set sync date public String setLastSequenceDate(String guid, long date) { NSqlQuery query = new NSqlQuery(db.getConnection()); query.prepare("Update LinkedNotebook set lastsequencedate=:date where guid=:guid"); query.bindValue(":date", date); query.bindValue(":guid", guid); if (!query.exec()) logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed."); String val = null; if (query.next()) val = query.valueString(0); return val; } // set sync number public String setLastSequenceNumber(String guid, int number) { NSqlQuery query = new NSqlQuery(db.getConnection()); query.prepare("Update LinkedNotebook set lastsequencenumber=:number where guid=:guid"); query.bindValue(":number", number); query.bindValue(":guid", guid); if (!query.exec()) logger.log(logger.EXTREME, "Linked notebook SQL retrieve by share name has failed."); String val = null; if (query.next()) val = query.valueString(0); return val; } // Get a list of linked notebooks that need to be updated public List getDirtyGuids() { List index = new ArrayList(); boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.exec("Select guid from LinkedNotebook where isDirty = true"); if (!check) logger.log(logger.EXTREME, "LinkedNotebook SQL retrieve has failed in getdirtyIds."); while (query.next()) { index.add(query.valueString(0)); } return index; } }