H2でユーザ定義関数/ストアドプロシージャを試してみる

H2では組み込みの関数の他に、ユーザ定義関数/ストアドプロシージャを作る事ができます。Triggerと同様にJavaで実装します。

実装の仕方は大きく2通りあります。

Triggerの時にやったようなJavaで実装してコンパイルして、H2のクラスパスにそのクラスを入れて、そのクラス名を登録する、というパターンが1つ(Referencing a Compiled Method)。

もう1つは、Javaのソースコードで宣言する、というやり方(Declaring Functions as Source Code)

とりあえず、非常にシンプルなパターンで両方試してみます。

まず、クラスを作ってコンパイルします。Triggerの時とは異なり、特定のインターフェースを実装する必要はありません。その代わり、呼ばれるメソッドはスタティックなメソッドである必要があります。

以下のようなクラスを作ってみました。

package com.karatebancho;

public class Crypt {
	public static String caesar(String src){
		if(src == null || src.length() == 0){
			return src;
		}
		StringBuilder sb = new StringBuilder();
		for(int i=0;i<src.length();i++){
			int c = src.charAt(i);
			if(c >= 33 && c <= 126){
				c += 3;
				if(c > 126){
					c-= 94;
				}
			}
			sb.append((char)c);
		}
		return sb.toString();
	}
}

解説するまでもない感じですが、ASCIIの通常の文字をシーザー暗号で暗号化するメソッドです。

適当にjarに固めて、起動時のクラスパスに入れます。

java -cp h2-1.2.132.jar:function.jar org.h2.tools.Shell -url jdbc:h2:mem:

で、SQLを投げます。

create alias caesar for "com.karatebancho.Crypt.caesar";

はい。もう使えます。

sql> call caesar('test');
CAESAR('test')
whvw

sql> select caesar(chr(x)),chr(x) from system_range(65,67);
CAESAR(CHR(X))|CHR(X)
D             |A
E             |B
F             |C
(3 rows, 10 ms)

この関数は、ストアドプロシージャに相当するので、fromの後とかにはおけません。

sql> select * from caesar('test');
Error: org.h2.jdbc.JdbcSQLException: 関数 "CAESAR" はリザルトセットを返さなければなりません
Function "CAESAR" must return a result set; SQL statement:
select * from caesar('test') [90000-132]

メッセージの和訳「リザルトセット」とするくらいなら、ResultSetのままでいいような気がしますが、それはさておき。まぁ、簡単にできました。

それでは、ソースコードで宣言するパターンをやってみます。関数の中身自体は同じくシーザー暗号化する関数です。

sql> CREATE ALIAS CAESAR AS $$String caesar(String src){if(src == null || src.length() == 0){return src;}StringBuilder sb = new StringBuilder();for(int i=0;i<src.length();i++){int c = src.charAt(i);if(c >= 33 && c <= 126){c += 3;if(c > 126){c-= 94;}}sb.append((char)c);}return sb.toString();}$$;
(Update count: 0, 725 ms)

まぁ、動きます。

sql> select caesar('caesar');     
CAESAR('caesar')
fdhvdu
(1 row, 31 ms)

ただ、パーサーがだめなのかShellが悪いのか、さきほどのソースコードをインデントしたりするととたんにSQLの文法エラーになったりします...