Discussions: Fix handling of user deletion

Which I broke by removing the DEFAULT clause on threads_posts.uid. That
column still used the old uid=0 for deleted users rather than the
uid=NULL that I was planning to migrate the entire DB to. So while I'm
fixing up the threads schema, may as well update this too.

There's still a bunch of columns relying on uid=0, but I can fix that
later.

2 files changed, 7 insertions, 7 deletions

diff --git a/lib/Multi/Feed.pm b/lib/Multi/Feed.pm
index 8110bee8..be367bbc 100644
--- a/lib/Multi/Feed.pm
+++ b/lib/Multi/Feed.pm
@@ -36,7 +36,7 @@ sub generate {
        FROM threads t
        JOIN threads_posts tp ON tp.tid = t.id AND tp.num = 1
        JOIN threads_boards tb ON tb.tid = t.id AND tb.type = 'an'
-       JOIN users u ON u.id = tp.uid
+       LEFT JOIN users u ON u.id = tp.uid
       WHERE NOT t.hidden AND NOT t.private
       ORDER BY t.id DESC
       LIMIT $1},
@@ -69,7 +69,7 @@ sub generate {
          extract('epoch' from tp.edited) AS updated, u.username, u.id AS uid, tp.msg AS summary
        FROM threads_posts tp
        JOIN threads t ON t.id = tp.tid
-       JOIN users u ON u.id = tp.uid
+       LEFT JOIN users u ON u.id = tp.uid
       WHERE NOT tp.hidden AND NOT t.hidden AND NOT t.private
       ORDER BY tp.date DESC
       LIMIT $1},
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index d4fb3781..0ca98212 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -246,7 +246,7 @@ sub formatid {
     push @msg, $c.(
       ($_->{rev}||1) == 1 ? "New $types{$_->{type}}" :
       $_->{type} eq 't' ? 'Reply to' : 'Edit of'
-    ).$NORMAL if $_->{username};
+    ).$NORMAL if exists $_->{username};
 
     # (always) main title
     push @msg, $_->{title};
@@ -255,7 +255,7 @@ sub formatid {
     push @msg, $c."Posted in$NORMAL $_->{boards}" if $_->{boards};
 
     # (only if username key is present) By [username]
-    push @msg, $c."By$NORMAL $_->{username}" if $_->{username};
+    push @msg, $c."By$NORMAL ".($_->{username}//'deleted') if exists $_->{username};
 
     # (only if comments key is present) Summary:
     $_->{comments} =~ s/\n/ /g if $_->{comments};
@@ -308,7 +308,7 @@ sub handleid {
     $t eq 'c' ? 'ch.name AS title, u.username, c.comments FROM changes c JOIN chars_hist ch ON c.id = ch.chid JOIN users u ON u.id = c.requester WHERE c.type = \'c\' AND c.itemid = $2 AND c.rev = $3' :
     $t eq 's' ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid JOIN users u ON u.id = c.requester JOIN staff_alias_hist sah ON sah.chid = c.id AND sah.aid = sh.aid WHERE c.type = \'s\' AND c.itemid = $2 AND c.rev = $3' :
     $t eq 'd' ? 'dh.title, u.username, c.comments FROM changes c JOIN docs_hist dh ON c.id = dh.chid JOIN users u ON u.id = c.requester WHERE c.type = \'d\' AND c.itemid = $2 AND c.rev = $3' :
-                't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id JOIN users u ON u.id = tp.uid WHERE NOT t.hidden AND NOT t.private AND t.id = vndbid(\'t\',$2) AND tp.num = $3'),
+                't.title, u.username, '.$GETBOARDS.' FROM threads t JOIN threads_posts tp ON tp.tid = t.id LEFT JOIN users u ON u.id = tp.uid WHERE NOT t.hidden AND NOT t.private AND t.id = vndbid(\'t\',$2) AND tp.num = $3'),
     [ $t, $id, $rev], $c if $rev && $t =~ /[dvprtcs]/;
 }
 
@@ -351,10 +351,10 @@ sub notify {
     WHERE c.id > $1 AND c.requester <> 1
     ORDER BY c.id},
   post => q{
-    SELECT 't' AS type, vndbid_num(tp.tid) AS id, tp.num AS rev, t.title, u.username, tp.date AS lastid, }.$GETBOARDS.q{
+    SELECT 't' AS type, vndbid_num(tp.tid) AS id, tp.num AS rev, t.title, COALESCE(u.username, 'deleted') AS username, tp.date AS lastid, }.$GETBOARDS.q{
     FROM threads_posts tp
     JOIN threads t ON t.id = tp.tid
-    JOIN users u ON u.id = tp.uid
+    LEFT JOIN users u ON u.id = tp.uid
     WHERE tp.date > $1 AND tp.num = 1 AND NOT t.hidden AND NOT t.private
     ORDER BY tp.date},
   trait => q{