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 b7cc0a17ceaae5c7fabe399ec6301cc142709b85..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,19 +268,14 @@ void SqlEngine::addRoomToDB(QHash<QString,QString> &aRoom)
             aRoom["id"]= query.lastInsertId().toString(); // 'id' is assigned automatically
             //LOG_AUTOTEST(query);
         }
-        // check if event is already there, i.e. room name changed
+        
+        // remove previous conference/room records; room names might have changed
         query = QSqlQuery(db);
-        query.prepare("SELECT * FROM EVENT_ROOM WHERE xid_conference=:conference_id AND xid_event=:event_id");
+        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();
-        if(query.next()) // event/conference exists --> delete it
-        {
-          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"]);
@@ -317,12 +319,12 @@ 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 );