こんにちは、@onunu です。

今年もAdvent Calendarの時期がやってまいりました。

本記事はLivesense AdventCalendar 10日目としてお届けしてまいります。よろしくお願いします。

はじめに

MySQLはチューリング完全です。今年GAになった8系はもちろんとして、5.7以前のバージョンにおいてもストアドプロージャ、ストアドファンクションなどの機能によって柔軟な運用を可能としていました。

その中でも特にMySQLを柔軟たらしめているのがUDF(User Defined Function)です。MySQLではUDFをC、またはC++で記述することができます。フルマネージドのDBが跋扈跳梁する昨今、UDFを使う機会はあまりないかとは思いますが、私なりの時代へのアンチテーゼとして解説したいと思います。

MySQLにおけるUDF

MySQLはplugin機構を有していて、所定のディレクトリにshared objectを配置することでMySQLがUDFを読むことができるようになります。整理すると、私たちがMySQLのUDFを定義し利用するためには以下の手順を実行します。

C/C++で任意の処理を記述する
コンパイルしplugin dirに配置する
MySQLで CREATE FUNCTION 文を実行する

また、C/C++がMySQLの関数として成立するためには以下の3つ(集約関数である場合には5つ)の関数を実装する必要があります。

XXX()をMySQLで実行する関数だとして、

xxx_init()
xxx()
xxx_deinit()
集約関数の場合
- xxx_clear()
- xxx_add()

それぞの役割について次節以降で解説していきます。

簡単なUDFを作ってみる

ここからは実際に関数を作成しながら解説します。
集計関数ではない、その行だけで完結する簡単な関数として、「文字列の引数を1つとり、prefixを付け加えて返す」処理を実装してみたいと思います。
イメージは以下の通り。

mysql> SELECT HELLO_FROM_CPP('onunuです') LIMIT 1;
+---------------------------------+
| HELLO_FROM_CPP('onunuです')     |
+---------------------------------+
| Hello, I'm from C++.onunuです   |
+---------------------------------+
1 row in set (0.00 sec)

xxx_init() について

xxx_init() は xxx() を実行するのに先行する初期化処理で、以下の用途で利用します。

SQL中の XXX()の引数の数、型をチェックする
メイン関数である xxx() の実行の際に引数を目的の型に強制的に変更させるかをMySQLに指示する
メイン関数のためのメモリの確保
結果の最大長の指定
- 結果の小数点以下の最大の桁数を指定する (REAL 関数の場合)
結果としてnullを許容するかどうかの指定

今回は「文字列の引数を一つとる」のが条件なので、それを処理するinit関数を記述します。

#include <mysql.h>
#include <m_string.h>

extern "C" {
  my_bool hello_from_cpp_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
  // 省略
}

my_bool hello_from_cpp_init(UDF_INIT *initid, UDF_ARGS *args, char *message) {
  if (args->arg_count != 1) {
    strcpy(message, "Invalid arguments");
    return 1;
  }
  if (args->arg_type[0] != STRING_RESULT) {
    strcpy(message, "Type cannot accept");
    return 1;
  }
  return 0;
}

xxx_deinit() について

解説が前後してしまいますが、先にxxx_deinit()について説明します。
xxx_deinit()は xxx()の初期化解除関数で、存在する場合(つまりなくても動作する)にxxx_init()によって割り当てられたメモリの解放が行われます。
今回は他に何も行う必要がないので、以下のようになりました。

#include <mysql.h>
#include <m_string.h>

extern "C" {
  // 省略
  void hello_from_cpp_deinit(UDF_INIT *initid);
}

void hello_from_cpp_deinit(UDF_INIT *initid) {
  // 今回の事後処理は何もなし
}

xxx() について

xxx() は実際の処理を行います。SQLのデータ型とC/C++の型の対応は以下のようになっています。

SQLの型	C/C++の型
`STRING`	`char *`
`INTERGER`	`long long`
`REAL`	`double`

今回は以下のような実装になりました。

#include <mysql.h>
#include <m_string.h>

extern "C" {
  // 省略
  char *hello_from_cpp(UDF_INIT *initid, UDF_ARGS *args, char *result,
      unsigned long *length, char *is_null, char *error);
  // 省略
}

char *hello_from_cpp(UDF_INIT *initid, UDF_ARGS *args, char *result,
    unsigned long *length, char *is_null, char *error) {
  std::string prefix = "Hello, I'm from C++.";
  std::string comment = args->args[0];
  std::string buf = prefix + comment;
  strcpy(result, buf.c_str());
  *length = strlen(result);
  return result;
}

*length は結果のデータ長を示す必要があります。

コンパイルとMySQLへの適用

コンパイルとshared objectの配置

ここまでで実際に処理を行うコードが完成しました。あとはコンパイルしてMySQLに関数として登録を行います。このあたりの処理では、 mysql_config コマンドが有用です。

mysql_config --cflags はコンパイル時に必要なヘッダファイルの場所を、mysql_config --plugindir はshared objectを設置すべき場所を返します。

$ mysql_config --cflags
-I/usr/local/Cellar/mysql/5.7.19/include/mysql
$ mysql_config --plugindir
/usr/local/Cellar/mysql/5.7.19/lib/plugin

コンパイルを行い、配置します。

$ clang++ -Wall hello_from_cpp.cpp -o `mysql_config --plugindir`/hello_from_cpp.so `mysql_config --cflags` -shared

MySQLでの適用

MySQLのコンソールに入ってUDFの登録を行います。
登録時には、関数名、返り値の型、so ファイル名が必要になります。

mysql> create function hello_from_cpp returns string soname 'hello_from_cpp.so';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT HELLO_FROM_CPP('onunuです') LIMIT 1;
+---------------------------------+
| HELLO_FROM_CPP('onunuです')     |
+---------------------------------+
| Hello, I'm from C++.onunuです   |
+---------------------------------+
1 row in set (0.00 sec)

終わりに

C/C++でMySQLのUDFを作る方法を解説しました。いかがでしたか?
さて、ここまで読んでいただいた方はきっと「いつ使うんだ?」と疑問に思っていることでしょう。同感です。まじめにユースケースを考えてみましたが、トリガーに関数を仕込むとか、 infomation schemaの情報取得を処理するとかでしょうか。あるいはHivemallのようにMySQLだけで機械学習を完結させるとか可能かもしれません。無責任な話ではありますが、私自身業務で利用したことはないし、この記事も勉強のために書いています。もしこんな感じでつかっているよ！という例がありましたら教えていただけますと幸いです。

これにてAdventCalendar10日目はおしまいです。今年もみなさんお疲れ様でした。少し早いですが、よいお年を！