Темпоральні розширення в стандарті SQL — це бази даних, які нерозривно пов'язані з часом. Темпоральні розширення актуальні для транзакційних баз даних (дата і час авіарейсів, надходження запасів на склади, початок обробки замовлень в Інтернет-магазинах тощо), і тим більше для аналітичних баз даних (зокрема, час є найважливішим виміром OLAP - кубів).

Введення До початку 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

Див. також

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