chardb: char-by-trait lookup + trait usage count + tag-code sharing

I'll have to optimize the updating of traits_chars as soon as I have
some data to test with.

Also renamed tags.c_vns to c_items, to have it share the same name as
traits.c_items. This makes it a lot easier to re-use code for both tags
and traits, such as what I did with dbTagTree/dbTraitTree -> dbTTTree
and the childtags() and parenttags() functions.

1 files changed, 13 insertions, 1 deletions

diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
index 14c3e30e..1332d666 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -12,7 +12,8 @@ CREATE TABLE traits (
   added timestamptz NOT NULL DEFAULT NOW(),
   state smallint NOT NULL DEFAULT 0,
   addedby integer NOT NULL DEFAULT 0 REFERENCES users (id),
-  "group" integer
+  "group" integer,
+  c_items integer NOT NULL DEFAULT 0
 );
 ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id);
 
@@ -24,6 +25,7 @@ CREATE TABLE traits_parents (
 
 CREATE TRIGGER insert_notify              AFTER  INSERT           ON traits        FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 
+ALTER TABLE tags RENAME c_vns TO c_items;
 
 
 -- character database -> chars
@@ -75,6 +77,14 @@ CREATE TABLE chars_vns (
   PRIMARY KEY(cid, vid, rid)
 );
 
+-- cache table
+CREATE TABLE traits_chars (
+  cid integer NOT NULL REFERENCES chars (id),
+  tid integer NOT NULL REFERENCES traits (id),
+  spoil smallint NOT NULL DEFAULT 0,
+  PRIMARY KEY(cid, tid)
+);
+
 CREATE SEQUENCE charimg_seq;
 
 
@@ -144,5 +154,7 @@ UPDATE edit_char SET name = 'Saya', original = '沙耶', "desc" = 'There is more
 INSERT INTO edit_char_traits VALUES (4, 0), (12, 0);
 SELECT edit_char_commit();
 
+SELECT traits_chars_calc();
+
 -- */