= Deleting old identi.ca account remote subs v0.04 ---- == Introduction Changes: * [0.04] Added some delete examples for [[http://postblue.info/|postblue]] ( [[http://sn.postblue.info/conversation/355083|dent]] ) * [0.03] Nothing, just regen'd for fun (and title change) * [0.02] Added group_member info * [0.01] Wrote it Key tables seem to be: * //profile// * //hubsub// * //subscription// * //ostatus_profile// * //group_member// **WARNING:** This is just what I quickly identified myself and is not necessarily all correct. Proceed with caution, backup your DB, blah blah blah. //**NOTE:** The profile entries should probably stay since they are referenced all over tha place (such as when you reference someone etc).// The SQL statements are simple and not in any way optimal for automated removal. There's some wicked JOINs you could do and other stuff to clear out ALL your identi.ca links automatically but I leave that as an exercise for the reader, I put this here mainly to show what I was able to gleem from my quick look at the database. The irony is that this document took me longer to write than what I was able to discover so I probably should have just written a tool but oh well. ---- == Removing users subscribed to you === Find the hubsubs {{{ mysql> SELECT topic,callback,created FROM hubsub WHERE callback LIKE '%identi.ca%'; +---------------------------------------------------------+-------------------------------------------+---------------------+ | topic | callback | created | +---------------------------------------------------------+-------------------------------------------+---------------------+ | http://u.qdnx.org/api/statuses/user_timeline/1.atom | http://identi.ca/main/push/callback/16396 | 2013-05-22 16:33:07 | | http://u.qdnx.org/api/statusnet/groups/timeline/35.atom | http://identi.ca/main/push/callback/16453 | 2013-06-01 13:45:57 | +---------------------------------------------------------+-------------------------------------------+---------------------+ 1 row in set (0.00 sec) }}} This appears to be what causes status.net to push out updates from //topic// to //callback//. There's not one per subscriber because it's smart ;) It'll probably be one entry for each user you have locally (who has identi.ca subscribers) and 1 for each group you have that identi.ca users are members of. Blow these away (if you're game). An example of how to do this. **Note that this example WILL delete ALL of the entries returned above**: {{{ mysql> DELETE FROM hubsub WHERE callback LIKE '%identi.ca%'; }}} To delete just one, you could do: {{{ mysql> DELETE FROM hubsub WHERE callback='http://identi.ca/main/push/callback/16396'; }}} This would just delete the first entry (the reference to the user timeline for user 1, in this case, me). === Find the subscription entries These are presumably what causes the list on the sidebar. {{{ mysql> SELECT subscription.subscriber AS who ,subscription.subscribed AS subto ,subscription.created AS created ,profile.id AS id ,profile.nickname AS nick ,profile.fullname AS name ,profile.profileurl AS url FROM subscription ,profile WHERE subscription.subscriber = profile.id AND profile.profileurl LIKE '%identi.ca%' ; }}} You should see a nice summary list: {{{ +-----+-------+---------------------+-----+-------------+-------------+------------------------------+ | who | subto | created | id | nick | name | url | +-----+-------+---------------------+-----+-------------+-------------+------------------------------+ | 30 | 1 | 2013-05-22 16:33:10 | 30 | lgdb | lgdb | http://identi.ca/lgdb | | 85 | 1 | 2013-06-01 12:30:20 | 85 | krayon | krayon | http://identi.ca/krayon | | 100 | 1 | 2013-05-22 16:41:48 | 100 | peter64 | peter64 | http://identi.ca/peter64 | | 126 | 1 | 2013-05-24 05:40:38 | 126 | dirklauer13 | dirklauer13 | http://identi.ca/dirklauer13 | | 156 | 1 | 2013-05-28 12:15:39 | 156 | nido | nido | http://identi.ca/nido | +-----+-------+---------------------+-----+-------------+-------------+------------------------------+ }}} There are the identi.ca users that are subscribed to my local users. //subto// is the ID of the user they are subscribed to. Each user (remote or local) has an entry in the profile table. This is to what the //subto// and //who// IDs refer in the above. They SHOULD all have an //ostatus_profile// entry too: {{{ mysql> SELECT subscription.subscriber AS who ,ostatus_profile.profile_id AS osid ,profile.id AS id ,profile.nickname AS nick ,profile.fullname AS name ,profile.profileurl AS url FROM subscription ,ostatus_profile ,profile WHERE ostatus_profile.profile_id = profile.id AND subscription.subscriber = profile.id AND profile.profileurl LIKE '%identi.ca%' ; }}} Yep: {{{ +-----+------+-----+-------------+-------------+------------------------------+ | who | osid | id | nick | name | url | +-----+------+-----+-------------+-------------+------------------------------+ | 30 | 30 | 30 | lgdb | lgdb | http://identi.ca/lgdb | | 85 | 85 | 85 | krayon | krayon | http://identi.ca/krayon | | 100 | 100 | 100 | peter64 | peter64 | http://identi.ca/peter64 | | 126 | 126 | 126 | dirklauer13 | dirklauer13 | http://identi.ca/dirklauer13 | | 156 | 156 | 156 | nido | nido | http://identi.ca/nido | +-----+------+-----+-------------+-------------+------------------------------+ }}} So the entries from //subscription// and //ostatus_profile// can be deleted. To delete the pesky //peter64//'s subscription to my account ( //sudto// **1** in the output earlier ) we could do the following. Note that I renamed the fields in the output from //subscriber// to //who// and from //subscribed// to //subto//: {{{ mysql> DELETE FROM subscription WHERE subscriber=100 AND subscribed=1; mysql> DELETE FROM ostatus_profile WHERE profile_id=100; }}} NOTE that you may not want to remove the ostatus_profile because it may be what controls the link in the '@USER' bit of any dents you've directed to them and maybe other stuff too. Basically this procedure is more or less surgically removing the user which you may not want to do. IF so, just remove the //subscription//. ---- == Removing users who are members of your groups If you haven't already, remove their hubsub's from the user step above. Now you can remove them from the //group_member// table. {{{ mysql> SELECT group_member.group_id AS gid ,profile.id AS id ,profile.nickname AS nick ,profile.fullname AS name ,profile.profileurl AS url FROM group_member ,profile WHERE group_member.profile_id = profile.id AND profile.profileurl LIKE '%identi.ca%' ; }}} Kill 'em: {{{ +-----+----+--------+--------+-------------------------+ | gid | id | nick | name | url | +-----+----+--------+--------+-------------------------+ | 35 | 85 | krayon | krayon | http://identi.ca/krayon | +-----+----+--------+--------+-------------------------+ }}} So that pesky '//krayon//' over at identica appears to be a member of my group with the id 35 eh... WELL... not any more: {{{ mysql> DELETE FROM group_member WHERE id=85 AND gid=35; }}} Since the 85 is his ID on MY instance, I could remove him from ALL of my groups by just leaving out the //gid// check: {{{ mysql> DELETE FROM group_member WHERE id=85; }}} ,,//**Generated using:** <>//,, <<< vim:set ts=4 sw=4 et syn=creole: >>>