Ensure releated data is removed when deleting events

1 files changed, 136 insertions, 2 deletions

diff --git a/libzeitgeist/sql-schema.vala b/libzeitgeist/sql-schema.vala
index 59bfc721..92ec4423 100644
--- a/libzeitgeist/sql-schema.vala
+++ b/libzeitgeist/sql-schema.vala
@@ -37,7 +37,7 @@ namespace Zeitgeist.SQLite
     {
 
         public const string CORE_SCHEMA = "core";
-        public const int CORE_SCHEMA_VERSION = 10;
+        public const int CORE_SCHEMA_VERSION = 11;
 
         private const string DATABASE_CREATION = "database_creation";
 
@@ -60,7 +60,7 @@ namespace Zeitgeist.SQLite
                     Timestamp.from_now ());
                 exec_query (database, schema_sql);
             }
-            else if (schema_version >= 3 && schema_version <= 9)
+            else if (schema_version >= 3 && schema_version <= 10)
             {
                 backup_database ();
 
@@ -124,6 +124,29 @@ namespace Zeitgeist.SQLite
                     exec_query (database, "DROP TABLE %s_old".printf (table));
                 }
 
+                // Delete orphaned rows
+                exec_query (database, """
+                    DELETE FROM text WHERE id NOT IN (SELECT subj_text FROM event_old)
+                """);
+                exec_query (database, """
+                    DELETE FROM storage WHERE id NOT IN (SELECT subj_storage FROM event_old)
+                """);
+                exec_query (database, """
+                    DELETE FROM uri WHERE id NOT IN (
+                        SELECT origin FROM event_old
+                        UNION
+                        SELECT subj_id FROM event_old
+                        UNION
+                        SELECT subj_id_current FROM event_old
+                        UNION
+                        SELECT subj_origin FROM event_old
+                        UNION
+                        SELECT subj_origin_current FROM event_old
+                    )
+                """);
+                exec_query (database, """
+                    DELETE FROM payload WHERE id NOT IN (SELECT payload FROM event_old)
+                """);
 
                 // Migrate events from the old table
                 //  - We initialize subj_origin_current to subj_origin as an
@@ -431,6 +454,117 @@ namespace Zeitgeist.SQLite
                 )
                 """);
 
+            // Triggers
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_subj_text
+                    AFTER DELETE ON event
+                    WHEN ((SELECT COUNT(*) FROM event WHERE subj_text=OLD.subj_text) == 0)
+                BEGIN
+                    DELETE FROM text WHERE id=OLD.subj_text;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_subj_storage
+                    AFTER DELETE ON event
+                    WHEN ((SELECT COUNT(*) FROM event WHERE subj_storage=OLD.subj_storage) == 0)
+                BEGIN
+                    DELETE FROM storage WHERE id=OLD.subj_storage;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_payload
+                    AFTER DELETE ON event
+                    WHEN (OLD.payload IS NOT NULL)
+                BEGIN
+                    DELETE FROM payload WHERE id=OLD.payload;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_origin
+                    AFTER DELETE ON event
+                    WHEN ((
+                        SELECT COUNT(*)
+                        FROM event
+                        WHERE
+                            origin=OLD.origin
+                            OR subj_id=OLD.origin
+                            OR subj_id_current=OLD.origin
+                            OR subj_origin=OLD.origin
+                            OR subj_origin_current=OLD.origin
+                        ) == 0)
+                BEGIN
+                    DELETE FROM uri WHERE id=OLD.origin;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_subj_id
+                    AFTER DELETE ON event
+                    WHEN ((
+                        SELECT COUNT(*)
+                        FROM event
+                        WHERE
+                            origin=OLD.subj_id
+                            OR subj_id=OLD.subj_id
+                            OR subj_id_current=OLD.subj_id
+                            OR subj_origin=OLD.subj_id
+                            OR subj_origin_current=OLD.subj_id
+                        ) == 0)
+                BEGIN
+                    DELETE FROM uri WHERE id=OLD.subj_id;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_subj_id_current
+                    AFTER DELETE ON event
+                    WHEN ((
+                        SELECT COUNT(*)
+                        FROM event
+                        WHERE
+                            origin=OLD.subj_id_current
+                            OR subj_id=OLD.subj_id_current
+                            OR subj_id_current=OLD.subj_id_current
+                            OR subj_origin=OLD.subj_id_current
+                            OR subj_origin_current=OLD.subj_id_current
+                        ) == 0)
+                BEGIN
+                    DELETE FROM uri WHERE id=OLD.subj_id_current;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_subj_origin
+                    AFTER DELETE ON event
+                    WHEN ((
+                        SELECT COUNT(*)
+                        FROM event
+                        WHERE
+                            origin=OLD.subj_origin
+                            OR subj_id=OLD.subj_origin
+                            OR subj_id_current=OLD.subj_origin
+                            OR subj_origin=OLD.subj_origin
+                            OR subj_origin_current=OLD.subj_origin
+                        ) == 0)
+                BEGIN
+                    DELETE FROM uri WHERE id=OLD.subj_origin;
+                END;
+            """);
+            exec_query (database, """
+                CREATE TRIGGER fkdc_event_subj_origin_current
+                    AFTER DELETE ON event
+                    WHEN ((
+                        SELECT COUNT(*)
+                        FROM event
+                        WHERE
+                            origin=OLD.subj_origin_current
+                            OR subj_id=OLD.subj_origin_current
+                            OR subj_id_current=OLD.subj_origin_current
+                            OR subj_origin=OLD.subj_origin_current
+                            OR subj_origin_current=OLD.subj_origin
+                        ) == 0)
+                BEGIN
+                    DELETE FROM uri WHERE id=OLD.subj_origin_current;
+                END;
+            """);
+
             // Extensions
             exec_query (database, """
                 CREATE TABLE IF NOT EXISTS extensions_conf (