Move config and sqlite database. They are both at the xdg-specified locations now:
[toast/confclerk.git] / src / sql / sqlengine.cpp
index b77729f..1a1c7f4 100644 (file)
@@ -1,3 +1,22 @@
+/*
+ * Copyright (C) 2010 Ixonos Plc.
+ * Copyright (C) 2011 Philipp Spitzer, gregor herrmann
+ *
+ * This file is part of ConfClerk.
+ *
+ * ConfClerk 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 of the License, or (at your option)
+ * any later version.
+ *
+ * ConfClerk 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.
+ *
+ * You should have received a copy of the GNU General Public License along with
+ * ConfClerk.  If not, see <http://www.gnu.org/licenses/>.
+ */
 
 #include <QSqlError>
 #include <QSqlQuery>
@@ -6,7 +25,10 @@
 #include <QDateTime>
 
 #include <QDir>
+#include <QDesktopServices>
 #include "sqlengine.h"
+#include <track.h>
+#include <conference.h>
 
 #include <QDebug>
 
@@ -30,15 +52,22 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN
     bool result = false;
     if(!QFile::exists(aDatabaseName)) // the DB (tables) doesn't exists, and so we have to create one
     {
-        // creating empty DB + tables
-        // ??? what is the best way of creating new empty DB ???
-        // we can either:
-        //  - create new DB + tables by issuing corresponding queries (used solution)
-        //  - create new DB from resource, which contains empty DB with tables
-        result = createTables(database);
+        // create Db
+        if (!database.open()) qDebug() << "Could not open database" << database.lastError();
+        QFile file(":/create_tables.sql");
+        file.open(QIODevice::ReadOnly | QIODevice::Text);
+        QString allSqlStatements = file.readAll();
+        foreach(QString sql, allSqlStatements.split(";")) {
+            QSqlQuery query(database);
+            if (!query.exec(sql)) qDebug() << "Could not execute query" << query.lastError();
+        }
+    }
+    else
+    {
+        database.open();
     }
 
-    database.open();
+    checkConferenceMap(database);
 
     //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
 
@@ -48,10 +77,12 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN
 void SqlEngine::initialize()
 {
     QString databaseName;
-    if(!QDir::home().exists(".fosdem"))
-        QDir::home().mkdir(".fosdem");
-    databaseName = QDir::homePath() + "/.fosdem/" + "fosdem.sqlite";
-
+    QString dataDirName;
+    dataDirName = QDesktopServices::storageLocation(QDesktopServices::DataLocation);
+    QDir dataDir = QDir(dataDirName).absolutePath();
+    if(!dataDir.exists())
+        dataDir.mkpath(dataDirName);
+    databaseName = dataDirName + "ConfClerk.sqlite";
     login("QSQLITE",databaseName);
 }
 
@@ -61,21 +92,41 @@ void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference)
 
     if (db.isValid() && db.isOpen())
     {
-        QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9', '%10'") \
-                         .arg(aConference["id"]) \
-                         .arg(aConference["title"]) \
-                         .arg(aConference["subtitle"]) \
-                         .arg(aConference["venue"]) \
-                         .arg(aConference["city"]) \
-                         .arg(aConference["start"]) \
-                         .arg(aConference["end"]) \
-                         .arg(aConference["days"]) \
-                         .arg(aConference["day_change"]) \
-                         .arg(aConference["timeslot_duration"]);
-
-        QString query = QString("INSERT INTO CONFERENCE (id,title,subtitle,venue,city,start,end,days,day_change,timeslot_duration) VALUES (%1)").arg(values);
-        QSqlQuery result (query, db);
-        //LOG_AUTOTEST(query);
+        int confId = 0;
+        QList<Conference> confsList = Conference::getAll();
+        if(confsList.count())
+        {
+            QListIterator<Conference> i(confsList);
+            while (i.hasNext())
+            {
+                Conference conf = i.next();
+                if( aConference["title"] == conf.title() )
+                {
+                    confId = conf.id();
+                    aConference["id"] = QString::number(confId);
+                    break;
+                }
+            }
+        }
+
+        if(!confId) // conference 'aConference' isn't in the table => insert
+        {
+            QSqlQuery query(db);
+            query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
+                                                    "day_change,timeslot_duration,active) "
+                            " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,:days,"
+                                                    ":day_change,:timeslot_duration,:active)");
+            foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
+                query.bindValue(QString(":") + prop_name, aConference[prop_name]);
+            }
+            query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
+            query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
+            query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
+            query.bindValue(":day_change", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+            query.bindValue(":active", confsList.count() > 0 ? 0 : 1);
+            if (!query.exec()) qDebug() << "Could not execute query to insert a conference:" << query.lastError();
+            aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
+        }
     }
 }
 
@@ -87,36 +138,79 @@ void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
 
     if (db.isValid() && db.isOpen())
     {
-        // The items of the Event are divided into the two tables EVENT and VIRTUAL_EVENT
-        // VIRTUAL_EVENT is for Full-Text-Serach Support
-        QTime duration = QTime::fromString(aEvent["duration"],TIME_FORMAT);
-        QDateTime startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT));
-        QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7'") \
-                         .arg(aEvent["conference_id"]) \
-                         .arg(aEvent["id"]) \
-                         .arg(QString::number(startDateTime.toTime_t())) \
-                         .arg(QString::number(duration.hour()*3600 + duration.minute()*60 + duration.second())) \
-                         .arg("123456") \
-                         .arg(aEvent["type"]) \
-                         .arg(aEvent["language"]);
-
-        QString query = QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_activity, type, language) VALUES (%1)").arg(values);
-        QSqlQuery result (query, db);
-        //LOG_AUTOTEST(query);
+        //insert event track to table and get track id
+        int conference = aEvent["conference_id"].toInt();
+        QString name = aEvent["track"];
+        Track track;
+        int trackId;
+        try
+        {
+            track = Track::retrieveByName(conference, name);
+            trackId = track.id();
+            /*qDebug() << QString("DEBUG: Track %1 in DB").arg(name);*/
+        }
+        catch (OrmNoObjectException &e) {
+            track.setConference(conference);
+            track.setName(name);
+            trackId = track.insert();
+            /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/
+        }
+        QDateTime startDateTime;
+        startDateTime.setTimeSpec(Qt::UTC);
+        startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
+        // qDebug() << "startDateTime: " << startDateTime.toString();
+
+        bool event_exists = false;
+        {
+            QSqlQuery check_event_query;
+            check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id");
+            check_event_query.bindValue(":xid_conference", aEvent["conference_id"]);
+            check_event_query.bindValue(":id", aEvent["id"]);
+            if (!check_event_query.exec()) {
+                qWarning() << "check event failed, conference id:" << aEvent["xid_conference"]
+                        << "event id:" << aEvent["id"]
+                        << "error:" << check_event_query.lastError()
+                        ;
+                return;
+            }
+            if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) {
+                event_exists = true;
+            }
+        }
 
-        // add some(text related) Event's items to VIRTUAL_EVENT table
-        QString values2 = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7'") \
-                          .arg(aEvent["conference_id"]) \
-                          .arg(aEvent["id"]) \
-                          .arg(aEvent["tag"]) \
-                          .arg(aEvent["title"]) \
-                          .arg(aEvent["subtitle"]) \
-                          .arg(aEvent["abstract"]) \
-                          .arg(aEvent["description"]);
-
-        QString query2 = QString("INSERT INTO VIRTUAL_EVENT (xid_conference, id, tag, title, subtitle, abstract, description) VALUES (%1)").arg(values2);
-        QSqlQuery result2 (query2, db);
-        //LOG_AUTOTEST(query2);
+        QSqlQuery result;
+        if (event_exists) {
+            result.prepare("UPDATE EVENT SET"
+                            " start = :start"
+                            ", duration = :duration"
+                            ", xid_track = :xid_track"
+                            ", type = :type"
+                            ", language = :language"
+                            ", tag = :tag"
+                            ", title = :title"
+                            ", subtitle = :subtitle"
+                            ", abstract = :abstract"
+                            ", description = :description"
+                                " WHERE id = :id AND xid_conference = :xid_conference");
+        } else {
+            result.prepare("INSERT INTO EVENT "
+                            " (xid_conference, id, start, duration, xid_track, type, "
+                                " language, tag, title, subtitle, abstract, description) "
+                            " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, "
+                                ":language, :tag, :title, :subtitle, :abstract, :description)");
+        }
+        result.bindValue(":xid_conference", aEvent["conference_id"]);
+        result.bindValue(":start", QString::number(startDateTime.toTime_t()));
+        result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+        result.bindValue(":xid_track", trackId);
+        static const QList<QString> props = QList<QString>()
+            << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description";
+        foreach (QString prop_name, props) {
+            result.bindValue(QString(":") + prop_name, aEvent[prop_name]);
+        }
+        if (!result.exec()) {
+            qWarning() << "event insert/update failed:" << result.lastError();
+        }
     }
 }
 
@@ -124,18 +218,23 @@ void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson)
 {
     QSqlDatabase db = QSqlDatabase::database();
 
-    //TODO: check if the person doesn't exist before inserting
     if (db.isValid() && db.isOpen())
     {
-        QString values = QString("'%1', '%2'").arg(aPerson["id"],aPerson["name"]);
-        QString query = QString("INSERT INTO PERSON (id,name) VALUES (%1)").arg(values);
-        QSqlQuery result (query, db);
-        //LOG_AUTOTEST(query);
-
-        values = QString("'%1', '%2', '%3'").arg(aPerson["conference_id"],aPerson["event_id"],aPerson["id"]);
-        query = QString("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (%1)").arg(values);
-        QSqlQuery resultEventPerson (query, db);
-        //LOG_AUTOTEST(query);
+        QSqlQuery query(db);
+        query.prepare("INSERT INTO PERSON (xid_conference,id,name) VALUES (:xid_conference, :id, :name)");
+        query.bindValue(":xid_conference", aPerson["conference_id"]);
+        query.bindValue(":id", aPerson["id"]);
+        query.bindValue(":name", aPerson["name"]);
+        query.exec(); // some queries fail due to the unique key constraint
+        // if (!query.exec()) qDebug() << "SQL query 'insert into person' failed: " << query.lastError();
+
+        query = QSqlQuery(db);
+        query.prepare("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (:xid_conference, :xid_event, :xid_person)");
+        query.bindValue(":xid_conference", aPerson["conference_id"]);
+        query.bindValue(":xid_event", aPerson["event_id"]);
+        query.bindValue(":xid_person", aPerson["id"]);
+        query.exec(); // some queries fail due to the unique key constraint
+        // if (!query.exec()) qDebug() << "SQL query 'insert into event_person' failed: " << query.lastError();
     }
 }
 
@@ -145,29 +244,37 @@ void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
 
     if (db.isValid() && db.isOpen())
     {
-        QString queryExist = QString("SELECT id FROM ROOM WHERE name='%1'").arg(aRoom["name"]);
-        QSqlQuery resultExist(queryExist,db);
+        QSqlQuery query(db);
+        query.prepare("SELECT id FROM ROOM WHERE xid_conference=:conference_id and name=:name");
+        query.bindValue(":conference_id", aRoom["conference_id"]);
+        query.bindValue(":name", aRoom["name"]);
+        if (!query.exec()) qDebug() << "Could not execute select room query: " << query.lastError();
         // now we have to check whether ROOM record with 'name' exists or not,
         // - if it doesn't exist yet, then we have to add that record to 'ROOM' table
         //   and assign autoincremented 'id' to aRoom
         // - if it exists, then we need to get its 'id' and assign it to aRoom
-        int roomId = -1;
-        if(resultExist.next()) // ROOM record with 'name' already exists: we need to get its 'id'
+        aRoom["id"] = "";
+        if(query.next()) // ROOM record with 'name' already exists: we need to get its 'id'
         {
-            roomId = resultExist.value(0).toInt();
+            aRoom["id"] = query.value(0).toString();
         }
         else // ROOM record doesn't exist yet, need to create it
         {
-            QString values = QString("'%1', '%2'").arg(aRoom["name"],aRoom["picture"]);
-            QString query = QString("INSERT INTO ROOM (name,picture) VALUES (%1)").arg(values);
-            QSqlQuery result (query, db);
-            roomId = result.lastInsertId().toInt(); // 'id' is assigned automatically
+            query = QSqlQuery(db);
+            query.prepare("INSERT INTO ROOM (xid_conference,name,picture) VALUES (:xid_conference, :name, :picture)");
+            query.bindValue(":xid_conference", aRoom["conference_id"]);
+            query.bindValue(":xid_name", aRoom["name"]);
+            query.bindValue(":xid_picture", aRoom["picture"]);
+            if (!query.exec()) qDebug() << "Could not execute 'insert into room ...' query." << query.lastError();
+            aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
             //LOG_AUTOTEST(query);
         }
-
-        QString values = QString("'%1', '%2', '%3'").arg(aRoom["conference_id"],aRoom["event_id"],QString::number(roomId));
-        QString query = QString("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (%1)").arg(values);
-        QSqlQuery result (query, db);
+        query = QSqlQuery(db);
+        query.prepare("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (:conference_id, :event_id, :room_id)");
+        query.bindValue(":conference_id", aRoom["conference_id"]);
+        query.bindValue(":event_id", aRoom["event_id"]);
+        query.bindValue(":room_id", aRoom["id"]);
+        if (!query.exec()) qDebug() << "Could not 'execute insert into event_room' query:" << query.lastError();
         //LOG_AUTOTEST(query);
     }
 }
@@ -179,94 +286,146 @@ void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
     //TODO: check if the link doesn't exist before inserting
     if (db.isValid() && db.isOpen())
     {
-        QString values = QString("'%1', '%2', '%3', '%4'").arg(aLink["event_id"],aLink["conference_id"],aLink["name"],aLink["url"]);
-        QString query = QString("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (%1)").arg(values);
-        QSqlQuery result(query, db);
+        QSqlQuery query(db);
+        query.prepare("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (:xid_event, :xid_conference, :name, :url)");
+        query.bindValue(":xid_event", aLink["event_id"]);
+        query.bindValue(":xid_conference", aLink["conference_id"]);
+        query.bindValue(":name", aLink["name"]);
+        query.bindValue(":url", aLink["url"]);
+        if (!query.exec()) qDebug() << "Error executing 'insert into link' query: " << query.lastError();
         //LOG_AUTOTEST(query);
     }
 }
 
-bool SqlEngine::createTables(QSqlDatabase &aDatabase)
+int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
 {
-    bool result = aDatabase.open();
+    QSqlDatabase db = QSqlDatabase::database();
 
-    if (aDatabase.isValid() && aDatabase.isOpen())
-    {
-        QSqlQuery query(aDatabase);
-
-        query.exec("CREATE TABLE CONFERENCE ( \
-            id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \
-            title VARCHAR NOT NULL , \
-            subtitle VARCHAR, \
-            venue VARCHAR, \
-            city VARCHAR NOT NULL , \
-            start DATETIME NOT NULL , \
-            end DATETIME NOT NULL , \
-            days INTEGER, \
-            day_change DATETIME, \
-            timeslot_duration DATETIME)");
-
-        query.exec("CREATE TABLE ACTIVITY ( \
-            id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL , \
-            name VARCHAR NOT NULL )");
-
-        query.exec("CREATE TABLE ROOM ( \
-            id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \
-            name VARCHAR NOT NULL , \
-            picture VARCHAR NOT NULL)");
-
-        query.exec("CREATE TABLE PERSON ( \
-            id INTEGER PRIMARY KEY  NOT NULL , \
-            name VARCHAR NOT NULL)");
-
-        query.exec("CREATE TABLE EVENT ( \
-            xid_conference INTEGER  NOT NULL, \
-            id INTEGER NOT NULL , \
-            start INTEGER NOT NULL , \
-            duration INTEGER NOT NULL , \
-            xid_activity INTEGER NOT NULL REFERENCES ACTIVITY(id), \
-            type VARCHAR, \
-            language VARCHAR, \
-            PRIMARY KEY (xid_conference,id), \
-            FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id) \
-            FOREIGN KEY(xid_activity) REFERENCES ACTIVITY(id))");
-
-        query.exec("CREATE VIRTUAL TABLE VIRTUAL_EVENT using fts3 ( \
-            xid_conference INTEGER  NOT NULL, \
-            id INTEGER NOT NULL , \
-            tag VARCHAR,title VARCHAR NOT NULL , \
-            subtitle VARCHAR, \
-            abstract VARCHAR, \
-            description VARCHAR, \
-            PRIMARY KEY (xid_conference,id))");
-
-        query.exec("CREATE TABLE EVENT_PERSON ( \
-            xid_conference INTEGER NOT NULL , \
-            xid_event INTEGER NOT NULL , \
-            xid_person INTEGER NOT NULL, \
-            FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id), \
-            FOREIGN KEY(xid_person) REFERENCES PERSON(id))");
-
-        query.exec("CREATE TABLE EVENT_ROOM ( \
-            xid_conference INTEGER NOT NULL , \
-            xid_event INTEGER NOT NULL , \
-            xid_room INTEGER NOT NULL, \
-            FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id), \
-            FOREIGN KEY(xid_room) REFERENCES ROOM(id))");
-
-        query.exec("CREATE TABLE LINK ( \
-            id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, \
-            xid_conference INTEGER NOT NULL, \
-            xid_event INTEGER NOT NULL, \
-            name VARCHAR, \
-            url VARCHAR NOT NULL, \
-            FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id))");
+    if ( !db.isValid() || !db.isOpen())
+        return -1;
+
+    if (aColumns.empty()) return -1;
+
+    // DROP
+    execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT");
+    // CREATE
+    execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL )");
+    // INSERT
+    QString sql = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
+                "SELECT EVENT.xid_conference, EVENT.id FROM EVENT ");
+    if( aColumns.contains("ROOM") ){
+        sql += "INNER JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
+        sql += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
     }
-    else
-    {
-        //LOG_WARNING("Database is not opened");
+    if( aColumns.contains("PERSON") ){
+        sql += "INNER JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
+        sql += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
+    }
+    sql += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
+
+    foreach (QString table, aColumns.uniqueKeys()){
+        foreach (QString column, aColumns.values(table)){
+            sql += QString("%1.%2 LIKE '\%' || :%1%2 || '\%' OR ").arg( table, column );
+        }
+    }
+    sql.chop( QString(" OR ").length() );
+    sql += QString(")");
+
+    QSqlQuery query(db);
+    query.prepare(sql);
+    foreach (QString table, aColumns.uniqueKeys()){
+        foreach (QString column, aColumns.values(table)){
+            query.bindValue(QString(":%1%2").arg(table, column), aKeyword );
+        }
+    }
+
+    if( !query.exec() ){
+       qDebug() << "Could not execute search query: " << query.lastError().text();
+       return -1;
     }
 
-    return result;
+    return 1;
 }
 
+bool SqlEngine::beginTransaction()
+{
+    QSqlDatabase db = QSqlDatabase::database();
+
+    return execQuery(db, "BEGIN IMMEDIATE TRANSACTION");
+}
+
+bool SqlEngine::commitTransaction()
+{
+    QSqlDatabase db = QSqlDatabase::database();
+
+    return execQuery(db, "COMMIT");
+}
+
+void SqlEngine::deleteConference(int id)
+{
+    QSqlDatabase db = QSqlDatabase::database();
+
+    if ( !db.isValid() || !db.isOpen()) {
+        return;
+    }
+
+    beginTransaction();
+
+    QHash<QString, QVariant> params;
+    params["xid_conference"] = id;
+    execQueryWithParameter(db, "DELETE FROM LINK WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM EVENT_ROOM WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM EVENT_PERSON WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM EVENT WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM ROOM WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM PERSON WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM TRACK WHERE xid_conference = :xid_conference", params);
+    execQueryWithParameter(db, "DELETE FROM CONFERENCE WHERE id = :xid_conference", params);
+
+    commitTransaction();
+}
+
+bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
+{
+    //qDebug() << "\nSQL: " << aQuery;
+
+    QSqlQuery sqlQuery(aDatabase);
+    if( !sqlQuery.exec(aQuery) ){
+       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
+       return false;
+    }
+    else{
+       //qDebug() << "SQL OK.\n";
+       return true;
+    }
+}
+
+bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params)
+{
+    qDebug() << "SQL:" << aQuery << "params:" << params;
+
+    QSqlQuery sqlQuery(aDatabase);
+    sqlQuery.prepare(aQuery);
+    foreach (QString param_key, params.keys()) {
+        sqlQuery.bindValue(param_key, params[param_key]);
+    }
+    if( !sqlQuery.exec() ){
+       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
+       return false;
+    }
+    else{
+       //qDebug() << "SQL OK.\n";
+       return true;
+    }
+}
+
+void SqlEngine::checkConferenceMap(QSqlDatabase &aDatabase)
+{
+    QSqlQuery sqlQuery(aDatabase);
+    sqlQuery.prepare("SELECT map FROM conference");
+    if (!sqlQuery.exec()) {
+        qWarning() << "column conference.map is missing; adding";
+        execQuery(aDatabase, "ALTER TABLE conference ADD COLUMN map VARCHAR")
+         and execQuery(aDatabase, "UPDATE conference SET map = ':/maps/campus.png' WHERE title = 'FOSDEM 2010'");
+    }
+}