some 'delegate' drawing optimizations
[toast/confclerk.git] / src / sql / sqlengine.cpp
index b77729f..fcaceb2 100644 (file)
@@ -7,6 +7,8 @@
 
 #include <QDir>
 #include "sqlengine.h"
+#include <track.h>
+#include <conference.h>
 
 #include <QDebug>
 
@@ -37,8 +39,10 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN
         //  - create new DB from resource, which contains empty DB with tables
         result = createTables(database);
     }
-
-    database.open();
+    else
+    {
+        database.open();
+    }
 
     //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
 
@@ -51,7 +55,6 @@ void SqlEngine::initialize()
     if(!QDir::home().exists(".fosdem"))
         QDir::home().mkdir(".fosdem");
     databaseName = QDir::homePath() + "/.fosdem/" + "fosdem.sqlite";
-
     login("QSQLITE",databaseName);
 }
 
@@ -61,21 +64,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
+        {
+            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
+        }
     }
 }
 
@@ -87,36 +110,45 @@ 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);
+        //insert event track to table and get track id
+        QString name = aEvent["track"];
+        Track track;
+        int trackId;
+        try
+        {
+            track = Track::retrieveByName(name);
+            trackId = track.id();
+            /*qDebug() << QString("DEBUG: Track %1 in DB").arg(name);*/
+        }
+        catch (OrmNoObjectException &e) {
+            track.setName(name);
+            trackId = track.insert();
+            /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/
+        }
         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'") \
+        QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', ? , ? , ? , ? , ? , '%8', '%9'") \
                          .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(-QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0))) \
+                         .arg(trackId) \
                          .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);
-
-        // 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);
+                         .arg(aEvent["language"]) \
+                         .arg("0") \
+                         .arg("0");
+
+        QString query =
+            QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, type, language, tag, title, subtitle, abstract, description, favourite, alarm) VALUES (%1)")
+            .arg(values);
+
+        QSqlQuery result;
+        result.prepare(query);
+        result.bindValue(0,aEvent["tag"]);
+        result.bindValue(1,aEvent["title"]);
+        result.bindValue(2,aEvent["subtitle"]);
+        result.bindValue(3,aEvent["abstract"]);
+        result.bindValue(4,aEvent["description"]);
+        result.exec();
     }
 }
 
@@ -194,73 +226,77 @@ bool SqlEngine::createTables(QSqlDatabase &aDatabase)
     {
         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))");
+        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 IGNORE, "
+            "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 IGNORE, "
+            "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 IGNORE, "
+            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
+            "FOREIGN KEY(xid_event) REFERENCES EVENT(id));");
     }
     else
     {
@@ -270,3 +306,57 @@ bool SqlEngine::createTables(QSqlDatabase &aDatabase)
     return result;
 }
 
+int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
+{
+    QSqlDatabase db = QSqlDatabase::database();
+
+    if ( !db.isValid() || !db.isOpen())
+        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 query = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id) "
+                "SELECT EVENT.xid_conference, EVENT.id FROM EVENT ");
+    if( aColumns.contains("ROOM") ){
+        query += "INNER JOIN EVENT_ROOM ON (EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
+        query += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
+    }
+    if( aColumns.contains("PERSON") ){
+        query += "INNER JOIN EVENT_PERSON ON (EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
+        query += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
+    }
+    query += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
+
+    foreach (QString table, aColumns.uniqueKeys()){
+        foreach (QString column, aColumns.values(table)){
+            query += QString("%1.%2 LIKE '\%%3\%' OR ").arg( table, column, aKeyword );
+        }
+    }
+    query.chop( QString(" OR ").length() );
+    query += QString(");");
+
+    execQuery( db, query );
+
+    //TODO: retun number of rows from SEARCH_EVENT
+    return 1;
+}
+
+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;
+    }
+}
+