Контроль целостности данных — одна из важнейших функций СУБД. Чем тщательнее этот контроль организован, тем проще реализовывать прикладную логику, ведь чем больше ограничений контролируется базой данных, тем меньше вариаций «а что, если» следует предусмотреть при реализации логики. В то же самое время контроль целостности оказывается достаточно удобно использовать и для проверки корректности работы прикладного слоя. Что-то вроде юнит-тестов. «Лишняя» проверка, порой может сослужить очень добрую службу.
Традиционный набор ограничений — ограничение первичного, внешнего ключей, уникальности при использовании нормализации позволяет удовлетворить подавляющее большинство случаев потребности контроля. Однако в случае, когда ограничение оказывается зависимым от значений в нескольких таблицах и строках, этих средств оказывается недостаточно. Такие ограничения приходится реализовывать триггерной логикой. И реализация далеко не всегда оказывается проста. Разработчику приходится держать в уме то, что модификация данных может проводиться в конкурентной среде, потому необходимо самостоятельно заботиться о блокировании ресурсов, при этом, еще и пытаясь избегать взаимных блокировок. Реализация ограничения строки может потребовать доступа к другим строкам этой же таблицы, что, в свою очередь является ограничением платформы — Oracle не позволяет обращаться к изменяемому в настоящее время(мутирующему) набору данных.
Но есть и другой путь. В некоторых случаях оказывается возможным использование ограничений, наложенных на материализованные представления, обновляемые по факту фиксации транзакций (fast refresh on commit). Такие ограничения будут работать как отложенные (deferred) и не будут позволять зафиксировать транзакцию, если вдруг целостность данных оказалась нарушенной. В рамках же модифицирующей транзакции ограничения могут нарушаться. С одной стороны это упрощает модификацию данных, с другой, мешает идентифицировать источник ошибки. В этой статье я хотел бы привести пару простых примеров реализации таких ограничений.
Реализацию подхода я хотел бы показать на вымышленном упрощенном примере. Оказалось достаточно сложно подобрать такой пример, чтобы он был достаточно прост для восприятия, но при этом, чтобы применение подхода было оправданным, не обессудьте если что вдруг получилось не так.
Пусть мы имеем необходимость учета товара в разрезе зоны размещения. Размещением в данном случае выступает магазин(S) или склад(W).
Зона — территория физическая или логическая каждого конкретного размещения. К примеру — торговый зал, или же даже полки торгового зала, материальная комната, холодильник, зона утерянного товара. Каждое размещение может иметь более одной зоны каждого типа, но одна зона каждого типа обязательно должна быть помечена как основная. Она будет использоваться по умолчанию, если зона операции явно не определена. Основная зона должна быть одна и только одна для каждого типа зоны. Это будет первый вид ограничения, который мы попытаемся реализовать.
Второй тип ограничения — состав зон. В нашем примере ограничимся лишь заданием правил для трех типов зон:
Каждая запись о размещении может находиться в трех состояниях — черновик (W), активная (A), не активная (I). Когда запись находится в состоянии «Черновик», мы даем пользователю наибольшую свободу действий и позволяем нарушать это ограничение.
Для реализации этого ограничения создадим материализованное представление, которое будет производить подсчет основных зон для каждого типа зоны размещения, и сверху на него наложим ограничение, контролирующее строгое равенство единице рассчитанного значения. Для запросов, на основе которых строятся материализованные представления определен целый ряд ограничений, который, к тому еще сильно ужесточается наложением требований к обновлению методом fast. В нашем случае мы имеем аггрегирующее материализованное представление, а потому должны создать materialized view log для таблицы zone, включающий rowid и новые значения, список полей которого должен включить все значения, которые могут повлиять на результат запроса
Так же мы обязаны включить в результат, возвращаемый запросом значение «count(*)»
Здесь следует отметить: для того чтобы оценить, может ли материализованное представление построенное по запросу использовать для обновления метод fast, существует процедура dbms_mivew.explain_mview. Крайне желательно использовать ее для контроля доступен ли для представления метод обновления fast. К примеру, если бы мы забыли указать в запросе count(*), материализованное представление с успехом бы создалось и корректно работало при выполнении операции вставки. Однако при модификации, удалении значение primary_count — не пересчитывалось бы, что нарушило бы логику нашего ограничения. Однако если мы используем explain_mview, оракл услужливо подскажет нам наш просчет.
Итак, материализованное представление создано, осталось только добавить ограничение
Прошу обратить внимание на то, что ограничение создается отложенным(deferred). Дело в том, что в процессе обновления представления ораклом, на каком-то промежуточном этапе, может оказаться что ограничение будет временно нарушено. Чтобы избежать таких ложных срабатываний, лучше устанавливать такие ограничения заведомо отложенными.
Проверим работу этого ограничения
Попытаемся создать зону с типом, не имеющем отметки «основной».
Попытаемся определить две основные зоны для размещения с одинаковым типом.
Это ограничение от предыдущего отличается тем, что опирается на значения не одной таблицы, а двух. Т.е. одновременно необходимо удовлетворить требования метода обновления fast для представлений с соединениями и аггрегирующих представлений. Но это не возможно. Мы не можем одновременно вывести в результат и rowid присоединенной строки и count(*). По этой причине придется строить каскад материализованных представлений. В одном будет производится соединение наборов данных, в другом — аггрегация.
Для начала необходимо создать materialized veiw log для таблицы размещений. Для таблицы зон будет использован ранее созданный лог.
Следом создаем join mivew. К сожалению ANSI синтаксис здесь оракл не воспринимает, испльзуем old-style join.
Создаем materialized veiw log для join представления
Создаем аггрегирующеее материализованное представление
ну и сами ограничения
Проверим работу ограничений:
Размещение успешно создано в статусе «Черновик». Попробуем активировать его:
Нет. Нельзя активировать размещение, если для него не определены необходимые для его типа зоны
Очень удобно иметь такие ограничения на этапе тестирования, старта, стабилизации проектов, когда не достает доверия к корректности работы логики, а данные могут модифицироваться и мимо прикладного звена.
Очевидно, что не стоит использовать такой подход в случаях, когда он дает заметную просадку по производительности.
В случае модификации, уточнения схемы данных, содержимое представлений может оказаться не достоверным и потребует полного обновления. Если время полного обновления материализованного представления может поставить под угрозу выполнение регламента технических работ, наверное, тоже не стоит использовать такой подход.
Традиционный набор ограничений — ограничение первичного, внешнего ключей, уникальности при использовании нормализации позволяет удовлетворить подавляющее большинство случаев потребности контроля. Однако в случае, когда ограничение оказывается зависимым от значений в нескольких таблицах и строках, этих средств оказывается недостаточно. Такие ограничения приходится реализовывать триггерной логикой. И реализация далеко не всегда оказывается проста. Разработчику приходится держать в уме то, что модификация данных может проводиться в конкурентной среде, потому необходимо самостоятельно заботиться о блокировании ресурсов, при этом, еще и пытаясь избегать взаимных блокировок. Реализация ограничения строки может потребовать доступа к другим строкам этой же таблицы, что, в свою очередь является ограничением платформы — Oracle не позволяет обращаться к изменяемому в настоящее время(мутирующему) набору данных.
Но есть и другой путь. В некоторых случаях оказывается возможным использование ограничений, наложенных на материализованные представления, обновляемые по факту фиксации транзакций (fast refresh on commit). Такие ограничения будут работать как отложенные (deferred) и не будут позволять зафиксировать транзакцию, если вдруг целостность данных оказалась нарушенной. В рамках же модифицирующей транзакции ограничения могут нарушаться. С одной стороны это упрощает модификацию данных, с другой, мешает идентифицировать источник ошибки. В этой статье я хотел бы привести пару простых примеров реализации таких ограничений.
Постановка задачи
Реализацию подхода я хотел бы показать на вымышленном упрощенном примере. Оказалось достаточно сложно подобрать такой пример, чтобы он был достаточно прост для восприятия, но при этом, чтобы применение подхода было оправданным, не обессудьте если что вдруг получилось не так.
Пусть мы имеем необходимость учета товара в разрезе зоны размещения. Размещением в данном случае выступает магазин(S) или склад(W).
Зона — территория физическая или логическая каждого конкретного размещения. К примеру — торговый зал, или же даже полки торгового зала, материальная комната, холодильник, зона утерянного товара. Каждое размещение может иметь более одной зоны каждого типа, но одна зона каждого типа обязательно должна быть помечена как основная. Она будет использоваться по умолчанию, если зона операции явно не определена. Основная зона должна быть одна и только одна для каждого типа зоны. Это будет первый вид ограничения, который мы попытаемся реализовать.
Второй тип ограничения — состав зон. В нашем примере ограничимся лишь заданием правил для трех типов зон:
- Зона хранения (K) — зона в которой осуществляется хранение товара эта зона обязательна для склада, но может быть определена и для магазина.
- Зона торгового зала(S) — обязательна для магазина и не может быть определена для склада
- Зона утерянного товара(L) — логическая зона, на которую будет перемещаться товар, статус которого не ясен, требует прояснения. Обязательна и для склада и для магазина
Каждая запись о размещении может находиться в трех состояниях — черновик (W), активная (A), не активная (I). Когда запись находится в состоянии «Черновик», мы даем пользователю наибольшую свободу действий и позволяем нарушать это ограничение.
Реализация
таблицы
create table location (
loc number primary key
,loc_type varchar2(1 char) check (loc_type in ('S','W'))
,status varchar2(1 char) not null check (status in ('W','A','I'))
,loc_desc varchar2(200 char)
);
create table zone(
zone number primary key
,loc number references location(loc)
,is_pirmary varchar(1 char) not null check (is_pirmary in ('Y','N'))
,zone_type varchar2(1 char) not null
,zone_desc varchar2(200 char)
);
insert into location values (1,'S','W','Магазин 1 без ошибок');
insert into zone values (1,1,'Y','K','Зона хранения магазина 1');
insert into zone values (2,1,'Y','S','Торговый зал магазина 1');
insert into zone values (3,1,'Y','L','Зона утерянного товара магазина 1');
commit;
Ограничение количества основных зон
Для реализации этого ограничения создадим материализованное представление, которое будет производить подсчет основных зон для каждого типа зоны размещения, и сверху на него наложим ограничение, контролирующее строгое равенство единице рассчитанного значения. Для запросов, на основе которых строятся материализованные представления определен целый ряд ограничений, который, к тому еще сильно ужесточается наложением требований к обновлению методом fast. В нашем случае мы имеем аггрегирующее материализованное представление, а потому должны создать materialized view log для таблицы zone, включающий rowid и новые значения, список полей которого должен включить все значения, которые могут повлиять на результат запроса
create materialized view log
on zone with rowid
,sequence
(zone,loc,zone_type,is_primary)
including new values
Так же мы обязаны включить в результат, возвращаемый запросом значение «count(*)»
create materialized view mv$zoneloc_pimary$chk
refresh fast on commit
as
select loc
,zone_type
,count(decode(is_primary,'Y',1)) primary_count
,count(*) cnt
from zone
group by loc,zone_type;
Здесь следует отметить: для того чтобы оценить, может ли материализованное представление построенное по запросу использовать для обновления метод fast, существует процедура dbms_mivew.explain_mview. Крайне желательно использовать ее для контроля доступен ли для представления метод обновления fast. К примеру, если бы мы забыли указать в запросе count(*), материализованное представление с успехом бы создалось и корректно работало при выполнении операции вставки. Однако при модификации, удалении значение primary_count — не пересчитывалось бы, что нарушило бы логику нашего ограничения. Однако если мы используем explain_mview, оракл услужливо подскажет нам наш просчет.
SQL> set serveroutput on
SQL> declare
2 result SYS.ExplainMVArrayType;
3 begin
4 dbms_mview.explain_mview(mv => 'select loc
5 ,zone_type
6 ,count(decode(is_primary,''Y'',1)) primary_counnt
7 --,count(*) cnt
8 from zone
9 group by loc,zone_type'
10 ,msg_array => result
11 );
12 for i in 1..result.count
13 loop
14 dbms_output.put(rpad(result(i).capability_name,30,' '));
15 dbms_output.put(' '||result(i).POSSIBLE);
16 dbms_output.put(' '||result(i).MSGTXT);
17 dbms_output.put_line(null);
18 end loop;
19 end;
20 /
PCT F
REFRESH_COMPLETE T
REFRESH_FAST T
REWRITE T
PCT_TABLE F relation is not a partitioned table
REFRESH_FAST_AFTER_INSERT T
REFRESH_FAST_AFTER_ONETAB_DML F COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ANY_DML F see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT F PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH T
REWRITE_PARTIAL_TEXT_MATCH T
REWRITE_GENERAL T
REWRITE_PCT F general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE F relation is not a partitioned table
PL/SQL procedure successfully completed
Итак, материализованное представление создано, осталось только добавить ограничение
alter table mv$zoneloc_pimary$chk
add constraint zone_loc_primary$chk
check (primary_count=1)
deferrable initially deferred;
Прошу обратить внимание на то, что ограничение создается отложенным(deferred). Дело в том, что в процессе обновления представления ораклом, на каком-то промежуточном этапе, может оказаться что ограничение будет временно нарушено. Чтобы избежать таких ложных срабатываний, лучше устанавливать такие ограничения заведомо отложенными.
Проверим работу этого ограничения
SQL> insert into location values (2,'S','W','Магазин 2 нарушения ограниченя основной зоны ');
1 row inserted
SQL> commit;
Commit complete
Попытаемся создать зону с типом, не имеющем отметки «основной».
SQL> insert into zone values (4,2,'N','S','Основной торговый зал не определен');
1 row inserted
SQL> commit;
ORA-02091: transaction rolled back
ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated
Попытаемся определить две основные зоны для размещения с одинаковым типом.
SQL> insert into zone values (5,2,'Y','L','Первая основная зона утерянного товара');
1 row inserted
SQL> commit;
Commit complete
SQL> insert into zone values (6,2,'Y','L','Вторая основная зона утерянного товара');
1 row inserted
SQL> commit;
ORA-02091: transaction rolled back
ORA-02290: check constraint (ZTXN.ZONE_LOC_PRIMARY$CHK) violated
Ограничение состава зон размещения
Это ограничение от предыдущего отличается тем, что опирается на значения не одной таблицы, а двух. Т.е. одновременно необходимо удовлетворить требования метода обновления fast для представлений с соединениями и аггрегирующих представлений. Но это не возможно. Мы не можем одновременно вывести в результат и rowid присоединенной строки и count(*). По этой причине придется строить каскад материализованных представлений. В одном будет производится соединение наборов данных, в другом — аггрегация.
Для начала необходимо создать materialized veiw log для таблицы размещений. Для таблицы зон будет использован ранее созданный лог.
create materialized view log
on location with rowid
,sequence
(loc,loc_type,status)
including new values;
Следом создаем join mivew. К сожалению ANSI синтаксис здесь оракл не воспринимает, испльзуем old-style join.
create materialized view mv$location$zone$join
refresh fast on commit
as
select l.loc
,l.loc_type
,z.zone
,z.zone_type
,l.rowid l_rowid
,z.rowid z_rowid
from location l
,zone z
where z.loc(+) = l.loc
and l.status in ('A','I')
Создаем materialized veiw log для join представления
create materialized view log
on mv$location$zone$join
with rowid
,sequence
(loc,loc_type,zone_type)
including new values;
Создаем аггрегирующеее материализованное представление
create materialized view mv$location$zone$agg
refresh fast on commit
as
select loc
,loc_type
,count(decode(zone_type,'K',1)) K_cnt
,count(decode(zone_type,'S',1)) S_cnt
,count(decode(zone_type,'L',1)) L_cnt
,count(*) cnt
from mv$location$zone$join
group by loc,loc_type;
ну и сами ограничения
alter table mv$location$zone$agg
add constraint wh_zones_chk
check(loc_type != 'W' or K_cnt > 0 and S_cnt = 0 and L_cnt > 0)
deferrable initially deferred;
alter table mv$location$zone$agg
add constraint store_zones_chk
check(loc_type != 'S' or K_cnt >= 0 and S_cnt > 0 and L_cnt > 0)
deferrable initially deferred;
Проверим работу ограничений:
SQL> insert into location (loc,loc_type,status,loc_desc)
2 values (3,'S','W','Магазин 3 не определены обязательные зоны');
1 row inserted
SQL> commit;
Commit complete
Размещение успешно создано в статусе «Черновик». Попробуем активировать его:
SQL> update location set status = 'A' where loc = 3;
1 row updated
SQL> commit;
ORA-02091: transaction rolled back
ORA-02290: check constraint (ZTXN.STORE_ZONES_CHK) violated
Нет. Нельзя активировать размещение, если для него не определены необходимые для его типа зоны
SQL> insert into zone(zone,loc,is_primary,zone_type) values (7,3,'Y','S');
1 row inserted
SQL> insert into zone(zone,loc,is_primary,zone_type) values (8,3,'Y','L');
1 row inserted
SQL> update location set status = 'A' where loc = 3;
1 row updated
Commit complete
Заключение
Когда это использовать
В первую очередь тогда, когда связь на столько сложна, что высок риск что разработчик приложения сможет не все учесть, а рассогласование критично. Я впервые такой подход использовал, когда проектировал структуру, закольцованную на себя же внешними ключами через семь таблиц. Причем эти таблицы ведутся разными бизнес-подразделениями. Это ограничение стоит и по сей день. И по сей день пользователи находят лазейки, шлют скришноты когда это ограничение срабатывает, а воспроизвести, чтоб закрыть лазейку в прикладном модуле — не удается, требуется стечение обстоятельств от нескольких пользователей.Очень удобно иметь такие ограничения на этапе тестирования, старта, стабилизации проектов, когда не достает доверия к корректности работы логики, а данные могут модифицироваться и мимо прикладного звена.
Когда это не использовать
Очевидно, что не стоит использовать такой подход в случаях, когда он дает заметную просадку по производительности.
В случае модификации, уточнения схемы данных, содержимое представлений может оказаться не достоверным и потребует полного обновления. Если время полного обновления материализованного представления может поставить под угрозу выполнение регламента технических работ, наверное, тоже не стоит использовать такой подход.
