diff options
author | Yorhel <git@yorhel.nl> | 2019-12-27 16:05:29 +0100 |
---|---|---|
committer | Yorhel <git@yorhel.nl> | 2019-12-27 16:05:29 +0100 |
commit | 8498c1779c15328049b9b2c7e14bf3755cfdaf85 (patch) | |
tree | bcd6801fd3544bee2ffc593ff48671d23922cc5a /util | |
parent | 2d406640e541c3bd5f9f309b5d0cc7776b7e63ce (diff) |
ulists: Update devdump.pl
Diffstat (limited to 'util')
-rwxr-xr-x | util/devdump.pl | 14 | ||||
-rw-r--r-- | util/sql/func.sql | 26 |
2 files changed, 21 insertions, 19 deletions
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/sql/func.sql b/util/sql/func.sql index 5bb10661..b9fa5ade 100644 --- a/util/sql/func.sql +++ b/util/sql/func.sql @@ -587,19 +587,19 @@ $$ LANGUAGE plpgsql; -- Create ulist labels for new users. -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 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; |