قابلیت SQL domain در اوراکل 23c

دیتابیس اوراکل در نسخه 23c سعی کرده تا بسیاری از قابلیتهای موجود در دیتابیسهای رابطه ای دیگر را در این version ارائه کند قابلیتهای ساده ای نظیر «Schema level privilege» – «Boolean data type» – «Direct Joins for UPDATE and DELETE» -«SELECT without FROM» و …

یکی دیگر از این قابلیتها که موضوع بحث این مستند هم هست، SQL domain می باشد که می تواند شامل مجموعه ای از محدودیتها و خصوصیتها باشد و با تخصیص آن به یک ستون، می توان محدودیتهایی را برای آن ستون اعمال کرد به عبارت دیگر، SQL domain امکان توسعه Data type را متناسب با Business فراهم می کند.

یکی از کاربردهای مهم این قابلیت به زمانی برمی گردد که بخواهیم برای مقادیر ورودی یک ستون، شرطهای به خصوصی را اعمال کنیم. مثلا برای ستون Age با نوع داده number، با شرط Age>=18، از ثبت مقادیر کمتر از 18 جلوگیری کنیم و یا به عنوان مثالی کاربردی تر، برای ستونی که قرار است آدرس Email در آن ذخیره شود، شرطی را اعمال کنیم تا این ستون، صرفا اطلاعات ورودی با فرمت [email protected] را بپذیرد.

البته در نسخه های قبل از 23c، این کار به روشها مختلفی نظیر trigger، check constraint و … قابل انجام بود به عنوان مثال، از طریق check constraint می توانیم محدودیتی را ایجاد کنیم تا ثبت اطلاعات در ستون Email صرفا به فرمت [email protected] قابل انجام باشد:

SQL> CREATE TABLE EMAIL_ADDRESS 2 ( 3 id NUMBER(10), 4 person_id NUMBER(20), 5 email VARCHAR2(500) constraint check_email 6 CHECK (regexp_like (email, '^(S+)@(S+).(S+)$')) 7 );
Table created

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail');
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'
SQL> insert into EMAIL_ADDRESS values(1,34,'[email protected]');
1 row inserted
SQL> update EMAIL_ADDRESS set email='vahidusefzadeh';
'ORA-02290: check constraint (USEF.CHECK_EMAIL) violated'

ممکن است در یک schema جداول متعددی وجود داشته باشند که آدرس email را در خودشان ذخیره می کنند در این صورت check constraint باید برای هر ستون تکرار شود فرض کنید می خواهیم تغییری را در فرمت email ایجاد کنیم و یا همه constraintها را غیرفعال کنیم بدیهی است در این موارد، مدیریت check constraint با چالشهایی همراه است!

SQL domain در این زمینه بسیار منعطف است و قابلیتهای مختلفی دارد و بعد از ایجاد می توان از آن در جداول مختلف استفاده کرد. هر domain باید حداقل شامل یک Data type باشد:

SQL> create domain DMN_check_email as varchar2(500);
Domain created.

SQL domain می تواند شامل constraintهای از نوع NOT NULL، NULL و یا check constraint باشد که در مثال زیر domainای را با نوع داده varchar2 و یک check constraint ایجاد می کنیم:

SQL> create domain DMN_check_email as varchar2(500) constraint check_email check (regexp_like (DMN_check_email, '^(S+)@(S+).(S+)$'));
Domain created.

*در این مثال برای constraint از نام check_email استفاده کردیم تعیین نام برای constraint اختیاری است و اگر تنظیم نشود، اوراکل به صورت خودکار نامی را برای آن در نظر می گیرد.

در مورد نحوه نمایش اطلاعات هم می توان از عبارت display استفاده کرد:

SQL> create domain DMN_check_email as varchar2(500) constraint check_email check (regexp_like (DMN_check_email, '^(S+)@(S+).(S+)$')) display upper('Email: '||DMN_check_email);
Domain created.

بعد از ایجاد DMN_check_email، می توان آن را به ستونی از یک جدول اختصاص داد البته یک domain می تواند توسط جداول مختلف مورد استفاده قرار بگیرد:

SQL> CREATE TABLE EMAIL_ADDRESS 2 ( 3 id NUMBER(10), 4 person_id NUMBER(20), 5 email DMN_check_email 6 );
Table created

SQL> desc EMAIL_ADDRESS Name Null? Type ---------------------- -------- ---------------------------- ID NUMBER(10) PERSON_ID NUMBER(20) EMAIL VARCHAR2(500) USEF.DMN_CHECK_EMAIL

این کار به روشهای دیگری هم قابل انجام است:

SQL> CREATE TABLE EMAIL_ADDRESS 2 ( 3 id NUMBER(10), 4 person_id NUMBER(20), 5 email varchar2(500) domain DMN_check_email 6 );
Table created

SQL> CREATE TABLE EMAIL_ADDRESS 2 ( 3 id NUMBER(10), 4 person_id NUMBER(20), 5 email domain DMN_check_email 6 );
Table created

با اجرای دستورات زیر عملکرد domain ایجاد شده را بررسی می کنیم:

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh@gmail');
ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated
SQL> insert into EMAIL_ADDRESS values(1,34,'[email protected]');
1 row inserted
SQL> insert into EMAIL_ADDRESS values(2,46,'[email protected]');
1 row created.
SQL> update EMAIL_ADDRESS set email='vahidusefzadeh';
'ORA-11534: check constraint (USEF.SYS_C008398) due to domain constraint USEF.CHECK_EMAIL of domain USEF.DMN_CHECK_EMAIL violated'

اطلاعات زیر در جدول ثبت شده اند:

SQL> select * from EMAIL_ADDRESS; ID PERSON_ID EMAIL
---------- ---------- ------------------------------ 1 34 [email protected] 2 46 [email protected]

برای اعمال تنظیمات مربوط به display هم می توانیم از تابع DOMAIN_DISPLAY استفاده کنیم:

SQL> select id,PERSON_ID,DOMAIN_DISPLAY(EMAIL) EMAIL from EMAIL_ADDRESS; ID PERSON_ID EMAIL
---------- ---------- ---------------------------------------- 1 34 EMAIL: [email protected] 1 46 EMAIL: [email protected]

از DMN_check_email می توان برای جداولی که قبلا ایجاد شده اند هم استفاده کرد:

SQL> CREATE TABLE EMAIL_ADDRESS2 2 ( 3 id NUMBER(10), 4 person_id NUMBER(20), 5 email varchar2(1000) 6 );
Table created

SQL> insert into EMAIL_ADDRESS2 values(1,34,'vahidusefzadeh');
1 row inserted
SQL> insert into EMAIL_ADDRESS2 values(2,34,'[email protected]');
1 row inserted
SQL> commit;
Commit complete
SQL> alter table EMAIL_ADDRESS2 modify email domain DMN_CHECK_EMAIL;
'ORA-02293: caot validate (USEF.) - check constraint violated'
SQL> delete EMAIL_ADDRESS2 where id=1;
1 row deleted
SQL> commit;
Commit complete
SQL> alter table EMAIL_ADDRESS2 modify email domain DMN_CHECK_EMAIL;
Table altered

لیست domainهای ایجاد شده را می توان با دستور زیر مشاهده کرد:

SQL> select owner, name from user_domains;
OWNER NAME
--------------- ---------------
USEF DMN_CHECK_EMAIL

ویوی user_domain_constraints هم constraint مربوط به domainها را نمایش می دهد:

select * from user_domain_constraints where domain_name='DMN_CHECK_EMAIL';
NAME SEARCH_CONDITION STATUS
----------- ------------------------------------------------------- --------
CHECK_EMAIL regexp_like (DMN_check_email, '^(S+)@(S+).(S+)$') ENABLED

*ویوی DBA_DOMAIN_COLS هم در این زمینه بسیار کاربردی است.

نکته جالب دیگر در زمینه domainها آن است که اوراکل هم به صورت خودکار domainهایی را ایجاد کرده است که می توانیم لیست آنها را از طریق ویوی all_domains ببنیم:

SQL> select name from all_domains where owner='SYS';
PHONE_NUMBER_D
EMAIL_D
DAY_SHORT_D
DAY_D
MONTH_SHORT_D
MONTH_D
YEAR_D
POSITIVE_NUMBER_D
NEGATIVE_NUMBER_D
NON_POSITIVE_NUMBER_D
NON_NEGATIVE_NUMBER_D
MAC_ADDRESS_D
SSN_D
CREDIT_CARD_NUMBER_D
IPV4_ADDRESS_D
IPV6_ADDRESS_D
SUBNET_MASK_D
SHA1_D
SHA256_D
SHA512_D
CIDR_D
MIME_TYPE_D
22 rows selected.

به عنوان نمونه رفتار IPV4_ADDRESS_D را بررسی می کنیم:

SQL> create table tbl1(ip_v4 IPV4_ADDRESS_D);
Table created
SQL> insert into tbl1 values('10.22.44.66');
1 row inserted
SQL> insert into tbl1 values('10.22.44.666');
ORA-11534: check constraint (USEF.SYS_C008400) due to domain constraint SYS.SYS_DOMAIN_C0015 of domain SYS.IPV4_ADDRESS_D violated
SQL> insert into tbl1 values('10.22.44.1.5');
ORA-11534: check constraint (USEF.SYS_C008400) due to domain constraint SYS.SYS_DOMAIN_C0015 of domain SYS.IPV4_ADDRESS_D violated

برای استخراج فرمت constraint مربوط به هر کدام از این domainها می توانیم از بسته dbms_metadata استفاده کنیم:

select dbms_metadata.get_ddl('SQL_DOMAIN', 'IPV4_ADDRESS_D','SYS') domain_ddl from dual;

CREATE DOMAIN "SYS"."IPV4_ADDRESS_D" AS VARCHAR2(15) CHECK (REGEXP_LIKE(ipv4_address_d,'^(([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5]).){3}([0-9]|[1-9][0-9]|1[0-9]{2}|2[0-4][0-9]|25[0-5])$')) ENABLE

حذف DOMAIN

در صورتی که domain ایجاد شده را قبلا برای ستونی استفاده نکرده باشیم، حذف domain بدون مشکل قابل انجام است:

SQL> drop domain DMN_check_email;
Done

اگر domain به ستونی اختصاص داده شود، در زمان حذف با خطای زیر مواجه خواهیم شد:

SQL> drop domain DMN_check_email;
'ORA-11502: Message 11502 not found; product=RDBMS; facility=ORA'

با اضافه کردن کلمه force به دستور drop domain، این domain حذف خواهد شد:

SQL> DESC EMAIL_ADDRESS
Name Type ----------- -------------------------------------- ID NUMBER(10) PERSON_ID NUMBER(20) EMAIL VARCHAR2(500) USEF.DMN_CHECK_EMAIL

SQL> drop domain DMN_check_email force;
Done

در این صورت check constraint هم از ستون مورد نظر برداشته می شود:

SQL> DESC EMAIL_ADDRESS Name Type ------------------------ --------------- ID NUMBER(10) PERSON_ID NUMBER(20) EMAIL VARCHAR2(500)

SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
1 row inserted

البته اگر از عبارت FORCE PRESERVE استفاده شود، constraint حذف باقی می ماند:

SQL> drop domain DMN_check_email force PRESERVE;
Domain dropped.
SQL> DESC EMAIL_ADDRESS Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER(10) PERSON_ID NUMBER(20) EMAIL VARCHAR2(500)
SQL> insert into EMAIL_ADDRESS values(1,34,'vahidusefzadeh');
ORA-02290: check constraint (USEF.SYS_C008407) violated

 

Multi Column Domain

همه مثالهایی که از ساخت Domain تا اینجا در این مستند مشاهده کردید، مربوط به Single Column Domain بود در صورتی که دو نوع دیگر از Domain هم وجود دارند Multi Column domain و Flexible domain.

در قسمت زیر نمونه ای از ساخت Domain از نوع Multi Column را مشاهده می کنید:

SQL> CREATE DOMAIN IR_NORTH AS ( province AS VARCHAR2(100), zipcode AS NUMBER ) CONSTRAINT IR_NORTH_ch CHECK(province in ('Mazandaran','Golestan','GILAN') and zipcode >1234);
Domain created.

SQL> CREATE TABLE TBL_IR_NORTH( id number(10), province VARCHAR2(100), zipcode NUMBER, domain IR_NORTH(province,zipcode) );
Table created.

SQL> insert into TBL_IR_NORTH values(1,'Mazandaran',12345);
1 row created.
SQL> insert into TBL_IR_NORTH values(2,'Tehran',123456);
ERROR at line 1:
ORA-11534: check constraint (SYS.SYS_C008299) due to domain constraint SYS.IR_NORTH_CH of domain SYS.IR_NORTH violated
SQL> insert into TBL_IR_NORTH values(2,'Mazandaran',1);
ERROR at line 1:
ORA-11534: check constraint (SYS.SYS_C008299) due to domain constraint SYS.IR_NORTH_CH of domain SYS.IR_NORTH violated

Flexible Domain

برای ساخت Flexible Domain نیاز داریم حداقل دو domain داشته باشیم:

SQL> CREATE DOMAIN IR_NORTH AS ( province AS VARCHAR2(100), zipcode AS NUMBER ) CONSTRAINT IR_NORTH_ch CHECK(province in ('Mazandaran','Golestan','GILAN') and zipcode >1234);
Domain created.

SQL> CREATE DOMAIN IR_SOUTH AS ( province AS VARCHAR2(100), zipcode AS NUMBER ) CONSTRAINT IR_SOUTH_CH CHECK(province in ('Khozestan','Bushehr') and zipcode between 1 and 1000);
Domain created.

SQL> create flexible domain IR_Regions (province,zipcode)
choose domain using (Regions varchar2(10))
from case when Regions in ('SOUTH') then IR_SOUTH(province,zipcode) when Regions in ('NORTH') then IR_NORTH(province,zipcode) end;
Domain created.

SQL> CREATE TABLE TBL_IR_Regions( id number(10), province VARCHAR2(100), zipcode NUMBER, Regions varchar2(10), domain IR_Regions(province,zipcode)using (Regions) );
Table created.

با درج اطلاعات زیر، رفتار این domain را بررسی می کنیم:

SQL> insert into TBL_IR_Regions values(1,'Khozestan',10,'SOUTH');
1 row created.
SQL> insert into TBL_IR_Regions values(2,'Khozestan',3000145,'SOUTH');
ORA-11534: check constraint (SYS.SYS_C008303) due to domain constraint SYS.SYS_DOMAIN_C0041 of domain SYS.IR_REGIONS violated
SQL> insert into TBL_IR_Regions values(2,'Khozestan',30,'NORTH');
ORA-11534: check constraint (SYS.SYS_C008304) due to domain constraint SYS.SYS_DOMAIN_C0040 of domain SYS.IR_REGIONS violated
SQL> insert into TBL_IR_Regions values(2,'Mazandaran',30,'NORTH');
ORA-11534: check constraint (SYS.SYS_C008304) due to domain constraint SYS.SYS_DOMAIN_C0040 of domain SYS.IR_REGIONS violated
SQL> insert into TBL_IR_Regions values(2,'Mazandaran',3098755,'NORTH');
1 row created.
SQL> insert into TBL_IR_Regions values(3,'Mazandaran',30987588,'SOUTH');
ORA-11534: check constraint (SYS.SYS_C008303) due to domain constraint SYS.SYS_DOMAIN_C0041 of domain SYS.IR_REGIONS violated

تالار های تخصصی...