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