Управление данными по времени в DB2 с обеспечением темпоральной согласованности

Обеспечение основанной на времени ссылочной целостности

Новые темпоральные функции, реализованные в выпуске IBM® DB2® 10, предоставляют мощные возможности для управления данными на основе времени. Например, для каждой строки данных вы можете назначить диапазон дат, определяющий, когда эта строка считается допустимой для ваших приложений или для вашего бизнеса. Таблицы, способные отслеживать такую бизнес-достоверность, называются темпоральными таблицами прикладного времени, а их периоды бизнес-времени могут относиться к прошлому, настоящему или будущему времени. Для родительской таблицы и дочерней таблицы, между которыми существует отношение на основе внешнего ключа, понятие ссылочной целостности можно расширить таким образом, чтобы охватить не только традиционное значение ключа, но и период бизнес-времени для любой выбранной строки. Предлагаемая статья рассматривает такую темпоральную ссылочную целостность и описывает реализацию согласованности по времени в СУБД DB2. Материал данной статьи применим как к продукту DB2 for z/OS®, так и к продукту DB2 for Linux®, UNIX®, and Windows®. Все приводимые в статье SQL-фрагменты были протестированы с продуктом DB2 10.1 for LUW.

Маттиас Никола, технический специалист, IBM

Доктор Никола (Nicola) является ведущим техническим специалистом по производительности DB2/XML в Лаборатории IBM в Силиконовой Долине (Silicon Valley Lab). Он работает над всеми аспектами производительности XML в DB2, включая XQuery, SQL/XML и все возможности, присущие XML в DB2. Доктор Никола тесно сотрудничает с коллективами разработчиков DB2 XML, также как и с заказчиками и деловыми партнерами, использующими XML, помогая им в проектировании, реализации и оптимизации решений на XML. До начала работы в IBM Доктор Никола работал над производительностью информационных хранилищ в Informix Software. Он также участвовал в течение четырех лет в исследовательских и промышленных проектах по распределенным и реплицированным базам данных. Он получил докторскую степень по вычислительной технике в 1999 в Политехническом Университете Аахена, Германия.



Мартин Соммерландт, специалист по DB2, консультант

Martin SommerlandtMartin Sommerlandt (Мартин Соммерландт) является обладателем сертификата IBM по разработке приложений DB2 и сертификата IBM по администрированию баз данных DB2. Во время работы в IBM он занимал должность инженера по производительности в лаборатории IBM Silicon Valley Lab, где отвечал за тестирование производительности и за анализ новых функций DB2, включая темпоральные таблицы.



28.06.2013

Введение в темпоральное управление данными с помощью DB2

В продукте DB2 10 for z/OS и в продукте DB2 10 for Linux, UNIX, and Windows реализовано управление данными на основе времени, которое позволяет манипулировать данными в прошедшем, в настоящем и в будущем времени, сохраняя при этом полную хронологию всех изменений этих данных. Набор темпоральных функций в DB2 носит название Time Travel Query.

DB2 поддерживает следующие три типа темпоральных таблиц.

  • Темпоральные таблицы системного времени — в этих таблицах DB2 прозрачно сохраняет хронологию обновления и удаления старых строк на протяжении определенного времени. С помощью новых стандартных конструкций языка SQL пользователи могут "вернуться в прошедшее время" и запросить из базы данные на любой выбранный момент в прошлом. Эта возможность базируется на внутрисистемных метках времени, которые DB2 использует для управления системным временем, которое иногда носит название время транзакции.
  • Темпоральные таблицы прикладного времени — в этих таблицах приложения хранят даты или метки времени для описания бизнес-достоверности данных. Новые SQL-конструкции позволяют приложениям вставлять, запрашивать, обновлять и удалять данные в прошлом, в настоящем и в будущем времени. DB2 автоматически применяет ограничения и разделения строк с целью корректного поддержания предоставленного приложением бизнес-времени, которое иногда носит название достоверное время.
  • Битемпоральные таблицы — эти таблицы оперируют как системным, так и бизнес-временем. Битемпоральные таблицы сочетают все возможности темпоральных таблиц системного времени и прикладного времени, что позволяет приложениям управлять бизнес-достоверностью данных, в то время как DB2 сохраняет полную хронологию всех обновлений и удалений данных.

При дальнейшем изложении материала данной статьи мы предполагаем, что читатель знаком с базовыми сведениями по темпоральным таблицам прикладного времени в DB2. Рассмотрение темпоральной ссылочной целостности также относится к бизнес-времени в битемпоральных таблицах. Читатель должен знать, как создавать и использовать такие таблицы, а также понимать, как DB2 осуществляет разделение строк, когда пользователь обновляет или удаляет данные для определенного интервала бизнес-времени. Вводные сведения по этим темам содержатся в статье A Matter of Time: Temporal Data Management in DB2 (Фактор времени: управление данными по времени в продукте DB2 10).


Что такое ссылочная целостность?

Пример

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

Традиционная ссылочная целостность

В листинге1 показаны две таблицы, используемые при ведении бизнеса в вышеупомянутой вымышленной компании. Родительская таблица product_avail содержит по одной строке для каждого доступного товара, в которой указывается поставщик этого товара. Дочерняя таблица promotion имеет по одной строке для каждого специального предложения, обозначаемого идентификатором промоакции, с идентификатором соответствующего товара и сниженной ценой на этот товар. Обе таблицы могли бы иметь и другие столбцы, однако для простоты изложения мы ограничимся этими столбцами, которых вполне достаточно на данный момент.

Листинг 1. Простой пример ссылочной целостности между родительской и дочерней таблицами
CREATE TABLE product_avail(
    prodID     INTEGER NOT NULL, 
    supplier   VARCHAR(32),
  PRIMARY KEY(prodID) );

CREATE TABLE promotion(
    promoID    INTEGER NOT NULL, 
    prodID     INTEGER NOT NULL, 
    price      DECIMAL(10,2), 
  PRIMARY KEY(promoID),
  FOREIGN KEY (prodID) REFERENCES product_avail(prodID) );

Поскольку промоакция как таковая может распространяться только на доступный (имеющийся в наличии) товар, каждая строка в таблице promotion должна содержать идентификатор prodID, присутствующий в таблице product_avail. Это условие и называется ссылочной целостностью, которая декларируется и реализуется посредством ограничения, налагаемого на таблицу promotion посредством внешнего ключа (FK) . Вследствие этого ограничения любая попытка вставить в таблицу promotion или обновить в ней идентификатор prodID, который не существует в таблице product_avail, отклоняется системой DB2.

Введение времени

В нашей вымышленной компании один и тот же товар в разное время года может поставляться разными поставщиками. Кроме того, компания может для одного и того же товара проводить различные промоакции в разные периоды времени. Поскольку время оказывается столь важным измерением бизнеса, мы создаем таблицы из листинга 1 как темпоральные таблицы прикладного времени; это означает, что мы включаем период BUSINESS_TIME в обе таблицы (см. листинг 2).

Добавленный период BUSINESS_TIME состоит из двух столбцов дат или меток времени и декларации PERIOD, которая определяет эту пару столбцов как период. Мы также хотим расширить определение первичного ключа с помощью дополнительных ключевых слов BUSINESS_TIME WITHOUT OVERLAPS, которые указывают, что одинаковое значение prodID может присутствовать в нескольких строках до тех пор, пока периоды BUSINESS_TIME этих строк не перекрываются. Аналогичное расширение первичного ключа применено к таблице promotion.

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

Листинг 2. Таблицы из листинга 1, преобразованные в темпоральные таблицы прикладного времени
CREATE TABLE product_avail(
    prodID       INTEGER NOT NULL,
    supplier     VARCHAR(32),
    avail_start  DATE NOT NULL,
    avail_end    DATE NOT NULL,
    PERIOD BUSINESS_TIME (avail_start, avail_end),
    PRIMARY KEY(prodID, BUSINESS_TIME WITHOUT OVERLAPS)  );
                
CREATE TABLE promotion(
    promoID    INTEGER NOT NULL,
    prodID     INTEGER NOT NULL,
    price      DECIMAL(10,2),
    promo_start  DATE NOT NULL,
    promo_end    DATE NOT NULL,
    PERIOD BUSINESS_TIME (promo_start, promo_end),
    PRIMARY KEY(promoID, BUSINESS_TIME WITHOUT OVERLAPS)  );

На рис. 1 показаны две таблицы с информацией о трех товарах и о двух промоакциях соответственно. Например, товар 9105 поставлялся компанией-поставщиком A в период с 1 января 2012 г. по 1 июня 2012 г., т.е. последним днем поставки было 31 мая 2012 г. (Обратите внимание, что периоды всегда определяются "исключительным" образом; т.е. указанные даты окончания не входят в срок действия). С 1 июня по 1 сентября товар 9015 поставлялся компанией B. На протяжении сентября и октября мы не получали товара 9015, а на протяжении ноября и декабря этот товар снова был доступен от поставщика A. В те периоды времени, когда у нас нет поставщика для какого-либо товара, этот товар рассматривается как отсутствующий на нашем складе.

Рисунок 1. Тестовые данные
Image shows two tables with sample data

Первичный ключ (prodID, BUSINESS_TIME WITHOUT OVERLAPS) гарантирует, что никакие два поставщика не поставляют один и тот же товар одновременно. Если мы хотим позволить нескольким поставщикам поставлять один и тот же товар одновременно, мы можем задать первичный ключ в следующем виде: (prodID, supplier, BUSINESS_TIME WITHOUT OVERLAPS).

Таблица promotion на рис.1 описывает две промоакции для товара 9105. Первая промоакция предлагает этот товар по цене 19,95 долл. в период с 15 января 2012 г. по 15 марта 2012 г. Таким образом, последний день сниженной цены – это 14 марта 2012 г. Вторая промоакция, действующая с 1 мая по 1 июля 2012 г., предлагает этот товар по цене 16,95 долл. В остальное время на товар 9105 действует обычная цена. Эта цена хранится в другом месте.

Темпоральная ссылочная целостность

Данные на рис.1 соблюдают традиционное отношение между двумя таблицами на основе внешнего ключа, поскольку каждая промоакция из таблицы promotion ссылается на идентификатор товара, который существует в таблице product_avail. Однако наша компания также должна гарантировать, что любая промоакция предлагается на протяжении только того промежутка времени, когда соответствующий товар реально доступен. Представьте себе замешательство и неудовлетворенность клиентов компании, которая объявляет скидку на товар, который недоступен ни в одном из ее магазинов.

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

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

На рис.2 показаны периоды бизнес-времени для учебных данных с рисунка 1, что позволяет нам исследовать некоторые упомянутые выше темпоральные отношения. Каждая строка таблицы представлена прямоугольником, длина которого соответствует продолжительность периода бизнес-времени этой строки. Строки из таблицы product_avail показаны синим цветом, а строки из таблицы promotion – зеленым цветом.

Анализ рисунка 2 позволяет сделать следующие выводы.

  • Для промоакций с идентификаторами promoID 16 и promoID 17 не соблюдается условие равенства периодов с их родительскими строками, поскольку периоды в таблице promotion короче периодов соответствующих родительских строк в таблице product_avail.
  • Промоакция 16 соблюдает условие включения периода (и даже условие включения периода с одиночной родительской строкой), поскольку период предложения этой акции содержится внутри одного периода, на протяжении которого товар 9105 поступает от компании-поставщика A.
  • Промоакция 17 удовлетворяет условию включения периода (с несколькими родительскими строками), поскольку ее период предложения состоит из месяцев май и июнь, на протяжении которых соответствующий товар предоставляется компаниями A и B без перерывов в поставках. Промоакция 17 не удовлетворяет условию включения периода с одиночной родительской строкой.
  • Промоакции 16 и 17 удовлетворяют условиям перекрытия и начала внутри периода для первой строки товара.
Рисунок 2. Временная шкала периодов из таблицы product_avail (верхняя часть, синий цвет) и периодов из таблицы promotion (нижняя часть, зеленый цвет)
Image shows timeline of two periods

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

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

Нарушения темпоральной ссылочной целостности

Наш маркетинговый отдел прогнозирует уменьшение объемов продаж на протяжении августа и сентября, поэтому решает на протяжении этого периода предлагать товар 9105 по цене всего лишь 15,95 долл. Для промоакции 18 выполним следующий оператор INSERT:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (18, 9105, '2012-08-01', '2012-10-01');

Эта новая промоакция не нарушает традиционной ссылочной целостности, поскольку внешний ключ 9105 существует в таблице product_avail. Тем не менее, как можно увидеть на рис.3, промоакция 18 нарушает выбранное нами понятие темпоральной ссылочной целостности, а именно, условие включения периода. Новая промоакция действует на всем протяжении сентября, однако у нас нет поставщика для товара 9105 на весь этот период (и мы не поддерживаем запасов).

Если бы для определения темпоральной ссылочной целостности мы выбрали бы условие "перекрытие" или "начало внутри", то промоакция 18 не нарушала бы этого условия.

Рисунок 3. Промоакция 18 нарушает условие темпоральной ссылочной целостности
Image shows a timeline of two periods

Мы могли бы выявить и устранить это нарушение темпоральной ссылочной целостности, обнаружив, что промоакция 18 заканчивается 01.10.2012, а эта дата находится за пределами любого существующего периода бизнес-времени для товара 9105 в таблице product_avail. Для исправления этой ситуации следует скорректировать промоакцию 18 или удалить ее из таблицы promotion.

Теперь рассмотрим другую промоакцию (под номером 19), которая вводится с помощью следующего оператора INSERT:

INSERT INTO promotion (promoID, prodID, price, promo_start, promo_end)
VALUES (19, 9105, '2012-08-01', '2012-12-01');

Это промоакция начинается 1 августа 2012 и заканчивается 1 декабря 2012. Хотя даты начала и окончания этой промоакции лежат в пределах существующих периодов доступности товара, условие темпоральной ссылочной целостности (включение периода) нарушено, поскольку в промежутке между этими двумя датами имеет место разрыв поставок. Как показано на рис.4, промоакция 19 действует на протяжении августа, сентября, октября и ноября, а на протяжении сентября и октября поставки товара отсутствуют.

Рисунок 4. Промоакция 19 нарушает условие темпоральной ссылочной целостности
Image shows a timeline of two periods

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

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


Варианты обеспечения темпоральной ссылочной целостности

Как правило, в области темпоральной ссылочной целостности имеются следующие четыре варианта.

  1. Отсутствие обеспечения. Можно не задавать никаких ограничений, если вы знаете, что темпоральная ссылочная целостность не важна для вашего приложения или ее условия никогда не нарушаются.
  2. Обеспечение на уровне приложения. Хороший выбор, если ваши приложения уже обеспечивают темпоральную ссылочную целостность. В противном случае данный вариант способен значительно усложнить приложения и увеличить нагрузку на их разработчиков.
  3. Обеспечение с помощью триггеров. Создайте для операций вставки, обновления и удаления триггеры, обеспечивающие соблюдение темпоральной ссылочной целостности.
  4. Обеспечение с помощью хранимых процедур. Создайте хранимые процедуры, обеспечивающие соблюдение темпоральной ссылочной целостности для множества строк в массовых операциях.

Ниже мы рассмотрим для вариантов 3 и 4 (триггеры и хранимые процедуры) несколько примеров, в которых реализуется условие "включение периода" между дочерней строкой и одной или несколькими родительскими строками. Примеры кода были протестированы с помощью продукта DB2 10 for Linux, UNIX, and Windows®, однако эти же общие концепции применимы и к продукту DB2 for z/OS®.


Обеспечение темпоральной ссылочной целостности с помощью триггеров

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

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

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

Простые триггеры темпоральной ссылочной целостности (без учета разрывов)

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

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

Описываемый триггер выполняет две проверки. Сначала он проверяет, содержится ли дата начала вставленной промоакции в бизнес-периоде строки product_avail с соответствующим значением идентификатора prodID. Затем он проверяет аналогичное условие для даты окончания вставленной промоакции. Если любая из этих проверок завершается неудачей (например, если количество соответствующих родительских строк равно нулю), то темпоральная ссылочная целостность считается нарушенной, в результате чего генерируется ошибка. Если даты начала и окончания содержатся внутри периодов соответствующих родительских строк, то темпоральная ссылочная целостность считается сохраненной, и вставка выполняется. В противном случае триггер отклоняет вставку, сигнализирует об особом SQL-состоянии и генерирует сообщение о нарушении темпоральной ссылочной целостности.

Листинг 3. Триггер для обеспечения темпоральной ссылочной целостности при вставке дочерней строки
CREATE TRIGGER promotion_insert_RI
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF (
        -- Is promotion.promo_start part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start <= new.promo_start 
        AND p.avail_end > new.promo_start)=0
    OR
        -- Is promotion.promo_end part of an existing product_avail period?
        (SELECT COUNT(*) count
        FROM product_avail p
        WHERE p.ProdID = new.ProdID
        AND p.avail_start < new.promo_end
        AND p.avail_end >= new.promo_end)=0
     ) -- promotion.promo_start AND promotion.promo_end must BOTH be 
    -- part of ANY period
   THEN SIGNAL SQLSTATE 'RI999' 
     SET MESSAGE_TEXT='PROMOTION PERIOD IS NOT FULLY CONTAINED IN EXISTING
                    PRODUCT PERIODS!';
    END IF;
END@

Аналогичный триггер необходим, если мы предполагаем возможность обновления таких столбцов таблицы promotion, как prodID, promo_start или promo_end. Например, обновление значений столбца promo_start или promo_end способно переместить или увеличить период промоакции и, соответственно, нарушить темпоральную ссылочную целостность. Логика данного триггера аналогична логике в листинге 4, за исключением того, что этот триггер срабатывает только в том случае, если на любой из перечисленных трех столбцов оказывает влияние оператор обновления.

Листинг 4. Триггер для обеспечения темпоральной ссылочной целостности при обновлении дочерней строки
CREATE TRIGGER promotion_update_RI
BEFORE UPDATE OF prodID, promo_start, promo_end ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
        -- same trigger body as in Listing 3
END IF;
END@

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


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

Если у родительских строк для определенного значения ключа могут быть разрывы между бизнес-периодами, как в нашем примере с доступностью товара, то проверка на темпоральную ссылочную целостность в триггере должна быть более изощренной. Необходимо усовершенствовать этот триггер, чтобы он также мог обнаруживать нарушения в виде разрывов (как у промоакции 19 на рис.4). В листинге 5 и в листинге 6 показаны два альтернативных способа написания триггера, который верифицирует условие "включение периода" с необходимой проверкой на наличие разрыва.

Триггер в листинге 5 выбирает из таблицы product_avail родительские строки, периоды которых перекрываются с периодом новой строки, вставляемой в таблицу promotion. Для каждой из этих родительских строк применение левого внешнего самосоединения к таблице product_avail извлекает "очередные" родительские строки, период которых начинается там, где заканчивается период предыдущей строки (current.avail_end = next.avail_start) (т.е. без разрыва). Строки результата выполнения левого внешнего соединения, имеющие значение NULL в столбце next.avail_start, отражают наличие разрыва в периодах родительских строк. Другие условия в триггере гарантируют, что даты начала и окончания новой промоакции содержатся внутри существующих родительских периодов.

Если в таблице product_avail нет родительской строки, у которой значение prodID соответствует значению prodID у недавно вставленной промоакции, то оператор outer join возвращает пустой набор результатов. В этом случае выражение SUM(start_check)+SUM(end_and_gap_check) ) в операторе SELECT верхнего уровня генерирует значение NULL. Это значение NULL заставляет функцию COALESCE возвратить значение –1, в результате чего тест целостности закономерно завершается неудачей.

Листинг 5. Обеспечение темпоральной ссылочной целостности при вставке дочерней строки с учетом разрывов в родительских периодах
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( SELECT COALESCE(SUM(start_check)+SUM(end_and_gap_check), -1) check 
        FROM (
        SELECT CASE WHEN new.promo_start < current.avail_start
                AND current.avail_start = 
                        (SELECT MIN(avail_start) 
                        FROM product_avail
                        WHERE prodID = new.prodID 
                        AND avail_start < new.promo_end
                        AND avail_end > new.promo_start)
                THEN -1-- PROMOTION.PROMO_START is out of product_avail range!
                ELSE  0-- PROMOTION.PROMO_START is in range of current or previous 
                        -- product_avail period
            END start_check,
            CASE WHEN current.avail_end < new.promo_end
                THEN CASE WHEN next.avail_start IS NULL
                          THEN -1-- PROMOTION is out of product_avail range, 
                                -- or product_avail contains gaps!
                          ELSE  0-- PROMOTION.PROMO_END is out of current 
                                -- product_avail period, but a connecting 
                                -- product_avail period exists
                        END ELSE 0 -- PROMOTION.PROMO_END is in a product_avail period
                END end_and_gap_check
            FROM product_avail AS current
            LEFT OUTER JOIN product_avail AS next
                        ON current.avail_end = next.avail_start
                        AND current.prodID = next.prodID
            WHERE current.prodID = new.prodID 
            AND current.avail_start < new.promo_end 
            AND current.avail_end > new.promo_start
        ) )<0 THEN SIGNAL SQLSTATE 'RI999' 
            SET MESSAGE_TEXT='PROMOTION PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

Чтобы понять логику этого триггера, лучше всего нарисовать диаграмму, как на рис.4, а затем просмотреть условия и CASE-выражения.

Код в листинге 5 – это всего лишь одно из возможных решений; вы сможете найти и другие способы для реализации аналогичных условий с помощью SQL. Одно из альтернативных решений демонстрируется в листинге 6. Тело триггера в листинге 6 проверяет три условия. Первый оператор SELECT подтверждает, что дата начала вставленной промоакции находится внутри бизнес-периода для строки товара с таким же значением ключа. Второй оператор SELECTподтверждает, что дата окончания новой промоакции находится внутри соответствующей строки товара. И, наконец, третий оператор SELECT выполняет проверку на наличие разрывов между периодами релевантных родительских строк в таблице product_avail.

Листинг 6. Обеспечение темпоральной ссылочной целостности при вставке дочерней строки с учетом разрывов в родительских периодах
CREATE OR REPLACE TRIGGER promotion_insert_RI_withGaps
BEFORE INSERT ON promotion
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
    IF ( -- verify that a product row exists with prodID = promtion.prodID
        -- and whose period contains promo_start
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start <= new.promo_start
        AND new.promo_start < prod.avail_end)   < 0 )
    OR ( -- verify that a product row exists with prodID = promotion.prodID 
        -- and whose period contains promo_end
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND prod.avail_start < new.promo_end
        AND new.promo_end <= prod.avail_end)   < 0 )
    OR ( -- check for any gaps between the relevant product rows that
        -- have prodID = promotion.prodID
        (SELECT COUNT(*)
        FROM product_avail prod
        WHERE new.prodID = prod.prodID
        AND new.promo_start < prod.avail_end
        AND prod.avail_end < new.promo_end
        AND NOT EXISTS (
                SELECT *
                FROM product_avail prod2
                WHERE prod2.prodID = prod.prodID
                    AND prod2.avail_start <= prod.avail_end
                    AND prod.avail_end < prod2.avail_end) )   > 0 )
    THEN SIGNAL SQLSTATE 'RI999' 
        SET MESSAGE_TEXT='PROMOTION PERIOD VIOLATES TEMPORAL RI!';
    END IF;
END @

Обеспечение темпоральной ссылочной целостности с помощью хранимых процедур

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

Например, процедура в проверяет темпоральную ссылочную целостность между таблицей product_avail и таблицей promotion. Предполагается, что периоды для выбранного товара в таблице product_avail не имеют разрывов. Базовая логика проверки ссылочной целостности в этой процедуре аналогична соответствующей логике триггера в листинге 3.

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

Листинг 7. Хранимая процедура для массовой проверки темпоральной ссылочной целостности
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    IF (SELECT COUNT(*) FROM (
        SELECT *  -- This SELECT obtains all promotion rows that violate temporal RI 
        FROM (
            SELECT prodID, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                   WHERE p.ProdID = promotion.ProdID
                      AND p.avail_start < promotion.promo_end
                      AND p.avail_end >= promotion.promo_end) c2
            FROM promotion) S
    -- count all promotion rows, for which either the first or the second check fails
        WHERE c1 = 0 OR c2 = 0
    ) T) > 0
    THEN SIGNAL SQLSTATE 'RI999' 
        SET MESSAGE_TEXT='AT LEAST ONE PROMOTION VIOLATES TEMPORAL RI!';
    ELSE SIGNAL SQLSTATE 'RI000' 
        SET MESSAGE_TEXT='TEMPORAL RI HAS BEEN SUCCESSFULLY VERIFIED!';
    END IF;
END@

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

Листинг 8. Хранимая процедура для копирования и удаления строк, нарушающих темпоральную ссылочную целостность
CREATE PROCEDURE check_temporal_integrity()
BEGIN
    INSERT INTO promo_exception
       SELECT promoID, prodID, price, promo_start, promo_end
       FROM (
            SELECT promoID, prodID, price, promo_start, promo_end,
                -- check whether a product row contains promotion.promo_start
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start <= promotion.promo_start
                    AND p.avail_end > promotion.promo_start) c1,
                -- check whether a product_avail row contains promotion.promo_end
                (SELECT COUNT(*) FROM product_avail p
                  WHERE p.ProdID = promotion.ProdID
                    AND p.avail_start < promotion.promo_end
                    AND p.avail_end >= promotion.promo_end) c2
                FROM promotion
            ) S
            -- select all promotion rows, for which either the first
            -- or the second check fails
            WHERE c1 = 0 OR c2 = 0;
                
    DELETE FROM promotion
    WHERE promoID IN (SELECT promoID FROM promo_exception);
END@

Заключение

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

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

Ресурсы

Научиться

Получить продукты и технологии

  • Теперь у вас есть возможность использовать DB2 бесплатно. Загрузите DB2 Express-C, бесплатную версию редакции DB2 Express Edition для сообщества, которая имеет такие же базовые функции по работе с данными, что и DB2 Express Edition, и способна служить прочным фундаментом для создания и развертывания приложений.

Обсудить

Комментарии

developerWorks: Войти

Обязательные поля отмечены звездочкой (*).


Нужен IBM ID?
Забыли Ваш IBM ID?


Забыли Ваш пароль?
Изменить пароль

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Профиль создается, когда вы первый раз заходите в developerWorks. Информация в вашем профиле (имя, страна / регион, название компании) отображается для всех пользователей и будет сопровождать любой опубликованный вами контент пока вы специально не укажите скрыть название вашей компании. Вы можете обновить ваш IBM аккаунт в любое время.

Вся введенная информация защищена.

Выберите имя, которое будет отображаться на экране



При первом входе в developerWorks для Вас будет создан профиль и Вам нужно будет выбрать Отображаемое имя. Оно будет выводиться рядом с контентом, опубликованным Вами в developerWorks.

Отображаемое имя должно иметь длину от 3 символов до 31 символа. Ваше Имя в системе должно быть уникальным. В качестве имени по соображениям приватности нельзя использовать контактный e-mail.

Обязательные поля отмечены звездочкой (*).

(Отображаемое имя должно иметь длину от 3 символов до 31 символа.)

Нажимая Отправить, Вы принимаете Условия использования developerWorks.

 


Вся введенная информация защищена.


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=40
Zone=Information Management
ArticleID=935625
ArticleTitle=Управление данными по времени в DB2 с обеспечением темпоральной согласованности
publish-date=06282013