613 lines
37 KiB
Python
613 lines
37 KiB
Python
from __future__ import annotations
|
|
|
|
from datetime import datetime, timezone
|
|
from typing import Optional
|
|
|
|
from sqlalchemy import BigInteger, Boolean, DateTime, Float, ForeignKey, Integer, String, Text, UniqueConstraint
|
|
from sqlalchemy.orm import Mapped, mapped_column, relationship
|
|
|
|
from app.db import Base
|
|
|
|
|
|
def now_utc() -> datetime:
|
|
return datetime.now(timezone.utc)
|
|
|
|
|
|
class Source(Base):
|
|
__tablename__ = "sources"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
catalog_entry_id: Mapped[Optional[int]] = mapped_column(ForeignKey("source_catalog_entries.id"), nullable=True, index=True)
|
|
name: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
kind: Mapped[str] = mapped_column(String(64), nullable=False) # gtfs, osm_geojson, osm_pbf, osm_diff
|
|
url: Mapped[str] = mapped_column(Text, nullable=False)
|
|
country: Mapped[Optional[str]] = mapped_column(String(8), nullable=True)
|
|
license: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
|
|
priority: Mapped[Optional[str]] = mapped_column(String(16), nullable=True, index=True)
|
|
mode_scope: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
source_basis: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
|
|
status: Mapped[str] = mapped_column(String(64), default="new", nullable=False)
|
|
last_error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
last_run_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
catalog_entry: Mapped[Optional["SourceCatalogEntry"]] = relationship()
|
|
datasets: Mapped[list["Dataset"]] = relationship(back_populates="source", cascade="all, delete-orphan")
|
|
update_checks: Mapped[list["SourceUpdateCheck"]] = relationship(back_populates="source", cascade="all, delete-orphan")
|
|
|
|
|
|
class SourceCatalogEntry(Base):
|
|
__tablename__ = "source_catalog_entries"
|
|
__table_args__ = (UniqueConstraint("catalog_key", name="uq_source_catalog_entry_key"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
catalog_key: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
geography: Mapped[Optional[str]] = mapped_column(String(128), nullable=True, index=True)
|
|
country_code: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
mode_scope: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
source_name: Mapped[str] = mapped_column(Text, nullable=False)
|
|
source_category: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
formats_apis: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
availability: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
coverage_notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
geometry_notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
disruptions_closures: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
operator_list_use: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
access_license_notes: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
priority: Mapped[Optional[str]] = mapped_column(String(32), nullable=True, index=True)
|
|
source_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
evidence_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
next_pipeline_action: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
status: Mapped[str] = mapped_column(String(64), default="backlog", nullable=False, index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
|
|
class Dataset(Base):
|
|
__tablename__ = "datasets"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
source_id: Mapped[int] = mapped_column(ForeignKey("sources.id"), nullable=False, index=True)
|
|
kind: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
local_path: Mapped[str] = mapped_column(Text, nullable=False)
|
|
sha256: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
|
|
status: Mapped[str] = mapped_column(String(64), default="imported", nullable=False)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
source: Mapped[Source] = relationship(back_populates="datasets")
|
|
|
|
|
|
class SourceUpdateCheck(Base):
|
|
__tablename__ = "source_update_checks"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
source_id: Mapped[int] = mapped_column(ForeignKey("sources.id"), nullable=False, index=True)
|
|
checked_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="checked", index=True)
|
|
update_available: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
|
|
reason: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
remote_url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
etag: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
last_modified: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
content_length: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
|
|
content_type: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
local_mtime: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
|
|
local_size: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
|
|
local_sha256: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|
|
active_dataset_id: Mapped[Optional[int]] = mapped_column(ForeignKey("datasets.id"), nullable=True, index=True)
|
|
active_dataset_sha256: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
|
|
source: Mapped[Source] = relationship(back_populates="update_checks")
|
|
active_dataset: Mapped[Optional[Dataset]] = relationship()
|
|
|
|
|
|
class OsmDiffState(Base):
|
|
__tablename__ = "osm_diff_states"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
source_id: Mapped[int] = mapped_column(ForeignKey("sources.id"), nullable=False, index=True)
|
|
raw_dataset_id: Mapped[Optional[int]] = mapped_column(ForeignKey("datasets.id"), nullable=True, index=True)
|
|
updates_url: Mapped[str] = mapped_column(Text, nullable=False)
|
|
sequence_number: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
|
|
timestamp: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="active", index=True)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
source: Mapped[Source] = relationship()
|
|
raw_dataset: Mapped[Optional[Dataset]] = relationship()
|
|
|
|
|
|
class Job(Base):
|
|
__tablename__ = "jobs"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
kind: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="queued", index=True)
|
|
description: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
progress_current: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
|
|
progress_total: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
|
|
priority: Mapped[int] = mapped_column(Integer, nullable=False, default=0, index=True)
|
|
requested_action: Mapped[Optional[str]] = mapped_column(String(32), nullable=True, index=True)
|
|
lease_owner: Mapped[Optional[str]] = mapped_column(String(255), nullable=True, index=True)
|
|
lease_expires_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
paused_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
|
|
result_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
dismissed_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True, index=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
started_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
|
|
|
|
events: Mapped[list["JobEvent"]] = relationship(back_populates="job", cascade="all, delete-orphan")
|
|
|
|
|
|
class JobEvent(Base):
|
|
__tablename__ = "job_events"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
job_id: Mapped[int] = mapped_column(ForeignKey("jobs.id"), nullable=False, index=True)
|
|
level: Mapped[str] = mapped_column(String(32), nullable=False, default="info", index=True)
|
|
event_type: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
|
|
message: Mapped[str] = mapped_column(Text, nullable=False)
|
|
progress_current: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
|
|
progress_total: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
|
|
job: Mapped[Job] = relationship(back_populates="events")
|
|
|
|
|
|
class PipelineRun(Base):
|
|
__tablename__ = "pipeline_runs"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
stage: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
|
|
version: Mapped[str] = mapped_column(String(128), nullable=False, index=True)
|
|
dependency_hash: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="running", index=True)
|
|
source_id: Mapped[Optional[int]] = mapped_column(ForeignKey("sources.id"), nullable=True, index=True)
|
|
dataset_id: Mapped[Optional[int]] = mapped_column(ForeignKey("datasets.id"), nullable=True, index=True)
|
|
job_id: Mapped[Optional[int]] = mapped_column(ForeignKey("jobs.id"), nullable=True, index=True)
|
|
input_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
output_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
error: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
finished_at: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
|
|
|
|
source: Mapped[Optional[Source]] = relationship()
|
|
dataset: Mapped[Optional[Dataset]] = relationship()
|
|
job: Mapped[Optional[Job]] = relationship()
|
|
|
|
|
|
class GtfsAgency(Base):
|
|
__tablename__ = "gtfs_agencies"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "agency_id", name="uq_gtfs_agency_dataset_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
agency_id: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
name: Mapped[str] = mapped_column(Text, nullable=False)
|
|
url: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
timezone: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
|
|
|
|
|
|
class GtfsStop(Base):
|
|
__tablename__ = "gtfs_stops"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "stop_id", name="uq_gtfs_stop_dataset_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
stop_id: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
parent_station: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
|
|
|
|
|
|
class GtfsRoute(Base):
|
|
__tablename__ = "gtfs_routes"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "route_id", name="uq_gtfs_route_dataset_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
route_id: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
agency_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
|
|
short_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
long_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
route_type: Mapped[Optional[int]] = mapped_column(Integer, nullable=True)
|
|
mode: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
route_scope: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
operator_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
geometry_geojson: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
min_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
min_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
route_key: Mapped[Optional[str]] = mapped_column(Text, nullable=True, index=True)
|
|
operator_key: Mapped[Optional[str]] = mapped_column(Text, nullable=True, index=True)
|
|
|
|
|
|
class GtfsTrip(Base):
|
|
__tablename__ = "gtfs_trips"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "trip_id", name="uq_gtfs_trip_dataset_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
route_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
trip_id: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
service_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
|
|
shape_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
|
|
|
|
|
|
class GtfsCalendar(Base):
|
|
__tablename__ = "gtfs_calendars"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "service_id", name="uq_gtfs_calendar_dataset_service"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
service_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
monday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
tuesday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
wednesday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
thursday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
friday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
saturday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
sunday: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
|
|
start_date: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
|
|
end_date: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
|
|
|
|
|
|
class GtfsCalendarDate(Base):
|
|
__tablename__ = "gtfs_calendar_dates"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "service_id", "date", name="uq_gtfs_calendar_date_dataset_service_date"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
service_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
date: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
|
|
exception_type: Mapped[int] = mapped_column(Integer, nullable=False)
|
|
|
|
|
|
class GtfsShape(Base):
|
|
__tablename__ = "gtfs_shapes"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "shape_id", name="uq_gtfs_shape_dataset_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
shape_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
geometry_geojson: Mapped[str] = mapped_column(Text, nullable=False)
|
|
min_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
min_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
|
|
|
|
class GtfsStopTime(Base):
|
|
__tablename__ = "gtfs_stop_times"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
trip_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
stop_id: Mapped[str] = mapped_column(String(255), nullable=False)
|
|
stop_sequence: Mapped[int] = mapped_column(Integer, nullable=False)
|
|
arrival_time: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
|
|
departure_time: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
|
|
arrival_seconds: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
|
|
departure_seconds: Mapped[Optional[int]] = mapped_column(Integer, nullable=True, index=True)
|
|
|
|
|
|
class CanonicalStop(Base):
|
|
__tablename__ = "canonical_stops"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
stop_key: Mapped[str] = mapped_column(String(255), nullable=False, unique=True, index=True)
|
|
name: Mapped[str] = mapped_column(Text, nullable=False)
|
|
normalized_name: Mapped[str] = mapped_column(Text, nullable=False, index=True)
|
|
lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
mode: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
|
|
class CanonicalStopLink(Base):
|
|
__tablename__ = "canonical_stop_links"
|
|
__table_args__ = (
|
|
UniqueConstraint("object_type", "dataset_id", "object_id", name="uq_canonical_stop_link_object"),
|
|
)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
canonical_stop_id: Mapped[int] = mapped_column(ForeignKey("canonical_stops.id"), nullable=False, index=True)
|
|
layer: Mapped[str] = mapped_column(String(64), nullable=False, index=True) # timetable, visual
|
|
object_type: Mapped[str] = mapped_column(String(64), nullable=False, index=True) # gtfs_stop, osm_feature
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
object_id: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
|
|
external_id: Mapped[str] = mapped_column(Text, nullable=False)
|
|
role: Mapped[Optional[str]] = mapped_column(String(64), nullable=True)
|
|
confidence: Mapped[float] = mapped_column(Float, nullable=False, default=1.0)
|
|
distance_m: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
|
|
canonical_stop: Mapped[CanonicalStop] = relationship()
|
|
|
|
|
|
class RoutePattern(Base):
|
|
__tablename__ = "route_patterns"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
pattern_key: Mapped[str] = mapped_column(String(255), nullable=False, unique=True, index=True)
|
|
route_ref: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
route_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
mode: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
route_scope: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
operator_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
source_kind: Mapped[str] = mapped_column(String(64), nullable=False, index=True) # osm, gtfs_proposed
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="active", index=True)
|
|
osm_feature_id: Mapped[Optional[int]] = mapped_column(ForeignKey("osm_features.id"), nullable=True, index=True)
|
|
gtfs_route_id: Mapped[Optional[int]] = mapped_column(ForeignKey("gtfs_routes.id"), nullable=True, index=True)
|
|
gtfs_shape_id: Mapped[Optional[str]] = mapped_column(String(255), nullable=True, index=True)
|
|
geometry_geojson: Mapped[str] = mapped_column(Text, nullable=False)
|
|
min_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
min_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
confidence: Mapped[float] = mapped_column(Float, nullable=False, default=1.0)
|
|
metadata_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
osm_feature: Mapped[Optional["OsmFeature"]] = relationship()
|
|
gtfs_route: Mapped[Optional[GtfsRoute]] = relationship()
|
|
|
|
|
|
class RoutePatternStop(Base):
|
|
__tablename__ = "route_pattern_stops"
|
|
__table_args__ = (UniqueConstraint("route_pattern_id", "sequence", name="uq_route_pattern_stop_sequence"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
route_pattern_id: Mapped[int] = mapped_column(ForeignKey("route_patterns.id"), nullable=False, index=True)
|
|
canonical_stop_id: Mapped[int] = mapped_column(ForeignKey("canonical_stops.id"), nullable=False, index=True)
|
|
sequence: Mapped[int] = mapped_column(Integer, nullable=False)
|
|
distance_along: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
source_kind: Mapped[str] = mapped_column(String(64), nullable=False, default="timetable")
|
|
confidence: Mapped[float] = mapped_column(Float, nullable=False, default=1.0)
|
|
|
|
route_pattern: Mapped[RoutePattern] = relationship()
|
|
canonical_stop: Mapped[CanonicalStop] = relationship()
|
|
|
|
|
|
class GtfsRoutePatternLink(Base):
|
|
__tablename__ = "gtfs_route_pattern_links"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "route_id", "shape_id", name="uq_gtfs_route_pattern_shape"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
gtfs_route_id: Mapped[int] = mapped_column(ForeignKey("gtfs_routes.id"), nullable=False, index=True)
|
|
route_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
shape_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
route_pattern_id: Mapped[int] = mapped_column(ForeignKey("route_patterns.id"), nullable=False, index=True)
|
|
confidence: Mapped[float] = mapped_column(Float, nullable=False, default=0)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="linked", index=True)
|
|
source_kind: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
reasons_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
|
|
gtfs_route: Mapped[GtfsRoute] = relationship()
|
|
route_pattern: Mapped[RoutePattern] = relationship()
|
|
|
|
|
|
class GtfsTripRoutePatternLink(Base):
|
|
__tablename__ = "gtfs_trip_route_pattern_links"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "trip_id", name="uq_gtfs_trip_route_pattern"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
trip_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
route_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
shape_id: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
|
|
route_pattern_id: Mapped[int] = mapped_column(ForeignKey("route_patterns.id"), nullable=False, index=True)
|
|
source_kind: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
confidence: Mapped[float] = mapped_column(Float, nullable=False, default=0)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="linked", index=True)
|
|
|
|
route_pattern: Mapped[RoutePattern] = relationship()
|
|
|
|
|
|
class OsmFeature(Base):
|
|
__tablename__ = "osm_features"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "osm_type", "osm_id", name="uq_osm_feature_dataset_type_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
osm_type: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
osm_id: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
kind: Mapped[str] = mapped_column(String(64), nullable=False, index=True) # route, stop, terminal, station, infra
|
|
mode: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
route_scope: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
ref: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
operator: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
network: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
geometry_geojson: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
min_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
min_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
tags_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
route_key: Mapped[Optional[str]] = mapped_column(Text, nullable=True, index=True)
|
|
operator_key: Mapped[Optional[str]] = mapped_column(Text, nullable=True, index=True)
|
|
|
|
|
|
class OsmAddress(Base):
|
|
__tablename__ = "osm_addresses"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "osm_type", "osm_id", name="uq_osm_address_dataset_type_id"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
osm_type: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
osm_id: Mapped[str] = mapped_column(String(64), nullable=False)
|
|
housenumber: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
street: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
place: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
postcode: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
city: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
country: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
unit: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
display_name: Mapped[str] = mapped_column(Text, nullable=False)
|
|
search_text: Mapped[str] = mapped_column(Text, nullable=False)
|
|
lat: Mapped[float] = mapped_column(Float, nullable=False)
|
|
lon: Mapped[float] = mapped_column(Float, nullable=False)
|
|
min_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
min_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lon: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
max_lat: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
geometry_geojson: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
tags_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
|
|
class RoutingNode(Base):
|
|
__tablename__ = "routing_nodes"
|
|
__table_args__ = (UniqueConstraint("dataset_id", "osm_node_id", name="uq_routing_node_dataset_osm"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
osm_node_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
|
|
lat: Mapped[float] = mapped_column(Float, nullable=False)
|
|
lon: Mapped[float] = mapped_column(Float, nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
|
|
class RoutingEdge(Base):
|
|
__tablename__ = "routing_edges"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
dataset_id: Mapped[int] = mapped_column(ForeignKey("datasets.id"), nullable=False, index=True)
|
|
osm_way_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
|
|
source_osm_node_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
|
|
target_osm_node_id: Mapped[int] = mapped_column(BigInteger, nullable=False, index=True)
|
|
source_lat: Mapped[float] = mapped_column(Float, nullable=False)
|
|
source_lon: Mapped[float] = mapped_column(Float, nullable=False)
|
|
target_lat: Mapped[float] = mapped_column(Float, nullable=False)
|
|
target_lon: Mapped[float] = mapped_column(Float, nullable=False)
|
|
highway: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
length_m: Mapped[float] = mapped_column(Float, nullable=False)
|
|
walk_cost_s: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
reverse_walk_cost_s: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
drive_cost_s: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
reverse_drive_cost_s: Mapped[Optional[float]] = mapped_column(Float, nullable=True)
|
|
geometry_geojson: Mapped[str] = mapped_column(Text, nullable=False)
|
|
min_lon: Mapped[float] = mapped_column(Float, nullable=False)
|
|
min_lat: Mapped[float] = mapped_column(Float, nullable=False)
|
|
max_lon: Mapped[float] = mapped_column(Float, nullable=False)
|
|
max_lat: Mapped[float] = mapped_column(Float, nullable=False)
|
|
tags_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
|
|
class RouteMatch(Base):
|
|
__tablename__ = "route_matches"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
gtfs_route_id: Mapped[int] = mapped_column(ForeignKey("gtfs_routes.id"), nullable=False, index=True)
|
|
osm_feature_id: Mapped[Optional[int]] = mapped_column(ForeignKey("osm_features.id"), nullable=True, index=True)
|
|
confidence: Mapped[float] = mapped_column(Float, nullable=False, default=0)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False) # matched, probable, weak, missing, accepted, rejected
|
|
rule_source: Mapped[str] = mapped_column(String(64), default="auto", nullable=False) # auto, manual
|
|
reasons_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
gtfs_route: Mapped[GtfsRoute] = relationship()
|
|
osm_feature: Mapped[Optional[OsmFeature]] = relationship()
|
|
|
|
|
|
class MatchRule(Base):
|
|
__tablename__ = "match_rules"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
rule_type: Mapped[str] = mapped_column(String(64), nullable=False) # accept_match, reject_match, alias, force_operator
|
|
selector_json: Mapped[str] = mapped_column(Text, nullable=False)
|
|
action_json: Mapped[str] = mapped_column(Text, nullable=False)
|
|
note: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
|
|
class JourneySearchCache(Base):
|
|
__tablename__ = "journey_search_cache"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
cache_key: Mapped[str] = mapped_column(String(128), nullable=False, unique=True, index=True)
|
|
cache_type: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
|
|
payload_json: Mapped[str] = mapped_column(Text, nullable=False)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False, index=True)
|
|
|
|
|
|
class TravelRequest(Base):
|
|
__tablename__ = "travel_requests"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
origin_stop_id: Mapped[str] = mapped_column(Text, nullable=False)
|
|
destination_stop_id: Mapped[str] = mapped_column(Text, nullable=False)
|
|
via_stop_id: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
departure_time: Mapped[str] = mapped_column(String(32), nullable=False)
|
|
service_date: Mapped[Optional[str]] = mapped_column(String(10), nullable=True, index=True)
|
|
max_transfers: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
|
|
transfer_seconds: Mapped[int] = mapped_column(Integer, nullable=False, default=120)
|
|
source_filter: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
preferences_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
|
|
itineraries: Mapped[list["Itinerary"]] = relationship(back_populates="request", cascade="all, delete-orphan")
|
|
|
|
|
|
class Itinerary(Base):
|
|
__tablename__ = "itineraries"
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
request_id: Mapped[int] = mapped_column(ForeignKey("travel_requests.id"), nullable=False, index=True)
|
|
title: Mapped[str] = mapped_column(Text, nullable=False)
|
|
family: Mapped[str] = mapped_column(String(64), nullable=False, index=True)
|
|
status: Mapped[str] = mapped_column(String(64), nullable=False, default="candidate", index=True)
|
|
saved: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
|
|
summary_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
score_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
payload_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False, index=True)
|
|
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, nullable=False)
|
|
|
|
request: Mapped[TravelRequest] = relationship(back_populates="itineraries")
|
|
legs: Mapped[list["ItineraryLeg"]] = relationship(back_populates="itinerary", cascade="all, delete-orphan")
|
|
|
|
|
|
class ItineraryLeg(Base):
|
|
__tablename__ = "itinerary_legs"
|
|
__table_args__ = (UniqueConstraint("itinerary_id", "sequence", name="uq_itinerary_leg_sequence"),)
|
|
|
|
id: Mapped[int] = mapped_column(Integer, primary_key=True)
|
|
itinerary_id: Mapped[int] = mapped_column(ForeignKey("itineraries.id"), nullable=False, index=True)
|
|
sequence: Mapped[int] = mapped_column(Integer, nullable=False)
|
|
mode: Mapped[Optional[str]] = mapped_column(String(64), nullable=True, index=True)
|
|
route_ref: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
route_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
from_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
to_name: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
departure_time: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
|
|
arrival_time: Mapped[Optional[str]] = mapped_column(String(32), nullable=True)
|
|
locked: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False, index=True)
|
|
payload_json: Mapped[Optional[str]] = mapped_column(Text, nullable=True)
|
|
|
|
itinerary: Mapped[Itinerary] = relationship(back_populates="legs")
|