From e161985cfa8ffbd3e0b093acd70764295f4e3284 Mon Sep 17 00:00:00 2001 From: Yorhel Date: Sat, 19 Mar 2011 16:56:23 +0100 Subject: chardb: Synchronized DB changes with util/sql/* and dbgraph.pl --- util/dbgraph.pl | 2 + util/sql/all.sql | 9 +++- util/sql/schema.sql | 97 +++++++++++++++++++++++++++++++++++++++++++- util/updates/update_2.19.sql | 2 - 4 files changed, 105 insertions(+), 5 deletions(-) (limited to 'util') diff --git a/util/dbgraph.pl b/util/dbgraph.pl index f7cb9923..90b48ef7 100755 --- a/util/dbgraph.pl +++ b/util/dbgraph.pl @@ -26,6 +26,8 @@ my %subgraphs = ( 'Users' => [qw| CCFFFF users votes rlists wlists vnlists sessions notifications users_prefs |], 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], + 'Traits' => [qw| D1C2FF traits traits_parents traits_chars |], + 'Characters' => [qw| F0C2FF chars chars_rev chars_traits chars_vns |], 'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |], ); diff --git a/util/sql/all.sql b/util/sql/all.sql index 0a6d1037..d05297da 100644 --- a/util/sql/all.sql +++ b/util/sql/all.sql @@ -4,8 +4,11 @@ -- data types CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv'); -CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p'); +CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o'); +CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears'); +CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c'); CREATE TYPE edit_rettype AS (iid integer, cid integer, rev integer); +CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b'); CREATE TYPE language AS ENUM ('cs', 'da', 'de', 'en', 'es', 'fi', 'fr', 'hu', 'it', 'ja', 'ko', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ru', 'sk', 'sv', 'tr', 'vi', 'zh'); CREATE TYPE medium AS ENUM ('cd', 'dvd', 'gdr', 'blr', 'flp', 'mrt', 'mem', 'umd', 'nod', 'in', 'otc'); CREATE TYPE notification_ntype AS ENUM ('pm', 'dbdel', 'listdel', 'dbedit', 'announce'); @@ -32,6 +35,7 @@ CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'sid CREATE TRIGGER hidlock_update BEFORE UPDATE ON vn FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); CREATE TRIGGER hidlock_update BEFORE UPDATE ON producers FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); CREATE TRIGGER hidlock_update BEFORE UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest) EXECUTE PROCEDURE update_hidlock(); +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 users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache(); @@ -55,6 +59,7 @@ CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify(); CREATE TRIGGER vn_rev_image_notify AFTER INSERT OR UPDATE ON vn_rev FOR EACH ROW WHEN (NEW.image < 0) EXECUTE PROCEDURE vn_rev_image_notify(); +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(); CREATE TRIGGER screenshot_process_notify AFTER INSERT OR UPDATE ON screenshots FOR EACH ROW WHEN (NEW.processed = FALSE) EXECUTE PROCEDURE screenshot_process_notify(); @@ -73,6 +78,7 @@ CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW CREATE TRIGGER insert_notify AFTER INSERT ON changes FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); CREATE TRIGGER insert_notify AFTER INSERT ON threads_posts FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); CREATE TRIGGER insert_notify AFTER INSERT ON tags FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); +CREATE TRIGGER insert_notify AFTER INSERT ON traits FOR EACH STATEMENT EXECUTE PROCEDURE insert_notify(); CREATE TRIGGER release_vncache_update AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.latest IS DISTINCT FROM NEW.latest OR OLD.hidden IS DISTINCT FROM NEW.hidden) @@ -104,6 +110,7 @@ CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE -- Sequences used for ID generation of items not in the DB CREATE SEQUENCE covers_seq; +CREATE SEQUENCE charimg_seq; -- Rows that are assumed to be available diff --git a/util/sql/schema.sql b/util/sql/schema.sql index 452983e0..39e9351a 100644 --- a/util/sql/schema.sql +++ b/util/sql/schema.sql @@ -25,6 +25,53 @@ CREATE TABLE changes ( ilock boolean NOT NULL DEFAULT FALSE ); +-- chars +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 +); + +-- chars_rev +CREATE TABLE chars_rev ( + id integer NOT NULL PRIMARY KEY, + cid integer NOT NULL, + 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, + main_spoil smallint NOT NULL DEFAULT 0 +); + +-- chars_traits +CREATE TABLE chars_traits ( + cid integer NOT NULL, + tid integer NOT NULL, + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(cid, tid) +); + +-- chars_vns +CREATE TABLE chars_vns ( + cid integer NOT NULL, + vid integer NOT NULL, + rid integer NULL, + spoil smallint NOT NULL DEFAULT 0, + role char_role NOT NULL DEFAULT 'main' +); + -- notifications CREATE TABLE notifications ( id serial PRIMARY KEY NOT NULL, @@ -192,7 +239,7 @@ CREATE TABLE tags ( meta boolean NOT NULL DEFAULT FALSE, added timestamptz NOT NULL DEFAULT NOW(), state smallint NOT NULL DEFAULT 0, - c_vns integer NOT NULL DEFAULT 0, + c_items integer NOT NULL DEFAULT 0, addedby integer NOT NULL DEFAULT 0, cat tag_category NOT NULL DEFAULT 'cont' ); @@ -260,6 +307,37 @@ CREATE TABLE threads_boards ( PRIMARY KEY(tid, type, iid) ); +-- 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, + "group" integer, + "order" smallint NOT NULL DEFAULT 0, + sexual boolean NOT NULL DEFAULT false, + c_items integer NOT NULL DEFAULT 0 +); + +-- traits_chars +CREATE TABLE traits_chars ( + cid integer NOT NULL, + tid integer NOT NULL, + spoil smallint NOT NULL DEFAULT 0, + PRIMARY KEY(cid, tid) +); + +-- traits_parents +CREATE TABLE traits_parents ( + trait integer NOT NULL, + parent integer NOT NULL, + PRIMARY KEY(trait, parent) +); + -- users CREATE TABLE users ( id SERIAL NOT NULL PRIMARY KEY, @@ -375,6 +453,15 @@ CREATE TABLE wlists ( ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT; +ALTER TABLE chars ADD FOREIGN KEY (latest) REFERENCES chars_rev (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE chars_rev ADD FOREIGN KEY (id) REFERENCES changes (id); +ALTER TABLE chars_rev ADD FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE chars_rev ADD FOREIGN KEY (main) REFERENCES chars (id); +ALTER TABLE chars_traits ADD FOREIGN KEY (cid) REFERENCES chars_rev (id); +ALTER TABLE chars_traits ADD FOREIGN KEY (tid) REFERENCES traits (id); +ALTER TABLE chars_vns ADD FOREIGN KEY (cid) REFERENCES chars_rev (id); +ALTER TABLE chars_vns ADD FOREIGN KEY (vid) REFERENCES vn (id); +ALTER TABLE chars_vns ADD FOREIGN KEY (rid) REFERENCES releases (id); ALTER TABLE notifications ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE notifications ADD FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED; @@ -408,6 +495,12 @@ ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_p ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id); ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT; ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id); +ALTER TABLE traits ADD FOREIGN KEY (addedby) REFERENCES users (id); +ALTER TABLE traits ADD FOREIGN KEY ("group") REFERENCES traits (id); +ALTER TABLE traits_parents ADD FOREIGN KEY (trait) REFERENCES traits (id); +ALTER TABLE traits_parents ADD FOREIGN KEY (parent) REFERENCES traits (id); +ALTER TABLE traits_chars ADD FOREIGN KEY (cid) REFERENCES chars (id); +ALTER TABLE traits_chars ADD FOREIGN KEY (tid) REFERENCES traits (id); ALTER TABLE users_prefs ADD FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE; ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id); @@ -428,7 +521,7 @@ ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id); - CREATE INDEX releases_vn_vid ON releases_vn (vid); CREATE INDEX tags_vn_date ON tags_vn (date); +CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (cid, vid, COALESCE(rid, 0)); diff --git a/util/updates/update_2.19.sql b/util/updates/update_2.19.sql index b867356f..f3e07642 100644 --- a/util/updates/update_2.19.sql +++ b/util/updates/update_2.19.sql @@ -1,5 +1,3 @@ --- These changes have not been synced with the /util/sql/ files yet. - -- character database -> traits -- cgit v1.2.3