krz/hutch-stats

  1"""initial schema"""
  2
  3from __future__ import annotations
  4
  5from alembic import op
  6import sqlalchemy as sa
  7from sqlalchemy import inspect
  8
  9from srht_contrib.config import Settings
 10
 11
 12revision = "20260409_0001"
 13down_revision = None
 14branch_labels = None
 15depends_on = None
 16
 17
 18def _table_names() -> set[str]:
 19    return set(inspect(op.get_bind()).get_table_names())
 20
 21
 22def _index_names(table_name: str) -> set[str]:
 23    return {index["name"] for index in inspect(op.get_bind()).get_indexes(table_name)}
 24
 25
 26def _tracked_repositories_needs_upgrade() -> bool:
 27    inspector = inspect(op.get_bind())
 28    columns = {column["name"]: column for column in inspector.get_columns("tracked_repositories")}
 29    actor_column = columns.get("actor")
 30    if actor_column is None or actor_column.get("nullable", True):
 31        return True
 32
 33    unique_constraints = {constraint["name"] for constraint in inspector.get_unique_constraints("tracked_repositories")}
 34    return "uq_tracked_repository_service_actor_name" not in unique_constraints
 35
 36
 37def _upgrade_tracked_repositories() -> None:
 38    default_actor = Settings().default_actor
 39    op.execute(
 40        sa.text(
 41            """
 42            CREATE TABLE tracked_repositories__alembic_new (
 43                id INTEGER NOT NULL PRIMARY KEY,
 44                service VARCHAR(32) NOT NULL,
 45                repo_name VARCHAR(255) NOT NULL,
 46                actor VARCHAR(255) NOT NULL,
 47                CONSTRAINT uq_tracked_repository_service_actor_name UNIQUE (service, actor, repo_name)
 48            )
 49            """
 50        )
 51    )
 52    op.execute(
 53        sa.text(
 54            """
 55            INSERT INTO tracked_repositories__alembic_new (id, service, repo_name, actor)
 56            SELECT id, service, repo_name, COALESCE(actor, :default_actor)
 57            FROM tracked_repositories
 58            """
 59        ).bindparams(default_actor=default_actor)
 60    )
 61    op.execute(sa.text("DROP TABLE tracked_repositories"))
 62    op.execute(sa.text("ALTER TABLE tracked_repositories__alembic_new RENAME TO tracked_repositories"))
 63
 64
 65def upgrade() -> None:
 66    table_names = _table_names()
 67
 68    if "contribution_events" not in table_names:
 69        op.create_table(
 70            "contribution_events",
 71            sa.Column("id", sa.Integer(), primary_key=True),
 72            sa.Column("service", sa.String(length=32), nullable=False),
 73            sa.Column("event_type", sa.String(length=64), nullable=False),
 74            sa.Column("actor", sa.String(length=255), nullable=False),
 75            sa.Column("repo_name", sa.String(length=255), nullable=True),
 76            sa.Column("resource_id", sa.String(length=255), nullable=False),
 77            sa.Column("external_uid", sa.String(length=255), nullable=False),
 78            sa.Column("occurred_at", sa.DateTime(timezone=True), nullable=False),
 79            sa.Column("weight", sa.Float(), nullable=False),
 80            sa.Column("raw_payload_json", sa.JSON(), nullable=True),
 81            sa.UniqueConstraint("service", "external_uid", name="uq_contribution_event_service_uid"),
 82        )
 83
 84    contribution_event_indexes = _index_names("contribution_events")
 85    if "ix_contribution_events_actor_occurred_at" not in contribution_event_indexes:
 86        op.create_index(
 87            "ix_contribution_events_actor_occurred_at",
 88            "contribution_events",
 89            ["actor", "occurred_at"],
 90            unique=False,
 91        )
 92    if "ix_contribution_events_service_occurred_at" not in contribution_event_indexes:
 93        op.create_index(
 94            "ix_contribution_events_service_occurred_at",
 95            "contribution_events",
 96            ["service", "occurred_at"],
 97            unique=False,
 98        )
 99
100    if "sync_states" not in table_names:
101        op.create_table(
102            "sync_states",
103            sa.Column("id", sa.Integer(), primary_key=True),
104            sa.Column("service", sa.String(length=32), nullable=False),
105            sa.Column("actor", sa.String(length=255), nullable=False),
106            sa.Column("cursor_value", sa.Text(), nullable=True),
107            sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False),
108            sa.UniqueConstraint("service", "actor", name="uq_sync_state_service_actor"),
109        )
110
111    if "tracked_repositories" not in table_names:
112        op.create_table(
113            "tracked_repositories",
114            sa.Column("id", sa.Integer(), primary_key=True),
115            sa.Column("service", sa.String(length=32), nullable=False),
116            sa.Column("repo_name", sa.String(length=255), nullable=False),
117            sa.Column("actor", sa.String(length=255), nullable=False),
118            sa.UniqueConstraint("service", "actor", "repo_name", name="uq_tracked_repository_service_actor_name"),
119        )
120    elif _tracked_repositories_needs_upgrade():
121        _upgrade_tracked_repositories()
122
123    if "actor_aliases" not in table_names:
124        op.create_table(
125            "actor_aliases",
126            sa.Column("id", sa.Integer(), primary_key=True),
127            sa.Column("canonical_actor", sa.String(length=255), nullable=False),
128            sa.Column("alias", sa.String(length=255), nullable=False),
129            sa.UniqueConstraint("alias", name="uq_actor_alias_alias"),
130        )
131
132
133def downgrade() -> None:
134    table_names = _table_names()
135
136    if "actor_aliases" in table_names:
137        op.drop_table("actor_aliases")
138    if "tracked_repositories" in table_names:
139        op.drop_table("tracked_repositories")
140    if "sync_states" in table_names:
141        op.drop_table("sync_states")
142    if "contribution_events" in table_names:
143        contribution_event_indexes = _index_names("contribution_events")
144        if "ix_contribution_events_service_occurred_at" in contribution_event_indexes:
145            op.drop_index("ix_contribution_events_service_occurred_at", table_name="contribution_events")
146        if "ix_contribution_events_actor_occurred_at" in contribution_event_indexes:
147            op.drop_index("ix_contribution_events_actor_occurred_at", table_name="contribution_events")
148        op.drop_table("contribution_events")