From: Shane Synan Date: Fri, 11 May 2018 06:37:55 +0000 (-0500) Subject: core: Fix PostgreSQL realname/avatarurl handling X-Git-Tag: travis-deploy-test~115 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=b8d7e647bca45cba82db5fa38c58f336aefe5768;hp=092e6b212637ffbf68800584b7c1f32d1931b602 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.) --- 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, '')