# SPDX-FileCopyrightText: 2026 Dennis Fink # # SPDX-License-Identifier: BSD-3-Clause """Replace long URL uniqueness indexes with portable SHA-256 fingerprints. Revision ID: d845aa7fbc19 Revises: c0272f25ce17 """ import sqlalchemy as sa from alembic import op from webmentions_ssg.models import source_target_digest as received_url_digest from webmentions_ssg.models import target_digest as sent_target_digest revision = "d845aa7fbc19" down_revision = "c0272f25ce17" branch_labels = None depends_on = None def _backfill_received() -> None: table = sa.table( "received_webmentions", sa.column("uuid", sa.Uuid()), sa.column("source", sa.Text()), sa.column("target", sa.Text()), sa.column("url_digest", sa.String(64)), ) conn = op.get_bind() while True: # Bounded batches avoid reading the entire production database into RAM. rows = conn.execute( sa.select(table.c.uuid, table.c.source, table.c.target) .where(table.c.url_digest.is_(None)) .limit(500) ).all() if not rows: return for identifier, source, target in rows: conn.execute( table.update() .where(table.c.uuid == identifier) .values(url_digest=received_url_digest(source, target)) ) def _backfill_sent() -> None: table = sa.table( "sent_webmentions", sa.column("uuid", sa.Uuid()), sa.column("target", sa.Text()), sa.column("target_digest", sa.String(64)), ) conn = op.get_bind() while True: rows = conn.execute( sa.select(table.c.uuid, table.c.target) .where(table.c.target_digest.is_(None)) .limit(500) ).all() if not rows: return for identifier, target in rows: conn.execute( table.update() .where(table.c.uuid == identifier) .values(target_digest=sent_target_digest(target)) ) def upgrade() -> None: with op.batch_alter_table("received_webmentions") as batch: batch.add_column(sa.Column("url_digest", sa.String(64), nullable=True)) with op.batch_alter_table("sent_webmentions") as batch: batch.add_column(sa.Column("target_digest", sa.String(64), nullable=True)) _backfill_received() _backfill_sent() # Batch operations are required for portable SQLite support. The source # and target Text columns remain unchanged, with no vendor-specific SQL. with op.batch_alter_table("received_webmentions") as batch: batch.alter_column("url_digest", existing_type=sa.String(64), nullable=False) batch.drop_constraint("uq_webmention_source_target", type_="unique") batch.create_unique_constraint("uq_webmention_url_digest", ["url_digest"]) with op.batch_alter_table("sent_webmentions") as batch: batch.alter_column("target_digest", existing_type=sa.String(64), nullable=False) batch.drop_constraint("uq_sent_webmention_source_target", type_="unique") batch.create_unique_constraint( "uq_sent_webmention_source_target_digest", ["source_id", "target_digest"] ) def downgrade() -> None: with op.batch_alter_table("sent_webmentions") as batch: batch.drop_constraint("uq_sent_webmention_source_target_digest", type_="unique") batch.create_unique_constraint( "uq_sent_webmention_source_target", ["source_id", "target"] ) batch.drop_column("target_digest") with op.batch_alter_table("received_webmentions") as batch: batch.drop_constraint("uq_webmention_url_digest", type_="unique") batch.create_unique_constraint( "uq_webmention_source_target", ["source", "target"] ) batch.drop_column("url_digest")