Excel で楽にSQL やseed を管理する方法

Rails + PostgreSQL の組み合わせが多いなと最近感じているshibuso です。Heroku もこの組み合わせですしRails 界隈では流行りなのでしょうか、個人的にはMySQL の方が長く触れてきたので好きなのですが。とは言えどちらもSQL です。今回はExcel を用いてテストデータがいくつか必要になった時にSQL を手軽に生成したり、管理する方法を紹介したいと思います。

この記事を読んで幸せになれそうなのは、プログラマ及びデータベースでデータを管理している方（ソーシャルゲームでのプランナーに当たるような方）です。ただしこれを読むにあたって、最低限のSQL を書ける知識が必要になります。SQL を書けない方は学習するか書ける方に手伝ってもらいつつ進めると、きっとそのうち書けるようになります、いつか、多分。それに加えて少しだけExcel を理解している必要があります。

実用例

いきなり「テストデータを作る」「データを管理する」と言われてもなかなか想像できないと思うので、欲しいデータとSQL の例を先に出します。

例1. 一貫性があるデータを大量に作る

テストをする際に、殆ど同じデータで構わないものの、見分けを付けたいために少し違うデータを用意したい場合があったりします。例えばメールを送るテストがあるとして、gmailを使えば「ユーザ名+適当な文字列@gmail.com」みたいなフォーマットで送信可能です。この「適当な文字列」の部分を楽に用意することができます。

sample+1	sample+2	sample+3

あるいはゲームで言えば「モンスター1」「モンスター2」のように、とりあえず見分けを付けたい場合があります。

モンスター1	モンスター2	モンスター3

そんな時のSQL 文は以下のようになります。（テーブルのCREATE 文は適当にご用意下さい）

-- SQL
-- 送信しないようにわざと「@gmail」としています
-- しかしRails + Devise のような環境ですとvalidate に失敗するのでご注意下さい
INSERT INTO users (id, email) VALUES (1, 'sample+1@gmail');
INSERT INTO users (id, email) VALUES (2, 'sample+2@gmail');
INSERT INTO users (id, email) VALUES (3, 'sample+3@gmail');

INSERT INTO monsters (id, name) VALUES (1, 'モンスター1');
INSERT INTO monsters (id, name) VALUES (2, 'モンスター2');
INSERT INTO monsters (id, name) VALUES (3, 'モンスター3');

例2. マスターデータを管理する

運用側が用意し必要がある時以外は更新を行わない、いわゆるマスターデータを管理する事も多々あると思います。

例えば全都道府県の名前は、私がこれまで生きてきた中で変更はありませんでした。しかし例えば「大阪府」が「大阪都」になるかもしれません。こういう情報の管理も楽に行えます。

SQL 文は以下のようなものを想定しています。

-- SQL
INSERT INTO prefectures (id, name) VALUS (1, '北海道');
INSERT INTO prefectures (id, name) VALUS (2, '青森県');
INSERT INTO prefectures (id, name) VALUS (3, '岩手県');

UPDATE prefectures SET name = '北海道' WHERE id = 1;
UPDATE prefectures SET name = '青森県' WHERE id = 2;
UPDATE prefectures SET name = '岩手県' WHERE id = 3;

実現方法

勘がいい人はもうここまでで実現方法が想像できているかもしれませんね。カラム名、値をそれぞれ決まったセルに入力し、最後にそれらをまとめてSQL を書き出す式を作ればいいのです。

上の画像は一番最初に挙げた例を実際にExcel で管理してみたものです。「=」で書き始めるとそれが式だと判断されますが、その後に「"」を付けることで文字列として扱ってくれます。これと途中途中に参照式を組み合わせることにより、SQL 文を作り出すことが可能です。書いた式は以下のとおりです。

="INSERT INTO "&$B$1&" ("&$B$2&", "&$C$2&") VALUES ("&B3&", '"&C3&"');"

注意すべき点としては

• テーブル名やカラム名は絶対参照にする
• カラムが文字列を扱う場合は「'」を前後にはさむ
• NULL やNOW() 等SQL 文に直接書きたいものはセルを参照せずにそのまま書き込んでしまうのも手

ぐらいでしょうか。上記の例ではINSERT 文を書きましたが、UPDATE 文にする場合は式の形が変わるものの、基本的にやることは同じです。

Excel を利用する利点としては、コピペが簡単にできることと、値を少しずつ増やすことが容易な点です。上記の図でのB4 は、普通に「2」と書いても良いですが、「=B3+1」と式を書くことも可能です。そしてそれを下にコピーしていくとどんどん数字が増えていきます。正に手間いらず。

式を書かなくても、セルの右下を引っ張ってオートフィル機能を使うのも便利です。C3 に当たる「sample+1@gmail」を書けば、その後は「sample+2@gmail」「sample+3@gmail」…と好きなだけ書けます。テストデータを大量に用意するのはもちろん、マスターデータを作る時にも活用できます。

おまけ1：seed を生成する

無理矢理感が少しありますが、書き換えればRails で利用するseed も書き出せます。例えば都道府県のデータを作ろうとします。

# seed
# 冪等を意識して書いています
prefecture_data = [
  { id: 1, name: '北海道' },
  { id: 2, name: '青森県' },
  { id: 3, name: '岩手県' },
# あとは略
]

prefecture_data.each do |d|
  next if Prefecture.find_by_id(d[:id]).present?
  h = Prefecture.new
  d.each do |k, v|
    h.send("#{k}=", v)
  end
  h.save!
end

このようなものが必要になった時に、「{ id: 1, name: '北海道' },」の部分を書き出すように、先程の要領で書き換えればいいのです。

マスターデータはもちろんSQL を流すことで対応可能ですが、seed を用いることのメリットとしてはRails のvalidate を通すことができるということがあります。validate を通すことでデータに不整合があった場合に気づくことが可能です。システムが意図していない値でもSQL 上問題ない場合があります。例えば最初に紹介したメールアドレスの例もSQL としては問題がありませんが、システムとしておかしいです。validate が通らないということで気づくことが可能です。

おまけ2：複数のテーブルと連携する

例えば1 シートに1 テーブルという管理方法をとれば、複数のテーブルを1 ファイルで管理することが可能です。また、別シートも参照することが可能なので、関連するテーブルの値を参照することも可能です。これにより関連テーブルを簡単に再現することが可能です。