50 lines
1.9 KiB
Python
50 lines
1.9 KiB
Python
|
"""normalize video titles for display; keep the raw one in original_title
|
||
|
|
|
||
|
|
Revision ID: 0039_title_normalize
|
||
|
|
Revises: 0038_asset_gc_notified
|
||
|
|
Create Date: 2026-07-03
|
||
|
|
|
||
|
|
Adds videos.original_title (the raw YouTube title) and rewrites videos.title to a normalized,
|
||
|
|
display-friendly form (emoji stripped, ALL-CAPS de-shouted, trailing SEO hashtags removed —
|
||
|
|
see app.titles). Reversible: original_title preserves the source, and title can be re-derived.
|
||
|
|
The generated search_vector column regenerates automatically as each title is updated.
|
||
|
|
"""
|
||
|
|
from typing import Sequence, Union
|
||
|
|
|
||
|
|
import sqlalchemy as sa
|
||
|
|
from alembic import op
|
||
|
|
|
||
|
|
from app.titles import normalize_title
|
||
|
|
|
||
|
|
revision: str = "0039_title_normalize"
|
||
|
|
down_revision: Union[str, None] = "0038_asset_gc_notified"
|
||
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
||
|
|
depends_on: Union[str, Sequence[str], None] = None
|
||
|
|
|
||
|
|
_BATCH = 2000
|
||
|
|
|
||
|
|
|
||
|
|
def upgrade() -> None:
|
||
|
|
op.add_column("videos", sa.Column("original_title", sa.Text(), nullable=True))
|
||
|
|
conn = op.get_bind()
|
||
|
|
# Preserve the raw title first (fast, set-based).
|
||
|
|
conn.execute(sa.text("UPDATE videos SET original_title = title WHERE title IS NOT NULL"))
|
||
|
|
# Then rewrite title to the normalized form, batched (each update regenerates its FTS vector).
|
||
|
|
rows = conn.execute(
|
||
|
|
sa.text("SELECT id, original_title FROM videos WHERE original_title IS NOT NULL")
|
||
|
|
).fetchall()
|
||
|
|
changes = []
|
||
|
|
for vid, raw in rows:
|
||
|
|
norm = normalize_title(raw)
|
||
|
|
if norm != raw:
|
||
|
|
changes.append({"i": vid, "t": norm})
|
||
|
|
stmt = sa.text("UPDATE videos SET title = :t WHERE id = :i")
|
||
|
|
for start in range(0, len(changes), _BATCH):
|
||
|
|
conn.execute(stmt, changes[start : start + _BATCH])
|
||
|
|
|
||
|
|
|
||
|
|
def downgrade() -> None:
|
||
|
|
conn = op.get_bind()
|
||
|
|
conn.execute(sa.text("UPDATE videos SET title = original_title WHERE original_title IS NOT NULL"))
|
||
|
|
op.drop_column("videos", "original_title")
|