alembic/versions/0001_initial.py (view raw)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 |
"""Initial schema."""
from __future__ import annotations
from collections.abc import Sequence
import sqlalchemy as sa
from alembic import op
revision: str = "0001_initial"
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None
def upgrade() -> None:
op.create_table(
"trackers",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("canonical_url", sa.String(length=512), nullable=False),
sa.Column("scheme", sa.String(length=16), nullable=False),
sa.Column("hostname", sa.String(length=255), nullable=False),
sa.Column("port", sa.Integer(), nullable=False),
sa.Column("path", sa.String(length=512), nullable=False),
sa.Column("current_status", sa.String(length=32), nullable=False),
sa.Column(
"first_seen_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.Column(
"last_seen_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.Column("last_checked_at", sa.DateTime(timezone=True), nullable=True),
sa.Column("next_check_at", sa.DateTime(timezone=True), nullable=True),
sa.Column("announced_interval_seconds", sa.Integer(), nullable=True),
sa.Column("last_latency_ms", sa.Float(), nullable=True),
sa.Column("supports_ipv4", sa.Boolean(), nullable=True),
sa.Column("supports_ipv6", sa.String(length=32), nullable=True),
sa.Column("terminal_cname", sa.String(length=255), nullable=True),
sa.Column("infrastructure_fingerprint", sa.String(length=128), nullable=True),
sa.Column("asn", sa.Integer(), nullable=True),
sa.Column("network_name", sa.String(length=255), nullable=True),
sa.Column("country_code", sa.String(length=8), nullable=True),
sa.Column("consecutive_failures", sa.Integer(), nullable=False),
sa.Column(
"created_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.Column(
"updated_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("canonical_url"),
)
op.create_index("ix_trackers_hostname", "trackers", ["hostname"])
op.create_index("ix_trackers_current_status", "trackers", ["current_status"])
op.create_index("ix_trackers_next_check_at", "trackers", ["next_check_at"])
op.create_index(
"ix_trackers_infrastructure_fingerprint", "trackers", ["infrastructure_fingerprint"]
)
op.create_table(
"tracker_sources",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("tracker_id", sa.Integer(), nullable=False),
sa.Column("source_name", sa.String(length=64), nullable=False),
sa.Column("source_url", sa.String(length=512), nullable=False),
sa.Column("is_best_at_source", sa.Boolean(), nullable=False),
sa.Column("is_stable_at_source", sa.Boolean(), nullable=False),
sa.Column("is_live_at_source", sa.Boolean(), nullable=False),
sa.Column(
"first_seen_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.Column(
"last_seen_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.ForeignKeyConstraint(["tracker_id"], ["trackers.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("tracker_id", "source_name", name="uq_tracker_source"),
)
op.create_index("ix_tracker_sources_tracker_id", "tracker_sources", ["tracker_id"])
op.create_table(
"probe_results",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("tracker_id", sa.Integer(), nullable=False),
sa.Column("checked_at", sa.DateTime(timezone=True), nullable=False),
sa.Column("status", sa.String(length=32), nullable=False),
sa.Column("latency_ms", sa.Float(), nullable=True),
sa.Column("ip_address", sa.String(length=64), nullable=True),
sa.Column("ip_family", sa.String(length=8), nullable=True),
sa.Column("response_valid", sa.Boolean(), nullable=False),
sa.Column("tracker_interval_seconds", sa.Integer(), nullable=True),
sa.Column("seeders", sa.Integer(), nullable=True),
sa.Column("leechers", sa.Integer(), nullable=True),
sa.Column("error_kind", sa.String(length=64), nullable=True),
sa.Column("error_detail", sa.String(length=512), nullable=True),
sa.ForeignKeyConstraint(["tracker_id"], ["trackers.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("ix_probe_results_tracker_id", "probe_results", ["tracker_id"])
op.create_index("ix_probe_results_checked_at", "probe_results", ["checked_at"])
op.create_table(
"dns_records",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("tracker_id", sa.Integer(), nullable=False),
sa.Column("record_type", sa.String(length=16), nullable=False),
sa.Column("value", sa.String(length=512), nullable=False),
sa.Column(
"first_seen_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.Column(
"last_seen_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.ForeignKeyConstraint(["tracker_id"], ["trackers.id"], ondelete="CASCADE"),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("tracker_id", "record_type", "value", name="uq_dns_record"),
)
op.create_index("ix_dns_records_tracker_id", "dns_records", ["tracker_id"])
op.create_table(
"source_import_runs",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("source_name", sa.String(length=64), nullable=False),
sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
sa.Column("finished_at", sa.DateTime(timezone=True), nullable=True),
sa.Column("status", sa.String(length=32), nullable=False),
sa.Column("received_count", sa.Integer(), nullable=False),
sa.Column("valid_count", sa.Integer(), nullable=False),
sa.Column("new_count", sa.Integer(), nullable=False),
sa.Column("unsupported_count", sa.Integer(), nullable=False),
sa.Column("error_message", sa.String(length=512), nullable=True),
sa.Column("etag", sa.String(length=255), nullable=True),
sa.Column("last_modified", sa.String(length=255), nullable=True),
sa.PrimaryKeyConstraint("id"),
)
op.create_index("ix_source_import_runs_source_name", "source_import_runs", ["source_name"])
op.create_table(
"collector_cache",
sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
sa.Column("source_name", sa.String(length=64), nullable=False),
sa.Column("feed_url", sa.String(length=512), nullable=False),
sa.Column("etag", sa.String(length=255), nullable=True),
sa.Column("last_modified", sa.String(length=255), nullable=True),
sa.Column(
"updated_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.PrimaryKeyConstraint("id"),
sa.UniqueConstraint("feed_url"),
)
op.create_table(
"app_state",
sa.Column("key", sa.String(length=64), nullable=False),
sa.Column("value", sa.Text(), nullable=False),
sa.Column(
"updated_at",
sa.DateTime(timezone=True),
server_default=sa.text("(CURRENT_TIMESTAMP)"),
nullable=False,
),
sa.PrimaryKeyConstraint("key"),
)
def downgrade() -> None:
op.drop_table("app_state")
op.drop_table("collector_cache")
op.drop_index("ix_source_import_runs_source_name", table_name="source_import_runs")
op.drop_table("source_import_runs")
op.drop_index("ix_dns_records_tracker_id", table_name="dns_records")
op.drop_table("dns_records")
op.drop_index("ix_probe_results_checked_at", table_name="probe_results")
op.drop_index("ix_probe_results_tracker_id", table_name="probe_results")
op.drop_table("probe_results")
op.drop_index("ix_tracker_sources_tracker_id", table_name="tracker_sources")
op.drop_table("tracker_sources")
op.drop_index("ix_trackers_infrastructure_fingerprint", table_name="trackers")
op.drop_index("ix_trackers_next_check_at", table_name="trackers")
op.drop_index("ix_trackers_current_status", table_name="trackers")
op.drop_index("ix_trackers_hostname", table_name="trackers")
op.drop_table("trackers")
|