1. SQLite-style in-process database engines in native Scheme (I can dream!)
SQLite has done a huge amount of work; duplicating it doesn't seem like a win.
2. Scheme implementations of wire protocols for databases such as MySQL,
PSQL, etc
Feasible but hard, and probably infeasible for commercial DBs.
3. Scheme adapters to in-process FFIs for libpq et al
Feasible and either easy or impossible, depending.
4. Scheme code to talk a standardised subprocess protocol to external
driver binaries, written in whatever language
One advantage of my proposed textual protocol is that you don't need any code: you just use display to give the server your bindings and query, and then repeatedly call read to get back the answers. (This assumes that the intermediary is trusted, which it ought to be, as you are running it on your own machine.)
...(4) is a useful option, but I think one that needs to be defined
separately from the Scheme-level API first,
Fair enough.
I'm not sure how the wire protocols work for MySQL, PostgreSQL, etc. but
I seem to recall that the library interfaces at least in principle allow
you to do something like:
rs1 = query("SELECT * FROM very_big_table");
rs2 = query("SELECT ... FROM t1, t2, t3, t4, t5 WHERE ...join
conditions...");
repeat {
r1 = rs1.read_row();
r2 = rs2.read_row();
...some process involving r1 and r2...
} until rs1.eof() || rs2.eof();
Good point; I have to think about that one. Your design seems a priori reasonable, except that I think the query IDs should be assigned by the client.