Skip to content

A reactive notebook for Python — run reproducible experiments, query with SQL, execute as a script, deploy as an app, and version with git. Stored as pure Python. All in a modern, AI-native editor.

57.4 KB
Raw Blame
# Copyright 2026 Marimo. All rights reserved.

from __future__ import annotations

from contextlib import contextmanager
from typing import TYPE_CHECKING
from unittest import mock

import pytest

from marimo._data.models import Database, DataTable, DataTableColumn, Schema
from marimo._dependencies.dependencies import DependencyManager
from marimo._sql.engines.sqlalchemy import (
    SQLAlchemyEngine,
    _index_by_table_name,
    safe_execute,
)
from marimo._sql.engines.types import (
    EngineCatalog,
    QueryEngine,
    default_inference_config,
)
from marimo._sql.sql import sql
from marimo._types.ids import VariableName

HAS_SQLALCHEMY = DependencyManager.sqlalchemy.has()
HAS_POLARS = DependencyManager.polars.has()
HAS_PANDAS = DependencyManager.pandas.has()

if TYPE_CHECKING:
    import sqlalchemy as sa

UNUSED_DB_NAME = "unused_db_name"


@pytest.fixture
def empty_sqlite_engine() -> sa.Engine:
    """Create an empty SQLite database for testing."""

    import sqlalchemy as sa

    engine = sa.create_engine("sqlite:///:memory:")
    return engine


@pytest.fixture
def sqlite_engine() -> sa.Engine:
    """Create a temporary SQLite database for testing."""

    import sqlalchemy as sa
    from sqlalchemy import text

    engine = sa.create_engine("sqlite:///:memory:")

    # Test if standard syntax works
    with engine.begin() as conn:
        conn.execute(
            text(
                """
                CREATE TABLE test (
                    id INTEGER PRIMARY KEY,
                    name TEXT
                )
                """
            )
        )
        conn.execute(
            text(
                """
                INSERT INTO test (id, name) VALUES
                (1, 'Alice'),
                (2, 'Bob'),
                (3, 'Charlie')
                """
            )
        )
        # Add another table in another schema
        conn.execute(text("ATTACH ':memory:' AS my_schema"))
        conn.execute(
            text(
                """
                CREATE TABLE my_schema.test2 (
                    id INTEGER PRIMARY KEY,
                    name TEXT
                )
                """
            )
        )

    # Test if mo.sql works
    sql("INSERT INTO test (id, name) VALUES (4, 'Rose')", engine=engine)
    return engine


@pytest.fixture
def sqlite_engine_meta() -> sa.Engine:
    """Create a temporary SQLite database with information_schema.
    Use a mock information_schema"""

    import sqlalchemy as sa

    engine = sa.create_engine("sqlite:///:memory:")

    sql("ATTACH ':memory:' AS information_schema", engine=engine)
    sql(
        "CREATE TABLE information_schema.tables (table_name TEXT)",
        engine=engine,
    )
    sql(
        "INSERT INTO information_schema.tables VALUES ('tables')",
        engine=engine,
    )

    return engine


@pytest.fixture
def sqlite_engine_with_view() -> sa.Engine:
    """Create a SQLite database with tables and views in two schemas."""

    import sqlalchemy as sa
    from sqlalchemy import text

    engine = sa.create_engine("sqlite:///:memory:")

    with engine.begin() as conn:
        conn.execute(
            text(
                """
                CREATE TABLE test (
                    id INTEGER PRIMARY KEY,
                    name TEXT
                )
                """
            )
        )
        conn.execute(
            text("CREATE VIEW test_view AS SELECT id, name FROM test")
        )
        conn.execute(text("ATTACH ':memory:' AS my_schema"))
        conn.execute(
            text(
                """
                CREATE TABLE my_schema.test2 (
                    id INTEGER PRIMARY KEY,
                    name TEXT
                )
                """
            )
        )
        conn.execute(
            text(
                "CREATE VIEW my_schema.test2_view AS "
                "SELECT id, name FROM my_schema.test2"
            )
        )

    return engine


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_dialect(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine dialect property."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    assert engine.dialect == "sqlite"


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_uses_default_inference_config(
    sqlite_engine: sa.Engine,
) -> None:
    """SQLAlchemy shares the default discovery config (see #9775)."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    assert engine.inference_config == default_inference_config()


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_is_instance(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine is an instance of the correct types."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("sqlite")
    )
    assert isinstance(engine, SQLAlchemyEngine)
    assert isinstance(engine, EngineCatalog)
    assert isinstance(engine, QueryEngine)


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_invalid_engine() -> None:
    """Test SQLAlchemyEngine with an invalid engine and inspector does not raise errors."""

    engine = SQLAlchemyEngine(connection=None, engine_name=None)  # type: ignore
    assert engine.inspector is None
    assert engine.default_database is None
    assert engine.default_schema is None


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_empty_engine(empty_sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine with an empty engine."""
    engine = SQLAlchemyEngine(
        connection=empty_sqlite_engine, engine_name=VariableName("sqlite")
    )

    databases = engine.get_databases(
        include_schemas=True, include_tables=True, include_table_details=True
    )
    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[Schema(name="main", tables=[])],
            engine=VariableName("sqlite"),
        )
    ]

    tables = engine.get_tables_in_schema(
        schema="main", database=UNUSED_DB_NAME, include_table_details=False
    )
    assert tables == []

    table_info = engine.get_table_details(
        table_name="test", schema_name="main", database_name=UNUSED_DB_NAME
    )
    assert table_info is None


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_sql_types() -> None:
    import sqlalchemy as sa

    sqlite_engine = sa.create_engine("sqlite:///:memory:")

    sql(
        """
        CREATE TABLE all_types (
            col_integer INTEGER,
            col_real REAL,
            col_numeric NUMERIC,
            col_text TEXT,
            col_blob BLOB,
            col_json JSON
        );
    """,
        engine=sqlite_engine,
    )
    sql(
        """
        INSERT INTO all_types (
            col_integer,
            col_real,
            col_numeric,
            col_text,
            col_blob,
            col_json
        ) VALUES
        (
            1,
            1.0,
            1.0,
            'text',
            X'01',
            '{"key": "value"}'
        );
    """,
        engine=sqlite_engine,
    )

    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    tables = engine.get_tables_in_schema(
        schema="main", database=UNUSED_DB_NAME, include_table_details=True
    )

    assert len(tables) == 1
    table = tables[0]
    assert table.source == "sqlite"
    assert table.source_type == "connection"
    assert table.name == "all_types"
    assert table.num_columns == 6
    assert table.num_rows is None

    columns = table.columns
    assert columns[0].name == "col_integer"
    assert columns[0].type == "integer"
    assert columns[0].external_type == "INTEGER"
    assert columns[0].sample_values == []  # not implemented

    assert columns[1].name == "col_real"
    assert columns[1].type == "number"
    assert columns[1].external_type == "REAL"

    assert columns[2].name == "col_numeric"
    assert columns[2].type == "number"
    assert columns[2].external_type == "NUMERIC"

    assert columns[3].name == "col_text"
    assert columns[3].type == "string"
    assert columns[3].external_type == "TEXT"

    assert columns[4].name == "col_blob"
    assert columns[4].type == "string"
    assert columns[4].external_type == "BLOB"

    assert columns[5].name == "col_json"
    assert columns[5].type == "string"
    assert columns[5].external_type == "JSON"


def get_expected_table(
    table_name: str, include_table_details: bool = True
) -> DataTable:
    "This test can be reused for sqlite_engine"
    return DataTable(
        source_type="connection",
        source="sqlite",
        name=table_name,
        num_rows=None,
        num_columns=2 if include_table_details else None,
        variable_name=None,
        engine=VariableName("test_sqlite"),
        primary_keys=["id"] if include_table_details else [],
        indexes=[],
        columns=[
            DataTableColumn(
                name="id",
                type="integer",
                external_type="INTEGER",
                sample_values=[],
            ),
            DataTableColumn(
                name="name",
                type="string",
                external_type="TEXT",
                sample_values=[],
            ),
        ]
        if include_table_details
        else [],
    )


def get_expected_schema(schema_name: str, table_name: str) -> Schema:
    return Schema(
        name=schema_name,
        tables=[get_expected_table(table_name)],
    )


def get_expected_view(view_name: str) -> DataTable:
    "Expected reflection of a view over a `get_expected_table` table."
    view = get_expected_table(view_name)
    view.type = "view"
    view.primary_keys = []
    return view


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_table_details(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine get_table method."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    table = engine.get_table_details(
        table_name="test", schema_name="main", database_name=UNUSED_DB_NAME
    )
    assert table == get_expected_table("test")

    # different schema
    table = engine.get_table_details(
        table_name="test2",
        schema_name="my_schema",
        database_name=UNUSED_DB_NAME,
    )
    assert table == get_expected_table("test2")

    # non-existent table
    assert (
        engine.get_table_details(
            table_name="non_existent",
            schema_name="main",
            database_name=UNUSED_DB_NAME,
        )
        is None
    )

    # non-existent schema
    assert (
        engine.get_table_details(
            table_name="test",
            schema_name="non_existent",
            database_name=UNUSED_DB_NAME,
        )
        is None
    )


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_tables_in_schema(
    sqlite_engine: sa.Engine,
) -> None:
    """Test SQLAlchemyEngine get_tables method."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    tables = engine.get_tables_in_schema(
        schema="main", database=UNUSED_DB_NAME, include_table_details=True
    )

    assert isinstance(tables, list)
    assert len(tables) == 1
    assert tables[0] == get_expected_table("test")

    # Test with other schema
    tables = engine.get_tables_in_schema(
        schema="my_schema", database=UNUSED_DB_NAME, include_table_details=True
    )
    assert isinstance(tables, list)
    assert len(tables) == 1
    assert tables[0] == get_expected_table("test2")

    # Test with non-existent schema
    assert (
        engine.get_tables_in_schema(
            schema="non_existent",
            database=UNUSED_DB_NAME,
            include_table_details=True,
        )
        == []
    )

    # Test with include_table_details false
    tables = engine.get_tables_in_schema(
        schema="main", database=UNUSED_DB_NAME, include_table_details=False
    )
    assert isinstance(tables, list)
    assert len(tables) == 1
    expected_table = get_expected_table("test", False)
    assert tables[0] == expected_table


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_tables_with_views(
    sqlite_engine_with_view: sa.Engine,
) -> None:
    """Views keep type='view' and full details on the batched path."""
    engine = SQLAlchemyEngine(
        sqlite_engine_with_view, engine_name=VariableName("test_sqlite")
    )
    tables = engine.get_tables_in_schema(
        schema="main", database=UNUSED_DB_NAME, include_table_details=True
    )
    assert tables == [
        get_expected_table("test"),
        get_expected_view("test_view"),
    ]

    tables = engine.get_tables_in_schema(
        schema="my_schema",
        database=UNUSED_DB_NAME,
        include_table_details=True,
    )
    assert tables == [
        get_expected_table("test2"),
        get_expected_view("test2_view"),
    ]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_match_per_table_details(
    sqlite_engine_with_view: sa.Engine,
) -> None:
    """Batched reflection returns the same tables as per-table reflection."""
    engine = SQLAlchemyEngine(
        sqlite_engine_with_view, engine_name=VariableName("test_sqlite")
    )
    batched = engine.get_tables_in_schema(
        schema="main", database=UNUSED_DB_NAME, include_table_details=True
    )
    with mock.patch.object(
        engine, "_get_batched_table_details", return_value=None
    ):
        per_table = engine.get_tables_in_schema(
            schema="main",
            database=UNUSED_DB_NAME,
            include_table_details=True,
        )
    assert batched == per_table


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_avoid_per_table_reflection(
    sqlite_engine: sa.Engine,
) -> None:
    """No per-table reflection runs when the batch covers every table."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    with mock.patch.object(
        engine, "_get_columns", wraps=engine._get_columns
    ) as spy:
        tables = engine.get_tables_in_schema(
            schema="main",
            database=UNUSED_DB_NAME,
            include_table_details=True,
        )
    assert tables == [get_expected_table("test")]
    spy.assert_not_called()


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_fall_back_when_multi_reflection_raises(
    sqlite_engine: sa.Engine,
) -> None:
    """A failing batch logs one warning and falls back per table."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    with (
        mock.patch.object(
            engine.inspector,
            "get_multi_columns",
            side_effect=RuntimeError("boom"),
        ),
        mock.patch("marimo._sql.engines.sqlalchemy.LOGGER") as mock_logger,
    ):
        tables = engine.get_tables_in_schema(
            schema="main",
            database=UNUSED_DB_NAME,
            include_table_details=True,
        )
    assert tables == [get_expected_table("test")]
    mock_logger.warning.assert_called_once_with(
        "Failed to get batched columns", exc_info=True
    )


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_nonexistent_schema_skips_batched_reflection(
    sqlite_engine: sa.Engine,
) -> None:
    """A schema with no tables returns [] without attempting a batch."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    with mock.patch.object(engine, "_get_batched_table_details") as batched:
        tables = engine.get_tables_in_schema(
            schema="non_existent",
            database=UNUSED_DB_NAME,
            include_table_details=True,
        )
    assert tables == []
    batched.assert_not_called()


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_skip_meta_schemas(
    sqlite_engine_meta: sa.Engine,
) -> None:
    """Test SQLAlchemyEngine get_tables method."""
    engine = SQLAlchemyEngine(
        sqlite_engine_meta, engine_name=VariableName("test_sqlite")
    )
    databases = engine.get_databases(
        include_schemas=True, include_tables=True, include_table_details=True
    )
    assert len(databases[0].schemas) == 2
    assert databases[0].schemas[0].name == "main"
    assert databases[0].schemas[1].name == "information_schema"

    information_schema = databases[0].schemas[1]
    assert information_schema.tables == []
    # Eager discovery was skipped for the meta schema, so the empty table
    # list is not authoritative — `tables_resolved` must be False so the
    # frontend doesn't treat it as "known empty".
    assert information_schema.tables_resolved is False


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_schemas(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine get_schemas method."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    schemas = engine.get_schemas(
        database=UNUSED_DB_NAME,
        include_tables=True,
        include_table_details=True,
    )

    assert isinstance(schemas, list)
    assert len(schemas) == 2

    schema = schemas[0]
    assert schema == get_expected_schema("main", "test")

    schema = schemas[1]
    assert schema == get_expected_schema("my_schema", "test2")

    # Test with include_table_details false
    schemas = engine.get_schemas(
        database=UNUSED_DB_NAME,
        include_tables=True,
        include_table_details=False,
    )
    assert isinstance(schemas, list)
    assert len(schemas) == 2

    schema = schemas[0]
    assert schema.name == "main"
    assert len(schema.tables) == 1

    expected_table = get_expected_table("test", include_table_details=False)
    assert schema.tables[0] == expected_table

    # Test with include_tables false
    schemas = engine.get_schemas(
        database=UNUSED_DB_NAME,
        include_tables=False,
        include_table_details=True,
    )
    assert isinstance(schemas, list)
    assert len(schemas) == 2

    schema = schemas[0]
    assert schema.name == "main"
    assert len(schema.tables) == 0

    schema = schemas[1]
    assert schema.name == "my_schema"
    assert len(schema.tables) == 0


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_get_databases(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine get_databases method."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    databases = engine.get_databases(
        include_schemas=True, include_tables=True, include_table_details=True
    )

    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[
                get_expected_schema("main", "test"),
                get_expected_schema("my_schema", "test2"),
            ],
            engine=VariableName("test_sqlite"),
        )
    ]

    # Test with include_table_details false
    databases = engine.get_databases(
        include_schemas=True, include_tables=True, include_table_details=False
    )
    tables_main = get_expected_table("test", include_table_details=False)
    tables_my_schema = get_expected_table("test2", include_table_details=False)

    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[
                Schema(name="main", tables=[tables_main]),
                Schema(name="my_schema", tables=[tables_my_schema]),
            ],
            engine=VariableName("test_sqlite"),
        )
    ]

    # Test with include_tables false
    databases = engine.get_databases(
        include_schemas=True, include_tables=False, include_table_details=True
    )
    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[
                Schema(name="main", tables=[], tables_resolved=False),
                Schema(name="my_schema", tables=[], tables_resolved=False),
            ],
            engine=VariableName("test_sqlite"),
        )
    ]

    # Test with include_schemas false
    databases = engine.get_databases(
        include_schemas=False, include_tables=True, include_table_details=True
    )
    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[],
            schemas_resolved=False,
            engine=VariableName("test_sqlite"),
        )
    ]

    # Test with include_schemas and include_tables false
    databases = engine.get_databases(
        include_schemas=False, include_tables=False, include_table_details=True
    )
    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[],
            schemas_resolved=False,
            engine=VariableName("test_sqlite"),
        )
    ]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_get_databases_auto(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine get_databases method with 'auto' option."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )

    # For SQLite, _is_cheap_discovery() returns True, so 'auto' should behave like True
    databases = engine.get_databases(
        include_schemas="auto",
        include_tables="auto",
        include_table_details="auto",
    )

    # Should be equivalent to setting all params to True since sqlite is a "cheap" dialect
    tables_main = get_expected_table("test", include_table_details=True)
    tables_my_schema = get_expected_table("test2", include_table_details=True)
    assert tables_main.columns == tables_my_schema.columns
    assert tables_main.primary_keys == tables_my_schema.primary_keys
    assert databases == [
        Database(
            name=":memory:",
            dialect="sqlite",
            schemas=[
                get_expected_schema("main", "test"),
                get_expected_schema("my_schema", "test2"),
            ],
            engine=VariableName("test_sqlite"),
        )
    ]

    # Test with a mock to simulate a non-cheap dialect
    with mock.patch.object(
        SQLAlchemyEngine, "_is_cheap_discovery", return_value=False
    ):
        # For a non-cheap dialect, 'auto' should behave like False
        databases = engine.get_databases(
            include_schemas="auto",
            include_tables="auto",
            include_table_details="auto",
        )

        assert databases == [
            Database(
                name=":memory:",
                dialect="sqlite",
                schemas=[],
                schemas_resolved=False,
                engine=VariableName("test_sqlite"),
            )
        ]


@pytest.mark.skipif(
    not HAS_SQLALCHEMY or not HAS_PANDAS,
    reason="SQLAlchemy and Pandas not installed",
)
def test_sqlalchemy_engine_execute(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine execute."""
    import pandas as pd
    import polars as pl

    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    result = engine.execute("SELECT * FROM test ORDER BY id")
    assert isinstance(result, (pd.DataFrame, pl.DataFrame))
    assert len(result) == 4


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_get_database_name(sqlite_engine: sa.Engine) -> None:
    """Test SQLAlchemyEngine get_database_name."""
    engine = SQLAlchemyEngine(
        sqlite_engine, engine_name=VariableName("test_sqlite")
    )
    assert engine.get_default_database() == ":memory:"

    import sqlalchemy as sa

    # Test with no database name
    engine = SQLAlchemyEngine(
        sa.create_engine("sqlite:///"), engine_name=VariableName("test_sqlite")
    )
    assert engine.get_default_database() == ""


@pytest.mark.skipif(
    not HAS_SQLALCHEMY or not HAS_POLARS or not HAS_PANDAS,
    reason="SQLAlchemy, Polars, and Pandas not installed",
)
def test_sqlalchemy_engine_sql_output_formats(
    sqlite_engine: sa.Engine,
) -> None:
    """Test SQLAlchemyEngine execute with different SQL output formats."""
    from unittest import mock

    import pandas as pd
    import polars as pl

    # Test with polars output format
    with mock.patch.object(
        SQLAlchemyEngine, "sql_output_format", return_value="polars"
    ):
        engine = SQLAlchemyEngine(
            sqlite_engine, engine_name=VariableName("test_sqlite")
        )
        result = engine.execute("SELECT * FROM test ORDER BY id")
        assert isinstance(result, pl.DataFrame)
        assert len(result) == 4

    # Test with lazy-polars output format
    with mock.patch.object(
        SQLAlchemyEngine, "sql_output_format", return_value="lazy-polars"
    ):
        engine = SQLAlchemyEngine(
            sqlite_engine, engine_name=VariableName("test_sqlite")
        )
        result = engine.execute("SELECT * FROM test ORDER BY id")
        assert isinstance(result, pl.LazyFrame)
        assert len(result.collect()) == 4

    # Test with pandas output format
    with mock.patch.object(
        SQLAlchemyEngine, "sql_output_format", return_value="pandas"
    ):
        engine = SQLAlchemyEngine(
            sqlite_engine, engine_name=VariableName("test_sqlite")
        )
        result = engine.execute("SELECT * FROM test ORDER BY id")
        assert isinstance(result, pd.DataFrame)
        assert len(result) == 4

    # Test with native output format
    with mock.patch.object(
        SQLAlchemyEngine, "sql_output_format", return_value="native"
    ):
        engine = SQLAlchemyEngine(
            sqlite_engine, engine_name=VariableName("test_sqlite")
        )
        result = engine.execute("SELECT * FROM test ORDER BY id")
        assert not isinstance(
            result, (pd.DataFrame, pl.DataFrame, pl.LazyFrame)
        )
        assert hasattr(result, "fetchall")

    # Test with auto output format (should use polars if available)
    with mock.patch.object(
        SQLAlchemyEngine, "sql_output_format", return_value="auto"
    ):
        engine = SQLAlchemyEngine(
            sqlite_engine, engine_name=VariableName("test_sqlite")
        )
        result = engine.execute("SELECT * FROM test ORDER BY id")
        assert isinstance(result, (pd.DataFrame, pl.DataFrame))
        assert len(result) == 4


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_cursor_metadata(
    sqlite_engine: sa.Engine,
) -> None:
    """Test SQLAlchemyEngine get_cursor_metadata."""
    from sqlalchemy.engine import CursorResult

    with mock.patch.object(
        SQLAlchemyEngine, "sql_output_format", return_value="native"
    ):
        engine = SQLAlchemyEngine(
            sqlite_engine, engine_name=VariableName("test_sqlite")
        )
        result = engine.execute("SELECT * FROM test ORDER BY id")
        assert isinstance(result, CursorResult)

        assert SQLAlchemyEngine.is_cursor_result(result)
        metadata = SQLAlchemyEngine.get_cursor_metadata(result)
        assert metadata is not None
        assert metadata["sql_statement_type"] == "Query / Unknown"


@pytest.mark.requires("sqlalchemy", "polars")
@pytest.mark.parametrize("output_format", ["polars", "lazy-polars", "auto"])
@pytest.mark.parametrize(
    "values",
    [
        [None] * 100 + [0.5],
        [None] * 1000 + ["late", None],
        [1] * 100 + ["late"],
        [1] * 100 + [1e100],
    ],
    ids=[
        "late-float",
        "late-string-after-nulls",
        "late-string-after-ints",
        "integer-overflow",
    ],
)
def test_sqlalchemy_schema_inference_fallback(
    empty_sqlite_engine: sa.Engine,
    output_format: str,
    values: list[float | str | None],
) -> None:
    import polars as pl
    import sqlalchemy as sa
    from polars.testing import assert_frame_equal

    with empty_sqlite_engine.begin() as connection:
        connection.execute(sa.text("CREATE TABLE source (value)"))
        connection.execute(sa.text("CREATE TABLE destination (value)"))
        connection.execute(
            sa.text("INSERT INTO source VALUES (:value)"),
            [{"value": value} for value in values],
        )
        rows = connection.execute(
            sa.text("SELECT value FROM source ORDER BY rowid")
        ).fetchall()

    with pytest.raises(
        pl.exceptions.ComputeError, match="infer_schema_length"
    ):
        pl.DataFrame(rows)
    expected = pl.DataFrame(rows, infer_schema_length=None)
    engine = SQLAlchemyEngine(empty_sqlite_engine)
    executed = mock.Mock()
    sa.event.listen(empty_sqlite_engine, "before_cursor_execute", executed)
    try:
        with mock.patch.object(
            engine, "sql_output_format", return_value=output_format
        ):
            result = engine.execute(
                "INSERT INTO destination SELECT value FROM source ORDER BY rowid "
                "RETURNING value"
            )
        executed.assert_called_once()
    finally:
        sa.event.remove(empty_sqlite_engine, "before_cursor_execute", executed)

    if output_format == "lazy-polars":
        assert isinstance(result, pl.LazyFrame)
        result = result.collect()
    assert_frame_equal(result, expected)
    with empty_sqlite_engine.connect() as connection:
        assert connection.execute(
            sa.text("SELECT COUNT(*) FROM destination")
        ).scalar_one() == len(values)


@pytest.mark.requires("sqlalchemy", "polars")
@pytest.mark.parametrize(
    "query",
    [
        "SELECT 1 AS value",
        "SELECT NULL AS value",
        "SELECT 1 AS value WHERE 0",
    ],
    ids=["valid", "all-null", "empty"],
)
def test_sqlalchemy_schema_inference_fast_path(
    empty_sqlite_engine: sa.Engine, query: str
) -> None:
    import polars as pl
    import sqlalchemy as sa
    from polars.testing import assert_frame_equal

    with empty_sqlite_engine.connect() as connection:
        expected = pl.DataFrame(connection.execute(sa.text(query)).fetchall())

    engine = SQLAlchemyEngine(empty_sqlite_engine)
    with (
        mock.patch.object(engine, "sql_output_format", return_value="polars"),
        mock.patch.object(pl, "DataFrame", wraps=pl.DataFrame) as constructor,
    ):
        result = engine.execute(query)
        constructor.assert_called_once()
        assert constructor.call_args.kwargs == {}
    assert_frame_equal(result, expected)


@pytest.mark.requires("sqlalchemy", "polars")
@pytest.mark.parametrize(
    "error_type", ["ComputeError", "SchemaError", "ValueError"]
)
def test_sqlalchemy_schema_inference_unrelated_error(
    empty_sqlite_engine: sa.Engine, error_type: str
) -> None:
    import polars as pl

    error = (
        ValueError("unrelated error")
        if error_type == "ValueError"
        else getattr(pl.exceptions, error_type)("unrelated error")
    )
    engine = SQLAlchemyEngine(empty_sqlite_engine)
    with (
        mock.patch.object(engine, "sql_output_format", return_value="polars"),
        mock.patch.object(pl, "DataFrame", side_effect=error) as constructor,
        pytest.raises(type(error)) as exc_info,
    ):
        engine.execute("SELECT 1 AS value")
    assert exc_info.value is error
    constructor.assert_called_once()


@pytest.mark.requires("sqlalchemy", "polars", "pandas")
@pytest.mark.parametrize("output_format", ["polars", "lazy-polars", "auto"])
@pytest.mark.parametrize(
    "message",
    ["cannot construct a common type", "increase infer_schema_length"],
)
def test_sqlalchemy_schema_inference_retry_failure(
    empty_sqlite_engine: sa.Engine, output_format: str, message: str
) -> None:
    import pandas as pd
    import polars as pl
    from pandas.testing import assert_frame_equal

    error = pl.exceptions.ComputeError(message)
    engine = SQLAlchemyEngine(empty_sqlite_engine)
    with (
        mock.patch.object(
            engine, "sql_output_format", return_value=output_format
        ),
        mock.patch.object(
            pl,
            "DataFrame",
            side_effect=[
                pl.exceptions.ComputeError(
                    "consider increasing `infer_schema_length`"
                ),
                error,
            ],
        ) as constructor,
    ):
        if output_format == "auto":
            result = engine.execute("SELECT 1 AS value")
            assert_frame_equal(result, pd.DataFrame({"value": [1]}))
        else:
            with pytest.raises(pl.exceptions.ComputeError) as exc_info:
                engine.execute("SELECT 1 AS value")
            assert exc_info.value is error

    assert constructor.call_count == 2
    assert constructor.call_args.kwargs == {"infer_schema_length": None}
    assert (
        constructor.call_args_list[0].args[0] is constructor.call_args.args[0]
    )


@pytest.mark.requires("sqlalchemy", "polars", "pandas")
@pytest.mark.parametrize("output_format", ["polars", "lazy-polars", "auto"])
def test_sqlalchemy_schema_inference_incompatible_types(
    empty_sqlite_engine: sa.Engine, output_format: str
) -> None:
    import pandas as pd
    import polars as pl
    from pandas.testing import assert_frame_equal

    query = """
        WITH RECURSIVE numbers(n) AS (
            SELECT 0 UNION ALL SELECT n + 1 FROM numbers WHERE n < 100
        )
        SELECT CASE WHEN n < 100 THEN 1 ELSE X'FF' END AS value
        FROM numbers ORDER BY n
    """
    engine = SQLAlchemyEngine(empty_sqlite_engine)
    with mock.patch.object(
        engine, "sql_output_format", return_value=output_format
    ):
        if output_format == "auto":
            result = engine.execute(query)
            assert_frame_equal(
                result, pd.DataFrame({"value": [1] * 100 + [b"\xff"]})
            )
        else:
            with pytest.raises(
                pl.exceptions.ComputeError, match="infer_schema_length"
            ) as exc_info:
                engine.execute(query)
            assert isinstance(
                exc_info.value.__cause__, pl.exceptions.SchemaError
            )


# ------------------------
# Decoracotors
# ------------------------


def test_returns_function_result_on_success():
    @safe_execute(fallback=None)
    def add(a, b):
        return a + b

    assert add(1, 2) == 3


def test_returns_fallback_on_exception():
    @safe_execute(fallback="oops")
    def fail():
        raise ValueError("boom")

    assert fail() == "oops"


@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_logs_on_exception(mock_logger):
    @safe_execute(
        fallback=None, message="something broke", log_level="warning"
    )
    def fail():
        raise TypeError("bad type")

    fail()

    mock_logger.warning.assert_called_once_with(
        "something broke", exc_info=True
    )


@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_respects_log_level_debug(mock_logger):
    @safe_execute(fallback=None, message="debug msg", log_level="debug")
    def fail():
        raise RuntimeError

    fail()

    mock_logger.debug.assert_called_once_with("debug msg", exc_info=True)


@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_respects_log_level_error(mock_logger):
    @safe_execute(fallback=None, message="error msg", log_level="error")
    def fail():
        raise RuntimeError

    fail()

    mock_logger.error.assert_called_once_with("error msg", exc_info=True)


@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_silent_exceptions_no_logging(mock_logger):
    @safe_execute(
        fallback=[],
        silent_exceptions=(NotImplementedError,),
    )
    def fail():
        raise NotImplementedError

    result = fail()

    assert result == []
    mock_logger.warning.assert_not_called()
    mock_logger.debug.assert_not_called()
    mock_logger.error.assert_not_called()
    mock_logger.info.assert_not_called()


@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_silent_exceptions_still_logs_other_errors(mock_logger):
    @safe_execute(
        fallback=-1,
        message="caught",
        log_level="error",
        silent_exceptions=(NotImplementedError,),
    )
    def fail():
        raise ValueError("not silent")

    result = fail()

    assert result == -1
    mock_logger.error.assert_called_once_with("caught", exc_info=True)


def test_preserves_function_metadata():
    @safe_execute(fallback=None)
    def my_func():
        """My docstring."""

    assert my_func.__name__ == "my_func"
    assert my_func.__doc__ == "My docstring."


def test_passes_args_and_kwargs():
    @safe_execute(fallback=None)
    def greet(name, greeting="Hello"):
        return f"{greeting}, {name}!"

    assert greet("Alice") == "Hello, Alice!"
    assert greet("Bob", greeting="Hi") == "Hi, Bob!"


def test_fallback_can_be_any_type():
    for fallback in (0, False, "", [], {}):

        @safe_execute(fallback=fallback)
        def fail():
            raise RuntimeError

        assert fail() == fallback


# ------------------------
# Tests for inspector methods
# ------------------------


@pytest.fixture
def make_engine():
    """Factory to create a SQLAlchemyEngine with a mocked Snowflake inspector."""

    def _factory(
        *,
        dialect="snowflake",
        default_database="MY_DB",
        schema_names=None,
        table_names=None,
        view_names=None,
        table_columns=None,
        columns=None,
        rows=None,
        pk_constraint=None,
        indexes=None,
        multi_columns=None,
        multi_pk_constraint=None,
        multi_indexes=None,
        supports_multi=True,
        inspector=True,
    ):
        if columns is None:
            columns = [
                "created_on",
                "name",
                "is_default",
                "is_current",
                "origin",
            ]
        if rows is None:
            rows = []

        # -- Mock query results --
        mock_result = mock.MagicMock()
        mock_result.keys.return_value = columns
        mock_result.fetchall.return_value = rows

        mock_conn = mock.MagicMock()
        mock_conn.execute.return_value = mock_result

        # -- Mock inspector --
        mock_inspector = None
        if inspector:
            mock_inspector = mock.MagicMock()
            mock_inspector.get_schema_names.return_value = schema_names or []
            mock_inspector.get_table_names.return_value = table_names or []
            mock_inspector.get_view_names.return_value = view_names or []
            mock_inspector.get_columns.return_value = table_columns or []
            mock_inspector.get_pk_constraint.return_value = pk_constraint or {
                "constrained_columns": []
            }
            mock_inspector.get_indexes.return_value = indexes or []
            if supports_multi:
                # Empty dicts by default so tests exercise the per-table
                # fallback instead of passing on bare MagicMock returns.
                mock_inspector.get_multi_columns.return_value = (
                    multi_columns or {}
                )
                mock_inspector.get_multi_pk_constraint.return_value = (
                    multi_pk_constraint or {}
                )
                mock_inspector.get_multi_indexes.return_value = (
                    multi_indexes or {}
                )
            else:
                # Simulate SQLAlchemy < 2.0, where hasattr(...) is False
                del mock_inspector.get_multi_columns
                del mock_inspector.get_multi_pk_constraint
                del mock_inspector.get_multi_indexes

        # -- Mock SQLAlchemy engine --
        mock_sa_engine = mock.MagicMock()
        mock_sa_engine.dialect.name = dialect
        mock_sa_engine.url.database = default_database
        mock_sa_engine.connect.return_value = mock.MagicMock(
            __enter__=mock.MagicMock(return_value=mock_conn),
            __exit__=mock.MagicMock(return_value=False),
        )

        # Patch inspect() so __init__ assigns our configured mock
        with mock.patch("sqlalchemy.inspect", return_value=mock_inspector):
            engine = SQLAlchemyEngine(
                mock_sa_engine, engine_name=VariableName("test")
            )

        # Point _connection to the mock so execute() calls work
        engine._connection = mock_sa_engine

        # Override _get_inspector to yield our mock inspector.
        # Uses *_args, **_kwargs to match any signature the real
        # method may have (e.g. database parameter) without
        # global side effects.
        inspector_contexts = []

        @contextmanager
        def fake_get_inspector(database=None):
            inspector_contexts.append(database)
            yield mock_inspector

        engine._get_inspector = fake_get_inspector

        # Expose mocks for test assertions
        engine._mock_inspector = mock_inspector
        engine._mock_conn = mock_conn
        engine._mock_inspector_contexts = inspector_contexts

        return engine

    return _factory


# ------------------------------------------------------------------ #
#  _get_inspector
# ------------------------------------------------------------------ #


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_inspector_executes_use_database():
    """executes dialect command and inspects the connection."""
    mock_conn = mock.MagicMock()
    mock_command = mock.MagicMock()

    mock_sa_engine = mock.MagicMock()
    mock_sa_engine.dialect.name = "snowflake"
    mock_sa_engine.url.database = "MY_DB"
    mock_sa_engine.connect.return_value.__enter__ = mock.MagicMock(
        return_value=mock_conn
    )
    mock_sa_engine.connect.return_value.__exit__ = mock.MagicMock(
        return_value=False
    )

    engine = SQLAlchemyEngine(mock_sa_engine, engine_name=VariableName("test"))

    with (
        mock.patch(
            "sqlalchemy.inspect", return_value=mock_command
        ) as patched_inspect,
        engine._get_inspector("my_db") as inspector,
    ):
        assert inspector is mock_command
        patched_inspect.assert_called_once_with(mock_conn)

    # Verify USE DATABASE was executed
    executed = mock_conn.execute.call_args[0][0]
    assert str(executed) == "USE DATABASE my_db"

    with (
        mock.patch(
            "sqlalchemy.inspect", return_value=mock_command
        ) as patched_inspect,
        engine._get_inspector("MY_DB@MYDB") as inspector,
    ):
        assert inspector is mock_command
        patched_inspect.assert_called_once_with(mock_conn)

    # Verify USE DATABASE was executed
    executed = mock_conn.execute.call_args[0][0]
    assert str(executed) == 'USE DATABASE "MY_DB@MYDB"'

    with (
        mock.patch(
            "sqlalchemy.inspect", return_value=mock_command
        ) as patched_inspect,
        engine._get_inspector("MY_db") as inspector,
    ):
        assert inspector is mock_command
        patched_inspect.assert_called_once_with(mock_conn)

    # Verify USE DATABASE was executed
    executed = mock_conn.execute.call_args[0][0]
    assert str(executed) == 'USE DATABASE "MY_db"'


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_inspector_yields_existing_inspector_for_non_use_database_dialect():
    """For dialects without USE DATABASE, yield self.inspector directly."""
    mock_sa_engine = mock.MagicMock()
    mock_sa_engine.dialect.name = "sqlite"
    mock_sa_engine.url.database = ":memory:"

    engine = SQLAlchemyEngine(mock_sa_engine, engine_name=VariableName("test"))

    mock_inspector = mock.MagicMock()
    engine.inspector = mock_inspector

    with engine._get_inspector("any_database") as inspector:
        assert inspector is mock_inspector

    # Verify that NO connection was opened and no command was executed
    mock_sa_engine.connect.assert_not_called()


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_tables_in_schema_reuses_connection():
    import sqlalchemy as sa

    mock_inspector = mock.MagicMock()
    mock_inspector.get_table_names.return_value = ["users"]
    mock_inspector.get_view_names.return_value = []
    mock_inspector.get_multi_columns.return_value = {
        ("public", "users"): [{"name": "id", "type": sa.INTEGER()}]
    }
    mock_inspector.get_multi_pk_constraint.return_value = {
        ("public", "users"): {"constrained_columns": ["id"]}
    }
    mock_inspector.get_multi_indexes.return_value = {("public", "users"): []}

    mock_connection = mock.MagicMock()
    mock_sa_engine = mock.MagicMock()
    mock_sa_engine.dialect.name = "snowflake"
    mock_sa_engine.url.database = "MY_DB"
    mock_sa_engine.connect.return_value.__enter__.return_value = (
        mock_connection
    )

    with mock.patch("sqlalchemy.inspect", return_value=mock_inspector):
        engine = SQLAlchemyEngine(
            mock_sa_engine, engine_name=VariableName("test")
        )
        tables = engine.get_tables_in_schema(
            schema="public",
            database="MY_DB",
            include_table_details=True,
        )

    assert [table.name for table in tables] == ["users"]
    mock_sa_engine.connect.assert_called_once_with()
    executed = mock_connection.execute.call_args[0][0]
    assert str(executed) == 'USE DATABASE "MY_DB"'


# ------------------------------------------------------------------ #
#  _get_schema_names
# ------------------------------------------------------------------ #


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_schema_names(make_engine):
    engine = make_engine(schema_names=["public", "staging"])
    assert engine._get_schema_names("MY_DB") == ["public", "staging"]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_schema_names_no_inspector(make_engine):
    engine = make_engine(inspector=False)
    assert engine._get_schema_names("MY_DB") == []


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_table_details_reuses_inspector_context(make_engine):
    import sqlalchemy as sa

    engine = make_engine(
        table_columns=[{"name": "id", "type": sa.INTEGER()}],
        pk_constraint={"constrained_columns": ["id"]},
    )

    table = engine.get_table_details(
        table_name="users",
        schema_name="public",
        database_name="MY_DB",
    )

    assert table is not None
    assert table.primary_keys == ["id"]
    assert len(engine._mock_inspector_contexts) == 1


# ------------------------------------------------------------------ #
#  _get_batched_table_details
# ------------------------------------------------------------------ #


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_uses_multi_reflection(make_engine):
    """Details come from get_multi_* calls on a single inspector."""
    import sqlalchemy as sa
    from sqlalchemy.engine.reflection import ObjectKind, ObjectScope

    engine = make_engine(
        table_names=["users"],
        view_names=["active_users"],
        multi_columns={
            ("public", "users"): [{"name": "id", "type": sa.INTEGER()}],
            ("public", "active_users"): [{"name": "id", "type": sa.INTEGER()}],
        },
        multi_pk_constraint={
            ("public", "users"): {"constrained_columns": ["id"]}
        },
        multi_indexes={
            ("public", "users"): [{"column_names": ["id"], "name": "idx"}]
        },
    )
    tables = engine.get_tables_in_schema(
        schema="public", database="MY_DB", include_table_details=True
    )

    assert [(t.name, t.type) for t in tables] == [
        ("users", "table"),
        ("active_users", "view"),
    ]
    assert tables[0].primary_keys == ["id"]
    assert tables[0].indexes == ["id"]
    # Missing PK/index entries fall back to [] like per-table reflection
    assert tables[1].primary_keys == []
    assert tables[1].indexes == []

    engine._mock_inspector.get_multi_columns.assert_called_once_with(
        schema="public",
        filter_names=["users", "active_users"],
        kind=ObjectKind.ANY,
        scope=ObjectScope.ANY,
    )
    for multi_method in (
        engine._mock_inspector.get_multi_pk_constraint,
        engine._mock_inspector.get_multi_indexes,
    ):
        multi_method.assert_called_once_with(
            schema="public",
            filter_names=["users"],
            kind=ObjectKind.ANY,
            scope=ObjectScope.ANY,
        )
    engine._mock_inspector.get_columns.assert_not_called()
    engine._mock_inspector.get_pk_constraint.assert_not_called()
    engine._mock_inspector.get_indexes.assert_not_called()
    assert len(engine._mock_inspector_contexts) == 1


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_without_multi_support(make_engine):
    """Inspectors without get_multi_* fall back per table, silently."""
    import sqlalchemy as sa

    engine = make_engine(
        table_names=["users"],
        table_columns=[{"name": "id", "type": sa.INTEGER()}],
        pk_constraint={"constrained_columns": ["id"]},
        supports_multi=False,
    )
    with mock.patch("marimo._sql.engines.sqlalchemy.LOGGER") as mock_logger:
        tables = engine.get_tables_in_schema(
            schema="public", database="MY_DB", include_table_details=True
        )

    assert [(t.name, t.type) for t in tables] == [("users", "table")]
    assert tables[0].primary_keys == ["id"]
    engine._mock_inspector.get_columns.assert_called_once_with(
        "users", schema="public"
    )
    mock_logger.warning.assert_not_called()


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_isolates_metadata_failures(make_engine):
    """Bulk metadata failures reuse batched columns and the same inspector."""
    import sqlalchemy as sa

    engine = make_engine(
        table_names=["users"],
        view_names=["active_users"],
        multi_columns={
            ("public", "users"): [{"name": "id", "type": sa.INTEGER()}],
            ("public", "active_users"): [{"name": "id", "type": sa.INTEGER()}],
        },
        pk_constraint={"constrained_columns": ["id"]},
        indexes=[{"column_names": ["id"], "name": "idx"}],
    )
    engine._mock_inspector.get_multi_pk_constraint.side_effect = RuntimeError
    engine._mock_inspector.get_multi_indexes.side_effect = RuntimeError

    tables = engine.get_tables_in_schema(
        schema="public", database="MY_DB", include_table_details=True
    )

    assert [(table.name, table.type) for table in tables] == [
        ("users", "table"),
        ("active_users", "view"),
    ]
    assert tables[0].primary_keys == ["id"]
    assert tables[0].indexes == ["id"]
    assert tables[1].primary_keys == []
    assert tables[1].indexes == []
    engine._mock_inspector.get_columns.assert_not_called()
    engine._mock_inspector.get_pk_constraint.assert_called_once_with(
        "users", schema="public"
    )
    engine._mock_inspector.get_indexes.assert_called_once_with(
        "users", schema="public"
    )
    assert len(engine._mock_inspector_contexts) == 1


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_straggler_falls_back_per_table(make_engine):
    """A table missing from the batch is fetched per table."""
    import sqlalchemy as sa

    engine = make_engine(
        table_names=["users", "orders"],
        table_columns=[{"name": "id", "type": sa.INTEGER()}],
        multi_columns={
            ("public", "users"): [{"name": "id", "type": sa.INTEGER()}]
        },
    )
    tables = engine.get_tables_in_schema(
        schema="public", database="MY_DB", include_table_details=True
    )

    assert [t.name for t in tables] == ["users", "orders"]
    engine._mock_inspector.get_columns.assert_called_once_with(
        "orders", schema="public"
    )
    assert len(engine._mock_inspector_contexts) == 1


# ------------------------------------------------------------------ #
#  _index_by_table_name (module-level, no mocking needed)
# ------------------------------------------------------------------ #


def test_index_by_table_name():
    reflected = {
        ("main", "users"): [{"name": "id"}],
        (None, "orders"): [{"name": "total"}],
    }
    assert _index_by_table_name(reflected) == {
        "users": [{"name": "id"}],
        "orders": [{"name": "total"}],
    }


def test_index_by_table_name_empty():
    assert _index_by_table_name({}) == {}


# ------------------------------------------------------------------ #
#  _extract_index_columns (static, no mocking needed)
# ------------------------------------------------------------------ #


def test_extract_index_columns_deduplicates():
    indexes = [
        {"column_names": ["col_a", "col_b"]},
        {"column_names": ["col_b", "col_c"]},
    ]
    assert SQLAlchemyEngine._extract_index_columns(indexes) == [
        "col_a",
        "col_b",
        "col_c",
    ]


def test_extract_index_columns_skips_none():
    indexes = [{"column_names": [None, "col_a", None]}]
    assert SQLAlchemyEngine._extract_index_columns(indexes) == ["col_a"]


def test_extract_index_columns_missing_key():
    indexes = [{"name": "idx"}, {"column_names": ["col_a"]}]
    assert SQLAlchemyEngine._extract_index_columns(indexes) == ["col_a"]


def test_extract_index_columns_empty():
    assert SQLAlchemyEngine._extract_index_columns([]) == []


# ---------------------------------
# Snowflake-specific GET databases
# ---------------------------------


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_all_in_lower_case_when_no_default(make_engine):
    engine = make_engine(
        default_database=None,
        rows=[
            ("2025-01-01", "DB_A", "N", "N", ""),
            ("2025-01-02", "DB_B", "N", "N", ""),
        ],
    )
    assert engine._get_snowflake_database_names() == ["db_a", "db_b"]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_only_default_when_present(make_engine):
    engine = make_engine(
        default_database="db_B",
        rows=[
            ("2025-01-01", "DB_A", "N", "N", ""),
            ("2025-01-02", "DB_B", "N", "Y", ""),
        ],
    )
    assert engine._get_snowflake_database_names() == ["db_b"]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_snowflake_database_names_lowercases_unless_special_characters(
    make_engine,
):
    engine = make_engine(
        default_database=None,
        rows=[
            ("2025-01-01", "DB_A", "N", "N", ""),
            ("2025-01-02", "DB_B", "N", "Y", ""),
            ("2025-01-02", "USER@snow", "N", "Y", ""),
        ],
    )
    assert engine._get_snowflake_database_names() == [
        "db_a",
        "db_b",
        "USER@snow",
    ]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_all_when_default_not_in_results(make_engine):
    engine = make_engine(
        default_database="MISSING",
        rows=[
            ("2025-01-01", "DB_A", "N", "N", ""),
            ("2025-01-02", "DB_B", "N", "Y", ""),
        ],
    )
    assert engine._get_snowflake_database_names() == ["db_a", "db_b"]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_empty_list_when_no_rows(make_engine):
    engine = make_engine(rows=[])
    assert engine._get_snowflake_database_names() == []


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_raises_when_name_column_missing(make_engine):
    engine = make_engine(columns=["id", "owner", "comment"])
    with pytest.raises(RuntimeError, match="'name' column not found"):
        engine._get_snowflake_database_names()


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_executes_show_databases(make_engine):
    engine = make_engine()
    engine._get_snowflake_database_names()
    executed_sql = engine._mock_conn.execute.call_args[0][0]
    assert str(executed_sql) == "SHOW DATABASES"


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_name_column_at_different_index(make_engine):
    engine = make_engine(
        columns=["id", "owner", "name"],
        rows=[(1, "ADMIN", "MY_DB")],
    )
    assert engine._get_snowflake_database_names() == ["my_db"]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_database_names_coerced_to_str(make_engine):
    engine = make_engine(columns=["name"], rows=[(123,), (456,)])
    assert engine._get_snowflake_database_names() == ["123", "456"]


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_get_database_names_delegates(make_engine):
    """Snowflake dialect delegates to _get_snowflake_database_names."""
    engine = make_engine(default_database="MY_DB")

    expected = ["DB_A", "DB_B"]
    with mock.patch.object(
        engine, "_get_snowflake_database_names", return_value=expected
    ) as mocked:
        result = engine._get_database_names()

    mocked.assert_called_once()
    assert result == expected


@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_default_database_queried_when_not_in_url(make_engine):
    """Snowflake falls back to querying the current database when the
    connection URL has no database (e.g. engines built with `creator=`)."""
    engine = make_engine(default_database=None)
    engine._mock_conn.execute.return_value.fetchone.return_value = ("MY_DB",)

    assert engine.get_default_database() == "MY_DB"
    executed_sql = engine._mock_conn.execute.call_args[0][0]
    assert str(executed_sql) == "SELECT current_database()"