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 63b124c..8ade1d3 100644 (file)
--- 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, '')