search update

[toast/confclerk.git] / src / sql / sqlengine.cpp

diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp
index 335359a0264cf82a4a0b5d7e53d1a7bf925673ac..b48e49dc950c57b1fddeb10b2ff2b248936964a4 100644 (file)
--- a/src/sql/sqlengine.cpp
+++ b/src/sql/sqlengine.cpp
@@ -232,7 +232,7 @@ bool SqlEngine::createTables(QSqlDatabase &aDatabase)
 
         query.exec("CREATE TABLE CONFERENCE ( \
             id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \
 
         query.exec("CREATE TABLE CONFERENCE ( \
             id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \

-            title VARCHAR NOT NULL , \
+            title VARCHAR UNIQUE NOT NULL , \

             subtitle VARCHAR, \
             venue VARCHAR, \
             city VARCHAR NOT NULL , \
             subtitle VARCHAR, \
             venue VARCHAR, \
             city VARCHAR NOT NULL , \


@@ -248,12 +248,12 @@ bool SqlEngine::createTables(QSqlDatabase &aDatabase)
 
         query.exec("CREATE TABLE ROOM ( \
             id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \
 
         query.exec("CREATE TABLE ROOM ( \
             id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \

-            name VARCHAR NOT NULL , \
+            name VARCHAR UNIQUE NOT NULL , \

             picture VARCHAR NOT NULL)");
 
         query.exec("CREATE TABLE PERSON ( \
             id INTEGER PRIMARY KEY  NOT NULL , \
             picture VARCHAR NOT NULL)");
 
         query.exec("CREATE TABLE PERSON ( \
             id INTEGER PRIMARY KEY  NOT NULL , \

-            name VARCHAR NOT NULL)");
+            name VARCHAR UNIQUE NOT NULL)");

 
         query.exec("CREATE TABLE EVENT ( \
             xid_conference INTEGER  NOT NULL, \
 
         query.exec("CREATE TABLE EVENT ( \
             xid_conference INTEGER  NOT NULL, \


@@ -277,14 +277,16 @@ bool SqlEngine::createTables(QSqlDatabase &aDatabase)
             xid_conference INTEGER NOT NULL , \
             xid_event INTEGER NOT NULL , \
             xid_person INTEGER NOT NULL, \
             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_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, \
             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_conference) REFERENCES CONFERENCE(id), \
+            FOREIGN KEY(xid_event) REFERENCES EVENT(id), \

             FOREIGN KEY(xid_room) REFERENCES ROOM(id))");
 
         query.exec("CREATE TABLE LINK ( \
             FOREIGN KEY(xid_room) REFERENCES ROOM(id))");
 
         query.exec("CREATE TABLE LINK ( \


@@ -293,7 +295,8 @@ bool SqlEngine::createTables(QSqlDatabase &aDatabase)
             xid_event INTEGER NOT NULL, \
             name VARCHAR, \
             url VARCHAR NOT NULL, \
             xid_event INTEGER NOT NULL, \
             name VARCHAR, \
             url VARCHAR NOT NULL, \

-            FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id))");
+            FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id), \
+            FOREIGN KEY(xid_event) REFERENCES EVENT(id)");

     }
     else
     {
     }
     else
     {


@@ -314,7 +317,7 @@ int SqlEngine::searchEvent(int aConferenceId, const QList<QString> &aColumns, co
     // DROP
     execQuery( db, "DROP TABLE IF EXISTS SEARCH_EVENT;");
     // CREATE
     // 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 "
     // INSERT
     QString query = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id) "
                 "SELECT xid_conference, id FROM EVENT "