You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
1600 lines
44 KiB
1600 lines
44 KiB
/* ============================================================
|
|
*
|
|
* 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 <renchi@pooh.tam.uiuc.edu>
|
|
* 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
|
|
|