Note that I don't have any current SQLite 3 code in front of me, so the following is more a description of concepts and/or references to the documentation on the sqlite.org website; some of my suggestions may be identical to things already done, and some may not be, I don't know which is which yet.
First of all, I believe that, to serve the most needs, SQLite should accept host parameter references in SQL strings in exactly 3 formats:
1. by name, as the SQL standard dictates, looking like: <colon><identifier>
2. by bind var array position name, looking like eg: <question mark><integer>
- or possibly like eg: <colon><integer><colon>
- not recommended since looks to similar to "by name"
- but probably not like eg: <colon><integer>
- only works if a delimited <identifier> can never be an integer, but it can be
3. by pure wildcard position, looking like: <question mark>
There would be a single root implementation where all host parameters are referenced by a 1..N index number, which is the second argument to the sqlite3_bind_*() functions. All actual binding operations would continue to use these as if #2 was the only option. This single root implementation would be like "named" such that each host parameter can be referenced any number of times in a SQL string, and in a different order than their binding index, but that the "identifier" is always an integer.
As an internally-implemented thin wrapper on top of this, #1 would lead to the addition of one conceptual function or two actual functions like this:
int sqlite3_host_param_name(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_host_param_name16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
Calling one of those functions would map a character string / "text" to one of the "named integer" host parameters so that they effectively become synonyms. For example, calling:
sqlite3_host_param_name(stmt, 1, 'foo', ...);
... would result in a statement "SELECT :foo AS a, ?1 AS b" having the same value in 'a' and 'b'.
A caller application would use #1 or #2 in exactly the same way, except #1 has a single extra step of calling host_param_name also. Of course, it doesn't matter whether host_param_name() or bind() is called first; they both just have to be before the execute().
The #3 above would also be an internally-implemented thin wrapper over #2. Any time the parser encounters a plain '?', it associates that position with the next consecutive index. A caller application makes exactly the same number of function calls for #3 as they do #2.
As far as formatting goes in SQL strings, I think #1 and #3 are nailed down perfectly. But I still think there is room for improvement in #2. For example, unless you have a vested interest in multiple aliases such as both ?N and :N:, I suggest getting rid of one of those, or getting rid of both and picking a third option that looks markedly different from both #1 and #3. If you keep an existing one, I suggest the ?N version. Otherwise, have just a leading sigil (no trailing), for consistency, and make it something other than ? and :.
And of course, anything host language specific, TCL or otherwise, shouldn't be in the core, so no $.
Once again, my apologies if this post is redundant, but I thought it easier to describe a target rather than simply a delta.
-- Darren Duncan
