Skip to content

Commit b9dbc21

Browse files
anandgupta42claude
andcommitted
feat(mcp): chain dependent sql_execute batch steps with {{label.column}} and {{label}}
Dependent queries cost a model round trip each. In a `queries` batch a later step can now use an earlier step's rows: `{{label.column}}` becomes a literal value list (SQL `(...)`, MongoDB JSON array; quoted, at most 1,000 source rows) and `{{label}}` a scratch table holding them (stored as `result_N`, steps on warehouse `scratch`). A referenced step fetches up to 1,000 rows. A reference to a failed, missing, oversized or non-scratch step fails only that step; the rest still run. Substituted SQL goes through the usual read-only checks. Descriptions +132 chars. Tests: `server.test.ts` (ALTIMATE_MCP_E2E=1): same-connection chain, cross-connection list + scratch join, over-cap list, mid-batch error. Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 24b4538 commit b9dbc21

4 files changed

Lines changed: 189 additions & 11 deletions

File tree

‎packages/opencode/src/altimate/mcp-server/adapter.ts‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -26,7 +26,7 @@ export const descriptions: Record<string, string> = {
2626
warehouse_add: "Add a connection when warehouse_list shows none or the one you need is missing.",
2727
schema_inspect: "List tables when table is omitted; otherwise one table's columns and value profile.",
2828
sql_execute:
29-
"Run SQL and return rows. Put independent queries in one call with `queries`; `tables` describes tables in the same call. Source writes need --allow-write.",
29+
"Run SQL and return rows. Chain queries in one call with `queries`: a later one can use {{label.column}} (value list from an earlier result) or {{label}} (its rows as a scratch table, warehouse scratch). `tables` describes tables first. Source writes need --allow-write.",
3030
sql_check: "Read-only data check: fanout, keys, grain, nulls or coverage.",
3131
sql_materialize: "Store a read-only query's rows as a scratch table, to join across connections.",
3232
answer_commit:
@@ -54,7 +54,7 @@ const PROPERTY_DESCRIPTIONS: Record<string, Record<string, string | null | typeo
5454
},
5555
sql_execute: {
5656
query: "SQL, or a MongoDB JSON command",
57-
queries: "Up to 8 independent queries, one labeled result each",
57+
queries: "Up to 8 queries, run in order, one labeled result each",
5858
"queries[].label": null,
5959
"queries[].query": null,
6060
"queries[].warehouse": null,

‎packages/opencode/src/altimate/mcp-server/server.ts‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -18,7 +18,7 @@ export const SERVER_INSTRUCTIONS = [
1818
"Analyst method for data questions:",
1919
"1. Start with warehouse_list (local tables, columns, a sample row); if none fit, warehouse_add one.",
2020
"2. Check stored value formats before filtering or joining: sql_execute `tables` returns value profiles in the same call as your queries.",
21-
"3. Compute in the database; put independent queries in one sql_execute call (`queries`).",
21+
"3. Compute in the database; chain dependent steps (any connection) in one sql_execute `queries` call.",
2222
"4. Cross-connection joins: sql_materialize the smaller filtered side into `scratch` and join there.",
2323
"5. Finish with answer_commit (`asked` = the requested items, one field each); reply with exactly its FINAL ANSWER text.",
2424
].join("\n")

‎packages/opencode/src/altimate/tools/sql-execute.ts‎

Lines changed: 83 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -51,8 +51,15 @@ const BATCH_MAX_PREVIEW_BYTES = 24 * 1024
5151
interface ItemBudget {
5252
maxBytes: number
5353
maxLines: number
54+
// A later batch step references this one: fetch at least this many rows and hand back the raw result.
55+
minRows?: number
56+
capture?: (result: SqlExecuteResult) => void
5457
}
5558

59+
// Batch steps reference earlier steps: {{label.column}} (literal value list) or {{label}} (scratch table).
60+
const STEP_REF = /\{\{([A-Za-z_]\w*)(?:\.([A-Za-z_]\w*))?\}\}/g
61+
const MAX_BOUND_ROWS = 1000
62+
5663
function generatedRead(sql: string, warehouse: string, stage: "source" | "result"): string {
5764
try {
5865
return readSql(sql, Registry.canonicalType(Registry.getConfig(warehouse)?.type) ?? undefined)
@@ -75,7 +82,7 @@ const parameters = z
7582
.max(MAX_BATCH_QUERIES, `queries accepts at most ${MAX_BATCH_QUERIES} items`)
7683
.nullish()
7784
.describe(
78-
`Up to ${MAX_BATCH_QUERIES} independent read-only queries run in one call, each returning its own labeled result; one failing query does not stop the others. Use instead of query when no query depends on another's result.`,
85+
`Up to ${MAX_BATCH_QUERIES} read-only queries run in order in one call, each returning its own labeled result; one failing query does not stop the others. A later query can use an earlier one's rows: {{label.column}} becomes a literal value list (at most ${MAX_BOUND_ROWS} rows) and {{label}} a scratch table (warehouse scratch).`,
7986
),
8087
query_from: z
8188
.object({
@@ -123,7 +130,7 @@ export const SqlExecuteTool = Tool.define(
123130
description:
124131
"Execute SQL against a connected data warehouse. Returns all selected columns as a formatted table. full_text_columns prioritizes named cells within the ordinary output budget and always saves returned rows to result_path; read with char_offset/max_chars recovers clipped text. [] or null, or omitting the field, turns the mode off. A non-empty list caps preview rows at 20: omit limit to use the default; larger limits clamp to 20 with a result note. Missing column names produce a bounded note listing available columns while still returning rows. " +
125132
"Provide exactly one of query, query_from or queries. query_from runs a read-only generator SELECT and executes its single SQL string result (up to 1 MiB) as a read-only SELECT on warehouse, without previewing or retyping the generated SQL. " +
126-
`queries runs up to ${MAX_BATCH_QUERIES} independent read-only queries in one call and returns one labeled result per query; batch queries that do not depend on each other. ` +
133+
`queries runs up to ${MAX_BATCH_QUERIES} read-only queries in order in one call and returns one labeled result per query; a later query can reference an earlier one with {{label.column}} (value list) or {{label}} (scratch table). ` +
127134
"For DuckDB table sets, generate query_table([...], true) with escaped table-name string literals. For thousands of tables, UNION ALL BY NAME batches of at most 128 names to stay within the default expression depth.",
128135
parameters,
129136
formatValidationError: (error: z.ZodError) => error.issues?.map((issue) => issue.message).join("; ") || LIMIT_ERROR,
@@ -310,7 +317,7 @@ export const SqlExecuteTool = Tool.define(
310317
const result = await Dispatcher.call("sql.execute", {
311318
sql: query,
312319
warehouse: args.warehouse,
313-
limit,
320+
limit: Math.max(limit, budget?.minRows ?? 0),
314321
query_timeout_ms: args.query_timeout_ms,
315322
...(readOnly ? { read_only: true } : {}),
316323
}, { signal: ctx.abort, sessionID: ctx.sessionID, ...(ctx.extra?.forceReadOnly === true ? { forceReadOnly: true } : {}) })
@@ -341,6 +348,7 @@ export const SqlExecuteTool = Tool.define(
341348
})
342349
}
343350
// altimate_change end
351+
budget?.capture?.(result)
344352

345353
// altimate_change start — emit SQL structure fingerprint telemetry on the
346354
// success path, BEFORE formatting the result. A query that reached this point
@@ -396,7 +404,7 @@ export const SqlExecuteTool = Tool.define(
396404
const lint = await lintSqlResult(sql, args.warehouse, ctx.sessionID)
397405
const formatted = await formatSqlResult(result, {
398406
mcpClient: ctx.extra?.mcpClient === true,
399-
limit,
407+
limit: Math.max(limit, budget?.minRows ?? 0),
400408
fullTextColumns: args.full_text_columns,
401409
// The statement text lets the formatter add the empty/near-empty
402410
// result hint when a string-literal filter is present.
@@ -512,21 +520,36 @@ async function executeBatch(
512520
let linesLeft = configured.maxLines
513521
const blocks: string[] = []
514522
const summary: { label: string; rowCount: number; error?: string; result_path?: string }[] = []
523+
const labels = items.map((item, index) => (item.label ?? "").replace(/\s+/g, " ").trim().slice(0, 60) || `q${index + 1}`)
524+
// Steps a later step references, as a value list ({{label.column}}) or a scratch table ({{label}}).
525+
const listRefs = new Set<string>()
526+
const tableRefs = new Set<string>()
527+
for (const item of items.slice(1))
528+
for (const [, ref, column] of item.query.matchAll(STEP_REF)) (column ? listRefs : tableRefs).add(ref)
529+
const captured = new Map<string, SqlExecuteResult>()
530+
const stored = new Map<string, string>()
515531
for (const [index, item] of items.entries()) {
516532
ctx.abort.throwIfAborted()
517533
const share = Math.floor(bytesLeft / (items.length - index))
518534
const lineShare = Math.max(1, Math.floor(linesLeft / (items.length - index)))
519-
const label = (item.label ?? "").replace(/\s+/g, " ").trim().slice(0, 60) || `q${index + 1}`
535+
const label = labels[index]
520536
const header = `[${label}]\n`
521537
const separator = blocks.length ? "\n\n" : ""
522538
const reserved = Buffer.byteLength(separator + header, "utf8") + Buffer.byteLength(BATCH_CLIPPED, "utf8")
523-
const budget = { maxBytes: Math.max(0, share - reserved), maxLines: Math.max(1, lineShare - 2) }
539+
const referenced = listRefs.has(label) || tableRefs.has(label)
540+
const budget: ItemBudget = {
541+
maxBytes: Math.max(0, share - reserved),
542+
maxLines: Math.max(1, lineShare - 2),
543+
...(referenced ? { minRows: MAX_BOUND_ROWS, capture: (raw: SqlExecuteResult) => captured.set(label, raw) } : {}),
544+
}
545+
const warehouse = item.warehouse?.trim() || args.warehouse
524546
let result: LegacyResult
525547
try {
548+
const query = resolveStepRefs(item.query, labels.slice(0, index), captured, stored, warehouse)
526549
result = await run(
527550
{
528-
query: item.query,
529-
warehouse: item.warehouse?.trim() || args.warehouse,
551+
query,
552+
warehouse,
530553
limit: args.limit,
531554
full_text_columns: args.full_text_columns,
532555
query_timeout_ms: args.query_timeout_ms,
@@ -538,6 +561,14 @@ async function executeBatch(
538561
const message = error instanceof Error ? error.message : String(error)
539562
result = { title: "SQL: ERROR", metadata: { error: message }, output: `Failed to execute SQL: ${message}` }
540563
}
564+
const failedStep = typeof result.metadata?.error === "string" && result.metadata?.success !== true
565+
if (failedStep) captured.delete(label)
566+
// A step referenced as {{label}} is stored in scratch (result_N), like a spilled result.
567+
const raw = captured.get(label)
568+
if (raw && tableRefs.has(label) && !raw.truncated) {
569+
const table = await storeSpill(raw, { signal: ctx.abort, sessionID: ctx.sessionID })
570+
if (table) stored.set(label, table)
571+
}
541572
const body = clipToBudget(result.output, budget.maxBytes, budget.maxLines)
542573
const block = separator + header + body
543574
blocks.push(block)
@@ -566,6 +597,50 @@ async function executeBatch(
566597
}
567598
}
568599

600+
/**
601+
* Replaces {{label.column}} with a literal list of that earlier step's distinct column values ((...) for SQL, a JSON
602+
* array for MongoDB) and {{label}} with the scratch table holding its rows. Throws, failing only this step, when the
603+
* reference names no earlier successful step, a missing column, a step with more than MAX_BOUND_ROWS rows, or a
604+
* table reference outside scratch.
605+
*/
606+
function resolveStepRefs(
607+
query: string,
608+
earlier: string[],
609+
captured: Map<string, SqlExecuteResult>,
610+
stored: Map<string, string>,
611+
warehouse: string | undefined,
612+
): string {
613+
return query.replace(STEP_REF, (match, ref: string, column: string | undefined) => {
614+
if (!earlier.includes(ref)) throw new Error(`${match} names no earlier step in this call.`)
615+
const source = captured.get(ref)
616+
if (!source) throw new Error(`${match}: step "${ref}" failed, so this step did not run.`)
617+
if (source.truncated)
618+
throw new Error(`${match}: step "${ref}" has more than ${MAX_BOUND_ROWS} rows; filter or aggregate it first.`)
619+
if (!column) {
620+
if (!isScratch(warehouse))
621+
throw new Error(`${match} is a scratch table: run this step on warehouse "scratch", or use {{${ref}.column}}.`)
622+
const table = stored.get(ref)
623+
if (!table) throw new Error(`${match}: storing step "${ref}" in scratch failed.`)
624+
return table
625+
}
626+
const index = source.columns.findIndex((name) => name.toLowerCase() === column.toLowerCase())
627+
if (index < 0) throw new Error(`${match}: step "${ref}" has no column ${column} (columns: ${source.columns.join(", ")}).`)
628+
const values = [...new Set(source.rows.map((row) => row[index]).filter((value) => value !== null && value !== undefined))]
629+
if (isMongoWarehouse(warehouse)) return JSON.stringify(values)
630+
const mysql = ["mysql", "mariadb"].includes(Registry.canonicalType(warehouse ? Registry.getConfig(warehouse)?.type : undefined) ?? "")
631+
return values.length ? `(${values.map((value) => sqlLiteral(value, mysql)).join(", ")})` : "(NULL)"
632+
})
633+
}
634+
635+
function sqlLiteral(value: unknown, mysql: boolean): string {
636+
if (typeof value === "number" && Number.isFinite(value)) return String(value)
637+
if (typeof value === "bigint") return value.toString()
638+
if (typeof value === "boolean") return value ? "TRUE" : "FALSE"
639+
const text = typeof value === "string" ? value : value instanceof Date ? value.toISOString() : JSON.stringify(value)
640+
const escaped = text.replaceAll("'", "''")
641+
return `'${mysql ? escaped.replaceAll("\\", "\\\\") : escaped}'`
642+
}
643+
569644
const BATCH_CLIPPED = "…[output clipped]"
570645

571646
/** Clip text to a byte and line budget on code-point boundaries, marking any clip. */

‎packages/opencode/test/altimate/mcp-server/server.test.ts‎

Lines changed: 103 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1152,3 +1152,106 @@ mcpTest("warehouse_add of a SQLite file this process cannot write opens it read-
11521152
expect(rows.body).toContain("quince")
11531153
})
11541154
// altimate_change end
1155+
1156+
// altimate_change start — chained batch steps: {{label.column}} value lists and {{label}} scratch tables
1157+
async function chainFixture() {
1158+
const dir = await mkdtemp(path.join(os.tmpdir(), "mcp-chain-test-"))
1159+
cleanups.push(() => rm(dir, { recursive: true, force: true }))
1160+
const a = new Database(path.join(dir, "a.sqlite"))
1161+
a.run("CREATE TABLE items (id INTEGER, name TEXT)")
1162+
a.run("INSERT INTO items VALUES (1, 'apple'), (2, 'pear'), (3, 'o''brien')")
1163+
a.run("CREATE TABLE big (id INTEGER)")
1164+
a.run(`INSERT INTO big VALUES ${Array.from({ length: 1500 }, (_, i) => `(${i})`).join(",")}`)
1165+
a.close()
1166+
const b = new Database(path.join(dir, "b.sqlite"))
1167+
b.run("CREATE TABLE prices (item_id INTEGER, label TEXT, price REAL)")
1168+
b.run("INSERT INTO prices VALUES (1, 'apple', 3.5), (2, 'pear', 1.25), (3, 'o''brien', 9.75)")
1169+
b.close()
1170+
const config = path.join(dir, "connections.json")
1171+
await Bun.write(config, JSON.stringify({ a: { type: "sqlite", path: "a.sqlite" }, b: { type: "sqlite", path: "b.sqlite" } }))
1172+
const server = await createServer({ connections: config })
1173+
const client = new Client({ name: "test", version: "1" })
1174+
const [clientTransport, serverTransport] = InMemoryTransport.createLinkedPair()
1175+
await server.connect(serverTransport)
1176+
await client.connect(clientTransport)
1177+
cleanups.push(async () => {
1178+
await client.close()
1179+
await server.close()
1180+
await Registry.closeAll()
1181+
})
1182+
return async (queries: Record<string, unknown>[], warehouse = "a") => {
1183+
const result = await client.callTool({ name: "sql_execute", arguments: { warehouse, queries } })
1184+
return { result, body: (result.content as { text?: string }[]).map((part) => part.text ?? "").join("\n") }
1185+
}
1186+
}
1187+
1188+
function block(body: string, label: string): string {
1189+
const start = body.indexOf(`[${label}]`)
1190+
if (start < 0) return ""
1191+
const next = body.indexOf("\n\n[", start + 1)
1192+
return body.slice(start, next < 0 ? undefined : next)
1193+
}
1194+
1195+
mcpTest("a later batch step filters on an earlier step's column on the same connection", async () => {
1196+
const batch = await chainFixture()
1197+
const { result, body } = await batch([
1198+
{ label: "pick", query: "SELECT id FROM items WHERE name IN ('apple', 'o''brien')" },
1199+
{ label: "names", query: "SELECT name FROM items WHERE id IN {{pick.id}} ORDER BY id" },
1200+
])
1201+
expect(result.isError).toBe(false)
1202+
expect(block(body, "names")).toContain("apple")
1203+
expect(block(body, "names")).toContain("o'brien")
1204+
expect(block(body, "names")).not.toContain("pear")
1205+
})
1206+
1207+
mcpTest("a batch step feeds another connection's IN list, and a scratch step joins stored step rows", async () => {
1208+
const batch = await chainFixture()
1209+
const { result, body } = await batch([
1210+
{ label: "ids", warehouse: "a", query: "SELECT id, name FROM items WHERE name <> 'pear'" },
1211+
{ label: "price", warehouse: "b", query: "SELECT item_id, label, price FROM prices WHERE label IN {{ids.name}}" },
1212+
{
1213+
label: "joined",
1214+
warehouse: "scratch",
1215+
query: "SELECT i.name, p.price FROM {{ids}} i JOIN {{price}} p ON p.item_id = i.id ORDER BY p.price DESC",
1216+
},
1217+
])
1218+
expect(result.isError).toBe(false)
1219+
expect(block(body, "price")).toContain("3.5")
1220+
expect(block(body, "price")).not.toContain("1.25")
1221+
expect(block(body, "joined")).toContain("9.75")
1222+
expect(block(body, "joined")).toContain("o'brien")
1223+
// A table reference outside scratch fails only that step.
1224+
const outside = await batch([
1225+
{ label: "ids", query: "SELECT id FROM items" },
1226+
{ label: "bad", query: "SELECT * FROM {{ids}}" },
1227+
])
1228+
expect(block(outside.body, "bad")).toContain('warehouse "scratch"')
1229+
})
1230+
1231+
mcpTest("a value list from a step with more than the bound row cap fails that step only", async () => {
1232+
const batch = await chainFixture()
1233+
const { body } = await batch([
1234+
{ label: "all", query: "SELECT id FROM big" },
1235+
{ label: "use", query: "SELECT count(*) AS n FROM big WHERE id IN {{all.id}}" },
1236+
{ label: "few", query: "SELECT id FROM big WHERE id < 3" },
1237+
{ label: "small", query: "SELECT count(*) AS n FROM big WHERE id IN {{few.id}}" },
1238+
])
1239+
expect(block(body, "use")).toContain("more than 1000 rows")
1240+
expect(block(body, "small")).toMatch(/\b3\b/)
1241+
})
1242+
1243+
mcpTest("a failing batch step fails its dependents and the remaining steps still run", async () => {
1244+
const batch = await chainFixture()
1245+
const { result, body } = await batch([
1246+
{ label: "bad", query: "SELEC id FROM items" },
1247+
{ label: "dep", query: "SELECT * FROM items WHERE id IN {{bad.id}}" },
1248+
{ label: "missing", query: "SELECT * FROM items WHERE id IN {{nope.id}}" },
1249+
{ label: "ok", query: "SELECT 42 AS answer" },
1250+
])
1251+
expect(result.isError).toBe(false)
1252+
expect(block(body, "bad")).toContain("Failed")
1253+
expect(block(body, "dep")).toContain('step "bad" failed')
1254+
expect(block(body, "missing")).toContain("names no earlier step")
1255+
expect(block(body, "ok")).toContain("42")
1256+
})
1257+
// altimate_change end

0 commit comments

Comments
 (0)