Язык Celesta-SQL

1. Язык CelestaSQL определения объектов базы данных

Скрипты на языке CelestaSQL должны иметь кодировку UTF-8.

На языке Celesta SQL пишутся скрипты определения гранул. Скрипт на языке CelestaSQL состоит из конструкций

в обязательном порядке разделённых точкой с запятой, начиная с CREATE GRAIN:

create_grain ; create_sequence create_table add_foreign_key create_index create_view create_materialized_view create_function

2. Комментарии в языке CelestaSQL

CelestaSQL поддерживает стандартные однострочные и многострочные комментарии, а также комментарии CelestaDoc:

-- однострочный комментарий
/*многострочный
    комментарий*/
 /**комментарий CelestaDoc*/

Обычные комментарии могут использоваться в любом месте текста, комментарии CelestaDoc — только непосредственно перед определением гранулы, таблицы, поля или индекса.

3. Идентификаторы объектов в языке CelestaSQL

Идентификаторы объектов — это, иначе говоря, имена гранул, таблиц, полей, индексов, ограничений и представлений, их синтаксис в Celesta имеет ряд строгих ограничений.

  1. В обычных СУБД дозволяется, чтобы идентификаторы объектов содержали знаки пробела и неалфавитные знаки, если эти имена заключены в обрамляющие символы, например «[]» для MS SQL Server. Т. е. в MS SQLServer допустимо, например, такое имя таблицы: [Long Table$Name]. Celesta, однако, не может поддерживать пробелы и неалфавитные знаки в идентификаторах потому, что имя каждой Celesta-таблицы должно являться именем Java-класса, а имя каждого Celesta-поля — именем Java-переменной. Поэтому идентификатор любого именованного объекта CelestaSQL должен как минимум удовлетворять правилам наименования переменной в Java, т. е. может состоять только из больших и малых букв латинского алфавита, цифр и знаков подчёркивания, при этом не может начинаться с цифры.

  2. Заключение идентификаторов Celesta в кавычки в скриптах CelestaSQL не допускается на уровне синтаксиса, т. к. практической надобности в этом нет (имена никогда не содержат пробелов). На системном уровне при формировании запросов к СУБД Celesta, однако, всегда заключает имена своих объектов в прямые кавычки ("ANSI quotes"), чтобы гарантировать, что базы данных Oracle, PostgreSQL и H2 не будут нарушать регистр букв, составляющих идентификатор.

  3. Идентификаторы в Celesta являются чувствительными к регистру, однако нельзя создавать две таблицы, имена которых отличаются только регистром.

  4. Длина любого идентификатора в Celesta не может быть больше 30 символов.

  5. Идентификаторы (префиксы) гранул, кроме того, не могут содержать в себе знаков подчёркивания. Это связано с тем, что комбинация имени гранулы со знаком подчёркивания и другим идентификатором зачастую используется во внутрисистемных целях Celesta, и запрет на использование знака подчёркивания в именах гранул необходим для исключения возможных неоднозначностей.

Обычной практикой является написание идентификаторов в CelestaSQL в "snake_case" — в процессе трансформации в Java-классы они конвертируются в 'CamelCase'.

4. Конструкция CREATE SCHEMA (GRAIN)

С выражения CREATE SCHEMA должен начинаться любой скрипт определения гранулы. Используется следующий синтаксис (слова GRAIN и SCHEMA являются синонимами):

CREATE GRAIN SCHEMA grain_name VERSION ' grain_version_tag ' WITH NO AUTOUPDATE create_grain

Опция WITH NO AUTOUPDATE исключает схему целиком из процесса автообновления базы данных. Как и соответствующая опция таблицы, может быть использована в ситуации, когда структура схемы находится под внешним управлением.

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

Версия состоит из перечисленных через запятую компонент и может выглядеть таким образом: 1.23,TITAN3.34. Читать это нужно следующим образом: базовая версия 1.23, доработка для проекта TITAN – 3.34. Регулярное выражение для проверки формата компонента: ([A-Z_]*)([0-9]+\\.[0-9]+). Требуется, чтобы каждая из составляющих версии имела двухкомпонентный (и только двухкомпонентный!) формат, а префикс либо отсутствовал, либо состоял из заглавных латинских букв и знака подчёркивания. Когда система определяет возможность автоапгрейда, сравниваются все тэги версии последовательно.

В этом смысле, по отношению к тэгу «1.23,TITAN3.34»:

  • «1.23,TITAN3.35» – более свежая версия (обновилась модификация), можно выполнять автоапгрейд

  • «1.24,TITAN3.34» – более свежая версия (обновилась базовая версия), можно выполнять автоапгрейд

  • «1.23,TITAN3.34,PLUTO1.00» – более свежая версия (добавилась ещё одна модификация), можно выполнять автоапгрейд

  • «TITAN3.34,1.23» – та же самая версия (порядок следования тэгов не играет роли), автоапгрейд выполняться будет лишь при несовпадении контрольных сумм, ошибки не произойдёт

  • «1.22,TITAN3.34» – более старая базовая версия, автоапгрейд выполняться не будет, произойдёт ошибка и Celesta остановится.

  • «1.22,TITAN3.36» – несогласующаяся версия, апдейт выполняться не будет, ошибка. Версии «1.23,PLUTO1.00», «1.25» также будут несогласующимся с версией «1.23,TITAN3.34» и не станут накатываться автоматически.

Каждая из версий сравнивается, как число с плавающей запятой.

5. Конструкция CREATE SEQUENCE

Используется следующий синтаксис:

CREATE SEQUENCE sequence_name START WITH integer_literal INCREMENT BY integer_literal MINVALUE integer_literal MAXVALUE integer_literal CYCLE create_sequence

Основными ограничениями и отличиями этой конструкции от аналогичной конструкции в различных СУБД являются:

  1. Если не указано значение MINVALUE, то оно автоматически выставляется равным значению START WITH (по умолчанию 1).

  2. Если не указано значение MAXVALUE, то оно автоматически выставляется равным значению Long.MAX_VALUE.

  3. Значение START WITH не обновляется для созданных ранее последовательностей, даже если было изменено в файле гранулы (связано с тем, что Oracle разрешает данную операцию только через удаление и пересоздание последовательности, а идеология Celesta не предусматривает удаление stateful объектов из БД).

  4. Если в грануле присутствует таблица с именем, например, A, то является недопустимым создание последовательности с именем A_seq, так как это имя зарезервировано системой.

6. Конструкция CREATE TABLE

Используется следующий синтаксис:

CREATE TABLE table_name ( table_constituent , ) table_options create_table
field_definition CONSTRAINT pk name primary_key_definition CONSTRAINT fk name foreign_key_definition table_constituent

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

6.1. Определения полей

field name int_field floating_field decimal_field text_field blob_field datetime_field datetime_with_time_zone_field bit_field PRIMARY KEY inline_fk_definition field_definition

field_definition (Определение поля) — конструкция, задающая тип поля, название поля, его свойства NULL/NOT NULL и DEFAULT, опционально может заканчиваться конструкцией PRIMARY KEY и/или FOREIGN KEY.

Сокращенное определение внешнего ключа (inline_fk_definition) имеет следующий синтаксис:

FOREIGN KEY REFERENCES table_ref ( field name ) fk_rules inline_fk_definition

Здесь table_ref — ссылка на таблицу, которая может быть однокомпонентной (если таблица, на которую указывает внешний ключ, находится в текущей грануле) или двухкомпонентной, с явным указанием имени гранулы:

grain name . table name table_ref

Определение синтаксиса правил внешних ключей (fk_rules) см. в разделе «Внешние ключи».

6.2. Типы данных

Используется следующая система типов данных.

Celesta Microsoft SQL Server Oracle PostgreSQL Firebird H2 Java type for cursor

Integer (32-bit)

INT

INT

NUMBER

INT4

INTEGER

INTEGER

java.lang.Integer

Floating point (64-bit)

REAL

FLOAT(53)

REAL

FLOAT8 [= DOUBLE PRECISION]

DOUBLE PRECISION

DOUBLE

ava.lang.Double

Fixed point (decimal)

DECIMAL(p,s)

DECIMAL(p,s)

NUMBER(p,s)

NUMERIC(p,s)

DECIMAL(p,s)

DECIMAL(p,s)

java.math.BigDecimal

String (Unicode)

VARCHAR(n)

NVARCHAR(n)

NVARCHAR(n)

VARCHAR(n)

VARCHAR(n)

VARCHAR(n)

java.lang.String

Long string (Unicode)

TEXT

NVARCHAR(MAX)

NCLOB

TEXT

BLOB SUB_TYPE TEXT

CLOB

java.lang.String

Binary

BLOB

VARBINARY(MAX)

BLOB

BYTEA

BLOB

VARBINARY(MAX)

java.lang.String

Date/time

DATETIME

DATETIME

TIMESTAMP

TIMESTAMP

TIMESTAMP

TIMESTAMP

java.util.Date

Date/time with time zone

DATETIME WITH TIME ZONE

DATETIMEOFFSET

TIMESTAMP WITH TIME ZONE

TIMESTAMPZ

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH TIME ZONE

java.time.ZonedDateTime

Boolean

BIT

BIT

NUMBER [check in (0, 1)]

BOOL

SMALLINT [check in (0, 1)]

BOOLEAN

java.lang.Boolean

См. также раздел «Особенности работы Celesta с поддерживаемыми типами СУБД».

Для каждого типа поля имеется свой вариант определения:

NOT NULL nullability
INT nullability DEFAULT integer literal NEXTVAL ( sequence name ) int_field

Вместо конструкции DEFAULT <целое число> для поля с типом INT может использоваться конструкция NEXTVAL(<sequence name>). Таким образом значение колонки будет инкрементироваться при вставке в зависимости от указанной последовательности. Стоит отметить, что можно использовать только последовательности, объявленные в той же грануле, что и таблица, к которой принадлежит колонка.

REAL nullability DEFAULT float.-point literal floating_field
DECIMAL ( integer literal , integer literal ) nullability DEFAULT float.-point literal decimal_field
VARCHAR ( integer literal ) TEXT nullability DEFAULT text literal text_field
BLOB nullability DEFAULT binary literal blob_field

Здесь <binary literal> — шестнадцатеричное представление последовательности байтов, начинающееся с 0x и не заключённые в кавычки, например: 0xFFAAFFAAFF.

DATETIME nullability DEFAULT ' YYYYMMDD ' GETDATE() datetime_field

В качестве значения DEFAULT для поля с типом DATETIME может использоваться функция GETDATE() (текущий момент времени).

DATETIME WITH TIME ZONE nullability datetime_with_time_zone_field
BIT nullability DEFAULT TRUE FALSE bit_field

6.3. Первичные ключи

PRIMARY KEY ( field name , ) primary_key_definition

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

  • сокращённый вариант — когда ключевые слова “PRIMARY KEY” идут сразу после определения поля, это даёт возможность более короткой и наглядной записи в случае, когда первичный ключ состоит всего из одного поля,

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

Ограничения:

  • Не допускается создание таблиц без PRIMARY KEY (за исключением READ ONLY-таблиц). Это необходимо для работоспособности классов доступа к данным.

  • В таблице может быть не более одного упоминания PRIMARY KEY, будь то сокращённое выражение в конце определения поля или составное выражение в определении таблицы.

  • Не допускается создание PK по полям с типами BLOB и TEXT.

  • Не допускается создание PK по nullable-полям.

  • Не допускается более одного вхождения одного и того же поля в определение PRIMARY KEY.

6.4. Внешние ключи

FOREIGN KEY ( field name , ) REFERENCES table_ref ( field name , ) fk_rules foreign_key_definition

foreign_key_definition (Определение внешнего ключа) — конструкция, задающая связь между таблицами по одному или нескольким полям. Как и в случае с конструкцией PRIMARY KEY, возможно использование в двух вариантах — сокращённом (встроенном в определение поля, связь по одному полю) и полном (перечисляется среди определений полей). Кроме того, внешний ключ можно создать вне определения таблицы при помощи конструкции "alter table add constraint":

ALTER TABLE table name ADD CONSTRAINT fk name foreign_key_definition add_foreign_key

Ограничения:

  • Foreign key, простой или составной, может ссылаться только на Primary Key, причём полностью. (В Celesta SQL не допускаются ограничения UNIQUE, поэтому из двух возможностей, стандартно предлагаемых в СУБД для Foreign Keys, остаётся только возможность ссылки на Primary Key).

  • Типы полей должны в точности совпадать (если поле – строковое, длина ссылающегося поля должна быть точно равна длине поля, на которое ссылаются).

  • Нельзя создать более одного определения Foreign Key на одном и том же наборе столбцов (частный случай – не может быть двух FK, определённых для одного и того же столбца).

fk_rules — ссылочные действия:

ON UPDATE NO ACTION CASCADE SET NULL ON DELETE NO ACTION CASCADE SET NULL fk_rules

Поддерживаемые ссылочные действия:

  • NO ACTION – запрет удаления/модификации родительской записи, если на неё есть ссылки.

  • SET NULL – выставление NULL в ссылках. Естественно, использовать это действие запрещено для NOT NULL-able полей.

  • CASCADE – каскадное удаление/обновление полей.

В момент создания foreign key сослаться можно только

  • на таблицу, определённую в текущей грануле выше по тексту,

  • на таблицу, определённую с другой грануле.

«Зацикленные» ссылки по foreign key (например, вида A→B→C→A) при разработке структуры базы данных в действительности нужны крайне редко и обычно говорят об ошибке проектировщика. Единственный широко применимый практически значимый пример «зацикливания» — ссылка таблицы на саму себя при организации иерархического перечня по принципу parent-child.

В Celesta не допускается создание «зацикленных» ссылок между таблицами, принадлежащими разным гранулам. Если надо организовать ссылки по кругу между несколькими таблицами внутри гранулы, то это можно сделать, воспользовавшись конструкцией "alter table add constraint foreign key".

В частности, работает следующий пример:

CREATE TABLE aa(idaa INT NOT NULL PRIMARY KEY, idc INT , textvalue nvarchar(10));
CREATE TABLE a (ida INT NOT NULL PRIMARY KEY, descr nvarchar(2), parent INT
                FOREIGN KEY REFERENCES a(ida), --ссылка таблицы на саму себя
                fff INT FOREIGN KEY REFERENCES aa(idaa) --первая часть круговой ссылки

внешний ключ, создаваемый вне таблицы:

ALTER TABLE aa ADD CONSTRAINT fk1
  FOREIGN KEY (idc) REFERENCES a(ida); --вторая часть круговой ссылки

Пример создания составного ключа, состоящего из двух полей:

CREATE TABLE empfiles(
  id INT NOT NULL PRIMARY KEY,
  user_id varchar(200) NOT NULL,
  orgtype_id varchar(255) NOT NULL,
  question_id varchar(30) NOT NULL,
  FOREIGN KEY (orgtype_id, question_id) REFERENCES schema.table(field, field)

6.5. Опции

Celesta позволяет указывать следующие опции после определения таблицы:

WITH READ ONLY NO VERSION CHECK NO AUTOUPDATE table_options

Таким образом, поддерживаются следующие возможности:

  • WITH VERSION CHECK — режим по умолчанию: включение механизма отслеживания версий записей для исключения потерянных обновлений (см. раздел «Защита от потерянных обновлений»). Указывать данную опцию явно не требуется.

  • WITH NO VERSION CHECK — отключение механизма отслеживания версий записей. Требуется в случаях, когда нет необходимости в защите от потерянных обновлений — например, в таблицах, используемых только для добавления записей. Возможность модифицировать данные в таблице при этой опции сохраняется, но возможны «потерянные обновления».

  • WITH READ ONLY — режим «только чтение». Требуется в случаях, когда данные таблицы поступают из внешних источников, а не заносятся средствами Celesta, либо если необходимо подключиться к таблице, относящейся к иному приложению, и потому нежелательно вносить в её данные какие-либо изменения. В этом режиме механизм отслеживания версий записей отключается, а класс для доступа к таблице генерируется без методов модификации данных. Кроме того, для таких таблиц не требуется указывать первичный ключ.

  • Опция NO AUTOUPDATE, которая может применяться совместно с данными опциями, отключает таблицу от процесса автообновления базы данных. Используется в случае, когда структура какой-либо таблицы изменяется в базе данных иными средствами и система не должна пытаться синхронизировать структуру этой таблицы с описанием на языке CelestaSQL автоматически.

7. Конструкция CREATE INDEX

Индексы применяются для ускорения фильтрации по полям таблиц и создаются при помощи следующей синтаксической конструкции:

CREATE INDEX index name ON table name ( field name , ) create_index

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

8. Конструкция CREATE VIEW

Представления (views) служат для доступа только на чтение к данным, собранным из одной или нескольких таблиц при помощи SQL-запроса SELECT. Для каждого своего представления Celesta создаёт объект-представление в базе данных, транслируя при этом SQL-запрос на языке CelestaSQL в соответствующий диалект языка SQL.

Представления создаются при помощи синтаксической конструкции

CREATE VIEW view name AS query create_view

  • <view name> (имя представления) должно быть уникальным в пределах гранулы и не совпадать с именем таблицы.

  • query (запрос) представляет собой SQL-запрос, имеющий следующий синтаксис:

select UNION ALL query

SELECT-запросы в CelestaSQL могут быть объединены в цепочку при помощи UNION ALL. Как обычно, требуется, чтобы каждый из запросов в цепочке возвращал одинаковое количество столбцов, и столбцы были совпадающих типов.

В качестве названий столбцов, возвращаемых выражением UNION ALL, принимаются названия столбцов первого SELECT-запроса. Nullability возвращаемых столбцов вычисляется по правилу: выражение столбца может возвращать null, если хотя бы в одном из запросов в цепочке UNION ALL соответствующий столбец может возвращать null.

SELECT DISTINCT term AS field alias aggregate , FROM from_clause WHERE condition group_by select
table_ref AS table alias INNER LEFT RIGHT JOIN table_ref AS table alias ON condition from_clause

Основными ограничениями и отличиями такого запроса от SQL-запросов в различных СУБД являются:

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

  1. Конструкция SELECT * не поддерживается, и всякое поле запроса, если только это не ссылка на поле таблицы с уникальным в рамках запроса именем, должно иметь определённый и уникальный псевдоним. Это нужно для возможности однозначного создания класса-курсора, поля которого соответствуют именам столбцов запроса.

  2. Не поддерживается конструкция ORDER BY, т. к. при необходимости определённым образом отсортировать результирующий набор следует воспользоваться методом orderBy(…​) соответствующего курсора.

  3. Не поддерживается конструкция GROUP BY…​HAVING.

  4. Не поддерживаются FULL JOIN (объединения LEFT и RIGHT joins) и CROSS JOIN (декартовы произведения таблиц).

  5. Не поддерживается конструкция WITH и вложенные запросы.

Большая часть из функциональности, не поддерживаемой на уровне CelestaSQL, эффективно эмулируется в API классов доступа к данным.

Ссылка на таблицу (table_ref) имеет синтаксис

line(optx('/<grain name>', '.'), '/<table name>')

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

Терм, определяющий поле представления, имеет следующий синтаксис:

- primary_term ( term ) UPPER LOWER ( term ) + - * / || term

Над выражениям типа INT и REAL допустимы обычные арифметические операции с обычным приоритетом: максимальный приоритет у унарного минуса, далее — умножение и деление («*», «/»), далее — сложение и вычитание («+», «-»). Над выражениями типа VARCHAR допустима операция конкатенации «||», а также функции UPPER и LOWER, сводящие текст к верхнему и нижнему регистру, соответственно. Операции над выражениями прочих типов недопустимы.

table name table alias . field name string literal integer literal float.-point literal TRUE FALSE GETDATE() $param id primary_term

Ссылки на поля могут быть однокомпонентными (если они однозначно указывают на поле определённой таблицы) либо двухкомпонентными, в этом случае в качестве первой компоненты следует указывать псевдоним таблицы из конструкции FROM либо — если явный псевдоним отсутствует — имя таблицы.

Специальный вид идентификатора <$param id> — знак $, за которым следует идентификатор — служит для ссылок на параметры функций.

Наконец, синтаксис логического выражения condition, используемого в конструкциях JOIN …​ ON и WHERE:

NOT predicate ( condition ) AND OR condition
term = > >= <= < <> LIKE term BETWEEN term AND term IN ( term , ) IS NULL predicate
COUNT ( * ) SUM MIN MAX ( term ) AS field alias aggregate

Следует отметить, что term внутри конструкции SUM должен являться числом.

GROUP BY field name field alias , group_by

Особенность конструкции GROUP BY в CelestaSQL заключается в необходимости всегда перечислять в ней все неагрегатные колонки из выборки.

9. Конструкция CREATE MATERIALIZED VIEW

Материализованные представления (materialized views) служат для доступа только на чтение к агрегатным данным, собранным из одной таблицы и объединенным при помощи выражения GROUP BY. Для каждого своего материализованного представления Celesta создаёт таблицу в базе данных, модифицируемую триггерами базы данных при изменении родительской таблицы.

Материализованные представления создаются при помощи синтаксической конструкции

CREATE MATERIALIZED VIEW view name AS SELECT materialized_aggregate field name AS field alias , FROM table_ref group_by create_materialized_view

Основными ограничениями и отличиями такого запроса от SQL-запросов в различных СУБД являются:

  1. Запросы строятся только на таблице текущей гранулы.

  2. Конструкция SELECT * не поддерживается.

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

  4. Неагрегатные колонки должны ссылаться на NOT NULL колонки родительской таблицы.

  5. Если в GROUP BY выражении участвует колонка типа DATETIME, то ее значения будут округляться в точности до дня (часы, минуты и более точные измерения отсекаются).

  6. Из агрегирующих операций доступны только SUM и COUNT.

COUNT ( * ) SUM ( term ) AS field alias materialized_aggregate

10. Конструкция CREATE FUNCTION

Данная конструкция определяет функции — они же параметризованные представления. Они служат для доступа только на чтение к данным, собранным из одной или нескольких таблиц при помощи SQL-запроса SELECT с учетом переданных параметров.

Функции создаются при помощи синтаксической конструкции

CREATE FUNCTION function name ( param_definition , ) AS query create_function

Параметры декларируются через запятую, с заданием имени и типа параметра:

param name INT REAL DECIMAL VARCHAR DATETIME BIT param_definition

Чтобы сослаться на параметр в выражении функции, необходимо перед именем параметра ставить знак $ (например, param1 будет фигурировать в выражении запроса как $param1).

Основными ограничениями и отличиями такого запроса от SQL-запросов в различных СУБД являются:

  1. Конструкция SELECT * не поддерживается.

  2. В объявлении обязательно должен иметься хотя бы один параметр.

  3. Все объявленные параметры обязательно должны использоваться.

В остальном синтаксическое выражение аналогично обычному View.

Пример функции:

CREATE FUNCTION pView2(param int, param2 varchar) AS
  select f1, f2, f3 from t1
  where f2 = $param AND f3 = $param2;