github.com/benoitkugler/goacve@v0.0.0-20201217100549-151ce6e55dc8/server/migration/v2_to_v3/menage/delete_old.sql

-- Ce script supprime les camps anciens ainsi que toutes les personnes
-- liées. Plus précisément on conserve :
--  - les membres de l'asso
--  - les donateurs
--  - les séjours 2018 et plus
--  - les participants/equipiers/responsables liés à de ces séjours
BEGIN;

CREATE FUNCTION too_old(date timestamp) RETURNS bool AS 'SELECT extract(year from date) < 2018;' LANGUAGE SQL;

CREATE TABLE keep_personnes (id_personne int REFERENCES personnes);

INSERT INTO
    keep_personnes (id_personne)
SELECT
    DISTINCT id
FROM
    (
        SELECT
            personnes.id
        FROM
            personnes
            JOIN factures f ON personnes.id = f.id_personne
            JOIN participants p ON f.id = p.id_facture
            JOIN camps c ON p.id_camp = c.id
        WHERE
            NOT too_old(c.date_debut)
        UNION
        SELECT
            personnes.id
        FROM
            personnes
            JOIN participants p ON p.id_personne = personnes.id
            JOIN camps c ON p.id_camp = c.id
        WHERE
            NOT too_old(c.date_debut)
        UNION
        SELECT
            personnes.id
        FROM
            personnes
            JOIN participantsimples p ON p.id_personne = personnes.id
            JOIN camps c ON p.id_camp = c.id
        WHERE
            NOT too_old(c.date_debut)
        UNION
        SELECT
            personnes.id
        FROM
            personnes
            JOIN equipiers p ON p.id_personne = personnes.id
            JOIN camps c ON p.id_camp = c.id
        WHERE
            NOT too_old(c.date_debut)
        UNION
        SELECT
            personnes.id
        FROM
            personnes
        WHERE
            personnes.rang_membre_asso <> ''
        UNION
        SELECT
            dons.id_personne
        FROM
            dons
        WHERE
            dons.id_personne IS NOT NULL
    ) AS pfpgcippgci;

DELETE FROM
    paiements
WHERE
    id = ANY (
        SELECT
            paiements.id
        FROM
            paiements
            JOIN factures f ON paiements.id_facture = f.id
            JOIN participants p ON f.id = p.id_facture
            JOIN camps c ON p.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    paiements
WHERE
    id_facture = ANY (
        SELECT
            factures.id
        FROM
            factures
        WHERE
            id_personne NOT IN (
                SELECT
                    keep_personnes.id_personne
                FROM
                    keep_personnes
            )
    );

DELETE FROM
    factures
WHERE
    id = ANY (
        SELECT
            factures.id
        FROM
            factures
            JOIN participants p ON factures.id = p.id_facture
            JOIN camps c ON p.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    factures
WHERE
    id_personne NOT IN (
        SELECT
            keep_personnes.id_personne
        FROM
            keep_personnes
    );

DELETE FROM
    documents
WHERE
    id = ANY (
        SELECT
            document_aides.id_document
        FROM
            document_aides
            JOIN aides a ON document_aides.id_aide = a.id
            JOIN participants p ON a.id_participant = p.id
            JOIN camps c ON p.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    aides
WHERE
    id = ANY (
        SELECT
            aides.id
        FROM
            aides
            JOIN participants p ON aides.id_participant = p.id
            JOIN camps c ON p.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    messages
WHERE
    id = ANY (
        SELECT
            message_placeliberes.id_message
        FROM
            message_placeliberes
            JOIN participants p ON message_placeliberes.id_participant = p.id
            JOIN camps c ON p.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    participants
WHERE
    id = ANY (
        SELECT
            participants.id
        FROM
            participants
            JOIN camps c ON participants.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    participantsimples
WHERE
    id = ANY (
        SELECT
            participantsimples.id
        FROM
            participantsimples
            JOIN camps c ON participantsimples.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    equipiers
WHERE
    id = ANY (
        SELECT
            equipiers.id
        FROM
            equipiers
            JOIN camps c ON equipiers.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    documents
WHERE
    id = ANY (
        SELECT
            document_camps.id_document
        FROM
            document_camps
            JOIN camps c ON document_camps.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    messages
WHERE
    id = ANY (
        SELECT
            message_documents.id_message
        FROM
            message_documents
            JOIN camps c ON message_documents.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    messages
WHERE
    id = ANY (
        SELECT
            message_sondages.id_message
        FROM
            message_sondages
            JOIN camps c ON message_sondages.id_camp = c.id
        WHERE
            too_old(c.date_debut)
    );

DELETE FROM
    camps
WHERE
    too_old(camps.date_debut);

DELETE FROM
    documents
WHERE
    id = ANY (
        SELECT
            id_document
        FROM
            document_personnes
        WHERE
            id_personne NOT IN (
                SELECT
                    id_personne
                FROM
                    keep_personnes
            )
    );

DELETE FROM
    personnes
WHERE
    id NOT IN (
        SELECT
            id_personne
        FROM
            keep_personnes
    );

-- cleanup
SELECT
    count(*)
FROM
    personnes;

DROP TABLE keep_personnes;

DROP FUNCTION too_old (date timestamp);

COMMIT;