1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
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")
|