Restructured the SqlEngine. Not yet finished (see "TODO" in the code).

diff --git a/src/app/main.cpp b/src/app/main.cpp
index 6f8a6ba1a316d9bb45fa5ca6057a503ce8bb209e..29f36fbec9f3badf2f178a05050ad27de6fa2f55 100644 (file)
--- a/src/app/main.cpp
+++ b/src/app/main.cpp
@@ -45,10 +45,7 @@ int main(int argc, char *argv[])
     QCoreApplication::setApplicationName("ConfClerk");
     QCoreApplication::setApplicationVersion(VERSION);
  
-    SqlEngine::initialize(); // creates "SQLITE" DB connection
-
     QWidget *window;
-
     window = new MainWindow;
 
 
@@ -76,7 +73,7 @@ int main(int argc, char *argv[])
 
     if(argc > 1)
     {
-        EventDialog dialog(atoi(argv[1]),window);
+        EventDialog dialog(atoi(argv[1]), window);
         dialog.exec();
     }
 #endif

diff --git a/src/create_tables.sql b/src/create_tables.sql
deleted file mode 100644 (file)
index 122c1cf..0000000
--- a/src/create_tables.sql
+++ /dev/null
@@ -1,74 +0,0 @@
-BEGIN TRANSACTION;
-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
-    , url VARCHAR);
-
-CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL
-    , xid_conference INTEGER NOT NULL
-    , name VARCHAR NOT NULL
-    , UNIQUE (xid_conference, name));
-
-CREATE TABLE ROOM ( id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL
-    , xid_conference INTEGER NOT NULL
-    , name VARCHAR NOT NULL
-    , picture VARCHAR NOT NULL
-    , UNIQUE (xid_conference, name));
-
-CREATE TABLE PERSON ( id INTEGER NOT NULL
-    , xid_conference INTEGER NOT NULL
-    , name VARCHAR NOT NULL
-    , UNIQUE (xid_conference, name)
-    , PRIMARY KEY (id, xid_conference)); 
-
-CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL
-    , id INTEGER NOT NULL
-    , start INTEGER NOT NULL
-    , duration INTEGER NOT NULL -- duration of the event in seconds
-    , 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));
-
-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_conference, xid_event) REFERENCES EVENT(xid_conference, id)
-    , FOREIGN KEY(xid_conference, xid_person) REFERENCES PERSON(xid_conference, id));
-
-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_conference, xid_event) REFERENCES EVENT(xid_conference, id)
-    , FOREIGN KEY(xid_conference, xid_room) REFERENCES ROOM(xid_conference, id));
-
-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_conference, xid_event) REFERENCES EVENT(xid_conference, id));
-COMMIT;

diff --git a/src/db.qrc b/src/db.qrc
index 03d8d30057937dd70dacfce743eeac8b880c0df6..d14bd42513494dc895338d7b3386f8ffeaa4b165 100644 (file)
--- a/src/db.qrc
+++ b/src/db.qrc
@@ -1,6 +1,6 @@
 <!DOCTYPE RCC><RCC version="1.0">
 <qresource>
-    <file>create_tables.sql</file>
+    <file>dbschema001.sql</file>
 </qresource>
 </RCC>
 

diff --git a/src/dbschema000.sql b/src/dbschema000.sql
new file mode 100644 (file)
index 0000000..122c1cf
--- /dev/null
+++ b/src/dbschema000.sql
@@ -0,0 +1,74 @@
+BEGIN TRANSACTION;
+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
+    , url VARCHAR);
+
+CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name));
+
+CREATE TABLE ROOM ( id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , picture VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name));
+
+CREATE TABLE PERSON ( id INTEGER NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name)
+    , PRIMARY KEY (id, xid_conference)); 
+
+CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL
+    , id INTEGER NOT NULL
+    , start INTEGER NOT NULL
+    , duration INTEGER NOT NULL -- duration of the event in seconds
+    , 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));
+
+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_conference, xid_event) REFERENCES EVENT(xid_conference, id)
+    , FOREIGN KEY(xid_conference, xid_person) REFERENCES PERSON(xid_conference, id));
+
+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_conference, xid_event) REFERENCES EVENT(xid_conference, id)
+    , FOREIGN KEY(xid_conference, xid_room) REFERENCES ROOM(xid_conference, id));
+
+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_conference, xid_event) REFERENCES EVENT(xid_conference, id));
+COMMIT;

diff --git a/src/dbschema001.sql b/src/dbschema001.sql
new file mode 100644 (file)
index 0000000..2e7f5fb
--- /dev/null
+++ b/src/dbschema001.sql
@@ -0,0 +1,76 @@
+BEGIN TRANSACTION;
+CREATE TABLE CONFERENCE ( id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL
+    , title VARCHAR 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
+    , url VARCHAR);
+
+CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name));
+
+CREATE TABLE ROOM ( id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , picture VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name));
+
+CREATE TABLE PERSON ( id INTEGER NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name)
+    , PRIMARY KEY (id, xid_conference)); 
+
+CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL
+    , id INTEGER NOT NULL
+    , start INTEGER NOT NULL
+    , duration INTEGER NOT NULL -- duration of the event in seconds
+    , 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));
+
+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_conference, xid_event) REFERENCES EVENT(xid_conference, id)
+    , FOREIGN KEY(xid_conference, xid_person) REFERENCES PERSON(xid_conference, id));
+
+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_conference, xid_event) REFERENCES EVENT(xid_conference, id)
+    , FOREIGN KEY(xid_conference, xid_room) REFERENCES ROOM(xid_conference, id));
+
+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_conference, xid_event) REFERENCES EVENT(xid_conference, id));
+
+PRAGMA user_version=1;
+COMMIT;

diff --git a/src/gui/mainwindow.cpp b/src/gui/mainwindow.cpp
index b6262244e87157f159a17cae825c659cda70eabd..9a17036083fac6466f6916c964bd1a13b7ab4845 100644 (file)
--- a/src/gui/mainwindow.cpp
+++ b/src/gui/mainwindow.cpp
@@ -50,14 +50,20 @@
 const QString PROXY_USERNAME;
 const QString PROXY_PASSWD;
 
-MainWindow::MainWindow(int aEventId, QWidget *aParent)
-    : QMainWindow(aParent)
-    , conferenceModel(new ConferenceModel(this))
-    , mXmlParser(new ScheduleXmlParser(this))
-    , mNetworkAccessManager(new QNetworkAccessManager(this))
-{
+MainWindow::MainWindow(int aEventId, QWidget *aParent): QMainWindow(aParent) {
     setupUi(this);
 
+    // Open database
+    sqlEngine = new SqlEngine(this);
+    searchTabContainer->setSqlEngine(sqlEngine);
+    connect(sqlEngine, SIGNAL(dbError(QString)), this, SLOT(showError(QString)));
+    sqlEngine->open();
+    sqlEngine->createOrUpdateDbSchema();
+
+    conferenceModel = new ConferenceModel(this);
+    mXmlParser = new ScheduleXmlParser(sqlEngine, this);
+    mNetworkAccessManager = new QNetworkAccessManager(this);
+
     saved_title = windowTitle();
 
 #ifdef N810
@@ -287,6 +293,12 @@ void MainWindow::unsetConference()
     setWindowTitle(saved_title);
 }
 
+
+void MainWindow::showError(const QString& message) {
+    error_message(message);
+}
+
+
 void MainWindow::on_settingsAction_triggered()
 {
     SettingsDialog dialog;
@@ -387,16 +399,15 @@ void MainWindow::importFromFile(const QString& filename, int conferenceId)
     importData(file.readAll(), "", conferenceId);
 }
 
-void MainWindow::removeConference(int id)
-{
-    Conference::deleteConference(id);
-    conferenceModel->conferenceRemoved();
 
+void MainWindow::removeConference(int id) {
+    sqlEngine->deleteConference(id);
+    conferenceModel->conferenceRemoved();
     emit conferenceRemoved();
 }
 
-void MainWindow::changeConferenceUrl(int id, const QString& url)
-{
+
+void MainWindow::changeConferenceUrl(int id, const QString& url) {
     Conference::getById(id).setUrl(url);
 }
 

diff --git a/src/gui/mainwindow.h b/src/gui/mainwindow.h
index 42f417a56eaf40cecd96351df6b04aaf539ae18e..a063075323e9e6e0a5399ea16af0c248235e0d5e 100644 (file)
--- a/src/gui/mainwindow.h
+++ b/src/gui/mainwindow.h
@@ -61,6 +61,8 @@ private slots:
 
     void useConference(int id);
     void unsetConference();
+
+    void showError(const QString& message);
 private:
     void fillAndShowConferenceHeader();
     void initTabs();
@@ -68,6 +70,7 @@ private:
     void importData(const QByteArray &aData, const QString& url, int conferenceId);
 
     QString saved_title;
+    SqlEngine* sqlEngine;
     ConferenceModel* conferenceModel;
     ScheduleXmlParser *mXmlParser;
     QNetworkAccessManager *mNetworkAccessManager;

diff --git a/src/gui/searchtabcontainer.cpp b/src/gui/searchtabcontainer.cpp
index 23cbdf6d288776607c82c2eb0415c68fec6ddeac..0800f99e67e05edf91b047f84650a7f91aa5388c 100644 (file)
--- a/src/gui/searchtabcontainer.cpp
+++ b/src/gui/searchtabcontainer.cpp
@@ -22,8 +22,7 @@
 #include "searchhead.h"
 #include <QMessageBox>
 
-SearchTabContainer::SearchTabContainer(QWidget *aParent) : TabContainer( aParent )
-{
+SearchTabContainer::SearchTabContainer(QWidget *aParent): TabContainer(aParent), sqlEngine(0) {
     header = new SearchHead(this);
     header->setObjectName(QString::fromUtf8("header"));
     QSizePolicy sizePolicy(QSizePolicy::MinimumExpanding, QSizePolicy::Expanding);
@@ -58,6 +57,8 @@ void SearchTabContainer::showSearchDialog(bool show) {
 
 
 void SearchTabContainer::searchButtonClicked() {
+    if (!sqlEngine) return;
+
     QHash<QString,QString> columns;
 
     SearchHead *searchHeader = static_cast<SearchHead*>(header);
@@ -78,7 +79,7 @@ void SearchTabContainer::searchButtonClicked() {
     if (confId == -1) return;
     Conference conf = Conference::getById(confId);
 
-    SqlEngine::searchEvent( confId, columns, keyword );
+    sqlEngine->searchEvent( confId, columns, keyword );
 
     int nrofFounds = 0;
     for (QDate d = conf.start(); d <= conf.end(); d = d.addDays(1))

diff --git a/src/gui/searchtabcontainer.h b/src/gui/searchtabcontainer.h
index 6144073602ab0237979b2ef8789745a0a7fce07b..c835f56eac65919386c259e15ddef6b20d8106de 100644 (file)
--- a/src/gui/searchtabcontainer.h
+++ b/src/gui/searchtabcontainer.h
@@ -25,12 +25,16 @@
 
 #include "tabcontainer.h"
 #include "searchhead.h"
+#include "sqlengine.h"
 
 class SearchTabContainer: public TabContainer {
     Q_OBJECT
+private:
+    SqlEngine* sqlEngine;
 public:
     SearchTabContainer(QWidget *aParent);
     virtual ~SearchTabContainer() {}
+    void setSqlEngine(SqlEngine* sqlEngine) {this->sqlEngine = sqlEngine;}
     bool searchDialogIsVisible() const;
     int searchResultCount(const QDate& date) const; ///< returns the number of events found on that specific date
 

diff --git a/src/mvc/conference.cpp b/src/mvc/conference.cpp
index b53255e0a59c3585f69ff2147cabc2b9fb3638e4..77cf596fd91c732913106edbe8764325cba18448 100644 (file)
--- a/src/mvc/conference.cpp
+++ b/src/mvc/conference.cpp
@@ -73,8 +73,4 @@ int Conference::activeConference()
     return -1;
 }
 
-void Conference::deleteConference(int id)
-{
-    SqlEngine::deleteConference(id);
-}
 

diff --git a/src/mvc/conference.h b/src/mvc/conference.h
index bc296dd809f18c5bad3e553eb9e79cc432fbffce..6a68204f85f05a4c77970750f0961dd7601915a0 100644 (file)
--- a/src/mvc/conference.h
+++ b/src/mvc/conference.h
@@ -36,7 +36,6 @@ public:
     static Conference getById(int id);
     static QList<Conference> getAll();
     static int activeConference(); ///< returns -1 if no conference is active
-    static void deleteConference(int id);
 
 public:
     int id() const { return value("id").toInt(); }

diff --git a/src/sql/schedulexmlparser.cpp b/src/sql/schedulexmlparser.cpp
index 2e7ba1d3b2cedacccb42d82025065bc78c42ce1a..6e6d653d02d36d7de2d96c0c315351fa3608d171 100644 (file)
--- a/src/sql/schedulexmlparser.cpp
+++ b/src/sql/schedulexmlparser.cpp
@@ -27,11 +27,10 @@
 
 #include <QDebug>
 
-ScheduleXmlParser::ScheduleXmlParser(QObject *aParent)
-    : QObject(aParent)
-{
+ScheduleXmlParser::ScheduleXmlParser(SqlEngine* sqlEngine, QObject *aParent): QObject(aParent),sqlEngine(sqlEngine) {
 }
 
+
 void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, int conferenceId)
 {
     QDomDocument document;
@@ -45,7 +44,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
 
     QDomElement scheduleElement = document.firstChildElement("schedule");
 
-    SqlEngine::beginTransaction();
+    sqlEngine->beginTransaction();
 
     QString conference_title;
     if (!scheduleElement.isNull())
@@ -66,7 +65,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
             conference["day_change"] = conferenceElement.firstChildElement("day_change").text(); // time
             conference["timeslot_duration"] = conferenceElement.firstChildElement("timeslot_duration").text(); // time
             conference["url"] = url;
-            SqlEngine::addConferenceToDB(conference, conferenceId);
+            sqlEngine->addConferenceToDB(conference, conferenceId);
             conferenceId = conference["id"].toInt();
             conference_title = conference["title"];
         }
@@ -104,7 +103,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
                         room["name"] = roomElement.attribute("name");
                         room["event_id"] = eventElement.attribute("id");
                         room["conference_id"] = QString::number(conferenceId,10);
-                        SqlEngine::addRoomToDB(room);
+                        sqlEngine->addRoomToDB(room);
 
                         // process event's nodes
                         QHash<QString,QString> event;
@@ -122,7 +121,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
                         event["language"] = eventElement.firstChildElement("language").text(); // language eg. "English"
                         event["abstract"] = eventElement.firstChildElement("abstract").text(); // string
                         event["description"] = eventElement.firstChildElement("description").text(); // string
-                        SqlEngine::addEventToDB(event);
+                        sqlEngine->addEventToDB(event);
                         // process persons' nodes
                         QDomElement personsElement = eventElement.firstChildElement("persons");
                         QDomNodeList personList = personsElement.elementsByTagName("person");
@@ -132,7 +131,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
                             person["name"] = personList.at(i).toElement().text();
                             person["event_id"] = eventElement.attribute("id");
                             person["conference_id"] = QString::number(conferenceId, 10);
-                            SqlEngine::addPersonToDB(person);
+                            sqlEngine->addPersonToDB(person);
                         }
                         // process links' nodes
                         QDomElement linksElement = eventElement.firstChildElement("links");
@@ -143,7 +142,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
                             link["url"] = linkList.at(i).toElement().attribute("href");
                             link["event_id"] = eventElement.attribute("id");
                             link["conference_id"] = QString::number(conferenceId, 10);
-                            SqlEngine::addLinkToDB(link);
+                            sqlEngine->addLinkToDB(link);
                         }
                         // emit signal to inform the user about the current status (how many events are parsed so far - expressed in %)
                         int status = currentEvent * 100 / totalEventsCount;
@@ -153,7 +152,7 @@ void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, i
             } // parsing room elements
         } // parsing day elements
     } // schedule element
-    SqlEngine::commitTransaction();
+    sqlEngine->commitTransaction();
     if (!conference_title.isNull()) {
         emit parsingScheduleEnd(conference_title);
     } else {

diff --git a/src/sql/schedulexmlparser.h b/src/sql/schedulexmlparser.h
index 63ce92e588a6f818f2eba17b0c83103fe7a1246f..c23c43e0f9d890d289e47b19aef40f74ac15176f 100644 (file)
--- a/src/sql/schedulexmlparser.h
+++ b/src/sql/schedulexmlparser.h
@@ -21,12 +21,15 @@
 #define SCHEDULEXMLPARSER_H_
 
 #include <QObject>
+#include "sqlengine.h"
 
 class ScheduleXmlParser : public QObject
 {
     Q_OBJECT
+    private:
+        SqlEngine* sqlEngine;
     public:
-        ScheduleXmlParser (QObject *aParent = NULL);
+        ScheduleXmlParser(SqlEngine* sqlEngine, QObject *aParent = NULL);
 
     public slots:
         void parseData(const QByteArray &aData, const QString& url, int conferenceId);

diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp
index c210b84352a4b71e1481260731a8898b6f59ec5d..be9e164b424d16fe15c61d78aedae52f4208f10a 100644 (file)
--- a/src/sql/sqlengine.cpp
+++ b/src/sql/sqlengine.cpp
@@ -35,286 +35,296 @@
 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());
 }
+

diff --git a/src/sql/sqlengine.h b/src/sql/sqlengine.h
index 9d9fe9b6bf325903ce1b59f3a57a738cfc56ae37..620b8b8d094ddb8bc73796b03a8edad275c715ec 100644 (file)
--- a/src/sql/sqlengine.h
+++ b/src/sql/sqlengine.h
@@ -22,33 +22,58 @@
 
 #include <QObject>
 #include <QHash>
+#include <QSqlDatabase>
 
-class QSqlDatabase;
 
-class SqlEngine : public QObject
-{
+class SqlEngine : public QObject {
     Q_OBJECT
     public:
+        QString dbFilename; ///< database filename including path
+        QSqlDatabase db; ///< this may be private one day...
+
         SqlEngine(QObject *aParent = NULL);
         ~SqlEngine();
-        static void initialize();
-        // if a conferenceId != 0 is given, the confernece is updated instead of inserted.
-        static void addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId);
-        static void addEventToDB(QHash<QString,QString> &aEvent);
-        static void addPersonToDB(QHash<QString,QString> &aPerson);
-        static void addLinkToDB(QHash<QString,QString> &aLink);
-        static void addRoomToDB(QHash<QString,QString> &aRoom);
-        static void deleteConference(int id);
-
-        static bool beginTransaction();
-        static bool commitTransaction();
-
-        // search Events for ....
-        static int searchEvent(int conferenceId, const QHash<QString,QString> &columns, const QString &keyword);
+
+        // Open/Close
+        void open(); ///< emits a database error if failed.
+        bool isOpen() const {return db.isOpen();}
+        void close() {db.close();}
+
+        // Schema version
+        /// returns the "user_version" of the database schema
+        /// we return -1 for an empty database
+        /// the database has to be open
+        /// returns -2 if an error occurs and emits the error message
+        int dbSchemaVersion();
+        /// called by createOrUpdateDbSchema. Do not use directly. true for success.
+        bool updateDbSchemaVersion000To001();
+        /// called by createOrUpdateDbSchma. Do not use directly. true for success.
+        bool createCurrentDbSchema();
+        /// creates the current database schema if an empty database is found,
+        /// otherwise updates the schema if an old one is found. true for success.
+        bool createOrUpdateDbSchema();
+
+        // if a conferneceId != 0 is given, the confernce is updated instead of inserted.
+        void addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId);
+        void addEventToDB(QHash<QString,QString> &aEvent);
+        void addPersonToDB(QHash<QString,QString> &aPerson);
+        void addLinkToDB(QHash<QString,QString> &aLink);
+        void addRoomToDB(QHash<QString,QString> &aRoom);
+        bool deleteConference(int id);
+
+        bool beginTransaction();
+        bool commitTransaction();
+
+        /// search Events for .... returns true if success
+        bool searchEvent(int conferenceId, const QHash<QString,QString> &columns, const QString &keyword);
     private:
         static QString login(const QString &aDatabaseType, const QString &aDatabaseName);
-        static bool execQuery(QSqlDatabase &aDatabase, const QString &aQuery);
-        static bool execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params);
+        /// emits a possible error message as signal. Does nothing if there was not last error
+        void emitSqlQueryError(const QSqlQuery& query);
+
+    signals:
+        /// emitted when a database errors occur
+        void dbError(const QString& message);
 };
 
 #endif /* SQLENGINE_H */