Traits: Convert to vndbids + add edit history

Much the same as the previous conversion of tags.

4 files changed, 83 insertions, 9 deletions

diff --git a/util/dbdump.pl b/util/dbdump.pl
index 3a764df7..5777b168 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -58,7 +58,7 @@ use VNDB::Schema;
 my %tables = (
     anime               => { where => 'id IN(SELECT va.aid FROM vn_anime va JOIN vn v ON v.id = va.id WHERE NOT v.hidden)' },
     chars               => { where => 'NOT hidden' },
-    chars_traits        => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden) AND tid IN(SELECT id FROM traits WHERE state = 2)' },
+    chars_traits        => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden) AND tid IN(SELECT id FROM traits WHERE NOT hidden)' },
     chars_vns           => { where => 'id IN(SELECT id FROM chars WHERE NOT hidden)'
                                 .' AND vid IN(SELECT id FROM vn WHERE NOT hidden)'
                                 .' AND (rid IS NULL OR rid IN(SELECT id FROM releases WHERE NOT hidden))'
@@ -86,8 +86,8 @@ my %tables = (
     tags                => { where => 'NOT hidden' },
     tags_parents        => { where => 'id IN(SELECT id FROM tags WHERE NOT hidden)' },
     tags_vn             => { where => 'tag IN(SELECT id FROM tags WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)', order => 'tag, vid, uid, date' },
-    traits              => { where => 'state = 2' },
-    traits_parents      => { where => 'trait IN(SELECT id FROM traits WHERE state = 2)' },
+    traits              => { where => 'NOT hidden' },
+    traits_parents      => { where => 'trait IN(SELECT id FROM traits WHERE NOT hidden)' },
     ulist_labels        => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.lbl = id AND ulist_labels.uid = uvl.uid)' },
     ulist_vns           => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
                                 .' AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl'
@@ -377,9 +377,9 @@ sub export_traits {
     require PerlIO::gzip;
 
     my $lst = $db->selectall_arrayref(q{
-        SELECT id, name, alias AS aliases, description, searchable, applicable, c_items AS chars,
-               (SELECT string_agg(parent::text, ',') FROM traits_parents WHERE trait = id) AS parents
-        FROM traits WHERE state = 2 ORDER BY id
+        SELECT vndbid_num(id) AS id, name, alias AS aliases, description, searchable, applicable, c_items AS chars,
+               (SELECT string_agg(vndbid_num(parent)::text, ',') FROM traits_parents tp WHERE tp.id = t.id) AS parents
+        FROM traits t WHERE NOT hidden ORDER BY id
     }, { Slice => {} });
     for(@$lst) {
       $_->{id} *= 1;
diff --git a/util/devdump.pl b/util/devdump.pl
index f43b717e..e90ac335 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -48,7 +48,6 @@ my $images = $db->selectcol_arrayref(q{
 sub copy {
     my($dest, $sql, $specials) = @_;
 
-    warn $dest;
     $sql ||= "SELECT * FROM $dest";
     $specials ||= {};
 
@@ -125,8 +124,7 @@ sub copy_entry {
 
     # Tags & traits
     copy_entry [qw/tags tags_parents/], $db->selectcol_arrayref('SELECT id FROM tags');
-    copy traits => undef, {addedby => 'user'};
-    copy 'traits_parents';
+    copy_entry [qw/traits traits_parents/], $db->selectcol_arrayref('SELECT id FROM traits');
 
     # Wikidata (TODO: This could be a lot more selective)
     copy 'wikidata';
diff --git a/util/unusedimages.pl b/util/unusedimages.pl
index 019d3c9d..d3050ea6 100755
--- a/util/unusedimages.pl
+++ b/util/unusedimages.pl
@@ -59,7 +59,9 @@ sub cleandb {
                 UNION ALL SELECT "desc" FROM staff
                 UNION ALL SELECT "desc" FROM staff_hist
                 UNION ALL SELECT description FROM tags
+                UNION ALL SELECT description FROM tags_hist
                 UNION ALL SELECT description FROM traits
+                UNION ALL SELECT description FROM traits_hist
                 UNION ALL SELECT comments FROM changes
                 UNION ALL SELECT msg FROM threads_posts
                 UNION ALL SELECT msg FROM reviews_posts
diff --git a/util/updates/wip-trait-history.sql b/util/updates/wip-trait-history.sql
new file mode 100644
index 00000000..a940799f
--- /dev/null
+++ b/util/updates/wip-trait-history.sql
@@ -0,0 +1,74 @@
+BEGIN;
+
+ALTER TABLE chars_traits      DROP CONSTRAINT chars_traits_tid_fkey;
+ALTER TABLE chars_traits_hist DROP CONSTRAINT chars_traits_hist_tid_fkey;
+ALTER TABLE traits            DROP CONSTRAINT traits_group_fkey;
+ALTER TABLE traits_parents    DROP CONSTRAINT traits_parents_trait_fkey;
+ALTER TABLE traits_parents    DROP CONSTRAINT traits_parents_parent_fkey;
+
+DROP TRIGGER insert_notify ON traits;
+DROP TRIGGER stats_cache_new  ON traits;
+DROP TRIGGER stats_cache_edit ON traits;
+
+ALTER TABLE traits ADD COLUMN hidden boolean NOT NULL DEFAULT FALSE;
+ALTER TABLE traits ADD COLUMN locked boolean NOT NULL DEFAULT TRUE;
+UPDATE traits SET hidden = (state <> 2), locked = (state = 1);
+ALTER TABLE traits DROP COLUMN state;
+
+ALTER TABLE traits ALTER COLUMN id DROP DEFAULT;
+ALTER TABLE traits ALTER COLUMN id TYPE vndbid USING vndbid('i', id);
+ALTER TABLE traits ALTER COLUMN id SET DEFAULT vndbid('i', nextval('traits_id_seq')::int);
+ALTER TABLE traits ADD CONSTRAINT traits_id_check CHECK(vndbid_type(id) = 'i');
+
+ALTER TABLE traits ALTER COLUMN "group" TYPE vndbid USING vndbid('i', "group");
+ALTER TABLE traits ALTER COLUMN name SET DEFAULT '';
+
+ALTER TABLE traits_parents    RENAME COLUMN trait TO id;
+ALTER TABLE traits_parents    ALTER COLUMN id     TYPE vndbid USING vndbid('i', id);
+ALTER TABLE traits_parents    ALTER COLUMN parent TYPE vndbid USING vndbid('i', parent);
+
+ALTER TABLE traits_chars      ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid);
+ALTER TABLE chars_traits      ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid);
+ALTER TABLE chars_traits_hist ALTER COLUMN tid TYPE vndbid USING vndbid('i', tid);
+
+CREATE TABLE traits_hist (
+  chid          integer NOT NULL,
+  "order"       smallint NOT NULL DEFAULT 0,
+  defaultspoil  smallint NOT NULL DEFAULT 0,
+  sexual        boolean NOT NULL DEFAULT false,
+  searchable    boolean NOT NULL DEFAULT true,
+  applicable    boolean NOT NULL DEFAULT true,
+  name          varchar(250) NOT NULL DEFAULT '',
+  alias         varchar(500) NOT NULL DEFAULT '',
+  description   text NOT NULL DEFAULT ''
+);
+
+CREATE TABLE traits_parents_hist (
+  chid     integer NOT NULL,
+  parent   vndbid NOT NULL,
+  PRIMARY KEY(chid, parent)
+);
+
+
+INSERT INTO changes (requester,itemid,rev,ihid,ilock,comments)
+    SELECT 'u1', id, 1, hidden, locked,
+'Automated import from when the trait database did not keep track of change histories.
+This trait was initially submitted by '||coalesce(nullif(addedby::text, 'u1'), 'an anonymous user')||' on '||added::date||', but has no doubt been updated over time by moderators.'
+      FROM traits;
+
+INSERT INTO traits_hist (chid, "order", defaultspoil, sexual, searchable, applicable, name, description, alias)
+    SELECT c.id, t."order", t.defaultspoil, t.sexual, t.searchable, t.applicable, t.name, t.description, t.alias
+      FROM traits t JOIN changes c ON c.itemid = t.id;
+
+INSERT INTO traits_parents_hist (chid, parent) SELECT c.id, t.parent FROM traits_parents t JOIN changes c ON c.itemid = t.id;
+
+ALTER TABLE traits DROP COLUMN addedby;
+
+\i sql/func.sql
+\i sql/editfunc.sql
+
+COMMIT;
+
+\i sql/tableattrs.sql
+\i sql/triggers.sql
+\i sql/perms.sql