From: Manuel Nickschas <sputnick@quassel-irc.org>
Date: Thu, 9 Nov 2017 22:23:22 +0000 (+0100)
Subject: sql: Fix population of lastmsgid for empty buffers
X-Git-Tag: travis-deploy-test~251
X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=f509a0d1065d4e42c9eb84e0ffab9e72517df04c;hp=0260fbe00ddbd1ce78b1870f40fce969b8f34ee5

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 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);