Bug: ADBC SQLite — dialect defaults to `postgres`, `begin()` collides with implicit transaction

[cofin]

Adapter: sqlspec.adapters.adbc
SQLSpec version: 0.47.0
Driver: adbc-driver-sqlite>=1.2.0

Summary

Two distinct bugs in the SQLSpec ADBC adapter make AdbcConfig(connection_config={"driver_name": "sqlite", ...}) unusable:

1. SQLSpec cannot detect the underlying dialect when the ADBC SQLite driver is configured, so it falls back to postgres.
2. AdbcDriver.begin() issues cursor.execute("BEGIN") even though ADBC SQLite is already inside an implicit transaction, which the driver rejects.

Both bugs are reproducible against a fresh SQLite ADBC config; no extension code is required.

Reproduction

import asyncio
import tempfile
from pathlib import Path

from sqlspec.adapters.adbc import AdbcConfig

async def main() -> None:
    tmp = Path(tempfile.mkdtemp()) / "demo.db"
    config = AdbcConfig(connection_config={"driver_name": "sqlite", "uri": str(tmp)})
    async with config.provide_session() as driver:
        await driver.execute("CREATE TABLE t (id INTEGER PRIMARY KEY)")
        await driver.begin()  # raises SQLSpecError("cannot start a transaction within a transaction")

asyncio.run(main())

Log output during setup:

WARNING  sqlspec.adapters.adbc:driver.py:93 Could not determine dialect from driver info. Defaulting to 'postgres'.

Subsequent failure:

sqlspec.exceptions.SQLSpecError: Failed to begin transaction: IO: Failed to step query: cannot start a transaction within a transaction

Expected Behavior

• When driver_name="sqlite" is given to AdbcConfig, the adapter should detect the SQLite dialect.
• AdbcDriver.begin() should be a no-op for drivers that hold an implicit transaction (or begin() should only fire when the connection has explicit-transaction semantics).

Suggested Fix Direction

• Read driver_name (or the adbc.driver connection metadata) directly when GetInfo-based detection comes back empty, instead of defaulting to postgres.
• Skip cursor.execute("BEGIN") for ADBC drivers whose connection is already in autocommit-with-implicit-transaction mode. SQLite via ADBC is the obvious case; document any other driver families with the same semantics.

File References

• sqlspec/adapters/adbc/core.py:160 (detect_dialect() — does not consult connection_config["driver_name"])
• sqlspec/adapters/adbc/driver.py:259 (begin() raises against ADBC SQLite's implicit transaction)