mastodon.sql

-- UNRELATED ACCOUNT ONLY
DELETE FROM statuses WHERE local IS false AND id NOT IN (SELECT status_id FROM status_pins) AND id NOT IN (SELECT status_id FROM favourites) AND id NOT IN (SELECT status_id FROM bookmarks) AND id NOT IN (SELECT id FROM statuses WHERE id IN (SELECT reblog_of_id FROM statuses WHERE reblog_of_id IS NOT NULL AND (account_id <= 10 OR account_id IN (SELECT target_account_id FROM follows WHERE account_id <= 10)))) AND id NOT IN (SELECT activity_id FROM notifications WHERE type = 'reblog') AND id NOT IN (SELECT status_id FROM mentions WHERE id IN (SELECT activity_id FROM notifications WHERE type = 'mention')) AND account_id NOT IN (SELECT target_account_id FROM follows WHERE account_id <= 10) AND created_at < '';

-- ALL
DELETE FROM statuses WHERE local IS false AND id NOT IN (SELECT status_id FROM status_pins) AND id NOT IN (SELECT status_id FROM favourites) AND id NOT IN (SELECT status_id FROM bookmarks) AND id NOT IN (SELECT id FROM statuses WHERE id IN (SELECT reblog_of_id FROM statuses WHERE account_id <= 10 AND reblog_of_id IS NOT NULL)) AND id NOT IN (SELECT activity_id FROM notifications WHERE type = 'reblog') AND id NOT IN (SELECT status_id FROM mentions WHERE id IN (SELECT activity_id FROM notifications WHERE type = 'mention')) AND created_at < '';

DELETE FROM preview_cards WHERE id NOT IN (SELECT preview_card_id FROM preview_cards_statuses WHERE status_id IN (SELECT status_id FROM status_pins WHERE account_id <= 10)) AND created_at < '';
DELETE FROM preview_cards AS x WHERE NOT EXISTS (SELECT * FROM preview_cards_statuses AS y WHERE x.id = y.preview_card_id);
DELETE FROM preview_cards_statuses AS x WHERE NOT EXISTS (SELECT * FROM preview_cards AS y WHERE x.preview_card_id = y.id);
DELETE FROM preview_cards_statuses AS x WHERE NOT EXISTS (SELECT * FROM statuses AS y WHERE x.status_id = y.id);