mORMot Open Source

proto

Member

From: Russia, Kostroma

Registered: 2011-09-12

Posts: 31

how to add a function charindex?

http://sqlite.org/contrib

extension-functions.c (50.96 KB) contributed by Liam Healy on 2010-02-06 15:45:07

Provide mathematical and string extension functions for SQL queries using the loadable extensions mechanism. Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: how to add a function charindex?

See the documentation - from http://synopse.info/fossil/wiki?name=Downloads - about custom SQL functions.

The SQL functions classes are explained at page 129 and later (in the today's revision).

You create your own class, then it will be added to the instance.

I can easily admit this is not an easy task, so I'll update the documentation, and provide code here.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: how to add a function charindex?

Here is an extract from the updated documentation:

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 implementing 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 24;
- 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, as stated in 21.

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):)

Implementing a function

Let us implement a CharIndex() SQL function, defined as such:

CharIndex ( SubText, Text [ , StartPos ] )

In here, SubText is the string of characters to look for in Text. StartPos indicates the starting index where charindex() should start looking for SubText in Text. Function shall return the position where the match occured, 0 when no match occurs. Characters are counted from 1, just like in PosEx() Delphi function.

The SQL function implementation pattern itself is explained in the sqlite3_create_function_v2() and TSQLFunctionFunc:
- argc is the number of supplied parameters, which are available in argv[] array (you can call ErrorWrongNumberOfArgs(Context) in case of unexpected incoming number of parameters);
- Use sqlite3_value_*(argv[]) functions to retrieve a parameter value;
- Then set the result value using sqlite3_result_*(Context,*) functions.

Here is a typical implementation code of the CharIndex() SQL function, calling the expected low-level SQLite3 API (note the cdecl calling convention, since it is a SQLite3 / C callback function):

procedure InternalSQLFunctionCharIndex(Context: TSQLite3FunctionContext;
  argc: integer; var argv: TSQLite3ValueArray); cdecl;
var StartPos: integer;
begin
  case argc of
  2: StartPos := 1;
  3: begin
    StartPos := sqlite3_value_int64(argv[2]);
    if StartPos<=0 then
      StartPos := 1;
  end;
  else begin
    ErrorWrongNumberOfArgs(Context);
    exit;
  end;
  end;
  if (sqlite3_value_type(argv[0])=SQLITE_NULL) or
     (sqlite3_value_type(argv[1])=SQLITE_NULL) then
    sqlite3_result_int64(Context,0) else
    sqlite3_result_int64(Context,SynCommons.PosEx(
      sqlite3_value_text(argv[0]),sqlite3_value_text(argv[1]),StartPos));
end;

This code just get the parameters values using sqlite3_value_*() functions, then call the PosEx() function to return the position of the supplied text, as an INTEGER, using sqlite3_result_int64().

The local StartPos variable is used to check for an optional third parameter to the SQL function, to specify the character index to start searching from.

The special case of a NULL parameter is handled by checking the incoming argument type, calling sqlite3_value_type(argv[]).

Registering a function

Direct low-level SQLite3 registration

Since we have a InternalSQLFunctionCharIndex() function defined, we may register it with direct SQLite3 API calls, as such:

sqlite3_create_function_v2(Demo.DB,
    'CharIndex',2,SQLITE_ANY,nil,InternalSQLFunctionCharIndex,nil,nil,nil);
  sqlite3_create_function_v2(Demo.DB,
    'CharIndex',3,SQLITE_ANY,nil,InternalSQLFunctionCharIndex,nil,nil,nil);

The function is registered twice, one time with 2 parameters, then with 3 parameters, to add an overloaded version with the optional StartPos parameter.

Class-driven registration

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.

So instead of calling low-level sqlite3_create_function_v2() API, you can declare the CharIndex SQL function as such:

Demo.RegisterSQLFunction(InternalSQLFunctionCharIndex,2,'CharIndex');
  Demo.RegisterSQLFunction(InternalSQLFunctionCharIndex,3,'CharIndex');

The two lines above will indeed wrap the following code:

Demo.RegisterSQLFunction(TSQLDataBaseSQLFunction.Create(InternalSQLFunctionCharIndex,2,'CharIndex'));
  Demo.RegisterSQLFunction(TSQLDataBaseSQLFunction.Create(InternalSQLFunctionCharIndex,3,'CharIndex'));

The RegisterSQLFunction() method is called twice, one time with 2 parameters, then with 3 parameters, to add an overloaded version with the optional StartPos parameter, as expected.

See http://synopse.info/fossil/info/1d37480166 for the associated code change (especially new TSQLDataBase.RegisterSQLFunction() overloaded methods).