From f509a0d1065d4e42c9eb84e0ffab9e72517df04c Mon Sep 17 00:00:00 2001 From: Manuel Nickschas Date: Thu, 9 Nov 2017 23:23:22 +0100 Subject: [PATCH 1/1] 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. --- .../20/upgrade_001_add_function_populate_lastmsgid.sql | 4 ++-- .../version/21/upgrade_001_update_buffer_set_lastmsgid.sql | 4 ++-- 2 files changed, 4 insertions(+), 4 deletions(-) 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 561422ab..79c82a95 100644 --- 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 - SET lastmsgid = ( + SET lastmsgid = COALESCE(( 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; 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 221e814a..48fba7e2 100644 --- 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 -SET lastmsgid = ( +SET lastmsgid = COALESCE(( SELECT messageid FROM backlog WHERE backlog.bufferid = buffer.bufferid ORDER BY messageid DESC LIMIT 1 -); +), 0); -- 2.20.1