summaryrefslogtreecommitdiff
path: root/util/sql/schema.sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/sql/schema.sql')
-rw-r--r--util/sql/schema.sql388
1 files changed, 388 insertions, 0 deletions
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
new file mode 100644
index 00000000..d5286a94
--- /dev/null
+++ b/util/sql/schema.sql
@@ -0,0 +1,388 @@
+
+
+-- anime
+CREATE TABLE anime (
+ id integer NOT NULL PRIMARY KEY,
+ year smallint,
+ ann_id integer,
+ nfo_id varchar(200),
+ type anime_type,
+ title_romaji,
+ title_kanji,
+ lastfetch timestamptz
+);
+
+-- changes
+CREATE TABLE changes (
+ id SERIAL NOT NULL PRIMARY KEY,
+ type dbentry_type NOT NULL,
+ rev integer NOT NULL DEFAULT 1,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ requester integer NOT NULL DEFAULT 0,
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ comments text NOT NULL DEFAULT ''
+);
+
+-- producers
+CREATE TABLE producers (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ rgraph integer
+);
+
+-- producers_relations
+CREATE TABLE producers_relations (
+ pid1 integer NOT NULL,
+ pid2 integer NOT NULL,
+ relation producer_relation NOT NULL,
+ PRIMARY KEY(pid1, pid2)
+);
+
+-- producers_rev
+CREATE TABLE producers_rev (
+ id integer NOT NULL PRIMARY KEY,
+ pid integer NOT NULL DEFAULT 0,
+ type character(2) NOT NULL DEFAULT 'co',
+ name varchar(200) NOT NULL DEFAULT '',
+ original varchar(200) NOT NULL DEFAULT '',
+ website varchar(250) NOT NULL DEFAULT '',
+ lang varchar NOT NULL DEFAULT 'ja',
+ "desc" text NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ l_wp varchar(150)
+);
+
+-- quotes
+CREATE TABLE quotes (
+ vid integer NOT NULL,
+ quote varchar(250) NOT NULL,
+ PRIMARY KEY(vid, quote)
+);
+
+-- releases
+CREATE TABLE releases (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE
+);
+
+-- releases_lang
+CREATE TABLE releases_lang (
+ rid integer NOT NULL,
+ lang varchar NOT NULL,
+ PRIMARY KEY(rid, lang)
+);
+
+-- releases_media
+CREATE TABLE releases_media (
+ rid integer NOT NULL DEFAULT 0,
+ medium medium NOT NULL,
+ qty smallint NOT NULL DEFAULT 1,
+ PRIMARY KEY(rid, medium, qty)
+);
+
+-- releases_platforms
+CREATE TABLE releases_platforms (
+ rid integer NOT NULL DEFAULT 0,
+ platform character(3) NOT NULL DEFAULT 0,
+ PRIMARY KEY(rid, platform)
+);
+
+-- releases_producers
+CREATE TABLE releases_producers (
+ rid integer NOT NULL,
+ pid integer NOT NULL,
+ developer boolean NOT NULL DEFAULT FALSE,
+ publisher boolean NOT NULL DEFAULT TRUE,
+ CHECK(developer OR publisher),
+ PRIMARY KEY(pid, rid)
+);
+
+-- releases_rev
+CREATE TABLE releases_rev (
+ id integer NOT NULL PRIMARY KEY,
+ rid integer NOT NULL DEFAULT 0,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ type release_type NOT NULL DEFAULT 'complete',
+ website varchar(250) NOT NULL DEFAULT '',
+ released integer NOT NULL DEFAULT 0,
+ notes text NOT NULL DEFAULT '',
+ minage smallint,
+ gtin bigint NOT NULL DEFAULT 0,
+ patch boolean NOT NULL DEFAULT FALSE,
+ catalog varchar(50) NOT NULL DEFAULT '',
+ resolution smallint NOT NULL DEFAULT 0,
+ voiced smallint NOT NULL DEFAULT 0,
+ freeware boolean NOT NULL DEFAULT FALSE,
+ doujin boolean NOT NULL DEFAULT FALSE,
+ ani_story smallint NOT NULL DEFAULT 0,
+ ani_ero smallint NOT NULL DEFAULT 0
+);
+
+-- releases_vn
+CREATE TABLE releases_vn (
+ rid integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0,
+ PRIMARY KEY(rid, vid)
+);
+
+-- relgraphs
+CREATE TABLE relgraphs (
+ id SERIAL PRIMARY KEY,
+ svg xml NOT NULL
+);
+
+-- rlists
+CREATE TABLE rlists (
+ uid integer NOT NULL DEFAULT 0,
+ rid integer NOT NULL DEFAULT 0,
+ vstat smallint NOT NULL DEFAULT 0,
+ rstat smallint NOT NULL DEFAULT 0,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ PRIMARY KEY(uid, rid)
+);
+
+-- screenshots
+CREATE TABLE screenshots (
+ id SERIAL NOT NULL PRIMARY KEY,
+ processed boolean NOT NULL DEFAULT FALSE,
+ width smallint NOT NULL DEFAULT 0,
+ height smallint NOT NULL DEFAULT 0
+);
+
+-- sessions
+CREATE TABLE sessions (
+ uid integer NOT NULL,
+ token bytea NOT NULL,
+ expiration timestamptz NOT NULL DEFAULT (now() + '1 year'::interval),
+ PRIMARY KEY (uid, token)
+);
+
+-- stats_cache
+CREATE TABLE stats_cache (
+ section varchar(25) NOT NULL PRIMARY KEY,
+ count integer NOT NULL DEFAULT 0
+);
+
+-- tags
+CREATE TABLE tags (
+ id SERIAL NOT NULL PRIMARY KEY,
+ name varchar(250) NOT NULL UNIQUE,
+ description text NOT NULL DEFAULT '',
+ 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,
+ addedby integer NOT NULL DEFAULT 1
+);
+
+-- tags_aliases
+CREATE TABLE tags_aliases (
+ alias varchar(250) NOT NULL PRIMARY KEY,
+ tag integer NOT NULL,
+);
+
+-- tags_parents
+CREATE TABLE tags_parents (
+ tag integer NOT NULL,
+ parent integer NOT NULL,
+ PRIMARY KEY(tag, parent)
+);
+
+-- tags_vn
+CREATE TABLE tags_vn (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ uid integer NOT NULL,
+ vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0),
+ spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2),
+ PRIMARY KEY(tag, vid, uid)
+);
+
+-- tags_vn_inherit
+CREATE TABLE tags_vn_inherit (
+ tag integer NOT NULL,
+ vid integer NOT NULL,
+ users integer NOT NULL,
+ rating real NOT NULL,
+ spoiler smallint NOT NULL
+);
+
+-- threads
+CREATE TABLE threads (
+ id SERIAL NOT NULL PRIMARY KEY,
+ title varchar(50) NOT NULL DEFAULT '',
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ count smallint NOT NULL DEFAULT 0
+);
+
+-- threads_posts
+CREATE TABLE threads_posts (
+ tid integer NOT NULL DEFAULT 0,
+ num smallint NOT NULL DEFAULT 0,
+ uid integer NOT NULL DEFAULT 0,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ edited timestamptz,
+ msg text NOT NULL DEFAULT '',
+ hidden boolean NOT NULL DEFAULT FALSE,
+ PRIMARY KEY(tid, num)
+);
+
+-- threads_boards
+CREATE TABLE threads_boards (
+ tid integer NOT NULL DEFAULT 0,
+ type character(2) NOT NULL DEFAULT 0,
+ iid integer NOT NULL DEFAULT 0,
+ lastread smallint NOT NULL,
+ PRIMARY KEY(tid, type, iid)
+);
+
+-- users
+CREATE TABLE users (
+ id SERIAL NOT NULL PRIMARY KEY,
+ username varchar(20) NOT NULL UNIQUE,
+ mail varchar(100) NOT NULL,
+ rank smallint NOT NULL DEFAULT 3,
+ passwd bytea NOT NULL DEFAULT '',
+ registered timestamptz NOT NULL DEFAULT NOW(),
+ show_nsfw boolean NOT NULL DEFAULT FALSE,
+ show_list boolean NOT NULL DEFAULT TRUE,
+ c_votes integer NOT NULL DEFAULT 0,
+ c_changes integer NOT NULL DEFAULT 0,
+ skin varchar(128) NOT NULL DEFAULT '',
+ customcss text NOT NULL DEFAULT '',
+ ip inet NOT NULL DEFAULT '0.0.0.0',
+ c_tags integer NOT NULL DEFAULT 0,
+ salt character(9) NOT NULL DEFAULT '',
+ ign_votes voolean NOT NULL DEFAULT FALSE
+);
+
+-- vn
+CREATE TABLE vn (
+ id SERIAL NOT NULL PRIMARY KEY,
+ latest integer NOT NULL DEFAULT 0,
+ locked boolean NOT NULL DEFAULT FALSE,
+ hidden boolean NOT NULL DEFAULT FALSE,
+ rgraph integer,
+ c_released integer NOT NULL DEFAULT 0,
+ c_languages varchar(32) NOT NULL DEFAULT '',
+ c_platforms varchar(32) NOT NULL DEFAULT '',
+ c_popularity real,
+ c_rating real,
+ c_votecount integer NOT NULL DEFAULT 0
+);
+
+-- vn_anime
+CREATE TABLE vn_anime (
+ vid integer NOT NULL,
+ aid integer NOT NULL,
+ PRIMARY KEY(vid, aid)
+);
+
+-- vn_relations
+CREATE TABLE vn_relations (
+ vid1 integer NOT NULL DEFAULT 0,
+ vid2 integer NOT NULL DEFAULT 0,
+ relation vn_relation NOT NULL,
+ PRIMARY KEY(vid1, vid2)
+);
+
+-- vn_rev
+CREATE TABLE vn_rev (
+ id integer NOT NULL PRIMARY KEY,
+ vid integer NOT NULL DEFAULT 0,
+ title varchar(250) NOT NULL DEFAULT '',
+ original varchar(250) NOT NULL DEFAULT '',
+ alias varchar(500) NOT NULL DEFAULT '',
+ img_nsfw boolean NOT NULL DEFAULT FALSE,
+ length smallint NOT NULL DEFAULT 0,
+ "desc" text NOT NULL DEFAULT '',
+ l_wp varchar(150) NOT NULL DEFAULT '',
+ l_vnn integer NOT NULL DEFAULT 0,
+ image integer NOT NULL DEFAULT 0,
+ l_encubed varchar(100) NOT NULL DEFAULT '',
+ l_renai varchar(100) NOT NULL DEFAULT ''
+);
+
+-- vn_screenshots
+CREATE TABLE vn_screenshots (
+ vid integer NOT NULL DEFAULT 0,
+ scr integer NOT NULL DEFAULT 0,
+ nsfw boolean NOT NULL DEFAULT FALSE,
+ rid integer DEFAULT NULL,
+ PRIMARY KEY(vid, scr)
+);
+
+-- votes
+CREATE TABLE votes (
+ vid integer NOT NULL DEFAULT 0,
+ uid integer NOT NULL DEFAULT 0,
+ vote integer NOT NULL DEFAULT 0,
+ date timestamptz NOT NULL DEFAULT NOW(),
+ PRIMARY KEY(vid, uid)
+);
+
+-- wlists
+CREATE TABLE wlists (
+ uid integer NOT NULL DEFAULT 0,
+ vid integer NOT NULL DEFAULT 0,
+ wstat smallint NOT NULL DEFAULT 0,
+ added timestamptz NOT NULL DEFAULT NOW(),
+ PRIMARY KEY(uid, vid)
+);
+
+
+
+ALTER TABLE changes ADD FOREIGN KEY (requester) REFERENCES users (id);
+ALTER TABLE producers ADD FOREIGN KEY (latest) REFERENCES producers_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE producers ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid1) REFERENCES producers_rev (id);
+ALTER TABLE producers_relations ADD FOREIGN KEY (pid2) REFERENCES producers (id);
+ALTER TABLE producers_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE producers_rev ADD FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE quotes ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE releases ADD FOREIGN KEY (latest) REFERENCES releases_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE releases_lang ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_media ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_platforms ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_producers ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_producers ADD FOREIGN KEY (pid) REFERENCES producers (id);
+ALTER TABLE releases_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE releases_rev ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE releases_vn ADD FOREIGN KEY (rid) REFERENCES releases_rev (id);
+ALTER TABLE releases_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE rlists ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE rlists ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE sessions ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE tags ADD FOREIGN KEY (addedby) REFERENCES users (id);
+ALTER TABLE tags_aliases ADD FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_parents ADD FOREIGN KEY (parent) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD FOREIGN KEY (tag) REFERENCES tags (id);
+ALTER TABLE tags_vn ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE tags_vn ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE threads ADD FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE threads_posts ADD FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE threads_posts ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE threads_boards ADD FOREIGN KEY (tid) REFERENCES threads (id);
+ALTER TABLE vn ADD FOREIGN KEY (latest) REFERENCES vn_rev (id) DEFERRABLE INITIALLY DEFERRED;
+ALTER TABLE vn ADD FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
+ALTER TABLE vn_anime ADD FOREIGN KEY (aid) REFERENCES anime (id);
+ALTER TABLE vn_anime ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid1) REFERENCES vn_rev (id);
+ALTER TABLE vn_relations ADD FOREIGN KEY (vid2) REFERENCES vn (id);
+ALTER TABLE vn_rev ADD FOREIGN KEY (id) REFERENCES changes (id);
+ALTER TABLE vn_rev ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (vid) REFERENCES vn_rev (id);
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (scr) REFERENCES screenshots (id);
+ALTER TABLE vn_screenshots ADD FOREIGN KEY (rid) REFERENCES releases (id);
+ALTER TABLE votes ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE votes ADD FOREIGN KEY (vid) REFERENCES vn (id);
+ALTER TABLE wlists ADD FOREIGN KEY (uid) REFERENCES users (id);
+ALTER TABLE wlists ADD FOREIGN KEY (vid) REFERENCES vn (id);
+