e-learning、オラクル研修、LMS(学習管理システム)のiStudy

e-learning、オラクル研修、LMS(学習管理システム)のiStudy

第127回「実用WEBアプリ 文書管理システム(8)ディレクトリの循環を防ぐ」

2015.04.30

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

前回は「更新」がテーマでしたね。文書やディレクトリの更新です。登録した文書の内容を変更したり、ディレクトリの名前を変えたりできます。

変更できる項目の中に「親ディレクトリのID」があります。これはその文書やディレクトリの所属先の親ディレクトリですね。そして、この項目を変更するときには大変注意しなければならないことがあります。それは「ディレクトリの循環」を防ぐということです。これが今回のテーマです。

「ディレクトリの循環」とは、そのディレクトリのサブディレクトリ(深さは問いません)が、親ディレクトリとなる状況をいいます。たとえ、DOCS表のOYA_DIR列(親ディレクトリのID)が、ID列(主キー)を参照する再帰的な外部キー制約が定義(バックナンバー第120回)してあっても、それだけだは「循環」を防ぐことはできません。循環を防ぐにはプログラムでチェックする必要があります。

そこで、今回はいきなり前回の更新処理プロシージャを修正するのではなく、まず親ディレクトリの妥当性をチェックするファンクションを先に作成しましょう。そして、次回で、更新処理プロシージャ内で、そのファンクションをコールして、親ディレクトリの妥当性をチェックするように修正します。
なぜ、更新処理プロシージャ内でチェックせずに、別のファンクションを作りそこでチェックするのかというと、同様のチェックを違うケースで行う予定だからです。具体的には、すこし後の回で、文書やディレクトリを複数選択して別の親ディレクトリに一括で移動させる処理を実装しようと考えています。その時にも同様のチェックが必要になるので、別プログラムとしてファンクション化しておくわけです。

「親ディレクトリのID」を更新するときのチェックはディレクトリの循環を防ぐチェックだけではありません。細かい点を含めれば他にもいろいろとあります。

1. 文書(KBN=1)の場合は、親ディレクトリ(OYA_ID列)がNULLであってはいけない
2. 親ディレクトリは文書(KBN=1)ではなく、ディレクトリ(KBN=2)でなければならない
3. ディレクトリ(KBN=2)の場合は、循環してはいけない(上記で説明ずみ)

ではこのようなチェックを行うファンクションをこれから作成します。

その前にいつものように、文書やディレクトリの情報を格納するDOCS表の定義を掲載します。この定義を参照しながらファンクションのロジックを確認してください。

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

1
2
3
4
5
6
7
8
9
10
11
SQL> DESC DOCS
  名前                                      NULL ?    型
  ----------------------------------------- -------- ----------------------------
  ID                                        NOT NULL NUMBER           --番号(主キー)
  TITLE                                     NOT NULL VARCHAR2(300)    --タイトル
  KBN                                       NOT NULL NUMBER           --区分1:文書 2:ディレクトリ
  HONBUN                                             CLOB             --本文(文書のときのみセット)
  OYA_DIR                                            NUMBER           --親ディレクトリ
  SEQ                                                NUMBER           --ディレクトリ内の順序
  INSERT_DATE                               NOT NULL DATE             --登録日
  UPDATE_DATE                               NOT NULL DATE             --更新日

以下は今回作成したファンクションです。名前をDOC_FUNC_OYA_CHECKにしました。このファンクションは戻り値がBOOLEAN型(TRUE/FALSE)です。チェックがOKの場合は、TRUEを返すようにします。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
CREATE OR REPLACE FUNCTION DOC_FUNC_OYA_CHECK
( P_ID  IN  DOCS.ID%TYPE,       -- 指定された番号
   P_OYA IN  DOCS.OYA_DIR%TYPE)  -- 親の番号
RETURN BOOLEAN    -- チェックOKの時は TRUEを返す。NGの時はエラーを発生させる
/***************************************************************************/
/**  親ディレクトリIDの妥当性をチェックするファンクション                  */
/***************************************************************************/
IS
/********************/
/* 宣言部           */
/********************/
V_KBN  DOCS.KBN%TYPE;      -- 指定された番号の区分(1:文書 2:ディレクトリ)
V_OYA_KBN  DOCS.KBN%TYPE;  -- 親の番号の区分(1:文書 2:ディレクトリ)
--
/** サブディレクトリのIDを格納するコレクション **/
-- 型の宣言
TYPE TAB_SUBDIR_TYPE IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
-- 変数の宣言
TAB_SUBDIR TAB_SUBDIR_TYPE;
/********************/
/* 実行部           */
/********************/
BEGIN
/**  指定された番号(P_ID)の区分を取得(1:文書 2:ディレクトリ) **/
    SELECT KBN INTO V_KBN FROM DOCS WHERE ID =  P_ID;
--
/********************************************/
/** 親の番号(P_OYA)が NULL のときのチェック  **/
/********************************************/
    IF P_OYA IS NULL THEN 
       IF V_KBN = 1 THEN  -- 指定された番号が文書ならNG
          RAISE_APPLICATION_ERROR(-20000,
                                '親ディレクトリのない文書は許可できません' );
       ELSE   -- 指定された番号がディレクトならOK (最上位ディレクトリのケース)
         RETURN TRUE --チェックOKで処理終わり
       END IF;
    END IF;
/******************************************************/
/**  以下は親の番号(P_OYA)が NULL でないときのチェック **/
/******************************************************/
--  そもそもその親の番号はディレクトリか?
     SELECT KBN INTO V_OYA_KBN FROM DOCS WHERE ID = P_OYA;
     IF V_OYA_KBN <> 2 THEN
        RAISE_APPLICATION_ERROR(-20001, 'P_OYA=' || TO_CHAR(P_OYA)
                                      || 'がディレクトリでありません' );
     END IF;
--
/**  以下は親の番号がディレクトリのとき **/
    IF V_KBN = 1 THEN  -- 指定された番号が文書のとき
       RETURN TRUE ;   --チェックOKで処理終わり
    END IF;
/*******************************************************************/
/* 以下は親の番号(P_OYA)と指定された番号(P_ID)がディレクトリのとき */
/*******************************************************************/
-- サブディレクトリのIDをコレクションに取得
    FOR REC IN ( SELECT ID FROM DOCS
                 WHERE KBN = 2
                 START WITH ID = P_ID
                 CONNECT BY PRIOR ID = OYA_DIR)  LOOP
        TAB_SUBDIR(REC.ID) := 'DUMMY' ;
    END LOOP;
-- 親の番号(P_OYA)がサブディレクトリに存在すればエラーとする
    IF TAB_SUBDIR.EXISTS(P_OYA) THEN
       RAISE_APPLICATION_ERROR(-20002,
       'ディレクトリが循環します P_ID=' || TO_CHAR(P_ID) ||
       ' P_OYA=' || TO_CHAR(P_OYA));
    ELSE
       RETURN TRUE ;   -- サブディレクトリに存在しなければチェックOK
    END IF;
END DOC_FUNC_OYA_CHECK;
/
 
ファンクションが作成されました。

ではこのソースコードの重要な部分について解説します。

最初に引数(2~3行目)ですが、P_IDとP_OYAです。コメントにもあるように、P_IDは対象となるディレクトリや文書のIDです。P_OYAはそれらのディレクトリや文書の所属する(予定)の親ディレクトリのIDです。そのP_IDにとって、そのP_OYAが妥当なものなのかどうかチェックするわけです。

次に実行部をみていくと、最初にP_IDが、文書なのかディレクトリなのかを判断するために、区分を取得しています(26行目 V_KBN)

続いて、31-38行目のIF文をみてください。ここでは、親ディレクトリが指定されていなとき(31行目 IF P_OYA IS NULL)、文書(V_KBN=1)の場合はエラー(33-34行目)であり、逆にディレクトリの場合はチェックOKであることが記述されています。つまり文書の場合は必ず親ディレクトリが必要ですが、ディレクトリの場合は親ディレクトリがなくてもよい(最上位ディレクトリ)ということです。なお、チェックがOKであれば、RETURN TRUE;(36行目) を実行します。ファンクションは値をRETURNすることが目的なので、36行目を実行すればここでファンクションの処理が終了します。

それ以下(42行目以下)の処理は親ディレクトリが指定されている場合です。ここで、その親ディレクトリのIDが本当にディレクトリの番号なのかどうかをチェックします。(44-47行目) もし、親ディレクトリに、文書のIDを指定することができてしまうと、その文書の下に子供たち(文書やサブディレクトリ)が存在することになり、おかしな状況となります。子供を持てるのはディレクトリだけにしたいので、チェックしているわけです。

49行目以下は、親ディレクトリのIDが間違いなく、文書でなくディレクトリである場合です。そのような状況で、対象となっている指定された番号が文書(V_KBN=1)であるときは、チェックOK(51行目)とします。文書の場合は、親ディレクトリに正しくディレクトリのIDがセットされていれば、OKです。文書は子供をもつことがない(親にならない)ので、循環することもありません。

しかし、ディレクトリの場合は循環することもありえるので、さらにチェックが必要です。そのチェックが53行目以下です。

そのチェックのためには、該当ディレクトリの下のサブディレクトリのIDをすべて取得する必要があります。そのために、57-62行目のカーソルFORループ文の処理があります。この文の57-60行目のSELECT文ですが、階層問い合わせです。階層問い合わせは、一つの表のなかに、親ディレクトリ、サブディレクトリのような階層関係があるときに階層構造(親、子、孫・・・)を問い合すものです。(バックナンバー第79回 再帰コールで階層問い合わせをしてみるを参照)

この階層問い合わせで、P_ID以下(59行目の START WITH ID = P_ID)のサブディレクトリ(58行目 WHERE KBN=2)のIDを一番最後の階層まですべてコレクション変数に取得します。このコレクション変数は15-20行目で宣言しています。

ここで、61行目の処理( TAB_SUBDIR(REC.ID) := 'DUMMY'; )が解りにくいと思います。
'DUMMY'を代入する意味は、コレクション変数に代入する値そのものには意味がないということを強調したものです。大事なのは、コレクション変数の添え字であるREC.IDです。ここでのコレクションはPL/SQL表(あるいは、索引付表、結合配列ともいう)です。PL/SQL表の添え字は連続番号である必要はありません。したがって、サブディレクトリのIDを添え字にしています。結果として、このPL/SQL表の添え字にサブディレクトリのIDが保持されます。

そして最後に、親ディレクトリのID(P_OYA)が、PL/SQL表の添え字(=サブディレクトリのID)に存在していれば、ディレクトリが循環すると判断できるので、エラーにします(64-70行目のIF文)。
このIF文の64行目のTAB_SUBDIR.EXISTS(P_OYA)という記述ですが、この「EXISTS」とは、コレクションに事前定義されたメソッドであり、添え字(キーともいう)の存在チェックです。つまり、P_OYAの値が、コレクションの添え字(キー)に存在していれば、ディレクトリが循環すると判断できるので、エラーにしている(65行目のRAISE_APPLICATION_ERROR)わけです。なお、「EXISTS」メソッドについては、バックナンバー第15回「結合配列のキーの存在チェック」に解説がありますので、詳細を知りたい方は参照してください。

以上が今回作成したDOC_FUNC_OYA_CHECKファンクションの処理概要です。
なお、このファンクションはBOOLEAN型(TRUE/FALSE)であり、チェックがOKであるときは、TRUEを返しますが、チェックがNGの時はFALSEを返すのでなく、エラー(RAISE_APPLICATION_ERROR)にしています。なぜチェックがNGのときにFALSEでなく、エラーにするかというとこのファンクションの呼び出し元に、チェックがNGの情報を渡したいからです。もし、チェックがNGのときにFALSEを返す仕様ですと、呼び出し元からすると、チェックがNGであったという結果しかわかりません。しかしRAISE_APPLICA_ERRORでエラーにすれば、呼び出し元にシステムエラー番号が返りますので、そのエラー番号からエラーの理由を知らせることができます。今回は以下の3種類のユーザ定義システムエラーが発生します。

ORA-20000 親ディレクトリのない文書は許可できません (33行目)
ORA-20001 P_OYA=nnnがディレクトリではありません (45行目)
ORA-20002 ディレクトリが循環します         (65行目)

このように、FALSEを返すのでなく、システムエラーにすれば、システムエラー番号をもとに、チェックがNGの理由を呼び出しもとに返すことができるのです。

では、このファンクションを早速、検証してみましょう。
以下の階層問い合わせをご覧ください。これは私の環境で、親ディレクトリ、文章、サブディレクトリの階層構造をなるべく立体的にわかりやすく表示したものです。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT LPAD( ' ' ,5*( LEVEL -1), ' ' ) ||
       'LEVEL=' || LEVEL || ' ' ||
       'ID=' ||
       TRIM(TO_CHAR(ID, '00' )) || ' ' ||
      'KBN=' ||KBN || ' ' ||
      'OYA_DIR=' || TRIM(TO_CHAR(OYA_DIR, '00' ))
AS "階層問い合わせの結果"
FROM   DOCS
START WITH OYA_DIR IS NULL
CONNECT BY PRIOR ID = OYA_DIR
/
 
階層問い合わせの結果
-------------------------------------------------------------------
LEVEL =1 ID=01 KBN=2 OYA_DIR=
      LEVEL =2 ID=02 KBN=2 OYA_DIR=01
           LEVEL =3 ID=13 KBN=2 OYA_DIR=02
                LEVEL =4 ID=14 KBN=1 OYA_DIR=13
      LEVEL =2 ID=03 KBN=1 OYA_DIR=01
      LEVEL =2 ID=04 KBN=2 OYA_DIR=01
           LEVEL =3 ID=05 KBN=1 OYA_DIR=04
           LEVEL =3 ID=07 KBN=1 OYA_DIR=04
           LEVEL =3 ID=09 KBN=1 OYA_DIR=04
           LEVEL =3 ID=10 KBN=2 OYA_DIR=04
                LEVEL =4 ID=11 KBN=2 OYA_DIR=10
LEVEL =1 ID=08 KBN=2 OYA_DIR=
      LEVEL =2 ID=12 KBN=2 OYA_DIR=08
 
13行が選択されました。

ここでこの階層問い合わせは最上位ディレクトリ(OYA_DIR IS NULL)を起点にしています。(9行目 START WITH句)
そして、13行が問い合わせされました(29行目)。つまり私の環境ではDOCS表に全部で13行があります。今回この検証のために、事前にいくつかディレクトリと文書を登録しておきました。

ここでLEVEL=1は最上位の階層なので、すべてOYA_DIRの値がありません(15行目、26行目 OYA_DIRに値がない)
例えば、20行目(ID=04)はLEVEL=2ですから、LEVEL=1である15行目(ID=01)の子供です。そして、20行目は同時にディレクトリです(KBN=2)。そして、25行目(ID=11)もディレクトリ(KBN=2)です。同時に、25行目は20行目(ID=04)の子供の子供のサブディレクトリ、つまり孫であることがわかります。ということは、20行目(ID=02)のOYA_DIRを11番にすると、ディレクトリが循環することになります。つまりID=02のOYA_DIRを11にすると、ディレクトリが循環します。

ではこの条件でDOC_FUNC_OYA_CHECKファンクションをコールして正しく動作するか、検証してみましょう。

以下の例をご覧ください。

SQL> SET SERVEROUTPUT ON -- 画面表示有効

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN
    IF  DOC_FUNC_OYA_CHECK(04,11) THEN
        DBMS_OUTPUT.PUT_LINE( 'TRUE' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( 'FALSE' );
    END IF;
END ;
/
 
BEGIN
*
行1でエラーが発生しました。:
ORA-20002: ディレクトリが循環します P_ID=4 P_OYA=11
ORA-06512: "SCOTT.DOC_FUNC_OYA_CHECK" , 行65
ORA-06512: 行2

ご覧のように、ID=4で親が11の場合はディレクトリが循環することをファンクションが検出し、ORA-200021のエラーとなりました。正しく動作したわけです。

ではID=04の行にとって、ID=12の行(階層問い合わせの最後の行)はサブディレクトリではありません。この場合はディレクトリが循環しないので、妥当です。この条件でファンクションをコールし、正しく動作するか検証してみましょう。

1
2
3
4
5
6
7
8
9
10
11
12
BEGIN
    IF  DOC_FUNC_OYA_CHECK(04,12) THEN
        DBMS_OUTPUT.PUT_LINE( 'TRUE' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( 'FALSE' );
    END IF;
END ;
/
 
TRUE
 
PL/SQLプロシージャが正常に完了しました。

ご覧のように、ファンクションがチェックOKと判断したので、TRUEを返し、その結果DBMS_OUTPUT.PUT_LINE('TRUE')が実行されて、'TRUE'と表示されたわけです。正しく動作しています。

以下のふたつのチェックもきちんと正しく動作します。

ORA-20000 親ディレクトリのない文書は許可できません (33行目)
ORA-20001 P_OYA=nnnがディレクトリではありません (45行目)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN
    IF  DOC_FUNC_OYA_CHECK(5, NULL ) THEN
        DBMS_OUTPUT.PUT_LINE( 'TRUE' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( 'FALSE' );
    END IF;
END ;
/
 
BEGIN
*
行1でエラーが発生しました。:
ORA-20000: 親ディレクトリのない文書は許可できません
ORA-06512: "SCOTT.DOC_FUNC_OYA_CHECK" , 行33
ORA-06512: 行2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN
    IF  DOC_FUNC_OYA_CHECK(5,14) THEN
        DBMS_OUTPUT.PUT_LINE( 'TRUE' );
    ELSE
        DBMS_OUTPUT.PUT_LINE( 'FALSE' );
    END IF;
END ;
/
 
BEGIN
*
行1でエラーが発生しました。:
ORA-20001: P_OYA=14がディレクトリでありません
ORA-06512: "SCOTT.DOC_FUNC_OYA_CHECK" , 行45
ORA-06512: 行2

それぞれきちんとチェックできていますね。

このように、親ディレクトリのIDを更新する場合は、常にこのDOC_FUNC_OYA_CHECKファンクションをコールすることにより、妥当な親ディレクトリのIDであることが保証できます。
では今回はここまでにします。次回は前回(第126回)の更新処理プロシージャを修正して今回のDOC_FUNC_OYA_CHECKファンクションをコールするように、修正しましょう。これにより、循環するディレクトリを含め、階層にまつわる妥当でない更新のケースを排除できます。

ご期待ください。

先頭へ戻る