muddybulldog

test

Maybe of interest to small, semi-private instances.

Based on original work by @[email protected]

/*
If you use Lemmy Community Seeder or other jumpstart method to automatically subscribe to communities you may find a large number of low-traffic communities build up over time. The following can be used to purge the content within these communities en masse. Will not purge local instance communities regardless of activity level. Tested with 18.1-18.3. No warranties.

This is useful for purging the content of dead communities if a purge fails or simply to get rid of unwanted content quickly.

IMPORTANT: 
You should always attempt to remove and/or purge communities via Lemmy's built-in mechanisms, first. This will give opportunity to signal remote instances to not deliver future content from these communities. As of the time of writing the effectiveness of these built-in actions have questionable reliability and effectiveness so this can be an alternative to at least get rid of the existing content.

Remote instances will not receive the appropriate signal to stop pushing content to your instance.
If they believe there are still subscribed users on your instance the community will eventually reappear with NEW content. This script will only be useful for getting rid of existing content. At this time there is no known method to reliably "correct" this at a later date.

No effort will be made to remove images from PICT-RS. Content will be orphaned and remain as flotsam 
until maintenance is performed on the PICT-RS registry.
*/

DO $$
DECLARE

-- Configurable variables
subscriberThreshold INT := 0;		-- Communities with less than subscriberThreshold subscribers will be purged.
postCount INT := 0;			-- Communities with less than postCount posts will be purged.
commentCount INT := 0;			-- Communities with less than commentCount comments will be purged.
dryRun BOOLEAN := true;			-- dryRun := true to simulate purge. dryRun := false to actually purge. Destructive. No warranties.
purgeCommunityID BOOLEAN := false;	-- purgeCommunityID := false don't purge community metadata (prevents new id being created if community still exists or reappears)
--

arow record;
communityID INT;	
communityName VARCHAR;

BEGIN
    for arow in
    
    SELECT community_aggregates.community_id, community.actor_id FROM
	   	public.community_aggregates INNER JOIN
    	public.community On public.community_aggregates.community_id = community.id
    	WHERE instance_id <> 1 AND ((community_aggregates.subscribers < subscriberThreshold) or (community_aggregates.posts < postCount) or (community_aggregates.comments < commentCount))

	loop
		communityID := arow.community_id;
		communityName := arow.actor_id;
		RAISE NOTICE 'Purging community content: %', communityName;

		-- Easiest way to disable triggers for a transaction.
		SET session_replication_role = replica;

		-- Delete Comment Likes
		DELETE FROM comment_like WHERE post_id IN (SELECT id FROM post WHERE community_id = communityID);
		
		-- Delete Comment Aggregates
		DELETE FROM comment_aggregates WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		-- Delete Comment Replies
		DELETE FROM comment_reply WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		-- Delete Comment Reports
		DELETE FROM comment_report WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		-- Delete Comment Saved
		DELETE FROM comment_saved WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		DELETE FROM community_aggregates WHERE community_id = communityID;
		DELETE FROM community_block WHERE community_id = communityID;
		DELETE FROM community_follower WHERE community_id = communityID;
		DELETE FROM community_language WHERE community_id = communityID;
		DELETE FROM community_moderator WHERE community_id = communityID;
		DELETE FROM community_person_ban WHERE community_id = communityID;
		DELETE FROM mod_add_community WHERE community_id = communityID;
		DELETE FROM mod_ban_from_community WHERE community_id = communityID;
		
		DELETE FROM mod_feature_post WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		DELETE FROM mod_hide_community WHERE community_id = communityID;
		
		DELETE FROM mod_lock_post WHERE post_id IN (
		    (SELECT id FROM post WHERE community_id = communityID)
		);
		
		DELETE FROM mod_remove_comment WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		DELETE FROM mod_remove_community WHERE community_id = communityID;
		
		DELETE FROM mod_remove_post WHERE post_id IN (
		    (SELECT id FROM post WHERE community_id = communityID)
		);
		
		DELETE FROM mod_transfer_community WHERE community_id = communityID;
		
		DELETE FROM person_mention WHERE comment_id IN (
		    SELECT id FROM comment WHERE post_id IN (
		        (SELECT id FROM post WHERE community_id = communityID)
		    )
		);
		
		DELETE FROM post_aggregates WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		
		DELETE FROM post_like WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		DELETE FROM post_read WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		DELETE FROM post_report WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		
		DELETE FROM post_saved WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		-- Delete comments for posts IN removed community
		DELETE FROM comment WHERE post_id IN (
		    SELECT id FROM post WHERE community_id = communityID
		);
		
		-- These last two must be last as they are used to find related entries IN all of the other tables.
		-- Delete the posts for the community
		DELETE FROM post WHERE community_id = communityID;
		    
		-- Delete the community
		IF purgeCommunityID = true THEN
			RAISE NOTICE 'Purging community metadata: %', communityName;
			DELETE FROM community WHERE id = communityID;
		ELSE
			RAISE NOTICE 'Retaining community metadata: %', communityName;
		END IF; 
		
	
***
If there are no errors or issues, commit
		IF dryRun = true THEN
			ROLLBACK;
		END IF;
			
		IF dryRun = false THEN
			COMMIT;
		END IF;
		
	END LOOP;

	IF dryRun = true THEN
		RAISE NOTICE 'Dry-run only. No data actually purged.';
		RAISE NOTICE 'Set dryRun BOOLEAN := true to really purge';
		ROLLBACK;
	END IF;
	
	IF dryRun = false THEN
		COMMIT;
	END IF;
END $$;
``` []() []()

CREATE OR REPLACE VIEW public.local_community_remote_subscribers AS
 SELECT community.name AS "community.name",
    community.actor_id AS "community.actor_id",
    person.name AS "person.name",
    person.display_name AS "person.display_name",
    person.actor_id AS "person.actor_id",
    person.last_refreshed_at AS "person.last_refreshed_at",
    person.bot_account AS "person.bot_account"
   FROM ((community
     JOIN community_follower ON ((community_follower.community_id = community.id)))
     JOIN person ON ((community_follower.person_id = person.id)))
  WHERE ((community.instance_id = 1) AND (person.instance_id <> 1));

This will create a view within the Lemmy database, for reusability. If you just want to run a one-off query or prefer to store it in other form, omit the first line.

New body

Original post: https://lemmy.ml/post/1848545 or https://mylemmy.win/post/117634 - reconciles all users, foreign and domestic. This may take a VERY long time (minutes, hours, days on a really big instance).

The following, modified version, reconciles only local users. For those interested in supporting users expecting these values to be surfaced like "karma" in apps that support it. Much faster than reconciling the entire user database. (works with 18.1; maybe later, maybe not; use at your own risk)

MERGE INTO "person_aggregates" AS "d"
 USING (SELECT "m"."id" AS "person_id"
             , coalesce("p"."post_count", 0) AS "post_count"
             , coalesce("p"."post_score", 0) AS "post_score"
             , coalesce("c"."comment_count", 0) AS "comment_count"
             , coalesce("c"."comment_score", 0) AS "comment_score"
          FROM "local_person" AS "m"
          LEFT JOIN (SELECT "p"."creator_id"
                          , count(distinct "p"."id") AS "post_count"
                          , sum("l"."score") AS "post_score"
                       FROM "post" AS "p"
                       LEFT JOIN "post_like" AS "l"
                         ON "l"."post_id" = "p"."id"
                      WHERE NOT "p"."removed"
                        AND NOT "p"."deleted"
                        AND "l"."person_id" <> "p"."creator_id"
                      GROUP BY "p"."creator_id") AS "p"
            ON "p"."creator_id" = "m"."id"
          LEFT JOIN (SELECT "c"."creator_id"
                          , count(distinct "c"."id") AS "comment_count"
                          , sum("l"."score") AS "comment_score"
                       FROM "comment" AS "c"
                       LEFT JOIN "comment_like" AS "l"
                         ON "l"."comment_id" = "c"."id"
                      WHERE NOT "c"."removed"
                        AND NOT "c"."deleted"
                        AND "l"."person_id" <> "c"."creator_id"
                      GROUP BY "c"."creator_id") AS "c"
            ON "c"."creator_id" = "m"."id"
         ORDER BY "m"."id") AS "s"
    ON "s"."person_id" = "d"."person_id"
  WHEN MATCHED AND ("d"."post_count" <> "s"."post_count" OR
                    "d"."post_score" <> "s"."post_score" OR
                    "d"."comment_count" <> "s"."comment_count" OR
                    "d"."comment_score" <> "s"."comment_score")
  THEN UPDATE
          SET "post_count" = "s"."post_count"
            , "post_score" = "s"."post_score"
            , "comment_count" = "s"."comment_count"
            , "comment_score" = "s"."comment_score";

CREATE OR REPLACE VIEW public.local_person_aggregates AS
 SELECT person_aggregates.id AS "person_aggregates.id",
    person_aggregates.person_id AS "person_aggregates.person_id",
    person_aggregates.post_count AS "person_aggregates.post_count",
    person_aggregates.post_score AS "person_aggregates.post_score",
    person_aggregates.comment_count AS "person_aggregates.comment_count",
    person_aggregates.comment_score AS "person_aggregates.comment_score"
   FROM (local_user
     JOIN person_aggregates ON ((person_aggregates.person_id = local_user.person_id)));

This will create a view within the Lemmy database, for reusability. If you just want to run a one-off query or prefer to store it in other form, omit the first line.

CREATE OR REPLACE VIEW public.local_person_subscribed_communities AS
 SELECT person.id AS "person.id",
    person.name AS "person.name",
    person.display_name AS "person.display_name",
    person.actor_id AS "person.actor_id",
    community.id AS "community.id",
    community.name AS "community.name",
    community.title AS "community.title",
    community.description AS "community.description",
    community.actor_id AS "community.actor_id",
    community.published AS "community.published",
    community.updated AS "community.updated",
    community.removed AS "community.removed"
   FROM ((community
     JOIN community_follower ON ((community_follower.community_id = community.id)))
     JOIN person ON ((community_follower.person_id = person.id)))
  WHERE (person.instance_id = 1);

This will create a view within the Lemmy database, for reusability. If you just want to run a one-off query or prefer to store it in other form, omit the first line.

CREATE OR REPLACE VIEW public.local_community_external_subscribers AS
 SELECT community.name AS "community.name",
    community.actor_id AS "community.actor_id",
    person.name AS "person.name",
    person.display_name AS "person.display_name",
    person.actor_id AS "person.actor_id",
    person.last_refreshed_at AS "person.last_refreshed_at",
    person.bot_account AS "person.bot_account"
   FROM ((community
     JOIN community_follower ON ((community_follower.community_id = community.id)))
     JOIN person ON ((community_follower.person_id = person.id)))
  WHERE (community.instance_id = 1);

This will create a view within the Lemmy database, for reusability. If you just want to run a one-off query or prefer to store it in other form, omit the first line.

cross-posted from: https://lemmy.world/post/1253449\n\n> Test

There's some misinformation floating around regarding Lemmy not having a karma system. While many have discovered otherwise, this is for those who may not have.

While it's not exposed in the Lemmy default user interface, Lemmy does have a fully functional karma system and it is visible in third party clients such as WefWef and Memmy.

Do with that what you will.

https://join-lemmy.org/api/interfaces/PersonAggregates.html

cross-posted from: https://lemmy.fmhy.ml/post/701679\n\n> Just trying out how to post a text post (without link/image).

I see you found my test community. Welcome aboard!