From 77f6ce0bb06e0496fbcc98656b82108d62c6aeef Mon Sep 17 00:00:00 2001
From: Yorhel <git@yorhel.nl>
Date: Tue, 22 Feb 2011 16:30:19 +0100
Subject: chardb: +char<->vn linking, +vn display on char pages

---
 util/sql/func.sql            | 6 +++++-
 util/updates/update_2.19.sql | 9 +++++----
 2 files changed, 10 insertions(+), 5 deletions(-)

(limited to 'util')

diff --git a/util/sql/func.sql b/util/sql/func.sql
index 6ae0312b..5a43ae0f 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -415,8 +415,10 @@ BEGIN
     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;
+    CREATE TEMPORARY TABLE edit_char_vns (LIKE chars_vns INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
+    ALTER TABLE edit_char_vns DROP COLUMN cid;
   EXCEPTION WHEN duplicate_table THEN
-    TRUNCATE edit_char, edit_char_traits;
+    TRUNCATE edit_char, edit_char_traits, edit_char_vns;
   END;
   PERFORM edit_revtable('c', hid);
   -- new char
@@ -426,6 +428,7 @@ BEGIN
   ELSE
     INSERT INTO edit_char SELECT name, original, alias, image, "desc", gender, 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;
+    INSERT INTO edit_char_vns SELECT vid, rid, spoil, role FROM chars_vns WHERE cid = hid;
   END IF;
 END;
 $$ LANGUAGE plpgsql;
@@ -442,6 +445,7 @@ BEGIN
   SELECT INTO r * FROM edit_commit();
   INSERT INTO chars_rev SELECT r.cid, r.iid, name, original, alias, image, "desc", gender, 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;
+  INSERT INTO chars_vns SELECT r.cid, vid, rid, spoil, role FROM edit_char_vns;
   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 c8fb1119..bc1b5afe 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -73,11 +73,12 @@ CREATE TABLE chars_traits (
 CREATE TABLE chars_vns (
   cid integer NOT NULL REFERENCES chars_rev (id),
   vid integer NOT NULL REFERENCES vn (id),
-  rid integer REFERENCES releases (id),
-  spoil boolean NOT NULL DEFAULT false,
-  role char_role NOT NULL DEFAULT 'main',
-  PRIMARY KEY(cid, vid, rid)
+  rid integer NULL REFERENCES releases (id),
+  spoil smallint NOT NULL DEFAULT 0,
+  role char_role NOT NULL DEFAULT 'main'
 );
+-- primary key won't work when one column allows NULL
+CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (cid, vid, COALESCE(rid, 0));
 
 -- cache table
 CREATE TABLE traits_chars (
-- 
cgit v1.2.3