※ 本記事は、Ulrike Schwinnによる”Less coding using new SQL Domains in 23c“を翻訳したものです。
2023年4月25日
新しい23cの機能を見直した時、新機能であるSQLドメイン(使用ドメインとも呼ばれる)にも行き着きました。短い定義から始めましょう。SQLドメインは、スキーマに属するディクショナリ・オブジェクトであり、共通値のオプション・プロパティおよび制約のセットをカプセル化します。SQLドメインは、制約、表示、順序付けおよび注釈の属性を提供します。SQLドメインを定義した後、そのドメインに関連付ける表の列を定義して、ドメインのオプションのプロパティおよび制約を明示的にそれらの列に適用できます。そのため、SQLドメインは、格納された列(JSONまたはリレーショナル)に追加情報を提供するために使用されるため、データの定義と検証に使用されます。
ノート: OracleのSQLドメイン実装では、SQL標準で指定されている関数よりも広い範囲の関数が提供されます(第2部 Foundation (SQL Foundation)を参照)。
自分のスキーマでドメインを作成、変更および削除できます。ドメインを操作するには、CREATE DOMAIN権限が必要です。
ノート: RESOURCEおよび新しい開発者ロールのDB_DEVELOPER_ROLEロールには、CREATE DOMAIN権限がすでに含まれています。
ドメインには、単一列、複数列およびフレキシブル・ドメインの3種類があります。また、使用できる組込みドメインもあります。
構文、データ・ディクショナリ・ビュー、新しいドメイン関数および多くの例については、次の23cのドキュメントを参照してください。:
(これらの2つの参照は、新機能を知るときに「my best friends」でした。 :))
簡単に開始できるように、表PERSONを中心にいくつかの例をまとめ、次の項目を取り上げました。:
単一列ドメインの作成および削除
SQLドメインを作成するには、SQL Language Referenceを参照してください。これは、CREATE DOMAIN構文の抜粋です。:
CREATE DOMAIN [IF NOT EXISTS] DomainName AS <Type> [STRICT]
[ DEFAULT [ON NULL..] <expression>]
[ [NOT] NULL]
[ CONSTRAINT [Name] CHECK (<expression>) [ ENABLE | DISABLE] ..]*
[ VALIDATE USING <json_schema_string>]
[ COLLATE collation ]
[ DISPLAY <expression> ]
[ ORDER <expression> ]
次を使用してドメインを削除することもできます …
DROP DOMAIN [IF EXISTS] domain_name [FORCE [PRESERVE]]
次の点に注意してください: PRESERVEと対比してFORCEを使用すると、すべての依存列からドメインの関連付けが解除されます。これには、ドメインから継承された列に対するすべての制約の削除が含まれます。ドメインから継承されたデフォルトも、これらのデフォルトが列に対して特に設定されていないかぎり削除されます。
SQLドメイン構文の様々な部分の詳細な説明は、ドキュメントを参照してください。使い方を理解するには、単純な例(電子メール・ドメインを作成して、それを個人表で使用します)を作成します。
create domain if not exists myemail_domain AS VARCHAR2(100) default on null 'XXXX' || '@missingmail.com' constraint email_c CHECK (REGEXP_LIKE (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$')) display sunbstr(myemail_domain, INSTR(myemail_domain, '@') + 1);
チェック制約EMAIL_Cは、列に有効なEメールが格納されているかどうかを調べます。DISPLAYは、表示目的でドメイン列を変換する方法を指定します。指定した列でSQL関数DOMAIN_DISPLAYを使用して表示できます。さて、person表で使ってみましょう。
drop table if exists person; create table person ( p_id number(5), p_name varchar2(50), p_sal number, p_email varchar2(100) domain myemail_domain ) annotations (display 'person_table');
表示されているように、注釈をSQLドメインと組み合せて使用することもできます。注釈の詳細が必要な場合は、最近の投稿「注釈 – 23cの新しいメタデータ」に説明と例があります。
次に、有効なデータを含む行を追加します。
insert into person values (1,'Bold',3000,null); insert into person values (1,'Schulte',1000, 'mschulte@gmx.net'); insert into person values (1,'Walter',1000,'twalter@t_online.de'); insert into person values (1,'Schwinn',1000, 'Ulrike.Schwinn@oracle.com'); insert into person values (1,'King',1000, 'aking@aol.com'); commit;
無効なデータを挿入してみましょう。次のエラー・メッセージが表示されます。:
SQL> insert into person values (1,'Schulte',3000, 'mschulte%gmx.net'); *insert into person values (1,'Schulte',3000, 'mschulte%gmx.net') * ERROR at line 1: ORA-11534: check constraint (SCOTT.SYS_C008254) due to domain constraint SCOTT.EMAIL_C of domain SCOTT.MYEMAIL_DOMAIN violated
次に、PERSON表を問い合せます …
SQL> select * from person; P_ID P_NAME P_SAL P_EMAIL ---------- ---------------------------------- ---------- -------------------------- 1 Bold 3000 XXXX@missingmail.com 1 Schulte 1000 mschulte@gmx.net 1 Walter 1000 twalter@t_online.de 1 Schwinn 1000 Ulrike.Schwinn@oracle.com 1 King 1000 aking@aol.com
SQLドメインの監視
SQLドメインをモニターする方法は様々です。たとえば、SQL*PlusでDESCRIBEを使用すると、列と関連付けられたドメインおよびNull制約がすでに表示されています。
SQL> desc person Name Null? Type ----------------------------------- -------- ------------------------------------ P_ID NUMBER(5) P_NAME VARCHAR2(50) P_SAL NUMBER P_EMAIL NOT NULL VARCHAR2(100) SCOTT.MYEMAIL_DOMAIN
すでに説明したように、ドメイン・プロパティの詳細を取得するために表の列とともに使用できる新しいドメイン関数があります。たとえば、DOMAIN_NAMEは、引数が関連付けられているドメインの修飾ドメイン名を返します。DOMAIN_DISPLAYは、引数が関連付けられているドメインのドメイン表示式を返します。詳細は、ドキュメントを参照してください。
SQL> col p_name format a25 SQL> col DISPLAY format a25 SQL> select p_name, domain_display(p_email) "Display" from person; P_NAME Display ------------------------- ------------------------- Bold missingmail.com Schulte gmx.net Walter t_online.de Schwinn oracle.com King aol.com
SQlドメインを監視する別の方法は、USER_DOMAIN、USER_DOMAIN_COLS、USER_DOMAIN_CONSTRAINTS (ALL/DBAも使用)などのデータ・ディクショナリ・ビューです。
次に例を示します。:
SQL> col owner format a15 SQL> select owner, name, data_display from user_domains; OWNER NAME --------------- ------------------------------ DATA_DISPLAY ------------------------------------------------------ SCOTT MYEMAIL_DOMAIN SUBSTR(myemail_domain, INSTR(myemail_domain, '@') + 1) SQL> select * from user_domain_constraints where domain_name='MYEMAIL_DOMAIN'; NAME DOMAIN_OWNER DOMAIN_NAME C ------------------------------ --------------- -------------------- - SEARCH_CONDITION STATUS -------------------------------------------------------------------------------- -------- DEFERRABLE DEFERRED VALIDATED GENERATED BAD RELY INVALID ORIGIN_CON_ID -------------- --------- ------------- -------------- --- ---- ------- ------------- EMAIL_C SCOTT MYEMAIL_DOMAIN C REGEXP_LIKE (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$') ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED USER NAME 3 SYS_DOMAIN_C0034 SCOTT MYEMAIL_DOMAIN C "MYEMAIL_DOMAIN" IS NOT NULL ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED GENERATED NAME 3
ただし、DDLコマンドを取得するための”good old”パッケージDBMS_METADATAはどうでしょうか。GET_DDLを試し、object_type引数としてSQL_DOMAINを使用します。そうすれば、結果を得られます。
SQL> select dbms_metadata.get_ddl('SQL_DOMAIN', 'MYEMAIL_DOMAIN') from dual;
DBMS_METADATA.GET_DDL('SQL_DOMAIN','MYEMAIL_DOMAIN')
----------------------------------------------------------------------------------------------------
CREATE DOMAIN "SCOTT"."MYEMAIL_DOMAIN" AS VARCHAR2(100) DEFAULT ON NULL 'XXXX' || '@missingmail.com'
CONSTRAINT "EMAIL_C" CHECK (REGEXP_LIKE (myemail_domain, '^(\S+)\@(\S+)\.(\S+)$')) ENABLE
DISPLAY SUBSTR(myemail_domain, INSTR(myemail_domain, '@') + 1)
組込みドメインの使用
Oracleには、簡単に開始できるだけでなく、表の列(電子メール、ssn、credit_cardなど)で直接使用できる組込みドメインが用意されています。名前、許可される値および説明を含むリストについては、ドキュメントを参照してください。
この情報を取得する別の方法は、ALL_DOMAINSを問い合せ、所有者SYSでフィルタすることです。その後、組込みドメインを受信します …
SQL> select name from all_domains where owner='SYS'; NAME ------------------------- PHONE_NUMBER_D EMAIL_D DAY_SHORT_D DAY_D MONTH_SHORT_D MONTH_D YEAR_D POSITIVE_NUMBER_D ...
ドメインEMAIL_Dで電子メール・エントリを調べます。データ・ディクショナリ・ビューを問い合せるか、パッケージDBMS_METADATAを使用して定義を取得します。
SQL> col domain_ddl format a100
SQL> select dbms_metadata.get_ddl('SQL_DOMAIN', 'EMAIL_D','SYS') domain_ddl from dual;
DOMAIN_DDL
----------------------------------------------------------------------------------------------------
CREATE DOMAIN "SYS"."EMAIL_D" AS VARCHAR2(4000) CHECK (REGEXP_LIKE (email_d, '^([a-zA-Z0-9!#$%&*+=
?^_`{|}~-]+(\.[A-Za-z0-9!#$%&*+=?^_`{|}~-]+)*)@(([a-zA-Z0-9]([a-zA-Z0-9-]*[a-zA-Z0-9])?\.)+[a-zA-Z0-
9]([a-zA-Z0-9-]*[a-zA-Z0-9])?)$')) ENABLE
次に、PERSON表を再作成します。列P_EMAILの長さを4000に調整する必要があることに注意してください。そうしないと、次のエラーが表示されます。:
SQL> create table person ( p_id number(5), p_name varchar2(50), p_sal number, p_email varchar2(2000) domain EMAIL_D ) annotations (display 'person_table'); create table person * ERROR at line 1: ORA-11517: the column data type does not match the domain column
しかし、この1つは機能します。 …
create table person ( p_id number(5), p_name varchar2(50), p_sal number, p_email varchar2(4000) domain EMAIL_D ) annotations (display 'person_table');
次のステップでは、データを挿入します。
insert into person values (1,'Bold',3000,null); 1 row created. insert into person values (1,'Schulte',1000, 'mschulte@gmx.net'); 1 row created. insert into person values (1,'Walter',1000,'twalter@t_online.de') * ERROR at line 1: ORA-11534: check constraint (SCOTT.SYS_C008255) due to domain constraint SYS.SYS_DOMAIN_C002 of domain SYS.EMAIL_D violated
記号「_」の付いたEメールは有効なエントリではないため、「-」に変更する必要があります。
SQL> insert into person values (1,'Walter',1000,'twalter@t-online.de'); 1 row created.
JSONの検証
23cでは、OracleデータベースはJSONデータ型だけでなく、JSONスキーマ検証もサポートしています。XMLスキーマに類似したJSONスキーマは、JSONドキュメントに注釈を付けて検証できるルールを定義します。スキーマでは、許可されるキーワード、値のデータ型、およびネスト可能な構造を指定します。キーと値のペアは、必須またはオプションとして定義できます。これで、IS JSONチェック制約句VALIDATEを使用してJSONスキーマを使用してJSONドキュメントを検証できます。また、チェック制約なしで短縮構文を使用することもできます。
次の例は、CREATE TABLEコマンドでインライン・スキーマ定義を使用する方法を示しています(チェック制約のない短縮構文)。
create table person (id NUMBER, p_record JSON VALIDATE '<json-schema>')
SQLドメインでは現在、JSON検証もサポートされています。インライン・スキーマ定義とSQLドメインの違いは、SQLドメインにSQLドメインへの参照が格納されることです(値によるコールではなく、参照によってコールされます)。SQLドメインが変更されると、検証ロジックも変更されます。SQLドメインが削除された場合、検証は行われず、この結果にエラーが発生します。
JSON検証句[VALIDATE USING <json_schema_string>]でSQLドメインを使用する例をあげましょう。JSONスキーマは、個人JSONドキュメントを記述します。
create domain p_recorddomain AS JSON VALIDATE USING '{
"type": "object",
"properties": {
"first_name": { "type": "string" },
"last_name": { "type": "string" },
"birthday": { "type": "string", "format": "date" },
"address": {
"type": "object",
"properties": {
"street_address": { "type": "string" },
"city": { "type": "string" },
"state": { "type": "string" },
"country": { "type" : "string" }
}
}
}
}' ;
create person (id NUMBER,
p_record JSON DOMAIN p_recorddomain);
有効なデータを挿入します
insert into person values (1, '{
"first_name": "George",
"last_name": "Washington",
"birthday": "1732-02-22",
"address": {
"street_address": "3200 Mount Vernon Memorial Highway",
"city": "Mount Vernon",
"state": "Virginia",
"country": "United States"
}
}');
次のレコードは有効なエントリではありません …
SQL> insert into person values (2, '{
"name": "George Washington",
"birthday": "February 22, 1732",
"address": "Mount Vernon, Virginia, United States"
}');
insert into person values (2, '{
*
ERROR at line 1:
ORA-40875: JSON schema validation error
スキーマを検証するためのチェック制約が自動的に作成されます。これを確認するには、USER_DOMAIN_CONSTRAINTSを問い合せます。
SQL> set long 400
SQL> col name format a20
SQL> select name, generated, constraint_type, search_condition
from user_domain_constraints where domain_name like 'P_RECORD%'
NAME GENERATED C
-------------------- -------------- -
SEARCH_CONDITION
---------------------------------------------------------------------
SYS_DOMAIN_C0035 GENERATED NAME C
"P_RECORDDOMAIN" IS JSON VALIDATE USING '{
"type": "object",
"properties": {
"first_name": { "type": "string" },
"last_name": { "type": "string" },
"birthday": { "type": "string", "format": "date" },
"address": {
"type": "object",
"properties": {
"street_address": { "type": "string" },
"city": { "type": "string" },
"state": { "type": "string" },
"country": { "type" : "string" } } } } }'
まとめ
SQLドメイン機能は、23cでのアプリケーション開発のための非常に強力な新しいツールです。一般SQLドメインでは、ユーザーは、抽象ドメイン固有の知識を簡単に再利用できるように、個別のデータ・ディクショナリ・オブジェクトを使用して列の用途を宣言できます。これらを使用して、格納された列に追加情報を提供し、その列を使用してデータを定義および検証できます。リレーショナル列のみでなく、新しいVALIDATE句を使用したJSONデータも検証できます。他のデータベース・オブジェクトと同様に、作成、削除および変更できます。これを使用すると、表定義を変更せずに検証ロジックまたは情報を変更できます。詳細は、ドキュメントの例を参照してください。この投稿では説明していない複数列ドメインおよびフレキシブル・ドメインに関するその他の例もあります。