From 4fdcb577534a7e7af8535cb98906b2d55e960659 Mon Sep 17 00:00:00 2001 From: Dessalines Date: Thu, 14 Jan 2021 15:22:07 -0500 Subject: [PATCH] Report only local counts in site_view. - Move open_registrations under top level. - Fixes #1340 --- .../down.sql | 24 ++- .../up.sql | 157 +++++++++++------- src/routes/nodeinfo.rs | 14 +- 3 files changed, 124 insertions(+), 71 deletions(-) diff --git a/migrations/2020-12-02-152437_create_site_aggregates/down.sql b/migrations/2020-12-02-152437_create_site_aggregates/down.sql index 2a2aa97d..914bdb8d 100644 --- a/migrations/2020-12-02-152437_create_site_aggregates/down.sql +++ b/migrations/2020-12-02-152437_create_site_aggregates/down.sql @@ -1,13 +1,21 @@ -- Site aggregates drop table site_aggregates; drop trigger site_aggregates_site on site; -drop trigger site_aggregates_user on user_; -drop trigger site_aggregates_post on post; -drop trigger site_aggregates_comment on comment; -drop trigger site_aggregates_community on community; +drop trigger site_aggregates_user_insert on user_; +drop trigger site_aggregates_user_delete on user_; +drop trigger site_aggregates_post_insert on post; +drop trigger site_aggregates_post_delete on post; +drop trigger site_aggregates_comment_insert on comment; +drop trigger site_aggregates_comment_delete on comment; +drop trigger site_aggregates_community_insert on community; +drop trigger site_aggregates_community_delete on community; drop function site_aggregates_site, - site_aggregates_user, - site_aggregates_post, - site_aggregates_comment, - site_aggregates_community; + site_aggregates_user_insert, + site_aggregates_user_delete, + site_aggregates_post_insert, + site_aggregates_post_delete, + site_aggregates_comment_insert, + site_aggregates_comment_delete, + site_aggregates_community_insert, + site_aggregates_community_delete; diff --git a/migrations/2020-12-02-152437_create_site_aggregates/up.sql b/migrations/2020-12-02-152437_create_site_aggregates/up.sql index b10a5f41..679543d1 100644 --- a/migrations/2020-12-02-152437_create_site_aggregates/up.sql +++ b/migrations/2020-12-02-152437_create_site_aggregates/up.sql @@ -10,10 +10,10 @@ create table site_aggregates ( insert into site_aggregates (site_id, users, posts, comments, communities) select id as site_id, - ( select coalesce(count(*), 0) from user_) as users, - ( select coalesce(count(*), 0) from post) as posts, - ( select coalesce(count(*), 0) from comment) as comments, - ( select coalesce(count(*), 0) from community) as communities + ( select coalesce(count(*), 0) from user_ where local = true) as users, + ( select coalesce(count(*), 0) from post where local = true) as posts, + ( select coalesce(count(*), 0) from comment where local = true) as comments, + ( select coalesce(count(*), 0) from community where local = true) as communities from site; -- initial site add @@ -36,91 +36,134 @@ execute procedure site_aggregates_site(); -- Add site aggregate triggers -- user -create or replace function site_aggregates_user() +create function site_aggregates_user_insert() returns trigger language plpgsql as $$ begin - IF (TG_OP = 'INSERT') THEN - update site_aggregates - set users = users + 1; - ELSIF (TG_OP = 'DELETE') THEN - -- Join to site since the creator might not be there anymore - update site_aggregates sa - set users = users - 1 - from site s - where sa.site_id = s.id; - END IF; + update site_aggregates + set users = users + 1; return null; end $$; -create trigger site_aggregates_user -after insert or delete on user_ +create function site_aggregates_user_delete() +returns trigger language plpgsql +as $$ +begin + -- Join to site since the creator might not be there anymore + update site_aggregates sa + set users = users - 1 + from site s + where sa.site_id = s.id; + return null; +end $$; + +create trigger site_aggregates_user_insert +after insert on user_ +for each row +when (NEW.local = true) +execute procedure site_aggregates_user_insert(); + +create trigger site_aggregates_user_delete +after delete on user_ for each row -execute procedure site_aggregates_user(); +when (OLD.local = true) +execute procedure site_aggregates_user_delete(); -- post -create function site_aggregates_post() +create function site_aggregates_post_insert() returns trigger language plpgsql as $$ begin - IF (TG_OP = 'INSERT') THEN - update site_aggregates - set posts = posts + 1; - ELSIF (TG_OP = 'DELETE') THEN - update site_aggregates sa - set posts = posts - 1 - from site s - where sa.site_id = s.id; - END IF; + update site_aggregates + set posts = posts + 1; return null; end $$; -create trigger site_aggregates_post -after insert or delete on post +create function site_aggregates_post_delete() +returns trigger language plpgsql +as $$ +begin + update site_aggregates sa + set posts = posts - 1 + from site s + where sa.site_id = s.id; + return null; +end $$; + +create trigger site_aggregates_post_insert +after insert on post +for each row +when (NEW.local = true) +execute procedure site_aggregates_post_insert(); + +create trigger site_aggregates_post_delete +after delete on post for each row -execute procedure site_aggregates_post(); +when (OLD.local = true) +execute procedure site_aggregates_post_delete(); -- comment -create function site_aggregates_comment() +create function site_aggregates_comment_insert() returns trigger language plpgsql as $$ begin - IF (TG_OP = 'INSERT') THEN - update site_aggregates - set comments = comments + 1; - ELSIF (TG_OP = 'DELETE') THEN - update site_aggregates sa - set comments = comments - 1 - from site s - where sa.site_id = s.id; - END IF; + update site_aggregates + set comments = comments + 1; return null; end $$; -create trigger site_aggregates_comment -after insert or delete on comment +create function site_aggregates_comment_delete() +returns trigger language plpgsql +as $$ +begin + update site_aggregates sa + set comments = comments - 1 + from site s + where sa.site_id = s.id; + return null; +end $$; + +create trigger site_aggregates_comment_insert +after insert on comment +for each row +when (NEW.local = true) +execute procedure site_aggregates_comment_insert(); + +create trigger site_aggregates_comment_delete +after delete on comment for each row -execute procedure site_aggregates_comment(); +when (OLD.local = true) +execute procedure site_aggregates_comment_delete(); -- community -create function site_aggregates_community() +create function site_aggregates_community_insert() returns trigger language plpgsql as $$ begin - IF (TG_OP = 'INSERT') THEN - update site_aggregates - set communities = communities + 1; - ELSIF (TG_OP = 'DELETE') THEN - update site_aggregates sa - set communities = communities - 1 - from site s - where sa.site_id = s.id; - END IF; + update site_aggregates + set communities = communities + 1; + return null; +end $$; + +create function site_aggregates_community_delete() +returns trigger language plpgsql +as $$ +begin + update site_aggregates sa + set communities = communities - 1 + from site s + where sa.site_id = s.id; return null; end $$; -create trigger site_aggregates_community -after insert or delete on community +create trigger site_aggregates_community_insert +after insert on community for each row -execute procedure site_aggregates_community(); +when (NEW.local = true) +execute procedure site_aggregates_community_insert(); +create trigger site_aggregates_community_delete +after delete on community +for each row +when (OLD.local = true) +execute procedure site_aggregates_community_delete(); diff --git a/src/routes/nodeinfo.rs b/src/routes/nodeinfo.rs index e6c5e6c4..df0064ec 100644 --- a/src/routes/nodeinfo.rs +++ b/src/routes/nodeinfo.rs @@ -46,12 +46,13 @@ async fn node_info(context: web::Data) -> Result, pub usage: NodeInfoUsage, + pub open_registrations: bool, } #[derive(Serialize, Deserialize, Debug)] @@ -88,7 +91,6 @@ struct NodeInfoUsage { pub users: NodeInfoUsers, pub local_posts: i64, pub local_comments: i64, - pub open_registrations: bool, } #[derive(Serialize, Deserialize, Debug)] -- 2.44.1