воскресенье, 19 февраля 2017 г.

Передача изменений между БД: подход в духе KISS

В БД SOURCE в таблице itemz ведется некий справочник. Время от времени в справочник добавляются новые строки и изменяются существующие. Возможно, строки даже иногда удаляются. В данных этого справочника нуждается БД DEST, причем контракт состоит в передаче из БД SOURCE всех изменений справочника. И как же организована передача изменений БД SOURCE в БД DEST? А вот так.

На таблице itemz висит триггер для INSERT, UPDATE (и возможно DELETE) отдельных строк. При срабатывании триггер вставляет строку в служебную таблицу itemz_log, сохраняя в ней первичный ключ измененной строки справочника, время и тип изменения (INSERT, UPDATE или DELETE). Время от времени по расписанию запускается процедура, которая анализирует накопившиеся в itemz_log изменения и, если изменения имели место, вставляет строки с новыми и измененными данными справочника в интерфейсную таблицу int_itemz, откуда их заберет БД DEST. Для удаленных строк через интерфейсную таблицу передается первичный ключ и признак удаления. После чего обработанные записи об изменениях из itemz_log удаляются.

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

Этот же механизм реализован для случаев, когда данные для интерфейсной таблицы получаются запросом, соединяющим несколько таблиц БД SOURCE. На каждой из таблиц висит триггер, который регистрирует изменения на уровне строк и вставляет записи об этих изменениях в соответствующие журнальные таблицы %_log - свои для каждой исходной таблицы. Процедуры, формирующие строки для интерфейсных таблиц на основании зарегистрированных изменений, очень разные и подчас весьма изощренные, учитывающие специфику конкретного интерфейса.

Такие решения для передачи изменений между БД, с незначительными вариациями, мне встречались много раз. Предлагаю альтернативный механизм в духе KISS (Keep It Simple, Stupid) - простой в реализации и тиражировании, сравнительно с описанным выше.

Вернемся к справочнику itemz в БД SOURCE. Существует запрос, который возвращает все строки и столбцы справочника, которые должны быть в БД DEST. Этот запрос использовался при первичной интеграции двух систем для наполнения справочника в БД DEST (после чего передавались только изменения). Запрос выглядит так:

select id, name, descr, status from itemz;

Назовем его базовым запросом. Результат базового запроса - это снимок справочника itemz, отражающий его текущее состояние. Если мы запомним этот снимок в служебной таблице itemz_, то позднее сможем получить разницу между прошлым и текущим снимками с помощью двух запросов:

-- вставленные и измененные строки itemz
select id, name, descr, status from itemz
minus
select id, name, descr, status from itemz_
;

-- удаленные из таблицы itemz строки
select id, name, descr, status 
from itemz_
where id not in (select id from itemz)
;

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

Второй запрос возвращает строки, физически удаленные из таблицы itemz после того, как был сохранен прошлый снимок в таблице itemz_. Это строки, которых нет в результате базового запроса, выполненного сейчас, и которые были при выполнении базового запроса в прошлый раз. Назовем такие строки отрицательной дельтой, или дельтой-минус.

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

Алгоритм передачи дельты таков:

  1. получить текущий снимок базовым запросом
  2. получить дельту-плюс как разность текущего и прошлого снимков
  3. получить дельту-минус по разности первичных ключей прошлого и текущего снимков
  4. передать дельту-плюс и -минус в интерфейс
  5. сохранить текущий снимок как прошлый

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

Алгоритм универсален и в том плане, что он может выполняться как в случаях, когда удаляются строки в исходных таблицах, так и в случаях, когда строки не удаляются. Если строки не удаляются, то дельта-минус всегда будет пуста.

Проиллюстрирую сказанное на примере.

Итак, в БД SOURCE есть исходная таблица itemz, данные в которой изменяются пользователями.

-- исходная таблица
create table itemz (
    id number primary key,
    name varchar2(50) not null,
    descr varchar2(1000),
    status number(1) default 1 not null -- 1 active 2 frozen
);

А таблица int_itemz - интерфейсная таблица, через которую изменения в itemz передаются в БД DEST:

-- интерфейсная таблица
create table int_itemz (
    id number,
    name varchar2(50),
    descr varchar2(1000),
    status number(1),
    deleted number(1) default 0 not null -- 1 deleted 0 alive
);

(Для простоты предположим, что БД DEST удалит из нее строки, которые заберет, - тогда нам не нужен специальный признак для того, чтобы помечать такие строки.)

Наполним таблицу itemz данными:

SQL> insert into itemz values (1, 'Линейка', 'Металлическая', 1);
1 row inserted
SQL> insert into itemz values (2, 'Простой карандаш', 'KOH-I-NOOR', 1);
1 row inserted
SQL> insert into itemz values (3, 'Точилка', 'Для карандашей', 1);
1 row inserted
SQL> commit;
Commit complete

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

Создадим две служебные таблицы:

-- прошлый снимок
create table itemz_ (
    id number primary key,
    name varchar2(50) not null,
    descr varchar2(1000),
    status number(1)
);

-- текущий снимок и дельта
create table itemz_tt_ (
    id number primary key,
    name varchar2(50) not null,
    descr varchar2(1000),
    status number(1),
    delta number(1) default 0 /*не дельта*/ not null
);

И определим процедуру передачи изменений:

create or replace procedure process_itemz_delta
is
begin
    -- 1. получить текущий снимок базовым запросом
    insert into itemz_tt_ (
        id,
        name,
        descr,
        status,
        delta
    )
    select
        id,
        name,
        descr,
        status,
        0 /*не дельта*/
    from itemz
    ;
    -- 2. получить дельту-плюс как разность текущего и прошлого снимков
    merge into itemz_tt_ tgt
    using ( -- строки, которых не было в прошлый раз
        select
            id,
            name,
            descr,
            status
        from itemz_tt_
        minus
        select
            id,
            name,
            descr,
            status
        from itemz_
    ) src
    on (src.id = tgt.id)
    when matched then
        update set delta = 1 /*дельта-плюс*/
    ;
    -- 3. получить дельту-минус
    merge into itemz_tt_ tgt
    using ( -- строки, которые были в прошлый раз
        select
            id,
            name,
            descr,
            status
        from itemz_
    ) src
    on (src.id = tgt.id)
    when not matched then
        insert (
            id,
            name,
            descr,
            status,
            delta
        ) values (
            src.id,
            src.name,
            src.descr,
            src.status,
            2/*дельта-минус*/
        )
    ;
    -- 4. передать дельту-плюс и -минус в интерфейс
    insert into int_itemz (
        id,
        name,
        descr,
        status,
        deleted
    ) select
        id,
        name,
        descr,
        status,
        case delta when 2 then 1 else 0 end
    from itemz_tt_
    where delta != 0 /*дельта-плюс и минус*/
    ;
    -- 5. сохранить текущий снимок как прошлый
    execute immediate 'truncate table itemz_';
    insert into itemz_ (
        id,
        name,
        descr,
        status
    ) select
        id,
        name,
        descr,
        status
    from itemz_tt_
    where delta != 2 /*кроме дельты-минус*/
    ;
    execute immediate 'truncate table itemz_tt_';
end process_itemz_delta;
/

Протестируем работу процедуры.

SQL> select * from itemz_;
   ID NAME                 DESCR                STATUS
----- -------------------- -------------------- ------

SQL> select * from itemz_tt_;
   ID NAME                 DESCR                STATUS DELTA
----- -------------------- -------------------- ------ -----

SQL> select * from int_itemz;
   ID NAME                 DESCR                STATUS DELETED
----- -------------------- -------------------- ------ -------

SQL> -- первичная передача itemz в интерфейсную таблицу
SQL> begin process_itemz_delta; end;
  2  /
PL/SQL procedure successfully completed

SQL> select * from itemz_;
   ID NAME                 DESCR                STATUS
----- -------------------- -------------------- ------
    1 Линейка              Металлическая             1
    2 Простой карандаш     KOH-I-NOOR                1
    3 Точилка              Для карандашей            1

SQL> select * from itemz_tt_;
   ID NAME                 DESCR                STATUS DELTA
----- -------------------- -------------------- ------ -----

SQL> select * from int_itemz;
   ID NAME                 DESCR                STATUS DELETED
----- -------------------- -------------------- ------ -------
    1 Линейка              Металлическая             1       0
    2 Простой карандаш     KOH-I-NOOR                1       0
    3 Точилка              Для карандашей            1       0

Пусть БД DEST забрала и удалила строки из int_itemz:

SQL> delete from int_itemz;
3 rows deleted

Пока изменений в исходной таблице нет, процедура ничего не передает в интерфейсную таблицу:

SQL> begin process_itemz_delta; end;
  2  /
PL/SQL procedure successfully completed

SQL> select * from itemz_;
   ID NAME                 DESCR                STATUS
----- -------------------- -------------------- ------
    1 Линейка              Металлическая             1
    2 Простой карандаш     KOH-I-NOOR                1
    3 Точилка              Для карандашей            1

SQL> select * from itemz_tt_;
   ID NAME                 DESCR                STATUS DELTA
----- -------------------- -------------------- ------ -----

SQL> select * from int_itemz;
   ID NAME                 DESCR                STATUS DELETED
----- -------------------- -------------------- ------ -------

Добавим, изменим и удалим строки в itemz, тем самым создав условия для получения дельты-плюс и дельты-минус при следующем запуске процедуры process_itemz_delta:

SQL> -- Новый айтем
SQL> insert into itemz values (4, 'Циркуль', 'Без комментариев', 1);
1 row inserted

SQL> -- Линейка 50 см
SQL> update itemz set descr = '50 см' where id = 1;
1 row updated

SQL> -- Точилка больше не нужна
SQL> delete itemz where id = 3;
1 row deleted

SQL> commit;
Commit complete

SQL> select * from itemz;

   ID NAME                 DESCR                STATUS
----- -------------------- -------------------- ------
    1 Линейка              50 см                     1
    2 Простой карандаш     KOH-I-NOOR                1
    4 Циркуль              Без комментариев          1

SQL> begin process_itemz_delta; end;
  2  /
PL/SQL procedure successfully completed

SQL> select * from itemz_;
   ID NAME                 DESCR                STATUS
----- -------------------- -------------------- ------
    1 Линейка              50 см                     1
    2 Простой карандаш     KOH-I-NOOR                1
    4 Циркуль              Без комментариев          1

SQL> select * from itemz_tt_;
   ID NAME                 DESCR                STATUS DELTA
----- -------------------- -------------------- ------ -----

SQL> select * from int_itemz;
   ID NAME                 DESCR                STATUS DELETED
----- -------------------- -------------------- ------ -------
    1 Линейка              50 см                     1       0
    4 Циркуль              Без комментариев          1       0
    3 Точилка              Для карандашей            1       1

Как видим, изменения попали в интерфейсную таблицу.

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

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

Вообще, интерфейс между системами проектируется согласно контракту между ними (эта игра для двоих), на который влияет объем передаваемых данных. Выше был рассмотрен контракт на передачу только изменений через интерфейсную таблицу. Если общее количество строк, которые формирует базовый запрос в БД-источнике, невелико (сколько это - думайте сами), а БД-приемник всегда MERGE'ит полученные через интерфейс данные в свои таблицы, то можно всегда передавать все строки, формируемые базовым запросом. При этом реализуется контракт на передачу всех строк и на стороне БД-источника совсем не будет издержек на формирование дельты.

В заключение замечу, что шаг 4 предложенного алгоритма "Передать дельту-плюс и -минус в интерфейс" не требует передачи данных именно через интерфейсную таблицу. Сформированная дельта может быть передана во внешнюю систему как угодно. Например, дельта может быть выгружена во внешний файл, который далее будет доставлен в другую систему для обработки; или дельта может быть передана через веб-сервис. А возможно, дельта нужна для формирования отчета, а не для передачи в другую систему. Поэтому шаг 4 допускает разные варианты реализации, в зависимости от поставленной задачи.

Удаляю демонстрационные таблицы и процедуру:

SQL> drop procedure process_itemz_delta;
Procedure dropped
SQL> drop table int_itemz;
Table dropped
SQL> drop table itemz_tt_;
Table dropped
SQL> drop table itemz_;
Table dropped
SQL> drop table itemz;
Table dropped

Комментариев нет:

Отправить комментарий