mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Custom SQL functions

The SQLite3 engine defines some standard SQL functions, like abs() min() max() or upper(). A complete list is available at http://www.sqlite.org/lang_corefunc.html

One of the greatest SQLite3 feature is the ability to define custom SQL functions in high-level language. In fact, its C API allows to implement new functions which may be called within a SQL query. In other database engine, such functions are usually named UDF (for User Defined Functions).

Some custom already defined SQL functions are defined by the framework. You may have to use, on the Server-side:
- Rank used for page ranking in FTS searches;
- Concat to process fast string concatenation;
- Soundex SoundexFR SoundexES for computing the English / French / Spanish soundex value of any text;
- IntegerDynArrayContains, ByteDynArrayContains, WordDynArrayContains, CardinalDynArrayContains, Int64DynArrayContains, CurrencyDynArrayContains, RawUTF8DynArrayContainsCase, RawDynArrayContainsNoCase, for direct search inside a BLOB column containing some dynamic array binary content (expecting either an INTEGER either a TEXT search value as 2nd parameter).

Those functions are no part of the SQlite3 engine, but are available inside our ORM to handle BLOB containing dynamic array properties.

Since you may use such SQL functions in an UPDATE or INSERT SQL statement, you may have an easy way of implementing server-side process of complex data, as such:

UPDATE MyTable SET SomeField=0 WHERE IntegerDynArrayContains(IntArrayField,:(10):)

It's possible to add some custom SQL functions to the SQlite3 engine itself, by creating a TSQLDataBaseSQLFunction custom class and calling the TSQLDataBase. RegisterSQLFunction method.

The standard way of using this is to override the TSQLRestServerDB. InitializeEngine virtual method, calling DB.RegisterSQLFunction() with an defined TSQLDataBaseSQLFunction custom class.

For instance, the following method will register a SQL function able to search into a BLOB-stored custom dynamic array type:

procedure TSQLDataBase.RegisterSQLFunction(aDynArrayTypeInfo: pointer;
  aCompare: TDynArraySortCompare; const aFunctionName: RawUTF8);
begin
  RegisterSQLFunction(
    TSQLDataBaseSQLFunctionDynArray.Create(aDynArrayTypeInfo,aCompare,aFunctionName));
end;

Here is the corresponding class definition:

/// to be used to define custom SQL functions for dynamic arrays BLOB search
  TSQLDataBaseSQLFunctionDynArray = class(TSQLDataBaseSQLFunction)
  protected
    fDummyDynArray: TDynArray;
    fDummyDynArrayValue: pointer;
  public
    /// initialize the corresponding SQL function
    // - if the function name is not specified, it will be retrieved from the type
    // information (e.g. TReferenceDynArray will use 'ReferenceDynArrayContains')
    // - the SQL function will expect two parameters: the first is the BLOB
    // field content, and the 2nd is the array element to search (set with
    // TDynArray.ElemSave() or with BinToBase64WithMagic(aDynArray.ElemSave())
    // if called via a Client and a JSON prepared parameter)
    // - you should better use the already existing faster SQL functions
    // Byte/Word/Integer/Cardinal/Int64/CurrencyDynArrayContains() if possible
    // (this implementation will allocate each dynamic array into memory before
    // comparison, and will be therefore slower than those optimized versions)
    constructor Create(aTypeInfo: pointer; aCompare: TDynArraySortCompare;
      const aFunctionName: RawUTF8='');
  end;

And the constructor implementation:

constructor TSQLDataBaseSQLFunctionDynArray.Create(aTypeInfo: pointer;
  aCompare: TDynArraySortCompare; const aFunctionName: RawUTF8);
begin
  fDummyDynArray.Init(aTypeInfo,fDummyDynArrayValue);
  fDummyDynArray.Compare := aCompare;
  if aFunctionName='' then
    fSQLName := RawUTF8(copy(ClassName,2,maxInt)) else
    fSQLName := aFunctionName;
  fInternalFunction := InternalSQLFunctionDynArrayBlob;
  fFunctionParametersCount := 2;
end;

The InternalSQLFunctionDynArrayBlob function is a low-level SQlite3 engine SQL function prototype, which will retrieve a BLOB content, then un-serialize it into a dynamic array (using the fDummyDynArrayValue. LoadFrom method), then call the standard ElemLoadFind method to search the supplied element.

with Func.fDummyDynArray do
  try
    LoadFrom(DynArray); // temporary allocate all dynamic array content
    try
      if ElemLoadFind(Elem)<0 then
        DynArray := nil;
    finally
      Clear; // release temporary array content
    end;

You can define a similar class in order to implement your own custom SQL function.

Here is how a custom SQL function using this TSQLDataBaseSQLFunctionDynArray class is registered in the supplied unitary tests to an existing database connection:

Demo.RegisterSQLFunction(TypeInfo(TIntegerDynArray),SortDynArrayInteger,
    'MyIntegerDynArrayContains');

This new SQL function expects two BLOBs arguments, the first being a reference to the BLOB column, and the 2nd the searched value. The function can be called as such (lines extracted from the framework regression tests):

aClient.OneFieldValues(TSQLRecordPeopleArray,'ID',
      FormatUTF8('MyIntegerDynArrayContains(Ints,:("%"):)',
        [BinToBase64WithMagic(@k,sizeof(k))]),IDs);

Note that since the 2nd parameter is expected to be a BLOB representation of the searched value, the BinToBase64WithMagic function is used to create a BLOB parameter, as expected by the ORM. Here, the element type is an integer, which is a pure binary variable (containing no reference-counted internal fields): so we use direct mapping from its binary in-memory representation; for more complex element type, you should use the generic BinToBase64WithMagic(aDynArray.ElemSave()) expression instead, calling TDynArray. ElemSave method.

Since the MyIntegerDynArrayContains function will create a temporary dynamic array in memory from each row, the dedicated IntegerDynArrayContains SQL function is faster.