オレオレSQLの作り方〜MySQL編〜


オレオレ言語全盛期の炎暑の候、いかがお過ごしでしょうか。

オレオレSQLの作り方〜PostgreSQL編〜に続き、オレオレSQLの作り方〜MySQL編〜という事で
MySQLに、自作のSQL関数(UDF)を組み込むやり方を簡単に紹介します。
MySQLは名の通り、私のSQLなのでオレオレSQLには持ってこいですね。

簡単に言えば

select oreore();
select oreore(column1, column2) from table1;

自分で定義したC言語のoreore関数をDBに組み込んで使おうという事です。



最終目標は、SQLの結果でJSONを返そうと思います。

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++の型の対応は以下の通りです。

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に関しては、ドキュメントを参照下さい(ドキュメントだけだとちょっと作るには厳しいかもしれません)