Fix unrequired person/role records imported

[andrew-gardener]

What isn’t working as expected on the website?

We would like to remove all person records where the person is ONLY attached to title records in one or more of the following roles: Donor, Former Owner, Dedicatee, Associated Name, Stereotyper, Binding designer, Binder, Adopter, Lithographer, Cartographer, Liscensor, Sculptor, Proprietor, Plaintiff, Papermaker, Printer of plates, Publisher of plates. If they are attached in any other role (all of our contributor roles), the record should not be deleted.

If you encountered an error message, please copy the relevant details or take a screenshot and paste it below.

N/A

What is the URL of the page where you encountered the bug?

N/A

What steps did you take before you encountered the bug?

N/A

What browser, operating system, and device were you using when you encountered the error?

N/A

Additional information

Persons associated with valid roles should not be removed but the invalid role linkages should be removed from them.

[andrew-gardener]

For record keeping

SQL to remove persons who are only associated with invalid roles

DELETE 
FROM person
WHERE person.id IN (
    WITH valid_associations AS (
        SELECT person.id AS id, COUNT(title_role.role_id) as valid_association_count
        FROM person
        JOIN title_role ON person.id = title_role.person_id
        WHERE title_role.role_id IN (1,2,3,4,5,6,7,8,9,10,11,12,13,16) 
        GROUP BY person.id
    ),
    invalid_associations AS (
        SELECT person.id AS id, COUNT(title_role.role_id) as invalid_association_count
        FROM person
        JOIN title_role ON person.id = title_role.person_id
        WHERE title_role.role_id NOT IN (1,2,3,4,5,6,7,8,9,10,11,12,13,16) 
        GROUP BY person.id
    )
    SELECT invalid_associations.id
    FROM invalid_associations
    LEFT JOIN valid_associations ON invalid_associations.id = valid_associations.id
    WHERE valid_associations.id IS NULL
)

SQL to remove associations with invalid roles

DELETE 
FROM title_role
WHERE role_id NOT IN (1,2,3,4,5,6,7,8,9,10,11,12,13,16) 

SQL to remove invalid roles

DELETE 
FROM role
WHERE id NOT IN (1,2,3,4,5,6,7,8,9,10,11,12,13,16)