REGEXP operator for SQLite3

ab · 2013-05-24 14:17:46

Our SQLite3 engine can now use regular expression within its SQL queries, by enabling the REGEXP operator in addition to standard SQL operators (= == != <> IS IN LIKE GLOB MATCH). It will use the Open Source PCRE library to perform the queries.

In order to enable the operator, you should include unit SynSQLite3RegEx.pas to your uses clause, and register the RegExp() SQL function to a given SQLite3 database instance, as such:

uses SynCommons, mORmot, mORMotSQLite3,
SynSQLite3RegEx;
...
Server := TSQLRestServerDB.Create(Model,'test.db3');
try
CreateRegExFunction(Server.DB.DB);
with TSQLRecordPeople.CreateAndFillPrepare(Client,
'FirstName REGEXP ?',['\bFinley\b']) do
try
while FillOne do begin
Check(LastName='Morse');
Check(IdemPChar(pointer(FirstName),'SAMUEL FINLEY '));
end;
finally
Free;
end;
finally
Server.Free;
end;

The above code will execute the following SQL statement (with a prepared parameter for the regular expression itself):

SELECT * from People WHERE Firstname REGEXP '\bFinley\b';

That is, it will find all objects where TSQLRecordPeople.FirstName will contain the 'Finley' word - /b in regular expression defines a word boundary search.

In fact, 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. Calling CreateRegExFunction() for a given connection will add a SQL function named "regexp()" at run-time, which will be called in order to implement the REGEXP operator.

It will use the statically linked PCRE library as available since Delphi XE, or will rely on the PCRE.pas wrapper unit as published at http://www.regular-expressions.info/dow … lRegEx.zip for older versions of Delphi.

This unit will call directly the UTF-8 API of the PCRE library, and maintain a per-connection cache of compiled regular expressions to ensure the best performance possible.

Comments for blog article http://blog.synopse.info/post/2013/05/2 … or-SQLite3

ebz · 2013-05-24 14:28:21

Thank you ab, this is very useful function
Before this, I just can find REGEXP function in commercial components (application)

chapa · 2013-06-12 12:19:39

It will be good to add RegExp functionality into SynDBExplorer in case of SQLite3 connection

ab · 2013-06-12 13:14:29

Good idea.

SynDBExplorer will now initialize the REGEXP function for SQLite3 (for Delphi XE and up only, since it requires the library).
See http://synopse.info/fossil/info/cd5a39b74a

chapa · 2013-06-12 13:17:43

Thanks

mpv · 2013-07-19 09:36:06

Little question: is PCRE thread safe?
I mean if I compile regexp (Compiled) in main thread and when use it from concurrent threads in this way:
function TStaticRule.Mutch(const Location: RawUTF8): boolean;
begin
Result := (Compiled <> nil) and (pcre_exec(Compiled, extra, pointer(Location), Length(Location), 0, PCRE_NO_UTF8_CHECK, nil, 0)>=0);
end;
Or I must compile it for every thread separately?

ab · 2013-07-19 09:38:32

I do not know if PCRE is thread safe.
I suppose it is.

Otherwise, we may need to add either a per-thread cache of compiled statements, or protect the execution with a critical section.
We need further testing...

mpv · 2013-07-19 09:46:28

UPS. I found this in documentation:

The compiled form of a regular expression is not altered during match-
ing, so the same compiled pattern can safely be used by several threads
at once.
If the just-in-time optimization feature is being used, it needs sepa-
rate memory stack areas for each thread. See the pcrejit documentation
for more details.

So if we call pcre_study without PCRE_STUDY_JIT_COMPILE flag (as in current version) - YES - it thread-safe

ab · 2013-07-19 14:12:24

Good news.

ab · 2014-03-16 19:54:04

Reference about the missing PCRE_NO_UTF8_CHECK option in official Delphi wrapping class is http://qc.embarcadero.com/wc/qcmain.aspx?d=108941

Our version define PCRE_NO_UTF8_CHECK, will retrieve directly UTF-8 content from the SQLite3 engine (with no further conversion nor temporary string allocation) and use a simple but efficient caching mechanism.
BTW our version will be Unicode-ready, even with pre-Delphi 2009 versions of the compiler, since we use directly the UTF-8 buffers, and no conversion to string.

mORMot Open Source

#1 2013-05-24 14:17:46

REGEXP operator for SQLite3

#2 2013-05-24 14:28:21

Re: REGEXP operator for SQLite3

#3 2013-06-12 12:19:39

Re: REGEXP operator for SQLite3

#4 2013-06-12 13:14:29

Re: REGEXP operator for SQLite3

#5 2013-06-12 13:17:43

Re: REGEXP operator for SQLite3

#6 2013-07-19 09:36:06

Re: REGEXP operator for SQLite3

#7 2013-07-19 09:38:32

Re: REGEXP operator for SQLite3

#8 2013-07-19 09:46:28

Re: REGEXP operator for SQLite3

#9 2013-07-19 14:12:24

Re: REGEXP operator for SQLite3

#10 2014-03-16 19:54:04

Re: REGEXP operator for SQLite3

Board footer