From 61346c970d0d600ce7dc5fad4ea8e4a5e629a048 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Tue, 4 Sep 2012 21:57:13 +0000 Subject: [PATCH] Restructured the SqlEngine. Not yet finished (see "TODO" in the code). --- src/app/main.cpp | 5 +- src/db.qrc | 2 +- src/{create_tables.sql => dbschema000.sql} | 0 src/dbschema001.sql | 76 +++ src/gui/mainwindow.cpp | 35 +- src/gui/mainwindow.h | 3 + src/gui/searchtabcontainer.cpp | 7 +- src/gui/searchtabcontainer.h | 4 + src/mvc/conference.cpp | 4 - src/mvc/conference.h | 1 - src/sql/schedulexmlparser.cpp | 19 +- src/sql/schedulexmlparser.h | 5 +- src/sql/sqlengine.cpp | 575 ++++++++++----------- src/sql/sqlengine.h | 63 ++- 14 files changed, 455 insertions(+), 344 deletions(-) rename src/{create_tables.sql => dbschema000.sql} (100%) create mode 100644 src/dbschema001.sql diff --git a/src/app/main.cpp b/src/app/main.cpp index 6f8a6ba..29f36fb 100644 --- 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/db.qrc b/src/db.qrc index 03d8d30..d14bd42 100644 --- a/src/db.qrc +++ b/src/db.qrc @@ -1,6 +1,6 @@ - create_tables.sql + dbschema001.sql diff --git a/src/create_tables.sql b/src/dbschema000.sql similarity index 100% rename from src/create_tables.sql rename to src/dbschema000.sql diff --git a/src/dbschema001.sql b/src/dbschema001.sql new file mode 100644 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 b626224..9a17036 100644 --- 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 42f417a..a063075 100644 --- 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 23cbdf6..0800f99 100644 --- a/src/gui/searchtabcontainer.cpp +++ b/src/gui/searchtabcontainer.cpp @@ -22,8 +22,7 @@ #include "searchhead.h" #include -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 columns; SearchHead *searchHeader = static_cast(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 6144073..c835f56 100644 --- 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 b53255e..77cf596 100644 --- 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 bc296dd..6a68204 100644 --- a/src/mvc/conference.h +++ b/src/mvc/conference.h @@ -36,7 +36,6 @@ public: static Conference getById(int id); static QList 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 2e7ba1d..6e6d653 100644 --- a/src/sql/schedulexmlparser.cpp +++ b/src/sql/schedulexmlparser.cpp @@ -27,11 +27,10 @@ #include -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 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 63ce92e..c23c43e 100644 --- a/src/sql/schedulexmlparser.h +++ b/src/sql/schedulexmlparser.h @@ -21,12 +21,15 @@ #define SCHEDULEXMLPARSER_H_ #include +#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 c210b84..be9e164 100644 --- 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 &aConference, int conferenceId) -{ - QSqlDatabase db = QSqlDatabase::database(); - if (db.isValid() && db.isOpen()) +void SqlEngine::addConferenceToDB(QHash &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() << "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() << "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() << "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() << "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 &aEvent) -{ - QSqlDatabase db = QSqlDatabase::database(); - if (db.isValid() && db.isOpen()) +void SqlEngine::addEventToDB(QHash &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 props = QList() - << "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 &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 props = QList() + << "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 &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 &aRoom) -{ - QSqlDatabase db = QSqlDatabase::database(); - if (db.isValid() && db.isOpen()) +void SqlEngine::addRoomToDB(QHash &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 &aLink) -{ - QSqlDatabase db = QSqlDatabase::database(); +void SqlEngine::addLinkToDB(QHash &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 &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 &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 &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 &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 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& 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 9d9fe9b..620b8b8 100644 --- a/src/sql/sqlengine.h +++ b/src/sql/sqlengine.h @@ -22,33 +22,58 @@ #include #include +#include -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 &aConference, int conferenceId); - static void addEventToDB(QHash &aEvent); - static void addPersonToDB(QHash &aPerson); - static void addLinkToDB(QHash &aLink); - static void addRoomToDB(QHash &aRoom); - static void deleteConference(int id); - - static bool beginTransaction(); - static bool commitTransaction(); - - // search Events for .... - static int searchEvent(int conferenceId, const QHash &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 &aConference, int conferenceId); + void addEventToDB(QHash &aEvent); + void addPersonToDB(QHash &aPerson); + void addLinkToDB(QHash &aLink); + void addRoomToDB(QHash &aRoom); + bool deleteConference(int id); + + bool beginTransaction(); + bool commitTransaction(); + + /// search Events for .... returns true if success + bool searchEvent(int conferenceId, const QHash &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& 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 */ -- 2.39.5