MyTAP + my_prove + MySQL::Sandbox で快適 SQL Unit Testing

MyTAP という MySQL 上で Test Anything Protocol を実現してしまうというプロダクトがあります。これと TAP::Parser::SourceHandler::MyTAP に付属している my_prove コマンドを使うとかなり簡単に SQL プログラミングでテストを行う事が出来ます。

また MySQL::Sandbox はだいぶ過去のエントリ*1 *2で紹介した事がありますが、異なる version の mysqld を色んな構成で簡単に作れてしまう優れものです。

今回はこれらを組み合わせてテストする方法について紹介しちゃいます。

MyTAP のインストール

MySQL::Sandbox を使って作った sandbox が $HOME/sandboxes/rsandbox_5_1_58 にあるとします。

$ cd ~/sandboxes/rsandbox_5_1_58
$ ./start_all
$ wget https://raw.github.com/theory/mytap/master/mytap.sql
$ ./master/use -e "SET GLOBAL log_bin_trust_function_creators = ON; SOURCE mytap.sql;"

として mysqld を立ち上げて、さらに mytap.sql をインストールしておきます。

テスト実践編

次にこんなソースを用意しておきます。

DELIMITER ;

DROP TABLE IF EXISTS foo;
CREATE TABLE foo (
  id int(10) unsigned not null auto_increment,
  name varchar(32) not null,
  primary key(id)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS bar;
CREATE TABLE bar (
  id int(10) unsigned not null auto_increment,
  name varchar(32) not null,
  primary key(id)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS baz;
CREATE TABLE baz (
  id int(10) unsigned not null auto_increment,
  name varchar(32) not null,
  primary key(id)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS table_rows_stat;
CREATE TABLE table_rows_stat (
  id int(10) unsigned not null auto_increment,
  table_name varchar(128) not null,
  rows bigint(20) unsigned not null,
  published_on int(10) unsigned not null,
  primary key(id),
  key on_table_name_and_published_on (table_name, published_on)
) ENGINE=InnoDB;

DROP PROCEDURE IF EXISTS record_table_rows_stat;
DELIMITER //
CREATE PROCEDURE record_table_rows_stat(match_table_pattern VARCHAR(255))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a_table VARCHAR(255);
  DECLARE matches_table_cursor CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME REGEXP @match_table_pattern;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  SET @match_table_pattern = match_table_pattern;

  OPEN matches_table_cursor;

  REPEAT
    FETCH matches_table_cursor INTO a_table;
    IF done = 0 THEN
      SET @rows_count_query = CONCAT('SELECT COUNT(*) INTO @rows_count FROM ', a_table);      
      PREPARE rows_count_stmt FROM @rows_count_query;
      EXECUTE rows_count_stmt;
      /* SELECT @a_table, @rows_count; */
      INSERT INTO table_rows_stat(table_name, rows, published_on) VALUES(a_table, @rows_count, UNIX_TIMESTAMP());
      DEALLOCATE PREPARE rows_count_stmt;
    END IF;
  UNTIL done END REPEAT;

  CLOSE matches_table_cursor;
END;
//
DELIMITER ;

正規表現でマッチしたテーブルを COUNT して table_rows_stat テーブルに突っ込むつもりの PROCEDURE です。実用性はまったく考えてませんw
この PROCEDURE がちゃんと動くかてすとしてみましょう。

テストはこんな感じです。

SELECT tap.plan(6);

/* setup schema */
SOURCE schema/example.sql;

/* call procedure and insert rows */
SET TIMESTAMP = UNIX_TIMESTAMP('2011-12-10 12:00:00');
CALL record_table_rows_stat('(foo|bar|baz)');

SET TIMESTAMP = UNIX_TIMESTAMP('2011-12-10 12:05:00');
INSERT INTO foo(name) VALUES('a'), ('b'), ('c');
INSERT INTO bar(name) VALUES('a'), ('b'), ('c'), ('d'), ('e');
INSERT INTO baz(name) VALUES('a'), ('b'), ('c'), ('d');

SET TIMESTAMP = UNIX_TIMESTAMP('2011-12-10 12:10:00');
CALL record_table_rows_stat('(foo|bar|baz)');

/* testing */
SET TIMESTAMP = 0;

SELECT tap.diag('At first, all table rows are empty');

SELECT tap.eq(rows, 0, CONCAT('The rows of ', table_name, ' at ', FROM_UNIXTIME(published_on), ' equals ', 0)) 
  FROM table_rows_stat WHERE published_on = UNIX_TIMESTAMP('2011-12-10 12:00:00') ORDER BY id ASC;

SELECT tap.diag('Second, all table has few rows');

SELECT tap.eq(rows, 3, CONCAT('The rows of ', table_name, ' at ', FROM_UNIXTIME(published_on), ' equals ', 3)) 
  FROM table_rows_stat WHERE table_name = 'foo' AND published_on = UNIX_TIMESTAMP('2011-12-10 12:10:00') ;
SELECT tap.eq(rows, 5, CONCAT('The rows of ', table_name, ' at ', FROM_UNIXTIME(published_on), ' equals ', 5)) 
  FROM table_rows_stat WHERE table_name = 'bar' AND published_on = UNIX_TIMESTAMP('2011-12-10 12:10:00') ;
SELECT tap.eq(rows, 4, CONCAT('The rows of ', table_name, ' at ', FROM_UNIXTIME(published_on), ' equals ', 4)) 
  FROM table_rows_stat WHERE table_name = 'baz' AND published_on = UNIX_TIMESTAMP('2011-12-10 12:10:00') ;

CALL tap.finish();

これらをこんな感じで配置します。

$ tree .
.
├── schema
│&#160;&#160; └── example.sql
└── t
    └── test_record_rows_stat.my

この状態で、

$ my_prove --verbose --database example --mysql-bin ~/sandboxes/rsandbox_5_1_58/master/use t/
t/test_record_rows_stat.my .. 
1..6
# At first, all table rows are empty
ok 1 - The rows of bar at 2011-12-10 12:00:00 equals 0
ok 2 - The rows of baz at 2011-12-10 12:00:00 equals 0
ok 3 - The rows of foo at 2011-12-10 12:00:00 equals 0
# Second, all table has few rows
ok 4 - The rows of foo at 2011-12-10 12:10:00 equals 3
ok 5 - The rows of bar at 2011-12-10 12:10:00 equals 5
ok 6 - The rows of baz at 2011-12-10 12:10:00 equals 4
ok
All tests successful.
Files=1, Tests=6,  0 wallclock secs ( 0.02 usr +  0.01 sys =  0.03 CPU)
Result: PASS

みたいにやりますよ。

ちょっと BK ですが、--mysql-bin オプションで作った sandbox の master に付属している mysql コマンドを指定しちゃった方が幸せです。

まとめ

これで好きなビルド方法で好きなバージョンの好きな構成にて、SQLだけのテストが書けますよと。
例えば mysql のバージョン移行時に何かしたりだとか、あるいは監視系にも使えるかもしれないし、セットアップ時の項目のテストにも出来そうですね。SHOW VARIABLES とか使ってですけど。

何かと夢が広がりんぐですねー。

*1:http://d.hatena.ne.jp/ZIGOROu/20090607/1244367464

*2: http://d.hatena.ne.jp/ZIGOROu/20090608/1244470674

日向夏特殊応援部隊

俺様向けメモ

MyTAP + my_prove + MySQL::Sandbox で快適 SQL Unit Testing

MyTAP のインストール

テスト実践編

まとめ