Manual SQL Delete Registration - AppStateESS/clubconnect GitHub Wiki

Because of the temporal nature of the design of Organization Registration, "delete" is not a thing we expected to have to do.

Because of the astonishing ability of users to find bugs in your software and exploit them over and over, turns out "delete" is a nice thing to have around, although it really shouldn't be something that happens often.

So, I made this handy-dandy function called remove_registration:

DECLARE
    reg RECORD;
    org RECORD;
BEGIN
    SELECT organization_id, officer_request_id, state INTO reg FROM sdr_organization_registration_view_current WHERE registration_id = id;

    IF reg.state = 'Certified' THEN
        RAISE NOTICE 'Specified registration is certified, skipping';
        RETURN;
    END IF;

    DELETE FROM sdr_organization_registration_state WHERE registration_id = id;
    DELETE FROM sdr_organization_registration_data  WHERE registration_id = id;
    DELETE FROM sdr_organization_registration       WHERE registration_id = id;

    DELETE FROM sdr_officer_request_member WHERE officer_request_id = reg.officer_request_id;
    DELETE FROM sdr_officer_request        WHERE officer_request_id = reg.officer_request_id;

    SELECT COUNT(*) AS ct INTO org FROM sdr_organization_instance WHERE organization_id = reg.organization_id;

    IF org.ct != 0 THEN
        RAISE NOTICE 'Organization Instance exists, deleted reg but not org.';
        RETURN;
    ELSE
        DELETE FROM sdr_organization WHERE sdr_organization.id = reg.organization_id;
    END IF;
END;

This function will NOT let you delete a registration that has been CERTIFIED, because that is something that REALLY REALLY should not ever happen. .... except when it should, so if that case comes up, it's probably because someone selected "new club" when they should have selected "existing club" and it got past Terri and became an official thing.

So to make sure you're really serious about deleting a certified registration, you have to un-certify it, like so:

SELECT registration_id FROM sdr_organization_registration_view_short WHERE fullname ilike '...';

This will get you a registration_id that you need to use to delete the Certified state and unexpire the Approved state, like so:

DELETE FROM sdr_organization_registration_state
    WHERE registration_id=... AND state='Certified';
UPDATE sdr_organization_registration_state SET effective_until=null
    WHERE registration_id=... AND state='Approved';

Here is how you actually use remove_registration():

Get the registration_id:

SELECT * FROM sdr_organization_registration_view_short WHERE fullname ILIKE '%...%';

And pass that into remove_registration():

SELECT remove_registration(...);

and done!