http_query.sh

#!/usr/bin/env bash
# freebase.cloud — free cloud databases, MCP-ready: https://freebase.cloud
#
# http_query.sh — hosted SQLite over HTTP, and the round-trip lesson.
#
# Sends statements to freebase.cloud's SQLite query API and times them, so the
# cost model of hosted SQLite is visible rather than theoretical.
#
#   export FREEBASE_NAMESPACE="wordbank"      # your namespace / database name
#
#   ./http_query.sh version                    # one statement
#   ./http_query.sh sql "SELECT count(*) FROM entries"
#   ./http_query.sh script wordbank.sql        # whole file in ONE request
#   ./http_query.sh roundtrips                 # the demonstration worth reading
#
# Requires curl. jq is used when present and skipped when not.

set -euo pipefail

API="${FREEBASE_API:-https://freebase.cloud/api/wire/query}"
NAMESPACE="${FREEBASE_NAMESPACE:-wordbank}"

have_jq() { command -v jq >/dev/null 2>&1; }

# One request = one round trip. Everything in this file is counted in these.
send() {
  local sql="$1"
  local body
  body=$(NS="$NAMESPACE" SQL="$sql" python3 -c '
import json, os
print(json.dumps({"namespace": os.environ["NS"],
                  "protocol": "sqlite",
                  "query": os.environ["SQL"]}))')
  curl -sS -X POST "$API" -H "Content-Type: application/json" -d "$body"
}

pretty() {
  if have_jq; then jq .; else cat; echo; fi
}

# Milliseconds, portably enough.
now_ms() { python3 -c 'import time; print(int(time.time()*1000))'; }

# Sets LAST_MS rather than echoing the number, so the printed line and the
# measurement do not have to share stdout.
LAST_MS=0
timed() {
  local label="$1" sql="$2" start end
  start=$(now_ms)
  send "$sql" >/dev/null
  end=$(now_ms)
  LAST_MS=$((end - start))
  printf '  %-46s %5d ms\n' "$label" "$LAST_MS"
}

case "${1:-help}" in

  version)
    send "SELECT sqlite_version() AS version" | pretty
    ;;

  sql)
    [ $# -ge 2 ] || { echo "usage: $0 sql \"SELECT ...\""; exit 2; }
    send "$2" | pretty
    ;;

  script)
    # The important detail: the whole file goes in one request, so it executes
    # as a single unit server-side. Splitting it into one request per statement
    # would be slower AND would lose the transaction boundary.
    [ $# -ge 2 ] || { echo "usage: $0 script wordbank.sql"; exit 2; }
    [ -f "$2" ] || { echo "no such file: $2"; exit 2; }
    echo "Sending $(grep -c ';' "$2") statement(s) from $2 as one request..."
    send "$(cat "$2")" | pretty
    ;;

  roundtrips)
    cat <<'EOF'
The round-trip demonstration
============================

Locally, the number of queries you run barely matters — a query costs the work
it does. Over HTTP the query cost is dominated by the round trip, so the number
of requests becomes the only number that matters.

Three ways to answer the same question, timed.

EOF
    echo "Setting up a small table (1 request)..."
    send "CREATE TABLE IF NOT EXISTS entries (
            headword TEXT PRIMARY KEY, region TEXT NOT NULL,
            gloss TEXT NOT NULL, lookups INTEGER NOT NULL DEFAULT 0)" >/dev/null
    send "INSERT OR IGNORE INTO entries (headword, region, gloss) VALUES
            ('mizzle','Devon','fine drizzling rain'),
            ('clarty','Northeast','sticky, muddy'),
            ('bide','Yorkshire','to endure'),
            ('dreich','Scotland','dreary and wet'),
            ('gurt','Somerset','great, large')" >/dev/null
    echo

    echo "A. One statement, one round trip:"
    timed "SELECT ... WHERE headword IN (5 values)" \
      "SELECT headword, gloss FROM entries
       WHERE headword IN ('mizzle','clarty','bide','dreich','gurt')"
    one_shot=$LAST_MS
    echo

    echo "B. Five statements, five round trips (the N+1 pattern):"
    loop_total=0
    for word in mizzle clarty bide dreich gurt; do
      timed "SELECT ... WHERE headword = '$word'" \
        "SELECT headword, gloss FROM entries WHERE headword = '$word'"
      loop_total=$((loop_total + LAST_MS))
    done
    echo "  ---"
    printf '  %-46s %5d ms  (one statement was %d ms)\n' \
      "total for the loop" "$loop_total" "$one_shot"
    echo

    echo "C. Read-modify-write, which is also incorrect:"
    timed "SELECT lookups ..." "SELECT lookups FROM entries WHERE headword='mizzle'"
    timed "UPDATE ... SET lookups = <value + 1>" \
      "UPDATE entries SET lookups = 1 WHERE headword='mizzle'"
    echo "  Two round trips, and anyone else's write between them is lost."
    echo
    echo "D. The same increment, atomically, in one:"
    timed "UPDATE ... SET lookups = lookups + 1" \
      "UPDATE entries SET lookups = lookups + 1 WHERE headword='mizzle'"

    cat <<EOF

Reading it
----------
A and B return identical data. B costs roughly five times as much, and the ratio
grows linearly with the size of your loop — at a hundred rows it is a hundred
round trips and a visibly broken page.

C and D produce the same number only when nothing else is writing. D is correct
under concurrency because SQLite evaluates lookups + 1 inside the statement; C
is a read-modify-write across two requests with no transaction spanning them,
which is the defining bug of stateless database APIs.

Against a local file the difference between A and B is microseconds and nobody
notices. That is the entire adjustment hosted SQLite asks of you: count round
trips, not queries.
EOF
    ;;

  help|*)
    cat <<EOF
usage: $0 <command>

  version              print SELECT sqlite_version()
  sql "<statement>"    run one statement
  script <file.sql>    send a whole file as ONE request (one transaction)
  roundtrips           timed demonstration of the HTTP cost model

environment:
  FREEBASE_NAMESPACE   your namespace (default: wordbank)
  FREEBASE_API         override the endpoint (default: $API)
EOF
    ;;
esac