summaryrefslogtreecommitdiff
path: root/util/updates/update_2.19.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/updates/update_2.19.sql')
-rw-r--r--util/updates/update_2.19.sql206
1 files changed, 206 insertions, 0 deletions
diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql
new file mode 100644
index 00000000..1d980f3d
--- /dev/null
+++ b/util/updates/update_2.19.sql
@@ -0,0 +1,206 @@
+
+-- character database -> traits
+
+CREATE TABLE traits (
+ id SERIAL PRIMARY KEY,
+ name varchar(250) NOT NULL,
+ alias varchar(500) NOT NULL DEFAULT '',
+ description text NOT NULL DEFAULT '',
+ 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),
+ "group" integer,
+ "order" smallint NOT NULL DEFAULT 0,
+ sexual boolean NOT NULL DEFAULT false,
+ c_items integer NOT NULL DEFAULT 0
+);
+ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id);
+
+CREATE TABLE traits_parents (
+ trait integer NOT NULL REFERENCES traits (id),
+ parent integer NOT NULL REFERENCES traits (id),
+ PRIMARY KEY(trait, parent)
+);
+
+CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify();
+
+ALTER TABLE tags RENAME c_vns TO c_items;
+
+
+-- character database -> chars
+
+CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
+CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
+CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
+
+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 '',
+ gender gender NOT NULL DEFAULT 'unknown',
+ 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,
+ bloodt blood_type NOT NULL DEFAULT 'unknown',
+ main integer REFERENCES chars (id),
+ main_spoil smallint NOT NULL DEFAULT 0
+);
+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 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 (
+ cid integer NOT NULL REFERENCES chars (id),
+ tid integer NOT NULL REFERENCES traits (id),
+ spoil smallint NOT NULL DEFAULT 0,
+ PRIMARY KEY(cid, tid)
+);
+
+CREATE SEQUENCE charimg_seq;
+
+
+
+-- 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 hidlock_update BEFORE UPDATE ON chars FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock();
+CREATE TRIGGER chars_rev_image_notify AFTER INSERT OR UPDATE ON chars_rev FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE chars_rev_image_notify();
+
+
+/* Debugging data *-/
+
+
+-- some traits, based on Echo's draft
+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), -- 5
+ ('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), -- 10
+ ('Twin Tails', false, 2, 2, 1),
+ ('Short', false, 2, 2, 1),
+ ('Straight', false, 2, 2, 1),
+ ('Eyes', true, 2, 2, NULL),
+ ('Color', true, 2, 2, 14), -- 15
+ ('Amber', false, 2, 2, 14),
+ ('Black', false, 2, 2, 14),
+ ('Red', false, 2, 2, 14),
+ ('Body', true, 2, 2, NULL),
+ ('Apparent age', true, 2, 2, 19), --20
+ ('Child', false, 2, 2, 19),
+ ('Teen', false, 2, 2, 19),
+ ('Young-Adult', false, 2, 2, 19),
+ ('Adult', false, 2, 2, 19),
+ ('Old', false, 2, 2, 19), -- 25
+ ('Body Type', true, 2, 2, 19),
+ ('Slim', false, 2, 2, 19),
+ ('Muscular', false, 2, 2, 19),
+ ('Overweight', false, 2, 2, 19),
+ ('Huge', false, 2, 2, 19); -- 30
+INSERT INTO traits_parents (trait, parent) VALUES
+ (2, 1),
+ (3, 2),
+ (4, 2),
+ (5, 2),
+ (6, 2),
+ (7, 1),
+ (8, 7),
+ (9, 8),
+ (9, 11),
+ (10, 7),
+ (11, 10),
+ (12, 7),
+ (13, 7),
+ (15, 14),
+ (16, 15),
+ (17, 15),
+ (18, 15),
+ (20, 19),
+ (21, 20),
+ (22, 20),
+ (23, 20),
+ (24, 20),
+ (25, 20),
+ (26, 19),
+ (27, 26),
+ (28, 26),
+ (29, 26),
+ (30, 26);
+
+
+-- phorni!
+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', height = 14, gender = 'f';
+INSERT INTO edit_char_vns VALUES (38, null, 0, 'primary'), (97, null, 2, 'appears');
+SELECT edit_char_commit();
+
+-- saya (incorrect test data)
+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', gender = 'f', main = 1;
+INSERT INTO edit_char_traits VALUES (4, 0), (12, 2), (22, 0), (27, 0), (18, 1);
+INSERT INTO edit_char_vns VALUES (97, null, 0, 'primary');
+SELECT edit_char_commit();
+
+-- lafiel (not even a VN character...)
+SELECT edit_char_init(null);
+UPDATE edit_revision SET comments = '3rd test entry', requester = 2, ip = '0.0.0.0';
+UPDATE edit_char SET name = 'Abriel Nei Debrusc Borl Paryun Lafiel', original = 'アブリアル・ネイ＝ドゥブレスク・パリューニュ・ベール・パリュン・ラフィール',
+ alias = E'Ablïarsec néïc Dubreuscr Bœrh Parhynr Lamhirh\nLafiel', gender = 'f', height = 163, weight = 53, main = 1, "desc" = 'Not scary at all!';
+INSERT INTO edit_char_traits VALUES (13, 0), (17, 0), (22, 0);
+INSERT INTO edit_char_vns VALUES (97, null, 0, 'primary'), (17, 2479, 1, 'side'), (17, 626, 2, 'primary'), (17, null, 0, 'appears');
+SELECT edit_char_commit();
+
+SELECT traits_chars_calc();
+
+-- */
+
generated by cgit v1.2.3 (git 2.25.1) at 2024-04-19 13:23:22 +0000