Changeset 61346c9 in confclerk_git for src


Ignore:
Timestamp:
09/04/12 23:57:13 (10 years ago)
Author:
Philipp Spitzer <philipp@…>
Branches:
master, qt5
Children:
1f12b6b
Parents:
8d6798d
Message:

Restructured the SqlEngine?. Not yet finished (see "TODO" in the code).

Location:
src
Files:
1 added
12 edited
1 moved

Legend:

Unmodified
Added
Removed

  • src/app/main.cpp

    r8d6798d r61346c9  
    4646    QCoreApplication::setApplicationVersion(VERSION);
    4747 
    48     SqlEngine::initialize(); // creates "SQLITE" DB connection
    49 
    5048    QWidget *window;
    51 
    5249    window = new MainWindow;
    5350
    … …  
    7774    if(argc > 1)
    7875    {
    79         EventDialog dialog(atoi(argv[1]),window);
     76        EventDialog dialog(atoi(argv[1]), window);
    8077        dialog.exec();
    8178    }

  • src/db.qrc

    r8d6798d r61346c9  
    11<!DOCTYPE RCC><RCC version="1.0">
    22<qresource>
    3     <file>create_tables.sql</file>
     3    <file>dbschema001.sql</file>
    44</qresource>
    55</RCC>

  • src/gui/mainwindow.cpp

    r8d6798d r61346c9  
    5151const QString PROXY_PASSWD;
    5252
    53 MainWindow::MainWindow(int aEventId, QWidget *aParent)
    54     : QMainWindow(aParent)
    55     , conferenceModel(new ConferenceModel(this))
    56     , mXmlParser(new ScheduleXmlParser(this))
    57     , mNetworkAccessManager(new QNetworkAccessManager(this))
    58 {
     53MainWindow::MainWindow(int aEventId, QWidget *aParent): QMainWindow(aParent) {
    5954    setupUi(this);
     55
     56    // Open database
     57    sqlEngine = new SqlEngine(this);
     58    searchTabContainer->setSqlEngine(sqlEngine);
     59    connect(sqlEngine, SIGNAL(dbError(QString)), this, SLOT(showError(QString)));
     60    sqlEngine->open();
     61    sqlEngine->createOrUpdateDbSchema();
     62
     63    conferenceModel = new ConferenceModel(this);
     64    mXmlParser = new ScheduleXmlParser(sqlEngine, this);
     65    mNetworkAccessManager = new QNetworkAccessManager(this);
    6066
    6167    saved_title = windowTitle();
    … …  
    287293    setWindowTitle(saved_title);
    288294}
     295
     296
     297void MainWindow::showError(const QString& message) {
     298    error_message(message);
     299}
     300
    289301
    290302void MainWindow::on_settingsAction_triggered()
    … …  
    388400}
    389401
    390 void MainWindow::removeConference(int id)
    391 {
    392     Conference::deleteConference(id);
     402
     403void MainWindow::removeConference(int id) {
     404    sqlEngine->deleteConference(id);
    393405    conferenceModel->conferenceRemoved();
    394 
    395406    emit conferenceRemoved();
    396407}
    397408
    398 void MainWindow::changeConferenceUrl(int id, const QString& url)
    399 {
     409
     410void MainWindow::changeConferenceUrl(int id, const QString& url) {
    400411    Conference::getById(id).setUrl(url);
    401412}

  • src/gui/mainwindow.h

    r8d6798d r61346c9  
    6262    void useConference(int id);
    6363    void unsetConference();
     64
     65    void showError(const QString& message);
    6466private:
    6567    void fillAndShowConferenceHeader();
    … …  
    6971
    7072    QString saved_title;
     73    SqlEngine* sqlEngine;
    7174    ConferenceModel* conferenceModel;
    7275    ScheduleXmlParser *mXmlParser;

  • src/gui/searchtabcontainer.cpp

    r8d6798d r61346c9  
    2323#include <QMessageBox>
    2424
    25 SearchTabContainer::SearchTabContainer(QWidget *aParent) : TabContainer( aParent )
    26 {
     25SearchTabContainer::SearchTabContainer(QWidget *aParent): TabContainer(aParent), sqlEngine(0) {
    2726    header = new SearchHead(this);
    2827    header->setObjectName(QString::fromUtf8("header"));
    … …  
    5958
    6059void SearchTabContainer::searchButtonClicked() {
     60    if (!sqlEngine) return;
     61
    6162    QHash<QString,QString> columns;
    6263
    … …  
    7980    Conference conf = Conference::getById(confId);
    8081
    81     SqlEngine::searchEvent( confId, columns, keyword );
     82    sqlEngine->searchEvent( confId, columns, keyword );
    8283
    8384    int nrofFounds = 0;

  • src/gui/searchtabcontainer.h

    r8d6798d r61346c9  
    2626#include "tabcontainer.h"
    2727#include "searchhead.h"
     28#include "sqlengine.h"
    2829
    2930class SearchTabContainer: public TabContainer {
    3031    Q_OBJECT
     32private:
     33    SqlEngine* sqlEngine;
    3134public:
    3235    SearchTabContainer(QWidget *aParent);
    3336    virtual ~SearchTabContainer() {}
     37    void setSqlEngine(SqlEngine* sqlEngine) {this->sqlEngine = sqlEngine;}
    3438    bool searchDialogIsVisible() const;
    3539    int searchResultCount(const QDate& date) const; ///< returns the number of events found on that specific date

  • src/mvc/conference.cpp

    r8d6798d r61346c9  
    7474}
    7575
    76 void Conference::deleteConference(int id)
    77 {
    78     SqlEngine::deleteConference(id);
    79 }
    8076

  • src/mvc/conference.h

    r8d6798d r61346c9  
    3737    static QList<Conference> getAll();
    3838    static int activeConference(); ///< returns -1 if no conference is active
    39     static void deleteConference(int id);
    4039
    4140public:

  • src/sql/schedulexmlparser.cpp

    r8d6798d r61346c9  
    2828#include <QDebug>
    2929
    30 ScheduleXmlParser::ScheduleXmlParser(QObject *aParent)
    31     : QObject(aParent)
    32 {
     30ScheduleXmlParser::ScheduleXmlParser(SqlEngine* sqlEngine, QObject *aParent): QObject(aParent),sqlEngine(sqlEngine) {
    3331}
     32
    3433
    3534void ScheduleXmlParser::parseData(const QByteArray &aData, const QString& url, int conferenceId)
    … …  
    4645    QDomElement scheduleElement = document.firstChildElement("schedule");
    4746
    48     SqlEngine::beginTransaction();
     47    sqlEngine->beginTransaction();
    4948
    5049    QString conference_title;
    … …  
    6766            conference["timeslot_duration"] = conferenceElement.firstChildElement("timeslot_duration").text(); // time
    6867            conference["url"] = url;
    69             SqlEngine::addConferenceToDB(conference, conferenceId);
     68            sqlEngine->addConferenceToDB(conference, conferenceId);
    7069            conferenceId = conference["id"].toInt();
    7170            conference_title = conference["title"];
    … …  
    105104                        room["event_id"] = eventElement.attribute("id");
    106105                        room["conference_id"] = QString::number(conferenceId,10);
    107                         SqlEngine::addRoomToDB(room);
     106                        sqlEngine->addRoomToDB(room);
    108107
    109108                        // process event's nodes
    … …  
    123122                        event["abstract"] = eventElement.firstChildElement("abstract").text(); // string
    124123                        event["description"] = eventElement.firstChildElement("description").text(); // string
    125                         SqlEngine::addEventToDB(event);
     124                        sqlEngine->addEventToDB(event);
    126125                        // process persons' nodes
    127126                        QDomElement personsElement = eventElement.firstChildElement("persons");
    … …  
    133132                            person["event_id"] = eventElement.attribute("id");
    134133                            person["conference_id"] = QString::number(conferenceId, 10);
    135                             SqlEngine::addPersonToDB(person);
     134                            sqlEngine->addPersonToDB(person);
    136135                        }
    137136                        // process links' nodes
    … …  
    144143                            link["event_id"] = eventElement.attribute("id");
    145144                            link["conference_id"] = QString::number(conferenceId, 10);
    146                             SqlEngine::addLinkToDB(link);
     145                            sqlEngine->addLinkToDB(link);
    147146                        }
    148147                        // emit signal to inform the user about the current status (how many events are parsed so far - expressed in %)
    … …  
    154153        } // parsing day elements
    155154    } // schedule element
    156     SqlEngine::commitTransaction();
     155    sqlEngine->commitTransaction();
    157156    if (!conference_title.isNull()) {
    158157        emit parsingScheduleEnd(conference_title);

  • src/sql/schedulexmlparser.h

    r8d6798d r61346c9  
    2222
    2323#include <QObject>
     24#include "sqlengine.h"
    2425
    2526class ScheduleXmlParser : public QObject
    2627{
    2728    Q_OBJECT
     29    private:
     30        SqlEngine* sqlEngine;
    2831    public:
    29         ScheduleXmlParser (QObject *aParent = NULL);
     32        ScheduleXmlParser(SqlEngine* sqlEngine, QObject *aParent = NULL);
    3033
    3134    public slots:

  • src/sql/sqlengine.cpp

    r8d6798d r61346c9  
    3636const QString TIME_FORMAT ("hh:mm");
    3737
    38 SqlEngine::SqlEngine(QObject *aParent)
    39     : QObject(aParent)
    40 {
    41 }
    42 
    43 SqlEngine::~SqlEngine()
    44 {
    45 }
    46 
    47 QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseName)
    48 {
    49     QSqlDatabase database = QSqlDatabase::addDatabase(aDatabaseType);
    50     database.setDatabaseName(aDatabaseName);
    51 
    52     bool result = false;
    53     if(!QFile::exists(aDatabaseName)) // the DB (tables) doesn't exists, and so we have to create one
    54     {
    55         // create Db
    56         if (!database.open()) qDebug() << "Could not open database" << database.lastError();
    57         QFile file(":/create_tables.sql");
    58         file.open(QIODevice::ReadOnly | QIODevice::Text);
    59         QString allSqlStatements = file.readAll();
    60         foreach(QString sql, allSqlStatements.split(";")) {
    61             if (sql.trimmed().length() == 0)     // do not execute empty queries like the last character from create_tables.sql
    62                 continue;
    63             QSqlQuery query(database);
    64             if (!query.exec(sql)) qDebug() << "Could not execute query '" << sql << "' error:" << query.lastError();
    65         }
    66     }
    67     else
    68     {
    69         database.open();
    70     }
    71 
    72     //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
    73 
    74     return result ? QString() : database.lastError().text();
    75 }
    76 
    77 void SqlEngine::initialize()
    78 {
    79     QString databaseName;
    80     QString dataDirName;
    81     dataDirName = QDesktopServices::storageLocation(QDesktopServices::DataLocation);
    82     QDir dataDir = QDir(dataDirName).absolutePath();
    83     if(!dataDir.exists())
    84         dataDir.mkpath(dataDirName);
    85     databaseName = dataDirName + "/ConfClerk.sqlite";
    86     login("QSQLITE",databaseName);
    87 }
    88 
    89 void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId)
    90 {
    91     QSqlDatabase db = QSqlDatabase::database();
    92 
    93     if (db.isValid() && db.isOpen())
    94     {
    95         // HACK
    96         // When city is empty, assign a dummy value. We probably want to find a way to change the database scheme ...
    97         // cf. #32
    98         if (aConference["city"].isEmpty()) aConference["city"] = "n/a";
    99 
    100         QSqlQuery query(db);
    101         if (conferenceId <= 0) // insert conference
    102         {
    103             query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
    104                                                     "day_change,timeslot_duration,active) "
    105                             " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,:days,"
    106                                                     ":day_change,:timeslot_duration,:active)");
    107             foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
    108                 query.bindValue(QString(":") + prop_name, aConference[prop_name]);
    109             }
    110             query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
    111             query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
    112             query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
    113             query.bindValue(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
    114             query.bindValue(":active", 1);
    115             if (!query.exec()) qDebug() << "Could not execute query to insert a conference:" << query.lastError();
    116             aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
    117         }
    118         else // update conference
    119         {
    120             query.prepare("UPDATE CONFERENCE set title=:title, url=:url, subtitle=:subtitle, venue=:venue, city=:city, start=:start, end=:end, days=:days,"
    121                                                 "day_change=:day_change, timeslot_duration=:timeslot_duration, active=:active "
    122                           "WHERE id=:id");
    123             foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
    124                 query.bindValue(QString(":") + prop_name, aConference[prop_name]);
    125             }
    126             query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
    127             query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
    128             query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
    129             query.bindValue(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
    130             query.bindValue(":active", 1);
    131             query.bindValue(":id", conferenceId);
    132             if (!query.exec()) qDebug() << "Could not execute query to update a conference:" << query.lastError();
    133             aConference["id"] = conferenceId;
    134         }
    135     }
    136 }
    137 
    138 void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent)
    139 {
    140     QSqlDatabase db = QSqlDatabase::database();
    141 
    142     if (db.isValid() && db.isOpen())
    143     {
    144         //insert event track to table and get track id
    145         int conference = aEvent["conference_id"].toInt();
    146         QString name = aEvent["track"];
    147         Track track;
    148         int trackId;
    149         try
    150         {
    151             track = Track::retrieveByName(conference, name);
    152             trackId = track.id();
    153         }
    154         catch (OrmNoObjectException &e) {
    155             track.setConference(conference);
    156             track.setName(name);
    157             trackId = track.insert();
    158         }
    159         QDateTime startDateTime;
    160         startDateTime.setTimeSpec(Qt::UTC);
    161         startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
    162 
    163         bool event_exists = false;
    164         {
    165             QSqlQuery check_event_query;
    166             check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id");
    167             check_event_query.bindValue(":xid_conference", aEvent["conference_id"]);
    168             check_event_query.bindValue(":id", aEvent["id"]);
    169             if (!check_event_query.exec()) {
    170                 qWarning() << "check event failed, conference id:" << aEvent["xid_conference"]
    171                         << "event id:" << aEvent["id"]
    172                         << "error:" << check_event_query.lastError()
    173                         ;
    174                 return;
    175             }
    176             if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) {
    177                 event_exists = true;
    178             }
    179         }
    180 
    181         QSqlQuery result;
    182         if (event_exists) {
    183             result.prepare("UPDATE EVENT SET"
    184                             " start = :start"
    185                             ", duration = :duration"
    186                             ", xid_track = :xid_track"
    187                             ", type = :type"
    188                             ", language = :language"
    189                             ", tag = :tag"
    190                             ", title = :title"
    191                             ", subtitle = :subtitle"
    192                             ", abstract = :abstract"
    193                             ", description = :description"
    194                                 " WHERE id = :id AND xid_conference = :xid_conference");
    195         } else {
    196             result.prepare("INSERT INTO EVENT "
    197                             " (xid_conference, id, start, duration, xid_track, type, "
    198                                 " language, tag, title, subtitle, abstract, description) "
    199                             " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, "
    200                                 ":language, :tag, :title, :subtitle, :abstract, :description)");
    201         }
    202         result.bindValue(":xid_conference", aEvent["conference_id"]);
    203         result.bindValue(":start", QString::number(startDateTime.toTime_t()));
    204         result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0)));
    205         result.bindValue(":xid_track", trackId);
    206         static const QList<QString> props = QList<QString>()
    207             << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description";
    208         foreach (QString prop_name, props) {
    209             result.bindValue(QString(":") + prop_name, aEvent[prop_name]);
    210         }
    211         if (!result.exec()) {
    212             qWarning() << "event insert/update failed:" << result.lastError();
    213         }
    214     }
    215 }
    216 
    217 void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson)
    218 {
    219     QSqlDatabase db = QSqlDatabase::database();
    220 
    221     if (db.isValid() && db.isOpen())
    222     {
    223         QSqlQuery query(db);
    224         query.prepare("INSERT INTO PERSON (xid_conference,id,name) VALUES (:xid_conference, :id, :name)");
    225         query.bindValue(":xid_conference", aPerson["conference_id"]);
    226         query.bindValue(":id", aPerson["id"]);
    227         query.bindValue(":name", aPerson["name"]);
    228         query.exec(); // some queries fail due to the unique key constraint
    229         // if (!query.exec()) qDebug() << "SQL query 'insert into person' failed: " << query.lastError();
    230 
     38SqlEngine::SqlEngine(QObject *aParent): QObject(aParent) {
     39    QDir dbPath(QDesktopServices::storageLocation(QDesktopServices::DataLocation));
     40    dbFilename = dbPath.absoluteFilePath("ConfClerk.sqlite");
     41}
     42
     43
     44SqlEngine::~SqlEngine() {
     45}
     46
     47
     48void SqlEngine::open() {
     49    QFileInfo dbFilenameInfo(dbFilename);
     50    dbFilenameInfo.absoluteDir().mkpath(""); // TODO ...
     51    db = QSqlDatabase::addDatabase("QSQLITE");
     52    db.setDatabaseName(dbFilename);
     53    db.open();
     54}
     55
     56
     57int SqlEngine::dbSchemaVersion() {
     58    QSqlQuery query(db);
     59    if (!query.exec("PRAGMA user_version")) {
     60        emitSqlQueryError(query);
     61        return -2;
     62    }
     63    query.first();
     64    int version = query.value(0).toInt();
     65    if (version == 0) {
     66        // check whether the tables are existing
     67        if (!query.exec("select count(*) from sqlite_master where name='CONFERENCE'")) {
     68            emitSqlQueryError(query);
     69            return -2;
     70        }
     71        query.first();
     72        if (query.value(0).toInt() == 1) return 0; // tables are existing
     73        return -1; // database seems to be empty (or has other tables)
     74    }
     75    return version;
     76}
     77
     78
     79bool SqlEngine::updateDbSchemaVersion000To001() {
     80    emit dbError("Upgrade 0 -> 1 not implemented yet");
     81    return false;
     82}
     83
     84
     85bool SqlEngine::createCurrentDbSchema() {
     86    QFile file(":/dbschema001.sql");
     87    file.open(QIODevice::ReadOnly | QIODevice::Text);
     88    QString allSqlStatements = file.readAll();
     89    QSqlQuery query(db);
     90    foreach(QString sql, allSqlStatements.split(";")) {
     91        if (sql.trimmed().isEmpty())  // do not execute empty queries like the last character from create_tables.sql
     92            continue;
     93        if (!query.exec(sql)) {
     94            emitSqlQueryError(query);
     95            return false;
     96        }
     97    }
     98    return true;
     99}
     100
     101
     102bool SqlEngine::createOrUpdateDbSchema() {
     103    int version = dbSchemaVersion();
     104    switch (version) {
     105    case -2:
     106        // the error has already been emitted by the previous function
     107        return false;
     108    case -1:
     109        // empty database
     110        return createCurrentDbSchema();
     111    case 0:
     112        // db schema version 0
     113        return updateDbSchemaVersion000To001();
     114    case 1:
     115        // current schema
     116        return true;
     117    default:
     118        // unsupported schema
     119        emit dbError(tr("Unsupported database schema version %1.").arg(version));
     120    }
     121    return false;
     122}
     123
     124
     125void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId) {
     126    // HACK
     127    // When city is empty, assign a dummy value. We probably want to find a way to change the database scheme ...
     128    // cf. #32
     129    if (aConference["city"].isEmpty()) aConference["city"] = "n/a";
     130
     131    QSqlQuery query(db);
     132    if (conferenceId <= 0) // insert conference
     133    {
     134        query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
     135                                                "day_change,timeslot_duration,active) "
     136                        " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,:days,"
     137                                                ":day_change,:timeslot_duration,:active)");
     138        foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
     139            query.bindValue(QString(":") + prop_name, aConference[prop_name]);
     140        }
     141        query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
     142        query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
     143        query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
     144        query.bindValue(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
     145        query.bindValue(":active", 1);
     146        emitSqlQueryError(query);
     147        aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
     148    }
     149    else // update conference
     150    {
     151        query.prepare("UPDATE CONFERENCE set title=:title, url=:url, subtitle=:subtitle, venue=:venue, city=:city, start=:start, end=:end, days=:days,"
     152                                            "day_change=:day_change, timeslot_duration=:timeslot_duration, active=:active "
     153                      "WHERE id=:id");
     154        foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
     155            query.bindValue(QString(":") + prop_name, aConference[prop_name]);
     156        }
     157        query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
     158        query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
     159        query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
     160        query.bindValue(":timeslot_duration", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
     161        query.bindValue(":active", 1);
     162        query.bindValue(":id", conferenceId);
     163        emitSqlQueryError(query);
     164        aConference["id"] = conferenceId;
     165    }
     166}
     167
     168
     169void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent) {
     170    //insert event track to table and get track id
     171    int conference = aEvent["conference_id"].toInt();
     172    QString name = aEvent["track"];
     173    Track track;
     174    int trackId;
     175    try
     176    {
     177        track = Track::retrieveByName(conference, name);
     178        trackId = track.id();
     179    }
     180    catch (OrmNoObjectException &e) {
     181        track.setConference(conference);
     182        track.setName(name);
     183        trackId = track.insert();
     184    }
     185    QDateTime startDateTime;
     186    startDateTime.setTimeSpec(Qt::UTC);
     187    startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
     188
     189    bool event_exists = false;
     190    {
     191        QSqlQuery check_event_query;
     192        check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id");
     193        check_event_query.bindValue(":xid_conference", aEvent["conference_id"]);
     194        check_event_query.bindValue(":id", aEvent["id"]);
     195        if (!check_event_query.exec()) {
     196            qWarning() << "check event failed, conference id:" << aEvent["xid_conference"]
     197                    << "event id:" << aEvent["id"]
     198                    << "error:" << check_event_query.lastError()
     199                    ;
     200            return;
     201        }
     202        if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) {
     203            event_exists = true;
     204        }
     205    }
     206
     207    QSqlQuery result;
     208    if (event_exists) {
     209        result.prepare("UPDATE EVENT SET"
     210                        " start = :start"
     211                        ", duration = :duration"
     212                        ", xid_track = :xid_track"
     213                        ", type = :type"
     214                        ", language = :language"
     215                        ", tag = :tag"
     216                        ", title = :title"
     217                        ", subtitle = :subtitle"
     218                        ", abstract = :abstract"
     219                        ", description = :description"
     220                            " WHERE id = :id AND xid_conference = :xid_conference");
     221    } else {
     222        result.prepare("INSERT INTO EVENT "
     223                        " (xid_conference, id, start, duration, xid_track, type, "
     224                            " language, tag, title, subtitle, abstract, description) "
     225                        " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, "
     226                            ":language, :tag, :title, :subtitle, :abstract, :description)");
     227    }
     228    result.bindValue(":xid_conference", aEvent["conference_id"]);
     229    result.bindValue(":start", QString::number(startDateTime.toTime_t()));
     230    result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0)));
     231    result.bindValue(":xid_track", trackId);
     232    static const QList<QString> props = QList<QString>()
     233        << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description";
     234    foreach (QString prop_name, props) {
     235        result.bindValue(QString(":") + prop_name, aEvent[prop_name]);
     236    }
     237    if (!result.exec()) {
     238        qWarning() << "event insert/update failed:" << result.lastError();
     239    }
     240}
     241
     242
     243void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson) {
     244    QSqlQuery query(db);
     245    query.prepare("INSERT INTO PERSON (xid_conference,id,name) VALUES (:xid_conference, :id, :name)");
     246    query.bindValue(":xid_conference", aPerson["conference_id"]);
     247    query.bindValue(":id", aPerson["id"]);
     248    query.bindValue(":name", aPerson["name"]);
     249    query.exec(); // TODO some queries fail due to the unique key constraint
     250    // if (!query.exec()) qDebug() << "SQL query 'insert into person' failed: " << query.lastError();
     251
     252    query = QSqlQuery(db);
     253    query.prepare("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (:xid_conference, :xid_event, :xid_person)");
     254    query.bindValue(":xid_conference", aPerson["conference_id"]);
     255    query.bindValue(":xid_event", aPerson["event_id"]);
     256    query.bindValue(":xid_person", aPerson["id"]);
     257    query.exec(); // TODO some queries fail due to the unique key constraint
     258    // if (!query.exec()) qDebug() << "SQL query 'insert into event_person' failed: " << query.lastError();
     259}
     260
     261
     262void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom) {
     263    QSqlQuery query(db);
     264    query.prepare("SELECT id FROM ROOM WHERE xid_conference=:conference_id and name=:name");
     265    query.bindValue(":conference_id", aRoom["conference_id"]);
     266    query.bindValue(":name", aRoom["name"]);
     267    emitSqlQueryError(query);
     268    // now we have to check whether ROOM record with 'name' exists or not,
     269    // - if it doesn't exist yet, then we have to add that record to 'ROOM' table
     270    //   and assign autoincremented 'id' to aRoom
     271    // - if it exists, then we need to get its 'id' and assign it to aRoom
     272    aRoom["id"] = "";
     273    if(query.next()) // ROOM record with 'name' already exists: we need to get its 'id'
     274    {
     275        aRoom["id"] = query.value(0).toString();
     276    }
     277    else // ROOM record doesn't exist yet, need to create it
     278    {
    231279        query = QSqlQuery(db);
    232         query.prepare("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (:xid_conference, :xid_event, :xid_person)");
    233         query.bindValue(":xid_conference", aPerson["conference_id"]);
    234         query.bindValue(":xid_event", aPerson["event_id"]);
    235         query.bindValue(":xid_person", aPerson["id"]);
    236         query.exec(); // some queries fail due to the unique key constraint
    237         // if (!query.exec()) qDebug() << "SQL query 'insert into event_person' failed: " << query.lastError();
    238     }
    239 }
    240 
    241 void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
    242 {
    243     QSqlDatabase db = QSqlDatabase::database();
    244 
    245     if (db.isValid() && db.isOpen())
    246     {
    247         QSqlQuery query(db);
    248         query.prepare("SELECT id FROM ROOM WHERE xid_conference=:conference_id and name=:name");
    249         query.bindValue(":conference_id", aRoom["conference_id"]);
    250         query.bindValue(":name", aRoom["name"]);
    251         if (!query.exec()) qDebug() << "Could not execute select room query: " << query.lastError();
    252         // now we have to check whether ROOM record with 'name' exists or not,
    253         // - if it doesn't exist yet, then we have to add that record to 'ROOM' table
    254         //   and assign autoincremented 'id' to aRoom
    255         // - if it exists, then we need to get its 'id' and assign it to aRoom
    256         aRoom["id"] = "";
    257         if(query.next()) // ROOM record with 'name' already exists: we need to get its 'id'
    258         {
    259             aRoom["id"] = query.value(0).toString();
    260         }
    261         else // ROOM record doesn't exist yet, need to create it
    262         {
    263             query = QSqlQuery(db);
    264             query.prepare("INSERT INTO ROOM (xid_conference,name,picture) VALUES (:xid_conference, :name, '')");
    265             query.bindValue(":xid_conference", aRoom["conference_id"]);
    266             query.bindValue(":xid_name", aRoom["name"]);
    267             if (!query.exec()) qDebug() << "Could not execute 'insert into room ...' query." << query.lastError();
    268             aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
    269             //LOG_AUTOTEST(query);
    270         }
    271        
    272         // remove previous conference/room records; room names might have changed
    273         query = QSqlQuery(db);
    274         query.prepare("DELETE FROM EVENT_ROOM WHERE xid_conference=:conference_id AND xid_event=:event_id");
    275         query.bindValue(":conference_id", aRoom["conference_id"]);
    276         query.bindValue(":event_id", aRoom["event_id"]);
    277         if (!query.exec()) qDebug() << "Could not execute SELECT * FROM EVENT_ROOM' query:" << query.lastError();
    278         // and insert new ones
    279         query = QSqlQuery(db);
    280         query.prepare("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (:conference_id, :event_id, :room_id)");
    281         query.bindValue(":conference_id", aRoom["conference_id"]);
    282         query.bindValue(":event_id", aRoom["event_id"]);
    283         query.bindValue(":room_id", aRoom["id"]);
    284         if (!query.exec()) qDebug() << "Could not 'execute insert into event_room' query:" << query.lastError();
    285     }
    286 }
    287 
    288 void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink)
    289 {
    290     QSqlDatabase db = QSqlDatabase::database();
    291 
     280        query.prepare("INSERT INTO ROOM (xid_conference,name,picture) VALUES (:xid_conference, :name, '')");
     281        query.bindValue(":xid_conference", aRoom["conference_id"]);
     282        query.bindValue(":xid_name", aRoom["name"]);
     283        emitSqlQueryError(query);
     284        aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
     285        //LOG_AUTOTEST(query);
     286    }
     287
     288    // remove previous conference/room records; room names might have changed
     289    query = QSqlQuery(db);
     290    query.prepare("DELETE FROM EVENT_ROOM WHERE xid_conference=:conference_id AND xid_event=:event_id");
     291    query.bindValue(":conference_id", aRoom["conference_id"]);
     292    query.bindValue(":event_id", aRoom["event_id"]);
     293    emitSqlQueryError(query);
     294    // and insert new ones
     295    query = QSqlQuery(db);
     296    query.prepare("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (:conference_id, :event_id, :room_id)");
     297    query.bindValue(":conference_id", aRoom["conference_id"]);
     298    query.bindValue(":event_id", aRoom["event_id"]);
     299    query.bindValue(":room_id", aRoom["id"]);
     300    emitSqlQueryError(query);
     301}
     302
     303
     304void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink) {
    292305    //TODO: check if the link doesn't exist before inserting
    293     if (db.isValid() && db.isOpen())
    294     {
    295         QSqlQuery query(db);
    296         query.prepare("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (:xid_event, :xid_conference, :name, :url)");
    297         query.bindValue(":xid_event", aLink["event_id"]);
    298         query.bindValue(":xid_conference", aLink["conference_id"]);
    299         query.bindValue(":name", aLink["name"]);
    300         query.bindValue(":url", aLink["url"]);
    301         if (!query.exec()) qDebug() << "Error executing 'insert into link' query: " << query.lastError();
    302     }
    303 }
    304 
    305 int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
    306 {
    307     QSqlDatabase db = QSqlDatabase::database();
    308 
    309     if ( !db.isValid() || !db.isOpen())
    310         return -1;
    311 
    312     if (aColumns.empty()) return -1;
     306    QSqlQuery query(db);
     307    query.prepare("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (:xid_event, :xid_conference, :name, :url)");
     308    query.bindValue(":xid_event", aLink["event_id"]);
     309    query.bindValue(":xid_conference", aLink["conference_id"]);
     310    query.bindValue(":name", aLink["name"]);
     311    query.bindValue(":url", aLink["url"]);
     312    emitSqlQueryError(query);
     313}
     314
     315
     316bool SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword) {
     317    if (aColumns.empty()) return false;
    313318
    314319    // DROP
    315     execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT");
     320    QSqlQuery query(db);
     321    query.exec("DROP TABLE IF EXISTS SEARCH_EVENT");
     322    emitSqlQueryError(query);
     323
    316324    // CREATE
    317     execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL )");
     325    query.exec("CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER  NOT NULL, id INTEGER NOT NULL )");
     326    emitSqlQueryError(query);
     327
    318328    // INSERT
    319329    QString sql = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
    … …  
    343353    sql += QString(")");
    344354
    345     QSqlQuery query(db);
    346355    query.prepare(sql);
    347356    for (int i = 0; i != searchKeywords.size(); ++i) {
    … …  
    354363    }
    355364
    356     if( !query.exec() ){
    357        qDebug() << "Could not execute search query: " << query.lastError().text();
    358        return -1;
    359     }
    360 
    361     return 1;
    362 }
    363 
    364 bool SqlEngine::beginTransaction()
    365 {
    366     QSqlDatabase db = QSqlDatabase::database();
    367 
    368     return execQuery(db, "BEGIN IMMEDIATE TRANSACTION");
    369 }
    370 
    371 bool SqlEngine::commitTransaction()
    372 {
    373     QSqlDatabase db = QSqlDatabase::database();
    374 
    375     return execQuery(db, "COMMIT");
    376 }
    377 
    378 void SqlEngine::deleteConference(int id)
    379 {
    380     QSqlDatabase db = QSqlDatabase::database();
    381 
    382     if ( !db.isValid() || !db.isOpen()) {
    383         return;
    384     }
    385 
    386     beginTransaction();
    387 
    388     QHash<QString, QVariant> params;
    389     params["xid_conference"] = id;
    390     execQueryWithParameter(db, "DELETE FROM LINK WHERE xid_conference = :xid_conference", params);
    391     execQueryWithParameter(db, "DELETE FROM EVENT_ROOM WHERE xid_conference = :xid_conference", params);
    392     execQueryWithParameter(db, "DELETE FROM EVENT_PERSON WHERE xid_conference = :xid_conference", params);
    393     execQueryWithParameter(db, "DELETE FROM EVENT WHERE xid_conference = :xid_conference", params);
    394     execQueryWithParameter(db, "DELETE FROM ROOM WHERE xid_conference = :xid_conference", params);
    395     execQueryWithParameter(db, "DELETE FROM PERSON WHERE xid_conference = :xid_conference", params);
    396     execQueryWithParameter(db, "DELETE FROM TRACK WHERE xid_conference = :xid_conference", params);
    397     execQueryWithParameter(db, "DELETE FROM CONFERENCE WHERE id = :xid_conference", params);
    398 
    399     commitTransaction();
    400 }
    401 
    402 bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
    403 {
    404     QSqlQuery sqlQuery(aDatabase);
    405     if( !sqlQuery.exec(aQuery) ){
    406        qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
    407        return false;
    408     }
    409     return true;
    410 }
    411 
    412 bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params)
    413 {
    414     QSqlQuery sqlQuery(aDatabase);
    415     sqlQuery.prepare(aQuery);
    416     foreach (QString param_key, params.keys()) {
    417         sqlQuery.bindValue(param_key, params[param_key]);
    418     }
    419     if( !sqlQuery.exec() ){
    420        qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
    421        return false;
    422     }
    423     return true;
    424 }
     365    bool success = query.exec();
     366    emitSqlQueryError(query);
     367    return success;
     368}
     369
     370
     371bool SqlEngine::beginTransaction() {
     372    QSqlQuery query(db);
     373    bool success = query.exec("BEGIN IMMEDIATE TRANSACTION");
     374    emitSqlQueryError(query);
     375    return success;
     376}
     377
     378
     379bool SqlEngine::commitTransaction() {
     380    QSqlQuery query(db);
     381    bool success = query.exec("COMMIT");
     382    emitSqlQueryError(query);
     383    return success;
     384}
     385
     386
     387bool SqlEngine::deleteConference(int id) {
     388    QSqlQuery query(db);
     389    bool success = query.exec("BEGIN IMMEDIATE TRANSACTION");
     390    emitSqlQueryError(query);
     391
     392    QStringList sqlList;
     393    sqlList << "DELETE FROM LINK WHERE xid_conference = ?"
     394            << "DELETE FROM EVENT_ROOM WHERE xid_conference = ?"
     395            << "DELETE FROM EVENT_PERSON WHERE xid_conference = ?"
     396            << "DELETE FROM EVENT WHERE xid_conference = ?"
     397            << "DELETE FROM ROOM WHERE xid_conference = ?"
     398            << "DELETE FROM PERSON WHERE xid_conference = ?"
     399            << "DELETE FROM TRACK WHERE xid_conference = ?"
     400            << "DELETE FROM CONFERENCE WHERE id = ?";
     401
     402    foreach (const QString& sql, sqlList) {
     403        query.prepare(sql);
     404        query.bindValue(0, id);
     405        success &= query.exec();
     406        emitSqlQueryError(query);
     407    }
     408
     409    success &= query.exec("COMMIT");
     410    emitSqlQueryError(query);
     411
     412    return success;
     413}
     414
     415
     416void SqlEngine::emitSqlQueryError(const QSqlQuery &query) {
     417    QSqlError error = query.lastError();
     418    if (error.type() == QSqlError::NoError) return;
     419    emit dbError(error.text());
     420}
     421

  • src/sql/sqlengine.h

    r8d6798d r61346c9  
    2323#include <QObject>
    2424#include <QHash>
     25#include <QSqlDatabase>
    2526
    26 class QSqlDatabase;
    2727
    28 class SqlEngine : public QObject
    29 {
     28class SqlEngine : public QObject {
    3029    Q_OBJECT
    3130    public:
     31        QString dbFilename; ///< database filename including path
     32        QSqlDatabase db; ///< this may be private one day...
     33
    3234        SqlEngine(QObject *aParent = NULL);
    3335        ~SqlEngine();
    34         static void initialize();
    35         // if a conferenceId != 0 is given, the confernece is updated instead of inserted.
    36         static void addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId);
    37         static void addEventToDB(QHash<QString,QString> &aEvent);
    38         static void addPersonToDB(QHash<QString,QString> &aPerson);
    39         static void addLinkToDB(QHash<QString,QString> &aLink);
    40         static void addRoomToDB(QHash<QString,QString> &aRoom);
    41         static void deleteConference(int id);
    4236
    43         static bool beginTransaction();
    44         static bool commitTransaction();
     37        // Open/Close
     38        void open(); ///< emits a database error if failed.
     39        bool isOpen() const {return db.isOpen();}
     40        void close() {db.close();}
    4541
    46         // search Events for ....
    47         static int searchEvent(int conferenceId, const QHash<QString,QString> &columns, const QString &keyword);
     42        // Schema version
     43        /// returns the "user_version" of the database schema
     44        /// we return -1 for an empty database
     45        /// the database has to be open
     46        /// returns -2 if an error occurs and emits the error message
     47        int dbSchemaVersion();
     48        /// called by createOrUpdateDbSchema. Do not use directly. true for success.
     49        bool updateDbSchemaVersion000To001();
     50        /// called by createOrUpdateDbSchma. Do not use directly. true for success.
     51        bool createCurrentDbSchema();
     52        /// creates the current database schema if an empty database is found,
     53        /// otherwise updates the schema if an old one is found. true for success.
     54        bool createOrUpdateDbSchema();
     55
     56        // if a conferneceId != 0 is given, the confernce is updated instead of inserted.
     57        void addConferenceToDB(QHash<QString,QString> &aConference, int conferenceId);
     58        void addEventToDB(QHash<QString,QString> &aEvent);
     59        void addPersonToDB(QHash<QString,QString> &aPerson);
     60        void addLinkToDB(QHash<QString,QString> &aLink);
     61        void addRoomToDB(QHash<QString,QString> &aRoom);
     62        bool deleteConference(int id);
     63
     64        bool beginTransaction();
     65        bool commitTransaction();
     66
     67        /// search Events for .... returns true if success
     68        bool searchEvent(int conferenceId, const QHash<QString,QString> &columns, const QString &keyword);
    4869    private:
    4970        static QString login(const QString &aDatabaseType, const QString &aDatabaseName);
    50         static bool execQuery(QSqlDatabase &aDatabase, const QString &aQuery);
    51         static bool execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params);
     71        /// emits a possible error message as signal. Does nothing if there was not last error
     72        void emitSqlQueryError(const QSqlQuery& query);
     73
     74    signals:
     75        /// emitted when a database errors occur
     76        void dbError(const QString& message);
    5277};
    5378
Note: See TracChangeset for help on using the changeset viewer.