Темпоральні розширення в стандарті SQL

До початку 1990-х рр. внесення даних до баз даних: дати-часу й обробки відповідної інформації покладалися на додатки, що було погано з усякого погляду і призвело до виникнення сумнозвісної проблеми 2000 року, яка викликала у всьому світі чимало клопоту.

Серйозний крок вперед був зроблений в 1992 р. в стандарті SQL / 92 (SQL: 1992), в якому були визначені спеціальні «темпоральні» типи даних (DATE, TIME, TIMESTAMP і INTERVAL). Ці типи даних не тільки забезпечують стандартне уявлення дат, значень часу і тимчасових інтервалів, але і набір корисних операцій над темпоральними значеннями, в тому числі, логічну операцію OVERLAP, що виробляє справжнє значення, якщо два заданих тимчасових діапазони мають непорожній перетин.

Однак, незважаючи на введення в SQL темпоральних типів даних, SQL - орієнтовані бази даних, які дозволяють зберігати тільки статичні миттєві зміни даних предметних областей. Наприклад, якщо в базі даних службовцю Іванову підвищують зарплату, то без хитрощів на рівні додатку в базі даних неможливо зберегти дані про його попередню зарплату і, тим більше, про розмір його зарплати рік тому.

В істинно темпоральних базах даних для кожного об'єкта бази даних автоматично підтримуються всі його стани від моменту створення до моменту знищення. Системи керування базами даних (СКБД) підтримують темпоральні бази даних, і самі називаються темпоральними СКБД. Починаючи з 1980-х років навколо темпоральних баз даних велася активна дослідницька робота, пов'язана як з теоретичними аспектами підтримки часу в базах даних, так і з проблемами реалізації темпоральних СКБД.

У 1995 р. була завершена робота спеціально створеного комітету під керівництвом Річарда Снодграсса з вироблення специфікацій мови SQL, що підтримує темпоральні бази даних (TSQL2). У тому ж році в комітеті по стандартизації SQL Міжнародної організації зі стандартизації (ISO) було розпочато проект з розробки розширень стандарту SQL (SQL: 1992) на основі ідей TSQL2. Однак аналіз експертів показав наявність в TSQL2 непереборних суперечностей. Крім того, для реалізації TSQL2 була потрібна дуже значна переробка існуючих SQL-орієнтованих СКБД, а основні виробники комерційних СКБД відповідного бажання не відчували. У результаті у 2001 р. проект був закритий.

Кількома роками пізніше в комітеті по стандартизації SQL надійшли нові пропозиції розширень SQL, які були схвалені комітетом. Це призвело до прийняття в кінці 2011 р. нового стандарту SQL: 2011 року, у другій частині якого (Foundation) містяться специфікації темпоральних розширень. Темпоральні засоби SQL детально обговорюються в книзі, але, як властиво Дейту, ці обговорення ведуться у вигляді критичного порівняння з власною темпоральною моделлю авторів, якій присвячена основна і досить об'ємна частина книги. Це дещо ускладнює розуміння суті темпоральних розширень в SQL: 2011.

У стандарті SQL: 2011 використовується так звана бітемпоральная модель. Це означає, що в SQL-орієнтованих базах даних можуть підтримуватися два види часу: транзакційні (transactional time, автоматично забезпечується системою) і істинні (valid time, що задаються додатками бази даних). У стандарті SQL транзакційний час називається системним (system time), а справжній час - прикладним (application time). Детальніше про це йдеться в основній частині статті.

До середини 2017 р. найповніше темпоральна частина SQL: 2011 підтримується в IBM DB2. Обидва види часу підтримуються і в Oracle, але реалізацію темпоральних засобів поки не можна вважати повною (крім того, схоже, що синтаксис темпоральних конструкцій в Oracle SQL відмінний від стандартного). Повна підтримка часу ще з 2010 р. знаходиться на Teradata, але реалізація спирається на TSQL2, а не на стандарт SQL. У ряді СКБД (в тому числі, в Microsoft SQL Server і PostgreSQL) є часткова підтримка часу з деякою опорою на SQL: 2011.

В основі бітемпоральної моделі SQL лежить поняття періоду часу (time period) — напівінтервалу осі часу, що починається в точці початкового часу (start time) і закінчується в точці кінцевого часу (end time). Майже у всіх випадках застосовується замкнуто-відкрита (closed-open) семантика періодів часу: період включає точку початкового часу і не включає точку кінцевого часу. Періоди часу можна визначати і пов'язувати з рядками таблиць.

На відміну від інших підходів (зокрема, TSQL2), в моделі SQL для періодів часу не вводиться спеціальний тип даних. Відмова від введення нового типу даних обґрунтовується наступними міркуваннями. По-перше, додавання нового типу даних в стандарт SQL зажадало б не тільки серйозних переробок в SQL-орієнтованих СКБД, але і оснащення відповідними засобами всіх мов серверного програмування, розширення всіх API для клієнтського програмування (ODBC, JDBC, .NET і т.д .), відповідних переробок засобів ETL і т.д. Все це (в кращому випадку) значно затримало б реалізацію темпоральних розширень.

По-друге, оскільки темпоральні розширення реально потрібні в багатьох додатках, при відсутності відповідної підтримки з боку СКБД багато розробників додатків реалізовували потрібні їм темпоральні можливості всередині додатків. Для цього зазвичай в таблиці баз даних додавалися два стовпці - проміжок часу і кінцевий час періоду. Подібні додатки простіше адаптувати до системної підтримки часу, якщо періоду часу як і раніше будуть відповідати два стовпці кожної темпоральної таблиці.

У стандарті SQL визначення періоду є частиною визначення таблиці, розширюючи набір метаданих, пов'язаних з цією таблицею. Визначення періоду - це іменований компонент визначення таблиці, що задає пару стовпців, в яких зберігаються точки початкового і кінцевого часу періоду. Ці стовпці є звичайними стовпчиками таблиці зі своїми власними іменами. Ім'я періоду часу знаходиться в тому ж просторі імен, що і імена стовпців, тому воно повинно відрізнятися від імені будь-якого стовпця тієї ж таблиці. В операції ALTER TABLE є можливість скасування ухвали періодів.

У бітемпоральній моделі SQL підтримуються два різновиди часу, традиційно звані істинним і транзакційним часом. Період істинного часу — це період, протягом якого рядок правильно відображає реальність користувачів бази даних. Період транзакційного часу — це період між двома послідовними змінами цього рядка (час життя рядка таблиці починається з її вставки в таблицю, включає нуль або більше число змін і завершується при видаленні рядка).

Для будь-якого рядка період транзакційного часу може довільним чином відрізнятися від періоду істинного часу. Наприклад, якщо 26 травня 2017 року в таблицю USA_Presidents вставляється рядок з даними Барака Обами, то відповідно в реальності вона коректна в періоді істинного часу з 20 січня 2009 року по 20 січня 2017 року. В періоді транзакційного часу даний рядок дійсний з 26 травня 2017 р. до найближчої операції зміни.

У SQL: з 2011 транзакційний час підтримується на основі механізму системно-версійності таблиць (system-versioned table), в визначення яких входить визначення періоду системного часу (system-time period), а справжній час — на основі таблиць, у визначення яких входить визначення періоду прикладного часу (application-time period). Ім'я періоду системного часу в стандарті визначено (SYSTEM_TIME), а ім'я періоду прикладного часу задається користувачами. Для кожної таблиці можна визначити не більше одного періоду системного часу і не більше одного періоду прикладного часу.

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

Для таких додатків потрібно, щоб їх користувачі відповідали за установку початкових та кінцевих точок періодів істинності рядків, і щоб користувачі могли вибирати будь-які значення часу (в минулому, сьогоденні або майбутньому) в якості початкових і кінцевих точок періодів. Крім того, користувачам повинна бути надана можливість змінювати періоди істинності існуючих рядків при виявленні помилок або надходження нової інформації.

CREATE TABLE EMP (
EMP_NO EMP_NO,
EMP_DEPT_NO DEPT_NO,
EMPStart DATE NOT NULL,
EMPEnd DATE NOT NULL,
PERIOD FOR EMPPeriod (EMPStart, EMPEnd));
У фрагменті коду наведено визначення таблиці службовців з періодом прикладного часу, де типом даних стовпців початку і кінця періоду є DATE, але можна використовувати тип TIMESTAMP, аби в обох стовпців типи даних були однаковими. В обох стовпчиках забороняються невизначені значення. Значення початкових і кінцевих точок періоду прикладного часу для цього рядка встановлюються при вставці цього рядка в таблицю за допомогою звичайного оператора INSERT.

• Кузнецов С.Д.
  
• Michael H. Böhlen. Temporal Database System Implementations. ACM SIGMOD Record 24(4), December 1995
• SQL System Properties Comparison

• Реляційна алгебра
• База даних
• Семантична оптимізація запитів СУБД