source: src/sql/sqlengine.cpp @ f548c17

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

use update for events when they are already exists

also use only parameters substitution for these queries

  • Property mode set to 100644
File size: 16.1 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        /*
54        // creating empty DB + tables
55        // ??? what is the best way of creating new empty DB ???
56        // we can either:
57        //  - create new DB + tables by issuing corresponding queries (used solution)
58        //  - create new DB from resource, which contains empty DB with tables
59        result = createTables(database);
60        */
61
62        // copy conference Db from resource, instead of creating
63        // empty tables and then parsing the schedule
64        QFile dbFile(aDatabaseName);
65        QFile(":/fosdem.sqlite").copy(aDatabaseName);
66        dbFile.setPermissions(QFile::ReadOwner | QFile::WriteOwner | QFile::ReadGroup | QFile::WriteGroup);
67        database.open();
68    }
69    else
70    {
71        database.open();
72    }
73
74    //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
75
76    return result ? QString() : database.lastError().text();
77}
78
79void SqlEngine::initialize()
80{
81    QString databaseName;
82    if(!QDir::home().exists(".fosdem"))
83        QDir::home().mkdir(".fosdem");
84    databaseName = QDir::homePath() + "/.fosdem/" + "fosdem.sqlite";
85    login("QSQLITE",databaseName);
86}
87
88void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference)
89{
90    QSqlDatabase db = QSqlDatabase::database();
91
92    if (db.isValid() && db.isOpen())
93    {
94        int confId = 0;
95        QList<Conference> confsList = Conference::getAll();
96        if(confsList.count())
97        {
98            QListIterator<Conference> i(confsList);
99            while (i.hasNext())
100            {
101                Conference conf = i.next();
102                if( aConference["title"] == conf.title() )
103                {
104                    confId = conf.id();
105                    aConference["id"] = QString::number(confId);
106                    break;
107                }
108            }
109        }
110
111        if(!confId) // conference 'aConference' isn't in the table => insert
112        {
113            QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9'") \
114                             .arg(aConference["title"]) \
115                             .arg(aConference["subtitle"]) \
116                             .arg(aConference["venue"]) \
117                             .arg(aConference["city"]) \
118                             .arg(QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \
119                             .arg(QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \
120                             .arg(aConference["days"]) \
121                             .arg(-QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0))) \
122                             .arg(-QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
123                             values.append(QString(", '%1'").arg(confsList.count()>0?"0":"1"));
124
125            QString query = QString("INSERT INTO CONFERENCE (title,subtitle,venue,city,start,end,days,day_change,timeslot_duration,active) VALUES (%1)").arg(values);
126            QSqlQuery result (query, db);
127            aConference["id"] = result.lastInsertId().toString(); // 'id' is assigned automatically
128        }
129    }
130}
131
132void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
133{
134    //LOG_DEBUG(QString("Adding event '%1' to DB").arg(*aEvent));
135
136    QSqlDatabase db = QSqlDatabase::database();
137
138    if (db.isValid() && db.isOpen())
139    {
140        //insert event track to table and get track id
141        QString name = aEvent["track"];
142        Track track;
143        int trackId;
144        try
145        {
146            track = Track::retrieveByName(name);
147            trackId = track.id();
148            /*qDebug() << QString("DEBUG: Track %1 in DB").arg(name);*/
149        }
150        catch (OrmNoObjectException &e) {
151            track.setName(name);
152            trackId = track.insert();
153            /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/
154        }
155        QDateTime startDateTime;
156        startDateTime.setTimeSpec(Qt::UTC);
157        startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
158        qDebug() << "startDateTime: " << startDateTime.toString();
159
160        bool event_exists = false;
161        {
162            QSqlQuery check_event_query;
163            check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id");
164            check_event_query.bindValue(":xid_conference", aEvent["xid_conference"]);
165            check_event_query.bindValue(":id", aEvent["id"]);
166            if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.first()) {
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 "
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        result.exec();
202    }
203}
204
205void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson)
206{
207    QSqlDatabase db = QSqlDatabase::database();
208
209    //TODO: check if the person doesn't exist before inserting
210    if (db.isValid() && db.isOpen())
211    {
212        QString values = QString("'%1', '%2'").arg(aPerson["id"],aPerson["name"]);
213        QString query = QString("INSERT INTO PERSON (id,name) VALUES (%1)").arg(values);
214        QSqlQuery result (query, db);
215        //LOG_AUTOTEST(query);
216
217        values = QString("'%1', '%2', '%3'").arg(aPerson["conference_id"],aPerson["event_id"],aPerson["id"]);
218        query = QString("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (%1)").arg(values);
219        QSqlQuery resultEventPerson (query, db);
220        //LOG_AUTOTEST(query);
221    }
222}
223
224void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
225{
226    QSqlDatabase db = QSqlDatabase::database();
227
228    if (db.isValid() && db.isOpen())
229    {
230        QString queryExist = QString("SELECT id FROM ROOM WHERE name='%1'").arg(aRoom["name"]);
231        QSqlQuery resultExist(queryExist,db);
232        // now we have to check whether ROOM record with 'name' exists or not,
233        // - if it doesn't exist yet, then we have to add that record to 'ROOM' table
234        //   and assign autoincremented 'id' to aRoom
235        // - if it exists, then we need to get its 'id' and assign it to aRoom
236        int roomId = -1;
237        if(resultExist.next()) // ROOM record with 'name' already exists: we need to get its 'id'
238        {
239            roomId = resultExist.value(0).toInt();
240        }
241        else // ROOM record doesn't exist yet, need to create it
242        {
243            QString values = QString("'%1', '%2'").arg(aRoom["name"],aRoom["picture"]);
244            QString query = QString("INSERT INTO ROOM (name,picture) VALUES (%1)").arg(values);
245            QSqlQuery result (query, db);
246            roomId = result.lastInsertId().toInt(); // 'id' is assigned automatically
247            //LOG_AUTOTEST(query);
248        }
249
250        QString values = QString("'%1', '%2', '%3'").arg(aRoom["conference_id"],aRoom["event_id"],QString::number(roomId));
251        QString query = QString("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (%1)").arg(values);
252        QSqlQuery result (query, db);
253        //LOG_AUTOTEST(query);
254    }
255}
256
257void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
258{
259    QSqlDatabase db = QSqlDatabase::database();
260
261    //TODO: check if the link doesn't exist before inserting
262    if (db.isValid() && db.isOpen())
263    {
264        QString values = QString("'%1', '%2', '%3', '%4'").arg(aLink["event_id"],aLink["conference_id"],aLink["name"],aLink["url"]);
265        QString query = QString("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (%1)").arg(values);
266        QSqlQuery result(query, db);
267        //LOG_AUTOTEST(query);
268    }
269}
270
271bool SqlEngine::createTables(QSqlDatabase &aDatabase)
272{
273    bool result = aDatabase.open();
274
275    if (aDatabase.isValid() && aDatabase.isOpen())
276    {
277        QSqlQuery query(aDatabase);
278
279        query.exec("CREATE TABLE CONFERENCE ( "
280            "id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, "
281            "title VARCHAR UNIQUE NOT NULL, "
282            "subtitle VARCHAR, "
283            "venue VARCHAR, "
284            "city VARCHAR NOT NULL, "
285            "start INTEGER NOT NULL, "
286            "end INTEGER NOT NULL, "
287            "days INTEGER, "
288            "day_change INTEGER, "
289            "timeslot_duration INTEGER, "
290            "active INTEGER DEFAULT 0);");
291
292        query.exec("CREATE TABLE TRACK ( "
293            "id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL, "
294            "name VARCHAR UNIQUE NOT NULL );");
295
296        query.exec("CREATE TABLE ROOM ( "
297            "id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, "
298            "name VARCHAR UNIQUE NOT NULL, "
299            "picture VARCHAR NOT NULL);");
300
301        query.exec("CREATE TABLE PERSON ( "
302            "id INTEGER PRIMARY KEY  NOT NULL, "
303            "name VARCHAR UNIQUE NOT NULL);");
304
305        query.exec("CREATE TABLE EVENT ( "
306            "xid_conference INTEGER  NOT NULL, "
307            "id INTEGER NOT NULL, "
308            "start INTEGER NOT NULL, "
309            "duration INTEGER NOT NULL, "
310            "xid_track INTEGER NOT NULL REFERENCES TRACK(id), "
311            "type VARCHAR, "
312            "language VARCHAR, "
313            "tag VARCHAR,title VARCHAR NOT NULL, "
314            "subtitle VARCHAR, "
315            "abstract VARCHAR, "
316            "description VARCHAR, "
317            "favourite INTEGER DEFAULT 0, "
318            "alarm INTEGER DEFAULT 0, "
319            "PRIMARY KEY (xid_conference,id), "
320            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
321            "FOREIGN KEY(xid_track) REFERENCES TRACK(id));");
322
323        query.exec("CREATE TABLE EVENT_PERSON ( "
324            "xid_conference INTEGER NOT NULL, "
325            "xid_event INTEGER NOT NULL, "
326            "xid_person INTEGER NOT NULL, "
327            "UNIQUE ( xid_conference, xid_event, xid_person ) ON CONFLICT REPLACE, "
328            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
329            "FOREIGN KEY(xid_event) REFERENCES EVENT(id), "
330            "FOREIGN KEY(xid_person) REFERENCES PERSON(id));");
331
332        query.exec("CREATE TABLE EVENT_ROOM ( "
333            "xid_conference INTEGER NOT NULL, "
334            "xid_event INTEGER NOT NULL, "
335            "xid_room INTEGER NOT NULL, "
336            "UNIQUE ( xid_conference, xid_event, xid_room ) ON CONFLICT REPLACE, "
337            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
338            "FOREIGN KEY(xid_event) REFERENCES EVENT(id), "
339            "FOREIGN KEY(xid_room) REFERENCES ROOM(id));");
340
341        query.exec("CREATE TABLE LINK ( "
342            "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
343            "xid_conference INTEGER NOT NULL, "
344            "xid_event INTEGER NOT NULL, "
345            "name VARCHAR, "
346            "url VARCHAR NOT NULL, "
347            "UNIQUE ( xid_conference, xid_event, url ) ON CONFLICT REPLACE, "
348            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
349            "FOREIGN KEY(xid_event) REFERENCES EVENT(id));");
350    }
351    else
352    {
353        //LOG_WARNING("Database is not opened");
354    }
355
356    return result;
357}
358
359int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
360{
361    QSqlDatabase db = QSqlDatabase::database();
362
363    if ( !db.isValid() || !db.isOpen())
364        return -1;
365
366
367    // DROP
368    execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT;");
369    // CREATE
370    execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL );");
371    // INSERT
372    QString query = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
373                "SELECT EVENT.xid_conference, EVENT.id FROM EVENT ");
374    if( aColumns.contains("ROOM") ){
375        query += "INNER JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
376        query += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
377    }
378    if( aColumns.contains("PERSON") ){
379        query += "INNER JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
380        query += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
381    }
382    query += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
383
384    foreach (QString table, aColumns.uniqueKeys()){
385        foreach (QString column, aColumns.values(table)){
386            query += QString("%1.%2 LIKE '\%%3\%' OR ").arg( table, column, aKeyword );
387        }
388    }
389    query.chop( QString(" OR ").length() );
390    query += QString(");");
391
392    execQuery( db, query );
393
394    return 1;
395}
396
397bool SqlEngine::beginTransaction()
398{
399    QSqlDatabase db = QSqlDatabase::database();
400
401    return execQuery(db, "BEGIN IMMEDIATE TRANSACTION");
402}
403
404bool SqlEngine::commitTransaction()
405{
406    QSqlDatabase db = QSqlDatabase::database();
407
408    return execQuery(db, "COMMIT");
409}
410
411bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
412{
413    //qDebug() << "\nSQL: " << aQuery;
414
415    QSqlQuery sqlQuery(aDatabase);
416    if( !sqlQuery.exec(aQuery) ){
417       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
418       return false;
419    }
420    else{
421       //qDebug() << "SQL OK.\n";
422       return true;
423    }
424}
425
Note: See TracBrowser for help on using the repository browser.