From b8d7e647bca45cba82db5fa38c58f336aefe5768 Mon Sep 17 00:00:00 2001 From: Shane Synan Date: Fri, 11 May 2018 01:37:55 -0500 Subject: [PATCH] core: Fix PostgreSQL realname/avatarurl handling In insert_message, when selecting a senderid coalesce "realname" and "avatarurl". This fixes inserting redundant senders due to NULL values, e.g. messages from the server during connect. Test case: start up core, client. Connect, check PostgreSQL database "sender" table. Disconnect, reconnect. There shouldn't be new NULL senders in the table (e.g. sender set, but NULL realname). NOTE: The realname/avatarurl columns can be NULL values. Due to this, we need to coalesce them to '' in order to use the same queries rather than "column = some value" and "column IS NULL". Both column and the input parameter need coalesced in case one or the other is NULL. As there's minimal functional difference in protocol handling between '' and NULL, we consider them the same. (This could be squashed down if preferred, I just wanted to document what was needed to fix this for PostgreSQL, and to simplify figuring out what to change should a better solution arise.) --- src/core/SQL/PostgreSQL/select_senderid.sql | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/core/SQL/PostgreSQL/select_senderid.sql b/src/core/SQL/PostgreSQL/select_senderid.sql index 63b124c5..8ade1d31 100644 --- a/src/core/SQL/PostgreSQL/select_senderid.sql +++ b/src/core/SQL/PostgreSQL/select_senderid.sql @@ -1,3 +1,3 @@ SELECT senderid FROM sender -WHERE sender = $1 AND realname = $2 AND avatarurl = $3 +WHERE sender = $1 AND coalesce(realname, '') = coalesce($2, '') AND coalesce(avatarurl, '') = coalesce($3, '') -- 2.20.1