source: src/sql/sqlengine.cpp @ ca90cb1

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

A header with the proper copyright/lincence statement was added into each source/header file.

  • Property mode set to 100644
File size: 14.6 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 = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT));
156        QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', ? , ? , ? , ? , ? , '%8', '%9'") \
157                         .arg(aEvent["conference_id"]) \
158                         .arg(aEvent["id"]) \
159                         .arg(QString::number(startDateTime.toTime_t())) \
160                         .arg(-QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0))) \
161                         .arg(trackId) \
162                         .arg(aEvent["type"]) \
163                         .arg(aEvent["language"]) \
164                         .arg("0") \
165                         .arg("0");
166
167        QString query =
168            QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, type, language, tag, title, subtitle, abstract, description, favourite, alarm) VALUES (%1)")
169            .arg(values);
170
171        QSqlQuery result;
172        result.prepare(query);
173        result.bindValue(0,aEvent["tag"]);
174        result.bindValue(1,aEvent["title"]);
175        result.bindValue(2,aEvent["subtitle"]);
176        result.bindValue(3,aEvent["abstract"]);
177        result.bindValue(4,aEvent["description"]);
178        result.exec();
179    }
180}
181
182void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson)
183{
184    QSqlDatabase db = QSqlDatabase::database();
185
186    //TODO: check if the person doesn't exist before inserting
187    if (db.isValid() && db.isOpen())
188    {
189        QString values = QString("'%1', '%2'").arg(aPerson["id"],aPerson["name"]);
190        QString query = QString("INSERT INTO PERSON (id,name) VALUES (%1)").arg(values);
191        QSqlQuery result (query, db);
192        //LOG_AUTOTEST(query);
193
194        values = QString("'%1', '%2', '%3'").arg(aPerson["conference_id"],aPerson["event_id"],aPerson["id"]);
195        query = QString("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (%1)").arg(values);
196        QSqlQuery resultEventPerson (query, db);
197        //LOG_AUTOTEST(query);
198    }
199}
200
201void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
202{
203    QSqlDatabase db = QSqlDatabase::database();
204
205    if (db.isValid() && db.isOpen())
206    {
207        QString queryExist = QString("SELECT id FROM ROOM WHERE name='%1'").arg(aRoom["name"]);
208        QSqlQuery resultExist(queryExist,db);
209        // now we have to check whether ROOM record with 'name' exists or not,
210        // - if it doesn't exist yet, then we have to add that record to 'ROOM' table
211        //   and assign autoincremented 'id' to aRoom
212        // - if it exists, then we need to get its 'id' and assign it to aRoom
213        int roomId = -1;
214        if(resultExist.next()) // ROOM record with 'name' already exists: we need to get its 'id'
215        {
216            roomId = resultExist.value(0).toInt();
217        }
218        else // ROOM record doesn't exist yet, need to create it
219        {
220            QString values = QString("'%1', '%2'").arg(aRoom["name"],aRoom["picture"]);
221            QString query = QString("INSERT INTO ROOM (name,picture) VALUES (%1)").arg(values);
222            QSqlQuery result (query, db);
223            roomId = result.lastInsertId().toInt(); // 'id' is assigned automatically
224            //LOG_AUTOTEST(query);
225        }
226
227        QString values = QString("'%1', '%2', '%3'").arg(aRoom["conference_id"],aRoom["event_id"],QString::number(roomId));
228        QString query = QString("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (%1)").arg(values);
229        QSqlQuery result (query, db);
230        //LOG_AUTOTEST(query);
231    }
232}
233
234void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
235{
236    QSqlDatabase db = QSqlDatabase::database();
237
238    //TODO: check if the link doesn't exist before inserting
239    if (db.isValid() && db.isOpen())
240    {
241        QString values = QString("'%1', '%2', '%3', '%4'").arg(aLink["event_id"],aLink["conference_id"],aLink["name"],aLink["url"]);
242        QString query = QString("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (%1)").arg(values);
243        QSqlQuery result(query, db);
244        //LOG_AUTOTEST(query);
245    }
246}
247
248bool SqlEngine::createTables(QSqlDatabase &aDatabase)
249{
250    bool result = aDatabase.open();
251
252    if (aDatabase.isValid() && aDatabase.isOpen())
253    {
254        QSqlQuery query(aDatabase);
255
256        query.exec("CREATE TABLE CONFERENCE ( "
257            "id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, "
258            "title VARCHAR UNIQUE NOT NULL, "
259            "subtitle VARCHAR, "
260            "venue VARCHAR, "
261            "city VARCHAR NOT NULL, "
262            "start INTEGER NOT NULL, "
263            "end INTEGER NOT NULL, "
264            "days INTEGER, "
265            "day_change INTEGER, "
266            "timeslot_duration INTEGER, "
267            "active INTEGER DEFAULT 0);");
268
269        query.exec("CREATE TABLE TRACK ( "
270            "id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL, "
271            "name VARCHAR UNIQUE NOT NULL );");
272
273        query.exec("CREATE TABLE ROOM ( "
274            "id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL, "
275            "name VARCHAR UNIQUE NOT NULL, "
276            "picture VARCHAR NOT NULL);");
277
278        query.exec("CREATE TABLE PERSON ( "
279            "id INTEGER PRIMARY KEY  NOT NULL, "
280            "name VARCHAR UNIQUE NOT NULL);");
281
282        query.exec("CREATE TABLE EVENT ( "
283            "xid_conference INTEGER  NOT NULL, "
284            "id INTEGER NOT NULL, "
285            "start INTEGER NOT NULL, "
286            "duration INTEGER NOT NULL, "
287            "xid_track INTEGER NOT NULL REFERENCES TRACK(id), "
288            "type VARCHAR, "
289            "language VARCHAR, "
290            "tag VARCHAR,title VARCHAR NOT NULL, "
291            "subtitle VARCHAR, "
292            "abstract VARCHAR, "
293            "description VARCHAR, "
294            "favourite INTEGER DEFAULT 0, "
295            "alarm INTEGER DEFAULT 0, "
296            "PRIMARY KEY (xid_conference,id), "
297            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
298            "FOREIGN KEY(xid_track) REFERENCES TRACK(id));");
299
300        query.exec("CREATE TABLE EVENT_PERSON ( "
301            "xid_conference INTEGER NOT NULL, "
302            "xid_event INTEGER NOT NULL, "
303            "xid_person INTEGER NOT NULL, "
304            "UNIQUE ( xid_conference, xid_event, xid_person ) ON CONFLICT IGNORE, "
305            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
306            "FOREIGN KEY(xid_event) REFERENCES EVENT(id), "
307            "FOREIGN KEY(xid_person) REFERENCES PERSON(id));");
308
309        query.exec("CREATE TABLE EVENT_ROOM ( "
310            "xid_conference INTEGER NOT NULL, "
311            "xid_event INTEGER NOT NULL, "
312            "xid_room INTEGER NOT NULL, "
313            "UNIQUE ( xid_conference, xid_event, xid_room ) ON CONFLICT IGNORE, "
314            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
315            "FOREIGN KEY(xid_event) REFERENCES EVENT(id), "
316            "FOREIGN KEY(xid_room) REFERENCES ROOM(id));");
317
318        query.exec("CREATE TABLE LINK ( "
319            "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
320            "xid_conference INTEGER NOT NULL, "
321            "xid_event INTEGER NOT NULL, "
322            "name VARCHAR, "
323            "url VARCHAR NOT NULL, "
324            "UNIQUE ( xid_conference, xid_event, url ) ON CONFLICT IGNORE, "
325            "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
326            "FOREIGN KEY(xid_event) REFERENCES EVENT(id));");
327    }
328    else
329    {
330        //LOG_WARNING("Database is not opened");
331    }
332
333    return result;
334}
335
336int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
337{
338    QSqlDatabase db = QSqlDatabase::database();
339
340    if ( !db.isValid() || !db.isOpen())
341        return -1;
342
343
344    // DROP
345    execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT;");
346    // CREATE
347    execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL );");
348    // INSERT
349    QString query = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
350                "SELECT EVENT.xid_conference, EVENT.id FROM EVENT ");
351    if( aColumns.contains("ROOM") ){
352        query += "INNER JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
353        query += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
354    }
355    if( aColumns.contains("PERSON") ){
356        query += "INNER JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
357        query += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
358    }
359    query += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
360
361    foreach (QString table, aColumns.uniqueKeys()){
362        foreach (QString column, aColumns.values(table)){
363            query += QString("%1.%2 LIKE '\%%3\%' OR ").arg( table, column, aKeyword );
364        }
365    }
366    query.chop( QString(" OR ").length() );
367    query += QString(");");
368
369    execQuery( db, query );
370
371    //TODO: retun number of rows from SEARCH_EVENT
372    return 1;
373}
374
375bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
376{
377    //qDebug() << "\nSQL: " << aQuery;
378
379    QSqlQuery sqlQuery(aDatabase);
380    if( !sqlQuery.exec(aQuery) ){
381       qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
382       return false;
383    }
384    else{
385       //qDebug() << "SQL OK.\n";
386       return true;
387    }
388}
389
Note: See TracBrowser for help on using the repository browser.