96 lines
3.7 KiB
Python
96 lines
3.7 KiB
Python
"""Import the legacy SQLite database into an empty PostgreSQL database.
|
|
|
|
The import is intentionally idempotent: if any application rows already exist in the
|
|
destination, nothing is changed. This makes it safe to run before every container start.
|
|
"""
|
|
|
|
import os
|
|
import sys
|
|
from pathlib import Path
|
|
|
|
from sqlalchemy import URL, MetaData, Table, create_engine, func, inspect, select, text
|
|
|
|
from models import InternetConnection, Phone, User, db
|
|
|
|
|
|
TABLES = (User.__table__, Phone.__table__, InternetConnection.__table__)
|
|
|
|
|
|
def destination_url():
|
|
database_url = os.environ.get("DATABASE_URL")
|
|
if database_url:
|
|
return database_url
|
|
return URL.create(
|
|
drivername="postgresql+psycopg2",
|
|
username=os.environ.get("TELCO_DB_USER", "telco"),
|
|
password=os.environ.get("TELCO_DB_PASSWORD", "telco"),
|
|
host=os.environ.get("TELCO_DB_HOST", "db"),
|
|
port=int(os.environ.get("TELCO_DB_PORT", "5432")),
|
|
database=os.environ.get("TELCO_DB_NAME", "telco"),
|
|
)
|
|
|
|
|
|
def main():
|
|
sqlite_path = Path(os.environ.get("TELCO_LEGACY_SQLITE_PATH", "/app/legacy-data/telco.db"))
|
|
target_engine = create_engine(destination_url())
|
|
|
|
# Create the current schema first. The data copy below then runs as one transaction.
|
|
db.metadata.create_all(target_engine)
|
|
|
|
try:
|
|
with target_engine.connect() as connection:
|
|
existing_rows = sum(
|
|
connection.execute(select(func.count()).select_from(table)).scalar_one()
|
|
for table in TABLES
|
|
)
|
|
if existing_rows:
|
|
print(f"PostgreSQL already contains {existing_rows} application rows; SQLite import skipped.")
|
|
return
|
|
|
|
if not sqlite_path.is_file():
|
|
print(
|
|
f"Legacy SQLite database not found at {sqlite_path}. "
|
|
"Place it there before the first start.",
|
|
file=sys.stderr,
|
|
)
|
|
raise SystemExit(1)
|
|
|
|
source_engine = create_engine(f"sqlite:///{sqlite_path.as_posix()}")
|
|
source_metadata = MetaData()
|
|
imported = {}
|
|
try:
|
|
source_table_names = set(inspect(source_engine).get_table_names())
|
|
with target_engine.begin() as destination, source_engine.connect() as source:
|
|
for target_table in TABLES:
|
|
if target_table.name not in source_table_names:
|
|
imported[target_table.name] = 0
|
|
continue
|
|
source_table = Table(target_table.name, source_metadata, autoload_with=source_engine)
|
|
target_columns = {column.name for column in target_table.columns}
|
|
rows = [
|
|
{key: value for key, value in row.items() if key in target_columns}
|
|
for row in source.execute(select(source_table)).mappings()
|
|
]
|
|
if rows:
|
|
destination.execute(target_table.insert(), rows)
|
|
imported[target_table.name] = len(rows)
|
|
|
|
# Explicit IDs were retained, so move PostgreSQL's sequences past them.
|
|
if destination.dialect.name == "postgresql":
|
|
for table in TABLES:
|
|
destination.execute(text(
|
|
"SELECT setval(pg_get_serial_sequence(:table_name, 'id'), "
|
|
f"COALESCE(MAX(id), 1), MAX(id) IS NOT NULL) FROM {table.name}"
|
|
), {"table_name": table.name})
|
|
finally:
|
|
source_engine.dispose()
|
|
|
|
summary = ", ".join(f"{name}={count}" for name, count in imported.items())
|
|
print(f"SQLite import complete: {summary}")
|
|
finally:
|
|
target_engine.dispose()
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|