Доступ к базе данных предоставляет группа классов серверного ядра приложения, перечисленные ниже. С их помощью можно выполнять весь набор CRUD-операций, учитывать права доступа текущего пользователя, помещать полученные данные в хранилища кэша.
Получение данных их базы
Select
Класс Terrasoft.Core.DB.Select предназначен для построения запросов выборки записей из таблиц базы данных. В результате создания и конфигурирования экземпляра этого класса будет построен запрос в базу данных приложения в виде SQL-выражения SELECT. В запрос можно добавить требуемые колонки, фильтры и условия ограничений. Результаты выполнения запроса возвращаются в виде экземпляра, реализующего интерфейс System.Data.IDataReader, либо скалярного значения требуемого типа.
При работе с классом Select не учитываются права доступа пользователя, использующего текущее соединение. Доступны абсолютно все записи из базы данных приложения. Также не учитываются данные, помещенные в хранилище кэша. Если необходимы дополнительные возможности по управлению правами доступа и работе с хранилищем кэша Creatio, следует использовать класс EntitySchemaQuery.
EntitySchemaQuery
Класс Terrasoft.Core.Entities.EntitySchemaQuery предназначен для построения запросов выборки записей из таблиц базы данных с учетом прав доступа текущего пользователя. В результате создания и конфигурирования экземпляра этого класса будет построен запрос в базу данных приложения в виде SQL-выражения SELECT. В запрос можно добавить требуемые колонки, фильтры и условия ограничений.
Класс EntitySchemaQuery реализует механизм работы с хранилищем (кэшем Creatio либо произвольным хранилищем, определенным пользователем). При выполнении запроса EntitySchemaQuery данные, полученные из базы данных на сервере, помещаются в кэш. В качестве кэша запроса может выступать произвольное хранилище, которое реализует интерфейс Terrasoft.Core.Store.ICacheStore. По умолчанию в качестве кэша запроса EntitySchemaQuery выступает кэш Creatio уровня сессии (данные доступны только в сессии текущего пользователя) с локальным хранением данных.
Для запросов EntitySchemaQuery можно определить дополнительные настройки, которые задают параметры для постраничного вывода результатов выполнения запроса, а также параметры построения иерархического запроса. Для этого предназначен класс Terrasoft.Core.Entities.EntitySchemaQueryOptions.
Результатом выполнения запроса EntitySchemaQuery является экземпляр Terrasoft.Nui.ServiceModel.DataContract.EntityCollection (коллекция экземпляров класса Terrasoft.Core.Entities.Entity). Каждый экземпляр Entity в коллекции представляет собой строку набора данных, возвращаемого запросом.
Особенности EntitySchemaQuery
1) Поддержка прав доступа
Запрос на выборку данных EntitySchemaQuery строится таким образом, чтобы учитывать права текущего пользователя. В результирующий набор попадут только те данные, к которым текущий пользователь имеет доступ согласно его правам. Дополнительно для EntitySchemaQuery можно регулировать условия наложения прав на связанные таблицы, присутствующие в запросе (которые присоединяются к запросу предложением JOIN). Эти условия определяются значением свойства JoinRightState экземпляра EntitySchemaQuery.
2) Механизм кеширования
В EntitySchemaQuery реализован механизм работы с хранилищем (кэшем Creatio либо произвольным хранилищем, определенным пользователем). При выполнении запроса EntitySchemaQuery данные, полученные из базы данных на сервере, помещаются в кэш. В качестве кэша запроса может выступать произвольное хранилище, которое реализует интерфейс ICacheStore. По умолчанию в качестве кэша запроса EntitySchemaQuery выступает кэш Creatio уровня сессии (данные доступны только в сессии текущего пользователя) с локальным хранением данных. Кэш запроса определяется свойством Cache экземпляра EntitySchemaQuery. С помощью свойства CacheItemName задается ключ доступа к кэшу запроса (пример 4).
3) Дополнительные настройки запроса
Для запросов EntitySchemaQuery можно определить дополнительные настройки, которые задают параметры для постраничного вывода результатов выполнения запроса, а также параметры построения иерархического запроса. Для этого предназначен класс EntitySchemaQueryOptions.
Свойства класса EntitySchemaQueryOptions:
- HierarchicalColumnName — имя колонки, использующейся для построения иерархического запроса;
- HierarchicalColumnValue — начальное значение иерархической колонки, от которого будет строиться иерархия;
- HierarchicalMaxDepth — максимальный уровень вложенности иерархического запроса;
- PageableConditionValues — значения условий постраничного вывода;
- PageableDirection — направление постраничного вывода;
- PageableRowCount — количество записей страницы результирующего набора данных, возвращаемого запросом.
Один и тот же экземпляр EntitySchemaQueryOptions можно использовать для получения результатов выполнения различных запросов, передавая его в качестве параметра методу GetEntityCollection() соответствующего запроса (Примеры EntitySchemaQuery).
Добавление данных
Insert
Класс Terrasoft.Core.DB.Insert предназначен для построения запросов на добавление записей в таблицы базы данных Creatio. В результате создания и конфигурирования экземпляра этого класса будет построен запрос в базу данных приложения в виде SQL-выражения INSERT. В результате выполнения запроса возвращается количество задействованных запросом записей.
InsertSelect
Класс Terrasoft.Core.DB.InsertSelect предназначен для построения запросов на добавление записей в таблицы базы данных Creatio. При этом в качестве источника добавляемых данных используется экземпляр класса Terrasoft.Core.DB.Select. В результате создания и конфигурирования экземпляра Terrasoft.Core.DB.InsertSelect будет построен запрос базу данных приложения в виде SQL-выражения INSERT INTO SELECT.
При работе с классом InsertSelect на добавленные записи не применяются права доступа по умолчанию. К таким записям не применены вообще никакие права приложения (по операциям на объект, по записям, по колонкам). Пользовательское соединение используется только для доступа к таблице базы данных.
После выполнения запроса InsertSelect в базу данных будет добавлено столько записей, сколько вернется в его подзапросе Select.
Изменение данных
Класс Terrasoft.Core.DB.Update предназначен для построения запросов на изменение записей в таблице базы данных Creatio. В результате создания и конфигурирования экземпляра этого класса будет построен запрос базу данных приложения в виде SQL-выражения UPDATE.
Удаление данных
Класс Terrasoft.Core.DB.Delete предназначен для построения запросов на удаление записей в таблице базы данных Creatio. В результате создания и конфигурирования экземпляра этого класса будет построен запрос базу данных приложения в виде SQL-выражения DELETE.
Работа с сущностью базы данных
Класс Terrasoft.Core.Entities.Entity предназначен для доступа к объекту, который представляет собой запись в таблице базы данных. Он также может использоваться для добавления, изменения и удаления определенных записей.
Корневая схема
Корневая схема — это схема (таблица в базе данных), относительно которой выполняется построение путей ко всем колонкам в запросе, в том числе к колонкам присоединяемых таблиц.
При построении путей к колонкам применяется принцип связей через справочные поля. Имя произвольной колонки, добавляемой в запрос, можно построить в виде цепочки взаимосвязанных звеньев, каждое из которых представляет собой "контекст" конкретной схемы, которая связывается с предыдущей по внешнему ключу.

В общем случае формат построения имени произвольной колонки из схемы N можно представить в следующем виде:
Контекст схемы 1].[....Контекст схемы N.Имя_колонки
Многопоточность при работе с базой данных
Использование нескольких потоков при работе с базой данных через UserConnection может привести к проблемам синхронизации старта и коммита транзакций.
Пример неправильного использования
Ниже приведен фрагмент исходного кода, в котором DBExecutor используется неправильно. Нельзя выполнять вызов методов экземпляра DBExecutor в параллельных потоках.
Пример правильного использования
Ниже приведен фрагмент исходного кода, в котором DBExecutor используется корректно. Вызов методов экземпляра DBExecutor производится последовательно, в одном потоке.
Работа с PostgreSQL
Общие рекомендации:
- Не рекомендуется использовать для создания триггеров, представлений, функций команду CREATE OR REPLACE. Вместо этого используйте конструкцию DROP … IF EXISTS (при необходимости можно использовать команду CASCADE), а затем CREATE OR REPLACE.
- Вместо схемы "dbo" используйте "public".
- Учитывайте регистрозависимость системных имен, используйте кавычки ("") для имен таблиц, колонок и т.д..
- Вместо типа BIT в MS SQL используйте в Postgres тип BOOL. Для проверки значения поля типа BOOL необязательно использовать конструкцию WHERE "boolColumn" = true, достаточно WHERE "boolColumn" или WHERE NOT "boolColumn".
- В Postgres можно использовать сокращенный вид явного преобразования ::TEXT.
- В Postgres при сравнении строк учитывается регистр. Для выполнения регистронезависимого сравнения можно использовать ключевое слово iLIKE. Однако учитывайте, что сравнение при этом значительно более медленное, чем при использовании комбинации UPPER+LIKE. Кроме того у комбинации UPPER+LIKE менее строгие правила применимости индексов, чем у iLIKE.
- Если нет какого-либо неявного приведения типов, то его можно создать с помощью команды CREATE CAST. Подробнее об этом читайте в документации PostgreSQL.
- В Postgres нет встроенной функции NESTLEVEL в рекурсивных процедурах. Для хранения текущего уровня рекурсии следует создавать специальный параметр процедуры.
- Вместо типа SYSNAME используйте тип NAME.
- Вместо пустых INSTEAD-триггеров создавайте правила, например:
- При выполнении команды UPDATE в Postgres не работает неявное преобразование типа INT в тип BOOL, даже при наличии соответствующего оператора CAST. Следует явно привести INT -значение к типу BOOL.
- Способы форматирования строковых литералов подробно описаны на сайте документации PostgreSQL:
- Вместо @@ROWCOUNT используйте следующую конструкцию:
- Вместо конструкции в MS SQL
Соответствие типов данных
Тип данных в дизайнере объекта Creatio | Тип данных в MS SQL | Тип данных в PostgreSQL |
---|---|---|
BLOB | VARBINARY | BYTEA |
Boolean | BIT | BOOLEAN |
Color | NVARCHAR | CHARACTER VARYING |
CRC | NVARCHAR | CHARACTER VARYING |
Currency | DECIMAL | NUMERIC |
Date | DATE | DATE |
Date/Time | DATETIME2 | TIMESTAMP WITHOUT TIME ZONE |
Decimal (0.00000001) | DECIMAL | NUMERIC |
Decimal (0.0001) | DECIMAL | NUMERIC |
Decimal (0.001) | DECIMAL | NUMERIC |
Decimal (0.01) | DECIMAL | NUMERIC |
Decimal (0.1) | DECIMAL | NUMERIC |
Encrypted string | NVARCHAR | CHARACTER VARYING |
File | VARBINARY | BYTEA |
Image | VARBINARY | BYTEA |
Image Link | UNIQUEIDENTIFIER | UUID |
Integer | INTEGER | INTEGER |
Lookup | UNIQUEIDENTIFIER | UUID |
Text (250 characters) | NVARCHAR(250) | CHARACTER VARYING |
Text (50 characters) | NVARCHAR(50) | CHARACTER VARYING |
Text (500 characters) | NVARCHAR(500) | CHARACTER VARYING |
Time | TIME | TIME WITHOUT TIME ZONE |
Unique identifier | UNIQUEIDENTIFIER | UUID |
Unlimited length text | NVARCHAR(MAX) | TEXT |
Привязка SQL-сценария к пакету
Если в пакете привязаны SQL-сценарии, например, для MS SQL, то для работы с Postgres создайте скрипт, который выполняет те же функции, но использует синтаксис PostgreSQL. Для этого на вкладке SQL-сценарии добавьте скрипт с типом СУБД PostgreSql.
