From 4884cca9ac061d2ccce87a1d6d912efc3cc04fab Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Sun, 20 Feb 2011 09:03:45 +0100
Subject: chardb: Added "group" property to traits

It's more like a cache, and has some unintuitive problems when a trait
is applied to multiple top-level traits. But this'll do the trick
anyway.
---
 util/sql/func.sql            |  6 +++++-
 util/updates/update_2.19.sql | 34 +++++++++++++++++++---------------
 2 files changed, 24 insertions(+), 16 deletions(-)

(limited to 'util')

diff --git a/util/sql/func.sql b/util/sql/func.sql
index aa453350..0938f76d 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -379,8 +379,10 @@ BEGIN
     CREATE TEMPORARY TABLE edit_char (LIKE chars_rev INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
     ALTER TABLE edit_char DROP COLUMN id;
     ALTER TABLE edit_char DROP COLUMN cid;
+    CREATE TEMPORARY TABLE edit_char_traits (LIKE chars_traits INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_char_traits DROP COLUMN cid;
   EXCEPTION WHEN duplicate_table THEN
-    TRUNCATE edit_char;
+    TRUNCATE edit_char, edit_char_traits;
   END;
   PERFORM edit_revtable('c', hid);
   -- new char
@@ -389,6 +391,7 @@ BEGIN
   -- load revision
   ELSE
     INSERT INTO edit_char SELECT name, original, alias, image, "desc", s_bust, s_waist, s_hip, b_month, b_day, height, weight,bloodt FROM chars_rev WHERE id = hid;
+    INSERT INTO edit_char_traits SELECT tid, spoil FROM chars_traits WHERE cid = hid;
   END IF;
 END;
 $$ LANGUAGE plpgsql;
@@ -404,6 +407,7 @@ BEGIN
   END IF;
   SELECT INTO r * FROM edit_commit();
   INSERT INTO chars_rev SELECT r.cid, r.iid, name, original, alias, image, "desc", s_bust, s_waist, s_hip, b_month, b_day, height, weight, bloodt FROM edit_char;
+  INSERT INTO chars_traits SELECT r.cid, tid, spoil FROM edit_char_traits;
   UPDATE chars SET latest = r.cid WHERE id = r.iid;
   RETURN r;
 END;
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
index 88058006..14c3e30e 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -11,8 +11,10 @@ CREATE TABLE traits (
   meta boolean NOT NULL DEFAULT false,
   added timestamptz NOT NULL DEFAULT NOW(),
   state smallint NOT NULL DEFAULT 0,
-  addedby integer NOT NULL DEFAULT 0 REFERENCES users (id)
+  addedby integer NOT NULL DEFAULT 0 REFERENCES users (id),
+  "group" integer
 );
+ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id);
 
 CREATE TABLE traits_parents (
   trait integer NOT NULL REFERENCES traits (id),
@@ -97,21 +99,22 @@ CREATE TRIGGER chars_rev_image_notify     AFTER  INSERT OR UPDATE ON chars_rev
 
 /* Debugging data *-/
 
+
 -- some traits, based on Echo's draft
-INSERT INTO traits (name, meta, state, addedby) VALUES
-  ('Hair', true, 2, 2),
-  ('Hair Color', true, 2, 2),
-  ('Auburn', false, 2, 2),
-  ('Black', false, 2, 2),
-  ('Blond', false, 2, 2),
-  ('Brown', false, 2, 2),
-  ('Hairstyle', true, 2, 2),
-  ('Bun', false, 2, 2),
-  ('Odango', false, 2, 2),
-  ('Ponytail', false, 2, 2),
-  ('Twin Tails', false, 2, 2),
-  ('Short', false, 2, 2),
-  ('Straight', false, 2, 2);
+INSERT INTO traits (name, meta, state, addedby, "group") VALUES
+  ('Hair', true, 2, 2, NULL),
+  ('Hair Color', true, 2, 2, 1),
+  ('Auburn', false, 2, 2, 1),
+  ('Black', false, 2, 2, 1),
+  ('Blond', false, 2, 2, 1),
+  ('Brown', false, 2, 2, 1),
+  ('Hairstyle', true, 2, 2, 1),
+  ('Bun', false, 2, 2, 1),
+  ('Odango', false, 2, 2, 1),
+  ('Ponytail', false, 2, 2, 1),
+  ('Twin Tails', false, 2, 2, 1),
+  ('Short', false, 2, 2, 1),
+  ('Straight', false, 2, 2, 1);
 INSERT INTO traits_parents (trait, parent) VALUES
   (2, 1),
   (3, 2),
@@ -138,6 +141,7 @@ SELECT edit_char_commit();
 SELECT edit_char_init(null);
 UPDATE edit_revision SET comments = '2nd test entry', requester = 2, ip = '0.0.0.0';
 UPDATE edit_char SET name = 'Saya', original = '沙耶', "desc" = 'There is more than meets the eye!', alias = 'Cute monster', height = 140, weight = 52, s_bust = 41, s_waist = 38, s_hip = 40, b_month = 3, b_day = 15, bloodt = 'a';
+INSERT INTO edit_char_traits VALUES (4, 0), (12, 0);
 SELECT edit_char_commit();
 
 -- */
-- 
cgit v1.2.3