summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2011-03-19 16:56:23 +0100
committerYorhel <git@yorhel.nl>2011-03-19 16:56:23 +0100
commite161985cfa8ffbd3e0b093acd70764295f4e3284 (patch)
tree0a6de976a469a0fa247cd70d0290e7fdc1de5485 /util
parent4bd3c0ed6a2b5cd7803a27af2c84d2b7354fca32 (diff)
chardb: Synchronized DB changes with util/sql/* and dbgraph.pl
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbgraph.pl2
-rw-r--r--util/sql/all.sql9
-rw-r--r--util/sql/schema.sql97
-rw-r--r--util/updates/update_2.19.sql2
4 files changed, 105 insertions, 5 deletions
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