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

# はじめに

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

# 拡張SQL関数の入手、構築

[SQLite Contributed Files](https://www.sqlite.org/contrib/download/)から、拡張SQL関数をダウンロード後、コンパイルして、共有ライブラリを作成します。

```bash
$ 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関数を使ってみます。<br>
.load コマンドの実行前は、acos関数が存在しないというエラーが発生していますが、実行後は、acos関数が問題なく実行されていることが確認できます。

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

python3

```python
$ 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関数を使ってみます。<br>
デフォルトのビルドでは、load_extension機能が無効になっていますので、まずは、sqlite3_enable_load_extension関数でload_extension機能を有効にしてから、sqlite3_load_extensionメソッドで拡張SQL関数の共有ライブラリを読み込みます。

```c
#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関数が問題なく実行されて、値が出力されることが確認できます。

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

# まとめ

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

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

# 付録 : 拡張SQL関数の説明

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

## 数学関数

|関数名  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|説明|
|:---|:---|
|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|π の定数値を浮動小数点の値として返します。|

## 文字列関数

|関数名  &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|説明|
|:---|:---|
|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で指定された文字列の中に存在する文字だけを返します。|

## 集約関数

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

# 参照

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

- [Run-Time Loadable Extensions](https://www.sqlite.org/loadext.html)
- [Load An Extension](https://www.sqlite.org/c3ref/load_extension.html)
- [Enable Or Disable Extension Loading](https://www.sqlite.org/c3ref/enable_load_extension.html)
- [SQLiteで拡張ライブラリを使う](https://wave.hatenablog.com/entry/2018/05/28/185000)
- [SQLiteのload_extensionでnot authorizedエラー](https://a5m2.mmatsubara.com/wp/?topic=sqlite%E3%81%AEload_extension%E3%81%A7not-authorized%E3%82%A8%E3%83%A9%E3%83%BC)