Add .pro.user.* to svn:ignore and remove it in the release target.

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

diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp
index 419b3e05823ff8f83693c0e33d579ab68d5923a6..4212e02b8904c5891915916922dba4b1b399b1dd 100644 (file)
--- a/src/sql/sqlengine.cpp
+++ b/src/sql/sqlengine.cpp
@@ -1,6 +1,6 @@
 /*
  * Copyright (C) 2010 Ixonos Plc.
- * Copyright (C) 2011 Philipp Spitzer, gregor herrmann
+ * Copyright (C) 2011-2012 Philipp Spitzer, gregor herrmann, Stefan Stahl
  *
  * This file is part of ConfClerk.
  *
@@ -58,8 +58,10 @@ QString SqlEngine::login(const QString &aDatabaseType, const QString &aDatabaseN
         file.open(QIODevice::ReadOnly | QIODevice::Text);
         QString allSqlStatements = file.readAll();
         foreach(QString sql, allSqlStatements.split(";")) {
+            if (sql.trimmed().length() == 0)     // do not execute empty queries like the last character from create_tables.sql
+                continue;
             QSqlQuery query(database);
-            if (!query.exec(sql)) qDebug() << "Could not execute query" << query.lastError();
+            if (!query.exec(sql)) qDebug() << "Could not execute query '" << sql << "' error:" << query.lastError();
         }
     }
     else
@@ -109,6 +111,11 @@ void SqlEngine::addConferenceToDB(QHash<QString,QString> &aConference)
 
         if(!confId) // conference 'aConference' isn't in the table => insert
         {
+            // HACK
+            // When city is empty, assign a dummy value. We probably want to find a way to change the database scheme ...
+            // cf. #32
+            if (aConference["city"].isEmpty()) aConference["city"] = "n/a";
+
             QSqlQuery query(db);
             query.prepare("INSERT INTO CONFERENCE (title,url,subtitle,venue,city,start,end,days,"
                                                     "day_change,timeslot_duration,active) "
@@ -261,6 +268,14 @@ void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
             aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
             //LOG_AUTOTEST(query);
         }
+        
+        // remove previous conference/room records; room names might have changed
+        query = QSqlQuery(db);
+        query.prepare("DELETE FROM EVENT_ROOM WHERE xid_conference=:conference_id AND xid_event=:event_id");
+        query.bindValue(":conference_id", aRoom["conference_id"]);
+        query.bindValue(":event_id", aRoom["event_id"]);
+        if (!query.exec()) qDebug() << "Could not execute SELECT * FROM EVENT_ROOM' query:" << query.lastError();
+        // and insert new ones
         query = QSqlQuery(db);
         query.prepare("INSERT INTO EVENT_ROOM (xid_conference,xid_event,xid_room) VALUES (:conference_id, :event_id, :room_id)");
         query.bindValue(":conference_id", aRoom["conference_id"]);
@@ -304,32 +319,35 @@ int SqlEngine::searchEvent(int aConferenceId, const QHash<QString,QString> &aCol
     QString sql = QString("INSERT INTO SEARCH_EVENT ( xid_conference, id ) "
                 "SELECT DISTINCT EVENT.xid_conference, EVENT.id FROM EVENT ");
     if( aColumns.contains("ROOM") ){
-        sql += "INNER JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
-        sql += "INNER JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
+        sql += "LEFT JOIN EVENT_ROOM ON ( EVENT.xid_conference = EVENT_ROOM.xid_conference AND EVENT.id = EVENT_ROOM.xid_event ) ";
+        sql += "LEFT JOIN ROOM ON ( EVENT_ROOM.xid_room = ROOM.id ) ";
     }
     if( aColumns.contains("PERSON") ){
-        sql += "INNER JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
-        sql += "INNER JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
+        sql += "LEFT JOIN EVENT_PERSON ON ( EVENT.xid_conference = EVENT_PERSON.xid_conference AND EVENT.id = EVENT_PERSON.xid_event ) ";
+        sql += "LEFT JOIN PERSON ON ( EVENT_PERSON.xid_person = PERSON.id ) ";
     }
     sql += QString("WHERE EVENT.xid_conference = %1 AND (").arg( aConferenceId );
 
-    QStringList searchKeywords = aKeyword.split(QRegExp("\\s+"));
-    foreach (QString table, aColumns.uniqueKeys()){
-        foreach (QString column, aColumns.values(table)){
-            for (int i=0; i < searchKeywords.count(); i++){
-                 sql += QString("%1.%2 LIKE '\%' || :%1%2%3 || '\%' OR ").arg(table).arg(column).arg(i);
+    QStringList searchKeywords = aKeyword.trimmed().split(QRegExp("\\s+"));
+    QStringList whereAnd;
+    for (int i=0; i < searchKeywords.count(); i++) {
+        QStringList whereOr;
+        foreach (QString table, aColumns.uniqueKeys()) {
+            foreach (QString column, aColumns.values(table)){
+                 whereOr.append(QString("%1.%2 LIKE '\%' || :%1%2%3 || '\%'").arg(table).arg(column).arg(i));
             }
         }
+        whereAnd.append(whereOr.join(" OR "));
     }
-    sql.chop( QString(" OR ").length() );
+    sql += whereAnd.join(") AND (");
     sql += QString(")");
 
     QSqlQuery query(db);
     query.prepare(sql);
-    foreach (QString table, aColumns.uniqueKeys()){
-        foreach (QString column, aColumns.values(table)){
-            for (int i = 0; i != searchKeywords.size(); ++i) {
-                QString keyword = searchKeywords[i];
+    for (int i = 0; i != searchKeywords.size(); ++i) {
+        QString keyword = searchKeywords[i];
+        foreach (QString table, aColumns.uniqueKeys()) {
+            foreach (QString column, aColumns.values(table)) {
                 query.bindValue(QString(":%1%2%3").arg(table).arg(column).arg(i), keyword );
             }
         }