peter
b1832afd18
Mechanical, whole-tree `ruff format` pass — no behaviour change. Recorded in .git-blame-ignore-revs (next commit) so git blame skips it.
55 lines
2.7 KiB
Python
55 lines
2.7 KiB
Python
"""drop 10 redundant single-column FK indexes (R12 S5)
|
|
|
|
Each of these columns already leads a composite UNIQUE constraint, whose btree covers leftmost-prefix
|
|
lookups on that column (and the ON DELETE CASCADE reverse lookups too) — so the extra single-column
|
|
`index=True` was pure write-amplification. Dropping them costs nothing on reads and saves an index
|
|
write per insert/update. Fast (index-only drop, no table rewrite). Reversible (downgrade recreates).
|
|
|
|
Redundant index -> the composite that already covers it:
|
|
ix_subscriptions_user_id <- uq_user_channel (user_id, channel_id)
|
|
ix_video_states_user_id <- uq_user_video (user_id, video_id)
|
|
ix_search_finds_user_id <- uq_user_search_video (user_id, video_id)
|
|
ix_explored_channels_user_id <- uq_user_explored_channel (user_id, channel_id)
|
|
ix_blocked_channels_user_id <- uq_user_blocked_channel (user_id, channel_id)
|
|
ix_saved_views_user_id <- uq_user_saved_view (user_id, name)
|
|
ix_playlist_items_playlist_id <- uq_playlist_video (playlist_id, video_id)
|
|
ix_download_shares_job_id <- uq_download_share (job_id, to_user_id)
|
|
ix_plex_states_user_id <- uq_plex_user_item (user_id, item_id)
|
|
ix_plex_playlist_items_playlist_id <- uq_plex_playlist_item (playlist_id, item_id)
|
|
"""
|
|
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
|
|
revision: str = "0062_drop_redundant_idx"
|
|
down_revision: Union[str, None] = "0061_dl_job_error_code"
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
# (index_name, table_name, column)
|
|
_REDUNDANT: list[tuple[str, str, str]] = [
|
|
("ix_subscriptions_user_id", "subscriptions", "user_id"),
|
|
("ix_video_states_user_id", "video_states", "user_id"),
|
|
("ix_search_finds_user_id", "search_finds", "user_id"),
|
|
("ix_explored_channels_user_id", "explored_channels", "user_id"),
|
|
("ix_blocked_channels_user_id", "blocked_channels", "user_id"),
|
|
("ix_saved_views_user_id", "saved_views", "user_id"),
|
|
("ix_playlist_items_playlist_id", "playlist_items", "playlist_id"),
|
|
("ix_download_shares_job_id", "download_shares", "job_id"),
|
|
("ix_plex_states_user_id", "plex_states", "user_id"),
|
|
("ix_plex_playlist_items_playlist_id", "plex_playlist_items", "playlist_id"),
|
|
]
|
|
|
|
|
|
def upgrade() -> None:
|
|
for idx, _table, _col in _REDUNDANT:
|
|
# IF EXISTS: a DB whose schema was stamped from a create_all (tests) won't have these once
|
|
# the models drop index=True, so a plain drop_index would error there.
|
|
op.execute(f"DROP INDEX IF EXISTS {idx}")
|
|
|
|
|
|
def downgrade() -> None:
|
|
for idx, table, col in _REDUNDANT:
|
|
op.create_index(idx, table, [col])
|