source: src/sql/sqlengine.cpp @ 508de33

qt5
Last change on this file since 508de33 was cec47c6, checked in by kirilma <kirilma@…>, 12 years ago

store path to conference map in database

path stored as additional field in conference table
if it's null or empty, "Show map" button is not shown
if existing database does not have the field, it will be automatically added

  • Property mode set to 100644
File size: 14.9 KB
Line 
1/*
2 * Copyright (C) 2010 Ixonos Plc.
3 *
4 * This file is part of fosdem-schedule.
5 *
6 * fosdem-schedule is free software: you can redistribute it and/or modify it
7 * under the terms of the GNU General Public License as published by the Free
8 * Software Foundation, either version 2 of the License, or (at your option)
9 * any later version.
10 *
11 * fosdem-schedule is distributed in the hope that it will be useful, but
12 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
13 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
14 * more details.
15 *
16 * You should have received a copy of the GNU General Public License along with
17 * fosdem-schedule.  If not, see <http://www.gnu.org/licenses/>.
18 */
19
20#include <QSqlError>
21#include <QSqlQuery>
22#include <QSqlRecord>
23#include <QVariant>
24#include <QDateTime>
25
26#include <QDir>
27#include "sqlengine.h"
28#include <track.h>
29#include <conference.h>
30
31#include <QDebug>
32
33const QString DATE_FORMAT ("yyyy-MM-dd");
34const QString TIME_FORMAT ("hh:mm");
35
36SqlEngine::SqlEngine(QObject *aParent)
37    : QObject(aParent)
38{
39}
40
41SqlEngine::~SqlEngine()
42{
43}
44
45QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseName)
46{
47    QSqlDatabase database = QSqlDatabase::addDatabase(aDatabaseType);
48    database.setDatabaseName(aDatabaseName);
49
50    bool result = false;
51    if(!QFile::exists(aDatabaseName)) // the DB (tables) doesn't exists, and so we have to create one
52    {
53        // copy conference Db from resource, instead of creating
54        // empty tables and then parsing the schedule
55        QFile dbFile(aDatabaseName);
56        QFile(":/fosdem.sqlite").copy(aDatabaseName);
57        dbFile.setPermissions(QFile::ReadOwner | QFile::WriteOwner | QFile::ReadGroup | QFile::WriteGroup);
58        database.open();
59    }
60    else
61    {
62        database.open();
63    }
64
65    checkConferenceMap(database);
66
67    //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
68
69    return result ? QString() : database.lastError().text();
70}
71
72void SqlEngine::initialize()
73{
74    QString databaseName;
75    if(!QDir::home().exists(".fosdem"))
76        QDir::home().mkdir(".fosdem");
77    databaseName = QDir::homePath() + "/.fosdem/" + "fosdem.sqlite";
78    login("QSQLITE",databaseName);
79}
80
81void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference)
82{
83    QSqlDatabase db = QSqlDatabase::database();
84
85    if (db.isValid() && db.isOpen())
86    {
87        int confId = 0;
88        QList<Conference> confsList = Conference::getAll();
89        if(confsList.count())
90        {
91            QListIterator<Conference> i(confsList);
92            while (i.hasNext())
93            {
94                Conference conf = i.next();
95                if( aConference["title"] == conf.title() )
96                {
97                    confId = conf.id();
98                    aConference["id"] = QString::number(confId);
99                    break;
100                }
101            }
102        }
103
104        if(!confId) // conference 'aConference' isn't in the table => insert
105        {
106            QSqlQuery query(db);
107            query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
108                                                    "day_change,timeslot_duration,active) "
109                            " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,:days,"
110                                                    ":day_change,:timeslot_duration,:active)");
111            foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
112                query.bindValue(QString(":") + prop_name, aConference[prop_name]);
113            }
114            query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
115            query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
116            query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
117            query.bindValue(":day_change", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
118            query.bindValue(":active", confsList.count() > 0 ? 0 : 1);
119            query.exec();
120            aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
121        }
122    }
123}
124
125void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
126{
127    //LOG_DEBUG(QString("Adding event '%1' to DB").arg(*aEvent));
128
129    QSqlDatabase db = QSqlDatabase::database();
130
131    if (db.isValid() && db.isOpen())
132    {
133        //insert event track to table and get track id
134        QString name = aEvent["track"];
135        Track track;
136        int trackId;
137        try
138        {
139            track = Track::retrieveByName(name);
140            trackId = track.id();
141            /*qDebug() << QString("DEBUG: Track %1 in DB").arg(name);*/
142        }
143        catch (OrmNoObjectException &e) {
144            track.setName(name);
145            trackId = track.insert();
146            /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/
147        }
148        QDateTime startDateTime;
149        startDateTime.setTimeSpec(Qt::UTC);
150        startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
151        qDebug() << "startDateTime: " << startDateTime.toString();
152
153        bool event_exists = false;
154        {
155            QSqlQuery check_event_query;
156            check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id");
157            check_event_query.bindValue(":xid_conference", aEvent["conference_id"]);
158            check_event_query.bindValue(":id", aEvent["id"]);
159            if (!check_event_query.exec()) {
160                qWarning() << "check event failed, conference id:" << aEvent["xid_conference"]
161                        << "event id:" << aEvent["id"]
162                        << "error:" << check_event_query.lastError()
163                        ;
164                return;
165            }
166            if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) {
167                event_exists = true;
168            }
169        }
170
171        QSqlQuery result;
172        if (event_exists) {
173            result.prepare("UPDATE EVENT SET"
174                            " start = :start"
175                            ", duration = :duration"
176                            ", xid_track = :xid_track"
177                            ", type = :type"
178                            ", language = :language"
179                            ", tag = :tag"
180                            ", title = :title"
181                            ", subtitle = :subtitle"
182                            ", abstract = :abstract"
183                            ", description = :description"
184                                " WHERE id = :id AND xid_conference = :xid_conference");
185        } else {
186            result.prepare("INSERT INTO EVENT "
187                            " (xid_conference, id, start, duration, xid_track, type, "
188                                " language, tag, title, subtitle, abstract, description) "
189                            " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, "
190                                ":language, :tag, :title, :subtitle, :abstract, :description)");
191        }
192        result.bindValue(":xid_conference", aEvent["conference_id"]);
193        result.bindValue(":start", QString::number(startDateTime.toTime_t()));
194        result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0)));
195        result.bindValue(":xid_track", trackId);
196        static const QList<QString> props = QList<QString>()
197            << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description";
198        foreach (QString prop_name, props) {
199            result.bindValue(QString(":") + prop_name, aEvent[prop_name]);
200        }
201        if (!result.exec()) {
202            qWarning() << "event insert/update failed:" << result.lastError();
203        }
204    }
205}
206
207void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson)
208{
209    QSqlDatabase db = QSqlDatabase::database();
210
211    //TODO: check if the person doesn't exist before inserting
212    if (db.isValid() && db.isOpen())
213    {
214        QString values = QString("'%1', '%2'").arg(aPerson["id"],aPerson["name"]);
215        QString query = QString("INSERT INTO PERSON (id,name) VALUES (%1)").arg(values);
216        QSqlQuery result (query, db);
217        //LOG_AUTOTEST(query);
218
219        values = QString("'%1', '%2', '%3'").arg(aPerson["conference_id"],aPerson["event_id"],aPerson["id"]);
220        query = QString("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (%1)").arg(values);
221        QSqlQuery resultEventPerson (query, db);
222        //LOG_AUTOTEST(query);
223    }
224}
225
226void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
227{
228    QSqlDatabase db = QSqlDatabase::database();
229
230    if (db.isValid() && db.isOpen())
231    {
232        QString queryExist = QString("SELECT id FROM ROOM WHERE name='%1'").arg(aRoom["name"]);
233        QSqlQuery resultExist(queryExist,db);
234        // now we have to check whether ROOM record with 'name' exists or not,
235        // - if it doesn't exist yet, then we have to add that record to 'ROOM' table
236        //   and assign autoincremented 'id' to aRoom
237        // - if it exists, then we need to get its 'id' and assign it to aRoom
238        int roomId = -1;
239        if(resultExist.next()) // ROOM record with 'name' already exists: we need to get its 'id'
240        {
241            roomId = resultExist.value(0).toInt();
242        }
243        else // ROOM record doesn't exist yet, need to create it
244        {
245            QString values = QString("'%1', '%2'").arg(aRoom["name"],aRoom["picture"]);
246            QString query = QString("INSERT INTO ROOM (name,picture) VALUES (%1)").arg(values);
247            QSqlQuery result (query, db);
248            roomId = result.lastInsertId().toInt(); // 'id' is assigned automatically
249            //LOG_AUTOTEST(query);
250        }
251
252        QString values = QString("'%1', '%2', '%3'").arg(aRoom["conference_id"],aRoom["event_id"],QString::number(roomId));
253        QString query = QString("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (%1)").arg(values);
254        QSqlQuery result (query, db);
255        //LOG_AUTOTEST(query);
256    }
257}
258
259void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
260{
261    QSqlDatabase db = QSqlDatabase::database();
262
263    //TODO: check if the link doesn't exist before inserting
264    if (db.isValid() && db.isOpen())
265    {
266        QString values = QString("'%1', '%2', '%3', '%4'").arg(aLink["event_id"],aLink["conference_id"],aLink["name"],aLink["url"]);
267        QString query = QString("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (%1)").arg(values);
268        QSqlQuery result(query, db);
269        //LOG_AUTOTEST(query);
270    }
271}
272
273int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
274{
275    QSqlDatabase db = QSqlDatabase::database();
276
277    if ( !db.isValid() || !db.isOpen())
278        return -1;
279
280
281    // DROP
282    execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT;");
283    // CREATE
284    execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL );");
285    // INSERT
286    QString query = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
287                "SELECT EVENT.xid_conference, EVENT.id FROM EVENT ");
288    if( aColumns.contains("ROOM") ){
289        query += "INNER JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
290        query += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
291    }
292    if( aColumns.contains("PERSON") ){
293        query += "INNER JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
294        query += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
295    }
296    query += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
297
298    foreach (QString table, aColumns.uniqueKeys()){
299        foreach (QString column, aColumns.values(table)){
300            query += QString("%1.%2 LIKE '\%%3\%' OR ").arg( table, column, aKeyword );
301        }
302    }
303    query.chop( QString(" OR ").length() );
304    query += QString(");");
305
306    execQuery( db, query );
307
308    return 1;
309}
310
311bool SqlEngine::beginTransaction()
312{
313    QSqlDatabase db = QSqlDatabase::database();
314
315    return execQuery(db, "BEGIN IMMEDIATE TRANSACTION");
316}
317
318bool SqlEngine::commitTransaction()
319{
320    QSqlDatabase db = QSqlDatabase::database();
321
322    return execQuery(db, "COMMIT");
323}
324
325void SqlEngine::deleteConference(int id)
326{
327    QSqlDatabase db = QSqlDatabase::database();
328
329    if ( !db.isValid() || !db.isOpen()) {
330        return;
331    }
332
333    beginTransaction();
334
335    QHash<QString, QVariant> params;
336    params["xid_conference"] = id;
337    execQueryWithParameter(db, "DELETE FROM LINK WHERE xid_conference = :xid_conference", params);
338    execQueryWithParameter(db, "DELETE FROM EVENT_ROOM WHERE xid_conference = :xid_conference", params);
339    execQueryWithParameter(db, "DELETE FROM EVENT_PERSON WHERE xid_conference = :xid_conference", params);
340    execQueryWithParameter(db, "DELETE FROM EVENT WHERE xid_conference = :xid_conference", params);
341    execQueryWithParameter(db, "DELETE FROM CONFERENCE WHERE id = :xid_conference", params);
342    execQuery(db, "DELETE FROM ROOM WHERE NOT EXISTS(SELECT * FROM EVENT_ROOM WHERE xid_room = ROOM.id)");
343    execQuery(db, "DELETE FROM PERSON WHERE NOT EXISTS(SELECT * FROM EVENT_PERSON WHERE xid_person = PERSON.id)");
344
345    commitTransaction();
346}
347
348bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
349{
350    //qDebug() << "\nSQL: " << aQuery;
351
352    QSqlQuery sqlQuery(aDatabase);
353    if( !sqlQuery.exec(aQuery) ){
354       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
355       return false;
356    }
357    else{
358       //qDebug() << "SQL OK.\n";
359       return true;
360    }
361}
362
363bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params)
364{
365    qDebug() << "SQL:" << aQuery << "params:" << params;
366
367    QSqlQuery sqlQuery(aDatabase);
368    sqlQuery.prepare(aQuery);
369    foreach (QString param_key, params.keys()) {
370        sqlQuery.bindValue(param_key, params[param_key]);
371    }
372    if( !sqlQuery.exec() ){
373       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
374       return false;
375    }
376    else{
377       //qDebug() << "SQL OK.\n";
378       return true;
379    }
380}
381
382void SqlEngine::checkConferenceMap(QSqlDatabase &aDatabase)
383{
384    QSqlQuery sqlQuery(aDatabase);
385    sqlQuery.prepare("SELECT map FROM conference");
386    if (!sqlQuery.exec()) {
387        qWarning() << "column conference.map is missing; adding";
388        execQuery(aDatabase, "ALTER TABLE conference ADD COLUMN map VARCHAR")
389         and execQuery(aDatabase, "UPDATE conference SET map = ':/maps/campus.png' WHERE title = 'FOSDEM 2010'");
390    }
391}
Note: See TracBrowser for help on using the repository browser.