/*
* Copyright (C) 2010 Ixonos Plc.
- * Copyright (C) 2011-2012 Philipp Spitzer, gregor herrmann, Stefan Stahl
+ * Copyright (C) 2011-2017 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");
}
void SqlEngine::open() {
// we may have to create the directory of the database
QFileInfo dbFilenameInfo(dbFilename);
-
- // First possibility (works with Linux, not tested on Windows:
- // QDir cwd;
- // cwd.mkpath(dbFilenameInfo.absolutePath());
-
- // Second possibility (works on Windows)
- dbFilenameInfo.absoluteDir().mkpath(".");
-
+ QDir cwd;
+ cwd.mkpath(dbFilenameInfo.absolutePath());
// We don't have to handle errors because in worst case, opening the database will fail
// and db.isOpen() returns false.
db = QSqlDatabase::addDatabase("QSQLITE");
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) {
- // 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";
+bool SqlEngine::applySqlFile(const QString sqlFile) {
+ QFile file(sqlFile);
+ 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;
+}
+
+void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId) {
QSqlQuery query(db);
- if (conferenceId <= 0) // insert conference
- {
- query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
+ 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,:days,"
+ " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,"
":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]);
- }
- 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,"
+ } 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" << "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;
+ }
+ 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;
{
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
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.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");
if (error.type() == QSqlError::NoError) return;
emit dbError(error.text());
}
-
ToastFreeware / toast / confclerk.git / blobdiff