Restructured the SqlEngine. Not yet finished (see "TODO" in the code).
[toast/confclerk.git] / src / sql / sqlengine.cpp
index c210b84352a4b71e1481260731a8898b6f59ec5d..be9e164b424d16fe15c61d78aedae52f4208f10a 100644 (file)
 const QString DATE_FORMAT ("yyyy-MM-dd");
 const QString TIME_FORMAT ("hh:mm");
 
-SqlEngine::SqlEngine(QObject *aParent)
-    : QObject(aParent)
-{
+SqlEngine::SqlEngine(QObject *aParent): QObject(aParent) {
+    QDir dbPath(QDesktopServices::storageLocation(QDesktopServices::DataLocation));
+    dbFilename = dbPath.absoluteFilePath("ConfClerk.sqlite");
 }
 
-SqlEngine::~SqlEngine()
-{
+
+SqlEngine::~SqlEngine() {
 }
 
-QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseName)
-{
-    QSqlDatabase database = QSqlDatabase::addDatabase(aDatabaseType);
-    database.setDatabaseName(aDatabaseName);
 
-    bool result = false;
-    if(!QFile::exists(aDatabaseName)) // the DB (tables) doesn't exists, and so we have to create one
-    {
-        // 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(";")) {
-            if (sql.trimmed().length() == 0)     // do not execute empty queries like the last character from create_tables.sql
-                continue;
-            QSqlQuery query(database);
-            if (!query.exec(sql)) qDebug() << "Could not execute query '" << sql << "' error:" << query.lastError();
-        }
+void SqlEngine::open() {
+    QFileInfo dbFilenameInfo(dbFilename);
+    dbFilenameInfo.absoluteDir().mkpath(""); // TODO ...
+    db = QSqlDatabase::addDatabase("QSQLITE");
+    db.setDatabaseName(dbFilename);
+    db.open();
+}
+
+
+int SqlEngine::dbSchemaVersion() {
+    QSqlQuery query(db);
+    if (!query.exec("PRAGMA user_version")) {
+        emitSqlQueryError(query);
+        return -2;
     }
-    else
-    {
-        database.open();
+    query.first();
+    int version = query.value(0).toInt();
+    if (version == 0) {
+        // check whether the tables are existing
+        if (!query.exec("select count(*) from sqlite_master where name='CONFERENCE'")) {
+            emitSqlQueryError(query);
+            return -2;
+        }
+        query.first();
+        if (query.value(0).toInt() == 1) return 0; // tables are existing
+        return -1; // database seems to be empty (or has other tables)
     }
+    return version;
+}
 
-    //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
 
-    return result ? QString() : database.lastError().text();
+bool SqlEngine::updateDbSchemaVersion000To001() {
+    emit dbError("Upgrade 0 -> 1 not implemented yet");
+    return false;
 }
 
-void SqlEngine::initialize()
-{
-    QString databaseName;
-    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);
+
+bool SqlEngine::createCurrentDbSchema() {
+    QFile file(":/dbschema001.sql");
+    file.open(QIODevice::ReadOnly | QIODevice::Text);
+    QString allSqlStatements = file.readAll();
+    QSqlQuery query(db);
+    foreach(QString sql, allSqlStatements.split(";")) {
+        if (sql.trimmed().isEmpty())  // do not execute empty queries like the last character from create_tables.sql
+            continue;
+        if (!query.exec(sql)) {
+            emitSqlQueryError(query);
+            return false;
+        }
+    }
+    return true;
+}
+
+
+bool SqlEngine::createOrUpdateDbSchema() {
+    int version = dbSchemaVersion();
+    switch (version) {
+    case -2:
+        // the error has already been emitted by the previous function
+        return false;
+    case -1:
+        // empty database
+        return createCurrentDbSchema();
+    case 0:
+        // db schema version 0
+        return updateDbSchemaVersion000To001();
+    case 1:
+        // current schema
+        return true;
+    default:
+        // unsupported schema
+        emit dbError(tr("Unsupported database schema version %1.").arg(version));
+    }
+    return false;
 }
 
-void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId)
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
-    if (db.isValid() && db.isOpen())
+void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId) {
+    // HACK
+    // When city is empty, assign a dummy value. We probably want to find a way to change the database scheme ...
+    // cf. #32
+    if (aConference["city"].isEmpty()) aConference["city"] = "n/a";
+
+    QSqlQuery query(db);
+    if (conferenceId <= 0) // insert conference
     {
-        // HACK
-        // When city is empty, assign a dummy value. We probably want to find a way to change the database scheme ...
-        // cf. #32
-        if (aConference["city"].isEmpty()) aConference["city"] = "n/a";
-
-        QSqlQuery query(db);
-        if (conferenceId <= 0) // insert conference
-        {
-            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(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
-            query.bindValue(":active", 1);
-            if (!query.exec()) qDebug() << "Could not execute query to insert a conference:" << query.lastError();
-            aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
+        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]);
         }
-        else // update conference
-        {
-            query.prepare("UPDATE CONFERENCE set title=:title, url=:url, subtitle=:subtitle, venue=:venue, city=:city, start=:start, end=:end, days=:days,"
-                                                "day_change=:day_change, timeslot_duration=:timeslot_duration, active=:active "
-                          "WHERE id=:id");
-            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(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
-            query.bindValue(":active", 1);
-            query.bindValue(":id", conferenceId);
-            if (!query.exec()) qDebug() << "Could not execute query to update a conference:" << query.lastError();
-            aConference["id"] = conferenceId;
+        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(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+        query.bindValue(":active", 1);
+        emitSqlQueryError(query);
+        aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
+    }
+    else // update conference
+    {
+        query.prepare("UPDATE CONFERENCE set title=:title, url=:url, subtitle=:subtitle, venue=:venue, city=:city, start=:start, end=:end, days=:days,"
+                                            "day_change=:day_change, timeslot_duration=:timeslot_duration, active=:active "
+                      "WHERE id=:id");
+        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(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+        query.bindValue(":active", 1);
+        query.bindValue(":id", conferenceId);
+        emitSqlQueryError(query);
+        aConference["id"] = conferenceId;
     }
 }
 
-void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
-    if (db.isValid() && db.isOpen())
+void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent) {
+    //insert event track to table and get track id
+    int conference = aEvent["conference_id"].toInt();
+    QString name = aEvent["track"];
+    Track track;
+    int trackId;
+    try
     {
-        //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();
-        }
-        catch (OrmNoObjectException &e) {
-            track.setConference(conference);
-            track.setName(name);
-            trackId = track.insert();
-        }
-        QDateTime startDateTime;
-        startDateTime.setTimeSpec(Qt::UTC);
-        startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
-
-        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;
-            }
-        }
+        track = Track::retrieveByName(conference, name);
+        trackId = track.id();
+    }
+    catch (OrmNoObjectException &e) {
+        track.setConference(conference);
+        track.setName(name);
+        trackId = track.insert();
+    }
+    QDateTime startDateTime;
+    startDateTime.setTimeSpec(Qt::UTC);
+    startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
 
-        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]);
+    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 (!result.exec()) {
-            qWarning() << "event insert/update failed:" << result.lastError();
+        if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) {
+            event_exists = true;
         }
     }
-}
 
-void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson)
-{
-    QSqlDatabase db = QSqlDatabase::database();
+    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();
+    }
+}
 
-    if (db.isValid() && db.isOpen())
-    {
-        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();
-    }
+void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson) {
+    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(); // TODO 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(); // TODO some queries fail due to the unique key constraint
+    // if (!query.exec()) qDebug() << "SQL query 'insert into event_person' failed: " << query.lastError();
 }
 
-void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
-    if (db.isValid() && db.isOpen())
+void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom) {
+    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"]);
+    emitSqlQueryError(query);
+    // 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
+    aRoom["id"] = "";
+    if(query.next()) // ROOM record with 'name' already exists: we need to get its 'id'
+    {
+        aRoom["id"] = query.value(0).toString();
+    }
+    else // ROOM record doesn't exist yet, need to create it
     {
-        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
-        aRoom["id"] = "";
-        if(query.next()) // ROOM record with 'name' already exists: we need to get its 'id'
-        {
-            aRoom["id"] = query.value(0).toString();
-        }
-        else // ROOM record doesn't exist yet, need to create it
-        {
-            query = QSqlQuery(db);
-            query.prepare("INSERT INTO ROOM (xid_conference,name,picture) VALUES (:xid_conference, :name, '')");
-            query.bindValue(":xid_conference", aRoom["conference_id"]);
-            query.bindValue(":xid_name", aRoom["name"]);
-            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);
-        }
-        
-        // remove previous conference/room records; room names might have changed
-        query = QSqlQuery(db);
-        query.prepare("DELETE FROM EVENT_ROOM WHERE xid_conference=:conference_id AND xid_event=:event_id");
-        query.bindValue(":conference_id", aRoom["conference_id"]);
-        query.bindValue(":event_id", aRoom["event_id"]);
-        if (!query.exec()) qDebug() << "Could not execute SELECT * FROM EVENT_ROOM' query:" << query.lastError();
-        // and insert new ones
         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();
+        query.prepare("INSERT INTO ROOM (xid_conference,name,picture) VALUES (:xid_conference, :name, '')");
+        query.bindValue(":xid_conference", aRoom["conference_id"]);
+        query.bindValue(":xid_name", aRoom["name"]);
+        emitSqlQueryError(query);
+        aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
+        //LOG_AUTOTEST(query);
     }
+
+    // remove previous conference/room records; room names might have changed
+    query = QSqlQuery(db);
+    query.prepare("DELETE FROM EVENT_ROOM WHERE xid_conference=:conference_id AND xid_event=:event_id");
+    query.bindValue(":conference_id", aRoom["conference_id"]);
+    query.bindValue(":event_id", aRoom["event_id"]);
+    emitSqlQueryError(query);
+    // and insert new ones
+    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"]);
+    emitSqlQueryError(query);
 }
 
-void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
+void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink) {
     //TODO: check if the link doesn't exist before inserting
-    if (db.isValid() && db.isOpen())
-    {
-        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();
-    }
+    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"]);
+    emitSqlQueryError(query);
 }
 
-int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
-{
-    QSqlDatabase db = QSqlDatabase::database();
-
-    if ( !db.isValid() || !db.isOpen())
-        return -1;
 
-    if (aColumns.empty()) return -1;
+bool SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword) {
+    if (aColumns.empty()) return false;
 
     // DROP
-    execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT");
+    QSqlQuery query(db);
+    query.exec("DROP TABLE IF EXISTS SEARCH_EVENT");
+    emitSqlQueryError(query);
+
     // CREATE
-    execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL )");
+    query.exec("CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL )");
+    emitSqlQueryError(query);
+
     // INSERT
     QString sql = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
                 "SELECT DISTINCT EVENT.xid_conference, EVENT.id FROM EVENT ");
@@ -342,7 +352,6 @@ int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aCol
     sql += whereAnd.join(") AND (");
     sql += QString(")");
 
-    QSqlQuery query(db);
     query.prepare(sql);
     for (int i = 0; i != searchKeywords.size(); ++i) {
         QString keyword = searchKeywords[i];
@@ -353,72 +362,60 @@ int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aCol
         }
     }
 
-    if( !query.exec() ){
-       qDebug() << "Could not execute search query: " << query.lastError().text();
-       return -1;
-    }
-
-    return 1;
+    bool success = query.exec();
+    emitSqlQueryError(query);
+    return success;
 }
 
-bool SqlEngine::beginTransaction()
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
-    return execQuery(db, "BEGIN IMMEDIATE TRANSACTION");
+bool SqlEngine::beginTransaction() {
+    QSqlQuery query(db);
+    bool success = query.exec("BEGIN IMMEDIATE TRANSACTION");
+    emitSqlQueryError(query);
+    return success;
 }
 
-bool SqlEngine::commitTransaction()
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
-    return execQuery(db, "COMMIT");
+bool SqlEngine::commitTransaction() {
+    QSqlQuery query(db);
+    bool success = query.exec("COMMIT");
+    emitSqlQueryError(query);
+    return success;
 }
 
-void SqlEngine::deleteConference(int id)
-{
-    QSqlDatabase db = QSqlDatabase::database();
 
-    if ( !db.isValid() || !db.isOpen()) {
-        return;
+bool SqlEngine::deleteConference(int id) {
+    QSqlQuery query(db);
+    bool success = query.exec("BEGIN IMMEDIATE TRANSACTION");
+    emitSqlQueryError(query);
+
+    QStringList sqlList;
+    sqlList << "DELETE FROM LINK WHERE xid_conference = ?"
+            << "DELETE FROM EVENT_ROOM WHERE xid_conference = ?"
+            << "DELETE FROM EVENT_PERSON WHERE xid_conference = ?"
+            << "DELETE FROM EVENT WHERE xid_conference = ?"
+            << "DELETE FROM ROOM WHERE xid_conference = ?"
+            << "DELETE FROM PERSON WHERE xid_conference = ?"
+            << "DELETE FROM TRACK WHERE xid_conference = ?"
+            << "DELETE FROM CONFERENCE WHERE id = ?";
+
+    foreach (const QString& sql, sqlList) {
+        query.prepare(sql);
+        query.bindValue(0, id);
+        success &= query.exec();
+        emitSqlQueryError(query);
     }
 
-    beginTransaction();
+    success &= query.exec("COMMIT");
+    emitSqlQueryError(query);
 
-    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();
+    return success;
 }
 
-bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
-{
-    QSqlQuery sqlQuery(aDatabase);
-    if( !sqlQuery.exec(aQuery) ){
-       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
-       return false;
-    }
-    return true;
-}
 
-bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& 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;
-    }
-    return true;
+void SqlEngine::emitSqlQueryError(const QSqlQuery &query) {
+    QSqlError error = query.lastError();
+    if (error.type() == QSqlError::NoError) return;
+    emit dbError(error.text());
 }
+