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

Function name                     Description
acos(N) Arc cosine of the numerical value specified by N(Reverse cosine)Returns.
asin(N) Arc sign of the numerical value specified by N(Inverse sine)Returns.
atan(N) Arc tangent of the numerical value specified by N(Reverse tangent)Returns.
atn2(N1, N2) N1,Arc tangent of the numerical value specified by N2(Reverse tangent)Returns.
atan2(N1, N2) N1,Arc tangent of the numerical value specified by N2(Reverse tangent)Returns.
acosh(N) Arc hyperbolic cosine of the number specified by N(Inverse hyperbolic cosine)Returns.
asinh(N) Ark hyperbolic sign with the number specified by N(Inverse hyperbolic sine))Returns.
atanh(N) Arc hyperbolic tangent with the number specified by N(Inverse hyperbolic tangent))Returns.
difference(S1, S2) S1,SOUNDEX for the character expression specified in S2()Measures the difference between the values and returns an integer value.
degrees(N) Converts and returns the radians specified by N.
radians(N) Converts the degree specified by N to radians and returns it.
cos(N) The cosine of the number specified by N(cosine)Returns.
sin(N) Sign of the number specified by N(sine)Returns.
tan(N) Tangent of the number specified by N(tangent)Returns.
cot(N) Cotangent of the number specified by N(Cotangent)Returns.
cosh(N) Hyperbolic cosine of the number specified by N(Hyperbolic cosine)Returns.
sinh(N) Hyperbolic sign of the number specified by N(Hyperbolic sine)Returns.
tanh(N) Hyperbolic tangent with the number specified by N(Hyperbolic tangent)Returns.
coth(N) Hyperbolic cotangent with the number specified by N(Hyperbolic cotangent)Returns.
exp Natural logarithm e(2.718281…)Returns a value raised to the power of the number specified by N.
log(N) Returns the natural logarithm of the number specified by N.
log10(N) Returns the base 10 logarithm of the number specified by N.
power(N1, N2) Returns the power of the number specified by N1 to the number specified by N2.
sign(N) Positive as the sign of the number specified by N(+1),zero(0),negative(-1)Returns one of.
sqrt(N) Returns the square root of the number specified by N.
square(N) Returns the square of the number specified by N.
ceil(N) Returns the value rounded up from the number specified by N.
floor(N) Returns the value with the number specified by N truncated.
pi Returns a constant value of π as a floating point value.

String function

Function name                     Description
replicate(S, N) Returns the string specified by S, repeated the number of times specified by N.
charindex(S1, S2[, N]) Returns the start position of the string specified by S2 from the string specified by S1. If N is specified, it will be the position to start the search.
leftstr(S, N) Returns the string from the beginning of the string specified by S by the number specified by N.
rightstr(S, N) Returns the string from the end of the string specified by S by the number specified by N.
reverse(S) Inverts and returns the string specified by S.
proper(S) Returns only the beginning of each word in the string specified by S, converted to uppercase, and the others to lowercase.
padl(S, N) Returns the string with N additional spaces before the beginning of the string specified by S.
padr(S, N) Returns a string with N spaces added to the end of the string specified by S.
padc(S, N) Returns the string, adding N spaces before and after the beginning and end of the string specified by S, respectively.
strfilter(S1, S2) Returns only the characters in the string specified in S1 that exist in the string specified in S2.

Aggregate function

Function name                     Description
stdev(N) The standard deviation is calculated and returned from all the numerical values specified by N.
variance(N) Specifies the variance from all the numbers specified by N and returns it.
mode(N) The mode is calculated and returned from all the numbers specified by N.
median(N) The median value is calculated and returned from all the numerical values specified by N.
lower_quartile(N) Finds and returns the lower quartile from all the numbers specified by N.
upper_quartile(N) Finds and returns the upper quartile from all the numbers specified by N.

reference

I referred to the following information.

Recommended Posts

In SQLite3, add just two lines and use the extension library (extended SQL function)!
Use the LibreOffice app in Python (3) Add library
We have extended the Python Proxy, which runs Proxy in just 200 lines, to add access denial functionality.
[VueSlsApp] Specify PYTHONPATH in Lambda and use the external library simply
Attempt to extend a function in the library (add copy function to pathlib)
Deploy and use the prediction model created in Python on SQL Server
Add lines and text on the image
How to use the C library in Python
Use the LIKE clause in golang x SQLite3
[C / C ++] Pass the value calculated in C / C ++ to a python function to execute the process, and use that value in C / C ++.