MySQL - 自作ストレージエンジンで初音ミクさんに歌っていただきましょう

この記事は MySQL Advent Calendar 2019 - Qiita 14日目の記事です。

三行で

初音ミク
かわいい
ポケットミクを制御するストレージエンジンを作った

ことの発端

あやしいストレージエンジンができてきた pic.twitter.com/R1b1iDRWGT
— miyakelp (@miyakelp_) 2019年11月11日

LTで紹介したMySQLでエアコンを動かすストレージエンジンですhttps://t.co/SCvlELIDXN
— miyakelp (@miyakelp_) 2019年11月18日

MyNA望年LT大会2019にて，エアコンを動かすためのストレージエンジンを紹介させていただきました。 INSERTやUPDATEでエアコンのモードや設定温度を切り替え，SELECTで現在の状況を確認できるといったものでしたが，当然MySQL単体で実現できるものではなく，TCPでコマンドを受け付けて赤外線の信号を送信するためのデバイスを別途作成する必要がありました。

今回のアドベントカレンダーもこのネタでいこうかと思ったのですが，自宅のエアコンが動く様子を撮ったデモ動画は非常にシュールなので，今回は動かす対象をポケットミクに変更して紹介します。

ポケットミクとは

ご存じの方も多いかと思いますが，発売から数年経過しているので簡単に紹介します。

ポケットミク*1 は手元で簡単に初音ミクの歌声で演奏することができる電子楽器です。付属のペンで演奏するだけでなく，PCと接続してMIDIデバイスとして使うことができ， MIDIメッセージで制御することもできます。歌声の自然さやパラメータは本家ソフトウェア版VOCALOIDに劣りますが，手軽さやMIDIで簡単に制御できる点が大きな魅力ですね。

MySQLのストレージエンジン

MySQL（mysql-server）の中心的な機能としてクライアントとの通信やクエリの解析・最適化，データの読み書きなどがありますが，このデータの読み書きの部分がプラガブルになっており，用途に応じて好きなものに切り替えることができます。

今日ではもっぱらInnoDBを使うことが多いかと思いますが，ときには大量のデータをひたすら記録したい欲求に駆られたり，全文検索をしてみたくなったり，エアコンを起動したくなったりすることもありますよね。そんなときには，データの読み書きを担当する「ストレージエンジン」を切り替えることで，目的の動作を達成することができます。

ストレージエンジンはインタフェースとなるクラスを継承して自分で実装することもできます。ということで，今回はポケットミクを制御するストレージエンジンを作成してみました。

デモ

www.youtube.com

こんな感じでINSERT文で演奏できます。動画中では面倒なのでストアドプロシージャを使っています。

ところで，YouTubeの字幕の言語設定にはなぜSQLがないのでしょうか。

実装していく

自作ストレージエンジンは先駆者がたくさんいる*2 *3ので、これらの記事やソースコードを参考にしながら実装していきます。ストレージエンジンの名前は「pocketmiku」にしました。

ソースコードはこちらに置いてあるので，適宜参照していただければと思います。

GitHub - miyakelp/mysql-pocketmiku: The cutest MySQL storage engine

MySQLは8.0.18をデバッグビルドして使用しています。

まず最初にやること

ゼロから実装するのはさすがに面倒なので，都合のいい雛形をコピーします。まずmysql-serverのソースコード *4をcloneしてきて，storageディレクトリに降ります。中を見るとinnobaseやmyisamなど見慣れた名前が並んでいますね。ここがストレージエンジンのコードを格納するディレクトリになっています。

今回はその中にあるexampleストレージエンジンを雛形として使います。 exampleストレージエンジンは機能としては何も持ちませんが，どうやらテストカバレッジ向上の一翼を担っているそうです*5。

このexampleディレクトリをコピーしてpocketmikuディレクトリを作成します。ついでに中のファイルもリネームして，その中身の「example」「EXAMPLE」という文字列も「pocketmiku」「POCKETMIKU」に置換してしまいましょう。

初期化とか

MySQLが起動すると，各ストレージエンジンを初期化するための関数が呼び出されます。その熱いリクエストに答えて様々なインスタンスの生成する処理が走るわけですが，インスタンスどうしの関係性がやや複雑なので，下のイメージ図をみてください。

ストレージエンジンのクラス・インスタンスの関係図 — ストレージエンジンのクラスとインスタンスの関係図

あくまでもイメージで正確には実装と異なるかもしれませんが，とりあえず以下の理解で話を進めます。

ストレージエンジンはMySQLの世界でたった1つhandlertonインスタンスを持つ
各コネクション，各テーブルごとに独立したhandlerインスタンスを持つ
1つのテーブルに対して各コネクションのhandlerで共有されているshareインスタンスがある

handlertonはhandlerの生成やトランザクション管理，handlerはテーブルをオープンや実際の読み書きを担当しています。そして，初期化関数は ha_pocketmiku.cc で実装されています。といっても，これは既にexampleで実装されているので，そのまま利用しています。

handlerton *pocketmiku_hton;

static int pocketmiku_init_func(void *p) {
  DBUG_TRACE;

  pocketmiku_hton = (handlerton *)p;
  pocketmiku_hton->state = SHOW_OPTION_YES;
  pocketmiku_hton->create = pocketmiku_create_handler;
  pocketmiku_hton->flags = HTON_CAN_RECREATE;
  pocketmiku_hton->is_supported_system_table
    = pocketmiku_is_supported_system_table;

  return 0;
}

MySQLが生成してくれたhandlertonに対してフラグ類やコールバック関数を設定していきます。例えばpocketmiku_hton->createはpocketmikuエンジンの出番がきたときにhandlerを生成するための関数pocketmiku_create_handler を設定しています。

今回はこれだけですが，InnoDBのコードをみると

// storage/innobase/handler/ha_innodb.cc 4600行目くらい
  innobase_hton->commit = innobase_commit;
  innobase_hton->rollback = innobase_rollback;

といった具合なので，トランザクションにまつわるエトセトラを捌くときはこのあたりにコールバック関数をセットして良きに計らうとよさそうですね（本当は今回やりたかった）。

開いたり読んだり書いたり

さて，無事に初期化が終わり，いよいよENGINE=pocketmikuなテーブルが作成されてpocketmikuエンジンの出番がやってきました。まずpocketmikuエンジンの役割を整理しておきます。

テーブルのオープン：MIDIデバイスを開く（ファイルポインタ取得）
INSERT / UPDATE：受け取った演奏パラメータ（音程，発音，強さ，長さ）をポケットミクに送信
SELECT：直前の演奏パラメータを取得

音程はMIDIのノート番号，強さはMIDIのベロシティなので，0-127の範囲です。発音はポケットミクのマニュアルに記載されている各文字に対応する0-127の番号です。長さはミリ秒でintに収まる範囲とします。

これらの操作はすべてhandlerのメンバ関数として実装します。また，今回はINSERT/UPDATEの作用は同じとするので，共通の関数を呼び出すだけの実装とします。

そして，これらの要件を満たす以下のスキーマにのみ対応することにします。

 CREATE TABLE `/dev/midi1` (
  `key` tinyint(4) DEFAULT NULL,
  `sound` tinyint(4) DEFAULT NULL,
  `velocity` tinyint(4) DEFAULT NULL,
  `length` int(11) DEFAULT NULL
) ENGINE=pocketmiku

1つのテーブルで1台のポケットミクを制御するので，演奏パラメータはすべてshareの中に格納しておけば良いでしょう。型はtinyintですが，内部的には文字列で受け渡しをするので，charの配列で実装します。

// ha_pocketmiku.h
class pocketmiku_share : public Handler_share {
...
  char note_key[4] = { 0 };
  char sound[4] = { 0 };
  char velocity[4] = { 0 };
  char length[11] = { 0 };
...
}

こんな感じでヘッダに変数を定義しておきます。

テーブルのオープン

ソースコードはここ

テーブルのオープンはha_pocketmiku::openに実装します。今回はテーブル名に「MIDIデバイスのスペシャルファイルのフルパス」を指定する約束にします。これで，「テーブル名の場所にあるファイル」を開くことでポケットミクのファイルディスクリプタを取得できます。ファイルディスクリプタは1つのテーブルで共通になるので，shareに押し込んでおけばよさそうですね。

// ha_pocketmiku.h
class pocketmiku_share : public Handler_share {
...
  int dev_fp = -1;
...
}

// ha_pocketmiku.cc
int ha_pocketmiku::open(const char *, int, uint, const dd::Table *) {
...
  share->dev_fp = my_open(table_share->table_name.str, O_RDWR, MYF(0));
...
}

よくあるCのプログラムと同じようにopenを呼ぶだけなのですが， MySQLではシステムコールをラップしたmy_*という関数を使います。

ちなみに，ha_pocketmiku::openの第1引数もテーブル名なのですが，こちらはエンコードされていて使いづらいので，ハードモードが好みの方におすすめです。

SELECT

ソースコードはここ

本当はINSERTが一番簡単なのですが，SELECTで何も返さないとUPDATEができないので，先にSELECTを実装します。 SELECTを実行したときに関数が呼ばれる順序は，exampleエンジンのコメントに丁寧な説明があります。

mysql-server/ha_example.cc at 8.0 · mysql/mysql-server · GitHub

つまり，rnd_initで初期化した後，rnd_nextが呼ばれまくって結果をつくるということです。 rnd_nextは1回呼ばれるごとに1行分のデータをバッファに書き出していきます。

ここで注意が必要なのは，rnd_nextは結果の末尾に達した次の呼び出しでEOFを返すということです。今回SELECTで取得する演奏パラメータは常に1行しか返しませんから，2回目のrnd_nextでEOFを返します。

この判定を行うためにフラグを準備しました。フラグは1コネクションに1つ必要なので，handlerのメンバとして実装します。

#include "sql/table.h"
#include "sql/field.h"

// ha_pocketmiku.h
class ha_pocketmiku : public handler {
...
  bool next_is_eof;
...
}

// ha_pocketmiku.cc
int ha_pocketmiku::rnd_init(bool) {
  DBUG_TRACE;
  next_is_eof = false;
  return 0;
}

int ha_pocketmiku::rnd_next(uchar *buf) {
...
  if (next_is_eof) {
    return HA_ERR_END_OF_FILE;
  }

  // バッファの先頭 table->s->null_bytes バイトはNULLフラグ
  // 0クリアしておかないと全部NULLに見える
  memset(buf, 0, table->s->null_bytes);

  // バッファ書き込みフラグをセットする
  org_bitmap = tmp_use_all_columns(table, table->write_set);

  // バッファに結果を詰める
}

バッファに詰める処理は，フィールド名で分岐してshareに格納してある演奏パラメータをコピーしているだけです。具体的には，各フィールドをループで舐めて，store関数を呼び出しています。すべて書くと長くなるので，GitHubのコードを合わせて見ていただければと思います。

書き込みフラグのセットを忘れるとアサーションで落ち，NULLフラグのクリアを忘れると何を書いてもNULLが返ってきます。意外と忘れがちなので，動かないときはこのあたりを疑ってみてください。

INSERT / UPDAT

ソースコードはここ

ようやく本番です。実際にポケットミクを鳴らしていきます。 INSERTはha_pocketmiku::write_row，UPDATEはha_pocketmiku::update_rowが呼び出されます。 pocketmikuエンジンでは，どちらの処理もやってきたデータをshareの演奏パラメータに突っ込むだけなので， ha_pocketmiku::write_update_rowを作成して，これを呼び出すことにします。

int ha_pocketmiku::write_row(uchar *) {
  return write_update_row();
}

int ha_pocketmiku::update_row(const uchar *, uchar *) {
  return write_update_row();
}

ha_pocketmiku::write_update_rowでは，まずやってきたデータを指すポインタを取得します。

String attribute(attribute_buffer, sizeof(attribute_buffer), &my_charset_bin);
// 読み取り用のフラグセット
my_bitmap_map *org_bitmap = tmp_use_all_columns(table, table->read_set);
for (Field **field = table->field; *field; field++) {
  (*field)->val_str(&attribute, &attribute);
...

これでattribute.ptr()とすると先頭ポインタが，attribute.length()とすると長さが取得できるようになります。あとは(*field)->field_nameをみて対象のフィールド名と同じ演奏パラメータに書き込みをします。

strncpy(share->note_key, attribute.ptr(), attribute.length());
share->note_key[attribute.length()] = '\0';

attribute.ptr()から始まる文字列は'\0'で終端されていない*6ので注意が必要です。しかもバッファは最初ゼロクリアされており，'\0'を植え忘れても起動直後はそれっぽく動きます。さらに各カラムのバッファは連続したアドレスに配置されているので，しばらく使っていると突然となりのカラムとくっついたデータが挿入されるという怪奇現象に悩まされるわけですね。。。

さて，ここまででINSERT/SELECTに対応できたので，一旦ビルドしてmysql-serverのlib/plugin以下に ha_pocketmiku.soをコピーして実行してみましょう。

INSTALL PLUGIN pocketmiku SONAME 'ha_pocketmiku.so';
CREATE DATABASE mikumiku;
CREATE TABLE mikumiku.`/dev/midi1`
  (`key` tinyint, sound tinyint, velocity tinyint, length int)
  ENGINE = pocketmiku;
INSERT INTO mikumiku.`/dev/midi1` (`key`, sound, velocity, length) VALUES (60, 0, 80,500);
SELECT * from mikumiku.`/dev/midi1`;

+------+-------+----------+--------+
| key  | sound | velocity | length |
+------+-------+----------+--------+
|   60 |     0 |       80 |    500 |
+------+-------+----------+--------+
1 row in set (0.00 sec)

動きましたね。では，いよいよポケットミクにデータを送信していきます。

ポケットミクにデータを送る

ポケットミクはMIDIメッセージで制御することができます。

ポケットミクのマニュアルにある通り，まずSysEx*7 で発音する文字を指定します。 soundで指定された番号をそのまま送れば良さそうです。

keyで指定された値をMIDIのノート番号（音程）， velocityで指定された値を強さ（MIDIのベロシティ）として，チャネル0にノートオン・ノートオフを送ることで，指定した音階で歌ってもらうことができます。

ノートオンからノートオフまでの間は，lengthミリ秒だけusleep関数で眠ることにします。

データ送信とスリープの流れをまとめるとこんな感じです。

// 発音指定
0xF0 0x43 0x79 0x09 0x11 0x0A 0x00 0x[sound] 0xF7 0x90 0x00 0x00
// ノートオン
0x90 0x[key] 0x[velocity]
// スリープ
usleep(lenght * 1000)
// ノートオフ
0x80 0x[key] 0x00

実際にデータを送信するにはwriteを呼べば良いのですが，例によってprefix my_がついた関数でラップされているので，こいつを使います。

my_write(share->dev_fp, note_on_data, 12, MYF(0)); // SysExとノートオンをまとめた
usleep(length * 1000);
my_write(share->dev_fp, note_off_data, 3, MYF(0));

さあ，これで完成です！

早速鳴らしてみます。

INSERT INTO mikumiku.`/dev/midi1` (`key`, `sound`, `velocity`, `length`) VALUES (
  60,
  0,
  80,
  1000
);

鳴りましたね！ C3（真ん中のド）で1秒間「あー」と発音しています。

しかし，このままでは毎回マニュアルを見ながら各種パラメータの番号を設定する必要があり，少し使いづらいですね。せっかくなので，MySQLの機能を使ってちょっと工夫してみます。

MySQLっぽいことをする

エアコンを動かしたときはこれでおしまいでしたが，今回はせっかくなのでMySQLっぽいことをしていきます。

作成したストレージエンジンに与える音程や発音の番号は直感的にはわかりづらいですよね。やはり，

120BPM で 4分音符 の C4（ド） であを鳴らす

のように指示できたほうが，実際に歌わせる際には便利です。そこで，これらの情報を格納したマスタテーブルを作成し，ストアドファンクションやストアドプロシージャを活用して，便利に演奏できるようにしてみます。

マスタテーブル

「あ」は0，「ぴょ」は94のように，文字からsoundに与える番号を引くテーブルmikumiku.sound_masterと，「C3」は60，「A6」は105のように，音名からkeyに与える番号を引くテーブルmikumiku.note_masterを作成します*8。

sound_masterは濁音・半濁音，発音記号の大文字小文字を区別する必要があるので，collationをutf8mb4_binにしておきます。

CREATE TABLE mikumiku.sound_master (sound VARCHAR(32) primary key, number INT) COLLATE utf8mb4_bin;
CREATE TABLE mikumiku.note_master (name VARCHAR(32) primary key, number INT);

中に入れておくデータはここにあります。

これでマニュアルを見なくてもSELECTを打てば一撃でパラメータを設定できるようになりました。

ストアドファンクション/プロシージャ

毎回長いINSERT文を書くのも大変ですし，演奏する楽曲のテンポや音符に合わせてlengthを計算するのも面倒です。

そこで，テンポと音符の長さからlengthを計算するストアドファンクションと，テンポ，音名，発音する文字，音符の長さ，強さを受け取ってINSERTするストアドプロシージャを作成します。

delimiter //
CREATE FUNCTION mikumiku.calc_length(bpm INT, note FLOAT) RETURNS INT DETERMINISTIC
begin
  RETURN (((60 * 1000 / bpm * note)));
end
//
delimiter ;

delimiter //
CREATE PROCEDURE mikumiku.note_on(IN bpm INT, IN note FLOAT, IN k VARCHAR(32), IN s VARCHAR(32),  IN v TINYINT)
begin
  INSERT INTO mikumiku.`/dev/midi1` (`key`, `sound`, `velocity`, `length`) VALUES (
    (SELECT number FROM  mikumiku.note_master WHERE name = k),
    (SELECT number FROM mikumiku.sound_master WHERE sound = s),
    v,
    mikumiku.calc_length(bpm, note)
  );
end
//
delimiter ;

このストアドファンクションとストアドプロシージャを使うと，このように簡単に演奏することができます！休符はSELECTでスリープすれば楽ちんですね。

CALL mikumiku.note_on(120, 1,  'C3',   'ど', 60);
SELECT sleep(mikumiku.calc_length(120, 1) / 1000.0);
CALL mikumiku.note_on(120, 1,  'E3',   'み', 60);
SELECT sleep(mikumiku.calc_length(120, 1) / 1000.0);
CALL mikumiku.note_on(120, 1,  'G3',   'そ', 60);
SELECT sleep(mikumiku.calc_length(120, 1) / 1000.0);

演奏してみる

『仰げば尊し』の冒頭部分を演奏するSQLです。

SET @bpm = 60;

CALL mikumiku.note_on(@bpm, 0.5, 'F#3',   'あ', 70);
CALL mikumiku.note_on(@bpm,   1, 'F#3',   'お', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'G3',   'げ', 70);
CALL mikumiku.note_on(@bpm,   1,  'A3',   'ば', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'A3',   'と', 70);
CALL mikumiku.note_on(@bpm,   1,  'B3',   'お', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'B3',   'と', 70);
CALL mikumiku.note_on(@bpm,   1,  'A3',   'し', 70);

CALL mikumiku.note_on(@bpm, 0.5, 'F#3',   'わ', 70);
CALL mikumiku.note_on(@bpm,   1,  'E3',   'が', 70);
CALL mikumiku.note_on(@bpm, 0.5, 'F#3',   'し', 70);
CALL mikumiku.note_on(@bpm,   1,  'G3',   'の', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'B3',   'お', 70);
CALL mikumiku.note_on(@bpm, 1.5,  'A3',   'ん', 70);
select sleep(mikumiku.calc_length(@bpm, 1) / 1000.0);

CALL mikumiku.note_on(@bpm, 0.5, 'F#3',   'お', 70);
CALL mikumiku.note_on(@bpm,   1, 'F#3',   'し', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'G3',   'え', 70);
CALL mikumiku.note_on(@bpm,   1,  'A3',   'の', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'A3',   'に', 70);
CALL mikumiku.note_on(@bpm,   1,  'B3',   'わ', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'B3',   'に', 70);
CALL mikumiku.note_on(@bpm,   1,  'A3',   'も', 70);

CALL mikumiku.note_on(@bpm, 0.5, 'F#3',   'は', 70);
CALL mikumiku.note_on(@bpm,   1,  'E3',   'や', 70);
CALL mikumiku.note_on(@bpm, 0.5,  'B3',   'い', 70);
CALL mikumiku.note_on(@bpm,   1,  'A3',   'く', 70);
CALL mikumiku.note_on(@bpm, 0.5, 'C#3',   'と', 70);
CALL mikumiku.note_on(@bpm, 1.5,  'D3',   'せ', 70);