On 10/12/2011 06:07 AM, Jeroen Vermeulen wrote: > Due to a problem with the person-merging code, it seems we have some > team membership records that can't be removed. > > Here's a particularly annoying case where membership requests can't be > approved or denied: > https://answers.launchpad.net/launchpad/+question/173909 > > As things stand, these requests are permanent garbage. Very annoying > for team admins. https://bugs.launchpad.net/bugs/58138 Yes, this should be a garbo job to handle deactivate, suspended, and merged persons. There is never enough time to implement a fix.
A similar fix was made for answer contacts a few months ago. The fix is almost identical to the script I have used to fix vestigial data. I am attaching my script -- Curtis Hovey http://launchpad.net/~sinzui
-- Update membership and delete subscriptions for merged, deactivated
-- and suspended users. This script clean all data that was not updated
-- by the status change that is still visible in the UI.
-- Proposed or Invited member that is merged or deactivated;
-- make declined (6)
-- staging 85
UPDATE TeamMembership
SET status = 6
WHERE id in (
SELECT TeamMembership.id
FROM TeamMembership
JOIN Person ON TeamMembership.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
AND TeamMembership.status in (1, 7)
)
;
-- Approved or Admin member, make deactivated (4)
-- Suspended users are not removed because some bots like ~katie must
-- be members of a team.
-- staging 44
UPDATE TeamMembership
SET status = 4
WHERE id in (
SELECT TeamMembership.id
FROM TeamMembership
JOIN Person ON TeamMembership.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Person.merged IS NOT NULL
OR (
Account.status = 30
AND TeamMembership.status in (2, 3))
)
;
-- Delete bugsubscriptions of deactivated and suspended users.
-- staging 10544
DELETE
FROM BugSubscription
WHERE id in (
SELECT BugSubscription.id
FROM BugSubscription
JOIN Person ON BugSubscription.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- Delete structuralsubscriptions of deactivated and suspended users.
-- staging 289
DELETE
FROM StructuralSubscription
WHERE id in (
SELECT StructuralSubscription.id
FROM StructuralSubscription
JOIN Person ON StructuralSubscription.subscriber = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- Delete SpecificationSubscription of deactivated and suspended users.
-- staging 14
DELETE
FROM SpecificationSubscription
WHERE id in (
SELECT SpecificationSubscription.id
FROM SpecificationSubscription
JOIN Person ON SpecificationSubscription.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- Delete BranchSubscription of deactivated and suspended users.
-- staging 167
DELETE
FROM BranchSubscription
WHERE id in (
SELECT BranchSubscription.id
FROM BranchSubscription
JOIN Person ON BranchSubscription.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- Delete ArchiveSubscriber of deactivated and suspended users.
-- staging 3
DELETE
FROM ArchiveSubscriber
WHERE id in (
SELECT ArchiveSubscriber.id
FROM ArchiveSubscriber
JOIN Person ON ArchiveSubscriber.subscriber = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- Delete AnswerContact of deactivated and suspended users.
-- staging 3
DELETE
FROM AnswerContact
WHERE id in (
SELECT AnswerContact.id
FROM AnswerContact
JOIN Person ON AnswerContact.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- Delete POSubscription of deactivated and suspended users.
-- staging 0
DELETE
FROM POSubscription
WHERE id in (
SELECT POSubscription.id
FROM POSubscription
JOIN Person ON POSubscription.person = Person.id
JOIN Account ON Person.account = Account.id
WHERE
Account.status in (30, 40)
)
;
-- ============
-- merged teams
-- ============
-- Update membership and delete subscriptions for merged teams
-- This script clean all data that was not updated
-- by the status change that is still visible in the UI.
-- Proposed or Invited member; make declined (6)
-- staging 43
UPDATE TeamMembership
SET status = 6
WHERE id in (
SELECT TeamMembership.id
FROM TeamMembership
JOIN Person ON TeamMembership.person = Person.id
WHERE
person.merged IS NOT NULL
AND TeamMembership.status in (1, 7)
)
;
-- Approved or Admin member, make deactivated (4)
-- staging 6
UPDATE TeamMembership
SET status = 4
WHERE id in (
SELECT TeamMembership.id
FROM TeamMembership
JOIN Person ON TeamMembership.person = Person.id
WHERE
person.merged IS NOT NULL
AND TeamMembership.status in (2, 3)
)
;
-- Delete bugsubscriptions of merged users and teams.
-- staging 5
DELETE
FROM BugSubscription
WHERE id in (
SELECT BugSubscription.id
FROM BugSubscription
JOIN Person ON BugSubscription.person = Person.id
WHERE
person.merged IS NOT NULL
)
;
-- Delete structuralsubscriptions of merged users and teams.
-- staging 5
DELETE
FROM StructuralSubscription
WHERE id in (
SELECT StructuralSubscription.id
FROM StructuralSubscription
JOIN Person ON StructuralSubscription.subscriber = Person.id
WHERE
person.merged IS NOT NULL
)
;
-- Delete SpecificationSubscription of merged users and teams.
-- staging 0
DELETE
FROM SpecificationSubscription
WHERE id in (
SELECT SpecificationSubscription.id
FROM SpecificationSubscription
JOIN Person ON SpecificationSubscription.person = Person.id
WHERE
person.merged IS NOT NULL
)
;
-- Delete BranchSubscription of merged users and teams.
-- staging 0
DELETE
FROM BranchSubscription
WHERE id in (
SELECT BranchSubscription.id
FROM BranchSubscription
JOIN Person ON BranchSubscription.person = Person.id
WHERE
person.merged IS NOT NULL
)
;
-- Delete ArchiveSubscriber of merged users and teams.
-- staging 0
DELETE
FROM ArchiveSubscriber
WHERE id in (
SELECT ArchiveSubscriber.id
FROM ArchiveSubscriber
JOIN Person ON ArchiveSubscriber.subscriber = Person.id
WHERE
person.merged IS NOT NULL
)
;
-- Delete AnswerContact of merged users and teams.
-- staging 1
DELETE
FROM AnswerContact
WHERE id in (
SELECT AnswerContact.id
FROM AnswerContact
JOIN Person ON AnswerContact.person = Person.id
WHERE
person.merged IS NOT NULL
)
;
-- Delete POSubscription of merged users and teams.
-- staging 0
DELETE
FROM POSubscription
WHERE id in (
SELECT POSubscription.id
FROM POSubscription
JOIN Person ON POSubscription.person = Person.id
WHERE
person.merged IS NOT NULL
)
;
signature.asc
Description: OpenPGP digital signature
_______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : launchpad-dev@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
