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

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

Кроссплатформенные запросы, которые выполняются на уровне объектов
класса.

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

Пример запроса
```scala
new OQuery(Bs_GoodsAta.Type) {  
  where(t.sSystemName === spMnemoCode)  
}
```
`OQuery` предоставляет подмножество [JPQL](https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL)
и используется для выбора данных по классу объектный кэш.

### Методы
- `where` \
  Условие запроса.
- `orderBy` \
  Выражение для сортировки результата
- `batchAll` \
  Массовая загрузка объектов. Возвращает строки с
  прогруженными записями всех коллекций этого класса.
- `batchIn` \
  Выполнение запроса с указанным перечнем коллекций, в которых требуется прогрузить данные.
- `forUpdate` \
  Выполнение запроса с блокированием вернувшихся записей
- `forUpdateNoWait` \
  Выполнение запроса с блокирование вернувшихся
  записей, без ожидания разблокирования, если записи уже заблокированы
  другой сессией.
- `tryCacheQueryResults` \
  Попытаться закешировать результат запроса.
  Смотри пункт «Кеширование»
- `unique` \
  Говорит, что запрос возвращает одну уникальную запись.
  Позволяет использовать cache-index’ы, указанные в orm класса

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

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

Пример:
```scala
new ElExpOQuery(api).forWhere(  
  _.get("sSystemName").equal("test".ns.get)  
).addOrdering(  
  _.get("sCaption")  
)
```
смотри примеры использования: `ru.bitec.app.btk.ElExpOQueryTest`

### Объектный запрос большого списка
```scala 
in.ru.bitec.app.btk.Btk_QueryPkg#largeInQuery
```
смотри примеры использования: `ru.bitec.app.btk.Btk_QueryPkgTest`

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

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

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

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

Запрос выглядит следующим образом:
```scala
new OQuery(entityAta.Type) {  
  unique()  
  where(t.sSystemName*=== spMnemoCode)  
}
```

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

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

Чтобы включить кэширования запроса:

1. Добавьте в запрос опцию `tryCacheQueryResults()`. \
   Результат такого запроса будет кэширован,
   если транзакция не находится в режиме редактирования разделяемых
   объектов. 
   
Пример запроса:
```scala
new OQuery(entityAta.Type) {  
  tryCacheQueryResults ()  
  where(t.sSystemName === spMnemoCode)  
}
```

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

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

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

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

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

Пример объявления:
```scala
lazy val idxidParent = TxIndex(Btk_GroupAta.Type)(_.idParentGroup)
```
Методы
- `byKey` \
  Возвращает итератор по ключу индекса.
- `refreshByKey` \
  Возвращает итератор по ключу индекса c обновлением из базы данных.
- `queryKeys` \
  Кеширование ключей индекса.
- `forPartition` \
  Открывает секцию для массового обновления индекса.
  Используется для прозрачного массового обновления после очистки транзакционного кэша.
  Секции могут быть вложенными друг в друга, в таком случае ключи суммируются.

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

Для обработки реляционных запросов в основном используется методы на
базе [anorm](http://playframework.github.io/anorm/).

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

### ASQL

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

### ATSQL

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

### ASelect

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

Пример:
```scala
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())
}
```
### Типичные ошибки

#### Некорректное использование запросов на чтение
```{attention}
Даже если такой код
работает в тестовом случае, это будет приводить к ошибкам в продакшене.
```

Пример неверного запроса:
```scala
ASQL"select * from table where id=$id for no key update".execute()
```
Так как данный запрос не регистрирует транзакцию, он может быть выполнен
в режиме автокоммита, что фактически делает его бесмысленным, так как
блокировка снимается сразу же после выполнения запроса.

```scala
ASQL"update table set a=$v where id=$c".execute()
```
Данный запрос может быть выполнен в текущей транзакции, а может быть не
выполнен в транзакции на чтение, поэтом становиться абсолютно не
предсказуемое поведение на откат, и контроль ссылочной. Что легко может
наводить ошибки на бизнес логику.

~~~{attention}
Внимание, все запросы на изменения данных должны быть транзакционными:
```scala
ATSQL"select * from table where id=$id for no key update".execute()

ATSQL"update table set a=$v where id=$c".execute()
```
~~~
Если вам необходимо выполнить запрос в отдельной транзакции, всегда
создавайте атомарные транзакции, помните, даже если в вашем тестовом
случаи транзакция не начата, это не значит что она не может быть начата
в другом контексте.

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

```scala
ATSQL"select * from reference where id=$id for update".execute()
```
Данная блокировка заблокирует работу всех документов по
справочнику, так как блокируется не только запись, но и все внешние
ключи.

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

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

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

```scala
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 форму

```scala

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("символ для замены") -> ваша переменная, ...)`

```scala
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|


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

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

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

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

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

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

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

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

- Сохранение данных в файл через команду copy
- Для каждой пачки
  - Начало транзакции
  - Копирование данных в gtt
  - Выполнение dml операции
  - Завершение транзакции

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

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

Постгресс по своей архитектуре менее зависим от длинны
транзакции( смотри [наполнение базы данных](https://www.postgresql.org/docs/9.6/populate.html)). 
Таким образом дробление нет потребности в дроблении на мелкие транзакции.

Однако слишком длинные транзакции негативно влияют на базу, 
происходят следующие негативные эффекты:
- блокировка данных
- блокируется сборщик мусора, что может привести к падению
  производительности запросов на dead row,и к распуханию файлов

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

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