#include <QDir>
#include "sqlengine.h"
+#include <track.h>
+#include <conference.h>
#include <QDebug>
// - create new DB from resource, which contains empty DB with tables
result = createTables(database);
}
-
- database.open();
+ else
+ {
+ database.open();
+ }
//LOG_INFO(QString("Opening '%1' database '%2'").arg(aDatabaseType).arg(aDatabaseName));
if (db.isValid() && db.isOpen())
{
- QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9', '%10'") \
- .arg(aConference["id"]) \
- .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)));
-
- QString query = QString("INSERT INTO CONFERENCE (id,title,subtitle,venue,city,start,end,days,day_change,timeslot_duration) VALUES (%1)").arg(values);
- QSqlQuery result (query, db);
- //LOG_AUTOTEST(query);
+ int confId = 0;
+ QList<Conference> confsList = Conference::getAll();
+ if(confsList.count())
+ {
+ QListIterator<Conference> i(confsList);
+ while (i.hasNext())
+ {
+ Conference conf = i.next();
+ if( aConference["title"] == conf.title() )
+ {
+ confId = conf.id();
+ aConference["id"] = QString::number(confId);
+ break;
+ }
+ }
+ }
+
+ 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
+ }
}
}
if (db.isValid() && db.isOpen())
{
- // track/activity has to be handled as the first, since it is necessary to get
- // track ID from the ACTIVITY table, or to create new ACTIVITY record
- // and get the ID from newly created record
- QString queryExist = QString("SELECT id FROM activity WHERE name='%1'").arg(aEvent["track"]);
- QSqlQuery resultExist(queryExist,db);
- // now we have to check whether ACTIVITY record with 'name' exists or not,
- // - if it doesn't exist yet, then we have to add that record to 'ACTIVITY' table
- // and assign autoincremented 'id' to aActivity
- // - if it exists, then we need to get its 'id' and assign it to aRoom
- int actId = -1;
- if(resultExist.next()) // ACTIVITY record with 'name' already exists: we need to get its 'id'
+ //insert event track to table and get track id
+ QString name = aEvent["track"];
+ Track track;
+ int trackId;
+ try
{
- actId = resultExist.value(0).toInt();
+ track = Track::retrieveByName(name);
+ trackId = track.id();
+ /*qDebug() << QString("DEBUG: Track %1 in DB").arg(name);*/
}
- else // ACTIVITY record doesn't exist yet, need to create it
- {
- QString values = QString("'%1'").arg(aEvent["track"]);
- QString query = QString("INSERT INTO activity (name) VALUES (%1)").arg(values);
- QSqlQuery result (query, db);
- actId = result.lastInsertId().toInt(); // 'id' is assigned automatically
+ catch (OrmNoObjectException &e) {
+ track.setName(name);
+ trackId = track.insert();
+ /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/
}
-
- // The items of the Event are divided into the two tables EVENT and VIRTUAL_EVENT
- // VIRTUAL_EVENT is for Full-Text-Serach Support
QDateTime startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT));
- QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9'") \
+ 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(QString::number(actId)) \
+ .arg(trackId) \
.arg(aEvent["type"]) \
.arg(aEvent["language"]) \
.arg("0") \
.arg("0");
- QString query = QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_activity, type, language, favourite, alarm) VALUES (%1)").arg(values);
- QSqlQuery result (query, db);
- //LOG_AUTOTEST(query);
-
- // add some(text related) Event's items to VIRTUAL_EVENT table
- QString values2 = QString("'%1', '%2', '%3', ? , ? , ? , ? ") \
- .arg(aEvent["conference_id"]) \
- .arg(aEvent["id"]) \
- .arg(aEvent["tag"]);
-
- QString query2 = QString("INSERT INTO VIRTUAL_EVENT (xid_conference, id, tag, title, subtitle, abstract, description) VALUES (%1)").arg(values2);
-
- QSqlQuery result2;
- result2.prepare(query2);
- result2.bindValue(0,aEvent["title"]);
- result2.bindValue(1,aEvent["subtitle"]);
- result2.bindValue(2,aEvent["abstract"]);
- result2.bindValue(3,aEvent["description"]);
- result2.exec();
- //LOG_AUTOTEST(query2);
+ 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);
+
+ 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();
}
}
{
QSqlQuery query(aDatabase);
- query.exec("CREATE TABLE CONFERENCE ( \
- id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , \
- title VARCHAR 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)");
-
- query.exec("CREATE TABLE ACTIVITY ( \
- id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , \
- name VARCHAR NOT NULL )");
-
- query.exec("CREATE TABLE ROOM ( \
- id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , \
- name VARCHAR NOT NULL , \
- picture VARCHAR NOT NULL)");
-
- query.exec("CREATE TABLE PERSON ( \
- id INTEGER PRIMARY KEY NOT NULL , \
- name VARCHAR 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_activity INTEGER NOT NULL REFERENCES ACTIVITY(id), \
- type VARCHAR, \
- language VARCHAR, \
- favourite INTEGER DEFAULT 0, \
- alarm INTEGER DEFAULT 0, \
- PRIMARY KEY (xid_conference,id), \
- FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id) \
- FOREIGN KEY(xid_activity) REFERENCES ACTIVITY(id))");
-
-#ifdef MAEMO
- // TBD: MAEMO Virtual tables compatibility (waiting for Marek).
- // MAEMO sqlite Qt driver doesn't provide FTS support by default - use the following HACK
- query.exec("CREATE TABLE VIRTUAL_EVENT ( \
- xid_conference INTEGER NOT NULL, \
- id INTEGER NOT NULL , \
- tag VARCHAR,title VARCHAR NOT NULL , \
- subtitle VARCHAR, \
- abstract VARCHAR, \
- description VARCHAR, \
- PRIMARY KEY (xid_conference,id))");
-#else
- query.exec("CREATE VIRTUAL TABLE VIRTUAL_EVENT using fts3 ( \
- xid_conference INTEGER NOT NULL, \
- id INTEGER NOT NULL , \
- tag VARCHAR,title VARCHAR NOT NULL , \
- subtitle VARCHAR, \
- abstract VARCHAR, \
- description VARCHAR, \
- PRIMARY KEY (xid_conference,id))");
-#endif
-
- query.exec("CREATE TABLE EVENT_PERSON ( \
- xid_conference INTEGER NOT NULL , \
- xid_event INTEGER NOT NULL , \
- xid_person INTEGER NOT NULL, \
- FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, 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, \
- FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, 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, \
- FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id))");
+ 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), "
+ "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 IGNORE, "
+ "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 IGNORE, "
+ "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 EVENT_CONFLICT ( "
+ "xid_conference INTEGER NOT NULL, "
+ "xid_event INTEGER NOT NULL, "
+ "conflict_event INTEGER NOT NULL, "
+ "UNIQUE ( xid_conference, xid_event, conflict_event ) ON CONFLICT IGNORE, "
+ "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
+ "FOREIGN KEY(xid_event) REFERENCES EVENT(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 IGNORE, "
+ "FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), "
+ "FOREIGN KEY(xid_event) REFERENCES EVENT(id));");
}
else
{
return result;
}
-int SqlEngine::searchEvent(int aConferenceId, const QList<QString> &aColumns, const QString &aKeyword)
+int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aColumns, const QString &aKeyword)
{
QSqlDatabase db = QSqlDatabase::database();
if ( !db.isValid() || !db.isOpen())
return -1;
- QString query = QString(
- "DROP TABLE IF EXISTS SEARCH_EVENT;"
- "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER NOT NULL, id INTEGER NOT NULL );"
- "INSERT INTO SEARCH_EVENT ( xid_conference, id) "
- "SELECT xid_conference, id FROM EVENT AS e INNER JOIN VIRTUAL_EVENT AS ve USING (xid_conference, id) "
- "WHERE xid_conference = %1 AND (").arg( aConferenceId );
- int i = 0;
- foreach (QString str, aColumns){
- query += QString("%1 LIKE '\%%2\%' OR ").arg( aColumns.at(i++), aKeyword );
+ // DROP
+ execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT;");
+ // CREATE
+ execQuery( db, "CREATE TEMP TABLE SEARCH_EVENT ( xid_conference INTEGER NOT NULL, id INTEGER NOT NULL );");
+ // INSERT
+ QString query = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id) "
+ "SELECT EVENT.xid_conference, EVENT.id FROM EVENT ");
+ if( aColumns.contains("ROOM") ){
+ query += "INNER JOIN EVENT_ROOM ON (EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
+ query += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
+ }
+ if( aColumns.contains("PERSON") ){
+ query += "INNER JOIN EVENT_PERSON ON (EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
+ query += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
+ }
+ query += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
+
+ foreach (QString table, aColumns.uniqueKeys()){
+ foreach (QString column, aColumns.values(table)){
+ query += QString("%1.%2 LIKE '\%%3\%' OR ").arg( table, column, aKeyword );
+ }
}
query.chop( QString(" OR ").length() );
query += QString(");");
- qDebug() << "\nSQL: " << query;
+ execQuery( db, query );
- db.exec();
+ //TODO: retun number of rows from SEARCH_EVENT
+ return 1;
+}
- if( db.lastError().isValid() && db.lastError().number() != 0 ){
- qDebug() << "SQL ERR: " << db.lastError().number() << ", " << db.lastError().text();
- return 0;
+bool SqlEngine::execQuery(QSqlDatabase &aDatabase, const QString &aQuery)
+{
+ qDebug() << "\nSQL: " << aQuery;
+
+ QSqlQuery sqlQuery(aDatabase);
+ if( !sqlQuery.exec(aQuery) ){
+ qDebug() << "SQL ERR: " << sqlQuery.lastError().number() << ", " << sqlQuery.lastError().text();
+ return false;
}
else{
- qDebug() << "SQL OK.\n";
- return 1;
+ qDebug() << "SQL OK.\n";
+ return true;
}
-
}
+
ToastFreeware / toast / confclerk.git / blobdiff