aboutsummaryrefslogtreecommitdiff
path: root/webmentions_ssg/migrations/versions/d845aa7fbc19_portable_url_fingerprints.py
blob: 0157df3c6ae9f297578d4c0d729e4a56f21effcd (plain) (blame)
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")