"""Table actions with separate read and write operations.""" import json from core import external_tools from . import legacy_action QUERY_PAGING_HELP = ( 'Send limit as a positive integer, for example {"limit": 100}. ' 'A limit of 501 or more requests pages of at most 500 rows. ' 'Send page.next_cursor as cursor with the same query until page.has_more is false. ' 'There is no total row cap.') DISCOVERY_HELP = ( "If your client exposes only the previous data tool, send the same action and arguments to that tool. " "Refresh or reconnect the client to discover table. Read loopable_guide with group table for its actions." ) READ_REFUSAL_FIXES = { "assistant_snapshot_unavailable": ( "This database's current data is unavailable. Describe it to read its fields. " "Try the same read again after its source sync completes."), "assistant_store_not_ready": ( "This database is temporarily unavailable. Try the same read again shortly."), "assistant_cross_tenant_store": ( "This connection cannot read that workspace. Reconnect to the intended workspace before reading."), "assistant_scope_not_applicable": ( "This database's access restriction could not be applied. Ask an Administrator to repair it. " "Do not remove or widen the restriction."), "assistant_filter_not_answerable": ( "This filter cannot be applied. Use only the fields and operators returned by describe_database. " "Keep the intended restriction when correcting the filter."), "query_group_type": ( "Month grouping requires a date field. Use a date field returned by describe_database, " "or omit unit to group by the field's values."), } 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. " + QUERY_PAGING_HELP + " " "Read loopable_guide with group table for the selected action's input schema. " + DISCOVERY_HELP ) #: The one skill.md section every query refusal names. QUERY_SYNTAX_SECTION = "Query syntax" QUERY_FILTER_EXAMPLE = {"conj": "and", "nodes": [{"colId": "", "op": "eq", "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": "", ' '"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": "", "fields": [""], "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: default 100. " + QUERY_PAGING_HELP + " 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": "", }}, indent=2), "```", "```json", json.dumps({"action": "query_database", "arguments": { "database": "", "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": "", "where": {"colId": "", "op": "eq", "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. " + DISCOVERY_HELP, "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}, }, }