Ensure releated data is removed when deleting events

2 files changed, 283 insertions, 7 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 (
diff --git a/test/direct/log-test.vala b/test/direct/log-test.vala
index 473e3b76..4018fb0d 100644
--- a/test/direct/log-test.vala
+++ b/test/direct/log-test.vala
@@ -21,6 +21,7 @@
  */
 
 using Zeitgeist;
+using Zeitgeist.SQLite;
 using Assertions;
 
 int main (string[] argv)
@@ -33,10 +34,66 @@ int main (string[] argv)
     return Test.run ();
 }
 
+void assert_deleted (GenericArray<uint32> ids,
+                     string table,
+                     Database database) throws Zeitgeist.EngineError
+{
+    uint32[] simple_ids = new uint32[ids.length];
+    for (int i = 0; i < ids.length; i++)
+    {
+        simple_ids[i] = ids[i];
+    }
+
+    unowned Sqlite.Database db = database.database;
+    string sql = """
+        SELECT value FROM %s WHERE id IN (%s)
+        """.printf (table, database.get_sql_string_from_event_ids (simple_ids));
+    int rc = db.exec (sql,
+        (n_columns, values, column_names) =>
+        {
+            error ("Delete event did not delete %s value %s\n", table, values[0]);
+        }, null);
+    database.assert_query_success (rc, "SQL Error");
+}
+
+void events_deleted (Zeitgeist.Log log,
+                     AsyncResult res,
+                     Array<uint32> event_ids,
+                     GenericArray<uint32> text_ids,
+                     GenericArray<uint32> storage_ids,
+                     GenericArray<uint32> payload_ids,
+                     GenericArray<uint32> uri_ids,
+                     Database database,
+                     MainLoop mainloop)
+{
+    try {
+        log.delete_events.end (res);
+    }
+    catch (Error error) {
+        critical ("Failed to delete events: %s", error.message);
+        return;
+    }
+
+    try {
+        assert_deleted (text_ids, "text", database);
+        assert_deleted (storage_ids, "storage", database);
+        assert_deleted (uri_ids, "uri", database);
+        assert_deleted (payload_ids, "payload", database);
+    }
+    catch (Error error) {
+        critical ("Failed to read database: %s", error.message);
+    }
+    finally {
+        mainloop.quit();
+    }
+}
+
 void events_received (Zeitgeist.Log log,
                       AsyncResult res,
                       GenericArray<Event> expected_events,
-                      Array<uint32> event_ids, MainLoop mainloop)
+                      Array<uint32> event_ids,
+                      Database database,
+                      MainLoop mainloop)
 {
     ResultSet events;
     try {
@@ -58,13 +115,83 @@ void events_received (Zeitgeist.Log log,
         assert (event.actor == exp_event.actor);
         assert (event.num_subjects () == exp_event.num_subjects ());
     }
-    // TODO: extend this delete test
-    log.delete_events.begin (event_ids, null, () => { mainloop.quit (); });
+
+    var text_ids = new GenericArray<uint32> ();
+    var storage_ids = new GenericArray<uint32> ();
+    var uri_ids = new GenericArray<uint32> ();
+    var payload_ids = new GenericArray<uint32> ();
+
+    uint32[] simple_event_ids = new uint32[event_ids.length];
+    for (int i = 0; i < event_ids.length; i++)
+        simple_event_ids[i] = event_ids.index (i);
+
+    try {
+        unowned Sqlite.Database db = database.database;
+        string sql = """
+            SELECT
+                subj_text,
+                subj_storage,
+                origin,
+                subj_id,
+                subj_id_current,
+                subj_origin,
+                subj_origin_current,
+                payload
+            FROM event
+            WHERE id IN (%s)
+            """.printf (database.get_sql_string_from_event_ids (simple_event_ids));
+
+        int rc = db.exec (sql,
+            (n_columns, values, column_names) =>
+            {
+                for (int i = 0; i < n_columns; i++)
+                {
+                    if (values[i] == null) {
+                        debug ("Column " + column_names[i]);
+                    }
+
+                    if (i == 0) {
+                        text_ids.add (uint.parse (values[i]));
+                    }
+                    else if (i == 1) {
+                        storage_ids.add (uint.parse (values[i]));
+                    }
+                    else if (i >= 2 && i <= 6) {
+                        uri_ids.add (uint.parse (values[i]));
+                    }
+                    else if (i == 7) {
+                        payload_ids.add (uint.parse (values[i]));
+                    }
+                }
+
+                return 0;
+            }, null);
+
+        database.assert_query_success (rc, "SQL Error");
+    }
+    catch (Error error) {
+        critical ("Failed to read database: %s", error.message);
+    }
+
+    log.delete_events.begin (event_ids, null, (log, res) => {
+        events_deleted (
+            (Zeitgeist.Log) log,
+            res,
+            event_ids,
+            text_ids,
+            storage_ids,
+            uri_ids,
+            payload_ids,
+            database,
+            mainloop
+        );
+    });
 }
 
 void events_inserted (Zeitgeist.Log log,
                        AsyncResult res,
                        GenericArray<Event> expected_events,
+                       Database database,
                        MainLoop mainloop)
 {
     Array<uint32> event_ids;
@@ -78,7 +205,7 @@ void events_inserted (Zeitgeist.Log log,
     assert (expected_events.length == event_ids.length);
 
     log.get_events.begin (event_ids, null, (log, res) => {
-        events_received ((Zeitgeist.Log) log, res, expected_events, event_ids, mainloop);
+        events_received ((Zeitgeist.Log) log, res, expected_events, event_ids, database, mainloop);
     });
 }
 
@@ -99,6 +226,12 @@ void insert_get_delete_test ()
     ev.interpretation = "foo://Interp";
     ev.manifestation = "foo://Manif";
     ev.actor = "app://firefox.desktop";
+    ev.origin = "app://firefox.desktop";
+
+    var payload = new ByteArray ();
+    uint8[] byte = { 255 };
+    payload.append (byte);
+    ev.payload = payload;
 
     su.uri = "file:///tmp/bar.txt";
     su.interpretation = "foo://TextDoc";
@@ -108,10 +241,19 @@ void insert_get_delete_test ()
     su.text = "bar.txt";
     su.storage = "bfb486f6-f5f8-4296-8871-0cc749cf8ef7";
 
+    Database database;
+    try {
+        database = new Zeitgeist.SQLite.Database ();
+    }
+    catch (Error error) {
+        critical ("Failed to open database");
+        return;
+    }
+
     /* This method call now owns all events, subjects, and the events array */
     Zeitgeist.Log.get_default ().insert_events.begin (
         expected_events, null, (log, res) => {
-            events_inserted ((Zeitgeist.Log) log, res, expected_events, mainloop);
+            events_inserted ((Zeitgeist.Log) log, res, expected_events, database, mainloop);
         });
     assert (expected_events.length == 1);