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 (file)
index 0000000..9b96b4a
--- /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 (file)
index 0000000..88d2e14
--- /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 (file)
index 0000000..9b96b4a
--- /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 (file)
index 0000000..88d2e14
--- /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 (file)
index 0000000..47a30f3
--- /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 (file)
index 0000000..8cd02b9
--- /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 (file)
index 0000000..47a30f3
--- /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 (file)
index 0000000..8cd02b9
--- /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 1ce6c88..e39fc27 100644 (file)
--- a/src/core/sql.qrc
+++ b/src/core/sql.qrc
@@ -64,6 +64,8 @@
     <file>./SQL/PostgreSQL/setup_040_network.sql</file>
     <file>./SQL/PostgreSQL/setup_050_buffer.sql</file>
     <file>./SQL/PostgreSQL/setup_060_backlog.sql</file>
     <file>./SQL/PostgreSQL/setup_040_network.sql</file>
     <file>./SQL/PostgreSQL/setup_050_buffer.sql</file>
     <file>./SQL/PostgreSQL/setup_060_backlog.sql</file>

+    <file>./SQL/PostgreSQL/setup_061_add_function_backlog_lastmsgid_update.sql</file>
+    <file>./SQL/PostgreSQL/setup_062_add_trigger_backlog_lastmsgid_update.sql</file>

     <file>./SQL/PostgreSQL/setup_070_coreinfo.sql</file>
     <file>./SQL/PostgreSQL/setup_080_ircservers.sql</file>
     <file>./SQL/PostgreSQL/setup_090_backlog_idx.sql</file>
     <file>./SQL/PostgreSQL/setup_070_coreinfo.sql</file>
     <file>./SQL/PostgreSQL/setup_080_ircservers.sql</file>
     <file>./SQL/PostgreSQL/setup_090_backlog_idx.sql</file>


@@ -99,6 +101,8 @@
     <file>./SQL/PostgreSQL/version/20/upgrade_002_run_function_populate_lastmsgid.sql</file>
     <file>./SQL/PostgreSQL/version/20/upgrade_003_correct_bad_lastseenmsgid.sql</file>
     <file>./SQL/PostgreSQL/version/20/upgrade_004_add_lastseenmsgid_constraint.sql</file>
     <file>./SQL/PostgreSQL/version/20/upgrade_002_run_function_populate_lastmsgid.sql</file>
     <file>./SQL/PostgreSQL/version/20/upgrade_003_correct_bad_lastseenmsgid.sql</file>
     <file>./SQL/PostgreSQL/version/20/upgrade_004_add_lastseenmsgid_constraint.sql</file>

+    <file>./SQL/PostgreSQL/version/21/upgrade_000_add_function_backlog_lastmsgid_update.sql</file>
+    <file>./SQL/PostgreSQL/version/21/upgrade_001_add_trigger_backlog_lastmsgid_update.sql</file>

     <file>./SQL/SQLite/delete_backlog_by_uid.sql</file>
     <file>./SQL/SQLite/delete_backlog_for_buffer.sql</file>
     <file>./SQL/SQLite/delete_backlog_for_network.sql</file>
     <file>./SQL/SQLite/delete_backlog_by_uid.sql</file>
     <file>./SQL/SQLite/delete_backlog_for_buffer.sql</file>
     <file>./SQL/SQLite/delete_backlog_for_network.sql</file>


@@ -163,6 +167,8 @@
     <file>./SQL/SQLite/setup_040_buffer_idx.sql</file>
     <file>./SQL/SQLite/setup_050_buffer_cname_idx.sql</file>
     <file>./SQL/SQLite/setup_060_backlog.sql</file>
     <file>./SQL/SQLite/setup_040_buffer_idx.sql</file>
     <file>./SQL/SQLite/setup_050_buffer_cname_idx.sql</file>
     <file>./SQL/SQLite/setup_060_backlog.sql</file>

+    <file>./SQL/SQLite/setup_061_add_trigger_backlog_lastmsgid_update_direct_insert.sql</file>
+    <file>./SQL/SQLite/setup_062_add_trigger_backlog_lastmsgid_update_direct_update.sql</file>

     <file>./SQL/SQLite/setup_070_coreinfo.sql</file>
     <file>./SQL/SQLite/setup_080_ircservers.sql</file>
     <file>./SQL/SQLite/setup_090_backlog_idx.sql</file>
     <file>./SQL/SQLite/setup_070_coreinfo.sql</file>
     <file>./SQL/SQLite/setup_080_ircservers.sql</file>
     <file>./SQL/SQLite/setup_090_backlog_idx.sql</file>


@@ -271,5 +277,7 @@
     <file>./SQL/SQLite/version/21/upgrade_004_insert_into_buffer_new_from_buffer.sql</file>
     <file>./SQL/SQLite/version/21/upgrade_005_drop_table_buffer.sql</file>
     <file>./SQL/SQLite/version/21/upgrade_006_alter_table_buffer_new_rename_to_buffer.sql</file>
     <file>./SQL/SQLite/version/21/upgrade_004_insert_into_buffer_new_from_buffer.sql</file>
     <file>./SQL/SQLite/version/21/upgrade_005_drop_table_buffer.sql</file>
     <file>./SQL/SQLite/version/21/upgrade_006_alter_table_buffer_new_rename_to_buffer.sql</file>

+    <file>./SQL/SQLite/version/22/upgrade_000_add_trigger_backlog_lastmsgid_update_direct_insert.sql</file>
+    <file>./SQL/SQLite/version/22/upgrade_001_add_trigger_backlog_lastmsgid_update_direct_update.sql</file>

 </qresource>
 </RCC>
 </qresource>
 </RCC>