Hatena::ブログ(Diary)

Yet Another Hackadelic

2011-12-10

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
│   └── 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 とか使ってですけど。

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

2011-11-25

replication status plugin のインストール

Replication Status Plugin という物がありまして、SHOW SLAVE STATUS で取れる値が information_schema.SLAVE_STATUS テーブルから取れるよって代物です。

SHOW 系のコマンドって変数代入出来ないから SQL でホゲホゲする事が出来ないんですよね。なのでこういう物があると中々便利かなと。

how do I install the Mysql Replication Status plugin に書いてあるまんまですが、こんな感じでインストールします。BUILDDIR が MySQL を一回ビルドしたディレクトリ、INSTALLDIR が MySQL をインストールした(--prefixで指定した値)ディレクトリだとして、

$ wget http://launchpad.net/is-replication-status/trunk/0.1/+download/MySQL-is_replication_status-0.1.tar.gz
$ tar xfz MySQL-is_replication_status-0.1.tar.gz 
$ cd MySQL-is_replication_status-0.1
$ ./configure --with-mysql-src=$BUILDDIR --prefix=$INSTALLDIR
$ make
$ make install

みたいな感じにしておくと、$INSTALLDIR/lib 以下に is_replication_status.a is_replication_status.la, is_replication_status.so ファイルが出来上がります。

このままだと install 出来ないので、

$ cd $INSTALLDIR/lib/
$ mv -v is_replication_status.* mysql/plugin

としてあげてから、おもむろに MySQL のコンソールより、

INSTALL PLUGIN MASTER STATUS soname 'is_replication_status.so';
INSTALL PLUGIN SLAVE_STATUS soname 'is_replication_status.so';

とかしてやるとインストール完了です。

利用方法

もの凄い簡単です。SHOW SLAVE STATUS で取得出来るフィールドがそのままカラム名になっていて、テーブルには1レコードしか入ってませんので、例えば

USE information_schema;
SELECT Exec_Master_Log_Pos FROM SLAVE_STATUS;

みたいな感じで使います。

2011-08-02

変数とストアドプロシージャについて

追記しました (2011-08-08T16:57:50Z)

割と弊社では MySQL の event scheduler を使ってみたり、ストアドプロシージャ、トリガーなど積極的に使っています。

今回はストアドプロシージャを書く上で、変数を色々使うのですが備忘録としてメモっておきます。

MySQL における変数には幾つか種類があります。

  • システム変数 (c.f. @@version)
  • ユーザー変数 (c.f. @var_name)
  • ローカル変数 (c.f. DECLARE var_name INT, ストアドプロシージャ内で使える)

これら変数の特徴は下記です。(MySQL 5.1.50 で確認)

-- システム変数 ユーザー変数 ローカル変数
スコープ GLOBAL *1 SESSION LOCAL
なし なし あり
宣言 なし なし あり
設定 SET GLOBAL / SET SESSION SET SET
SELECT INTO 不可
EXECUTE USING 不可 不可

一応幾つか説明しておくと、まずは SELECT INTO ですけど、

SELECT foo, bar INTO @foo_value, @bar_value FROM sample WHERE id = 10;

とかやると foo, bar の値がそれぞれ @foo_value, @bar_value に突っ込まれます。

次に EXECUTE USING ですけど、

SET @sample_id = 1;
PREPARE stmt FROM 'SELECT * FROM sample WHERE id = ?';
EXECUTE stmt USING @sample_id;
DEALLOCATE PREPARE stmt;

みたいな感じで事前に作った placeholder 付きのステートメントを EXECUTE で値を bind して使う事が出来ますが、残念ながらローカル変数をここに用いてはいけません。

考え方にもよりますが、最近のストアドプロシージャ内での編数の使い方として DECLARE で宣言した変数はテーブルのカラム名と区別出来る命名にしないとややこしくて分からなくなるのと、

placeholder への bind に使えないのでユーザー変数だけの利用に原則とどめています。

追記 (2011-08-08T16:57:50Z)

2011-08-06 で言及されてたのでちょっと触れておきます。

1. あんまり早くない

    • 対比として DBI を使ったスクリプトならば、アドバンテージがあるとしたらネットワークを介してクエリを打ち込まなくなる程度の物しか無いですね。なので大して早く無いというのは概ね同意です

2. スローログの件

    • そもそもスローログに載るような用途には使ってないので、この点に関してはなるほどなぁと言う感じです。

3. コンパイルエラーの件

    • これどうにかなりませんかねぇ。

4. テストが書きにくい

    • それ Test::Fixture::DBI で出来るよ

とまぁ、大体 id:tsucchi1022 さんと同じ意見ではあるのですが、弊社で使っている用途について簡単に説明すると現時点では、

1. Partition の add, drop を procedure + event scheduer で実施している

2. Trigger の内部で呼び出したりしている

3. 最近は Purge をプロシージャでやってたりする

    • Purge の度に書き捨てのスクリプト作るのもなんだかなぁと最近思ってたので。

いずれにせよ、Experimental な使い方している感は多々あります。Purge に関しては sql_log_bin = 0 としてバイナリログを無効化し、すべてのマスタとスレーブで同じ purge 用の procedure を叩いたりしてます。

See Also

*1:ただし SET SESSION 文の場合は SESSION 単位

2011-05-18

SET TIMESTAMP 構文で時刻の固定

SELECT * FROM hidek WHERE created_on < UNIX_TIMESTAMP() - ? ORDER BY created_on ASC LIMIT 100;

とかそういうクエリをテストしたい場合、Fixture の日時が固定値だと色々と不都合がありますね。

そういう時は、no title にある SET 構文の SET TIMESTAMP を使うとセッションごとに UNIX_TIMESTAMP(), NOW() の値を固定出来ます。

root@localhost[test]:14> SET TIMESTAMP = 1302447600;
Query OK, 0 rows affected (0.00 sec)

root@localhost[test]:15> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1302447600 |
+------------------+
1 row in set (0.00 sec)

root@localhost[test]:12> SET TIMESTAMP = DEFAULT;
Query OK, 0 rows affected (0.00 sec)

root@localhost[test]:13> SELECT UNIX_TIMESTAMP();
+------------------+
| UNIX_TIMESTAMP() |
+------------------+
|       1305711386 |
+------------------+
1 row in set (0.00 sec)

こんな感じアル!

2011-02-13 心変わり

Range Partitioning と日付型の選び方

だいぶ前に身を持って知った訳ですが、どう見ても BK なのでここに書いておきますよと。

データサイズ 日付演算 1日未満の分割 刈り込み (pruning)
datetime 8 byte ×
timestamp 4 byte ×
uint(10) 4 byte ×

1日未満の分割が必要無く、データサイズもへっちゃらと言う御仁は datetime を、日付演算なんぞ要らんという方は uint(10) がお勧め。timestamp は pruning が効かないのが泣けるです。