Материализованные представления, как средство контроля целостности данных

Источник: habrahabr

Контроль целостности данных - одна из важнейших функций СУБД. Чем тщательнее этот контроль организован, тем проще реализовывать прикладную логику, ведь чем больше ограничений контролируется базой данных, тем меньше вариаций "а что, если" следует предусмотреть при реализации логики. В то же самое время контроль целостности оказывается достаточно удобно использовать и для проверки корректности работы прикладного слоя. Что-то вроде юнит-тестов. "Лишняя" проверка, порой может сослужить очень добрую службу.

Традиционный набор ограничений - ограничение первичного, внешнего ключей, уникальности при использовании нормализации позволяет удовлетворить подавляющее большинство случаев потребности контроля. Однако в случае, когда ограничение оказывается зависимым от значений в нескольких таблицах и строках, этих средств оказывается недостаточно. Такие ограничения приходится реализовывать триггерной логикой. И реализация далеко не всегда оказывается проста. Разработчику приходится держать в уме то, что модификация данных может проводиться в конкурентной среде, потому необходимо самостоятельно заботиться о блокировании ресурсов, при этом, еще и пытаясь избегать взаимных блокировок. Реализация ограничения строки может потребовать доступа к другим строкам этой же таблицы, что, в свою очередь является ограничением платформы - 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

Заключение

Когда это использовать
В первую очередь тогда, когда связь на столько сложна, что высок риск что разработчик приложения сможет не все учесть, а рассогласование критично. Я впервые такой подход использовал, когда проектировал структуру, закольцованную на себя же внешними ключами через семь таблиц. Причем эти таблицы ведутся разными бизнес-подразделениями. Это ограничение стоит и по сей день. И по сей день пользователи находят лазейки, шлют скришноты когда это ограничение срабатывает, а воспроизвести, чтоб закрыть лазейку в прикладном модуле - не удается, требуется стечение обстоятельств от нескольких пользователей.

Очень удобно иметь такие ограничения на этапе тестирования, старта, стабилизации проектов, когда не достает доверия к корректности работы логики, а данные могут модифицироваться и мимо прикладного звена.

Когда это не использовать

Очевидно, что не стоит использовать такой подход в случаях, когда он дает заметную просадку по производительности.

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

Новое решение по интеграции с базами данных для SQLBase.

Сертификат MySQL 5.0 Developer (CMDEV).

IBM Rational ClearQuest (FAQ).

Выполнение триггеров в определенном порядке (исходники).

Главная » Sql