157 lines
6.6 KiB
Python
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)
|