diff options
Diffstat (limited to 'alembic')
| -rw-r--r-- | alembic/env.py | 58 | ||||
| -rw-r--r-- | alembic/script.py.mako | 18 | ||||
| -rw-r--r-- | alembic/versions/20260409_0001_initial_schema.py | 148 |
3 files changed, 224 insertions, 0 deletions
diff --git a/alembic/env.py b/alembic/env.py new file mode 100644 index 0000000..6fcbeb0 --- /dev/null +++ b/alembic/env.py @@ -0,0 +1,58 @@ +from __future__ import annotations + +from logging.config import fileConfig + +from alembic import context +from sqlalchemy import engine_from_config, pool + +from srht_contrib.config import Settings +from srht_contrib.db import Base +from srht_contrib.models import ActorAlias, ContributionEvent, SyncState, TrackedRepository # noqa: F401 + +config = context.config + +if config.config_file_name is not None: + fileConfig(config.config_file_name) + +target_metadata = Base.metadata + + +def get_database_url() -> str: + configured_url = config.get_main_option("sqlalchemy.url") + if configured_url: + return configured_url + return Settings().database_url + + +def run_migrations_offline() -> None: + context.configure( + url=get_database_url(), + target_metadata=target_metadata, + literal_binds=True, + dialect_opts={"paramstyle": "named"}, + ) + + with context.begin_transaction(): + context.run_migrations() + + +def run_migrations_online() -> None: + section = config.get_section(config.config_ini_section) or {} + section["sqlalchemy.url"] = get_database_url() + connectable = engine_from_config( + section, + prefix="sqlalchemy.", + poolclass=pool.NullPool, + ) + + with connectable.connect() as connection: + context.configure(connection=connection, target_metadata=target_metadata) + + with context.begin_transaction(): + context.run_migrations() + + +if context.is_offline_mode(): + run_migrations_offline() +else: + run_migrations_online() diff --git a/alembic/script.py.mako b/alembic/script.py.mako new file mode 100644 index 0000000..40c6b2b --- /dev/null +++ b/alembic/script.py.mako @@ -0,0 +1,18 @@ +"""${message}""" + +revision = ${repr(up_revision)} +down_revision = ${repr(down_revision)} +branch_labels = ${repr(branch_labels)} +depends_on = ${repr(depends_on)} + +from alembic import op +import sqlalchemy as sa +${imports if imports else ""} + + +def upgrade() -> None: + ${upgrades if upgrades else "pass"} + + +def downgrade() -> None: + ${downgrades if downgrades else "pass"} diff --git a/alembic/versions/20260409_0001_initial_schema.py b/alembic/versions/20260409_0001_initial_schema.py new file mode 100644 index 0000000..dffbe61 --- /dev/null +++ b/alembic/versions/20260409_0001_initial_schema.py @@ -0,0 +1,148 @@ +"""initial schema""" + +from __future__ import annotations + +from alembic import op +import sqlalchemy as sa +from sqlalchemy import inspect + +from srht_contrib.config import Settings + + +revision = "20260409_0001" +down_revision = None +branch_labels = None +depends_on = None + + +def _table_names() -> set[str]: + return set(inspect(op.get_bind()).get_table_names()) + + +def _index_names(table_name: str) -> set[str]: + return {index["name"] for index in inspect(op.get_bind()).get_indexes(table_name)} + + +def _tracked_repositories_needs_upgrade() -> bool: + inspector = inspect(op.get_bind()) + columns = {column["name"]: column for column in inspector.get_columns("tracked_repositories")} + actor_column = columns.get("actor") + if actor_column is None or actor_column.get("nullable", True): + return True + + unique_constraints = {constraint["name"] for constraint in inspector.get_unique_constraints("tracked_repositories")} + return "uq_tracked_repository_service_actor_name" not in unique_constraints + + +def _upgrade_tracked_repositories() -> None: + default_actor = Settings().default_actor + op.execute( + sa.text( + """ + CREATE TABLE tracked_repositories__alembic_new ( + id INTEGER NOT NULL PRIMARY KEY, + service VARCHAR(32) NOT NULL, + repo_name VARCHAR(255) NOT NULL, + actor VARCHAR(255) NOT NULL, + CONSTRAINT uq_tracked_repository_service_actor_name UNIQUE (service, actor, repo_name) + ) + """ + ) + ) + op.execute( + sa.text( + """ + INSERT INTO tracked_repositories__alembic_new (id, service, repo_name, actor) + SELECT id, service, repo_name, COALESCE(actor, :default_actor) + FROM tracked_repositories + """ + ).bindparams(default_actor=default_actor) + ) + op.execute(sa.text("DROP TABLE tracked_repositories")) + op.execute(sa.text("ALTER TABLE tracked_repositories__alembic_new RENAME TO tracked_repositories")) + + +def upgrade() -> None: + table_names = _table_names() + + if "contribution_events" not in table_names: + op.create_table( + "contribution_events", + sa.Column("id", sa.Integer(), primary_key=True), + sa.Column("service", sa.String(length=32), nullable=False), + sa.Column("event_type", sa.String(length=64), nullable=False), + sa.Column("actor", sa.String(length=255), nullable=False), + sa.Column("repo_name", sa.String(length=255), nullable=True), + sa.Column("resource_id", sa.String(length=255), nullable=False), + sa.Column("external_uid", sa.String(length=255), nullable=False), + sa.Column("occurred_at", sa.DateTime(timezone=True), nullable=False), + sa.Column("weight", sa.Float(), nullable=False), + sa.Column("raw_payload_json", sa.JSON(), nullable=True), + sa.UniqueConstraint("service", "external_uid", name="uq_contribution_event_service_uid"), + ) + + contribution_event_indexes = _index_names("contribution_events") + if "ix_contribution_events_actor_occurred_at" not in contribution_event_indexes: + op.create_index( + "ix_contribution_events_actor_occurred_at", + "contribution_events", + ["actor", "occurred_at"], + unique=False, + ) + if "ix_contribution_events_service_occurred_at" not in contribution_event_indexes: + op.create_index( + "ix_contribution_events_service_occurred_at", + "contribution_events", + ["service", "occurred_at"], + unique=False, + ) + + if "sync_states" not in table_names: + op.create_table( + "sync_states", + sa.Column("id", sa.Integer(), primary_key=True), + sa.Column("service", sa.String(length=32), nullable=False), + sa.Column("actor", sa.String(length=255), nullable=False), + sa.Column("cursor_value", sa.Text(), nullable=True), + sa.Column("updated_at", sa.DateTime(timezone=True), nullable=False), + sa.UniqueConstraint("service", "actor", name="uq_sync_state_service_actor"), + ) + + if "tracked_repositories" not in table_names: + op.create_table( + "tracked_repositories", + sa.Column("id", sa.Integer(), primary_key=True), + sa.Column("service", sa.String(length=32), nullable=False), + sa.Column("repo_name", sa.String(length=255), nullable=False), + sa.Column("actor", sa.String(length=255), nullable=False), + sa.UniqueConstraint("service", "actor", "repo_name", name="uq_tracked_repository_service_actor_name"), + ) + elif _tracked_repositories_needs_upgrade(): + _upgrade_tracked_repositories() + + if "actor_aliases" not in table_names: + op.create_table( + "actor_aliases", + sa.Column("id", sa.Integer(), primary_key=True), + sa.Column("canonical_actor", sa.String(length=255), nullable=False), + sa.Column("alias", sa.String(length=255), nullable=False), + sa.UniqueConstraint("alias", name="uq_actor_alias_alias"), + ) + + +def downgrade() -> None: + table_names = _table_names() + + if "actor_aliases" in table_names: + op.drop_table("actor_aliases") + if "tracked_repositories" in table_names: + op.drop_table("tracked_repositories") + if "sync_states" in table_names: + op.drop_table("sync_states") + if "contribution_events" in table_names: + contribution_event_indexes = _index_names("contribution_events") + if "ix_contribution_events_service_occurred_at" in contribution_event_indexes: + op.drop_index("ix_contribution_events_service_occurred_at", table_name="contribution_events") + if "ix_contribution_events_actor_occurred_at" in contribution_event_indexes: + op.drop_index("ix_contribution_events_actor_occurred_at", table_name="contribution_events") + op.drop_table("contribution_events") |