From 8af1fb492850ef96d7ba413bdd7be6a5fa63419d Mon Sep 17 00:00:00 2001
From: Philipp Spitzer <philipp@spitzer.priv.at>
Date: Tue, 23 Apr 2024 22:31:13 +0200
Subject: [PATCH] Stale events are deleted now after reloading conference.

---
 src/sql/schedulexmlparser.cpp |  8 ++++++++
 src/sql/schedulexmlparser.h   |  1 +
 src/sql/sqlengine.cpp         | 36 +++++++++++++++++++++++++++++++++++
 src/sql/sqlengine.h           |  1 +
 4 files changed, 46 insertions(+)

diff --git a/src/sql/schedulexmlparser.cpp b/src/sql/schedulexmlparser.cpp
index 64e266d..f758727 100644
--- a/src/sql/schedulexmlparser.cpp
+++ b/src/sql/schedulexmlparser.cpp
@@ -93,6 +93,10 @@ void ScheduleXmlParser::parseDataImpl(const QByteArray &aData, const QString& ur
         // we need to get count of all events in order to emit 'progressStatus' signal
         int totalEventsCount = scheduleElement.elementsByTagName("event").count();
 
+        // collect all event IDs so that we can identify previously existing events that were deleted
+        // when refreshing the conference
+        QSet<QString> importedEventIds;
+
         // parsing day elements
         int currentEvent = 0; // hold global idx of processed event
         QDomNodeList dayList = scheduleElement.elementsByTagName("day");
@@ -128,6 +132,7 @@ void ScheduleXmlParser::parseDataImpl(const QByteArray &aData, const QString& ur
                         // process event's nodes
                         QHash<QString,QString> event;
                         event["id"] = eventElement.attribute("id");
+                        importedEventIds.insert(event["id"]);
                         event["conference_id"] = QString::number(conferenceId, 10);
                         QTime event_start = QTime::fromString(eventElement.firstChildElement("start").text(), sqlEngine->TIME_FORMAT);
                         event["start"] = event_start.toString(sqlEngine->TIME_FORMAT); // time eg. 10:00
@@ -190,6 +195,9 @@ void ScheduleXmlParser::parseDataImpl(const QByteArray &aData, const QString& ur
             } // parsing room elements
         } // parsing day elements
 
+        // Delete events that exist from a previous import but have not been imported again
+        sqlEngine->deleteStaleEvents(conferenceId, importedEventIds);
+
         // Re-write conference as utc_offset was not known previously
         if (!conference.value("utc_offset").isEmpty()) {
             sqlEngine->addConferenceToDB(conference, conferenceId, conferenceId != 0);
diff --git a/src/sql/schedulexmlparser.h b/src/sql/schedulexmlparser.h
index ae15a70..ffb340d 100644
--- a/src/sql/schedulexmlparser.h
+++ b/src/sql/schedulexmlparser.h
@@ -28,6 +28,7 @@ class ScheduleXmlParser : public QObject
     Q_OBJECT
     private:
         SqlEngine* sqlEngine;
+        /// conference ID is assigned automatically if 0
         void parseDataImpl(const QByteArray &aData, const QString& url, int conferenceId);
     public:
         ScheduleXmlParser(SqlEngine* sqlEngine, QObject *aParent = NULL);
diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp
index 54228c3..2d8672c 100644
--- a/src/sql/sqlengine.cpp
+++ b/src/sql/sqlengine.cpp
@@ -459,6 +459,42 @@ bool SqlEngine::deleteConference(int id) {
 }
 
 
+bool SqlEngine::deleteStaleEvents(int conferenceId, QSet<QString> eventIdsToKeep) {
+    QSqlQuery query(db);
+
+    // get all event IDs from conference
+    query.prepare("SELECT id FROM event WHERE xid_conference = ?");
+    query.bindValue(0, conferenceId);
+    bool success = query.exec();
+    emitSqlQueryError(query);
+    if (!success) return false;
+    QSet<QString> existingEventIds;
+    while (query.next()) existingEventIds.insert(query.value(0).toString());
+
+    // determine events that are not existing anymore
+    QSet<QString> eventIdsToRemove = existingEventIds.subtract(eventIdsToKeep);
+
+    // delete events including entries from referencing tables
+    QList<QString> tables = {"link", "event_room", "event_person"};
+    for(const QString& eventId: eventIdsToRemove) {
+        for(const QString& table: tables) {
+            query.prepare(QString("DELETE FROM %1 WHERE xid_conference = ? AND xid_event = ?").arg(table));
+            query.bindValue(0, conferenceId);
+            query.bindValue(1, eventId);
+            success &= query.exec();
+            emitSqlQueryError(query);
+        }
+        query.prepare("DELETE FROM event WHERE xid_conference = ? AND id = ?");
+        query.bindValue(0, conferenceId);
+        query.bindValue(1, eventId);
+        success &= query.exec();
+        emitSqlQueryError(query);
+    }
+
+    return success;
+}
+
+
 void SqlEngine::emitSqlQueryError(const QSqlQuery &query) {
     QSqlError error = query.lastError();
     if (error.type() == QSqlError::NoError) return;
diff --git a/src/sql/sqlengine.h b/src/sql/sqlengine.h
index 85ace97..097a9a6 100644
--- a/src/sql/sqlengine.h
+++ b/src/sql/sqlengine.h
@@ -66,6 +66,7 @@ class SqlEngine : public QObject {
         void addLinkToDB(QHash<QString,QString> &aLink);
         void addRoomToDB(QHash<QString,QString> &aRoom);
         bool deleteConference(int id);
+        bool deleteStaleEvents(int conferenceId, QSet<QString> eventIdsToKeep);
 
         bool beginTransaction();
         bool commitTransaction();
-- 
2.39.5