[PYTHON] In SQLite3, add just two lines and use the extension library (extended SQL function)!

Introduction

In SQLite3, it is possible to introduce a shared library as an extension library, similar to PostgreSQL's EXTENSION (extension). In fact, you can use the extension library by adding only two lines, ** enable_load_extension and load_extension! ** As an example, let's introduce and use the extension SQL functions (extension-functions.c) published in SQLite Contributed Files. SQLite3 has fewer SQL functions than other RDBMSs, but it can be supplemented by introducing extended SQL functions. Let's introduce it with SQLite3 CLI (Command Line Intferface), python3, C language on CentOS7.

Obtaining and building extended SQL functions

Download the extended SQL function from SQLite Contributed Files and compile it to create a shared library.

$ wget http://www.sqlite.org/contrib/download/extension-functions.c?get=25
$ mv extension-functions.c\?get\=25 extension-functions.c
$ gcc -fPIC -lm -shared extension-functions.c -o libsqlitefunctions.so
$ ls lib*
libsqlite3.la  libsqlitefunctions.so  libtclsqlite3.la  libtool

Use extension library with SQLite3 CLI

Use the SQLite3 CLI to load the shared library of extended SQL functions with the .load command and try using the acos function.
Before executing the .load command, there was an error that the acos function did not exist, but after executing it, you can confirm that the acos function is executed without any problem.

$ sqlite3 test1.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> SELECT acos(0.5);
Error: no such function: acos
sqlite> .load ./libsqlitefunctions.so
sqlite> SELECT acos(0.5);
1.0471975511966

Use extension library with python3

Load the shared library of extended SQL functions in python3 and try using the acos function.
In python3, the load_extension function is disabled, so first enable the load_extension function with the enable_load_extension method, and then load the shared library of the extended SQL function with the load_extension method. You can see that the acos function is executed without any problem and the value is output.

python3

$ python3
Python 3.6.8 (default, Aug  7 2019, 17:28:10) 
[GCC 4.8.5 20150623 (Red Hat 4.8.5-39)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> con = sqlite3.connect(":memory:")
>>> con.enable_load_extension(True)
>>> con.load_extension("./libsqlitefunctions.so")
>>> cur = con.cursor()
>>> cur.execute("SELECT acos(0.5)")
<sqlite3.Cursor object at 0x7fa84fdf3c00>
>>> cur.fetchone()
(1.0471975511965979,)

Use extension library in C language

Load the shared library of extended SQL functions in a C language program and try using the acos function.
In the default build, the load_extension function is disabled, so first enable the load_extension function with the sqlite3_enable_load_extension function, and then load the shared library of the extended SQL function with the sqlite3_load_extension method.

#include <stdio.h>
#include <assert.h>
#include "sqlite3.h"

int main(int argc, char **argv){
  sqlite3 *db = NULL;
  int rc = SQLITE_OK;
  char *sql = "SELECT acos(0.5);";
  char *zErrMsg = NULL;
  sqlite3_stmt *stmt = NULL;

  rc = sqlite3_open(":memory:", &db);
  assert(rc==SQLITE_OK);
  sqlite3_enable_load_extension(db, 1);
  assert(rc==SQLITE_OK);
  rc = sqlite3_load_extension(db,"./libsqlitefunctions.so", NULL,&zErrMsg);
  assert(rc==SQLITE_OK);
  rc = sqlite3_prepare(db, sql, -1, &stmt, NULL);
  sqlite3_reset(stmt);
  while (SQLITE_ROW == (rc = sqlite3_step(stmt))){
    double  value = sqlite3_column_double(stmt, 0);
    printf("%lf\n", value);
  }
  assert(rc==SQLITE_DONE);
  sqlite3_finalize(stmt);
  sqlite3_close(db);
  return(0);
}

If you compile and execute as follows, you can see that the acos function is executed without any problem and the value is output.

$ gcc -Wall -o evalext evalext.c -lsqlite3 -L./.lib -I./
$ ./evalext 
1.047198

Summary

In SQLite3, as introduced this time, you can easily incorporate the shared library into SQLite3 as an extension library to expand its functions. Various extension libraries are open to the public, but you can expand the original functions by creating your own extension library.

If you find any mistakes or concerns about the description, we would appreciate it if you could give us feedback in the edit request or comment.

Appendix: Description of Extended SQL Functions

The following mathematical functions, string functions, and aggregate functions are available for extended SQL functions.

Mathematical function

String function

Aggregate function

reference

I referred to the following information.

• Run-Time Loadable Extensions
• Load An Extension
• Enable Or Disable Extension Loading -Use extension library with SQLite -[Not authorized error in SQLite load_extension](https://a5m2.mmatsubara.com/wp/?topic=sqlite%E3%81%AEload_extension%E3%81%A7not-authorized%E3%82%A8%E3% 83% A9% E3% 83% BC)