Export script: run multi-statement SQL with log pane and multiple output files

[BorisTyshkevich]

Part of #68 (Roadmap to 1.0.0) and a follow-up to #87.

Problem

#87 adds a direct Export path for one editor query: stream a full uncapped result to disk, bypassing the grid. But the editor also runs multi-statement scripts (runScript, src/ui/app.js:605). Pressing Export on a script needs different behavior:

• execute statements sequentially (CH's HTTP interface runs one statement per request), not one blob;
• preserve session semantics for SET / CREATE TEMPORARY TABLE;
• stream each row-returning statement to its own file, uncapped, without buffering;
• run non-row statements for effect and log OK/error;
• show a running log pane; stop safely on first failure; cancel cleanly.

This is its own UX, not something to bury inside the single-query flow.

Depends on #87 (read this first)

This issue lands after #87 (in active implementation separately) and reuses its helpers. Two of #87's deliverables need extending — these belong to #99's scope, applied to whatever #87 ships (don't expect #87 to include them):

1. exportQuery must accept params. Export button: stream full query result to disk as TSV (File System Access API, uncapped, bypasses the grid) #87's signature is exportQuery(ctx, sql, { queryId, signal, format }). Script export needs session_id to ride along, so thread params through into the URL alongside query_id — exactly like runQuery already does (ch-client.js:419):

   export async function exportQuery(ctx, sql, { queryId, signal, format, params } = {}) {
     const url = chUrl(ctx.origin, {
       format: format || 'TabSeparatedWithNames',
       params: { ...(queryId ? { query_id: queryId } : {}), ...(params || {}) },
     });
     const resp = await authedFetch(ctx, url, sql, signal);
     if (!resp.ok) throw new Error(parseExceptionText(await resp.text()));
     return resp;
   }

2. The Export button dispatches by statement count. Export button: stream full query result to disk as TSV (File System Access API, uncapped, bypasses the grid) #87 wires the button straight to single-file exportDirect. This issue refactors that into an exportEntry() that splits and branches — mirroring runEntry (app.js:694) — and makes exportDirect(sql) take the chosen statement so a selection/single-split statement flows through:

   async function exportEntry() {
     if (app.state.exporting.value) return;
     const ta = app.dom.editorTextarea;
     const sel = ta ? ta.value.slice(ta.selectionStart, ta.selectionEnd) : '';
     const input = sel.trim() !== '' ? sel : app.activeTab().sql;
     const statements = splitStatements(input);
     if (!statements.length) { flashToast('Nothing to export', { document: doc }); return; }
     if (statements.length === 1) return exportDirect(statements[0]);   // #87 single-file
     return exportScriptEntry(statements);
   }

Reused verbatim from #87: prepareExportSql ({ sql, format }), formatFileMeta, streamToFile (hold-back writer, returns the mid-stream message or null), findExceptionFrame, the showSaveFilePicker seam, and app.state.exporting.

Feature detection & button gating

showSaveFilePicker and showDirectoryPicker are the same File System Access family — every browser that has one has the other (Chromium ≥ 86, secure context). So the button's enabled/tooltip state stays exactly as #87 (app.canExport()); no new visible gate. A separate canExportScript() guards the script path defensively for the theoretical split:

const showDirectoryPicker = env.showDirectoryPicker
  || (win.showDirectoryPicker ? win.showDirectoryPicker.bind(win) : null);
app.canExportScript = () => !!showDirectoryPicker && !!secureCtx;   // secureCtx from #87

If it's ever false while single-export is enabled, the script path degrades to a toast (not a silent no-op):
Script export requires Chrome/Edge directory access over HTTPS.

Entry: exportScriptEntry

Directory picker first (transient-activation rule from #87), and skip the prompt entirely when there's nothing to export:

async function exportScriptEntry(statements) {
  if (!app.canExportScript()) {
    flashToast('Script export requires Chrome/Edge directory access over HTTPS', { document: doc });
    return;
  }
  if (!statements.some(isRowReturning)) {              // sync — check before prompting
    flashToast('Nothing to export — script has no result-producing statements.', { document: doc });
    return;
  }
  // Picker FIRST — before any await (activation). mode:'readwrite' or createWritable fails.
  let dir;
  try {
    dir = await showDirectoryPicker({ mode: 'readwrite' });
  } catch (e) {
    if (e && e.name === 'AbortError') return;          // dismissed → silent no-op
    flashToast('Folder dialog failed: ' + errMsg(e), { document: doc });
    return;
  }
  await ensureConfig();
  if (!(await getToken())) { chCtx.onSignedOut(); return; }
  await exportScript(statements, dir);
}

Writing several files may trigger a one-time per-directory write-permission prompt — expected.

Core helper — per-statement filename (src/core/export.js, 100% covered)

/**
 * Deterministic per-statement export filename: `<NNN>-<slug>.<ext>` (e.g.
 * `001-select.tsv`). `index` is the statement's 0-based position in the script;
 * the prefix is `index+1` zero-padded to 3, so it matches the log pane's `#`
 * column (non-row statements consume a number, leaving intentional gaps). `slug`
 * comes from inferQueryName → sanitized, lowercased, ≤ 24 chars (empty → 'query').
 * `taken` (Set of names already used this run) de-dupes with `-2`, `-3`, …
 * Pure — the caller adds the returned name to `taken`.
 */
export function scriptExportName(index, stmt, ext, taken) {
  const num = String(index + 1).padStart(3, '0');
  const slug = (inferQueryName(stmt).replace(/^Query · /, '') || stmt)
    .toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '').slice(0, 24) || 'query';
  let name = `${num}-${slug}.${ext}`;
  for (let n = 2; taken && taken.has(name); n++) name = `${num}-${slug}-${n}.${ext}`;
  return name;
}

Execution model (src/ui/app.js) — the corrected loop

Sequential, one shared session for the whole script, its own abort/query-id state (never app.state.run*, and distinct from #87's single-export state) so Cancel can reach the in-flight stream. The log lives in a tab.result variant that holds only per-statement metadata — status/file/bytes/time — never the exported rows (that's the memory guarantee + the "grid not populated with rows" criterion).

// export-script state — reassigned each iteration so Cancel targets the active stream
let exportScriptAbort = null;
let exportScriptQueryId = null;
let exportScriptCancelled = false;
let exportScriptTick = null;

async function exportScript(statements, dir) {
  const tab = app.activeTab();
  const sp = sessionParamsFor(tab, statements);        // one session for all statements
  const entries = statements.map((sql, i) => ({
    i, sql, type: isRowReturning(sql) ? 'rows' : 'effect',
    status: 'pending', file: null, bytes: 0, startedAt: null, ms: 0, error: null,
  }));
  tab.result = { scriptExport: entries };              // log pane — metadata only, no rows
  app.state.resultSort = { col: null, dir: 'asc' };
  exportScriptCancelled = false;
  app.state.exporting.value = true;
  const taken = new Set();
  // Live elapsed for the running row (bytes tick via onProgress; this ticks time).
  exportScriptTick = setInterval(() => renderResults(app), 200);
  renderResults(app);
  try {
    for (const e of entries) {
      if (exportScriptCancelled) { e.status = 'skipped'; continue; }
      const { sql, format } = prepareExportSql(e.sql);
      exportScriptQueryId = 'export-' + uid('');       // real prefix (uid drops it under randomUUID)
      exportScriptAbort = new AbortController();
      const signal = exportScriptAbort.signal;
      e.startedAt = now();
      e.status = e.type === 'rows' ? 'exporting' : 'running';
      renderResults(app);
      try {
        if (e.type !== 'rows') {
          // Run for effect (reuses runQuery; it returns { error }, doesn't throw on a CH error).
          const out = await ch.runQuery(chCtx, e.sql,
            { format: 'TSV', signal, queryId: exportScriptQueryId, params: sp });
          if (out.error != null) throw new Error(out.error);
          e.status = 'ok';
        } else {
          const { ext, mime } = formatFileMeta(format);
          const name = scriptExportName(e.i, e.sql, ext, taken);
          taken.add(name);
          e.file = name;
          const fileHandle = await dir.getFileHandle(name, { create: true });
          const resp = await ch.exportQuery(chCtx, sql,
            { queryId: exportScriptQueryId, signal, format, params: sp });
          const tag = resp.headers.get('X-ClickHouse-Exception-Tag');
          const midErr = await streamToFile(resp, fileHandle,
            { signal, tag, onProgress: (b) => { e.bytes = b; } });   // streamToFile does NOT throw mid-stream
          if (midErr) {                                 // ← must check the return (was ignored in draft)
            e.status = 'failed';
            e.error = 'File may be incomplete; server failed after streaming started. ' + midErr;
            e.ms = now() - e.startedAt;
            break;                                      // stop-on-first-failure
          }
          e.status = 'ok';
        }
        e.ms = now() - e.startedAt;
        renderResults(app);
      } catch (ex) {                                    // pre-header CH error / network / abort
        e.ms = now() - e.startedAt;
        if (ex && ex.name === 'AbortError') { e.status = 'cancelled'; exportScriptCancelled = true; }
        else { e.status = 'failed'; e.error = String((ex && ex.message) || ex); }
        break;                                          // stop-on-first-failure
      }
    }
    for (const e of entries) if (e.status === 'pending') e.status = 'skipped';  // after a stop
  } finally {
    clearInterval(exportScriptTick); exportScriptTick = null;
    exportScriptAbort = null;
    exportScriptQueryId = null;
    app.state.exporting.value = false;
    // A schema-mutating effect statement that actually ran refreshes the tree (mirrors runScript).
    if (entries.some((e) => e.status === 'ok' && isSchemaMutatingSql(e.sql))) app.loadSchema();
    renderResults(app);
  }
}

function cancelExportScript() {
  exportScriptCancelled = true;                         // stops the loop from starting the next
  if (exportScriptAbort) exportScriptAbort.abort();     // aborts the in-flight stream → AbortError
  ch.killQuery(chCtx, exportScriptQueryId, sqlString);  // best-effort, never throws
}

Wire exportEntry (button) and cancelExportScript (pane) into app.actions.

No retry. Unlike runScript (which retries once on SESSION_IS_LOCKED / transient network for read-only statements, app.js:644-653), export is strictly stop-on-first-failure with no retry: statements run one-at-a-time in a single session so the session lock can't self-collide, and a partially-written file shouldn't be silently re-attempted. (If a SESSION_IS_LOCKED ever surfaces it's reported like any other error.)

Log pane (src/ui/results.js)

r.scriptExport needs an early branch in both places that special-case r.script, before the normal-result paths:

1. buildToolbar (results.js:316) — add if (r && r.scriptExport) { … return toolbar; } before the normal toolbar, mirroring the r.script branch. It shows "Export script · N statements", live total elapsed/progress, and a Cancel export button (res-act cancel-act → app.actions.cancelExportScript()), and returns early so the log gets no view tabs, no row-limit selector, no Copy, no Export. (Falling through would render all of those and a re-entrant Export button — the bug this guards.)
2. renderResults body (results.js:135) — add the r.scriptExport body branch alongside r.script.

Body columns:

#	Statement	Type	Status	File	Bytes	Time

• Statement: truncated SQL (reuse the script grid's SQL cell). Type: rows / effect.
• Status: pending · running · exporting · ok · failed · cancelled · skipped (colored like the script grid's ok/error).
• File: the target name for row statements; blank for effect statements. Bytes: formatBytes(e.bytes), live. Time: e.ms (live for the running row via the 200 ms ticker).
• On a failed row, show e.error (incl. the "File may be incomplete…" note) inline.

The log is metadata only — it never holds result rows, so tab.result carries no exported data.

Output format (per statement)

Same rule as #87, applied per statement via prepareExportSql: keep an explicit trailing FORMAT <x>, else append FORMAT TabSeparatedWithNames; extension from formatFileMeta(format). Non-row statements produce no file. (A row-returning statement that yields zero rows still gets a header-only file — expected, matches isRowReturning's keyword classification.)

Error handling

Stop on first failure; already-completed files stay on disk.

• Pre-header CH error (exportQuery throws; or runQuery returns { error } which we rethrow) → row failed with the parsed exception; stop.
• Mid-stream CH error (streamToFile returns a message via the __exception__ frame + X-ClickHouse-Exception-Tag, format-independent — see Export button: stream full query result to disk as TSV (File System Access API, uncapped, bypasses the grid) #87) → row failed, file marked incomplete (File may be incomplete; server failed after streaming started.), the exception is excised from the file by the hold-back writer; stop.
• Network error (fetch TypeError) → row failed; stop.
• User cancel → active row cancelled, remaining skipped, completed files kept.

Browser support

Same stance as #87: Chromium + secure context only; Firefox/Safari and plain-HTTP Chromium are unavailable (button aria-disabled + tooltip from #87). Script path additionally needs showDirectoryPicker (ships with showSaveFilePicker, so effectively co-available).

Acceptance criteria

• Export on one statement uses the Export button: stream full query result to disk as TSV (File System Access API, uncapped, bypasses the grid) #87 single-file flow; on multiple, opens script-export.
• Script export asks for a directory once, before any async auth/config work, with mode: 'readwrite'; dismissing it is a silent no-op.
• A script with no row-returning statements shows "Nothing to export — …" and does not prompt for a directory.
• Statements execute sequentially in one shared session (sessionParamsFor); SET / CREATE TEMPORARY TABLE visible to later statements; never concurrent.
• Non-row statements run for effect, log ok/failed, and produce no file.
• Row-returning statements stream uncapped to separate files; explicit trailing FORMAT <x> respected per statement, else TabSeparatedWithNames; extension matches the format; names are NNN-slug.ext, de-duped, prefixed by script position.
• The log pane updates status, filename, bytes, and elapsed live.
• Cancel aborts the active request, issues KILL QUERY for the active export id, marks the current statement cancelled and the rest skipped, and keeps completed files.
• A mid-stream error is detected (via the __exception__ frame), surfaced in the log as failed/incomplete — not reported as success — and excised from the file.
• Stop-on-first-failure: a pre-header/network error halts the loop; remaining statements skipped; completed files remain.
• tab.result is not populated with exported rows (log holds metadata only); memory stays flat across a multi-million-row script export.
• exportQuery (Export button: stream full query result to disk as TSV (File System Access API, uncapped, bypasses the grid) #87) forwards params so session_id reaches CH; the Export button dispatches via exportEntry.
• npm test green at the per-file gate.

Test plan (per layer)

• core — scriptExportName: 001-select.tsv shape, zero-pad, slug from inferQueryName, fallback to 'query', -2/-3 dedup against taken, extension honored, index = script position. (prepareExportSql/formatFileMeta/findExceptionFrame already covered by Export button: stream full query result to disk as TSV (File System Access API, uncapped, bypasses the grid) #87.)
• net — exportQuery forwards params (session_id) and query_id into the URL; runQuery non-row path returns { error } on a CH error (rethrown by the loop).
• ui (app.js) — inject showDirectoryPicker (fake dir handle whose getFileHandle returns capturing file handles), showSaveFilePicker, fetch (ReadableStream), env.isSecureContext. Assert: 1-statement → exportDirect, N → exportScript; picker opens before ensureConfig/getToken; sequential order + one shared session_id on every request; non-row logs ok, no file created; row statements write to distinct files with correct names; streamToFile mid-stream return → row failed/incomplete, loop stops (regression guard for the ignored-return bug); pre-header throw → failed + stop + rest skipped; cancel mid-stream → active cancelled + rest skipped + killQuery(activeId) + completed files kept; tab.result never gains rows; exporting toggles and export state resets in finally; no-row-returning script → toast, no picker.
• ui (results.js) — buildToolbar early-returns for r.scriptExport with "Export script · N statements" + Cancel and no view tabs / row-limit / Copy / Export; renderResults body renders the columns + statuses; Cancel calls cancelExportScript.

Out of scope

Firefox/Safari fallback · zipping results into one archive · parallel export of statements · background export surviving tab close · resume/retry of partial exports.