Anyone out there have any insight on how to define a regexp() function
for SQLite at run-time as described at
http://www.sqlite.org/lang_expr.html#regexp so that I can say things like:
echo "select foo from bar where baz REGEXP '^[Cc]'" | db.select
while using the sqlite driver for managing vector attributes?
Without taking any special action in 6.2.2cvs I get:
DBMI-SQLite driver error:
Error in sqlite3_prepare():select * from gtnotes2 where comments regexp
'^[Cc]orn'
no such function: regexp
Thanks.
Neil
--
Neil Best <nbest@lanworth.com>
Project Analyst
Lanworth, Inc. (formerly Forest One, Inc.)
tel 630 250 8664
Neil Best wrote:
Anyone out there have any insight on how to define a regexp() function
for SQLite at run-time as described at
http://www.sqlite.org/lang_expr.html#regexp
http://www.sqlite.org/capi3ref.html#sqlite3_create_function
This would need to be done in the driver (db/drivers/sqlite), probably
whenever a database is opened (db__driver_open_database, in db.c).
As for implementation: regcomp/regexec should be available on all Unix
systems, but you might need a separate library for Windows.
--
Glynn Clements <glynn@gclements.plus.com>
Glynn Clements wrote:
Neil Best wrote:
Anyone out there have any insight on how to define a regexp() function
for SQLite at run-time as described at
http://www.sqlite.org/lang_expr.html#regexp
http://www.sqlite.org/capi3ref.html#sqlite3_create_function
This would need to be done in the driver (db/drivers/sqlite), probably
whenever a database is opened (db__driver_open_database, in db.c).
As for implementation: regcomp/regexec should be available on all Unix
systems, but you might need a separate library for Windows.
Glynn, thanks for the reply. Programming at this level is not my strong suit so it's difficult for me to gauge how much effort would be involved in filling this gap. I have to wonder why this stub in SQLite has not been filled in but it seems like there are enough work-arounds, like getting the results from SQLite and using your favorite scripting language to do the regex filtering, such that the demand is simply not there. I found a piece of code on the SQLite user list that claims to meet this need[1] -- maybe someone would be interested in vetting this. I will write to the author and see what I can learn, especially regarding his claim that linking to regex(7) doesn't really solve the problem. The fact that his post generated zero follow-ups is an expression of lack of interest in the SQLite community, I would say. Thanks.
Neil
[1] http://article.gmane.org/gmane.comp.db.sqlite.general/23584
Neil Best wrote:
>>Anyone out there have any insight on how to define a regexp() function
>>for SQLite at run-time as described at
>>http://www.sqlite.org/lang_expr.html#regexp
>
>
> http://www.sqlite.org/capi3ref.html#sqlite3_create_function
>
> This would need to be done in the driver (db/drivers/sqlite), probably
> whenever a database is opened (db__driver_open_database, in db.c).
>
> As for implementation: regcomp/regexec should be available on all Unix
> systems, but you might need a separate library for Windows.
>
Glynn, thanks for the reply. Programming at this level is not my strong
suit so it's difficult for me to gauge how much effort would be involved
in filling this gap. I have to wonder why this stub in SQLite has not
been filled in
Portability, perhaps. Windows systems won't have regexec(), while some
Unix systems may have it in a separate library, requiring additional
linker switches. There's also the choice of basic/extended/perl
syntax.
but it seems like there are enough work-arounds, like
getting the results from SQLite and using your favorite scripting
language to do the regex filtering, such that the demand is simply not
there. I found a piece of code on the SQLite user list that claims to
meet this need[1] -- maybe someone would be interested in vetting this.
It seems sound, although a version using POSIX REs would be preferable
to PCREs, both for portability and for compatibility with other
drivers.
I will write to the author and see what I can learn, especially
regarding his claim that linking to regex(7) doesn't really solve the
problem.
I think that he's saying that using PCREs is bad for compatibility, as
most other SQL implementations use POSIX REs.
--
Glynn Clements <glynn@gclements.plus.com>