Доступ к данным через ORM

Сложный
PDF

Доступ к базе данных предоставляет группа классов серверного ядра приложения, перечисленные ниже. С их помощью можно выполнять весь набор 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 предназначен для доступа к объекту, который представляет собой запись в таблице базы данных. Он также может использоваться для добавления, изменения и удаления определенных записей.

Корневая схема 

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

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

src_UsingEntitySchemaQuery1_ru.jpg

В общем случае формат построения имени произвольной колонки из схемы N можно представить в следующем виде:

Контекст схемы 1].[....Контекст схемы N.Имя_колонки

Многопоточность при работе с базой данных 

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

Важно. Проблема возникает при работе с базой данных, даже если DBExecutor не используется напрямую, а используется, например, через EntitySchemaQuery.

Важно. Поскольку для работы с базой данных используются неуправляемые (unmanaged) ресурсы, то создание экземпляра DBExecutor необходимо оборачивать в оператор using. Или же явно вызывать метод Dispose() для освобождения ресурсов. Подробнее об использовании оператора using можно можно узнать из документации.

Пример неправильного использования 

Ниже приведен фрагмент исходного кода, в котором DBExecutor используется неправильно. Нельзя выполнять вызов методов экземпляра DBExecutor в параллельных потоках.

// Создание параллельного потока.
var task = new Task(() => {
    // Использование экземпляра DBExecutor в параллельном потоке.
    using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
        dbExecutor.StartTransaction();
        //...
        dbExecutor.CommitTransaction();
    }
});
// Запуск асинхронной задачи в параллельном потоке.
// Выполнение программы в основном потоке продолжается дальше.
task.Start();
//...
var select = (Select)new Select(UserConnection)
        .Column("Id")
        .From("Contact")
        .Where("Name")
        .IsEqual(Column.Parameter("Supervisor"));
// Использование экземпляра DBExecutor в основном потоке приведет к возникновению ошибки,
// т.к. экземпляр DBExecutor уже используется в параллельном потоке.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
        while (dataReader.Read()) {
            //...
        }
    }
}

Пример правильного использования 

Ниже приведен фрагмент исходного кода, в котором DBExecutor используется корректно. Вызов методов экземпляра DBExecutor производится последовательно, в одном потоке.

// Первое использование экземпляра DBExecutor в основном потоке.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    dbExecutor.StartTransaction();
    //...
    dbExecutor.CommitTransaction();
}
//...
var select = (Select)new Select(UserConnection)
        .Column("Id")
        .From("Contact")
        .Where("Name")
        .IsEqual(Column.Parameter("Supervisor"));
// Повторное использование экземпляра DBExecutor в основном потоке.
using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
    using (IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
        while (dataReader.Read()) {
            //...
        }
    }
}

Работа с PostgreSQL 

Общие рекомендации:

  1. Не рекомендуется использовать для создания триггеров, представлений, функций команду CREATE OR REPLACE. Вместо этого используйте конструкцию DROP … IF EXISTS (при необходимости можно использовать команду CASCADE), а затем CREATE OR REPLACE.
  2. Вместо схемы "dbo" используйте "public".
  3. Учитывайте регистрозависимость системных имен, используйте кавычки ("") для имен таблиц, колонок и т.д..
  4. Вместо типа BIT в MS SQL используйте в Postgres тип BOOL. Для проверки значения поля типа BOOL необязательно использовать конструкцию WHERE "boolColumn" = true, достаточно WHERE "boolColumn" или WHERE NOT "boolColumn".
  5. В Postgres можно использовать сокращенный вид явного преобразования ::TEXT.
  6. В Postgres при сравнении строк учитывается регистр. Для выполнения регистронезависимого сравнения можно использовать ключевое слово iLIKE. Однако учитывайте, что сравнение при этом значительно более медленное, чем при использовании комбинации UPPER+LIKE. Кроме того у комбинации UPPER+LIKE менее строгие правила применимости индексов, чем у iLIKE.
  7. Если нет какого-либо неявного приведения типов, то его можно создать с помощью команды CREATE CAST. Подробнее об этом читайте в документации PostgreSQL.
  8. В Postgres нет встроенной функции NESTLEVEL в рекурсивных процедурах. Для хранения текущего уровня рекурсии следует создавать специальный параметр процедуры.
  9. Вместо типа SYSNAME используйте тип NAME.
  10. Вместо пустых INSTEAD-триггеров создавайте правила, например:
    CREATE RULE RU_VwAdministrativeObjects AS
    ON UPDATE TO "VwAdministrativeObjects"
    DO INSTEAD NOTHING;
    
  11. При выполнении команды UPDATE в Postgres не работает неявное преобразование типа INT в тип BOOL, даже при наличии соответствующего оператора CAST. Следует явно привести INT -значение к типу BOOL.
  12. Способы форматирования строковых литералов подробно описаны на сайте документации PostgreSQL:
  13. Вместо @@ROWCOUNT используйте следующую конструкцию:
    DECLARE rowsCount BIGINT = 0;
    GET DIAGNOSTICS rowsCount = row_count;
    
  14. Вместо конструкции в MS SQL
    (CASE WHEN EXISTS (
      SELECT 1
      FROM [SysSSPEntitySchemaAccessList]
      WHERE [SysSSPEntitySchemaAccessList].[EntitySchemaUId] = [BaseSchemas].[UId]
      )
    THEN 1 ELSE 0 END) AS [IsInSSPEntitySchemaAccessList]
    
    в PostgreSql следует использовать конструкцию
    EXISTS (
      SELECT 1
      FROM "SysSSPEntitySchemaAccessList"
      WHERE "EntitySchemaUId" = BaseSchema."UId"
     ) "IsInSSPEntitySchemaAccessList"
    
    Полученное в результате запроса поле будет иметь тип BOOL.

Соответствие типов данных 

Таблица соответствия типов данных Creatio, MS SQL и PostgreSQL
Тип данных в дизайнере объекта 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.

scr_postgresql_adding.png