summaryrefslogtreecommitdiff
path: root/util/sql
diff options
context:
space:
mode:
Diffstat (limited to 'util/sql')
l---------util/sql1
-rw-r--r--util/sql/all.sql11
-rw-r--r--util/sql/data.sql15
-rw-r--r--util/sql/func.sql749
-rw-r--r--util/sql/perms.sql166
-rw-r--r--util/sql/schema.sql983
-rw-r--r--util/sql/superuser_init.sql15
-rw-r--r--util/sql/tableattrs.sql141
-rw-r--r--util/sql/triggers.sql353
9 files changed, 1 insertions, 2433 deletions
diff --git a/util/sql b/util/sql
new file mode 120000
index 00000000..44657b95
--- /dev/null
+++ b/util/sql
@@ -0,0 +1 @@
+../sql \ No newline at end of file
diff --git a/util/sql/all.sql b/util/sql/all.sql
deleted file mode 100644
index 1e01dd3f..00000000
--- a/util/sql/all.sql
+++ /dev/null
@@ -1,11 +0,0 @@
--- NOTE: Make sure you're cd'ed in the vndb root directory before running this script
-
-\set ON_ERROR_STOP 1
-\i util/sql/schema.sql
-\i util/sql/data.sql
-\i util/sql/func.sql
-\i util/sql/editfunc.sql
-\i util/sql/tableattrs.sql
-\i util/sql/triggers.sql
-\set ON_ERROR_STOP 0
-\i util/sql/perms.sql
diff --git a/util/sql/data.sql b/util/sql/data.sql
deleted file mode 100644
index 3283c035..00000000
--- a/util/sql/data.sql
+++ /dev/null
@@ -1,15 +0,0 @@
-INSERT INTO users (id, username, mail, perm, notify_dbedit) VALUES (0, 'deleted', 'del@vndb.org', 0, FALSE);
-INSERT INTO users (id, username, mail, perm, notify_dbedit) VALUES (1, 'multi', 'multi@vndb.org', 0, FALSE);
-SELECT setval('users_id_seq', 2);
-
-INSERT INTO stats_cache (section, count) VALUES
- ('users', 1),
- ('vn', 0),
- ('producers', 0),
- ('releases', 0),
- ('chars', 0),
- ('staff', 0),
- ('tags', 0),
- ('traits', 0),
- ('threads', 0),
- ('threads_posts', 0);
diff --git a/util/sql/func.sql b/util/sql/func.sql
deleted file mode 100644
index f7eeccc1..00000000
--- a/util/sql/func.sql
+++ /dev/null
@@ -1,749 +0,0 @@
--- A small note on the function naming scheme:
--- edit_* -> revision insertion abstraction functions
--- *_notify -> functions issuing a PgSQL NOTIFY statement
--- notify_* -> functions creating entries in the notifications table
--- user_* -> functions to manage users and sessions
--- update_* -> functions to update a cache
--- *_update ^ (I should probably rename these to
--- *_calc ^ the update_* scheme for consistency)
--- I like to keep the nouns in functions singular, in contrast to the table
--- naming scheme where nouns are always plural. But I'm not very consistent
--- with that, either.
-
-
--- strip_bb_tags(text) - simple utility function to aid full-text searching
-CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$
- SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
-$$ LANGUAGE sql IMMUTABLE;
-
--- Wrapper around to_tsvector() and strip_bb_tags(), implemented in plpgsql and
--- with an associated cost function to make it opaque to the query planner and
--- ensure the query planner realizes that this function is _slow_.
-CREATE OR REPLACE FUNCTION bb_tsvector(t text) RETURNS tsvector AS $$
-BEGIN
- RETURN to_tsvector('english', public.strip_bb_tags(t));
-END;
-$$ LANGUAGE plpgsql IMMUTABLE COST 500;
-
--- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers.
-CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
- -- The website doesn't require the [spoiler] tag to be closed, the outer replace catches that case.
- SELECT regexp_replace(regexp_replace(t, '\[spoiler\].*?\[/spoiler\]', ' ', 'ig'), '\[spoiler\].*', ' ', 'i');
-$$ LANGUAGE sql IMMUTABLE;
-
-
--- Assigns a score to the relevance of a substring match, intended for use in
--- an ORDER BY clause. Exact matches are ordered first, prefix matches after
--- that, and finally a normal substring match. Not particularly fast, but
--- that's to be expected of naive substring searches.
--- Pattern must be escaped for use as a LIKE pattern.
-CREATE OR REPLACE FUNCTION substr_score(str text, pattern text) RETURNS integer AS $$
-SELECT CASE
- WHEN str ILIKE pattern THEN 0
- WHEN str ILIKE pattern||'%' THEN 1
- WHEN str ILIKE '%'||pattern||'%' THEN 2
- ELSE 3
-END;
-$$ LANGUAGE SQL;
-
-
--- update_vncache(id) - updates some c_* columns in the vn table
-CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
- UPDATE vn SET
- c_released = COALESCE((
- SELECT MIN(r.released)
- FROM releases r
- JOIN releases_vn rv ON r.id = rv.id
- WHERE rv.vid = $1
- AND r.type <> 'trial'
- AND r.hidden = FALSE
- AND r.released <> 0
- GROUP BY rv.vid
- ), 0),
- c_olang = ARRAY(
- SELECT lang
- FROM releases_lang
- WHERE id = (
- SELECT r.id
- FROM releases_vn rv
- JOIN releases r ON rv.id = r.id
- WHERE r.released > 0
- AND NOT r.hidden
- AND rv.vid = $1
- ORDER BY r.released
- LIMIT 1
- )
- ),
- c_languages = ARRAY(
- SELECT rl.lang
- FROM releases_lang rl
- JOIN releases r ON r.id = rl.id
- JOIN releases_vn rv ON r.id = rv.id
- WHERE rv.vid = $1
- AND r.type <> 'trial'
- AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
- AND r.hidden = FALSE
- GROUP BY rl.lang
- ORDER BY rl.lang
- ),
- c_platforms = ARRAY(
- SELECT rp.platform
- FROM releases_platforms rp
- JOIN releases r ON rp.id = r.id
- JOIN releases_vn rv ON rp.id = rv.id
- WHERE rv.vid = $1
- AND r.type <> 'trial'
- AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
- AND r.hidden = FALSE
- GROUP BY rp.platform
- ORDER BY rp.platform
- )
- WHERE id = $1;
-$$ LANGUAGE sql;
-
-
--- Update vn.c_popularity, c_rating and c_votecount
-CREATE OR REPLACE FUNCTION update_vnvotestats() RETURNS void AS $$
- WITH votes(vid, uid, vote) AS ( -- List of all non-ignored VN votes
- SELECT vid, uid, vote FROM ulist_vns WHERE vote IS NOT NULL AND uid NOT IN(SELECT id FROM users WHERE ign_votes)
- ), avgcount(avgcount) AS ( -- Average number of votes per VN
- SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes
- ), avgavg(avgavg) AS ( -- Average vote average
- SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) x(a)
- ), ratings(vid, count, rating) AS ( -- Ratings and vote counts
- SELECT vid, COALESCE(COUNT(uid), 0),
- COALESCE(
- ((SELECT avgcount FROM avgcount) * (SELECT avgavg FROM avgavg) + SUM(vote)::real) /
- ((SELECT avgcount FROM avgcount) + COUNT(uid)::real),
- 0)
- FROM votes
- GROUP BY vid
- ), popularities(vid, win) AS ( -- Popularity scores (before normalization)
- SELECT vid, SUM(rank)
- FROM (
- SELECT uid, vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788 FROM votes
- ) x(uid, vid, rank)
- GROUP BY vid
- ), stats(vid, rating, count, popularity) AS ( -- Combined stats
- SELECT v.id, COALESCE(r.rating, 0), COALESCE(r.count, 0)
- , p.win/(SELECT MAX(win) FROM popularities)
- FROM vn v
- LEFT JOIN ratings r ON r.vid = v.id
- LEFT JOIN popularities p ON p.vid = v.id AND p.win > 0
- )
- UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity FROM stats WHERE id = vid;
-$$ LANGUAGE SQL;
-
-
-
--- c_weight = if not_referenced then 0 else lower(c_votecount) -> higher(c_weight) && higher(*_stddev) -> higher(c_weight)
---
--- One solution:
---
--- 1 # Minimum weight for referenced images
--- * max(1, 10 - c_votecount) # 0 votes -> 10x more likely to be selected, 9+ votes -> 1x. Something non-linear may be nicer...
--- * (1+c_sexual_stddev*6) # stddev is 0..1.5, upscaled to 1x..9x more likely to be selected. Any stddev>0.8 (=4.8x more likely) is probably worth looking at
--- * (1+c_violence_stddev*6)
---
--- Extremes: 1 .. 810
---
--- Alternative solution (currently implemented):
---
--- votes_weight = max(0, 10 - c_votecount)/10 -> linear weight between 0..1, 0 being OK and 1 being BAD
--- *_stddev_weight = *_stddev/1.5 -> ^
--- weight = min(1, votes_weight*100 + sexual_stddev_weight*100 + violence_stddev_weight*100)
---
--- Extremes: 1 .. 300, easier to tune and reason about, but still linear
---
--- Neither of those solutions are grounded in theory, I've no clue how
--- statistics work. I suspect confidence intervals/levels are more appropriate
--- for this use case.
---
--- Non-'ch' image weights are currently reduced to 20% in order to prioritize
--- character images.
-CREATE OR REPLACE FUNCTION update_images_cache(image_id) RETURNS void AS $$
-BEGIN
- -- Have to dynamically construct the query here, a
- -- WHERE ($1 IS NULL OR s.id = $1)
- -- causes the planner to miss a bunch of optimizations.
- EXECUTE $sql$UPDATE images
- SET c_votecount = votecount, c_sexual_avg = sexual_avg, c_sexual_stddev = sexual_stddev
- , c_violence_avg = violence_avg, c_violence_stddev = violence_stddev, c_weight = weight
- FROM (
- SELECT s.*,
- CASE WHEN x.id IS NULL THEN 0
- ELSE greatest(1,
- ((greatest(0, 10.0 - s.votecount)/10)*100 + coalesce(s.sexual_stddev/1.5, 0)*100 + coalesce(s.violence_stddev/1.5, 0)*100)
- * (CASE WHEN (x.id).itype = 'ch' THEN 1 ELSE 0.2 END)
- )
- END AS weight
- FROM (
- SELECT i.id, count(iv.id) AS votecount
- , avg(sexual) AS sexual_avg, stddev_pop(sexual) AS sexual_stddev
- , avg(violence) AS violence_avg, stddev_pop(violence) AS violence_stddev
- FROM images i
- LEFT JOIN image_votes iv ON iv.id = i.id
- GROUP BY i.id
- ) s
- LEFT JOIN (
- SELECT image FROM vn WHERE NOT hidden AND image IS NOT NULL
- UNION ALL SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden
- UNION ALL SELECT image FROM chars WHERE NOT hidden AND image IS NOT NULL
- ) x(id) ON s.id = x.id
- $sql$ || (CASE WHEN $1 IS NULL THEN '' ELSE 'WHERE s.id = '||quote_literal($1)||'::image_id' END) || $sql$
- ) weights
- WHERE weights.id = images.id
- $sql$;
-END; $$ LANGUAGE plpgsql;
-
-
-
--- Update users.c_vns, c_votes and c_wish for one user (when given an id) or all users (when given NULL)
-CREATE OR REPLACE FUNCTION update_users_ulist_stats(integer) RETURNS void AS $$
-BEGIN
- WITH cnt(uid, votes, vns, wish) AS (
- SELECT u.id
- , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND uv.vote IS NOT NULL) -- Voted
- , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist
- , COUNT(DISTINCT uvl.vid) FILTER (WHERE NOT ul.private AND ul.id = 5) -- Wishlist
- FROM users u
- LEFT JOIN ulist_vns_labels uvl ON uvl.uid = u.id
- LEFT JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = u.id
- LEFT JOIN ulist_vns uv ON uv.uid = u.id AND uv.vid = uvl.vid
- WHERE $1 IS NULL OR u.id = $1
- GROUP BY u.id
- ) UPDATE users SET c_votes = votes, c_vns = vns, c_wish = wish FROM cnt WHERE id = uid;
-END;
-$$ LANGUAGE plpgsql; -- Don't use "LANGUAGE SQL" here; Make sure to generate a new query plan at invocation time.
-
-
-
--- Recalculate tags_vn_inherit.
--- When a vid is given, only the tags for that vid will be updated. These
--- incremental updates do not affect tags.c_items, so that may still get
--- out-of-sync.
-CREATE OR REPLACE FUNCTION tag_vn_calc(uvid integer) RETURNS void AS $$
-BEGIN
- IF uvid IS NULL THEN
- DROP INDEX IF EXISTS tags_vn_inherit_tag_vid;
- TRUNCATE tags_vn_inherit;
- ELSE
- DELETE FROM tags_vn_inherit WHERE vid = uvid;
- END IF;
-
- INSERT INTO tags_vn_inherit (tag, vid, rating, spoiler)
- -- Group votes to generate a list of directly-upvoted (vid, tag) pairs.
- -- This is essentually the same as the tag listing on VN pages.
- WITH RECURSIVE t_avg(tag, vid, vote, spoiler) AS (
- SELECT tv.tag, tv.vid, AVG(tv.vote)::real, CASE WHEN COUNT(tv.spoiler) = 0 THEN MIN(t.defaultspoil) ELSE AVG(tv.spoiler)::real END
- FROM tags_vn tv
- JOIN tags t ON t.id = tv.tag
- WHERE NOT tv.ignore AND t.state = 2
- AND vid NOT IN(SELECT id FROM vn WHERE hidden)
- AND (uvid IS NULL OR vid = uvid)
- GROUP BY tv.tag, tv.vid
- HAVING AVG(tv.vote) > 0
- -- Add parent tags
- ), t_all(lvl, tag, vid, vote, spoiler) AS (
- SELECT 15, * FROM t_avg
- UNION ALL
- SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler
- FROM t_all ta
- JOIN tags_parents tp ON tp.tag = ta.tag
- WHERE ta.lvl > 0
- )
- -- Merge
- SELECT tag, vid, AVG(vote)
- , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.4 THEN 1 ELSE 0 END)::smallint
- FROM t_all
- WHERE tag IN(SELECT id FROM tags WHERE searchable)
- GROUP BY tag, vid;
-
- IF uvid IS NULL THEN
- CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
- UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
- END IF;
-
- RETURN;
-END;
-$$ LANGUAGE plpgsql SECURITY DEFINER;
-
-
--- Recalculate traits_chars. Pretty much same thing as tag_vn_calc().
-CREATE OR REPLACE FUNCTION traits_chars_calc(ucid integer) RETURNS void AS $$
-BEGIN
- IF ucid IS NULL THEN
- DROP INDEX IF EXISTS traits_chars_tid;
- TRUNCATE traits_chars;
- ELSE
- DELETE FROM traits_chars WHERE cid = ucid;
- END IF;
-
- INSERT INTO traits_chars (tid, cid, spoil)
- -- all char<->trait links of the latest revisions, including chars inherited from child traits.
- -- (also includes non-searchable traits, because they could have a searchable trait as parent)
- WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler) AS (
- SELECT 15, tid, ct.id, spoil
- FROM chars_traits ct
- WHERE id NOT IN(SELECT id from chars WHERE hidden)
- AND (ucid IS NULL OR ct.id = ucid)
- UNION ALL
- SELECT lvl-1, tp.parent, tc.cid, tc.spoiler
- FROM traits_chars_all tc
- JOIN traits_parents tp ON tp.trait = tc.tid
- JOIN traits t ON t.id = tp.parent
- WHERE t.state = 2
- AND tc.lvl > 0
- )
- -- now grouped by (tid, cid), with non-searchable traits filtered out
- SELECT tid, cid
- , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
- FROM traits_chars_all
- WHERE tid IN(SELECT id FROM traits WHERE searchable)
- GROUP BY tid, cid;
-
- IF ucid IS NULL THEN
- CREATE INDEX traits_chars_tid ON traits_chars (tid);
- UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id);
- END IF;
- RETURN;
-END;
-$$ LANGUAGE plpgsql SECURITY DEFINER;
-
-
--- Fully recalculate all rows in stats_cache
-CREATE OR REPLACE FUNCTION update_stats_cache_full() RETURNS void AS $$
-BEGIN
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM users)-1 WHERE section = 'users';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM vn WHERE hidden = FALSE) WHERE section = 'vn';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM releases WHERE hidden = FALSE) WHERE section = 'releases';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM producers WHERE hidden = FALSE) WHERE section = 'producers';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM chars WHERE hidden = FALSE) WHERE section = 'chars';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM staff WHERE hidden = FALSE) WHERE section = 'staff';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM tags WHERE state = 2) WHERE section = 'tags';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM traits WHERE state = 2) WHERE section = 'traits';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads WHERE hidden = FALSE) WHERE section = 'threads';
- UPDATE stats_cache SET count = (SELECT COUNT(*) FROM threads_posts WHERE hidden = FALSE
- AND EXISTS(SELECT 1 FROM threads WHERE threads.id = tid AND threads.hidden = FALSE)) WHERE section = 'threads_posts';
-END;
-$$ LANGUAGE plpgsql;
-
-
--- Create ulist labels for new users.
-CREATE OR REPLACE FUNCTION ulist_labels_create(integer) RETURNS void AS $$
- INSERT INTO ulist_labels (uid, id, label, private)
- VALUES ($1, 1, 'Playing', false),
- ($1, 2, 'Finished', false),
- ($1, 3, 'Stalled', false),
- ($1, 4, 'Dropped', false),
- ($1, 5, 'Wishlist', false),
- ($1, 6, 'Blacklist', false),
- ($1, 7, 'Voted', false)
- ON CONFLICT (uid, id) DO NOTHING;
-$$ LANGUAGE SQL;
-
-
-
-
-----------------------------------------------------------
--- revision insertion abstraction --
-----------------------------------------------------------
-
--- The two functions below are utility functions used by the item-specific functions in editfunc.sql
-
--- create temporary table for generic revision info, and returns the chid of the revision being edited (or NULL).
-CREATE OR REPLACE FUNCTION edit_revtable(xtype dbentry_type, xitemid integer, xrev integer) RETURNS integer AS $$
-DECLARE
- ret integer;
- x record;
-BEGIN
- BEGIN
- CREATE TEMPORARY TABLE edit_revision (
- type dbentry_type NOT NULL,
- itemid integer,
- requester integer,
- ip inet,
- comments text,
- ihid boolean,
- ilock boolean
- );
- EXCEPTION WHEN duplicate_table THEN
- TRUNCATE edit_revision;
- END;
- SELECT INTO x id, ihid, ilock FROM changes c WHERE type = xtype AND itemid = xitemid AND rev = xrev;
- INSERT INTO edit_revision (type, itemid, ihid, ilock) VALUES (xtype, xitemid, COALESCE(x.ihid, FALSE), COALESCE(x.ilock, FALSE));
- RETURN x.id;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
-CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$
-DECLARE
- ret edit_rettype;
- xtype dbentry_type;
-BEGIN
- SELECT type INTO xtype FROM edit_revision;
- SELECT itemid INTO ret.itemid FROM edit_revision;
- -- figure out revision number
- SELECT MAX(rev)+1 INTO ret.rev FROM changes WHERE type = xtype AND itemid = ret.itemid;
- SELECT COALESCE(ret.rev, 1) INTO ret.rev;
- -- insert DB item
- IF ret.itemid IS NULL THEN
- CASE xtype
- WHEN 'v' THEN INSERT INTO vn DEFAULT VALUES RETURNING id INTO ret.itemid;
- WHEN 'r' THEN INSERT INTO releases DEFAULT VALUES RETURNING id INTO ret.itemid;
- WHEN 'p' THEN INSERT INTO producers DEFAULT VALUES RETURNING id INTO ret.itemid;
- WHEN 'c' THEN INSERT INTO chars DEFAULT VALUES RETURNING id INTO ret.itemid;
- WHEN 's' THEN INSERT INTO staff DEFAULT VALUES RETURNING id INTO ret.itemid;
- WHEN 'd' THEN INSERT INTO docs DEFAULT VALUES RETURNING id INTO ret.itemid;
- END CASE;
- END IF;
- -- insert change
- INSERT INTO changes (type, itemid, rev, requester, ip, comments, ihid, ilock)
- SELECT type, ret.itemid, ret.rev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO ret.chid;
- RETURN ret;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
--- Check for stuff to be done when an item has been changed
-CREATE OR REPLACE FUNCTION edit_committed(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
-DECLARE
- xoldchid integer;
-BEGIN
- SELECT id INTO xoldchid FROM changes WHERE type = xtype AND itemid = xedit.itemid AND rev = xedit.rev-1;
-
- -- Set producers.rgraph to NULL and notify when:
- -- 1. There's a new producer entry with some relations
- -- 2. The producer name/type/language has changed
- -- 3. The producer relations have been changed
- IF xtype = 'p' THEN
- IF -- 1.
- (xoldchid IS NULL AND EXISTS(SELECT 1 FROM producers_relations_hist WHERE chid = xedit.chid))
- OR (xoldchid IS NOT NULL AND (
- -- 2.
- EXISTS(SELECT 1 FROM producers_hist p1, producers_hist p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.chid = xoldchid AND p2.chid = xedit.chid)
- -- 3.
- OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid)
- OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid)
- ))
- THEN
- UPDATE producers SET rgraph = NULL WHERE id = xedit.itemid;
- NOTIFY relgraph; -- This notify is not done by the producer_relgraph_notify trigger for new entries or if rgraph was already NULL
- END IF;
- END IF;
-
- -- Set vn.rgraph to NULL and notify when:
- -- 1. There's a new vn entry with some relations
- -- 2. The vn title has changed
- -- 3. The vn relations have been changed
- IF xtype = 'v' THEN
- IF -- 1.
- (xoldchid IS NULL AND EXISTS(SELECT 1 FROM vn_relations_hist WHERE chid = xedit.chid))
- OR (xoldchid IS NOT NULL AND (
- -- 2.
- EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE v2.title <> v1.title AND v1.chid = xoldchid AND v2.chid = xedit.chid)
- -- 3.
- OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid)
- OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid)
- ))
- THEN
- UPDATE vn SET rgraph = NULL WHERE id = xedit.itemid;
- NOTIFY relgraph;
- END IF;
- END IF;
-
- -- Set c_search to NULL and notify when
- -- 1. A new VN entry is created
- -- 2. The vn title/original/alias has changed
- IF xtype = 'v' THEN
- IF -- 1.
- xoldchid IS NULL OR
- -- 2.
- EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE (v2.title <> v1.title OR v2.original <> v1.original OR v2.alias <> v1.alias) AND v1.chid = xoldchid AND v2.chid = xedit.chid)
- THEN
- UPDATE vn SET c_search = NULL WHERE id = xedit.itemid;
- NOTIFY vnsearch;
- END IF;
- END IF;
-
- -- Set related vn.c_search columns to NULL and notify when
- -- 1. A new release is created
- -- 2. A release has been hidden or unhidden
- -- 3. The release title/original has changed
- -- 4. The releases_vn table differs from a previous revision
- IF xtype = 'r' THEN
- IF -- 1.
- xoldchid IS NULL OR
- -- 2.
- EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = xedit.chid AND c2.id = xoldchid) OR
- -- 3.
- EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = xedit.chid) OR
- -- 4.
- EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid) OR
- EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid)
- THEN
- UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid));
- NOTIFY vnsearch;
- END IF;
- END IF;
-
- -- Call update_vncache() for related VNs when a release has been created or edited
- -- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity)
- IF xtype = 'r' THEN
- PERFORM update_vncache(vid) FROM (
- SELECT DISTINCT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid)
- ) AS v(vid);
- END IF;
-
- -- Call traits_chars_calc() for characters to update the traits cache
- IF xtype = 'c' THEN
- PERFORM traits_chars_calc(xedit.itemid);
- END IF;
-
- -- Call notify_dbdel() if an entry has been deleted
- -- Call notify_listdel() if a vn/release entry has been deleted
- IF xoldchid IS NOT NULL
- AND EXISTS(SELECT 1 FROM changes WHERE id = xoldchid AND NOT ihid)
- AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND ihid)
- THEN
- PERFORM notify_dbdel(xtype, xedit);
- IF xtype = 'v' OR xtype = 'r' THEN
- PERFORM notify_listdel(xtype, xedit);
- END IF;
- END IF;
-
- -- Call notify_dbedit() if a non-hidden entry has been edited
- IF xoldchid IS NOT NULL AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND NOT ihid)
- THEN
- PERFORM notify_dbedit(xtype, xedit);
- END IF;
-
- -- Make sure all visual novels linked to a release have a corresponding entry
- -- in ulist_vns for users who have the release in rlists. This is action (3) in
- -- update_vnlist_rlist().
- IF xtype = 'r' AND xoldchid IS NOT NULL
- THEN
- INSERT INTO ulist_vns (uid, vid)
- SELECT rl.uid, rv.vid FROM rlists rl JOIN releases_vn rv ON rv.id = rl.rid WHERE rl.rid = xedit.itemid
- ON CONFLICT (uid, vid) DO NOTHING;
- END IF;
-
- -- Call update_images_cache() where appropriate
- IF xtype = 'c'
- THEN
- PERFORM update_images_cache(image) FROM chars_hist WHERE chid IN(xoldchid,xedit.chid) AND image IS NOT NULL;
- END IF;
- IF xtype = 'v'
- THEN
- PERFORM update_images_cache(image) FROM vn_hist WHERE chid IN(xoldchid,xedit.chid) AND image IS NOT NULL;
- PERFORM update_images_cache(scr) FROM vn_screenshots_hist WHERE chid IN(xoldchid,xedit.chid);
- END IF;
-END;
-$$ LANGUAGE plpgsql;
-
-
-
-
-----------------------------------------------------------
--- notification functions --
-----------------------------------------------------------
-
-
--- called when an entry has been deleted
-CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'dbdel'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
- FROM changes h
- -- join info about the deletion itself
- JOIN changes h2 ON h2.id = xedit.chid
- -- Fetch the latest name/title of the entry
- -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
- JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
- UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
- UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
- UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
- UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid
- UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
- ) x(title) ON true
- WHERE h.type = xtype AND h.itemid = xedit.itemid
- AND h.requester <> 1 -- exclude Multi
- AND h.requester <> h2.requester; -- exclude the user who deleted the entry
-$$ LANGUAGE sql;
-
-
-
--- Called when a non-deleted item has been edited.
-CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'dbedit'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
- FROM changes h
- -- join info about the edit itself
- JOIN changes h2 ON h2.id = xedit.chid
- -- Fetch the latest name/title of the entry
- JOIN ( SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
- UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
- UNION SELECT p.name FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
- UNION SELECT c.name FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
- UNION SELECT d.title FROM docs d WHERE xtype = 'd' AND d.id = xedit.itemid
- UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
- ) x(title) ON true
- WHERE h.type = xtype AND h.itemid = xedit.itemid
- AND h.requester <> h2.requester -- exclude the user who edited the entry
- AND h2.requester <> 1 -- exclude edits by Multi
- -- exclude users who don't want this notify
- AND EXISTS(SELECT 1 FROM users u WHERE u.id = h.requester AND notify_dbedit);
-$$ LANGUAGE sql;
-
-
-
--- called when a VN/release entry has been deleted
-CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
- -- look for users who should get this notify
- FROM (
- SELECT uid FROM ulist_vns WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
- ) u
- -- fetch info about this edit
- JOIN changes c ON c.id = xedit.chid
- JOIN (
- SELECT title FROM vn WHERE xtype = 'v' AND id = xedit.itemid
- UNION SELECT title FROM releases WHERE xtype = 'r' AND id = xedit.itemid
- ) x ON true
- WHERE c.requester <> u.uid;
-$$ LANGUAGE sql;
-
-
-
-
-----------------------------------------------------------
--- user management --
-----------------------------------------------------------
--- XXX: These functions run with the permissions of the 'vndb' user.
-
-
--- Returns the raw scrypt parameters (N, r, p and salt) for this user, in order
--- to create an encrypted pass. Returns NULL if this user does not have a valid
--- password.
-CREATE OR REPLACE FUNCTION user_getscryptargs(integer) RETURNS bytea AS $$
- SELECT
- CASE WHEN length(passwd) = 46 THEN substring(passwd from 1 for 14) ELSE NULL END
- FROM users WHERE id = $1
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Create a new web session for this user (uid, scryptpass, token)
-CREATE OR REPLACE FUNCTION user_login(integer, bytea, bytea) RETURNS boolean AS $$
- INSERT INTO sessions (uid, token, expires, type) SELECT $1, $3, NOW() + '1 month', 'web' FROM users
- WHERE length($2) = 46 AND length($3) = 20
- AND id = $1 AND passwd = $2
- RETURNING true
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
-CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$
- DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'web'
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Returns true if the given session token is valid.
--- As a side effect, this also extends the expiration time of web sessions.
-CREATE OR REPLACE FUNCTION user_isvalidsession(integer, bytea, session_type) RETURNS bool AS $$
- UPDATE sessions SET expires = NOW() + '1 month'
- WHERE uid = $1 AND token = $2 AND type = $3 AND $3 = 'web'
- AND expires < NOW() + '1 month'::interval - '6 hours'::interval;
- SELECT true FROM sessions WHERE uid = $1 AND token = $2 AND type = $3 AND expires > NOW();
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
-CREATE OR REPLACE FUNCTION user_emailexists(text, integer) RETURNS boolean AS $$
- SELECT true FROM users WHERE lower(mail) = lower($1) AND ($2 IS NULL OR id <> $2) LIMIT 1
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Create a password reset token. args: email, token. Returns: user id.
--- Doesn't work for usermods, otherwise an attacker could use this function to
--- gain access to all user's emails by obtaining a reset token of a usermod.
--- Ideally Postgres itself would send the user an email so that the application
--- calling this function doesn't even get the token, and thus can't get access
--- to someone's account. But alas, that'd require a separate process.
-CREATE OR REPLACE FUNCTION user_resetpass(text, bytea) RETURNS integer AS $$
- INSERT INTO sessions (uid, token, expires, type)
- SELECT id, $2, NOW()+'1 week', 'pass' FROM users
- WHERE lower(mail) = lower($1) AND length($2) = 20 AND perm & 128 = 0
- RETURNING uid
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_reset_token, new_pass
-CREATE OR REPLACE FUNCTION user_setpass(integer, bytea, bytea) RETURNS boolean AS $$
- WITH upd(id) AS (
- UPDATE users SET passwd = $3
- WHERE id = $1
- AND length($3) = 46
- AND ( (passwd = $2 AND length($2) = 46)
- OR EXISTS(SELECT 1 FROM sessions WHERE uid = $1 AND token = $2 AND type = 'pass' AND expires > NOW())
- )
- RETURNING id
- ), del AS( -- Not referenced, but still guaranteed to run
- DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
- )
- SELECT true FROM upd
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Internal function, used to verify whether user ($2 with session $3) is
--- allowed to access sensitive data from user $1.
-CREATE OR REPLACE FUNCTION user_isauth(integer, integer, bytea) RETURNS boolean AS $$
- SELECT true FROM users
- WHERE id = $2
- AND EXISTS(SELECT 1 FROM sessions WHERE uid = $2 AND token = $3 AND type = 'web')
- AND ($2 = $1 OR perm & 128 = 128)
-$$ LANGUAGE SQL;
-
-
--- uid of user email to get, uid currently logged in, session token of currently logged in.
--- Ensures that only the user itself or a useradmin can get someone's email address.
-CREATE OR REPLACE FUNCTION user_getmail(integer, integer, bytea) RETURNS text AS $$
- SELECT mail FROM users WHERE id = $1 AND user_isauth($1, $2, $3)
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Set a token to change a user's email address.
--- Args: uid, web-token, new-email-token, email
-CREATE OR REPLACE FUNCTION user_setmail_token(integer, bytea, bytea, text) RETURNS void AS $$
- INSERT INTO sessions (uid, token, expires, type, mail)
- SELECT id, $3, NOW()+'1 week', 'mail', $4 FROM users
- WHERE id = $1 AND user_isauth($1, $1, $2) AND length($3) = 20
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
--- Actually change a user's email address, given a valid token.
-CREATE OR REPLACE FUNCTION user_setmail_confirm(integer, bytea) RETURNS boolean AS $$
- WITH u(mail) AS (
- DELETE FROM sessions WHERE uid = $1 AND token = $2 AND type = 'mail' AND expires > NOW() RETURNING mail
- )
- UPDATE users SET mail = (SELECT mail FROM u) WHERE id = $1 AND EXISTS(SELECT 1 FROM u) RETURNING true;
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
-CREATE OR REPLACE FUNCTION user_setperm(integer, integer, bytea, integer) RETURNS void AS $$
- UPDATE users SET perm = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
-CREATE OR REPLACE FUNCTION user_admin_setpass(integer, integer, bytea, bytea) RETURNS void AS $$
- WITH upd(id) AS (
- UPDATE users SET passwd = $4 WHERE id = $1 AND user_isauth(-1, $2, $3) AND length($4) = 46 RETURNING id
- )
- DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
-$$ LANGUAGE SQL SECURITY DEFINER;
-
-
-CREATE OR REPLACE FUNCTION user_admin_setmail(integer, integer, bytea, text) RETURNS void AS $$
- UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
-$$ LANGUAGE SQL SECURITY DEFINER;
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
deleted file mode 100644
index d1b452c4..00000000
--- a/util/sql/perms.sql
+++ /dev/null
@@ -1,166 +0,0 @@
--- vndb_site
-
-DROP OWNED BY vndb_site;
-GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_site;
-GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_site;
-GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_site;
-
-GRANT SELECT, INSERT ON anime TO vndb_site;
-GRANT SELECT, INSERT ON changes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON chars TO vndb_site;
-GRANT SELECT, INSERT ON chars_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON chars_traits TO vndb_site;
-GRANT SELECT, INSERT ON chars_traits_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON chars_vns TO vndb_site;
-GRANT SELECT, INSERT ON chars_vns_hist TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON docs TO vndb_site;
-GRANT SELECT, INSERT ON docs_hist TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON images TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON image_votes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON producers TO vndb_site;
-GRANT SELECT, INSERT ON producers_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON producers_relations TO vndb_site;
-GRANT SELECT, INSERT ON producers_relations_hist TO vndb_site;
-GRANT SELECT ON quotes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON releases TO vndb_site;
-GRANT SELECT, INSERT ON releases_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON releases_lang TO vndb_site;
-GRANT SELECT, INSERT ON releases_lang_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON releases_media TO vndb_site;
-GRANT SELECT, INSERT ON releases_media_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON releases_platforms TO vndb_site;
-GRANT SELECT, INSERT ON releases_platforms_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON releases_producers TO vndb_site;
-GRANT SELECT, INSERT ON releases_producers_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON releases_vn TO vndb_site;
-GRANT SELECT, INSERT ON releases_vn_hist TO vndb_site;
-GRANT SELECT ON relgraphs TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_site;
--- No access to the 'sessions' table, managed by the user_* functions.
-GRANT SELECT ON shop_denpa TO vndb_site;
-GRANT SELECT ON shop_dlsite TO vndb_site;
-GRANT SELECT ON shop_jlist TO vndb_site;
-GRANT SELECT ON shop_mg TO vndb_site;
-GRANT SELECT ON shop_playasia TO vndb_site;
-GRANT SELECT, INSERT, UPDATE ON staff TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON staff_alias TO vndb_site;
-GRANT SELECT, INSERT ON staff_alias_hist TO vndb_site;
-GRANT SELECT, INSERT ON staff_hist TO vndb_site;
-GRANT SELECT, UPDATE ON stats_cache TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON tags TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON tags_aliases TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON tags_parents TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON tags_vn TO vndb_site;
-GRANT SELECT ON tags_vn_inherit TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON threads TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON threads_boards TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_options TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON threads_poll_votes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON threads_posts TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON traits TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON traits_chars TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON traits_parents TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
-
--- users table is special; The 'perm', 'passwd' and 'mail' columns are
--- protected and can only be accessed through the user_* functions.
-GRANT SELECT (id, username, registered, perm, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish, ulist_votes, ulist_vnlist, ulist_wish, c_imgvotes),
- INSERT (id, username, mail, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish, ulist_votes, ulist_vnlist, ulist_wish, c_imgvotes),
- UPDATE ( username, registered, c_votes, c_changes, ip, c_tags, ign_votes, email_confirmed, skin, customcss, filter_vn, filter_release, show_nsfw, hide_list, notify_dbedit, notify_announce, vn_list_own, vn_list_wish, tags_all, tags_cont, tags_ero, tags_tech, spoilers, traits_sexual, nodistract_can, nodistract_noads, nodistract_nofancy, support_can, support_enabled, uniname_can, uniname, pubskin_can, pubskin_enabled, c_vns, c_wish, ulist_votes, ulist_vnlist, ulist_wish, c_imgvotes) ON users TO vndb_site;
-
-GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site;
-GRANT SELECT, INSERT ON vn_anime_hist TO vndb_site;
-GRANT SELECT, INSERT ON vn_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON vn_relations TO vndb_site;
-GRANT SELECT, INSERT ON vn_relations_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON vn_screenshots TO vndb_site;
-GRANT SELECT, INSERT ON vn_screenshots_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON vn_seiyuu TO vndb_site;
-GRANT SELECT, INSERT ON vn_seiyuu_hist TO vndb_site;
-GRANT SELECT, INSERT, DELETE ON vn_staff TO vndb_site;
-GRANT SELECT, INSERT ON vn_staff_hist TO vndb_site;
-GRANT SELECT, INSERT ON wikidata TO vndb_site;
-
-
-
-
--- vndb_multi
--- (Assuming all modules are loaded)
-
-DROP OWNED BY vndb_multi;
-GRANT CONNECT, TEMP ON DATABASE :DBNAME TO vndb_multi;
-GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO vndb_multi;
-GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vndb_multi;
-
-GRANT SELECT, UPDATE ON anime TO vndb_multi;
-GRANT SELECT ON changes TO vndb_multi;
-GRANT SELECT ON chars TO vndb_multi;
-GRANT SELECT ON chars_hist TO vndb_multi;
-GRANT SELECT ON chars_traits TO vndb_multi;
-GRANT SELECT ON chars_vns TO vndb_multi;
-GRANT SELECT ON docs TO vndb_multi;
-GRANT SELECT ON docs_hist TO vndb_multi;
-GRANT SELECT, UPDATE ON images TO vndb_multi;
-GRANT SELECT ON image_votes TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON login_throttle TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON notifications TO vndb_multi;
-GRANT SELECT, UPDATE ON producers TO vndb_multi;
-GRANT SELECT ON producers_hist TO vndb_multi;
-GRANT SELECT ON producers_relations TO vndb_multi;
-GRANT SELECT ON quotes TO vndb_multi;
-GRANT SELECT ON releases TO vndb_multi;
-GRANT SELECT ON releases_hist TO vndb_multi;
-GRANT SELECT ON releases_lang TO vndb_multi;
-GRANT SELECT ON releases_media TO vndb_multi;
-GRANT SELECT ON releases_platforms TO vndb_multi;
-GRANT SELECT ON releases_producers TO vndb_multi;
-GRANT SELECT ON releases_vn TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON relgraphs TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON rlists TO vndb_multi;
-GRANT SELECT (expires) ON sessions TO vndb_multi;
-GRANT DELETE ON sessions TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON shop_denpa TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON shop_dlsite TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON shop_jlist TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON shop_mg TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON shop_playasia TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON shop_playasia_gtin TO vndb_multi;
-GRANT SELECT ON staff TO vndb_multi;
-GRANT SELECT ON staff_alias TO vndb_multi;
-GRANT SELECT ON staff_alias_hist TO vndb_multi;
-GRANT SELECT ON staff_hist TO vndb_multi;
-GRANT SELECT, UPDATE ON stats_cache TO vndb_multi;
-GRANT SELECT ON tags TO vndb_multi;
-GRANT SELECT ON tags_aliases TO vndb_multi;
-GRANT SELECT ON tags_parents TO vndb_multi;
-GRANT SELECT ON tags_vn TO vndb_multi;
-GRANT SELECT ON tags_vn_inherit TO vndb_multi; -- tag_vn_calc() is SECURITY DEFINER due to index drop/create, so no extra perms needed here
-GRANT SELECT ON threads TO vndb_multi;
-GRANT SELECT ON threads_boards TO vndb_multi;
-GRANT SELECT ON threads_posts TO vndb_multi;
-GRANT SELECT, UPDATE ON traits TO vndb_multi;
-GRANT SELECT ON traits_chars TO vndb_multi; -- traits_chars_calc() is SECURITY DEFINER
-GRANT SELECT ON traits_parents TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
-
-GRANT SELECT (id, username, registered, c_votes, c_changes, c_tags, c_vns, c_wish, c_imgvotes, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
- UPDATE ( c_votes, c_changes, c_tags, c_vns, c_wish, c_imgvotes) ON users TO vndb_multi;
-GRANT DELETE ON users TO vndb_multi;
-
-GRANT SELECT, UPDATE ON vn TO vndb_multi;
-GRANT SELECT ON vn_anime TO vndb_multi;
-GRANT SELECT ON vn_hist TO vndb_multi;
-GRANT SELECT ON vn_relations TO vndb_multi;
-GRANT SELECT ON vn_screenshots TO vndb_multi;
-GRANT SELECT ON vn_screenshots_hist TO vndb_multi;
-GRANT SELECT ON vn_seiyuu TO vndb_multi;
-GRANT SELECT ON vn_staff TO vndb_multi;
-GRANT SELECT ON vn_staff_hist TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE ON wikidata TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
deleted file mode 100644
index a34bc998..00000000
--- a/util/sql/schema.sql
+++ /dev/null
@@ -1,983 +0,0 @@
--- Convention for database items with version control:
---
--- CREATE TABLE items ( -- dbentry_type=x
--- id SERIAL PRIMARY KEY,
--- locked boolean NOT NULL DEFAULT FALSE,
--- hidden boolean NOT NULL DEFAULT FALSE,
--- -- item-specific columns here
--- );
--- CREATE TABLE items_hist ( -- History of the 'items' table
--- chid integer NOT NULL, -- references changes.id
--- -- item-specific columns here
--- );
---
--- The '-- dbentry_type=x' comment is required, and is used by
--- util/sqleditfunc.pl to generate the correct editing functions. The history
--- of the 'locked' and 'hidden' flags is recorded in the changes table. It's
--- possible for 'items' to have more item-specific columns than 'items_hist'.
--- Some columns are caches or otherwise autogenerated, and do not need to be
--- versioned.
---
--- item-related tables work roughly the same:
---
--- CREATE TABLE items_field (
--- id integer, -- references items.id
--- -- field-specific columns here
--- );
--- CREATE TABLE items_field_hist ( -- History of the 'items_field' table
--- chid integer, -- references changes.id
--- -- field-specific columns here
--- );
---
--- The changes and *_hist tables contain all the data. In a sense, the other
--- tables related to the item are just a cache/view into the latest versions.
--- All modifications to the item tables has to go through the edit_* functions
--- in editfunc.sql, these are also responsible for keeping things synchronized.
---
--- Columns marked with a '[pub]' comment on the same line are included in the
--- public database dump. Be aware that not all properties of the to-be-dumped
--- data is annotated in this file. Which tables and which rows are exported is
--- defined in util/dbdump.pl.
---
--- Note: Every CREATE TABLE clause and each column should be on a separate
--- line. This file is parsed by lib/VNDB/Schema.pm and it doesn't implement a
--- full SQL query parser.
-
-
--- data types
-
-CREATE TYPE anime_type AS ENUM ('tv', 'ova', 'mov', 'oth', 'web', 'spe', 'mv');
-CREATE TYPE blood_type AS ENUM ('unknown', 'a', 'b', 'ab', 'o');
-CREATE TYPE board_type AS ENUM ('an', 'db', 'ge', 'v', 'p', 'u');
-CREATE TYPE char_role AS ENUM ('main', 'primary', 'side', 'appears');
-CREATE TYPE credit_type AS ENUM ('scenario', 'chardesign', 'art', 'music', 'songs', 'director', 'staff');
-CREATE TYPE cup_size AS ENUM ('', 'AAA', 'AA', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z');
-CREATE TYPE dbentry_type AS ENUM ('v', 'r', 'p', 'c', 's', 'd');
-CREATE TYPE edit_rettype AS (itemid integer, chid integer, rev integer);
-CREATE TYPE gender AS ENUM ('unknown', 'm', 'f', 'b');
-CREATE TYPE language AS ENUM ('ar', 'bg', 'ca', 'cs', 'da', 'de', 'el', 'en', 'eo', 'es', 'fi', 'fr', 'gd', 'he', 'hr', 'hu', 'id', 'it', 'ja', 'ko', 'mk', 'ms', 'lt', 'lv', 'nl', 'no', 'pl', 'pt-pt', 'pt-br', 'ro', 'ru', 'sk', 'sl', 'sv', 'ta', 'th', 'tr', 'uk', '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');
-CREATE TYPE notification_ltype AS ENUM ('v', 'r', 'p', 'c', 't', 's', 'd');
-CREATE TYPE platform AS ENUM ('win', 'dos', 'lin', 'mac', 'ios', 'and', 'dvd', 'bdp', 'fmt', 'gba', 'gbc', 'msx', 'nds', 'nes', 'p88', 'p98', 'pce', 'pcf', 'psp', 'ps1', 'ps2', 'ps3', 'ps4', 'psv', 'drc', 'sat', 'sfc', 'swi', 'wii', 'wiu', 'n3d', 'x68', 'xb1', 'xb3', 'xbo', 'web', 'oth');
-CREATE TYPE producer_type AS ENUM ('co', 'in', 'ng');
-CREATE TYPE producer_relation AS ENUM ('old', 'new', 'sub', 'par', 'imp', 'ipa', 'spa', 'ori');
-CREATE TYPE release_type AS ENUM ('complete', 'partial', 'trial');
-CREATE TYPE tag_category AS ENUM('cont', 'ero', 'tech');
-CREATE TYPE vn_relation AS ENUM ('seq', 'preq', 'set', 'alt', 'char', 'side', 'par', 'ser', 'fan', 'orig');
-CREATE TYPE resolution AS ENUM ('unknown', 'nonstandard', '640x480', '800x600', '1024x768', '1280x960', '1600x1200', '640x400', '960x600', '960x640', '1024x576', '1024x600', '1024x640', '1280x720', '1280x800', '1366x768', '1600x900', '1920x1080');
-CREATE TYPE session_type AS ENUM ('web', 'pass', 'mail');
-
-CREATE TYPE image_type AS ENUM ('ch', 'cv', 'sf');
-CREATE TYPE image_id AS (itype image_type, id int);
-
--- Sequences used for ID generation of items not in the DB
-CREATE SEQUENCE covers_seq;
-CREATE SEQUENCE charimg_seq;
-CREATE SEQUENCE screenshots_seq;
-
-
-
--- anime
-CREATE TABLE anime (
- id integer NOT NULL PRIMARY KEY, -- [pub]
- year smallint, -- [pub]
- ann_id integer, -- [pub]
- nfo_id varchar(200), -- [pub]
- type anime_type, -- [pub]
- title_romaji varchar(250), -- [pub]
- title_kanji varchar(250), -- [pub]
- lastfetch timestamptz
-);
-
--- changes
-CREATE TABLE changes (
- id SERIAL PRIMARY KEY,
- type dbentry_type NOT NULL,
- itemid integer 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 '',
- ihid boolean NOT NULL DEFAULT FALSE,
- ilock boolean NOT NULL DEFAULT FALSE
-);
-
--- chars
-CREATE TABLE chars ( -- dbentry_type=c
- id SERIAL PRIMARY KEY, -- [pub]
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- name varchar(250) NOT NULL DEFAULT '', -- [pub]
- original varchar(250) NOT NULL DEFAULT '', -- [pub]
- alias varchar(500) NOT NULL DEFAULT '', -- [pub]
- image image_id CONSTRAINT chars_image_check CHECK((image).itype = 'ch'), -- [pub]
- "desc" text NOT NULL DEFAULT '', -- [pub]
- gender gender NOT NULL DEFAULT 'unknown', -- [pub]
- s_bust smallint NOT NULL DEFAULT 0, -- [pub]
- s_waist smallint NOT NULL DEFAULT 0, -- [pub]
- s_hip smallint NOT NULL DEFAULT 0, -- [pub]
- b_month smallint NOT NULL DEFAULT 0, -- [pub]
- b_day smallint NOT NULL DEFAULT 0, -- [pub]
- height smallint NOT NULL DEFAULT 0, -- [pub]
- weight smallint, -- [pub]
- bloodt blood_type NOT NULL DEFAULT 'unknown', -- [pub]
- main integer, -- [pub] chars.id
- main_spoil smallint NOT NULL DEFAULT 0, -- [pub]
- cup_size cup_size NOT NULL DEFAULT '', -- [pub]
- age smallint -- [pub]
-);
-
--- chars_hist
-CREATE TABLE chars_hist (
- chid integer NOT NULL PRIMARY KEY,
- name varchar(250) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- image image_id CONSTRAINT chars_hist_image_check CHECK((image).itype = 'ch'),
- "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,
- bloodt blood_type NOT NULL DEFAULT 'unknown',
- main integer, -- chars.id
- main_spoil smallint NOT NULL DEFAULT 0,
- cup_size cup_size NOT NULL DEFAULT '',
- age smallint
-);
-
--- chars_traits
-CREATE TABLE chars_traits (
- id integer NOT NULL, -- [pub]
- tid integer NOT NULL, -- [pub] traits.id
- spoil smallint NOT NULL DEFAULT 0, -- [pub]
- PRIMARY KEY(id, tid)
-);
-
--- chars_traits_hist
-CREATE TABLE chars_traits_hist (
- chid integer NOT NULL,
- tid integer NOT NULL, -- traits.id
- spoil smallint NOT NULL DEFAULT 0,
- PRIMARY KEY(chid, tid)
-);
-
--- chars_vns
-CREATE TABLE chars_vns (
- id integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub] vn.id
- rid integer NULL, -- [pub] releases.id
- spoil smallint NOT NULL DEFAULT 0, -- [pub]
- role char_role NOT NULL DEFAULT 'main' -- [pub]
-);
-
--- chars_vns_hist
-CREATE TABLE chars_vns_hist (
- chid integer NOT NULL,
- vid integer NOT NULL, -- vn.id
- rid integer NULL, -- releases.id
- spoil smallint NOT NULL DEFAULT 0,
- role char_role NOT NULL DEFAULT 'main'
-);
-
--- docs
-CREATE TABLE docs ( -- dbentry_type=d
- id SERIAL PRIMARY KEY, -- [pub]
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- title varchar(200) NOT NULL DEFAULT '', -- [pub]
- content text NOT NULL DEFAULT '', -- [pub]
- html text -- cache, can be manually updated with util/update-docs-html-cache.pl
-);
-
--- docs_hist
-CREATE TABLE docs_hist (
- chid integer NOT NULL PRIMARY KEY,
- title varchar(200) NOT NULL DEFAULT '',
- content text NOT NULL DEFAULT '',
- html text -- cache
-);
-
--- images
-CREATE TABLE images (
- id image_id NOT NULL PRIMARY KEY, -- [pub]
- width smallint NOT NULL, -- [pub]
- height smallint NOT NULL, -- [pub]
- c_votecount integer NOT NULL DEFAULT 0, -- [pub] (cached columns are marked [pub] for easy querying...)
- c_sexual_avg float, -- [pub]
- c_sexual_stddev float, -- [pub]
- c_violence_avg float, -- [pub]
- c_violence_stddev float, -- [pub]
- c_weight float NOT NULL DEFAULT 0 -- [pub]
-);
-
--- image_votes
-CREATE TABLE image_votes (
- id image_id NOT NULL, -- [pub]
- uid integer, -- [pub]
- sexual smallint NOT NULL CHECK(sexual >= 0 AND sexual <= 2), -- [pub]
- violence smallint NOT NULL CHECK(violence >= 0 AND violence <= 2), -- [pub]
- date timestamptz NOT NULL DEFAULT NOW() -- [pub]
-);
-
--- login_throttle
-CREATE TABLE login_throttle (
- ip inet NOT NULL PRIMARY KEY,
- timeout timestamptz NOT NULL
-);
-
--- notifications
-CREATE TABLE notifications (
- id serial PRIMARY KEY,
- uid integer NOT NULL,
- date timestamptz NOT NULL DEFAULT NOW(),
- read timestamptz,
- ntype notification_ntype NOT NULL,
- ltype notification_ltype NOT NULL,
- iid integer NOT NULL,
- subid integer,
- c_title text NOT NULL,
- c_byuser integer NOT NULL DEFAULT 0
-);
-
--- producers
-CREATE TABLE producers ( -- dbentry_type=p
- id SERIAL PRIMARY KEY, -- [pub]
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- type producer_type NOT NULL DEFAULT 'co', -- [pub]
- name varchar(200) NOT NULL DEFAULT '', -- [pub]
- original varchar(200) NOT NULL DEFAULT '', -- [pub]
- website varchar(250) NOT NULL DEFAULT '', -- [pub]
- lang language NOT NULL DEFAULT 'ja', -- [pub]
- "desc" text NOT NULL DEFAULT '', -- [pub]
- alias varchar(500) NOT NULL DEFAULT '', -- [pub]
- l_wp varchar(150), -- [pub] (deprecated)
- rgraph integer, -- relgraphs.id
- l_wikidata integer -- [pub]
-);
-
--- producers_hist
-CREATE TABLE producers_hist (
- chid integer NOT NULL PRIMARY KEY,
- type producer_type NOT NULL DEFAULT 'co',
- name varchar(200) NOT NULL DEFAULT '',
- original varchar(200) NOT NULL DEFAULT '',
- website varchar(250) NOT NULL DEFAULT '',
- lang language NOT NULL DEFAULT 'ja',
- "desc" text NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- l_wp varchar(150),
- l_wikidata integer
-);
-
--- producers_relations
-CREATE TABLE producers_relations (
- id integer NOT NULL, -- [pub]
- pid integer NOT NULL, -- [pub] producers.id
- relation producer_relation NOT NULL, -- [pub]
- PRIMARY KEY(id, pid)
-);
-
--- producers_relations_hist
-CREATE TABLE producers_relations_hist (
- chid integer NOT NULL,
- pid integer NOT NULL, -- producers.id
- relation producer_relation NOT NULL,
- PRIMARY KEY(chid, pid)
-);
-
--- quotes
-CREATE TABLE quotes (
- vid integer NOT NULL,
- quote varchar(250) NOT NULL,
- PRIMARY KEY(vid, quote)
-);
-
--- releases
-CREATE TABLE releases ( -- dbentry_type=r
- id SERIAL PRIMARY KEY, -- [pub]
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- title varchar(300) NOT NULL DEFAULT '', -- [pub]
- original varchar(250) NOT NULL DEFAULT '', -- [pub]
- type release_type NOT NULL DEFAULT 'complete', -- [pub]
- website varchar(250) NOT NULL DEFAULT '', -- [pub]
- catalog varchar(50) NOT NULL DEFAULT '', -- [pub]
- gtin bigint NOT NULL DEFAULT 0, -- [pub]
- released integer NOT NULL DEFAULT 0, -- [pub]
- notes text NOT NULL DEFAULT '', -- [pub]
- minage smallint, -- [pub]
- patch boolean NOT NULL DEFAULT FALSE, -- [pub]
- freeware boolean NOT NULL DEFAULT FALSE, -- [pub]
- doujin boolean NOT NULL DEFAULT FALSE, -- [pub]
- resolution resolution NOT NULL DEFAULT 'unknown', -- [pub]
- voiced smallint NOT NULL DEFAULT 0, -- [pub]
- ani_story smallint NOT NULL DEFAULT 0, -- [pub]
- ani_ero smallint NOT NULL DEFAULT 0, -- [pub]
- uncensored boolean NOT NULL DEFAULT FALSE, -- [pub]
- engine varchar(50) NOT NULL DEFAULT '', -- [pub]
- l_steam integer NOT NULL DEFAULT 0, -- [pub]
- l_dlsite text NOT NULL DEFAULT '', -- [pub]
- l_dlsiteen text NOT NULL DEFAULT '', -- [pub]
- l_gog text NOT NULL DEFAULT '', -- [pub]
- l_denpa text NOT NULL DEFAULT '', -- [pub]
- l_jlist text NOT NULL DEFAULT '', -- [pub]
- l_gyutto integer[] NOT NULL DEFAULT '{}', -- [pub]
- l_digiket integer NOT NULL DEFAULT 0, -- [pub]
- l_melon integer NOT NULL DEFAULT 0, -- [pub]
- l_mg integer NOT NULL DEFAULT 0, -- [pub]
- l_getchu integer NOT NULL DEFAULT 0, -- [pub]
- l_getchudl integer NOT NULL DEFAULT 0, -- [pub]
- l_dmm text[] NOT NULL DEFAULT '{}', -- [pub]
- l_itch text NOT NULL DEFAULT '', -- [pub]
- l_jastusa text NOT NULL DEFAULT '', -- [pub]
- l_egs integer NOT NULL DEFAULT 0, -- [pub]
- l_erotrail integer NOT NULL DEFAULT 0, -- [pub]
- l_toranoana bigint NOT NULL DEFAULT 0, -- [pub]
- l_melonjp integer NOT NULL DEFAULT 0, -- [pub]
- l_gamejolt integer NOT NULL DEFAULT 0, -- [pub]
- l_nutaku text NOT NULL DEFAULT '' -- [pub]
-);
-
--- releases_hist
-CREATE TABLE releases_hist (
- chid integer NOT NULL PRIMARY KEY,
- title varchar(300) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
- type release_type NOT NULL DEFAULT 'complete',
- website varchar(250) NOT NULL DEFAULT '',
- catalog varchar(50) NOT NULL DEFAULT '',
- gtin bigint NOT NULL DEFAULT 0,
- released integer NOT NULL DEFAULT 0,
- notes text NOT NULL DEFAULT '',
- minage smallint,
- patch boolean NOT NULL DEFAULT FALSE,
- freeware boolean NOT NULL DEFAULT FALSE,
- doujin boolean NOT NULL DEFAULT FALSE,
- resolution resolution NOT NULL DEFAULT 'unknown',
- voiced smallint NOT NULL DEFAULT 0,
- ani_story smallint NOT NULL DEFAULT 0,
- ani_ero smallint NOT NULL DEFAULT 0,
- uncensored boolean NOT NULL DEFAULT FALSE,
- engine varchar(50) NOT NULL DEFAULT '',
- l_steam integer NOT NULL DEFAULT 0,
- l_dlsite text NOT NULL DEFAULT '',
- l_dlsiteen text NOT NULL DEFAULT '',
- l_gog text NOT NULL DEFAULT '',
- l_denpa text NOT NULL DEFAULT '',
- l_jlist text NOT NULL DEFAULT '',
- l_gyutto integer[] NOT NULL DEFAULT '{}',
- l_digiket integer NOT NULL DEFAULT 0,
- l_melon integer NOT NULL DEFAULT 0,
- l_mg integer NOT NULL DEFAULT 0,
- l_getchu integer NOT NULL DEFAULT 0,
- l_getchudl integer NOT NULL DEFAULT 0,
- l_dmm text[] NOT NULL DEFAULT '{}',
- l_itch text NOT NULL DEFAULT '',
- l_jastusa text NOT NULL DEFAULT '',
- l_egs integer NOT NULL DEFAULT 0,
- l_erotrail integer NOT NULL DEFAULT 0,
- l_toranoana bigint NOT NULL DEFAULT 0,
- l_melonjp integer NOT NULL DEFAULT 0,
- l_gamejolt integer NOT NULL DEFAULT 0,
- l_nutaku text NOT NULL DEFAULT ''
-);
-
--- releases_lang
-CREATE TABLE releases_lang (
- id integer NOT NULL, -- [pub]
- lang language NOT NULL, -- [pub]
- PRIMARY KEY(id, lang)
-);
-
--- releases_lang_hist
-CREATE TABLE releases_lang_hist (
- chid integer NOT NULL,
- lang language NOT NULL,
- PRIMARY KEY(chid, lang)
-);
-
--- releases_media
-CREATE TABLE releases_media (
- id integer NOT NULL, -- [pub]
- medium medium NOT NULL, -- [pub]
- qty smallint NOT NULL DEFAULT 1, -- [pub]
- PRIMARY KEY(id, medium, qty)
-);
-
--- releases_media_hist
-CREATE TABLE releases_media_hist (
- chid integer NOT NULL,
- medium medium NOT NULL,
- qty smallint NOT NULL DEFAULT 1,
- PRIMARY KEY(chid, medium, qty)
-);
-
--- releases_platforms
-CREATE TABLE releases_platforms (
- id integer NOT NULL, -- [pub]
- platform platform NOT NULL, -- [pub]
- PRIMARY KEY(id, platform)
-);
-
--- releases_platforms_hist
-CREATE TABLE releases_platforms_hist (
- chid integer NOT NULL,
- platform platform NOT NULL,
- PRIMARY KEY(chid, platform)
-);
-
--- releases_producers
-CREATE TABLE releases_producers (
- id integer NOT NULL, -- [pub]
- pid integer NOT NULL, -- [pub] producers.id
- developer boolean NOT NULL DEFAULT FALSE, -- [pub]
- publisher boolean NOT NULL DEFAULT TRUE, -- [pub]
- CHECK(developer OR publisher),
- PRIMARY KEY(id, pid)
-);
-
--- releases_producers_hist
-CREATE TABLE releases_producers_hist (
- chid integer NOT NULL,
- pid integer NOT NULL, -- producers.id
- developer boolean NOT NULL DEFAULT FALSE,
- publisher boolean NOT NULL DEFAULT TRUE,
- CHECK(developer OR publisher),
- PRIMARY KEY(chid, pid)
-);
-
--- releases_vn
-CREATE TABLE releases_vn (
- id integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub] vn.id
- PRIMARY KEY(id, vid)
-);
-
--- releases_vn_hist
-CREATE TABLE releases_vn_hist (
- chid integer NOT NULL,
- vid integer NOT NULL, -- vn.id
- PRIMARY KEY(chid, vid)
-);
-
--- relgraphs
-CREATE TABLE relgraphs (
- id SERIAL PRIMARY KEY,
- svg xml NOT NULL
-);
-
--- rlists
-CREATE TABLE rlists (
- uid integer NOT NULL DEFAULT 0, -- [pub]
- rid integer NOT NULL DEFAULT 0, -- [pub]
- status smallint NOT NULL DEFAULT 0, -- [pub]
- added timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(uid, rid)
-);
-
--- sessions
-CREATE TABLE sessions (
- uid integer NOT NULL,
- token bytea NOT NULL,
- added timestamptz NOT NULL DEFAULT NOW(),
- expires timestamptz NOT NULL,
- type session_type NOT NULL,
- mail text,
- PRIMARY KEY (uid, token)
-);
-
--- shop_denpa
-CREATE TABLE shop_denpa (
- id text NOT NULL PRIMARY KEY,
- lastfetch timestamptz,
- deadsince timestamptz,
- sku text NOT NULL DEFAULT '',
- price text NOT NULL DEFAULT ''
-);
-
--- shop_dlsite
-CREATE TABLE shop_dlsite (
- id text NOT NULL PRIMARY KEY,
- lastfetch timestamptz,
- deadsince timestamptz,
- shop text NOT NULL DEFAULT '',
- price text NOT NULL DEFAULT ''
-);
-
--- shop_jlist
-CREATE TABLE shop_jlist (
- id text NOT NULL PRIMARY KEY,
- lastfetch timestamptz,
- deadsince timestamptz,
- jbox boolean NOT NULL DEFAULT false,
- price text NOT NULL DEFAULT '' -- empty when unknown or not in stock
-);
-
--- shop_mg
-CREATE TABLE shop_mg (
- id integer NOT NULL PRIMARY KEY,
- lastfetch timestamptz,
- deadsince timestamptz,
- r18 boolean NOT NULL DEFAULT true,
- price text NOT NULL DEFAULT ''
-);
-
--- shop_playasia
-CREATE TABLE shop_playasia (
- pax text NOT NULL PRIMARY KEY,
- gtin bigint NOT NULL,
- lastfetch timestamptz,
- url text NOT NULL DEFAULT '',
- price text NOT NULL DEFAULT ''
-);
-
--- shop_playasia_gtin
-CREATE TABLE shop_playasia_gtin (
- gtin bigint NOT NULL PRIMARY KEY,
- lastfetch timestamptz
-);
-
--- staff
-CREATE TABLE staff ( -- dbentry_type=s
- id SERIAL PRIMARY KEY, -- [pub]
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- aid integer NOT NULL DEFAULT 0, -- [pub] staff_alias.aid
- gender gender NOT NULL DEFAULT 'unknown', -- [pub]
- lang language NOT NULL DEFAULT 'ja', -- [pub]
- "desc" text NOT NULL DEFAULT '', -- [pub]
- l_wp varchar(150) NOT NULL DEFAULT '', -- [pub] (deprecated)
- l_site varchar(250) NOT NULL DEFAULT '', -- [pub]
- l_twitter varchar(16) NOT NULL DEFAULT '', -- [pub]
- l_anidb integer, -- [pub]
- l_wikidata integer, -- [pub]
- l_pixiv integer NOT NULL DEFAULT 0 -- [pub]
-);
-
--- staff_hist
-CREATE TABLE staff_hist (
- chid integer NOT NULL PRIMARY KEY,
- aid integer NOT NULL DEFAULT 0, -- Can't refer to staff_alias.id, because the alias might have been deleted
- gender gender NOT NULL DEFAULT 'unknown',
- lang language NOT NULL DEFAULT 'ja',
- "desc" text NOT NULL DEFAULT '',
- l_wp varchar(150) NOT NULL DEFAULT '',
- l_site varchar(250) NOT NULL DEFAULT '',
- l_twitter varchar(16) NOT NULL DEFAULT '',
- l_anidb integer,
- l_wikidata integer,
- l_pixiv integer NOT NULL DEFAULT 0
-);
-
--- staff_alias
-CREATE TABLE staff_alias (
- id integer NOT NULL, -- [pub]
- aid SERIAL PRIMARY KEY, -- [pub] Globally unique ID of this alias
- name varchar(200) NOT NULL DEFAULT '', -- [pub]
- original varchar(200) NOT NULL DEFAULT '' -- [pub]
-);
-
--- staff_alias_hist
-CREATE TABLE staff_alias_hist (
- chid integer NOT NULL,
- aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted
- name varchar(200) NOT NULL DEFAULT '',
- original varchar(200) NOT NULL DEFAULT '',
- PRIMARY KEY(chid, aid)
-);
-
--- 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, -- [pub]
- name varchar(250) NOT NULL UNIQUE, -- [pub]
- description text NOT NULL DEFAULT '', -- [pub]
- added timestamptz NOT NULL DEFAULT NOW(),
- state smallint NOT NULL DEFAULT 0, -- [pub]
- c_items integer NOT NULL DEFAULT 0,
- addedby integer NOT NULL DEFAULT 0,
- cat tag_category NOT NULL DEFAULT 'cont', -- [pub]
- defaultspoil smallint NOT NULL DEFAULT 0, -- [pub]
- searchable boolean NOT NULL DEFAULT TRUE, -- [pub]
- applicable boolean NOT NULL DEFAULT TRUE -- [pub]
-);
-
--- tags_aliases
-CREATE TABLE tags_aliases (
- alias varchar(250) NOT NULL PRIMARY KEY, -- [pub]
- tag integer NOT NULL -- [pub]
-);
-
--- tags_parents
-CREATE TABLE tags_parents (
- tag integer NOT NULL, -- [pub]
- parent integer NOT NULL, -- [pub]
- PRIMARY KEY(tag, parent)
-);
-
--- tags_vn
-CREATE TABLE tags_vn (
- tag integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub]
- uid integer, -- [pub]
- vote smallint NOT NULL DEFAULT 3 CHECK (vote >= -3 AND vote <= 3 AND vote <> 0), -- [pub]
- spoiler smallint CHECK(spoiler >= 0 AND spoiler <= 2), -- [pub]
- date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- ignore boolean NOT NULL DEFAULT false, -- [pub]
- notes text NOT NULL DEFAULT '' -- [pub]
-);
-
--- tags_vn_inherit
-CREATE TABLE tags_vn_inherit (
- tag integer NOT NULL,
- vid 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,
- poll_question varchar(100),
- poll_max_options smallint NOT NULL DEFAULT 1,
- poll_preview boolean NOT NULL DEFAULT FALSE, -- deprecated
- poll_recast boolean NOT NULL DEFAULT FALSE, -- deprecated
- private boolean NOT NULL DEFAULT FALSE
-);
-
--- threads_poll_options
-CREATE TABLE threads_poll_options (
- id SERIAL PRIMARY KEY,
- tid integer NOT NULL,
- option varchar(100) NOT NULL
-);
-
--- threads_poll_votes
-CREATE TABLE threads_poll_votes (
- tid integer NOT NULL,
- uid integer NOT NULL,
- optid integer NOT NULL,
- date timestamptz DEFAULT NOW(),
- PRIMARY KEY (tid, uid, optid)
-);
-
--- 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 board_type NOT NULL,
- iid integer NOT NULL DEFAULT 0,
- PRIMARY KEY(tid, type, iid)
-);
-
--- traits
-CREATE TABLE traits (
- id SERIAL PRIMARY KEY, -- [pub]
- name varchar(250) NOT NULL, -- [pub]
- alias varchar(500) NOT NULL DEFAULT '', -- [pub]
- description text NOT NULL DEFAULT '', -- [pub]
- added timestamptz NOT NULL DEFAULT NOW(),
- state smallint NOT NULL DEFAULT 0, -- [pub]
- addedby integer NOT NULL DEFAULT 0,
- "group" integer, -- [pub]
- "order" smallint NOT NULL DEFAULT 0, -- [pub]
- sexual boolean NOT NULL DEFAULT false, -- [pub]
- c_items integer NOT NULL DEFAULT 0,
- defaultspoil smallint NOT NULL DEFAULT 0, -- [pub]
- searchable boolean NOT NULL DEFAULT true, -- [pub]
- applicable boolean NOT NULL DEFAULT true -- [pub]
-);
-
--- traits_chars
--- This table is a cache for the data in chars_traits and includes child traits
--- into parent traits. In order to improve performance, there are no foreign
--- key constraints on this table.
-CREATE TABLE traits_chars (
- cid integer NOT NULL, -- chars (id)
- tid integer NOT NULL, -- traits (id)
- spoil smallint NOT NULL DEFAULT 0
-);
-
--- traits_parents
-CREATE TABLE traits_parents (
- trait integer NOT NULL, -- [pub]
- parent integer NOT NULL, -- [pub]
- PRIMARY KEY(trait, parent)
-);
-
--- ulist_labels
-CREATE TABLE ulist_labels (
- uid integer NOT NULL, -- [pub] user.id
- id integer NOT NULL, -- [pub] 0 < builtin < 10 <= custom, ids are reused
- label text NOT NULL, -- [pub]
- private boolean NOT NULL,
- PRIMARY KEY(uid, id)
-);
-
--- ulist_vns
-CREATE TABLE ulist_vns (
- uid integer NOT NULL, -- [pub] users.id
- vid integer NOT NULL, -- [pub] vn.id
- added timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- lastmod timestamptz NOT NULL DEFAULT NOW(), -- [pub] updated when anything in this row has changed?
- vote_date timestamptz, -- [pub] Used for "recent votes" - also updated when vote has changed?
- vote smallint CHECK(vote IS NULL OR vote BETWEEN 10 AND 100), -- [pub]
- started date, -- [pub]
- finished date, -- [pub]
- notes text NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY(uid, vid)
-);
-
--- ulist_vns_labels
-CREATE TABLE ulist_vns_labels (
- uid integer NOT NULL, -- [pub] user.id
- lbl integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub] vn.id
- PRIMARY KEY(uid, lbl, vid)
-);
-
--- users
-CREATE TABLE users (
- id SERIAL NOT NULL PRIMARY KEY, -- [pub]
- username varchar(20) NOT NULL UNIQUE, -- [pub]
- mail varchar(100) NOT NULL,
- perm smallint NOT NULL DEFAULT 1+4+16,
- -- A valid passwd column is 46 bytes:
- -- 4 bytes: N (big endian)
- -- 1 byte: r
- -- 1 byte: p
- -- 8 bytes: salt
- -- 32 bytes: scrypt(passwd, global_salt + salt, N, r, p, 32)
- -- Anything else is invalid, account disabled.
- passwd bytea NOT NULL DEFAULT '',
- registered timestamptz NOT NULL DEFAULT NOW(),
- c_votes integer NOT NULL DEFAULT 0,
- c_changes integer NOT NULL DEFAULT 0,
- ip inet NOT NULL DEFAULT '0.0.0.0',
- c_tags integer NOT NULL DEFAULT 0,
- ign_votes boolean NOT NULL DEFAULT FALSE, -- [pub]
- email_confirmed boolean NOT NULL DEFAULT FALSE,
- skin text NOT NULL DEFAULT '',
- customcss text NOT NULL DEFAULT '',
- filter_vn text NOT NULL DEFAULT '',
- filter_release text NOT NULL DEFAULT '',
- show_nsfw boolean NOT NULL DEFAULT FALSE,
- hide_list boolean NOT NULL DEFAULT FALSE, -- deprecated, replaced with ulist_labels.private
- notify_dbedit boolean NOT NULL DEFAULT TRUE,
- notify_announce boolean NOT NULL DEFAULT FALSE,
- vn_list_own boolean NOT NULL DEFAULT FALSE,
- vn_list_wish boolean NOT NULL DEFAULT FALSE, -- Not used anymore, wishlist column in the VN list view has been removed
- tags_all boolean NOT NULL DEFAULT FALSE,
- tags_cont boolean NOT NULL DEFAULT TRUE,
- tags_ero boolean NOT NULL DEFAULT FALSE,
- tags_tech boolean NOT NULL DEFAULT TRUE,
- spoilers smallint NOT NULL DEFAULT 0,
- traits_sexual boolean NOT NULL DEFAULT FALSE,
- nodistract_can boolean NOT NULL DEFAULT FALSE,
- nodistract_noads boolean NOT NULL DEFAULT FALSE,
- nodistract_nofancy boolean NOT NULL DEFAULT FALSE,
- support_can boolean NOT NULL DEFAULT FALSE,
- support_enabled boolean NOT NULL DEFAULT FALSE,
- uniname_can boolean NOT NULL DEFAULT FALSE,
- uniname text NOT NULL DEFAULT '',
- pubskin_can boolean NOT NULL DEFAULT FALSE,
- pubskin_enabled boolean NOT NULL DEFAULT FALSE,
- c_vns integer NOT NULL DEFAULT 0,
- c_wish integer NOT NULL DEFAULT 0,
- ulist_votes jsonb,
- ulist_vnlist jsonb,
- ulist_wish jsonb,
- c_imgvotes integer NOT NULL DEFAULT 0
-);
-
--- vn
-CREATE TABLE vn ( -- dbentry_type=v
- id SERIAL PRIMARY KEY, -- [pub]
- locked boolean NOT NULL DEFAULT FALSE,
- hidden boolean NOT NULL DEFAULT FALSE,
- title varchar(250) NOT NULL DEFAULT '', -- [pub]
- original varchar(250) NOT NULL DEFAULT '', -- [pub]
- alias varchar(500) NOT NULL DEFAULT '', -- [pub]
- length smallint NOT NULL DEFAULT 0, -- [pub]
- img_nsfw boolean NOT NULL DEFAULT FALSE, -- [pub]
- image image_id CONSTRAINT vn_image_check CHECK((image).itype = 'cv'), -- [pub]
- "desc" text NOT NULL DEFAULT '', -- [pub]
- l_wp varchar(150) NOT NULL DEFAULT '', -- [pub] (deprecated)
- l_encubed varchar(100) NOT NULL DEFAULT '', -- [pub] (deprecated)
- l_renai varchar(100) NOT NULL DEFAULT '', -- [pub]
- rgraph integer, -- relgraphs.id
- c_released integer NOT NULL DEFAULT 0,
- c_languages language[] NOT NULL DEFAULT '{}',
- c_olang language[] NOT NULL DEFAULT '{}',
- c_platforms platform[] NOT NULL DEFAULT '{}',
- c_popularity real, -- [pub]
- c_rating real, -- [pub]
- c_votecount integer NOT NULL DEFAULT 0, -- [pub]
- c_search text,
- l_wikidata integer -- [pub]
-);
-
--- vn_hist
-CREATE TABLE vn_hist (
- chid integer NOT NULL PRIMARY KEY,
- title varchar(250) NOT NULL DEFAULT '',
- original varchar(250) NOT NULL DEFAULT '',
- alias varchar(500) NOT NULL DEFAULT '',
- length smallint NOT NULL DEFAULT 0,
- img_nsfw boolean NOT NULL DEFAULT FALSE,
- image image_id CONSTRAINT vn_hist_image_check CHECK((image).itype = 'cv'),
- "desc" text NOT NULL DEFAULT '',
- l_wp varchar(150) NOT NULL DEFAULT '',
- l_encubed varchar(100) NOT NULL DEFAULT '',
- l_renai varchar(100) NOT NULL DEFAULT '',
- l_wikidata integer
-);
-
--- vn_anime
-CREATE TABLE vn_anime (
- id integer NOT NULL, -- [pub]
- aid integer NOT NULL, -- [pub] anime.id
- PRIMARY KEY(id, aid)
-);
-
--- vn_anime_hist
-CREATE TABLE vn_anime_hist (
- chid integer NOT NULL,
- aid integer NOT NULL, -- anime.id
- PRIMARY KEY(chid, aid)
-);
-
--- vn_relations
-CREATE TABLE vn_relations (
- id integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub] vn.id
- relation vn_relation NOT NULL, -- [pub]
- official boolean NOT NULL DEFAULT TRUE, -- [pub]
- PRIMARY KEY(id, vid)
-);
-
--- vn_relations_hist
-CREATE TABLE vn_relations_hist (
- chid integer NOT NULL,
- vid integer NOT NULL, -- vn.id
- relation vn_relation NOT NULL,
- official boolean NOT NULL DEFAULT TRUE,
- PRIMARY KEY(chid, vid)
-);
-
--- vn_screenshots
-CREATE TABLE vn_screenshots (
- id integer NOT NULL, -- [pub]
- scr image_id NOT NULL CONSTRAINT vn_screenshots_scr_check CHECK((scr).itype = 'sf'), -- [pub] images.id
- rid integer, -- [pub] releases.id (only NULL for old revisions, nowadays not allowed anymore)
- nsfw boolean NOT NULL DEFAULT FALSE, -- [pub]
- PRIMARY KEY(id, scr)
-);
-
--- vn_screenshots_hist
-CREATE TABLE vn_screenshots_hist (
- chid integer NOT NULL,
- scr image_id NOT NULL CONSTRAINT vn_screenshots_hist_scr_check CHECK((scr).itype = 'sf'),
- rid integer,
- nsfw boolean NOT NULL DEFAULT FALSE,
- PRIMARY KEY(chid, scr)
-);
-
--- vn_seiyuu
-CREATE TABLE vn_seiyuu (
- id integer NOT NULL, -- [pub]
- aid integer NOT NULL, -- [pub] staff_alias.aid
- cid integer NOT NULL, -- [pub] chars.id
- note varchar(250) NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY (id, aid, cid)
-);
-
--- vn_seiyuu_hist
-CREATE TABLE vn_seiyuu_hist (
- chid integer NOT NULL,
- aid integer NOT NULL, -- staff_alias.aid, but can't reference it because the alias may have been deleted
- cid integer NOT NULL, -- chars.id
- note varchar(250) NOT NULL DEFAULT '',
- PRIMARY KEY (chid, aid, cid)
-);
-
--- vn_staff
-CREATE TABLE vn_staff (
- id integer NOT NULL, -- [pub]
- aid integer NOT NULL, -- [pub] staff_alias.aid
- role credit_type NOT NULL DEFAULT 'staff', -- [pub]
- note varchar(250) NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY (id, aid, role)
-);
-
--- vn_staff_hist
-CREATE TABLE vn_staff_hist (
- chid integer NOT NULL,
- aid integer NOT NULL, -- See note at vn_seiyuu_hist.aid
- role credit_type NOT NULL DEFAULT 'staff',
- note varchar(250) NOT NULL DEFAULT '',
- PRIMARY KEY (chid, aid, role)
-);
-
--- wikidata
-CREATE TABLE wikidata (
- id integer NOT NULL PRIMARY KEY, -- [pub]
- lastfetch timestamptz,
- enwiki text, -- [pub]
- jawiki text, -- [pub]
- website text[], -- [pub] P856
- vndb text[], -- [pub] P3180
- mobygames text[], -- [pub] P1933
- mobygames_company text[], -- [pub] P4773
- gamefaqs_game integer[], -- [pub] P4769
- gamefaqs_company integer[], -- [pub] P6182
- anidb_anime integer[], -- [pub] P5646
- anidb_person integer[], -- [pub] P5649
- ann_anime integer[], -- [pub] P1985
- ann_manga integer[], -- [pub] P1984
- musicbrainz_artist uuid[], -- [pub] P434
- twitter text[], -- [pub] P2002
- vgmdb_product integer[], -- [pub] P5659
- vgmdb_artist integer[], -- [pub] P3435
- discogs_artist integer[], -- [pub] P1953
- acdb_char integer[], -- [pub] P7013
- acdb_source integer[], -- [pub] P7017
- indiedb_game text[], -- [pub] P6717
- howlongtobeat integer[], -- [pub] P2816
- crunchyroll text[], -- [pub] P4110
- igdb_game text[], -- [pub] P5794
- giantbomb text[], -- [pub] P5247
- pcgamingwiki text[], -- [pub] P6337
- steam integer[], -- [pub] P1733
- gog text[], -- [pub] P2725
- pixiv_user integer[], -- [pub] P5435
- doujinshi_author integer[] -- [pub] P7511
-);
diff --git a/util/sql/superuser_init.sql b/util/sql/superuser_init.sql
deleted file mode 100644
index 6e94167c..00000000
--- a/util/sql/superuser_init.sql
+++ /dev/null
@@ -1,15 +0,0 @@
--- This script should be run before all other scripts and as a PostgreSQL
--- superuser. It will create the VNDB database and required users.
--- All other SQL scripts should be run by the 'vndb' user.
-
--- In order to "activate" a user, i.e. to allow login, you need to manually run
--- the following for each user you want to activate:
--- ALTER ROLE rolename LOGIN PASSWORD 'password';
-
-CREATE ROLE vndb;
-CREATE DATABASE vndb OWNER vndb;
-
--- The website
-CREATE ROLE vndb_site;
--- Multi
-CREATE ROLE vndb_multi;
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
deleted file mode 100644
index 00efbd48..00000000
--- a/util/sql/tableattrs.sql
+++ /dev/null
@@ -1,141 +0,0 @@
--- Constraints
-
-ALTER TABLE changes ADD CONSTRAINT changes_requester_fkey FOREIGN KEY (requester) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE chars ADD CONSTRAINT chars_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
-ALTER TABLE chars ADD CONSTRAINT chars_image_fkey FOREIGN KEY (image) REFERENCES images (id);
-ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_main_fkey FOREIGN KEY (main) REFERENCES chars (id);
-ALTER TABLE chars_hist ADD CONSTRAINT chars_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
-ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
-ALTER TABLE chars_traits ADD CONSTRAINT chars_traits_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
-ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE chars_traits_hist ADD CONSTRAINT chars_traits_hist_tid_fkey FOREIGN KEY (tid) REFERENCES traits (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_id_fkey FOREIGN KEY (id) REFERENCES chars (id);
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
-ALTER TABLE chars_vns ADD CONSTRAINT chars_vns_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id) DEFERRABLE;
-ALTER TABLE chars_vns_hist ADD CONSTRAINT chars_vns_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
-ALTER TABLE image_votes ADD CONSTRAINT image_votes_id_fkey FOREIGN KEY (id) REFERENCES images (id);
-ALTER TABLE notifications ADD CONSTRAINT notifications_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE notifications ADD CONSTRAINT notifications_c_byuser_fkey FOREIGN KEY (c_byuser) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE producers ADD CONSTRAINT producers_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE producers ADD CONSTRAINT producers_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
-ALTER TABLE producers_hist ADD CONSTRAINT producers_chid_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE producers_hist ADD CONSTRAINT producers_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
-ALTER TABLE producers_relations ADD CONSTRAINT producers_relations_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_id_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE producers_relations_hist ADD CONSTRAINT producers_relations_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE quotes ADD CONSTRAINT quotes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE releases_hist ADD CONSTRAINT releases_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_lang ADD CONSTRAINT releases_lang_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_lang_hist ADD CONSTRAINT releases_lang_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_media ADD CONSTRAINT releases_media_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_media_hist ADD CONSTRAINT releases_media_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_platforms ADD CONSTRAINT releases_platforms_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_platforms_hist ADD CONSTRAINT releases_platforms_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_producers ADD CONSTRAINT releases_producers_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_producers_hist ADD CONSTRAINT releases_producers_hist_pid_fkey FOREIGN KEY (pid) REFERENCES producers (id);
-ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_id_fkey FOREIGN KEY (id) REFERENCES releases (id);
-ALTER TABLE releases_vn ADD CONSTRAINT releases_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE releases_vn_hist ADD CONSTRAINT releases_vn_hist_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE rlists ADD CONSTRAINT rlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE rlists ADD CONSTRAINT rlists_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id);
-ALTER TABLE sessions ADD CONSTRAINT sessions_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE staff ADD CONSTRAINT staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE staff ADD CONSTRAINT staff_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
-ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE staff_hist ADD CONSTRAINT staff_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
-ALTER TABLE staff_alias ADD CONSTRAINT staff_alias_id_fkey FOREIGN KEY (id) REFERENCES staff (id);
-ALTER TABLE staff_alias_hist ADD CONSTRAINT staff_alias_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE tags ADD CONSTRAINT tags_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE tags_aliases ADD CONSTRAINT tags_aliases_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_parents ADD CONSTRAINT tags_parents_parent_fkey FOREIGN KEY (parent) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_tag_fkey FOREIGN KEY (tag) REFERENCES tags (id);
-ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE tags_vn ADD CONSTRAINT tags_vn_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE threads ADD CONSTRAINT threads_id_fkey FOREIGN KEY (id, count) REFERENCES threads_posts (tid, num) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE threads_poll_options ADD CONSTRAINT threads_poll_options_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
-ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id) ON DELETE CASCADE;
-ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE threads_poll_votes ADD CONSTRAINT threads_poll_votes_optid_fkey FOREIGN KEY (optid) REFERENCES threads_poll_options (id) ON DELETE CASCADE;
-ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE threads_posts ADD CONSTRAINT threads_posts_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE threads_boards ADD CONSTRAINT threads_boards_tid_fkey FOREIGN KEY (tid) REFERENCES threads (id);
-ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey FOREIGN KEY (addedby) REFERENCES users (id) ON DELETE SET DEFAULT;
-ALTER TABLE traits ADD CONSTRAINT traits_group_fkey FOREIGN KEY ("group") REFERENCES traits (id);
-ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_trait_fkey FOREIGN KEY (trait) REFERENCES traits (id);
-ALTER TABLE traits_parents ADD CONSTRAINT traits_parents_parent_fkey FOREIGN KEY (parent) REFERENCES traits (id);
-ALTER TABLE ulist_labels ADD CONSTRAINT ulist_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns ADD CONSTRAINT ulist_vns_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_lbl_fkey FOREIGN KEY (uid,lbl) REFERENCES ulist_labels (uid,id) ON DELETE CASCADE;
-ALTER TABLE ulist_vns_labels ADD CONSTRAINT ulist_vns_labels_uid_vid_fkey FOREIGN KEY (uid,vid) REFERENCES ulist_vns (uid,vid) ON DELETE CASCADE;
-ALTER TABLE vn ADD CONSTRAINT vn_rgraph_fkey FOREIGN KEY (rgraph) REFERENCES relgraphs (id);
-ALTER TABLE vn ADD CONSTRAINT vn_image_fkey FOREIGN KEY (image) REFERENCES images (id);
-ALTER TABLE vn ADD CONSTRAINT vn_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
-ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_image_fkey FOREIGN KEY (image) REFERENCES images (id);
-ALTER TABLE vn_hist ADD CONSTRAINT vn_hist_l_wikidata_fkey FOREIGN KEY (l_wikidata)REFERENCES wikidata (id);
-ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_anime ADD CONSTRAINT vn_anime_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
-ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vn_anime_hist ADD CONSTRAINT vn_anime_hist_aid_fkey FOREIGN KEY (aid) REFERENCES anime (id);
-ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_relations ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vn_relations_hist ADD CONSTRAINT vn_relations_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
-ALTER TABLE vn_screenshots ADD CONSTRAINT vn_screenshots_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
-ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_scr_fkey FOREIGN KEY (scr) REFERENCES images (id);
-ALTER TABLE vn_screenshots_hist ADD CONSTRAINT vn_screenshots_hist_rid_fkey FOREIGN KEY (rid) REFERENCES releases (id) DEFERRABLE;
-ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn_seiyuu ADD CONSTRAINT vn_seiyuu_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey FOREIGN KEY (cid) REFERENCES chars (id);
-ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_id_fkey FOREIGN KEY (id) REFERENCES vn (id);
-ALTER TABLE vn_staff ADD CONSTRAINT vn_staff_aid_fkey FOREIGN KEY (aid) REFERENCES staff_alias (aid) DEFERRABLE INITIALLY DEFERRED;
-ALTER TABLE vn_staff_hist ADD CONSTRAINT vn_staff_hist_chid_fkey FOREIGN KEY (chid) REFERENCES changes (id) ON DELETE CASCADE;
-
-
-
--- Indices
-
-CREATE INDEX chars_main ON chars (main) WHERE main IS NOT NULL AND NOT hidden; -- Only used on /c+
-CREATE INDEX chars_vns_vid ON chars_vns (vid);
-CREATE INDEX chars_image ON chars (image);
-CREATE UNIQUE INDEX image_votes_pkey ON image_votes (uid, id);
-CREATE INDEX image_votes_id ON image_votes (id);
-CREATE INDEX notifications_uid ON notifications (uid);
-CREATE INDEX releases_producers_pid ON releases_producers (pid);
-CREATE INDEX releases_vn_vid ON releases_vn (vid);
-CREATE INDEX staff_alias_id ON staff_alias (id);
-CREATE UNIQUE INDEX tags_vn_pkey ON tags_vn (tag,vid,uid);
-CREATE INDEX tags_vn_date ON tags_vn (date);
-CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
-CREATE INDEX tags_vn_uid ON tags_vn (uid) WHERE uid IS NOT NULL;
-CREATE INDEX tags_vn_vid ON tags_vn (vid);
-CREATE INDEX shop_playasia__gtin ON shop_playasia (gtin);
-CREATE INDEX threads_posts_date ON threads_posts (date);
-CREATE INDEX threads_posts_ts ON threads_posts USING gin(bb_tsvector(msg));
-CREATE INDEX threads_posts_uid ON threads_posts (uid); -- Only really used on /u+ pages to get stats
-CREATE INDEX traits_chars_tid ON traits_chars (tid);
-CREATE INDEX vn_image ON vn (image);
-CREATE INDEX vn_screenshots_scr ON vn_screenshots (scr);
-CREATE INDEX vn_seiyuu_aid ON vn_seiyuu (aid); -- Only used on /s+?
-CREATE INDEX vn_seiyuu_cid ON vn_seiyuu (cid); -- Only used on /c+?
-CREATE INDEX vn_staff_aid ON vn_staff (aid);
-CREATE UNIQUE INDEX changes_itemrev ON changes (type, itemid, rev);
-CREATE UNIQUE INDEX chars_vns_pkey ON chars_vns (id, vid, COALESCE(rid, 0));
-CREATE UNIQUE INDEX chars_vns_hist_pkey ON chars_vns_hist (chid, vid, COALESCE(rid, 0));
-CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL; -- For VN recent votes & vote graph. INCLUDE(vote) speeds up vote graph even more
-CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
diff --git a/util/sql/triggers.sql b/util/sql/triggers.sql
deleted file mode 100644
index 5768c55e..00000000
--- a/util/sql/triggers.sql
+++ /dev/null
@@ -1,353 +0,0 @@
--- keep the c_tags, c_changes and c_imgvotes columns in the users table up to date
--- Assumption: The column referencing the user is never modified.
-
-CREATE OR REPLACE FUNCTION update_users_cache() RETURNS trigger AS $$
-BEGIN
- IF TG_TABLE_NAME = 'changes' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
- ELSE
- UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
- END IF;
- ELSIF TG_TABLE_NAME = 'tags_vn' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
- ELSE
- UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
- END IF;
- ELSIF TG_TABLE_NAME = 'image_votes' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_imgvotes = c_imgvotes + 1 WHERE id = NEW.uid;
- ELSE
- UPDATE users SET c_imgvotes = c_imgvotes - 1 WHERE id = OLD.uid;
- END IF;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_imgvotes_update AFTER INSERT OR DELETE ON image_votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-
-
-
-
--- the stats_cache table
-
-CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
-DECLARE
- unhidden boolean;
- hidden boolean;
-BEGIN
- IF TG_OP = 'INSERT' THEN
- IF TG_TABLE_NAME = 'users' THEN
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- ELSE
- IF TG_TABLE_NAME = 'threads_posts' THEN
- IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND threads.hidden = FALSE) THEN
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- END IF;
- ELSE
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- END IF;
- END IF;
-
- ELSIF TG_OP = 'UPDATE' THEN
- IF TG_TABLE_NAME IN('tags', 'traits') THEN
- unhidden := OLD.state <> 2 AND NEW.state = 2;
- hidden := OLD.state = 2 AND NEW.state <> 2;
- ELSE
- unhidden := OLD.hidden AND NOT NEW.hidden;
- hidden := NOT unhidden;
- END IF;
- IF unhidden THEN
- IF TG_TABLE_NAME = 'threads' THEN
- UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
- END IF;
- UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
- ELSIF hidden THEN
- IF TG_TABLE_NAME = 'threads' THEN
- UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
- END IF;
- UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
- END IF;
-
- ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN
- UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE 'plpgsql';
-
-CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON producers FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON releases FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON releases FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON chars FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON chars FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON staff FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON staff FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON tags FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON tags FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON traits FOR EACH ROW WHEN (NEW.state = 2) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON traits FOR EACH ROW WHEN (OLD.state IS DISTINCT FROM NEW.state) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON threads FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_new AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache_edit AFTER UPDATE ON threads_posts FOR EACH ROW WHEN (OLD.hidden IS DISTINCT FROM NEW.hidden) EXECUTE PROCEDURE update_stats_cache();
-CREATE TRIGGER stats_cache AFTER INSERT OR DELETE ON users FOR EACH ROW EXECUTE PROCEDURE update_stats_cache();
-
-
-
-
--- insert rows into anime for new vn_anime.aid items
-
-CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$
-BEGIN
- INSERT INTO anime (id) VALUES (NEW.aid) ON CONFLICT (id) DO NOTHING;
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER vn_anime_aid_new BEFORE INSERT ON vn_anime FOR EACH ROW EXECUTE PROCEDURE vn_anime_aid();
-CREATE TRIGGER vn_anime_aid_edit BEFORE UPDATE ON vn_anime FOR EACH ROW WHEN (OLD.aid IS DISTINCT FROM NEW.aid) EXECUTE PROCEDURE vn_anime_aid();
-
-
-
-
--- Send a notify whenever anime info should be fetched
-
-CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
- BEGIN NOTIFY anime; RETURN NULL; END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER anime_fetch_notify AFTER INSERT OR UPDATE ON anime FOR EACH ROW WHEN (NEW.lastfetch IS NULL) EXECUTE PROCEDURE anime_fetch_notify();
-
-
-
-
--- insert rows into wikidata for new l_wikidata items
-
-CREATE OR REPLACE FUNCTION wikidata_insert() RETURNS trigger AS $$
-BEGIN
- INSERT INTO wikidata (id) VALUES (NEW.l_wikidata) ON CONFLICT (id) DO NOTHING;
- RETURN NEW;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER producers_wikidata_new BEFORE INSERT ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER producers_wikidata_edit BEFORE UPDATE ON producers FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER producers_hist_wikidata_new BEFORE INSERT ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER producers_hist_wikidata_edit BEFORE UPDATE ON producers_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_wikidata_new BEFORE INSERT ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_wikidata_edit BEFORE UPDATE ON staff FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_hist_wikidata_new BEFORE INSERT ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER staff_hist_wikidata_edit BEFORE UPDATE ON staff_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_wikidata_new BEFORE INSERT ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_wikidata_edit BEFORE UPDATE ON vn FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_hist_wikidata_new BEFORE INSERT ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL) EXECUTE PROCEDURE wikidata_insert();
-CREATE TRIGGER vn_hist_wikidata_edit BEFORE UPDATE ON vn_hist FOR EACH ROW WHEN (NEW.l_wikidata IS NOT NULL AND OLD.l_wikidata IS DISTINCT FROM NEW.l_wikidata) EXECUTE PROCEDURE wikidata_insert();
-
-
-
-
--- For each row in rlists, there should be at least one corresponding row in
--- ulist_vns for each VN linked to that release.
--- 1. When a row is deleted from ulist_vns, also remove all rows from rlists
--- with that VN linked.
--- 2. When a row is inserted to rlists and there is not yet a corresponding row
--- in ulist_vns, add a row to ulist_vns for each vn linked to the release.
--- 3. When a release is edited to add another VN, add those VNs to ulist_vns
--- for everyone who has the release in rlists.
--- This is done in edit_committed().
--- #. When a release is edited to remove a VN, that VN kinda should also be
--- removed from ulist_vns, but only if that ulist_vns entry was
--- automatically added as part of the rlists entry and the user has not
--- changed anything in the ulist_vns row. This isn't currently done.
-CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$
-BEGIN
- -- 1.
- IF TG_TABLE_NAME = 'ulist_vns' THEN
- DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT id FROM releases_vn WHERE vid = OLD.vid);
- -- 2.
- ELSE
- INSERT INTO ulist_vns (uid, vid)
- SELECT NEW.uid, rv.vid FROM releases_vn rv WHERE rv.id = NEW.rid
- ON CONFLICT (uid, vid) DO NOTHING;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE CONSTRAINT TRIGGER update_ulist_vns_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
-CREATE CONSTRAINT TRIGGER update_rlist_ulist_vns AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
-
-
-
-
--- Create ulist_label rows when a new user is added
-
-CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS 'BEGIN PERFORM ulist_labels_create(NEW.id); RETURN NULL; END' LANGUAGE plpgsql;
-
-CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
-
-
-
-
--- Set/unset the 'Voted' label when voting.
-
-CREATE OR REPLACE FUNCTION ulist_voted_label() RETURNS trigger AS $$
-BEGIN
- IF NEW.vote IS NULL THEN
- DELETE FROM ulist_vns_labels WHERE uid = NEW.uid AND vid = NEW.vid AND lbl = 7;
- ELSE
- INSERT INTO ulist_vns_labels (uid, vid, lbl) VALUES (NEW.uid, NEW.vid, 7) ON CONFLICT (uid, vid, lbl) DO NOTHING;
- END IF;
- RETURN NULL;
-END
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
-
-
-
-
--- 1. Send a notify when vn.rgraph is set to NULL, and there are related entries in vn_relations
--- 2. Set rgraph to NULL when c_languages or c_released has changed
-
-CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
-BEGIN
- IF EXISTS(SELECT 1 FROM vn_relations WHERE id = NEW.id) THEN
- -- 1.
- IF NEW.rgraph IS NULL THEN
- NOTIFY relgraph;
- -- 2.
- ELSE
- UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
- END IF;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER vn_relgraph_notify AFTER UPDATE ON vn FOR EACH ROW
- WHEN ((OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL)
- OR (NEW.rgraph IS NOT NULL AND (OLD.c_released IS DISTINCT FROM NEW.c_released OR OLD.c_languages IS DISTINCT FROM NEW.c_languages))
- ) EXECUTE PROCEDURE vn_relgraph_notify();
-
-
-
-
--- Send a notify when producers.rgraph is set to NULL and there are related entries in producers_relations
-
-CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
-BEGIN
- IF EXISTS(SELECT 1 FROM producers_relations WHERE id = NEW.id) THEN
- NOTIFY relgraph;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER producer_relgraph_notify AFTER UPDATE ON producers FOR EACH ROW WHEN (OLD.rgraph IS NOT NULL AND NEW.rgraph IS NULL) EXECUTE PROCEDURE producer_relgraph_notify();
-
-
-
-
--- NOTIFY on insert into changes/posts/tags/trait
-
-CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
-BEGIN
- IF TG_TABLE_NAME = 'changes' THEN
- NOTIFY newrevision;
- ELSIF TG_TABLE_NAME = 'threads_posts' THEN
- NOTIFY newpost;
- ELSIF TG_TABLE_NAME = 'tags' THEN
- NOTIFY newtag;
- ELSIF TG_TABLE_NAME = 'traits' THEN
- NOTIFY newtrait;
- END IF;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-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();
-
-
-
-
--- Send a vnsearch notification when the c_search column is set to NULL.
-
-CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS 'BEGIN NOTIFY vnsearch; RETURN NULL; END;' LANGUAGE plpgsql;
-
-CREATE TRIGGER vn_vnsearch_notify AFTER UPDATE ON vn FOR EACH ROW WHEN (OLD.c_search IS NOT NULL AND NEW.c_search IS NULL) EXECUTE PROCEDURE vn_vnsearch_notify();
-
-
-
-
--- Add a notification when someone posts in someone's board.
-
-CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$
-BEGIN
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid
- FROM threads t
- JOIN threads_boards tb ON tb.tid = t.id
- WHERE t.id = NEW.tid
- AND tb.type = 'u'
- AND tb.iid <> NEW.uid -- don't notify when posting in your own board
- AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet
- SELECT 1
- FROM notifications n
- WHERE n.uid = tb.iid
- AND n.ntype = 'pm'
- AND n.iid = t.id
- AND n.read IS NULL
- );
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
-
-
-
-
--- Add a notification when a thread is created in /t/an
-
-CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
-BEGIN
- INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
- SELECT 'announce', 't', u.id, t.id, 1, t.title, NEW.uid
- FROM threads t
- JOIN threads_boards tb ON tb.tid = t.id
- -- get the users who want this announcement
- JOIN users u ON u.notify_announce
- WHERE t.id = NEW.tid
- AND tb.type = 'an' -- announcement board
- AND NOT t.hidden;
- RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
-
-
-
-
--- Call update_images_cache() for every change on image_votes
-
-CREATE OR REPLACE FUNCTION update_images_cache() RETURNS trigger AS $$
-BEGIN
- PERFORM update_images_cache(id) FROM (SELECT OLD.id UNION SELECT NEW.id) AS x(id) WHERE id IS NOT NULL;
- RETURN NULL;
-END
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER image_votes_cache AFTER INSERT OR UPDATE OR DELETE ON image_votes FOR EACH ROW EXECUTE PROCEDURE update_images_cache();