SQLiteにはsqlite3_create_functionなどの関数があり、ユーザー定義関数が登録できるようになっています。 ユーザー定義関数というのは、
select count(id) from テーブル
のcountに当たるようなsql文で使う関数を自作したものです。 count等の元からある関数の置き換えもできるとか。
これは↓のjdbcドライバを使えばjavaでも可能です。
SQLiteJDBCはネイティブで書かれた各環境向けのSQLite本体をjarファイル内に持っていて、それに対してjniでアクセス、jdbcドライバとして動作するようにしたものです。 sqlite3_create_functionの部分もラッピングされています。
以下、次の環境で試したことを書きます。
- SQLite JDBC Driver 3.7.2
- Java 1.7
- Windows 7 home 64bit
配布サイトのjavadocと併せて読んでください。
まずはSQLiteJDBCのorg.sqlite.Functionを継承したクラスを作成します。 ちょっとお行儀の悪いコードですが、動作確認用にこんなサンプルを書いてみました。
// UserFunctionCount.java
package sqliteuserfunctiontest;
import java.sql.SQLException;
import org.sqlite.Function;
// シングルスレッド用
public class UserFunctionCount extends Function
{
private int _count = 0;
public String getName()
{
return "ufCount";
}
public void setCount(int value)
{
_count = value;
}
@Override
protected void xFunc() throws SQLException
{
_count++;
int value = value_int(0);
System.out.println(getName() + ".xFunc : count = " + _count + " : " + value);
String res = getName() + '[' + _count + ", " + value + "]";
result(res);
}
}
オーバーライドしている「xFunc」がコールバックメソッドです。 order byが適用される前に呼ばれるので注意してください。
select ufCount(id) from テーブル
というふうに使ったら、select中にsqliteから呼び出されます。
xFuncの中では「value_int(0)」のようなFunctionのプロテクトメソッド「value_型(index)」で値を受け取ることができます。 ここでindexは列番号で、0ベースです。 jdbcの「ResultSet.getInt(index)」などは1ベースですが、それとは違うので注意。
型がわかっている状況でしか試してないので確かめてませんが、自作関数に何列の項目が渡されたかは「int args()」メソッドで、その列の型が何なのかは「int value_type(index)」で調べることができる ... はずです。 value_typeメソッドで帰ってくる値は、多分ここに書いてある数字じゃないかと予想してます。
- SQLiteオフィシャル - Datatypes In SQLite Version 3 - 2.2 Affinity Name Examples
あと、これも確かめてないけどvalue_bytesメソッドはvalue_blobメソッドで得られるbyte配列の大きさを表しているっぽいです。 このSQLiteJDBCでBLOBを扱うときは、ResultSetの場合はgetBlobではなくgetBytesを、PreparedStatementの場合はsetBlobではなくsetBytesを使いますが、xFuncの中ではvalue_blobを使うんじゃないかなぁと予想しています。
sqliteに通知する計算結果は「result(?)」メソッドに渡します。 resultメソッドは扱える型の数だけオーバーロードがあるので、目的の型で結果を返しましょう。
作ったユーザー定義関数はorg.sqlite.Functionクラスのcreateで登録します。
Connection c = DriverManager.getConnection(なんたら); UserFunctionCount ufunc = new UserFunctionCount(); Function.create(c, ufunc.getName(), ufunc);
これで元からsqlにあるcount関数のようにユーザー定義関数を使えます。 UserFunctionCount.getNameで「ufCount」という文字列を返すようにしているので、ユーザー定義関数の名前は「ufCount」です。
ちょっとここで補足を。 上のコードはシングルスレッド用です。 マルチスレッドで使用すると別々のスレッドからxFuncが呼ばれて「private int _count」メンバーが交互に書き換えられてしまい、正しい結果が得られなくなる可能性があります。 ただこれは、1つのConnectionを複数のスレッドで使いまわしたときの話です。 1つのスレッドに1つのConnectionを用意して、それぞれにユーザー定義関数のインスタンスを作って登録した場合はこのような不都合はありません。
さらに言うと↑のサンプルUserFunctionCountは「ちょっとお行儀の悪いコード」でした。 そもそもorg.sqlite.Functionは「sqliteから渡された、1行に含まれる項目だけを見て計算する」ためのユーザー定義関数を作るクラスです。 集計関数を作るためのクラスではありません。 そのため毎回countプロパティを自前でリセットしなければならないという欠点があります。
では、集計関数を作るときはどうすればいいんでしょうか? org.sqlite.Function.Aggregateクラスを使います。
// UserFunctionAggregateSum.java
package sqliteuserfunctiontest;
import java.sql.SQLException;
import org.sqlite.Function;
public class UserFunctionAggregateSum extends Function.Aggregate
{
private int _count;
private int _sum;
public String getName()
{
return "ufaSum";
}
@Override
protected void xStep() throws SQLException
{
int value = value_int(0);
_count++;
_sum += value;
System.out.println(
getName() +".xStep : count = " + _count +
" : sum = " + _sum +
" : " + value);
}
@Override
protected void xFinal() throws SQLException
{
System.out.println(
getName() +".xFinal : count = " + _count + " : " + _sum
);
String res = getName() + "[" + _count + "個, " + _sum + "]";
result(res);
}
}
各行の処理はxFuncの代わりに「xStep」メソッドを継承して行います。 そして、集計結果出力のために「xFinal」メソッドを継承します。 resultはxFinalで書きます。
ちなみに、Function.AggregateはFunctionのサブクラスですが、xFuncメソッドはfinal指定で既に実装されており継承できません。 そのxFuncメソッドの中身は空であり、SQLiteから呼ばれることすらありません。
本題に戻って、登録方法はFunctionのときと同じです。 Function.createをします。
Connection c = DriverManager.getConnection(なんたら); UserFunctionAggregateSum ufaSum = new UserFunctionAggregateSum(); Function.create(c, ufaSum.getName(), ufaSum);
↑のコードを見ての通り、サンプルのUserFunctionAggregateSumクラスにはsql文で呼び出されるときの初期化コードがありません。 しかし、繰り返して使ってみると2つのプライベートメンバー_count、_sumがリセットされているのが分かります。 どのようにリセットしているかというと、cloneメソッドです。 Function.AggregateはCloneableを実装しています。 sql文で呼ばれるたびに登録したインスタンスのcloneを作り、そのインスタンスのパラメータで処理しているのです。 これはおそらく、1つのConnectionに複数のスレッドからアクセスした場合、Connectionに登録されたユーザー定義関数を正常に動かすための仕組みです。 ということで、Function.Aggregateの継承クラスで参照型のメンバー変数(クラス)を扱う場合、cloneメソッドの@Overrideも必要になります。
確かめるためにこんなコードを書いてみました。
// UserFunctionAggregateCount.java
package sqliteuserfunctiontest;
import java.sql.SQLException;
import org.sqlite.Function;
public class UserFunctionAggregateCount
extends Function.Aggregate
{
public class CountParam
{
public int value;
}
private CountParam _count;
public UserFunctionAggregateCount()
{
String cn = this.getClass().getSimpleName();
System.out.println("!" + cn + " constructor.");
_count = new CountParam();
}
public String getName()
{
return "ufaCount";
}
@Override
protected void xStep() throws SQLException
{
_count.value++;
int count = _count.value;
int value = value_int(0);
System.out.println(
getName() + ".xFunc : count = " + count + " : " + value
);
}
@Override
protected void xFinal() throws SQLException
{
int count = _count.value;
System.out.println(getName() + ".xFinal : count = " + count);
String res = getName() + "[" + count + "個]";
result(res);
}
@Override
public Object clone() throws CloneNotSupportedException
{
// jdbcのソースではjniで呼ぶcソース中でcloneしていた。
// NativeDB.c 内の xStep関数でclone&NewGlobalRef
// 同 xFinal関数でDeleteGlobalRef → cloneはGC待ち
UserFunctionAggregateCount res =
(UserFunctionAggregateCount)super.clone();
String cn = this.getClass().getSimpleName();
System.out.println(
"!" + cn + " clone. " +
"クローン元とクローンのCountParamインスタンスは" +
(this._count == res._count ? "同じ。" : "違う。")
);
return res;
}
}
「確かにcloneが呼ばれてるね」ってのを確かめるためのコードですが、一応注意喚起のため、メンバークラスをディープコピーせずにシャローコピーのままにしてみました。
こんなテーブルに
create table tmain (col1 integer, col2 text)
適当な行を5つ入れて実行してみると。
PrintStream out = System.out;
Connection c = DriverManager.getConnection(なんたら);
Statement s = c.createStatement();
out.println("◆UserFunctionAggregateCountの登録");
UserFunctionAggregateCount ufaCount = new UserFunctionAggregateCount();
Function.create(c, ufaCount.getName(), ufaCount);
out.println("◆UserFunctionAggregateCountを使ってselect");
ResultSet res = s.executeQuery(
"select ufaCount(col1) as count from tmain order by col1"
);
res.next();
out.println("resA : " + res.getString("count"));
out.println("◆cloneメソッドがシャローコピーのため、前のsql文の影響を受けてしまう。");
res = s.executeQuery(
"select ufaCount(col1) as count from tmain order by col1"
);
res.next();
out.println("resB : " + res.getString("count"));
こんな結果になります。
◆UserFunctionAggregateCountの登録 !UserFunctionAggregateCount constructor. ◆UserFunctionAggregateCountを使ってselect !UserFunctionAggregateCount clone. クローン元とクローンのCountParamインスタンスは同じ。 ufaCount.xFunc : count = 1 : 74 ufaCount.xFunc : count = 2 : 28 ufaCount.xFunc : count = 3 : 1 ufaCount.xFunc : count = 4 : 66 ufaCount.xFunc : count = 5 : 63 ufaCount.xFinal : count = 5 resA : ufaCount[5個] ◆cloneメソッドがシャローコピーのため、前のsql文の影響を受けてしまう。 !UserFunctionAggregateCount clone. クローン元とクローンのCountParamインスタンスは同じ。 ufaCount.xFunc : count = 6 : 74 ufaCount.xFunc : count = 7 : 28 ufaCount.xFunc : count = 8 : 1 ufaCount.xFunc : count = 9 : 66 ufaCount.xFunc : count = 10 : 63 ufaCount.xFinal : count = 10 resB : ufaCount[10個]
sql文が実行されてからxFuncが呼ばれるまでの間にcloneが作られていることがわかります。 そしてこのサンプルではcloneメソッドでメンバークラスのディープコピーをしなかったため、前のsql文の影響を受けてしまっています。 こういう不具合を起こさないように、きちんとcloneメソッドをオーバーライドして、メンバー変数のディープコピーをしましょう。 そして、マルチスレッドの場合は、どのタイミングでどのインスタンスがcloneされるのかをしっかり把握しておく必要があります。
だいたいこのくらいの情報があれば、ユーザー定義関数は作れるかな? SQLiteJDBCべったりのコードになりますね。
使い道はやはり、暗号化/複合化関数の自作だと思うんですよ。 SQLiteはその辺が弱いようなので。 暗号化/複合化関数が用意できるなら、暗号化テキストの全文検索みたいなこともできるようになりますよね? こういうのと組み合わせるといいかも。
- 過去の投稿 ... java : 鍵の長さ256bitのAESを使う
でもまぁ、自作ってことは他の管理ツールから見れなくなるのか。 大人しくMySQLとかPostgreSQLとかにした方が良さそうな...
でお買い物
