Логическая целостность данных, выгружаемых из БД

Задача: передавать данные из одной БД в другую, отсекая ненужные для интеграции данные – слишком старые или логически нерелевантные. При этом вся совокупность данных, выгруженных из БД-источника, должна оставаться логически целостной. Это значит,

• недопустима выгрузка данных, не отвечающих критериям выгрузки,
• недопустима невыгрузка данных, отвечающих критериям выгрузки, и
• недопустима выгрузка данных, ссылающихся на данные, которые не выгружаются (ссылочная целостность).

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

Задача усложняется, когда нужно выгружать из таблиц только часть данных, – а такая задача возникает довольно часто. Попробуем ее решить.

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

stuff                     неизменно   возрастает  возрастает
                          при update? при update? при insert?
------------------------- ----------- ----------- -----------
stuff_id    int              да                       да
stuff_date  date             да
descr       varchar(200)
kind        int              да
stage       int                          да
status      int
fisrt_date  date             да*
last_date   date             да*
cre_time    timestamp        да                       да
cre_user    varchar(10)      да
mod_time    timestamp                    да
mod_user    varchar(10)

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

Поля, имеющие "да" в столбце "Неизменно при update?", сохраняют свои значения неизменными после создания строки. Поле stuff_id - первичный ключ, и в силу этого неизменно. Поле stuff_date - дата документа (или фактической бизнес-операции), неизменно в силу неизменности свершившегося факта. Поле kind - тип документа (или фактической бизнес-операции), неизменно в силу неизменности свершившегося факта. Поля first_date и last_date задают период действия: поле first_date становится неизменным в день fisrt_date (когда период начался), а поле last_date становится неизменным на следующий после last_date день (когда период закончился) - в силу неизменности свершившегося факта. Поля cre_time и cre_user содержат метку времени создания строки и идентификатор пользователя, создавшего строку, соответственно, и неизменны в силу неизменности свершившегося факта. Назовем такие поля update-неизменными. (К сожалению, контролировать неизменность полей в БД можно только с помощью триггеров и нельзя с помощью ограничений целостности. См. мой пост Какого ограничения целостности не хватает в SQL.)

Поля, имеющие "да" в столбце "Возрастает при update?", имеют упорядоченный набор возможных значений, и приобретают эти значения только в порядке возрастания. Поле stage содержит номер текущего этапа обработки строки (этапа техпроцесса, этапа обработки заказа и т.п.) и его значение со временем только возрастает – в силу бизнес-логики. Поле mod_time содержит метку времени последнего обновления строки, и его значение со временем только возрастает в силу стрелы времени. Назовем такие поля update-упорядоченными. (Контролировать выполнение таких правил на уровне БД можно только с помощью триггеров и нельзя с помощью ограничений целостности.)

Поля, имеющие "да" в столбце "Возрастает при insert?" интересны тем, что их значения в новых строках, добавляемых в таблицу, всегда больше значений этих полей в строках, созданных ранее. Поле stuff_id - суррогатный ключ, значения которого получаются из числовой последовательности, и в силу этого новые значения больше полученных ранее. Поле cre_time - метка времени создания строки, и его значения в новых строках больше его значений в созданных ранее строках в силу стрелы времени. Назовем такие поля insert-упорядоченными. (Выполнение таких правил в БД поддерживается последовательностями и значениями по умолчанию для столбцов таблиц.)

Поля, не имеющие "да" ни в одном из столбцов, могут получать произвольные новые значения в рамках их доменов. Поле descr (описание) может получить в качестве значения любую строку длиной до 200 символов. Поле status (статус) может получить любое из двух значений 1 (активен) или 2 (неактивен) в произвольном порядке. Значением поля mod_user может стать идентификатор любого пользователя, который последним внес изменения в строку.

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

select *
from stuff
where kind = 1
    and stuff_date >= date '2022-01-01'
;

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

select *
from stuff
where stage >= 3
    or mod_date >= date '2022-01-01'
;

Строки, которые возвращались этим запросом ранее, будут вовзращаться и в будущем (конечно, если не будут удалены из таблицы stuff).

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

select *
from stuff
where cre_date >= date '2022-01-01'
;

Условия выборки из всех вышериведенных запросов могут использоваться в запросах для выгрузки текущих изменений (insert, update, delete) строк таблицы stuff. Они гарантированно не уберут из выгрузки изменений строку, которая ранее попала в полную выгрузку. И не позволят попасть в выгрузку изменений строкам, которые не попали бы в полную выгрузку сегодня.

(Технические аспекты выгрузки изменений см. в моем посте про захват и обработку изменений в БД.)

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

select *
from stuff
where status = 1 /* активен */
;

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

(Если же использовать условие status = 1 /* активен */ в первоначальной выгрузке и не использовать его в выгрузке изменений, то будут выгружаться изменения строк, которые не попали в первоначальную выгрузку. Если с помощью подобного условия вы хотите только ограничить объем первоначальной выгрузки, а далее выгружать все изменения, то без проблем - до тех пор, пока вы отдаете себе отчет в том, что делаете.)

Итак, мы рассмотрели, как включение в критерий выгрузки update-неизменных, update-упорядоченных, insert-упорядоченных и прозвольно изменяемых полей влияет

• на воспроизводимость результатов полной выгрузки при ее многократном выполнении,
• на возможность согласованного использования одного и того же критерия в полной выгрузке и выгрузке изменений.

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

Сделаем следующий шаг. Теперь у нас есть две таблицы, parent и child, связанные отношением один-ко-многим по полю parent_id.

parent
------
parent_id
name

child
-----
child_id
parent_id
status
child_type
child_date

Необходимо

• выгружать строки таблицы child, где child_type = "обычный" и child_date >= 2022-01-01, причем эти поля update-неизменны,
• выгружать строки таблицы parent, на которые ссылаются выгружаемые строки таблицы child.

При первоначальной выгрзуке

• Выгрузить строки child, где

  child_type = 'обычный'
  И
  child_date >= date '2022-01-01'
  

• Выгрузить строки parent, где

  существует дочерняя строка child, где
  child_type = 'обычный'
  И
  child_date >= date '2022-01-01'
  

При выгрузке изменений

• Выгрузить строку child, где

  child_type = 'обычный'
  И
  child_date >= date '2022-01-01'
  

• Выгрузить строки parent, где

  существует дочерняя строка child, где
  child_type = 'обычный'
  И
  child_date >= date '2022-01-01'
  

Раскроем выгрузку изменений строк parent:

• выгружать ли добавленную строку? Очевидно, что
  • у только что добавленной строки parent нет дочерних строк;
  • дочерние строки child, отвечающие критерию выгрузки, могут появиться – и тогда, если строка parent не выгружена, у внешних клиентов возникнет проблема ребенка-сироты - строка child без родительской строки.
• достаточно ли выгружать обновленные строки parent, для которых в настоящий момент есть дочерние строки, отвечающие критерию выгрузки? Очевидно, что
  • раньше у строки parent могли быть дочерние строки child, отвечающие критерию выгрузки, позднее удаленные, - и тогда, если строка parent не выгрузится, внешние клиенты не узнают об ее изменении;
  • в будущем у строки parent могут появиться дочерние строки child, отвечающие критерию выгрузки, – и тогда, если строка parent не выгружена, возникнет проблема ребенка-сироты.
• выгружать ли удаленную строку? Очевидно, что
  • раньше у строки parent могли быть дочерние строки child, отвечающие критерию выгрузки, позднее удаленные, - и тогда, если строка parent не выгружена, внешние клиенты не узнают об ее удалении.

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

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

Возможные решения этой проблемы:

1. при добавлении дочерней строки помечать родительскую строку как измененную – чтобы она также выгружалась;
2. выгружать все родительские строки при первоначальной выгрузке и изменения всех родительских строк позднее.

Первое решение чревато тем, что возникнет поток избыточных "изменений" строк parent – ведь те из них, которые уже имеют дочерние строки child, уже выгружались ранее. Это можно принять как умеренную плату за логическую целостность. А можно исключить избыточность, помечая как измененные только ранее не выгружавшиеся строки parent (а для этого, в общем случае, придется запоминать ранее выгруженные.)

Второе решение может привести к логической избыточности строк parent, поступающих внешним клиентам, если не для всех строк parent имеются дочерние строки child, отвечающие критерию выгрузки. Однако, не возникает поток избыточных "изменений", и это решение проще в реализации, чем первое.

Выбор за вами. Примите во внимание размер таблицы parent и частоту изменений в ней.

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

При первоначальной выгрзуке

• Выгрузить строки child, где

  child_type = 'обычный'
  И
  child_date >= date '2022-01-01'
  

• Выгрузить все строки parent.

При выгрузке изменений

• Выгрузить строку child, где

  child_type = 'обычный'
  И
  child_date >= date '2022-01-01'
  

• Выгрузить строку parent.

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

Исходя из трехуровнего логического представления – справочники, мастер-сущности, документы – можно предложить такой подход к выгрузке данных из реальной БД:

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

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

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

В заключение замечу, что все изложенное выше носит эмпирический и эвристический характер; выросло из опыта, поверено логикой и практикой.