Сложное ограничение целостности (constraint)

в 9:53, , рубрики: oracle, sql

Однажды возникла необходимость организовать сложный контроль вводимой одновременно в несколько таблиц логически связанной информации в БД ORACLE. Преобразование начального логически корректного набора данных в таблицах в конечный логически корректный набор производится последовательностью DML операторов. При этом проводить модификацию может произвольный клиент поведение которого неконтролируемо а структура данных такова, что в процессе выполнения пошаговой модификации на некоторых шагах набор данных может становится логически ошибочным.

Простейший пример это таблица истории значений из трех полей: значение, начальная дата действия значения, конечная дата действия значения. Логически корректная история не может иметь записей у которых перекрываются периоды действия значений. Для изменения границы действия двух соседних значений необходимо изменить две даты — дату конца действия предыдущего значения в предыдущей записи и дату начала действия последующего в следующей записи. Если границу смены значений двигать по времени вперед и на первом шаге переставить вперед дату окончания действия значения первой записи получим логически ошибочный набор данных. Именно поэтому решить задачу табличными триггерами невозможно — они срабатывают по каждому оператору модификации данных.

Реальная задача слегка отличается от простейшего примера. Набор данных разложен в десятке таблиц, алгоритм бизнес правил контроля вылился в процедуру на 400 строк с обращением через линки к API на других серверах.

Для реализации такого контроля нужен был триггер, который срабатывает только один раз в транзакции по событию COMMIT, с возможностью отката транзакции по результату отработки процедуры контроля бизнес логики. Такой триггер нашелся.

CREATE OR REPLACE TRIGGER <mv_as>
  AFTER DELETE OR INSERT OR UPDATE  ON  MV

где MV — это:

CREATE MATERIALIZED VIEW MV 
  REFRESH COMPLETE ON COMMIT 
  AS SELECT <,,,,> FROM <tab>;

Рассмотрим на примере подробности реализации. Набор данных.

CREATE  TABLE EMP
 (EMPNO NUMBER(4) NOT NULL,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(17,2),
  DEPTNO NUMBER(2));

CREATE TABLE DEPT 
  (DEPTNO NUMBER(2) NOT NULL,
   DNAME VARCHAR2(14), 
   LOC VARCHAR2(13));

insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (31, 'OPERATIONS', 'CHICAGO');

insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7369, 'SMITH', 'CLERK', 7902, TIMESTAMP '1980-12-17 00:00:00', 2800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7499, 'ALLEN', 'SALESMAN', 7698, TIMESTAMP '1981-02-20 00:00:00', 1600, 300, 31);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7521, 'WARD', 'SALESMAN', 7698, TIMESTAMP '1981-02-22 00:00:00', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7566, 'JONES', 'MANAGER', 7839, TIMESTAMP '1981-04-02 00:00:00', 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7650, 'MARTIN', 'SALESMAN', 7698, TIMESTAMP '1981-09-28 00:00:00', 1251, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7698, 'BLAKE', 'MANAGER', 7839, TIMESTAMP '1981-05-01 00:00:00', 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7782, 'CLARK', 'MANAGER', 7839, TIMESTAMP '1981-06-09 00:00:00', 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7708, 'SCOTT', 'ANALYST', 7566, TIMESTAMP '1982-12-09 00:00:00', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7639, 'KING', 'PRESIDENT', null, TIMESTAMP '1981-11-17 00:00:00', 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7844, 'TURNER', 'SALESMAN', 7698, TIMESTAMP '1981-09-10 00:00:00', 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7876, 'ADAMS', 'CLERK', 7788, TIMESTAMP '1982-01-12 00:00:00', 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7900, 'JAMES', 'CLERK', 7698, TIMESTAMP '1981-12-03 00:00:00', 950, null, 31);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7902, 'FORD', 'ANALYST', 7566, TIMESTAMP '1981-12-03 00:00:00', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
   values (7934, 'MILLER', 'CLERK', 7782, TIMESTAMP '1982-01-23 00:00:00', 1300, null, 10);

alter table emp add constraint еmр_рk primary key(empno); 
alter table dept add constraint dept_pk primary key(deptno); 
alter table emp add constraint emp_fk_dept foreign key (deptno) references dept;

Данные примера является набором сущностей типа — «Сотрудник» с информацией о подразделении и месте расположения подразделения. Попробуем реализовать для этих данных бизнес правило ограничивающее количество сотрудников с должностью 'CLERK' в одном городе не более 2х.

В общем случае правил бизнес контроля может быть несколько а в одной транзакции проходить модификация информации нескольких сотрудников. Соответственно на момент commit-а нам надо иметь два набора информации:

— набор полей которые были модифицированы определит список бизнес правил которые должны быть проконтролированы;
— набор идентификаторов сотрудников подлежащих контролю.

Практический список правил бизнес контроля и их сложность позволяют без критической нагрузки на сервер осуществлять проверку каждого модифицированного сотрудника по всем реализованным правилам. Это допущение позволит в нашем случае упростить реализацию ограничения целостности.

Создаем таблицу которая будет содержать набор идентификаторов сотрудников модифицированных текущей транзакцией.

create table emp_chk ( emp_no NUMBER,
                            i NUMBER);
alter table emp_chk  add constraint PK_emp_no primary key (emp_no);

На все таблицы содержащие информацию для правила контроля вешаем триггера которыми будем вставлять в emp_chk идентификаторы модифицированных сотрудников. Некоторые комментарии к триггерам. Заказчик боевого применения функционала контроля потребовал совместимость с ORACLE-9, поэтому триггера не составные (compound).

Возможность отключения констрэйнта реализована пакетной функцией var_chk.chk_on. Использование для этой цели функции дает возможность управлять контролем не только статически (через конфигурационную таблицу) но и динамически (например для разных сеансов БД). Полный текст пакета будет приведен позже.

Использование MERGE вызвано желанием проводить модификацию одним оператором. Поле emp_chk.i является платой за использование MERGE т.к. написать MERGE без фразы WHEN MATCHED не удалось.

CREATE OR REPLACE TRIGGER emp_chk_ar
  AFTER DELETE OR INSERT OR UPDATE
  ON  emp
  FOR EACH ROW
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

MERGE INTO emp_chk a
   USING (SELECT  nvl(:new.empno, :old.empno) AS emp_no
                 , 1 AS i
             FROM dual  ) b
     ON (a.emp_no = b.emp_no)
     WHEN NOT MATCHED THEN  
          INSERT (a.emp_no, a.i) VALUES (b.emp_no, b.i)
     WHEN  MATCHED THEN  UPDATE
         SET a.i = b.i;
END emp_chk_ar;

CREATE OR REPLACE TRIGGER dept_chk_ar
  AFTER DELETE OR INSERT OR UPDATE
  ON  dept
  FOR  EACH  ROW
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

MERGE INTO emp_chk a
   USING (
           SELECT emp.empno AS emp_no , 1 AS i
             FROM emp 
            WHERE emp.deptno = NVL(:new.deptno, :old.deptno)
        ) b
     ON (a.emp_no = b.emp_no)
     WHEN NOT MATCHED THEN  
          INSERT (a.emp_no, a.i) VALUES (b.emp_no, b.i)
     WHEN  MATCHED THEN  UPDATE
          SET a.i = b.i;
END dept_chk_ar;

Очистку таблицы emp_chk в начале новой транзакции осуществляют следующие триггера. Управляет очисткой пакетная переменная var_chk.first_dml_in_commit:

CREATE OR REPLACE TRIGGER emp_chk_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  emp
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

IF var_chk.first_dml_in_commit = 1 THEN
     DELETE FROM emp_chk;
END IF;
var_chk.first_dml_in_commit := 0 ;
END emp_chk_bs;

CREATE OR REPLACE TRIGGER dept_chk_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  dept
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;
IF var_chk.first_dml_in_commit = 1 THEN
     DELETE FROM emp_chk;
END IF;
var_chk.first_dml_in_commit := 0;
END dept_chk_bs;

Создаем materialized view.

CREATE MATERIALIZED VIEW chk_emp_mv
  REFRESH COMPLETE ON COMMIT AS
      SELECT emp_no,i FROM emp_chk;

Триггер инициализирующий var_chk.first_dml_in_commit обеспечивает очистку EMP_CHK в начале транзакции.

CREATE OR REPLACE TRIGGER chk_emp_mv_bs
  BEFORE DELETE OR INSERT OR UPDATE
  ON  chk_emp_mv
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;
  var_chk.first_dml_in_commit := 1 ;
END chk_emp_mv_bs;

Собственно триггер запускающий бизнес контроль.

CREATE OR REPLACE TRIGGER chk_emp_mv_as
  AFTER DELETE OR INSERT OR UPDATE
  ON  chk_emp_mv
DECLARE
v_result NUMBER;
v_errtxt VARCHAR2(512);
BEGIN
IF var_chk.chk_on != 1 THEN
  RETURN;
END IF;

FOR cur IN (SELECT t.emp_no FROM CHK_EMP_MV t) LOOP
SELECT
XMLELEMENT("EMP",
XMLAGG(XMLELEMENT("EMPNO",tb.empno,
         XMLELEMENT("ENAME", tb.ename),
         XMLELEMENT("JOB", tb.job),
         XMLELEMENT("MGR", tb.mgr),
         XMLELEMENT("SAL", tb.sal),
         XMLELEMENT("DEPTNO", tb.DEPTNO),
         XMLELEMENT("COMM", tb.comm),
         XMLELEMENT("HIREDATE", TO_CHAR(tb.hiredate,'dd.mm.yyyy'))
         ,(SELECT
             XMLELEMENT("DEPT",
               XMLAGG(XMLELEMENT("DEPTNO",d.deptno,
                      XMLELEMENT("DNAME",d.dname),
                      XMLELEMENT("LOC",d.loc)
                        )))
               FROM dept d WHERE d.deptno = tb.deptno
         )
  ))
).GETCLOBVAL() INTO var_chk.var_emp_val
FROM   ( 
    SELECT * FROM  EMP
        WHERE emp.empno = cur.emp_no
    ) tb
GROUP BY empno,sal,mgr,job,hiredate,ename,deptno,comm
;
-- бизнес логика
v_result := emp_logic(cur.emp_no,v_errtxt);
var_chk.write_log(v_result,v_errtxt);
IF v_result = 1 THEN
  RAISE_APPLICATION_ERROR (-20555,v_errtxt);
END IF;
END LOOP;
END chk_emp_mv_as;

Некоторые комментарии к тексту CHK_EMP_MV_AS. Отладка и контроль функционирования констрэйнта можно облегчить логированием. Учтем, что в случае возникновения ошибки набор данных предъявленный для commit-а обычно откатывается и теряется. В данной реализации в лог пишется не только итоговый статус обработки но и весь набор данных сотрудника подвергшегося модификации предъявленный для commit-a независимо от результата обработки. Снимки наборов данных помещаются в поле emp_chk_log.XML. Лог пишется пакетной функцией var_chk.write_log в таблицу:

create table emp_chk_log
   (     ts        DATE,
     status        NUMBER,
        XML        CLOB
    );

Все бизнес правила реализованы в отдельной функции emp_logic. Функция не является членом пакета. Это позволяет разделить в разработке и сопровождении бизнес правила констрйнта и слой системных механизмов его функционирования. Ниже текст пакета var_chk.

CREATE OR REPLACE PACKAGE var_chk
AS
  first_dml_in_commit NUMBER;
  var_emp_val CLOB;

  FUNCTION chk_on  return NUMBER;
  PROCEDURE write_log (p_status NUMBER
                      ,p_err_txt VARCHAR2);
END;

CREATE OR REPLACE PACKAGE BODY var_chk
AS
--------------------
FUNCTION chk_on  RETURN NUMBER IS
-- Возвращает 1 - контроль включен
--            0 - контроль выключен           
BEGIN
  RETURN 1;
END chk_on;
---------------------
PROCEDURE write_log (p_status NUMBER
                    ,p_err_txt VARCHAR2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

INSERT INTO emp_chk_log
 (ts,status,xml,err_txt)
  VALUES (sysdate,p_status,var_emp_val,SUBSTR(p_err_txt,1,512));
COMMIT;
END write_log;
---------------------
BEGIN
  first_dml_in_commit :=1;
  dbms_lob.createtemporary(var_emp_val,true);
END;

Функция контроля бизнес правил.

CREATE OR REPLACE FUNCTION emp_logic
                 (p_emp_no NUMBER
                 ,p_errtxt OUT VARCHAR2 )
 RETURN NUMBER IS
 v_emp_count NUMBER;
 v_emp_loc dept.loc%TYPE;
BEGIN

SELECT dept.loc,COUNT(*) INTO v_emp_loc,v_emp_count
FROM emp, dept,
   (
     SELECT emp.job, dept.loc 
       FROM emp, dept
      WHERE emp.deptno = dept.deptno
        AND emp.empno = p_emp_no
        AND emp.job = 'CLERK'
) p
WHERE emp.deptno = dept.deptno
 AND p.loc=dept.loc 
 AND p.job=emp.job
GROUP BY dept.loc
;
IF v_emp_count > 2 
THEN p_errtxt:='Ошибка:В филиале '||v_emp_loc||' больше 2х клерков';
       RETURN 1;
END IF;

RETURN 0;
END emp_logic;

Проверим велосипед на ходу.

SQL> UPDATE EMP SET JOB='CLERK' WHERE  EMPNO=7708;

1 row updated.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-20555: Ошибка:В филиале DALLAS больше 2х клерков ORA-06512: at
"ZH.CHK_EMP_MV_AS", line 43
ORA-04088: error during execution of trigger 'ZH.CHK_EMP_MV_AS'


SQL> UPDATE EMP SET JOB='CLERK' WHERE  EMPNO=7369;    

1 row updated.

SQL> commit;

Commit complete.

SQL> select ts,status,to_char(xml) from emp_chk_log;

TS		    STATUS
--------------- ----------
TO_CHAR(XML)
--------------------------------------------------------------------------------
30-MAR-16		 1
<EMP><EMPNO>7708<ENAME>SCOTT</ENAME><JOB>CLERK</JOB><MGR>7566</MGR><SAL>3000</SA
L><DEPTNO>20</DEPTNO><COMM></COMM><HIREDATE>09.12.1982</HIREDATE><DEPT><DEPTNO>2
0<DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></DEPTNO></DEPT></EMPNO></EMP>

TS		    STATUS
--------------- ----------
TO_CHAR(XML)
--------------------------------------------------------------------------------
30-MAR-16		 0
<EMP><EMPNO>7369<ENAME>SMITH</ENAME><JOB>CLERK</JOB><MGR>7902</MGR><SAL>2800</SA
L><DEPTNO>20</DEPTNO><COMM></COMM><HIREDATE>17.12.1980</HIREDATE><DEPT><DEPTNO>2
0<DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC></DEPTNO></DEPT></EMPNO></EMP>


SQL>

Реальная реализация этого решения работает на трех десятках серверов центрального офиса и филиалов с весны 2015 года.

Автор: mamonm

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js