C++ SQLite Yet Another Api (YAPI) Part 2

In C+ SQLite Yet Another Api (YAPI) Part 2, we will be explaining how is User Defined Functions, SQLite mechanism wrapped up.

Part one is here.

CREATE FUNCTION does not work in SQLite.  In SQLite user-defined function is taken in a very literal sense.  It is not SQL. It is defined by a user in her programming language of the API in use, to talk to SQLite code which in turn manages the data file in the back.

Here is the proverbial picture that speaks more than thousand words. An UML sequence diagram.

That is higher level than code. No actual names from the code are used. The purpose is to explain the concept.

Thus if you find yourself programming in standard C++, your UDF will be a standard C++ function. It will be called when and if mentioned in SQL statements.  It will have to be written following the set of rules available here.

Now, let us see how this actually feels and works in an real life example.

Use Case: palindrome

Requirement: given English dictionary in a single column table, select only those words which are palindromes.

First, we will take the English “words in a file”, from this GitHub project.

Second, we shall create an SQLite database with one table in it that has one column. Let us call this table,words and let us call a single column in that table: word.

With one simple SQL statement, we might accomplish this task.

The only little problem is we have not, UDF functionality in SQLite SQL dialect, that will allow us to write a function,palindrome as we could do in let say T-SQL.

This is where we use SQLite user-defined functions concept to write a C++ function that will be “called back” from SQLite machinery when this function is required.

And, of course, we shall use our little dbj++sql YAPI to help us out. First the palindrome function as bog standard C++.

Now the actual function that will be regitered as SQLite callback to be used whenever needed from inside any SQL statement. Like for example SELECT word FROM words WHERE (1 == palindrome(word)).

The signature of this function is prescribed by SQLite API.

Phew. That was not trivial. Fortunately SQLIte documentation is prety god and of course you have the dbj++sql API to ease the burden of usage. Let’s see it.

Before this last test unit do not forget to provide canonical shape of the result callback we mentioned in the code.

As anything done for the first time this is admittedly a bit intimidating. There are ways and techniques to make this easier for a casual user. Stay tuned for the next release of the DBJ++SQLite YAPI (Yet Another API).

There are also a few very good SQLite Windows desktop management applications. And few of them do provide facility to create UDF’s in scripting languages, javascript for example, straight from a GUI front end.

I have found SQLiteStudio very usable, with the lively ecosystem of users, and very active developers aka contributors.

SqliteStudio in use
SqliteStudio , dialogue for creating UDF’s in JavaScript dialect.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.