kaxil commented on code in PR #76799:
URL: https://github.com/apache/airflow/pull/76799#discussion_r4580721896
##########
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)
+ except NotImplementedError:
+ 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)
- ]
+ inspector = self._get_inspector()
+ hook = self._get_db_hook()
+ # common-sql releases before the comment methods lack them; fall back
to plain columns.
+ if get_columns := getattr(hook, "get_table_columns_with_comments",
None):
Review Comment:
The `getattr` fallbacks mean `get_schema` silently drops comments on an
older `common-sql`, while `list_tables(include_table_comments=True)` still
returns them because that path goes through the Inspector. The provider's
`common-sql` floor in `pyproject.toml` is unchanged. Could the floor be bumped
with `# use next version` and these fallbacks dropped? If not, `sql.rst` should
name the version that added the methods instead of "the release that added
them".
##########
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)
+ except NotImplementedError:
Review Comment:
`get_schema` catches broad exceptions around its comment lookups and logs at
debug, but only `NotImplementedError` is caught here. A role that can list
tables but can't read the comment catalog will make
`list_tables(include_table_comments=True)` and every `search_tables` call fail,
with no name-only fallback. Should this degrade to `{}` the same way?
Also, `get_comments(schema=schema)` reflects the whole schema even when
`allowed_tables` is set. Would passing `filter_names` with the allowed names
for that schema keep the read to what the agent can see? On dialects without an
optimized multi-reflect, SQLAlchemy's default falls back to one
`get_table_comment` per table, so "one call per schema" depends on the dialect.
##########
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)
Review Comment:
The catalog is built from comments already cut to 200 characters, so
`KeywordTableSearchBackend` never sees the tail of a longer comment. A keyword
at character 250 of a table comment won't match, but the docs say it matches
against the table's comment. Could the catalog keep the full text and
`truncate_comment` apply only on output (it already does in `_search_tables`),
or should the docs state the limit?
##########
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:
This loops over two cases inside one test. Could it be
`@pytest.mark.parametrize` with ids like `no-backend` and `with-backend`, so a
failure names the case?
--
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]