sql: Fix population of lastmsgid for empty buffers

Buffers may be empty, in which case the query to determine the last
message ID in them returns NULL, violating the constraint for the
newly introduced column. Use 0 instead.

Closes GH-315.

diff --git a/src/core/SQL/PostgreSQL/version/20/upgrade_001_add_function_populate_lastmsgid.sql b/src/core/SQL/PostgreSQL/version/20/upgrade_001_add_function_populate_lastmsgid.sql
index 561422a..79c82a9 100644 (file)
--- a/src/core/SQL/PostgreSQL/version/20/upgrade_001_add_function_populate_lastmsgid.sql
+++ b/src/core/SQL/PostgreSQL/version/20/upgrade_001_add_function_populate_lastmsgid.sql
@@ -5,12 +5,12 @@ BEGIN
        FOR i IN SELECT * FROM buffer
        LOOP
                UPDATE buffer
        FOR i IN SELECT * FROM buffer
        LOOP
                UPDATE buffer

-                       SET lastmsgid = (
+                       SET lastmsgid = COALESCE((

                                SELECT backlog.messageid
                                FROM backlog
                                WHERE backlog.bufferid = i.bufferid
                                ORDER BY messageid DESC LIMIT 1
                                SELECT backlog.messageid
                                FROM backlog
                                WHERE backlog.bufferid = i.bufferid
                                ORDER BY messageid DESC LIMIT 1

-                       )
+                       ), 0)

                        WHERE buffer.bufferid = i.bufferid;
        END LOOP;
        RETURN;
                        WHERE buffer.bufferid = i.bufferid;
        END LOOP;
        RETURN;

diff --git a/src/core/SQL/SQLite/version/21/upgrade_001_update_buffer_set_lastmsgid.sql b/src/core/SQL/SQLite/version/21/upgrade_001_update_buffer_set_lastmsgid.sql
index 221e814..48fba7e 100644 (file)
--- a/src/core/SQL/SQLite/version/21/upgrade_001_update_buffer_set_lastmsgid.sql
+++ b/src/core/SQL/SQLite/version/21/upgrade_001_update_buffer_set_lastmsgid.sql
@@ -1,8 +1,8 @@
 UPDATE buffer
 UPDATE buffer

-SET lastmsgid = (
+SET lastmsgid = COALESCE((

        SELECT messageid 
        FROM backlog 
        WHERE backlog.bufferid = buffer.bufferid
        ORDER BY messageid 
        DESC LIMIT 1
        SELECT messageid 
        FROM backlog 
        WHERE backlog.bufferid = buffer.bufferid
        ORDER BY messageid 
        DESC LIMIT 1

-);
+), 0);