/*
* 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.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import com.evernote.edam.type.NoteSortOrder;
import com.evernote.edam.type.Notebook;
import com.evernote.edam.type.Publishing;
import com.trolltech.qt.core.QBuffer;
import com.trolltech.qt.core.QByteArray;
import com.trolltech.qt.core.QIODevice;
import com.trolltech.qt.gui.QIcon;
import com.trolltech.qt.gui.QImage;
import com.trolltech.qt.gui.QPixmap;
import cx.fbn.nevernote.Global;
import cx.fbn.nevernote.sql.driver.NSqlQuery;
import cx.fbn.nevernote.utilities.ApplicationLogger;
import cx.fbn.nevernote.utilities.Pair;
public class NotebookTable {
private final ApplicationLogger logger;
DatabaseConnection db;
private final String dbName;
NSqlQuery notebookCountQuery;
// Constructor
public NotebookTable(ApplicationLogger l, DatabaseConnection d) {
logger = l;
db = d;
dbName = "Notebook";
}
// Constructor
public NotebookTable(ApplicationLogger l, DatabaseConnection d, String name) {
logger = l;
db = d;
dbName = name;
}
// Create the table
public void createTable(boolean addDefaulte) {
NSqlQuery query = new NSqlQuery(db.getConnection());
logger.log(logger.HIGH, "Creating table "+dbName+"...");
if (!query.exec("Create table "+dbName+" (guid varchar primary key, " +
"sequence integer, " +
"name varchar, "+
"defaultNotebook varchar, "+
"serviceCreated timestamp, " +
"serviceUpdated timestamp, "+
"published boolean, "+
"isDirty boolean, "+
"autoEncrypt boolean, "+
"local boolean, "+
"archived boolean)"))
logger.log(logger.HIGH, "Table "+dbName+" creation FAILED!!!");
Notebook newnote = new Notebook();
newnote.setDefaultNotebook(true);
newnote.setName("My Notebook");
newnote.setPublished(false);
newnote.setGuid("1");
// Setup an initial notebook
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
query = new NSqlQuery(db.getConnection());
query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "
+"serviceCreated, serviceUpdated, published, "
+ "isDirty, autoEncrypt, "
+ "local, archived) Values("
+":guid, :sequence, :name, :defaultNotebook, "
+":serviceCreated, :serviceUpdated, :published, "
+":isDirty, :autoEncrypt, "
+":local, false)");
query.bindValue(":guid", newnote.getGuid());
query.bindValue(":sequence", newnote.getUpdateSequenceNum());
query.bindValue(":name", newnote.getName());
query.bindValue(":defaultNotebook", newnote.isDefaultNotebook());
StringBuilder serviceCreated = new StringBuilder(simple.format(newnote.getServiceCreated()));
StringBuilder serviceUpdated = new StringBuilder(simple.format(newnote.getServiceUpdated()));
if (serviceUpdated.toString() == null)
serviceUpdated = serviceCreated;
query.bindValue(":serviceCreated", serviceCreated.toString());
query.bindValue(":serviceUpdated", serviceCreated.toString());
query.bindValue(":published",newnote.isPublished());
query.bindValue(":isDirty", true);
query.bindValue(":autoEncrypt", false);
query.bindValue(":local", false);
boolean check = query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Initial "+dbName+" Table insert failed.");
logger.log(logger.MEDIUM, query.lastError().toString());
}
}
// Drop the table
public void dropTable() {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.exec("Drop table "+dbName);
}
// Save an individual notebook
public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local) {
addNotebook(tempNotebook, isDirty, local, false, false);
}
// Save an individual notebook
public void addNotebook(Notebook tempNotebook, boolean isDirty, boolean local, boolean linked, boolean readOnly) {
boolean check;
SimpleDateFormat simple = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Insert Into "+dbName+" (guid, sequence, name, defaultNotebook, "
+"serviceCreated, serviceUpdated, published, "
+ "publishingUri, publishingOrder, publishingAscending, publishingPublicDescription, "
+ "isDirty, autoEncrypt, stack, "
+ "local, archived, readOnly, linked) Values("
+":guid, :sequence, :name, :defaultNotebook, "
+":serviceCreated, :serviceUpdated, :published, "
+":publishingUri, :publishingOrder, :publishingAscending, :publishingPublicDescription, "
+":isDirty, :autoEncrypt, "
+":stack, :local, false, :readOnly, :linked)");
query.bindValue(":guid", tempNotebook.getGuid());
query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());
query.bindValue(":name", tempNotebook.getName());
query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());
StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));
StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));
if (serviceUpdated.toString() == null)
serviceUpdated = serviceCreated;
query.bindValue(":serviceCreated", serviceCreated.toString());
query.bindValue(":serviceUpdated", serviceCreated.toString());
query.bindValue(":published",tempNotebook.isPublished());
query.bindValue(":linked", linked);
query.bindValue(":readOnly", readOnly);
if (tempNotebook.isPublished() && tempNotebook.getPublishing() != null) {
Publishing p = tempNotebook.getPublishing();
query.bindValue(":publishingUri", p.getUri());
query.bindValue(":publishingOrder", p.getOrder().getValue());
query.bindValue(":publishingAscending", p.isAscending());
query.bindValue(":publishingPublicDescription", p.getPublicDescription());
} else {
query.bindValue(":publishingUri", "");
query.bindValue(":publishingOrder", 1);
query.bindValue(":publishingAscending", 1);
query.bindValue(":publishingPublicDescription", "");
}
if (isDirty)
query.bindValue(":isDirty", true);
else
query.bindValue(":isDirty", false);
query.bindValue(":autoEncrypt", false);
query.bindValue(":local", local);
query.bindValue(":stack", tempNotebook.getStack());
check = query.exec();
if (!check) {
logger.log(logger.MEDIUM, ""+dbName+" Table insert failed.");
logger.log(logger.MEDIUM, query.lastError().toString());
}
}
// Delete the notebook based on a guid
public void expungeNotebook(String guid, boolean needsSync) {
boolean check;
Notebook n;
n = getNotebook(guid);
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("delete from "+dbName+" where guid=:guid");
if (!check) {
logger.log(logger.EXTREME, dbName+" SQL delete prepare has failed.");
logger.log(logger.EXTREME, query.lastError().toString());
}
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.MEDIUM, dbName+" delete failed.");
// Signal the parent that work needs to be done
if (needsSync && n!=null && n.getUpdateSequenceNum() > 0) {
DeletedTable deletedTable = new DeletedTable(logger, db);
deletedTable.addDeletedItem(guid, dbName);
}
}
// Update a notebook
public void updateNotebook(Notebook 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("Update "+dbName+" set sequence=:sequence, name=:name, defaultNotebook=:defaultNotebook, " +
"serviceCreated=:serviceCreated, serviceUpdated=:serviceUpdated, "+
"published=:published, isDirty=:isDirty, publishinguri=:uri, "+
"publishingOrder=:order, " +
"publishingAscending=:ascending, " +
"publishingPublicDescription=:desc, " +
"stack=:stack " +
"where guid=:guid ");
query.bindValue(":sequence", tempNotebook.getUpdateSequenceNum());
query.bindValue(":name", tempNotebook.getName());
query.bindValue(":defaultNotebook", tempNotebook.isDefaultNotebook());
StringBuilder serviceCreated = new StringBuilder(simple.format(tempNotebook.getServiceCreated()));
StringBuilder serviceUpdated = new StringBuilder(simple.format(tempNotebook.getServiceUpdated()));
query.bindValue(":serviceCreated", serviceCreated.toString());
query.bindValue(":serviceUpdated", serviceUpdated.toString());
query.bindValue(":published", tempNotebook.isPublished());
query.bindValue(":isDirty", isDirty);
if (tempNotebook.isPublished()) {
query.bindValue(":uri", tempNotebook.getPublishing().getUri());
query.bindValue(":order", tempNotebook.getPublishing().getOrder().getValue());
query.bindValue(":ascending", tempNotebook.getPublishing().isAscending());
query.bindValue(":desc", tempNotebook.getPublishing().getPublicDescription());
} else {
query.bindValue(":uri", "");
query.bindValue(":order", NoteSortOrder.CREATED.getValue());
query.bindValue(":ascending", false);
query.bindValue(":desc", "");
}
query.bindValue(":guid", tempNotebook.getGuid());
query.bindValue(":stack", tempNotebook.getStack());
check = query.exec();
if (!check) {
logger.log(logger.MEDIUM, dbName+" Table update failed.");
logger.log(logger.MEDIUM, query.lastError().toString());
}
}
// Load notebooks from the database
public List<Notebook> getAll() {
Notebook tempNotebook;
List<Notebook> index = new ArrayList<Notebook>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.exec("Select guid, sequence, name, defaultNotebook, " +
"serviceCreated, "+
"serviceUpdated, "+
"published, stack, publishinguri, publishingascending, publishingPublicDescription, "+
"publishingOrder from "+dbName+" order by name");
if (!check)
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
while (query.next()) {
tempNotebook = new Notebook();
tempNotebook.setGuid(query.valueString(0));
int sequence = new Integer(query.valueString(1)).intValue();
tempNotebook.setUpdateSequenceNum(sequence);
tempNotebook.setName(query.valueString(2));
tempNotebook.setDefaultNotebook(query.valueBoolean(3, false));
DateFormat indfm = null;
try {
indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
// indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
} catch (Exception e) { }
try {
tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
} catch (ParseException e) {
e.printStackTrace();
}
tempNotebook.setPublished(new Boolean(query.valueString(6)));
tempNotebook.setStack(query.valueString(7));
if (tempNotebook.isPublished()) {
Publishing p = new Publishing();
p.setUri(query.valueString(8));
p.setAscending(query.valueBoolean(9, false));
p.setPublicDescription(query.valueString(10));
p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
tempNotebook.setPublishing(p);
}
index.add(tempNotebook);
}
return index;
}
public List<Notebook> getAllLocal() {
Notebook tempNotebook;
List<Notebook> index = new ArrayList<Notebook>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.exec("Select guid, sequence, name, defaultNotebook, " +
"serviceCreated, serviceUpdated, published, stack from "+dbName+" where local=true order by name");
if (!check)
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
while (query.next()) {
tempNotebook = new Notebook();
tempNotebook.setGuid(query.valueString(0));
int sequence = new Integer(query.valueString(1)).intValue();
tempNotebook.setUpdateSequenceNum(sequence);
tempNotebook.setName(query.valueString(2));
DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
// indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
try {
tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
} catch (ParseException e) {
e.printStackTrace();
}
if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))
tempNotebook.setStack(query.valueString(7));
index.add(tempNotebook);
}
return index;
}
// Archive or un-archive a notebook
public void setArchived(String guid, boolean val) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Update "+dbName+" set archived=:archived where guid=:guid");
if (!check)
logger.log(logger.EXTREME, dbName+" SQL archive update has failed.");
query.bindValue(":guid", guid);
query.bindValue(":archived", val);
query.exec();
}
// Load non-archived notebooks from the database
public List<Notebook> getAllArchived() {
Notebook tempNotebook;
List<Notebook> index = new ArrayList<Notebook>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.exec("Select guid, sequence, name, defaultNotebook, " +
"serviceCreated, serviceUpdated, published, stack, "+
"publishinguri, publishingascending, publishingPublicDescription, "+
"publishingOrder " +
"from "+dbName+" where archived=true order by name");
if (!check)
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
while (query.next()) {
tempNotebook = new Notebook();
tempNotebook.setGuid(query.valueString(0));
int sequence = new Integer(query.valueString(1)).intValue();
tempNotebook.setUpdateSequenceNum(sequence);
tempNotebook.setName(query.valueString(2));
DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
// indfm = new SimpleDateFormat("EEE MMM dd HH:mm:ss yyyy");
try {
tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
} catch (ParseException e) {
e.printStackTrace();
}
tempNotebook.setPublished(new Boolean(query.valueString(6)));
if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))
tempNotebook.setStack(query.valueString(7));
if (tempNotebook.isPublished()) {
Publishing p = new Publishing();
p.setUri(query.valueString(8));
p.setAscending(query.valueBoolean(9, false));
p.setPublicDescription(query.valueString(10));
p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
tempNotebook.setPublishing(p);
}
index.add(tempNotebook);
}
return index;
}
// Check for a local/remote notebook
public boolean isNotebookLocal(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select local from "+dbName+" where guid=:guid");
query.bindValue(":guid", guid);
query.exec();
if (!query.next()) {
return false;
}
boolean returnValue = query.valueBoolean(0, false);
return returnValue;
}
// Check for a local/remote notebook
public boolean isNotebookLinked(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select linked from "+dbName+" where guid=:guid");
query.bindValue(":guid", guid);
query.exec();
if (!query.next()) {
return false;
}
boolean returnValue = query.valueBoolean(0, false);
return returnValue;
}
public boolean isReadOnly(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select readOnly from "+dbName+" where guid=:guid and readOnly=true");
query.bindValue(":guid", guid);
query.exec();
if (!query.next()) {
return false;
}
boolean returnValue = query.valueBoolean(0, false);
return returnValue;
}
// Update a notebook sequence number
public void updateNotebookSequence(String guid, int sequence) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Update "+dbName+" set sequence=:sequence where guid=:guid");
query.bindValue(":guid", guid);
query.bindValue(":sequence", sequence);
query.exec();
if (!check) {
logger.log(logger.MEDIUM, dbName+" sequence update failed.");
logger.log(logger.MEDIUM, query.lastError());
}
}
// Update a notebook GUID number
public void updateNotebookGuid(String oldGuid, String newGuid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update "+dbName+" set guid=:newGuid where guid=:oldGuid");
query.bindValue(":oldGuid", oldGuid);
query.bindValue(":newGuid", newGuid);
if (!query.exec()) {
logger.log(logger.MEDIUM, dbName+" guid update failed.");
logger.log(logger.MEDIUM, query.lastError());
}
// Update any notes containing the notebook guid
query.prepare("Update Note set notebookGuid=:newGuid where notebookGuid=:oldGuid");
query.bindValue(":oldGuid", oldGuid);
query.bindValue(":newGuid", newGuid);
if (!query.exec()) {
logger.log(logger.MEDIUM, dbName+" guid update for note failed.");
logger.log(logger.MEDIUM, query.lastError());
}
// Update any watch folders with the new guid
query = new NSqlQuery(db.getConnection());
query.prepare("Update WatchFolders set notebook=:newGuid where notebook=:oldGuid");
query.bindValue(":oldGuid", oldGuid);
query.bindValue(":newGuid", newGuid);
if (!query.exec()) {
logger.log(logger.MEDIUM, "Update WatchFolder notebook failed.");
logger.log(logger.MEDIUM, query.lastError().toString());
}
}
// Get a list of notes that need to be updated
public List <Notebook> getDirty() {
Notebook tempNotebook;
List<Notebook> index = new ArrayList<Notebook>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.exec("Select guid, sequence, name, defaultNotebook, " +
"serviceCreated, serviceUpdated, published, stack, "+
"publishinguri, publishingascending, publishingPublicDescription, "+
"publishingOrder " +
"from "+dbName+" where isDirty=true and local=false and linked=false");
if (!check)
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
while (query.next()) {
tempNotebook = new Notebook();
tempNotebook.setGuid(query.valueString(0));
int sequence = new Integer(query.valueString(1)).intValue();
tempNotebook.setUpdateSequenceNum(sequence);
tempNotebook.setName(query.valueString(2));
DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
try {
tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
} catch (ParseException e) {
e.printStackTrace();
}
tempNotebook.setPublished(new Boolean(query.valueString(6)));
if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))
tempNotebook.setStack(query.valueString(7));
if (tempNotebook.isPublished()) {
Publishing p = new Publishing();
p.setUri(query.valueString(8));
p.setAscending(query.valueBoolean(9, false));
p.setPublicDescription(query.valueString(10));
p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))
p.setPublicDescription(null);
tempNotebook.setPublishing(p);
}
index.add(tempNotebook);
}
return index;
}
// Get a list of notes that need to be updated
public Notebook getNotebook(String guid) {
Notebook tempNotebook;
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select guid, sequence, name, defaultNotebook, " +
"serviceCreated, serviceUpdated, published, stack, "+
"publishinguri, publishingascending, publishingPublicDescription, "+
"publishingOrder " +
"from "+dbName+" where guid=:guid");
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
while (query.next()) {
tempNotebook = new Notebook();
tempNotebook.setGuid(query.valueString(0));
int sequence = new Integer(query.valueString(1)).intValue();
tempNotebook.setUpdateSequenceNum(sequence);
tempNotebook.setName(query.valueString(2));
DateFormat indfm = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.S");
try {
tempNotebook.setServiceCreated(indfm.parse(query.valueString(4)).getTime());
tempNotebook.setServiceUpdated(indfm.parse(query.valueString(5)).getTime());
} catch (ParseException e) {
e.printStackTrace();
}
tempNotebook.setPublished(new Boolean(query.valueString(6)));
if (query.valueString(7) != null && !query.valueString(7).trim().equals(""))
tempNotebook.setStack(query.valueString(7));
if (tempNotebook.isPublished()) {
Publishing p = new Publishing();
p.setUri(query.valueString(8));
p.setAscending(query.valueBoolean(9, false));
p.setPublicDescription(query.valueString(10));
p.setOrder(NoteSortOrder.findByValue(query.valueInteger(11)));
if (p.getPublicDescription() != null && p.getPublicDescription().trim().equalsIgnoreCase(""))
p.setPublicDescription(null);
tempNotebook.setPublishing(p);
}
return tempNotebook;
}
return null;
}
// This is a convience method to check if a tag exists & update/create based upon it
public void syncNotebook(Notebook notebook, boolean isDirty) {
if (!exists(notebook.getGuid())) {
addNotebook(notebook, isDirty, isDirty);
return;
}
updateNotebook(notebook, isDirty);
}
// This is a convience method to check if a tag exists & update/create based upon it
public void syncLinkedNotebook(Notebook notebook, boolean isDirty, boolean readOnly) {
if (!exists(notebook.getGuid())) {
addNotebook(notebook, isDirty, false, true, readOnly);
return;
}
updateNotebook(notebook, isDirty);
}
// does a record exist?
private boolean exists(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select guid from "+dbName+" where guid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
boolean retval = query.next();
return retval;
}
// Reset the dirty flag. Typically done after a sync.
public void resetDirtyFlag(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update "+dbName+" set isdirty='false' where guid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error resetting "+dbName+" dirty field.");
}
// Set the default notebook
public void setDefaultNotebook(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update "+dbName+" set defaultNotebook=false, isDirty=true where linked=false and defaultNotebook=true");
if (!query.exec())
logger.log(logger.EXTREME, "Error removing default "+dbName+".");
query.prepare("Update "+dbName+" set defaultNotebook=true, isDirty=true where guid=:guid and linked=false");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error setting default "+dbName+".");
}
// Get a list of all icons
public HashMap<String, QIcon> getAllIcons() {
HashMap<String, QIcon> values = new HashMap<String, QIcon>();
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.exec("SELECT guid, icon from "+dbName+" where ARCHIVED != true"))
logger.log(logger.EXTREME, "Error executing "+dbName+" getAllIcons select.");
while (query.next()) {
if (query.getBlob(1) != null) {
String guid = query.valueString(0);
QByteArray blob = new QByteArray(query.getBlob(1));
QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));
values.put(guid, icon);
}
}
return values;
}
// Get the notebooks custom icon
public QIcon getIcon(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");
if (!query.next() || query.getBlob(0) == null)
return null;
QByteArray blob = new QByteArray(query.getBlob(0));
QIcon icon = new QIcon(QPixmap.fromImage(QImage.fromData(blob)));
return icon;
}
// Get the notebooks custom icon
public QByteArray getIconAsByteArray(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.prepare("Select icon from "+dbName+" where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error finding "+dbName+" icon.");
if (!query.next() || query.getBlob(0) == null)
return null;
QByteArray blob = new QByteArray(query.getBlob(0));
return blob;
}
// Set the notebooks custom icon
public void setIcon(String guid, QIcon icon, String type) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (icon == null) {
if (!query.prepare("update "+dbName+" set icon=null where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
} else {
if (!query.prepare("update "+dbName+" set icon=:icon where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing "+dbName+" icon select.");
QBuffer buffer = new QBuffer();
if (!buffer.open(QIODevice.OpenModeFlag.ReadWrite)) {
logger.log(logger.EXTREME, "Failure to open buffer. Aborting.");
return;
}
QPixmap p = icon.pixmap(32, 32);
QImage i = p.toImage();
i.save(buffer, type.toUpperCase());
buffer.close();
QByteArray b = new QByteArray(buffer.buffer());
if (!b.isNull() && !b.isEmpty())
query.bindValue(":icon", b.toByteArray());
else
return;
}
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.LOW, "Error setting "+dbName+" icon. " +query.lastError());
}
// Set the notebooks custom icon
public void setReadOnly(String guid, boolean readOnly) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.prepare("update "+dbName+" set readOnly=:readOnly where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing "+dbName+" read only.");
query.bindValue(":guid", guid);
query.bindValue(":readOnly", readOnly);
if (!query.exec())
logger.log(logger.LOW, "Error setting "+dbName+" read only. " +query.lastError());
}
// does a record exist?
public String findNotebookByName(String newname) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select guid from "+dbName+" where name=:newname");
query.bindValue(":newname", newname);
if (!query.exec())
logger.log(logger.EXTREME, dbName+" SQL retrieve has failed.");
String val = null;
if (query.next())
val = query.valueString(0);
return val;
}
// Get a note tag counts
public List<Pair<String,Integer>> getNotebookCounts() {
List<Pair<String,Integer>> counts = new ArrayList<Pair<String,Integer>>();
if (notebookCountQuery == null) {
notebookCountQuery = new NSqlQuery(db.getConnection());
notebookCountQuery.prepare("select notebookGuid, count(guid) from note where active=1 group by notebookguid;");
}
if (!notebookCountQuery.exec()) {
logger.log(logger.EXTREME, "NoteTags SQL getTagCounts has failed.");
logger.log(logger.MEDIUM, notebookCountQuery.lastError());
return null;
}
while (notebookCountQuery.next()) {
Pair<String,Integer> newCount = new Pair<String,Integer>();
newCount.setFirst(notebookCountQuery.valueString(0));
newCount.setSecond(notebookCountQuery.valueInteger(1));
counts.add(newCount);
}
return counts;
}
// Get/Set stacks
public void clearStack(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update "+dbName+" set stack='' where guid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error clearing "+dbName+" stack.");
}
// Get/Set stacks
public void setStack(String guid, String stack) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update "+dbName+" set stack=:stack, isDirty=true where guid=:guid");
query.bindValue(":guid", guid);
query.bindValue(":stack", stack);
if (!query.exec())
logger.log(logger.EXTREME, "Error setting notebook stack.");
}
// Get all stack names
public List<String> getAllStackNames() {
List<String> stacks = new ArrayList<String>();
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.exec("Select distinct stack from "+dbName)) {
logger.log(logger.EXTREME, "Error getting all stack names.");
return null;
}
while (query.next()) {
if (query.valueString(0) != null && !query.valueString(0).trim().equals(""))
stacks.add(query.valueString(0));
}
return stacks;
}
// Rename a stack
public void renameStacks(String oldName, String newName) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.prepare("update "+dbName+" set stack=:newName where stack=:oldName")) {
logger.log(logger.EXTREME, "Error preparing in renameStacks.");
return;
}
query.bindValue(":oldName", oldName);
query.bindValue(":newName", newName);
if (!query.exec()) {
logger.log(logger.EXTREME, "Error updating stack names");
return;
}
if (!query.prepare("update SystemIcon set name=:newName where name=:oldName and type='STACK'")) {
logger.log(logger.EXTREME, "Error preparing icon rename in renameStacks.");
return;
}
query.bindValue(":oldName", oldName);
query.bindValue(":newName", newName);
if (!query.exec()) {
logger.log(logger.EXTREME, "Error updating stack names for SystemIcon");
return;
}
}
// Get/Set stacks
public boolean stackExists(String stack) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select guid from "+dbName+" where stack=:stack limit 1");
query.bindValue(":stack", stack);
if (!query.exec())
logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");
if (query.next())
return true;
else
return false;
}
// Set Publishing
public void setPublishing(String guid, boolean published, Publishing p) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update "+dbName+" set publishingPublicDescription=:publishingPublicDescription, " +
"publishingUri=:publishingUri, publishingOrder=:publishingOrder, published=:published, "+
"publishingAscending=:publishingAscending, isdirty=true where "+
"guid=:guid");
query.bindValue(":publishingPublicDescription", p.getPublicDescription());
query.bindValue(":publishingUri", p.getUri());
query.bindValue(":publishingOrder", p.getOrder().getValue());
query.bindValue(":publishingAscending", p.isAscending());
query.bindValue(":publishingPublicDescription", p.getPublicDescription());
query.bindValue(":published", published);
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error setting "+dbName+" stack.");
}
// Get a notebook by uri
public String getNotebookByUri(String uri) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Select guid "
+"from "+dbName+" where publishingUri=:uri");
query.bindValue(":uri", uri);
check = query.exec();
if (!check)
logger.log(logger.EXTREME, "Notebook SQL retrieve guid by uri has failed.");
if (query.next()) {
return query.valueString(0);
}
return null;
}
// Get a notebook's sort order
public int getSortColumn(String guid) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
if (Global.getSortOrder() != Global.View_List_Wide)
check = query.prepare("Select wide_sort_column "
+"from "+dbName+" where guid=:guid");
else
check = query.prepare("Select narrow_sort_column "
+"from "+dbName+" where guid=:guid");
query.bindValue(":guid", guid);
check = query.exec();
if (!check) {
logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");
return -1;
}
if (query.next()) {
return query.valueInteger(0);
}
return -1;
}
// Get a notebook's sort order
public int getSortOrder(String guid) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
if (Global.getSortOrder() != Global.View_List_Wide)
check = query.prepare("Select wide_sort_order "
+"from "+dbName+" where guid=:guid");
else
check = query.prepare("Select narrow_sort_order "
+"from "+dbName+" where guid=:guid");
query.bindValue(":guid", guid);
check = query.exec();
if (!check) {
logger.log(logger.EXTREME, "Notebook SQL retrieve sort order has failed.");
return -1;
}
if (query.next()) {
return query.valueInteger(0);
}
return -1;
}
// Get a notebook's sort order
public void setSortOrder(String guid, int column, int order) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
if (Global.getSortOrder() != Global.View_List_Wide)
check = query.prepare("Update "+dbName+" set wide_sort_order=:order, wide_sort_column=:column where guid=:guid");
else
check = query.prepare("Update "+dbName+" set narrow_sort_order=:order, narrow_sort_column=:column where guid=:guid");
query.bindValue(":guid", guid);
query.bindValue(":order", order);
query.bindValue(":column", column);
check = query.exec();
if (!check)
logger.log(logger.EXTREME, "Notebook SQL set sort order has failed.");
}
// Is a notebook a linked notebook?
public boolean isLinked(String guid) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Select guid "
+"from "+dbName+" where guid=:guid and linked=true");
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.EXTREME, "Notebook SQL isLinked failed.");
if (query.next()) {
return true;
}
return false;
}
// Given a notebook, what tags are valid for it?
public List<String> getValidLinkedTags(String guid) {
boolean check;
List<String> tags = new ArrayList<String>();
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("select distinct tagGuid from noteTags " +
"where noteGuid in " +
"(SELECT guid from note where notebookguid=:guid)");
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");
while (query.next()) {
tags.add(query.valueString(0));
}
return tags;
}
// Given a notebook, what tags are valid for it?
public void deleteLinkedTags(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("select distinct tagguid from noteTags " +
"where noteGuid in " +
"(SELECT guid from note where notebookguid=:guid)");
query.bindValue(":guid", guid);
boolean check = query.exec();
if (!check)
logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");
while(query.next()) {
db.getTagTable().expungeTag(query.valueString(0), false);
}
query.prepare("delete from note " +
"where notebookguid=:guid");
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.EXTREME, "Notebook SQL getValidLinedTags failed.");
return;
}
// Given a notebook, what tags are valid for it?
public void convertFromSharedNotebook(String guid, boolean local) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update Notebook set sequence=0, published=false, isdirty=true, local=:local, publishinguri=''"
+" where guid=:guid");
query.bindValue(":guid", guid);
if (local)
query.bindValue(":local", true);
else
query.bindValue(":local", false);
if (!query.exec())
logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook error.");
query.prepare("Update Note set updatesequencenumber=0, isdirty=true"
+" where notebookguid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "NotebookTable.convertToLocalNotebook #2 error.");
return;
}
}