The application should not be setting the mapping between the parameter names and their index numbers. This should be done automatically by SQLite as it parses the SQL statement. As each named parameter is encountered SQLite should scan the parameter table to see if this name has already be assigned an index number. If it has, then that number is used for this parameter in the VDBE code that is being generated. If not, then this is a new parameter which is assigned the next highest unused index number automatically.
The reason I suggested having an application function for just explicit mapping is so that we can save on a bloat of bind() functions. There are about 7-10 bind functions now, one for each of the data types of values being bound, and what host params they are being bound to is given as an integer, the second argument. If we ditch the extra function I mentioned, then we will need a set of bind() functions each of whose second argument is the string param name instead of the integer. And twice if we want UTF-8 and UTF-16 versions. A good 15 extra functions.
If there is a concern with cost of scanning the table for each named parameter, this could be replaced with a hash based lookup using SQLite's existing hash functions. I doubt that this will beneficial for anything but extreme cases with very many named parameters.
In either case this process only takes time when preparing the statement. There is no additional overhead when executing a precompiled statement multiple times.
A lookup hash will, of course, still be used by SQLite to map the :ident in the SQL statement and the names given by host_param_name(). And the value for a :foo used more than once in a SQL statement will still be bound exactly once and used multiple times, just as ?N is.
So the question is, would we rather have 1-2 extra host_param_name() functions, or 14-20 extra bind() functions? Of course, the 14-20 may actually be better, and if we actually want to do it that way, then it's all great news to me; and that approach does mean exactly one function call by the app instead of 2.
(FYI, the named parameters, as given in my #1, and the SQL standard, is all I ever plan to use myself, and if it were to come to that being the only means provided, I would be fine with it. The main advantage of the positional numbered options is speed in some environments, or maybe not.)
Would anyone suffer if the plain '?' were just dropped entirely, and :ident + ?N were the only options?
Both of the remaining would work great for generated SQL; in fact, with my own SQL generator, named params are dead easy to deal with, but purely positional plain-? ones are an order of magnitude more difficult.
Even with hand-coded SQL, plain '?' are a pain in all but the most trivial statements, since programmers have to be really careful to get their lists of bind values exactly matched up correctly with the '?', with additional trouble if we want to edit, and that disallows use of the same value multiple times. Plain '?' is generally a detriment to troubleshooting.
So then, if we drop bare '?' entirely, then that will let us conceptually or actually make both the ?N and the :ident into hash keys in a sparse list. The second argument to the existing bind() would no longer be an array index per se. Moreover, the ?N and :ident can be conceptually separate lists, where elements from both can be used in the same SQL statement. The ? or : would just tell SQLite how to treat the characters following, as an integer or identifier.
For "backwards compatability", any plain '?' could still be allowed, and be mixed with both other usages, and each '?' occurance would implicitly be the same as ?1, ?2, etc.
-- Darren Duncan
