/* * This file is part of NixNote/NeighborNote * 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.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.List; import com.evernote.edam.type.SharedNotebook; import cx.fbn.nevernote.sql.driver.NSqlQuery; import cx.fbn.nevernote.utilities.ApplicationLogger; public class SharedNotebookTable { private final ApplicationLogger logger; DatabaseConnection db; // Constructor public SharedNotebookTable(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 SharedNotebook..."); if (!query.exec("Create table SharedNotebook (id long primary key, " + "userid Integer, " + "notebookGuid VarChar, "+ "email VarChar, "+ "notebookModifiable boolean, " + "requireLogin boolean, "+ "serviceCreated timestamp, "+ "shareKey VarChar, "+ "username VarChar, "+ "icon blob, " + "isDirty boolean)")) logger.log(logger.HIGH, "Table SharedNotebook creation FAILED!!!"); } // Drop the table public void dropTable() { NSqlQuery query = new NSqlQuery(db.getConnection()); query.exec("Drop table SharedNotebook"); } // Save an individual notebook public void addNotebook(SharedNotebook tempNotebook, boolean isDirty) { boolean check; SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S"); NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Insert Into SharedNotebook (id, userid, notebookGuid, email, " +"notebookModifiable, requireLogin, serviceCreated, shareKey, username, isDirty) " + " Values(" +":id, :userid, :notebookGuid, :email, " +":notebookModifiable, :requireLogin, :serviceCreated, " +":shareKey, :username, :isDirty)"); query.bindValue(":id", tempNotebook.getId()); query.bindValue(":userid", tempNotebook.getUserId()); query.bindValue(":notebookGuid", tempNotebook.getNotebookGuid()); query.bindValue(":email", tempNotebook.getEmail()); query.bindValue(":notebookModifiable", tempNotebook.isNotebookModifiable()); query.bindValue(":requireLogin", tempNotebook.isRequireLogin()); StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated())); query.bindValue(":serviceCreated", serviceCreated.toString()); query.bindValue(":shareKey", tempNotebook.getShareKey()); query.bindValue(":username", tempNotebook.getUsername()); if (isDirty) query.bindValue(":isDirty", true); else query.bindValue(":isDirty", false); check = query.exec(); if (!check) { logger.log(logger.MEDIUM, "SharedNotebook Table insert failed."); logger.log(logger.MEDIUM, query.lastError().toString()); } } // Check if a notebook exists public boolean exists(long id) { NSqlQuery query = new NSqlQuery(db.getConnection()); logger.log(logger.EXTREME, "Checking if shared notebook " +id +" exists"); boolean check = query.prepare("Select id from sharednotebook where id=:id"); query.bindValue(":id", id); check = query.exec(); if (!check) { logger.log(logger.MEDIUM, "SharedNotebook Table exists check failed."); logger.log(logger.MEDIUM, query.lastError().toString()); } if (query.next()) return true; logger.log(logger.EXTREME, "Shared notebook " +id +" does not exist"); return false; } // Delete the notebook based on a id public void expungeNotebook(long id, boolean needsSync) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("delete from SharedNotebook " +"where id=:id"); if (!check) { logger.log(logger.EXTREME, "SharedNotebook SQL delete prepare has failed."); logger.log(logger.EXTREME, query.lastError().toString()); } query.bindValue(":id", id); check = query.exec(); if (!check) logger.log(logger.MEDIUM, "SharedNotebook delete failed."); // Signal the parent that work needs to be done if (needsSync) { DeletedTable deletedTable = new DeletedTable(logger, db); deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook"); } } // Delete the notebook based on a id public void expungeNotebookByGuid(String id, boolean needsSync) { boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("delete from SharedNotebook " +"where guid=:id"); if (!check) { logger.log(logger.EXTREME, "SharedNotebook SQL delete by notebook guid prepare has failed."); logger.log(logger.EXTREME, query.lastError().toString()); } query.bindValue(":id", id); check = query.exec(); if (!check) logger.log(logger.MEDIUM, "SharedNotebook delete by notebook guid failed."); // Signal the parent that work needs to be done if (needsSync) { DeletedTable deletedTable = new DeletedTable(logger, db); deletedTable.addDeletedItem(new Long(id).toString(), "SharedNotebook"); } } // Update a notebook public void updateNotebook(SharedNotebook tempNotebook, boolean isDirty) { logger.log(logger.EXTREME, "*Updating Shared Notebook*"); logger.log(logger.EXTREME, "ID: " + tempNotebook.getId()); logger.log(logger.EXTREME, "Userid: " + tempNotebook.getUserId()); logger.log(logger.EXTREME, "Email: " + tempNotebook.getEmail()); logger.log(logger.EXTREME, "Notebook Guid: " + tempNotebook.getNotebookGuid()); logger.log(logger.EXTREME, "Share Key: " + tempNotebook.getShareKey()); logger.log(logger.EXTREME, "Username: " + tempNotebook.getUsername()); boolean check; if (!exists(tempNotebook.getId())) { addNotebook(tempNotebook, isDirty); return; } List list = getAll(); logger.log(logger.EXTREME, "Dumping shared notebooks"); for (int i=0; i getAll() { SharedNotebook tempNotebook; List index = new ArrayList(); boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.exec("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " + "serviceCreated, "+ "shareKey, username from SharedNotebook"); if (!check) logger.log(logger.EXTREME, "Notebook SQL retrieve has failed."); while (query.next()) { tempNotebook = new SharedNotebook(); tempNotebook.setId(query.valueLong(0)); tempNotebook.setUserId(query.valueInteger(1)); tempNotebook.setNotebookGuid(query.valueString(2)); tempNotebook.setEmail(query.valueString(3)); tempNotebook.setNotebookModifiable(query.valueBoolean(4,false)); tempNotebook.setRequireLogin(query.valueBoolean(5,true)); DateFormat indfm = null; try { indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S"); } catch (Exception e) { } try { tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime()); } catch (ParseException e) { e.printStackTrace(); } tempNotebook.setShareKey(query.valueString(7)); tempNotebook.setUsername(query.valueString(8)); index.add(tempNotebook); } return index; } // Load notebooks from the database public List getForNotebook(String guid) { SharedNotebook tempNotebook; List index = new ArrayList(); boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.prepare("Select id, userid, notebookGuid, email, notebookModifiable, requireLogin, " + "serviceCreated, "+ "shareKey, username from SharedNotebook where notebookGuid=:notebookGuid "); if (!check) logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL prepare has failed."); query.bindValue(":notebookGuid", guid); check = query.exec(); if (!check) logger.log(logger.EXTREME, "SharedNotebook getForNotebook SQL exec has failed."); while (query.next()) { tempNotebook = new SharedNotebook(); tempNotebook.setId(query.valueLong(0)); tempNotebook.setUserId(query.valueInteger(1)); tempNotebook.setNotebookGuid(query.valueString(2)); tempNotebook.setEmail(query.valueString(3)); tempNotebook.setNotebookModifiable(query.valueBoolean(4,false)); tempNotebook.setRequireLogin(query.valueBoolean(5,true)); DateFormat indfm = null; try { indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S"); } catch (Exception e) { } try { tempNotebook.setServiceCreated(indfm.parse(query.valueString(6)).getTime()); } catch (ParseException e) { e.printStackTrace(); } tempNotebook.setShareKey(query.valueString(7)); tempNotebook.setUsername(query.valueString(8)); index.add(tempNotebook); } return index; } // Get a list of shared notebooks that need to be updated public List getDirtyIds() { List index = new ArrayList(); boolean check; NSqlQuery query = new NSqlQuery(db.getConnection()); check = query.exec("Select id from SharedNotebook where isDirty = true"); if (!check) logger.log(logger.EXTREME, "SharedNotebook SQL retrieve has failed in getdirtyIds."); while (query.next()) { index.add(query.valueLong(0)); } return index; } }