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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

REGEXP operator for SQLite3

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

Offline

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

ebz
Member
Registered: 2011-07-20
Posts: 18

Re: REGEXP operator for SQLite3

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

Offline

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

chapa
Member
Registered: 2012-04-30
Posts: 117

Re: REGEXP operator for SQLite3

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: REGEXP operator for SQLite3

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

Offline

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

chapa
Member
Registered: 2012-04-30
Posts: 117

Re: REGEXP operator for SQLite3

Thanks smile

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: REGEXP operator for SQLite3

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: REGEXP operator for SQLite3

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...

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: REGEXP operator for SQLite3

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: REGEXP operator for SQLite3

Good news.
smile

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: REGEXP operator for SQLite3

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.

Offline

Board footer

Powered by FluxBB