Traits: Convert to vndbids + add edit history

Much the same as the previous conversion of tags.

2 files changed, 12 insertions, 17 deletions

diff --git a/lib/Multi/API.pm b/lib/Multi/API.pm
index c7110585..b64319a1 100644
--- a/lib/Multi/API.pm
+++ b/lib/Multi/API.pm
@@ -859,7 +859,7 @@ my %GET_CHARACTER = (
       fetch => [[ 'id', 'SELECT id, tid, spoil FROM chars_traits WHERE id IN(%s)',
         sub { my($n, $r) = @_;
           for my $i (@$n) {
-            $i->{traits} = [ map [ $_->{tid}*1, $_->{spoil}*1 ], grep $i->{id} eq $_->{id}, @$r ];
+            $i->{traits} = [ map [ idnum($_->{tid}), $_->{spoil}*1 ], grep $i->{id} eq $_->{id}, @$r ];
           }
         },
       ]],
@@ -930,8 +930,8 @@ my %GET_CHARACTER = (
       [ inta  => 'c.id IN(SELECT cv.id FROM chars_vns cv WHERE cv.vid IN(:value:))', {'=',1}, process => \'v', join => ',' ],
     ],
     traits => [
-      [ int   => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid = :value:)',   {'=' => 'IN', '!=' => 'NOT IN'}, range => [1,1e6] ],
-      [ inta  => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', range => [1,1e6] ],
+      [ int   => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid = :value:)',   {'=' => 'IN', '!=' => 'NOT IN'}, process => \'i' ],
+      [ inta  => 'c.id :op:(SELECT tc.cid FROM traits_chars tc WHERE tc.tid IN(:value:))', {'=' => 'IN', '!=' => 'NOT IN'}, join => ',', process => \'i' ],
     ],
   },
 );
diff --git a/lib/Multi/IRC.pm b/lib/Multi/IRC.pm
index aab05223..9809ea35 100644
--- a/lib/Multi/IRC.pm
+++ b/lib/Multi/IRC.pm
@@ -199,13 +199,12 @@ sub set_logger {
 sub set_notify {
   pg_cmd q{SELECT
     (SELECT id FROM changes ORDER BY id DESC LIMIT 1) AS rev,
-    (SELECT id FROM traits ORDER BY id DESC LIMIT 1) AS trait,
     (SELECT date FROM threads_posts ORDER BY date DESC LIMIT 1) AS post,
     (SELECT id FROM reviews ORDER BY id DESC LIMIT 1) AS review
   }, undef, sub {
     return if pg_expect $_[0], 1;
     %lastnotify = %{($_[0]->rowsAsHashes())[0]};
-    push_watcher pg->listen($_, on_notify => \&notify) for qw{newrevision newpost newtag newtrait newreview};
+    push_watcher pg->listen($_, on_notify => \&notify) for qw{newrevision newpost newreview};
   };
 }
 
@@ -298,7 +297,7 @@ sub handleid {
     $id =~ /^s/ ? 'sa.name AS title FROM staff s JOIN staff_alias sa ON sa.aid = s.aid AND sa.id = s.id WHERE s.id = $1' :
     $id =~ /^t/ ? 'title, '.$GETBOARDS.' FROM threads t WHERE NOT t.hidden AND NOT t.private AND t.id = $1' :
     $id =~ /^g/ ? 'name AS title FROM tags WHERE id = $1' :
-    $id =~ /^i/ ? 'name AS title FROM traits WHERE id = vndbid_num($1)' :
+    $id =~ /^i/ ? 'name AS title FROM traits WHERE id = $1' :
     $id =~ /^d/ ? 'title FROM docs WHERE id = $1' :
     $id =~ /^w/ ? 'v.title, u.username FROM reviews w JOIN vn v ON v.id = w.vid LEFT JOIN users u ON u.id = w.uid WHERE w.id = $1' :
                   'r.title FROM releases r WHERE r.id = $1'),
@@ -313,9 +312,10 @@ sub handleid {
     $id =~ /^s/ ? 'sah.name AS title, u.username, c.comments FROM changes c JOIN staff_hist sh ON c.id = sh.chid LEFT 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.itemid = $1 AND c.rev = $2' :
     $id =~ /^d/ ? 'dh.title, u.username, c.comments FROM changes c JOIN docs_hist dh ON c.id = dh.chid LEFT JOIN users u ON u.id = c.requester WHERE c.itemid = $1 AND c.rev = $2' :
     $id =~ /^g/ ? 'th.name AS title, u.username, c.comments FROM changes c JOIN tags_hist th ON c.id = th.chid LEFT JOIN users u ON u.id = c.requester WHERE c.itemid = $1 AND c.rev = $2' :
+    $id =~ /^i/ ? 'th.name AS title, u.username, c.comments FROM changes c JOIN traits_hist th ON c.id = th.chid LEFT JOIN users u ON u.id = c.requester WHERE c.itemid = $1 AND c.rev = $2' :
     $id =~ /^w/ ? 'v.title, u.username FROM reviews_posts wp JOIN reviews w ON w.id = wp.id JOIN vn v ON v.id = w.vid LEFT JOIN users u ON u.id = wp.uid WHERE wp.id = $1 AND wp.num = $2' :
                   '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 = $1 AND tp.num = $2'),
-    [ $id, $rev], $c if $rev && $id =~ /^[dvprtcsgw]/;
+    [ $id, $rev], $c if $rev && $id =~ /^[dvprtcsgiw]/;
 }
 
 
@@ -327,8 +327,8 @@ sub vndbid {
   my @id; # [ type, id, ref ]
   for (split /[, ]/, $msg) {
     next if length > 15 or m{[a-z]{3,6}://}i; # weed out URLs and too long things
-    push @id, /^(?:.*[^\w]|)([wdvprtcsg][1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2 ] # x+.+
-            : /^(?:.*[^\w]|)([wdvprtugics][1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, '' ] : ();        # x+
+    push @id, /^(?:.*[^\w]|)([wdvprtcsgi][1-9][0-9]*)\.([1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, $2 ] # x+.+
+            : /^(?:.*[^\w]|)([wdvprtcsgiu][1-9][0-9]*)(?:[^\w].*|)$/ ? [ $1, '' ] : ();         # x+
   }
   handleid($chan, @$_) for @id;
 }
@@ -338,13 +338,13 @@ sub vndbid {
 
 sub notify {
   my(undef, $sel) = @_;
-  my $k = {qw|newrevision rev  newpost post  newtrait trait  newreview review|}->{$sel};
+  my $k = {qw|newrevision rev  newpost post  newreview review|}->{$sel};
   return if !$k || !$lastnotify{$k};
 
   my $q = {
   rev => q{
     SELECT c.rev, c.comments, c.id AS lastid, c.itemid AS id,
-      COALESCE(vh.title, rh.title, ph.name, ch.name, sah.name, dh.title, th.name) AS title, u.username
+      COALESCE(vh.title, rh.title, ph.name, ch.name, sah.name, dh.title, th.name, ih.name) AS title, u.username
     FROM changes c
     LEFT JOIN vn_hist vh ON vndbid_type(c.itemid) = 'v' AND c.id = vh.chid
     LEFT JOIN releases_hist rh ON vndbid_type(c.itemid) = 'r' AND c.id = rh.chid
@@ -354,6 +354,7 @@ sub notify {
     LEFT JOIN staff_alias_hist sah ON vndbid_type(c.itemid) = 's' AND sah.aid = sh.aid AND sah.chid = c.id
     LEFT JOIN docs_hist dh ON vndbid_type(c.itemid) = 'd' AND c.id = dh.chid
     LEFT JOIN tags_hist th ON vndbid_type(c.itemid) = 'g' AND c.id = th.chid
+    LEFT JOIN traits_hist ih ON vndbid_type(c.itemid) = 'i' AND c.id = ih.chid
     JOIN users u ON u.id = c.requester
     WHERE c.id > $1 AND c.requester <> 'u1'
     ORDER BY c.id},
@@ -364,12 +365,6 @@ sub notify {
     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{
-    SELECT 'i'||t.id AS id, t.name AS title, u.username, t.id AS lastid
-    FROM traits t
-    JOIN users u ON u.id = t.addedby
-    WHERE t.id > $1
-    ORDER BY t.id},
   review => q{
     SELECT w.id, v.title, u.username, w.id AS lastid
     FROM reviews w