SQLite3 で、たった2行追加して拡張ライブラリ(拡張SQL関数)を使おう!

はじめに

SQLite3 では、PostgreSQL の EXTENSION (拡張)と同じように、共有ライブラリを拡張ライブラリとして導入することが可能です。実際、enable_load_extension と load_extension の2行だけを追加することで拡張ライブラリを使うことができます!
例として、SQLite Contributed Filesで公開されている拡張SQL関数(extension-functions.c)を導入して使ってみましょう。
SQLite3 は他のRDBMSと比べて、SQL関数が少ないのですが、拡張SQL関数を導入することで補うことができます。CentOS7で、SQLite3 CLI(Command Line Intferface)、python3、C言語で導入してみましょう。

拡張SQL関数の入手、構築

SQLite Contributed Filesから、拡張SQL関数をダウンロード後、コンパイルして、共有ライブラリを作成します。

$ 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

SQLite3 CLIで拡張ライブラリを使用

SQLite3 CLI で拡張SQL関数の共有ライブラリを .load コマンドで読み込んで、acos関数を使ってみます。

.load コマンドの実行前は、acos関数が存在しないというエラーが発生していますが、実行後は、acos関数が問題なく実行されていることが確認できます。

$ 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

python3で拡張ライブラリを使用

python3 で拡張SQL関数の共有ライブラリを読み込んで、acos関数を使ってみます。

python3では、load_extension機能が無効になっていますので、まずは、enable_load_extensionメソッドでload_extension機能を有効にしてから、load_extensionメソッドで拡張SQL関数の共有ライブラリを読み込みます。
acos関数が問題なく実行されて、値が出力されることが確認できます。

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,)

C言語で拡張ライブラリを使用

C言語のプログラムで拡張SQL関数の共有ライブラリを読み込んで、acos関数を使ってみます。

デフォルトのビルドでは、load_extension機能が無効になっていますので、まずは、sqlite3_enable_load_extension関数でload_extension機能を有効にしてから、sqlite3_load_extensionメソッドで拡張SQL関数の共有ライブラリを読み込みます。

#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);
}

以下のようにコンパイルして実行しますと、acos関数が問題なく実行されて、値が出力されることが確認できます。

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

まとめ

SQLite3 では今回、紹介しましたように、簡単に共有ライブラリを拡張ライブラリとして、SQLite3 に取り込むことができて、機能を拡充することができます。さまざまな拡張ライブラリが公開されていますが、自分で拡張ライブラリを作成することで、オリジナル機能を拡充することが可能です。

もし、記述について誤りがあったり、気になることがあれば、編集リクエストやコメントでフィードバックしていただけると助かります。

付録 : 拡張SQL関数の説明

拡張SQL関数では、以下の数学関数、文字列関数、集約関数が利用できます。

数学関数

関数名	説明
acos(N)	N で指定した数値のアークコサイン(逆余弦)を返します。
asin(N)	N で指定した数値のアークサイン(逆正弦)を返します。
atan(N)	N で指定した数値のアークタンジェント(逆正接)を返します。
atn2(N1, N2)	N1, N2 で指定した数値のアークタンジェント(逆正接)を返します。
atan2(N1, N2)	N1, N2 で指定した数値のアークタンジェント(逆正接)を返します。
acosh(N)	N で指定した数値のアークハイパボリックコサイン(逆双曲線余弦)を返します。
asinh(N)	N で指定した数値のアークハイパボリックサイン(逆双曲線正弦))を返します。
atanh(N)	N で指定した数値のアークハイパボリックタンジェント(逆双曲線正接))を返します。
difference(S1, S2)	S1, S2 で指定された文字式を対象に SOUNDEX() 値の差を測定し、整数値を返します。
degrees(N)	N で指定したラジアンを度に変換して返します。
radians(N)	N で指定した度をラジアンに変換して返します。
cos(N)	N で指定した数値のコサイン(余弦)を返します。
sin(N)	N で指定した数値のサイン(正弦)を返します。
tan(N)	N で指定した数値のタンジェント(正接)を返します。
cot(N)	N で指定した数値のコタンジェント(余接)を返します。
cosh(N)	N で指定した数値のハイパボリックコサイン(双曲線余弦)を返します。
sinh(N)	N で指定した数値のハイパボリックサイン(双曲線正弦)を返します。
tanh(N)	N で指定した数値のハイパボリックタンジェント(双曲線正接)を返します。
coth(N)	N で指定した数値のハイパボリックコタンジェント(双曲線余接)を返します。
exp	自然対数 e (2.718281…) に N で指定した数値で累乗した値を返します。
log(N)	N で指定された数値の自然対数を返します。
log10(N)	N で指定された数値の10を底とした対数を返します。
power(N1, N2)	N1 で指定された数値を N2 で指定された数値で累乗した値を返します。
sign(N)	N で指定され数値の符号として正(+1)、ゼロ(0)、負(-1)のいずれかを返します。
sqrt(N)	N で指定された数値を平方根した値を返します。
square(N)	N で指定された数値を2乗した値を返します。
ceil(N)	N で指定された数値を切り上げた値を返します。
floor(N)	N で指定された数値を切り捨てた値を返します。
pi	π の定数値を浮動小数点の値として返します。

文字列関数

関数名	説明
replicate(S, N)	S で指定された文字列を N で指定された数値回数だけ繰り返して返します。
charindex(S1, S2[, N])	S1 で指定された文字列から、S2で指定された文字列の開始位置を返します。N が指定されている場合、検索を開始する位置となります。
leftstr(S, N)	S で指定された文字列の先頭から、N で指定された数値分だけ文字列を返します。
rightstr(S, N)	S で指定された文字列の最後から、N で指定された数値分だけ文字列を返します。
reverse(S)	S で指定された文字列を反転させて返します。
proper(S)	S で指定された文字列の各単語の先頭だけを大文字、それ以外を小文字に変換して返します。
padl(S, N)	S で指定された文字列の先頭より前に、N 個のスペースを追加して文字列を返します。
padr(S, N)	S で指定された文字列の最後に、N 個のスペースを追加して文字列を返します。
padc(S, N)	S で指定された文字列の先頭より前と最後、それぞれ N 個のスペースを追加して文字列を返します。
strfilter(S1, S2)	S1 で指定された文字列の文字で、S2で指定された文字列の中に存在する文字だけを返します。

集約関数

関数名	説明
stdev(N)	N で指定され数値の全てから標準偏差を求めて返します。
variance(N)	N で指定され数値の全てから分散を求めて返します。
mode(N)	N で指定され数値の全てから最頻値を求めて返します。
median(N)	N で指定され数値の全てから中央値を求めて返します。
lower_quartile(N)	N で指定され数値の全てから下位四分位点を求めて返します。
upper_quartile(N)	N で指定され数値の全てから上位四分位点を求めて返します。

参照

以下の情報を参考にさせて頂きました。

• Run-Time Loadable Extensions
• Load An Extension
• Enable Or Disable Extension Loading
• SQLiteで拡張ライブラリを使う
• SQLiteのload_extensionでnot authorizedエラー