/*
* 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.HashMap;
import java.util.List;
import com.evernote.edam.type.Tag;
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.sql.driver.NSqlQuery;
import cx.fbn.nevernote.utilities.ApplicationLogger;
public class TagTable {
private final ApplicationLogger logger;
DatabaseConnection db;
public TagTable (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 Tag...");
if (!query.exec("Create table Tag (guid varchar primary key, " +
"parentGuid varchar, sequence integer, hashCode integer, name varchar, isDirty boolean)"))
logger.log(logger.HIGH, "Table TAG creation FAILED!!!");
}
// Drop the table
public void dropTable() {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.exec("Drop table Tag");
}
// Get tags for a specific notebook
// get all tags
public List<Tag> getTagsForNotebook(String notebookGuid) {
Tag tempTag;
List<Tag> index = new ArrayList<Tag>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Select guid, parentGuid, sequence, name"
+" from Tag where notebookGuid=:notebookGuid");
if (!check) {
logger.log(logger.EXTREME, "Tag SQL prepare getTagsForNotebook has failed.");
logger.log(logger.EXTREME, query.lastError());
}
query.bindValue(":notebookGuid", notebookGuid);
query.exec();
while (query.next()) {
tempTag = new Tag();
tempTag.setGuid(query.valueString(0));
if (query.valueString(1) != null)
tempTag.setParentGuid(query.valueString(1));
else
tempTag.setParentGuid(null);
int sequence = new Integer(query.valueString(2)).intValue();
tempTag.setUpdateSequenceNum(sequence);
tempTag.setName(query.valueString(3));
index.add(tempTag);
}
return index;
}
// get all tags
public List<Tag> getAll() {
Tag tempTag;
List<Tag> index = new ArrayList<Tag>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.exec("Select guid, parentGuid, sequence, name"
+" from Tag where notebookguid not in (select guid from notebook where archived=true)");
if (!check) {
logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");
logger.log(logger.EXTREME, query.lastError());
}
while (query.next()) {
tempTag = new Tag();
tempTag.setGuid(query.valueString(0));
if (query.valueString(1) != null)
tempTag.setParentGuid(query.valueString(1));
else
tempTag.setParentGuid(null);
int sequence = new Integer(query.valueString(2)).intValue();
tempTag.setUpdateSequenceNum(sequence);
tempTag.setName(query.valueString(3));
index.add(tempTag);
}
return index;
}
public Tag getTag(String guid) {
Tag tempTag = new Tag();
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.prepare("Select guid, parentGuid, sequence, name"
+" from Tag where guid=:guid"))
logger.log(logger.EXTREME, "Tag select by guid SQL prepare has failed.");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Tag select by guid SQL exec has failed.");
if (!query.next()) {
return tempTag;
}
tempTag.setGuid(query.valueString(0));
tempTag.setParentGuid(query.valueString(1));
int sequence = new Integer(query.valueString(2)).intValue();
tempTag.setUpdateSequenceNum(sequence);
tempTag.setName(query.valueString(3));
return tempTag;
}
// Update a tag
public void updateTag(Tag tempTag, boolean isDirty) {
updateTag(tempTag, isDirty, "");
}
// Update a tag
public void updateTag(Tag tempTag, boolean isDirty, String realName) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Update Tag set parentGuid=:parentGuid, sequence=:sequence, "+
"hashCode=:hashCode, name=:name, isDirty=:isDirty "
+"where guid=:guid");
if (!check) {
logger.log(logger.EXTREME, "Tag SQL update prepare has failed.");
logger.log(logger.EXTREME, query.lastError());
}
query.bindValue(":parentGuid", tempTag.getParentGuid());
query.bindValue(":sequence", tempTag.getUpdateSequenceNum());
query.bindValue(":hashCode", tempTag.hashCode());
query.bindValue(":name", tempTag.getName());
query.bindValue(":isDirty", isDirty);
query.bindValue(":guid", tempTag.getGuid());
check = query.exec();
if (!check)
logger.log(logger.MEDIUM, "Tag Table update failed.");
}
// Delete a tag
public void expungeTag(String guid, boolean needsSync) {
boolean check;
Tag t = getTag(guid);
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("delete from Tag "
+"where guid=:guid");
if (!check) {
logger.log(logger.EXTREME, "Tag SQL delete prepare has failed.");
logger.log(logger.EXTREME, query.lastError());
}
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.MEDIUM, "Tag delete failed.");
check = query.prepare("delete from NoteTags "
+"where tagGuid=:guid");
if (!check) {
logger.log(logger.EXTREME, "NoteTags SQL delete prepare has failed.");
logger.log(logger.EXTREME, query.lastError());
}
query.bindValue(":guid", guid);
check = query.exec();
if (!check)
logger.log(logger.MEDIUM, "NoteTags delete failed.");
// Add the work to the parent queue
if (needsSync && t!= null && t.getUpdateSequenceNum() > 0) {
DeletedTable del = new DeletedTable(logger, db);
del.addDeletedItem(guid, "Tag");
}
}
// Save a tag
public void addTag(Tag tempTag, boolean isDirty) {
addTag(tempTag, isDirty, false, "", "");
}
// Save a tag
public void addTag(Tag tempTag, boolean isDirty, boolean isLinked, String realName, String notebookGuid) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Insert Into Tag (guid, parentGuid, sequence, hashCode, name, isDirty, linked, realName, notebookGuid)"
+" Values(:guid, :parentGuid, :sequence, :hashCode, :name, :isDirty, :linked, :realName, :notebookGuid)");
if (!check) {
logger.log(logger.EXTREME, "Tag SQL insert prepare has failed.");
logger.log(logger.EXTREME, query.lastError());
}
query.bindValue(":guid", tempTag.getGuid());
query.bindValue(":parentGuid", tempTag.getParentGuid());
query.bindValue(":sequence", tempTag.getUpdateSequenceNum());
query.bindValue(":hashCode", tempTag.hashCode());
query.bindValue(":name", tempTag.getName());
query.bindValue(":isDirty", isDirty);
query.bindValue(":linked", isLinked);
query.bindValue(":realName", realName);
query.bindValue(":notebookGuid", notebookGuid);
check = query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Tag Table insert failed.");
logger.log(logger.MEDIUM, query.lastError());
}
}
// Update a tag's parent
public void updateTagParent(String guid, String parentGuid) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Update Tag set parentGuid=:parentGuid where guid=:guid");
if (!check) {
logger.log(logger.EXTREME, "Tag SQL tag parent update prepare has failed.");
logger.log(logger.EXTREME, query.lastError());
}
query.bindValue(":parentGuid", parentGuid);
query.bindValue(":guid", guid);
check = query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Tag parent update failed.");
logger.log(logger.MEDIUM, query.lastError());
}
}
//Save tags from Evernote
public void saveTags(List<Tag> tags) {
Tag tempTag;
for (int i=0; i<tags.size(); i++) {
tempTag = tags.get(i);
addTag(tempTag, false);
}
}
// Update a tag sequence number
public void updateTagSequence(String guid, int sequence) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Update Tag set sequence=:sequence where guid=:guid");
query.bindValue(":sequence", sequence);
query.bindValue(":guid", guid);
query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Tag sequence update failed.");
logger.log(logger.MEDIUM, query.lastError());
}
}
// Update a tag sequence number
public void updateTagGuid(String oldGuid, String newGuid) {
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.prepare("Update Tag set guid=:newGuid where guid=:oldGuid");
query.bindValue(":newGuid", newGuid);
query.bindValue(":oldGuid", oldGuid);
query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Tag guid update failed.");
logger.log(logger.MEDIUM, query.lastError());
}
check = query.prepare("Update Tag set parentGuid=:newGuid where parentGuid=:oldGuid");
query.bindValue(":newGuid", newGuid);
query.bindValue(":oldGuid", oldGuid);
query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Tag guid update failed.");
logger.log(logger.MEDIUM, query.lastError());
}
check = query.prepare("Update NoteTags set tagGuid=:newGuid where tagGuid=:oldGuid");
query.bindValue(":newGuid", newGuid);
query.bindValue(":oldGuid", oldGuid);
query.exec();
if (!check) {
logger.log(logger.MEDIUM, "Tag guid update failed for NoteTags.");
logger.log(logger.MEDIUM, query.lastError());
}
}
// Get dirty tags
public List<Tag> getDirty() {
Tag tempTag;
List<Tag> index = new ArrayList<Tag>();
boolean check;
NSqlQuery query = new NSqlQuery(db.getConnection());
check = query.exec("Select guid, parentGuid, sequence, name"
+" from Tag where isDirty = true");
if (!check)
logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");
while (query.next()) {
tempTag = new Tag();
tempTag.setGuid(query.valueString(0));
tempTag.setParentGuid(query.valueString(1));
int sequence = new Integer(query.valueString(2)).intValue();
tempTag.setUpdateSequenceNum(sequence);
tempTag.setName(query.valueString(3));
if (tempTag.getParentGuid() != null && tempTag.getParentGuid().equals(""))
tempTag.setParentGuid(null);
index.add(tempTag);
}
return index;
}
// Find a guid based upon the name
public String findTagByName(String name) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select guid from tag where name=:name");
query.bindValue(":name", name);
if (!query.exec())
logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");
String val = null;
if (query.next())
val = query.valueString(0);
return val;
}
// Get the linked notebook guid for this tag
public String getNotebookGuid(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select notebookguid from tag where guid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");
String val = null;
if (query.next())
val = query.valueString(0);
return val;
}
// given a guid, does the tag exist
public boolean exists(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Select guid from tag where guid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Tag SQL retrieve has failed.");
boolean retval = query.next();
return retval;
}
// This is a convience method to check if a tag exists & update/create based upon it
public void syncLinkedTag(Tag tag, String notebookGuid, boolean isDirty) {
if (exists(tag.getGuid())) {
Tag t = getTag(tag.getGuid());
String realName = tag.getName();
tag.setName(t.getName());
updateTag(tag, isDirty, realName);
}
else
addTag(tag, isDirty, true, tag.getName(), notebookGuid);
}
// This is a convience method to check if a tag exists & update/create based upon it
public void syncTag(Tag tag, boolean isDirty) {
if (exists(tag.getGuid()))
updateTag(tag, isDirty);
else
addTag(tag, isDirty);
}
public void resetDirtyFlag(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.prepare("Update tag set isdirty=false where guid=:guid");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error resetting tag dirty field.");
}
// Get the custom icon
public QIcon getIcon(String guid) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (!query.prepare("Select icon from tag where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing tag icon select.");
query.bindValue(":guid", guid);
if (!query.exec())
logger.log(logger.EXTREME, "Error finding tag 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;
}
// Set the custom icon
public void setIcon(String guid, QIcon icon, String type) {
NSqlQuery query = new NSqlQuery(db.getConnection());
if (icon == null) {
if (!query.prepare("update tag set icon=null where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing tag icon update.");
} else {
if (!query.prepare("update tag set icon=:icon where guid=:guid"))
logger.log(logger.EXTREME, "Error preparing tag icon update.");
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 tag icon. " +query.lastError());
}
// 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 tag"))
logger.log(logger.EXTREME, "Error executing SavedSearch 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;
}
// Remove unused tags that are linked tags
public void removeUnusedLinkedTags() {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.exec("Delete from tag where linked=true and guid not in (select distinct tagguid from notetags);");
}
public void cleanupTags() {
NSqlQuery query = new NSqlQuery(db.getConnection());
query.exec("Update tag set parentguid=null where parentguid not in (select distinct guid from tag);");
}
public List<String> findChildren(String guid, List<Tag> tagList) {
List<String> returnValue = new ArrayList<String>();
for (int i=0; i<tagList.size(); i++) {
if (tagList.get(i).getParentGuid().equalsIgnoreCase(guid)) {
returnValue.add(tagList.get(i).getName());
List<String> childMatch = findChildren(tagList.get(i).getGuid(), tagList);
for (int j=0; j<childMatch.size(); j++) {
returnValue.add(childMatch.get(j));
}
}
}
return returnValue;
}
}