snowflake · Packs

01 / apply it

yardr pack apply ./snowflake

From a clone of yardr-packs: the pack's directory. yardr pack check ./snowflake shows what it would change first. It goes into the workspace itself, and into no repo · depot.

yardr pack apply https://yardr.dev/packs/snowflake/

From this site, as the files below.

Lives in snowflake/ at a2b86e6 · SETUP.md

02 / the files

Every file, as it ships.

# Snowflake as a pack of kind source: the integration, imported into the
# yard and into no depot. What Snowflake is here: the declared source
# snowflake, which runs a SELECT and bounds its answer, lists the databases,
# schemas and tables, and reads a statement back by its handle, all
# through Snowflake's SQL API as one Snowflake user. One call, kept apart,
# runs any other statement the user's role allows. Its credential is
# minted from the user's key pair, by snowflake/mint, the mint command the
# pack brings beside its declaration.
#
#   yardr pack apply /Users/mckean/projects/repos/yardr-packs/snowflake
#
# The source is named snowflake: a pack is named after the service it
# talks to. Snowflake is per account, so the declaration ships a
# placeholder address that SETUP.md has the operator replace in the yard's
# copy. A pack of kind source asks no grants at import, so what a crew
# member may call is granted afterwards, by the setup notes. Every agent
# granted a call acts as that one Snowflake user: what its role may read or
# change is the bound, whatever the calls accept.
name: snowflake
kind: source
setup: SETUP.md

sources:
  snowflake:
    declaration: snowflake.yaml

# Suggested grants: named sets of the source's calls, printed numbered by
# pack show. Suggesting grants nothing: the operator grants them, and a
# terminal pack apply ignores them. A preset names read, every read call,
# or a call by its name: no word stands for every update, so write is the
# one update call, execute. The two are apart on purpose, and a grantee who
# needs both is given both.
presets:
  read:
    about: Run a SELECT with a bounded answer, list databases, schemas and tables, and read a statement back; change nothing
    grants:
      snowflake: [read]
  write:
    about: Run one statement that is not a SELECT, as the role allows; read is a preset of its own
    grants:
      snowflake: [execute]

#!/bin/sh
# live-check: the snowflake pack against a real Snowflake account, through
# the real yardr in a scratch yard of its own. Not part of ./test, which
# needs no network and no secret: this needs the account, the user with its
# public key set, and the key file.
#
#   ./live-check <account identifier> [user [key file]]
#
# The account identifier is written as the account's address writes it
# (myorg-acct1, or xy12345.us-east-2.aws); the user is YARDR and the key file
# ~/.yardr/keys/snowflake_rsa_key.p8 unless they are given, as SETUP.md names
# them. SNOWFLAKE_BASE replaces the address, for a dry run against
# testdata/standin.py on a loopback port. SNOWFLAKE_WAREHOUSE names the
# warehouse the SELECTs run on, where the user has no default one (a SELECT
# with no warehouse is refused 422, and the reason is not shown).
#
# It follows SETUP.md steps 3 and 4 (the pack applied, the operator's copy
# with the address, the three names given by yardr source key), then makes
# the check call and reads: SHOW DATABASES, SHOW SCHEMAS, SHOW TABLES, a
# SELECT of three rows from SNOWFLAKE_SAMPLE_DATA, the status of that
# statement, the same SELECT with async=true polled through status, and two
# statements that Snowflake should refuse (a second statement in a request,
# and a mistake). Every statement is a SELECT or a SHOW; execute is never
# called. It prints counts, column names and statuses, and never a token, a
# key or a value of a row. It exits 0 when everything that should work did,
# and 1 otherwise; the scratch yard and its server are removed either way.
set -u
cd "$(dirname "$0")" || exit 1
here=$(pwd)
account=${1:?usage: ./live-check <account identifier> [user [key file]]}
user=${2:-YARDR}
key=${3:-$HOME/.yardr/keys/snowflake_rsa_key.p8}
host=$(printf '%s' "$account" | LC_ALL=C tr 'A-Z' 'a-z')
base=${SNOWFLAKE_BASE:-https://$host.snowflakecomputing.com}
wh=${SNOWFLAKE_WAREHOUSE:-}
YARDR=${YARDR:-$(command -v yardr || echo /Users/mckean/go/bin/yardr)}
for tool in jq openssl; do
  command -v "$tool" >/dev/null 2>&1 || { echo "live-check: $tool is not on PATH"; exit 1; }
done
[ -x "$YARDR" ] || { echo "live-check: no yardr ($YARDR)"; exit 1; }
[ -f "$key" ] || { echo "live-check: the key file is not there"; exit 1; }

# A short path: a unix socket's is held to 100 bytes.
core=$(mktemp -d /tmp/snowlive.XXXXXX) || exit 1
mkdir -p "$core/h" "$core/mine"
yrun() { env -i HOME="$core/h" PATH="$PATH" "$YARDR" --home "$core/y" --actor tester "$@"; }
cleanup() {
  yrun server stop >/dev/null 2>&1
  rm -rf "${core:?}"
}
trap cleanup EXIT
trap 'exit 1' HUP INT TERM
fail=0
no() { echo "   FAILED: $*"; fail=1; }

# summ <json file> is what a ResultSet or a QueryStatus holds, without its values.
summ() {
  jq -c 'if has("error") then {error: .error} else {keys: keys, code: .code, message: .message, numRows: .resultSetMetaData.numRows, columns: [(.resultSetMetaData.rowType // [])[].name], rows: ((.data // []) | length)} end' "$1" 2>/dev/null || echo "(not JSON)"
}
# listed <json file> <column> <value> is true when the column holds the value.
listed() {
  jq -r --arg c "$2" --arg v "$3" '((.resultSetMetaData.rowType // []) | map(.name) | index($c)) as $i | if $i == null then "false" else ([.data[]?[$i]] | any(. == $v) | tostring) end' "$1" 2>/dev/null
}
# call <label> <call and parameters...> runs one call into $core/<label>.json and says how it went.
call() {
  label=$1
  shift
  yrun call snowflake "$@" --json >"$core/$label.json" 2>"$core/$label.err"
  rc=$?
  echo "-- $label: exit $rc $(summ "$core/$label.json")"
}

echo "== a scratch yard, the pack applied from its directory, and the operator's copy (base $base)"
yrun init --harness claude --operator tester >"$core/init.out" 2>&1 || { echo "the scratch yard could not be made: $(tail -c 200 "$core/init.out")"; exit 1; }
yrun pack apply "$here" --yes >"$core/out" 2>&1 || { echo "the pack did not apply: $(tail -c 200 "$core/out")"; exit 1; }
cp -R "$here/snowflake" "$core/mine/"
sed "s|^base: .*|base: $base|" "$here/snowflake.yaml" >"$core/mine/snowflake.yaml"
yrun source add "$core/mine/snowflake.yaml" >"$core/out" 2>&1 || { echo "source add failed: $(tail -c 200 "$core/out")"; exit 1; }
printf 'SNOWFLAKE_ACCOUNT=%s\nSNOWFLAKE_USER=%s\nSNOWFLAKE_KEY_FILE=%s\n' "$account" "$user" "$key" | yrun source key snowflake >"$core/out" 2>&1 ||
  { echo "source key failed: $(tail -c 200 "$core/out")"; exit 1; }
echo "account $account, user $user, key file given by its path"

echo "== source check: the minted token, and SHOW DATABASES"
yrun source check snowflake >"$core/check.out" 2>&1
rc=$?
echo "-- source check: exit $rc"
grep -iE "answered|refused|held|is not set|check call" "$core/check.out" | cut -c1-240 | head -4
[ "$rc" = 0 ] || no "the check call did not succeed, so Snowflake did not take the token (a 401 is Snowflake's: see SETUP.md step 4)"

echo "== the reads"
call databases databases
[ "$(listed "$core/databases.json" name SNOWFLAKE_SAMPLE_DATA)" = true ] && echo "   SNOWFLAKE_SAMPLE_DATA is listed" || no "SNOWFLAKE_SAMPLE_DATA is not among the databases"
call schemas schemas database=SNOWFLAKE_SAMPLE_DATA
[ "$(listed "$core/schemas.json" name TPCH_SF1)" = true ] && echo "   TPCH_SF1 is listed" || no "TPCH_SF1 is not among the schemas"
call tables tables database=SNOWFLAKE_SAMPLE_DATA schema=TPCH_SF1
[ "$(listed "$core/tables.json" name CUSTOMER)" = true ] && echo "   CUSTOMER is listed" || no "CUSTOMER is not among the tables"
call query query "statement=SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3" ${wh:+"warehouse=$wh"}
[ "$(jq '(.data // []) | length' "$core/query.json" 2>/dev/null)" = 3 ] || no "the SELECT did not answer three rows"

echo "== status of the finished statement"
h=$(jq -r '.statementHandle // empty' "$core/query.json" 2>/dev/null)
if [ -n "$h" ]; then
  call status status "handle=$h"
  [ "$(jq '(.data // []) | length' "$core/status.json" 2>/dev/null)" = 3 ] || no "status did not answer the three rows of the finished statement"
else
  no "the SELECT answered no statementHandle to read"
fi

echo "== async=true: the handle at once, then status until the rows are there"
call async-query query "statement=SELECT C_CUSTKEY FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER LIMIT 2" async=true ${wh:+"warehouse=$wh"}
ah=$(jq -r '.statementHandle // empty' "$core/async-query.json" 2>/dev/null)
if [ -n "$ah" ]; then
  n=0
  while [ "$n" -lt 15 ]; do
    n=$((n + 1))
    yrun call snowflake status "handle=$ah" --json >"$core/poll.json" 2>"$core/poll.err"
    jq -e '.data' "$core/poll.json" >/dev/null 2>&1 && break
    sleep 1
  done
  echo "-- status after $n poll(s): $(summ "$core/poll.json")"
  [ "$(jq '(.data // []) | length' "$core/poll.json" 2>/dev/null)" = 2 ] || no "status never answered the two rows of the async statement"
else
  no "the async SELECT answered no statementHandle"
fi

echo "== what Snowflake should refuse (these are expected to fail)"
call two-statements query "statement=SELECT 1; SELECT 2" ${wh:+"warehouse=$wh"}
if [ "$(jq 'has("error")' "$core/two-statements.json" 2>/dev/null)" = true ]; then echo "   a second statement in a request is refused, as expected"; else no "a second statement was not refused"; fi
call mistake query "statement=SELECT NOPE FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER LIMIT 1" ${wh:+"warehouse=$wh"}
if [ "$(jq 'has("error")' "$core/mistake.json" 2>/dev/null)" = true ]; then echo "   a mistake is refused, as expected (the reason is not shown: $(jq -r '.error' "$core/mistake.json" 2>/dev/null | cut -c1-90))"; else no "a mistake was not refused"; fi

if [ "$fail" = 0 ]; then echo "live-check: pass"; else echo "live-check: FAILED"; fi
exit $fail

# Snowflake

Snowflake as a pack of kind source, for a yard that answers a data question from
a warehouse. An agent runs a SELECT through Snowflake's SQL API and reads its
first page of rows, lists the databases, schemas and tables, and reads a
statement back by its handle. One call, kept apart, runs any other statement.
The yard acts as one Snowflake user, signed in with a key pair. Nothing here is
compiled into yardr; apply it with:

```sh
yardr pack apply /path/to/yardr-packs/snowflake
```

SETUP.md is the operator's steps, including the SQL that makes the user and the
role. `jira` in this repository is the pattern (a minted credential, a
placeholder base), and this file says where Snowflake differs.

## How it hangs together

**The calls.** All six go to one path of the account, `/api/v2/statements`, and
are declared in `snowflake.yaml`.

| call | kind | request | what it sends |
|---|---|---|---|
| `query` | read | `POST` | `statement`, which must begin with `SELECT` (case and leading space free); `timeout`, 60 s unless given; `database`, `schema`, `warehouse`, `role`, each optional; `async` |
| `databases` | read | `POST` | `SHOW DATABASES`; `warehouse`, `role` optional. The check call |
| `schemas` | read | `POST` | `SHOW SCHEMAS IN DATABASE {database}` |
| `tables` | read | `POST` | `SHOW TABLES IN SCHEMA {database}.{schema}` |
| `status` | read | `GET /api/v2/statements/{handle}` | the handle, a UUID |
| `execute` | update | `POST` | any one `statement`, with the options of `query` |

`database`, `schema`, `warehouse` and `role` are plain identifiers
(`^[A-Za-z_][A-Za-z0-9_$]*$`, 255 characters at most), which is also what keeps
`schemas` and `tables` from writing anything but a name into the statement they
build. A name that needs quotes is not accepted there; `query` on the database's
`INFORMATION_SCHEMA` reaches it. Left out, the options are the user's own
defaults, since Snowflake falls back to them and the file gives none.

**The answer.** `statementHandle`, `resultSetMetaData` (the columns in
`rowType`, `numRows`, `partitionInfo`) and `data`, the rows, every value a
string whatever its type, as Snowflake writes them. Only the first partition of
a result comes back: Snowflake splits a large result into partitions of its own
size, and the others are fetched with `?partition=N`, which no call here makes.
The metadata says when there is more (`numRows` is the whole result, the first
`partitionInfo` the part that came), so a statement is written with a `LIMIT`,
and an answer over 256 KB is refused, not cut. The code, message and times a
ResultSet also carries are left out; `status` keeps them.

**A statement that runs long.** A call ends after 30 seconds, and Snowflake
hands back a statement's handle (a 202) only after 45. So a statement that takes
30 to 45 seconds ends its call with a timeout and no handle, and Snowflake runs
it on, up to its `timeout` (60 s unless given). `async=true` on `query` or
`execute` is the way out: the request answers its handle at once, and `status`
polls it. A running statement answers with a code, a message and no data; a
finished one answers with its rows, as `query` does; a failed one is refused as
422, and a statement cut by its timeout as 408, on the request that made it and
on `status` after it. `async` is optional and left out of the request unless it
is given. It is there because without it the 202 that `status` exists to follow
could never be seen.

**The credential.** Snowflake takes a key-pair login as a JWT signed with the
user's private key, which lives an hour at most. `snowflake/mint` makes one from
three names that are no secret, the account identifier, the user and the path of
the key file, and the key itself is no variable and in no file the yard writes.

| claim | value |
|---|---|
| header | `{"alg":"RS256","typ":"JWT"}` |
| `iss` | `<ACCOUNT>.<USER>.SHA256:<fingerprint>`, the fingerprint the base64 of the SHA-256 of the public key's DER: the `RSA_PUBLIC_KEY_FP` of `DESCRIBE USER` |
| `sub` | `<ACCOUNT>.<USER>` |
| `iat`, `exp` | now, and 59 minutes on |

`ACCOUNT` and `USER` are uppercase. The account is written as Snowflake's
page on the token says: a period of `organization.account` becomes a hyphen
(`myorg-acct1` and `myorg.acct1` are the same), and a locator loses its region
(`xy12345.us-east-2.aws` is `XY12345`). The page's Python sample treats a
period differently, by cutting the identifier at the first one; the page's
prose is what is followed here. The core stops
using a token five minutes before it expires, so the mint is run about once an
hour. Header `X-Snowflake-Authorization-Token-Type` is not sent: the pages say
Snowflake examines the token when it is left out, and a declared header takes
one value.

The mint refuses with one fixed line that quotes no input, and nothing on
stdout, for a missing or malformed name, a key path that is not absolute, or is
no readable regular file, a key that has a passphrase (it is refused, and not
asked for), is not RSA or is no key, and a PATH without `jq` or `openssl`.

**The base.** An account is per operator, so the declaration ships the
placeholder `https://example.snowflakecomputing.com` and SETUP.md has the
operator write their own into the yard's copy, as jira's does. A pack applied
again puts the placeholder back; the credential is held back from it, so
nothing is sent to a host nobody owns, and SETUP.md step 7 says how to notice.

**Writing, kept apart.** `execute` is the one update call. It is granted by its
name alone, which is how an update call is granted: `read` covers none. The pack
suggests two presets, `read` (`[read]`) and `write` (`[execute]`), and a grantee
who needs both is given both. A preset names `read` or a call, and `update` is
no word of either, so `write` grants `execute` by its name. A pack applied from
a directory takes the grants by name (`yardr source grant snowflake execute
...`), since `--preset` is for a pack inside yardr's binary.

What the user's role may do is the bound. `query` accepts a SELECT only, which
catches a mistaken `INSERT` and stops nothing a SELECT can do; it does not stop
`SELECT 1; SELECT 2` either, but Snowflake takes one statement to a request
unless the request sets a statement count, and the file never does. A role that
cannot write answers `execute` with 422. The trial account's role is read-only,
so `execute` is tested against fixtures and has never been called live.

**No idempotency key.** Snowflake takes one as the `requestId` of the request's
query (with `retry=true`), and a declared source's idempotency header cannot
carry a query, so a call tried again after a lost answer may run twice. Do not
retry an `execute`: pass `async=true`, and read what became of it from `status`.

**What a refusal shows.** The core keeps no body of a request the service
refused, in case the body repeats the request. For Snowflake that is the reason
a statement failed: a SQL error comes back as 422 and the call says "the service
answered 422", with the error text lost. An agent that writes SQL has the
columns from `resultSetMetaData` and the tables from `tables`, and has to guess
at a mistake. It is the core's rule and not this pack's to change.

## Files

- `pack.yaml`, `snowflake.yaml`: the pack with its `read` and `write` presets,
and the declaration.
- `snowflake/mint`: POSIX sh, `openssl` and `jq`, the token from the key. The key
is read by `openssl` from its path. The token is built by the shell and printed
by `printf`, a builtin, so it is on no argument list, and nothing but a fixed
line reaches stderr.
- `live-check`: the pack against a real account, in a scratch yard of its own,
by hand: see below. It is not part of `test`.
- `testdata/`: the answers (`answers/*.json`, ResultSet, QueryStatus and error
bodies written from the reference's shapes, not recorded from an account), the
cases (`calls/*.case`) and their goldens, a stand-in for Snowflake
(`standin.py`), the scratch-yard part of the test (`core.sh`), and the
declaration's and the check's goldens.
- `.gitignore`: the operator's copy of the declaration, `snowflake.local.yaml`.

## Runtimes

`snowflake/mint` needs `sh`, `openssl` and `jq` 1.6 or later, on the PATH the
yard's server has. The whole test passes with the `openssl` macOS ships
(LibreSSL 3.3), with OpenSSL 3.6 and with OpenSSL 4.0, and the mint runs under
macOS's `sh` and under `dash`. The test needs those tools, and `yardr` and
`python3`; without one it fails and says which, since a skipped part would leave
the calls unpinned.

## Testing

```sh
./test
```

in this directory, about five seconds, no network and no secret. The gate
(`.yardr/test`) runs `yardr pack check` first.

- `mint`: eight forms of account identifier against a key pair made for the
run and thrown away (the claims, the header, the fingerprint worked out
apart in python, iat now and exp 59 minutes on, the signature verified
against the public key with `openssl`, `expires_at`), and the refusals above,
each with its one fixed line. No run writes a file, and no key byte, key path
or input reaches an output.
- the declaration as the yard reads it, against `testdata/declaration.golden`;
the kinds (five reads, `execute` the update); `pack show`'s two presets; the
grants SETUP.md teaches, read out of it and run as written.
- `testdata/calls/*.case`, each one `yardr call` made by the real yardr against
the stand-in, which answers the case's recorded-style body and status. A
golden holds the request the stand-in saw (method, path and query, headers, the
body), the exit status, what the call kept of the answer, and what it said.
The cases are the six calls with their options, the first partition of a
larger result, the 202 of a running statement, the 422, 408, 401 and 404 a
refused request shows, and the values a call turns away before sending: a
statement that is no SELECT, a comment or a `WITH` in front, an identifier
with a dot, a space or a quote, a handle that is no UUID.
- the check call, and that the token the yard sent was made by `snowflake/mint`
from the env file's names, is signed by the key and claims the account and
user; and that the key's bytes are in no file of the yard and no token is.

`UPDATE_GOLDEN=1 ./test` writes the goldens again. Read the diff before you
commit it.

`./live-check <account identifier> [user [key file]]` is the pack against a real
account, and the one thing here that needs the network, the account and the key.
It runs the real yardr in a scratch yard of its own, applies the pack from its
directory, gives the three names as SETUP.md does, and makes the check call and
these reads: the databases, the schemas and tables of `SNOWFLAKE_SAMPLE_DATA`, a
SELECT of three rows, the status of that statement, the same SELECT with
`async=true` polled through `status`, and two statements Snowflake should
refuse. Every one is a SELECT or a SHOW; `execute` is never called. It prints
counts, column names and statuses, and never a token, a key or a row, and it
exits 1 if anything that should work did not.

## What was checked, and what was not

Checked on 3 November 2026 against Snowflake's pages (authenticating,
submitting requests, handling responses, handling errors and the reference): the
claims and the fingerprint, the header and the token-type header, the body of a
statement and the meaning of `timeout`, the `async` parameter and the 45 seconds,
the 200, 202, 408 and 422 answers and their fields, `GET` of a handle, and
partitions.

Checked the same day against a Snowflake trial account (an organization-account
identifier, a service user with a key pair, a role that only reads), by
`./live-check` through the real yardr in a scratch yard, and once more by hand
for the long statement below. Everything it made was a SELECT or a SHOW:

- the token is taken: the claims `<ORGANIZATION>-<ACCOUNT>.<USER>` and the key's
fingerprint, signed RS256, with no token-type header and with the
`User-Agent: yardr` that is all the core sends;
- `SHOW DATABASES`, `SHOW SCHEMAS IN DATABASE` and `SHOW TABLES IN SCHEMA` are
taken as statements of the API, and answer their columns and rows (`databases`
is the check call, and answered 200);
- `query` answers a SELECT's columns and rows, and `status` of its handle
answers the same rows with the code `090001`;
- a second statement in a request is refused (422), as the pages' default of one
statement says, and so is a mistake in a statement, with the reason not shown;
- a statement that runs past a call's 30 seconds ends the call with "nothing
within 30s" and no handle (`SELECT SYSTEM$WAIT(35)` was the statement). With
`async=true` the handle comes back at once; `status` then answers `333334`,
"Asynchronous execution in progress", and no data while the statement runs,
and its rows once it is done;
- a statement cut by its own `timeout` (a 10 second wait with `timeout=3`) is
refused as 408 on the request that made it, and with `async=true` the
`status` that follows it answers 408 too. The pages say that of the request
only.

Not checked:

- `execute`, which has only been run against the stand-in: the trial's role
grants no writes, and nothing is to be written there;
- an account named by its locator (`xy12345.us-east-2.aws`): the mint's reading
of the forms is the pages' prose, tried offline on all of them;
- an account whose name has an underscore: the pages say nothing, and the mint
leaves it as given.

## Left out

The metrics-report flow and a dbt depot (the demo's other pieces), cancelling a
statement, the other partitions of a large result, multi-statement requests and
transactions, the `requestId` that would make a retried write safe (nothing in a
declared source carries it), and the token-type header. A table's columns are
`query` on the database's `INFORMATION_SCHEMA`, so there is no call for them.

# Setting up Snowflake

The pack wires a Snowflake account into the yard as the declared source
`snowflake`. An agent may run a SELECT and read its first page of rows, list
the databases, schemas and tables, and read a statement back by its handle.
One call, kept apart, runs any other statement. Snowflake sends nothing in, so
there is no hook and no webhook to set up.

**The yard acts as one Snowflake user.** Every agent granted a call of this
source is that user to Snowflake, with that user's role. `query` accepts a
SELECT and nothing else, which catches a mistaken statement and fences
nothing: a SELECT reads all that the role may read. What the role is granted in
Snowflake is the bound. Make the user for the yard alone, and give its role the
databases the yard should read and no others. Nobody is asked before a call is
made; the grant is the permission.

This file uses the names of the trial account the pack was proved on: the user
`YARDR`, the role `YARDR_READ`, the warehouse `YARDR_WH` (a small one that step 2
makes), the shared database `SNOWFLAKE_SAMPLE_DATA` and the key file
`~/.yardr/keys/snowflake_rsa_key.p8`. Change them where yours differ.

You need a Snowflake account and `ACCOUNTADMIN` on it for step 2. The pack's
mint needs `openssl` and `jq` on the yard's machine. Commands below call the
pack's directory (the `snowflake` directory of the yardr-packs checkout)
`<pack dir>`.

1. **The key pair, on the yard's machine.** Snowflake takes a key-pair login:
   the yard signs a short-lived token with the user's private key, and
   Snowflake checks it against the public key it was given. The private key
   stays in a file that only you can read. It is never pasted into a command,
   a card or a note: the yard is given its path, and the mint reads it from
   there. Skip this step if the key already exists. A new file takes its mode
   from the shell's umask, so narrow it first. Each command below is one thing
   to type, in a block of its own:
   ```sh
   umask 077
   ```
   ```sh
   mkdir -p ~/.yardr/keys
   ```
   ```sh
   openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -nocrypt -out ~/.yardr/keys/snowflake_rsa_key.p8
   ```
   ```sh
   openssl rsa -in ~/.yardr/keys/snowflake_rsa_key.p8 -pubout -out ~/.yardr/keys/snowflake_rsa_key.pub
   ```
   ```sh
   chmod 600 ~/.yardr/keys/snowflake_rsa_key.p8
   ```
   The key must be RSA, in PEM, and without a passphrase (`-nocrypt`): the mint
   refuses an encrypted key rather than ask for its passphrase. The `.pub` file
   is the public key and is no secret.

2. **The role and the user, in Snowflake.** In a worksheet, as `ACCOUNTADMIN`.
   The role reads the sample data and nothing else, so `execute` fails on this
   account until you grant it more (step 5 says when that is wanted). The
   warehouse is the compute a SELECT runs on and is billed while it runs; this
   one is the smallest size and suspends after a minute. An account that
   already has a warehouse the yard may use can name that one instead. The
   sample database is in most accounts; the `CREATE DATABASE` statement makes it
   from Snowflake's share where it is missing, and does nothing where it is
   there:
   ```sql
   USE ROLE ACCOUNTADMIN;
   CREATE WAREHOUSE IF NOT EXISTS YARDR_WH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
   CREATE DATABASE IF NOT EXISTS SNOWFLAKE_SAMPLE_DATA FROM SHARE SFC_SAMPLES.SAMPLE_DATA;
   CREATE ROLE IF NOT EXISTS YARDR_READ;
   GRANT USAGE ON WAREHOUSE YARDR_WH TO ROLE YARDR_READ;
   GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE_SAMPLE_DATA TO ROLE YARDR_READ;
   CREATE USER IF NOT EXISTS YARDR TYPE = SERVICE DEFAULT_ROLE = YARDR_READ DEFAULT_WAREHOUSE = YARDR_WH;
   GRANT ROLE YARDR_READ TO USER YARDR;
   ```
   A worksheet may run only the statement the cursor is in: select all of them
   before you run, or run them one at a time. Give the user its public key: the
   body of the `.pub` file on one line,
   without the `-----BEGIN PUBLIC KEY-----` and `-----END PUBLIC KEY-----`
   lines. This prints it:
   ```sh
   grep -v -e '-----' ~/.yardr/keys/snowflake_rsa_key.pub | tr -d '\n'; echo
   ```
   ```sql
   ALTER USER YARDR SET RSA_PUBLIC_KEY = '<the line above>';
   ```
   Snowflake names the key by its fingerprint, and the token the mint signs
   names the same one. Check that they agree. In the worksheet,
   ```sql
   DESCRIBE USER YARDR;
   ```
   shows `RSA_PUBLIC_KEY_FP`, a `SHA256:` and a string of letters. This prints
   the string for the key file, which must be the same after the `SHA256:`:
   ```sh
   openssl rsa -in ~/.yardr/keys/snowflake_rsa_key.p8 -pubout -outform DER 2>/dev/null | openssl dgst -sha256 -binary | openssl base64
   ```
   If Snowflake says the user does not exist, the statements above did not run:
   run them again. If `RSA_PUBLIC_KEY_FP` is empty or is another string, the user
   does not have this key. `CREATE USER IF NOT EXISTS` leaves a user that was
   already there as it was, so run the `ALTER USER` above again, and describe the
   user again. Snowflake answers every token it cannot match to a user and a key
   in the same way, with a 401 and `JWT token is invalid`, whichever of them is
   missing, so this check is how you tell.

3. **The pack and the account's address.** Apply the pack as the operator:
   ```sh
   yardr pack apply <pack dir>
   ```
   The declaration it installs gives the source a placeholder address,
   `https://example.snowflakecomputing.com`. A source's credential goes only to
   the hosts its file names, so the yard's copy of the file must name yours.
   Your account identifier is the organization's name and the account's, joined
   by a hyphen. Snowsight shows it in the details of the account, and a
   worksheet prints it:
   ```sql
   SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();
   ```
   An older account is known by its locator and region instead, such as
   `xy12345.us-east-2.aws`, and the address and the next step use that. Write
   the address into a copy of the declaration, beside the original so that the
   program it names is found, and add the copy to the yard. The copy is ignored
   by git.
   ```sh
   sed 's|^base: .*|base: https://<account identifier>.snowflakecomputing.com|' <pack dir>/snowflake.yaml > <pack dir>/snowflake.local.yaml
   ```
   ```sh
   yardr source add <pack dir>/snowflake.local.yaml
   ```
   ```sh
   yardr source show snowflake
   ```
   The first line of `source show` is the address in use. Until it is yours,
   `yardr source check snowflake` calls the placeholder and fails.

4. **The three names, in the yard.** The credential is three names, none of them
   a secret: `SNOWFLAKE_ACCOUNT`, the account identifier of step 3, written the
   way you gave it there (the mint turns it into what Snowflake wants in a
   token: capitals, and no region on a locator); `SNOWFLAKE_USER`, the user's
   name, `YARDR`; and `SNOWFLAKE_KEY_FILE`, the key's whole path, with no `~`
   since nothing expands it. The pack's `snowflake/mint` signs a token from the
   key at that path, once an hour. The key itself is not a variable and is in
   no file the yard writes. The three names go in the source's env file, mode
   0600, which `yardr source key` writes from lines on its standard input:
   ```sh
   printf 'SNOWFLAKE_ACCOUNT=%s\nSNOWFLAKE_USER=%s\nSNOWFLAKE_KEY_FILE=%s\n' '<account identifier>' 'YARDR' "$HOME/.yardr/keys/snowflake_rsa_key.p8" | yardr source key snowflake
   ```
   Then check the source:
   ```sh
   yardr source check snowflake
   ```
   The check mints a token, runs `SHOW DATABASES` and prints what the role can
   see. An answer of 401 means Snowflake did not take the token. The usual
   causes are a user that has no public key, or one that is not this key's
   (compare the fingerprints in step 2), an account identifier that is not the
   one the address uses, and a machine whose clock is wrong, since the token is
   dated by it.

5. **Who may call what.** The reads are granted by `read`, which covers
   `query`, `databases`, `schemas`, `tables` and `status`. `execute` is the one
   call that changes anything and is granted by its name alone, to each group or
   crew member that needs it. `yardr pack show snowflake` prints the two presets
   the pack suggests, `read` and `write`, and a pack applied from a directory
   takes them by name, as these lines give them:
   ```sh
   yardr source grant snowflake read --crew yardmaster
   ```
   A grantee who comes later gets one card:
   ```sh
   yardr bead ask <bead> "Let the reviewers query Snowflake" --run "source grant snowflake read --group reviewers" --context "The reviewers check an answer that was read from Snowflake; the card names each call it covers."
   ```
   `execute` is granted separately, and only where the role may change data. On
   the trial account it may not, so granting it only makes it answer 422:
   ```sh
   yardr source grant snowflake execute --crew yardmaster
   ```
   A grantee reads with the role the user has, and a call may name another with
   its `role` option. Give the user one role. A user given a second, wider role
   makes that role open to everyone who may `query`, who can then read with it.
   One caller may make 60 calls of a source in an hour, 10 of them updates; if
   an agent that explores a schema runs out, raise it with `yardr source set
   snowflake calls_per_hour=<n>`.

6. **Try it.** In a session that was granted `read`, or as the operator:
   ```sh
   yardr call snowflake databases
   ```
   ```sh
   yardr call snowflake query statement="SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 5"
   ```
   The answer is the statement's handle, the columns (`resultSetMetaData`) and
   the first page of the rows (`data`), every value a string. The call ends
   after 30 seconds; for a statement that may take longer, add `async=true` to
   get its handle at once, then read it from `status`, which answers a running
   statement with a code and a message and no data:
   ```sh
   yardr call snowflake status handle=<the statementHandle of the answer>
   ```
   To see the whole pack work against the account without touching your yard,
   run `live-check` of the pack's directory. It makes a scratch yard of its own,
   gives it the three names of step 4, and reads: the databases, the schemas and
   tables of the sample data, a SELECT, its status, and the same SELECT as an
   async statement. It makes only SELECTs and SHOWs, prints counts and column
   names and no row, and removes the scratch yard. A user other than `YARDR`, or
   a key file other than step 1's, follows as the next arguments:
   ```sh
   <pack dir>/live-check <account identifier>
   ```

7. **Updating the pack, and a key that changes.** A pack applied again replaces
   the source's declaration whole, and the placeholder address comes back with
   it. Your env file and the grants stay. After pulling the checkout, read what
   would change:
   ```sh
   yardr pack check <pack dir>
   ```
   If you apply the pack, the plan lists the address as `base: <your address> ->
   https://example.snowflakecomputing.com`. That line is how you notice. Until
   you add your copy again, the credential is held back from the placeholder,
   and `yardr source check snowflake` says so. The shorter way skips the apply:
   make the copy again from the new `snowflake.yaml` with step 3's `sed` and add
   it with `yardr source add`.

   To change the key, give the user the new public key as a second one, switch
   the file the yard names, and then take the old one away:
   ```sql
   ALTER USER YARDR SET RSA_PUBLIC_KEY_2 = '<the new public key>';
   ```
   Put the new private key at the path of `SNOWFLAKE_KEY_FILE`, or give the new
   path as in step 4, and check the source. A token already minted lives until
   its hour ends, an hour at most. Then
   ```sql
   ALTER USER YARDR UNSET RSA_PUBLIC_KEY;
   ```

8. **Ending.** Remove the source, which takes its grants with it, and the
   files:
   ```sh
   yardr source rm snowflake
   ```
   ```sh
   rm ~/.yardr/sources/snowflake.env ~/.yardr/keys/snowflake_rsa_key.p8 ~/.yardr/keys/snowflake_rsa_key.pub
   ```
   In Snowflake, `DROP USER YARDR;` and `DROP ROLE YARDR_READ;` end the access.

#!/bin/sh
# mint: the credential Snowflake's SQL API takes, made from the user's
# private key. stdin {"inputs": {"SNOWFLAKE_ACCOUNT": …, "SNOWFLAKE_USER": …,
# "SNOWFLAKE_KEY_FILE": …}, "scope": …}, stdout {"token": …,
# "expires_at": …}, as a source's mint_command is run.
#
# A key-pair login is a JWT signed with the private key (RS256). Its claims
# name the account, the user and the key by its fingerprint:
#
#   iss  <ACCOUNT>.<USER>.SHA256:<fingerprint>
#   sub  <ACCOUNT>.<USER>
#   iat  now           exp  now + 59 minutes (Snowflake takes an hour at most)
#
# The fingerprint is the base64 of the SHA-256 of the public key's DER, the
# value `DESCRIBE USER` shows as RSA_PUBLIC_KEY_FP. ACCOUNT and USER are
# uppercase. The account is written as Snowflake wants it in a claim:
#   myorg-acct1      organization and account, as the host writes them: as it is
#   myorg.acct1      the same with a period: the period becomes a hyphen
#   xy12345          an account locator: as it is
#   xy12345.us-east-1, xy12345.us-east-2.aws
#                    a locator with its region (and cloud): the region goes
# A period is read as a region when a second period or a hyphen follows it:
# an account name has no hyphen, and a region always has one.
#
# The key is read by openssl from its path, which is all that reaches an
# argument; the key's bytes pass through no variable, no argument and no
# file of ours. The token is built in the shell and printed by printf, a
# builtin, so it is on no argument list either. Nothing but a fixed line
# reaches stderr, and none quotes an input: openssl's and jq's own messages
# can name a path or a value, so both are dropped. Needs openssl and jq.
set -eu

fail() { echo "snowflake/mint: $*" >&2; exit 1; }

command -v jq >/dev/null 2>&1 || fail "needs jq on PATH"
command -v openssl >/dev/null 2>&1 || fail "needs openssl on PATH"

input=$(cat)

# field <name> is the string the inputs hold under name, and nothing if
# they hold none (or the input is no JSON).
field() { printf '%s' "$input" | jq -j --arg n "$1" '.inputs[$n] | strings' 2>/dev/null || true; }

account=$(field SNOWFLAKE_ACCOUNT)
user=$(field SNOWFLAKE_USER)
keyfile=$(field SNOWFLAKE_KEY_FILE)
[ -n "$account" ] && [ -n "$user" ] && [ -n "$keyfile" ] ||
  fail "want SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER and SNOWFLAKE_KEY_FILE among the inputs"

case $account in
  [!A-Za-z0-9]* | *[!A-Za-z0-9._-]*)
    fail "SNOWFLAKE_ACCOUNT is no account identifier: letters, digits, hyphens, underscores and periods" ;;
esac
case $user in
  *[[:space:]]* | *[[:cntrl:]]*) fail "SNOWFLAKE_USER is no user name: it has a space or a control character" ;;
esac
case $account in
  *.*.* | *.*-*) account=${account%%.*} ;;
  *.*) account=${account%%.*}-${account#*.} ;;
esac
# ASCII capitals only, on purpose (the C locale and the ranges): that is what a
# claim holds, and a locale's idea of upper case could change a name.
# shellcheck disable=SC2018,SC2019
account=$(printf '%s' "$account" | LC_ALL=C tr 'a-z' 'A-Z')
# shellcheck disable=SC2018,SC2019
user=$(printf '%s' "$user" | LC_ALL=C tr 'a-z' 'A-Z')

# The core runs a command in a directory of its own choosing, so only a
# whole path names the key.
case $keyfile in
  /*) ;;
  *) fail "SNOWFLAKE_KEY_FILE is no absolute path: a ~ is not expanded" ;;
esac
[ -f "$keyfile" ] && [ -r "$keyfile" ] || fail "SNOWFLAKE_KEY_FILE names no readable regular file"

# -passin pass: gives a key that wants a passphrase an empty one, so it is
# refused here instead of asked for on a terminal; rsa also refuses a key
# of another kind, which would sign something Snowflake does not read.
openssl rsa -in "$keyfile" -passin pass: -noout >/dev/null 2>&1 </dev/null ||
  fail "the key in SNOWFLAKE_KEY_FILE is no RSA private key without a passphrase (PEM, as openssl pkcs8 -nocrypt writes it)"

# A pipeline's status is its last command's, so what the first one made is
# looked at: the hash of nothing is a fingerprint too, and must not pass.
der=$(openssl rsa -in "$keyfile" -passin pass: -pubout -outform DER 2>/dev/null </dev/null | openssl base64 -A)
[ -n "$der" ] || fail "cannot read the public key from the key in SNOWFLAKE_KEY_FILE"
fp=$(printf '%s' "$der" | openssl base64 -d -A | openssl dgst -sha256 -binary | openssl base64 -A)

# b64url is base64 without padding in the URL alphabet, as a JWT writes it.
b64url() { openssl base64 -A | tr '+/' '-_' | tr -d '='; }

now=$(date +%s)
exp=$((now + 3540))
header=$(printf '%s' '{"alg":"RS256","typ":"JWT"}' | b64url)
payload=$(jq -cjn --arg iss "$account.$user.SHA256:$fp" --arg sub "$account.$user" --argjson iat "$now" --argjson exp "$exp" \
  '{iss: $iss, sub: $sub, iat: $iat, exp: $exp}' | b64url)
sig=$(printf '%s' "$header.$payload" | openssl dgst -sha256 -sign "$keyfile" -passin pass: 2>/dev/null | b64url)
[ -n "$sig" ] || fail "cannot sign with the key in SNOWFLAKE_KEY_FILE"

# The expiry is the token's own, to the second; the core stops using the
# token five minutes before it.
expires_at=$(jq -nr --argjson t "$exp" '$t | todate')
printf '{"token":"%s.%s.%s","expires_at":"%s"}\n' "$header" "$payload" "$sig" "$expires_at"

# Snowflake as a declared source: what an agent may ask of one Snowflake
# account through its SQL API, by one declared HTTPS request per thing, with
# a key-pair JWT as the credential. Six calls: a SELECT with a bounded answer,
# the lists of databases, schemas and tables, a statement read back by its
# handle, and, apart from them, one that runs any other statement. Jira's
# declaration (jira in this repository) is the pattern, and Snowflake
# differs where noted.
#
# Checked against Snowflake's documentation on 3 November 2026 (the SQL API
# pages: authenticating, submitting requests, handling responses, handling
# errors, and the reference), against the real yardr in a scratch yard with a
# stand-in for Snowflake, and, the same day, against a trial account
# (README.md, "What was checked", says what was and was not):
#
#   POST /api/v2/statements takes the JSON body {statement, timeout, database,
#     schema, warehouse, role}. timeout is in seconds, and 0 is the most
#     Snowflake allows, 604800; left out, the STATEMENT_TIMEOUT_IN_SECONDS
#     parameter applies (2 days unless set). database, schema,
#     warehouse and role are case-sensitive, and left out they are the
#     user's defaults (DEFAULT_NAMESPACE, DEFAULT_WAREHOUSE, DEFAULT_ROLE),
#     so this file gives none. A request with more than one statement needs
#     MULTI_STATEMENT_COUNT, which this file does not set, so a second
#     statement is Snowflake's refusal.
#   The query parameter async=true runs the statement asynchronously and
#     answers its handle at once. Without it Snowflake answers the result if
#     the statement is done in 45 seconds, and a handle (202) if it is not.
#   200 answers a ResultSet {code, sqlState, message, statementHandle,
#     createdOn, statementStatusUrl, resultSetMetaData, data, stats}: data is
#     the first partition of the rows, every value a string; stats is there
#     for DML. 202 answers a QueryStatus {code, sqlState, message,
#     statementHandle, createdOn, statementStatusUrl}. 408 (the timeout cut
#     the statement) and 422 (the statement failed) answer a QueryStatus and a
#     QueryFailureStatus, which yardr does not keep of a refused request.
#   GET /api/v2/statements/{statementHandle} answers 202 and a QueryStatus
#     while the statement runs, and 200 and the ResultSet when it is done; one
#     the timeout cut answers 408 there too (seen on an account, the pages say
#     it of the POST). The other partitions of a large result are fetched with
#     ?partition=N, which this file does not declare.
#   Headers: Authorization: Bearer <JWT>, Accept and Content-Type
#     application/json, and a User-Agent, which yardr sets to yardr.
#     X-Snowflake-Authorization-Token-Type is optional: "if you omit it,
#     Snowflake determines the token type by examining the token", and a
#     declared header takes one value, so none is declared.
#
# The account settled what the pages leave open: databases, schemas and tables
# are SHOW statements, which the pages neither list as taken nor leave out,
# and the API takes them; and it takes yardr's bare User-Agent for the
# "name/version" the pages ask for. Not checked live: execute, which the
# trial's read-only role could not run, and an account named by its locator.
#
# The name is snowflake: a pack is named after the service it talks to.
#
# A command is a list, run without a shell. A path that does not start
# with / is a file beside this one, which `yardr source add` copies into
# the yard.
name: snowflake
about: Snowflake SQL API. Runs a SELECT with a bounded answer, lists databases, schemas and tables, reads a statement by its handle; execute runs any other statement.

# The names of the variables, never the values. Snowflake takes a key-pair
# login as a JWT, which lives an hour at most, signed with the user's
# private key. A call's header takes one {credential}, so the three names
# below select the credential and snowflake/mint makes it, SNOWFLAKE_JWT,
# once an hour: the account identifier, the user's name, and the path of
# the file that holds the private key. All three are asked in the clear
# because none is a secret: the key itself is never a variable, and no
# command, event or note holds it, since the mint reads it from its path.
# They live in the source's env file.
credential:
  env: SNOWFLAKE_JWT
  header: "Authorization: Bearer {credential}"
  mint:
    - name: SNOWFLAKE_ACCOUNT
      secret: false
    - name: SNOWFLAKE_USER
      secret: false
    - name: SNOWFLAKE_KEY_FILE
      secret: false
  mint_command: [snowflake/mint]

# ---- what an agent may ask of it, through `yardr call` --------------------
# Declared HTTPS requests, granted one by one. A command is never something
# an agent calls.
#
# base is the account's address, and an account is per operator, so this is
# a placeholder that SETUP.md has the operator replace in the yard's copy of
# this file: the credential goes to the hosts a file names and to no others.
# A pack applied again replaces the yard's copy and puts this one back, and
# the credential then goes nowhere until the copy is added again (SETUP.md
# says how to notice).
#
# No update call carries an idempotency key. Snowflake takes one as the
# requestId of the request's query (with retry=true), and a declared header
# cannot carry a query, so idempotency is not named: an update tried again
# after a lost answer may run twice. The way out is async=true on execute,
# which answers the handle at once, and status, which says what became of
# the statement.
#
# Every call goes to the one path, /api/v2/statements, and the one account.
# What the user's role may read or change is Snowflake's to bound: the
# pattern on query is a guard against a mistaken statement, not a fence.
base: https://example.snowflakecomputing.com
check: databases
calls:
  query:
    kind: read
    about: >-
      Run one SELECT and read the first page of its rows. Give the statement a
      LIMIT: only the first partition comes back (numRows and partitionInfo in
      resultSetMetaData say how much more there is), and an answer over 256 KB
      is refused, not cut. The statement must begin with SELECT, and what the
      role may read is Snowflake's to enforce. An answer that is a
      statementHandle and nothing else is a statement still running: read it
      from status. The call ends after 30 s, so pass async=true for a long
      statement and get the handle at once. timeout is the seconds Snowflake
      lets the statement run, 60 unless given; 0 is seven days, not none.
    params:
      # Free SQL, held to a SELECT at its start: a mistaken INSERT or DROP
      # does not reach Snowflake, and one a role would not allow anyway is
      # refused there. A comment, a WITH or a bracket in front is refused
      # too: put a WITH in a subquery.
      statement: { type: string, pattern: '(?i)^\s*select\b', max: 8000 }
      # Seconds Snowflake lets the statement run before it cancels it. 60
      # unless given; 0 is Snowflake's maximum of seven days, so give a
      # number. The call itself ends after 30 s whatever this is.
      timeout: { type: int, default: 60 }
      # The user's own defaults when left out. Names as SHOW lists them,
      # case and all, as plain identifiers.
      database: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      schema: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      warehouse: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      role: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      async: { type: bool, optional: true }
    request:
      method: POST
      path: /api/v2/statements
      query: { async: "{async}" }
      body: { statement: "{statement}", timeout: "{timeout}", database: "{database}", schema: "{schema}", warehouse: "{warehouse}", role: "{role}" }
    # Not the code, message and times: the rows and what describes them. A
    # statement still running has a handle and no data.
    answer: { select: [statementHandle, resultSetMetaData, data], max: 262144 }
  databases:
    kind: read
    about: List the databases the role can see. The check call.
    params:
      warehouse: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      role: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
    request:
      method: POST
      path: /api/v2/statements
      body: { statement: "SHOW DATABASES", warehouse: "{warehouse}", role: "{role}" }
    answer: { select: [statementHandle, resultSetMetaData, data], max: 262144 }
  schemas:
    kind: read
    about: >-
      List the schemas of one database, named as databases gives it. A name
      that needs quotes is not accepted; use query on its INFORMATION_SCHEMA.
    params:
      # An unquoted identifier: no space, quote, dot or semicolon can reach
      # the statement it is written into.
      database: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, log: true }
    request:
      method: POST
      path: /api/v2/statements
      body: { statement: "SHOW SCHEMAS IN DATABASE {database}" }
    answer: { select: [statementHandle, resultSetMetaData, data], max: 262144 }
  tables:
    kind: read
    about: >-
      List the tables of one schema. Their columns are query's to read, from
      the database's INFORMATION_SCHEMA.COLUMNS or a SELECT with LIMIT 1 and
      its rowType.
    params:
      database: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, log: true }
      schema: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, log: true }
    request:
      method: POST
      path: /api/v2/statements
      body: { statement: "SHOW TABLES IN SCHEMA {database}.{schema}" }
    answer: { select: [statementHandle, resultSetMetaData, data], max: 262144 }
  status:
    kind: read
    about: >-
      Read one statement by the statementHandle an earlier call answered with.
      Running: a code and a message and no data. Done: the first partition of
      its rows, as query answers. Failed: the call is refused with 422, or with
      408 where its timeout cut it, and the reason is not shown.
    params:
      handle: { type: string, pattern: "^[0-9A-Fa-f]{8}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{12}$", max: 36, log: true }
    request:
      method: GET
      path: "/api/v2/statements/{handle}"
    # The six fields of a QueryStatus, which is what a running statement
    # answers, and with them the result, because a statement that is done
    # answers the same request with its ResultSet.
    answer: { select: [code, sqlState, message, statementHandle, createdOn, statementStatusUrl, resultSetMetaData, data], max: 262144 }
  execute:
    kind: update
    about: >-
      Run one statement that is not a SELECT (insert, create, drop, grant) as
      the role allows. It changes something, and a call tried again after a
      lost answer may run twice: async=true answers the statementHandle at
      once, and status says what became of the statement. timeout is in
      seconds, 60 unless given, and 0 is seven days, not none.
    params:
      # Free SQL: this is the call that is not held to a SELECT, and the
      # reason it is an update. Snowflake runs one statement unless the
      # request sets a statement count, which this file does not.
      statement: { type: string, max: 8000 }
      timeout: { type: int, default: 60 }
      database: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      schema: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      warehouse: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      role: { type: string, pattern: "^[A-Za-z_][A-Za-z0-9_$]*$", max: 255, optional: true, log: true }
      async: { type: bool, optional: true }
    request:
      method: POST
      path: /api/v2/statements
      query: { async: "{async}" }
      body: { statement: "{statement}", timeout: "{timeout}", database: "{database}", schema: "{schema}", warehouse: "{warehouse}", role: "{role}" }
    # stats is there for DML: the rows inserted, updated and deleted.
    answer: { select: [statementHandle, resultSetMetaData, data, stats], max: 262144 }

#!/bin/sh
# The snowflake pack's tests: what the gate runs beside yardr pack check.
# Needs no network and no secret, done in seconds. Needs jq, openssl,
# python3 and yardr, and fails without any of them: a part that was skipped
# would leave the pack unpinned.
#
#   mint   snowflake/mint against a key pair made here and thrown away. The
#          claims for each form of account identifier (uppercase, the
#          period of organization.account a hyphen, a locator's region
#          dropped); the header, the fingerprint (computed apart, in
#          python), iat and exp 59 minutes apart; the signature verified
#          against the public key; the answer's shape and expiry. Then what
#          it refuses: a missing, empty or malformed input, a key path that
#          is no absolute, readable, regular file, a key that is encrypted,
#          not RSA or no key, and a PATH without jq or openssl. Each
#          refusal is one fixed line that quotes no input, with nothing on
#          stdout. No run writes a file, and no key byte, key path or
#          input reaches an output.
#   core   testdata/core.sh: the real yardr in a scratch yard. The
#          declaration as the yard reads it, the presets and the grants
#          SETUP.md teaches, the check, and each testdata/calls/*.case, one
#          `yardr call` against a stand-in for Snowflake on a loopback port
#          (testdata/standin.py) that answers recorded-style ResultSet and
#          QueryStatus bodies. A case's golden holds the request the
#          stand-in saw, the exit status, what the call kept of the answer
#          and what it said on stderr; a call the declaration should turn
#          away shows as no request at all. The token the yard sent is
#          checked against the public key, so the whole way, from the env
#          file through the real mint to the request, is held.
#
# UPDATE_GOLDEN=1 ./test writes the goldens again from what the programs
# print; read the diff before keeping it.
set -u
cd "$(dirname "$0")" || exit 1
here=$(pwd)
fail=0

for tool in jq openssl python3; do
  command -v "$tool" >/dev/null 2>&1 || { echo "test: $tool is not on PATH: the pack needs it"; exit 1; }
done
YARDR=${YARDR:-$(command -v yardr || echo /Users/mckean/go/bin/yardr)}
[ -x "$YARDR" ] || { echo "test: no yardr ($YARDR): the declaration and the calls are read through it"; exit 1; }

tmp=$(mktemp -d)
# The core part's scratch yard and stand-in are made when its turn comes,
# and the yard's server, which its first command starts, would outlive the
# directory it owns: stop it first. core is empty until then.
core=
standin_pid=
cleanup() {
  [ -z "${standin_pid:-}" ] || kill "$standin_pid" 2>/dev/null
  if [ -n "${core:-}" ]; then
    env -i HOME="$core/h" PATH="$PATH" "$YARDR" --home "$core/y" --actor tester server stop >/dev/null 2>&1
    rm -rf "${core:?}"
  fi
  rm -rf "${tmp:?}"
}
trap cleanup EXIT
trap 'exit 1' HUP INT TERM

# check <name> <actual file> <golden file>: ok when they are the same.
check() {
  if [ "${UPDATE_GOLDEN:-}" = 1 ]; then
    if ! cmp -s "$2" "$3"; then cp "$2" "$3"; echo "test: $1 golden written"; fi
    return
  fi
  if [ ! -e "$3" ]; then
    echo "test: $1 has no golden $3 (UPDATE_GOLDEN=1 ./test writes it)"
    fail=1
  elif cmp -s "$2" "$3"; then
    echo "test: $1 ok"
  else
    echo "test: $1 differs from $3:"
    diff "$3" "$2" | sed 's/^/    /' | head -30
    fail=1
  fi
}
# ok <name> <condition's exit status> [detail]
ok() {
  if [ "$2" = 0 ]; then echo "test: $1 ok"; else echo "test: $1 FAILED${3:+: $3}"; fail=1; fi
}

# ---- the key pair, made here and thrown away ---------------------------------
keys=$tmp/keys
mkdir "$keys" "$tmp/work" "$tmp/home"
(
  umask 077
  openssl genrsa -out "$keys/k.pem" 2048 >/dev/null 2>&1 &&
    openssl pkcs8 -topk8 -inform PEM -in "$keys/k.pem" -nocrypt -out "$keys/k.p8" &&
    openssl rsa -in "$keys/k.p8" -pubout -out "$keys/pub.pem" 2>/dev/null &&
    openssl pkcs8 -topk8 -inform PEM -in "$keys/k.pem" -v2 aes256 -passout pass:throwaway -out "$keys/encrypted.p8" &&
    openssl ecparam -name prime256v1 -genkey -noout -out "$keys/ec.pem" 2>/dev/null &&
    openssl pkcs8 -topk8 -inform PEM -in "$keys/ec.pem" -nocrypt -out "$keys/ec.p8" &&
    printf 'this is no key\n' >"$keys/junk.p8" &&
    : >"$keys/empty.p8" &&
    cp "$keys/k.p8" "$keys/locked.p8" &&
    chmod 000 "$keys/locked.p8"
) || { echo "test: the throwaway keys could not be made"; exit 1; }
mkdir "$keys/dir.p8"

# What the key is looked for by: a line of its body, which no output of the
# mint may hold.
needle=$(sed -n 2p "$keys/k.p8")
[ "${#needle}" -ge 20 ] || { echo "test: the throwaway key has no body line to look for"; exit 1; }

# The fingerprint of the public key, worked out apart from the mint: the
# SHA-256 of the DER, which is the PEM's body decoded.
fp=$(python3 -I -c '
import base64, hashlib, sys
body = "".join(l for l in open(sys.argv[1]).read().splitlines() if not l.startswith("-----"))
print("SHA256:" + base64.b64encode(hashlib.sha256(base64.b64decode(body)).digest()).decode())' "$keys/pub.pem")
case $fp in SHA256:*=) ;; *) echo "test: no fingerprint worked out: $fp"; exit 1 ;; esac

# ---- a JWT, taken apart -------------------------------------------------------
# b64url_decode <text> writes the bytes that base64url text stands for.
b64url_decode() {
  _s=$(printf '%s' "$1" | tr '_-' '/+')
  case $((${#_s} % 4)) in 2) _s="$_s==" ;; 3) _s="$_s=" ;; esac
  printf '%s' "$_s" | openssl base64 -d -A
}
# jwt_part <token> <1 | 2> is the header or the payload, decoded.
jwt_part() {
  _r=${1#*.}
  if [ "$2" = 1 ]; then b64url_decode "${1%%.*}"; else b64url_decode "${_r%%.*}"; fi
}
# jwt_signed <token> <public key> is 0 when the third part is RS256 over the
# first two: RSASSA-PKCS1-v1_5 with SHA-256, which is what openssl dgst
# -sha256 -verify checks of an RSA key.
jwt_signed() {
  _r=${1#*.}
  b64url_decode "${_r#*.}" >"$tmp/sig.bin"
  printf '%s' "${1%.*}" | openssl dgst -sha256 -verify "$2" -signature "$tmp/sig.bin" >/dev/null 2>&1
}

# holding <tokens file> <dir>... lists the files under the dirs that hold any
# of the tokens, one token to a line of the file. There are one or two tokens
# in a run of core.sh: the check is made by the command line's process and the
# calls by the server's, each with a cache of its own, so a second boundary
# between them makes a second token. The helper is tried here with both.
holding() {
  _f=$1
  shift
  while IFS= read -r _t; do
    [ -z "$_t" ] || grep -rlF -e "$_t" "$@" 2>/dev/null
  done <"$_f"
}
mkdir "$tmp/held"
printf 'aaa.bbb.ccc\nddd.eee.fff\n' >"$tmp/held.tokens"
printf 'nothing to find\n' >"$tmp/held/clean"
[ -z "$(holding "$tmp/held.tokens" "$tmp/held")" ]
ok "the search for tokens in files finds none where there are none, with two tokens" $?
printf 'x ddd.eee.fff y\n' >"$tmp/held/leak"
[ "$(holding "$tmp/held.tokens" "$tmp/held")" = "$tmp/held/leak" ]
ok "the search for tokens in files finds the second of two" $?

# ---- mint ----------------------------------------------------------------------
# mint_input <account> <user> <key file> is what the core writes on stdin.
mint_input() {
  jq -cn --arg a "$1" --arg u "$2" --arg k "$3" '{inputs: {SNOWFLAKE_ACCOUNT: $a, SNOWFLAKE_USER: $u, SNOWFLAKE_KEY_FILE: $k}, scope: ""}'
}
# mint_run <stdin> [PATH] runs the mint as the core does, in a bare
# environment, from a directory of its own: stdout is in $tmp/m.out, stderr
# in $tmp/m.err, the status in $rc.
mint_run() {
  printf '%s' "$1" | (cd "$tmp/work" && env -i PATH="${2:-$PATH}" HOME="$tmp/home" "$here/snowflake/mint") >"$tmp/m.out" 2>"$tmp/m.err"
  rc=$?
}
# mint_leaks says, on stdout, what of the key or the inputs the last run
# let out.
mint_leaks() {
  if [ -s "$tmp/m.out" ] && grep -qF -e "$needle" "$tmp/m.out"; then echo " the key's bytes are in stdout;"; fi
  if grep -qF -e "$needle" -e "$keys" -e CANARY "$tmp/m.err"; then echo " an input or the key is in stderr;"; fi
  if [ -n "$(find "$tmp/work" "$tmp/home" -type f 2>/dev/null)" ]; then echo " a file was written;"; fi
}

# mint_claims <account in> <user in> <account in the claims> <user in the claims>
mint_claims() {
  t0=$(date +%s)
  mint_run "$(mint_input "$1" "$2" "$keys/k.p8")"
  t1=$(date +%s)
  bad=$(mint_leaks)
  [ "$rc" = 0 ] || bad="$bad exit $rc;"
  [ ! -s "$tmp/m.err" ] || bad="$bad stderr is not empty;"
  tok=$(jq -r '.token // empty' "$tmp/m.out" 2>/dev/null)
  if [ -z "$tok" ]; then
    bad="$bad no token;"
  else
    jq -e '(keys == ["expires_at", "token"]) and (.token | test("^[A-Za-z0-9_-]+\\.[A-Za-z0-9_-]+\\.[A-Za-z0-9_-]+$"))' "$tmp/m.out" >/dev/null 2>&1 ||
      bad="$bad the answer is not {expires_at, token} with a token of three base64url parts;"
    jwt_part "$tok" 1 | jq -e '. == {alg: "RS256", typ: "JWT"}' >/dev/null 2>&1 || bad="$bad the header is not RS256 JWT;"
    payload=$(jwt_part "$tok" 2)
    printf '%s' "$payload" | jq -e --arg iss "$3.$4.$fp" --arg sub "$3.$4" --argjson t0 "$t0" --argjson t1 "$t1" \
      '(keys == ["exp", "iat", "iss", "sub"]) and .iss == $iss and .sub == $sub and .iat >= $t0 and .iat <= $t1 and (.exp - .iat) == 3540' >/dev/null 2>&1 ||
      bad="$bad the claims are not iss $3.$4.<fingerprint>, sub $3.$4, iat now and exp 59 minutes on: $payload;"
    jq -e --argjson e "$(printf '%s' "$payload" | jq '.exp // 0')" '.expires_at == ($e | todate)' "$tmp/m.out" >/dev/null 2>&1 ||
      bad="$bad expires_at is not exp as RFC 3339;"
    jwt_signed "$tok" "$keys/pub.pem" || bad="$bad the signature does not hold against the public key;"
  fi
  [ -z "$bad" ]
  ok "mint claims for account $1, user $2" $? "$bad"
}
mint_claims myorg-acct1 yardr MYORG-ACCT1 YARDR
mint_claims MyOrg-Acct_2 Ada.Lovelace@Example.com MYORG-ACCT_2 ADA.LOVELACE@EXAMPLE.COM
mint_claims myorg.acct1 yardr MYORG-ACCT1 YARDR
mint_claims xy12345 yardr XY12345 YARDR
mint_claims xy12345.us-east-1 yardr XY12345 YARDR
mint_claims xy12345.us-east-2.aws yardr XY12345 YARDR
mint_claims xy12345.west-europe.azure yardr XY12345 YARDR
mint_claims xy12345.us-central1.gcp yardr XY12345 YARDR

# mint_refuses <what> <stdin> <the one line wanted on stderr> [PATH]
mint_refuses() {
  mint_run "$2" "${4:-}"
  bad=$(mint_leaks)
  [ "$rc" = 1 ] || bad="$bad exit $rc, wanted 1;"
  [ ! -s "$tmp/m.out" ] || bad="$bad stdout is not empty;"
  [ "$(cat "$tmp/m.err")" = "$3" ] || bad="$bad stderr is '$(cat "$tmp/m.err")';"
  [ -z "$bad" ]
  ok "mint refuses $1 with one fixed line that quotes no input" $? "$bad"
}
want_inputs='snowflake/mint: want SNOWFLAKE_ACCOUNT, SNOWFLAKE_USER and SNOWFLAKE_KEY_FILE among the inputs'
want_account='snowflake/mint: SNOWFLAKE_ACCOUNT is no account identifier: letters, digits, hyphens, underscores and periods'
want_user='snowflake/mint: SNOWFLAKE_USER is no user name: it has a space or a control character'
want_path='snowflake/mint: SNOWFLAKE_KEY_FILE is no absolute path: a ~ is not expanded'
want_file='snowflake/mint: SNOWFLAKE_KEY_FILE names no readable regular file'
want_key='snowflake/mint: the key in SNOWFLAKE_KEY_FILE is no RSA private key without a passphrase (PEM, as openssl pkcs8 -nocrypt writes it)'
good=$(mint_input acct yardr "$keys/k.p8")
mint_refuses "no input at all" '' "$want_inputs"
mint_refuses "an input that is no JSON" 'not json CANARY' "$want_inputs"
mint_refuses "inputs that are a string" '{"inputs":"CANARY"}' "$want_inputs"
mint_refuses "no inputs" '{"scope":""}' "$want_inputs"
mint_refuses "no account" "$(printf '%s' "$good" | jq -c 'del(.inputs.SNOWFLAKE_ACCOUNT)')" "$want_inputs"
mint_refuses "no user" "$(printf '%s' "$good" | jq -c 'del(.inputs.SNOWFLAKE_USER)')" "$want_inputs"
mint_refuses "no key file" "$(printf '%s' "$good" | jq -c 'del(.inputs.SNOWFLAKE_KEY_FILE)')" "$want_inputs"
mint_refuses "an empty user" "$(mint_input acct '' "$keys/k.p8")" "$want_inputs"
mint_refuses "an account that is a number" "$(printf '%s' "$good" | jq -c '.inputs.SNOWFLAKE_ACCOUNT = 5')" "$want_inputs"
mint_refuses "an account with a space" "$(mint_input 'CANARY acct' yardr "$keys/k.p8")" "$want_account"
mint_refuses "an account with a slash" "$(mint_input 'CANARY/acct' yardr "$keys/k.p8")" "$want_account"
mint_refuses "an account that starts with a hyphen" "$(mint_input '-CANARY' yardr "$keys/k.p8")" "$want_account"
mint_refuses "a user with a space" "$(mint_input acct 'CANARY user' "$keys/k.p8")" "$want_user"
mint_refuses "a user with a tab" "$(mint_input acct "$(printf 'CANARY\tuser')" "$keys/k.p8")" "$want_user"
mint_refuses "a key path that is relative" "$(mint_input acct yardr CANARY/key.p8)" "$want_path"
# The ~ is the point: the env file holds it as typed, and nothing expands it.
# shellcheck disable=SC2088
mint_refuses "a key path that starts with a ~" "$(mint_input acct yardr '~/CANARY.p8')" "$want_path"
mint_refuses "a key path that names nothing" "$(mint_input acct yardr /CANARY/nothing.p8)" "$want_file"
mint_refuses "a key path that is a directory" "$(mint_input acct yardr "$keys/dir.p8")" "$want_file"
if [ "$(id -u)" != 0 ]; then
  mint_refuses "a key file that cannot be read" "$(mint_input acct yardr "$keys/locked.p8")" "$want_file"
fi
mint_refuses "a key with a passphrase, without asking for it" "$(mint_input acct yardr "$keys/encrypted.p8")" "$want_key"
mint_refuses "a key that is not RSA" "$(mint_input acct yardr "$keys/ec.p8")" "$want_key"
mint_refuses "a file that is no key" "$(mint_input acct yardr "$keys/junk.p8")" "$want_key"
mint_refuses "an empty key file" "$(mint_input acct yardr "$keys/empty.p8")" "$want_key"

# A PATH that holds what the mint runs besides the program under test.
for tool in jq openssl; do
  mkdir "$tmp/without-$tool"
  for p in cat tr date jq openssl; do
    [ "$p" = "$tool" ] || ln -s "$(command -v "$p")" "$tmp/without-$tool/$p"
  done
  mint_refuses "a PATH without $tool" "$good" "snowflake/mint: needs $tool on PATH" "$tmp/without-$tool"
done

# ---- core ------------------------------------------------------------------------
. ./testdata/core.sh

if [ "$fail" = 0 ]; then echo "test: snowflake: all ok"; else echo "test: snowflake: FAILED"; fi
exit $fail

{"resultSetMetaData":{"numRows":1,"format":"jsonv2","partitionInfo":[{"rowCount":1,"uncompressedSize":2}],"rowType":[{"name":"number of rows inserted","database":"","schema":"","table":"","precision":19,"byteLength":null,"scale":0,"type":"fixed","nullable":false,"collation":null,"length":null}]},"data":[["1"]],"code":"090001","statementStatusUrl":"/api/v2/statements/01b8c4e4-0000-8f3a-0000-0013001a2b6f","sqlState":"00000","statementHandle":"01b8c4e4-0000-8f3a-0000-0013001a2b6f","message":"Statement executed successfully.","createdOn":1762207320000,"stats":{"numRowsInserted":1,"numRowsUpdated":0,"numRowsDeleted":0,"numDuplicateRowsUpdated":0}}

{"code":"000904","message":"SQL compilation error: error line 1 at position 7\ninvalid identifier 'AFAF'","sqlState":"42000","statementHandle":"01b8c4e3-0000-8f3a-0000-0013001a2b5e"}

{"code":"390144","message":"JWT token is invalid.","data":{"authnMethod":"KEYPAIR_JWT"},"success":false}

{"code":"333334","message":"Asynchronous execution in progress. Use provided query id to perform query monitoring and management.","statementHandle":"01b8c4e2-0000-8f3a-0000-0013001a2b4d","sqlState":"00000","statementStatusUrl":"/api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d","createdOn":1762207260000}

{"resultSetMetaData":{"numRows":3,"format":"jsonv2","partitionInfo":[{"rowCount":3,"uncompressedSize":128}],"rowType":[{"name":"C_CUSTKEY","database":"SNOWFLAKE_SAMPLE_DATA","schema":"TPCH_SF1","table":"CUSTOMER","precision":38,"byteLength":null,"scale":0,"type":"fixed","nullable":false,"collation":null,"length":null},{"name":"C_NAME","database":"SNOWFLAKE_SAMPLE_DATA","schema":"TPCH_SF1","table":"CUSTOMER","precision":null,"byteLength":100,"scale":null,"type":"text","nullable":false,"collation":null,"length":25},{"name":"C_ACCTBAL","database":"SNOWFLAKE_SAMPLE_DATA","schema":"TPCH_SF1","table":"CUSTOMER","precision":12,"byteLength":null,"scale":2,"type":"fixed","nullable":false,"collation":null,"length":null}]},"data":[["60001","Customer#000060001","9957.56"],["60002","Customer#000060002","742.46"],["60003","Customer#000060003","1747.29"]],"code":"090001","statementStatusUrl":"/api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d","sqlState":"00000","statementHandle":"01b8c4e2-0000-8f3a-0000-0013001a2b4d","message":"Statement executed successfully.","createdOn":1762207260000}

{"resultSetMetaData":{"numRows":5,"format":"jsonv2","partitionInfo":[{"rowCount":3,"uncompressedSize":128},{"rowCount":2,"uncompressedSize":86,"compressedSize":71}],"rowType":[{"name":"C_CUSTKEY","database":"SNOWFLAKE_SAMPLE_DATA","schema":"TPCH_SF1","table":"CUSTOMER","precision":38,"byteLength":null,"scale":0,"type":"fixed","nullable":false,"collation":null,"length":null},{"name":"C_NAME","database":"SNOWFLAKE_SAMPLE_DATA","schema":"TPCH_SF1","table":"CUSTOMER","precision":null,"byteLength":100,"scale":null,"type":"text","nullable":false,"collation":null,"length":25},{"name":"C_ACCTBAL","database":"SNOWFLAKE_SAMPLE_DATA","schema":"TPCH_SF1","table":"CUSTOMER","precision":12,"byteLength":null,"scale":2,"type":"fixed","nullable":false,"collation":null,"length":null}]},"data":[["60001","Customer#000060001","9957.56"],["60002","Customer#000060002","742.46"],["60003","Customer#000060003","1747.29"]],"code":"090001","statementStatusUrl":"/api/v2/statements/01b8c4e1-0000-8f3a-0000-0013001a2b3c?requestId=a1b2c3d4-0000-4000-8000-000000000001","sqlState":"00000","statementHandle":"01b8c4e1-0000-8f3a-0000-0013001a2b3c","message":"Statement executed successfully.","createdOn":1762207200000}

{"resultSetMetaData":{"numRows":2,"format":"jsonv2","partitionInfo":[{"rowCount":2,"uncompressedSize":196}],"rowType":[{"name":"created_on","database":"","schema":"","table":"","precision":0,"byteLength":null,"scale":9,"type":"timestamp_ltz","nullable":false,"collation":null,"length":null},{"name":"name","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":false,"collation":null,"length":16777216},{"name":"is_default","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":true,"collation":null,"length":16777216},{"name":"owner","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":true,"collation":null,"length":16777216},{"name":"kind","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":true,"collation":null,"length":16777216}]},"data":[["1762200000.000000000","SNOWFLAKE","N","","APPLICATION"],["1762200060.000000000","SNOWFLAKE_SAMPLE_DATA","N","ACCOUNTADMIN","IMPORTED DATABASE"]],"code":"090001","statementStatusUrl":"/api/v2/statements/01b8c4e5-0000-8f3a-0000-0013001a2b70","sqlState":"00000","statementHandle":"01b8c4e5-0000-8f3a-0000-0013001a2b70","message":"Statement executed successfully.","createdOn":1762207380000}

{"resultSetMetaData":{"numRows":3,"format":"jsonv2","partitionInfo":[{"rowCount":3,"uncompressedSize":210}],"rowType":[{"name":"created_on","database":"","schema":"","table":"","precision":0,"byteLength":null,"scale":9,"type":"timestamp_ltz","nullable":false,"collation":null,"length":null},{"name":"name","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":false,"collation":null,"length":16777216},{"name":"database_name","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":false,"collation":null,"length":16777216},{"name":"owner","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":true,"collation":null,"length":16777216}]},"data":[["1762200060.000000000","INFORMATION_SCHEMA","SNOWFLAKE_SAMPLE_DATA",""],["1762200060.000000000","TPCH_SF1","SNOWFLAKE_SAMPLE_DATA",""],["1762200060.000000000","TPCH_SF10","SNOWFLAKE_SAMPLE_DATA",""]],"code":"090001","statementStatusUrl":"/api/v2/statements/01b8c4e6-0000-8f3a-0000-0013001a2b81","sqlState":"00000","statementHandle":"01b8c4e6-0000-8f3a-0000-0013001a2b81","message":"Statement executed successfully.","createdOn":1762207440000}

{"resultSetMetaData":{"numRows":3,"format":"jsonv2","partitionInfo":[{"rowCount":3,"uncompressedSize":240}],"rowType":[{"name":"created_on","database":"","schema":"","table":"","precision":0,"byteLength":null,"scale":9,"type":"timestamp_ltz","nullable":false,"collation":null,"length":null},{"name":"name","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":false,"collation":null,"length":16777216},{"name":"database_name","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":false,"collation":null,"length":16777216},{"name":"schema_name","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":false,"collation":null,"length":16777216},{"name":"kind","database":"","schema":"","table":"","precision":null,"byteLength":16777216,"scale":null,"type":"text","nullable":true,"collation":null,"length":16777216},{"name":"rows","database":"","schema":"","table":"","precision":19,"byteLength":null,"scale":0,"type":"fixed","nullable":true,"collation":null,"length":null}]},"data":[["1762200060.000000000","CUSTOMER","SNOWFLAKE_SAMPLE_DATA","TPCH_SF1","TABLE","150000"],["1762200060.000000000","LINEITEM","SNOWFLAKE_SAMPLE_DATA","TPCH_SF1","TABLE","6001215"],["1762200060.000000000","ORDERS","SNOWFLAKE_SAMPLE_DATA","TPCH_SF1","TABLE","1500000"]],"code":"090001","statementStatusUrl":"/api/v2/statements/01b8c4e7-0000-8f3a-0000-0013001a2b92","sqlState":"00000","statementHandle":"01b8c4e7-0000-8f3a-0000-0013001a2b92","message":"Statement executed successfully.","createdOn":1762207500000}

=== args
databases
role=x y

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call databases: parameter role does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
databases
warehouse=YARDR_WH
role=YARDR_READ
=== answer
@show-databases

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"role":"YARDR_READ","statement":"SHOW DATABASES","warehouse":"YARDR_WH"}
--- status
0
--- stdout
{
  "data": [
    [
      "1762200000.000000000",
      "SNOWFLAKE",
      "N",
      "",
      "APPLICATION"
    ],
    [
      "1762200060.000000000",
      "SNOWFLAKE_SAMPLE_DATA",
      "N",
      "ACCOUNTADMIN",
      "IMPORTED DATABASE"
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 2,
    "partitionInfo": [
      {
        "rowCount": 2,
        "uncompressedSize": 196
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "",
        "length": null,
        "name": "created_on",
        "nullable": false,
        "precision": 0,
        "scale": 9,
        "schema": "",
        "table": "",
        "type": "timestamp_ltz"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "is_default",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "owner",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "kind",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      }
    ]
  },
  "statementHandle": "01b8c4e5-0000-8f3a-0000-0013001a2b70"
}
--- stderr

=== args
databases
=== answer
@show-databases

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SHOW DATABASES"}
--- status
0
--- stdout
{
  "data": [
    [
      "1762200000.000000000",
      "SNOWFLAKE",
      "N",
      "",
      "APPLICATION"
    ],
    [
      "1762200060.000000000",
      "SNOWFLAKE_SAMPLE_DATA",
      "N",
      "ACCOUNTADMIN",
      "IMPORTED DATABASE"
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 2,
    "partitionInfo": [
      {
        "rowCount": 2,
        "uncompressedSize": 196
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "",
        "length": null,
        "name": "created_on",
        "nullable": false,
        "precision": 0,
        "scale": 9,
        "schema": "",
        "table": "",
        "type": "timestamp_ltz"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "is_default",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "owner",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "kind",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      }
    ]
  },
  "statementHandle": "01b8c4e5-0000-8f3a-0000-0013001a2b70"
}
--- stderr

=== args
execute
statement=CREATE TABLE YARDR_DEMO.PUBLIC.NOTES (ID NUMBER, TEXT STRING)
async=true
=== answer
@running
=== status
202

--- requests
POST /api/v2/statements?async=true
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"CREATE TABLE YARDR_DEMO.PUBLIC.NOTES (ID NUMBER, TEXT STRING)","timeout":60}
--- status
0
--- stdout
{
  "statementHandle": "01b8c4e2-0000-8f3a-0000-0013001a2b4d"
}
--- stderr

=== args
execute
statement=INSERT INTO SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER (C_CUSTKEY) VALUES (1)
=== answer
@failed
=== status
422

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"INSERT INTO SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER (C_CUSTKEY) VALUES (1)","timeout":60}
--- status
1
--- stdout
{
  "error": "source snowflake, call execute: the service answered 422"
}
--- stderr

=== args
execute

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call execute: parameter statement is missing"
}
--- stderr

=== args
execute
statement=INSERT INTO YARDR_DEMO.PUBLIC.NOTES VALUES (1, 'hello')
database=YARDR_DEMO
schema=PUBLIC
=== answer
@dml

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"database":"YARDR_DEMO","schema":"PUBLIC","statement":"INSERT INTO YARDR_DEMO.PUBLIC.NOTES VALUES (1, 'hello')","timeout":60}
--- status
0
--- stdout
{
  "data": [
    [
      "1"
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 1,
    "partitionInfo": [
      {
        "rowCount": 1,
        "uncompressedSize": 2
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "",
        "length": null,
        "name": "number of rows inserted",
        "nullable": false,
        "precision": 19,
        "scale": 0,
        "schema": "",
        "table": "",
        "type": "fixed"
      }
    ]
  },
  "statementHandle": "01b8c4e4-0000-8f3a-0000-0013001a2b6f",
  "stats": {
    "numDuplicateRowsUpdated": 0,
    "numRowsDeleted": 0,
    "numRowsInserted": 1,
    "numRowsUpdated": 0
  }
}
--- stderr

=== args
query
statement=SELECT 1
async=maybe

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter async is true or false"
}
--- stderr

=== args
query
statement=SELECT 1
database=SNOWFLAKE.SAMPLE

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter database does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
query
statement=SELECT 1
role=ACCOUNTADMIN; DROP

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter role does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
query
statement=SELECT 1
timeout=soon

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter timeout is no whole number"
}
--- stderr

=== args
query
statement=SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3
=== answer
@jwt-invalid
=== status
401

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3","timeout":60}
--- status
1
--- stdout
{
  "error": "source snowflake, call query: the service answered 401"
}
--- stderr

=== args
query
statement=/* x */ SELECT 1

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter statement does not match its pattern (?i)^\\s*select\\b"
}
--- stderr

=== args
query
statement=SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3
=== answer
@running
=== status
408

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3","timeout":60}
--- status
1
--- stdout
{
  "error": "source snowflake, call query: the service answered 408"
}
--- stderr

=== args
query
statement=DROP TABLE T

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter statement does not match its pattern (?i)^\\s*select\\b"
}
--- stderr

=== args
query
statement=SELECT AFAF FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER LIMIT 1
=== answer
@failed
=== status
422

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SELECT AFAF FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER LIMIT 1","timeout":60}
--- status
1
--- stdout
{
  "error": "source snowflake, call query: the service answered 422"
}
--- stderr

=== args
query
statement=INSERT INTO T VALUES (1)

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter statement does not match its pattern (?i)^\\s*select\\b"
}
--- stderr

=== args
query
statement=  select count(*) from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
=== answer
@running
=== status
202

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"  select count(*) from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS","timeout":60}
--- status
0
--- stdout
{
  "statementHandle": "01b8c4e2-0000-8f3a-0000-0013001a2b4d"
}
--- stderr

=== args
query

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter statement is missing"
}
--- stderr

=== args
query
statement=SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3
timeout=10
database=SNOWFLAKE_SAMPLE_DATA
schema=TPCH_SF1
warehouse=YARDR_WH
role=YARDR_READ
async=true
=== answer
@running
=== status
202

--- requests
POST /api/v2/statements?async=true
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"database":"SNOWFLAKE_SAMPLE_DATA","role":"YARDR_READ","schema":"TPCH_SF1","statement":"SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3","timeout":10,"warehouse":"YARDR_WH"}
--- status
0
--- stdout
{
  "statementHandle": "01b8c4e2-0000-8f3a-0000-0013001a2b4d"
}
--- stderr

=== args
query
statement=SELECT 'it''s' AS "Q" LIMIT 1
=== answer
@select-done

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SELECT 'it''s' AS \"Q\" LIMIT 1","timeout":60}
--- status
0
--- stdout
{
  "data": [
    [
      "60001",
      "Customer#000060001",
      "9957.56"
    ],
    [
      "60002",
      "Customer#000060002",
      "742.46"
    ],
    [
      "60003",
      "Customer#000060003",
      "1747.29"
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 3,
    "partitionInfo": [
      {
        "rowCount": 3,
        "uncompressedSize": 128
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": null,
        "name": "C_CUSTKEY",
        "nullable": false,
        "precision": 38,
        "scale": 0,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "fixed"
      },
      {
        "byteLength": 100,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": 25,
        "name": "C_NAME",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "text"
      },
      {
        "byteLength": null,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": null,
        "name": "C_ACCTBAL",
        "nullable": false,
        "precision": 12,
        "scale": 2,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "fixed"
      }
    ]
  },
  "statementHandle": "01b8c4e2-0000-8f3a-0000-0013001a2b4d"
}
--- stderr

=== args
query
statement=selection 1

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter statement does not match its pattern (?i)^\\s*select\\b"
}
--- stderr

=== args
query
statement=SELECT 1; SELECT 2
=== answer
@failed
=== status
422

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SELECT 1; SELECT 2","timeout":60}
--- status
1
--- stdout
{
  "error": "source snowflake, call query: the service answered 422"
}
--- stderr

=== args
query
statement=WITH X AS (SELECT 1) SELECT * FROM X

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call query: parameter statement does not match its pattern (?i)^\\s*select\\b"
}
--- stderr

=== args
query
statement=SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3
=== answer
@select-first-partition

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SELECT C_CUSTKEY, C_NAME, C_ACCTBAL FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER ORDER BY C_ACCTBAL DESC LIMIT 3","timeout":60}
--- status
0
--- stdout
{
  "data": [
    [
      "60001",
      "Customer#000060001",
      "9957.56"
    ],
    [
      "60002",
      "Customer#000060002",
      "742.46"
    ],
    [
      "60003",
      "Customer#000060003",
      "1747.29"
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 5,
    "partitionInfo": [
      {
        "rowCount": 3,
        "uncompressedSize": 128
      },
      {
        "compressedSize": 71,
        "rowCount": 2,
        "uncompressedSize": 86
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": null,
        "name": "C_CUSTKEY",
        "nullable": false,
        "precision": 38,
        "scale": 0,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "fixed"
      },
      {
        "byteLength": 100,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": 25,
        "name": "C_NAME",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "text"
      },
      {
        "byteLength": null,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": null,
        "name": "C_ACCTBAL",
        "nullable": false,
        "precision": 12,
        "scale": 2,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "fixed"
      }
    ]
  },
  "statementHandle": "01b8c4e1-0000-8f3a-0000-0013001a2b3c"
}
--- stderr

=== args
schemas
database=SNOWFLAKE_SAMPLE_DATA; DROP DATABASE X

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call schemas: parameter database does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
schemas
database=1DB

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call schemas: parameter database does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
schemas
database="My Db"

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call schemas: parameter database does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
schemas
database=SNOWFLAKE_SAMPLE_DATA
=== answer
@show-schemas

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SHOW SCHEMAS IN DATABASE SNOWFLAKE_SAMPLE_DATA"}
--- status
0
--- stdout
{
  "data": [
    [
      "1762200060.000000000",
      "INFORMATION_SCHEMA",
      "SNOWFLAKE_SAMPLE_DATA",
      ""
    ],
    [
      "1762200060.000000000",
      "TPCH_SF1",
      "SNOWFLAKE_SAMPLE_DATA",
      ""
    ],
    [
      "1762200060.000000000",
      "TPCH_SF10",
      "SNOWFLAKE_SAMPLE_DATA",
      ""
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 3,
    "partitionInfo": [
      {
        "rowCount": 3,
        "uncompressedSize": 210
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "",
        "length": null,
        "name": "created_on",
        "nullable": false,
        "precision": 0,
        "scale": 9,
        "schema": "",
        "table": "",
        "type": "timestamp_ltz"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "database_name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "owner",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      }
    ]
  },
  "statementHandle": "01b8c4e6-0000-8f3a-0000-0013001a2b81"
}
--- stderr

=== args
status
handle=01b8c4e2-0000-8f3a-0000-0013001a2b4d
=== answer
@select-done

--- requests
GET /api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d
  auth: ok
  accept: application/json
  user-agent: yardr
--- status
0
--- stdout
{
  "code": "090001",
  "createdOn": 1762207260000,
  "data": [
    [
      "60001",
      "Customer#000060001",
      "9957.56"
    ],
    [
      "60002",
      "Customer#000060002",
      "742.46"
    ],
    [
      "60003",
      "Customer#000060003",
      "1747.29"
    ]
  ],
  "message": "Statement executed successfully.",
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 3,
    "partitionInfo": [
      {
        "rowCount": 3,
        "uncompressedSize": 128
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": null,
        "name": "C_CUSTKEY",
        "nullable": false,
        "precision": 38,
        "scale": 0,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "fixed"
      },
      {
        "byteLength": 100,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": 25,
        "name": "C_NAME",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "text"
      },
      {
        "byteLength": null,
        "collation": null,
        "database": "SNOWFLAKE_SAMPLE_DATA",
        "length": null,
        "name": "C_ACCTBAL",
        "nullable": false,
        "precision": 12,
        "scale": 2,
        "schema": "TPCH_SF1",
        "table": "CUSTOMER",
        "type": "fixed"
      }
    ]
  },
  "sqlState": "00000",
  "statementHandle": "01b8c4e2-0000-8f3a-0000-0013001a2b4d",
  "statementStatusUrl": "/api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d"
}
--- stderr

=== args
status
handle=01b8c4e2-0000-8f3a-0000-0013001a2b4d
=== answer
@failed
=== status
422

--- requests
GET /api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d
  auth: ok
  accept: application/json
  user-agent: yardr
--- status
1
--- stdout
{
  "error": "source snowflake, call status: the service answered 422"
}
--- stderr

=== args
status
handle=01b8c4e2-0000-8f3a-0000-0013001a2b4d
=== status
404

--- requests
GET /api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d
  auth: ok
  accept: application/json
  user-agent: yardr
--- status
1
--- stdout
{
  "error": "source snowflake, call status: the service answered 404"
}
--- stderr

=== args
status
handle=../../etc/passwd

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call status: parameter handle does not match its pattern ^[0-9A-Fa-f]{8}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{12}$"
}
--- stderr

=== args
status
handle=01b8c4e2-0000-8f3a-0000-0013001a2b4d
=== answer
@running
=== status
202

--- requests
GET /api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d
  auth: ok
  accept: application/json
  user-agent: yardr
--- status
0
--- stdout
{
  "code": "333334",
  "createdOn": 1762207260000,
  "message": "Asynchronous execution in progress. Use provided query id to perform query monitoring and management.",
  "sqlState": "00000",
  "statementHandle": "01b8c4e2-0000-8f3a-0000-0013001a2b4d",
  "statementStatusUrl": "/api/v2/statements/01b8c4e2-0000-8f3a-0000-0013001a2b4d"
}
--- stderr

=== args
status
handle=0000

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call status: parameter handle does not match its pattern ^[0-9A-Fa-f]{8}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{12}$"
}
--- stderr

=== args
tables
database=SNOWFLAKE_SAMPLE_DATA
schema=TPCH_SF1.LINEITEM

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call tables: parameter schema does not match its pattern ^[A-Za-z_][A-Za-z0-9_$]*$"
}
--- stderr

=== args
tables
database=SNOWFLAKE_SAMPLE_DATA

--- requests
--- status
1
--- stdout
{
  "error": "source snowflake, call tables: parameter schema is missing"
}
--- stderr

=== args
tables
database=SNOWFLAKE_SAMPLE_DATA
schema=TPCH_SF1
=== answer
@show-tables

--- requests
POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SHOW TABLES IN SCHEMA SNOWFLAKE_SAMPLE_DATA.TPCH_SF1"}
--- status
0
--- stdout
{
  "data": [
    [
      "1762200060.000000000",
      "CUSTOMER",
      "SNOWFLAKE_SAMPLE_DATA",
      "TPCH_SF1",
      "TABLE",
      "150000"
    ],
    [
      "1762200060.000000000",
      "LINEITEM",
      "SNOWFLAKE_SAMPLE_DATA",
      "TPCH_SF1",
      "TABLE",
      "6001215"
    ],
    [
      "1762200060.000000000",
      "ORDERS",
      "SNOWFLAKE_SAMPLE_DATA",
      "TPCH_SF1",
      "TABLE",
      "1500000"
    ]
  ],
  "resultSetMetaData": {
    "format": "jsonv2",
    "numRows": 3,
    "partitionInfo": [
      {
        "rowCount": 3,
        "uncompressedSize": 240
      }
    ],
    "rowType": [
      {
        "byteLength": null,
        "collation": null,
        "database": "",
        "length": null,
        "name": "created_on",
        "nullable": false,
        "precision": 0,
        "scale": 9,
        "schema": "",
        "table": "",
        "type": "timestamp_ltz"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "database_name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "schema_name",
        "nullable": false,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": 16777216,
        "collation": null,
        "database": "",
        "length": 16777216,
        "name": "kind",
        "nullable": true,
        "precision": null,
        "scale": null,
        "schema": "",
        "table": "",
        "type": "text"
      },
      {
        "byteLength": null,
        "collation": null,
        "database": "",
        "length": null,
        "name": "rows",
        "nullable": true,
        "precision": 19,
        "scale": 0,
        "schema": "",
        "table": "",
        "type": "fixed"
      }
    ]
  },
  "statementHandle": "01b8c4e7-0000-8f3a-0000-0013001a2b92"
}
--- stderr

POST /api/v2/statements
  auth: ok
  accept: application/json
  content-type: application/json
  user-agent: yardr
  body: {"statement":"SHOW DATABASES"}

# The core part of the snowflake test, sourced by ./test: the declaration as
# the yard reads it, the presets and the grants SETUP.md teaches, the check
# call, and each testdata/calls/*.case made through the real yardr against a
# stand-in on a loopback port (testdata/standin.py). Everything runs in a
# scratch yard under a short path (a unix socket's path is held to 100
# bytes) with its own home and an environment of nothing but PATH: none of
# this session's YARDR_* variables lead back to a real yard. No daemon is
# started, so no agent is, and the only listener is the stand-in's, on
# 127.0.0.1 and a port the system chooses.
#
# ./test has set $YARDR, $here, $tmp, $keys (the throwaway key pair), $fp,
# $needle, check, ok, jwt_part, jwt_signed and cleanup (which stops the
# stand-in by its pid, stops the scratch yard's server, and removes the
# scratch yard, whatever core is by then).
core=$(mktemp -d /tmp/snowflaket.XXXXXX)
mkdir -p "$core/h"
yrun() { env -i HOME="$core/h" PATH="$PATH" "$YARDR" --home "$core/y" --actor tester "$@"; }

if ! yrun init --harness claude --operator tester >"$core/init.out" 2>&1; then
  echo "test: core FAILED: the scratch yard could not be made: $(tail -c 300 "$core/init.out")"
  fail=1
elif ! yrun pack apply "$here" --yes >"$core/apply.out" 2>&1; then
  echo "test: core FAILED: the pack did not apply from its directory: $(tail -c 300 "$core/apply.out")"
  fail=1
else
  # -- the declaration as the yard reads it, with the shipped base
  yrun source show snowflake --json | jq -S .declaration >"$core/declaration.json"
  check "declaration as the yard reads it (base, credential, calls)" "$core/declaration.json" testdata/declaration.golden
  # The kinds are what keeps writing apart: read covers every read call and
  # no update, and execute, the one update, is granted by its name.
  jq -e '.base == "https://example.snowflakecomputing.com" and .check == "databases" and (has("idempotency") | not)
         and ([.calls[] | select(.kind == "read") | .name] == ["query", "databases", "schemas", "tables", "status"])
         and ([.calls[] | select(.kind == "update") | .name] == ["execute"])
         and (.credential.mint == ["SNOWFLAKE_ACCOUNT", "SNOWFLAKE_USER", "SNOWFLAKE_KEY_FILE"])
         and (.credential.not_secret == .credential.mint)
         and .credential.mint_command == ["snowflake/mint"] and .credential.env == "SNOWFLAKE_JWT"
         and .credential.header == "Authorization: Bearer {credential}"' "$core/declaration.json" >/dev/null
  ok "the shipped base is the placeholder, five calls read and execute the one update, the three mint names are asked in the clear, and no idempotency header is named" $?
  yrun pack show snowflake >"$core/packshow.out" 2>&1
  grep -q '1\. read: ' "$core/packshow.out" && grep -q '2\. write: ' "$core/packshow.out" &&
    awk '/1\. read: /{ r = 1; next } /2\. write: /{ r = 2; next } r == 1 && /snowflake:/ { read = $0 } r == 2 && /snowflake:/ { write = $0 }
         END { exit !(read ~ /snowflake: read$/ && write ~ /snowflake: execute$/) }' "$core/packshow.out"
  ok "pack show prints the two presets, read granting read and write granting execute alone" $?

  # -- the grants SETUP.md teaches, run as written. They are read out of
  # SETUP.md and not copied here: a copy would pass whatever SETUP teaches,
  # and --preset is refused for a pack applied from a directory. The card of
  # SETUP.md grants the group reviewers, which a fresh yard has none of.
  grep -o 'source grant [^`"]*' SETUP.md >"$core/grants"
  if [ ! -s "$core/grants" ]; then
    echo "test: grant FAILED: SETUP.md teaches no source grant command"
    fail=1
  elif ! yrun group add reviewers --runner manual >"$core/out" 2>&1; then
    echo "test: grant FAILED: the group of the card could not be made: $(tail -c 300 "$core/out")"
    fail=1
  else
    while IFS= read -r cmd; do
      # A grant is plain words, so $cmd is split on purpose. Its stdin is
      # /dev/null so that it cannot read the rest of the list.
      # shellcheck disable=SC2086
      if yrun $cmd </dev/null >"$core/out" 2>&1; then
        echo "test: grant ok (yardr $cmd)"
      else
        echo "test: grant FAILED: yardr refused 'yardr $cmd', which SETUP.md teaches: $(tail -c 300 "$core/out")"
        fail=1
      fi
    done <"$core/grants"
    # What those grants gave: read as the word for the read calls, and
    # execute as an update, granted by its name; nothing else was granted.
    yrun source grants --json >"$core/grants.json" 2>&1
    jq -e 'length > 0
           and ([.[] | .kinds | to_entries[]] | all((.key == "read" and .value == "read") or (.key == "execute" and .value == "update")))
           and ([.[] | .kinds | has("read")] | any) and ([.[] | .kinds | has("execute")] | any)' "$core/grants.json" >/dev/null
    ok "SETUP.md's grants give read as read and execute as an update, nothing else" $? "$(head -c 300 "$core/grants.json")"
  fi

  # -- the calls and the check, through the real yardr against the stand-in.
  # The operator's copy is the declaration with its base replaced, as
  # SETUP.md makes it; here the account is the stand-in.
  mkdir -p "$core/mine"
  cp -R "$here/snowflake" "$core/mine/"
  : >"$core/requests"
  : >"$core/tokens"
  printf '{}\n' >"$core/answer.json"
  python3 -I testdata/standin.py "$core/port" "$core/requests" "$core/answer.json" "$core/tokens" 2>"$core/standin.err" &
  standin_pid=$!
  i=0
  while [ ! -s "$core/port" ] && [ "$i" -lt 200 ]; do sleep 0.05; i=$((i + 1)); done
  if [ ! -s "$core/port" ]; then
    echo "test: core FAILED: the stand-in did not listen: $(cat "$core/standin.err")"
    fail=1
  else
    site=http://127.0.0.1:$(cat "$core/port")
    sed "s|^base: .*|base: $site|" "$here/snowflake.yaml" >"$core/mine/snowflake.yaml"
    # The credential is given as SETUP.md gives it: yardr source key, with
    # the three names on its standard input, which the server writes into
    # the source's env file. The account is lower case here and the user
    # too, so the claims show the mint uppercasing them.
    yrun source add "$core/mine/snowflake.yaml" >"$core/add.out" 2>&1 \
      && printf 'SNOWFLAKE_ACCOUNT=myorg-acct1\nSNOWFLAKE_USER=yardr\nSNOWFLAKE_KEY_FILE=%s\n' "$keys/k.p8" | yrun source key snowflake >>"$core/add.out" 2>&1 \
      && yrun source set snowflake calls_per_hour=1000 >>"$core/add.out" 2>&1 \
      && yrun source set snowflake updates_per_hour=1000 >>"$core/add.out" 2>&1
    ok "the operator's copy is added and its three names given by source key" $? "$(tail -c 300 "$core/add.out")"

    # -- the check call, minted by the pack's own mint
    : >"$core/requests"
    printf '%s\n' "$(cat testdata/answers/show-databases.json)" >"$core/answer.json"
    yrun source check snowflake >"$core/check.out" 2>&1
    rc=$?
    [ "$rc" = 0 ] && grep -q 'SNOWFLAKE_SAMPLE_DATA' "$core/check.out"
    ok "source check reaches the account with a minted token and shows the databases" $? "exit $rc: $(tail -c 300 "$core/check.out")"
    check "the check call's request" "$core/requests" testdata/check.requests.golden

    # A case file is sections, each opened by a line "=== name": args (the
    # call and its parameters, one word to a line), answer (the stand-in's
    # body; a line "@name" stands for testdata/answers/name.json) and status
    # (its HTTP status, 200 if left out).
    for c in testdata/calls/*.case; do
      n=$(basename "$c" .case)
      d="$core/case_$n"
      mkdir "$d"
      awk -v dir="$d" '/^=== /{ name = substr($0, 5); f = dir "/" name; printf "" > f; next } { print >> f }' "$c"
      if [ -e "$d/answer" ]; then
        first=$(head -n 1 "$d/answer")
        case $first in
          @*) cp "testdata/answers/${first#@}.json" "$core/answer.json" ;;
          *) cp "$d/answer" "$core/answer.json" ;;
        esac
      else
        printf '{}\n' >"$core/answer.json"
      fi
      rm -f "$core/answer.json.status"
      [ ! -e "$d/status" ] || cp "$d/status" "$core/answer.json.status"
      : >"$core/requests"
      set --
      while IFS= read -r word; do set -- "$@" "$word"; done <"$d/args"
      yrun call snowflake "$@" --json >"$d/stdout" 2>"$d/stderr"
      status=$?
      {
        echo "--- requests"
        cat "$core/requests"
        echo "--- status"
        echo "$status"
        echo "--- stdout"
        if [ -s "$d/stdout" ]; then jq -S . "$d/stdout" 2>/dev/null || cat "$d/stdout"; fi
        echo "--- stderr"
        cat "$d/stderr"
      } >"$d/golden"
      check "call $n" "$d/golden" "testdata/calls/$n.golden"
    done
    rm -f "$core/answer.json.status"

    # Of all the requests the cases made: every one came to the one path, a
    # POST or, for status, a GET of a handle, with a token of the shape
    # Snowflake takes, and Snowflake's headers.
    cat "$core"/case_*/golden >"$core/all.golden"
    ! grep -q 'auth: WRONG\|auth: missing' "$core/all.golden"
    ok "every request carried a bearer token of the shape Snowflake takes" $?
    ! grep -E '^[A-Z]+ ' "$core/all.golden" | grep -vE '^(POST /api/v2/statements(\?async=true)?|GET /api/v2/statements/[0-9a-f-]{36})$' | grep -q .
    ok "every request is a POST to /api/v2/statements (async only as async=true) or a GET of one statement" $?
    ! grep -E '^  (accept|content-type|user-agent): ' "$core/all.golden" | grep -vE '^  (accept: application/json|content-type: application/json|user-agent: yardr)$' | grep -q .
    ok "every request asked for JSON, sent JSON and named yardr as its User-Agent" $?

    # -- the token the yard sent: the real mint, from the env file's names
    kill "$standin_pid" 2>/dev/null
    standin_pid=
    ntok=$(grep -c . "$core/tokens")
    bad=
    [ "$ntok" -ge 1 ] || bad=" the stand-in saw no token;"
    while IFS= read -r tok; do
      [ -n "$tok" ] || continue
      jwt_signed "$tok" "$keys/pub.pem" || bad="$bad a token is not signed by the key;"
      jwt_part "$tok" 2 | jq -e --arg iss "MYORG-ACCT1.YARDR.$fp" --arg sub MYORG-ACCT1.YARDR '.iss == $iss and .sub == $sub and (.exp - .iat) == 3540' >/dev/null ||
        bad="$bad a token's claims are not those of the env file's account and user;"
    done <"$core/tokens"
    [ -z "$bad" ]
    ok "the token the yard sent was minted by snowflake/mint from the env file ($ntok seen), signed by the key and claiming the account and user" $? "$bad"

    # -- the key is in no file the run made, and no token is either
    envfile=$core/y/sources/snowflake.env
    grep -qF "$keys/k.p8" "$envfile" && ! grep -qF "$needle" "$envfile"
    ok "the env file names the key by its path and holds none of its bytes" $?
    leaks=$(grep -rlF -e "$needle" "$core/y" "$core/h" 2>/dev/null)
    [ -z "$leaks" ]
    ok "the key's bytes are in no file of the yard" $? "found in: $leaks"
    # (One token or two: holding, in ./test, says why.)
    tleaks=$(holding "$core/tokens" "$core/y" "$core/h")
    [ -z "$tleaks" ]
    ok "no token is in any file of the yard (it is minted in memory)" $? "found in: $tleaks"
    ! grep -qF -e "$needle" "$core/check.out" "$core/add.out" "$core"/case_*/stdout "$core"/case_*/stderr
    ok "the key is in no output of the yard's commands" $?
  fi
fi
[ -z "${standin_pid:-}" ] || { kill "$standin_pid" 2>/dev/null; standin_pid=; }

{
  "about": "Snowflake SQL API. Runs a SELECT with a bounded answer, lists databases, schemas and tables, reads a statement by its handle; execute runs any other statement.",
  "base": "https://example.snowflakecomputing.com",
  "calls": [
    {
      "about": "Run one SELECT and read the first page of its rows. Give the statement a LIMIT: only the first partition comes back (numRows and partitionInfo in resultSetMetaData say how much more there is), and an answer over 256 KB is refused, not cut. The statement must begin with SELECT, and what the role may read is Snowflake's to enforce. An answer that is a statementHandle and nothing else is a statement still running: read it from status. The call ends after 30 s, so pass async=true for a long statement and get the handle at once. timeout is the seconds Snowflake lets the statement run, 60 unless given; 0 is seven days, not none.",
      "answer": {
        "max": 262144,
        "select": [
          "statementHandle",
          "resultSetMetaData",
          "data"
        ]
      },
      "kind": "read",
      "name": "query",
      "params": [
        {
          "max": 8000,
          "name": "statement",
          "pattern": "(?i)^\\s*select\\b",
          "type": "string"
        },
        {
          "default": 60,
          "name": "timeout",
          "optional": true,
          "type": "int"
        },
        {
          "log": true,
          "max": 255,
          "name": "database",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "schema",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "warehouse",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "role",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "name": "async",
          "optional": true,
          "type": "bool"
        }
      ],
      "request": {
        "body": {
          "database": "{database}",
          "role": "{role}",
          "schema": "{schema}",
          "statement": "{statement}",
          "timeout": "{timeout}",
          "warehouse": "{warehouse}"
        },
        "method": "POST",
        "path": "/api/v2/statements",
        "query": [
          {
            "key": "async",
            "value": "{async}"
          }
        ]
      }
    },
    {
      "about": "List the databases the role can see. The check call.",
      "answer": {
        "max": 262144,
        "select": [
          "statementHandle",
          "resultSetMetaData",
          "data"
        ]
      },
      "kind": "read",
      "name": "databases",
      "params": [
        {
          "log": true,
          "max": 255,
          "name": "warehouse",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "role",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        }
      ],
      "request": {
        "body": {
          "role": "{role}",
          "statement": "SHOW DATABASES",
          "warehouse": "{warehouse}"
        },
        "method": "POST",
        "path": "/api/v2/statements"
      }
    },
    {
      "about": "List the schemas of one database, named as databases gives it. A name that needs quotes is not accepted; use query on its INFORMATION_SCHEMA.",
      "answer": {
        "max": 262144,
        "select": [
          "statementHandle",
          "resultSetMetaData",
          "data"
        ]
      },
      "kind": "read",
      "name": "schemas",
      "params": [
        {
          "log": true,
          "max": 255,
          "name": "database",
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        }
      ],
      "request": {
        "body": {
          "statement": "SHOW SCHEMAS IN DATABASE {database}"
        },
        "method": "POST",
        "path": "/api/v2/statements"
      }
    },
    {
      "about": "List the tables of one schema. Their columns are query's to read, from the database's INFORMATION_SCHEMA.COLUMNS or a SELECT with LIMIT 1 and its rowType.",
      "answer": {
        "max": 262144,
        "select": [
          "statementHandle",
          "resultSetMetaData",
          "data"
        ]
      },
      "kind": "read",
      "name": "tables",
      "params": [
        {
          "log": true,
          "max": 255,
          "name": "database",
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "schema",
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        }
      ],
      "request": {
        "body": {
          "statement": "SHOW TABLES IN SCHEMA {database}.{schema}"
        },
        "method": "POST",
        "path": "/api/v2/statements"
      }
    },
    {
      "about": "Read one statement by the statementHandle an earlier call answered with. Running: a code and a message and no data. Done: the first partition of its rows, as query answers. Failed: the call is refused with 422, or with 408 where its timeout cut it, and the reason is not shown.",
      "answer": {
        "max": 262144,
        "select": [
          "code",
          "sqlState",
          "message",
          "statementHandle",
          "createdOn",
          "statementStatusUrl",
          "resultSetMetaData",
          "data"
        ]
      },
      "kind": "read",
      "name": "status",
      "params": [
        {
          "log": true,
          "max": 36,
          "name": "handle",
          "pattern": "^[0-9A-Fa-f]{8}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{4}-[0-9A-Fa-f]{12}$",
          "type": "string"
        }
      ],
      "request": {
        "method": "GET",
        "path": "/api/v2/statements/{handle}"
      }
    },
    {
      "about": "Run one statement that is not a SELECT (insert, create, drop, grant) as the role allows. It changes something, and a call tried again after a lost answer may run twice: async=true answers the statementHandle at once, and status says what became of the statement. timeout is in seconds, 60 unless given, and 0 is seven days, not none.",
      "answer": {
        "max": 262144,
        "select": [
          "statementHandle",
          "resultSetMetaData",
          "data",
          "stats"
        ]
      },
      "kind": "update",
      "name": "execute",
      "params": [
        {
          "max": 8000,
          "name": "statement",
          "type": "string"
        },
        {
          "default": 60,
          "name": "timeout",
          "optional": true,
          "type": "int"
        },
        {
          "log": true,
          "max": 255,
          "name": "database",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "schema",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "warehouse",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "log": true,
          "max": 255,
          "name": "role",
          "optional": true,
          "pattern": "^[A-Za-z_][A-Za-z0-9_$]*$",
          "type": "string"
        },
        {
          "name": "async",
          "optional": true,
          "type": "bool"
        }
      ],
      "request": {
        "body": {
          "database": "{database}",
          "role": "{role}",
          "schema": "{schema}",
          "statement": "{statement}",
          "timeout": "{timeout}",
          "warehouse": "{warehouse}"
        },
        "method": "POST",
        "path": "/api/v2/statements",
        "query": [
          {
            "key": "async",
            "value": "{async}"
          }
        ]
      }
    }
  ],
  "check": "databases",
  "credential": {
    "env": "SNOWFLAKE_JWT",
    "header": "Authorization: Bearer {credential}",
    "mint": [
      "SNOWFLAKE_ACCOUNT",
      "SNOWFLAKE_USER",
      "SNOWFLAKE_KEY_FILE"
    ],
    "mint_command": [
      "snowflake/mint"
    ],
    "not_secret": [
      "SNOWFLAKE_ACCOUNT",
      "SNOWFLAKE_USER",
      "SNOWFLAKE_KEY_FILE"
    ]
  },
  "name": "snowflake"
}

# A stand-in for Snowflake's SQL API, for the calls and the check the test
# makes through the real yardr. It answers every request with the body and
# status the test has left in its answer files, read again for each request,
# so one process serves every case in turn. It records each request in the
# log it is given: the method, and the path and query exactly as they
# arrived, whether Authorization held a bearer token of the shape Snowflake
# takes (three base64url segments, the first the header {"alg":"RS256",
# "typ":"JWT"}), the Accept, Content-Type and User-Agent headers, and the
# JSON body with its keys in order. Any method is recorded, so that a
# request that is not the one expected shows in a golden.
#
# The token itself is not in the log: it goes, once for each token seen, to
# the token file, where the test checks its signature and claims against the
# public key. It writes the port it bound, on 127.0.0.1, to the file it is
# given once it listens.
#
#   python3 -I standin.py <port file> <request log> <answer file> <token file>
#
# The status is the first line of <answer file>.status, 200 if there is no
# such file.
import base64
import http.server
import json
import os
import re
import sys

port_file, log_file, answer_file, token_file = sys.argv[1:5]
seen = set()


def b64url(text):
    return base64.urlsafe_b64decode(text + "=" * (-len(text) % 4))


def bearer(header):
    """Returns ("ok" | "missing" | "WRONG", token)."""
    if not header or not header.startswith("Bearer "):
        return "missing", ""
    token = header[len("Bearer "):]
    parts = token.split(".")
    if len(parts) != 3 or not all(re.fullmatch(r"[A-Za-z0-9_-]+", p) for p in parts):
        return "WRONG", ""
    try:
        head = json.loads(b64url(parts[0]))
    except ValueError:
        return "WRONG", ""
    return ("ok", token) if head == {"alg": "RS256", "typ": "JWT"} else ("WRONG", "")


class Handler(http.server.BaseHTTPRequestHandler):
    def handle_any(self):
        length = int(self.headers.get("Content-Length") or 0)
        raw = self.rfile.read(length) if length else b""
        auth, token = bearer(self.headers.get("Authorization"))
        if token and token not in seen:
            seen.add(token)
            with open(token_file, "a") as f:
                f.write(token + "\n")
        lines = ["%s %s" % (self.command, self.path), "  auth: %s" % auth]
        for name in ("Accept", "Content-Type", "User-Agent"):
            if self.headers.get(name) is not None:
                lines.append("  %s: %s" % (name.lower(), self.headers.get(name)))
        if raw:
            try:
                lines.append("  body: " + json.dumps(json.loads(raw), separators=(",", ":")))
            except ValueError:
                lines.append("  body: NOT JSON")
        with open(log_file, "a") as f:
            f.write("\n".join(lines) + "\n")
        status = 200
        if os.path.exists(answer_file + ".status"):
            with open(answer_file + ".status") as f:
                status = int(f.readline())
        with open(answer_file, "rb") as f:
            body = f.read()
        self.send_response(status)
        self.send_header("Content-Type", "application/json")
        self.send_header("Content-Length", str(len(body)))
        self.end_headers()
        if self.command != "HEAD":
            self.wfile.write(body)

    do_GET = do_POST = do_PUT = do_PATCH = do_DELETE = do_HEAD = handle_any

    def log_message(self, *args):
        pass


server = http.server.HTTPServer(("127.0.0.1", 0), Handler)
with open(port_file, "w") as f:
    f.write(str(server.server_address[1]))
server.serve_forever()