bool result = false;
if(!QFile::exists(aDatabaseName)) // the DB (tables) doesn't exists, and so we have to create one
{
- /*
- // creating empty DB + tables
- // ??? what is the best way of creating new empty DB ???
- // we can either:
- // - create new DB + tables by issuing corresponding queries (used solution)
- // - create new DB from resource, which contains empty DB with tables
- result = createTables(database);
- */
-
// copy conference Db from resource, instead of creating
// empty tables and then parsing the schedule
QFile dbFile(aDatabaseName);
database.open();
}
+ checkConferenceMap(database);
+
//LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
return result ? QString() : database.lastError().text();
if(!confId) // conference 'aConference' isn't in the table => insert
{
- QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9'") \
- .arg(aConference["title"]) \
- .arg(aConference["subtitle"]) \
- .arg(aConference["venue"]) \
- .arg(aConference["city"]) \
- .arg(QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \
- .arg(QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t()) \
- .arg(aConference["days"]) \
- .arg(-QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0))) \
- .arg(-QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
- values.append(QString(", '%1'").arg(confsList.count()>0?"0":"1"));
-
- QString query = QString("INSERT INTO CONFERENCE (title,subtitle,venue,city,start,end,days,day_change,timeslot_duration,active) VALUES (%1)").arg(values);
- QSqlQuery result (query, db);
- aConference["id"] = result.lastInsertId().toString(); // 'id' is assigned automatically
+ QSqlQuery query(db);
+ query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
+ "day_change,timeslot_duration,active) "
+ " VALUES (:title,:url,:subtitle,:venue,:city,:start,:end,:days,"
+ ":day_change,:timeslot_duration,:active)");
+ foreach (QString prop_name, (QList<QString>() << "title" << "url" << "subtitle" << "venue" << "city" << "days")) {
+ query.bindValue(QString(":") + prop_name, aConference[prop_name]);
+ }
+ query.bindValue(":start", QDateTime(QDate::fromString(aConference["start"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
+ query.bindValue(":end", QDateTime(QDate::fromString(aConference["end"],DATE_FORMAT),QTime(0,0),Qt::UTC).toTime_t());
+ query.bindValue(":day_change", -QTime::fromString(aConference["day_change"],TIME_FORMAT).secsTo(QTime(0,0)));
+ query.bindValue(":day_change", -QTime::fromString(aConference["timeslot_duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+ query.bindValue(":active", confsList.count() > 0 ? 0 : 1);
+ query.exec();
+ aConference["id"] = query.lastInsertId().toString(); // 'id' is assigned automatically
}
}
}
startDateTime.setTimeSpec(Qt::UTC);
startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT),Qt::UTC);
qDebug() << "startDateTime: " << startDateTime.toString();
- QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', ? , ? , ? , ? , ? , '%8', '%9'") \
- .arg(aEvent["conference_id"]) \
- .arg(aEvent["id"]) \
- .arg(QString::number(startDateTime.toTime_t())) \
- .arg(-QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0))) \
- .arg(trackId) \
- .arg(aEvent["type"]) \
- .arg(aEvent["language"]) \
- .arg("0") \
- .arg("0");
-
- QString query =
- QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, type, language, tag, title, subtitle, abstract, description, favourite, alarm) VALUES (%1)")
- .arg(values);
-
- qDebug() << query;
+
+ bool event_exists = false;
+ {
+ QSqlQuery check_event_query;
+ check_event_query.prepare("SELECT * FROM EVENT WHERE xid_conference = :xid_conference AND id = :id");
+ check_event_query.bindValue(":xid_conference", aEvent["conference_id"]);
+ check_event_query.bindValue(":id", aEvent["id"]);
+ if (!check_event_query.exec()) {
+ qWarning() << "check event failed, conference id:" << aEvent["xid_conference"]
+ << "event id:" << aEvent["id"]
+ << "error:" << check_event_query.lastError()
+ ;
+ return;
+ }
+ if (check_event_query.isActive() and check_event_query.isSelect() and check_event_query.next()) {
+ event_exists = true;
+ }
+ }
+
QSqlQuery result;
- result.prepare(query);
- result.bindValue(0,aEvent["tag"]);
- result.bindValue(1,aEvent["title"]);
- result.bindValue(2,aEvent["subtitle"]);
- result.bindValue(3,aEvent["abstract"]);
- result.bindValue(4,aEvent["description"]);
- result.exec();
+ if (event_exists) {
+ result.prepare("UPDATE EVENT SET"
+ " start = :start"
+ ", duration = :duration"
+ ", xid_track = :xid_track"
+ ", type = :type"
+ ", language = :language"
+ ", tag = :tag"
+ ", title = :title"
+ ", subtitle = :subtitle"
+ ", abstract = :abstract"
+ ", description = :description"
+ " WHERE id = :id AND xid_conference = :xid_conference");
+ } else {
+ result.prepare("INSERT INTO EVENT "
+ " (xid_conference, id, start, duration, xid_track, type, "
+ " language, tag, title, subtitle, abstract, description) "
+ " VALUES (:xid_conference, :id, :start, :duration, :xid_track, :type, "
+ ":language, :tag, :title, :subtitle, :abstract, :description)");
+ }
+ result.bindValue(":xid_conference", aEvent["conference_id"]);
+ result.bindValue(":start", QString::number(startDateTime.toTime_t()));
+ result.bindValue(":duration", -QTime::fromString(aEvent["duration"],TIME_FORMAT).secsTo(QTime(0,0)));
+ result.bindValue(":xid_track", trackId);
+ static const QList<QString> props = QList<QString>()
+ << "id" << "type" << "language" << "tag" << "title" << "subtitle" << "abstract" << "description";
+ foreach (QString prop_name, props) {
+ result.bindValue(QString(":") + prop_name, aEvent[prop_name]);
+ }
+ if (!result.exec()) {
+ qWarning() << "event insert/update failed:" << result.lastError();
+ }
}
}
}
}
-bool SqlEngine::createTables(QSqlDatabase &aDatabase)
-{
- bool result = aDatabase.open();
-
- if (aDatabase.isValid() && aDatabase.isOpen())
- {
- QSqlQuery query(aDatabase);
-
- query.exec("CREATE TABLE CONFERENCE ( "
- "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
- "title VARCHAR UNIQUE NOT NULL, "
- "subtitle VARCHAR, "
- "venue VARCHAR, "
- "city VARCHAR NOT NULL, "
- "start INTEGER NOT NULL, "
- "end INTEGER NOT NULL, "
- "days INTEGER, "
- "day_change INTEGER, "
- "timeslot_duration INTEGER, "
- "active INTEGER DEFAULT 0);");
-
- query.exec("CREATE TABLE TRACK ( "
- "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
- "name VARCHAR UNIQUE NOT NULL );");
-
- query.exec("CREATE TABLE ROOM ( "
- "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
- "name VARCHAR UNIQUE NOT NULL, "
- "picture VARCHAR NOT NULL);");
-
- query.exec("CREATE TABLE PERSON ( "
- "id INTEGER PRIMARY KEY NOT NULL, "
- "name VARCHAR UNIQUE NOT NULL);");
-
- query.exec("CREATE TABLE EVENT ( "
- "xid_conference INTEGER NOT NULL, "
- "id INTEGER NOT NULL, "
- "start INTEGER NOT NULL, "
- "duration INTEGER NOT NULL, "
- "xid_track INTEGER NOT NULL REFERENCES TRACK(id), "
- "type VARCHAR, "
- "language VARCHAR, "
- "tag VARCHAR,title VARCHAR NOT NULL, "
- "subtitle VARCHAR, "
- "abstract VARCHAR, "
- "description VARCHAR, "
- "favourite INTEGER DEFAULT 0, "
- "alarm INTEGER DEFAULT 0, "
- "PRIMARY KEY (xid_conference,id) ON CONFLICT REPLACE, "
- "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
- "FOREIGN KEY(xid_track) REFERENCES TRACK(id));");
-
- query.exec("CREATE TABLE EVENT_PERSON ( "
- "xid_conference INTEGER NOT NULL, "
- "xid_event INTEGER NOT NULL, "
- "xid_person INTEGER NOT NULL, "
- "UNIQUE ( xid_conference, xid_event, xid_person ) ON CONFLICT REPLACE, "
- "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
- "FOREIGN KEY(xid_event) REFERENCES EVENT(id), "
- "FOREIGN KEY(xid_person) REFERENCES PERSON(id));");
-
- query.exec("CREATE TABLE EVENT_ROOM ( "
- "xid_conference INTEGER NOT NULL, "
- "xid_event INTEGER NOT NULL, "
- "xid_room INTEGER NOT NULL, "
- "UNIQUE ( xid_conference, xid_event, xid_room ) ON CONFLICT REPLACE, "
- "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
- "FOREIGN KEY(xid_event) REFERENCES EVENT(id), "
- "FOREIGN KEY(xid_room) REFERENCES ROOM(id));");
-
- query.exec("CREATE TABLE LINK ( "
- "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
- "xid_conference INTEGER NOT NULL, "
- "xid_event INTEGER NOT NULL, "
- "name VARCHAR, "
- "url VARCHAR NOT NULL, "
- "UNIQUE ( xid_conference, xid_event, url ) ON CONFLICT REPLACE, "
- "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
- "FOREIGN KEY(xid_event) REFERENCES EVENT(id));");
- }
- else
- {
- //LOG_WARNING("Database is not opened");
- }
-
- return result;
-}
-
int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
{
QSqlDatabase db = QSqlDatabase::database();
return 1;
}
+bool SqlEngine::beginTransaction()
+{
+ QSqlDatabase db = QSqlDatabase::database();
+
+ return execQuery(db, "BEGIN IMMEDIATE TRANSACTION");
+}
+
+bool SqlEngine::commitTransaction()
+{
+ QSqlDatabase db = QSqlDatabase::database();
+
+ return execQuery(db, "COMMIT");
+}
+
+void SqlEngine::deleteConference(int id)
+{
+ QSqlDatabase db = QSqlDatabase::database();
+
+ if ( !db.isValid() || !db.isOpen()) {
+ return;
+ }
+
+ beginTransaction();
+
+ QHash<QString, QVariant> params;
+ params["xid_conference"] = id;
+ execQueryWithParameter(db, "DELETE FROM LINK WHERE xid_conference = :xid_conference", params);
+ execQueryWithParameter(db, "DELETE FROM EVENT_ROOM WHERE xid_conference = :xid_conference", params);
+ execQueryWithParameter(db, "DELETE FROM EVENT_PERSON WHERE xid_conference = :xid_conference", params);
+ execQueryWithParameter(db, "DELETE FROM EVENT WHERE xid_conference = :xid_conference", params);
+ execQueryWithParameter(db, "DELETE FROM CONFERENCE WHERE id = :xid_conference", params);
+ execQuery(db, "DELETE FROM ROOM WHERE NOT EXISTS(SELECT * FROM EVENT_ROOM WHERE xid_room = ROOM.id)");
+ execQuery(db, "DELETE FROM PERSON WHERE NOT EXISTS(SELECT * FROM EVENT_PERSON WHERE xid_person = PERSON.id)");
+
+ commitTransaction();
+}
+
bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
{
//qDebug() << "\nSQL: " << aQuery;
}
}
+bool SqlEngine::execQueryWithParameter(QSqlDatabase &aDatabase, const QString &aQuery, const QHash<QString, QVariant>& params)
+{
+ qDebug() << "SQL:" << aQuery << "params:" << params;
+
+ QSqlQuery sqlQuery(aDatabase);
+ sqlQuery.prepare(aQuery);
+ foreach (QString param_key, params.keys()) {
+ sqlQuery.bindValue(param_key, params[param_key]);
+ }
+ if( !sqlQuery.exec() ){
+ qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
+ return false;
+ }
+ else{
+ //qDebug() << "SQL OK.\n";
+ return true;
+ }
+}
+
+void SqlEngine::checkConferenceMap(QSqlDatabase &aDatabase)
+{
+ QSqlQuery sqlQuery(aDatabase);
+ sqlQuery.prepare("SELECT map FROM conference");
+ if (!sqlQuery.exec()) {
+ qWarning() << "column conference.map is missing; adding";
+ execQuery(aDatabase, "ALTER TABLE conference ADD COLUMN map VARCHAR")
+ and execQuery(aDatabase, "UPDATE conference SET map = ':/maps/campus.png' WHERE title = 'FOSDEM 2010'");
+ }
+}
ToastFreeware / debian / fosdem-schedule.git / blobdiff