49 lines
1.3 KiB
Python
49 lines
1.3 KiB
Python
|
"""Add tenant-local deduplication index on files.
|
||
|
|
|
||
|
|
Plan 6.6: Partial unique index on (tenant_id, content_hash)
|
||
|
|
WHERE content_hash IS NOT NULL AND deleted_at IS NULL.
|
||
|
|
|
||
|
|
Before creating the unique index, checks for existing duplicates.
|
||
|
|
|
||
|
|
Revision ID: 0098
|
||
|
|
Revises: 0097
|
||
|
|
"""
|
||
|
|
|
||
|
|
from __future__ import annotations
|
||
|
|
|
||
|
|
from alembic import op
|
||
|
|
import sqlalchemy as sa
|
||
|
|
|
||
|
|
revision = "0098"
|
||
|
|
down_revision = "0097"
|
||
|
|
branch_labels = None
|
||
|
|
depends_on = None
|
||
|
|
|
||
|
|
|
||
|
|
def upgrade() -> None:
|
||
|
|
# Check for duplicates before creating unique index
|
||
|
|
conn = op.get_bind()
|
||
|
|
duplicates = conn.execute(
|
||
|
|
sa.text(
|
||
|
|
"SELECT tenant_id, content_hash, count(*) FROM files "
|
||
|
|
"WHERE content_hash IS NOT NULL AND deleted_at IS NULL "
|
||
|
|
"GROUP BY tenant_id, content_hash HAVING count(*) > 1"
|
||
|
|
)
|
||
|
|
).fetchall()
|
||
|
|
|
||
|
|
if duplicates:
|
||
|
|
raise RuntimeError(
|
||
|
|
f"Cannot create unique index: {len(duplicates)} duplicate (tenant_id, content_hash) pairs found. "
|
||
|
|
"Data cleanup required before migration."
|
||
|
|
)
|
||
|
|
|
||
|
|
op.execute(
|
||
|
|
"CREATE UNIQUE INDEX IF NOT EXISTS uq_files_tenant_content_hash "
|
||
|
|
"ON files (tenant_id, content_hash) "
|
||
|
|
"WHERE content_hash IS NOT NULL AND deleted_at IS NULL"
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def downgrade() -> None:
|
||
|
|
op.execute("DROP INDEX IF EXISTS uq_files_tenant_content_hash")
|