Язык Celesta-SQL

Материал из Course Orchestra
Перейти к: навигация, поиск

1. Справочник Celesta

1.1 Введение и основные понятия
1.2 Запуск и авто-обновление
1.3 Базовая настройка
1.4 Системные таблицы
1.5 CelestaSQL
1.6 CelestaDoc
1.7 Контексты сессии и вызова
1.8 Курсоры
1.9 BLOB-поля
1.10 Option-поля
1.11 Защита от потерянных обновлений
1.12 Метаданные Celesta
1.13 CelestaUnit

2. Celesta и базы данных

2.1 Особенности работы Celesta с поддерживаемыми типами СУБД
2.2 Проектирование базы данных Celesta в DBSchema

3. Создание решений с использованием Celesta для ShowCase

3.1 Программа обучения Celesta
3.2 Подготовка рабочего места для работы с Celesta
3.2.1 Для разработчиков платформы
3.2.2 Для разработчиков решений
3.3 Системные гранулы Celesta
3.3.1 common
3.3.1.1 Экспорт/импорт данных
3.3.1.2 Навигатор
3.3.1.3 Серии номеров
3.3.1.4 Иерархия Дьюи
3.3.1.5 Системные функции
3.3.1.6 Реестр настроек
3.3.1.7 Mailsender
3.3.1.8 Common.filter
3.3.2 common.api
3.3.4 security
3.3.3 lyra
3.4 Стандартные гранулы Celesta
3.4.1 dirusing
3.4.2 workflow
3.4.3 File repository
3.5 Отрисовка элементов Showcase при помощи Celesta
3.5.1 Конвертер XML-JSON
3.5.2 Навигатор (Navigator)
3.5.3 Информационная панель (Datapanel)
3.5.4 Серверное действие (Server activity)
3.5.5 Вебтекст (WebText)
3.5.6 Грид (Grid)
3.5.6.1 Панель инструментов (ToolBar)
3.5.7 XForms
3.5.7.1 Селекторы
3.5.7.2 Submission
3.5.7.3 Загрузка/Выгрузка файлов (Upload/Download)

5. Решение проблем

5.1 Проблемы с кодировкой jython-файлов

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

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


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

1.file.png

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

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

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

  /**комментарий CelestaDoc*/

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

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

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

  1. В обычных СУБД дозволяется, чтобы идентификаторы объектов содержали знаки пробела и неалфавитные знаки, если эти имена заключены в обрамляющие символы, например «[]» для MS SQL Server. Т. е. в MS SQLServer допустимо, например, такое имя таблицы: [Long Table$Name]. Celesta, однако, не может поддерживать пробелы и неалфавитные знаки в идентификаторах потому, что имя каждой Celesta-таблицы должно являться именем Python-класса, а имя каждого Celesta-поля — именем Python-переменной. Поэтому идентификатор любого именованного объекта CelestaSQL должен удовлетворять правилам наименования переменной в Python, т. е. может состоять только из больших и малых букв латинского алфавита, цифр и знаков подчёркивания, при этом не может начинаться с цифры.
  2. Заключение идентификаторов Celesta в кавычки в скриптах CelestaSQL не допускается на уровне синтаксиса, т. к. практической надобности в этом нет (имена никогда не содержат пробелов). На системном уровне при формировании запросов к СУБД Celesta, однако, всегда заключает имена своих объектов в прямые кавычки ("ANSI quotes"), чтобы гарантировать, что базы данных Oracle, PostgreSQL и H2 не будут нарушать регистр букв, составляющих идентификатор.
  3. Идентификаторы в Celesta являются чувствительными к регистру, однако нельзя создавать две таблицы, имена которых отличаются только регистром.
  4. Длина любого идентификатора в Celesta не может быть больше 30 символов.
  5. Идентификаторы (префиксы) гранул, кроме того, не могут содержать в себе знаков подчёркивания. Это связано с тем, что комбинация имени гранулы со знаком подчёркивания и другим идентификатором зачастую используется во внутрисистемных целях Celesta, и запрет на использование знака подчёркивания в именах гранул необходим для исключения возможных неоднозначностей.

Конструкция CREATE GRAIN

Единственной конструкцией, отличающей Celesta SQL от стандартного языка DDL, является выражение CREATE GRAIN, с которого должен начинаться любой скрипт определения гранулы. Используется следующий синтаксис:

2.create grain.png

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

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

3.create table.png 4.table constituent.png

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

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

4.field definition.png

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

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

5.inline fk definition.png

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

6.table ref.png

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


Типы данных

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


Integer (32-bit) Floating point (64-bit) String (Unicode) Long string (Unicode) Binary Date/Time Boolean
CELESTA INT REAL VARCHAR(n) TEXT BLOB DATETIME BIT
Microsoft SQL Server INT FLOAT(53) NVARCHAR(n) NVARCHAR(MAX) VARBINARY(MAX) DATETIME BIT
Oracle NUMBER REAL NVARCHAR2(n) NCLOB BLOB TIMESTAMP NUMBER

[check in (0, 1)]

PostgreSQL INT4 FLOAT8

[= DOUBLE PRECISION]

VARCHAR(n) TEXT BYTEA TIMESTAMP BOOL
H2 INTEGER DOUBLE VARCHAR(n) CLOB VARBINARY(MAX) TIMESTAMP BOOLEAN

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

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

8.int field.png

Вместо конструкции DEFAULT <целое число> для поля с типом INT может использоваться слово IDENTITY. Значения столбца, объявленного таким образом, будут автоматически инкрементироваться при вставке. В каждой таблице может использоваться не более одного IDENTITY-поля. Разработчик решения должен понимать тот факт, что несмотря на cхожесть синтаксиса создания автоинкрементного поля с опцией "IDENTITY" для SQL Server, в действительности Celesta не использует эти опции при работе с соответствующими базами данных. Поля инкрементируются в INSERT-триггерах, причём при работе с Oracle и Postgre применяется механизм SEQUENCE-ов, а при работе с MS SQL соответствующий механизм эмулируется при помощи логики триггеров и таблицы celesta.sequences. Причина отказа от использования автоинкрементных полей в том, что свойство автоинкрементности в MS SQL (в отличие от триггеров) невозможно произвольным образом добавить к полю или отбросить от поля, что лишило бы Celesta необходимой гибкости изменения структуры данных. См. также раздел «Особенности работы Celesta с поддерживаемыми типами СУБД».

9.floating field.png

10.text field.png

11.blob field.png

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

12.datetime field.png

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

13.bit field.png

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

14.primary key definition.png

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

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

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

  • Самое важное: для обеспечения работоспособности системы не допускается создание таблиц без PRIMARY KEY. (За исключением READ ONLY-таблиц).
  • В таблице может быть не более одного упоминания PRIMARY KEY, будь то сокращённое выражение в конце определения поля или составное выражение в определении таблицы.
  • Не допускается создание PK по полям с типами BLOB и TEXT.
  • Не допускается создание PK по nullable-полям.
  • Не допускается более одного вхождения одного и того же поля в определение PRIMARY KEY.

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

15.foreign key definition.png


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

16.add foreign key.png


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

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

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

7.fk rules.png

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

  • 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 IDENTITY 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 IDENTITY 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)
.................

Опции

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

5.table options.png

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

  • WITH VERSION CHECK — режим по умолчанию: включение механизма отслеживания версий записей для исключения потерянных обновлений (см. раздел Защита от потерянных обновлений). Указывать данную опцию явно не требуется.
  • WITH NO VERSION CHECK — отключение механизма отслеживания версий записей. Требуется в случаях, когда нет желания вводить новое системное поле в таблицу. Возможность модифицировать данные в таблице при этом сохраняется, но возможны «потерянные обновления».
  • WITH READ ONLY — режим «только чтение». Требуется в случаях, когда данные таблицы поступают из внешних источников, а не заносятся средствами Celesta, либо если необходимо подключиться к таблице, относящейся к иному приложению, и потому нежелательно вносить в её структуру какие-либо изменения. В этом режиме механизм отслеживания версий записей отключается, а ORM-класс для доступа к таблице генерируется без методов модификации данных. Кроме того, для таких таблиц не требуется указывать первичный ключ.
  • Опция NO AUTOUPDATE, которая может применяться совместно с данными опциями, отключает таблицу от процесса автообновления базы данных. Используется в случае, когда структура какой-либо таблицы изменяется в базе данных вручную и система не должна пытаться синхронизировать структуру этой таблицы с описанием на языке CelestaSQL автоматически.

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

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

17.create index.png

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

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

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

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

18.create view.png

  • <view name> (имя представления) должно быть уникальным в пределах гранулы и не совпадать с именем таблицы.
  • query (запрос) представляет собой SQL-запрос, имеющий следующий синтаксис:

19.query.png

20.from clause.png

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

  1. Запросы строятся только на таблицах (текущей гранулы или соседних гранул). Во избежание выстраивания неэффективных конструкций невозможно построение запросов на представлениях.
  2. Конструкция SELECT * не поддерживается, и всякое поле запроса, если только это не ссылка на поле таблицы с уникальным в рамках запроса именем, должно иметь определённый и уникальный псевдоним. Это нужно для возможности однозначного создания класса-курсора, поля которого соответствуют именам столбцов запроса.
  3. Не поддерживается конструкция ORDER BY, т. к. при необходимости определённым образом отсортировать результирующий набор следует воспользоваться методом orderBy(...) соответствующего курсора.
  4. Не поддерживается конструкция GROUP BY...HAVING (разработка поддержки этой конструкции возможна лишь при возникновении обоснованной необходимости в данной функциональности).
  5. Не поддерживаются FULL JOIN (объединения LEFT и RIGHT joins) и CROSS JOIN (декартовы произведения таблиц). Практическая значимость FULL JOIN сомнительна. CROSS JOIN сам по себе также не бывает практически необходим, неосторожное использование может привести к проблемам производительности.
  6. Не поддерживаются конструкции UNION, WITH, любые вложенные запросы. Конструкция бизнес-приложения должна предусматривать достаточное количество таблиц с данными, чтобы в этих конструкциях не было необходимости.

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

6.table ref.png

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

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

21.term.png

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

22.primary term.png

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

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

23.condition.png


24.predicate.png

25.aggregate.png

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

26.group by.png

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


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

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

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

Create materialized view.png

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

  1. Запросы строятся только на таблицe текущей гранулы.
  2. Конструкция SELECT * не поддерживается, и всякое поле запроса, если только это не ссылка на поле таблицы с уникальным в рамках запроса именем, должно иметь определённый и уникальный псевдоним. Это нужно для возможности однозначного создания класса-курсора, поля которого соответствуют именам столбцов запроса.
  3. В выборке обязательно должен участвовать хотя бы один агрегатный и хотя бы один неагрегатный столбец.
  4. Неагрегатные колонки должны ссылаться на not null колонки родительской таблицы.
  5. Если в group by выражении участвует колонка типа datetime, то ее значения будут округляться в точности до дня (часы, минуты и более точные измерения отсекаются).


Materialized aggregate.png


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

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

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

Create function.png

Param definition.png

Param literal.png


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

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

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

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

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