[Social-discuss] Questions about gnusocial notices and data retention

[Steve Revilak]

Hello Social Discuss,

I'd like to ask a few question about post deletions and data
retention.

Older versions of gnusocial handled post deletion by deleting posts.
The current master branch does something a little different: it
retains the post, but replaces the body with something to the effect
of 

 Steve R deleted notice
 {{tag:social.mayfirst.org,2017-08-06:noticeId=3622382:objectType=note}}.

I prefer to leave posts up for a couple of weeks and then delete them;
this is my own personal data retention policy.  And it will looks
silly if my profile (over time) contains a few weeks of posts,
followed by lots and lots of deletions.

I've considered removing these deleted posts directly from gnusocial's
database, and based on my observations, I think the approprate
statements are

 delete from notice where verb = 'delete'; 
 delete from conversation where id not in (select conversation from notice);

Are there downsides to this?  Are there other tables that need to be
cleaned out?

My second question is more along the lines of site-wide data
retention.  I'd like to purge non-local notices from my gnusocial
instance after N months (where N is something like 6).  For this case,
it looks like the appropriate statements are

 delete from notice where is_local <> 1 and created < @some_date;
 delete from conversation where id not in (select conversation from notice);

(Is "is_local <> 1" sufficient, or should I verify that
notice.profile_id doesn't appear in user.id?)

Do these approaches seem reasonable, or would you suggest something
else?

Steve

signature.asc
Description: Digital signature