ストアドプロシージャについて質問を頂いたのでまとめ
試用版を公開しました
ストアドプロシージャ使った開発
まず、ストアドプロシージャを使うのは、UIとDBを完全に疎結合にすることを目的としています。勘違いされるのが多いのは、ストアドプロシージャはパフォーマンスが必要な複雑な処理で使うとか、そういう先入観があるからでしょうか。
UIがアクセスするのはストアドプロシージャのみです。
同じ処理があったとしても、必ず、UI側がアクセスする処理に対して1つストアドプロシージャを作り、他では利用しません。
UI用のストアドプロシージャが、処理用のストアドプロシージャ・ファンクションを呼び出す形で構築します。実際の処理を行うストアドプロシージャ・ファンクションは、パフォーマンスを考慮しながら、再利用できるようにある程度分割していきます。
参照
http://d.hatena.ne.jp/Sikushima/archive?word=%A5%C7%A5%C1%A5%E5%A1%BC%A5%F3
規模によっては、できればUI側がアクセスするユーザ(スキーマ)と実テーブル、実処理を行うストアドプロシージャを分けるのがよいでしょう。弊社ぐらいの規模だと、実際には全員が、UIも、ストアドプロシージャも扱うのでそこまではしないけれど。
UIとのインターフェース用のストアドプロシージャは
・アクセスログの管理(もちろん、自立トランザクションで処理)
・処理用ストアドプロシージャの呼び出し
・エラー処理
・コミット、ロールバック(弊社では、これはUI側で行うが)
以上のようなことを行います。
DBのユーザを分けていれば、Visual Studioや Java はUI側のユーザしか見せなければいい。見えないのですから、テーブルなど実DBオブジェクトを意識することなく、UIの要件を固め実装できるでしょう。完全な疎結合にできます。
UIの実装をDBの設計よりも先に行う
くどいですが、完全に疎結合になっているため、インターフェースさえ確定すればそれぞれ独立して開発、テスト(納品)が可能です。
オブジェクト指向言語とSQLは全く考え方が違うものですが、これまで、設計をする人、実装をする人という担当の分け方をしてきました。
オブジェクト指向言語を担当する人、SQLを担当する人、更にそれを設計・実装に分割しても良い。とにかく、考え方が違う言語を同じプロジェクトで使わねばならないのであれば、使用範囲を明確に分けてしまおう。というのが基本的な考え方で、ユーザの興味はUIに向きがちですから、UIから作る方が要件を固めるのも簡単になります。
基本、UIはアジャイルが向くと思います。アジャイルなんてと思われるかも知れませんが、要件定義・基本設計の間だって、紙の資料(電子化されていても紙の体裁の)が何度も往復しています。DB側のビジネスロジックを抜いて考えれば、紙の体裁にするよりも早くコーディング可能です。しかも、ユーザに分かりやすいので往復回数も抑えることができます。
アクセスログについて
例えば、testProcという文字型と、整数型の2つのパラメータを持ったストアドプロシージャがあったとしましょう。
Visual Studio側では
"exec testProc ?, ?"
または、
"exec testProc @Name, @Mode"
のようなSQLステートメントにパラメータをバインドして実行することになるでしょう。
このとき、ストアドプロシージャ(testProc)の最初の処理として、
exec testProc '入力値', 1;
とバインド変数をリテラルに戻してログに出力します。このログは、エンドユーザがどのような更新を行い参照を行ったかまで、比較的小さな容量で完全な形で残すことができます。
また、UI側を先に構築すれば、UI側が行ったユニットテストと全く同じテストを行うことが可能になります。
ツールについて
確かに、ストアドプロシージャ向けのツールは非常に少ないです。それだけユーザが少ないということを反映してのことでしょう。
デバッグツール(ステップ実行)としては、SQLServerはVisual Studio、または、Manegement Studio、Oracle は SI Object Browser や Visual Studio、Eclipseで、PostgreSQL は PGAdmin などで行うことが可能です。Visual Studioでは、.Net言語からシームレスにストアドプロシージャのステップ実行ができる。Eclipseでもプラグインを入れることでシームレスにデバッグできます。
言語との組合せによってはシームレスにデバッグとは行かないかも知れませんが、先ほど書いたようにログを出力する形にしておけば、ある程度、再現してテストすることも可能です。完全に疎結合になっていればUI側からシームレスにデバッグできなくても、そんなに困ることはありません。
私は必要性を感じないのですが、カバレッジツールやユニットテストのツールは自作するしかないでしょう。DBのオブジェクト(カタログ・ディクショナリ)情報は、基本的にSQLで取り出すことができます。
■SQLServer
http://msdn.microsoft.com/ja-jp/library/ms189783.aspx
■Oracle
http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05771-04/statviews_1.htm#118047
■PostgreSQL
http://www.postgresql.jp/document/current/html/catalogs.html
RDBMSではオブジェクトの情報もすべてテーブルに入っていますから、上の様な情報を元にSQLを組み立てればほとんどのツールは自作できます。
自作するのは、めんどくさいと思う人はご依頼ください(笑)。じゃなかった、適当なツールを購入すれば良いのです。
保守性も上がる
DB側のオブジェクトの依存関係はすべて、カタログ(ディクショナリ)情報にありますから、追いかけることができます。各種のツールからもクロスリファレンスとして出力が可能で、UIからの呼び出しに対して一対一の対応になるストアドプロシージャを用意していれば、完全な疎結合になっているのですから、シームレスなクロスリファレンスにならないことはそれほど大きな問題ではない。
一般的な構造で、例えば、O/Rマッパでテーブル・ビューに対して一対一の対応になっている場合では、UI側のクロスリファレンスは活きてくるでしょう。しかし、SQL文を書いたとき、そのSQL文に入っているテーブルも、ビューも、ユーザ定義関数なども、UI側のクロスリファレンスからも、DB側のクロスリファレンスからも漏れます。
つまり、SQL文を書かないと行けないプロジェクトでは、ストアドプロシージャにした方が圧倒的に保守性も上がります。
目指すところはエンドユーザコンピューティング(EUC)
現在の
コンサル → PM → PL → SE → PG
というヒエラルキーでは、基本的に、全員に言語的なスキルと、SQLの両方ができないと問題があります。現実的に両方を高いレベルで、全員ができることはないから、SQLを使わない方向へつまりデチューンの方向に進めたり、実装経験のない技術者がまかり通り、プロジェクトに悪影響を与えることになる。
しかし、完全に疎結合にし、それぞれを独立して開発することができれば
コンサルタント
フレームワーク担当者
デザイナー
UI設計者(UI実装者)
DB設計者(DB実装者)
インフラ系技術者
テスター
保守担当者
と専門性を持て人員配置することができる。技術系のキャリアパスも作ることが可能になります。
この中で、最も専門性の低いのがUI設計者・UI実装者です。弊社ではほぼ新人でも可能です。ここを担当するのはむしろ顧客の情報システム部が向いている。顧客側がやりたいことを自身で作るわけですから、コミュニケーションギャップなしに実現することが可能になります。アジャイルが向きます。
.NetやJavaの専門性というのは、フレームワーク担当者ぐらいしか必要なくなる。エンドユーザコンピューティングが進む。というのが私の理想です。
保守用のドキュメントはソースから機械的に作った方が正確でコストも安く付きます。
出来上がったUIはそのままDB設計者に対しての要件定義書であり、スタブ作成用のエクセルが詳細設計書になっています。後は、順序が逆ですが、基本設計にあたるテーブル設計を行えばよい。
すべてが良くなることはない。しかし、SIerが抱えている問題のかなりの問題を解決することが可能になります。
ご興味のある方は、http://www.g1sys.co.jp/stub/ スタブ作成マクロをまず買ってみてください。
個人的に
マイクロソフトさんでセミナーをやるから書くのですけれど(笑)
個人的に、新人にやらせた感じとして、EclipseよりもVisual Studioの方が分かりやすいようです。LinuxよりWindows、OracleよりSQL Server、が分かりやすいでしょう。
しかし、ただただ、使い易い、覚え易いでは、なかなか、LinuxやJavaの「ライセンス無料である」という印象を超えることは難しいと思います。しかし、ライセンス費なんてのは開発費、保守費に比べて微々たるものなのです。まあ、弊社も500万以下の案件ではLinuxでやるけどね。
ストアドプロシージャ中心の開発にすれば、確実に工数は下がります(慣れてないとそんなに下がらないかも知れませんが)ストアドプロシージャ中心の開発にすれば、Oracle、Javaを選択するよりも、SQLServer、.Netの方が圧倒的にメリットがあります。
まあ、SIerに嫌われる提案ではあるのですが、エンドユーザが変わったらSIerは必然的に変わりますから、エンドユーザを捕まえる戦略の方が、Windowsも、SQLServerも、Visual Studioも、数が出ると思うのですけどね(笑)
ストアドプロシージャの書き易さは埋めがたい差だと考えています。