/*
* Copyright (C) 2010 Ixonos Plc.
- * Copyright (C) 2011-2012 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::updateDbSchemaVersion000To001() {
- emit dbError("Upgrade 0 -> 1 not implemented yet");
- return false;
+ return applySqlFile(":/dbschema000to001.sql");
+}
+
+
+bool SqlEngine::updateDbSchemaVersion001To002() {
+ return applySqlFile(":/dbschema001to002.sql");
}
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;
+ 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) {
+bool SqlEngine::applySqlFile(const QString sqlFile) {
+ QFile file(sqlFile);
+ file.open(QIODevice::ReadOnly | QIODevice::Text);
+ QString allSqlStatements = file.readAll();
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]);
+ 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;
}
- 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;
+ return true;
+}
+
+
+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);
+ bool insert = conferenceId <= 0;
+ if (insert) { // insert conference
+ query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,"
+ "day_change,timeslot_duration,utc_offset,display_time_shift,active) "
+ " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,"
+ ":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);
+ }
+ 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();
}
}
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;
{
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"]);
+ query.exec();
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
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
//LOG_AUTOTEST(query);
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"]);
+ query.exec();
emitSqlQueryError(query);
// and insert new ones
query = QSqlQuery(db);
query.bindValue(":conference_id", aRoom["conference_id"]);
query.bindValue(":event_id", aRoom["event_id"]);
query.bindValue(":room_id", aRoom["id"]);
+ query.exec();
emitSqlQueryError(query);
}
query.bindValue(":xid_conference", aLink["conference_id"]);
query.bindValue(":name", aLink["name"]);
query.bindValue(":url", aLink["url"]);
+ query.exec();
emitSqlQueryError(query);
}
-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