So, to move forward on this bug a decision needs to be made at what
level it will be handled.
1. At the SQLite level.
2. At the provider level (ex. System.Data.SQLite, Python providers, etc.)
3. At the user level (ex. NHibernate, Entity Framework, User
Applications, etc.)
Doing it in #3 would involve figuring out which parameters would be
removed and not including those, a very difficult option.
Doing it in #2 would involve adding something that does manual
parameter parsing and validation (such as the parameter tokenizer).
Doing it in #1 would involve things that I don't understand, though it
would simultaneously correct the problems with all providers mentioned
on this thread.
I'm not clear on who is maintaining System.Data.SQLite, but I would
certainly be happy to see some progress towards the resolution of this
issue. Since I don't believe #3 is a feasible option (nor even the
correct place to abstract away the SQLite oddity), the solution should
be #1 or #2.
For #2 there is a performance hit on every query performed using such
a provider (.NET, Python, etc.). The queries need to be parsed by the
provider to determine validity. If the SQLite syntax changes, these
providers need to be updated.
For #1, it sounds like there is a minor performance penalty, but
perhaps it can be implemented in a way where the effects are minimal.
#1 has the advantage that it may be possible to avoid any sort of
significant performance hit unless an "optimized" parameter is
encountered. #2 doesn't have this luxury since it doesn't know when a
parameter might be optimized out.
What would the SQLite team suggest to help progress the fix for this?
If it's at the System.Data.SQLite level, I would be willing to help
contribute a fix. If that were the case, I would hope that the SQLite
syntax could be parsed by a regex for performance reasons.
Patrick Earl
On Wed, Jun 1, 2011 at 10:36 AM, Stephan Beal <sgb...@googlemail.com> wrote:
> On Wed, Jun 1, 2011 at 6:24 PM, Patrick Earl <pate...@patearl.net> wrote:
>
>> From these, the "best" solution seems to be re-tokenizing the sql in
>> the provider. Is there another work-around? It seems that
>> implementing this at the database level would be the most efficient
>> approach. Obviously re-tokenizing all the SQL would be expensive.
>
>
> Actually... if you just want to tokenize the SQL for the parameters, as
> opposed to checking the validity of the SQL itself, it is not all that
> difficult to do. i recently did just that to add named parameter support to
> the MySQL back-end of a db access abstraction API. MySQLv5 doesn't support
> named parameters natively, so i wrote a relatively simple tokenizer which
> can fish them out and another routine which converts named parameters to
> question marks so that we can feed the data to mysql (but can also remember
> enough info to map the named param positions to qmark positions):
>
> http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/b5ba5aa115c73b63179456d1fed7846a11878c05
>
> Search that file for:
>
> cpdo_find_next_named_param
> cpdo_named_params_to_qmarks
>
> The code for those functions is public domain and should be generic enough
> to extract for your own use (almost without modification - i think only the
> return code symbols would need to be replaced). The docs are in the header
> file:
>
> http://fossil.wanderinghorse.net/repos/cpdo/index.cgi/artifact/6749b977687afa081f6b5b3e7fc3b19cd62bb70a
>
> That code has worked for me very well so far, and i haven't yet had any
> mis-parsing except on code which was itself not legal SQL (i.e. it doesn't
> seem to create any new problems where there isn't already a problem).
>
> --
> ----- stephan beal
> http://wanderinghorse.net/home/stephan/
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
