SQL文を外だしするには？

###前提・実現したいこと
お世話になります。

SQL文をプログラム外部に配置することで、可読性・メンテナンス性を
向上させることができるようです。

参考にしたページ

SQL文をプログラム内のあちらこちらに書くのもどうしたものか
と考えていたので、
確かに参考ページのように外だしできれば、
どこに必要なSQL文があるか把握しやすいと思いました。

そこで今回の質問内容は、
「SQL文を外だしするのに利用できるフォーマットはどのようなものがあるか」
ということです。

以下ようなことができるフォーマットであれば、
SQL文を外だしして便利に扱えるのではないかと思っています。

1.コメントを書ける
// 商品一覧を取得する
sql001 = "select * from goods"

2.長いSQL文を改行しても一つの文として認識する

3.PHPの専用フォーマットでなく、別言語からも利用できる

4.SQL文に名前を付け識別・特定ができる
sql002 = "select * from users"

select_all_categories = "select * from categories"

5.言語機能やライブラリなどが存在していて簡単にフォーマットを利用できる

※私が「SQL文をプログラム内のあちらこちらに書くのをどうにかしたい」と
上で書いたので、回答者の皆様に「SQL文の共通化」のために外部化したいと
伝わってしまったようです。
むしろ逆で「複雑・使い回しできない特化したSQL文」を
どう管理するかがこの質問の理由・目的です。

誤解を招く質問内容で申し訳ありませんでした。

以下補足になります。

クエリビルダのような機能を利用して書くには少し大変な・書きにくい

問い合わせ処理を生のSQL文で書いてしまいたいという状況を想定しています。

簡単なSQL文というより、JOINやサブクエリなどが必要な「複雑・使い回しできない特化したSQL文」

を上手く管理したいというのが質問した理由です。

フォーマットの条件の

1(コメントを書ける)
2(join・サブクエリなどを利用すると長くなるので改行したい)
4(コメントと合わせて理解しやすいように重複しないユニークなエイリアスを付けたい。
および、iniファイルのようなname=value型の構造をイメージしていた)
は上記のような利用を想定していたからです。

XMLやJSONを見ましたが、SQL文を外だしするという用途には、少し使いにくいような気がします。
よろしくお願いいたします。

###補足情報(言語/FW/ツール等のバージョンなど)
PHP 5.5以降
SQL

行動規範の内容に同意します

回答5件

プログラミングは通常、適切な抽象化を行うことが品質の維持に直結していてそれを行うことが推奨されるんですが、
こと、SQLにおいては、それはアンチパターンになると考えています。

SQLをソースコードから切り離して1箇所に纏めようという試みは昔から行われていて、その代表的な物がJavaのDAO（Data Acess Object）パターンだったりするわけですが、そうしたアプローチを採用した場合に何が起こるのかを想定したシナリオを時系列順に記します。

###第1段階：実装開始

とても気持ちよく実装できます。きれいなコードを心掛けている人ほど楽しく、良いシステムを作っている実感がみなぎっています。

###第2段階：2カ月後

実装の過程で行われた仕様変更への対応や初期段階での実装の改善などの対応が出始めます。
この段階では実装者の記憶がまだ生きているため、とくに問題を感じずに修正が行われ、品質は維持されます。

###第3段階：3カ月後

開発も終盤に差し掛かりテストが行われる段階です。既に書き溜めたソースコードも膨大になってきて記憶だけでは賄いきれなくなっています。SQLストアの項目量も相当な数になっています。

運用テストの結果でNGとなった項目についての修正等が行われますが、少しずつメンテナンス性の低さが浮き彫りになり始めます。
まず、修正を行うためにソースコードの問題個所を特定するのですが、修正しようと思ったその場所に問題のSQLは存在しません。SQLは外だしで1箇所に纏められてしまっているからです。

この段階では納期のお尻も見え始め、時間に追われる事が多くなってきますが、SQLを外出しにした事による改修の難しさに気付きはじめます。

例えばDAOの様に単純なメソッド呼び出しになっているのであれば、IDEのコードジャンプ機能を利用して比較的楽に該当SQLの場所までは辿りつけるのですが、今回の命題のように汎用フォーマットを利用しているとそれも出来ません。
結果、都度“シンボルについてSQLのストアを検索して対象のSQLを探す作業”が必要になってきます。この不便さが改修を遅らせます。時間に追われている段階でのこの作業はストレスを増大させ、関係のないミスも増えるようになります。

また、この頃から既に、後述の問題も少しずつ露呈し始めます。

###第4段階：リリース（4ヶ月後）

お疲れ様です。後のメンテナンスのために、最終的なコードの整理とメンテナンス用のドキュメントを作成しましょう。
SQLストアについての仕様書も併せて作成します。

###第5段階：運用後の仕様変更、逐次バグ対応（6カ月後）

運用を行っていると「ここはこうした方が便利だ」「ここの動作はおかしい」といった改善要望が必ず出てきます。システムは作ることに意味があるのではなく、使うことに意味があるので当然なのですが、この時点で本当の問題が発覚してきます。

例えば、ある検索結果の表示項目を変更しなければならなくなったとします。この“処理Ａ”のために、まず、ソースコード上でその表示項目を取得している場所を特定しますが、前述したとおり、その場所にSQLクエリは存在しません。例によって、シンボルでクエリを検索により特定します。

クエリが特定できたのでその修正を行いたいところですが、ちょっと待ってください。本当にこれを修正しても良いのでしょうか？

コードの抽象化の目的の一つは“再利用”です。抽象化されているこのクエリは、実は、別の場所でも使われいる可能性があります。“処理Ａ”のためにDBからデータを取得しているクエリは同様に“処理Ｂ”や“処理Ｃ”のデータも取得しているかもしれません。

実際、安易にSQLをいじったために、後日、全く別の場所でのデグレードが発覚してその対応に追われたりします。

ですから、このクエリを変更するためには、今回の改修とは関係のない処理Ｂや処理Ｃについても動作テストの必要性が出てきます。

動作テスト以前に、その箇所の特定方法も問題になってきます。

開発から半年も経過しているので、既に記憶は頼りになりません。リリース時に作成したドキュメントがあるので“まだ”ある程度は頼りになるでしょう。

適切な改修を行ってテストを行い、本番環境にリリースします。ドキュメントも更新しておきましょう。

###第6段階：更なる継時的な改修（10ヶ月以降）

既に運用段階にあることから、当該システムのソースコードに携わる事は日常的では無くなっています。主に運用を行っていたり、あるいは別のシステムを作っていたりします。もはや、開発当時の記憶は残っていないに等しい状態です。

ですが、機能追加や改善要望はある日突然やってきます。

その度に当該システムのソースコードと再会することになりますが、今となってはほぼ、初見と変わりません。

例によって問題のソースコード箇所を特定しますが、そこにSQLはありません。シンボルを足掛かりにSQLストアを検索し、該当SQLを探り当てます。しかし、安易に改修をしてはいけません。他で使われているかもしれないからです。このため、影響範囲を調べます。

必ずしも影響範囲を調べる時間が与えられるとは限りません。外部的に見て些細な改修であれば、「ここ、ちょちょっと直してよ」という程度の改修と捉えられることもよくあることです。

こうなると、だんだん品質はなし崩しになっていきます。

一番簡単なのは、新規シンボルを起こして新たなSQLをストアする事です。これであれば絶対に他に影響が広がる事はありません。ドキュメントに新規SQLについての情報を追加しておけば問題ないでしょう。

こうして少しずつ、SQLストア内に不協和音が漂いはじめます。

###第7段階：崩壊期（1年～2年以降）

SQLストアを見てみましょう。新たな改修の度につぎ足されたSQLがあちこちに見受けられるようになっています。どこで使っているのかよくわからないSQL、それはおろか、使っているのかいないのかすらわからないSQLがあちこちに存在するようになります。この頃には、ドキュメントとの乖離も頻繁に見られるようになっています。

本来なら品質を維持するためにコードとドキュメントの整頓を行いたいところですが、そんな時間は与えられません。

忙しい時間の中で改修依頼が断続的にやってきて、結果、つぎはぎのSQLが追加されて行きます。ドキュメントはメンテナンスされなくなって、ほぼ、意味のない紙屑となります。

###第8段階：末期（3年）

当初綺麗だったコードは、今となっては見る影もありません。場当たり的に追加されたコード、メンテナンスされていないドキュメントが、システム担当者を苦しめます。

場合によっては、開発当初のシステム担当者は既に存在しません。嫌気がさして辞めてしまいました。

あらたな担当者のあなたは、この燦燦たる荒野を、空の星だけを頼りに毎日さまよいます。

今日も追加改修依頼が来ました。

まったくあてにならないドキュメントからなんとか該当ソースコードを探り当て、そこで見つけたシンボルからSQLストアを検索して対象SQLクエリにたどり着いて、やっと改修の目途を立てます。

しかし、安易に改修してはいけません。他の処理でも共通のクエリが利用されているかもしれないからです。

どうしましょう？　あなたに与えられた時間はそれほど多くはありません。ゆっくり影響範囲を調べている時間もありません。

あなたはおもむろに、新たなシンボルを追加し、元のSQLクエリをコピーペーストしてから今回の修正対応を行うことにしました。これなら他に影響が出ることは絶対にありません。ドキュメントの更改も行う必要はありません。だって、あなたがこの職に就いた時点でもうとっくに現状と乖離していたんですから。

手早く改修を終えたあなたは、壮大なごみクズの山となったSQLストアファイルを閉じてコーヒーを飲んでからため息をつきます。

『この仕様考えた奴、死ねばいいのに…』

投稿2018/03/24 12:51

退会済みユーザー

総合スコア0

質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう！

追記

追記2

関連した質問