kaxil commented on code in PR #76799:
URL: https://github.com/apache/airflow/pull/76799#discussion_r4580818687


##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],

Review Comment:
   Could this block come from a real run? Hand-written output drifts from what 
the tool returns, and a reader can't tell which one they're looking at. A 
`FunctionModel` driving the toolset against a Postgres table with comments 
captures it without an LLM key.



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report

Review Comment:
   Could this name the version? common.ai still allows 
`apache-airflow-providers-common-sql>=2.2.0`, so plenty of readers will land on 
the fallback and need the number to know what to upgrade to.



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.
+
+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 result is summarized (see 
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with 
``comments_omitted``, and the
+agent gets them 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. 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. Based on the dialects' reflection 
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and 
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table 
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On 
PostgreSQL it was a single

Review Comment:
   "On PostgreSQL it was a single query when tried" has no source a reader can 
check, and the paragraph just before says the Snowflake and BigQuery behaviour 
was read from the dialects, not run. Could the page either state what was run 
(dialect version, what was counted) or drop the claim? Pinning 
`snowflake-sqlalchemy` 2.1.0 and `sqlalchemy-bigquery` 1.17.3 will also go 
stale with the next release.



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.

Review Comment:
   "For the same reason" doesn't hold: the toolset swallows any exception from 
`get_table_comment` (a permission error, say), not only a missing method or a 
dialect without comments. Could it say what actually happens, that the error is 
logged at debug and `get_schema` returns the columns without `table_comment`?



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.
+
+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 result is summarized (see 
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with 
``comments_omitted``, and the
+agent gets them 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. 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. Based on the dialects' reflection 
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and 
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table 
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On 
PostgreSQL it was a single
+query when tried. 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``.
+
+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.toolsets.sql_search import 
KeywordTableSearchBackend
+
+    toolset = SQLToolset(
+        db_conn_id="snowflake_hq",
+        allowed_tables=["MODEL_CRM.SF_ASTRO_ORGS", 
"MODEL_ASTRO.DEPLOYMENT_IMAGE_DETAILS"],
+        search_backend=KeywordTableSearchBackend(),
+    )
+
+``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; column
+comments are not searched. 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. It introspects once per toolset 
instance, then
+searches that result.
+
+Every result is checked against ``allowed_tables`` 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.
+
+**Plugging in another backend.** A backend is any object with a

Review Comment:
   Could this be a real subsection with a short example? Besides making it 
linkable: the page recommends a backend that "builds its catalog with one 
dialect-specific query" for Snowflake and BigQuery, but the protocol hands a 
backend only `get_catalog`, which is the per-table path. An example showing how 
such a backend gets a connection (its own hook on the same `db_conn_id`?) would 
make that advice usable.



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.
+
+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 result is summarized (see 
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with 
``comments_omitted``, and the
+agent gets them 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. 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. Based on the dialects' reflection 
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and 
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table 
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On 
PostgreSQL it was a single
+query when tried. 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``.
+
+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

Review Comment:
   Could the page show what the agent gets back here and from `search_tables`? 
Neither shape appears anywhere on the page. A `{"table_count": ..., "hint": 
...}` and a `{"tables": [...]}` sample from a real run would tell people 
writing prompts or their own backend what to expect.



##########
providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py:
##########
@@ -280,6 +301,8 @@ def build_schema_result(
         )
 
     full: dict[str, Any] = {"columns": selected, "column_count": len(selected)}
+    if table_comment:
+        full["table_comment"] = truncate_comment(table_comment)

Review Comment:
   On the size check a few lines down: `_size(full)` includes the comments, so 
a table whose columns fit but whose comments overflow `max_result_bytes` drops 
straight to the summary. The agent then loses the types and most column names 
it would have had without comments. Would retrying the full list with column 
comments stripped (and `comments_omitted` set) before falling back to the 
summary work? That keeps the feature from making `get_schema` worse on tables 
that used to fit.



##########
providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py:
##########
@@ -554,23 +623,106 @@ def add(schema: str | None, name: str, display: str) -> 
None:
         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": 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 comment, capped, read once 
per schema."""
+        inspector = self._get_inspector()
+        comments = {
+            table_schema: self._get_table_comments(inspector, table_schema)
+            for table_schema in {table_schema for table_schema, _, _ in tables}
+        }
+        return [
+            (display, truncate_comment(comment) if (comment := 
comments[table_schema].get(name)) else None)
+            for table_schema, name, display in tables
+        ]
 
-        return dumps_masked(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) -> 
dict[str, str]:
+        """Return ``{table name: comment}`` for a schema in one call; empty 
when the dialect has none."""
+        # 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)

Review Comment:
   `get_multi_table_comment` takes `filter_names`, and SQLAlchemy's per-table 
fallback skips any table not in it. Passing the allowed names for this schema 
would tie the Snowflake/BigQuery cost to `allowed_tables` instead of the whole 
schema; today two allowed tables in a 2,000-table dataset still cost 2,000 
calls on a dialect that relies on the default. The docs could then say 
`allowed_tables` bounds it.



##########
providers/common/ai/src/airflow/providers/common/ai/toolsets/sql.py:
##########
@@ -554,23 +623,106 @@ def add(schema: str | None, name: str, display: str) -> 
None:
         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:

Review Comment:
   With `include_table_comments=True` and no `search_backend`, the list has no 
size bound: each comment is capped at 200 characters but the total isn't tied 
to `max_result_bytes`. The name-only list was already unbounded, so this can 
stay as is, but the docs only mention the shared budget under `get_schema`. 
Worth a sentence in the `include_table_comments` parameter entry.



##########
providers/common/ai/src/airflow/providers/common/ai/toolsets/sql_search.py:
##########
@@ -0,0 +1,82 @@
+# 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. 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 and comments.
+
+    Query words are split on whitespace. A table's score is the number of 
words found in its name
+    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.casefold()
+            comment_text = (comment or "").casefold()
+            score = sum(self._NAME_WEIGHT * (word in name_text) + (word in 
comment_text) for word in words)

Review Comment:
   `name` is the display name, so for a non-default schema it is 
`SCHEMA.TABLE`. A query word like `crm` then matches every table in `MODEL_CRM` 
with the name weight of 3, which the docs describe as matching the "table 
name". Should this match the table part only, or should the docs say the schema 
is included?



##########
providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py:
##########
@@ -108,6 +113,13 @@ def _dumps(payload: Any) -> str:
     return dumps_masked(payload, **_DUMP_KWARGS)
 
 
+def truncate_comment(comment: str, *, max_chars: int = 
DEFAULT_MAX_COMMENT_CHARS) -> str:

Review Comment:
   The cut happens before `dumps_masked`, and masking matches whole registered 
secrets, so a secret that straddles the 200-character cut leaves its prefix 
unmasked. Narrow, since it needs a credential in a comment, but masking first 
and cutting after would avoid it.



##########
providers/common/ai/docs/agent_security.rst:
##########
@@ -186,7 +186,8 @@ 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.

Review Comment:
   Could this page also carry the identity point from the new warning in 
`sql.rst`? Someone reading the security page for what `allowed_tables` 
guarantees won't learn that a standalone backend indexed with another role can 
leak table names when `allowed_tables` is unset.



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.
+
+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 result is summarized (see 
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with 
``comments_omitted``, and the
+agent gets them 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. 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. Based on the dialects' reflection 
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and 
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table 
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On 
PostgreSQL it was a single
+query when tried. 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``.
+
+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.toolsets.sql_search import 
KeywordTableSearchBackend
+
+    toolset = SQLToolset(
+        db_conn_id="snowflake_hq",
+        allowed_tables=["MODEL_CRM.SF_ASTRO_ORGS", 
"MODEL_ASTRO.DEPLOYMENT_IMAGE_DETAILS"],
+        search_backend=KeywordTableSearchBackend(),
+    )
+
+``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; column
+comments are not searched. 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. It introspects once per toolset 
instance, then
+searches that result.
+
+Every result is checked against ``allowed_tables`` 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.

Review Comment:
   This only holds when `allowed_tables` is set. With the default allow-all, 
`_is_ref_allowed` passes every name, so a backend indexed with a broader role 
can show the agent table names and comments its connection can't see. Could 
this sentence say so and point at the warning below as the control in that case?



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.
+
+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 result is summarized (see 
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with 
``comments_omitted``, and the
+agent gets them 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. 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. Based on the dialects' reflection 
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and 
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table 
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On 
PostgreSQL it was a single
+query when tried. 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``.
+
+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.toolsets.sql_search import 
KeywordTableSearchBackend
+
+    toolset = SQLToolset(
+        db_conn_id="snowflake_hq",
+        allowed_tables=["MODEL_CRM.SF_ASTRO_ORGS", 
"MODEL_ASTRO.DEPLOYMENT_IMAGE_DETAILS"],
+        search_backend=KeywordTableSearchBackend(),
+    )
+
+``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; column
+comments are not searched. 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. It introspects once per toolset 
instance, then

Review Comment:
   "It" reads as the backend, but the toolset builds the catalog and keeps it 
for the instance's lifetime, for any backend that calls `get_catalog`. Worth 
saying that a table created after the first search won't show up until the 
toolset is built again?



##########
providers/common/ai/docs/toolsets/sql.rst:
##########
@@ -337,9 +344,114 @@ 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. 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:
+
+.. code-block:: json
+
+    {"columns": [{"name": "amt_adj_2", "type": "NUMERIC", "comment": "Net of 
refunds"}],
+     "column_count": 1, "table_comment": "Customer orders"}
+
+Comments are read through ``DbApiHook.get_table_comment`` and
+``DbApiHook.get_table_columns_with_comments``, which ``common.sql`` provides 
from the
+release that added them. With an older ``common.sql``, or on a dialect that 
cannot report
+comments (SQLite, for one), ``get_schema`` works as before and returns none. A 
failure reading
+the *table* comment is ignored for the same reason; a failure reading the 
columns is not.
+
+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 result is summarized (see 
:ref:`bounded-schema-results`),
+the column comments are left out, the summary says so with 
``comments_omitted``, and the
+agent gets them 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. 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. Based on the dialects' reflection 
implementations (read, not run
+against a live database), Snowflake (``snowflake-sqlalchemy`` 2.1.0) and 
BigQuery
+(``sqlalchemy-bigquery`` 1.17.3) do not implement it, so there each table 
costs one query, or one
+API call on BigQuery, and a schema with many tables makes this slow. On 
PostgreSQL it was a single
+query when tried. 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``.
+
+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.toolsets.sql_search import 
KeywordTableSearchBackend
+
+    toolset = SQLToolset(
+        db_conn_id="snowflake_hq",

Review Comment:
   This example doesn't show the case the section is about. With two 
`allowed_tables`, `list_tables` never gets near `max_listed_tables`, so 
`search_tables` only adds a tool call. The names also look like internal 
warehouse names (the earlier example on main already has them). Something like 
this covers both:
   
   ```python
   toolset = SQLToolset(
       db_conn_id="warehouse",
       schema="analytics",
       search_backend=KeywordTableSearchBackend(),
   )
   ```



##########
providers/common/ai/provider.yaml:
##########
@@ -615,6 +615,7 @@ toolsets:
     python-modules:
       - airflow.providers.common.ai.toolsets.hook
       - airflow.providers.common.ai.toolsets.sql
+      - airflow.providers.common.ai.toolsets.sql_search

Review Comment:
   `sql_search` has no class ending in `Toolset`, so it doesn't fit under 
`toolsets`: for a module like this, `process_module` in 
`dev/registry/extract_versions.py` falls back to a synthetic `SqlSearchToolset` 
registry entry. Could this line and the `sql_search` entry in 
`TOOLSET_TABLE_ENTRIES` go? The backend stays importable and documented in 
`sql.rst`.



##########
providers/common/ai/src/airflow/providers/common/ai/utils/query_results.py:
##########
@@ -95,7 +99,8 @@
     "where the budget allows a `type_histogram` and a `sample_columns` 
preview) with `truncated` "
     "set, `truncated_by` naming the limit that was hit, and a `hint`. Call 
{tool_name} again with "
     "a `name_contains` substring to retrieve the specific columns you need 
instead of the whole "
-    "table."
+    "table. A column or the table itself carries a `comment` when the database 
provides one; "

Review Comment:
   `DataFusionToolset` builds its `get_schema` description from this template 
too and never returns comments, so its agents are told about keys that won't 
come. The table's key is also `table_comment`, not `comment`. Could the comment 
sentence be added only for `SQLToolset`, say through a parameter on 
`build_get_schema_description`?



-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to