summaryrefslogtreecommitdiff
path: root/util
diff options
context:
space:
mode:
authorYorhel <git@yorhel.nl>2019-12-27 16:05:29 +0100
committerYorhel <git@yorhel.nl>2019-12-27 16:05:29 +0100
commit8498c1779c15328049b9b2c7e14bf3755cfdaf85 (patch)
treebcd6801fd3544bee2ffc593ff48671d23922cc5a /util
parent2d406640e541c3bd5f9f309b5d0cc7776b7e63ce (diff)
ulists: Update devdump.pl
Diffstat (limited to 'util')
-rwxr-xr-xutil/devdump.pl14
-rw-r--r--util/sql/func.sql26
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;