はじめに

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

本記事の前提条件

こちらを参考に、23aiがインストールしてあること

実際に試してみた

まずはJSON Schemaを試すのに使うユーザーを作成します。

oracleユーザーから、systemユーザーにログインします。
```
 [oracle@db23cfree ~]$ sqlplus system/password@freepdb1
```

検証用のユーザーを作成します。

SQL> CREATE USER jsonschema_test IDENTIFIED BY "password";

権限を付与します。

SQL> GRANT CREATE TABLE UNLIMITED TABLESPACE TO jsonschema_test;

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の検証

まず検証のために、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.

以下のように、制約の情報を参照することができます。

SQL> SELECT constraint_name, search_condition_vc, precheck
     FROM all_constraints
     WHERE table_name='PRODUCT';

結果は以下のようになります。

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.

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

また特定の制約が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
   );

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

最初の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の有効化

次に、既存の表に対してPRECHECKプロパティを有効化してみます。
以下の例では、サンプルスキーマにあるHR.EMPLOYEESの表を使用しています。
※ 23aiに対するサンプルスキーマのインストールはこちらなどを参考にして行います。

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)

この表には既にCHECK制約 EMP_SALARY_MINがあります。ALL_CONSTRAINTSのPRECHECK列はこの制約に対してNULLになります。以下のDDL文で PRECHECKプロパティを設定できます。
```
 ALTER TABLE HR.EMPLOYEES 
  MODIFY CONSTRAINT EMP_SALARY_MIN PRECHECK;
```

以下のSELECT文から、制約の情報を参照できます。

SELECT CONSTRAINT_NAME, SEARCH_CONDITION_VC, PRECHECK
  FROM ALL_CONSTRAINTS 
  WHERE TABLE_NAME='EMPLOYEES' AND CONSTRAINT_NAME='EMP_SALARY_MIN';

結果は以下のようになります。

CONSTRAINT_NAME
--------------------------------------------------------------------------------
SEARCH_CONDITION_VC
--------------------------------------------------------------------------------
PRECHECK
--------
EMP_SALARY_MIN
salary > 0
PRECHECK

この制約がクライアント側の検証に関係なく、対応するJSONスキーマに含めないようにする場合は、次のようにPRECHECKプロパティの代わりに、NOPRECHECKプロパティを設定できます。
```
SQL> ALTER TABLE HR.EMPLOYEES
 MODIFY CONSTRAINT EMP_SALARY_MIN NOPRECHECK;

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

Table altered.
```

新しく追加された制約情報は、以下のようにクエリできます：

SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION_VC, PRECHECK
      FROM ALL_CONSTRAINTS
      WHERE TABLE_NAME='EMPLOYEES' AND CONSTRAINT_NAME='EMP_COMMISSION_PCT_MIN';

結果は以下のようになります。

CONSTRAINT_NAME
--------------------------------------------------------------------------------
SEARCH_CONDITION_VC
--------------------------------------------------------------------------------
PRECHECK
--------
EMP_COMMISSION_PCT_MIN
COMMISSION_PCT >= 0.1

以下の例では、チェック条件に2つの列を持つ制約に相当するJSONスキーマがないため、制約は暗黙的にNOPRECHECKに設定されます。
```
SQL> ALTER TABLE HR.EMPLOYEES
 ADD CONSTRAINT EMP_MAX_BONUS CHECK ((SALARY * COMMISSION_PCT) < 6000);

Table altered.
```

同様に新しく追加された制約の情報は、以下のようにクエリできます。

SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION_VC, PRECHECK
  FROM ALL_CONSTRAINTS
  WHERE TABLE_NAME='EMPLOYEES' AND CONSTRAINT_NAME='EMP_MAX_BONUS';

結果は、以下のようになります。

CONSTRAINT_NAME
--------------------------------------------------------------------------------
SEARCH_CONDITION_VC
--------------------------------------------------------------------------------
PRECHECK
--------
EMP_MAX_BONUS
(SALARY * COMMISSION_PCT) < 6000

この例で 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機能を使用すると、無効なデータがデータベースに送信されないように制限することができます。

参考文献