sqlload 0.75.0

SQL data loading tooling.
"""SQLLoad's MotherDuck bridge: SQL run on PyPI's duckdb (1.4.1 to 1.5.6, which MotherDuck accepts) for a SQLLoad process, whose bundled DuckDB MotherDuck doesn't accept yet.

SQLLoad starts it as `python -c <this file>` and sends one JSON request per line on stdin; each reply is one JSON line on stdout, tagged with its request's id:

    {"id": 1, "op": "open", "database": "md:shop?motherduck_token=...", "config": {...}}
        -> {"id": 1, "ok": true, "duckdb": "1.5.6", "python": "3.12.3"}
    {"id": 2, "op": "run", "cursor": 3, "sql": "SELECT ...", "params": ["raw"]}
        -> {"id": 2, "ok": true, "rows": [["id", "BIGINT"], ...]}
    {"id": 3, "op": "stream", "cursor": 4, "sql": "SELECT ...", "rows": 100000}
        -> {"id": 3, "ok": true}
    {"id": 4, "op": "next", "cursor": 4, "path": "/tmp/batch.arrow"}
        -> {"id": 4, "ok": true, "rows": 100000}, the next batch of at most that many rows written there as an Arrow IPC file; 0 rows once all are read
    {"op": "close", "cursor": 3}
        -> no reply
    {"op": "interrupt", "cursor": 3}
        -> no reply; the cursor's running statement fails, and its reply says so

A failure replies {"id": n, "ok": false, "error": "...", "type": "CatalogException"}. Each cursor is a connection of its own to
the opened database (DuckDB's `cursor()`), created on its first request and run on its own thread in request order, so one
table's transaction never mixes with another's. The bridge exits when stdin closes, which rolls back any open transaction.
"""

import sys

# `python -c` puts the working directory first on the path, where a stray duckdb.py would shadow the real package.
if sys.path and sys.path[0] == "":
	del sys.path[0]

import json
import math
import os
import queue
import signal
import threading


def plain(value):
	"""`value` as JSON can carry it: numbers, text, booleans and NULL as they are, lists item by item, anything else as its text."""
	if value is None or isinstance(value, (bool, int, str)):
		return value
	if isinstance(value, float):
		return value if math.isfinite(value) else str(value)
	if isinstance(value, (list, tuple)):
		return [plain(item) for item in value]
	return str(value)


def write_next(batches, path):
	"""The next batch `batches` holds, written to the existing file `path` as an Arrow IPC file; its row count, 0 once every batch is read. A missing `path` fails."""
	import pyarrow.ipc

	try:
		batch = batches.read_next_batch()
	except StopIteration:
		return 0
	# SQLLoad created the file, readable by its user only; opening it rather than creating it means a file SQLLoad has already removed (its read stopped) is never written again.
	with open(path, "r+b") as sink:
		sink.truncate()
		with pyarrow.ipc.new_file(sink, batch.schema) as writer:
			writer.write_batch(batch)
	return batch.num_rows


def main():
	# Replies go to a private copy of stdout; anything else that prints (DuckDB, an extension) lands on stderr instead.
	replies = os.fdopen(os.dup(1), "wb", buffering=0)
	os.dup2(2, 1)
	sys.stdout = sys.stderr
	# Ctrl-C reaches the whole process group; SQLLoad ends, its pipe closes, and that ends the bridge.
	signal.signal(signal.SIGINT, signal.SIG_IGN)
	lock = threading.Lock()

	def reply(message):
		line = json.dumps(message, ensure_ascii=True, allow_nan=False).encode("ascii") + b"\n"
		with lock:
			replies.write(line)

	def failed(request, error):
		reply({"id": request.get("id"), "ok": False, "error": str(error), "type": type(error).__name__})

	database = None
	cursors = {}
	opened = {}

	def serve(work, number):
		cursor = None
		batches = None
		while True:
			request = work.get()
			if request is None:
				break
			try:
				if cursor is None:
					cursor = opened[number] = database.cursor()
				if request["op"] == "next":
					reply({"id": request["id"], "ok": True, "rows": write_next(batches, request["path"])})
					continue
				cursor.execute(request["sql"], request.get("params") or None)
				if request["op"] == "stream":
					# Arrow batches straight from DuckDB's streaming result, one at a time as `next` asks, so the bridge never holds them all.
					# duckdb 1.4 names the reader fetch_record_batch; 1.5 adds to_arrow_reader, and 2.0 keeps only that.
					reader = getattr(cursor, "to_arrow_reader", None) or cursor.fetch_record_batch
					batches = reader(request["rows"])
					reply({"id": request["id"], "ok": True})
					continue
				rows = cursor.fetchall() if cursor.description else []
				reply({"id": request["id"], "ok": True, "rows": [[plain(value) for value in row] for row in rows]})
			except BaseException as error:  # noqa: BLE001 - every failure goes back to SQLLoad as its reply, or it would wait forever
				failed(request, error)
		opened.pop(number, None)
		if cursor is not None:
			cursor.close()

	for line in sys.stdin.buffer:
		request = json.loads(line)
		op = request.get("op")
		if op == "open":
			try:
				import duckdb

				database = duckdb.connect(request["database"], config=request.get("config") or {})
				reply({"id": request["id"], "ok": True, "duckdb": duckdb.__version__, "python": sys.version.split()[0]})
			except Exception as error:  # noqa: BLE001
				failed(request, error)
		elif op in ("run", "stream", "next"):
			work = cursors.get(request["cursor"])
			if work is None:
				work = cursors[request["cursor"]] = queue.Queue()
				threading.Thread(target=serve, args=(work, request["cursor"]), daemon=True).start()
			work.put(request)
		elif op == "interrupt":
			# DuckDB's interrupt is safe from another thread: the cursor's running statement stops and rolls back.
			running = opened.get(request["cursor"])
			if running is not None:
				running.interrupt()
		elif op == "close":
			work = cursors.pop(request["cursor"], None)
			if work is not None:
				work.put(None)
		else:
			failed(request, ValueError(f"unknown op {op!r}"))
	# stdin closed: SQLLoad is done or gone. Statements still running are abandoned, and open transactions roll back.
	replies.flush()
	os._exit(0)


main()