Changeset 7d7659d for src


Ignore:
Timestamp:
01/21/10 14:24:08 (13 years ago)
Author:
pavelpa <pavelpa@…>
Branches:
master, qt5
Children:
06570e9
Parents:
d8d5bd2
Message:

combined EVENT and VIRTUAL_EVENT => 'EVENT' now

  • Maemo sqlite doesn't support Full-Text-Search
Location:
src
Files:
4 edited

Legend:

Unmodified
Added
Removed
  • src/mvc/event.cpp

    rd8d5bd2 r7d7659d  
    11#include "event.h"
    22
    3 // 'event' record is splitted into two separate tables 'event' and 'virtual_event'
    4 // for the FTS (Full-Text-Search) support and so, it is necessary to provide/use
    5 // two table names + corresponding parameters/methods, see bellow
    6 QString const Event::sTable1Name = QString("event");
    7 QString const Event::sTable2Name = QString("virtual_event");
    8 int const Event::sTable1ColCount = 9; // see 'toRecord()' for more details
    9 int const Event::sTable2ColCount = 5; // see 'toRecord()' for more details
     3QString const Event::sTableName = QString("event");
    104
    115QSqlRecord const Event::sColumns = Event::toRecord(QList<QSqlField>()
    12     /* 'columns from Table 1 */
    136    << QSqlField("id", QVariant::Int)
    147    << QSqlField("xid_conference", QVariant::Int)
     
    2013    << QSqlField("favourite", QVariant::Bool)
    2114    << QSqlField("alarm", QVariant::Bool)
    22     /* 'columns' from Table2 */
    2315    << QSqlField("tag", QVariant::String)
    2416    << QSqlField("title", QVariant::String)
     
    3022Event Event::getById(int id, int conferenceId)
    3123{
     24
    3225    QSqlQuery query;
    33     query.prepare(
    34             selectQueryJoin2T("id")
    35             + QString("WHERE %1.id = :id AND %1.xid_conference = :conf").arg(sTable1Name));
     26    query.prepare(selectQuery() + "WHERE id = :id AND xid_conference = :conf");
    3627    query.bindValue(":id", id);
    3728    query.bindValue(":conf", conferenceId);
    38 
    3929    return loadOne(query);
    4030}
     
    4333{
    4434    QSqlQuery query;
    45     query.prepare(
    46             selectQueryJoin2T("id")
    47             + QString("WHERE %1.xid_conference = :conf AND %1.start >= :start AND %1.start < :end ORDER BY %1.%2").arg(sTable1Name).arg(orderBy));
     35    query.prepare(selectQuery() + QString("WHERE xid_conference = :conf AND start >= :start AND start < :end ORDER BY %1").arg(orderBy));
    4836    query.bindValue(":conf", conferenceId);
    4937    query.bindValue(":start", convertToDb(date, QVariant::DateTime));
     
    5644{
    5745    QSqlQuery query;
    58     query.prepare(
    59             selectQueryJoin2T("id")
    60             + QString("WHERE %1.xid_conference = :conf AND %1.start >= :start AND %1.start < :end AND %1.favourite = 1 ORDER BY %1.start").arg(sTable1Name));
     46    query.prepare(selectQuery() + QString("WHERE xid_conference = :conf AND start >= :start AND start < :end AND favourite = 1 ORDER BY start"));
    6147    query.bindValue(":conf", conferenceId);
    6248    query.bindValue(":start", convertToDb(date, QVariant::DateTime));
     
    117103{
    118104
    119     QString strQuery = QString("SELECT %1 FROM EVENT INNER JOIN VIRTUAL_EVENT USING (xid_conference, id) "
    120         "INNER JOIN SEARCH_EVENT USING (xid_conference, id) ").arg( columnsForSelectJoin2T() );
    121     strQuery += QString(
    122         "WHERE %1.xid_conference = :conf AND %1.start >= :start AND %1.start < :end ORDER BY %1.%2").arg(sTable1Name, orderBy);
     105    QString strQuery = QString("SELECT %1 FROM EVENT INNER JOIN SEARCH_EVENT USING (xid_conference, id) ").arg(columnsForSelect());
     106    strQuery += QString("WHERE xid_conference = :conf AND start >= :start AND start < :end ORDER BY %1").arg(orderBy);
    123107    qDebug() << strQuery;
    124108    QSqlQuery query;
  • src/mvc/event.h

    rd8d5bd2 r7d7659d  
    1919public:
    2020    static const QSqlRecord sColumns;
    21     //static QString const sTableName;
    22     static const QString sTable1Name;
    23     static const QString sTable2Name;
    24     static const int sTable1ColCount;
    25     static const int sTable2ColCount;
     21    static QString const sTableName;
    2622public:
    2723    static Event getById(int id, int conferenceId);
     
    3026    static QList<Event> getSearchResultByDate(const QDate& date, int conferenceId, QString orderBy);
    3127public:
    32     // Table 1
    3328    int id() const { return value("id").toInt(); }
    3429    int conferenceId() const { return value("xid_conference").toInt(); }
     
    4136    bool hasAlarm() const { return value("alarm").toBool(); }
    4237    bool hasTimeConflict() const { return true; /*return value("warning").toBool()*/; } //TODO
    43     // Table 2 : virtual table for FTS (Full Text Search)
    4438    QString tag() const { return value("tag").toString(); }
    4539    QString title() const { return value("title").toString(); }
     
    5145    QStringList persons() const;
    5246
    53     // Table 1
    5447    void setId(int id) { setValue("id", id); }
    5548    void setConferenceId(int conferenceId) { setValue("xid_conference", conferenceId); }
     
    6154    void setFavourite(bool favourite) { setValue("favourite", (int)((favourite))); }
    6255    void setHasAlarm(bool alarm) { setValue("alarm", (int)((alarm))); }
    63     // Table 2 : virtual table for FTS (Full Text Search)
    6456    void setTag(const QString& tag) { setValue("tag", tag); }
    6557    void setTitle(const QString& title) { setValue("title", title); }
  • src/orm/ormrecord.h

    rd8d5bd2 r7d7659d  
    1010#include <QDebug>
    1111
    12 // INFO:
    13 // all record items/columns may be defined in one table (1.), or
    14 // they can be splitted in two separate tables (2.) (eg. for FTS support)
    15 // 1.) you have to define "static QString const sTableName"
    16 // 2.) you have to define two table names:
    17 //     "static QString const sTable1Name"
    18 //     "static QString const sTable2Name"
    19 // and since all record items/columns are handled by one QSqlRecord,
    20 // you have to also define number of columns that belongs to table 1 (1.), and table 2 (2.)
    21 // 1.) "static int const sTable1ColCount"
    22 // 2.) "static int const sTable2ColCount"
    23 // there are also defined auxiliary methods for 1-Table/2-Tables approach, see bellow
    24 
    2512class OrmException
    2613{
     
    6249    static QString selectQuery();
    6350    static QString updateQuery();
    64     // record items/columns are stored in two tables
    65     static QString columnsForSelectJoin2T(); // for joining two tables
    66     static QString selectQueryJoin2T(const QString &key); // for joining two tables
    6751
    6852    static QVariant convertToC(QVariant value, QVariant::Type colType);
     
    172156
    173157template <typename T>
    174 QString OrmRecord<T>::columnsForSelectJoin2T()
    175 {
    176     Q_ASSERT((T::sTable1ColCount+T::sTable2ColCount) == T::sColumns.count());
    177 
    178     QStringList prefixedColumns;
    179     for (int i=0; i<T::sTable1ColCount; i++)
    180     {
    181         prefixedColumns.append(QString("%1.%2").arg(T::sTable1Name, T::sColumns.field(i).name()));
    182     }
    183     for (int j=0; j<T::sTable2ColCount; j++)
    184     {
    185         prefixedColumns.append(QString("%1.%2").arg(T::sTable2Name, T::sColumns.field(T::sTable1ColCount+j).name()));
    186     }
    187     return prefixedColumns.join(",");
    188 }
    189 
    190 template <typename T>
    191158QString OrmRecord<T>::selectQuery()
    192159{
     
    194161}
    195162
    196 
    197 template <typename T>
    198 QString OrmRecord<T>::selectQueryJoin2T(const QString &key)
    199 {
    200     return QString("SELECT %1 FROM %2 INNER JOIN %3 ON %4.%5 = %6.%7 ").arg(
    201             columnsForSelectJoin2T(),
    202             T::sTable1Name,
    203             T::sTable2Name,
    204             T::sTable1Name,
    205             key,
    206             T::sTable2Name,
    207             key);
    208 }
    209 
    210163template <typename T>
    211164QString OrmRecord<T>::updateQuery()
    212165{
    213     return QString("UPDATE %1 ").arg(T::sTable1Name);
     166    return QString("UPDATE %1 ").arg(T::sTableName);
    214167}
    215168
  • src/sql/sqlengine.cpp

    rd8d5bd2 r7d7659d  
    105105            /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/
    106106        }
    107         // The items of the Event are divided into the two tables EVENT and VIRTUAL_EVENT
    108         // VIRTUAL_EVENT is for Full-Text-Serach Support
    109107        QDateTime startDateTime = QDateTime(QDate::fromString(aEvent["date"],DATE_FORMAT),QTime::fromString(aEvent["start"],TIME_FORMAT));
    110         QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', '%8', '%9'") \
     108        QString values = QString("'%1', '%2', '%3', '%4', '%5', '%6', '%7', ? , ? , ? , ? , ? , '%8', '%9'") \
    111109                         .arg(aEvent["conference_id"]) \
    112110                         .arg(aEvent["id"]) \
     
    119117                         .arg("0");
    120118
    121         QString query = QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, type, language, favourite, alarm) VALUES (%1)").arg(values);
    122         QSqlQuery result (query, db);
    123         //LOG_AUTOTEST(query);
    124 
    125         // add some(text related) Event's items to VIRTUAL_EVENT table
    126         QString values2 = QString("'%1', '%2', '%3', ? , ? , ? , ? ") \
    127                           .arg(aEvent["conference_id"]) \
    128                           .arg(aEvent["id"]) \
    129                           .arg(aEvent["tag"]);
    130 
    131         QString query2 = QString("INSERT INTO VIRTUAL_EVENT (xid_conference, id, tag, title, subtitle, abstract, description) VALUES (%1)").arg(values2);
    132 
    133         QSqlQuery result2;
    134         result2.prepare(query2);
    135         result2.bindValue(0,aEvent["title"]);
    136         result2.bindValue(1,aEvent["subtitle"]);
    137         result2.bindValue(2,aEvent["abstract"]);
    138         result2.bindValue(3,aEvent["description"]);
    139         result2.exec();
    140         //LOG_AUTOTEST(query2);
     119        QString query =
     120            QString("INSERT INTO EVENT (xid_conference, id, start, duration, xid_track, type, language, tag, title, subtitle, abstract, description, favourite, alarm) VALUES (%1)")
     121            .arg(values);
     122
     123        QSqlQuery result;
     124        result.prepare(query);
     125        result.bindValue(0,aEvent["tag"]);
     126        result.bindValue(1,aEvent["title"]);
     127        result.bindValue(2,aEvent["subtitle"]);
     128        result.bindValue(3,aEvent["abstract"]);
     129        result.bindValue(4,aEvent["description"]);
     130        qDebug() << result.lastQuery();
     131        result.exec();
    141132    }
    142133}
     
    250241            type VARCHAR, \
    251242            language VARCHAR, \
     243            tag VARCHAR,title VARCHAR NOT NULL , \
     244            subtitle VARCHAR, \
     245            abstract VARCHAR, \
     246            description VARCHAR, \
    252247            favourite INTEGER DEFAULT 0, \
    253248            alarm INTEGER DEFAULT 0, \
     
    255250            FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id) \
    256251            FOREIGN KEY(xid_track) REFERENCES TRACK(id))");
    257 
    258 #ifdef MAEMO
    259         // TBD: MAEMO Virtual tables compatibility (waiting for Marek).
    260         // MAEMO sqlite Qt driver doesn't provide FTS support by default - use the following HACK
    261         query.exec("CREATE TABLE VIRTUAL_EVENT ( \
    262             xid_conference INTEGER  NOT NULL, \
    263             id INTEGER NOT NULL , \
    264             tag VARCHAR,title VARCHAR NOT NULL , \
    265             subtitle VARCHAR, \
    266             abstract VARCHAR, \
    267             description VARCHAR, \
    268             PRIMARY KEY (xid_conference,id))");
    269 #else
    270         query.exec("CREATE VIRTUAL TABLE VIRTUAL_EVENT using fts3 ( \
    271             xid_conference INTEGER  NOT NULL, \
    272             id INTEGER NOT NULL , \
    273             tag VARCHAR,title VARCHAR NOT NULL , \
    274             subtitle VARCHAR, \
    275             abstract VARCHAR, \
    276             description VARCHAR, \
    277             PRIMARY KEY (xid_conference,id))");
    278 #endif
    279252
    280253        query.exec("CREATE TABLE EVENT_PERSON ( \
Note: See TracChangeset for help on using the changeset viewer.