This is an automated email from the ASF dual-hosted git repository.
Lee-W pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/airflow.git
The following commit(s) were added to refs/heads/main by this push:
new 35eedb3749b Let SQLToolset agents see table comments and search large
schemas (#76799)
35eedb3749b is described below
commit 35eedb3749bb54047b1a5a329126a5104f8a7bef
Author: Wei Lee <[email protected]>
AuthorDate: Wed Nov 18 00:39:07 2026 +0800
Let SQLToolset agents see table comments and search large schemas (#76799)
---
providers/common/ai/docs/agent_security.rst | 5 +-
.../ai/docs/toolsets/progressive_disclosure.rst | 3 +-
providers/common/ai/docs/toolsets/sql.rst | 232 ++++++++++-
providers/common/ai/pyproject.toml | 4 +-
.../airflow/providers/common/ai/toolsets/sql.py | 203 ++++++++-
.../providers/common/ai/utils/query_results.py | 86 +++-
.../providers/common/ai/utils/sql_search.py | 85 ++++
.../unit/common/ai/toolsets/test_datafusion.py | 5 +
.../ai/tests/unit/common/ai/toolsets/test_sql.py | 452 ++++++++++++++++++++-
.../unit/common/ai/utils/test_query_results.py | 101 +++++
.../tests/unit/common/ai/utils/test_sql_search.py | 70 ++++
.../src/airflow/providers/common/sql/hooks/sql.py | 35 ++
.../src/airflow/providers/common/sql/hooks/sql.pyi | 4 +
.../sql/tests/unit/common/sql/hooks/test_sql.py | 60 +++
14 files changed, 1303 insertions(+), 42 deletions(-)
diff --git a/providers/common/ai/docs/agent_security.rst
b/providers/common/ai/docs/agent_security.rst
index 9ce4a350a7f..9fe73ac7779 100644
--- a/providers/common/ai/docs/agent_security.rst
+++ b/providers/common/ai/docs/agent_security.rst
@@ -186,7 +186,10 @@ How ``allowed_tables`` is enforced
When ``allowed_tables`` is set it governs every tool, not just discovery:
-- ``list_tables`` and ``get_schema`` only reveal listed tables.
+- ``list_tables``, ``get_schema`` and, when a ``search_backend`` is set,
``search_tables``
+ only reveal listed tables. With ``allowed_tables`` unset, ``search_tables``
results are not filtered,
+ so a standalone backend indexed with a broader role than the agent's
connection can leak
+ table names and comments; see :ref:`the identity warning
<sql-toolset-search-identity>`.
- ``query`` and ``check_query`` parse the SQL with `sqlglot
<https://github.com/tobymao/sqlglot>`_ and reject it before execution if it
references any table that is not on the list. Tables reached indirectly are
diff --git a/providers/common/ai/docs/toolsets/progressive_disclosure.rst
b/providers/common/ai/docs/toolsets/progressive_disclosure.rst
index c27c77ddac0..0978bf88199 100644
--- a/providers/common/ai/docs/toolsets/progressive_disclosure.rst
+++ b/providers/common/ai/docs/toolsets/progressive_disclosure.rst
@@ -138,7 +138,8 @@ When it does not help
answer almost anything, so hiding them saves a few definitions and adds a
search request
to nearly every run. On a warehouse with hundreds of tables, ``list_tables``
output grows with the table count; limit it with ``allowed_tables`` (see
-:ref:`sql-toolset-restricted`).
+:ref:`sql-toolset-restricted`), or pass a ``search_backend`` so the agent
finds tables with
+``search_tables`` instead of reading the whole list (see
:ref:`sql-toolset-table-search`).
``AgentSkillsToolset`` has four tools, and its list of skills goes into the
agent's
instructions, which ``.defer_loading()`` does not touch, so the part of its
cost that grows
diff --git a/providers/common/ai/docs/toolsets/sql.rst
b/providers/common/ai/docs/toolsets/sql.rst
index 914451fb2d2..0ba86a6ac03 100644
--- a/providers/common/ai/docs/toolsets/sql.rst
+++ b/providers/common/ai/docs/toolsets/sql.rst
@@ -19,7 +19,8 @@ SQL databases: ``SQLToolset``
=============================
Curated toolset wrapping
-:class:`~airflow.providers.common.sql.hooks.sql.DbApiHook` with four tools:
+:class:`~airflow.providers.common.sql.hooks.sql.DbApiHook` with four tools,
and a fifth,
+``search_tables``, when you pass a ``search_backend``:
.. list-table::
:header-rows: 1
@@ -28,10 +29,16 @@ Curated toolset wrapping
* - Tool
- Description
* - ``list_tables``
- - Lists available table names (filtered by ``allowed_tables`` if set)
+ - Lists available table names (filtered by ``allowed_tables`` if set).
With a
+ ``search_backend``, returns only a count once there are more than
+ ``max_listed_tables`` (see :ref:`sql-toolset-table-search`)
* - ``get_schema``
- - Returns a table's columns as JSON, with a ``name_contains`` filter and a
- bounded summary on very wide tables (see :ref:`bounded-schema-results`)
+ - Returns a table's columns as JSON, with table and column comments where
the
+ database has them, a ``name_contains`` filter and a bounded summary on
very
+ wide tables (see :ref:`bounded-schema-results`)
+ * - ``search_tables``
+ - Only with ``search_backend``. Finds tables by a free-text query over
table names
+ and comments (see :ref:`sql-toolset-table-search`)
* - ``query``
- Executes a SQL query and returns bounded, columnar JSON (see
:ref:`bounded-query-results`)
@@ -337,9 +344,223 @@ Parameters
Must be a valid Python identifier. Set it when one agent has another toolset
with the
same tool names, since duplicate tool names are rejected. A function passed
to
``.approval_required()`` sees these prefixed names; see ``allow_writes``
above.
+- ``include_table_comments``: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names; ``comment``
is
+ present only for tables that have one. Default ``False``. ``get_schema``
returns
+ comments regardless. Without a ``search_backend`` the list has no size
limit: each
+ comment is capped at 200 characters, but the total is not bound by
``max_result_bytes``.
+ See :ref:`sql-toolset-comments`.
+- ``search_backend``: Registers the ``search_tables`` tool. Default ``None``,
no search
+ tool. See :ref:`sql-toolset-table-search`.
+- ``max_listed_tables``: With a ``search_backend``, the number of tables above
which
+ ``list_tables`` stops listing and points at ``search_tables``. Default
``100``. No
+ effect without a ``search_backend``.
- ``max_retries``: How many times the model may correct a failed call to these
tools. Default ``None``, the agent's ``retries``. See
:ref:`toolset-retry-budget`.
+.. _sql-toolset-comments:
+
+Table and column comments
+-------------------------
+
+A column named ``amt_adj_2`` tells an agent little; the comment the database
holds for
+it often does. ``get_schema`` returns ``table_comment`` and a ``comment`` on
each column
+where the database reports one. This is what it returned for a ``customers``
table with
+comments, on PostgreSQL (the ``email`` and ``country_code`` columns have
comments, the
+others do not):
+
+.. code-block:: json
+
+
{"columns":[{"name":"customer_id","type":"INTEGER"},{"name":"email","type":"TEXT","comment":"Login
address, unique per
customer"},{"name":"full_name","type":"TEXT"},{"name":"created_at","type":"TIMESTAMP"},{"name":"country_code","type":"CHAR(2)","comment":"ISO
3166-1 alpha-2"}],"column_count":5,"table_comment":"People who have an account
in the shop."}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``. On a dialect that cannot report
comments
+(SQLite, for one), ``get_schema`` works as before and returns none. A failure
reading the
+columns fails the call. A failure reading the *table* comment, of any kind, a
permission
+error included, is logged at debug level and ``get_schema`` returns the
columns without
+``table_comment``.
+
+Each comment is cut to 200 characters. Comments share ``max_result_bytes``
with the
+columns, so a wide table with long comments can cross the budget where the
same table
+without them would not. When the columns fit but their comments do not,
``get_schema``
+returns the full column list without the column comments and says so with
``comments_omitted``.
+Only when the columns alone do not fit is the result summarized (see
+:ref:`bounded-schema-results`), and the summary leaves the column comments out
too. In both
+cases the agent gets the comments back by asking for the columns it needs with
``name_contains``.
+
+``include_table_comments=True`` adds the table comments to ``list_tables``,
and the default
+search backend reads the same comments to build its catalog. Both call the
Inspector's
+``get_multi_table_comment`` once per schema. When ``allowed_tables`` is set,
only the allowed
+tables' comments are requested. What that costs depends on the dialect. Where
the dialect does
+not implement ``get_multi_table_comment`` itself, SQLAlchemy's default reads
the comments one
+table at a time.
+
+On PostgreSQL 14 with SQLAlchemy 2.0, one ``list_tables`` call with
+``include_table_comments=True`` on a schema of ten tables issued two SQL
statements, counted
+with SQLAlchemy's ``before_cursor_execute`` event: one listing the table names
and one
+reading the comments of all the tables. Snowflake and BigQuery were not run.
Reading their
+SQLAlchemy dialects' source suggests they do not implement
``get_multi_table_comment``, so
+there each table whose comment is read would cost one query, or one API call
on BigQuery, and a
+schema with many tables would make this slow. On such databases leave
+``include_table_comments`` off for large schemas, or pass a custom
``search_backend`` that
+builds its catalog with one dialect-specific query, for example against
+``INFORMATION_SCHEMA`` (see :ref:`sql-toolset-custom-search-backend`). If the
comments cannot
+be read, for example because the role cannot see the comment catalog, tables
are returned
+without them. A lost connection is still reported as a connection error. The
catalog the
+search backend reads is built once per toolset instance (see
+:ref:`sql-toolset-table-search`), so the default search backend will not look
for comments
+again.
+
+SQLAlchemy 1.4 has no ``get_multi_table_comment``, so there ``list_tables``
returns tables without
+comments rather than reading them table by table. The default search backend
then has no comments
+to match, and a query whose words appear only in comments returns nothing;
matching on table
+names still works.
+
+.. _sql-toolset-table-search:
+
+Finding tables in a large warehouse
+-----------------------------------
+
+Listing every table stops working once there are hundreds. Pass a
``search_backend`` and the
+agent gets a ``search_tables`` tool that takes a free-text ``query`` and an
optional
+``limit`` (default 10, at most 50) and returns matching tables with their
comments. Once
+there are more than ``max_listed_tables`` tables, ``list_tables`` returns only
their number
+and a pointer to ``search_tables`` instead of the whole list. Without a
``search_backend``
+``list_tables`` is unchanged, however many tables there are.
+
+.. code-block:: python
+
+ from airflow.providers.common.ai.toolsets.sql import SQLToolset
+ from airflow.providers.common.ai.utils.sql_search import
KeywordTableSearchBackend
+
+ toolset = SQLToolset(
+ db_conn_id="warehouse",
+ schema="analytics",
+ search_backend=KeywordTableSearchBackend(),
+ )
+
+The outputs below come from a run on PostgreSQL with ten tables. That run also
set
+``include_table_comments=True`` and ``max_listed_tables=5``, which the block
above leaves at
+their defaults (``False`` and 100), so with the block as written
``list_tables`` would list all
+ten tables. With those two arguments set, ``list_tables`` returned:
+
+.. code-block:: json
+
+ {"table_count": 10, "hint": "There are 10 tables, too many to list (the
limit is 5). Use search_tables to find the ones you need."}
+
+and ``search_tables`` with the query ``parcels carrier`` returned:
+
+.. code-block:: json
+
+ {"tables": [{"name": "shipments", "comment": "Parcels handed to a carrier
for an order."}]}
+
+A query that matches nothing returns ``{"tables": []}``. Comments are cut to
200
+characters in these results, the same as in ``list_tables``.
+
+``KeywordTableSearchBackend`` splits the query on whitespace and matches each
word,
+case-insensitively, as a substring of the table name and of the table's
comment. For a
+``SCHEMA.TABLE`` name only the table part is matched, not the schema. Column
comments are
+not searched, and a word is not stemmed: a query for ``returned`` does not
match a table
+named ``returns``. A word found in the name counts three times a word found in
the comment;
+tables matching nothing are dropped and ties are ordered by name.
+
+The toolset builds the table catalog the first time a backend asks for it and
keeps it for
+the lifetime of the toolset instance. This holds for any backend that calls
``get_catalog``,
+the default one included, so a table created after the first search does not
show up until
+the toolset is built again.
+
+When ``allowed_tables`` is set, every result is checked against it before the
agent sees it,
+whichever backend produced it, so a backend that knows about more tables than
the agent may
+use cannot widen access. When ``allowed_tables`` is unset (the default,
allow-all), the
+toolset filters nothing: a backend indexed with a broader role than the
agent's connection can
+show the agent table names and comments that connection cannot see. In that
case the
+:ref:`warning below <sql-toolset-search-identity>` is the only control.
+
+.. _sql-toolset-search-identity:
+
+.. warning::
+
+ Introspection must run as the same identity the agent queries with, because
+ ``INFORMATION_SCHEMA`` and the equivalent catalogs show a role only what
that role can see.
+ The default path is correct by construction: the toolset introspects
through the same
+ ``db_conn_id`` it queries with. A standalone backend, an external vector
store for
+ instance, is responsible for building its index with that identity or
equivalent
+ privileges. Otherwise it can surface tables the agent cannot use, or miss
tables it can.
+
+.. _sql-toolset-custom-search-backend:
+
+Plugging in another backend
+^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+A backend is any object with a ``search_tables(query, limit, *, get_catalog)``
method
+returning ``(name, comment)`` pairs, best match first (see
+:class:`~airflow.providers.common.ai.utils.sql_search.SQLTableSearchBackend`).
Names
+must be in the form ``list_tables`` returns -- bare for the default schema,
+``SCHEMA.TABLE`` otherwise. A backend with its own index, such as a vector
store, a search
+service or a retriever over table descriptions, ignores ``get_catalog`` and
queries that
+index. This provider ships no such backend; integration code is yours to write
and test
+against your own store.
+
+``get_catalog`` is how the default backend reads comments, through
SQLAlchemy's reflection; on a
+dialect without ``get_multi_table_comment`` that costs one query per table. A
backend that avoids
+it has to reach the database itself. This example resolves a hook from the
same ``db_conn_id``
+the toolset queries with and runs one query against the PostgreSQL catalogs.
The query reads
+PostgreSQL's ``pg_class`` and ``pg_description``, the catalogs SQLAlchemy's
PostgreSQL dialect
+reads table comments from; adapt it to the catalog of your database:
+
+.. code-block:: python
+
+ from collections.abc import Callable, Sequence
+
+ from airflow.providers.common.ai.toolsets.sql import SQLToolset
+ from airflow.providers.common.ai.utils.sql_search import TableEntry
+ from airflow.providers.common.compat.sdk import BaseHook
+
+
+ class PostgresCatalogSearchBackend:
+ def __init__(self, db_conn_id: str, schema: str) -> None:
+ self._db_conn_id = db_conn_id
+ self._schema = schema
+
+ def search_tables(
+ self, query: str, limit: int, *, get_catalog: Callable[[],
Sequence[TableEntry]]
+ ) -> Sequence[TableEntry]:
+ hook = BaseHook.get_connection(self._db_conn_id).get_hook()
+ patterns = [f"%{word}%" for word in query.casefold().split()]
+ if not patterns:
+ return []
+ rows = hook.get_records(
+ "SELECT c.relname, d.description "
+ "FROM pg_catalog.pg_class c "
+ "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
+ "LEFT JOIN pg_catalog.pg_description d "
+ "ON d.objoid = c.oid AND d.objsubid = 0 "
+ "AND d.classoid = 'pg_catalog.pg_class'::regclass "
+ "WHERE n.nspname = %s AND c.relkind IN ('r', 'p', 'f') "
+ "AND (lower(c.relname) LIKE ANY (%s) OR lower(d.description)
LIKE ANY (%s)) "
+ "ORDER BY c.relname LIMIT %s",
+ parameters=(self._schema, patterns, patterns, limit),
+ )
+ return [(name, comment) for name, comment in rows]
+
+
+ toolset = SQLToolset(
+ db_conn_id="warehouse",
+ schema="analytics",
+ search_backend=PostgresCatalogSearchBackend("warehouse", "analytics"),
+ )
+
+This backend splits the query into words like the default one, but it is not
equivalent to it.
+``%`` and ``_`` in the agent's query act as ``LIKE`` wildcards (the values are
bound, so this
+is not injection), it does not weigh a name match above a comment match, and
it orders by table
+name.
+
+When ``allowed_tables`` is set, the toolset checks what such a backend returns
against it
+before the agent sees it, the same as for the default backend. With
``allowed_tables`` unset
+nothing is filtered, so the warning above applies: the backend queries with
whatever identity
+its hook's connection has. Comments the backend returns are masked for
registered secrets
+and cut to 200 characters before the agent sees them.
+
.. _bounded-query-results:
Bounded query results
@@ -422,7 +643,8 @@ summary that reports the shape and points at the filter:
``truncated_by`` is ``max_columns`` or ``max_result_bytes``.
``type_histogram`` counts
columns per type (capped to the most common, the tail folded into one entry),
and
``sample_columns`` previews the first columns -- both only while they fit the
budget, so
-a pathologically small budget still returns the ``column_count`` and ``hint``.
A filtered
+a pathologically small budget still returns the ``column_count`` and ``hint``.
Column comments count
+against the same budget (see :ref:`sql-toolset-comments`). A filtered
call echoes ``name_contains`` and adds ``total_columns`` so a subset is never
mistaken for
the whole table.
diff --git a/providers/common/ai/pyproject.toml
b/providers/common/ai/pyproject.toml
index 63c0ecde342..19eefd0cfd1 100644
--- a/providers/common/ai/pyproject.toml
+++ b/providers/common/ai/pyproject.toml
@@ -157,11 +157,11 @@ dependencies = [
"pyarrow>=22.0.0; python_version >= '3.14'",
]
"sql" = [
- "apache-airflow-providers-common-sql>=2.2.0",
+ "apache-airflow-providers-common-sql>=2.2.0", # use next version
"sqlglot>=30.0.0",
]
"common.sql" = [
- "apache-airflow-providers-common-sql>=2.2.0"
+ "apache-airflow-providers-common-sql>=2.2.0" # use next version
]
"langchain" = [
"langchain>=1.0.0",
diff --git
a/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
b/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
index 4c647276d24..b3918566cb6 100644
--- a/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
+++ b/providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py
@@ -18,6 +18,7 @@
from __future__ import annotations
+import logging
from contextlib import suppress
from typing import TYPE_CHECKING, Any
@@ -43,7 +44,7 @@ from pydantic_ai.tools import ToolDefinition
from pydantic_ai.toolsets.abstract import ToolsetTool
from airflow.providers.common.ai.exceptions import SQLToolsetConnectionError
-from airflow.providers.common.ai.utils.masking import dumps_masked
+from airflow.providers.common.ai.utils.masking import dumps_masked,
mask_secrets
from airflow.providers.common.ai.utils.query_results import (
DEFAULT_MAX_COLUMNS,
DEFAULT_MAX_RESULT_BYTES,
@@ -51,6 +52,7 @@ from airflow.providers.common.ai.utils.query_results import (
build_get_schema_description,
build_query_result,
build_schema_result,
+ truncate_comment,
)
from airflow.providers.common.ai.utils.tool_definition import
build_args_validator, return_schema_kwargs
from airflow.providers.common.ai.utils.tool_prefix import ToolPrefixMixin
@@ -63,6 +65,10 @@ if TYPE_CHECKING:
from pydantic_ai._run_context import RunContext
from sqlalchemy.engine.reflection import Inspector
+ from airflow.providers.common.ai.utils.sql_search import
SQLTableSearchBackend, TableEntry
+
+
+log = logging.getLogger(__name__)
# Sentinel distinguishing "caller did not pass ``allowed_tables``" (expose
every
# table) from an explicit value. Every explicit falsy value -- ``None`` as
much as
@@ -92,6 +98,26 @@ _GET_SCHEMA_SCHEMA: dict[str, Any] = {
"required": ["table_name"],
}
+_DEFAULT_SEARCH_LIMIT = 10
+_MAX_SEARCH_LIMIT = 50
+
+_SEARCH_TABLES_SCHEMA: dict[str, Any] = {
+ "type": "object",
+ "properties": {
+ "query": {
+ "type": "string",
+ "description": "Words describing the data you are looking for,
matched against table names and comments.",
+ },
+ "limit": {
+ "type": "integer",
+ "description": "Maximum number of tables to return (default 10, at
most 50).",
+ "minimum": 1,
+ "maximum": 50,
+ },
+ },
+ "required": ["query"],
+}
+
_QUERY_SCHEMA: dict[str, Any] = {
"type": "object",
"properties": {
@@ -307,6 +333,21 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
:param max_retries: How many times the model may correct a failed call to
one of these
tools before the run fails. ``None`` (the default) uses the agent's
tool retry
budget, its ``retries``, as pydantic-ai's own toolsets do.
+ :param include_table_comments: When ``True``, ``list_tables`` returns
+ ``{"name": ..., "comment": ...}`` objects instead of bare names, with
``comment``
+ present only for tables that have one (each capped to 200 characters).
Without a
+ ``search_backend`` the list has no size limit: the total is not bound
by
+ ``max_result_bytes``. Default ``False`` keeps the list of names.
``get_schema`` always
+ returns table and column comments where the database reports them,
regardless of this flag.
+ :param search_backend: Registers a ``search_tables`` tool that finds
tables by a free-text
+ query, for databases with too many tables to list. Pass
+
:class:`~airflow.providers.common.ai.utils.sql_search.KeywordTableSearchBackend`
for a
+ keyword match over table names and comments, or any object implementing
+
:class:`~airflow.providers.common.ai.utils.sql_search.SQLTableSearchBackend`.
Whatever it
+ returns is filtered through ``allowed_tables``. ``None`` (the default)
registers no search tool.
+ :param max_listed_tables: With a ``search_backend``, ``list_tables``
returns only the number of
+ tables and a pointer to ``search_tables`` once there are more than
this many. Default ``100``.
+ Without a ``search_backend`` there is no limit.
"""
# Rendered, on a copy, by AgentOperator. Deliberately not
``template_fields``, which
@@ -326,6 +367,9 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
max_columns: int = DEFAULT_MAX_COLUMNS,
tool_prefix: str = "",
max_retries: int | None = None,
+ include_table_comments: bool = False,
+ search_backend: SQLTableSearchBackend | None = None,
+ max_listed_tables: int = 100,
) -> None:
self._max_retries = validate_max_retries(max_retries)
self._set_tool_prefix(tool_prefix)
@@ -352,6 +396,10 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
self._max_rows = max_rows
self._max_result_bytes = max_result_bytes
self._max_columns = max_columns
+ self._include_table_comments = include_table_comments
+ self._search_backend = search_backend
+ self._max_listed_tables = max_listed_tables
+ self._catalog: list[TableEntry] | None = None
self._hook: DbApiHook | None = None
# Canonical ``(catalog, schema, table)`` view of allowed_tables for
membership
@@ -449,16 +497,30 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
max_retries = self._get_tool_max_retries(ctx)
tools: dict[str, ToolsetTool[Any]] = {}
- for base, description, schema in (
+ definitions = [
("list_tables", "List available table names in the database.",
_LIST_TABLES_SCHEMA),
(
"get_schema",
-
build_get_schema_description(tool_name=self._build_tool_name("get_schema")),
+ build_get_schema_description(
+ tool_name=self._build_tool_name("get_schema"),
include_comments=True
+ ),
_GET_SCHEMA_SCHEMA,
),
("query", _QUERY_DESCRIPTION, _QUERY_SCHEMA),
("check_query", "Validate SQL syntax without executing it.",
_CHECK_QUERY_SCHEMA),
- ):
+ ]
+ if self._search_backend is not None:
+ definitions.append(
+ (
+ "search_tables",
+ "Find tables by words describing the data you need,
matched against table names "
+ "and comments. Returns JSON of the form "
+ '{"tables": [{"name": ..., "comment": ...}, ...]}; pass a
returned name to '
+ f"{self._build_tool_name('get_schema')}.",
+ _SEARCH_TABLES_SCHEMA,
+ )
+ )
+ for base, description, schema in definitions:
# sequential=True keeps pydantic-ai from running these calls
concurrently
# within a turn; run_blocking's process-wide lock serializes them
with the
# blocking calls of the other toolsets that use it.
@@ -489,7 +551,7 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
tool: ToolsetTool[Any],
) -> Any:
base = self._find_base_tool_name(name)
- if base not in ("list_tables", "get_schema", "query", "check_query"):
+ if base not in self._get_tool_names():
raise ValueError(f"Unknown tool: {name!r}")
try:
return await self.run_blocking(self._run_tool, base, tool_args)
@@ -504,14 +566,23 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
# can fix its SQL within the run, bounded by the tool's
max_retries.
raise ModelRetry(
f"The {name} tool failed: {e}\n"
- f"Use the {self._build_tool_name('list_tables')} and
{self._build_tool_name('get_schema')} tools "
- "to inspect the database, "
- "then fix the query and try again."
+ f"Use the {' and '.join(self._build_tool_name(n) for n in
self._get_discovery_tool_names())} tools "
+ "to inspect the database, then fix the query and try again."
) from e
+ def _get_tool_names(self) -> tuple[str, ...]:
+ names = ("list_tables", "get_schema", "query", "check_query")
+ return (*names, "search_tables") if self._search_backend is not None
else names
+
+ def _get_discovery_tool_names(self) -> tuple[str, ...]:
+ names = ("list_tables", "get_schema")
+ return (*names, "search_tables") if self._search_backend is not None
else names
+
def _run_tool(self, name: str, tool_args: dict[str, Any]) -> str:
if name == "list_tables":
return self._list_tables()
+ if name == "search_tables":
+ return self._search_tables(tool_args["query"],
tool_args.get("limit"))
if name == "get_schema":
return self._get_schema(tool_args["table_name"],
tool_args.get("name_contains"))
if name == "query":
@@ -529,9 +600,10 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
return self._schema, table_name
return schema, table
- def _list_tables(self) -> str:
+ def _find_allowed_tables(self) -> list[tuple[str | None, str, str]]:
+ """Introspect the exposed schemas; return ``(schema, name, display
name)`` per allowed table."""
inspector = self._get_inspector()
- tables: list[str] = []
+ tables: list[tuple[str | None, str, str]] = []
# Dedupe by (schema, table) so a table reachable both qualified and
via the
# default schema (e.g. "public.users" and "users" with
schema="public") is
# listed once. Case-folded because databases reflect identifiers in
their case.
@@ -541,7 +613,7 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
key = self._canonical_ref("", schema, name)
if self._is_ref_allowed("", schema, name) and key not in seen:
seen.add(key)
- tables.append(display)
+ tables.append((schema, name, display))
# Schemas referenced by qualified allowed_tables entries: introspect
each
# and return matching tables fully qualified so they round-trip to
get_schema.
@@ -554,23 +626,122 @@ class SQLToolset(ToolPrefixMixin, AirflowToolset):
if self._include_default_schema:
for name in inspector.get_table_names(schema=self._schema):
add(self._schema, name, name)
+ return tables
+
+ def _list_tables(self) -> str:
+ tables = self._find_allowed_tables()
+ if self._search_backend is not None and len(tables) >
self._max_listed_tables:
+ return dumps_masked(
+ {
+ "table_count": len(tables),
+ "hint": (
+ f"There are {len(tables)} tables, too many to list
(the limit is "
+ f"{self._max_listed_tables}). Use
{self._build_tool_name('search_tables')} to find the ones you need."
+ ),
+ }
+ )
+ if not self._include_table_comments:
+ return dumps_masked([display for _, _, display in tables])
+ return dumps_masked(
+ [
+ {"name": name, **({"comment": truncate_comment(comment)} if
comment else {})}
+ for name, comment in self._get_table_entries(tables)
+ ]
+ )
+
+ def _get_table_entries(self, tables: list[tuple[str | None, str, str]]) ->
list[TableEntry]:
+ """Pair each table's display name with its full comment, registered
secrets masked, read once per schema."""
+ inspector = self._get_inspector()
+ names_by_schema: dict[str | None, list[str]] = {}
+ for table_schema, name, _ in tables:
+ names_by_schema.setdefault(table_schema, []).append(name)
+ # Names come from `tables` (as reflected), not from allowed_tables:
SQLAlchemy's
+ # filter_names match is case-sensitive while allowed_tables matching
is not.
+ comments = {
+ table_schema: self._get_table_comments(
+ inspector, table_schema, None if self._allowed_canonical is
None else names
+ )
+ for table_schema, names in names_by_schema.items()
+ }
+ # Full text so search sees the whole comment; masked because the
catalog is handed to `search_backend`.
+ return [
+ (display, mask_secrets(comment) if (comment :=
comments[table_schema].get(name)) else None)
+ for table_schema, name, display in tables
+ ]
+
+ def _get_catalog(self) -> list[TableEntry]:
+ """Return the exposed tables with comments, introspected on first use
and kept for this toolset."""
+ if self._catalog is None:
+ self._catalog =
self._get_table_entries(self._find_allowed_tables())
+ return self._catalog
+
+ def _search_tables(self, query: str, limit: int | None) -> str:
+ if self._search_backend is None:
+ raise ValueError("search_tables requires a search_backend")
+ # The schema's maximum is only advice to the model; the cap has to
hold here.
+ limit = min(max(_DEFAULT_SEARCH_LIMIT if limit is None else limit, 1),
_MAX_SEARCH_LIMIT)
+ found = self._search_backend.search_tables(query, limit,
get_catalog=self._get_catalog)
+ tables: list[dict[str, str]] = []
+ for display, comment in found:
+ schema, table = self._split_table_identifier(display)
+ if not self._is_ref_allowed("", schema, table):
+ continue
+ tables.append({"name": display, **({"comment":
truncate_comment(comment)} if comment else {})})
+ if len(tables) == limit:
+ break
+ return dumps_masked({"tables": tables})
+
+ @staticmethod
+ def _get_table_comments(
+ inspector: Inspector, schema: str | None, table_names: Sequence[str] |
None
+ ) -> dict[str, str]:
+ """
+ Return ``{table name: comment}`` for a schema in one call; empty when
the dialect has none.
- return dumps_masked(tables)
+ ``table_names`` limits the read to those tables; ``None`` reads the
whole schema.
+ """
+ # SQLAlchemy 1.4, which common-sql still supports, has no
get_multi_table_comment.
+ # Reading comments one table at a time instead would cost a query per
table, so
+ # on 1.4 the tables simply come back without comments.
+ if (get_comments := getattr(inspector, "get_multi_table_comment",
None)) is None:
+ return {}
+ try:
+ reflected = get_comments(schema=schema, filter_names=table_names)
+ except NotImplementedError:
+ return {}
+ except Exception as e:
+ # A lost connection is not degraded: the result is cached by the
search catalog.
+ if _is_lost_connection(e):
+ raise
+ # Unreadable comments (a role without access to the comment
catalog, a driver
+ # error) must not take the table names down with them.
+ log.debug("Could not read the table comments of schema %s",
schema, exc_info=True)
+ return {}
+ return {name: text for (_, name), info in reflected.items() if (text
:= info.get("text"))}
def _get_schema(self, table_name: str, name_contains: str | None = None)
-> str:
schema, table = self._split_table_identifier(table_name)
if not self._is_ref_allowed("", schema, table):
return dumps_masked({"error": f"Table {table_name!r} is not in the
allowed tables list."})
- columns = [
- {"name": col["name"], "type": str(col["type"])}
- for col in self._get_inspector().get_columns(table, schema=schema)
- ]
+ # Called only for its side effect: SQLAlchemy setup failures surface
here as
+ # SQLToolsetConnectionError; dropping it lets them escape the hook as
plain errors.
+ self._get_inspector()
+ hook = self._get_db_hook()
+ columns = hook.get_table_columns_with_comments(table, schema=schema)
+ table_comment: str | None = None
+ # Broad on purpose: a hook-level failure reading only the comment must
not
+ # take get_schema down when the columns were read fine.
+ try:
+ table_comment = hook.get_table_comment(table, schema=schema)
+ except Exception:
+ log.debug("Could not read the comment of table %s", table,
exc_info=True)
return build_schema_result(
columns,
max_columns=self._max_columns,
max_result_bytes=self._max_result_bytes,
name_contains=name_contains,
tool_name=self._build_tool_name("get_schema"),
+ table_comment=table_comment,
)
def _dialect_for_validation(self) -> str | None:
diff --git
a/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
b/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
index eff89771fdc..4ce0fad2aa8 100644
--- a/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
+++ b/providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py
@@ -40,7 +40,7 @@ from collections import Counter
from collections.abc import Sequence
from typing import Any
-from airflow.providers.common.ai.utils.masking import dumps_masked
+from airflow.providers.common.ai.utils.masking import dumps_masked,
mask_secrets
# A policy default, not a limit imposed by any storage, protocol, or model
layer:
# roughly 16k tokens at 4 characters per token. Large enough that ordinary
queries are
@@ -64,6 +64,10 @@ _SCHEMA_TYPE_HISTOGRAM_OTHER_KEY = "(other)"
# "here is what the names look like, now filter", never as a usable prefix of
the table.
_SCHEMA_SAMPLE_SIZE = 20
+# Longest table or column comment returned, in characters. Comments are free
text written by
+# people, so one pasted paragraph must not be able to dominate the result.
+DEFAULT_MAX_COMMENT_CHARS = 200
+
# Tool results are machine-read, so no whitespace. ensure_ascii=False matters
as much as
# the separators: escaping one CJK character to \uXXXX costs six bytes instead
of three,
# so an ASCII-escaped result is charged several times over against the budget
and
@@ -98,16 +102,40 @@ _GET_SCHEMA_DESCRIPTION_TEMPLATE = (
"table."
)
+# Appended only for toolsets whose results can carry comments, so the
description never promises
+# keys a toolset does not return.
+_GET_SCHEMA_COMMENTS_DESCRIPTION = (
+ " Each column carries a `comment`, and the result a `table_comment`, when
the database provides "
+ "one; `comments_omitted` means the column comments were left out to fit."
+)
+
+
+def build_get_schema_description(*, tool_name: str = "get_schema",
include_comments: bool = False) -> str:
+ """
+ Render the ``get_schema`` tool description naming the tool as the model
sees it.
-def build_get_schema_description(*, tool_name: str = "get_schema") -> str:
- """Render the ``get_schema`` tool description naming the tool as the model
sees it."""
- return _GET_SCHEMA_DESCRIPTION_TEMPLATE.format(tool_name=tool_name)
+ :param tool_name: The tool name as the model sees it, including any prefix.
+ :param include_comments: Describe the comment keys; set only for toolsets
that return comments.
+ """
+ description = _GET_SCHEMA_DESCRIPTION_TEMPLATE.format(tool_name=tool_name)
+ if include_comments:
+ description += _GET_SCHEMA_COMMENTS_DESCRIPTION
+ return description
def _dumps(payload: Any) -> str:
return dumps_masked(payload, **_DUMP_KWARGS)
+def truncate_comment(comment: str, *, max_chars: int =
DEFAULT_MAX_COMMENT_CHARS) -> str:
+ """Mask registered secrets in ``comment``, then cap it to ``max_chars``
characters, marking a cut with a trailing ellipsis."""
+ # Mask before cutting: a cut secret is a prefix that no longer equals the
registered value, so the masker cannot find it.
+ comment = mask_secrets(comment)
+ if len(comment) <= max_chars:
+ return comment
+ return comment[: max_chars - 1] + "\u2026"
+
+
def _size(payload: Any) -> int:
"""Measure the serialized size in bytes -- not characters, which diverge
outside ASCII."""
return len(_dumps(payload).encode("utf-8"))
@@ -228,6 +256,7 @@ def build_schema_result(
max_result_bytes: int,
name_contains: str | None = None,
tool_name: str = "get_schema",
+ table_comment: str | None = None,
) -> str:
"""
Render a table's columns as a bounded JSON tool result.
@@ -238,7 +267,13 @@ def build_schema_result(
the way to retrieve specific columns. ``columns`` is assumed to hold
distinct names (a table's
introspected columns are unique by construction).
- :param columns: ``{"name", "type"}`` dicts in table order.
+ Comments count against the same budget as the columns: each one is capped
to
+ ``DEFAULT_MAX_COMMENT_CHARS`` characters, and when the result does not
fit, the column comments
+ are dropped first (``comments_omitted`` says so) and the full column list
is returned. Only if
+ the columns alone still do not fit does the summary replace them; it
likewise carries no column
+ comments.
+
+ :param columns: ``{"name", "type"}`` dicts in table order, each optionally
with a ``comment``.
:param max_columns: Column count above which a summary replaces the full
list.
:param max_result_bytes: Budget for the serialized result.
:param name_contains: Case-insensitive substring. When given (and
non-empty), only matching
@@ -246,8 +281,12 @@ def build_schema_result(
for the whole table.
:param tool_name: Name of the ``get_schema`` tool as the model sees it
(including any
``tool_prefix``), so hints point at this toolset's own tool.
+ :param table_comment: The table's comment, returned as ``table_comment``
when non-empty.
"""
name_contains = name_contains or None
+ columns = [
+ {**col, "comment": truncate_comment(col["comment"])} if "comment" in
col else col for col in columns
+ ]
total_columns = len(columns)
if name_contains is not None:
needle = name_contains.casefold()
@@ -280,11 +319,25 @@ def build_schema_result(
)
full: dict[str, Any] = {"columns": selected, "column_count": len(selected)}
+ if table_comment:
+ full["table_comment"] = truncate_comment(table_comment)
if name_contains is not None:
full["name_contains"] = name_contains
full["total_columns"] = total_columns
- if len(selected) <= max_columns and _size(full) <= max_result_bytes:
- return _dumps(full)
+ if len(selected) <= max_columns:
+ if _size(full) <= max_result_bytes:
+ return _dumps(full)
+ if any("comment" in col for col in selected):
+ without_comments = {
+ **full,
+ "columns": _drop_column_comments(selected),
+ "comments_omitted": True,
+ "hint": _build_comments_omitted_hint(
+ max_result_bytes=max_result_bytes,
name_contains=name_contains, tool_name=tool_name
+ ),
+ }
+ if _size(without_comments) <= max_result_bytes:
+ return _dumps(without_comments)
return _summarize_schema(
selected,
@@ -293,9 +346,21 @@ def build_schema_result(
max_result_bytes=max_result_bytes,
name_contains=name_contains,
tool_name=tool_name,
+ table_comment=full.get("table_comment"),
)
+def _drop_column_comments(columns: Sequence[dict[str, str]]) -> list[dict[str,
str]]:
+ return [{k: v for k, v in col.items() if k != "comment"} for col in
columns]
+
+
+def _build_comments_omitted_hint(*, max_result_bytes: int, name_contains: str
| None, tool_name: str) -> str:
+ reason = f"Column comments were left out to fit max_result_bytes
({max_result_bytes})."
+ if name_contains is not None:
+ return f"{reason} Use a more specific name_contains substring to get
their comments."
+ return f"{reason} Call {tool_name} with a name_contains substring to get
the comments of the columns you need."
+
+
def _summarize_schema(
selected: list[dict[str, str]],
*,
@@ -304,6 +369,7 @@ def _summarize_schema(
max_result_bytes: int,
name_contains: str | None,
tool_name: str,
+ table_comment: str | None,
) -> str:
"""Build the bounded summary returned when the full column list does not
fit."""
# Count is checked before bytes: it is the cheaper, more explainable
bound. A bytes-only
@@ -328,6 +394,12 @@ def _summarize_schema(
"truncated_by": truncated_by,
"hint": f"{reason} {move}",
}
+ if table_comment:
+ output["table_comment"] = table_comment
+ if any("comment" in col for col in selected):
+ output["comments_omitted"] = True
+ output["hint"] += " Column comments are omitted from this summary and
are returned with the columns."
+ selected = _drop_column_comments(selected)
if name_contains is not None:
output["name_contains"] = name_contains
output["total_columns"] = total_columns
diff --git
a/providers/common/ai/src/airflow/providers/common/ai/utils/sql_search.py
b/providers/common/ai/src/airflow/providers/common/ai/utils/sql_search.py
new file mode 100644
index 00000000000..9cdd70fde42
--- /dev/null
+++ b/providers/common/ai/src/airflow/providers/common/ai/utils/sql_search.py
@@ -0,0 +1,85 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+"""Table search for
:class:`~airflow.providers.common.ai.toolsets.sql.SQLToolset`."""
+
+from __future__ import annotations
+
+from typing import TYPE_CHECKING, Protocol
+
+if TYPE_CHECKING:
+ from collections.abc import Callable, Sequence
+
+# A table as the agent sees it: ``(qualified_name, comment)``. The name is
exactly what
+# ``list_tables`` would return -- bare for the default schema,
``SCHEMA.TABLE`` otherwise --
+# so it round-trips to ``get_schema``.
+TableEntry = tuple[str, str | None]
+
+
+class SQLTableSearchBackend(Protocol):
+ """
+ Finds tables relevant to a free-text query, for the ``search_tables`` tool.
+
+ The toolset filters whatever a backend returns through ``allowed_tables``
before the agent
+ sees it, so a backend that knows about more tables than the agent may use
cannot widen access.
+ It can still waste its own ``limit``, so an index built for the agent
should cover only the
+ tables the agent may use.
+ """
+
+ def search_tables(
+ self, query: str, limit: int, *, get_catalog: Callable[[],
Sequence[TableEntry]]
+ ) -> Sequence[TableEntry]:
+ """
+ Return up to ``limit`` tables, best match first.
+
+ :param query: The agent's free-text query.
+ :param limit: Maximum number of results wanted.
+ :param get_catalog: Lists the tables the toolset exposes, introspected
with the same
+ connection the agent queries with and cached for the toolset's
lifetime. Comments
+ are the full text, not cut to the length the agent sees, with the
secrets Airflow has
+ registered already masked as ``***``; the toolset cuts comments
when it returns them
+ to the agent. A backend with its own index (a vector store, a
search service)
+ ignores it.
+ """
+ ...
+
+
+class KeywordTableSearchBackend:
+ """
+ Default backend: case-insensitive substring match of each query word
against table names (without their schema) and comments.
+
+ Query words are split on whitespace. A table's score is the number of
words found in its table name
+ (the part after any schema) weighted at ``_NAME_WEIGHT`` plus the number
found in its comment; tables scoring zero are
+ dropped, and ties are broken by name.
+ """
+
+ _NAME_WEIGHT = 3
+
+ def search_tables(
+ self, query: str, limit: int, *, get_catalog: Callable[[],
Sequence[TableEntry]]
+ ) -> list[TableEntry]:
+ if not (words := query.casefold().split()):
+ return []
+ scored: list[tuple[int, str, TableEntry]] = []
+ for entry in get_catalog():
+ name, comment = entry
+ name_text = name.rpartition(".")[2].casefold()
+ comment_text = (comment or "").casefold()
+ score = sum(self._NAME_WEIGHT * (word in name_text) + (word in
comment_text) for word in words)
+ if score:
+ scored.append((score, name, entry))
+ scored.sort(key=lambda item: (-item[0], item[1]))
+ return [entry for _, _, entry in scored[:limit]]
diff --git
a/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
b/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
index f149912da23..d3f0b7e81e9 100644
--- a/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
+++ b/providers/common/ai/tests/unit/common/ai/toolsets/test_datafusion.py
@@ -158,6 +158,11 @@ class TestDataFusionToolsetToolPrefixModelFacingText:
tools = asyncio.run(ts.get_tools(ctx=MagicMock(spec=RunContext)))
assert "Call lake_get_schema again" in
tools["lake_get_schema"].tool_def.description
+ def test_get_schema_description_does_not_mention_comments(self):
+ ts = DataFusionToolset([_make_mock_datasource_config()])
+ tools = asyncio.run(ts.get_tools(ctx=MagicMock(spec=RunContext)))
+ assert "comment" not in
tools["get_schema"].tool_def.description.casefold()
+
def test_get_schema_hint_names_the_prefixed_tool(self):
ts = DataFusionToolset([_make_mock_datasource_config()],
tool_prefix="lake", max_columns=2)
ts._engine = _make_mock_engine(
diff --git a/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
b/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
index cdbe3bf86a8..493b68fa80c 100644
--- a/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
+++ b/providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py
@@ -20,7 +20,7 @@ import asyncio
import json
import sqlite3
import traceback
-from unittest.mock import MagicMock, PropertyMock, patch
+from unittest.mock import MagicMock, PropertyMock, call, patch
import pytest
from pydantic_ai._run_context import RunContext
@@ -32,6 +32,7 @@ from sqlalchemy.exc import OperationalError
from airflow.providers.common.ai.exceptions import SQLToolsetConnectionError
from airflow.providers.common.ai.toolsets.sql import SQLToolset
+from airflow.providers.common.ai.utils.sql_search import
KeywordTableSearchBackend
from airflow.providers.common.ai.utils.tool_definition import
_SUPPORTS_RETURN_SCHEMA
from airflow.providers.common.sql.hooks.sql import DbApiHook
@@ -68,8 +69,11 @@ def _make_mock_db_hook(
"""Create a mock DbApiHook with sensible defaults."""
mock = MagicMock(spec=DbApiHook)
mock.inspector = MagicMock(spec=Inspector)
+ # Assigned rather than taken from the spec: SQLAlchemy 1.4's Inspector has
no such method.
+ mock.inspector.get_multi_table_comment = MagicMock(return_value={})
mock.inspector.get_table_names.return_value = table_names or ["users",
"orders"]
- mock.inspector.get_columns.return_value = table_schema or [
+ mock.get_table_comment.return_value = None
+ mock.get_table_columns_with_comments.return_value = table_schema or [
{"name": "id", "type": "INTEGER"},
{"name": "name", "type": "VARCHAR"},
]
@@ -122,6 +126,15 @@ class TestSQLToolsetGetTools:
tools = asyncio.run(ts.get_tools(ctx=MagicMock()))
assert set(tools.keys()) == {"list_tables", "get_schema", "query",
"check_query"}
+ def test_search_tables_is_registered_only_with_a_backend(self):
+ ts = SQLToolset("pg_default",
search_backend=KeywordTableSearchBackend())
+ tools = asyncio.run(ts.get_tools(ctx=MagicMock()))
+ assert set(tools.keys()) == {"list_tables", "get_schema", "query",
"check_query", "search_tables"}
+ validator = tools["search_tables"].args_validator
+ assert validator.validate_python({"query": "orders"}) == {"query":
"orders"}
+ with pytest.raises(ValidationError):
+ validator.validate_python({})
+
def test_tool_definitions_have_descriptions(self):
ts = SQLToolset("pg_default")
tools = asyncio.run(ts.get_tools(ctx=MagicMock()))
@@ -214,6 +227,10 @@ class TestSQLToolsetToolPrefixModelFacingText:
description = tools["pg_get_schema"].tool_def.description
assert "Call pg_get_schema again" in description
+ def test_get_schema_description_names_the_table_comment_key(self):
+ tools =
asyncio.run(SQLToolset("pg_default").get_tools(ctx=MagicMock()))
+ assert "`table_comment`" in tools["get_schema"].tool_def.description
+
def test_get_schema_description_without_prefix_is_unchanged(self):
tools =
asyncio.run(SQLToolset("pg_default").get_tools(ctx=MagicMock()))
assert "Call get_schema again" in
tools["get_schema"].tool_def.description
@@ -305,7 +322,7 @@ class TestSQLToolsetGetSchema:
"columns": [{"name": "id", "type": "INTEGER"}, {"name": "name",
"type": "VARCHAR"}],
"column_count": 2,
}
- mock_hook.inspector.get_columns.assert_called_once_with("users",
schema=None)
+
mock_hook.get_table_columns_with_comments.assert_called_once_with("users",
schema=None)
def test_name_contains_filters_the_columns(self):
"""``name_contains`` threads from the tool call through to the bounded
result."""
@@ -366,13 +383,13 @@ class TestSQLToolsetGetSchema:
assert "error" in data
assert "secrets" in data["error"]
# The guard must short-circuit before touching the database.
- ts._hook.inspector.get_columns.assert_not_called()
+ ts._hook.get_table_columns_with_comments.assert_not_called()
def test_introspection_error_raises_model_retry(self):
"""A failure while reading a table's schema is returned to the agent
as a retry."""
ts = SQLToolset("pg_default")
ts._hook = _make_mock_db_hook()
- ts._hook.inspector.get_columns.side_effect = Exception('relation
"users" does not exist')
+ ts._hook.get_table_columns_with_comments.side_effect =
Exception('relation "users" does not exist')
with pytest.raises(ModelRetry) as exc_info:
asyncio.run(
@@ -924,6 +941,420 @@ class TestSQLToolsetConnectionFailures:
assert "postgresql://svc:***@db" in str(exc_info.value)
+class TestSQLToolsetComments:
+ @staticmethod
+ def _get_schema(ts: SQLToolset, **args) -> dict:
+ args = {"table_name": "users", **args}
+ return json.loads(asyncio.run(ts.call_tool("get_schema", args,
ctx=MagicMock(), tool=MagicMock())))
+
+ @staticmethod
+ def _list_tables(ts: SQLToolset):
+ return json.loads(asyncio.run(ts.call_tool("list_tables", {},
ctx=MagicMock(), tool=MagicMock())))
+
+ def test_get_schema_returns_table_and_column_comments(self):
+ ts = SQLToolset("pg_default")
+ ts._hook = _make_mock_db_hook(
+ table_schema=[
+ {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+ {"name": "name", "type": "VARCHAR"},
+ ]
+ )
+ ts._hook.get_table_comment.return_value = "Registered users"
+
+ assert self._get_schema(ts) == {
+ "columns": [
+ {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+ {"name": "name", "type": "VARCHAR"},
+ ],
+ "column_count": 2,
+ "table_comment": "Registered users",
+ }
+ ts._hook.get_table_comment.assert_called_once_with("users",
schema=None)
+
+ def test_get_schema_truncates_long_comments(self):
+ ts = SQLToolset("pg_default")
+ ts._hook = _make_mock_db_hook(table_schema=[{"name": "id", "type":
"INTEGER", "comment": "c" * 500}])
+ ts._hook.get_table_comment.return_value = "t" * 500
+
+ data = self._get_schema(ts)
+ assert data["columns"][0]["comment"] == "c" * 199 + "\u2026"
+ assert data["table_comment"] == "t" * 199 + "\u2026"
+
+ @pytest.mark.enable_redact
+ @pytest.mark.parametrize(
+ "entry_point", ["get-schema-column", "get-schema-table",
"list-tables", "search-tables"]
+ )
+ def test_a_secret_crossing_the_comment_cut_is_masked(self, entry_point,
registered_secret):
+ comment = "x" * 190 + registered_secret + "y" * 50
+ ts = SQLToolset("pg_default", include_table_comments=True,
search_backend=KeywordTableSearchBackend())
+ ts._hook = _make_mock_db_hook(
+ table_names=["users"], table_schema=[{"name": "id", "type":
"INTEGER", "comment": comment}]
+ )
+ ts._hook.get_table_comment.return_value = comment
+ ts._hook.inspector.get_multi_table_comment.return_value = {(None,
"users"): {"text": comment}}
+
+ if entry_point == "get-schema-column":
+ actual = self._get_schema(ts)["columns"][0]["comment"]
+ elif entry_point == "get-schema-table":
+ actual = self._get_schema(ts)["table_comment"]
+ elif entry_point == "list-tables":
+ actual = self._list_tables(ts)[0]["comment"]
+ else:
+ result = json.loads(
+ asyncio.run(
+ ts.call_tool("search_tables", {"query": "users"},
ctx=MagicMock(), tool=MagicMock())
+ )
+ )
+ actual = result["tables"][0]["comment"]
+
+ assert actual == "x" * 190 + "***" + "y" * 6 + "\u2026"
+
+ def test_get_schema_survives_table_comment_failure(self):
+ ts = SQLToolset("pg_default")
+ ts._hook = _make_mock_db_hook()
+ ts._hook.get_table_comment.side_effect = Exception("permission denied")
+
+ assert self._get_schema(ts)["column_count"] == 2
+
+ def test_column_comments_are_dropped_before_the_columns(self):
+ """Columns that fit on their own are returned in full, without
comments, once comments overflow."""
+ columns = [{"name": f"c{i}", "type": "INTEGER"} for i in range(10)]
+ with_comments = [{**col, "comment": "x" * 200} for col in columns]
+ budget = 800
+
+ plain = SQLToolset("pg_default", max_result_bytes=budget)
+ plain._hook = _make_mock_db_hook(table_schema=columns)
+ assert "truncated" not in self._get_schema(plain)
+
+ commented = SQLToolset("pg_default", max_result_bytes=budget)
+ commented._hook = _make_mock_db_hook(table_schema=with_comments)
+ data = self._get_schema(commented)
+ assert "truncated" not in data
+ assert data["columns"] == columns
+ assert data["column_count"] == 10
+ assert data["comments_omitted"] is True
+ assert "name_contains" in data["hint"]
+
+ def test_list_tables_default_shape_ignores_comments(self):
+ ts = SQLToolset("pg_default")
+ ts._hook = _make_mock_db_hook(table_names=["users", "orders"])
+
+ assert self._list_tables(ts) == ["users", "orders"]
+ ts._hook.inspector.get_multi_table_comment.assert_not_called()
+
+ def test_list_tables_with_comments_returns_name_and_comment(self):
+ ts = SQLToolset("pg_default", include_table_comments=True,
schema="public")
+ ts._hook = _make_mock_db_hook(table_names=["users", "orders"])
+ ts._hook.inspector.get_multi_table_comment.return_value = {
+ ("public", "users"): {"text": "u" * 500},
+ ("public", "orders"): {"text": None},
+ }
+
+ assert self._list_tables(ts) == [
+ {"name": "users", "comment": "u" * 199 + "\u2026"},
+ {"name": "orders"},
+ ]
+
ts._hook.inspector.get_multi_table_comment.assert_called_once_with(schema="public",
filter_names=None)
+
+ def test_list_tables_with_comments_filters_and_qualifies(self):
+ ts = SQLToolset("sf", allowed_tables=["S1.A"],
include_table_comments=True)
+ hook = _make_mock_db_hook(table_names=["A", "B"])
+ hook.inspector.get_multi_table_comment.return_value = {
+ ("S1", "A"): {"text": "table a"},
+ ("S1", "B"): {"text": "secret b"},
+ }
+ ts._hook = hook
+
+ assert self._list_tables(ts) == [{"name": "S1.A", "comment": "table
a"}]
+
+ def
test_list_tables_omits_comments_when_inspector_cannot_read_them_in_bulk(self):
+ """SQLAlchemy 1.4's Inspector has no get_multi_table_comment."""
+ ts = SQLToolset("pg_default", include_table_comments=True)
+ ts._hook = _make_mock_db_hook()
+ ts._hook.inspector = MagicMock(spec=["get_table_names"])
+ ts._hook.inspector.get_table_names.return_value = ["users"]
+
+ assert self._list_tables(ts) == [{"name": "users"}]
+
+ def
test_list_tables_with_comments_degrades_when_dialect_cannot_report(self):
+ ts = SQLToolset("pg_default", include_table_comments=True)
+ ts._hook = _make_mock_db_hook(table_names=["users"])
+ ts._hook.inspector.get_multi_table_comment.side_effect =
NotImplementedError
+
+ assert self._list_tables(ts) == [{"name": "users"}]
+
+ @pytest.mark.parametrize(
+ ("allowed_tables", "schema", "reflected", "expected_calls"),
+ [
+ pytest.param(
+ ["S1.A", "S2.C"],
+ None,
+ {"S1": ["A", "B"], "S2": ["C", "D"]},
+ [call(schema="S1", filter_names=["A"]), call(schema="S2",
filter_names=["C"])],
+ id="qualified-per-schema",
+ ),
+ pytest.param(
+ ["users"],
+ "public",
+ {"public": ["USERS", "SECRETS"]},
+ [call(schema="public", filter_names=["USERS"])],
+ id="reflected-case-not-user-input",
+ ),
+ ],
+ )
+ def test_only_allowed_tables_comments_are_requested(
+ self, allowed_tables, schema, reflected, expected_calls
+ ):
+ ts = SQLToolset("sf", allowed_tables=allowed_tables, schema=schema,
include_table_comments=True)
+ hook = _make_mock_db_hook()
+ hook.inspector.get_table_names.side_effect = lambda schema=None:
reflected.get(schema, [])
+ ts._hook = hook
+
+ self._list_tables(ts)
+
+ comment_reads = hook.inspector.get_multi_table_comment
+ assert comment_reads.call_count == len(expected_calls)
+ comment_reads.assert_has_calls(expected_calls, any_order=True)
+
+ @pytest.mark.parametrize(
+ "tool",
+ [pytest.param("list_tables", id="list-tables"),
pytest.param("search_tables", id="search-tables")],
+ )
+ def test_comment_read_failure_leaves_tables_without_comments(self, tool):
+ ts = SQLToolset("pg_default", include_table_comments=True,
search_backend=KeywordTableSearchBackend())
+ ts._hook = _make_mock_db_hook(table_names=["users"])
+ ts._hook.inspector.get_multi_table_comment.side_effect =
OperationalError(
+ "SELECT", {}, Exception("permission denied")
+ )
+ args = {"query": "users"} if tool == "search_tables" else {}
+
+ result = json.loads(_call(ts, tool, args))
+
+ assert result == ([{"name": "users"}] if tool == "list_tables" else
{"tables": [{"name": "users"}]})
+
+ @pytest.mark.parametrize(
+ "tool",
+ [pytest.param("list_tables", id="list-tables"),
pytest.param("search_tables", id="search-tables")],
+ )
+ def test_comment_read_on_lost_connection_raises_and_is_not_cached(self,
tool):
+ ts = SQLToolset("pg_default", include_table_comments=True,
search_backend=KeywordTableSearchBackend())
+ ts._hook = _make_mock_db_hook(table_names=["users"])
+ ts._hook.inspector.get_multi_table_comment.side_effect =
OperationalError(
+ "SELECT",
+ {},
+ sqlite3.OperationalError("server closed the connection"),
+ connection_invalidated=True,
+ )
+ args = {"query": "users"} if tool == "search_tables" else {}
+
+ with pytest.raises(SQLToolsetConnectionError, match="server closed the
connection"):
+ _call(ts, tool, args)
+
+ assert ts._catalog is None
+
+
+class _FakeSearchBackend:
+ """Returns canned results, ignoring the query, so tests control exactly
what the toolset must filter."""
+
+ def __init__(self, results: list[tuple[str, str | None]]) -> None:
+ self.results = results
+ self.limits: list[int] = []
+
+ def search_tables(self, query, limit, *, get_catalog):
+ self.limits.append(limit)
+ return self.results
+
+
+class _CatalogRecordingBackend:
+ """Records the catalog it is handed, so tests can see what a custom
backend receives."""
+
+ def __init__(self) -> None:
+ self.catalogs: list = []
+
+ def search_tables(self, query, limit, *, get_catalog):
+ self.catalogs.append(list(get_catalog()))
+ return []
+
+
+class TestSQLToolsetSearchTables:
+ @staticmethod
+ def _call(ts: SQLToolset, name: str, args: dict | None = None):
+ return json.loads(asyncio.run(ts.call_tool(name, args or {},
ctx=MagicMock(), tool=MagicMock())))
+
+ @pytest.mark.enable_redact
+ def test_backend_receives_full_masked_comments(self, registered_secret):
+ backend = _CatalogRecordingBackend()
+ ts = SQLToolset("pg_default", search_backend=backend)
+ ts._hook = _make_mock_db_hook(table_names=["users"])
+ ts._hook.inspector.get_multi_table_comment.return_value = {
+ (None, "users"): {"text": "x" * 190 + registered_secret + "y" * 50}
+ }
+
+ self._call(ts, "search_tables", {"query": "users"})
+
+ assert backend.catalogs == [[("users", "x" * 190 + "***" + "y" * 50)]]
+
+ def test_default_backend_matches_words_past_the_output_cut(self):
+ ts = SQLToolset("pg_default",
search_backend=KeywordTableSearchBackend())
+ ts._hook = _make_mock_db_hook(table_names=["ledger"])
+ ts._hook.inspector.get_multi_table_comment.return_value = {
+ (None, "ledger"): {"text": "a" * 300 + " invoices"}
+ }
+
+ result = self._call(ts, "search_tables", {"query": "invoices"})
+
+ assert result == {"tables": [{"name": "ledger", "comment": "a" * 199 +
"\u2026"}]}
+
+ def test_results_outside_allowed_tables_are_filtered(self):
+ """A backend that knows about more tables than the agent may use
cannot widen access."""
+ backend = _FakeSearchBackend([("secrets", "passwords"), ("orders",
"Orders"), ("users", None)])
+ ts = SQLToolset("pg_default", allowed_tables=["orders", "users"],
search_backend=backend)
+
+ result = self._call(ts, "search_tables", {"query": "anything"})
+
+ assert result == {"tables": [{"name": "orders", "comment": "Orders"},
{"name": "users"}]}
+
+ def
test_qualified_results_are_matched_against_qualified_allowed_tables(self):
+ backend = _FakeSearchBackend([("S1.A", None), ("S2.A", None), ("A",
None)])
+ ts = SQLToolset("sf", allowed_tables=["S1.A"], search_backend=backend)
+
+ assert self._call(ts, "search_tables", {"query": "a"}) == {"tables":
[{"name": "S1.A"}]}
+
+ @pytest.mark.parametrize(
+ ("requested", "expected_limit"),
+ [
+ pytest.param(None, 10, id="default"),
+ pytest.param(3, 3, id="explicit"),
+ pytest.param(1000, 50, id="capped"),
+ pytest.param(-5, 1, id="floored"),
+ pytest.param(0, 1, id="zero-is-floored-not-defaulted"),
+ ],
+ )
+ def test_limit_is_bounded(self, requested, expected_limit):
+ backend = _FakeSearchBackend([])
+ ts = SQLToolset("pg_default", search_backend=backend)
+ args = {"query": "x"} if requested is None else {"query": "x",
"limit": requested}
+
+ self._call(ts, "search_tables", args)
+
+ assert backend.limits == [expected_limit]
+
+ def test_results_are_cut_to_limit_after_filtering(self):
+ backend = _FakeSearchBackend([("secret", None), ("a", None), ("b",
None), ("c", None)])
+ ts = SQLToolset("pg_default", allowed_tables=["a", "b", "c"],
search_backend=backend)
+
+ result = self._call(ts, "search_tables", {"query": "x", "limit": 2})
+
+ assert [t["name"] for t in result["tables"]] == ["a", "b"]
+
+ def test_result_comments_are_truncated(self):
+ backend = _FakeSearchBackend([("orders", "c" * 500)])
+ ts = SQLToolset("pg_default", search_backend=backend)
+
+ result = self._call(ts, "search_tables", {"query": "x"})
+
+ assert result["tables"][0]["comment"] == "c" * 199 + "\u2026"
+
+ @pytest.mark.enable_redact
+ def
test_custom_backend_comment_with_a_secret_crossing_the_cut_is_masked(self,
registered_secret):
+ backend = _FakeSearchBackend([("users", "x" * 190 + registered_secret
+ "y" * 50)])
+ ts = SQLToolset("pg_default", search_backend=backend)
+
+ result = self._call(ts, "search_tables", {"query": "x"})
+
+ assert result["tables"][0]["comment"] == "x" * 190 + "***" + "y" * 6 +
"\u2026"
+
+ def test_default_backend_introspects_once_with_the_toolsets_hook(self):
+ ts = SQLToolset(
+ "pg_default", allowed_tables=["users", "orders"],
search_backend=KeywordTableSearchBackend()
+ )
+ ts._hook = _make_mock_db_hook(table_names=["users", "orders",
"secrets"])
+ ts._hook.inspector.get_multi_table_comment.return_value = {
+ (None, "users"): {"text": "Registered customers"},
+ (None, "orders"): {"text": None},
+ (None, "secrets"): {"text": "customers passwords"},
+ }
+
+ first = self._call(ts, "search_tables", {"query": "customers"})
+ second = self._call(ts, "search_tables", {"query": "orders"})
+
+ assert first == {"tables": [{"name": "users", "comment": "Registered
customers"}]}
+ assert second == {"tables": [{"name": "orders"}]}
+ ts._hook.inspector.get_table_names.assert_called_once_with(schema=None)
+ ts._hook.inspector.get_multi_table_comment.assert_called_once_with(
+ schema=None, filter_names=["users", "orders"]
+ )
+
+ def test_degraded_catalog_is_cached(self):
+ ts = SQLToolset("pg_default", include_table_comments=True,
search_backend=KeywordTableSearchBackend())
+ ts._hook = _make_mock_db_hook(table_names=["users"])
+ ts._hook.inspector.get_multi_table_comment.side_effect =
OperationalError(
+ "SELECT", {}, Exception("permission denied")
+ )
+
+ self._call(ts, "search_tables", {"query": "users"})
+ self._call(ts, "search_tables", {"query": "users"})
+
+ assert ts._hook.inspector.get_multi_table_comment.call_count == 1
+
+ def
test_search_omits_comments_when_inspector_cannot_read_them_in_bulk(self):
+ """SQLAlchemy 1.4's Inspector has no get_multi_table_comment."""
+ ts = SQLToolset("pg_default",
search_backend=KeywordTableSearchBackend())
+ ts._hook = _make_mock_db_hook()
+ ts._hook.inspector = MagicMock(spec=["get_table_names"])
+ ts._hook.inspector.get_table_names.return_value = ["users"]
+
+ assert self._call(ts, "search_tables", {"query": "users"}) ==
{"tables": [{"name": "users"}]}
+
+ def test_calling_search_tables_without_a_backend_is_unknown(self):
+ ts = SQLToolset("pg_default")
+ ts._hook = _make_mock_db_hook()
+
+ with pytest.raises(ValueError, match="Unknown tool: 'search_tables'"):
+ asyncio.run(ts.execute_tool("search_tables", {"query": "x"},
ctx=MagicMock(), tool=MagicMock()))
+
+ @pytest.mark.parametrize(
+ ("backend", "mentioned"),
+ [
+ pytest.param(None, False, id="no-backend"),
+ pytest.param(_FakeSearchBackend([]), True, id="with-backend"),
+ ],
+ )
+ def test_error_hint_points_at_search_tables_only_when_registered(self,
backend, mentioned):
+ ts = SQLToolset("pg_default", search_backend=backend)
+ ts._hook = _make_mock_db_hook()
+ ts._hook.inspector.get_table_names.side_effect = Exception("boom")
+
+ with pytest.raises(ModelRetry) as exc_info:
+ asyncio.run(ts.call_tool("list_tables", {}, ctx=MagicMock(),
tool=MagicMock()))
+
+ assert ("search_tables" in exc_info.value.message) is mentioned
+
+ @pytest.mark.parametrize(
+ ("table_count", "listed"),
+ [pytest.param(3, True, id="at-threshold"), pytest.param(4, False,
id="over-threshold")],
+ )
+ def test_list_tables_stops_listing_over_max_listed_tables(self,
table_count, listed):
+ names = [f"t{i}" for i in range(table_count)]
+ ts = SQLToolset("pg_default", search_backend=_FakeSearchBackend([]),
max_listed_tables=3)
+ ts._hook = _make_mock_db_hook(table_names=names)
+
+ result = self._call(ts, "list_tables")
+
+ if listed:
+ assert result == names
+ else:
+ assert result["table_count"] == table_count
+ assert "search_tables" in result["hint"]
+
+ def test_list_tables_has_no_limit_without_a_backend(self):
+ names = [f"t{i}" for i in range(150)]
+ ts = SQLToolset("pg_default", max_listed_tables=3)
+ ts._hook = _make_mock_db_hook(table_names=names)
+
+ assert self._call(ts, "list_tables") == names
+
+
class TestSQLToolsetMultiSchema:
"""Schema-qualified allowed_tables span multiple schemas in one
database."""
@@ -932,7 +1363,8 @@ class TestSQLToolsetMultiSchema:
hook = MagicMock(spec=DbApiHook)
hook.inspector = MagicMock()
hook.inspector.get_table_names.side_effect = lambda schema=None:
tables_by_schema.get(schema, [])
- hook.inspector.get_columns.return_value = [{"name": "id", "type":
"INTEGER"}]
+ hook.get_table_columns_with_comments.return_value = [{"name": "id",
"type": "INTEGER"}]
+ hook.get_table_comment.return_value = None
return hook
def test_list_tables_spans_multiple_schemas(self):
@@ -984,7 +1416,7 @@ class TestSQLToolsetMultiSchema:
)
)
assert result == {"columns": [{"name": "id", "type": "INTEGER"}],
"column_count": 1}
- ts._hook.inspector.get_columns.assert_called_once_with(
+ ts._hook.get_table_columns_with_comments.assert_called_once_with(
"DEPLOYMENT_IMAGE_DETAILS", schema="MODEL_ASTRO"
)
@@ -1000,14 +1432,14 @@ class TestSQLToolsetMultiSchema:
)
)
assert "error" in result
- ts._hook.inspector.get_columns.assert_not_called()
+ ts._hook.get_table_columns_with_comments.assert_not_called()
def test_get_schema_unqualified_uses_default_schema(self):
ts = SQLToolset("pg", schema="public")
ts._hook = self._schema_aware_hook({"public": ["users"]})
asyncio.run(ts.call_tool("get_schema", {"table_name": "users"},
ctx=MagicMock(), tool=MagicMock()))
- ts._hook.inspector.get_columns.assert_called_once_with("users",
schema="public")
+
ts._hook.get_table_columns_with_comments.assert_called_once_with("users",
schema="public")
def test_list_tables_matches_case_insensitively(self):
"""Snowflake reflects unquoted names lowercased; uppercase
allowed_tables still match."""
@@ -1040,7 +1472,7 @@ class TestSQLToolsetMultiSchema:
)
)
assert "error" not in result
- ts._hook.inspector.get_columns.assert_called_once_with(
+ ts._hook.get_table_columns_with_comments.assert_called_once_with(
"deployment_image_details", schema="MODEL_ASTRO"
)
diff --git
a/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
b/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
index 3ab3a2428ca..8dc042768ac 100644
--- a/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
+++ b/providers/common/ai/tests/unit/common/ai/utils/test_query_results.py
@@ -27,6 +27,7 @@ from airflow.providers.common.ai.utils.query_results import (
build_get_schema_description,
build_query_result,
build_schema_result,
+ truncate_comment,
)
@@ -176,6 +177,13 @@ def
test_a_secret_that_json_escapes_is_masked_in_the_rows(register_secret):
assert result["rows"] == [["admin", "***"]]
[email protected]_redact
+def test_truncate_comment_masks_a_secret_before_cutting(registered_secret):
+ comment = "x" * 190 + registered_secret + "y" * 50
+
+ assert truncate_comment(comment) == "x" * 190 + "***" + "y" * 6 + "\u2026"
+
+
def _schema(columns, *, max_columns=100, max_result_bytes=65_536,
name_contains=None) -> dict:
return json.loads(
build_schema_result(
@@ -344,6 +352,19 @@ class TestSchemaToolName:
def test_description_defaults_to_the_bare_name(self):
assert "Call get_schema again" in build_get_schema_description()
+ @pytest.mark.parametrize(
+ ("include_comments", "mentions_comments"),
+ [
+ pytest.param(True, True, id="with-comments"),
+ pytest.param(False, False, id="without-comments"),
+ ],
+ )
+ def test_description_mentions_comments_only_when_asked(self,
include_comments, mentions_comments):
+ description =
build_get_schema_description(include_comments=include_comments)
+ assert ("`table_comment`" in description) is mentions_comments
+ assert ("`comments_omitted`" in description) is mentions_comments
+ assert ("comment" in description.casefold()) is mentions_comments
+
def test_hints_name_the_given_tool(self):
no_match = json.loads(
build_schema_result(
@@ -361,3 +382,83 @@ class TestSchemaToolName:
)
assert "Call pg_get_schema without name_contains" in no_match["hint"]
assert "Call pg_get_schema with a name_contains substring" in
summary["hint"]
+
+
[email protected](
+ ("comment", "expected"),
+ [
+ pytest.param("short", "short", id="under-limit"),
+ pytest.param("a" * 200, "a" * 200, id="at-limit"),
+ pytest.param("a" * 201, "a" * 199 + "\u2026", id="over-limit"),
+ ],
+)
+def test_truncate_comment(comment, expected):
+ assert truncate_comment(comment) == expected
+
+
+class TestSchemaComments:
+ def test_table_comment_is_returned_only_when_present(self):
+ with_comment = json.loads(
+ build_schema_result(
+ _cols(2), max_columns=100, max_result_bytes=65_536,
table_comment="Customer orders"
+ )
+ )
+ without = json.loads(build_schema_result(_cols(2), max_columns=100,
max_result_bytes=65_536))
+ assert with_comment["table_comment"] == "Customer orders"
+ assert "table_comment" not in without
+
+ def test_summary_keeps_table_comment_and_drops_column_comments(self):
+ cols = [{**col, "comment": "note"} for col in _cols(250)]
+ data = json.loads(
+ build_schema_result(cols, max_columns=100,
max_result_bytes=65_536, table_comment="Wide table")
+ )
+ assert data["truncated_by"] == "max_columns"
+ assert data["table_comment"] == "Wide table"
+ assert data["comments_omitted"] is True
+ assert "comments are omitted" in data["hint"]
+ assert all("comment" not in col for col in data["sample_columns"])
+
+ @pytest.mark.parametrize(
+ ("name_contains", "advice"),
+ [
+ pytest.param(None, "with a name_contains substring",
id="unfiltered"),
+ pytest.param("col", "more specific name_contains", id="filtered"),
+ ],
+ )
+ def test_column_comments_are_dropped_before_summarizing(self,
name_contains, advice):
+ cols = _cols(10)
+ data = json.loads(
+ build_schema_result(
+ [{**col, "comment": "x" * 200} for col in cols],
+ max_columns=100,
+ max_result_bytes=1000,
+ name_contains=name_contains,
+ table_comment="Wide",
+ )
+ )
+ assert data["columns"] == cols
+ assert "truncated" not in data
+ assert data["comments_omitted"] is True
+ assert data["table_comment"] == "Wide"
+ assert advice in data["hint"]
+ if name_contains is not None:
+ assert data["name_contains"] == name_contains
+ assert data["total_columns"] == 10
+
+ def test_columns_too_wide_even_without_comments_are_summarized(self):
+ data = json.loads(
+ build_schema_result(
+ [{**col, "comment": "x" * 200} for col in _cols(10, prefix="c"
* 100)],
+ max_columns=100,
+ max_result_bytes=1000,
+ )
+ )
+ assert data["truncated_by"] == "max_result_bytes"
+ assert data["comments_omitted"] is True
+ assert "columns" not in data
+ assert data["sample_columns"]
+
+ def test_summary_without_comments_is_unchanged(self):
+ data = json.loads(build_schema_result(_cols(250), max_columns=100,
max_result_bytes=65_536))
+ assert "comments_omitted" not in data
+ assert "table_comment" not in data
diff --git a/providers/common/ai/tests/unit/common/ai/utils/test_sql_search.py
b/providers/common/ai/tests/unit/common/ai/utils/test_sql_search.py
new file mode 100644
index 00000000000..56908be021b
--- /dev/null
+++ b/providers/common/ai/tests/unit/common/ai/utils/test_sql_search.py
@@ -0,0 +1,70 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+from __future__ import annotations
+
+import pytest
+
+from airflow.providers.common.ai.utils.sql_search import
KeywordTableSearchBackend
+
+CATALOG = [
+ ("orders", "Customer purchase orders"),
+ ("order_items", None),
+ ("users", "Registered customers and their ORDER history"),
+ ("audit_log", "Security events"),
+]
+
+
+def _search(query: str, limit: int = 10) -> list[str]:
+ result = KeywordTableSearchBackend().search_tables(query, limit,
get_catalog=lambda: CATALOG)
+ return [name for name, _ in result]
+
+
+class TestKeywordTableSearchBackend:
+ @pytest.mark.parametrize(
+ ("query", "expected"),
+ [
+ pytest.param("ORDERS", ["orders"], id="case-insensitive"),
+ pytest.param("order", ["orders", "order_items", "users"],
id="name-hits-outrank-comment-hits"),
+ pytest.param("customer order", ["orders", "order_items", "users"],
id="multiple-words-add-up"),
+ pytest.param("security", ["audit_log"], id="comment-only-match"),
+ pytest.param("zzz", [], id="no-match"),
+ pytest.param(" ", [], id="blank-query"),
+ ],
+ )
+ def test_ranking(self, query, expected):
+ assert _search(query) == expected
+
+ @pytest.mark.parametrize(
+ ("query", "expected"),
+ [
+ pytest.param("crm", ["MODEL_CRM.CRM_CONTACTS"], id="schema-word"),
+ pytest.param("astro", ["MODEL_CRM.SF_ASTRO_ORGS"],
id="table-part-still-matches"),
+ ],
+ )
+ def test_schema_is_not_matched_as_part_of_the_name(self, query, expected):
+ catalog = [
+ ("MODEL_CRM.SF_ASTRO_ORGS", "Salesforce organisations"),
+ ("MODEL_CRM.CRM_CONTACTS", None),
+ ("MODEL_ASTRO.DEPLOYMENT", "Deployments"),
+ ]
+ result = KeywordTableSearchBackend().search_tables(query, 10,
get_catalog=lambda: catalog)
+ assert [name for name, _ in result] == expected
+
+ def test_ties_break_by_name_and_limit_applies(self):
+ catalog = [("b_t", None), ("a_t", None), ("c_t", None)]
+ result = KeywordTableSearchBackend().search_tables("t", 2,
get_catalog=lambda: catalog)
+ assert result == [("a_t", None), ("b_t", None)]
diff --git a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
index fe5c3e9e673..03f3be87514 100644
--- a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
+++ b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.py
@@ -394,6 +394,41 @@ class DbApiHook(BaseHook):
columns = self.inspector.get_columns(table_name, schema=schema)
return [{"name": col["name"], "type": str(col["type"])} for col in
columns]
+ def get_table_comment(self, table_name: str, schema: str | None = None) ->
str | None:
+ """
+ Return the table comment using SQLAlchemy Inspector.
+
+ :param table_name: Name of the table.
+ :param schema: Optional schema/namespace name.
+ :return: The comment, or ``None`` when the table has none or the
dialect cannot report comments.
+ """
+ try:
+ comment = self.inspector.get_table_comment(table_name,
schema=schema)
+ except NotImplementedError:
+ return None
+ return comment.get("text") or None
+
+ def get_table_columns_with_comments(
+ self, table_name: str, schema: str | None = None
+ ) -> list[dict[str, str]]:
+ """
+ Return column names and types for a table, plus each column's comment
where one exists.
+
+ Same as :meth:`get_table_schema`, except that a column carries a
``comment`` key
+ only when the dialect reports a non-empty comment for it.
+
+ :param table_name: Name of the table.
+ :param schema: Optional schema/namespace name.
+ :return: List of dicts with ``name`` and ``type`` keys, and
``comment`` when present.
+ """
+ result: list[dict[str, str]] = []
+ for col in self.inspector.get_columns(table_name, schema=schema):
+ column = {"name": col["name"], "type": str(col["type"])}
+ if comment := col.get("comment"):
+ column["comment"] = comment
+ result.append(column)
+ return result
+
@cached_property
def dialect_name(self) -> str:
if make_url is not None:
diff --git
a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
index fab16ced47e..7f605f0a58d 100644
--- a/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
+++ b/providers/common/sql/src/airflow/providers/common/sql/hooks/sql.pyi
@@ -97,6 +97,10 @@ class DbApiHook(BaseHook):
def get_sqlalchemy_engine(self, engine_kwargs: Incomplete | None = None)
-> Engine: ...
@cached_property
def inspector(self) -> Inspector: ...
+ def get_table_comment(self, table_name: str, schema: str | None = None) ->
str | None: ...
+ def get_table_columns_with_comments(
+ self, table_name: str, schema: str | None = None
+ ) -> list[dict[str, str]]: ...
@cached_property
def dialect_name(self) -> str: ...
@cached_property
diff --git a/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
b/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
index 3207721367a..f3930e6e87f 100644
--- a/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
+++ b/providers/common/sql/tests/unit/common/sql/hooks/test_sql.py
@@ -28,6 +28,7 @@ import pandas as pd
import polars as pl
import pytest
from sqlalchemy import event
+from sqlalchemy.engine import Inspector
from airflow.exceptions import AirflowProviderDeprecationWarning
from airflow.models import Connection
@@ -464,6 +465,65 @@ class TestDbApiHookGetTableSchema:
mock_inspector.get_columns.assert_called_once_with("my_table",
schema="my_schema")
+class TestDbApiHookGetTableComments:
+ @pytest.mark.db_test
+ @pytest.mark.parametrize(
+ ("inspector_result", "expected"),
+ [
+ pytest.param({"text": "Customer orders"}, "Customer orders",
id="has-comment"),
+ pytest.param({"text": None}, None, id="comment-is-none"),
+ pytest.param({}, None, id="no-text-key"),
+ pytest.param(NotImplementedError(), None,
id="dialect-unsupported"),
+ ],
+ )
+ def test_get_table_comment(self, inspector_result, expected):
+ dbapi_hook = mock_db_hook(DbApiHook)
+ mock_inspector = MagicMock(spec=Inspector)
+ if isinstance(inspector_result, Exception):
+ mock_inspector.get_table_comment.side_effect = inspector_result
+ else:
+ mock_inspector.get_table_comment.return_value = inspector_result
+ with patch.object(
+ type(dbapi_hook), "inspector", new_callable=PropertyMock,
return_value=mock_inspector
+ ):
+ result = dbapi_hook.get_table_comment("orders", schema="sales")
+
+ assert result == expected
+ mock_inspector.get_table_comment.assert_called_once_with("orders",
schema="sales")
+
+ @pytest.mark.db_test
+ def test_get_table_columns_with_comments(self):
+ dbapi_hook = mock_db_hook(DbApiHook)
+ mock_inspector = MagicMock(spec=Inspector)
+ mock_inspector.get_columns.return_value = [
+ {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+ {"name": "name", "type": "VARCHAR(255)", "comment": None},
+ {"name": "note", "type": "TEXT", "comment": ""},
+ {"name": "created", "type": "TIMESTAMP"},
+ ]
+ with patch.object(
+ type(dbapi_hook), "inspector", new_callable=PropertyMock,
return_value=mock_inspector
+ ):
+ result = dbapi_hook.get_table_columns_with_comments("users",
schema="s")
+
+ assert result == [
+ {"name": "id", "type": "INTEGER", "comment": "Primary key"},
+ {"name": "name", "type": "VARCHAR(255)"},
+ {"name": "note", "type": "TEXT"},
+ {"name": "created", "type": "TIMESTAMP"},
+ ]
+ mock_inspector.get_columns.assert_called_once_with("users", schema="s")
+
+ def test_comment_methods_on_dialect_without_comment_support(self,
tmp_path):
+ db_path = tmp_path / "plain.db"
+ with closing(sqlite3.connect(db_path)) as conn:
+ conn.execute("CREATE TABLE users (id INTEGER)")
+ hook = DBApiHookForTests(conn_id=DEFAULT_CONN_ID)
+ with patch.object(hook, "get_uri", autospec=True,
return_value=f"sqlite:///{db_path}"):
+ assert hook.get_table_comment("users") is None
+ assert hook.get_table_columns_with_comments("users") == [{"name":
"id", "type": "INTEGER"}]
+
+
def test_inspector_is_cached():
"""inspector should return the same object on repeated access (not create
N engines)."""
hook = DBApiHookForTests(conn_id=DEFAULT_CONN_ID)