Lee-W commented on code in PR #76799:
URL: https://github.com/apache/airflow/pull/76799#discussion_r4597419406
##########
providers/common/ai/tests/unit/common/ai/toolsets/test_sql.py:
##########
@@ -924,6 +937,266 @@ def
test_a_connection_failure_carrying_a_secret_is_raised_masked(self, tmp_path,
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"
+
+ 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_get_schema_falls_back_on_hook_without_comment_methods(self):
+ """A common-sql release that predates the comment methods still serves
get_schema."""
+ ts = SQLToolset("pg_default")
+ hook = MagicMock(spec=["inspector"])
+ hook.inspector.get_columns.return_value = [{"name": "id", "type":
"INTEGER"}]
+ ts._hook = hook
+
+ assert self._get_schema(ts) == {"columns": [{"name": "id", "type":
"INTEGER"}], "column_count": 1}
+ hook.inspector.get_columns.assert_called_once_with("users",
schema=None)
+
+ def test_comments_count_against_the_byte_budget(self):
+ """Columns that fit on their own are summarized once their comments
push past the budget."""
+ columns = [{"name": f"c{i}", "type": "INTEGER"} for i in range(10)]
+ with_comments = [{**col, "comment": "x" * 200} for col in columns]
+ budget = 600
+
+ 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 data["truncated_by"] == "max_result_bytes"
+ assert data["comments_omitted"] is True
+ assert all("comment" not in col for col in data["sample_columns"])
+
+ 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")
+
+ 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"}]
+
+
+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 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())))
+
+ 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"
+
+ 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)
+
+ 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()))
+
+ def test_error_hint_points_at_search_tables_only_when_registered(self):
+ for backend, mentioned in ((None, False), (_FakeSearchBackend([]),
True)):
Review Comment:
Parametrized, with `no-backend` and `with-backend` as ids.
##########
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:
Replaced with the output of a real run against PostgreSQL tables that have
comments.
--
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]