MySQL / SQLite schema adapters

[Cyberfilo]

Today schema introspection is Postgres-only (pg_catalog queries in schema.py). Introduce a thin adapter abstraction so MySQL and SQLite can be added. The retrieval/prompt/safety layers are DB-agnostic already; this is mostly the introspection + dialect bits.

Likely larger than a one-liner — open with a design sketch. Postgres stays the reference.

[wondr-wclabs]

I can take this on, but I think it should be split so the first PR does not over-expand the package.

Proposed shape:

• Introduce a narrow database/dialect abstraction rather than putting more conditionals into schema.py:
  • SchemaIntrospector or similar: returns the existing Schema dataclasses.
  • QueryDialect/dialect metadata: at minimum the sqlglot read dialect and any identifier/default-schema conventions.
  • Keep Postgres as the reference implementation by moving the current pg_catalog queries behind that adapter without changing their behavior.
• Add SQLite first because it is dependency-free via the stdlib and gives the abstraction a real second implementation:
  • tables/views from sqlite_schema
  • columns from PRAGMA table_info / PRAGMA table_xinfo if hidden/generated columns matter
  • FKs from PRAGMA foreign_key_list
  • use a stable synthetic schema name such as main internally, but render unqualified names similarly to Postgres public so prompts stay readable.
• Treat MySQL as either a follow-up PR or an optional-extra PR because it needs a driver dependency. I would avoid adding a required runtime dependency just to support a second backend. The adapter would use information_schema.tables, columns, key_column_usage, and table_constraints, but driver choice should be explicit (pymysql optional extra seems lightweight; mysqlclient is less portable).
• Thread the chosen dialect into safety validation instead of hard-coding sqlglot.parse(..., read="postgres"). The invariant stays the same: only one read-only SELECT-style statement can execute.
• Keep retrieval and prompt formatting unchanged against the existing Schema/Table/Column/ForeignKey model. That is the point of the adapter: introspection changes, downstream ranking/prompting does not.

Test plan I would use:

• unit-test Postgres adapter construction with mocked fetch_dicts to prove existing row mapping is unchanged
• SQLite integration tests using an in-memory sqlite3 database, covering PKs, nullable columns, views if supported, and FKs
• safety tests proving the dialect value is passed through while the write/DDL rejection behavior remains intact
• no live MySQL test in the first PR unless you want optional dependency support in the same change

My preference: first PR = adapter abstraction + SQLite, Postgres behavior preserved; second PR = MySQL optional extra once the adapter seam is reviewed.