Custom functions for sqlite

I make my programs with delphi 7 personal version that I got free of charge as soon as it was released in 2002 and still runs very well under Windows 7 (both 7 have nothing to do!). This personal version is missing the database functions. So I acquired a Pro license to use disqlite3 – one of the delphi implementations of the sqlite database manager – made by a German named Ralph Junker who created the site The Delphi Inspiration. Although the license is quite expensive, it is an investment that I do not regret, because it allowed me to develop many things that I could not do without. And in the end this add-on is much more powerful, flexible, lightweight than the heavy and rigid components that had been provided at the time with the professional version delphi 7.

Recently, I needed to use an advanced sqlite feature: create my own SQL function. That means creating a custom function that can be used in an SQL string like the core functions included in the program.

For example, if you have a table called myTable, and a text field (in UTF16) called FirstName, you can write a SQL query of the type

SELECT * FROM myTable WHERE upper(FirstName) LIKE 'JUL%'

to select not only records whose name is  JULES, JULIETTE, JULIEN, JULIE etc .., but also those that were written in lowercase or with random case, such as jules, Juliette, JuLes etc … In this example the function upper is a built-in function in sqlite that compares the string JUL% to the uppercase-converted FirstName, which makes J equal to j for the comparison by the LIKE operator.

My issue was that this upper function is very basic and works only for ASCII characters. For accented characters, it does not work: for example if you want the ‘HERVÉ’ Firstname to be equivalent to ‘Hervé’. Thus in myTable there are records whose Firstame is ‘Hervé’, but the following code gives no result:

SELECT * FROM myTable WHERE upper(FirstName) = 'HERVÉ'

On the other hand, the following code gives the recordings with ‘Hervé’ first names, but not those who have a HERVÉ firstname:

SELECT * FROM myTable WHERE upper(FirstName) = 'HERVé'.

This is due to fact that the upper function modifies only the 26 letters of the alphabet that are not accented, and does not put accented characters in upper case. Actually there is a function wideuppercase in delphi (in the unit sysutils.pas) which passes very well not only all the characters accentuated in uppercase, but also the cedillas or œ. It would be very interesting to have something equivalent in a SQL query, hence the idea of ​​making my own function, which will be called wideupper for obvious reasons.

The problem is that the documentation for doing this is very poor. The one provided by Ralph Junker merely copies the general sqlite documentation. The only mention that I found there is the following:

An application may define additional functions written in C and added to the database engine using the sqlite3_create_function () API.

It’s pretty limited, and the study of the function sqlite3_create_function does not give much more clear ideas because it is totally in a C programming context. So I desperately looked for some examples on the Internet that would put me on the path but I did not find anything useful. After a lot of trial and error, I finally arrived at a result. Therefore, to help all those who would be in the same case as me, I publish here this example that can inspire programmers under delphi who use sqlite.

The first thing is to declare in your program the desired function, respecting the conditions that will allow sqlite to take advantage of it. This function is here:

procedure wideupper (ctx: Pointer; n: integer; args: sqlite3_value_ptr_array_ptr);
var z: wideString;
begin
   z: = sqlite3_value_str16(args [0]);
   z: = wideuppercase(z);
   sqlite3_result_str16(ctx, z); // result of the function
end;

Admit it’s impossible to invent that alone, and finding it from what you read on the Internet was hardly easier. So this is a procedure in delphi that takes as arguments a pointer, an integer and a weird array of pointers which is fortunately defined in the unit disqlite3api. We can lose interest in these arguments, which will be transparent to us, at least in this example.

The procedure itself begins by retrieving a variable z as widestring which will be formed precisely from the 1st argument of index 0 of this array, args [0]. This will be the input string of our function, for example ‘Hervé’ to use the example above. We will then naturally apply the aforementioned wideuppercase delphi function, which correctly puts accented characters in uppercase letters.

Then, the resulting widestring variable, containig ‘HERVÉ’ in our example, will be given to sqlite3_result_str16, which will allow SQL to retrieve this result. You ultimately have the prototype of all custom functions on widestring variables, which you can add to SQLITE in delphi. Just keep the first and the last line, and replace the middle line with any code that takes an input variable widestring z, and output the modified variable z as a widestring.

The second thing to do is notify your database that it can use this feature. The code I am using is:

nerr: = sqlite3_create_function16(pBase, 'wideupper', 1, SQLITE_UTF16 OR SQLITE_DETERMINISTIC, nil, @wideupper, nil, nil);

This code should be called after initializing your database, for example just after you get the pointer pBase that designates it in a statement like:

nerr: = sqlite3_open16 (pwidechar(wFile), @pbase);

I use here the integer variable nerr to retrieve the error code from the instruction. If everything went well it must be sqlite_Ok (0). And if everything went well, you can now use SQL queries like

SELECT * FROM myTable WHERE wideupper(FirstName) = 'HERVÉ'

which will give the desired result, considering that’ Hervé ‘and’ HERVÉ ‘are equivalent This was my little contribution to SQLITE programming under delphi.