79 lines
3.0 KiB
Python
79 lines
3.0 KiB
Python
"""Add mobile_local_id to inspections and issues for mobile sync idempotency.
|
|
|
|
Phase B — iPad offline inspection submission.
|
|
|
|
Each inspection or issue submitted from the iPad app carries a UUID generated
|
|
on the device (mobile_local_id). The server checks this field before creating
|
|
a new record so that network retries never produce duplicate rows.
|
|
|
|
Revision ID: phase_b_mobile_local_id
|
|
Revises: phase12_performance_indexes
|
|
"""
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
revision = 'phase_b_mobile_local_id'
|
|
down_revision = 'phase12_performance_indexes'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def _column_exists(conn, table, column):
|
|
result = conn.execute(sa.text(
|
|
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS "
|
|
"WHERE TABLE_SCHEMA = DATABASE() "
|
|
"AND TABLE_NAME = :table AND COLUMN_NAME = :column"
|
|
), {"table": table, "column": column})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def _index_exists(conn, table, index):
|
|
result = conn.execute(sa.text(
|
|
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS "
|
|
"WHERE TABLE_SCHEMA = DATABASE() "
|
|
"AND TABLE_NAME = :table AND INDEX_NAME = :index"
|
|
), {"table": table, "index": index})
|
|
return result.scalar() > 0
|
|
|
|
|
|
def upgrade():
|
|
conn = op.get_bind()
|
|
|
|
# ── inspections.mobile_local_id ───────────────────────────────────────
|
|
if not _column_exists(conn, 'inspections', 'mobile_local_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE inspections ADD COLUMN mobile_local_id VARCHAR(64) NULL"
|
|
))
|
|
|
|
if not _index_exists(conn, 'inspections', 'idx_inspections_mobile_local_id'):
|
|
op.execute(sa.text(
|
|
"CREATE INDEX idx_inspections_mobile_local_id ON inspections(mobile_local_id)"
|
|
))
|
|
|
|
# ── issues.mobile_local_id ────────────────────────────────────────────
|
|
if not _column_exists(conn, 'issues', 'mobile_local_id'):
|
|
op.execute(sa.text(
|
|
"ALTER TABLE issues ADD COLUMN mobile_local_id VARCHAR(64) NULL"
|
|
))
|
|
|
|
if not _index_exists(conn, 'issues', 'idx_issues_mobile_local_id'):
|
|
op.execute(sa.text(
|
|
"CREATE INDEX idx_issues_mobile_local_id ON issues(mobile_local_id)"
|
|
))
|
|
|
|
|
|
def downgrade():
|
|
conn = op.get_bind()
|
|
|
|
if _index_exists(conn, 'inspections', 'idx_inspections_mobile_local_id'):
|
|
op.execute(sa.text("DROP INDEX idx_inspections_mobile_local_id ON inspections"))
|
|
|
|
if _column_exists(conn, 'inspections', 'mobile_local_id'):
|
|
op.execute(sa.text("ALTER TABLE inspections DROP COLUMN mobile_local_id"))
|
|
|
|
if _index_exists(conn, 'issues', 'idx_issues_mobile_local_id'):
|
|
op.execute(sa.text("DROP INDEX idx_issues_mobile_local_id ON issues"))
|
|
|
|
if _column_exists(conn, 'issues', 'mobile_local_id'):
|
|
op.execute(sa.text("ALTER TABLE issues DROP COLUMN mobile_local_id")) |