Перейти к основному содержанию
`Ctrl`+`K`

# Взаимодействие с базой данных

## Объектные запросы

Кроссплатформенные запросы, которые выполняются на уровне объектов класса. При выполнении запроса идет обращение к базе данных, если по классу не настроено кэширование запросов.

Пример запроса

    new OQuery(Bs_GoodsAta.Type) {
      where(t.sSystemName === spMnemoCode)
    }

`OQuery` предоставляет подмножество JPQL и используется для выбора данных по классу объектный кэш.

### Методы

- `where`
  Условие запроса.

- `orderBy`
  Выражение для сортировки результата

- `batchAll`
  Массовая загрузка объектов. Возвращает строки с прогруженными записями всех коллекций этого класса.

- `batchIn`
  Выполнение запроса с указанным перечнем коллекций, в которых требуется прогрузить данные.

- `forUpdate`
  Выполнение запроса с блокированием вернувшихся записей

- `forUpdateNoWait`
  Выполнение запроса с блокирование вернувшихся записей, без ожидания разблокирования, если записи уже заблокированы другой сессией.

- `tryCacheQueryResults`
  Попытаться закешировать результат запроса. Смотри пункт «Кеширование»

- `unique`
  Говорит, что запрос возвращает одну уникальную запись. Позволяет использовать cache-index’ы, указанные в orm класса

### Объектный запрос c помощью выражений EclipseLink

Функционал, расширяющий использование объектных запросов, который позволяет запрашивать данные из неизвестных классов (когда заранее неизвестен тип Ata). Класс:`ru.bitec.app.gtk.eclipse.query.ElExpOQuery`

Пример:

    new ElExpOQuery(api).forWhere(
      _.get("sSystemName").equal("test".ns.get)
    ).addOrdering(
      _.get("sCaption")
    )

смотри примеры использования: `ru.bitec.app.btk.ElExpOQueryTest`

### Объектный запрос большого списка

    in.ru.bitec.app.btk.Btk_QueryPkg#largeInQuery

смотри примеры использования: `ru.bitec.app.btk.Btk_QueryPkgTest`

### Кеширование объектных запросов

Кеширование запросов работает только для классов с разделяемым режимом кеширования(`Shared`).

#### Кэширование по полю

Кеширование через cache-index’ы указанные в orm классе. Такой запрос возвращает одну строку и дополняется командой `unique()`. Например для атрибутов мнемокода класса в orm формируется запись:

    <cache-index>
      <column-name>SSYSTEMNAME</column-name>
    </cache-index>

Запрос выглядит следующим образом:

    new OQuery(entityAta.Type) {
      unique()
      where(t.sSystemName*=== spMnemoCode)
    }

#### Кэширование объектных запросов

Кэширование объектных запросов возможно по требованию, в случае, если класс настроен для сохранения в разделяемом кэше.

Чтобы включить кэширование запроса, добавьте в запрос опцию `tryCacheQueryResults()`.
Результат такого запроса будет кэширован, если транзакция не находится в режиме редактирования разделяемых объектов.

Пример запроса:

    new OQuery(entityAta.Type) {
      tryCacheQueryResults ()
      where(t.sSystemName === spMnemoCode)
    }

Внимание

Объектный запрос не отображает удаленные строки из текущего рабочего пространства, однако он не способен отслеживать изменения в `where` условии. Если в текущем рабочем пространстве был изменен мнемокод, то объектный запрос вернет данные без учета этих изменений.

Для гарантированного получения согласованных изменений в объектном запросе необходимо вызывать перед ним `flush`.

## Транзакционный индекс

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

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

Примечание

В отличие от объектного запроса, транзакционный индекс видит любые изменения в рабочем пространстве по индексируемому полю.

Пример объявления:

    lazy val idxidParent = TxIndex(Btk_GroupAta.Type)(_.idParentGroup)

Методы

- `byKey`
  Возвращает итератор по ключу индекса.

- `refreshByKey`
  Возвращает итератор по ключу индекса c обновлением из базы данных.

- `queryKeys`
  Кеширование ключей индекса.

- `forPartition`
  Открывает секцию для массового обновления индекса. Используется для прозрачного массового обновления после очистки транзакционного кэша. Секции могут быть вложенными друг в друга, в таком случае ключи суммируются.

## Реляционные запросы

Для обработки реляционных запросов в основном используется методы на базе anorm.

Для более удобного использования в контекст бизнес логики добавлены дополнительные функции.

Внимание

Реляционные запросы полностью игнорируют изменения в рабочем пространстве. Если требуется выполнить согласованный запрос, используйте метод `session.flush()`

### ASQL

Выполнение запроса на чтение

### ATSQL

Выполнение запроса с изменением данных или блокировками

### ASelect

Выполнение запросов на чтение\запись с большим кол-вом колонок.

Пример:

    for (rv <- new ASelect {
      val nParentLevel = asInt("nParentLevel")
      val gidParent = asString("gidParent")
      val gidChild = asString("gidChild")
      val idParent = asLong("idParent")
      SQL"""
      select nParentLevel,gidParent,gidChild,idParent,idChild from table
      """
    }) {
      println(rv.nParentLevel())
      //запрос поля без его предварительного объявления
      println(rv.get("idChild").asNLong())
    }

### Типичные ошибки

#### Некорректное использование запросов на чтение

Внимание

Даже если такой код работает в тестовом формате, это будет приводить к ошибкам в продакшене.

Пример неверного запроса:

    ASQL"select * from table where id=$id for no key update".execute()

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

    ASQL"update table set a=$v where id=$c".execute()

Данный запрос может быть выполнен в текущей транзакции, а может быть не выполнен в транзакции на чтение, поэтому становится абсолютно непредсказуемое поведение на откат, и контроль ссылочной. Что легко может приводить к ошибкам в бизнес-логике.

Внимание

Внимание, все запросы на изменения данных должны быть транзакционными:

    ATSQL"select * from table where id=$id for no key update".execute()

    ATSQL"update table set a=$v where id=$c".execute()

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

#### Некорректное использование блокировок

    ATSQL"select * from reference where id=$id for update".execute()

Данная блокировка заблокирует работу всех документов по справочнику, так как блокируется не только запись, но и все внешние ключи.

Всегда пишите `for no key update`, если у вас нет четкого обоснования необходимости иной блокировки.

#### Некорректное формирование запроса

Будьте осторожнее с подставлением значений в строку (интерполяции) при формировании sql запроса.

    ATSQL(s"select * from reference where gid=$gid for update")
    ASQL(s"select * from reference where gid=$gid for update")
    new ASelect {
      //...
      SQL(s"select * from reference where gid=$gid for update")
    }

В данном примере значение gid неправильно подставится в sql выражение, т.к. технически подстановка идет в обычную строку. Никто не догадается правильно конвертировать данные в корректную для sql форму

    ATSQL(s"select * from reference where gid={gid} for update").on(Symbol("gid") -> gid)
    ASQL(s"select * from reference where gid={gid} for update").on(Symbol("gid") -> gid)
    new ASelect {
      //...
      SQL(s"select * from reference where gid={gid} for update")
        on(Symbol("gid") -> gid/*, другие параметры*/)
    }

Для случаев, когда вам нужно собрать запрос из обычной строки, используйте замену символов `on(Symbol("символ для замены") -> ваша переменная, ...)`

    ATSQL"select * from reference where gid=$gid for update"
    ASQL"select * from reference where gid=$gid for update"
    new ASelect {
      //...
      SQL"select * from reference where gid=$gid for update"
    }

Тут будет подставлено все правильно

## Работа с большими данными

Postgresql не поддерживает bulk insert\select\update, более того, коммит закроет любой открытый курсор. Это делает невозможной обработку больших объёмов данных частями на сервере, так как очень длинные транзакции перегружают wal и в конечном счете сильно снижают общую производительность сервера. Поэтому необходимо выполнять партиционирование на сервере приложения.

Замеры производительности показывают, что наиболее производительным средством перегонки данных является sql команда postgresql «copy».

Сравнительная таблица производительности:

Операция 100000 записей | Приблизительное время(ms) на сервере
--- | ---
Вставка записей одним sql в базе | 214
Общее время запроса\вставки через copy | 728
Вставка записей одним sql в базе при наличии индексов | 2785
Общее время вставки через массовый flush при наличии индексов из сервера в базу | 19562
Общее время вставки через flush по одной записи при наличии индексов из сервера в базу | 156710
Запрос данных через ResultSet | 396
Запрос данных через команду copy | 330

Примечание

На десктопе разница между вставкой через copy и вставкой прямым sql может достигать различий в 10 раз. Финальные тесты производительности нужно замерять исключительно на серверном оборудовании.

Вставка через flush по одной записи может работать в 1000 раз медленнее, особенно это актуально для данных с аудитом.

Вставка через batch на чистом sql сравнима по порядку с copy, хоть и медленнее.

Массовая вставка на чистом sql должна быть раза в 4 быстрее за счет отсутствия затрат на кэш.

Таким образом наиболее высокую производительность при массовой работе с данными могут обеспечивают следующие алгоритмы:

### Массовая вставка

Через команду copy

### Массовый update\delete

- Сохранение данных в файл через команду copy

- Для каждой пачки

  - Начало транзакции

  - Копирование данных в gtt

  - Выполнение dml операции

  - Завершение транзакции

Внимание

Транзакция не должна завершаться до конца обработки пачки, иначе это приведет к потере данных в gtt(при завершении транзакции сессия отпускается)

### Размер транзакции

Постгресс по своей архитектуре менее зависим от длины транзакции (смотри наполнение базы данных). Таким образом нет потребности в дроблении на мелкие транзакции.

Слишком длинные транзакции негативно влияют на базу, происходят следующие негативные эффекты:

- блокировка данных

- блокировка сборщика мусора, что может привести к падению производительности запросов на dead row и к распуханию файлов

Таким образом на сервисных операциях, если они не превышают несколько гигабайтов(зависит от базы), разбиением транзакций можно пренебречь.

Желательно, чтобы оперативные транзакции не занимали больше 10 минут (зависит от частоты транзакций и нагрузки на базу) и не вызывали блокировок.