diff options
author | Yorhel <git@yorhel.nl> | 2019-12-30 15:20:00 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-12-30 15:20:00 +0100 |
commit | d1bb5b82255c764edecc659c78d5b9f4e36555e8 (patch) | |
tree | 53058ac9a25d0e82968da77e28f46d1137204e6b /util | |
parent | 13287329e70cbaf155c85e3054f2496411e21b21 (diff) | |
parent | ddb0d385eeb112de6e544adefbbac1cb0b8a957a (diff) |
Merge branch 'ulist'
Diffstat (limited to 'util')
-rwxr-xr-x | util/dbdump.pl | 53 | ||||
-rwxr-xr-x | util/devdump.pl | 14 | ||||
-rwxr-xr-x | util/docker-init.sh | 9 | ||||
-rw-r--r-- | util/sql/func.sql | 161 | ||||
-rw-r--r-- | util/sql/perms.sql | 27 | ||||
-rw-r--r-- | util/sql/schema.sql | 69 | ||||
-rw-r--r-- | util/sql/tableattrs.sql | 25 | ||||
-rw-r--r-- | util/updates/update_wip_lists.sql | 47 | ||||
-rwxr-xr-x | util/vndb-dev-server.pl | 7 | ||||
-rwxr-xr-x | util/vndb3.pl | 74 |
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; -} |