Changeset 7d7659d
- Timestamp:
- 01/21/10 14:24:08 (13 years ago)
- Branches:
- master, qt5
- Children:
- 06570e9
- Parents:
- d8d5bd2
- Location:
- src
- Files:
-
- 4 edited
Legend:
- Unmodified
- Added
- Removed
-
src/mvc/event.cpp
rd8d5bd2 r7d7659d 1 1 #include "event.h" 2 2 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 3 QString const Event::sTableName = QString("event"); 10 4 11 5 QSqlRecord const Event::sColumns = Event::toRecord(QList<QSqlField>() 12 /* 'columns from Table 1 */13 6 << QSqlField("id", QVariant::Int) 14 7 << QSqlField("xid_conference", QVariant::Int) … … 20 13 << QSqlField("favourite", QVariant::Bool) 21 14 << QSqlField("alarm", QVariant::Bool) 22 /* 'columns' from Table2 */23 15 << QSqlField("tag", QVariant::String) 24 16 << QSqlField("title", QVariant::String) … … 30 22 Event Event::getById(int id, int conferenceId) 31 23 { 24 32 25 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"); 36 27 query.bindValue(":id", id); 37 28 query.bindValue(":conf", conferenceId); 38 39 29 return loadOne(query); 40 30 } … … 43 33 { 44 34 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)); 48 36 query.bindValue(":conf", conferenceId); 49 37 query.bindValue(":start", convertToDb(date, QVariant::DateTime)); … … 56 44 { 57 45 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")); 61 47 query.bindValue(":conf", conferenceId); 62 48 query.bindValue(":start", convertToDb(date, QVariant::DateTime)); … … 117 103 { 118 104 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); 123 107 qDebug() << strQuery; 124 108 QSqlQuery query; -
src/mvc/event.h
rd8d5bd2 r7d7659d 19 19 public: 20 20 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; 26 22 public: 27 23 static Event getById(int id, int conferenceId); … … 30 26 static QList<Event> getSearchResultByDate(const QDate& date, int conferenceId, QString orderBy); 31 27 public: 32 // Table 133 28 int id() const { return value("id").toInt(); } 34 29 int conferenceId() const { return value("xid_conference").toInt(); } … … 41 36 bool hasAlarm() const { return value("alarm").toBool(); } 42 37 bool hasTimeConflict() const { return true; /*return value("warning").toBool()*/; } //TODO 43 // Table 2 : virtual table for FTS (Full Text Search)44 38 QString tag() const { return value("tag").toString(); } 45 39 QString title() const { return value("title").toString(); } … … 51 45 QStringList persons() const; 52 46 53 // Table 154 47 void setId(int id) { setValue("id", id); } 55 48 void setConferenceId(int conferenceId) { setValue("xid_conference", conferenceId); } … … 61 54 void setFavourite(bool favourite) { setValue("favourite", (int)((favourite))); } 62 55 void setHasAlarm(bool alarm) { setValue("alarm", (int)((alarm))); } 63 // Table 2 : virtual table for FTS (Full Text Search)64 56 void setTag(const QString& tag) { setValue("tag", tag); } 65 57 void setTitle(const QString& title) { setValue("title", title); } -
src/orm/ormrecord.h
rd8d5bd2 r7d7659d 10 10 #include <QDebug> 11 11 12 // INFO:13 // all record items/columns may be defined in one table (1.), or14 // 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 bellow24 25 12 class OrmException 26 13 { … … 62 49 static QString selectQuery(); 63 50 static QString updateQuery(); 64 // record items/columns are stored in two tables65 static QString columnsForSelectJoin2T(); // for joining two tables66 static QString selectQueryJoin2T(const QString &key); // for joining two tables67 51 68 52 static QVariant convertToC(QVariant value, QVariant::Type colType); … … 172 156 173 157 template <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>191 158 QString OrmRecord<T>::selectQuery() 192 159 { … … 194 161 } 195 162 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 210 163 template <typename T> 211 164 QString OrmRecord<T>::updateQuery() 212 165 { 213 return QString("UPDATE %1 ").arg(T::sTable 1Name);166 return QString("UPDATE %1 ").arg(T::sTableName); 214 167 } 215 168 -
src/sql/sqlengine.cpp
rd8d5bd2 r7d7659d 105 105 /*qDebug() << QString("DEBUG: Track %1 added to DB").arg(name);*/ 106 106 } 107 // The items of the Event are divided into the two tables EVENT and VIRTUAL_EVENT108 // VIRTUAL_EVENT is for Full-Text-Serach Support109 107 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'") \ 111 109 .arg(aEvent["conference_id"]) \ 112 110 .arg(aEvent["id"]) \ … … 119 117 .arg("0"); 120 118 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(); 141 132 } 142 133 } … … 250 241 type VARCHAR, \ 251 242 language VARCHAR, \ 243 tag VARCHAR,title VARCHAR NOT NULL , \ 244 subtitle VARCHAR, \ 245 abstract VARCHAR, \ 246 description VARCHAR, \ 252 247 favourite INTEGER DEFAULT 0, \ 253 248 alarm INTEGER DEFAULT 0, \ … … 255 250 FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id) \ 256 251 FOREIGN KEY(xid_track) REFERENCES TRACK(id))"); 257 258 #ifdef MAEMO259 // TBD: MAEMO Virtual tables compatibility (waiting for Marek).260 // MAEMO sqlite Qt driver doesn't provide FTS support by default - use the following HACK261 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 #else270 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 #endif279 252 280 253 query.exec("CREATE TABLE EVENT_PERSON ( \
Note: See TracChangeset
for help on using the changeset viewer.