Sample bot-post delete SQL for Diaspora

gistfile1.txt

### Use at your own risk! ###

No warranty is implied.

This is a sample set of SQL statements (2 to be exact) that will delete old posts from bots on Diaspora pod.

It is intended as a starting point for more sophisticated scripts.  Everything is hard-coded right now.

1.  It is for PostgreSQL.
2.  It will delete posts (and their taggings) from the bots [email protected], [email protected], and
    [email protected] that are greater than 31 days old, have no likes, comments, or reshares, and are not
    from the rss-dilbert or rss-xkcd feed.
3.  It is "slow" on large databases, primarily due to the dilbert and xkcd lookups. That is, it may take more
    than a second or two to run.
4.  Edit to suit your desires.

-----------------------------

    DELETE FROM posts p 
        WHERE likes_count < 1 
        AND reshares_count < 1 
        AND comments_count < 1 
        AND diaspora_handle 
            IN ('[email protected]','[email protected]','[email protected]')
        AND text NOT LIKE '%#rss-dilbert%' 
        AND text NOT LIKE '%#rss-xkcd%' 
        AND now() - created_at > '31 days'::INTERVAL;


    DELETE FROM taggings
        WHERE id IN 
            (SELECT t.id FROM taggings t 
            LEFT JOIN posts p 
                ON t.taggable_id  = p.id 
                    WHERE p.id IS NULL 
                    AND t.taggable_type = 'Post');


-----------------------------