launchpad-dev team mailing list archive

[curtis Hovey <curtis.hovey@xxxxxxxxxxxxx>]

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
    )
;

Attachment: signature.asc
Description: OpenPGP digital signature