loopable / api /tool_groups /data.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
d598032 verified
Raw History Blame
15.1 kB
"""Table actions with separate read and write operations."""
import json
from core import external_tools
from . import legacy_action
READ_ARGUMENT_HELP = (
"Call table with action describe_database or query_database and an arguments object. "
"arguments.database must be a string. Put query options inside arguments. "
"fields must be an array of field keys. limit must be an integer from 1 to 500. "
"Read loopable_guide with group table for the selected action's input schema."
)
#: The one skill.md section every query refusal names.
QUERY_SYNTAX_SECTION = "Query syntax"
QUERY_FILTER_EXAMPLE = {"conj": "and", "nodes": [{"colId": "<field key>", "op": "eq", "value": "<value>"}]}
QUERY_SYNTAX_FIX = (
"Filters use {conj, nodes: [{colId, op, value}]}, for example "
+ json.dumps(QUERY_FILTER_EXAMPLE, separators=(", ", ": ")) + ". "
"groupBy is a list of 1 to 4 field keys, or {field, unit: month}, with at least one aggregate and no sorts. "
"Only a connected database groups. Any other database answers query_groups_unavailable. "
"See " + QUERY_SYNTAX_SECTION + " in skill.md."
)
#: Query refusal codes that carry QUERY_SYNTAX_FIX.
QUERY_SYNTAX_CODES = frozenset({
"query_arguments_invalid", "query_aggregates_invalid", "query_aggregate_type",
"query_groups_invalid", "query_groups_unavailable", "assistant_bad_filter",
})
#: S79-T11: a schema-valid filter the read path refuses names the field and operator rules.
BAD_FILTER_FIX = (
"Use field keys and operators that describe_database returns. "
"isAnyOf and isNoneOf take values, an array. between takes value and value2."
)
#: S79-T11: an unknown database key names the call that lists keys, then the current query shape.
UNKNOWN_DATABASE_FIX = (
'No readable database has that key. Call table with {"action": "list_databases"} and use a database key it '
'returns. Then call table with {"action": "query_database", "arguments": {"database": "<database key>", '
'"limit": 100}}.'
)
def bad_filter_message(message):
"""Rewrite the read path's filter refusal as one plain sentence plus the field and operator rules."""
text = " ".join(str(message or "").split()).rstrip(".")
for prefix, subject in (("assistant filters ", "Filters "), ("assistant filter ", "The filter ")):
if text.startswith(prefix):
text = subject + text[len(prefix):]
text = (text[:1].upper() + text[1:] + ".") if text else "The filter cannot be read."
return text if BAD_FILTER_FIX in text else text + " " + BAD_FILTER_FIX
def with_query_syntax(message):
"""Append the filter shape, the groupBy rule and the skill.md pointer once."""
text = str(message or "").rstrip()
return text if QUERY_SYNTAX_FIX in text else (text + " " + QUERY_SYNTAX_FIX).strip()
#: The measured full product pull and its limit, cause and fix (rule 1). Never a cap.
FULL_PULL_NOTE = (
"Product data: a full pull reads about 6,000 rows in 12 pages, and each page after the first takes about 2 seconds. "
"The first page after a server restart can take about 3 minutes, because the product list is built from Odoo "
"on first use. If that call times out, repeat it. The next call reads the built list and nothing is capped."
)
def query_syntax_section():
"""Return the skill.md section that every query refusal names."""
example = {"action": "query_database", "arguments": {
"database": "<visible database key>", "fields": ["<field key>"],
"filters": QUERY_FILTER_EXAMPLE, "limit": 500}}
lines = [
"## " + QUERY_SYNTAX_SECTION, "",
"Every query refusal names this section. Call table with action query_database:", "",
"```json", json.dumps(example, indent=2), "```", "",
"- filters: {conj, nodes: [{colId, op, value}]}. conj is and or or. A node is a condition "
"{colId, op, value}, or a group {conj, children: [...]}. isAnyOf and isNoneOf take values, an array. "
"between takes value and value2. A link condition is {link, where}. Filters allow 3 levels, "
"100 nodes and 50 conditions per group.",
"- A flat object such as {\"status\": \"open\"}, or a condition with field or operator keys, is refused "
"with tool_arguments_invalid. Use colId and op.",
"- op: " + ", ".join(sorted(external_tools.FILTER_OPS)) + ". describe_database names the operators "
"each field accepts.",
"- sorts: [{field, direction}], direction asc or desc, at most 8.",
"- aggregates: [{fn, field}], fn count, count_distinct, sum, avg, min or max, at most 16. "
"count may omit field. sum and avg need a numeric field (query_aggregate_type otherwise).",
"- groupBy: 1 to 4 field keys, or {field, unit: month} for a date field. A grouped query needs at least "
"one aggregate and no sorts (query_groups_invalid otherwise). Only a connected database, such as an "
"Odoo sales lines mirror, groups. Any other database answers 409 query_groups_unavailable: read its "
"records by page, or ask for aggregates without groupBy.",
"- Paging: limit is 1 to 500, default 100. Send page.next_cursor as cursor with the same arguments "
"until page.has_more is false. Only fields, filters, sorts, limit, cursor, aggregates and groupBy are "
"accepted (query_arguments_invalid otherwise).",
"- Full pulls: no row cap applies to a connected source. Read every page at limit 500.",
]
if FULL_PULL_NOTE:
lines.append("- " + FULL_PULL_NOTE)
return lines + [""]
GROUPING_EXAMPLE = {
"action": "query_database", "arguments": {
"database": "ut_odoo_order_lines", "fields": [],
"groupBy": ["pricelist"],
"aggregates": [{"fn": "count"}, {"fn": "sum", "field": "price_subtotal"}],
"limit": 2,
},
}
def query_guide(rows):
"""Explain query results and offer an example only with readable fields."""
lines = [
"## Calling Table", "", READ_ARGUMENT_HELP,
"Use a database key returned by list_databases. Replace the example key before calling.",
"```json", json.dumps({"action": "describe_database", "arguments": {
"database": "<visible database key>",
}}, indent=2), "```",
"```json", json.dumps({"action": "query_database", "arguments": {
"database": "<visible database key>", "limit": 1,
}}, indent=2), "```",
"## Filter operators", "",
"Accepted operator names: " + ", ".join(sorted(external_tools.FILTER_OPS)) + ".",
QUERY_SYNTAX_FIX,
"describe_database returns operators and operands for each readable field. "
"Use eq for equality, gte and lte for inclusive date bounds, and isAnyOf with values for membership. "
"Date comparisons use value in YYYY-MM-DD format. Membership uses a values array. "
"Only use operators declared for that field. Combine date bounds in an AND group's nodes.", "",
"## Grouping and paging", "",
"Use groupBy with 1 to 4 readable field keys. For calendar months, use an object with "
"field set to a readable date field's key and unit set to month. Grouping needs a connected database, "
"at least one aggregate and no sorts. Groups arrive in key order.",
"The top-level aggregates cover the whole permitted, filtered result, never one page. "
"Each groups entry has keys and aggregates for that entire group. "
"page.returned counts groups on the current page. records is empty for grouped queries. "
"A null group key means the field has no value. It remains a separate group and counts toward the total.",
"count without a field counts records. count with a field counts non-null values. "
"Other functions are count_distinct, sum, avg, min and max. sum and avg need numeric fields.",
"Use page.next_cursor as cursor with the same arguments to get the next page. "
"Stop when page.has_more is false. Do not add the repeated top-level aggregates across pages. "
"Connected database pages use one completed sync. If a sync completes between pages, "
"query_cursor_stale means run the same query again without a cursor. Call query_database with error.retry_query "
"for a fresh first page. It keeps the original query and removes only cursor. "
"Discard earlier pages before collecting the new result. "
"query_cursor_invalid means the cursor cannot be read. Start again without cursor. "
"query_source_unavailable means the database is temporarily unavailable. Try the same query again shortly. "
"If the result or permissions change, start again without a cursor.",
"## Link filters", "",
"Describe the source database and use one declared link field per call: "
'{"link": "<link field key>", "where": {"colId": "<target field>", "op": "eq", "value": "<value>"}}. '
"Pass it as filters, or put it in an AND filter's nodes beside source conditions. "
"where is a condition or a children group on the target database. "
"Both databases and their join fields must be readable by this account and credential. "
"A link pin intersects the source filter. No matching target members return zero records. "
"Each call follows one link. Make a second call for the next link. "
"total_rows counts every matching permitted record. Follow page.next_cursor to collect the result.",
"Limits: 500 rows or groups per page, default 100. At most 200 selected fields, "
"16 aggregates and 8 sorts. Filters allow 3 levels, 100 nodes and 50 conditions per group. "
"These filter limits bound query validation work. If a filter exceeds them, split it into separate queries. "
"assistant_unknown_field suggests at most 5 close visible field keys. Use a key returned by describe_database. "
"Use the Table module: call tool table with action describe_database "
"and arguments containing database set to a visible database key. "
"The unqualified describe_database call also remains available.", "",
]
args = GROUPING_EXAMPLE["arguments"]
visible = next((row for row in rows if row["database"] == args["database"]), {})
keys = {field.get("key") for field in visible.get("fields", [])}
if (visible.get("answerable") and visible.get("schema", {}).get("source_kind") == "mirror_user_table"
and {"pricelist", "price_subtotal"} <= keys):
lines += ["### Example: Odoo sales lines by pricelist", "",
"Call table with this JSON. It counts sales lines and sums their amounts using your current access.",
"```json", json.dumps(GROUPING_EXAMPLE, indent=2), "```",
"Collect every page. Add each group's count aggregate and compare it with the count in this ungrouped call:",
"```json", json.dumps({"action": "query_database", "arguments": {
"database": args["database"], "fields": [], "aggregates": args["aggregates"], "limit": 1,
}}, indent=2), "```",
"The grouped and ungrouped counts and sums must agree. A null pricelist is included."]
else:
lines += ["The Odoo grouping example needs access to Sales order lines and its Pricelist and Line $ fields. "
"This connection cannot currently use that example. Describe a listed database before choosing fields."]
return "\n".join(lines)
def _report_limitation(ctx, args):
"""Send an AI's bounded question to the team without changing tenant data."""
from routes_feedback import record_limitation
recorded = record_limitation(ctx.session, tool=args["tool"], action=args["action"],
code=args.get("code", "capability_unavailable"), question=args["question"])
return {"recorded": recorded}
def _operation_status(ctx, args):
"""Read the result of this connection's proposed change."""
from routes_external import _operation_status as read_status
return read_status(ctx.session, args)
def _delivery_photos(ctx, args):
"""List one Odoo delivery's photos when this credential and account may read that delivery."""
import odoo_attachments
from loopy_links import public_base
from routes_external import _assert_database_scope
def key_allowed(key):
_assert_database_scope(ctx.session, key)
photos = odoo_attachments.list_photos(ctx.session, args["picking_id"], key_allowed=key_allowed,
base=public_base() or "")
return {"picking_id": args["picking_id"], "photos": photos}
GROUP = {
"name": "table", "title": "Table",
"guide": "Table: list databases, read fields, group and page records, propose changes, list an Odoo delivery's photos with delivery_photos, or report a limitation. Read loopable_guide(group='table') for actions and a grouping example.",
"actions": {
"operation_status": {
"read": True, "destructive": False, "confirm": False,
"schema": {"type": "object", "properties": {
"operation_id": {"type": "string", "pattern": "^[a-f0-9]{64}$"},
}, "required": ["operation_id"], "additionalProperties": False},
"handler": _operation_status,
},
"delivery_photos": {
"read": True, "destructive": False, "confirm": False,
"schema": {"type": "object", "properties": {
"picking_id": {"type": "integer", "minimum": 1, "maximum": 2147483647,
"description": "The stock.picking id: odoo_id in Odoo shipments."},
}, "required": ["picking_id"], "additionalProperties": False},
"handler": _delivery_photos,
},
"report_limitation": {
"read": True, "destructive": False, "confirm": False,
"schema": {"type": "object", "properties": {
"tool": {"type": "string", "minLength": 1, "maxLength": 100},
"action": {"type": "string", "minLength": 1, "maxLength": 100},
"code": {"type": "string", "maxLength": 100},
"question": {"type": "string", "minLength": 1, "maxLength": 500,
"description": "Your question in plain words. Omit records, cell values, filters and credentials."},
}, "required": ["tool", "action", "question"], "additionalProperties": False},
"handler": _report_limitation,
},
"list_databases": legacy_action("list_databases"),
"describe_database": legacy_action("describe_database"),
"query_database": legacy_action("query_database"),
**{"database." + action: legacy_action(
"database_object", action=action,
read=action in external_tools._OBJECT_READ_ACTIONS["database_object"],
destructive=action in {"record_delete", "field_delete"})
for action in external_tools._DATABASE_ACTIONS},
},
}