SynSQLite3RegEx.pas

/// REGEXP function for SQLite3 Database using PCRE library
// - this unit is a part of the freeware Synopse mORMot framework,
// licensed under a MPL/GPL/LGPL tri-license; version 1.18
unit SynSQLite3RegEx;

{
    This file is part of Synopse mORMot framework.

    Synopse mORMot framework. Copyright (C) 2018 Arnaud Bouchez
      Synopse Informatique - https://synopse.info

  *** BEGIN LICENSE BLOCK *****
  Version: MPL 1.1/GPL 2.0/LGPL 2.1

  The contents of this file are subject to the Mozilla Public License Version
  1.1 (the "License"); you may not use this file except in compliance with
  the License. You may obtain a copy of the License at
  http://www.mozilla.org/MPL

  Software distributed under the License is distributed on an "AS IS" basis,
  WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
  for the specific language governing rights and limitations under the License.

  The Original Code is Synopse mORMot framework.

  The Initial Developer of the Original Code is Arnaud Bouchez.

  Portions created by the Initial Developer are Copyright (C) 2018
  the Initial Developer. All Rights Reserved.

  Contributor(s):
  Alternatively, the contents of this file may be used under the terms of
  either the GNU General Public License Version 2 or later (the "GPL"), or
  the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
  in which case the provisions of the GPL or the LGPL are applicable instead
  of those above. If you wish to allow use of your version of this file only
  under the terms of either the GPL or the LGPL, and not to allow others to
  use your version of this file under the terms of the MPL, indicate your
  decision by deleting the provisions above and replace them with the notice
  and other provisions required by the GPL or the LGPL. If you do not delete
  the provisions above, a recipient may use your version of this file under
  the terms of any one of the MPL, the GPL or the LGPL.

  ***** END LICENSE BLOCK *****



    REGEXP function for SQLite3 Database engine
   *********************************************

  This unit allows to enable a REGEXP operator to be used in SQlite3 statements.

  It will use the PCRE library as available since Delphi XE, or will rely
  on http://www.regular-expressions.info/download/TPerlRegEx.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.


  Version 1.18
  - initial revision, implementing feature request [659d1df866]

}

{$I Synopse.inc} // define HASINLINE USETYPEINFO CPU32 CPU64 OWNNORMTOUPPER

interface

uses
{$ifdef ISDELPHIXE}
  // use direct PCRE library as available since Delphi XE
  RegularExpressionsAPI,
{$else}
  // download from http://www.regular-expressions.info/download/TPerlRegEx.zip
  PCRE,
{$endif}
  SysUtils,
  SynCommons,
  SynSQLite3;

/// register the REGEXP SQL function to a given SQLite3 engine instance
// - allow execution of statements as such:
// ! SELECT column FROM table WHERE column REGEXP '<here goes your expression>';
function CreateRegExpFunction(DB: TSQLite3DB): boolean;


implementation

type
  TPCREcache = record
    source: RawUTF8;
    compiled: PPCRE;
    extra: PPCREExtra;
  end;
  TPCREcaches = array of TPCREcache;
  PPCREcaches = ^TPCREcaches;

const
  // small regex compilation cache is enough in practice
  MAX_PCRECACHE = 16;


procedure InternalRegExp(Context: TSQLite3FunctionContext;
  argc: integer; var argv: TSQLite3ValueArray); cdecl;
var regexp, text: PUTF8Char;
    errMsg: PAnsiChar;
    errPos: integer;
    found, result: boolean;
    i, n: integer;
    reg: PPCRE;
    cache: PPCREcaches;
    c: array[1..sizeof(TPCREcache)] of byte; // bulk mem block to avoid ref count
procedure CompileError;
begin // sub procedure to avoid temp RawUTF8
  sqlite3.result_error(Context,pointer(FormatUTF8(
    'REGEXP "%": % at pos %',[regexp,errMsg,errPos])));
end;
begin
  if argc<>2 then begin
    ErrorWrongNumberOfArgs(Context);
    Exit;
  end;
  cache := sqlite3.user_data(Context);
  regexp := sqlite3.value_text(argv[0]);
  if regexp=nil then begin
    sqlite3.result_error(Context,'REGEXP with no expression');
    exit;
  end;
  text := sqlite3.value_text(argv[1]);
  if text=nil then begin
    sqlite3.result_error(Context,'REGEXP with no text');
    exit;
  end;
  found := false;
  for i := 0 to high(cache^) do
    if StrComp(regexp,pointer(cache^[i].source))=0 then begin
      found := true;
      if i>0 then begin // always put last found item at first place
        move(cache^[i],c,sizeof(c));
        move(cache^[0],cache^[1],i*sizeof(c));
        move(c,cache^[0],sizeof(c));
      end;
      break;
    end;
  if not found then begin
    reg := pcre_compile(pointer(regexp),0,@errMsg,@errPos,nil);
    if reg=nil then begin
      CompileError;
      exit;
    end;
    n := length(cache^);
    if n=MAX_PCRECACHE then
      with cache^[MAX_PCRECACHE-1] do begin
        source := ''; // avoid memory leak
        pcre_dispose(compiled,extra,nil);
      end else
      SetLength(cache^,n+1);
    move(cache^[0],cache^[1],n*sizeof(c)); // new item is at first place
    with cache^[0] do begin
      pointer(source) := nil; // avoid GPF
      source := regexp;
      compiled := reg;
      extra := pcre_study(compiled,0,@errMsg);
    end;
  end;
  with cache^[0] do
    result := pcre_exec(compiled,extra,pointer(text),StrLen(text),0,PCRE_NO_UTF8_CHECK,nil,0)>=0;
    // (faster with PCRE_NO_UTF8_CHECK option)
  sqlite3.result_int64(Context,ord(result));
end;

procedure InternalRegExpDestroy(cache: PPCREcaches); cdecl;
var i: integer;
begin
  if cache<>nil then begin
    for i := 0 to high(cache^) do
      pcre_dispose(cache^[i].compiled,cache^[i].extra,nil);
    Dispose(cache);
  end;
end;

function CreateRegExpFunction(DB: TSQLite3DB): boolean;
var cache: PPCREcaches;
begin
  if Assigned(sqlite3) and (DB<>0) then begin
    New(cache);
    result := sqlite3.create_function_v2(DB,'REGEXP',2,SQLITE_UTF8,cache,
      InternalRegExp,nil,nil,@InternalRegExpDestroy)=SQLITE_OK;
    if not result then
      Dispose(cache);
   end else
    result := false;
end;



end.