こんにちは。インストラクターの蓑島です。

前回まで、PL/SQLを使ったWEBアプリの作成について基本的な解説をしてきました。
今回からは応用編ということで、ある程度実用的なWEBアプリを作成したいと思います。すなわち「実用WEBアプリ」シリーズです。
まずはタイトルにあるようにこれから数回にわたり、文書管理システムを構築してみます。

このシステムは大変簡単な仕組みですが便利で役に立ちます。本メルマガを読んでぜひお手元の環境に構築してみてください。

このシステムは以下のような概要です。

1. ブラウザを通して文章やディレクトリをデータベースに登録・更新できる。
2. ブラウザでその文章を表示できる。
3. ブラウザで、キーワードによる、文章の検索ができる。

皆さんが普段ご利用のWEB上のサービスでこれと似た機能はよくあると思いますが、それをオラクルデータベースとPL/SQL言語だけで実装してみるわけです。
今回は概要説明ですが、次回と次々回くらいで、とりあえず最低限の使える状態（文書登録から表示）まで実装したいと考えています。それ以降は、少しずつ機能を追加し利便性を増しながら解説していきたいと思います。

ということで、これから新規のアプリケーションを構築するわけですが、まず最初になすべきことは、いきなりプログラミングではなく「設計」です。「設計」は本メルマガのメインテーマではないのですが重要です。
「設計」には主に「データ」の設計と「プロセス」の設計があります。表（テーブル）の数が多い大規模なシステムであるほど、「データ」の設計は重要になります。
データの設計とは、簡単に言えばデータベースにどのような表を持たせるかという設計であり、「データベース設計」ともいます。
これに対して、「プロセス」の設計はアプリケーションのプログラミングロジックに最終的に反映される設計です。
どちらの設計を優先的に行うべきかというと、「データ」の設計の方です。もし「プロセス」の設計を優先し、プロセスに合わせて「データ」を設計するとそのデータは必ずしも「正規化」されておらず、後々、根本的な矛盾が発覚するなど大きな問題になることがあります。あるいは本番稼働後、蓄積されたデータを分析し有効活用などする際に、そのような有効活用が難しくなるかもしれません。

なお、正規化されたデータベースが、データベース設計の最終目標ではありません。例えばデータ量の多いシステムの場合、正規化だけを目標に設計を行うと、パフォーマンス上の問題を引き起こすことがあります。詳細は説明しませんが、実際のデータベース設計は正規化した後で、パフォーマンス要件も視野にいれてさらに進んでいきます。
（概念設計→論理設計→物理設計）

では今回、これから作成しようとしている、文書管理システムのデータの設計とはどのようなものでしょうか？
このシステムはとても単純なので、それほど神経質に構えて考える必要はないのですが、それでも、いくつか考慮事項があります。
今回の場合、「文書」と「ディレクトリ」の関係をどのようにとらえるかにより、考え方がいろいろとあります。
例えば、それらを別個のものととらえて「文書」表と「ディレクトリ」表というように、それぞれ別の表を用意するという考え方ができます。あるいは、ディレクトリと、文章はどちらも「親ディレクトリ」を持つという共通点があります。
ですからディレクトリを本文のない特殊な文書ととらえて、「文書」表だけでディレクトリと文書を管理するという考え方も可能です。しかし「ディレクトリ」と「文書」とはまったく違う外観なので別物では？と思われるかもしれませんが、それはアプリケーションプログラムがそのような外観で表示しているだけであり、それにとらわれて考える必要はないのです。

今回は後者、つまり、「ディレクトリを本文のない特殊な文書ととらえて、ディレクトリも文書も同じ文書表で管理する」という考え方でいきたいと思います。
ではその文書表ですが、以下のような表定義で作成しました。

SQL> SHOW USER
ユーザーは"SCOTT"です。

まず、この表の1行1行が、一つの文章、またはディレクトリに対応します。その区別は、KBN列で行っているわけです。
ここで文書とかディレクトリといっても、皆さんがPCで普段作成しているファイルの文書とかディレクトリのことではなく、あくまでもデータベース内に格納された情報としての（いわば仮想的な）文書やディレクトリです。それらの情報をPL/SQLで作成したWEBアプリケーションによってブラウザ上でそれらしく表示するわけです。

上記コードのコメントにあるように、区分(KBN列)の値が1であれば、文書を表し、2であれば、ディレクトリを表すとします。
CHECK制約(コード4行目)で、KBN列に格納できる値は1か2のみと制限します。また、1列目がID列(コード2行目)ですが、この文書あるいはディレクトリの主キーの番号です。つまりこの番号が決まれば、文書またはディレクトリが特定できるわけです。
また、OYA_DIR列(コード6行目)は、親ディレクトリのIDを表しますが、外部キー制約(REFERENCES)がついています。外部キー制約は通常は他の表の主キー列を参照するのですが、ここでは「REFERENCES DOCS(ID)」とあるように、自分の表（DOCS表）の主キー列(ID)を参照します。
なお参考までに、このように自分の表を参照する外部キーを再帰的な外部キーといいますが、再帰的な外部キー列には、NOT NULLの指定はできません。
例えば、最上位のディレクトリの親ディレクトリは存在しません。その時、親ディレクトリの番号(OYA_DIR)は NULLです。このように、再帰的な外部キーはNULLを許容する設定が必要です。ですからNOT NULL の指定はできないのです。実際に、再帰的外部キー列にNOT NULL を指定するとCREATE TABLE文がエラーになります。

しかしディレクトリではなく、文書の場合については、必ず親ディレクトリを持たせると考えることもできます。そのように考えるのであれば、文書の場合(KBN=1)については、親ディレクトリ(OYA_DIR)に値があること(NOT NULL)を保証しなければなりません。
そこで、以下のようなCHECK制約により、そのことを保証します。

これにより、文書(KBN=1)の場合は、必ず、OYA_DIRに値があること(OYA_DIR IS NOT NULL)をシステム的にチェックするので、文書は必ず親ディレクトリを持つことを保証できます。
なお、ディレクトリ(KBN=2)の場合はOR条件なので、それだけで条件を満たします。よってディレクトリ(KBN=2)の場合は親ディレクトリがあってもなくても構いません。

その他、大事な列としては、本文、つまりHONBUN列(コード5行目)です。
ここに文書の本文が格納されますので、この列は大きなテキストデータです。データ型はCLOBにしました。なお、上述したように、ディレクトリは本文のない特別な文書と考えています。よって、ディレクトリ（KBN=2)の場合は、本文(HONBUN列)はNULLでなければならないわけです。そこで、以下のようなCHECK制約によりそのことを保証します。

これにより、ディレクトリ(KBN=2)の場合は本文を持たない(HONBUN IS NULL)ことが保証できるわけです。
なお、文書(KBN=1)の場合はOR条件なので、それだけで条件を満たします。よって本文(HONBUN)はあってもなくても構いません。

このような表定義により、文書の場合とディレクトリの場合のデータ登録上の制限の違いをデータベース上でチェックできるので、これらの制限に違反するデータの登録を確実に防止できます。
これにより、プログラムロジックにそのようなチェックを記述する必要がないので、アプリケーションを単純化できます。またその動作も確実なものになります。

では今回は、ここまでとします。
次回はこの文書表にディレクトリや文書を登録する処理を実装してみましょう。
ご期待ください。