Changes:
Key tables seem to be:
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.
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).
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.
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: creole -f HTML4 -s style.css -t 'Deleting old identi.ca account remote subs' del_identica_subs.creole.txt