e0824c4f1b0b_responsables_assoc_unicity.py

"""responsables_assoc_unicity

Revision ID: e0824c4f1b0b
Revises: bc85a55e63e1
Create Date: 2025-02-03 16:45:13.082716

"""

from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "e0824c4f1b0b"
down_revision = "bc85a55e63e1"
branch_labels = None
depends_on = None


def upgrade():
    # Suppression des doublons dans la table d'association notes_formsemestre_responsables
    op.execute(
        """
        WITH duplicates AS (
            SELECT
                formsemestre_id,
                responsable_id,
                ctid AS row_identifier,
                row_number() OVER (
                    PARTITION BY formsemestre_id, responsable_id
                    ORDER BY ctid
                ) AS rn
            FROM notes_formsemestre_responsables
        )
        DELETE FROM notes_formsemestre_responsables
        WHERE ctid IN (
            SELECT row_identifier FROM duplicates WHERE rn > 1
        );
    """
    )

    with op.batch_alter_table(
        "notes_formsemestre_responsables", schema=None
    ) as batch_op:
        batch_op.alter_column(
            "formsemestre_id", existing_type=sa.INTEGER(), nullable=False
        )
        batch_op.alter_column(
            "responsable_id", existing_type=sa.INTEGER(), nullable=False
        )
        batch_op.create_unique_constraint(
            "uq_notes_formsemestre_responsables", ["formsemestre_id", "responsable_id"]
        )


def downgrade():
    with op.batch_alter_table(
        "notes_formsemestre_responsables", schema=None
    ) as batch_op:
        batch_op.alter_column(
            "responsable_id", existing_type=sa.INTEGER(), nullable=True
        )
        batch_op.alter_column(
            "formsemestre_id", existing_type=sa.INTEGER(), nullable=True
        )
        batch_op.drop_constraint("uq_notes_formsemestre_responsables", type_="unique")