store room map in database
[toast/confclerk.git] / src / sql / sqlengine.cpp
index 65079e7..c87ce2b 100644 (file)
@@ -50,15 +50,6 @@ 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);
-        */
-
         // copy conference Db from resource, instead of creating
         // empty tables and then parsing the schedule
         QFile dbFile(aDatabaseName);
@@ -71,6 +62,8 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN
         database.open();
     }
 
+    checkConferenceMap(database);
+
     //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
 
     return result ? QString() : database.lastError().text();
@@ -110,21 +103,21 @@ void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference)
 
         if(!confId) // conference 'aConference' isn't in the table => insert
         {
-            QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9'") \
-                             .arg(aConference["title"]) \
-                             .arg(aConference["subtitle"]) \
-                             .arg(aConference["venue"]) \
-                             .arg(aConference["city"]) \
-                             .arg(QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \
-                             .arg(QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \
-                             .arg(aConference["days"]) \
-                             .arg(-QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0))) \
-                             .arg(-QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
-                             values.append(QString(", '%1'").arg(confsList.count()>0?"0":"1"));
-
-            QString query = QString("INSERT INTO CONFERENCE (title,subtitle,venue,city,start,end,days,day_change,timeslot_duration,active) VALUES (%1)").arg(values);
-            QSqlQuery result (query, db);
-            aConference["id"] = result.lastInsertId().toString(); // 'id' is assigned automatically
+            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);
+            query.exec();
+            aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
         }
     }
 }
@@ -161,9 +154,16 @@ void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
         {
             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["xid_conference"]);
+            check_event_query.bindValue(":xid_conference", aEvent["conference_id"]);
             check_event_query.bindValue(":id", aEvent["id"]);
-            if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.first()) {
+            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;
             }
         }
@@ -186,7 +186,7 @@ void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
             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 "
+                            " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, "
                                 ":language, :tag, :title, :subtitle, :abstract, :description)");
         }
         result.bindValue(":xid_conference", aEvent["conference_id"]);
@@ -198,7 +198,9 @@ void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
         foreach (QString prop_name, props) {
             result.bindValue(QString(":") + prop_name, aEvent[prop_name]);
         }
-        result.exec();
+        if (!result.exec()) {
+            qWarning() << "event insert/update failed:" << result.lastError();
+        }
     }
 }
 
@@ -268,94 +270,6 @@ void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
     }
 }
 
-bool SqlEngine::createTables(QSqlDatabase &aDatabase)
-{
-    bool result = aDatabase.open();
-
-    if (aDatabase.isValid() && aDatabase.isOpen())
-    {
-        QSqlQuery query(aDatabase);
-
-        query.exec("CREATE TABLE CONFERENCE ( "
-            "id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, "
-            "title VARCHAR UNIQUE NOT NULL, "
-            "subtitle VARCHAR, "
-            "venue VARCHAR, "
-            "city VARCHAR NOT NULL, "
-            "start INTEGER NOT NULL, "
-            "end INTEGER NOT NULL, "
-            "days INTEGER, "
-            "day_change INTEGER, "
-            "timeslot_duration INTEGER, "
-            "active INTEGER DEFAULT 0);");
-
-        query.exec("CREATE TABLE TRACK ( "
-            "id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL, "
-            "name VARCHAR UNIQUE NOT NULL );");
-
-        query.exec("CREATE TABLE ROOM ( "
-            "id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, "
-            "name VARCHAR UNIQUE NOT NULL, "
-            "picture VARCHAR NOT NULL);");
-
-        query.exec("CREATE TABLE PERSON ( "
-            "id INTEGER PRIMARY KEY  NOT NULL, "
-            "name VARCHAR UNIQUE 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_track INTEGER NOT NULL REFERENCES TRACK(id), "
-            "type VARCHAR, "
-            "language VARCHAR, "
-            "tag VARCHAR,title VARCHAR NOT NULL, "
-            "subtitle VARCHAR, "
-            "abstract VARCHAR, "
-            "description VARCHAR, "
-            "favourite INTEGER DEFAULT 0, "
-            "alarm INTEGER DEFAULT 0, "
-            "PRIMARY KEY (xid_conference,id), "
-            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
-            "FOREIGN KEY(xid_track) REFERENCES TRACK(id));");
-
-        query.exec("CREATE TABLE EVENT_PERSON ( "
-            "xid_conference INTEGER NOT NULL, "
-            "xid_event INTEGER NOT NULL, "
-            "xid_person INTEGER NOT NULL, "
-            "UNIQUE ( xid_conference, xid_event, xid_person ) ON CONFLICT REPLACE, "
-            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
-            "FOREIGN KEY(xid_event) REFERENCES EVENT(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, "
-            "UNIQUE ( xid_conference, xid_event, xid_room ) ON CONFLICT REPLACE, "
-            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
-            "FOREIGN KEY(xid_event) REFERENCES EVENT(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, "
-            "UNIQUE ( xid_conference, xid_event, url ) ON CONFLICT REPLACE, "
-            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
-            "FOREIGN KEY(xid_event) REFERENCES EVENT(id));");
-    }
-    else
-    {
-        //LOG_WARNING("Database is not opened");
-    }
-
-    return result;
-}
-
 int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
 {
     QSqlDatabase db = QSqlDatabase::database();
@@ -408,6 +322,29 @@ bool SqlEngine::commitTransaction()
     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 CONFERENCE WHERE id = :xid_conference", params);
+    execQuery(db, "DELETE FROM ROOM WHERE NOT EXISTS(SELECT * FROM EVENT_ROOM WHERE xid_room = ROOM.id)");
+    execQuery(db, "DELETE FROM PERSON WHERE NOT EXISTS(SELECT * FROM EVENT_PERSON WHERE xid_person = PERSON.id)");
+
+    commitTransaction();
+}
+
 bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
 {
     //qDebug() << "\nSQL: " << aQuery;
@@ -423,3 +360,32 @@ bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
     }
 }
 
+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'");
+    }
+}