[GRASS-user] SQLite REGEXP

[Glynn Clements]

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 at gclements.plus.com>