/*
* Copyright (C) 2010 Ixonos Plc.
- * Copyright (C) 2011-2017 Philipp Spitzer, gregor herrmann, Stefan Stahl
+ * Copyright (C) 2011-2021 Philipp Spitzer, gregor herrmann, Stefan Stahl
*
* This file is part of ConfClerk.
*
#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");
}
}
+bool SqlEngine::updateDbSchemaVersion001To002() {
+ return applySqlFile(":/dbschema001to002.sql");
+}
+
+
bool SqlEngine::createCurrentDbSchema() {
- return applySqlFile(":/dbschema001.sql");
+ return applySqlFile(":/dbschema002.sql");
}
// db schema version 0
return updateDbSchemaVersion000To001();
case 1:
+ // db schema version 1
+ return updateDbSchemaVersion001To002();
+ case 2:
// current schema
return true;
default:
}
-void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId) {
+QDateTime parseDateIgnoreTime(QString dateStr) {
+ QDateTime dateTime = QDateTime::fromString(dateStr, Qt::DateFormat::ISODate);
+ dateTime.setOffsetFromUtc(0);
+ return dateTime;
+}
+
+
+void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId, bool omit_display_time_shift) {
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) "
+ "day_change,timeslot_duration,utc_offset,display_time_shift,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
+ ":day_change,:timeslot_duration,:utc_offset,:display_time_shift,:active)");
+ } else { // update conference
+ QString update = "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, utc_offset=:utc_offset";
+ if (!omit_display_time_shift) update += ", display_time_shift=:display_time_shift";
+ update += ", active=:active WHERE id=:id";
+ query.prepare(update);
}
- 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", parseDateIgnoreTime(aConference["start"]).toTime_t());
+ query.bindValue(":end", parseDateIgnoreTime(aConference["end"]).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)));
+ QVariant utc_offset;
+ if (!aConference.value("utc_offset").isEmpty()) utc_offset = aConference["utc_offset"].toInt();
+ query.bindValue(":utc_offset", utc_offset);
+ if (!omit_display_time_shift) {
+ QVariant display_time_shift;
+ if (!aConference.value("display_time_shift").isEmpty()) display_time_shift = aConference["display_time_shift"].toInt();
+ query.bindValue(":display_time_shift", display_time_shift);
+ }
+ 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();
}
}
}
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);
}
-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
}
+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");
}
+bool SqlEngine::deleteStaleEvents(int conferenceId, QSet<QString> eventIdsToKeep) {
+ QSqlQuery query(db);
+
+ // get all event IDs from conference
+ query.prepare("SELECT id FROM event WHERE xid_conference = ?");
+ query.bindValue(0, conferenceId);
+ bool success = query.exec();
+ emitSqlQueryError(query);
+ if (!success) return false;
+ QSet<QString> existingEventIds;
+ while (query.next()) existingEventIds.insert(query.value(0).toString());
+
+ // determine events that are not existing anymore
+ QSet<QString> eventIdsToRemove = existingEventIds.subtract(eventIdsToKeep);
+
+ // delete events including entries from referencing tables
+ QList<QString> tables = {"link", "event_room", "event_person"};
+ for(const QString& eventId: eventIdsToRemove) {
+ for(const QString& table: tables) {
+ query.prepare(QString("DELETE FROM %1 WHERE xid_conference = ? AND xid_event = ?").arg(table));
+ query.bindValue(0, conferenceId);
+ query.bindValue(1, eventId);
+ success &= query.exec();
+ emitSqlQueryError(query);
+ }
+ query.prepare("DELETE FROM event WHERE xid_conference = ? AND id = ?");
+ query.bindValue(0, conferenceId);
+ query.bindValue(1, eventId);
+ success &= query.exec();
+ emitSqlQueryError(query);
+ }
+
+ return success;
+}
+
+
void SqlEngine::emitSqlQueryError(const QSqlQuery &query) {
QSqlError error = query.lastError();
if (error.type() == QSqlError::NoError) return;
emit dbError(error.text());
}
-
ToastFreeware / toast / confclerk.git / blobdiff