AppMaster предлагает обширные возможности для работы с базой данных. Например, с помощью блока Search можно найти необходимые данные, присоединить к ним связанные таблицы, отсортировать в правильном порядке и т.д. Тем не менее, в определенных ситуациях этого может оказаться недостаточно и тогда на помощь приходит блок SQL Exec. Он позволяет выполнять любые запросы к базе, используя всю мощь SQL.
Рассмотрим работу блока на примере базы, которая содержит в себе каталог книг.
Выполним небольшую подготовительную работу. Нужно создать бизнес процесс, который в самом простом виде позволял бы отправлять запросы, а также получать их результат.
На начальном этапе этого должно быть достаточно. Можно опубликовать приложение и перейти к тестированию. Для этого очень удобно использовать Swagger, который автоматически создается при публикации.
Используем простой запрос, который должен запросить все книги из базы. SELECT * FROM public.book
Обратите внимание, что сама таблица (как и все остальные таблицы, созданные в редакторе БД) имеет префикс с указанием схемы - public.
Можно убедиться в том, что запрос выполнен успешно, результат получен.
Но проблема в том, что очень трудно воспринимать ответ в таком виде. Возможное решение - немного доработать запрос и оставить в нем только необходимые поля. Например, название книги и количество страниц. Помимо этого разумным будет установить лимит, запрашивать не все книги из базы, а ограничиться десятью.
SELECT name, pages FROM public.book LIMIT 10
Уже гораздо лучше, но все еще не подходит для реального использования. Ведь в бизнес-процессе нужно не только получить запрос, но и что-то сделать с его результатом. А для этого недостаточно иметь результат в текстовом виде, нужно превратить его в модель, подходящую для дальнейшего использования.
Для этого вернемся в бизнес-процесс и немного доработаем его. В блоке end добавим новую переменную - массив моделей book.
А также понадобится блок, который выполнит преобразование JSON, полученного в результате запроса, в модель. Deserialize JSON To Model.
Теперь можно перейти к более сложной логике. Сделаем бизнес-процесс который:
- Получает на вход название книги.
- Определяет к какой категории (жанру) она относится.
- Выдает в качестве результат 3 случайных книги из той же категории (при этом книга из запроса не должна попасть в их число)
Для этого создадим новый бизнес-процесс, в котором совместим поиск с помощью стандартных блоков и использование запросов к SQL.
Первым блоком найдем книгу по ее названию, а также определим к каким категориям она относится. Для этого используем блок search со следующими параметрами:
- _With = Categories - помимо самой книги, в результате запроса нужна информация из связанной таблицы категорий.
- _Limit = 1 - требуется найти только одну книгу.
- _Ilike = False - название должно точно соответствовать запрашиваемому.
- Name - указатель названия книги, передается из блока Start.
Блоком Array Element с индексом 0 возьмем первую (и единственную) книгу из результата.
Книга может относиться к нескольким различным категориям одновременно и в таком случае нас устроит любая из них. Ее можно выбрать случайным образом с помощью блока Random element
После этого у нас есть все необходимые данные и остается лишь составить сам запрос, который может выглядеть подобным образом:
SELECT * FROM public.book WHERE id IN (SELECT rel1_id FROM public.book_categorys_category_books_pivot WHERE book_categorys_category_books_pivot.rel2_id = X) AND id <> Y ORDER BY random() LIMIT 3
Где X - это id категории книги, а Y - id самой книги.
Обратите внимание на то, что данный запрос включает в себя подзапрос. Сначала из таблицы book_categorys_category_books_pivot (она используется для хранения информации о связях между двумя таблицами) находятся все идентификаторы книг, соответствующие выбранной категории. После этого выполняется запрос, который находит случайные 3 книги, соответствующие указанному диапазону, за исключением id книги, чье название изначально было передано в бизнес-процесс.
Для более подробного изучения структуры базы данных проекта можно воспользоваться кнопкой Open DB в настройках Deploy plans.
Она позволит открыть в редактор БД и получить прямой доступ к просмотру и редактированию данных. Впрочем, стоит проявлять осторожность и учитывать тот факт, что изменение самой структуры данных в редакторе сделает невозможным дальнейшую публикацию проекта.
Вернемся к созданию бизнес-процесса. Необходимо завершить составление запроса, а для этого выполнить преобразование id книги и категории из integer в string, а также собрать окончательный запрос блоком Concat Strings (Multiple).
Последний шаг - выполнение запроса, преобразование результата в модель и отправка в блок End в качестве результата запроса.
Можно сохранить изменения, создать эндпойнт, опубликовать проект и убедиться, что запрос работает правильно. При этом один блок SQL Exec со сложным составным запросом был использован, чтобы заменить множество других блоков и упростить структуру бизнес-процесса.
Использование блок SQL Exec не ограничивается поиском данных и может быть задействовано в самых разнообразных сценариях. Тезисно рассмотрим еще несколько вариантов
- Подсчет количества комментариев у книги с id=X
SELECT COUNT(id) FROM public.comment WHERE book_id = X - Подсчет среднего рейтинга книги, с учетом всех выставленных оценок в комментариях:
SELECT AVG(rate) FROM public.comment WHERE book_id = X - Удаление всех комментариев, написанных до 2023 года (может использоваться, например, для быстрой очистки лога).
DELETE * FROM public.comment WHERE created_at < '2023-01-01'
В завершение стоит отметить, что для повышения безопасности в блоке SQL Exec добавлен фильтр на опасные операции, которые могут привести к изменению схемы. CREATE/ALTER/DROP/TRUNCATE для TABLE|COLUMN|INDEX|CONSTRAINT|SEQUENCE|SCHEMA|DATABASE отключены фильтром если приложение размещено на серверах AppMaster. При размещении on-premise - по умолчанию доступны любые запросы без ограничений.
