65 lines
1.9 KiB
Python
65 lines
1.9 KiB
Python
"""Add unique constraint to ISBN field
|
|
|
|
Revision ID: 5584e4fd820e
|
|
Revises: 0c155d83c55b
|
|
Create Date: 2026-03-31 13:41:16.356631
|
|
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision = '5584e4fd820e'
|
|
down_revision = '0c155d83c55b'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
|
|
# First, convert empty string ISBNs to a temporary placeholder
|
|
# This is needed because we can't set NULL while column is NOT NULL
|
|
op.execute("UPDATE book SET isbn = '__EMPTY_ISBN__' WHERE isbn = ''")
|
|
|
|
# Remove duplicate ISBNs, keeping only the book with the lowest ID
|
|
op.execute("""
|
|
DELETE FROM book
|
|
WHERE isbn != '__EMPTY_ISBN__'
|
|
AND isbn != ''
|
|
AND id NOT IN (
|
|
SELECT MIN(id)
|
|
FROM book
|
|
WHERE isbn != '__EMPTY_ISBN__'
|
|
AND isbn != ''
|
|
GROUP BY isbn
|
|
)
|
|
""")
|
|
|
|
with op.batch_alter_table('book', schema=None) as batch_op:
|
|
# Make the column nullable
|
|
batch_op.alter_column('isbn',
|
|
existing_type=sa.VARCHAR(length=20),
|
|
nullable=True)
|
|
|
|
# Now convert the placeholder values to NULL
|
|
op.execute("UPDATE book SET isbn = NULL WHERE isbn = '__EMPTY_ISBN__'")
|
|
|
|
with op.batch_alter_table('book', schema=None) as batch_op:
|
|
# Add the unique constraint
|
|
batch_op.create_unique_constraint('uq_book_isbn', ['isbn'])
|
|
|
|
# ### end Alembic commands ###
|
|
|
|
|
|
def downgrade():
|
|
# ### commands auto generated by Alembic - please adjust! ###
|
|
with op.batch_alter_table('book', schema=None) as batch_op:
|
|
batch_op.drop_constraint('uq_book_isbn', type_='unique')
|
|
batch_op.alter_column('isbn',
|
|
existing_type=sa.VARCHAR(length=20),
|
|
nullable=False)
|
|
|
|
# ### end Alembic commands ###
|