Framework Custom 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, which can be serialized as any record type.
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):)
Your own SQL functions
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.
Feedback and comments are welcome in our forum.