SQLをテストする方法がよく分からなくなったこと
PG中のSQLにバグがないことを検証する単体テストをしろって言われたんだけど、テストの設計はあまりやった事がないのでちょっと困った・・・
<テストの条件>
1.PGの機能は、元のデータベース(A)からSQLで取得したデータを
別のデータベース(B)のテーブルに書き込む
というもの
2.SQLは複数のテーブルを結合していて
それぞれにWHERE文で抽出条件を指定していて、やや複雑
3.テスト内容は、下記の形でPGの正当性を示せ、という物
①「PGを実行すると、データベースAからはN件のデータが取得されるはず」
②そして実際に実行してみると
「データベースBにN件のデータが追加された」
③よってこのSQLは正しい
4.データベースAには本番データをもとに作ったテストデータが現在入っているが
このデータは参照のみ許可されており、削除・編集してはならない
データベースBのデータは好きにいじってよい(全部空にしちゃってもよい)
<考えた事>
データベースAの特定のデータ2,3件を抽出して
「このデータはBに書き込まれる」「このデータは書き込まれない」というのを
検証するのは簡単だ。私も今まではそういう風にテストしていた。
しかし、今回の指示はそう簡単ではない。
データベースAをのデータを一つ一つ見て行って
「このデータはBに書き込まれる」「このデータは書き込まれない」と調べていけば
原理的には検証可能だが、そんな全数調査できるほど小規模なデータ数ではない。
SQL中に出てくる各抽出条件すべてを総ナメするように
テストパターンを機械的に作って、それをデータベースAに入れたうえで
テストすれば、「N件出てくるはず」というテストはできるだろうが
今回の場合、データベースAの中身はいじるなと言われているのでそれはできない
かといって、他にどういう方法をとったとしても結局
① PG中で実行しているSQLを実行する→N件取得される
② PGを実際に実行する→N件取得される
①と②の件数が一致したのでテスト成功です!
という「お前、それ同じSQL流してるんだから当たり前じゃん」という
テストと等しい内容のテストしか思いつかなくて行き詰ってしまいました
<知りたい事>
1)何か上記の条件を満たすいいテスト方法はないでしょうか
2)そもそも、どんなテストも厳密に完全にPGの正当性を検証することはできない
(同じPGを二回作って検証することになってしまう)
と思うので、自分のアプローチ(もしくは上司からの指示)がそもそも間違っている気がします。
この状況で全件数モレなく取得することを検証するテストを考えるのが無駄であるならば
せめて、一般的にSQLの正しさを検証するテストにおける頻出チェックポイント
みたいなものがあったら教えてください。
【SQL Server】固定長文字列と可変長文字列の比較について
今日DBをいじっていて気付いた事があったのでメモ。
【概要】
1) 固定長文字列変数に桁数いっぱいの文字列を入れる
2) 可変長文字列変数に①と同じ文字列を格納する
1)と2)を比較演算子で比べると結果はFalseとなる
【環境】
SQL Server 2008 R2
【説明】
<コード例1>
DECLARE @hoge as varchar
SET @hoge = ‘A’
と
DECLARE @fuga as char(3)
SET @fuga = ‘A’
であれば比較すると@hoge≠@fugaになるのは分かる。
Varcharは可変長でcharは固定長だから、上記の場合の変数@fugaには
‘A’の後に空白が2個付け足されて格納されるから、明らかに@hogeとは別物になるからだ。
しかし、次の場合はどうなると思います?
<コード例2>
DECLARE @hoge as varchar
SET @hoge = ‘ABC’
と
DECLARE @fuga as char(3)
SET @fuga = ‘ABC’
この場合は@fugaは文字列長最大まで文字が入力されているので、<コード例1>の場合と違って隙間を埋めるための空白は追加されない。だから@hoge=@fugaになるんじゃないかなーといい加減な考えでコードを書いていたら原因不明のエラーを生んでしまって原因究明まで結構な時間を無駄にしてしまった。
そう、実際には、<コード例2>の場合も@hoge≠@fugaになる。
やっぱり型の違いには気を付けないといけないよね…という話。
※後でよくよく調べてみた。
MSDNを参照すると
「varcharの格納サイズは、入力したデータの実際の長さ + 2 バイトとなります」
って書いてある…
追加の2バイトがあるんだからcharとvarcharでは同じ文字列でも確保される領域サイズは違うんじゃないですか!
知らなかった!恥ずかしい!
参考:char および varchar (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms176089(v=SQL.120).aspx
最近開発で困っているちょっとした事1
タイトルの通りです。開発言語はいずれもVB.NET
●ヌル参照エラーが発生した場合、どの変数がヌルだったのか正確にわからない
デバッグ中に次のエラーが出たとします。
「NullReferenceException: オブジェクト参照がオブジェクト インスタンスに設定されていません」
ソースコードのどの行でヌル参照エラーが起きたのかまではエラー情報からわかりますし、デバッグ実行中ならどの行で実行が止まったのか見ればすぐわかります。
しかし、その行で複数の変数が使用されていた場合、どの変数がヌルだったのが原因でエラーになったのかは例外情報を見てもわかりません。
何か確認する方法はあるのでしょうか・・・(それとも私が例外情報の見方を正しく理解していないだけで、本当は例外情報を読めばわかるのかなあ?)
●あるプログラムAが別のプログラムBを呼び出している場合のBのデバッグについて
プログラムA.exeはAの処理の内部で、プログラムB.exeを起動しています。
そのため、Aをデバッグ実行した場合は、Aのソースコードのどこかの行でブレークすることはできますが
Aから呼び出されたBのソースコードのどこかの行でブレークしたいと思っても
ブレークで止めることはできません。Bの処理はデバッグ実行とは無関係で、普通にBを起動したときと同じように動作しているのと同じだからです。
「じゃあ、AじゃなくてBをデバッグ実行で起動したらいいんじゃないか?」とおっしゃる方は当然いるでしょうが、自分が担当したプログラムではそれもできないのです。なぜなら、AはBを起動するときにコマンドライン引数で、とある変数を渡して起動しているからです。その変数を受け取ってからでないと Bは正しく動作しないので、B単独でデバッグ実行しても、適切な検証ができないのです。
何かうまい方法はないものか・・・
●Visual Studio でクラス図をもっともーっと実用的にしたい
筆者の使用環境:Visual Studio 2010
Visual Studioにはクラス図生成機能がありますが、これ、ぶっちゃけクラス図と呼ぶのもおこがましいしょぼい図しか生成できません。
一番足りないと思うのは、「クラスAはクラスBを参照しているという”関連”を線で図示する機能」です。
今開発してるプログラムで、前任者から引き継いだコードを読み解くのに苦労しています。
「どのクラスがどのクラスを使用しているのか、ソースコードからクラス図を一発で生成できたら便利なのになあ」
とよく思います。(まあ、すべてのクラス間の関連が線で引かれたらそれはそれで見づらい図になったりするんですが・・・)
しかし、Visual Studioのかゆい所に手が届かない部分を補強するアドインを見つけました!こちらのModelingPowerToysってやつです!↓
でも残念なことに・・・これでもまだ私の目的は満たされなかった・・・何がまずいかというと、このツールは・・・「クラスAはクラスBのリストをフィールドとして持つが、クラスAがクラスBのオブジェクトを直接持っているわけではない」場合には関連の線が引かれないようなのです!!
今私が解析したいプログラムは、あるクラスのフィールドが別クラスのリストであることが多いので、これでは役に立たない・・・がんばっていろいろ漁ったのに調べた時間が無駄になってしまった・・・
WHERE句とON句、どちらに条件を記述するかによってSQLの結果は変わるのか
WHERE句に条件を記述した場合と、ON句に条件を記述した場合とで
結果はどうなるのか気になったので実験しました。
まずはWHERE句を使用する場合を試しましょう。
上記のテーブルに対し、次のSQL文1を実行します。
<SQL文1>
SELECT
W.WORKER_NAME,
W.JOIN_DATE,
SECTION_NAME
FROM MST_WORKER AS W
JOIN MST_SECTION AS S
ON W.SECTION_CODE=S.SECTION_CODE
WHERE W.JOIN_DATE = '2015-04-01'
SQL文1の結果
次にON句を使用する場合です。
<SQL文2>
SELECT
W.WORKER_NAME,
W.JOIN_DATE,
SECTION_NAME
FROM MST_WORKER AS W
JOIN MST_SECTION AS S
ON W.SECTION_CODE=S.SECTION_CODE
AND W.JOIN_DATE = '2015-04-01'
SQL文2の結果
SQL文1の結果とまったく同じである事が分かります。
以前、私は<SQL文1>の書き方しか知らなかったのですが
<SQL文2>の書き方もできる事に気づいて「あれ?こんな書き方してもいいのかな?」と思い、
この実験を行ってみた次第です。
さて、SQL文1もSQL文2も表示される結果は同じという事は
条件を記述するのはWHERE句、ON句どちらでもよいのでしょうか?
実はそうではありません。
今までは内部結合の例でしたが、今度は外部結合で同じ事を試してみます。
下記のSQL文を実行してみましょう。
<SQL文3>
SELECT
W.WORKER_NAME,
W.JOIN_DATE,
SECTION_NAME
FROM MST_WORKER AS W
LEFT JOIN MST_SECTION AS S
ON W.SECTION_CODE=S.SECTION_CODE
AND W.JOIN_DATE = '2015-04-01'
SQL文3の結果
結果が変わりましたね!
左外部結合の場合はON句に指定された条件を全て満たす行が存在しない場合
左のテーブル(この場合はMST_WORKER)の行に対し、NULL行を結合するのでこういう動きになります。
(右外部結合の場合は逆に、右のテーブルの行に対してNULL行を結合します。)
結論として、外部結合の場合はON句とWHERE句、どちらに条件を書くかで結果が変わる事が分かりました。
じゃあ、内部結合の場合はON句に条件記述する<SQL文2>のやり方をしてもいいのか?という疑問がわいてきますが
それについては、また今度時間があれば考察してみたいと思います。
意見があればコメントください。
ついでに、WHERE句に書いた場合の動作も載せておきます。
<SQL文4>
SELECT
W.WORKER_NAME,
W.JOIN_DATE,
SECTION_NAME
FROM MST_WORKER AS W
LEFT JOIN MST_SECTION AS S
ON W.SECTION_CODE=S.SECTION_CODE
WHERE W.JOIN_DATE = '2015-04-01'
SQL文4の結果
SQL文1,2と同じ結果ですね。SECTION_CODEを結合キーとして
外部結合した後、WHERE句で絞り込みをかける為、SQL文3とは結果が変わります。