[ca90cb1] | 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 | */ |
---|
[72f6fe4] | 19 | |
---|
| 20 | #include <QSqlError> |
---|
| 21 | #include <QSqlQuery> |
---|
| 22 | #include <QSqlRecord> |
---|
| 23 | #include <QVariant> |
---|
| 24 | #include <QDateTime> |
---|
| 25 | |
---|
| 26 | #include <QDir> |
---|
| 27 | #include "sqlengine.h" |
---|
[72cd3af] | 28 | #include <track.h> |
---|
[1735f55] | 29 | #include <conference.h> |
---|
[72f6fe4] | 30 | |
---|
| 31 | #include <QDebug> |
---|
| 32 | |
---|
| 33 | const QString DATE_FORMAT ("yyyy-MM-dd"); |
---|
| 34 | const QString TIME_FORMAT ("hh:mm"); |
---|
| 35 | |
---|
| 36 | SqlEngine::SqlEngine(QObject *aParent) |
---|
| 37 | : QObject(aParent) |
---|
| 38 | { |
---|
| 39 | } |
---|
| 40 | |
---|
| 41 | SqlEngine::~SqlEngine() |
---|
| 42 | { |
---|
| 43 | } |
---|
| 44 | |
---|
| 45 | QString 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 | { |
---|
[4b8ce66] | 53 | // create Db |
---|
| 54 | if (!database.open()) qDebug() << "Could not open database" << database.lastError(); |
---|
| 55 | QFile file(":/create_tables.sql"); |
---|
| 56 | file.open(QIODevice::ReadOnly | QIODevice::Text); |
---|
| 57 | QString allSqlStatements = file.readAll(); |
---|
| 58 | foreach(QString sql, allSqlStatements.split(";")) { |
---|
| 59 | QSqlQuery query(database); |
---|
| 60 | if (!query.exec(sql)) qDebug() << "Could not execute query" << query.lastError(); |
---|
| 61 | } |
---|
[72f6fe4] | 62 | } |
---|
[e662750] | 63 | else |
---|
| 64 | { |
---|
| 65 | database.open(); |
---|
| 66 | } |
---|
[72f6fe4] | 67 | |
---|
[cec47c6] | 68 | checkConferenceMap(database); |
---|
| 69 | |
---|
[72f6fe4] | 70 | //LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName)); |
---|
| 71 | |
---|
| 72 | return result ? QString() : database.lastError().text(); |
---|
| 73 | } |
---|
| 74 | |
---|
| 75 | void SqlEngine::initialize() |
---|
| 76 | { |
---|
| 77 | QString databaseName; |
---|
| 78 | if(!QDir::home().exists(".fosdem")) |
---|
| 79 | QDir::home().mkdir(".fosdem"); |
---|
| 80 | databaseName = QDir::homePath() + "/.fosdem/" + "fosdem.sqlite"; |
---|
| 81 | login("QSQLITE",databaseName); |
---|
| 82 | } |
---|
| 83 | |
---|
| 84 | void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference) |
---|
| 85 | { |
---|
| 86 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 87 | |
---|
| 88 | if (db.isValid() && db.isOpen()) |
---|
| 89 | { |
---|
[1735f55] | 90 | int confId = 0; |
---|
| 91 | QList<Conference> confsList = Conference::getAll(); |
---|
| 92 | if(confsList.count()) |
---|
| 93 | { |
---|
| 94 | QListIterator<Conference> i(confsList); |
---|
| 95 | while (i.hasNext()) |
---|
| 96 | { |
---|
| 97 | Conference conf = i.next(); |
---|
| 98 | if( aConference["title"] == conf.title() ) |
---|
| 99 | { |
---|
| 100 | confId = conf.id(); |
---|
| 101 | aConference["id"] = QString::number(confId); |
---|
| 102 | break; |
---|
| 103 | } |
---|
| 104 | } |
---|
| 105 | } |
---|
| 106 | |
---|
| 107 | if(!confId) // conference 'aConference' isn't in the table => insert |
---|
| 108 | { |
---|
[d06ae27] | 109 | QSqlQuery query(db); |
---|
| 110 | query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days," |
---|
| 111 | "day_change,timeslot_duration,active) " |
---|
| 112 | " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,:days," |
---|
| 113 | ":day_change,:timeslot_duration,:active)"); |
---|
| 114 | foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) { |
---|
| 115 | query.bindValue(QString(":") + prop_name, aConference[prop_name]); |
---|
| 116 | } |
---|
| 117 | query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()); |
---|
| 118 | query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()); |
---|
| 119 | query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0))); |
---|
| 120 | query.bindValue(":day_change", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0))); |
---|
| 121 | query.bindValue(":active", confsList.count() > 0 ? 0 : 1); |
---|
[f657e9c] | 122 | if (!query.exec()) qDebug() << "Could not execute query to insert a conference:" << query.lastError(); |
---|
[d06ae27] | 123 | aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically |
---|
[1735f55] | 124 | } |
---|
[72f6fe4] | 125 | } |
---|
| 126 | } |
---|
| 127 | |
---|
| 128 | void SqlEngine::addEventToDB(QHash<QString,QString> &aEvent) |
---|
| 129 | { |
---|
| 130 | //LOG_DEBUG(QString("Adding event '%1' to DB").arg(*aEvent)); |
---|
| 131 | |
---|
| 132 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 133 | |
---|
| 134 | if (db.isValid() && db.isOpen()) |
---|
| 135 | { |
---|
[005e2b7] | 136 | //insert event track to table and get track id |
---|
[51529bd] | 137 | int conference = aEvent["conference_id"].toInt(); |
---|
[72cd3af] | 138 | QString name = aEvent["track"]; |
---|
| 139 | Track track; |
---|
| 140 | int trackId; |
---|
| 141 | try |
---|
[926f106] | 142 | { |
---|
[141a5c2] | 143 | track = Track::retrieveByName(conference, name); |
---|
[72cd3af] | 144 | trackId = track.id(); |
---|
| 145 | /*qDebug() << QString("DEBUG: Track %1 in DB").arg(name);*/ |
---|
[926f106] | 146 | } |
---|
[72cd3af] | 147 | catch (OrmNoObjectException &e) { |
---|
[51529bd] | 148 | track.setConference(conference); |
---|
[72cd3af] | 149 | track.setName(name); |
---|
| 150 | trackId = track.insert(); |
---|
| 151 | /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/ |
---|
[926f106] | 152 | } |
---|
[5d9409d] | 153 | QDateTime startDateTime; |
---|
| 154 | startDateTime.setTimeSpec(Qt::UTC); |
---|
| 155 | startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC); |
---|
[f09a1b9] | 156 | // qDebug() << "startDateTime: " << startDateTime.toString(); |
---|
[f548c17] | 157 | |
---|
| 158 | bool event_exists = false; |
---|
| 159 | { |
---|
| 160 | QSqlQuery check_event_query; |
---|
| 161 | check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id"); |
---|
[c129da26] | 162 | check_event_query.bindValue(":xid_conference", aEvent["conference_id"]); |
---|
[f548c17] | 163 | check_event_query.bindValue(":id", aEvent["id"]); |
---|
[c129da26] | 164 | if (!check_event_query.exec()) { |
---|
| 165 | qWarning() << "check event failed, conference id:" << aEvent["xid_conference"] |
---|
| 166 | << "event id:" << aEvent["id"] |
---|
| 167 | << "error:" << check_event_query.lastError() |
---|
| 168 | ; |
---|
| 169 | return; |
---|
| 170 | } |
---|
| 171 | if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) { |
---|
[f548c17] | 172 | event_exists = true; |
---|
| 173 | } |
---|
| 174 | } |
---|
| 175 | |
---|
[7d7659d] | 176 | QSqlQuery result; |
---|
[f548c17] | 177 | if (event_exists) { |
---|
| 178 | result.prepare("UPDATE EVENT SET" |
---|
| 179 | " start = :start" |
---|
| 180 | ", duration = :duration" |
---|
| 181 | ", xid_track = :xid_track" |
---|
| 182 | ", type = :type" |
---|
| 183 | ", language = :language" |
---|
| 184 | ", tag = :tag" |
---|
| 185 | ", title = :title" |
---|
| 186 | ", subtitle = :subtitle" |
---|
| 187 | ", abstract = :abstract" |
---|
| 188 | ", description = :description" |
---|
| 189 | " WHERE id = :id AND xid_conference = :xid_conference"); |
---|
| 190 | } else { |
---|
| 191 | result.prepare("INSERT INTO EVENT " |
---|
| 192 | " (xid_conference, id, start, duration, xid_track, type, " |
---|
| 193 | " language, tag, title, subtitle, abstract, description) " |
---|
[c129da26] | 194 | " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, " |
---|
[f548c17] | 195 | ":language, :tag, :title, :subtitle, :abstract, :description)"); |
---|
| 196 | } |
---|
| 197 | result.bindValue(":xid_conference", aEvent["conference_id"]); |
---|
| 198 | result.bindValue(":start", QString::number(startDateTime.toTime_t())); |
---|
| 199 | result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0))); |
---|
| 200 | result.bindValue(":xid_track", trackId); |
---|
| 201 | static const QList<QString> props = QList<QString>() |
---|
| 202 | << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description"; |
---|
| 203 | foreach (QString prop_name, props) { |
---|
| 204 | result.bindValue(QString(":") + prop_name, aEvent[prop_name]); |
---|
| 205 | } |
---|
[c129da26] | 206 | if (!result.exec()) { |
---|
| 207 | qWarning() << "event insert/update failed:" << result.lastError(); |
---|
| 208 | } |
---|
[72f6fe4] | 209 | } |
---|
| 210 | } |
---|
| 211 | |
---|
| 212 | void SqlEngine::addPersonToDB(QHash<QString,QString> &aPerson) |
---|
| 213 | { |
---|
| 214 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 215 | |
---|
| 216 | if (db.isValid() && db.isOpen()) |
---|
| 217 | { |
---|
[63b2343] | 218 | // TODO: SQL Injection!!! |
---|
[51529bd] | 219 | QString values = QString("'%1', '%2', '%3'").arg(aPerson["conference_id"],aPerson["id"],aPerson["name"]); |
---|
| 220 | QString query = QString("INSERT INTO PERSON (xid_conference,id,name) VALUES (%1)").arg(values); |
---|
[72f6fe4] | 221 | QSqlQuery result (query, db); |
---|
| 222 | //LOG_AUTOTEST(query); |
---|
| 223 | |
---|
[63b2343] | 224 | // TODO: SQL Injection!!! |
---|
[72f6fe4] | 225 | values = QString("'%1', '%2', '%3'").arg(aPerson["conference_id"],aPerson["event_id"],aPerson["id"]); |
---|
| 226 | query = QString("INSERT INTO EVENT_PERSON (xid_conference,xid_event,xid_person) VALUES (%1)").arg(values); |
---|
| 227 | QSqlQuery resultEventPerson (query, db); |
---|
| 228 | //LOG_AUTOTEST(query); |
---|
| 229 | } |
---|
| 230 | } |
---|
| 231 | |
---|
| 232 | void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom) |
---|
| 233 | { |
---|
| 234 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 235 | |
---|
| 236 | if (db.isValid() && db.isOpen()) |
---|
| 237 | { |
---|
[63b2343] | 238 | QSqlQuery query(db); |
---|
| 239 | query.prepare("SELECT id FROM ROOM WHERE xid_conference=:conference_id and name=:name"); |
---|
| 240 | query.bindValue(":conference_id", aRoom["conference_id"]); |
---|
| 241 | query.bindValue(":name", aRoom["name"]); |
---|
| 242 | if (!query.exec()) qDebug() << "Could not execute select room query: " << query.lastError(); |
---|
[72f6fe4] | 243 | // now we have to check whether ROOM record with 'name' exists or not, |
---|
| 244 | // - if it doesn't exist yet, then we have to add that record to 'ROOM' table |
---|
| 245 | // and assign autoincremented 'id' to aRoom |
---|
| 246 | // - if it exists, then we need to get its 'id' and assign it to aRoom |
---|
[909ea23] | 247 | aRoom["id"] = ""; |
---|
[63b2343] | 248 | if(query.next()) // ROOM record with 'name' already exists: we need to get its 'id' |
---|
[72f6fe4] | 249 | { |
---|
[f09a1b9] | 250 | aRoom["id"] = query.value(0).toString(); |
---|
[72f6fe4] | 251 | } |
---|
| 252 | else // ROOM record doesn't exist yet, need to create it |
---|
| 253 | { |
---|
[909ea23] | 254 | query = QSqlQuery(db); |
---|
| 255 | query.prepare("INSERT INTO ROOM (xid_conference,name,picture) VALUES (:xid_conference, :name, :picture)"); |
---|
| 256 | query.bindValue(":xid_conference", aRoom["conference_id"]); |
---|
| 257 | query.bindValue(":xid_name", aRoom["name"]); |
---|
| 258 | query.bindValue(":xid_picture", aRoom["picture"]); |
---|
| 259 | if (!query.exec()) qDebug() << "Could not execute 'insert into room ...' query." << query.lastError(); |
---|
[f09a1b9] | 260 | aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically |
---|
[72f6fe4] | 261 | //LOG_AUTOTEST(query); |
---|
| 262 | } |
---|
[63b2343] | 263 | query = QSqlQuery(db); |
---|
[f09a1b9] | 264 | query.prepare("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (:conference_id, :event_id, :room_id)"); |
---|
[63b2343] | 265 | query.bindValue(":conference_id", aRoom["conference_id"]); |
---|
| 266 | query.bindValue(":event_id", aRoom["event_id"]); |
---|
[f09a1b9] | 267 | query.bindValue(":room_id", aRoom["id"]); |
---|
[909ea23] | 268 | if (!query.exec()) qDebug() << "Could not 'execute insert into event_room' query:" << query.lastError(); |
---|
[72f6fe4] | 269 | //LOG_AUTOTEST(query); |
---|
| 270 | } |
---|
| 271 | } |
---|
| 272 | |
---|
| 273 | void SqlEngine::addLinkToDB(QHash<QString,QString> &aLink) |
---|
| 274 | { |
---|
| 275 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 276 | |
---|
| 277 | //TODO: check if the link doesn't exist before inserting |
---|
| 278 | if (db.isValid() && db.isOpen()) |
---|
| 279 | { |
---|
[dc54e87] | 280 | QSqlQuery query(db); |
---|
| 281 | query.prepare("INSERT INTO LINK (xid_event, xid_conference, name, url) VALUES (:xid_event, :xid_conference, :name, :url)"); |
---|
| 282 | query.bindValue(":xid_event", aLink["event_id"]); |
---|
| 283 | query.bindValue(":xid_conference", aLink["conference_id"]); |
---|
| 284 | query.bindValue(":name", aLink["name"]); |
---|
| 285 | query.bindValue(":url", aLink["url"]); |
---|
| 286 | if (!query.exec()) qDebug() << "Error executing 'insert into link' query: " << query.lastError(); |
---|
[72f6fe4] | 287 | //LOG_AUTOTEST(query); |
---|
| 288 | } |
---|
| 289 | } |
---|
| 290 | |
---|
[c7b58d4] | 291 | int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword) |
---|
[990afd5] | 292 | { |
---|
| 293 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 294 | |
---|
| 295 | if ( !db.isValid() || !db.isOpen()) |
---|
| 296 | return -1; |
---|
| 297 | |
---|
[a55781e] | 298 | if (aColumns.empty()) return -1; |
---|
[990afd5] | 299 | |
---|
[e662750] | 300 | // DROP |
---|
[63b2343] | 301 | execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT"); |
---|
[e662750] | 302 | // CREATE |
---|
[63b2343] | 303 | execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER NOT NULL, id INTEGER NOT NULL )"); |
---|
[e662750] | 304 | // INSERT |
---|
[a55781e] | 305 | QString sql = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) " |
---|
[c7b58d4] | 306 | "SELECT EVENT.xid_conference, EVENT.id FROM EVENT "); |
---|
| 307 | if( aColumns.contains("ROOM") ){ |
---|
[a55781e] | 308 | sql += "INNER JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) "; |
---|
| 309 | sql += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) "; |
---|
[c7b58d4] | 310 | } |
---|
| 311 | if( aColumns.contains("PERSON") ){ |
---|
[a55781e] | 312 | sql += "INNER JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) "; |
---|
| 313 | sql += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) "; |
---|
[c7b58d4] | 314 | } |
---|
[a55781e] | 315 | sql += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId ); |
---|
[c7b58d4] | 316 | |
---|
| 317 | foreach (QString table, aColumns.uniqueKeys()){ |
---|
| 318 | foreach (QString column, aColumns.values(table)){ |
---|
[a55781e] | 319 | sql += QString("%1.%2 LIKE '\%' || :%1%2 || '\%' OR ").arg( table, column ); |
---|
[c7b58d4] | 320 | } |
---|
[990afd5] | 321 | } |
---|
[a55781e] | 322 | sql.chop( QString(" OR ").length() ); |
---|
| 323 | sql += QString(")"); |
---|
[990afd5] | 324 | |
---|
[a55781e] | 325 | QSqlQuery query(db); |
---|
| 326 | query.prepare(sql); |
---|
| 327 | foreach (QString table, aColumns.uniqueKeys()){ |
---|
| 328 | foreach (QString column, aColumns.values(table)){ |
---|
| 329 | query.bindValue(QString(":%1%2").arg(table, column), aKeyword ); |
---|
| 330 | } |
---|
| 331 | } |
---|
| 332 | |
---|
| 333 | if( !query.exec() ){ |
---|
| 334 | qDebug() << "Could not execute search query: " << query.lastError().text(); |
---|
| 335 | return -1; |
---|
| 336 | } |
---|
[990afd5] | 337 | |
---|
[e662750] | 338 | return 1; |
---|
| 339 | } |
---|
| 340 | |
---|
[1bad318] | 341 | bool SqlEngine::beginTransaction() |
---|
| 342 | { |
---|
| 343 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 344 | |
---|
| 345 | return execQuery(db, "BEGIN IMMEDIATE TRANSACTION"); |
---|
| 346 | } |
---|
| 347 | |
---|
| 348 | bool SqlEngine::commitTransaction() |
---|
| 349 | { |
---|
| 350 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 351 | |
---|
| 352 | return execQuery(db, "COMMIT"); |
---|
| 353 | } |
---|
| 354 | |
---|
[d97bcab] | 355 | void SqlEngine::deleteConference(int id) |
---|
| 356 | { |
---|
| 357 | QSqlDatabase db = QSqlDatabase::database(); |
---|
| 358 | |
---|
| 359 | if ( !db.isValid() || !db.isOpen()) { |
---|
| 360 | return; |
---|
| 361 | } |
---|
| 362 | |
---|
| 363 | beginTransaction(); |
---|
| 364 | |
---|
| 365 | QHash<QString, QVariant> params; |
---|
| 366 | params["xid_conference"] = id; |
---|
| 367 | execQueryWithParameter(db, "DELETE FROM LINK WHERE xid_conference = :xid_conference", params); |
---|
| 368 | execQueryWithParameter(db, "DELETE FROM EVENT_ROOM WHERE xid_conference = :xid_conference", params); |
---|
| 369 | execQueryWithParameter(db, "DELETE FROM EVENT_PERSON WHERE xid_conference = :xid_conference", params); |
---|
| 370 | execQueryWithParameter(db, "DELETE FROM EVENT WHERE xid_conference = :xid_conference", params); |
---|
[c8b84e1] | 371 | execQueryWithParameter(db, "DELETE FROM ROOM WHERE xid_conference = :xid_conference", params); |
---|
| 372 | execQueryWithParameter(db, "DELETE FROM PERSON WHERE xid_conference = :xid_conference", params); |
---|
| 373 | execQueryWithParameter(db, "DELETE FROM TRACK WHERE xid_conference = :xid_conference", params); |
---|
[d97bcab] | 374 | execQueryWithParameter(db, "DELETE FROM CONFERENCE WHERE id = :xid_conference", params); |
---|
| 375 | |
---|
| 376 | commitTransaction(); |
---|
| 377 | } |
---|
| 378 | |
---|
[e662750] | 379 | bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery) |
---|
| 380 | { |
---|
[bcf67d6] | 381 | //qDebug() << "\nSQL: " << aQuery; |
---|
[990afd5] | 382 | |
---|
[e662750] | 383 | QSqlQuery sqlQuery(aDatabase); |
---|
| 384 | if( !sqlQuery.exec(aQuery) ){ |
---|
| 385 | qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text(); |
---|
| 386 | return false; |
---|
[990afd5] | 387 | } |
---|
| 388 | else{ |
---|
[bcf67d6] | 389 | //qDebug() << "SQL OK.\n"; |
---|
[e662750] | 390 | return true; |
---|
[990afd5] | 391 | } |
---|
| 392 | } |
---|
[9f367eb] | 393 | |
---|
[d97bcab] | 394 | bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params) |
---|
| 395 | { |
---|
| 396 | qDebug() << "SQL:" << aQuery << "params:" << params; |
---|
| 397 | |
---|
| 398 | QSqlQuery sqlQuery(aDatabase); |
---|
| 399 | sqlQuery.prepare(aQuery); |
---|
| 400 | foreach (QString param_key, params.keys()) { |
---|
| 401 | sqlQuery.bindValue(param_key, params[param_key]); |
---|
| 402 | } |
---|
| 403 | if( !sqlQuery.exec() ){ |
---|
| 404 | qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text(); |
---|
| 405 | return false; |
---|
| 406 | } |
---|
| 407 | else{ |
---|
| 408 | //qDebug() << "SQL OK.\n"; |
---|
| 409 | return true; |
---|
| 410 | } |
---|
| 411 | } |
---|
| 412 | |
---|
[cec47c6] | 413 | void SqlEngine::checkConferenceMap(QSqlDatabase &aDatabase) |
---|
| 414 | { |
---|
| 415 | QSqlQuery sqlQuery(aDatabase); |
---|
| 416 | sqlQuery.prepare("SELECT map FROM conference"); |
---|
| 417 | if (!sqlQuery.exec()) { |
---|
| 418 | qWarning() << "column conference.map is missing; adding"; |
---|
| 419 | execQuery(aDatabase, "ALTER TABLE conference ADD COLUMN map VARCHAR") |
---|
| 420 | and execQuery(aDatabase, "UPDATE conference SET map = ':/maps/campus.png' WHERE title = 'FOSDEM 2010'"); |
---|
| 421 | } |
---|
| 422 | } |
---|