Chars: add lie flag for traits

Still TODO: filtering & searching.

2 files changed, 9 insertions, 5 deletions

diff --git a/sql/func.sql b/sql/func.sql
index adc8ec07..0657297d 100644
--- a/sql/func.sql
+++ b/sql/func.sql
@@ -335,17 +335,17 @@ BEGIN
     DELETE FROM traits_chars WHERE cid = ucid;
   END IF;
 
-  INSERT INTO traits_chars (tid, cid, spoil)
+  INSERT INTO traits_chars (tid, cid, spoil, lie)
     -- all char<->trait links of the latest revisions, including chars inherited from child traits.
     -- (also includes non-searchable traits, because they could have a searchable trait as parent)
-    WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler) AS (
-        SELECT 15, tid, ct.id, spoil
+    WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, lie) AS (
+        SELECT 15, tid, ct.id, spoil, lie
           FROM chars_traits ct
          WHERE id NOT IN(SELECT id from chars WHERE hidden)
            AND (ucid IS NULL OR ct.id = ucid)
            AND NOT EXISTS (SELECT 1 FROM traits t WHERE t.id = ct.tid AND t.hidden)
       UNION ALL
-        SELECT lvl-1, tp.parent, tc.cid, tc.spoiler
+        SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, tc.lie
         FROM traits_chars_all tc
         JOIN traits_parents tp ON tp.id = tc.tid
         JOIN traits t ON t.id = tp.parent
@@ -355,6 +355,7 @@ BEGIN
     -- now grouped by (tid, cid), with non-searchable traits filtered out
     SELECT tid, cid
          , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
+         , bool_and(lie)
       FROM traits_chars_all
      WHERE tid IN(SELECT id FROM traits WHERE searchable)
      GROUP BY tid, cid;
diff --git a/sql/schema.sql b/sql/schema.sql
index de4311df..8758a354 100644
--- a/sql/schema.sql
+++ b/sql/schema.sql
@@ -218,6 +218,7 @@ CREATE TABLE chars_traits (
   id         vndbid NOT NULL, -- [pub]
   tid        vndbid NOT NULL, -- [pub] traits.id
   spoil      smallint NOT NULL DEFAULT 0, -- [pub]
+  lie        boolean NOT NULL DEFAULT false, -- [pub]
   PRIMARY KEY(id, tid)
 );
 
@@ -226,6 +227,7 @@ CREATE TABLE chars_traits_hist (
   chid       integer NOT NULL,
   tid        vndbid NOT NULL, -- traits.id
   spoil      smallint NOT NULL DEFAULT 0,
+  lie        boolean NOT NULL DEFAULT false,
   PRIMARY KEY(chid, tid)
 );
 
@@ -990,7 +992,8 @@ CREATE TABLE traits_hist (
 CREATE TABLE traits_chars (
   cid    vndbid NOT NULL,  -- chars (id)
   tid    vndbid NOT NULL,  -- traits (id)
-  spoil  smallint NOT NULL DEFAULT 0
+  spoil  smallint NOT NULL DEFAULT 0,
+  lie    boolean NOT NULL DEFAULT false
 );
 
 -- traits_parents