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 d5245bec003248ffeef2cde6590196b49a3f0f2d..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,7 +23,12 @@
 #include <QSqlRecord>
 #include <QVariant>
 #include <QDateTime>
+#include "qglobal.h"
+#if QT_VERSION >= 0x050000
 #include <QStandardPaths>
+#else
+#include <QDesktopServices>
+#endif
 
 #include <QDir>
 #include "sqlengine.h"
@@ -32,11 +37,12 @@
 
 #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,40 +146,32 @@ 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();
     }
 }
@@ -179,6 +185,7 @@ void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent) {
     Track track;
     int trackId;
     QString trackName = aEvent["track"];
+    if (trackName.isEmpty()) trackName = tr("No track");
     try
     {
         track = Track::retrieveByName(conferenceId, trackName);
@@ -191,8 +198,6 @@ void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent) {
     }
     QDate startDate = QDate::fromString(aEvent["date"], DATE_FORMAT);
     QTime startTime = QTime::fromString(aEvent["start"], TIME_FORMAT);
-    // consider day_change (note that if day_change is e.g. at 04:00 AM, an event starting at 02:00 AM has the previous date in the XML file)
-    if (startTime < conference.dayChangeTime()) startDate = startDate.addDays(1);
     QDateTime startDateTime;
     startDateTime.setTimeSpec(Qt::UTC);
     startDateTime = QDateTime(startDate, startTime, Qt::UTC);
@@ -329,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
@@ -400,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");
@@ -434,4 +447,3 @@ void SqlEngine::emitSqlQueryError(const QSqlQuery &query) {
     if (error.type() == QSqlError::NoError) return;
     emit dbError(error.text());
 }
-