Add utc_offset and display_time_shift columns to conference database.

[toast/confclerk.git] / src / sql / sqlengine.cpp

diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp
index 88ebc1f2b4f889ec091c83b8534c5349881a4193..ae4a0164ac1f9d2f94ba10b766fa9f02ad5b7e79 100644 (file)
--- a/src/sql/sqlengine.cpp
+++ b/src/sql/sqlengine.cpp
@@ -1,6 +1,6 @@
 /*
  * Copyright (C) 2010 Ixonos Plc.
- * Copyright (C) 2011-2013 Philipp Spitzer, gregor herrmann, Stefan Stahl
+ * Copyright (C) 2011-2017 Philipp Spitzer, gregor herrmann, Stefan Stahl
  *
  * This file is part of ConfClerk.
  *
@@ -23,20 +23,26 @@
 #include <QSqlRecord>
 #include <QVariant>
 #include <QDateTime>
+#include "qglobal.h"
+#if QT_VERSION >= 0x050000
+#include <QStandardPaths>
+#else
+#include <QDesktopServices>
+#endif
 
 #include <QDir>
-#include <QDesktopServices>
 #include "sqlengine.h"
 #include "track.h"
 #include "conference.h"
 
 #include <QDebug>
 
-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), DATE_FORMAT("yyyy-MM-dd"), TIME_FORMAT("hh:mm") {
+#if QT_VERSION >= 0x050000
+    QDir dbPath(QStandardPaths::writableLocation(QStandardPaths::DataLocation));
+#else
     QDir dbPath(QDesktopServices::storageLocation(QDesktopServices::DataLocation));
+#endif
     dbFilename = dbPath.absoluteFilePath("ConfClerk.sqlite");
 }
 
@@ -85,8 +91,13 @@ bool SqlEngine::updateDbSchemaVersion000To001() {
 }
 
 
+bool SqlEngine::updateDbSchemaVersion001To002() {
+    return applySqlFile(":/dbschema001to002.sql");
+}
+
+
 bool SqlEngine::createCurrentDbSchema() {
-    return applySqlFile(":/dbschema001.sql");
+    return applySqlFile(":/dbschema002.sql");
 }
 
 
@@ -103,6 +114,9 @@ bool SqlEngine::createOrUpdateDbSchema() {
         // db schema version 0
         return updateDbSchemaVersion000To001();
     case 1:
+        // db schema version 1
+        return updateDbSchemaVersion001To002();
+    case 2:
         // current schema
         return true;
     default:
@@ -132,64 +146,61 @@ bool SqlEngine::applySqlFile(const QString sqlFile) {
 
 void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId) {
     QSqlQuery query(db);
-    if (conferenceId <= 0) // insert conference
-    {
+    bool insert = conferenceId <= 0;
+    if (insert) { // insert conference
         query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,"
                                                 "day_change,timeslot_duration,active) "
                         " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,"
                                                 ":day_change,:timeslot_duration,:active)");
-        foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city")) {
-            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.exec();
-        emitSqlQueryError(query);
-        aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
-    }
-    else // update conference
-    {
+    } else { // update conference
         query.prepare("UPDATE CONFERENCE set title=:title, url=:url, subtitle=:subtitle, venue=:venue, city=:city, start=:start, end=:end,"
                                             "day_change=:day_change, timeslot_duration=:timeslot_duration, active=:active "
                       "WHERE id=:id");
-        foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city")) {
-            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);
-        query.exec();
-        emitSqlQueryError(query);
+    }
+    foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city")) {
+        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());
+    QTime dayChange = QTime::fromString(aConference["day_change"].left(TIME_FORMAT.size()), TIME_FORMAT);
+    query.bindValue(":day_change", QTime(0, 0).secsTo(dayChange));
+    query.bindValue(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+    query.bindValue(":active", 1);
+    if (!insert) query.bindValue(":id", conferenceId);
+    query.exec();
+    emitSqlQueryError(query);
+    if (insert) {
+        aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
+    } else {
         aConference["id"] = QVariant(conferenceId).toString();
     }
 }
 
 
 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"];
+    int conferenceId = aEvent["conference_id"].toInt();
+    Conference conference = Conference::getById(conferenceId);
+
+    // insert event track to table and get track id
     Track track;
     int trackId;
+    QString trackName = aEvent["track"];
+    if (trackName.isEmpty()) trackName = tr("No track");
     try
     {
-        track = Track::retrieveByName(conference, name);
+        track = Track::retrieveByName(conferenceId, trackName);
         trackId = track.id();
     }
     catch (OrmNoObjectException &e) {
-        track.setConference(conference);
-        track.setName(name);
+        track.setConference(conferenceId);
+        track.setName(trackName);
         trackId = track.insert();
     }
+    QDate startDate = QDate::fromString(aEvent["date"], DATE_FORMAT);
+    QTime startTime = QTime::fromString(aEvent["start"], TIME_FORMAT);
     QDateTime startDateTime;
     startDateTime.setTimeSpec(Qt::UTC);
-    startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
+    startDateTime = QDateTime(startDate, startTime, Qt::UTC);
 
     bool event_exists = false;
     {
@@ -285,7 +296,7 @@ void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom) {
         query = QSqlQuery(db);
         query.prepare("INSERT INTO ROOM (xid_conference,name) VALUES (:xid_conference, :name)");
         query.bindValue(":xid_conference", aRoom["conference_id"]);
-        query.bindValue(":xid_name", aRoom["name"]);
+        query.bindValue(":name", aRoom["name"]);
         query.exec();
         emitSqlQueryError(query);
         aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
@@ -323,7 +334,7 @@ void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink) {
 }
 
 
-bool SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword) {
+bool SqlEngine::searchEvent(int aConferenceId, const QMultiHash<QString,QString> &aColumns, const QString &aKeyword) {
     if (aColumns.empty()) return false;
 
     // DROP
@@ -394,6 +405,14 @@ bool SqlEngine::commitTransaction() {
 }
 
 
+bool SqlEngine::rollbackTransaction() {
+    QSqlQuery query(db);
+    bool success = query.exec("ROLLBACK");
+    emitSqlQueryError(query);
+    return success;
+}
+
+
 bool SqlEngine::deleteConference(int id) {
     QSqlQuery query(db);
     bool success = query.exec("BEGIN IMMEDIATE TRANSACTION");
@@ -428,4 +447,3 @@ void SqlEngine::emitSqlQueryError(const QSqlQuery &query) {
     if (error.type() == QSqlError::NoError) return;
     emit dbError(error.text());
 }
-