From: Shane Synan Date: Sun, 16 Apr 2017 04:13:59 +0000 (-0500) Subject: core: Add backlog trigger to update lastmsgid X-Git-Tag: travis-deploy-test~293 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=1d1178f7d73ab19e62798f23c1d0cd5e0b4cdb59 core: Add backlog trigger to update lastmsgid Add PostgreSQL function 'backlog_lastmsgid_update()' that sets the 'lastmsgid' of a buffer according to the latest message ID available. Also add a PostgreSQL trigger 'backlog_lastmsgid_update' that acts whenever a new message is inserted to or updated in the backlog table. Add two SQLite triggers, 'backlog_lastmsgid_update_trigger_insert' for inserting messages into the backlog, and 'backlog_lastmsgid_update_trigger_update' for updating messages in the backlog. Both triggers update the 'lastmsgid' of a buffer according to the latest message ID available. Unfortunately, SQLite lacks niceties such as functions or complex triggers, requiring two separate triggers to do the same thing. Ah well, SQLite is much easier to bundle and set up. This fixes some cases where 'update_lastmsgid' was not being called when it should, namely when inserting multiple messages. NOTE: Currently Quassel does not offer an option to delete specific messages without deleting a buffer, nor to update the message ID of existing messages (this would break many assumptions). However, for completion-sake, the UPDATE triggers are included. DELETE triggers should be added if individual message deletion is added. Thanks to justJanne for the suggestion and PostgreSQL code! --- diff --git a/src/core/SQL/PostgreSQL/setup_061_add_function_backlog_lastmsgid_update.sql b/src/core/SQL/PostgreSQL/setup_061_add_function_backlog_lastmsgid_update.sql new file mode 100644 index 00000000..9b96b4a6 --- /dev/null +++ b/src/core/SQL/PostgreSQL/setup_061_add_function_backlog_lastmsgid_update.sql @@ -0,0 +1,12 @@ +CREATE OR REPLACE FUNCTION public.backlog_lastmsgid_update() +RETURNS trigger +AS $BODY$ + BEGIN + UPDATE buffer + SET lastmsgid = new.messageid + WHERE buffer.bufferid = new.bufferid + AND buffer.lastmsgid < new.messageid; + RETURN new; + END +$BODY$ +LANGUAGE plpgsql; diff --git a/src/core/SQL/PostgreSQL/setup_062_add_trigger_backlog_lastmsgid_update.sql b/src/core/SQL/PostgreSQL/setup_062_add_trigger_backlog_lastmsgid_update.sql new file mode 100644 index 00000000..88d2e141 --- /dev/null +++ b/src/core/SQL/PostgreSQL/setup_062_add_trigger_backlog_lastmsgid_update.sql @@ -0,0 +1,5 @@ +CREATE TRIGGER backlog_lastmsgid_update_trigger +AFTER INSERT OR UPDATE +ON public.backlog +FOR EACH ROW +EXECUTE PROCEDURE public.backlog_lastmsgid_update(); diff --git a/src/core/SQL/PostgreSQL/version/21/upgrade_000_add_function_backlog_lastmsgid_update.sql b/src/core/SQL/PostgreSQL/version/21/upgrade_000_add_function_backlog_lastmsgid_update.sql new file mode 100644 index 00000000..9b96b4a6 --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/21/upgrade_000_add_function_backlog_lastmsgid_update.sql @@ -0,0 +1,12 @@ +CREATE OR REPLACE FUNCTION public.backlog_lastmsgid_update() +RETURNS trigger +AS $BODY$ + BEGIN + UPDATE buffer + SET lastmsgid = new.messageid + WHERE buffer.bufferid = new.bufferid + AND buffer.lastmsgid < new.messageid; + RETURN new; + END +$BODY$ +LANGUAGE plpgsql; diff --git a/src/core/SQL/PostgreSQL/version/21/upgrade_001_add_trigger_backlog_lastmsgid_update.sql b/src/core/SQL/PostgreSQL/version/21/upgrade_001_add_trigger_backlog_lastmsgid_update.sql new file mode 100644 index 00000000..88d2e141 --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/21/upgrade_001_add_trigger_backlog_lastmsgid_update.sql @@ -0,0 +1,5 @@ +CREATE TRIGGER backlog_lastmsgid_update_trigger +AFTER INSERT OR UPDATE +ON public.backlog +FOR EACH ROW +EXECUTE PROCEDURE public.backlog_lastmsgid_update(); diff --git a/src/core/SQL/SQLite/setup_061_add_trigger_backlog_lastmsgid_update_direct_insert.sql b/src/core/SQL/SQLite/setup_061_add_trigger_backlog_lastmsgid_update_direct_insert.sql new file mode 100644 index 00000000..47a30f3c --- /dev/null +++ b/src/core/SQL/SQLite/setup_061_add_trigger_backlog_lastmsgid_update_direct_insert.sql @@ -0,0 +1,10 @@ +CREATE TRIGGER IF NOT EXISTS backlog_lastmsgid_update_trigger_insert +AFTER INSERT +ON backlog +FOR EACH ROW + BEGIN + UPDATE buffer + SET lastmsgid = new.messageid + WHERE buffer.bufferid = new.bufferid + AND buffer.lastmsgid < new.messageid; + END diff --git a/src/core/SQL/SQLite/setup_062_add_trigger_backlog_lastmsgid_update_direct_update.sql b/src/core/SQL/SQLite/setup_062_add_trigger_backlog_lastmsgid_update_direct_update.sql new file mode 100644 index 00000000..8cd02b9a --- /dev/null +++ b/src/core/SQL/SQLite/setup_062_add_trigger_backlog_lastmsgid_update_direct_update.sql @@ -0,0 +1,10 @@ +CREATE TRIGGER IF NOT EXISTS backlog_lastmsgid_update_trigger_update +AFTER UPDATE +ON backlog +FOR EACH ROW + BEGIN + UPDATE buffer + SET lastmsgid = new.messageid + WHERE buffer.bufferid = new.bufferid + AND buffer.lastmsgid < new.messageid; + END diff --git a/src/core/SQL/SQLite/version/22/upgrade_000_add_trigger_backlog_lastmsgid_update_direct_insert.sql b/src/core/SQL/SQLite/version/22/upgrade_000_add_trigger_backlog_lastmsgid_update_direct_insert.sql new file mode 100644 index 00000000..47a30f3c --- /dev/null +++ b/src/core/SQL/SQLite/version/22/upgrade_000_add_trigger_backlog_lastmsgid_update_direct_insert.sql @@ -0,0 +1,10 @@ +CREATE TRIGGER IF NOT EXISTS backlog_lastmsgid_update_trigger_insert +AFTER INSERT +ON backlog +FOR EACH ROW + BEGIN + UPDATE buffer + SET lastmsgid = new.messageid + WHERE buffer.bufferid = new.bufferid + AND buffer.lastmsgid < new.messageid; + END diff --git a/src/core/SQL/SQLite/version/22/upgrade_001_add_trigger_backlog_lastmsgid_update_direct_update.sql b/src/core/SQL/SQLite/version/22/upgrade_001_add_trigger_backlog_lastmsgid_update_direct_update.sql new file mode 100644 index 00000000..8cd02b9a --- /dev/null +++ b/src/core/SQL/SQLite/version/22/upgrade_001_add_trigger_backlog_lastmsgid_update_direct_update.sql @@ -0,0 +1,10 @@ +CREATE TRIGGER IF NOT EXISTS backlog_lastmsgid_update_trigger_update +AFTER UPDATE +ON backlog +FOR EACH ROW + BEGIN + UPDATE buffer + SET lastmsgid = new.messageid + WHERE buffer.bufferid = new.bufferid + AND buffer.lastmsgid < new.messageid; + END diff --git a/src/core/sql.qrc b/src/core/sql.qrc index 1ce6c88e..e39fc27b 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -64,6 +64,8 @@ ./SQL/PostgreSQL/setup_040_network.sql ./SQL/PostgreSQL/setup_050_buffer.sql ./SQL/PostgreSQL/setup_060_backlog.sql + ./SQL/PostgreSQL/setup_061_add_function_backlog_lastmsgid_update.sql + ./SQL/PostgreSQL/setup_062_add_trigger_backlog_lastmsgid_update.sql ./SQL/PostgreSQL/setup_070_coreinfo.sql ./SQL/PostgreSQL/setup_080_ircservers.sql ./SQL/PostgreSQL/setup_090_backlog_idx.sql @@ -99,6 +101,8 @@ ./SQL/PostgreSQL/version/20/upgrade_002_run_function_populate_lastmsgid.sql ./SQL/PostgreSQL/version/20/upgrade_003_correct_bad_lastseenmsgid.sql ./SQL/PostgreSQL/version/20/upgrade_004_add_lastseenmsgid_constraint.sql + ./SQL/PostgreSQL/version/21/upgrade_000_add_function_backlog_lastmsgid_update.sql + ./SQL/PostgreSQL/version/21/upgrade_001_add_trigger_backlog_lastmsgid_update.sql ./SQL/SQLite/delete_backlog_by_uid.sql ./SQL/SQLite/delete_backlog_for_buffer.sql ./SQL/SQLite/delete_backlog_for_network.sql @@ -163,6 +167,8 @@ ./SQL/SQLite/setup_040_buffer_idx.sql ./SQL/SQLite/setup_050_buffer_cname_idx.sql ./SQL/SQLite/setup_060_backlog.sql + ./SQL/SQLite/setup_061_add_trigger_backlog_lastmsgid_update_direct_insert.sql + ./SQL/SQLite/setup_062_add_trigger_backlog_lastmsgid_update_direct_update.sql ./SQL/SQLite/setup_070_coreinfo.sql ./SQL/SQLite/setup_080_ircservers.sql ./SQL/SQLite/setup_090_backlog_idx.sql @@ -271,5 +277,7 @@ ./SQL/SQLite/version/21/upgrade_004_insert_into_buffer_new_from_buffer.sql ./SQL/SQLite/version/21/upgrade_005_drop_table_buffer.sql ./SQL/SQLite/version/21/upgrade_006_alter_table_buffer_new_rename_to_buffer.sql + ./SQL/SQLite/version/22/upgrade_000_add_trigger_backlog_lastmsgid_update_direct_insert.sql + ./SQL/SQLite/version/22/upgrade_001_add_trigger_backlog_lastmsgid_update_direct_update.sql