オレオレSQLの作り方〜MySQL編〜
オレオレ言語全盛期の炎暑の候、いかがお過ごしでしょうか。
オレオレSQLの作り方〜PostgreSQL編〜に続き、オレオレSQLの作り方〜MySQL編〜という事で
MySQLに、自作のSQL関数(UDF)を組み込むやり方を簡単に紹介します。
MySQLは名の通り、私のSQLなのでオレオレSQLには持ってこいですね。
簡単に言えば
select oreore();
select oreore(column1, column2) from table1;
自分で定義したC言語のoreore関数をDBに組み込んで使おうという事です。
select json(a, b) from c;
の結果で
{ "aaa" : 1, "bbb" : "abc" }
固定値を返す関数
早速、固定で999を返す関数を作ってみます。
※バージョンはMySQL5.0.62です。インストール等は省略します。
#include <stdio.h> #include <mysql/mysql.h> #ifdef __cplusplus extern "C" { #endif my_bool ore1_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void ore1_deinit(UDF_INIT *initid); long long ore1(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error); #ifdef __cplusplus } #endif my_bool ore_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { return 0; } void ore_deinit(UDF_INIT *initid) { // void } long long ore(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { return 999; }
※MySQL4xのマニュアルだと xxx_init や xxx_deinit は省略可能とありますが
MySQL5xだとxxx_initが無いと関数登録時にエラーになるようです
これをコンパイルし、/usr/lib へコピーします。
※LD_LIBRARY_PATHに追加するやり方が望ましいですが、今回は/usr/libへコピーします
cc -shared -I /usr/include/mysql -o oreore.so oreore.c cp oreore.so /usr/lib
作成したオブジェクトの関数を呼び出す関数を定義します。
create functionを使用します。
mysql> create function ore returns integer soname 'oreore.so'; Query OK, 0 rows affected (0.00 sec)
では、早速作った関数を実行
mysql> select ore(); +-------+ | ore() | +-------+ | 999 | +-------+ 1 row in set (0.00 sec)
999が正しく表示されていますね。
| SQL の型 | C/C++ の型 |
|---|---|
| STRING | char * |
| INTEGER | long long |
| REAL | double |
オレオレSQL開発でのデバッグ方法
デバッグ作業の効率をあげるために、フロントエンドにメッセージを出力してみます。
my_dbug.hのDBUG_PRINTを使用したりしたのですが、PostgreSQLのelog関数のようなものは
見あたりませんでした。
xxx_init内では引数チェック等を行い、xxx_initの引数messageにメッセージを設定可能ですが
xxx関数ではmessageは使用出来ない為、stderrにメッセージを出力しログファイルで確認する
方法が容易かと思います。
先ほどのoreore.cに1行追加します
fprintf(stderr, "%s\n", "デバッグのテストですよ"); return 999;
再度、コンパイルしUDFを登録しなおすと/var/log/mysqld.logに
デバッグのテストですよ
が出力されています。
ログをtailしながら開発するのが楽です。
引数を取得
次に引数を取得してみます。
テーブル作成
mysql> create table table1 (column1 int, column2 varchar(32)); Query OK, 0 rows affected (0.00 sec) mysql> describe table1; +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | column1 | int(11) | YES | | NULL | | | column2 | varchar(32) | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
oreore.cを少し修正します。
引数チェック
my_bool ore_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { // 引数の数が2じゃない場合はエラー if (args->arg_count != 2) { strcpy(message, "ore() requires two arguments!"); return 1; } // 引数の型チェック 1番目がintで2番目が文字列じゃない場合エラー if (args->arg_type[0] != INT_RESULT || args->arg_type[1] != STRING_RESULT) { strcpy(message,"ore(1, 'a') requires a integer and an string!"); return 1; } return 0; }
引数を取得
long long ore(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { long long arg1 = *((long long*)args->args[0]); const char *arg2 = args->args[1]; fprintf(stderr, "arg1=[%lld] arg2=[%s]\n", arg1, arg2); return 999; }
ここで、先ほどの引数無しのoreore関数を削除しておきます。
登録した関数を削除するには、drop function ユーザ関数 を使います。
mysql> drop function ore; Query OK, 0 rows affected (0.00 sec) mysql> create function ore returns integer soname 'oreore.so'; Query OK, 0 rows affected (0.00 sec)
早速、動かしてみます。
引数チェックを確認してみましょう。
引数無し
mysql> select ore(); ERROR: ore() requires two arguments!
引数の数が不正
mysql> select ore(1,1,1); ERROR: ore() requires two arguments!
引数の型が不正
mysql> select ore('AAA', 111);
ERROR:
ore(1, 'a') requires a integer and an string!"
正常系
mysql> select ore(123, 'test'); +------------------+ | ore(123, 'test') | +------------------+ | 999 | +------------------+ 1 row in set (0.00 sec)
ログには
arg1=[123] arg2=[test]
と表示されています
テーブルにデータを入れて実行してみましょう。
mysql> insert into table1 values(1, 'aaa'); Query OK, 1 row affected (0.00 sec) mysql> insert into table1 values(2, 'bbb'); Query OK, 1 row affected (0.00 sec) mysql> select * from table1; +---------+---------+ | column1 | column2 | +---------+---------+ | 1 | aaa | | 2 | bbb | +---------+---------+ 2 rows in set (0.00 sec) mysql> select ore(column1, column2) from table1; +-----------------------+ | ore(column1, column2) | +-----------------------+ | 999 | | 999 | +-----------------------+ 2 rows in set (0.01 sec)
ログファイルには
arg1=[1] arg2=[aaa] arg1=[2] arg2=[bbb]
それぞれのレコードに対応する引数が取得できていますね。
JSONを返すオレオレ関数
前の〜PostgreSQL編〜はHTMLでしたので、今回はJSONにします。
最後に、JSONのテンプレートをテーブルに登録して出力する値を動的に変えてみます。
テーブル作成
mysql> create table json_test (id int, name varchar(32)); Query OK, 0 rows affected (0.02 sec) mysql> insert into json_test values (1, 'よねむら'); Query OK, 1 row affected (0.00 sec) mysql> insert into json_test values(2, 'もぺもぺ'); Query OK, 1 row affected (0.00 sec)
オレオレ関数jsonを作成
※今までと異なり戻り値が文字列なので注意が必要です。xxx()関数の定義が違います。
#include <stdio.h> #include <string.h> #include <mysql/mysql.h> #ifdef __cplusplus extern "C" { #endif my_bool json_init(UDF_INIT *initid, UDF_ARGS *args, char *message); void json_deinit(UDF_INIT *initid); char *json(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error); #ifdef __cplusplus } #endif my_bool json_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { // 引数の数が2じゃない場合はエラー if (args->arg_count != 2) { strcpy(message, "json() requires two arguments!"); return 1; } // 引数の型チェック 1番目がintで2番目が文字列じゃない場合エラー if (args->arg_type[0] != INT_RESULT || args->arg_type[1] != STRING_RESULT) { strcpy(message,"json(1, 'a') requires a integer and an string!"); return 1; } args->arg_type[0] = INT_RESULT; args->arg_type[1] = STRING_RESULT; return 0; } void json_deinit(UDF_INIT *initid) { // void } char *json(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error __attribute__((unused))) { long long arg1 = *((long long*)args->args[0]); const char *arg2 = args->args[1]; sprintf(result, "{ \"id\" : %lld, \"name\" : \"%s\" }" , arg1, arg2); *length = strlen(result); return result; }
関数登録
※returns を string にする必要があります。
mysql> create function json returns string soname 'oreore.so'; Query OK, 0 rows affected (0.00 sec)
固定値でテスト
mysql> select json(111, 'JSON TEST') as json_str;
+--------------------------------------+
| json_str |
+--------------------------------------+
| { "id" : 111, "name" : "JSON TEST" } |
+--------------------------------------+
1 row in set (0.00 sec)JSON文字列が返ってきていますね。
全データ取得
mysql> select id, json(id, name) from json_test;
+------+---------------------------------------+
| id | json(id, name) |
+------+---------------------------------------+
| 1 | { "id" : 1, "name" : "よねむら" } |
| 2 | { "id" : 2, "name" : "もぺもぺ" } |
+------+---------------------------------------+
2 rows in set (0.00 sec)
idが1の結果だけを取得
mysql> select id, json(id, name) from json_test where id=1;
+------+---------------------------------------+
| id | json(id, name) |
+------+---------------------------------------+
| 1 | { "id" : 1, "name" : "よねむら" } |
+------+---------------------------------------+
1 row in set (0.00 sec)
idが2の結果だけを取得
mysql> select id, json(id, name) from json_test where id=2;
+------+---------------------------------------+
| id | json(id, name) |
+------+---------------------------------------+
| 2 | { "id" : 2, "name" : "もぺもぺ" } |
+------+---------------------------------------+
1 row in set (0.00 sec)
案件だと、返すJSONを動的に切り替えたい場合もあると思うのでJSONのテンプレートを
マスタとして登録してみます。
おまけでHTMLも登録。
mysql> create table template (template_id int, template_str varchar(64)); Query OK, 0 rows affected (0.00 sec) mysql> insert into template values (1, '{ "hoge_id" : %lld, "hoge_name" : "%s" }'); Query OK, 1 row affected (0.00 sec) mysql> insert into template values (2, '{ "アイディー" : %lld, "名前" : "%s" }'); Query OK, 1 row affected (0.00 sec) mysql> insert into template values (3, '<html><body>id=%lld<br>name=%s</body></html>'); Query OK, 1 row affected (0.00 sec) mysql> select * from template; +-------------+-----------------------------------------------+ | template_id | template_str | +-------------+-----------------------------------------------+ | 1 | { "hoge_id" : %lld, "hoge_name" : "%s" } | | 2 | { "アイディー" : %lld, "名前" : "%s" } | | 3 | <html><body>id=%lld<br>name=%s</body></html> | +-------------+-----------------------------------------------+ 3 rows in set (0.00 sec)
templateを受け取るように、先ほどの関数を変更します。
※引数チェックも3つに変更してください
char *json(UDF_INIT *initid, UDF_ARGS *args, char *result, unsigned long *length, char *is_null, char *error __attribute__((unused))) { long long arg1 = *((long long*)args->args[0]); const char *arg2 = args->args[1]; const char *arg3 = args->args[2]; sprintf(result, arg3 , arg1, arg2); *length = strlen(result); return result; }
実行
動的に出力するテンプレートを変える
mysql> select json(id, name, (select template_str from template where template_id=2)) as result from json_test where id=2; +------------------------------------------------------+ | result | +------------------------------------------------------+ | { "アイディー" : 2, "名前" : "もぺもぺ" } | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json(id, name, (select template_str from template where template_id=3)) as result from json_test where id=1; +-----------------------------------------------------+ | result | +-----------------------------------------------------+ | <html><body>id=1<br>name=よねむら</body></html> | +-----------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json(id, name, (select template_str from template where template_id=1)) as result from json_test where id=2; +-------------------------------------------------+ | result | +-------------------------------------------------+ | { "hoge_id" : 2, "hoge_name" : "もぺもぺ" } | +-------------------------------------------------+ 1 row in set (0.00 sec)
ネタ要素満点ですが自分で定義した関数をRDBに登録して使う方法も活用次第では可能性が広がるので
是非、MySQLでオレオレSQL(UDF)を堪能してみて下さい。
MySQLのUDFに関しては、ドキュメントを参照下さい（ドキュメントだけだとちょっと作るには厳しいかもしれません）
