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

[Bob Jonkman]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Looks like there's not much activity on this list. You may find it
useful to join the XMPP chat room at
xmpp://address@hidden or the IRC channel
irc://irc.freenode.net/#social (or webchat
https://webchat.freenode.net?channels=social )

The XMPP and IRC are the same, bridged by a bot. It seems there are
several people in IRC using Matrix, too.

- --Bob.


On 2017-10-12 09:31 PM, Steve Revilak wrote:
> 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
> 
> 

- -- 
Bob Jonkman <address@hidden>          Phone: +1-519-635-9413
SOBAC Microcomputer Services             http://sobac.com/sobac/
Software   ---   Office & Business Automation   ---   Consulting
GnuPG Fngrprnt:04F7 742B 8F54 C40A E115 26C2 B912 89B0 D2CC E5EA

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2
Comment: Ensure confidentiality, authenticity, non-repudiability

iEYEARECAAYFAlnjrG0ACgkQuRKJsNLM5erO/gCgth/woSlNEtBXeJ3FsK+AnZUd
iEQAoKI2tRJqR3FubQjkJycPO8Q9vB04
=Vmvz
-----END PGP SIGNATURE-----