X-Git-Url: https://git.toastfreeware.priv.at/debian/fosdem-schedule.git/blobdiff_plain/d24759e40202681c17ff790f6b7b8c3eb20bc277..refs/heads/upstream:/src/sql/sqlengine.cpp?ds=sidebyside diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp index 90cb989..c87ce2b 100644 --- a/src/sql/sqlengine.cpp +++ b/src/sql/sqlengine.cpp @@ -50,15 +50,6 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN bool result = false; if(!QFile::exists(aDatabaseName)) // the DB (tables) doesn't exists, and so we have to create one { - /* - // creating empty DB + tables - // ??? what is the best way of creating new empty DB ??? - // we can either: - // - create new DB + tables by issuing corresponding queries (used solution) - // - create new DB from resource, which contains empty DB with tables - result = createTables(database); - */ - // copy conference Db from resource, instead of creating // empty tables and then parsing the schedule QFile dbFile(aDatabaseName); @@ -71,6 +62,8 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN database.open(); } + checkConferenceMap(database); + //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName)); return result ? QString() : database.lastError().text(); @@ -110,21 +103,21 @@ void SqlEngine::addConferenceToDB(QHash &aConference) if(!confId) // conference 'aConference' isn't in the table => insert { - QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9'") \ - .arg(aConference["title"]) \ - .arg(aConference["subtitle"]) \ - .arg(aConference["venue"]) \ - .arg(aConference["city"]) \ - .arg(QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \ - .arg(QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \ - .arg(aConference["days"]) \ - .arg(-QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0))) \ - .arg(-QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0))); - values.append(QString(", '%1'").arg(confsList.count()>0?"0":"1")); - - QString query = QString("INSERT INTO CONFERENCE (title,subtitle,venue,city,start,end,days,day_change,timeslot_duration,active) VALUES (%1)").arg(values); - QSqlQuery result (query, db); - aConference["id"] = result.lastInsertId().toString(); // 'id' is assigned automatically + QSqlQuery query(db); + 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() << "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(":day_change", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0))); + query.bindValue(":active", confsList.count() > 0 ? 0 : 1); + query.exec(); + aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically } } } @@ -156,30 +149,58 @@ void SqlEngine::addEventToDB(QHash &aEvent) startDateTime.setTimeSpec(Qt::UTC); startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC); qDebug() << "startDateTime: " << startDateTime.toString(); - QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', ? , ? , ? , ? , ? , '%8', '%9'") \ - .arg(aEvent["conference_id"]) \ - .arg(aEvent["id"]) \ - .arg(QString::number(startDateTime.toTime_t())) \ - .arg(-QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0))) \ - .arg(trackId) \ - .arg(aEvent["type"]) \ - .arg(aEvent["language"]) \ - .arg("0") \ - .arg("0"); - - QString query = - QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, type, language, tag, title, subtitle, abstract, description, favourite, alarm) VALUES (%1)") - .arg(values); - - qDebug() << query; + + bool event_exists = false; + { + QSqlQuery check_event_query; + check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id"); + check_event_query.bindValue(":xid_conference", aEvent["conference_id"]); + check_event_query.bindValue(":id", aEvent["id"]); + if (!check_event_query.exec()) { + qWarning() << "check event failed, conference id:" << aEvent["xid_conference"] + << "event id:" << aEvent["id"] + << "error:" << check_event_query.lastError() + ; + return; + } + if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) { + event_exists = true; + } + } + QSqlQuery result; - result.prepare(query); - result.bindValue(0,aEvent["tag"]); - result.bindValue(1,aEvent["title"]); - result.bindValue(2,aEvent["subtitle"]); - result.bindValue(3,aEvent["abstract"]); - result.bindValue(4,aEvent["description"]); - result.exec(); + if (event_exists) { + result.prepare("UPDATE EVENT SET" + " start = :start" + ", duration = :duration" + ", xid_track = :xid_track" + ", type = :type" + ", language = :language" + ", tag = :tag" + ", title = :title" + ", subtitle = :subtitle" + ", abstract = :abstract" + ", description = :description" + " WHERE id = :id AND xid_conference = :xid_conference"); + } else { + result.prepare("INSERT INTO EVENT " + " (xid_conference, id, start, duration, xid_track, type, " + " language, tag, title, subtitle, abstract, description) " + " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, " + ":language, :tag, :title, :subtitle, :abstract, :description)"); + } + result.bindValue(":xid_conference", aEvent["conference_id"]); + result.bindValue(":start", QString::number(startDateTime.toTime_t())); + result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0))); + result.bindValue(":xid_track", trackId); + static const QList props = QList() + << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description"; + foreach (QString prop_name, props) { + result.bindValue(QString(":") + prop_name, aEvent[prop_name]); + } + if (!result.exec()) { + qWarning() << "event insert/update failed:" << result.lastError(); + } } } @@ -249,94 +270,6 @@ void SqlEngine::addLinkToDB(QHash &aLink) } } -bool SqlEngine::createTables(QSqlDatabase &aDatabase) -{ - bool result = aDatabase.open(); - - if (aDatabase.isValid() && aDatabase.isOpen()) - { - QSqlQuery query(aDatabase); - - query.exec("CREATE TABLE CONFERENCE ( " - "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " - "title VARCHAR UNIQUE NOT NULL, " - "subtitle VARCHAR, " - "venue VARCHAR, " - "city VARCHAR NOT NULL, " - "start INTEGER NOT NULL, " - "end INTEGER NOT NULL, " - "days INTEGER, " - "day_change INTEGER, " - "timeslot_duration INTEGER, " - "active INTEGER DEFAULT 0);"); - - query.exec("CREATE TABLE TRACK ( " - "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " - "name VARCHAR UNIQUE NOT NULL );"); - - query.exec("CREATE TABLE ROOM ( " - "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " - "name VARCHAR UNIQUE NOT NULL, " - "picture VARCHAR NOT NULL);"); - - query.exec("CREATE TABLE PERSON ( " - "id INTEGER PRIMARY KEY NOT NULL, " - "name VARCHAR UNIQUE NOT NULL);"); - - query.exec("CREATE TABLE EVENT ( " - "xid_conference INTEGER NOT NULL, " - "id INTEGER NOT NULL, " - "start INTEGER NOT NULL, " - "duration INTEGER NOT NULL, " - "xid_track INTEGER NOT NULL REFERENCES TRACK(id), " - "type VARCHAR, " - "language VARCHAR, " - "tag VARCHAR,title VARCHAR NOT NULL, " - "subtitle VARCHAR, " - "abstract VARCHAR, " - "description VARCHAR, " - "favourite INTEGER DEFAULT 0, " - "alarm INTEGER DEFAULT 0, " - "PRIMARY KEY (xid_conference,id) ON CONFLICT REPLACE, " - "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), " - "FOREIGN KEY(xid_track) REFERENCES TRACK(id));"); - - query.exec("CREATE TABLE EVENT_PERSON ( " - "xid_conference INTEGER NOT NULL, " - "xid_event INTEGER NOT NULL, " - "xid_person INTEGER NOT NULL, " - "UNIQUE ( xid_conference, xid_event, xid_person ) ON CONFLICT REPLACE, " - "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), " - "FOREIGN KEY(xid_event) REFERENCES EVENT(id), " - "FOREIGN KEY(xid_person) REFERENCES PERSON(id));"); - - query.exec("CREATE TABLE EVENT_ROOM ( " - "xid_conference INTEGER NOT NULL, " - "xid_event INTEGER NOT NULL, " - "xid_room INTEGER NOT NULL, " - "UNIQUE ( xid_conference, xid_event, xid_room ) ON CONFLICT REPLACE, " - "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), " - "FOREIGN KEY(xid_event) REFERENCES EVENT(id), " - "FOREIGN KEY(xid_room) REFERENCES ROOM(id));"); - - query.exec("CREATE TABLE LINK ( " - "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " - "xid_conference INTEGER NOT NULL, " - "xid_event INTEGER NOT NULL, " - "name VARCHAR, " - "url VARCHAR NOT NULL, " - "UNIQUE ( xid_conference, xid_event, url ) ON CONFLICT REPLACE, " - "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), " - "FOREIGN KEY(xid_event) REFERENCES EVENT(id));"); - } - else - { - //LOG_WARNING("Database is not opened"); - } - - return result; -} - int SqlEngine::searchEvent(int aConferenceId, const QHash &aColumns, const QString &aKeyword) { QSqlDatabase db = QSqlDatabase::database(); @@ -375,6 +308,43 @@ int SqlEngine::searchEvent(int aConferenceId, const QHash &aCol return 1; } +bool SqlEngine::beginTransaction() +{ + QSqlDatabase db = QSqlDatabase::database(); + + return execQuery(db, "BEGIN IMMEDIATE TRANSACTION"); +} + +bool SqlEngine::commitTransaction() +{ + QSqlDatabase db = QSqlDatabase::database(); + + return execQuery(db, "COMMIT"); +} + +void SqlEngine::deleteConference(int id) +{ + QSqlDatabase db = QSqlDatabase::database(); + + if ( !db.isValid() || !db.isOpen()) { + return; + } + + beginTransaction(); + + QHash params; + params["xid_conference"] = id; + execQueryWithParameter(db, "DELETE FROM LINK WHERE xid_conference = :xid_conference", params); + execQueryWithParameter(db, "DELETE FROM EVENT_ROOM WHERE xid_conference = :xid_conference", params); + execQueryWithParameter(db, "DELETE FROM EVENT_PERSON WHERE xid_conference = :xid_conference", params); + execQueryWithParameter(db, "DELETE FROM EVENT WHERE xid_conference = :xid_conference", params); + execQueryWithParameter(db, "DELETE FROM CONFERENCE WHERE id = :xid_conference", params); + execQuery(db, "DELETE FROM ROOM WHERE NOT EXISTS(SELECT * FROM EVENT_ROOM WHERE xid_room = ROOM.id)"); + execQuery(db, "DELETE FROM PERSON WHERE NOT EXISTS(SELECT * FROM EVENT_PERSON WHERE xid_person = PERSON.id)"); + + commitTransaction(); +} + bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery) { //qDebug() << "\nSQL: " << aQuery; @@ -390,3 +360,32 @@ bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery) } } +bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash& params) +{ + qDebug() << "SQL:" << aQuery << "params:" << params; + + QSqlQuery sqlQuery(aDatabase); + sqlQuery.prepare(aQuery); + foreach (QString param_key, params.keys()) { + sqlQuery.bindValue(param_key, params[param_key]); + } + if( !sqlQuery.exec() ){ + qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text(); + return false; + } + else{ + //qDebug() << "SQL OK.\n"; + return true; + } +} + +void SqlEngine::checkConferenceMap(QSqlDatabase &aDatabase) +{ + QSqlQuery sqlQuery(aDatabase); + sqlQuery.prepare("SELECT map FROM conference"); + if (!sqlQuery.exec()) { + qWarning() << "column conference.map is missing; adding"; + execQuery(aDatabase, "ALTER TABLE conference ADD COLUMN map VARCHAR") + and execQuery(aDatabase, "UPDATE conference SET map = ':/maps/campus.png' WHERE title = 'FOSDEM 2010'"); + } +}