5. Развитие стандарта SQL после SQL/92

В заключение курса приведем обзор развития стандарта языка SQL после принятия SQL/92. Как было отмечено в предыдущих разделах, в стандарте SQL/92 по сравнению со стандартом SQL/89 язык был расширен главным образом количественно, хотя даже этих количественных расширений оказалось достаточно для того, чтобы стандарт SQL/92 не удалось полностью реализовать в большинстве коммерческих СУБД. Поскольку SQL/92 не удовлетворял значительной части претензий, исторически предъявляемых к языку SQL, был сформирован новый комитет, который должен был выработать стандарт языка с качественными расширениями. Результатом этой работы стал стандарт SQL:1999 (ранее известный как SQL/3 или SQL3), за которым последовали стандарты SQL:2003, SQL:2006, SQL:2008, SQL:2011, SQL:2016 и SQL:2023. Начиная с версии SQL:1999, в наименовании стандарта вместо дефиса используется двоеточие, что приводит его в соответствие с правилами именования стандартов ISO.

Каждая последующая редакция стандарта формально замещает предыдущую, так что декларации о соответствии продукта более ранним версиям стандарта не имеют официальной силы. Вместе с тем, ни одна из существующих реализаций не поддерживает стандарт полностью — даже наиболее продвинутые в этом отношении СУБД, такие как PostgreSQL, реализуют лишь подавляющее большинство обязательных возможностей ядра (Core SQL), оставляя без поддержки ряд необязательных расширений.

Начиная с SQL:1999, стандарт был расщеплен на несколько независимо разрабатываемых частей, что отражало осознание практической невозможности разработки и согласования единого документа огромного объема. В последней на данный момент редакции SQL:2023 стандарт ISO/IEC 9075 состоит из одиннадцати частей:

• Часть 1: SQL/Framework — описание назначения и структуры стандарта;
• Часть 2: SQL/Foundation — основные свойства языка (типы данных, выражения, определение схемы, манипулирование данными, управление транзакциями);
• Часть 3: SQL/CLI (Call-Level Interface) — интерфейс уровня вызовов;
• Часть 4: SQL/PSM (Persistent Stored Modules) — хранимые процедуры и управляющие конструкции;
• Часть 9: SQL/MED (Management of External Data) — управление внешними данными;
• Часть 10: SQL/OLB (Object Language Bindings) — привязки к объектным языкам;
• Часть 11: SQL/Schemata — информационная схема и схемы определений;
• Часть 13: SQL/JRT — подпрограммы и типы с использованием языка Java;
• Часть 14: SQL/XML — спецификации, связанные с XML;
• Часть 15: SQL/MDA (Multidimensional Arrays) — многомерные массивы;
• Часть 16: SQL/PGQ (Property Graph Queries) — запросы над графами свойств.

Пропуски в нумерации (части 5–8, 12) объясняются тем, что соответствующие части либо были объединены с другими (так, часть 5, посвященная встроенному и динамическому SQL, была включена в часть 2 при переходе к SQL:2003), либо так и не были выпущены. Наибольшее практическое значение для разработчиков и пользователей баз данных имеет часть 2 (SQL/Foundation), которая определяет собственно язык SQL.

Принципиально изменился и подход к определению уровней соответствия стандарту. Вместо трех уровней SQL/92 (Entry, Intermediate и Full), начиная с SQL:1999 определяется набор индивидуальных возможностей (features), крупное подмножество которых составляет обязательное ядро (Core SQL). Все остальные возможности являются необязательными. Этот подход оказался значительно более практичным, поскольку позволяет точно описать, какие именно возможности стандарта поддерживаются данной реализацией.

Рассмотрим последовательно основные нововведения каждой редакции стандарта.

5.1. SQL:1999 — объектные расширения, рекурсия и процедурные возможности

Стандарт SQL:1999 (формально ISO/IEC 9075:1999) был опубликован несколькими выпусками в период с 1999 по 2002 г. и стал первой редакцией стандарта, включившей качественные расширения языка. Именно в SQL:1999 был закреплен ряд возможностей, отсутствие которых долгое время рассматривалось как существенное ограничение языка SQL.

Набор встроенных типов данных был расширен типом BOOLEAN. Хотя по причине поддержки неопределенных значений языку SQL свойственно применение трехзначной логики, тип BOOLEAN, в соответствии со стандартом, содержит только два значения — TRUE и FALSE (для представления значения UNKNOWN рекомендуется использовать NULL, что, конечно, не вполне естественно). Следует заметить, что реализации типа BOOLEAN в коммерческих СУБД появились с заметной задержкой: в ряде систем этот тип долгое время отсутствовал или поддерживался лишь частично.

Была включена в язык возможность определения пользовательских типов данных. Стандарт предусматривает два механизма: различимые типы (distinct types), позволяющие создать новый тип на основе существующего атомарного типа с собственной семантикой и контролем типов, и структурные типы (structured types), дающие возможность определения абстрактных типов данных с произвольно сложной внутренней структурой. Структурные типы поддерживают наследование в стиле объектно-ориентированного подхода, включая одиночное наследование и возможность определения методов. Появилась также возможность определения подтаблиц, наследующих свойства супертаблиц.

Среди новых типов коллекций был введен тип ARRAY, позволяющий хранить упорядоченные наборы элементов в одном столбце. Остальные типы коллекций (MULTISET) были добавлены позднее, в SQL:2003.

Одним из важнейших нововведений SQL:1999 стало появление обобщенных табличных выражений (Common Table Expressions, CTE) с поддержкой рекурсии. Конструкция WITH [RECURSIVE] позволяет определить именованное табличное выражение, которое может ссылаться само на себя, и тем самым обеспечивает выполнение рекурсивных запросов непосредственно на уровне языка SQL. Одним из существенных ограничений SQL вплоть до SQL/92 была невозможность стандартными средствами выразить рекурсивную обработку: для обхода иерархических и графовых структур данных приходилось прибегать к процедурным расширениям или нестандартным конструкциям наподобие оператора CONNECT BY, реализованного в Oracle. Рекурсивные CTE устраняют это ограничение в рамках стандартного декларативного языка. К настоящему времени рекурсивные CTE поддерживаются основными СУБД, включая PostgreSQL, IBM Db2, Microsoft SQL Server, Oracle, MariaDB, MySQL и SQLite.

Конструкция GROUP BY была расширена предложениями ROLLUP, CUBE и GROUPING SETS, что позволило формировать в одном запросе промежуточные итоги по различным комбинациям группирующих столбцов — возможность, важная при построении аналитических отчетов.

В стандарт были включены средства определения триггеров как комбинации спецификаций события и действия (оператор CREATE TRIGGER). Действие триггера определяется как SQL-процедура, в которой могут использоваться как операторы SQL, так и ряд управляющих конструкций. Следует заметить, что этот механизм к моменту стандартизации уже много лет был реализован в нескольких коммерческих СУБД, в частности в Oracle.

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

Что касается управления транзакциями, то произошел возврат к идее System R о возможности установки внутри транзакции точек сохранения (savepoints). В операторе ROLLBACK можно указать идентификатор ранее установленной точки сохранения, и тогда будет произведен откат транзакции не к ее началу, а к этой точке сохранения. Эта возможность впоследствии была широко реализована и стала повсеместно используемой на практике.

Подробное рассмотрение нововведений SQL:1999 с примерами их использования можно найти в курсе С.Д. Кузнецова «Базы данных. Вводный курс»: рекурсивные запросы и аналитические расширения GROUP BY — в лекции 20, триггеры — в лекции 21 (раздел 21.4), роли и управление привилегиями — в лекции 22, точки сохранения — в лекции 22 (раздел 22.3.6), определяемые пользователями типы данных — в лекции 23.

5.2. SQL:2003 — генераторы последовательностей, XML и расширения типов

Стандарт SQL:2003 (ISO/IEC 9075:2003) стал следующей крупной редакцией после SQL:1999. Именно при переходе к SQL:2003 часть 5 стандарта (встроенный и динамический SQL) была объединена с частью 2 (SQL/Foundation), и стандарт приобрел ту структуру из девяти частей, которая сохранялась до 2019 г.

Одним из наиболее практически значимых нововведений SQL:2003 стало введение генераторов последовательностей (sequence generators) посредством оператора CREATE SEQUENCE. Генераторы последовательностей обеспечивают получение уникальных числовых значений, что необходимо, в частности, для генерации суррогатных первичных ключей. До включения этой возможности в стандарт различные СУБД использовали для этих целей собственные несовместимые механизмы (SERIAL в PostgreSQL, AUTO_INCREMENT в MySQL, IDENTITY в SQL Server). В SQL:2003 был также стандартизирован столбец с автоматической генерацией значений (GENERATED ALWAYS AS IDENTITY).

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

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

Значительным расширением стало появление средств работы с данными в формате XML. Была введена новая часть стандарта — часть 14 (SQL/XML), определяющая тип данных XML, набор функций для преобразования между реляционными данными и XML (XMLELEMENT, XMLFOREST, XMLAGG, XMLQUERY, XMLTABLE и другие), а также механизм публикации реляционных данных в виде XML-документов. Появление SQL/XML отразило состояние технологий начала 2000-х годов, когда XML широко рассматривался как универсальный формат обмена данными.

Детальное обсуждение нововведений SQL:2003, включая конструктор типов мультимножеств, табличные функции, расширения оператора CREATE TABLE, генераторы последовательностей, идентифицирующие и генерируемые столбцы, а также оператор MERGE с подробными примерами, приведено в статье С.Д. Кузнецова «Наиболее интересные новшества в стандарте SQL:2003».

5.3. SQL:2006 и SQL:2008 — развитие XML и уточнение языка

Редакция SQL:2006 (ISO/IEC 9075-14:2006) представляла собой обновление только части 14 стандарта (SQL/XML) и не являлась полной ревизией всех частей. В ней были существенно расширены средства взаимодействия SQL с XML, в частности, добавлена поддержка языка XQuery для выполнения запросов к XML-данным, хранимым в базе данных.

Стандарт SQL:2008 (ISO/IEC 9075:2008) стал полной ревизией всех девяти частей стандарта. Хотя он не содержал столь масштабных нововведений, как SQL:1999 или SQL:2003, в нем был проведен ряд важных уточнений и дополнений.

Было легализовано использование предложения ORDER BY вне определений курсоров, что отражало давно сложившуюся практику всех реализаций. Появились триггеры INSTEAD OF, позволяющие определить действие, выполняемое вместо операции вставки, обновления или удаления (что особенно полезно для обновляемых представлений). Был добавлен оператор TRUNCATE для быстрого удаления всех строк из таблицы. Введено предложение FETCH FIRST ... ROWS ONLY для ограничения количества возвращаемых строк запроса, что стандартизировало функциональность, ранее доступную лишь через нестандартные расширения (LIMIT в PostgreSQL и MySQL, TOP в SQL Server, ROWNUM в Oracle).

5.4. SQL:2011 — темпоральные расширения

Стандарт SQL:2011 (ISO/IEC 9075:2011), принятый в декабре 2011 г., привнес в язык SQL одно из наиболее значительных концептуальных расширений — поддержку темпоральных (временных) данных. Проблема управления данными, изменяющимися во времени, обсуждалась в сообществе баз данных с начала 1990-х годов. Попытка включить темпоральные расширения, основанные на языке TSQL2, в SQL:1999 не увенчалась успехом — соответствующий проект (часть 7 — SQL/Temporal) был отменен в конце 2001 г. В SQL:2011 темпоральные возможности были включены непосредственно в часть 2 (SQL/Foundation) на основе более простого и практичного подхода.

Стандарт определяет два вида темпоральных таблиц. Таблицы с периодом действия приложения (application-time period tables, также называемые таблицами действительного времени) позволяют пользователю явно указывать временной интервал, в течение которого данная строка является действительной. Для этого определяются два обычных столбца (начало и конец периода) и специальное объявление PERIOD FOR. Стандарт обеспечивает автоматическое расщепление временных интервалов при операциях UPDATE и DELETE, а также поддержку темпоральных первичных ключей с предложением WITHOUT OVERLAPS, гарантирующим отсутствие пересечений периодов действия для одного и того же ключа.

Системно-версионные таблицы (system-versioned tables, также называемые таблицами транзакционного времени) автоматически сохраняют историю изменений данных. При определении такой таблицы используется объявление PERIOD FOR SYSTEM_TIME и предложение WITH SYSTEM VERSIONING. Каждое изменение строки (операция UPDATE или DELETE) автоматически сохраняет предыдущую версию строки в таблице истории, фиксируя время начала и окончания действия каждой версии. Для запросов к историческим данным введены специальные конструкции: FOR SYSTEM_TIME AS OF для получения состояния данных на указанный момент времени, а также FOR SYSTEM_TIME BETWEEN ... AND ... и FOR SYSTEM_TIME FROM ... TO ... для запросов по временным диапазонам.

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

Для работы с периодами введены новые темпоральные предикаты: CONTAINS, OVERLAPS, EQUALS, PRECEDES, SUCCEEDS, IMMEDIATELY PRECEDES и IMMEDIATELY SUCCEEDS, представляющие собой модифицированные варианты отношений Аллена для временных интервалов.

К настоящему времени поддержка системно-версионных таблиц реализована в IBM Db2, Microsoft SQL Server, MariaDB, Oracle и ряде других СУБД. В PostgreSQL работа над отдельными темпоральными расширениями стандарта также ведется.

Помимо темпоральных расширений, SQL:2011 включал ряд усовершенствований оконных функций и предложения FETCH.

5.5. SQL:2016 — поддержка JSON и сопоставление с образцом

Стандарт SQL:2016 (ISO/IEC 9075:2016) стал редакцией, в которой язык SQL получил стандартные средства работы с данными в формате JSON. К середине 2010-х годов JSON во многих областях стал основным форматом обмена данными в веб-приложениях и сервисах, и отсутствие стандартных средств работы с JSON в SQL становилось все более ощутимым.

В SQL:2016 был введен набор функций SQL/JSON: JSON_VALUE для извлечения скалярного значения из JSON-документа по указанному пути, JSON_QUERY для извлечения JSON-фрагмента, JSON_TABLE для преобразования JSON-данных в реляционную форму (аналогично XMLTABLE для XML), JSON_EXISTS для проверки наличия элемента и JSON_OBJECT, JSON_ARRAY, JSON_OBJECTAGG, JSON_ARRAYAGG для конструирования JSON-документов из реляционных данных. Для адресации элементов JSON-документов используется язык SQL/JSON path, основанный на синтаксе JavaScript.

Следует подчеркнуть, что в SQL:2016 был стандартизирован набор функций для работы с JSON, но не был введен собственный тип данных JSON — JSON-данные представлялись в виде символьных строк. Отдельный тип данных JSON появился лишь в SQL:2023.

Другим значительным нововведением SQL:2016 стало сопоставление строк с образцом (row pattern recognition) посредством конструкции MATCH_RECOGNIZE. Эта конструкция позволяет описывать шаблоны последовательностей строк с помощью регулярных выражений и находить соответствия этим шаблонам в упорядоченных наборах данных. Типичные области применения — обнаружение тенденций в финансовых данных, анализ поведения пользователей, выявление аномалий в потоках событий.

Были также введены полиморфные табличные функции (polymorphic table functions, PTF) — табличные функции, возвращаемый тип которых не фиксирован заранее, а определяется на основании фактических аргументов вызова. Эта возможность значительно расширяет выразительность языка в тех случаях, когда структура результата зависит от входных данных.

Промежуток между SQL:2016 и следующей редакцией стандарта (SQL:2023) составил семь лет — второй по продолжительности в истории стандартизации SQL после промежутка между SQL/92 и SQL:1999. Задержка была отчасти вызвана пандемией COVID-19, нарушившей привычный график заседаний комитетов, отчасти — масштабностью проекта SQL/PGQ.

5.6. SQL:2023 — графовые запросы, тип JSON и новые функции

Стандарт SQL:2023 (ISO/IEC 9075:2023), принятый в июне 2023 г., является девятой редакцией международного стандарта языка SQL. Два наиболее заметных нововведения этой редакции отражают стремление интегрировать в реляционную парадигму средства работы с нереляционными моделями данных.

Одним из наиболее заметных расширений стало появление новой части стандарта — части 16: SQL/PGQ (Property Graph Queries), определяющей средства описания и запросов графов свойств (property graphs). Граф свойств представляет собой граф, вершины и ребра которого обладают метками и свойствами (парами ключ–значение). SQL/PGQ позволяет определить граф свойств поверх существующих реляционных таблиц (посредством оператора CREATE PROPERTY GRAPH с указанием таблиц вершин и ребер) и выполнять запросы к нему с помощью нового оператора GRAPH_TABLE в предложении FROM. Для описания образцов графа используется нотация, основанная на представлении вершин в круглых скобках и ребер в квадратных скобках со стрелками, указывающими направление.

Введение SQL/PGQ сокращает функциональный разрыв между реляционными СУБД и специализированными графовыми базами данных, позволяя выполнять графовые запросы над данными, хранящимися в обычных таблицах, без необходимости перемещения данных в отдельное хранилище. При этом графовое представление работает подобно представлению (view) — оно не создает копии данных, и изменения в исходных таблицах отражаются в графе. Промышленная поддержка SQL/PGQ только начинает появляться; одним из первых примеров является Oracle Database 23ai. Работа над реализацией SQL/PGQ в PostgreSQL ведется на уровне прототипа.

Вторым крупным нововведением SQL:2023 стало введение собственного типа данных JSON, дополняющего функции SQL/JSON, стандартизированные в SQL:2016. Наличие отдельного типа позволяет определить для JSON-значений операции сравнения и упорядочения, что делает возможным использование JSON в выражениях GROUP BY, ORDER BY и в операциях с множествами. Был введен упрощенный синтаксис доступа к элементам JSON-документа (simplified accessor) через точечную нотацию и индексацию массивов, что существенно уменьшает многословность запросов по сравнению с использованием функций JSON_QUERY и JSON_VALUE.

В SQL:2023 также появился ряд новых скалярных и агрегатных функций, многие из которых давно присутствовали в реализациях, но до этого момента не были стандартизированы: GREATEST, LEAST, LPAD, RPAD, LTRIM, RTRIM, а также агрегатная функция ANY_VALUE. Были добавлены шестнадцатеричные целочисленные литералы и ряд других уточнений синтаксиса.

С учетом новой части 16, стандарт ISO/IEC 9075:2023 включает одиннадцать действующих частей. Часть 2 (SQL/Foundation), определяющая ядро языка, составляет более 1700 страниц — для сравнения, полный текст стандарта SQL/92 занимал около 600 страниц.