chardb: Added char entry tables and updated the basic revision framework

The Perl code and SQL-revisioning code only handles the name, original,
alias and desc fields at the moment. There is a basic /i+ and /i+.+ page
for testing, which should have all the functionality required for the
revisioning framework.

2 files changed, 113 insertions, 1 deletions

diff --git a/util/sql/func.sql b/util/sql/func.sql
index 74f08147..4fc95165 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -164,6 +164,7 @@ BEGIN
   INSERT INTO edit_revision (type, iid, ihid, ilock) VALUES (t,
     (       SELECT vid FROM vn_rev WHERE id = i
       UNION SELECT rid FROM releases_rev WHERE id = i
+      UNION SELECT cid FROM chars_rev WHERE id = i
       UNION SELECT pid FROM producers_rev WHERE id = i),
     COALESCE((SELECT ihid FROM changes WHERE id = i), FALSE),
     COALESCE((SELECT ilock FROM changes WHERE id = i), FALSE)
@@ -189,6 +190,7 @@ BEGIN
       JOIN (  SELECT id FROM vn_rev        WHERE t = 'v' AND vid = i
         UNION SELECT id FROM releases_rev  WHERE t = 'r' AND rid = i
         UNION SELECT id FROM producers_rev WHERE t = 'p' AND pid = i
+        UNION SELECT id FROM chars_rev     WHERE t = 'c' AND cid = i
       ) x(id) ON x.id = c.id
       ORDER BY c.id DESC
       LIMIT 1;
@@ -204,6 +206,7 @@ BEGIN
       WHEN 'v' THEN INSERT INTO vn        (latest) VALUES (0) RETURNING id INTO r.iid;
       WHEN 'r' THEN INSERT INTO releases  (latest) VALUES (0) RETURNING id INTO r.iid;
       WHEN 'p' THEN INSERT INTO producers (latest) VALUES (0) RETURNING id INTO r.iid;
+      WHEN 'c' THEN INSERT INTO chars     (latest) VALUES (0) RETURNING id INTO r.iid;
     END CASE;
   ELSE
     r.iid := i;
@@ -370,6 +373,46 @@ $$ LANGUAGE plpgsql;
 
 
 
+-- PLACEHOLDERS, not complete yet
+
+CREATE OR REPLACE FUNCTION edit_char_init(cid integer) RETURNS void AS $$
+BEGIN
+  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;
+  EXCEPTION WHEN duplicate_table THEN
+    TRUNCATE edit_char;
+  END;
+  PERFORM edit_revtable('c', cid);
+  -- new char
+  IF cid IS NULL THEN
+    INSERT INTO edit_char DEFAULT VALUES;
+  -- load revision
+  ELSE
+    INSERT INTO edit_char SELECT name, original, alias, image, "desc" FROM chars_rev WHERE id = cid;
+  END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
+CREATE OR REPLACE FUNCTION edit_char_commit() RETURNS edit_rettype AS $$
+DECLARE
+  r edit_rettype;
+BEGIN
+  IF (SELECT COUNT(*) FROM edit_char) <> 1 THEN
+    RAISE 'edit_char must have exactly one row!';
+  END IF;
+  SELECT INTO r * FROM edit_commit();
+  INSERT INTO chars_rev SELECT r.cid, r.iid, name, original, alias, image, "desc" FROM edit_char;
+  UPDATE chars SET latest = r.cid WHERE id = r.iid;
+  RETURN r;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
 
 
 ----------------------------------------------------------
@@ -819,3 +862,4 @@ BEGIN
 END;
 $$ LANGUAGE plpgsql;
 
+
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
index 4bb7f547..43337ef9 100644
--- a/util/updates/update_2.19.sql
+++ b/util/updates/update_2.19.sql
@@ -24,8 +24,76 @@ CREATE TABLE traits_parents (
   PRIMARY KEY(trait, parent)
 );
 
+CREATE TRIGGER insert_notify              AFTER  INSERT           ON traits        FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+
+
+
+-- character database -> chars
+
+CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
+
+CREATE TABLE chars (
+  id SERIAL PRIMARY KEY,
+  latest integer NOT NULL DEFAULT 0,
+  locked boolean NOT NULL DEFAULT FALSE,
+  hidden boolean NOT NULL DEFAULT FALSE
+);
+
+CREATE TABLE chars_rev (
+  id         integer  NOT NULL PRIMARY KEY REFERENCES changes (id),
+  cid        integer  NOT NULL REFERENCES chars (id),
+  name       varchar(250) NOT NULL DEFAULT '',
+  original   varchar(250) NOT NULL DEFAULT '',
+  alias      varchar(500) NOT NULL DEFAULT '',
+  image      integer  NOT NULL DEFAULT 0,
+  "desc"     text     NOT NULL DEFAULT '',
+  s_bust     smallint NOT NULL DEFAULT 0,
+  s_waist    smallint NOT NULL DEFAULT 0,
+  s_hip      smallint NOT NULL DEFAULT 0,
+  b_month    smallint NOT NULL DEFAULT 0,
+  b_day      smallint NOT NULL DEFAULT 0,
+  height     smallint NOT NULL DEFAULT 0,
+  weight     smallint NOT NULL DEFAULT 0,
+  main       integer  REFERENCES chars (id),
+  main_spoil boolean  NOT NULL DEFAULT false
+);
+ALTER TABLE chars ADD FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED;
+
+CREATE TABLE chars_traits (
+  cid integer NOT NULL REFERENCES chars_rev (id),
+  tid integer NOT NULL REFERENCES traits (id),
+  spoil smallint NOT NULL DEFAULT 0,
+  PRIMARY KEY(cid, tid)
+);
+
+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)
+);
+
+
+
+-- allow characters to be versioned using the changes table
+
+CREATE TYPE dbentry_type_tmp AS ENUM ('v', 'r', 'p', 'c');
+ALTER TABLE changes ALTER COLUMN "type" TYPE dbentry_type_tmp USING "type"::text::dbentry_type_tmp;
+DROP FUNCTION edit_revtable(dbentry_type, integer);
+DROP TYPE dbentry_type;
+ALTER TYPE dbentry_type_tmp RENAME TO dbentry_type;
+
+
+-- load the updated functions
+
 \i util/sql/func.sql
 
-CREATE TRIGGER insert_notify              AFTER  INSERT           ON traits        FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
 
+-- test
+--SELECT edit_char_init(null);
+--UPDATE edit_revision SET comments = 'New test entry', requester = 2, ip = '0.0.0.0';
+--UPDATE edit_char SET name = 'Phorni', original = 'フォーニ', "desc" = 'Sprite of Music';
+--SELECT edit_char_commit();