[sqlite] REGEXP pcre DLL for Windows

[sonypsx]

Yes of course - the answer is: didn't even know that a default do exist.
Sorry!

Grabbed the regexp.c compiled it - and yes - it works - plain and simple!

Thanks a lot!

For the archive if someone else try to compile it:

gcc -s -O4 -I c:\sqlite\src\ -shared -o c:\sqlite\bin\regexp.dll
c:\sqlite\ext\misc\regexp.c

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Richard
Hipp
Gesendet: Dienstag, 19. Mai 2015 00:34
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] REGEXP pcre DLL for Windows

On 5/18/15, Keith Medcalf  wrote:
>
> Is there any reason why the default REGEXP is unsuitable?
>
> ext/misc/regexp.c
>
> in the source distribution ...
>

Just in case it helps to answer the above question

The RE syntax recognized by regexp.c is summarized in the header comment
which can be perused here:
https://www.sqlite.org/src/artifact/af92cdaa5058fcec

The RE syntax recognized by regexp.c is a subset of PCRE.  (The languages
recognized by PCRE actually go beyond true Regular Expressions - but that is
a topic for purists and theoreticians.)  On the other hand, the regexp.c
matcher is guaranteed to run in constant time (relative to the size of the
string being scanned) whereas PCREs can run in exponential time for certain
pathological cases.
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP pcre DLL for Windows

[sonypsx]

Hello Coder,

yes that is working fine! Thank you!
As I'm always in struggle when I try to compile such things for sqlite.
Do you have a small step by step HOWTO compiled this?
Especially the used gcc parameters and so ...

Would be great for the future but anyway - the provided DLL is working.
Thanks!

Best regards
Martin

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Random
Coder
Gesendet: Montag, 18. Mai 2015 19:59
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] REGEXP pcre DLL for Windows

On Mon, May 18, 2015 at 9:43 AM, sonypsx  wrote:
> may i kindly ask again about REGEX pcre DLL for Windows?
>
> Do someone have an idea or even a precompiled DLL which works under
sqlite?

As you've found, you don't just need a pcre dll for windows, you need a
sqlite3 module to implement the callbacks sqlite uses.  I'm not aware if
there's version generally available.  I have a version I built for internal
use, based off some public domain code, you can grab the source or DLL from:

http://bucket-1f8c9d86.s3.amazonaws.com/sqlite3-pcre-src.zip
http://bucket-1f8c9d86.s3.amazonaws.com/sqlite3-pcre.zip

It works for my purposes.  I can't support it beyond that, though.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP pcre DLL for Windows

[sonypsx]

Dear SQLite Users,

may i kindly ask again about REGEX pcre DLL for Windows?

Do someone have an idea or even a precompiled DLL which works under sqlite?

Best regards
Martin

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von sonypsx
Gesendet: Mittwoch, 06. Mai 2015 16:55
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] REGEXP pcre DLL for Windows

Hello,

can somebody me please and provide a working almost recent precompiled
windows dll version of "pcre"  which is capable to load within sqlite3?
I did try hard, found some precompiled ones at
http://www.airesoft.co.uk/pcre.
But they cannot be loaded within sqlite3.  I did the try with the version
8.33. I did tried both 32bit and 64bit version. Of course an older version
produce the same error.
At the end I just show that a working dll (spellfix.dll) is loading fine.

I also tried to compile some pcre versions but I ended up either with errors
while compiling or with the same error found below.
Here is the output of my try: (as I'm using a german version I translated
the error message when try to load that dll)

16.06.2013  00:30   321 536 pcre3.dll
13.03.2015  19:4833 792 spellfix.dll
13.03.2015  20:07   566 272 sqlite3.exe
03.05.2015  02:22   305 152 test2.s3db

c:\sqlite.test>sqlite3.exe

SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test2.s3db
sqlite> .load pcre3.dll
Error: Cannot find the procedure
sqlite> .load spellfix.dll
sqlite>

Best regards
Martin

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP pcre DLL for Windows

[Richard Hipp]

On 5/18/15, Keith Medcalf  wrote:
>
> Is there any reason why the default REGEXP is unsuitable?
>
> ext/misc/regexp.c
>
> in the source distribution ...
>

Just in case it helps to answer the above question

The RE syntax recognized by regexp.c is summarized in the header
comment which can be perused here:
https://www.sqlite.org/src/artifact/af92cdaa5058fcec

The RE syntax recognized by regexp.c is a subset of PCRE.  (The
languages recognized by PCRE actually go beyond true Regular
Expressions - but that is a topic for purists and theoreticians.)  On
the other hand, the regexp.c matcher is guaranteed to run in constant
time (relative to the size of the string being scanned) whereas PCREs
can run in exponential time for certain pathological cases.
-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] REGEXP pcre DLL for Windows

[Keith Medcalf]

Is there any reason why the default REGEXP is unsuitable?

ext/misc/regexp.c

in the source distribution ...

> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of sonypsx
> Sent: Monday, 18 May, 2015 10:44
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] REGEXP pcre DLL for Windows
> 
> Dear SQLite Users,
> 
> may i kindly ask again about REGEX pcre DLL for Windows?
> 
> Do someone have an idea or even a precompiled DLL which works under
> sqlite?
> 
> Best regards
> Martin
> 
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von
> sonypsx
> Gesendet: Mittwoch, 06. Mai 2015 16:55
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] REGEXP pcre DLL for Windows
> 
> Hello,
> 
> can somebody me please and provide a working almost recent precompiled
> windows dll version of "pcre"  which is capable to load within sqlite3?
> I did try hard, found some precompiled ones at
> http://www.airesoft.co.uk/pcre.
> But they cannot be loaded within sqlite3.  I did the try with the version
> 8.33. I did tried both 32bit and 64bit version. Of course an older version
> produce the same error.
> At the end I just show that a working dll (spellfix.dll) is loading fine.
> 
> I also tried to compile some pcre versions but I ended up either with
> errors
> while compiling or with the same error found below.
> Here is the output of my try: (as I'm using a german version I translated
> the error message when try to load that dll)
> 
> 16.06.2013  00:30   321 536 pcre3.dll
> 13.03.2015  19:4833 792 spellfix.dll
> 13.03.2015  20:07   566 272 sqlite3.exe
> 03.05.2015  02:22   305 152 test2.s3db
> 
> c:\sqlite.test>sqlite3.exe
> 
> SQLite version 3.8.8.3 2015-02-25 13:29:11
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> .open test2.s3db
> sqlite> .load pcre3.dll
> Error: Cannot find the procedure
> sqlite> .load spellfix.dll
> sqlite>
> 
> Best regards
> Martin
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP pcre DLL for Windows

[Random Coder]

On Mon, May 18, 2015 at 9:43 AM, sonypsx  wrote:
> may i kindly ask again about REGEX pcre DLL for Windows?
>
> Do someone have an idea or even a precompiled DLL which works under sqlite?

As you've found, you don't just need a pcre dll for windows, you need
a sqlite3 module to implement the callbacks sqlite uses.  I'm not
aware if there's version generally available.  I have a version I
built for internal use, based off some public domain code, you can
grab the source or DLL from:

http://bucket-1f8c9d86.s3.amazonaws.com/sqlite3-pcre-src.zip
http://bucket-1f8c9d86.s3.amazonaws.com/sqlite3-pcre.zip

It works for my purposes.  I can't support it beyond that, though.

[sqlite] REGEXP pcre DLL for Windows

[sonypsx]

Hello,



can somebody me please and provide a working almost recent precompiled
windows dll version of "pcre"  which is capable to load within sqlite3?

I did try hard, found some precompiled ones at
http://www.airesoft.co.uk/pcre.

But they cannot be loaded within sqlite3.  I did the try with the version
8.33. I did tried both 32bit and 64bit version. Of course an older version
produce the same error.

At the end I just show that a working dll (spellfix.dll) is loading fine.



I also tried to compile some pcre versions but I ended up either with errors
while compiling or with the same error found below.



Here is the output of my try: (as I'm using a german version I translated
the error message when try to load that dll)



16.06.2013  00:30   321 536 pcre3.dll

13.03.2015  19:4833 792 spellfix.dll

13.03.2015  20:07   566 272 sqlite3.exe

03.05.2015  02:22   305 152 test2.s3db



c:\sqlite.test>sqlite3.exe

SQLite version 3.8.8.3 2015-02-25 13:29:11

Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

sqlite> .open test2.s3db

sqlite> .load pcre3.dll

Error: Cannot find the procedure



sqlite> .load spellfix.dll

sqlite>



Best regards

Martin

Re: [sqlite] regexp within trim function

[Igor Tandetnik]

Linuxed  wrote:
> Is it possible to use regexp within a trim function? For example, if I wanted 
> to remove every alpha-numeric character at the end
> of a string I could use the statement below. Is there any way to 
> simplify/condense the statement through regexp or a wildcard
> character?

Not with a built-in function. You can always write a custom function, and do 
whatever you want there.

> Also, if I want to remove single or double quotes in addition to 
> alpha-numeric characters how can I include them in
> the string of characters to trim? I've tried \' and \" without success.   

You include double quotes as-is. You escape an apostrophe by doubling it up:

'Here''s a "quote"'

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] regexp within trim function

[Linuxed]

Is it possible to use regexp within a trim function?  For example, if I wanted 
to remove every alpha-numeric character at the end of a string I could use the 
statement below.  Is there any way to simplify/condense the statement through 
regexp or a wildcard character?  Also, if I want to remove single or double 
quotes in addition to alpha-numeric characters how can I include them in the 
string of characters to trim?  I've tried \' and \" without success.


rtrim('this is an example. this is another example', 
'1234567890qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM')
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order - embarassed newbie

[Teg]

Hello James630165,

I guess it depends on how good a programmer you are. I just used the
web site and some reference books on SQL. Wrote my own wrapper and
improve it as time goes on and I need new features.

C

Sunday, January 23, 2011, 8:05:08 PM, you wrote:

Jac> JCC/DRH
Jac>  
Jac> If a person speaks good English and has a logical mind, has previously
Jac> programmed in C ... why is there a need to buy "The definitive guide to
Jac> programming SQLite"?
Jac>  
Jac> I understand that "Everyone does".
Jac>  
Jac> Jim.
Jac> ___
Jac> sqlite-users mailing list
Jac> sqlite-users@sqlite.org
Jac> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 
Best regards,
 Tegmailto:t...@djii.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order - embarassed newbie

[Duquette, William H (318K)]

Being one who speaks good English, has a logical mind, and has previously 
programmed in C, AND who had used SQLite for around five years on the strength 
of that, I still found the book useful when I read it a couple of months ago.  
I already knew the basics, but it shed light on a few obscure areas for me.

Will


On 1/23/11 5:05 PM, "james630...@aol.com"  wrote:

JCC/DRH

If a person speaks good English and has a logical mind, has previously
programmed in C ... why is there a need to buy "The definitive guide to
programming SQLite"?

I understand that "Everyone does".

Jim.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order

[Jean-Christophe Deschamps]

Hi Igor,

>I'm not quite sure what you are talking about, but see the 
>documentation of like() and glob() functions here:

You're right about LIKE and GLOB.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order

[Igor Tandetnik]

Jean-Christophe Deschamps  wrote:
> Digging further I found mention of this only in the VTAB description,
> but may be a mention or link to the infix argument reversal should
> appear in the LIKE, GLOB, REGEXP, MATCH description, as implementing
> simple extension functions doesn't imply immersion in VTAB stuff (IMVHO).

I'm not quite sure what you are talking about, but see the documentation of 
like() and glob() functions here:

http://www.sqlite.org/lang_corefunc.html

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order - embarassed newbie

[James630165]

JCC/DRH
 
If a person speaks good English and has a logical mind, has previously  
programmed in C ... why is there a need to buy "The definitive guide to  
programming SQLite"?
 
I understand that "Everyone does".
 
Jim.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order

[Jean-Christophe Deschamps]

> > Sorry for elementary questions but when implementing a REGEXP function
> > (I'm using PCRE from pcre.org) I need to know in which order the two
> > arguments (target, pattern) will be passed to the function.
> >
> > I also would like to retain the last pattern used in compiled form
> > (connection-wise).  Is the use of sqlite3_get_auxdata possible in this
> > context as it is in overriding LIKE functions, for instance?
> >
>
>REGEXP works just like LIKE and GLOB.

Thanks Richard for this confirmation.

Digging further I found mention of this only in the VTAB description, 
but may be a mention or link to the infix argument reversal should 
appear in the LIKE, GLOB, REGEXP, MATCH description, as implementing 
simple extension functions doesn't imply immersion in VTAB stuff (IMVHO).

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP parameter order

[Richard Hipp]

On Sun, Jan 23, 2011 at 5:02 PM, Jean-Christophe Deschamps 
wrote:

> Hi all,
>
> Sorry for elementary questions but when implementing a REGEXP function
> (I'm using PCRE from pcre.org) I need to know in which order the two
> arguments (target, pattern) will be passed to the function.
>
> I also would like to retain the last pattern used in compiled form
> (connection-wise).  Is the use of sqlite3_get_auxdata possible in this
> context as it is in overriding LIKE functions, for instance?
>

REGEXP works just like LIKE and GLOB.


>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP parameter order

[Jean-Christophe Deschamps]

Hi all,

Sorry for elementary questions but when implementing a REGEXP function 
(I'm using PCRE from pcre.org) I need to know in which order the two 
arguments (target, pattern) will be passed to the function.

I also would like to retain the last pattern used in compiled form 
(connection-wise).  Is the use of sqlite3_get_auxdata possible in this 
context as it is in overriding LIKE functions, for instance?

Thank you.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP

[Thomas Briggs]

   I think it had more to do with a) the fact that it's non-standard
syntax and b) compiling in the regex library would unnecessary bloat
the binary.

   See pcre.org for a free (non-GPL) regex library that has proven to
work nicely with SQLite. :)

   -T


On Tue, Dec 16, 2008 at 1:44 PM, Griggs, Donald
 wrote:
> Regarding:  "...No regexp() user function is defined by default..."
>
> The reason, if I recall correctly, is that the regexp routines that were
> available were GPL'd and would mess with the licensing of sqlite.
> ___
> 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

Re: [sqlite] REGEXP

[Griggs, Donald]

Regarding:  "...No regexp() user function is defined by default..."

The reason, if I recall correctly, is that the regexp routines that were
available were GPL'd and would mess with the licensing of sqlite.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP

[Scott Baker]

Ben Marchbanks wrote:
> I am confused. Is REGEXP enabled in SQLite or does there have to be a 
> regexp custom function created ?

The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If a user-defined
function named "regexp" is added at run-time, that function will be called
in order to implement the REGEXP operator.

http://www.sqlite.org/lang_expr.html

It's something you'll have to add.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] REGEXP

[Ben Marchbanks]

I am confused. Is REGEXP enabled in SQLite or does there have to be a 
regexp custom function created ?

-- 
*Ben Marchbanks*

www.magazooms.com 
Signature
Email: b...@magazooms.com 
Phone: (864) 284.9918
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] REGEXP implementation with pcre

[Alexey Tourbin]

On Wed, Jul 11, 2007 at 09:07:39AM -0500, Neil Best wrote:
> see code from original post at
> http://article.gmane.org/gmane.comp.db.sqlite.general/23584

BTW, the code has been released under the same terms as SQLite itself.
http://git.altlinux.org/people/at/packages/?p=sqlite3-pcre.git
git://git.altlinux.org/people/at/packages/sqlite3-pcre.git

> I came across this note from last fall while looking for information on
> how to activate the REGEXP functionalilty. Not only would I like to know
> whether this particular solution is correct and appropriate, but I am 
> generally curious about why no regexp() function has found its way into 
> the SQLite code for general distribution.

Because you need a regexp libaray, which can be as complicated
as sqlite itself.  Basically you need pcre library, which is simply not
part of sqlite.  This is why the best way to implement regexp() function
is with loadable module, as far as I see.


pgpOX1ABVxnKi.pgp
Description: PGP signature

[sqlite] REGEXP implementation with pcre

[Neil Best]

On 14 Nov 2006 Alexey Tourbin <[EMAIL PROTECTED]> writes:


Hello,

I've just written sqlite3 loadable module which implements REGEXP 
operator with Perl-Compatible Regular Expressions library.  It has

 LRU cache for compiled regular expressions, so it is probably
fast.


$ cflags=`pkg-config --cflags sqlite3 libpcre` $ libs=`pkg-config 
--libs sqlite3 libpcre` $ gcc -shared -o pcre.so -Wall -g -fPIC 
$cflags pcre.c $libs


Note that regular expression flags can be embedded within regular 
expressions themselves, e.g.


sqlite> .load ./pcre.so sqlite> SELECT "asdf" REGEXP "(?i)^A"; 1 
sqlite>


This is native perl syntax, and this is what POSIX regex(7) does 
not allow (which makes them pretty much useless for REGEXP syntax).




I still consider whether to release the code under the GPL or make 
it public domain.  Are there any other sqlite3 extensions out there

 yet?




see code from original post at
http://article.gmane.org/gmane.comp.db.sqlite.general/23584

I came across this note from last fall while looking for information on
how to activate the REGEXP functionalilty. Not only would I like to know
whether this particular solution is correct and appropriate, but I am 
generally curious about why no regexp() function has found its way into 
the SQLite code for general distribution. Is it just that there is no 
demand because people peform any regex filtering in their favorite 
scripting language after obtaining the superset of data from the 
database? Personally I would like to be able to narrow my results down 
as soon as possible so that the working data is defined by a query to 
the extent possible before processing begins. This is my first foray 
into the world of SQLite, so I am largely ignorant of the history and

culture of the project, so any back stories would be engightening. FYI,
I was using SQLite to manage attributes of spatial data in the GRASS GIS
environment while our Postgres server was temporarily unavailable. I am 
glad to have SQLite in my quiver and I see great potential for making 
large data sets more portable, a good compromise between PostGIS and the 
inherent shortcomings of the shapefile format. I look forward to any 
comments on this topic. Alexey, are you still around? Have you made any 
enhancements to your solution?


Neil







-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Regexp

[Eric Bohlman]

T wrote:
I know there are hooks for adding a Regexp function/operator ourselves, 
but I need to know it's available on other machines with standard 
install. It's the same reasoning, I guess, as why Trim() was added, but 
Regexp seems to serve a wider need.


I'm pretty sure the problem is that while there are many open-source 
regexp libraries, none of them are public domain and consequently 
couldn't be added to the standard SQLite distribution without 
compromising its public-domain status.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Regexp

[T]

Hi,

I noticed in recent versions of SQLite the addition of some functions  
such as Trim(). So I am hopeful that at least one more function,  
Regexp(), can be included since it is such a central and common need.  
Time and time again I come up with needing a Regexp function, and I  
notice that the SQLite expression syntax even caters for it, but  
doesn't actually implement it.


I need full PCRE (ie Perl Compatible Regular Expression) support, for  
things such as grouping, replacing, "g i s" options.


For instance, Trim() could have been accomplished via Regexp, such as:

Trim = '   text between spaces   ' Regexp '/[:space:]*(.*?)[:space:]*/s'

I'm not sure how the SQLite syntax should cater for extracting a  
particular group from the match (ie the text match between the  
brackets above).


I know there are hooks for adding a Regexp function/operator  
ourselves, but I need to know it's available on other machines with  
standard install. It's the same reasoning, I guess, as why Trim() was  
added, but Regexp seems to serve a wider need.


Please and thankyou,
Tom

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] REGEXP implementation with pcre

[Alexey Tourbin]

Hello,

I've just written sqlite3 loadable module which implements REGEXP
operator with Perl-Compatible Regular Expressions library.  It has
LRU cache for compiled regular expressions, so it is probably fast.

$ cflags=`pkg-config --cflags sqlite3 libpcre`
$ libs=`pkg-config --libs sqlite3 libpcre`
$ gcc -shared -o pcre.so -Wall -g -fPIC $cflags pcre.c $libs

Note that regular expression flags can be embedded within regular
expressions themselves, e.g.

sqlite> .load ./pcre.so
sqlite> SELECT "asdf" REGEXP "(?i)^A";
1
sqlite> 

This is native perl syntax, and this is what POSIX regex(7) does
not allow (which makes them pretty much useless for REGEXP syntax).

I still consider whether to release the code under the GPL or 
make it public domain.  Are there any other sqlite3 extensions
out there yet?
#include 
#include 
#include 
#include 
#include 
SQLITE_EXTENSION_INIT1

typedef struct {
char *s;
pcre *p;
pcre_extra *e;
} cache_entry;

#define CACHE_SIZE 16

static
void regexp(sqlite3_context *ctx, int argc, sqlite3_value **argv)
{
const char *re, *str;
pcre *p;
pcre_extra *e;

assert(argc == 2);

re = (const char *) sqlite3_value_text(argv[0]);
if (!re) {
sqlite3_result_error(ctx, "no regexp", -1);
return;
}

str = (const char *) sqlite3_value_text(argv[1]);
if (!str) {
sqlite3_result_error(ctx, "no string", -1);
return;
}

/* simple LRU cache */
{
int i;
int found = 0;
cache_entry *cache = sqlite3_user_data(ctx);

assert(cache);

for (i = 0; i < CACHE_SIZE && cache[i].s; i++)
if (strcmp(re, cache[i].s) == 0) {
found = 1;
break;
}
if (found) {
if (i > 0) {
cache_entry c = cache[i];
memmove(cache + 1, cache, i * sizeof(cache_entry));
cache[0] = c;
}
}
else {
cache_entry c;
const char *err;
int pos;
c.p = pcre_compile(re, 0, , , NULL);
if (!c.p) {
char *e2 = sqlite3_mprintf("%s: %s (offset %d)", re, err, pos);
sqlite3_result_error(ctx, e2, -1);
sqlite3_free(e2);
return;
}
c.e = pcre_study(c.p, 0, );
c.s = strdup(re);
if (!c.s) {
sqlite3_result_error(ctx, "strdup: ENOMEM", -1);
pcre_free(c.p);
pcre_free(c.e);
return;
}
i = CACHE_SIZE - 1;
if (cache[i].s) {
free(cache[i].s);
assert(cache[i].p);
pcre_free(cache[i].p);
pcre_free(cache[i].e);
}
memmove(cache + 1, cache, i * sizeof(cache_entry));
cache[0] = c;
}
p = cache[0].p;
e = cache[0].e;
}

{
int rc;
assert(p);
rc = pcre_exec(p, e, str, strlen(str), 0, 0, NULL, 0);
sqlite3_result_int(ctx, rc >= 0);
return;
}
}

int sqlite3_extension_init(sqlite3 *db, char **err, const sqlite3_api_routines 
*api)
{
SQLITE_EXTENSION_INIT2(api)
cache_entry *cache = calloc(CACHE_SIZE, sizeof(cache_entry));
if (!cache) {
*err = "calloc: ENOMEM";
return 1;
}
sqlite3_create_function(db, "REGEXP", 2, SQLITE_UTF8, cache, regexp, 
NULL, NULL);
return 0;
}


pgpJFDCDbJUAg.pgp
Description: PGP signature

RE: [sqlite] REGEXP

[CARTER-HITCHIN, David, FM]

> Hi Ulrich, thanks for the answer but I need to implement a 
> function regexp() for SQLite inside a program in C++.

Look at boost:

http://www.boost.org/libs/regex/doc/introduction.html

Regards,

David Carter-Hitchin.
--
Royal Bank of Scotland
Interest Rate Derivatives IT


***
The Royal Bank of Scotland plc. Registered in Scotland No 90312. Registered 
Office: 36 St Andrew Square, Edinburgh EH2 2YB. 
Authorized and regulated by the Financial Services Authority 
 
This e-mail message is confidential and for use by the 
addressee only. If the message is received by anyone other 
than the addressee, please return the message to the sender 
by replying to it and then delete the message from your 
computer. Internet e-mails are not necessarily secure. The 
Royal Bank of Scotland plc does not accept responsibility for 
changes made to this message after it was sent. 

Whilst all reasonable care has been taken to avoid the 
transmission of viruses, it is the responsibility of the recipient to 
ensure that the onward transmission, opening or use of this 
message and any attachments will not adversely affect its 
systems or data. No responsibility is accepted by The Royal 
Bank of Scotland plc in this regard and the recipient should carry 
out such virus and other checks as it considers appropriate. 
Visit our websites at: 
http://www.rbos.com
http://www.rbsmarkets.com 

Re: [sqlite] REGEXP

[Danilo]

Hi Ulrich, thanks for the answer but I need to implement a function regexp() for 
SQLite inside a program in C++.


Thanks, Danilo.


Ulrich Schöbel ha scritto:

Hi Danilo,

take a look at
http://aspn.activestate.com/ASPN/docs/ActiveTcl/tcl/TclCmd/re_syntax.htm

Kind regards

Ulrich


On Friday 17 March 2006 10:13, Danilo wrote:

Hi to All ,
I'm looking for further information about REGEXP
(http://www.sqlite.org/lang_expr.html).
Someone knows how to point out me some examples!

Thanks, Danilo.
Home Page: http://www.digitazero.org
venerdì 17 marzo 2006, 9.07

Re: [sqlite] REGEXP

[Ulrich Schöbel]

Hi Danilo,

take a look at
http://aspn.activestate.com/ASPN/docs/ActiveTcl/tcl/TclCmd/re_syntax.htm

Kind regards

Ulrich


On Friday 17 March 2006 10:13, Danilo wrote:
> Hi to All ,
> I'm looking for further information about REGEXP
> (http://www.sqlite.org/lang_expr.html).
> Someone knows how to point out me some examples!
>
> Thanks, Danilo.
> Home Page: http://www.digitazero.org
> venerdì 17 marzo 2006, 9.07

[sqlite] REGEXP

[Danilo]

Hi to All ,
I'm looking for further information about REGEXP 
(http://www.sqlite.org/lang_expr.html).

Someone knows how to point out me some examples!

Thanks, Danilo.
Home Page: http://www.digitazero.org
venerdì 17 marzo 2006, 9.07

RE: [sqlite] REGEXP as an operator

[John Buck]

Yes. I needed it to work as an operator in the form: FIELD REGEXP ".*",  not
regexp(FIELD,".*")
  

-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 10, 2005 10:32 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] REGEXP as an operator

On 5/10/05, John Buck <[EMAIL PROTECTED]> wrote:
> Greetings,
> 
> I am new to this list and the Sqlight project.
> 
> I ran across it while doing research on locating an alternative to the
Mysql
> Embedded engine.  Sqlite seems like it will do the trick with a few
tweaks.
> I just wanted to share one of those tweaks for comment.
> 
> One problem I ran into was I had used REGEXP as an operator throughout
> hundreds of lines of SQL.
> 
> So with a little work I added REGEXP as an operator to Sqlite 3.21.
> You can download the change for a limited time from
> municipal-software.com/Sqlite3.21REGEXP.zip

I guess you added it to the code directly and didn't create your own
function?

Re: [sqlite] REGEXP as an operator

[Jay Sprenkle]

On 5/10/05, John Buck <[EMAIL PROTECTED]> wrote:
> Greetings,
> 
> I am new to this list and the Sqlight project.
> 
> I ran across it while doing research on locating an alternative to the Mysql
> Embedded engine.  Sqlite seems like it will do the trick with a few tweaks.
> I just wanted to share one of those tweaks for comment.
> 
> One problem I ran into was I had used REGEXP as an operator throughout
> hundreds of lines of SQL.
> 
> So with a little work I added REGEXP as an operator to Sqlite 3.21.
> You can download the change for a limited time from
> municipal-software.com/Sqlite3.21REGEXP.zip

I guess you added it to the code directly and didn't create your own function?

[sqlite] REGEXP as an operator

[John Buck]

Greetings,

I am new to this list and the Sqlight project.  

I ran across it while doing research on locating an alternative to the Mysql
Embedded engine.  Sqlite seems like it will do the trick with a few tweaks.
I just wanted to share one of those tweaks for comment.


One problem I ran into was I had used REGEXP as an operator throughout
hundreds of lines of SQL.

So with a little work I added REGEXP as an operator to Sqlite 3.21.
You can download the change for a limited time from
municipal-software.com/Sqlite3.21REGEXP.zip


I use this with both windows (Visual Studio) and linux (GCC) compiler.

It makes use of the boost regular expression library available at
www.boost.org.

If you do not want to use the Boost implementation, you can create your own
in the file Extensions.cpp. 
 (just replace the call to the boost library with what-ever you want to use)

To enable the REGEXP operator,
Replace the files in the sqlite folder with those contained within the zip
file.
 
Compile with SQLITE_BOOST_REGEXP defined. (Depending on your environment you
might need to first compile Extensions.cpp and link with the resulting
object)   

If you compile without SQLITE_BOOST_REGEXP defined, regexp will be a synonym
for glob, and the boost library is not necessary.


Why do it this way and not simply use sqlite3_create_function?

In Mysql, REGEXP is an operator, and I was looking to make a drop in
replacement to an existing project with hundreds of lines of SQL already
written where REGEXP is used as an operator.  (IE:   Field REGEXP '.*10$'
). 

The changes are simple enough that maybe they can be added to the next
release. 

--
John Buck
[EMAIL PROTECTED]