diff options
Diffstat (limited to 'util')
-rw-r--r-- | util/dump.sql | 15 | ||||
-rwxr-xr-x | util/init.pl | 70 | ||||
-rw-r--r-- | util/updates/update_2.9.sql | 76 |
3 files changed, 85 insertions, 76 deletions
diff --git a/util/dump.sql b/util/dump.sql index 0fdf5696..e0af79e8 100644 --- a/util/dump.sql +++ b/util/dump.sql @@ -69,7 +69,8 @@ CREATE TABLE producers_rev ( website varchar(250) NOT NULL DEFAULT '', lang varchar NOT NULL DEFAULT 'ja', "desc" text NOT NULL DEFAULT '', - alias varchar(500) NOT NULL DEFAULT '' + alias varchar(500) NOT NULL DEFAULT '', + l_wp varchar(150) ); -- quotes @@ -129,7 +130,7 @@ CREATE TABLE releases_rev ( website varchar(250) NOT NULL DEFAULT '', released integer NOT NULL, notes text NOT NULL DEFAULT '', - minage smallint NOT NULL DEFAULT -1, + minage smallint, gtin bigint NOT NULL DEFAULT 0, patch boolean NOT NULL DEFAULT FALSE, catalog varchar(50) NOT NULL DEFAULT '', @@ -290,7 +291,9 @@ CREATE TABLE vn ( c_released integer NOT NULL DEFAULT 0, c_languages varchar(32) NOT NULL DEFAULT '', c_platforms varchar(32) NOT NULL DEFAULT '', - c_popularity real NOT NULL DEFAULT 0 + c_popularity real, + c_rating real, + c_votecount integer NOT NULL DEFAULT 0 ); -- vn_anime @@ -478,11 +481,11 @@ BEGIN SELECT v.uid, v.vid, sqrt(count(*))::real FROM votes v JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote - WHERE v.uid NOT IN(SELECT id FROM users WHERE ign_votes) + JOIN users u ON u.id = v.uid AND NOT ign_votes GROUP BY v.vid, v.uid; CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; - UPDATE vn SET c_popularity = COALESCE((SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id), 0); + UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id); RETURN; END; $$ LANGUAGE plpgsql; @@ -575,7 +578,7 @@ BEGIN SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs(); -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS - SELECT tag, vid, uid, MAX(vote)::real AS vote, AVG(spoiler)::real AS spoiler + SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler FROM tags_vn_all GROUP BY tag, vid, uid; -- grouped by (tag, vid) and serialized into a table DROP INDEX IF EXISTS tags_vn_bayesian_tag; diff --git a/util/init.pl b/util/init.pl deleted file mode 100755 index ac533174..00000000 --- a/util/init.pl +++ /dev/null @@ -1,70 +0,0 @@ -#!/usr/bin/perl - - -# This script should be run after you've somehow managed to fetch -# all the versioned files from the git repo. - - -print "Initializing the files and directories needed to run VNDB...\n"; - - -# determine our root directory -use Cwd 'abs_path'; -our $ROOT; -BEGIN { - ($ROOT = abs_path $0) =~ s{/util/init\.pl$}{}; -} - - -print " Using project root: $ROOT\n"; -print "\n"; - - - -print "Creating directory structures...\n"; -for my $d (qw| cv st sf |) { - print " /static/$d\n"; - mkdir "$ROOT/static/$d" or die "mkdir '$ROOT/static/$d': $!\n"; - for my $i (0..99) { - my $n = sprintf '%s/static/%s/%02d', $ROOT, $d, $i; - mkdir $n or die "mkdir '$n': $!\n"; - chmod 0777, $n or die "chmod 777 '$n': $!\n"; - } -} -print "\n"; - - -print "Creating /www\n"; -print " You can use this directory to store all files you want to\n"; -print " be available from the main domain. A favicon.ico for example.\n"; -mkdir "$ROOT/www" or die $!; -print "\n"; - - -print "Writing robots.txt in /static and /www\n"; -print " You probably don't want your personal copy of VNDB to end up\n"; -print " in the google results, so I'll install a default robots.txt\n"; -print " for you. You're free to modify them as you wish.\n"; -for ('static/robots.txt', 'www/robots.txt') { - print " $_ exists, skipping...\n", next if -f "$ROOT/$_"; - open my $F, '>', "$ROOT/$_" or die "$_: $!\n"; - print $F "User-agent: *\nDisallow: /\n"; - close $F; -} -print "\n"; - - -if(!-f "$ROOT/data/config.pl") { - # TODO: create a template config file - print "No custom config file found, please write one!\n"; -} -print "\n"; - - -print "Everything is initialized! Now make sure to configure your\n"; -print "webserver and to initialize a postgresql database (using\n"; -print "dump.sql)\n"; - - - - diff --git a/util/updates/update_2.9.sql b/util/updates/update_2.9.sql new file mode 100644 index 00000000..3b3e147d --- /dev/null +++ b/util/updates/update_2.9.sql @@ -0,0 +1,76 @@ + +-- another fix in the calculation of the tags_vn_bayesian.spoiler column + +CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$ +BEGIN + -- all votes for all tags + CREATE OR REPLACE TEMPORARY VIEW tags_vn_all AS + SELECT * FROM tags_vn UNION SELECT * FROM tag_vn_childs(); + -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry + CREATE OR REPLACE TEMPORARY VIEW tags_vn_grouped AS + SELECT tag, vid, uid, MAX(vote)::real AS vote, COALESCE(AVG(spoiler), 0)::real AS spoiler + FROM tags_vn_all GROUP BY tag, vid, uid; + -- grouped by (tag, vid) and serialized into a table + DROP INDEX IF EXISTS tags_vn_bayesian_tag; + TRUNCATE tags_vn_bayesian; + INSERT INTO tags_vn_bayesian + SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating, + (CASE WHEN AVG(spoiler) < 0.7 THEN 0 WHEN AVG(spoiler) > 1.3 THEN 2 ELSE 1 END)::smallint AS spoiler + FROM tags_vn_grouped + GROUP BY tag, vid + HAVING AVG(vote) > 0; + CREATE INDEX tags_vn_bayesian_tag ON tags_vn_bayesian (tag); + -- now perform the bayesian ranking calculation + UPDATE tags_vn_bayesian tvs SET rating = + ((SELECT AVG(users)::real * AVG(rating)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users*rating) + / ((SELECT AVG(users)::real FROM tags_vn_bayesian WHERE tag = tvs.tag) + users)::real; + -- and update the VN count in the tags table as well + UPDATE tags SET c_vns = (SELECT COUNT(*) FROM tags_vn_bayesian WHERE tag = id); + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT tag_vn_calc(); + + + +-- releases_rev.minage should accept NULL +ALTER TABLE releases_rev ALTER COLUMN minage DROP NOT NULL; +ALTER TABLE releases_rev ALTER COLUMN minage DROP DEFAULT; +UPDATE releases_rev SET minage = NULL WHERE minage < 0; + + +-- wikipedia link for producers +ALTER TABLE producers_rev ADD COLUMN l_wp varchar(150); + + +-- bayesian rating +ALTER TABLE vn ADD COLUMN c_rating real; +ALTER TABLE vn ADD COLUMN c_votecount integer NOT NULL DEFAULT 0; +UPDATE vn SET + c_rating = (SELECT ( + ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes)*(SELECT AVG(a)::real FROM (SELECT AVG(vote) FROM votes GROUP BY vid) AS v(a)) + SUM(vote)::real) / + ((SELECT COUNT(vote)::real/COUNT(DISTINCT vid)::real FROM votes) + COUNT(uid)::real) + ) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes) + ), + c_votecount = COALESCE((SELECT count(*) FROM votes WHERE vid = id AND uid NOT IN(SELECT id FROM users WHERE ign_votes)), 0); + + +-- vn.c_popularity can be NULL +ALTER TABLE vn ALTER COLUMN c_popularity DROP NOT NULL; +ALTER TABLE vn ALTER COLUMN c_popularity DROP DEFAULT; +CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$ +BEGIN + CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS + SELECT v.uid, v.vid, sqrt(count(*))::real + FROM votes v + JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote + JOIN users u ON u.id = v.uid AND NOT ign_votes + GROUP BY v.vid, v.uid; + CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS + SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid; + UPDATE vn SET c_popularity = (SELECT win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 WHERE vid = id); + RETURN; +END; +$$ LANGUAGE plpgsql; +SELECT update_vnpopularity(); + |