#include "event.h"
-// 'event' record is splitted into two separate tables 'event' and 'virtual_event'
-// for the FTS (Full-Text-Search) support and so, it is necessary to provide/use
-// two table names + corresponding parameters/methods, see bellow
-QString const Event::sTable1Name = QString("event");
-QString const Event::sTable2Name = QString("virtual_event");
-int const Event::sTable1ColCount = 9; // see 'toRecord()' for more details
-int const Event::sTable2ColCount = 5; // see 'toRecord()' for more details
+QString const Event::sTableName = QString("event");
QSqlRecord const Event::sColumns = Event::toRecord(QList<QSqlField>()
- /* 'columns from Table 1 */
<< QSqlField("id", QVariant::Int)
<< QSqlField("xid_conference", QVariant::Int)
<< QSqlField("start", QVariant::DateTime)
<< QSqlField("language", QVariant::String)
<< QSqlField("favourite", QVariant::Bool)
<< QSqlField("alarm", QVariant::Bool)
- /* 'columns' from Table2 */
<< QSqlField("tag", QVariant::String)
<< QSqlField("title", QVariant::String)
<< QSqlField("subtitle", QVariant::String)
Event Event::getById(int id, int conferenceId)
{
+
QSqlQuery query;
- query.prepare(
- selectQueryJoin2T("id")
- + QString("WHERE %1.id = :id AND %1.xid_conference = :conf").arg(sTable1Name));
+ query.prepare(selectQuery() + "WHERE id = :id AND xid_conference = :conf");
query.bindValue(":id", id);
query.bindValue(":conf", conferenceId);
-
return loadOne(query);
}
QList<Event> Event::getByDate(const QDate& date, int conferenceId, QString orderBy)
{
QSqlQuery query;
- query.prepare(
- selectQueryJoin2T("id")
- + QString("WHERE %1.xid_conference = :conf AND %1.start >= :start AND %1.start < :end ORDER BY %1.%2").arg(sTable1Name).arg(orderBy));
+ query.prepare(selectQuery() + QString("WHERE xid_conference = :conf AND start >= :start AND start < :end ORDER BY %1").arg(orderBy));
query.bindValue(":conf", conferenceId);
query.bindValue(":start", convertToDb(date, QVariant::DateTime));
query.bindValue(":end", convertToDb(date.addDays(1), QVariant::DateTime));
QList<Event> Event::getFavByDate(const QDate& date, int conferenceId)
{
QSqlQuery query;
- query.prepare(
- selectQueryJoin2T("id")
- + QString("WHERE %1.xid_conference = :conf AND %1.start >= :start AND %1.start < :end AND %1.favourite = 1 ORDER BY %1.start").arg(sTable1Name));
+ query.prepare(selectQuery() + QString("WHERE xid_conference = :conf AND start >= :start AND start < :end AND favourite = 1 ORDER BY start"));
query.bindValue(":conf", conferenceId);
query.bindValue(":start", convertToDb(date, QVariant::DateTime));
query.bindValue(":end", convertToDb(date.addDays(1), QVariant::DateTime));
QList<Event> Event::getSearchResultByDate(const QDate& date, int conferenceId, QString orderBy)
{
- QString strQuery = QString("SELECT %1 FROM EVENT INNER JOIN VIRTUAL_EVENT USING (xid_conference, id) "
- "INNER JOIN SEARCH_EVENT USING (xid_conference, id) ").arg( columnsForSelectJoin2T() );
- strQuery += QString(
- "WHERE %1.xid_conference = :conf AND %1.start >= :start AND %1.start < :end ORDER BY %1.%2").arg(sTable1Name, orderBy);
+ QString strQuery = QString("SELECT %1 FROM EVENT INNER JOIN SEARCH_EVENT USING (xid_conference, id) ").arg(columnsForSelect());
+ strQuery += QString("WHERE xid_conference = :conf AND start >= :start AND start < :end ORDER BY %1").arg(orderBy);
qDebug() << strQuery;
QSqlQuery query;
query.prepare( strQuery );
{
public:
static const QSqlRecord sColumns;
- //static QString const sTableName;
- static const QString sTable1Name;
- static const QString sTable2Name;
- static const int sTable1ColCount;
- static const int sTable2ColCount;
+ static QString const sTableName;
public:
static Event getById(int id, int conferenceId);
static QList<Event> getByDate(const QDate & date, int conferenceId, QString orderBy);
static QList<Event> getFavByDate(const QDate & date, int conferenceId); // get Favourities by Date
static QList<Event> getSearchResultByDate(const QDate& date, int conferenceId, QString orderBy);
public:
- // Table 1
int id() const { return value("id").toInt(); }
int conferenceId() const { return value("xid_conference").toInt(); }
QDateTime start() const { return value("start").toDateTime(); }
bool isFavourite() const { return value("favourite").toBool(); }
bool hasAlarm() const { return value("alarm").toBool(); }
bool hasTimeConflict() const { return true; /*return value("warning").toBool()*/; } //TODO
- // Table 2 : virtual table for FTS (Full Text Search)
QString tag() const { return value("tag").toString(); }
QString title() const { return value("title").toString(); }
QString subtitle() const { return value("subtitle").toString(); }
QString room() const;
QStringList persons() const;
- // Table 1
void setId(int id) { setValue("id", id); }
void setConferenceId(int conferenceId) { setValue("xid_conference", conferenceId); }
void setStart(const QDateTime & start) { setValue("start", start); }
void setLanguage(const QString & language) { setValue("language", language); }
void setFavourite(bool favourite) { setValue("favourite", (int)((favourite))); }
void setHasAlarm(bool alarm) { setValue("alarm", (int)((alarm))); }
- // Table 2 : virtual table for FTS (Full Text Search)
void setTag(const QString& tag) { setValue("tag", tag); }
void setTitle(const QString& title) { setValue("title", title); }
void setSubtitle(const QString& subtitle) { setValue("subtitle", subtitle); }
#include <QDateTime>
#include <QDebug>
-// INFO:
-// all record items/columns may be defined in one table (1.), or
-// they can be splitted in two separate tables (2.) (eg. for FTS support)
-// 1.) you have to define "static QString const sTableName"
-// 2.) you have to define two table names:
-// "static QString const sTable1Name"
-// "static QString const sTable2Name"
-// and since all record items/columns are handled by one QSqlRecord,
-// you have to also define number of columns that belongs to table 1 (1.), and table 2 (2.)
-// 1.) "static int const sTable1ColCount"
-// 2.) "static int const sTable2ColCount"
-// there are also defined auxiliary methods for 1-Table/2-Tables approach, see bellow
-
class OrmException
{
};
static QString columnsForSelect(const QString& prefix = QString());
static QString selectQuery();
static QString updateQuery();
- // record items/columns are stored in two tables
- static QString columnsForSelectJoin2T(); // for joining two tables
- static QString selectQueryJoin2T(const QString &key); // for joining two tables
static QVariant convertToC(QVariant value, QVariant::Type colType);
static QVariant convertToDb(QVariant value, QVariant::Type colType);
return prefixedColumns.join(",");
}
-template <typename T>
-QString OrmRecord<T>::columnsForSelectJoin2T()
-{
- Q_ASSERT((T::sTable1ColCount+T::sTable2ColCount) == T::sColumns.count());
-
- QStringList prefixedColumns;
- for (int i=0; i<T::sTable1ColCount; i++)
- {
- prefixedColumns.append(QString("%1.%2").arg(T::sTable1Name, T::sColumns.field(i).name()));
- }
- for (int j=0; j<T::sTable2ColCount; j++)
- {
- prefixedColumns.append(QString("%1.%2").arg(T::sTable2Name, T::sColumns.field(T::sTable1ColCount+j).name()));
- }
- return prefixedColumns.join(",");
-}
-
template <typename T>
QString OrmRecord<T>::selectQuery()
{
return QString("SELECT %1 FROM %2 ").arg(columnsForSelect(), T::sTableName);
}
-
-template <typename T>
-QString OrmRecord<T>::selectQueryJoin2T(const QString &key)
-{
- return QString("SELECT %1 FROM %2 INNER JOIN %3 ON %4.%5 = %6.%7 ").arg(
- columnsForSelectJoin2T(),
- T::sTable1Name,
- T::sTable2Name,
- T::sTable1Name,
- key,
- T::sTable2Name,
- key);
-}
-
template <typename T>
QString OrmRecord<T>::updateQuery()
{
- return QString("UPDATE %1 ").arg(T::sTable1Name);
+ return QString("UPDATE %1 ").arg(T::sTableName);
}
template <typename T>
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("0") \
.arg("0");
- QString query = QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, 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"]);
+ qDebug() << result.lastQuery();
+ result.exec();
}
}
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))");
-#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 , \
ToastFreeware / toast / confclerk.git / commitdiff