summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-30 15:20:00 +0100
committerYorhel <git@yorhel.nl>2019-12-30 15:20:00 +0100
commitd1bb5b82255c764edecc659c78d5b9f4e36555e8 (patch)
tree53058ac9a25d0e82968da77e28f46d1137204e6b /util
parent13287329e70cbaf155c85e3054f2496411e21b21 (diff)
parentddb0d385eeb112de6e544adefbbac1cb0b8a957a (diff)
Merge branch 'ulist'
Diffstat (limited to 'util')
-rwxr-xr-xutil/dbdump.pl53
-rwxr-xr-xutil/devdump.pl14
-rwxr-xr-xutil/docker-init.sh9
-rw-r--r--util/sql/func.sql161
-rw-r--r--util/sql/perms.sql27
-rw-r--r--util/sql/schema.sql69
-rw-r--r--util/sql/tableattrs.sql25
-rw-r--r--util/updates/update_wip_lists.sql47
-rwxr-xr-xutil/vndb-dev-server.pl7
-rwxr-xr-xutil/vndb3.pl74
10 files changed, 227 insertions, 259 deletions
diff --git a/util/dbdump.pl b/util/dbdump.pl
index dba08504..640ff6fc 100755
--- a/util/dbdump.pl
+++ b/util/dbdump.pl
@@ -28,6 +28,7 @@ use DBI;
use DBD::Pg;
use File::Copy 'cp';
use File::Find 'find';
+use Time::HiRes 'time';
use Cwd 'abs_path';
our $ROOT;
@@ -42,6 +43,10 @@ use VNDB::Schema;
# Tables are exported with an explicit ORDER BY to make them more deterministic
# and avoid potentially leaking information about internal state (such as when
# a user last updated their account).
+#
+# Hidden DB entries, private user lists and various other rows with no
+# interesting references are excluded from the dumps. Keeping all references
+# consistent with those omissions complicates the WHERE clauses somewhat.
my %tables = (
anime => { where => 'id IN(SELECT va.aid FROM vn_anime va JOIN vn v ON v.id = va.id WHERE NOT v.hidden)' },
chars => { where => 'NOT hidden' },
@@ -59,7 +64,12 @@ my %tables = (
releases_platforms => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden)' },
releases_producers => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND pid IN(SELECT id FROM producers WHERE NOT hidden)' },
releases_vn => { where => 'id IN(SELECT id FROM releases WHERE NOT hidden) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
- rlists => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list) AND rid IN(SELECT id FROM releases WHERE NOT hidden)' },
+ rlists => { where => 'EXISTS(SELECT 1 FROM releases r'
+ .' JOIN releases_vn rv ON rv.id = r.id'
+ .' JOIN vn v ON v.id = rv.vid'
+ .' JOIN ulist_vns_labels uvl ON uvl.vid = rv.vid'
+ .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl'
+ .' WHERE r.id = rlists.rid AND uvl.uid = rlists.uid AND NOT r.hidden AND NOT v.hidden AND NOT ul.private)' },
screenshots => { where => 'id IN(SELECT scr FROM vn_screenshots vs JOIN vn v ON v.id = vs.id WHERE NOT v.hidden)' },
staff => { where => 'NOT hidden' },
staff_alias => { where => 'id IN(SELECT id FROM staff WHERE NOT hidden)' },
@@ -69,17 +79,15 @@ my %tables = (
tags_vn => { where => 'tag IN(SELECT id FROM tags WHERE state = 2) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
traits => { where => 'state = 2' },
traits_parents => { where => 'trait IN(SELECT id FROM traits WHERE state = 2)' },
- # Only include users that are relevant for this dump.
- # (The 'DISTINCT' isn't necessary, but does make the query faster)
- # (Users with their votes ignored are still included. W/e)
- users => { where => q{
- ( id NOT IN(SELECT DISTINCT id FROM users WHERE hide_list)
- AND id IN(SELECT DISTINCT uid FROM rlists
- UNION SELECT DISTINCT uid FROM wlists
- UNION SELECT DISTINCT uid FROM vnlists
- UNION SELECT DISTINCT uid FROM votes)
- ) OR id IN(SELECT DISTINCT uid FROM tags_vn)
- } },
+ ulist_labels => { where => 'NOT private AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl WHERE uvl.lbl = id AND ulist_labels.uid = uvl.uid)' },
+ ulist_vns => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
+ .' AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl'
+ .' JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl'
+ .' WHERE ulist_vns.uid = uvl.uid AND ulist_vns.vid = uvl.vid AND NOT ul.private)' },
+ ulist_vns_labels => { where => 'vid IN(SELECT id FROM vn WHERE NOT hidden)'
+ .' AND EXISTS(SELECT 1 FROM ulist_labels ul WHERE ul.uid = ulist_vns_labels.uid AND id = lbl AND NOT ul.private)' },
+ users => { where => 'id IN(SELECT DISTINCT uvl.uid FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.uid = uvl.uid AND ul.id = uvl.lbl WHERE NOT ul.private)'
+ .' OR id IN(SELECT DISTINCT uid FROM tags_vn)' },
vn => { where => 'NOT hidden' },
vn_anime => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
vn_relations => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden)' },
@@ -88,13 +96,9 @@ my %tables = (
.' AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)'
.' AND cid IN(SELECT id FROM chars WHERE NOT hidden)' },
vn_staff => { where => 'id IN(SELECT id FROM vn WHERE NOT hidden) AND aid IN(SELECT sa.aid FROM staff_alias sa JOIN staff s ON s.id = sa.id WHERE NOT s.hidden)' },
- vnlists => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
- votes => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list OR ign_votes)'
- .' AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
wikidata => { where => q{id IN(SELECT l_wikidata FROM producers WHERE NOT hidden
UNION SELECT l_wikidata FROM staff WHERE NOT hidden
UNION SELECT l_wikidata FROM vn WHERE NOT hidden)} },
- wlists => { where => 'uid NOT IN(SELECT id FROM users WHERE hide_list) AND vid IN(SELECT id FROM vn WHERE NOT hidden)' },
);
my @tables = map +{ name => $_, %{$tables{$_}} }, sort keys %tables;
@@ -121,7 +125,6 @@ sub export_table {
my @cols = grep $_->{pub}, @{$schema->{cols}};
die "No columns to export for table '$table->{name}'\n" if !@cols;;
- #print "# Dumping $table->{name}\n";
my $fn = "$dest/$table->{name}";
# Truncate all timestamptz columns to a day, to avoid leaking privacy-sensitive info.
@@ -130,12 +133,15 @@ sub export_table {
my $order = $schema->{primary} ? join ', ', map "\"$_\"", @{$schema->{primary}} : $table->{order};
die "Table '$table->{name}' is missing an ORDER BY clause\n" if !$order;
+ my $start = time;
$db->do(qq{COPY (SELECT $cols FROM "$table->{name}" $where ORDER BY $order) TO STDOUT});
open my $F, '>:utf8', $fn;
my $v;
print $F $v while($db->pg_getcopydata($v) >= 0);
close $F;
+ #printf "# Dumped %s in %.3fs\n", $table->{name}, time-$start;
+
open $F, '>', "$fn.header";
print $F join "\t", map $_->{name}, @cols;
print $F "\n";
@@ -269,14 +275,15 @@ sub export_votes {
open my $F, '>:gzip:utf8', $dest;
$db->do(q{COPY (
- SELECT vv.vid||' '||vv.uid||' '||vv.vote||' '||to_char(vv.date, 'YYYY-MM-DD')
- FROM votes vv
- JOIN users u ON u.id = vv.uid
- JOIN vn v ON v.id = vv.vid
+ SELECT uv.vid||' '||uv.uid||' '||uv.vote||' '||to_char(uv.vote_date, 'YYYY-MM-DD')
+ FROM ulist_vns uv
+ JOIN users u ON u.id = uv.uid
+ JOIN vn v ON v.id = uv.vid
WHERE NOT v.hidden
AND NOT u.ign_votes
- AND NOT u.hide_list
- ORDER BY vv.vid, vv.uid
+ AND uv.vote IS NOT NULL
+ AND EXISTS(SELECT 1 FROM ulist_vns_labels uvl JOIN ulist_labels ul ON ul.id = uvl.lbl AND ul.uid = uvl.uid WHERE uv.uid = uvl.uid AND uv.vid = uvl.vid AND NOT ul.private)
+ ORDER BY uv.vid, uv.uid
) TO STDOUT
});
my $v;
diff --git a/util/devdump.pl b/util/devdump.pl
index 9b3480bf..e3d198c2 100755
--- a/util/devdump.pl
+++ b/util/devdump.pl
@@ -87,6 +87,8 @@ sub copy_entry {
print "\\set ON_ERROR_STOP 1\n";
print "\\i util/sql/schema.sql\n";
print "\\i util/sql/data.sql\n";
+ print "\\i util/sql/func.sql\n";
+ print "\\i util/sql/editfunc.sql\n";
# Copy over all sequence values
my @seq = sort @{ $db->selectcol_arrayref(
@@ -107,6 +109,7 @@ sub copy_entry {
[ 8, 'user6', 'user6@vndb.org', 21 ],
[ 9, 'user7', 'user7@vndb.org', 21 ],
);
+ print "SELECT ulist_labels_create(id) FROM users;\n";
# Tags & traits
copy tags => undef, {addedby => 'user'};
@@ -146,13 +149,13 @@ sub copy_entry {
# VN-related niceties
copy tags_vn => "SELECT DISTINCT ON (tag,vid,uid%10) * FROM tags_vn WHERE vid IN($vids)", {uid => 'user'};
copy quotes => "SELECT * FROM quotes WHERE vid IN($vids)";
- copy votes => "SELECT vid, uid%8+2 AS uid, (percentile_cont((uid%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(date) AS date FROM votes WHERE vid IN($vids) GROUP BY vid, uid%8", {uid => 'user'};
+ my $votes = "SELECT vid, uid%8+2 AS uid, (percentile_cont((uid%8+1)::float/9) WITHIN GROUP (ORDER BY vote))::smallint AS vote, MIN(date) AS vote_date FROM votes WHERE vid IN($vids) GROUP BY vid, uid%8";
+ copy ulist_vns => $votes, {uid => 'user'};
+ copy ulist_vns_labels => "SELECT vid, uid, 7 AS lbl FROM ($votes) x", {uid => 'user'};
# Releases
copy_entry r => [qw/releases releases_lang releases_media releases_platforms releases_producers releases_vn/], $releases;
- print "\\i util/sql/func.sql\n";
- print "\\i util/sql/editfunc.sql\n";
print "\\i util/sql/tableattrs.sql\n";
# Update some caches
@@ -160,11 +163,10 @@ sub copy_entry {
print "SELECT traits_chars_calc(NULL);\n";
print "SELECT update_vncache(id) FROM vn;\n";
print "SELECT update_stats_cache_full();\n";
- print "SELECT update_vnpopularity();\n";
- print "UPDATE users u SET c_votes = (SELECT COUNT(*) FROM votes v WHERE v.uid = u.id);\n";
+ print "SELECT update_vnvotestats();\n";
+ print "SELECT update_users_ulist_stats(NULL);\n";
print "UPDATE users u SET c_tags = (SELECT COUNT(*) FROM tags_vn v WHERE v.uid = u.id);\n";
print "UPDATE users u SET c_changes = (SELECT COUNT(*) FROM changes c WHERE c.requester = u.id);\n";
- # TODO: The vn.c_rating and vn.c_votecount stats are still inconsistent
print "\\set ON_ERROR_STOP 0\n";
print "\\i util/sql/perms.sql\n";
diff --git a/util/docker-init.sh b/util/docker-init.sh
index c78c5e76..d6994583 100755
--- a/util/docker-init.sh
+++ b/util/docker-init.sh
@@ -89,7 +89,7 @@ pg_start() {
# Should run as devuser
devshell() {
cd /var/www
- util/vndb-dev-server.pl $1
+ util/vndb-dev-server.pl
bash
}
@@ -100,15 +100,10 @@ case "$1" in
su devuser -c '/var/www/util/docker-init.sh pg_start'
exec su devuser -c '/var/www/util/docker-init.sh devshell'
;;
- 3)
- mkdevuser
- su devuser -c '/var/www/util/docker-init.sh pg_start'
- exec su devuser -c '/var/www/util/docker-init.sh devshell 3'
- ;;
pg_start)
pg_start
;;
devshell)
- devshell $2
+ devshell
;;
esac
diff --git a/util/sql/func.sql b/util/sql/func.sql
index dab2ddbb..b9fa5ade 100644
--- a/util/sql/func.sql
+++ b/util/sql/func.sql
@@ -102,24 +102,59 @@ CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
$$ LANGUAGE sql;
-
--- recalculate vn.c_popularity
-CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
- -- the following querie only update VNs which have valid votes, so make sure to reset all rows first.
- UPDATE vn SET c_popularity = NULL;
- WITH t2(vid, win) AS (
+-- 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 v.uid, v.vid, ((rank() OVER (PARTITION BY uid ORDER BY vote))::real - 1) ^ 0.36788
- FROM votes v
- JOIN users u ON u.id = v.uid AND NOT ign_votes
- ) t1(uid, vid, rank)
- GROUP BY vid
+ 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_popularity = s1.win/(SELECT MAX(win) FROM t2) FROM t2 s1 WHERE s1.vid = vn.id AND s1.win > 0;
+ UPDATE vn SET c_rating = rating, c_votecount = count, c_popularity = popularity FROM stats WHERE id = vid;
$$ LANGUAGE SQL;
+
+-- 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(*) FILTER (WHERE ul.id = 7) -- Voted
+ , COUNT(DISTINCT uvl.vid) FILTER (WHERE ul.id NOT IN(5,6)) -- Labelled, but not wishlish/blacklist
+ , COUNT(DISTINCT uvl.vid) FILTER (WHERE 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 AND NOT ul.private
+ 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
@@ -408,6 +443,16 @@ BEGIN
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;
END;
$$ LANGUAGE plpgsql;
@@ -418,16 +463,10 @@ $$ LANGUAGE plpgsql;
----------------------------------------------------------
--- keep the c_* columns in the users table up to date
+-- keep the c_tags and c_changes columns in the users table up to date
CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
BEGIN
- IF TG_TABLE_NAME = 'votes' THEN
- IF TG_OP = 'INSERT' THEN
- UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
- ELSE
- UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
- END IF;
- ELSIF TG_TABLE_NAME = 'changes' THEN
+ IF TG_TABLE_NAME = 'changes' THEN
IF TG_OP = 'INSERT' THEN
UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
ELSE
@@ -519,48 +558,64 @@ $$ LANGUAGE plpgsql;
-- For each row in rlists, there should be at least one corresponding row in
--- vnlists for at least one of the VNs linked to that release.
--- 1. When a row is deleted from vnlists, also remove all rows from rlists that
--- would otherwise not have a corresponding row in vnlists
+-- 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 vnlists, add a row in vnlists (with status=unknown) for each vn linked
--- to the release.
+-- 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 = 'vnlists' THEN
- DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT rv.id
- -- fetch all related rows in rlists
- FROM releases_vn rv
- JOIN rlists rl ON rl.rid = rv.id
- WHERE rv.vid = OLD.vid AND rl.uid = OLD.uid
- -- and test for a corresponding row in vnlists
- AND NOT EXISTS(
- SELECT 1
- FROM releases_vn rvi
- JOIN vnlists vl ON vl.vid = rvi.vid AND uid = OLD.uid
- WHERE rvi.id = rv.id
- ));
-
+ 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 vnlists (uid, vid) SELECT NEW.uid, rv.vid
- -- all VNs linked to the release
- FROM releases_vn rv
- WHERE rv.id = NEW.rid
- -- but only if there are no corresponding rows in vnlists yet
- AND NOT EXISTS(
- SELECT 1
- FROM releases_vn rvi
- JOIN vnlists vl ON vl.vid = rvi.vid
- WHERE rvi.id = NEW.rid AND vl.uid = NEW.uid
- );
+ 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 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;
+
+CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS 'BEGIN PERFORM ulist_labels_create(NEW.id); RETURN NULL; END' LANGUAGE plpgsql;
+
+
+
+-- 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;
+
+
-- Send a notify whenever anime info should be fetched
CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
@@ -707,10 +762,8 @@ CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype
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 votes WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM vnlists WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM wlists WHERE xtype = 'v' AND vid = xedit.itemid
- UNION SELECT uid FROM rlists WHERE xtype = 'r' AND rid = xedit.itemid
+ 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
diff --git a/util/sql/perms.sql b/util/sql/perms.sql
index a038103e..e649526f 100644
--- a/util/sql/perms.sql
+++ b/util/sql/perms.sql
@@ -61,15 +61,15 @@ 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 ulists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_labels TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulists_vn_labels 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),
- 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),
- 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) ON users TO vndb_site;
+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),
+ 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),
+ 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) ON users TO vndb_site;
GRANT SELECT, INSERT, UPDATE ON vn TO vndb_site;
GRANT SELECT, INSERT, DELETE ON vn_anime TO vndb_site;
@@ -83,9 +83,6 @@ 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, UPDATE, DELETE ON vnlists TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_site;
GRANT SELECT, INSERT ON wikidata TO vndb_site;
@@ -147,9 +144,14 @@ 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 (id, username, registered, c_votes, c_changes, c_tags, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
- UPDATE ( c_votes, c_changes, c_tags) ON users 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, ign_votes, email_confirmed, hide_list, notify_dbedit, notify_announce),
+ UPDATE ( c_votes, c_changes, c_tags, c_vns, c_wish) 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;
@@ -159,7 +161,4 @@ 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, DELETE ON vnlists TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON votes TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON wlists TO vndb_multi;
GRANT SELECT, INSERT, UPDATE ON wikidata TO vndb_multi;
diff --git a/util/sql/schema.sql b/util/sql/schema.sql
index aa380aa4..3bc27c77 100644
--- a/util/sql/schema.sql
+++ b/util/sql/schema.sql
@@ -704,6 +704,37 @@ CREATE TABLE traits_parents (
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]
@@ -723,18 +754,18 @@ CREATE TABLE users (
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,
+ 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,
+ 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,
+ 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,
@@ -749,7 +780,9 @@ CREATE TABLE users (
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
+ pubskin_enabled boolean NOT NULL DEFAULT FALSE,
+ c_vns integer NOT NULL DEFAULT 0,
+ c_wish integer NOT NULL DEFAULT 0
);
-- vn
@@ -881,25 +914,6 @@ CREATE TABLE vn_staff_hist (
PRIMARY KEY (chid, aid, role)
);
--- vnlists
-CREATE TABLE vnlists (
- uid integer NOT NULL, -- [pub]
- vid integer NOT NULL, -- [pub]
- status smallint NOT NULL DEFAULT 0, -- [pub]
- added TIMESTAMPTZ NOT NULL DEFAULT NOW(), -- [pub]
- notes varchar NOT NULL DEFAULT '', -- [pub]
- PRIMARY KEY(uid, vid)
-);
-
--- votes
-CREATE TABLE votes (
- vid integer NOT NULL DEFAULT 0, -- [pub]
- uid integer NOT NULL DEFAULT 0, -- [pub]
- vote integer NOT NULL DEFAULT 0, -- [pub]
- date timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(vid, uid)
-);
-
-- wikidata
CREATE TABLE wikidata (
id integer NOT NULL PRIMARY KEY, -- [pub]
@@ -934,12 +948,3 @@ CREATE TABLE wikidata (
pixiv_user integer[], -- [pub] P5435
doujinshi_author integer[] -- [pub] P7511
);
-
--- wlists
-CREATE TABLE wlists (
- uid integer NOT NULL DEFAULT 0, -- [pub]
- vid integer NOT NULL DEFAULT 0, -- [pub]
- wstat smallint NOT NULL DEFAULT 0, -- [pub]
- added timestamptz NOT NULL DEFAULT NOW(), -- [pub]
- PRIMARY KEY(uid, vid)
-);
diff --git a/util/sql/tableattrs.sql b/util/sql/tableattrs.sql
index ed25bc98..c9b598b1 100644
--- a/util/sql/tableattrs.sql
+++ b/util/sql/tableattrs.sql
@@ -67,6 +67,13 @@ ALTER TABLE traits ADD CONSTRAINT traits_addedby_fkey
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_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;
@@ -93,12 +100,6 @@ ALTER TABLE vn_seiyuu_hist ADD CONSTRAINT vn_seiyuu_hist_cid_fkey
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;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE vnlists ADD CONSTRAINT vnlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE votes ADD CONSTRAINT votes_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE votes ADD CONSTRAINT votes_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
-ALTER TABLE wlists ADD CONSTRAINT wlists_uid_fkey FOREIGN KEY (uid) REFERENCES users (id) ON DELETE CASCADE;
-ALTER TABLE wlists ADD CONSTRAINT wlists_vid_fkey FOREIGN KEY (vid) REFERENCES vn (id);
@@ -122,18 +123,17 @@ CREATE INDEX traits_chars_tid ON traits_chars (tid);
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 INDEX votes_date ON votes (date desc); -- Mainly used on /v+ pages, other pages don't really need it
-CREATE INDEX votes_uid ON votes (uid);
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;
-- Triggers
CREATE TRIGGER users_changes_update AFTER INSERT OR DELETE ON changes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
-CREATE TRIGGER users_votes_update AFTER INSERT OR DELETE ON votes FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER users_tags_update AFTER INSERT OR DELETE ON tags_vn FOR EACH ROW EXECUTE PROCEDURE update_users_cache();
CREATE TRIGGER stats_cache_new AFTER INSERT ON vn FOR EACH ROW WHEN (NEW.hidden = FALSE) EXECUTE PROCEDURE update_stats_cache();
@@ -189,7 +189,10 @@ CREATE TRIGGER insert_notify AFTER INSERT ON traits
CREATE TRIGGER notify_pm AFTER INSERT ON threads_posts FOR EACH ROW EXECUTE PROCEDURE notify_pm();
CREATE TRIGGER notify_announce AFTER INSERT ON threads_posts FOR EACH ROW WHEN (NEW.num = 1) EXECUTE PROCEDURE notify_announce();
+CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
+CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+
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();
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON vnlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
-CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER INSERT ON rlists DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
diff --git a/util/updates/update_wip_lists.sql b/util/updates/update_wip_lists.sql
index b453a406..0ff7a452 100644
--- a/util/updates/update_wip_lists.sql
+++ b/util/updates/update_wip_lists.sql
@@ -113,44 +113,25 @@ COMMIT;
\timing
+DROP FUNCTION update_vnpopularity();
-CREATE OR REPLACE FUNCTION ulist_labels_create() RETURNS trigger AS $$
-BEGIN
- INSERT INTO ulist_labels (uid, id, label, private)
- VALUES (NEW.id, 1, 'Playing', false),
- (NEW.id, 2, 'Finished', false),
- (NEW.id, 3, 'Stalled', false),
- (NEW.id, 4, 'Dropped', false),
- (NEW.id, 5, 'Wishlist', false),
- (NEW.id, 6, 'Blacklist', false),
- (NEW.id, 7, 'Voted', false);
- RETURN NULL;
-END
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
-
+ALTER TABLE users ADD COLUMN c_vns integer NOT NULL DEFAULT 0;
+ALTER TABLE users ADD COLUMN c_wish integer NOT NULL DEFAULT 0;
+\i util/sql/func.sql
+\i util/sql/perms.sql
-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;
+DROP TRIGGER users_votes_update ON votes;
+CREATE TRIGGER ulist_labels_create AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE ulist_labels_create();
CREATE TRIGGER ulist_voted_label AFTER INSERT OR UPDATE ON ulist_vns FOR EACH ROW EXECUTE PROCEDURE ulist_voted_label();
+CREATE CONSTRAINT TRIGGER update_vnlist_rlist AFTER DELETE ON ulist_vns DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE update_vnlist_rlist();
+\timing
+SELECT update_users_ulist_stats(NULL);
+CREATE INDEX ulist_vns_voted ON ulist_vns (vid, vote_date) WHERE vote IS NOT NULL;
+CREATE INDEX users_ign_votes ON users (id) WHERE ign_votes;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_site;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_site;
-
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_labels TO vndb_multi;
-GRANT SELECT, INSERT, UPDATE, DELETE ON ulist_vns_labels TO vndb_multi;
+-- Can be done later:
+-- DROP TABLE wlists, vnlists, votes;
diff --git a/util/vndb-dev-server.pl b/util/vndb-dev-server.pl
index d1d051a8..a7571e10 100755
--- a/util/vndb-dev-server.pl
+++ b/util/vndb-dev-server.pl
@@ -15,8 +15,6 @@ use Cwd 'abs_path';
my $listen_port = $ENV{TUWF_HTTP_SERVER_PORT} || 3000;
$ENV{TUWF_HTTP_SERVER_PORT} = $listen_port+1;
-my $script = $ARGV[0] && $ARGV[0] eq '3' ? 'vndb3.pl' : 'vndb.pl';
-
my($pid, $prog, $killed);
sub prog_start {
@@ -30,7 +28,7 @@ sub prog_start {
}
print $d;
};
- $prog = run_cmd "$ROOT/util/$script",
+ $prog = run_cmd "$ROOT/util/vndb.pl",
'$$' => \$pid,
'>' => $output,
'2>' => $output;
@@ -104,7 +102,6 @@ sub checkmod {
chdir $ROOT;
$check->($_) for (qw{
util/vndb.pl
- util/vndb3.pl
data/conf.pl
});
@@ -152,6 +149,6 @@ while(1) {
my $prog_conn = AE::cv;
tcp_connect '127.0.0.1', $ENV{TUWF_HTTP_SERVER_PORT}, sub { $prog_conn->send(shift); };
- my $prog_fh = $prog_conn->recv || die "Unable to connect to $script? $!";
+ my $prog_fh = $prog_conn->recv || die "Unable to connect to vndb.pl? $!";
pipe_fhs($serv_fh, $prog_fh);
}
diff --git a/util/vndb3.pl b/util/vndb3.pl
deleted file mode 100755
index 693639b0..00000000
--- a/util/vndb3.pl
+++ /dev/null
@@ -1,74 +0,0 @@
-#!/usr/bin/perl
-
-use strict;
-use warnings;
-use TUWF;
-
-use Cwd 'abs_path';
-my $ROOT;
-BEGIN { ($ROOT = abs_path $0) =~ s{/util/vndb3\.pl$}{}; }
-use lib $ROOT.'/lib';
-
-use PWLookup;
-use VNDB::Config;
-
-$|=1; # Disable buffering on STDOUT, otherwise vndb-dev-server.pl won't pick up our readyness notification.
-
-# Make the configuration available as tuwf->conf
-sub TUWF::Object::conf { config }
-
-
-# Make our root path available as tuwf->root
-# Optionally accepts other path components to assemble a file path:
-# tuwf->root('static/sf/01/1.jpg')
-sub TUWF::Object::root { shift; join '/', $ROOT, @_ }
-
-
-# tuwf->imgpath(cg => $image_id)
-sub TUWF::Object::imgpath {
- tuwf->root(static => $_[1] => sprintf '%02d/%d.jpg', $_[2]%100, $_[2]);
-}
-
-
-# tuwf->imgurl(cv => $image_id)
-sub TUWF::Object::imgurl {
- sprintf '%s/%s/%02d/%d.jpg', $_[0]->conf->{url_static}, $_[1], $_[2]%100, $_[2];
-}
-
-
-# tuwf->resDenied
-sub TUWF::Object::resDenied {
- TUWF::_very_simple_page(403, '403 - Permission Denied', 'You do not have the permission to access this page.');
-}
-
-# tuwf->isUnsafePass($pass)
-sub TUWF::Object::isUnsafePass {
- $_[0]->conf->{password_db} && PWLookup::lookup($_[0]->conf->{password_db}, $_[1])
-}
-
-
-TUWF::set %{ config->{tuwf} || {} };
-
-TUWF::set import_modules => 0;
-
-# If we're running standalone, serve www/ and static/ too.
-TUWF::hook before => sub {
- my $static = tuwf->{_TUWF}{http} &&
- ( tuwf->resFile(tuwf->root('www'), tuwf->reqPath)
- || tuwf->resFile(tuwf->root('static'), tuwf->reqPath)
- );
- if($static) {
- tuwf->resHeader('Cache-Control' => 'max-age=31536000');
- tuwf->done;
- }
-};
-
-
-require VN3::Validation; # Load this early, to ensure the custom_validations are available
-TUWF::load_recursive 'VN3';
-
-if($ARGV[0] && $ARGV[0] eq 'elmgen') {
- VN3::ElmGen::print();
-} else {
- TUWF::run;
-}