diff options
Diffstat (limited to 'util')
-rw-r--r-- | util/sql/func.sql | 44 | ||||
-rw-r--r-- | util/updates/update_2.19.sql | 70 |
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(); |