SQLiteで拡張ライブラリを使う

 軽量コンパクトなDBエンジンのSQLiteを最近よく利用しています。
 シングルボードコンピュータで収集したデータを格納するような用途にはよくマッチしていると思います。

 ですが、SQLiteのDBに収集したデータを活用しようと思ったとき、使用可能なSQL関数がかなり限定的なことに気付いて困ることがあります。
 以下のようにsin、cosといった基本的な三角関数すら無いですし、logやsqrt等も無い、集計関数でもメジアン標準偏差といった基本的なものすら無い。。。

sqlite> select sin(PI());
Error: no such function: PI
sqlite> select sin(3.14);
Error: no such function: sin
sqlite> select sqrt(4);
Error: no such function: sqrt

 こんな場面で威力を発揮するのが拡張ライブラリ(extension library)です。
 

拡張ライブラリ機能

 SQLiteCLI(sqlite3)で.helpを叩くと以下のコマンド(meta-command)があることが判ります。

 .load FILE ?ENTRY?     Load an extension library

 要するにこのコマンドでロード可能な拡張ライブラリを用意すれば、SQLiteの機能拡張が行えます。
 

拡張ライブラリの準備(ビルド)

 本投稿では、自前で拡張ライブラリのコードを記述するのではなく、既に存在するライブラリを利用させてもらいます。

 SQLite公式の以下のページに、Contributed Filesとしていくつかの非公式ソフトウェアが紹介されており、ダウンロード可能です。
http://www.sqlite.org/contrib

 なお、以下の文言があることは注意しましょう。

The files below are contributed by users and are not part of the standard SQLite package. The content of these files has not been verified. Use at your own risk.

 適当に意訳すると、「これらのファイルはユーザが貢献してくれたもので、標準のSQLiteパッケージの一部ではありません。ファイル内容は未検証です。使うならあなた自身の責任で。」といった感じです。
 この注意事項に同意のうえ、利用することにします。

extension-functions.c contributed by Liam Healy

 Contributed Files中のextension-functions.cを使うと、以下の関数群が利用可能になります。他の多くのRDBMSExcelのような表計算ソフトでも使われるような、ごく標準的に用いられる関数群です。

  • 数学関数
    • acos
    • asin
    • atan
    • atn2
    • atan2
    • acosh
    • asinh
    • atanh
    • difference
    • degrees
    • radians
    • cos
    • sin
    • tan
    • cot
    • cosh
    • sinh
    • tanh
    • coth
    • exp
    • log
    • log10
    • power
    • sign
    • sqrt
    • square
    • ceil
    • floor
    • pi
  • 文字列関数
    • replicate
    • charindex
    • leftstr
    • rightstr
    • reverse
    • proper
    • padl
    • padr
    • padc
    • strfilter
    • ltrim*1
    • rtrim*2
    • trim*3
    • replace*4
  • 集計関数
    • stdev
    • variance
    • mode
    • median
    • lower_quartile
    • upper_quartile


 コンパイルまでの基本的な流れは以下になります。

  1. 今回利用する拡張ライブラリのソースコード、extension-functions.c contributed by Liam Healyを先述のページからダウンロード
  2. このソースコード(extension-functions.c)をビルドするために必要なSQLite自体のソースコードを、以下のSQLite公式のダウンロードページからダウンロード
    SQLite Download Page
  3. コンパイル

 本投稿記載時点の最新ファイルを元にした場合、LINUX系OSなら具体的には以下のようなコマンドになります。

# Create directory for compile SQLite library
mkdir ~/sqlite_dev
cd ~/sqlite_dev

# Download source files
## Check the latest extension-functions.c from http://www.sqlite.org/contrib
wget http://www.sqlite.org/contrib/download/extension-functions.c?get=25
## Check the latest source filename from http://www.sqlite.org/download.html
wget http://www.sqlite.org/2018/sqlite-amalgamation-3230100.zip

# Prepare the files
mv ./extension-functions.c?get=25 ./extension-functions.c
unzip sqlite-amalgamation-3230100.zip

# Compile
gcc -shared -I ./sqlite-amalgamation-3230100/ -o libsqlitefunctions.so extension-functions.c

 これで、拡張ライブラリの実体が、~/sqlite_dev/libsqlitefunctions.soに生成されます。
 

拡張ライブラリの利用

 前述の通りSQLiteCLI(sqlite3)から、.loadコマンドで読み込めば利用可能です。

.load /home/USERNAME/sqlite_dev/libsqlitefunctions.so

※ ~/sqlite_dev/libsqlitefunctions.soと指定した場合、~の展開が出来ないようでエラーとなりましたので、フルパス表現にしています。
 

動作確認

 本投稿の冒頭でエラーとなっていたような関数群が正常に実行可能なことで、動作確認できます。

sqlite> select sin(PI());
1.22464679914735e-16
sqlite> select sin(3.14);
0.00159265291648683
sqlite> select sqrt(4);
2.0

 ここで、sin(PI())が1.22464679914735 \times 10^{-16}とほぼ0ながらも綺麗な0では無いことに気付きます。が、2行目で実行したsin(3.14)よりもはるかに0に近いことも判ります。
 念のためソースコードを追いかけてみると、sinを含め多くの数学関数*5が、C言語の標準の数学関数をそのまま呼び出しています。
 また、PIについてはC言語のmath.hに定義されているM_PIを使うようになっています*6
 要するに独自実装した結果、0ではない変な値になっているわけではなく、C言語で同様の計算を行った場合と同じです。
 念のため、検証用のコードをC言語で書いてみます。

$ cat test.c
#include <stdio.h>
#include <math.h>

#define PI 3.14159265358979323846

int main(int argc, char *args[])
{
    double val1 = 0.0;
    double val2 = 0.0;

    val1 = sin(M_PI); // use PI value which defined in math.h
    val2 = sin(PI); // use PI value which will be defined in extension-functions.c if not defined in math.h
    printf("val1= %15.14e, (%lf)\n", val1, val2);
    printf("val2= %15.14e, (%lf)\n", val2, val2);

    return 0;
}

$ gcc -o test test.c

$ ./test
val1= 1.22464679914735e-16, (0.000000)
val2= 1.22464679914735e-16, (0.000000)

 ということで、C言語で標準の数学関数を利用した場合の値と完全一致しました。
 10^{-16}オーダーの誤差が許容できないような高精度科学技術計算とか金融とかの用途でなければ実用上何も問題ありません*7
 浮動小数点演算と、無限に続く\piを有限の固定値として扱う以上、回避できない誤差なので気にせず無視しましょう*8
 

拡張ライブラリの自動ロード

 標準ではSQLiteCLI(sqlite3)を起動する都度、.loadコマンドで拡張ライブラリを読み込む必要があります。(なお、sqlite3の引数指定で拡張ライブラリをロードさせるようなオプションもありません。)
 それが面倒なら、~/.sqlitercを使って自動でロードするように設定可能です。

 manで確認すると、~/.sqlitercについて以下のように説明されています。

If the file ~/.sqliterc exists, it is processed first. can be found in the user's home directory, it is
read and processed. It should generally only contain meta-commands.

 すなわち、.loadコマンド(のようなmeta-command)を~/.sqlitercに書いておけば、sqlite3を起動すると始めに実行させることができます。
 



以上。

*1:※現行のSQLiteには既に実装されているため、デフォルトではビルド対象外

*2:※現行のSQLiteには既に実装されているため、デフォルトではビルド対象外

*3:※現行のSQLiteには既に実装されているため、デフォルトではビルド対象外

*4:※現行のSQLiteには既に実装されているため、デフォルトではビルド対象外

*5:sqrt, acos, asin, atan, acosh, asinh, atanh, sin, cos, tan, cot, sinh, cosh, tanh, coth, log, log10, exp, rad2deg, deg2rad

*6:もし未定義ならM_PIを3.14159265358979323846として定義

*7: (業務で)詳しくない偉い人に説明するなら、C言語の標準ライブラリで計算するのと同精度ですが何か問題でも?と言った趣旨で良いのではないかと。

*8:きっちりSIN( PI() )=0になるExcelGoogleスプレッドシートはどうやって実装しているのか気になりますけど…