SQLite3で三角関数や対数関数を使う

SQLite3では原則四則演算しか処理できないため、細かい分析をしにくいのが難点。

そこでSQLite.orgのContributed Filesからextension-functions.cを使って、三角関数や対数関数などの計算ができるようSQL関数を拡張します。

extension-functions.cで使用可能となる関数は以下の通り。

Math: 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.
String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter.
Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile.

シェアードライブラリの作成

まず、以下のサイトからsqlite-amalgamation-3071602.zipとextension-funcutions.cをダウンロードします。zipは解凍してちょんまげ。

sqlite-amalgamation-3071602.zip：SQLite Download Page
http://www.sqlite.org/download.html

extension-functions.c：Contributed Files
http://www.sqlite.org/contrib

次にextension-functions.cの置いてあるフォルダで以下のコマンドを実行し、シェアードライブラリlibsqlitefunctions.soを作成します。"path"にはsqlite-amalgamation-3071602のパス（sqlite3ext.hの置いてあるフォルダのパス)を指定。

gcc -shared -I "path" -o libsqlitefunctions.so extension-functions.c

extension-functions.cをsqlite-amalgamation-3071602のフォルダにコピーし、そこでライブラリを作成するのが一番楽だと思います。

シェアードライブラリをSQLite3で使用する

libsqlitefunctions.soを読み込むことで、以後、前述の関数が使用できるようになります。ただし一度対話モードを終了させると再度読み込みが必要。

ライブラリの読み込みは以下のようにすればOK。

sqlite> select load_extension("libsqlitefunctions.so");

または

sqlite> .load libsqlitefunctions.so;

ライブラリを読み込んだ後で以下のように打ち込んで、

sqlite> select cos(radians(45));
0.707106781186548

と返ってきたら成功。

シェルスクリプトでの使い方

SQLite3はシェルスクリプトを用いて、

sqlite3 -option hoge.db "select fuga, hogefuga from fugahoge ; " >piyo.txt

などといった形でデータベースから値を引き出せますが、load_extensionに相当する
オプションが存在しないようなので、下記のようにEOFを使って結果を得る他ないみたいです。もっとスマートな方法とかあるのかな･･･？

sqlite3 hoge.db <<EOF
.output piyo.txt
select load_extension("libsqlitefunctions.so");
select fuga, log(hogefuga) from fugahoge;
.q
EOF

MacなどではSecurity ConsiderationsとかでSQLiteの拡張機能が無効になっている場合があるらしいのですが、その辺りの解決方法はよくわからず･･･。参考のsqlite - Loadable ExtensionsやPython標準ライブラリリファレンス 12.6一番下の記述などがヒントになる･･･かなぁ･･･？

参考文献

sqlite - Loadable Extensions
http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

シェアードライブラリを使用してSQLiteに独自関数を追加する方法
http://pgkiss.web.fc2.com/sqlite/addOriginalFunc.html

※sqlite3_enable_load_extensionに関して
Python標準ライブラリリファレンス　12.6. sqlite3 — SQLite データベースに対する DB-API 2.0 インタフェース
http://docs.python.jp/3.3/library/sqlite3.html