RubyでMySQLのUDFを作る

昨日はCでMySQLのUDFを作ってみたんだけど、今日はRubyで作ってみる。Rubyと言ってもmrubyだけど。

mrubyは5年ぶりくらいに使ってみたんだけど、相変わらずドキュメントが少なくてなかなかつらい…。

まず mruby のビルド。MySQLのUDFは共有ライブラリにしないといけないので、パッチをあてて make。

% git clone git@github.com:mruby/mruby.git
% cd mruby
% patch -p1 < /path/to/mruby-shared.patch
% make

mruby-shared.patch はこれ

diff --git a/build_config.rb b/build_config.rb
index 254a28ce..310191e3 100644
--- a/build_config.rb
+++ b/build_config.rb
@@ -35,6 +35,10 @@ MRuby::Build.new do |conf|
   #   cc.compile_options = %Q[%{flags} -MMD -o "%{outfile}" -c "%{infile}"]
   # end
 
+  conf.cc do |cc|
+    cc.flags = '-fPIC'
+  end
+
   # mrbc settings
   # conf.mrbc do |mrbc|
   #   mrbc.compile_options = "-g -B%{funcname} -o-" # The -g option is required for line numbers
@@ -59,6 +63,11 @@ MRuby::Build.new do |conf|
   #   archiver.archive_options = 'rs "%{outfile}" %{objs}'
   # end
 
+  conf.archiver do |archiver|
+    archiver.command = 'gcc'
+    archiver.archive_options = '-shared -o %{outfile} %{objs}'
+  end
+
   # Parser generator settings
   # conf.yacc do |yacc|
   #   yacc.command = ENV['YACC'] || 'bison'
diff --git a/lib/mruby/build.rb b/lib/mruby/build.rb
index 8154b2b1..80f0b782 100644
--- a/lib/mruby/build.rb
+++ b/lib/mruby/build.rb
@@ -61,7 +61,7 @@ module MRuby
         if ENV['OS'] == 'Windows_NT'
           @exts = Exts.new('.o', '.exe', '.a')
         else
-          @exts = Exts.new('.o', '', '.a')
+          @exts = Exts.new('.o', '', '.so')
         end
 
         build_dir = build_dir || ENV['MRUBY_BUILD_DIR'] || "#{MRUBY_ROOT}/build"

関数のメインのRubyスクリプト fib.rb

LONG_LONG_MAX = 9223372036854775807

def fib(n)
  b = 1
  c = 0
  n.times do
    a, b = b, c
    c = a + b
    raise 'Overflow' if c > LONG_LONG_MAX
  end
  c
end

一応ちゃんと動くか確認

% /path/to/mruby/bin/mruby -r./fib.rb -e 'p fib(10)'
55
% /path/to/mruby/bin/mruby -r./fib.rb -e 'p fib(100)'
trace (most recent call last):
    [2] -e:1
    [1] -e:6:in fib
-e:9:in fib: Overflow (RuntimeError)

mrubyバイトコードにコンパイル。

% /path/to/mruby/bin/mrbc -Bfib_mrb fib.rb

バイトコードが埋め込まれた fib.c が出来る。内容はこんなの。

/* dumped in little endian order.
   use `mrbc -E` option for big endian CPU. */
#include <stdint.h>
#ifdef __cplusplus
extern const uint8_t fib_mrb[];
#endif
const uint8_t
#if defined __GNUC__
__attribute__((aligned(4)))
#elif defined _MSC_VER
__declspec(align(4))
#endif
fib_mrb[] = {
0x45,0x54,0x49,0x52,0x30,0x30,0x30,0x36,0x64,0x45,0x00,0x00,0x01,0x5f,0x4d,0x41,
0x54,0x5a,0x30,0x30,0x30,0x30,0x49,0x52,0x45,0x50,0x00,0x00,0x01,0x0e,0x30,0x30,
0x30,0x32,0x00,0x00,0x00,0x96,0x00,0x01,0x00,0x03,0x00,0x01,0x00,0x00,0x00,0x14,
0x02,0x01,0x00,0x1c,0x01,0x00,0x61,0x01,0x56,0x02,0x00,0x5d,0x01,0x01,0x0e,0x01,
0x01,0x37,0x01,0x67,0x00,0x00,0x00,0x01,0x01,0x00,0x13,0x39,0x32,0x32,0x33,0x33,
0x37,0x32,0x30,0x33,0x36,0x38,0x35,0x34,0x37,0x37,0x35,0x38,0x30,0x37,0x00,0x00,
0x00,0x02,0x00,0x0d,0x4c,0x4f,0x4e,0x47,0x5f,0x4c,0x4f,0x4e,0x47,0x5f,0x4d,0x41,
0x58,0x00,0x00,0x03,0x66,0x69,0x62,0x00,0x00,0x00,0x00,0x72,0x00,0x05,0x00,0x07,
0x00,0x01,0x00,0x00,0x00,0x14,0x00,0x00,0x33,0x04,0x00,0x00,0x07,0x03,0x06,0x04,
0x01,0x05,0x01,0x55,0x06,0x00,0x2f,0x05,0x00,0x00,0x37,0x04,0x00,0x00,0x00,0x00,
0x00,0x00,0x00,0x01,0x00,0x05,0x74,0x69,0x6d,0x65,0x73,0x00,0x00,0x00,0x01,0x31,
0x00,0x03,0x00,0x06,0x00,0x00,0x00,0x00,0x00,0x3d,0x00,0x00,0x33,0x00,0x00,0x00,
0x1f,0x03,0x03,0x00,0x1f,0x04,0x04,0x00,0x01,0x02,0x03,0x20,0x04,0x03,0x00,0x01,
0x03,0x02,0x1f,0x04,0x03,0x00,0x3b,0x03,0x20,0x03,0x04,0x00,0x1f,0x03,0x04,0x00,
0x1b,0x04,0x00,0x44,0x03,0x23,0x03,0x00,0x39,0x10,0x03,0x4f,0x04,0x00,0x2e,0x03,
0x01,0x01,0x21,0x00,0x3b,0x0f,0x03,0x37,0x03,0x00,0x00,0x00,0x01,0x00,0x00,0x08,
0x4f,0x76,0x65,0x72,0x66,0x6c,0x6f,0x77,0x00,0x00,0x00,0x02,0x00,0x0d,0x4c,0x4f,
0x4e,0x47,0x5f,0x4c,0x4f,0x4e,0x47,0x5f,0x4d,0x41,0x58,0x00,0x00,0x05,0x72,0x61,
0x69,0x73,0x65,0x00,0x4c,0x56,0x41,0x52,0x00,0x00,0x00,0x33,0x00,0x00,0x00,0x05,
0x00,0x01,0x6e,0x00,0x01,0x26,0x00,0x01,0x62,0x00,0x01,0x63,0x00,0x01,0x61,0x00,
0x00,0x00,0x01,0x00,0x01,0x00,0x02,0x00,0x02,0x00,0x03,0x00,0x03,0x00,0x04,0x00,
0x01,0x00,0x01,0x00,0x04,0x00,0x02,0x45,0x4e,0x44,0x00,0x00,0x00,0x00,0x08,
};

MySQLのUDFからfibメソッドを実行するためのファイル fib_udf.c を作成。

#include <stdio.h>
#include <string.h>
#include "mruby.h"
#include "mruby/string.h"
#include "mruby/irep.h"
#include "mysql.h"

extern const uint8_t fib_mrb[];

bool fib_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
{
  if (args->arg_count != 1) {  // 引数が1個じゃなければエラー
    strcpy(message, "fib() requires one argument");
    return true;
  }
  args->arg_type[0] = INT_RESULT; // 引数は整数に自動変換する

  mrb_state *mrb = mrb_open();
  if (!mrb) {  // mruby 初期化でエラーになった
    strcpy(message, "mrb_open() error");
    return true;
  }
  initid->ptr = (void *)mrb;
  mrb_load_irep(mrb, fib_mrb);
  if (mrb->exc) {  // mrb 実行でエラーになった
    // 例外オブジェクトを inspect
    mrb_value s = mrb_funcall(mrb, mrb_obj_value(mrb->exc), "inspect", 0);
    // C文字列化
    const char *cs = mrb_string_value_cstr(mrb, &s);
    // MySQLのエラーメッセージに設定
    strncpy(message, cs, MYSQL_ERRMSG_SIZE);
    // 標準エラー出力にも書き出し
    fprintf(stderr, "%s\n", cs);
    return true;
  }
  return false;
}

void fib_deinit(UDF_INIT *initid)
{
  mrb_state *mrb = (mrb_state *)initid->ptr;
  if (mrb) {
    mrb_close(mrb);
  }
}

long long fib(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error)
{
  mrb_state *mrb = (mrb_state *)initid->ptr;
  long long n = *((long long *)args->args[0]);
  // トップレベルの fib メソッド呼び出し
  mrb_value ret = mrb_funcall(mrb, mrb_top_self(mrb), "fib", 1, mrb_fixnum_value(n));
  if (mrb->exc) { // 例外が発生した
    // 例外オブジェクトを inspect
    mrb_value s = mrb_funcall(mrb, mrb_obj_value(mrb->exc), "inspect", 0);
    // 標準エラー出力に書き出し
    fprintf(stderr, "fib(%lld): %s\n", n, mrb_string_value_cstr(mrb, &s));
    // 戻り値は NULL
    *is_null = true;
    return 0;
  }
  // Integerオブジェクトを long long に変換して返す
  return mrb_fixnum(ret);
}

コンパイル。

LD_RUN_PATH=/path/to/mruby/build/host/lib gcc -shared -fPIC -I /usr/local/mysql/include -I /path/to/mruby/include fib_udf.c fib.c -L /path/to/mruby/build/host/lib -lmruby -lm -o fib.so

配置。既に同じ名前のファイルを使ってた場合は、mysql で drop function fib してからじゃないと、mysqld が落ちるので注意。

% sudo cp fib.so /usr/local/mysql/lib/plugin/

使う。

% mysql -uroot
mysql> create function fib returns integer soname 'fib.so';
Query OK, 0 rows affected (0.03 sec)
mysql> select fib(10);
+---------+
| fib(10) |
+---------+
|      55 |
+---------+
1 row in set (0.04 sec)

mysql> select fib(100);
+----------+
| fib(100) |
+----------+
|     NULL |
+----------+
1 row in set (0.03 sec)

オーバーフローが起きた場合はエラーログに出力される。

fib(100): Overflow (RuntimeError)

続く