66 lines
3 KiB
Python
66 lines
3 KiB
Python
|
"""richer full-text search document (title + keywords + search terms + description)
|
||
|
|
|
||
|
|
Revision ID: 0032_search_document
|
||
|
|
Revises: 0031_title_fts
|
||
|
|
Create Date: 2026-06-30
|
||
|
|
|
||
|
|
Broadens the feed's full-text search from the title alone to a weighted search document, so the
|
||
|
|
local search behaves more like YouTube's — finding videos by the uploader's own keywords or by
|
||
|
|
the search query that surfaced them, not only by words in the title:
|
||
|
|
|
||
|
|
- `keywords` — the creator's snippet.tags (already fetched with the snippet we request, so
|
||
|
|
zero extra quota), stored space-joined.
|
||
|
|
- `search_terms` — distinct live-search queries that surfaced the video (across all users),
|
||
|
|
appended by the search route. Folds YouTube's relevance judgement into local
|
||
|
|
search: a video YT returned for a query becomes findable by it locally.
|
||
|
|
|
||
|
|
These plus a truncated description feed a STORED generated `search_vector` column, weighted
|
||
|
|
title (A) > keywords+search_terms (B) > description (C) so ts_rank ranks title hits highest. A
|
||
|
|
plain GIN index on the column guarantees the planner uses it (no expression/param matching). The
|
||
|
|
title-only expression index from 0031 is dropped (superseded). Existing rows get title +
|
||
|
|
description indexed immediately; keywords/search_terms fill in as videos are (re-)enriched and
|
||
|
|
searched. Uses the `unaccent_simple` config from 0031 (accent-insensitive).
|
||
|
|
"""
|
||
|
|
from typing import Sequence, Union
|
||
|
|
|
||
|
|
import sqlalchemy as sa
|
||
|
|
from alembic import op
|
||
|
|
|
||
|
|
revision: str = "0032_search_document"
|
||
|
|
down_revision: Union[str, None] = "0031_title_fts"
|
||
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
||
|
|
depends_on: Union[str, Sequence[str], None] = None
|
||
|
|
|
||
|
|
_SEARCH_VECTOR_EXPR = (
|
||
|
|
"setweight(to_tsvector('public.unaccent_simple', coalesce(title, '')), 'A') || "
|
||
|
|
"setweight(to_tsvector('public.unaccent_simple', coalesce(keywords, '') || ' ' || "
|
||
|
|
"coalesce(search_terms, '')), 'B') || "
|
||
|
|
"setweight(to_tsvector('public.unaccent_simple', left(coalesce(description, ''), 1000)), 'C')"
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def upgrade() -> None:
|
||
|
|
op.add_column("videos", sa.Column("keywords", sa.Text(), nullable=True))
|
||
|
|
op.add_column("videos", sa.Column("search_terms", sa.Text(), nullable=True))
|
||
|
|
# Replace the title-only index with a generated weighted search_vector + its GIN index.
|
||
|
|
op.execute("DROP INDEX IF EXISTS ix_videos_title_fts")
|
||
|
|
op.execute(
|
||
|
|
f"ALTER TABLE videos ADD COLUMN search_vector tsvector "
|
||
|
|
f"GENERATED ALWAYS AS ({_SEARCH_VECTOR_EXPR}) STORED"
|
||
|
|
)
|
||
|
|
op.execute(
|
||
|
|
"CREATE INDEX ix_videos_search_vector ON videos USING gin (search_vector)"
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def downgrade() -> None:
|
||
|
|
op.execute("DROP INDEX IF EXISTS ix_videos_search_vector")
|
||
|
|
op.drop_column("videos", "search_vector")
|
||
|
|
op.drop_column("videos", "search_terms")
|
||
|
|
op.drop_column("videos", "keywords")
|
||
|
|
# Recreate the title-only FTS index from 0031.
|
||
|
|
op.execute(
|
||
|
|
"CREATE INDEX ix_videos_title_fts ON videos "
|
||
|
|
"USING gin (to_tsvector('public.unaccent_simple', coalesce(title, '')))"
|
||
|
|
)
|