aboutsummaryrefslogtreecommitdiff
path: root/webmentions_ssg/migrations/versions
diff options
context:
space:
mode:
Diffstat (limited to 'webmentions_ssg/migrations/versions')
-rw-r--r--webmentions_ssg/migrations/versions/d845aa7fbc19_portable_url_fingerprints.py109
1 files changed, 109 insertions, 0 deletions
diff --git a/webmentions_ssg/migrations/versions/d845aa7fbc19_portable_url_fingerprints.py b/webmentions_ssg/migrations/versions/d845aa7fbc19_portable_url_fingerprints.py
new file mode 100644
index 0000000..0157df3
--- /dev/null
+++ b/webmentions_ssg/migrations/versions/d845aa7fbc19_portable_url_fingerprints.py
@@ -0,0 +1,109 @@
+# SPDX-FileCopyrightText: 2026 Dennis Fink <me+coding@dennisfink.me>
+#
+# 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.url_digest import received_url_digest, 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")