Defining SQLite functions at runtime

SQLite database engine supports much of the SQL syntax, but the shell program (sqlite3.exe in Windows) doesn't provide statements to define SQL functions interactively. Users would like to

  • define a function F(a,b)=(a+b)/2:
    SELECT DEFINE_FUNCTION('F', 2, 'SELECT (?1+?2)/2');
  • use F(...) in the next sql statements:
    SELECT Q1, Q2, F(Q1,Q2) FROM STUDENTS;

 and this can be done using the sqlfun extension available here.

Downloads

  • sqlfun10.zip: sqlite3ext_sqlfun extension in Win32 DLL format, SQLite executable 3.6.23.1 (taken from SQLite website), source code, SQL test example, makefile for MSYS+MinGW

How to use the DLL extension

  • if you already have SQLite installed, you only need the file sqlite3ext_sqlfun.dll included in the package
  • if you don't have SQLite installed, you can use the sqlite3.exe file provided, or download the latest version of the shell executable for Win32 (usually called sqlite.exe or sqlite3.exe) from the SQLite website.
  • start your sqlite shell, it will show version and a command prompt (note for MSYS users: if prompt isn't shown, try starting sqlite3.exe with the -interactive option)
  • now type:
    .load ./sqlite3ext_sqlfun.dll
  • if you get an error message, the most probable cause is that SQLite cannot locate the DLL file. The above command worked when putting sqlite3.exe and sqlite3ext_sqlfun.dll in the same directory, then running sqlite3.exe from that same directory. If your setup is different, you might need to specify a different path to the DLL file
  • if no errors were reported, we can now try to define a function f (it's case-insensitive), having 2 arguments ?1 and ?2:
    SELECT define_function('F',2,'SELECT (?1+?2)/2');
  • the function can now be used in the next statements:
    SELECT F(1,2);
    SELECT X,Y,F(X,Y) FROM a_table_with_x_and_y_columns;
  • you can now create all the queries and views using f
  • IMPORTANT: please note that function definitions are not permanently stored in the database, so you have to call the define_function statements at every new connection, before calling your user-defined functions. You may choose to organize things in better ways: for example:
    CREATE TABLE FUNCTION_TAB(
      name VARCHAR,
      args INTEGER,
      sqltext VARCHAR,
      CONSTRAINT pk_function_tab PRIMARY KEY (name, args)
    );

    and then store your function definitions there. When opening a connection you may now .load the extension and then  execute:
    SELECT define_function(name,args,sqltext) FROM FUNCTION_TAB;

How to compile sources in Windows

The following steps were performed under Windows XP using the MSYS+MinGW environment, so Visual Studio developers are on their own here: they will have to create their own project to compile (inspection of the Makefile helps in this case).

Download the extension package and uncompress it. From the MSYS console, go to the sqlfun directory and perform the following steps ("$" is the command prompt; you'll have to run the commands highlighted in bold).

$ dir
total 404
-rw-r--r--    1 andrea   Administ      345 Apr 11 12:42 Makefile
-rwxr-xr-x    1 andrea   Administ   528522 Mar 29 14:02 sqlite3.exe
-rw-r--r--    1 andrea   Administ   271229 Apr 10 23:57 sqlite3.h
-rw-r--r--    1 andrea   Administ    20686 Apr 10 23:57 sqlite3ext.h
-rw-r--r--    1 andrea   Administ     2629 Apr 11 00:57 sqlite3ext_sqlfun.c
-rw-r--r--    1 andrea   Administ      686 Apr 11 12:45 test.sql

$ make
gcc -I. -shared -osqlite3ext_sqlfun.dll sqlite3ext_sqlfun.c

$ make test
./sqlite3.exe prova.sqlite ".read test.sql"
define_function('f',2,'select (?1+?2)/2')
-----------------------------------------
1
a           b           f_should_be  f_calculated_is  error
----------  ----------  -----------  ---------------  ----------
2.0         2.0         2.0          2.0              0.0
1.0         2.0         1.5          1.5              0.0
-1.0        1.0         0.0          0.0              0.0
1.0         2.2         1.6          1.6              0.0