>     So a table where SQL strings are weak references pointing to
>     sqlite_stmt foreign objects?
>
> They aren't actually weak references, but soft references: that is, they
> break when there is too much memory pressure as opposed to when there
> are no other references.  In this case "memory pressure" means within
> the cache, not general memory.  I think that weak-reference behavior
> (i.e. removing the string when it is GCed on the Scheme side) is not
> really necessary.

True. Weak references might actually undermine the caching behavior if
the GC is too aggressive, since it would reclaim statements right away
if they are not constantly in use?

We also need to be careful to call C destructors on the stmt objects.
Caching is not fun.

>     And it's probably good enough to compare the strings
>     using eq?.
>
> Depends where it is.  If it's on the C side, you don't want to do that
> because gc, hence the idea of a string hash.  If it's on the Scheme side
> eq? is probably good enough.

Scheme-side is best so the FFI doesn't have to unnecessarily convert
Scheme strings to C strings which are not used.

>     If you want to optimize for speed, then (sql-do `("mumble @a @b @c
>     mumble" a ,a b ,b c ,c)) is a bit wasteful as well. SQLite uses
>     positional parameters internally, and the user must convert named
>     parameters to positional ones using sqlite3_bind_parameter_index().
>
> I can't believe the additional cost is worth the loss of clarity and
> readability.

I wonder whether a cache is worth the effort to begin with. Profile
before optimizing and all that. If many wrappers have one, perhaps it
is. There's a school of thought where people are like "always compile
your regexps" but you have to eval them a thousand times for it to
matter. For most purposes, the nicest thing about pre-compiling stuff is
that some errors are caught earlier.

> It's true that PostgreSQL doesn't support named parameters
> in binding, but worst-case we can parse them out ourselves and replace
> them with the $nn format.

Oh damn, Postgres of all databases. "Et tu, Brute?" This is not going to
be fun. Check out how the Python wrapper does it:
<https://github.com/psycopg/psycopg3/blob/7a3e8bf3807d333b06f50a1ecff754a03ed417b2/psycopg3/psycopg3/utils/queries.py#L101>.
I suggest we don't do this, and leave Postgres named parameters to a DSL.

> One nice thing about @ is that it can be
> replaced pretty uniformly except within strings and "-quoted
> identifiers, as it has no meaning in any SQL dialect (which is not true
> of :).

Many people have borne such optimism. Unfortunately I've had it beaten
out of me enough times in the past to be opposed to any escaping that
isn't absolutely required. We should admit that no-one can understand
string escaping, especially against an incompletely known set of
evolving tools, and there's no reward for trying to understand it.

A SQL DSL, on the other hand, would be a very nice thing to have. Since
it has full control over its own output, it can also do any necessary
escaping more safely by restricting its output to a conservative set of
idioms.