agenthub/backend/tests/test_db_portability.py

157 lines
6.6 KiB
Python

"""Portability proof for "SQLite today, PostgreSQL/MySQL is only a URL change".
No connection is opened here: the schema is *compiled* with the real PostgreSQL
and MySQL DDL compilers, which is the part that actually breaks during a
migration (dialect-only types, unbounded VARCHAR, index key width, missing
constraint names, server-side defaults MySQL refuses).
"""
from __future__ import annotations
import re
from sqlalchemy import Boolean, DateTime, Float, Integer, JSON, String, Text
from sqlalchemy.dialects import mysql, postgresql, sqlite
from sqlalchemy.dialects.mysql.aiomysql import MySQLDialect_aiomysql
from sqlalchemy.dialects.postgresql.asyncpg import PGDialect_asyncpg
from sqlalchemy.dialects.sqlite.aiosqlite import SQLiteDialect_aiosqlite
from sqlalchemy.engine.url import make_url
from sqlalchemy.schema import CreateTable, CreateIndex
import agenthub.db.models # noqa: F401 (register mappers)
from agenthub.db.base import Base
PORTABLE_TYPES = (String, Text, Integer, Float, Boolean, DateTime, JSON)
DIALECT_ONLY = ("JSONB", "UUID", "ARRAY", "HSTORE", "MONEY", "CITEXT", "BYTEA")
def test_boot_adds_a_column_the_first_release_did_not_have(tmp_path):
"""An existing ``agenthub.db`` keeps working: additive columns are ALTERed in.
``create_all`` only creates missing tables, so ``agent_type`` (added with the
agent-type/avatar work) needs an explicit ADD COLUMN for databases created
before it existed — and the rows already in there must survive the boot.
"""
import asyncio
import sqlite3
from sqlalchemy.ext.asyncio import create_async_engine
from agenthub.db.init_db import create_all
path = tmp_path / "legacy.db"
legacy = sqlite3.connect(path)
legacy.execute(
"CREATE TABLE agents (id VARCHAR(36) PRIMARY KEY, name VARCHAR(120), driver VARCHAR(48))"
)
legacy.execute("INSERT INTO agents VALUES ('agt_1', 'legacy', 'nanobot')")
legacy.commit()
legacy.close()
async def migrate() -> None:
engine = create_async_engine(f"sqlite+aiosqlite:///{path}")
try:
await create_all(engine)
finally:
await engine.dispose()
asyncio.run(migrate())
asyncio.run(migrate()) # idempotent: a second boot must not try again
conn = sqlite3.connect(path)
try:
columns = {row[1] for row in conn.execute("PRAGMA table_info(agents)")}
assert "agent_type" in columns
assert conn.execute("SELECT name, driver, agent_type FROM agents").fetchone() == (
"legacy",
"nanobot",
"",
)
finally:
conn.close()
# The sync dialects are the base classes of the async ones used at runtime, so
# this is the DDL asyncpg / aiomysql will send.
DIALECTS = {"sqlite": sqlite.dialect(), "postgresql": postgresql.dialect(), "mysql": mysql.dialect()}
def ddl(engine: str) -> str:
dialect = DIALECTS[engine]
chunks = [str(CreateTable(table).compile(dialect=dialect)) for table in Base.metadata.sorted_tables]
for table in Base.metadata.sorted_tables:
chunks += [str(CreateIndex(index).compile(dialect=dialect)) for index in table.indexes]
return chr(10).join(chunks).upper()
def test_every_table_builds_on_every_engine() -> None:
tables = {table.name for table in Base.metadata.sorted_tables}
assert len(tables) >= 11
for engine in DIALECTS:
script = ddl(engine)
missing = [name for name in tables if name.upper() not in script]
assert not missing, f"{engine} cannot build: {missing}"
def test_only_portable_column_types_are_used() -> None:
for table in Base.metadata.sorted_tables:
for column in table.columns:
assert isinstance(column.type, PORTABLE_TYPES), f"{table.name}.{column.name}: {column.type}"
if type(column.type) is String:
# MySQL rejects an unbounded VARCHAR; SQLite would hide it.
assert column.type.length, f"{table.name}.{column.name} has no length"
# MySQL refuses DEFAULT on TEXT/BLOB, so defaults stay in Python.
assert column.server_default is None and column.computed is None, f"{table.name}.{column.name}"
def test_identifiers_are_strings_or_a_portable_engine_sequence() -> None:
for table in Base.metadata.sorted_tables:
keys = list(table.primary_key.columns)
assert len(keys) == 1, table.name + ' has a composite key'
for key in keys:
if isinstance(key.type, String):
assert key.type.length, f"{table.name}.{key.name}: string key needs a length"
assert key.autoincrement is not True, f"{table.name}.{key.name} asks for a sequence"
else:
# Append-only logs may use SERIAL / AUTO_INCREMENT ... which only
# works on a bare Integer, never on BIGSERIAL or UUID.
assert type(key.type) is Integer, f"{table.name}.{key.name}: {key.type}"
def test_no_dialect_only_types_leak_into_the_schema() -> None:
for engine in DIALECTS:
script = ddl(engine)
for banned in DIALECT_ONLY:
assert banned not in script, f"{engine} schema contains {banned}"
def test_indexed_string_columns_fit_the_mysql_key_limit() -> None:
for table in Base.metadata.sorted_tables:
indexed = {column for index in table.indexes for column in index.columns}
for column in table.columns:
if not isinstance(column.type, String) or isinstance(column.type, Text):
continue
if not (column.index or column.unique or column in indexed):
continue
assert column.type.length <= 191, f"{table.name}.{column.name} is too wide to index on MySQL"
def test_constraints_are_named_so_migrations_stay_deterministic() -> None:
names = re.findall("CONSTRAINT ([A-Za-z0-9_]+)", ddl("postgresql"))
names += re.findall("CONSTRAINT ([A-Za-z0-9_]+)", ddl("mysql"))
names += re.findall("CREATE INDEX ([A-Za-z0-9_]+)", ddl("mysql"))
assert len(names) > 10, names
for name in names:
assert re.match("^(PK|FK|UQ|CK|IX|IX_)_", name), name
def test_the_async_drivers_are_the_dialects_compiled_above() -> None:
cases = {
"sqlite+aiosqlite:///./agenthub.db": (SQLiteDialect_aiosqlite, "sqlite", "aiosqlite"),
"postgresql+asyncpg://hub:hub@localhost:5432/agenthub": (PGDialect_asyncpg, "postgresql", "asyncpg"),
"mysql+aiomysql://hub:hub@localhost:3306/agenthub": (MySQLDialect_aiomysql, "mysql", "aiomysql"),
}
for url, (expected, name, driver) in cases.items():
resolved = make_url(url).get_dialect()
assert resolved is expected
assert (resolved.name, resolved.driver) == (name, driver)