#include <QDir>
#include "sqlengine.h"
#include <track.h>
+#include <conference.h>
#include <QDebug>
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())
{
- //insert event track to table
+ //insert event track to table and get track id
QString name = aEvent["track"];
Track track;
int trackId;
{
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 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 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_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, \
- 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();
// DROP
execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT;");
// CREATE
- execQuery( db, "CREATE TABLE SEARCH_EVENT ( xid_conference INTEGER NOT NULL, id INTEGER NOT NULL );");
+ 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 xid_conference, id FROM EVENT "
- "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 );
+ "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(");");
return true;
}
}
+
ToastFreeware / toast / confclerk.git / blobdiff