summaryrefslogtreecommitdiffstats
path: root/src/digikam/albumdb.cpp
diff options
context:
space:
mode:
Diffstat (limited to 'src/digikam/albumdb.cpp')
-rw-r--r--src/digikam/albumdb.cpp1599
1 files changed, 1599 insertions, 0 deletions
diff --git a/src/digikam/albumdb.cpp b/src/digikam/albumdb.cpp
new file mode 100644
index 00000000..9b08d5c4
--- /dev/null
+++ b/src/digikam/albumdb.cpp
@@ -0,0 +1,1599 @@
+/* ============================================================
+ *
+ * This file is a part of digiKam project
+ * http://www.digikam.org
+ *
+ * Date : 2004-06-18
+ * Description : database album interface.
+ *
+ * Copyright (C) 2004-2005 by Renchi Raju <[email protected]>
+ * Copyright (C) 2006-2007 by Gilles Caulier <caulier dot gilles at gmail dot com>
+ * Copyright (C) 2006-2007 by Marcel Wiesweg <marcel dot wiesweg at gmx dot de>
+ *
+ * This program is free software; you can redistribute it
+ * and/or modify it under the terms of the GNU General
+ * Public License as published by the Free Software Foundation;
+ * either version 2, or (at your option)
+ * any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * ============================================================ */
+
+/** @file albumdb.cpp */
+
+// C Ansi includes.
+
+extern "C"
+{
+#include "sqlite3.h"
+#include <sys/time.h>
+}
+
+// C++ includes.
+
+#include <cstdio>
+#include <cstdlib>
+#include <ctime>
+
+// TQt includes.
+
+#include <tqfile.h>
+#include <tqfileinfo.h>
+#include <tqdir.h>
+
+// KDE includes.
+
+#include <tdelocale.h>
+
+// Local includes.
+
+#include "ddebug.h"
+#include "albummanager.h"
+#include "album.h"
+#include "albumdb.h"
+#include "albumsettings.h"
+
+namespace Digikam
+{
+
+typedef struct sqlite3_stmt sqlite3_stmt;
+typedef struct sqlite3 sqleet3; // hehe.
+
+class AlbumDBPriv
+{
+
+public:
+
+ AlbumDBPriv()
+ {
+ valid = false;
+ dataBase = 0;
+ }
+
+ bool valid;
+
+ sqleet3 *dataBase;
+
+ IntList recentlyAssignedTags;
+};
+
+AlbumDB::AlbumDB()
+{
+ d = new AlbumDBPriv;
+}
+
+AlbumDB::~AlbumDB()
+{
+ if (d->dataBase)
+ {
+ sqlite3_close(d->dataBase);
+ }
+
+ delete d;
+}
+
+bool AlbumDB::isValid() const
+{
+ return d->valid;
+}
+
+void AlbumDB::setDBPath(const TQString& path)
+{
+ if (d->dataBase)
+ {
+ sqlite3_close(d->dataBase);
+ d->dataBase = 0;
+ }
+
+ d->valid = false;
+
+ sqlite3_open(TQFile::encodeName(path), &d->dataBase);
+ if (d->dataBase == 0)
+ {
+ DWarning() << "Cannot open database: "
+ << sqlite3_errmsg(d->dataBase)
+ << endl;
+ }
+ else
+ {
+ initDB();
+ }
+}
+
+void AlbumDB::initDB()
+{
+ d->valid = false;
+
+ // Check if we have the required tables
+
+ TQStringList values;
+
+ if (!execSql( TQString("SELECT name FROM sqlite_master"
+ " WHERE type='table'"
+ " ORDER BY name;"),
+ &values ))
+ {
+ return;
+ }
+
+ if (!values.contains("Albums"))
+ {
+ if (!execSql( TQString("CREATE TABLE Albums\n"
+ " (id INTEGER PRIMARY KEY,\n"
+ " url TEXT NOT NULL UNIQUE,\n"
+ " date DATE NOT NULL,\n"
+ " caption TEXT,\n"
+ " collection TEXT,\n"
+ " icon INTEGER);") ))
+ {
+ return;
+ }
+
+ if (!execSql( TQString("CREATE TABLE Tags\n"
+ " (id INTEGER PRIMARY KEY,\n"
+ " pid INTEGER,\n"
+ " name TEXT NOT NULL,\n"
+ " icon INTEGER,\n"
+ " iconkde TEXT,\n"
+ " UNIQUE (name, pid));") ))
+ {
+ return;
+ }
+
+ if (!execSql( TQString("CREATE TABLE TagsTree\n"
+ " (id INTEGER NOT NULL,\n"
+ " pid INTEGER NOT NULL,\n"
+ " UNIQUE (id, pid));") ))
+ {
+ return;
+ }
+
+ if (!execSql( TQString("CREATE TABLE Images\n"
+ " (id INTEGER PRIMARY KEY,\n"
+ " name TEXT NOT NULL,\n"
+ " dirid INTEGER NOT NULL,\n"
+ " caption TEXT,\n"
+ " datetime DATETIME,\n"
+ " UNIQUE (name, dirid));") ))
+ {
+ return;
+ }
+
+
+ if (!execSql( TQString("CREATE TABLE ImageTags\n"
+ " (imageid INTEGER NOT NULL,\n"
+ " tagid INTEGER NOT NULL,\n"
+ " UNIQUE (imageid, tagid));") ))
+ {
+ return;
+ }
+
+ if (!execSql( TQString("CREATE TABLE ImageProperties\n"
+ " (imageid INTEGER NOT NULL,\n"
+ " property TEXT NOT NULL,\n"
+ " value TEXT NOT NULL,\n"
+ " UNIQUE (imageid, property));") ))
+ {
+ return;
+ }
+
+ if ( !execSql( TQString( "CREATE TABLE Searches \n"
+ " (id INTEGER PRIMARY KEY, \n"
+ " name TEXT NOT NULL UNIQUE, \n"
+ " url TEXT NOT NULL);" ) ) )
+ {
+ return;
+ }
+
+ if (!execSql( TQString("CREATE TABLE Settings \n"
+ "(keyword TEXT NOT NULL UNIQUE,\n"
+ " value TEXT);") ))
+ return;
+ else
+ setSetting("DBVersion","1");
+
+ // TODO: see which more indices are needed
+ // create indices
+ execSql("CREATE INDEX dir_index ON Images (dirid);");
+ execSql("CREATE INDEX tag_index ON ImageTags (tagid);");
+
+ // create triggers
+
+ // trigger: delete from Images/ImageTags/ImageProperties
+ // if Album has been deleted
+ execSql("CREATE TRIGGER delete_album DELETE ON Albums\n"
+ "BEGIN\n"
+ " DELETE FROM ImageTags\n"
+ " WHERE imageid IN (SELECT id FROM Images WHERE dirid=OLD.id);\n"
+ " DELETE From ImageProperties\n"
+ " WHERE imageid IN (SELECT id FROM Images WHERE dirid=OLD.id);\n"
+ " DELETE FROM Images\n"
+ " WHERE dirid = OLD.id;\n"
+ "END;");
+
+ // trigger: delete from ImageTags/ImageProperties
+ // if Image has been deleted
+ execSql("CREATE TRIGGER delete_image DELETE ON Images\n"
+ "BEGIN\n"
+ " DELETE FROM ImageTags\n"
+ " WHERE imageid=OLD.id;\n"
+ " DELETE From ImageProperties\n "
+ " WHERE imageid=OLD.id;\n"
+ " UPDATE Albums SET icon=null \n "
+ " WHERE icon=OLD.id;\n"
+ " UPDATE Tags SET icon=null \n "
+ " WHERE icon=OLD.id;\n"
+ "END;");
+
+ // trigger: delete from ImageTags if Tag has been deleted
+ execSql("CREATE TRIGGER delete_tag DELETE ON Tags\n"
+ "BEGIN\n"
+ " DELETE FROM ImageTags WHERE tagid=OLD.id;\n"
+ "END;");
+
+ // trigger: insert into TagsTree if Tag has been added
+ execSql("CREATE TRIGGER insert_tagstree AFTER INSERT ON Tags\n"
+ "BEGIN\n"
+ " INSERT INTO TagsTree\n"
+ " SELECT NEW.id, NEW.pid\n"
+ " UNION\n"
+ " SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid;\n"
+ "END;");
+
+ // trigger: delete from TagsTree if Tag has been deleted
+ execSql("CREATE TRIGGER delete_tagstree DELETE ON Tags\n"
+ "BEGIN\n"
+ " DELETE FROM Tags\n"
+ " WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);\n"
+ " DELETE FROM TagsTree\n"
+ " WHERE id IN (SELECT id FROM TagsTree WHERE pid=OLD.id);\n"
+ " DELETE FROM TagsTree\n"
+ " WHERE id=OLD.id;\n"
+ "END;");
+
+ // trigger: delete from TagsTree if Tag has been deleted
+ execSql("CREATE TRIGGER move_tagstree UPDATE OF pid ON Tags\n"
+ "BEGIN\n"
+ " DELETE FROM TagsTree\n"
+ " WHERE\n"
+ " ((id = OLD.id)\n"
+ " OR\n"
+ " id IN (SELECT id FROM TagsTree WHERE pid=OLD.id))\n"
+ " AND\n"
+ " pid IN (SELECT pid FROM TagsTree WHERE id=OLD.id);\n"
+ " INSERT INTO TagsTree\n"
+ " SELECT NEW.id, NEW.pid\n"
+ " UNION\n"
+ " SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid\n"
+ " UNION\n"
+ " SELECT id, NEW.pid FROM TagsTree WHERE pid=NEW.id\n"
+ " UNION\n"
+ " SELECT A.id, B.pid FROM TagsTree A, TagsTree B\n"
+ " WHERE\n"
+ " A.pid = NEW.id AND B.id = NEW.pid;\n"
+ "END;");
+ }
+
+ d->valid = true;
+}
+
+AlbumInfo::List AlbumDB::scanAlbums()
+{
+ AlbumInfo::List aList;
+
+ TQString basePath(AlbumManager::instance()->getLibraryPath());
+
+ TQStringList values;
+ execSql( "SELECT A.id, A.url, A.date, A.caption, A.collection, B.url, I.name \n "
+ "FROM Albums AS A \n "
+ " LEFT OUTER JOIN Images AS I ON A.icon=I.id \n"
+ " LEFT OUTER JOIN Albums AS B ON B.id=I.dirid;", &values);
+
+ TQString iconAlbumUrl, iconName;
+
+ for (TQStringList::iterator it = values.begin(); it != values.end();)
+ {
+ AlbumInfo info;
+
+ info.id = (*it).toInt();
+ ++it;
+ info.url = *it;
+ ++it;
+ info.date = TQDate::fromString(*it, TQt::ISODate);
+ ++it;
+ info.caption = *it;
+ ++it;
+ info.collection = *it;
+ ++it;
+ iconAlbumUrl = *it;
+ ++it;
+ iconName = *it;
+ ++it;
+
+ if (!iconName.isEmpty())
+ {
+ info.icon = basePath + iconAlbumUrl + '/' + iconName;
+ }
+
+ aList.append(info);
+ }
+
+ return aList;
+}
+
+TagInfo::List AlbumDB::scanTags()
+{
+ TagInfo::List tList;
+
+ TQString basePath(AlbumManager::instance()->getLibraryPath());
+
+ TQStringList values;
+ execSql( "SELECT T.id, T.pid, T.name, A.url, I.name, T.iconkde \n "
+ "FROM Tags AS T LEFT OUTER JOIN Images AS I ON I.id=T.icon \n "
+ " LEFT OUTER JOIN Albums AS A ON A.id=I.dirid; ", &values );
+
+ TQString iconName, iconKDE, albumURL;
+
+ for (TQStringList::iterator it = values.begin(); it != values.end();)
+ {
+ TagInfo info;
+
+ info.id = (*it).toInt();
+ ++it;
+ info.pid = (*it).toInt();
+ ++it;
+ info.name = *it;
+ ++it;
+ albumURL = *it;
+ ++it;
+ iconName = *it;
+ ++it;
+ iconKDE = *it;
+ ++it;
+
+ if ( albumURL.isEmpty() )
+ {
+ info.icon = iconKDE;
+ }
+ else
+ {
+ info.icon = basePath + albumURL + '/' + iconName;
+ }
+
+ tList.append(info);
+ }
+
+ return tList;
+}
+
+SearchInfo::List AlbumDB::scanSearches()
+{
+ SearchInfo::List searchList;
+
+ TQStringList values;
+ execSql( "SELECT id, name, url FROM Searches;", &values);
+
+ for (TQStringList::iterator it = values.begin(); it != values.end();)
+ {
+ SearchInfo info;
+
+ info.id = (*it).toInt();
+ ++it;
+ info.name = (*it);
+ ++it;
+ info.url = (*it);
+ ++it;
+
+ searchList.append(info);
+ }
+
+ return searchList;
+}
+
+void AlbumDB::beginTransaction()
+{
+ execSql( "BEGIN TRANSACTION;" );
+}
+
+void AlbumDB::commitTransaction()
+{
+ execSql( "COMMIT TRANSACTION;" );
+}
+
+int AlbumDB::addAlbum(const TQString& url, const TQString& caption,
+ const TQDate& date, const TQString& collection)
+{
+ if (!d->dataBase)
+ return -1;
+
+ execSql( TQString("REPLACE INTO Albums (url, date, caption, collection) "
+ "VALUES('%1', '%2', '%3', '%4');")
+ .arg(escapeString(url),
+ date.toString(TQt::ISODate),
+ escapeString(caption),
+ escapeString(collection)));
+
+ int id = sqlite3_last_insert_rowid(d->dataBase);
+ return id;
+}
+
+void AlbumDB::setAlbumCaption(int albumID, const TQString& caption)
+{
+ execSql( TQString("UPDATE Albums SET caption='%1' WHERE id=%2;")
+ .arg(escapeString(caption),
+ TQString::number(albumID) ));
+}
+
+void AlbumDB::setAlbumCollection(int albumID, const TQString& collection)
+{
+ execSql( TQString("UPDATE Albums SET collection='%1' WHERE id=%2;")
+ .arg(escapeString(collection),
+ TQString::number(albumID)) );
+}
+
+void AlbumDB::setAlbumDate(int albumID, const TQDate& date)
+{
+ execSql( TQString("UPDATE Albums SET date='%1' WHERE id=%2;")
+ .arg(date.toString(TQt::ISODate))
+ .arg(albumID) );
+}
+
+void AlbumDB::setAlbumIcon(int albumID, TQ_LLONG iconID)
+{
+ execSql( TQString("UPDATE Albums SET icon=%1 WHERE id=%2;")
+ .arg(iconID)
+ .arg(albumID) );
+}
+
+
+TQString AlbumDB::getAlbumIcon(int albumID)
+{
+ TQStringList values;
+ execSql( TQString("SELECT B.url, I.name \n "
+ "FROM Albums AS A \n "
+ " LEFT OUTER JOIN Images AS I ON I.id=A.icon \n "
+ " LEFT OUTER JOIN Albums AS B ON B.id=I.dirid \n "
+ "WHERE A.id=%1;")
+ .arg(albumID), &values );
+ if (values.isEmpty())
+ return TQString();
+
+ TQStringList::iterator it = values.begin();
+ TQString url = *it;
+ ++it;
+ TQString icon = *it;
+ if (icon.isEmpty())
+ return TQString();
+
+ TQString basePath(AlbumManager::instance()->getLibraryPath());
+ basePath += url;
+ basePath += '/' + icon;
+
+ return basePath;
+}
+
+void AlbumDB::deleteAlbum(int albumID)
+{
+ execSql( TQString("DELETE FROM Albums WHERE id=%1")
+ .arg(albumID) );
+}
+
+int AlbumDB::addTag(int parentTagID, const TQString& name, const TQString& iconKDE,
+ TQ_LLONG iconID)
+{
+ if (!d->dataBase)
+ return -1;
+
+ if (!execSql( TQString("INSERT INTO Tags (pid, name) "
+ "VALUES( %1, '%2')")
+ .arg(parentTagID)
+ .arg(escapeString(name))))
+ {
+ return -1;
+ }
+
+ int id = sqlite3_last_insert_rowid(d->dataBase);
+
+ if (!iconKDE.isEmpty())
+ {
+ execSql( TQString("UPDATE Tags SET iconkde='%1' WHERE id=%2;")
+ .arg(escapeString(iconKDE),
+ TQString::number(id)));
+ }
+ else
+ {
+ execSql( TQString("UPDATE Tags SET icon=%1 WHERE id=%2;")
+ .arg(iconID)
+ .arg(id));
+ }
+
+ return id;
+}
+
+void AlbumDB::deleteTag(int tagID)
+{
+ execSql( TQString("DELETE FROM Tags WHERE id=%1")
+ .arg(tagID) );
+}
+
+void AlbumDB::setTagIcon(int tagID, const TQString& iconKDE, TQ_LLONG iconID)
+{
+ if (!iconKDE.isEmpty())
+ {
+ execSql( TQString("UPDATE Tags SET iconkde='%1', icon=0 WHERE id=%2;")
+ .arg(escapeString(iconKDE),
+ TQString::number(tagID)));
+ }
+ else
+ {
+ execSql( TQString("UPDATE Tags SET icon=%1 WHERE id=%2;")
+ .arg(iconID)
+ .arg(tagID));
+ }
+}
+
+TQString AlbumDB::getTagIcon(int tagID)
+{
+ TQStringList values;
+ execSql( TQString("SELECT A.url, I.name, T.iconkde \n "
+ "FROM Tags AS T \n "
+ " LEFT OUTER JOIN Images AS I ON I.id=T.icon \n "
+ " LEFT OUTER JOIN Albums AS A ON A.id=I.dirid \n "
+ "WHERE T.id=%1;")
+ .arg(tagID), &values );
+
+ if (values.isEmpty())
+ return TQString();
+
+ TQString basePath(AlbumManager::instance()->getLibraryPath());
+
+ TQString iconName, iconKDE, albumURL, icon;
+
+ TQStringList::iterator it = values.begin();
+
+ albumURL = *it;
+ ++it;
+ iconName = *it;
+ ++it;
+ iconKDE = *it;
+ ++it;
+
+ if ( albumURL.isEmpty() )
+ {
+ icon = iconKDE;
+ }
+ else
+ {
+ icon = basePath + albumURL + '/' + iconName;
+ }
+
+ return icon;
+}
+
+void AlbumDB::setTagParentID(int tagID, int newParentTagID)
+{
+ execSql( TQString("UPDATE Tags SET pid=%1 WHERE id=%2;")
+ .arg(newParentTagID)
+ .arg(tagID) );
+}
+
+int AlbumDB::addSearch(const TQString& name, const KURL& url)
+{
+ if (!d->dataBase)
+ return -1;
+
+ TQString str("INSERT INTO Searches (name, url) \n"
+ "VALUES('$$@@$$', '$$##$$');");
+ str.replace("$$@@$$", escapeString(name));
+ str.replace("$$##$$", escapeString(url.url()));
+
+ if (!execSql(str))
+ {
+ return -1;
+ }
+
+ return sqlite3_last_insert_rowid(d->dataBase);
+}
+
+void AlbumDB::updateSearch(int searchID, const TQString& name,
+ const KURL& url)
+{
+ TQString str = TQString("UPDATE Searches SET name='$$@@$$', url='$$##$$' \n"
+ "WHERE id=%1")
+ .arg(searchID);
+ str.replace("$$@@$$", escapeString(name));
+ str.replace("$$##$$", escapeString(url.url()));
+
+ execSql(str);
+}
+
+void AlbumDB::deleteSearch(int searchID)
+{
+ execSql( TQString("DELETE FROM Searches WHERE id=%1")
+ .arg(searchID) );
+}
+
+void AlbumDB::setSetting(const TQString& keyword,
+ const TQString& value )
+{
+ execSql( TQString("REPLACE into Settings VALUES ('%1','%2');")
+ .arg(escapeString(keyword),
+ escapeString(value) ));
+}
+
+TQString AlbumDB::getSetting(const TQString& keyword)
+{
+ TQStringList values;
+ execSql( TQString("SELECT value FROM Settings "
+ "WHERE keyword='%1';")
+ .arg(escapeString(keyword)), &values );
+
+ if (values.isEmpty())
+ return TQString();
+ else
+ return values[0];
+}
+
+bool AlbumDB::execSql(const TQString& sql, TQStringList* const values,
+ const bool debug)
+{
+ if ( debug )
+ DDebug() << "SQL-query: " << sql << endl;
+
+ if ( !d->dataBase )
+ {
+ DWarning() << k_funcinfo << "SQLite pointer == NULL"
+ << endl;
+ return false;
+ }
+
+ const char* tail;
+ sqlite3_stmt* stmt;
+ int error;
+
+ //compile SQL program to virtual machine
+ error = sqlite3_prepare(d->dataBase, sql.utf8(), -1, &stmt, &tail);
+ if ( error != SQLITE_OK )
+ {
+ DWarning() << k_funcinfo
+ << "sqlite_compile error: "
+ << sqlite3_errmsg(d->dataBase)
+ << " on query: "
+ << sql << endl;
+ return false;
+ }
+
+ int cols = sqlite3_column_count(stmt);
+
+ while ( true )
+ {
+ error = sqlite3_step( stmt );
+
+ if ( error == SQLITE_DONE || error == SQLITE_ERROR )
+ break;
+
+ //iterate over columns
+ for ( int i = 0; values && i < cols; i++ )
+ {
+ *values << TQString::fromUtf8( (const char*)sqlite3_column_text( stmt, i ) );
+ }
+ }
+
+ sqlite3_finalize( stmt );
+
+ if ( error != SQLITE_DONE )
+ {
+ DWarning() << "sqlite_step error: "
+ << sqlite3_errmsg( d->dataBase )
+ << " on query: "
+ << sql << endl;
+ return false;
+ }
+
+ return true;
+}
+
+TQString AlbumDB::escapeString(TQString str) const
+{
+ str.replace( "'", "''" );
+ return str;
+}
+
+TQString AlbumDB::getItemCaption(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT caption FROM Images "
+ "WHERE id=%1;")
+ .arg(imageID),
+ &values );
+
+ if (!values.isEmpty())
+ return values[0];
+ else
+ return TQString();
+}
+
+TQString AlbumDB::getItemCaption(int albumID, const TQString& name)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT caption FROM Images "
+ "WHERE dirid=%1 AND name='%2';")
+ .arg(albumID)
+ .arg(escapeString(name)),
+ &values );
+
+ if (!values.isEmpty())
+ return values[0];
+ else
+ return TQString();
+}
+
+TQDateTime AlbumDB::getItemDate(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT datetime FROM Images "
+ "WHERE id=%1;")
+ .arg(imageID),
+ &values );
+
+ if (values.isEmpty())
+ return TQDateTime();
+ else
+ return TQDateTime::fromString(values[0], TQt::ISODate);
+}
+
+TQDateTime AlbumDB::getItemDate(int albumID, const TQString& name)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT datetime FROM Images "
+ "WHERE dirid=%1 AND name='%2';")
+ .arg(albumID)
+ .arg(escapeString(name)),
+ &values );
+
+ if (values.isEmpty())
+ return TQDateTime();
+ else
+ return TQDateTime::fromString(values[0], TQt::ISODate);
+}
+
+TQ_LLONG AlbumDB::getImageId(int albumID, const TQString& name)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT id FROM Images "
+ "WHERE dirid=%1 AND name='%2';")
+ .arg(albumID)
+ .arg(escapeString(name)),
+ &values );
+
+ if (values.isEmpty())
+ return -1;
+ else
+ return (values[0]).toLongLong();
+}
+
+TQStringList AlbumDB::getItemTagNames(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT name FROM Tags \n "
+ "WHERE id IN (SELECT tagid FROM ImageTags \n "
+ " WHERE imageid=%1) \n "
+ "ORDER BY name;")
+ .arg(imageID),
+ &values );
+
+ return values;
+}
+
+IntList AlbumDB::getItemTagIDs(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT tagid FROM ImageTags \n "
+ "WHERE imageID=%1;")
+ .arg(imageID),
+ &values );
+
+ IntList ids;
+
+ if (values.isEmpty())
+ return ids;
+
+ for (TQStringList::iterator it=values.begin(); it != values.end(); ++it)
+ {
+ ids << (*it).toInt();
+ }
+ return ids;
+}
+
+bool AlbumDB::hasTags(const LLongList& imageIDList)
+{
+ IntList ids;
+
+ if (imageIDList.isEmpty())
+ return false;
+
+ TQStringList values;
+
+ TQString sql = TQString("SELECT count(tagid) FROM ImageTags "
+ "WHERE imageid=%1 ")
+ .arg(imageIDList.first());
+
+ LLongList::const_iterator iter = imageIDList.begin();
+ ++iter;
+
+ while (iter != imageIDList.end())
+ {
+ sql += TQString(" OR imageid=%2 ")
+ .arg(*iter);
+ ++iter;
+ }
+
+ sql += TQString(";");
+ execSql( sql, &values );
+
+ if (values[0] == "0")
+ return false;
+ else
+ return true;
+}
+
+IntList AlbumDB::getItemCommonTagIDs(const LLongList& imageIDList)
+{
+ IntList ids;
+
+ if (imageIDList.isEmpty())
+ return ids;
+
+ TQStringList values;
+
+ TQString sql = TQString("SELECT DISTINCT tagid FROM ImageTags "
+ "WHERE imageid=%1 ")
+ .arg(imageIDList.first());
+
+ LLongList::const_iterator iter = imageIDList.begin();
+ ++iter;
+
+ while (iter != imageIDList.end())
+ {
+ sql += TQString(" OR imageid=%2 ")
+ .arg(*iter);
+ ++iter;
+ }
+
+ sql += TQString(";");
+ execSql( sql, &values );
+
+ if (values.isEmpty())
+ return ids;
+
+ for (TQStringList::iterator it=values.begin(); it != values.end(); ++it)
+ {
+ ids << (*it).toInt();
+ }
+ return ids;
+}
+
+void AlbumDB::setItemCaption(TQ_LLONG imageID,const TQString& caption)
+{
+ TQStringList values;
+
+ execSql( TQString("UPDATE Images SET caption='%1' "
+ "WHERE id=%2;")
+ .arg(escapeString(caption),
+ TQString::number(imageID) ));
+}
+
+void AlbumDB::setItemCaption(int albumID, const TQString& name, const TQString& caption)
+{
+ TQStringList values;
+
+ execSql( TQString("UPDATE Images SET caption='%1' "
+ "WHERE dirid=%2 AND name='%3';")
+ .arg(escapeString(caption),
+ TQString::number(albumID),
+ escapeString(name)) );
+}
+
+void AlbumDB::addItemTag(TQ_LLONG imageID, int tagID)
+{
+ execSql( TQString("REPLACE INTO ImageTags (imageid, tagid) "
+ "VALUES(%1, %2);")
+ .arg(imageID)
+ .arg(tagID) );
+
+ if (!d->recentlyAssignedTags.contains(tagID))
+ {
+ d->recentlyAssignedTags.push_front(tagID);
+ if (d->recentlyAssignedTags.size() > 10)
+ d->recentlyAssignedTags.pop_back();
+ }
+}
+
+void AlbumDB::addItemTag(int albumID, const TQString& name, int tagID)
+{
+ execSql( TQString("REPLACE INTO ImageTags (imageid, tagid) \n "
+ "(SELECT id, %1 FROM Images \n "
+ " WHERE dirid=%2 AND name='%3');")
+ .arg(tagID)
+ .arg(albumID)
+ .arg(escapeString(name)) );
+}
+
+IntList AlbumDB::getRecentlyAssignedTags() const
+{
+ return d->recentlyAssignedTags;
+}
+
+void AlbumDB::removeItemTag(TQ_LLONG imageID, int tagID)
+{
+ execSql( TQString("DELETE FROM ImageTags "
+ "WHERE imageID=%1 AND tagid=%2;")
+ .arg(imageID)
+ .arg(tagID) );
+}
+
+void AlbumDB::removeItemAllTags(TQ_LLONG imageID)
+{
+ execSql( TQString("DELETE FROM ImageTags "
+ "WHERE imageID=%1;")
+ .arg(imageID) );
+}
+
+TQStringList AlbumDB::getItemNamesInAlbum(int albumID, bool recurssive)
+{
+ TQStringList values;
+
+ if (recurssive)
+ {
+ KURL url(getAlbumURL(albumID));
+ execSql( TQString("SELECT Images.name "
+ "FROM Images "
+ "WHERE Images.dirid "
+ "IN (SELECT DISTINCT id "
+ "FROM Albums "
+ "WHERE url='%1' OR url LIKE '\%%2\%')")
+ .arg(escapeString(url.path())).arg(escapeString(url.path(1))), &values);
+ }
+ else
+ {
+ execSql( TQString("SELECT Images.name "
+ "FROM Images "
+ "WHERE Images.dirid=%1")
+ .arg(albumID), &values );
+ }
+ return values;
+}
+
+TQStringList AlbumDB::getAllItemURLsWithoutDate()
+{
+ TQStringList values;
+ execSql( TQString("SELECT Albums.url||'/'||Images.name "
+ "FROM Images, Albums "
+ "WHERE Images.dirid=Albums.Id "
+ "AND (Images.datetime is null or "
+ " Images.datetime == '');"),
+ &values );
+
+ TQString libraryPath = AlbumManager::instance()->getLibraryPath() + '/';
+ for (TQStringList::iterator it = values.begin(); it != values.end();
+ ++it)
+ {
+ *it = libraryPath + *it;
+ }
+
+ return values;
+}
+
+int AlbumDB::getOrCreateAlbumId(const TQString& folder)
+{
+ TQStringList values;
+ execSql( TQString("SELECT id FROM Albums WHERE url ='%1';")
+ .arg( escapeString(folder) ), &values);
+
+ int albumID;
+ if (values.isEmpty())
+ {
+ execSql( TQString ("INSERT INTO Albums (url, date) "
+ "VALUES ('%1','%2')")
+ .arg(escapeString(folder),
+ TQDateTime::currentDateTime().toString(TQt::ISODate)) );
+ albumID = sqlite3_last_insert_rowid(d->dataBase);
+ } else
+ albumID = values[0].toInt();
+
+ return albumID;
+}
+
+TQ_LLONG AlbumDB::addItem(int albumID,
+ const TQString& name,
+ const TQDateTime& datetime,
+ const TQString& comment,
+ int rating,
+ const TQStringList &keywordsList)
+{
+ execSql ( TQString ("REPLACE INTO Images "
+ "( caption , datetime, name, dirid ) "
+ " VALUES ('%1','%2','%3',%4) " )
+ .arg(escapeString(comment),
+ datetime.toString(TQt::ISODate),
+ escapeString(name),
+ TQString::number(albumID)) );
+
+ TQ_LLONG item = sqlite3_last_insert_rowid(d->dataBase);
+
+ // Set Rating value to item in database.
+
+ if ( item != -1 && rating != -1 )
+ setItemRating(item, rating);
+
+ // Set existing tags in database or create new tags if not exist.
+
+ if ( item != -1 && !keywordsList.isEmpty() )
+ {
+ IntList tagIDs = getTagsFromTagPaths(keywordsList);
+ for (IntList::iterator it = tagIDs.begin(); it != tagIDs.end(); ++it)
+ {
+ addItemTag(item, *it);
+ }
+ }
+
+ return item;
+}
+
+IntList AlbumDB::getTagsFromTagPaths(const TQStringList &keywordsList, bool create)
+{
+ if (keywordsList.isEmpty())
+ return IntList();
+
+ IntList tagIDs;
+
+ TQStringList keywordsList2Create;
+
+ // Create a list of the tags currently in database
+
+ TagInfo::List currentTagsList;
+
+ TQStringList values;
+ execSql( "SELECT id, pid, name FROM Tags;", &values );
+
+ for (TQStringList::iterator it = values.begin(); it != values.end();)
+ {
+ TagInfo info;
+
+ info.id = (*it).toInt();
+ ++it;
+ info.pid = (*it).toInt();
+ ++it;
+ info.name = *it;
+ ++it;
+ currentTagsList.append(info);
+ }
+
+ // For every tag in keywordsList, scan taglist to check if tag already exists.
+
+ for (TQStringList::const_iterator kwd = keywordsList.begin();
+ kwd != keywordsList.end(); ++kwd )
+ {
+ // split full tag "url" into list of single tag names
+ TQStringList tagHierarchy = TQStringList::split('/', *kwd);
+ if (tagHierarchy.isEmpty())
+ continue;
+
+ // last entry in list is the actual tag name
+ bool foundTag = false;
+ TQString tagName = tagHierarchy.back();
+ tagHierarchy.pop_back();
+
+ for (TagInfo::List::iterator tag = currentTagsList.begin();
+ tag != currentTagsList.end(); ++tag )
+ {
+ // There might be multiple tags with the same name, but in different
+ // hierarchies. We must check them all until we find the correct hierarchy
+ if ((*tag).name == tagName)
+ {
+ int parentID = (*tag).pid;
+
+ // Check hierarchy, from bottom to top
+ bool foundParentTag = true;
+ TQStringList::iterator parentTagName = tagHierarchy.end();
+
+ while (foundParentTag && parentTagName != tagHierarchy.begin())
+ {
+ --parentTagName;
+
+ foundParentTag = false;
+
+ for (TagInfo::List::iterator parentTag = currentTagsList.begin();
+ parentTag != currentTagsList.end(); ++parentTag )
+ {
+ // check if name is the same, and if ID is identical
+ // to the parent ID we got from the child tag
+ if ( (*parentTag).id == parentID &&
+ (*parentTag).name == (*parentTagName) )
+ {
+ parentID = (*parentTag).pid;
+ foundParentTag = true;
+ break;
+ }
+ }
+
+ // If we traversed the list without a match,
+ // foundParentTag will be false, the while loop breaks.
+ }
+
+ // If we managed to traverse the full hierarchy,
+ // we have our tag.
+ if (foundParentTag)
+ {
+ // add to result list
+ tagIDs.append((*tag).id);
+ foundTag = true;
+ break;
+ }
+ }
+ }
+
+ if (!foundTag)
+ keywordsList2Create.append(*kwd);
+ }
+
+ // If tags do not exist in database, create them.
+
+ if (create && !keywordsList2Create.isEmpty())
+ {
+ for (TQStringList::iterator kwd = keywordsList2Create.begin();
+ kwd != keywordsList2Create.end(); ++kwd )
+ {
+ // split full tag "url" into list of single tag names
+ TQStringList tagHierarchy = TQStringList::split('/', *kwd);
+
+ if (tagHierarchy.isEmpty())
+ continue;
+
+ int parentTagID = 0;
+ int tagID = 0;
+ bool parentTagExisted = true;
+
+ // Traverse hierarchy from top to bottom
+ for (TQStringList::iterator tagName = tagHierarchy.begin();
+ tagName != tagHierarchy.end(); ++tagName)
+ {
+ tagID = 0;
+
+ // if the parent tag did not exist, we need not check if the child exists
+ if (parentTagExisted)
+ {
+ for (TagInfo::List::iterator tag = currentTagsList.begin();
+ tag != currentTagsList.end(); ++tag )
+ {
+ // find the tag with tag name according to tagHierarchy,
+ // and parent ID identical to the ID of the tag we found in
+ // the previous run.
+ if ((*tag).name == (*tagName) && (*tag).pid == parentTagID)
+ {
+ tagID = (*tag).id;
+ break;
+ }
+ }
+ }
+
+ if (tagID != 0)
+ {
+ // tag already found in DB
+ parentTagID = tagID;
+ continue;
+ }
+
+ // Tag does not yet exist in DB, add it
+ tagID = addTag(parentTagID, (*tagName), TQString(), 0);
+
+ if (tagID == -1)
+ {
+ // Something is wrong in database. Abort.
+ break;
+ }
+
+ // append to our list of existing tags (for following keywords)
+ TagInfo info;
+ info.id = tagID;
+ info.pid = parentTagID;
+ info.name = (*tagName);
+ currentTagsList.append(info);
+
+ parentTagID = tagID;
+ parentTagExisted = false;
+ }
+
+ // add to result list
+ tagIDs.append(tagID);
+ }
+ }
+
+ return tagIDs;
+}
+
+int AlbumDB::getItemAlbum(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql ( TQString ("SELECT dirid FROM Images "
+ "WHERE id=%1;")
+ .arg(imageID),
+ &values);
+
+ if (!values.isEmpty())
+ return values.first().toInt();
+ else
+ return 1;
+}
+
+TQString AlbumDB::getItemName(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql ( TQString ("SELECT name FROM Images "
+ "WHERE id=%1;")
+ .arg(imageID),
+ &values);
+
+ if (!values.isEmpty())
+ return values.first();
+ else
+ return TQString();
+}
+
+bool AlbumDB::setItemDate(TQ_LLONG imageID,
+ const TQDateTime& datetime)
+{
+ execSql ( TQString ("UPDATE Images SET datetime='%1'"
+ "WHERE id=%2;")
+ .arg(datetime.toString(TQt::ISODate),
+ TQString::number(imageID)) );
+
+ return true;
+}
+
+bool AlbumDB::setItemDate(int albumID, const TQString& name,
+ const TQDateTime& datetime)
+{
+ execSql ( TQString ("UPDATE Images SET datetime='%1'"
+ "WHERE dirid=%2 AND name='%3';")
+ .arg(datetime.toString(TQt::ISODate),
+ TQString::number(albumID),
+ escapeString(name)) );
+
+ return true;
+}
+
+void AlbumDB::setItemRating(TQ_LLONG imageID, int rating)
+{
+ execSql ( TQString ("REPLACE INTO ImageProperties "
+ "(imageid, property, value) "
+ "VALUES(%1, '%2', '%3');")
+ .arg(imageID)
+ .arg("Rating")
+ .arg(rating) );
+}
+
+int AlbumDB::getItemRating(TQ_LLONG imageID)
+{
+ TQStringList values;
+
+ execSql( TQString("SELECT value FROM ImageProperties "
+ "WHERE imageid=%1 and property='%2';")
+ .arg(imageID)
+ .arg("Rating"),
+ &values);
+
+ if (!values.isEmpty())
+ return values[0].toInt();
+ else
+ return 0;
+}
+
+TQStringList AlbumDB::getItemURLsInAlbum(int albumID)
+{
+ TQStringList values;
+
+ TQString basePath(AlbumManager::instance()->getLibraryPath());
+
+ AlbumSettings::ImageSortOrder order = AlbumSettings::instance()->getImageSortOrder();
+
+ TQString sqlString;
+ switch(order)
+ {
+ case AlbumSettings::ByIName:
+ sqlString = TQString("SELECT Albums.url||'/'||Images.name FROM Images, Albums "
+ "WHERE Albums.id=%1 AND Albums.id=Images.dirid "
+ "ORDER BY Images.name COLLATE NOCASE;")
+ .arg(albumID);
+ break;
+ case AlbumSettings::ByIPath:
+ // Dont collate on the path - this is to maintain the same behaviour
+ // that happens when sort order is "By Path"
+ sqlString = TQString("SELECT Albums.url||'/'||Images.name FROM Images, Albums "
+ "WHERE Albums.id=%1 AND Albums.id=Images.dirid "
+ "ORDER BY Albums.url,Images.name;")
+ .arg(albumID);
+ break;
+ case AlbumSettings::ByIDate:
+ sqlString = TQString("SELECT Albums.url||'/'||Images.name FROM Images, Albums "
+ "WHERE Albums.id=%1 AND Albums.id=Images.dirid "
+ "ORDER BY Images.datetime;")
+ .arg(albumID);
+ break;
+ case AlbumSettings::ByIRating:
+ sqlString = TQString("SELECT Albums.url||'/'||Images.name FROM Images, Albums, ImageProperties "
+ "WHERE Albums.id=%1 AND Albums.id=Images.dirid "
+ "AND Images.id = ImageProperties.imageid "
+ "AND ImageProperties.property='Rating' "
+ "ORDER BY ImageProperties.value DESC;")
+ .arg(albumID);
+ break;
+ default:
+ sqlString = TQString("SELECT Albums.url||'/'||Images.name FROM Images, Albums "
+ "WHERE Albums.id=%1 AND Albums.id=Images.dirid;")
+ .arg(albumID);
+ break;
+ }
+ execSql( sqlString, &values );
+
+ for (TQStringList::iterator it = values.begin(); it != values.end(); ++it)
+ {
+ *it = basePath + *it;
+ }
+
+ return values;
+}
+
+LLongList AlbumDB::getItemIDsInAlbum(int albumID)
+{
+ LLongList itemIDs;
+
+ TQStringList itemNames = getItemNamesInAlbum(albumID);
+
+ for (TQStringList::iterator it = itemNames.begin(); it != itemNames.end(); ++it)
+ {
+ TQ_LLONG id = getImageId(albumID, *it);
+ itemIDs.append(id);
+ }
+
+ return itemIDs;
+}
+
+TQStringList AlbumDB::getItemURLsInTag(int tagID, bool recursive)
+{
+ TQStringList values;
+
+ TQString basePath(AlbumManager::instance()->getLibraryPath());
+
+ TQString imagesIdClause;
+ if (recursive)
+ imagesIdClause = TQString("SELECT imageid FROM ImageTags "
+ " WHERE tagid=%1 "
+ " OR tagid IN (SELECT id FROM TagsTree WHERE pid=%2)")
+ .arg(tagID).arg(tagID);
+ else
+ imagesIdClause = TQString("SELECT imageid FROM ImageTags WHERE tagid=%1").arg(tagID);
+
+ execSql( TQString("SELECT Albums.url||'/'||Images.name FROM Images, Albums "
+ "WHERE Images.id IN (%1) "
+ "AND Albums.id=Images.dirid;")
+ .arg(imagesIdClause), &values );
+
+ for (TQStringList::iterator it = values.begin(); it != values.end(); ++it)
+ {
+ *it = basePath + *it;
+ }
+
+ return values;
+}
+
+LLongList AlbumDB::getItemIDsInTag(int tagID, bool recursive)
+{
+ LLongList itemIDs;
+ TQStringList values;
+
+ if (recursive)
+ execSql( TQString("SELECT imageid FROM ImageTags "
+ " WHERE tagid=%1 "
+ " OR tagid IN (SELECT id FROM TagsTree WHERE pid=%2)")
+ .arg(tagID).arg(tagID), &values );
+ else
+ execSql( TQString("SELECT imageid FROM ImageTags WHERE tagid=%1;")
+ .arg(tagID), &values );
+
+ for (TQStringList::iterator it = values.begin(); it != values.end(); ++it)
+ {
+ itemIDs << (*it).toLong();
+ }
+
+ return itemIDs;
+}
+
+TQString AlbumDB::getAlbumURL(int albumID)
+{
+ TQStringList values;
+ execSql( TQString("SELECT url from Albums where id=%1")
+ .arg( albumID), &values);
+ return values[0];
+}
+
+TQDate AlbumDB::getAlbumLowestDate(int albumID)
+{
+ TQStringList values;
+ execSql( TQString("SELECT MIN(datetime) FROM Images "
+ "WHERE dirid=%1 GROUP BY dirid")
+ .arg( albumID ), &values);
+ TQDate itemDate = TQDate::fromString( values[0], TQt::ISODate );
+ return itemDate;
+}
+
+TQDate AlbumDB::getAlbumHighestDate(int albumID)
+{
+ TQStringList values;
+ execSql( TQString("SELECT MAX(datetime) FROM Images "
+ "WHERE dirid=%1 GROUP BY dirid")
+ .arg( albumID ), &values);
+ TQDate itemDate = TQDate::fromString( values[0], TQt::ISODate );
+ return itemDate;
+}
+
+TQDate AlbumDB::getAlbumAverageDate(int albumID)
+{
+ TQStringList values;
+ execSql( TQString("SELECT datetime FROM Images WHERE dirid=%1")
+ .arg( albumID ), &values);
+
+ int differenceInSecs = 0;
+ int amountOfImages = 0;
+ TQDateTime baseDateTime;
+
+ for (TQStringList::iterator it = values.begin(); it != values.end(); ++it)
+ {
+ TQDateTime itemDateTime = TQDateTime::fromString( *it, TQt::ISODate );
+ if (itemDateTime.isValid())
+ {
+ ++amountOfImages;
+ if ( baseDateTime.isNull() )
+ baseDateTime=itemDateTime;
+ else
+ differenceInSecs += itemDateTime.secsTo( baseDateTime );
+ }
+ }
+
+ if ( amountOfImages > 0 )
+ {
+ TQDateTime averageDateTime;
+ averageDateTime.setTime_t( baseDateTime.toTime_t() -
+ (int)( differenceInSecs/amountOfImages ) );
+ return ( averageDateTime.date() );
+ }
+ else
+ return TQDate();
+}
+
+void AlbumDB::deleteItem(int albumID, const TQString& file)
+{
+ execSql( TQString("DELETE FROM Images "
+ "WHERE dirid=%1 AND name='%2';")
+ .arg(albumID)
+ .arg(escapeString(file)) );
+}
+
+void AlbumDB::setAlbumURL(int albumID, const TQString& url)
+{
+ TQString u = escapeString(url);
+
+ // first delete any stale albums left behind
+ execSql( TQString("DELETE FROM Albums WHERE url = '%1'")
+ .arg(u) );
+
+ // now update the album url
+ execSql( TQString("UPDATE Albums SET url = '%1' WHERE id = %2;")
+ .arg(u, TQString::number(albumID) ));
+}
+
+void AlbumDB::setTagName(int tagID, const TQString& name)
+{
+ execSql( TQString("UPDATE Tags SET name='%1' WHERE id=%2;")
+ .arg(escapeString(name), TQString::number(tagID) ));
+}
+
+void AlbumDB::moveItem(int srcAlbumID, const TQString& srcName,
+ int dstAlbumID, const TQString& dstName)
+{
+
+ // first delete any stale database entries if any
+ deleteItem(dstAlbumID, dstName);
+
+ execSql( TQString("UPDATE Images SET dirid=%1, name='%2' "
+ "WHERE dirid=%3 AND name='%4';")
+ .arg(TQString::number(dstAlbumID), escapeString(dstName),
+ TQString::number(srcAlbumID), escapeString(srcName)) );
+}
+
+int AlbumDB::copyItem(int srcAlbumID, const TQString& srcName,
+ int dstAlbumID, const TQString& dstName)
+{
+ // check for src == dest
+ if (srcAlbumID == dstAlbumID && srcName == dstName)
+ return -1;
+
+ // find id of src image
+ TQStringList values;
+ execSql( TQString("SELECT id FROM Images "
+ "WHERE dirid=%1 AND name='%2';")
+ .arg(TQString::number(srcAlbumID), escapeString(srcName)),
+ &values);
+
+ if (values.isEmpty())
+ return -1;
+
+ int srcId = values[0].toInt();
+
+ // first delete any stale database entries if any
+ deleteItem(dstAlbumID, dstName);
+
+ // copy entry in Images table
+ execSql( TQString("INSERT INTO Images (dirid, name, caption, datetime) "
+ "SELECT %1, '%2', caption, datetime FROM Images "
+ "WHERE id=%3;")
+ .arg(TQString::number(dstAlbumID), escapeString(dstName),
+ TQString::number(srcId)) );
+
+ int dstId = sqlite3_last_insert_rowid(d->dataBase);
+
+ // copy tags
+ execSql( TQString("INSERT INTO ImageTags (imageid, tagid) "
+ "SELECT %1, tagid FROM ImageTags "
+ "WHERE imageid=%2;")
+ .arg(TQString::number(dstId), TQString::number(srcId)) );
+
+ // copy properties (rating)
+ execSql( TQString("INSERT INTO ImageProperties (imageid, property, value) "
+ "SELECT %1, property, value FROM ImageProperties "
+ "WHERE imageid=%2;")
+ .arg(TQString::number(dstId), TQString::number(srcId)) );
+
+ return dstId;
+}
+
+TQ_LLONG AlbumDB::lastInsertedRow()
+{
+ return sqlite3_last_insert_rowid(d->dataBase);
+}
+
+} // namespace Digikam
+
generated by cgit v1.2.1 (git 2.18.0) at 2024-12-03 22:14:02 +0000