Oracle Database 23ai Free - Developer Releaseで、Precheckable Constraints  using JSON SCHEMAを試してみた

# はじめに
23aiの新機能「Precheckable Constraints  using JSON SCHEMA」を試してみました。

# 本記事の前提条件 

* [こちら](https://qiita.com/500InternalServerError/items/45b4dd0cfc04fb1bab5e)を参考に、23aiがインストールしてあること

# 実際に試してみた
* まずはJSON Schemaを試すのに使うユーザーを作成します。
1. oracleユーザーから、systemユーザーにログインします。
    ```
     [oracle@db23cfree ~]$ sqlplus system/password@freepdb1
    ```
1. 検証用のユーザーを作成します。
    ```
    SQL> CREATE USER jsonschema_test IDENTIFIED BY "password";
    ```
1. 権限を付与します。
    ```
    SQL> GRANT CREATE TABLE UNLIMITED TABLESPACE TO jsonschema_test;
    ```
1. jsonschema_testユーザーでログインします。
    ```
    [oracle@db23cfree ~]$ sqlplus jsonschema_test/password@freepdb1
    ```
### PRECHECK構文と定義の確認
*  表作成時に"PRECHECK"を使って、CHECK制約を定義する構文は以下のようになります。
    ```
    CREATE TABLE <table_name> (
      <column_definition> CHECK (<condition>) [<constraint_state>] [PRECHECK | NOPRECHECK],
      CONSTRAINT <constraint_name> CHECK (<condition>) 
        [<constraint_state>] [PRECHECK | NOPRECHECK] 
    )
    ```
*  また、表作成後に"PRECHECK"を使って、CHECK制約を定義する構文は以下のようになります。
    ```
    ALTER TABLE <table_name> 
      MODIFY CONSTRAINT <constraint_name> [<constraint_state>] [PRECHECK | NOPRECHECK]

    ALTER TABLE <table_name> 
      ADD CONSTRAINT <constraint_name> CHECK (<condition>) [<constraint_state>]
    [PRECHECK | NOPRECHECK]

    ```

### 新規の表に対するPrecheckable Constraints  using JSON SCHEMAの検証
1. まず検証のために、Product表を作成します。

    ```
    SQL> CREATE TABLE Product(
      Id NUMBER NOT NULL PRIMARY KEY,
      Name VARCHAR2(50) CHECK (regexp_like(Name, '^Product')),
      Category VARCHAR2(10) NOT NULL CHECK (CATEGORY IN ('Home', 'Apparel')),
      Price NUMBER CHECK (mod(price,4) = 0 and 10 < price),
      Description VARCHAR2(50) CHECK (Length(Description) <= 40),
      Created_At DATE,
      Updated_At DATE,
      CONSTRAINT MIXEDCOL CHECK (Created_At > Updated_At)
    );
    
    Table created.
    ```

1. 以下のように、制約の情報を参照することができます。
    ```
    SQL> SELECT constraint_name, search_condition_vc, precheck
         FROM all_constraints
         WHERE table_name='PRODUCT';
    ```

1. 結果は以下のようになります。
    ```
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    SYS_C008730
    "ID" IS NOT NULL
    
    
    SYS_C008731
    "CATEGORY" IS NOT NULL
    
    
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    
    SYS_C008732
    regexp_like(Name, '^Product')
    
    
    SYS_C008733
    CATEGORY IN ('Home', 'Apparel')
    
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    
    
    SYS_C008734
    mod(price,4) = 0 and 10 < price
    
    
    SYS_C008735
    
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    Length(Description) <= 40
    
    
    MIXEDCOL
    Created_At > Updated_At
    
    
    
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    SYS_C008737
    
    8 rows selected.

    ```
1. 結果を見ると、SYS_C008515、SYS_C008516、SYS_C008517およびSYS_C008518制約にPRECHECKプロパティがあるのに対して、MIXEDCOL制約にはNOPRECHECKプロパティがあることが分かります。これは、MIXEDCOL制約の対応するCHECK条件において、2つの列の間で不等式の関係があり、JSONスキーマと同等ではないからです。

1. また特定の制約がPRECHECKに設定できないとき、それについて事前に知りたい場合は、制約に対して明示的にPRECHECKのキーワードを使用します。
例えば、以下のようにMIXEDCOL制約に対してPRECHECKを指定することができます。
既に、Product表が作成されている場合は、表を削除してから、以下のCREATE文を実行します。
    ```
    SQL> CREATE TABLE Product(
         Id NUMBER NOT NULL PRIMARY KEY,
          Name VARCHAR2(50) CHECK (regexp_like(Name, '^Product')),
          Category VARCHAR2(10) NOT NULL CHECK (CATEGORY IN ('Home', 'Apparel')),
          Price NUMBER CHECK (mod(price,4) = 0 and 10 < price),
          Description VARCHAR2(50) CHECK (Length(Description) <= 40),
          Created_At DATE,
          Updated_At DATE,
          CONSTRAINT MIXEDCOL CHECK (Created_At > Updated_At) PRECHECK
       );
    ```
1. DDL 文は失敗し、ORA-40544 エラーが返されます。
   
    ```
    　  CONSTRAINT MIXEDCOL CHECK (Created_At > Updated_At) PRECHECK
                              *
        ERROR at line 9:
        ORA-40544: CHECK expression of 'MIXEDCOL' constraint not possible to use as
        PRECHECK condition
    ```

1. 最初のCREATE TABLE文を使用して作成される、PRODUCT表に対応するJSONスキーマを表示します。PRECHECKプロパティを持つ制約は、対応する列内のCHECK制約条件に対応するサブ・スキーマを持ちます。("allOf"の項目を参照)
一方、同等のJSONスキーマを持たないMIXEDCOL制約は、"dbNoPrecheck "配列に記載されています。
```
    SQL> select json_serialize(
                 dbms_json_schema.describe(
                   object_name => 'PRODUCT',
                   owner_name  => 'JSONSCHEMA_TEST')
                 pretty) as json_schema;
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
    {
      "title" : "PRODUCT",
      "dbObject" : "JSONSCHEMA_TEST.PRODUCT",
      "type" : "object",
      "dbObjectType" : "table",
      "properties" :
      {
        "ID" :
        {
          "extendedType" : "number"
        },
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
        "NAME" :
        {
          "extendedType" :
          [
            "null",
            "string"
          ],
          "maxLength" : 50,
          "allOf" :
          [
            {
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
              "pattern" : "^Product"
            }
          ]
        },
        "CATEGORY" :
        {
          "extendedType" : "string",
          "maxLength" : 10,
          "allOf" :
          [
            {
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
              "enum" :
              [
                "Home",
                "Apparel"
              ]
            }
          ]
        },
        "PRICE" :
        {
          "extendedType" :
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
          [
            "null",
            "number"
          ],
          "allOf" :
          [
            {
              "allOf" :
              [
                {
                  "multipleOf" : 4
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
                },
                {
                  "exclusiveMinimum" : 10
                }
              ]
            }
          ]
        },
        "DESCRIPTION" :
        {
          "extendedType" :
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
          [
            "null",
            "string"
          ],
          "maxLength" : 50,
          "allOf" :
          [
            {
              "maxLength" : 40
            }
          ]
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
        },
        "CREATED_AT" :
        {
          "extendedType" :
          [
            "null",
            "date"
          ]
        },
        "UPDATED_AT" :
        {
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
          "extendedType" :
          [
            "null",
            "date"
          ]
        }
      },
      "required" :
      [
        "ID",
        "CATEGORY"
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
      ],
      "dbNoPrecheck" :
      [
        {
          "dbConstraintName" : "MIXEDCOL",
          "dbConstraintExpression" : "Created_At > Updated_At"
        }
      ],
      "dbPrimaryKey" :
      [
        "ID"
    
    JSON_SCHEMA
    --------------------------------------------------------------------------------
      ]
    }
```



### 既存の表に対するPRECHECKの有効化
1. 次に、既存の表に対してPRECHECKプロパティを有効化してみます。
以下の例では、サンプルスキーマにあるHR.EMPLOYEESの表を使用しています。
※ 23aiに対するサンプルスキーマのインストールは[こちら](https://github.com/oracle-samples/db-sample-schemas)などを参考にして行います。
    ```
    DESC HR.EMPLOYEES;
     Name                                      Null?    Type
     ----------------------------------------- -------- ----------------------------
     EMPLOYEE_ID                               NOT NULL NUMBER(6)
     FIRST_NAME                                         VARCHAR2(20)
     LAST_NAME                                 NOT NULL VARCHAR2(25)
     EMAIL                                     NOT NULL VARCHAR2(25)
     PHONE_NUMBER                                       VARCHAR2(20)
     HIRE_DATE                                 NOT NULL DATE
     JOB_ID                                    NOT NULL VARCHAR2(10)
     SALARY                                             NUMBER(8,2)
     COMMISSION_PCT                                     NUMBER(2,2)
     MANAGER_ID                                         NUMBER(6)
     DEPARTMENT_ID                                      NUMBER(4)

    ```
1. この表には既にCHECK制約 EMP_SALARY_MINがあります。ALL_CONSTRAINTSのPRECHECK列はこの制約に対してNULLになります。以下のDDL文で PRECHECKプロパティを設定できます。
    ```
     ALTER TABLE HR.EMPLOYEES 
      MODIFY CONSTRAINT EMP_SALARY_MIN PRECHECK;
    ```
1. 以下のSELECT文から、制約の情報を参照できます。
    ```
    SELECT CONSTRAINT_NAME, SEARCH_CONDITION_VC, PRECHECK
      FROM ALL_CONSTRAINTS 
      WHERE TABLE_NAME='EMPLOYEES' AND CONSTRAINT_NAME='EMP_SALARY_MIN';
    ```
1. 結果は以下のようになります。
    ```
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    EMP_SALARY_MIN
    salary > 0
    PRECHECK
    ```
1. この制約がクライアント側の検証に関係なく、対応するJSONスキーマに含めないようにする場合は、次のようにPRECHECKプロパティの代わりに、NOPRECHECKプロパティを設定できます。
    ```
    SQL> ALTER TABLE HR.EMPLOYEES
     MODIFY CONSTRAINT EMP_SALARY_MIN NOPRECHECK;

    Table altered.
    ```
1. 次の例のように、新しい制約を追加することもできます。
次の例では、PRECHECKキーワードを指定せずに新しい制約が追加できます。
この制約には、同等のJSONスキーマがあるため、暗黙的にPRECHECKに設定されます。
    ```
    SQL> ALTER TABLE HR.EMPLOYEES
         ADD CONSTRAINT EMP_COMMISSION_PCT_MIN CHECK (COMMISSION_PCT >= 0.1);
    
    Table altered.
    ```

1. 新しく追加された制約情報は、以下のようにクエリできます：
    ```
    SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION_VC, PRECHECK
          FROM ALL_CONSTRAINTS
          WHERE TABLE_NAME='EMPLOYEES' AND CONSTRAINT_NAME='EMP_COMMISSION_PCT_MIN';
    ```
1. 結果は以下のようになります。
    ```
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    EMP_COMMISSION_PCT_MIN
    COMMISSION_PCT >= 0.1
    ```
1. 以下の例では、チェック条件に2つの列を持つ制約に相当するJSONスキーマがないため、制約は暗黙的にNOPRECHECKに設定されます。
    ```
    SQL> ALTER TABLE HR.EMPLOYEES
     ADD CONSTRAINT EMP_MAX_BONUS CHECK ((SALARY * COMMISSION_PCT) < 6000);

    Table altered.
    ```
1. 同様に新しく追加された制約の情報は、以下のようにクエリできます。
    ```
    SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION_VC, PRECHECK
      FROM ALL_CONSTRAINTS
      WHERE TABLE_NAME='EMPLOYEES' AND CONSTRAINT_NAME='EMP_MAX_BONUS';
    ```
1. 結果は、以下のようになります。
    ```
    CONSTRAINT_NAME
    --------------------------------------------------------------------------------
    SEARCH_CONDITION_VC
    --------------------------------------------------------------------------------
    PRECHECK
    --------
    EMP_MAX_BONUS
    (SALARY * COMMISSION_PCT) < 6000
    ```
1. この例で PRECHECK キーワードを使用すると、以下のようにエラーが発生し、DDL 文は失敗します。
    ```
    SQL> ALTER TABLE HR.EMPLOYEES
     ADD CONSTRAINT EMP_MAX_BONUS CHECK ((SALARY * COMMISSION_PCT) < 6000) PRECHECK;
     
    ADD CONSTRAINT EMP_MAX_BONUS CHECK ((SALARY * COMMISSION_PCT) < 6000) PRECHECK
                                   *
    ERROR at line 2:
    ORA-40544: CHECK expression of 'EMP_MAX_BONUS' constraint not possible to use
    as PRECHECK condition
    ```

# まとめ
PRECHECKの機能は、データがデータベースに登録される前に、クライアント側でJSONデータを事前に検証するオプションを提供します。PRECHECK機能を使用すると、無効なデータがデータベースに送信されないように制限することができます。


# 参考文献
* [Precheckable Constraints using JSON SCHEMA](https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/data-integrity.html#GUID-E13067AB-5F81-4A58-8485-1DCE77DE0029)
* [Using PRECHECK to Pre-validate JSON Data](https://docs.oracle.com/en/database/oracle/oracle-database/23/adfns/data-integrity.html#GUID-278AD7DD-C45D-4F43-8D60-8ABA2B062296)
* [Oracle Database Sample Schemas](https://github.com/oracle-samples/db-sample-schemas)