From 3bdcd24292da77b4bf51d6c247193ef06cc609bd Mon Sep 17 00:00:00 2001 From: Oliver Sauder Date: Sun, 11 Apr 2021 22:20:47 +0400 Subject: Ensure releated data is removed when deleting events --- libzeitgeist/sql-schema.vala | 138 ++++++++++++++++++++++++++++++++++++++- test/direct/log-test.vala | 152 +++++++++++++++++++++++++++++++++++++++++-- 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 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 event_ids, + GenericArray text_ids, + GenericArray storage_ids, + GenericArray payload_ids, + GenericArray 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 expected_events, - Array event_ids, MainLoop mainloop) + Array 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 (); + var storage_ids = new GenericArray (); + var uri_ids = new GenericArray (); + var payload_ids = new GenericArray (); + + 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 expected_events, + Database database, MainLoop mainloop) { Array 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); -- cgit v1.2.3