Реляционные базы данных в Clojure
Содержание
- Реляционные базы данных
Реляционные базы данных
В этой главе мы поговорим о том, как работать с реляционными базами данных в Clojure. Бóльшую часть описания займет библиотека clojure.java.jdbc и дополнения к ней. Вы узнаете, какие проблемы встречаются в этой области и как их решают в Clojure.
В бекенд-разработке базы данных занимают центральное место. Если говорить упрощенно, то любая программа сводится к обработке данных. Конечно, они поступают не только из баз, но и сети и файлов. Однако в целом за доступ к информации отвечают именно базы данных — специальные программы, сложные, но с богатыми возможностями.
Базы данных, или сокращенно БД, бывают разных видов. Они различаются в архитектуре, способе хранения информации, типом связи с клиентом. Некоторые базы работают только на клиенте, то есть в рамках одного компьютера. Другие хранят только текст и оставляют типы на усмотрение клиента. Встречаются базы, где данные хранятся в оперативной памяти и пропадают после выключения.
Мы не ставим цель охватить как можно больше баз и способов работы с ними. Наоборот, сфокусируем внимание на том, что встретит вас в реальном проекте. Скорей всего это будет классическая реляционная база данных вроде PostgreSQL или MySQL. О них мы и будем говорить.
Реляционные базы данных называют так из за модели реляционной алгебры. Это изящная математическая модель с набором операций, например выборкой, проекцией, декартовым произведением и другими. Из модели следуют строгие правила о том, как работает та или иная операция. Поэтому базы данных работают не спонтанно, а по четким алгебраическим правилам. Из этих правил следуют нормальные формы (первая, вторая и третья), доказать которые можно аналитически, а не на глаз.
Мы будем учить реляционную алгебру с самых азов. Обратитесь к статье в Википедии или книгам, где она описана без привязки к конкретной БД.
Перейдем к понятиям, более привычным программисту. Базы хранят содержимое в таблицах. Запись в таблице называется кортежем и состоит из отдельных полей. Поля могут быть разного типа. Состав полей и их порядок одинаков в рамках таблицы. Не может быть так, что в первой записи два поля, а во второй три. Если нужно указать, что в поле нет значения, в него пишут специальное пустое, чаще всего NULL.
У записи есть особое поле, которое называют первичным ключом, Primary key. Ключ однозначно указывает запись в таблице. Не может быть двух записей с одинаковым ключом. Чаще всего роль ключа играет число с автонумерацией, но иногда это строка, например электронная почта или артикул товара.
В редких случаях ключ может быть составным, то есть определяться парой полей, например тип платежной системы и номер платежа. В этом случае мы допускаем, что в таблице могут быть несколько платежей от Stripe с разными номерами или платежи от Stripe и AppStore с одинаковым номером, но не то и другое вместе.
gateway | trx_no |
-----------+---------
stripe | 1 | ;; ok
stripe | 2 | ;; ok
appstore | 2 | ;; ok
appstore | 2 | ;; error
Внешним ключом называется поле, которые ссылается на первичный ключ другой таблицы. Для краткости его называют ссылкой (ref). Примером может быть поле user_id таблицы профилей, которое указывает на поле id таблицы пользователей.
CREATE TABLE users(
id serial primary key,
name text not null
);
CREATE TABLE profiles(
id serial primary key,
user_id integer not null references users(id),
avatar text
);
Ссылка отличается от числа особыми свойствами: она гарантирует, что указанный пользователь действительно существует. Обычное число этой гарантии не дает. Говорят, что ссылки поддерживают целостность базы. Целостность означает, в базе нет ссылок на несуществующие записи.
Кроме целостности, внешний ключ поддерживает реакцию на удаление. По умолчанию нельзя удалить сущность, на которую кто-то ссылается. Однако можно задать правило, что связанная сущность тоже удаляется. Так, при удалении пользователя удалится и его профиль, ведь он не нужен в отрыве от пользователя. В другом случае ссылка на удаленную сущность станет NULL, если это разрешено в свойствах поля.
Из университета мы знаем, что связи бывают разных типов: один к одному или многим, многие ко многим. Профиль, который ссылается на пользователя — это связь один к одному. Несколько заказов у пользователя — один ко многим. Тип связи легко задать ограничением на поле ссылки. Если в таблице профилей сделать поле user_id уникальным, не получится создать два профиля одному пользователю. Связь “многие ко многим” строят через таблицу-мост со ссылками на другие таблицы. Чтобы сущности нельзя было соединить несколько раз, применяют составной первичный ключ из ссылок.
Запросы
База данных обращается с миром через SQL (Structured Query Language) — структурированный язык запросов. Это текст, который описывает наши намерения — прочитать таблицу, добавить запись, обновить поле. Запросы имеют четкую структуру, которая чаще всего зависит от главного оператора. К ним относятся SELECT, INSERT, UPDATE, DELETE и другие команды.
Существуют несколько стандартов SQL, обозначенных годами, когда они были приняты: SQL’92, ‘99, ‘2003 и другие. Каждая база данных поддерживает стандарт определенного года включая предыдущие. Кроме стандарта базы предлагают расширения — возможности, которые не входят в него. При чтении документации обращайте внимание на но, относится ли конкретная возможность к стандарту или же это частное решение.
В аббревиатуре SQL последняя буква означает language, язык. Однако это не язык программирования, потому что на нем нельзя выразить алгоритм задачи. Говоря точнее, SQL не полон по Тьюрингу: вы не сможете построить SQL-выражение на нем самом. Поэтому сложные запросы строят при помощи полноценных (полных по Тьюрингу) языков, например Java, Python, Clojure.
Некоторые базы данных предлагают встроенные языки и операторы, чтобы это исправить. Так, в PostgreSQL доступен блок DO, где работают переменные, циклы и даже перехват исключений. Но это частное решение, которое не входит в стандарт.
Доступ из Clojure
Теперь когда мы освежили теорию, перейдем к практике — Clojure. По умолчанию язык не предлагает доступа к базам данных. Чтобы работать с ними, подключают библиотеку clojure.java.jdbc — тонкую обертку над JDBC. Так называется встроенный в Java пакет для реляционных баз данных.
JDBC (Java Database Connectivity) отсчитывает свой возраст с 1997 года. Главная задача JDBC — предоставить общий API для разных баз. Для этого JDBC устроен из нескольких слоев. Потребители используют общий API, который, в зависимости от типа базы, вызывает разные драйверы.
Драйверы служат для связи JDBC с бекендом. Каждый из них реализует бинарный протокол, по которому работает база. На сегодняшний день JDBC поддерживает все известные реляционные СУБД: PostgreSQL, MySQL, Oracle, SQLite и другие. Существуют драйверы для файловых хранилищ, например Excel, DBF, CSV.
Для работы с какой-либо базой вам понадобится драйвер к ней. Драйверы поставляются отдельно и должны быть объявлены в зависимостях проекта. JDBC автоматически находит и подключает драйвер; программисту не нужно заботиться об этом.
Перечислим основные сущности JDBC:
-
DriverManager— класс для управления драйвером конкретной БД. Получает соединение с базой, по которому в дальнейшем идет обмен данными. -
PreparedStatement— подготовленное выражение. Так называется запрос, который прошел стадию подготовки в базе и теперь может быть вызван с разными параметрами. -
ResultSet— источник, из которого читают результат запроса. Обычно приходит из методаexecuteQueryподготовленного выражения.
Следующий код на Java показывает, как подключиться к базе, выбрать записи из таблицы и вывести на экран. Задача сводится к созданию указанных классов и вызову их методов:
import java.sql.*;
public class JDBCExample {
static final String DB_URL = "jdbc:postgresql://127.0.0.1/test";
static final String USER = "book";
static final String PASS = "book";
static final String QUERY = "SELECT * FROM users";
public static void main(String[] args) {
try {
Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(QUERY);
while (rs.next()) {
System.out.println("ID: " + rs.getInt("id"));
System.out.println("First name: " + rs.getString("fname"));
System.out.println("Last name: " + rs.getString("lname"));
System.out.println("Email: " + rs.getString("email"));
System.out.println("Age: " + rs.getInt("age"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
В идеале при смене базы код остается прежним, а меняется только синтаксис SQL. И хотя это верно лишь отчасти, JDBC решает основную задачу — предлагает единый доступ к разным бекендам.
Похожий стандарт существует и в других языках, например в Python. Там он называется DB API. Его задача — обозначить минимальный набор правил, которым должен следовать драйвер. С этим требованием легче писать общий код для разных баз.
Знакомство с clojure.java.jdbc
Из примера на Java видно, что даже простой запрос требует многих строк кода. В Clojure принято упрощать рутину, и библиотека clojure.java.jdbc создана именно для этого. Она создает тонкую обертку вокруг классов DriverManager, PreparedStatement и других, чтобы получить результат буквально парой строк. Добавьте библиотеку и драйвер PostgreSQL в проект:
;; project.clj
[org.clojure/java.jdbc "0.7.8"]
[org.postgresql/postgresql "42.1.3"]
Предположим, сервер PostgreSQL развернут локально. Вот как выполнить в нему запрос:
(def db {:dbtype "postgresql"
:dbname "test"
:host "127.0.0.1"
:user "book"
:password "book"})
(jdbc/query db "select 1 as value")
({:value 1})
Мы объявили параметры подключения и передали в функцию jdbc/query. Запрос возвращает постоянное число, поэтому он сработает, даже если в базе нет таблиц.
Пока мы не ушли вперед, разберем, что произошло. Переменная db называется JDBC-спекой. Это словарь с различными полями: хостом, портом, пользователем и другими. Из него библиотека библиотека получает соединение с базой. По завершении запроса соединение закрывается. JDBC-спека принимает разные формы, и позже мы рассмотрим их.
Зависимость [org.postgresql/postgresql "42.1.3"] нужна для работы с PostgreSQL. Ее драйвер находится в репозитории Maven. Чтобы не распылять внимание на разные базы, остановим выбор на PostgreSQL до конца главы. В пользу PostgreSQL говорит зрелость и мощь проекта, его открытый код и бесплатный доступ. В России действует официальный вендор PostgresPro. Их силами созданы полезные расширения, переведена документация на русский язык.
Тот факт, что мы используем именно PostgreSQL, JDBC поймет из поля dbtype со значением “postgresql”. Он автоматически загрузит драйвер, так что импортировать его в Clojure не нужно.
Чтобы запрос выполнился, должен работать локальный сервер PostgreSQL. Наиболее быстрый способ запустить его с нужными настройками — вызывать одноименный Docker-образ. Следующая команда:
> docker run --rm -it -e POSTGRES_PASSWORD=pass postgres
запустит сервер на локальном порту 5432. Имя базы, пользователь и пароль определяются переменными окружения; в примере выше пароль к базе будет “pass”. Запуск этого образа мы обсуждали в прошлой книге о Clojure, а именно в четвертой и седьмой главах. В случае трудностей изучите примеры оттуда.
Если все настроено правильно и вы увидели результат jdbc/query, примите поздравления. Первый этап пройден, и вы готовы двигаться дальше.
Основы clojure.java.jdbc
Библиотека предлагает несколько функций для работы с базой. Рассмотрим наиболее важные из них.
Чтение
Чаще всего в коде используют query – функцию, которая выполняет запрос и возвращает результат. Она принимает jdbc-спеку и запрос. Последний может быть либо строкой:
(jdbc/query db "select * from users")
, либо вектором, где первый элемент — запрос с подстановками, а остальные элементы — параметры. Подобную запись называют SQL-вектором.
(jdbc/query db ["select * from users where id = ?" 1])
Знаки вопроса означают подстановку; позже параметры становятся на их место. Ниже мы узнаем, в чем разница между значением в запросе и параметром. Кроме запроса, query принимает параметр, который указывает, как обработать результат. Эти тонкости мы тоже обсудим позже.
Функция get-by-id предлагает быстрый доступ к записи по первичному ключу. Она ожидает имя таблицы и значение ключа. Например, если в таблице users первичный ключ называется id, то чтобы получить первого пользователя, выполним:
(jdbc/get-by-id db :users 1)
{:id 1, :fname "John", :lname "Smith", :email "test@test.com", :age 25}
Функция вернет либо одну запись, либо nil. Это важное отличие от query, результат которой всегда коллекция, в том числе для одной записи.
По умолчанию get-by-id считает, что первичный ключ называется id. Если это не так, укажите его имя после значения:
(jdbc/get-by-id db :users 1 "account_id")
Функция find-by-keys выполняет отбор по нескольким полям. Предположим, мы хотим выбрать пользователей с именем John и возрастом 25 лет. Для этого укажем в find-by-keys имя таблицы и словарь вида {поле => значение}:
(jdbc/find-by-keys db :users {:fname "John" :age 25})
База выполнит запрос:
SELECT * FROM users WHERE fname = $1 AND age = $2
parameters: $1 = 'John', $2 = '25'
Функция принимает любое число полей в словаре. Они соединяются оператором AND, потому что это наиболее частый случай. Для более тонкого отбора, например с помощью OR, функция не подойдет. Что делать в этом случае мы рассмотрим позже.
Заметим, что find-by-keys вернет список, даже если нашлась только одна запись. В некоторых случаях мы намеренно ищем только первую. Заведем функцию find-first с теми же аргументами, которая оборачивает результат в first.
(defn find-first [db table filters]
(first (jdbc/find-by-keys db table filters)))
В этом случае результат будет либо первой записью, либо nil:
(find-first db :users {:fname "John" :age 25})
{:id 1, :fname "John", :lname "Smith", :email "test@test.com", :age 25}
Это не оптимальное решение, потому что запрос к базе не содержит оператора limit 1. Без него база вернет все записи, лишние из которых отбрасываются на стороне Clojure. Доработайте find-first так, чтобы база возвращала одну запись силами SQL.
Вставка
Функция insert! добавляет запись в таблицу. Восклицательный знак на конце означает, что вызов меняет состояние. Insert! принимает таблицу, в которую происходит запись. Значения могут быть переданы в разном виде. Наиболее частый сценарий — словарь вида {поле => значение}, например:
(jdbc/insert! db :users {:fname "Ivan"
:lname "Petrov"
:email "ivan@test.com"
:age 87})
Во втором варианте функция принимает списки полей и значений по отдельности. Их длины должны быть равны:
(jdbc/insert! db :users
[:fname :lname :email :age]
["Andy" "Stone" "andy@test.com" 33])
Оба вызова сводятся к подобному запросу:
INSERT INTO users ( fname, lname, email, age ) VALUES ( $1, $2, $3, $4 )
parameters: $1 = 'Andy', $2 = 'Stone', $3 = 'andy@test.com', $4 = '33'
Результат insert! отличается от типа базы. Большинство отвечает на INSERT списком первичных ключей, которые появились в результате вставки. Имена ключей тоже отличаются. MySQL вернет набор записей с полем generated_key. PostgreSQL вернет запись целиком из-за оператора RETURNING *. Драйвер JDBC добавит его в конец запроса автоматически.
Хотя insert! работает только с одной записью, он не оборачивает результат в first. В итоге вы получите список с одним словарем. Если новый ключ нужен для дальнейших действий, добраться до него можно так:
(let [get-pk
(some-fn :id :generated_key)
fields
{:fname "Ivan"
:lname "Petrov"
:email "ivan@test.com"
:age 87}
db-result
(jdbc/insert! db :users fields)]
(-> db-result first get-pk))
Обновление
Функция update! обновляет таблицу. Она принимает JDBC-спеку, имя таблицы, словарь полей и WHERE-часть. Рассмотрим пример:
(jdbc/update! db :users {:age 50} ["id = ?" 7])
Словарь {:age 50} означает новые значения полей. Их может быть больше одного, например, вместе с возрастом мы решили обновить имя и почту. Последний параметр функции — SQL-вектор, который будет добавлен к запросу после оператора WHERE. В примере выше update! порождает следующий запрос:
UPDATE users SET age = $1 WHERE id = $2
parameters: $1 = '50', $2 = '7'
В векторе может быть несколько условий, соединенных с помощью AND или OR:
(jdbc/update! db :users {:email "test@test.com"} ["id = ? OR id = ?" 1 3])
Что равнозначно:
UPDATE users SET email = $1 WHERE id = $2 OR id = $3
parameters: $1 = 'test@test.com', $2 = '1', $3 = '3'
SQL-вектор для WHERE обязательный: функцию нельзя вызвать без условия. Возникает вопрос, как выполнить UPDATE для всей таблицы? Редко, но случается, что мы хотим обновить все записи. Для этого передают вектор ["true"], что означает истину для каждой записи.
Предположим, в таблице пользователей создали колонку с признаком активности:
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT false;
Чтобы выставить истину всем пользователям, выполним:
(jdbc/update! db :users {:is_active true} ["true"])
Что равносильно:
UPDATE users SET is_active = $1 WHERE true
parameters: $1 = 't'
Функция update! возвращает список с одним числом. Оно означает число записей, которые были обновлены. Полезно добавить это число в лог:
(let [[num-updated]
(jdbc/update! db :users {:is_active true} ["true"])]
(log/infof "%s records updated" num-updated))
;; 2021-07-05 09:47:19,581 INFO my.module - 11 records updated
Удаление
Функция delete! удаляет записи из таблицы. Ее сигнатура похожа на update!: параметры подключения, имя таблицы и WHERE-часть. Вот как удалить пользователей старше 50 лет:
(jdbc/delete! db :users ["age >= ?" 50])
Итоговый запрос:
DELETE FROM users WHERE age >= $1
parameters: $1 = '50'
По аналогии с update!, в WHERE могут быть сложные условия с AND или OR. Результат функции — число удаленных записей.
Выполнение
Еще одна важная функция называется execute!. Она выполняет любой запрос, отличный от SELECT. Особенность SELECT в том, что он возвращает результат, который нужно прочитать и обработать. Функция execute!, напротив, выполняет команду без чтения результата.
На роль команды без результата подходит создание индекса. Добавим индекс в таблицу пользователей по имени:
(jdbc/execute! db "create index users_fname on users(fname);")
Затем удалим его:
(jdbc/execute! db "drop index users_fname;")
Другой пример — очистить таблицы после прогона тестов. Специальная команда TRUNCATE делает это эффективней DELETE. Она учитывает несколько таблиц за раз и каскадную очистку: все таблицы, что ссылаются на перечисленные, тоже очищаются.
(jdbc/execute! db "truncate users cascade;")
Еще один сценарий для execute! — временно отключить триггеры на время тестов или миграций. Предположим, мы завели триггер на обновление пользователей. Чтобы не вызывать его в тестах, напишем фикстуру :once, которая отключит триггер перед прогоном:
(jdbc/execute! db "ALTER TABLE users DISABLE TRIGGER on_users_update")
, а затем включит такой же командой с параметром ENABLE.
Практика
Выше мы разобрали базовые CRUD-операции (Create, Read, Update, Delete) и служебную функцию execute!. Вместе они покрывают бóльшую часть задач, которые ждут вас производстве. Перед тем как идти вперед, отложите книгу и позанимайтесь, чтобы закрепить знания.
Создайте проект и подключите к нему библиотеку clojure.java.jdbc. Настройте локальную базу данных (в Docker или нативно) и подключитесь к ней. Создайте простую таблицу и опробуйте изученные действия: вставку, выборку, поиск, обновление, удаление. Добавьте индекс на некоторые поля. Проделайте все из Clojure, не прибегая к утилитам вроде pgAdmin, DataGrip и другим.
Подробнее о запросах
Выше мы упоминали, что функция query принимает либо строку запроса, либо вектор с параметрами. Разберемся, в чем разница между подходами.
Представим, что нужно найти пользователя по известному id, например 13. Приведем варианты со строкой и вектором. В первом выражении значение 13 “вшито” в запрос, а во втором выступает параметром.
(jdbc/query db "select * from users where id = 13")
(jdbc/query db ["select * from users where id = ?" 13])
Строгое правило: если запрос зависит от внешних условий, используйте второй вариант с параметрами. Подставлять значение в строку функциями format или str считается грубой ошибкой и влечет риски. На это есть две причины: SQL-инъекции и подготовленные выражения. Обе важны и нуждаются в объяснении.
SQL-инъекции
Инъекцией называют случай, когда в запрос попадает вредоносный текст. Это возможно при ручной подстановке параметров в шаблон. Пик инъекций пришелся на середину нулевых годов в эпоху расцвета PHP. Новый язык резко сократил порог входа в веб-разработку, и ей занялись в том числе те, кто не имел представлений о безопасности. В PHP типичный запрос выглядит так (пример взят из интернета):
$user_id = 13;
$query = sprintf("SELECT * FROM users WHERE id = %s", $user_id);
$result = mysql_query($query);
Разберемся, в чем проблема с выражением выше. Для начала перепишем его на Clojure:
(def user-id 13)
(jdbc/query db (format "select * from users where id = %s" user-id))
До тех пор, пока в user-id находится число, запрос работает без ошибок. Но что если параметр приходит из адресной строки браузера? Предположим, кто-то исправил URL следующим образом:
http://site.com/users?user-id=13+OR+TRUE
Хакер понимает, что перед 13 идет выражение WHERE id = .... Идея в том, чтобы через оператор OR добавить условие, которое истинно всегда, например TRUE или 1=1. В результате даже если первое выражение ложно, второе будет истиной. Оператор OR сделает выбор в пользу второго, и запись попадет в выборку. В результате база вернет все записи таблицы без фильтра по id. Так вы раскроете чужие данные.
Изменим user-id на вредоносную строку и выполним запрос с форматированием. Получим все записи таблицы users.
(def user-id "13 OR TRUE")
(jdbc/query db
(format "select * from users where id = %s" user-id))
;; ({:id 1, ...}, {:id 2, ...}, ...)
В интернете популярен комикс xkcd об SQL-инъекциях. Учитель спрашивает родителя, почему они назвали сына Robert'; DROP TABLE students;. Легко догадаться, что на сервере был следующий код:
(let [fname "Robert"
query (str "select * from users where fname = '" fname "'")]
(jdbc/query db query))
;; select * from users where fname = 'Robert';
Если задать переменной fname то, что вписал в поле Роберт:
(let [fname "Robert'; DROP TABLE users;--"]
...)
, запрос примет вид:
select * from users where fname = 'Robert'; DROP TABLE users;--'
Обратите внимание на двойной минус в конце. Он нужен для того, чтобы закомментировать последнюю одинарную кавычку. База данных отбросит ее и получит два запроса через точку с запятой. Если выполнить это выражение, сработает поиск по имени Robert, а затем из таблицы users удалятся все записи. Опробуйте трюк на локальной базе.
Атаки с UNION
Главная задача хакера не в том, чтобы удалить данные — их легко восстановить из резервной копии. Его цель — заполучить данные, к которым нет доступа. Рассмотрим тот же пример с поиском пользователя. На этот раз вместо DROP TABLE мы напишем другой вредоносный SQL, а именно объединение запроса с таким же, но без отбора. Для этого служит оператор UNION:
(let [fname "Robert' UNION select * from users--"
query (str "select * from users where fname = '" fname "'")]
(jdbc/query db query))
Результат:
select * from users where fname = 'Robert' UNION select * from users--'
Итоговый запрос вернет всех пользователей независимо от того, сколько их нашлось в первой части. Конечно, для подобных атак нужно знать структуру таблицы. Однако и эти данные можно получить SQL-инъекцией, добавив UNION с таблицами information_schema.tables, pg_user и другими. Таблицы из схемы information_schema хранят метаданные о других таблицах: их имена, состав и типы колонок. Если хакер доберется до этих сведений через инъекции, он узнает о базе все.
Кавычки
Даже если переменная приходит от надежного источника, вы не застрахованы от проблем с кавычками. Например, библиотекарь добавляет книгу “Д’Артаньян и три мушкетёра”. На клавиатуре нет знака апострофа, поэтому после буквы Д сотрудник поставит верхнюю одинарную кавычку. В большинстве баз данных она служит для строковых литералов. При форматировании такой строки вы получите ошибку синтаксиса:
(def book "Д'Артаньян и три мушкетёра")
(def sql (format "insert into books (title) values ('%s')" book))
;; insert into books (title) values ('Д'Артаньян и три мушкетёра')
С точки зрения SQL строка заканчивается после Д, и дальше идет что-то ошибочное.
Подстановка параметров сводит на нет эти угрозы. Прежде всего JDBC следит за их типом. Если мы ожидаем поиск по числовому ID, то при передаче строки получим ошибку даже если строка содержит цифру. При этом ошибка возникнет до того, как запрос уйдет в базу:
(jdbc/query db ["select * from users where id = ?" "1"])
Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2476).
ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 30
При подстановке строки драйвер экранирует опасные символы. В PostgreSQL для этого служит одинарная кавычка. Вот как безопасно выбрать из базы всех Д’Артаньянов (обратите внимание на двойную кавычку в параметре $1):
(jdbc/query db ["select * from users where fname = ?" "Д'Артаньян"])
;; select * from users where fname = $1
;; parameters: $1 = 'Д''Артаньян'
Важно, что параметры доступны не в любом месте запроса. Например, нельзя задать параметром имя таблицы или состав полей. Поэтому запрос:
(jdbc/query db ["select * from ?" "users"])
не сработает из-за ошибки синтаксиса. А если вы хотите задать имена полей параметрами:
(jdbc/query db ["select ?, ?, ? from users" "id" "fname" "email"])
, то вас ждет разочарование. Запрос вернет строки “id”, “fname” и “email”, а не значения одноименных полей. Чтобы строить запросы, где таблицы и поля зависят от условий, прибегают к специальным библиотекам. Позже мы рассмотрим наиболее популярные из них.
Подготовленные выражения
Вторая причина в пользу параметров в том, с ними база получит выигрыш в скорости. Следует помнить, что даже простой запрос выполняется поэтапно. Процесс напоминает компиляцию: выражение разбирают на лексемы и строят дерево. Например, строка 3 + 2 * 2 после разбора станет списком (+ 3 (* 2 2)), который проще выполнить благодаря структуре.
База данных тоже выполняет разбор и анализ запроса. На выходе получается так называемое Prepared Statement, подготовленное выражение. Это объектное представление запроса, который прошел анализ и теперь может быть выполнен. Предположим, поступил запрос на поиск пользователя по id:
select * from users where id = 1
Схематично представим его в виде дерева на Clojure:
{:type :select
:tables [:users]
:fields [:id :fname :lname :email :age]
:where [[:= :id ?]]
:params [:integer]}
Смысловые части запроса разложены по полям. Обратите внимание, что вместо звёздочки следует явный список полей. Известно число параметров и их тип. Осталось вызвать подготовленное выражение с конкретным ID, чтобы получить результат.
Из структуры выше понятно, почему таблица не может быть параметром. Если бы она менялась динамически, пришлось бы каждый раз вычислять набор полей и их тип, что рушит идею Prepared Statement — сделать это единожды. Наоборот, подстановка чисел 5, 15 или 99 на место id не влечет перестройку выражения.
Покажем, как работать с подготовленными выражениями в Clojure. Чтобы создать его, вызывают функцию jdbc/prepare-statement. Первым параметром она ожидает открытое TCP-соединение с базой. Соединение получают функцией jdbc/get-connection из JDBC-спеки:
(def conn
(jdbc/get-connection db))
(def prep-stmt
(jdbc/prepare-statement conn "select * from users where id = ?"))
Второй аргумент jdbc/prepare-statement — произвольный запрос со знаками вопроса на месте параметров. В переменной prep-stmt окажется объект с типом PreparedStatement. Чтобы выполнить его, передайте в jdbc/query вектор, где первый элемент — подготовленное выражение:
(jdbc/query db [prep-stmt 1])
По аналогии напишем выражение для вставки пользователя и выполним его через execute!:
(def stmt-insert
(jdbc/prepare-statement conn "insert into users (fname, lname, age, email) values (?, ?, ?, ?)"))
(jdbc/execute! db [stmt-insert "John" "Smith" 20 "john@test.com"])
;; (1)
Подготовленные выражения несут состояние, поэтому их нельзя объявлять на верхнем уровне модуля. Выражения адаптируют под систему, на которой основан проект. Если это Component, объявляют компонент, который зависит от базы. При запуске компонент создает выражения и записывает в слоты, а в момент остановки закрывает их.
Напишем подобный компонент. Для экономии места заполним только одно выражение для поиска пользователя по номеру:
(defrecord UserManager
[db
conn
stmt-get-by-id]
component/Lifecycle
(start [this]
(let [conn (jdbc/get-connection db)
stmt-get-by-id
(jdbc/prepare-statement conn "select * from users where id = ?")]
(assoc this
:conn conn
:stmt-get-by-id stmt-get-by-id)))
(stop [this]
(.close conn)
(.close stmt-get-by-id)))
Добавим протокол для действий с пользователями. Пока что в нем один метод поиска по ID:
(defprotocol IUserManager
(get-by-id [this id]))
Расширим тип UserManager этим протоколом. В теле get-by-id вызываем функцию jdbc/query, при этом соединение и подготовленное выражение приходят из слотов записи.
IUserManager
(get-by-id [this id]
(first (jdbc/query conn [stmt-get-by-id id])))
Компонент в действии:
(def user-mgr
(-> {:db db}
map->UserManager
component/start))
(get-by-id user-mgr 1)
(get-by-id user-mgr 2)
Запросы, выполненные через подготовленные выражения, работают быстрее обычных, потому что не требуют разбора. Для нагруженных проектов это может оказаться критично.
Результат запроса
Рассмотрим, что предлагает библиотека для работы с ответом. По умолчанию функция query возвращает последовательность словарей. Структура каждого словаря одинакова: его ключи — поля выборки, приведенные к типу Keyword, а значения — то, что пришло для этого поля из базы. Пример с пользователями:
(jdbc/query db "select * from users")
({:id 1
:fname "Ivan"
:lname "Petrov"
:email "test@test.com"
:age 42}
{:id 2
:fname "John"
:lname "Smith"
:email "john@test.com"
:age 20})
Функция jdbc/query оборачивает результат ResultSet в функцию jdbc/result-set-seq. Последняя приводит его к ленивой коллекции “кложурных” значений. Поскольку список словарей — наиболее удобная структура, именно этот способ задан по умолчанию. В особых случаях поведение query меняют так, чтобы получить данные в другом виде. Но прежде чем мы узнаем, как это сделать, вспомним, как устроен объект ResultSet.
Этот класс напоминает источник данных со внутренним указателем. По умолчанию указатель находится до первого элемента. У ResultSet несколько методов, чтобы сдвигать его, но самый простой называется .next(). Результат метода означает, удался ли сдвиг или нет (получим ложь, если достигли конца). Обход ResultSet сводится к шаблону:
(while (.next rs)
(println (.getInt rs "id"))
(println (.getString rs "fname")))
Методы getInt, getString и другие вернут значения поля по его имени (если вызваны со строкой) или индексу (с числом).
Так как указатель стоит до первого элемента, начальный вызов .next сдвигает его на первый. Это существенно упрощает код. Если бы указатель сразу стоял на первом элементе, пришлось бы обработать его до входа в цикл, а потом скопировать тот же код внутрь цикла, что выглядит неуклюже:
(println (.getInt rs "id"))
(println (.getString rs "fname"))
(while (.next rs)
(println (.getInt rs "id"))
(println (.getString rs "fname")))
Методы getInt и getString выше говорят о том, что мы знаем структуру ответа заранее. Это не всегда так. Данные о полях можно получить из объекта ResultSetMetaData. Он возвращает состав полей, их имена и типы. С его помощью задают функцию, которая принимает очередной элемент ResultSet и переводит его в словарь.
Модуль clojure.core предлагает функцию resultset-seq, чтобы привести ResultSet к ленивой цепочке словарей. В свободное время изучите ее код: он занимает всего 13 строк.
По умолчанию query прогоняет ленивую выборку через doall. В результате все элементы ResultSet будут прочитаны. В зависимости от логики чтением можно управлять, например делать это отложено. При таком подходе требуется ручное закрытие объекта ResultSet. О ленивости и ее влиянии на результат мы скажем чуть позже.
Теперь когда мы знакомы с ResultSet, рассмотрим, в каком еще виде можно читать данные из базы.
Массивы
Список словарей удобен, но иногда требуется список векторов, например:
[[1 "Ivan" "Petrov" "test@test.com"]
[2 "Ivan" "Petrov" "ivan@test.com"]
[3 "John" "Smith" "john@test.com"]]
Такая структура полезна для CSV-файлов. Их формат известен заранее, поэтому заголовок указывают однажды в начале файла. Библиотеки для записи CSV ожидают список именно векторов, а не словарей. Если мы выбрали из базы словари, нужно привести их к вектору, что означает лишнюю работу. Будет лучше, если функция query вернет данные в готовом виде.
Параметр {:as-arrays? true} в query означает, что элементы списка будут векторами. Первый ряд содержит имена колонок:
(jdbc/query db "select * from users" {:as-arrays? true})
[[:id :fname :lname :email :age]
[1 "Ivan" "Petrov" "test@test.com" 42]
[2 "Ivan" "Petrov" "ivan@test.com" 87]
[3 "John" "Smith" "john@test.com" 20]]
Предположим, мы хотим выгрузить пользователей в файл CSV. Для этого используем библиотеку clojure.data.csv и стандартный модуль IO:
;; [clojure.data.csv :as csv]
;; [clojure.java.io :as io]
(with-open [writer (io/writer "users.csv")]
(->> (jdbc/query db "select * from users" {:as-arrays? true})
(csv/write-csv writer)))
В папке проекта вы найдете файл users.csv. Проверьте содержимое в табличном редакторе. Напишите универсальную функцию для выгрузки любой таблицы. Она принимает имя таблицы, путь к CSV-файлу и, по желанию, набор полей на тот случай, если нужны только некоторые из них.
Список векторов полезен не только для CSV, но и для матричных данных или работы со статистикой.
Ключи
По умолчанию JDBC приводит имена полей к типу Keyword одноименной функцией. Если в выборке было поле user_name, в словаре окажется ключ :user_name. Это именно то, что нужно в большинстве случаев, поскольку Clojure поощряет кейворды. Но и здесь порой нужна тонкая настройка.
Параметр {:keywordize? false} означает, нужно ли приводить поля к кейвордам. По умолчанию он истина, но если передать ложь, получим строки:
(jdbc/query db "select * from users" {:keywordize? false})
({"id" 1
"fname" "Ivan"
"lname" "Petrov"
"email" "test@test.com"
"age" 42}
{"id" 3
"fname" "John"
"lname" "Smith"
"email" "john@test.com"
"age" 20})
Параметр удобно использовать в паре с {:as-arrays? true}. С ним имена таблиц станут строками, и в CSV-файле у заголовков не будет двоеточия.
В поле :identifiers передают функцию обработки имени поля. По умолчанию используется clojure.string/lower-case, то есть приведение к нижнему регистру. С ним поле "VAL" становится :val:
(jdbc/query db "select 1 as VAL")
;; ({:val 1})
Вы можете передать функцию, которая оставляет регистр прежним или заменяет подчеркивание на дефис, чтобы получить :user-name из "user_name".
Параметр qualifier задает пространство полям выборки. Например, если мы выбираем пользователей, удобно назначить им пространство :user, чтобы позже не перепутать с другими сущностями.
(first (jdbc/query db "select * from users" {:qualifier "user"}))
{:user/id 2
:user/fname "Ivan"
:user/lname "Petrov"
:user/email "ivan@test.com"
:user/age 87}
Из того, что JDBC преобразует ключи к кейвордам, можно извлечь пользу. Когда в запросе участвует несколько таблиц с оператором JOIN, в результат попадают разные сущности. Чтобы одноименные поля не перемешивались, их наделяют префиксом, например, user_ для пользователя и profile_ для профиля:
SELECT
u.id AS user_id,
u.fname AS user_fname,
u.lname AS user_lname,
p.avatar AS profile_avatar
FROM users u
JOIN profiles p ON p.user_id = u.id;
Работать с такими ключами в Clojure неудобно. Вместо префикса и подчеркиваний мы ожидаем пространства и дефис. Если назначить полям псевдонимы вида “сущность/поле”, получим нужные ключи. Однако псевдоним с косой чертой нужно взять в двойные кавычки, чтобы не нарушить синтаксис SQL:
SELECT
u.id AS "user/id",
u.fname AS "user/fname",
u.lname AS "user/lname",
p.avatar AS "profile/avatar"
FROM users u
JOIN profiles p ON p.user_id = u.id;
Вывод psql:
user/id | user/fname | user/lname | profile/avatar
---------+------------+------------+----------------
1 | Ivan | Petrov | kitten.jpg
Если передать запрос в jdbc/query, получим удобные ключи:
({:user/id 1
:user/fname "Ivan"
:user/lname "Petrov"
:profile/avatar "kitten.jpg"})
Однако писать псевдонимы вручную для каждого поля долго. Кроме того, в Clojure приходится экранировать двойные кавычки, чтобы не вызвать ошибку синтаксиса. В результате запрос выглядит шумно и занимает много места:
(jdbc/query db "
select
u.id as \"user/id\",
u.fname as \"user/fname\",
...
")
Ниже мы узнаем, как исправить эти недостатки.
Ленивость
По умолчанию результат jdbc/query не ленив, то есть полностью вычислен (realized в терминах Clojure). Он уже не зависит от соединения с базой и объекта ResultSet. Если точнее, из ResultSet получают ленивую последовательность, которая позже вычисляется с помощью doall (для списка словарей) или vec (когда :as-arrays? истина).
Ключ :result-set-fn определяет, что именно будет предпринято с ленивой коллекцией до того, как она вычислена. По умолчанию это функция doall или vec в зависимости от параметров. Предположим теперь, мы хотим получить истинно ленивый результат без вычисления. Очевидно, в поле :result-set-fn просится функция identity. Однако с ней запрос бросит исключение:
(jdbc/query db "select * from users" {:result-set-fn identity})
idError printing return value (PSQLException) at org.postgresql.jdbc.PgResultSet/checkClosed (PgResultSet.java:2758).
This ResultSet is closed.
Оно возникло потому, что после выполнения запроса JDBC закрывает объект ResultSet. При печати коллекции в REPL мы читаем данные из ResultSet уже после его закрытия, что приводит к ошибке.
Функция :result-set-fn полезна в тех случаях, когда нужно обработать элементы по одному, не дожидаясь всего ответа. Часто это связано с операциями ввода-вывода. Представим, нужно записать выборку в файл. Будет неэффективно выгружать все данные в память и затем в файл — гораздо лучше читать записи по одной и сразу записывать. Даже если записей миллионы, мы не израсходуем память.
(jdbc/query db "select * from users"
{:as-arrays? true
:keywordize? false
:result-set-fn
(fn [rows]
(with-open [writer (io/writer "users.csv")]
(csv/write-csv writer rows)))})
В примере выше мы записываем ленивую коллекцию rows в файл, не дожидаясь чтения всего ответа. Подход оправдывает себя на больших выборках.
В редких случаях вам понадобится ручная работа с ResultSet. Доступ к нему предоставляет функция jdbc/db-query-with-resultset, которая принимает объект ResultSet. Делайте с ним что угодно, но помните: после выхода из функции он будет закрыт.
Как правило, работа с ResultSet сводится к итерации (while (.next ...)) и чтению полей. Этот метод эффективней result-set-fn, потому что не производит ленивую коллекцию, которая потребляет ресурсы.
(jdbc/db-query-with-resultset
db "select * from users"
(fn [^ResultSet rs]
(while (.next rs)
(let [id (.getInt rs "id")
fname (.getString rs "fname")
lname (.getString rs "lname")]
(println id fname lname)))))
Выше мы просто печатаем данные на экран. На практике мы бы отправили их в сеть, в файл или поток.
Транзакции
Чтобы изменения в базе были согласованы, нужны транзакции. Транзакция обещает, что в изменения не вмешается кто-то другой, и все они завершатся либо успехом, либо откатом к прежнему состоянию. Исключен третий нежелательный вариант, когда в базе остались частичные изменения.
Приведем типичные случаи с транзакциями. Это может быть сбор денег на проект (краудфандинг). Каждый раз, когда кто-то совершил платеж, итоговая сумма пересчитывается. Если делать это не в транзакции, можно пропустить платеж, который случился при пересчете, и итог будет неверным.
Даже если с базой работает один человек, транзакции необходимы. Представим органайзер фотографий. Каждая из них может быть в нескольких альбомах, иметь свои теги, ссылки на места и людей, запись в поисковом индексе. Теперь мы удаляем фотографию. Очистка должна произойти либо во всех таблицах, либо нигде. Представьте, что фотография не видна в альбоме, но появляется в поиске — это раздражает.
Технически транзакция означает, что два и более запроса окружены командами BEGIN и COMMIT. Приведем сеанс psql со вставкой данных в транзакции. В первый запрос мы добавили оператор RETURNING, чтобы получить созданный базой первичный ключ. Мы ссылаемся на него во втором запросе.
BEGIN;
INSERT INTO users (fname, email) VALUES ('Ivan', 'ivan@test.com') RETURNING id;
-- 59
INSERT INTO profiles (user_id, avatar) VALUES (59, 'me.jpg');
COMMIT;
Если хотя бы один запрос потерпит неудачу, COMMIT не сработает, и обе таблицы останутся нетронуты.
Рассмотрим, как управлять транзакциями в Clojure. Если выполнить два запроса в лоб:
(jdbc/insert! db :users {:fname "Ivan"})
(jdbc/insert! db :users {:fname "Huan"})
, увидим в логах PostgreSQL следующие записи:
BEGIN
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Ivan'
COMMIT
BEGIN
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Huan'
COMMIT
Каждый запрос оказался в отдельной транзакции, что вовсе не то, что мы ожидали. Так происходит потому, что функции на изменение (с восклицательным знаком на конце) принимают необязательный аргумент :transaction?. По умолчанию он истина, что значит выполнить запрос в отдельной транзакции. Если мы укажем ложь для этого флага:
(jdbc/insert! db :users {:fname "Ivan"} {:transaction? false})
(jdbc/insert! db :users {:fname "Huan"} {:transaction? false})
, транзакций не будет совсем:
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Ivan'
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Huan'
Другая причина, по которой у нас не выходит транзакция, в том, что запросы должны быть в рамках одного соединения. По умолчанию JDBC открывает новое соединение на запрос, что неэффективно и не дает управлять транзакциями. Мы должны открыть соединение, начать транзакцию и выполнить в нём все запросы. Код на Clojure должен быть обернут в try/catch, чтобы в случае исключения выполнить ROLLBACK. Если не было ошибок, выполнить COMMIT. В конце закрыть соединение.
Если каждый раз писать всё это вручную, обязательно допустим ошибку. Должна быть единая точка (макрос или функция), которая управляет процессом. Clojure предлагает макрос with-db-transaction. Он принимает JDBC-спеку, параметры транзакции и произвольный код. Коротко его логику можно описать так:
- получить TCP-соединение из JDBC-спеки.
- Запомнить его параметры
autocommit,readonlyи другие. - Выставить параметр
autocommit=false. - Связать соединение с символом из макроса.
- Выполнить тело макроса в блоке
letс этой переменной. Запросы должны использовать новое соединение. - Если не было исключений, вызвать
COMMIT. - В блоке
catchвызватьROLLBACKи повторно бросить исключение. - В блоке
finallyвернуть соединению начальные параметры.
Быстрая проверка макроса:
(jdbc/with-db-transaction [tx db]
(jdbc/insert! tx :users {:fname "Ivan"})
(jdbc/insert! tx :users {:fname "Huan"}))
На этот раз оба запроса окажутся в одной транзакции:
BEGIN
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Ivan'
INSERT INTO users ( fname ) VALUES ( $1 )
parameters: $1 = 'Huan'
COMMIT
Свойства транзакции определяет первый параметр макроса. Это вектор, от которого ожидают по крайней мере два символа по принципу let. Первый символ — имя, с которым будет связано соединение с транзакцией. Второй символ указывает на текущую JDBC-спеку. Первым символом обычно указывают tx или trx – сокращение от transaction.
Типичная ошибка в работе с макросом — установить транзакцию, но пользоваться прежним соединением. В этом случае пользы от макроса нет.
(jdbc/with-db-transaction [tx db]
(jdbc/insert! db :users {:fname "Ivan"})
(jdbc/insert! db :users {:fname "Huan"}))
Более сложный пример с записью в разные таблицы. Сперва создаем пользователя, затем профиль со ссылкой на него:
(jdbc/with-db-transaction [tx db]
(let [[{user-id :id}]
(jdbc/insert! tx :users {:fname "Ivan"})
[{profile-id :id}]
(jdbc/insert! tx :profiles {:user_id user-id :avatar "cat.jpg"})]
{:user/id user-id
:profile/id profile-id}))
Код вернет словарь с ключами новых сущностей. Для ясности мы добавили им пространства.
Третий элемент макроса принимает параметры транзакции. Он может отсутствовать, и тогда сработают параметры по умолчанию. Наиболее интересен ключ :isolation, уровень изоляции. Он определяет, как управлять данными в параллельных транзакциях, а именно на каком этапе каждой их них видны изменения другой.
Предположим, одновременно открыты две транзакции. Первая изменила запись, но еще не вызвала COMMIT. Вторая читает эту же запись. Получит ли она исходные данные или с изменениями? Что если первая транзакция вызвала COMMIT? Должна ли вторая увидеть эти изменения, ведь на момент BEGIN их не было?
Уровень изоляции управляет подобными случаями. Современные базы выделяют следующие уровни:
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
Они следуют по нарастанию безопасности в работе с параллельными транзакциями. С уровнем READ UNCOMMITTED транзакции B доступны незавершенные изменения, сделанные в A. Возможно, они подлежат откату, потому полагаться на эти данные будет ошибочно.
Уровень READ COMMITTED означает, что транзакция B получит изменения из А, только если они зафиксированы. READ COMMITTED считается достаточным для большинства случаев и установлен по умолчанию в SQL Server и PostgreSQL.
Уровень REPEATABLE READ обеспечивает так называемое повторное чтение. С ним транзакция A запоминает состояние базы на момент BEGIN. Даже если другие транзакции внесли изменения, чтение данных в A вернет их прежнюю версию. Если А меняет записи, обновленные другими транзакциями, произойдет конфликт, и придется выполнить ее повторно или отказаться от изменений.
Максимальный уровень SERIALIZABLE гарантирует безопасность с транзакциями. Когда он включен, база выполняет транзакции так, словно они следуют одна за другой, а не параллельно.
Сказанное не значит, что нужно выставлять наивысший уровень SERIALIZABLE. Хоть он и решает ряд проблем, но замедляет обработку данных. Меняйте уровень изоляции только если четко понимаете, чем вас не устраивает текущий.
В этой главе мы не рассматриваем изоляцию в деталях. Ее изучение требует примеров с таблицами и параллельными запросами. Эта тема не касается Clojure, потому что не зависит языка программирования. Для быстрого знакомства прочтите статью в Википедии. Еще глубже материал изложен в документации PostgresPro, в том числе на русском языке. Самое детальное описание вы найдете в книге Егора Рогова “PostgreSQL изнутри”. Автор горячо рекомендует эту книгу к прочтению.
Разберем, как задать уровень изоляции в Clojure. Макрос with-db-transaction принимает ключ :isolation. Его возможные значения — кейводы:
:none:read-committed:read-uncommitted:repeatable-read:serializable
Внутри макроса они транслируются в константы Java. Пример с уровнем SERIALIZABLE, заданным явно:
(jdbc/with-db-transaction
[tx db {:isolation :serializable}]
...)
Если выполнить код, в логах PostgreSQL увидим записи:
SHOW TRANSACTION ISOLATION LEVEL
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN
...
COMMIT
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
Макрос временно заменяет уровень изоляции на указанный. После фиксации транзакции или ее отката возвращается прежний уровень. Уровень по умолчанию можно задать в настройках базы данных. В этом случае не придется передавать ключ :isolation в макрос каждый раз.
JDBC-спека с состоянием
В словаре, который принимают функции JDBC, нет состояния, и не ясно, откуда оно приходит. Это особенность библиотеки для Clojure: с базой можно работать как с состоянием, так и без него. Но чтобы код был эффективным, вы должны понимать, что происходит на нижнем уровне JDBC.
Под состоянием спеки понимают TCP-соединение, по которому клиент и база передают данные. В следующем словаре никакого соединения нет:
(def db {:dbtype "postgresql"
:dbname "test"
...})
Если передать его в функцию query, произойдет следующее:
-
из словаря библиотека построит JDBC URL — длинную строку, похожую на адрес страницы в интернете. Для конфигурации выше результат будет примерно таким: “jdbc:postgresql://127.0.0.1:5432/test”.
-
По URL получим открытое TCP-соединение. Через него функция
queryотправит и получит данные. -
После выполнения
queryсоединение закроется.
Аналогично устроены функции update!, delete! и другие. Все они сводятся к единой точке, где создается соединение, происходит обмен данных, затем оно закрывается.
Открывать соединение на каждый запрос расточительно. Чтобы использовать его повторно, JDBC предлагает макрос with-db-connection. Макрос связывает символ с копией спеки, в одном из полей которой находится соединение.
(jdbc/with-db-connection [conn db]
(jdbc/query conn "select 1"))
Макрос добавляет соединение в спеку с ключом :connection. Функция, которая строит соединение по словарю, проверяет этот ключ. Если в нем уже есть соединение, функция вернет его же. Вместе это работает как кэш. Исследуем переменную conn и убедимся, что в ней заполнено поле :connection:
(jdbc/with-db-connection [conn db]
(print conn))
{:dbtype "postgresql"
:dbname "test"
...
:connection #object[org.postgresql.jdbc.PgConnection 0x46b37678 "org.postgresql.jdbc.PgConnection@46b37678"]}
Чтобы оценить издержки на соединение с базой, напишем замер. Выполним примитивный запрос многократно, в первом случае — каждый раз с новым соединением, во втором — с одним и тем же.
(time (dotimes [_ 1000]
(jdbc/query db "select 1")))
;; "Elapsed time: 19097.466607 msecs"
(time
(jdbc/with-db-connection [conn db]
(dotimes [_ 1000]
(jdbc/query conn "select 1"))))
;; "Elapsed time: 1680.252291 msecs"
Разница в одиннадцать раз в пользу постоянного соединения. Очевидно, что первый вариант — соединение на запрос — подойдет только разовым скриптам. Для долгоиграющих систем нужно постоянное соединение. Это подводит нас к JDBC-спеке с состоянием.
Чтобы управлять спекой с минимальными усилиями, обратимся к библиотеке Mount. Мы познакомились с ней в первой книге в главе про системы. Объявим компонент, который при запуске открывает TCP-соединение и дописывает его в поле :connection спеки:
(require '[mount.core :as mount :refer [defstate]])
(defstate DB
:start
(assoc db :connection
(jdbc/get-connection db))
В фазе остановки закрываем соединение и возвращаем исходную спеку без состояния:
:stop
(let [{:keys [connection]} DB]
(when connection
(.close connection))
db))
Вызовем (mount/start), чтобы включить компонент. Теперь каждый запрос к базе использует постоянное соединение, что быстрее:
(jdbc/query DB "select 1")
Пул соединений
У открытого соединения есть проблема: его легко потерять. Проведем эксперимент: когда компонент базы включен, перезапустите сервер базы данных. Компонент придет в негодность: при попытке выполнить запрос получим ошибку о том, что соединение закрыто.
Execution error (PSQLException) at org.postgresql.jdbc.PgConnection/checkClosed (PgConnection.java:766).
This connection has been closed.
Чтобы исправить компонент, придется перезапустить его — вручную вызвать функции start и stop, которые откроют новое соединение.
Сбои в сети случаются часто, и перезапускать компонент при потере соединения тяжело. Нужен механизм, который бы делал это за нас. Так работают пулы соединений, которые открывают их несколько за раз. Пул следит за обрывами связи и обновляет соединения. Он работает в отдельном потоке и не блокирует наши действия.
О пулах мы говорили в первой книге, поэтому приведем только пример. Подключите библиотеку HikariCP в проект:
;; [hikari-cp "2.8.0"]
(require '[hikari-cp.core :as cp])
Функция cp/make-datasource принимает конфигурацию пула и строит объект DataSource. Его записывают в словарь спеки с ключом :datasource. По аналогии с :connection, JDBC проверяет это поле, прежде чем открывать соединение. Если источник задан, библиотека берет соединение из него. В случае с :datasource мы получим заранее открытое соединение, и запрос пройдет так же быстро, как в случае с :connection. Новый компонент:
(def pool-config ;; truncated
{:minimum-idle 10
:maximum-pool-size 10
:adapter "postgresql"
:username "book"
:server-name "127.0.0.1"
:port-number 5432})
(defstate DB
:start
(let [pool (cp/make-datasource pool-config)]
{:datasource pool})
:stop
(-> DB :datasource cp/close-datasource))
Повторите эксперимент с отключением базы. Вы увидите тревожные сообщения, но затем HikariCP получит новое соединение, и система продолжит работу. Это крайне важно для веб-приложений, поэтому в боевом запуске всегда используют пул. Спеку без состояния применяют только для разработки или в тестах.
Состояние поддерживают не только с помощью Mount. Это может быть и другой фреймворк, например Component. Вот как задать JDBC-спеку, которая реагирует на методы start и stop:
(def db
(with-meta
{:dbtype "postgresql"
:dbname "test"
:host "127.0.0.1"
:user "book"
:password "book"}
{'com.stuartsierra.component/start
(fn [this]
(assoc this :connection
(jdbc/get-connection this)))
'com.stuartsierra.component/stop
(fn [{:as this :keys [connection]}]
(when connection
(.close connection))
(dissoc this :connection))}))
Компонент включают, работают с ним и отключают:
(def db-started (component/start db))
(jdbc/query db-started "select ...")
(jdbc/execute! db-started "update ...")
(component/stop db-started)
Мы запустили компонент вручную, но на практике им управляет система. Если вы не уверены в том, что происходит, обратитесь к главе о системах из первой книги. По аналогии напишите компонент для Integrant – еще одной системы управления компонентами, которую мы изучили там же.
SQLite
Особое место в обзоре занимает SQLite. Это не серверная, а клиентская база данных. Отличие от PostgreSQL или MySQL в том, что в SQLite нет центрального сервера, который обслуживает клиентов. SQLite работает на вашем компьютере и является частью проекта.
SQLite хранит данные в файлах по принципу один файл — одна база, что удобно для обмена. Например, внутренняя система раз в сутки выгружает базу SQLite с абонентами и их последними платежами. Эти данные показывает веб-приложение, и ему не нужно обращаться ко внутренней системе.
SQLite подходит для хранения настроек или историй действий пользователя. Браузеры и другие программы ведут несколько SQLite-баз. Ради интереса исследуйте папки профилей Google Chrome, Firefox или Telegram — в каждой их них вы найдете файлы с расширением *.db или *.sqlite.
SQLite ведет себя как обычная реляционная база данных. Ее основные сущности — таблицы, первичные ключи и индексы. База поддерживает транзакции, когда изменяются записи либо в нескольких таблицах, либо ни в одной. Для чтения нет ограничений на число клиентов, но для записи это не так: только один клиент может изменять данные. При попытке сделать это второй клиент получит ошибку, что база заблокирована на запись. Поэтому SQLite используют в основном в программах для одного пользователя. В этом случае данные меняются по очереди, а не параллельно.
Особенность SQLite в том, что она может хранить данные в памяти без обращения к диску. Это полезно, когда программа интенсивно читает данные. Если загрузить их в память целиком, доступ к данным будет быстрее. Иногда программа выполняет долгий расчет и использует базу в памяти как временное хранилище. Позже ее можно сохранить на диск.
Как и в PostgreSQL, работа с SQLite сводится к функциям query, insert!, update! и другим. Разница в том, что, во-первых, нужно подключить драйвер базы, а во-вторых, изменить JDBC-спеку. Добавьте драйвер SQLite в проект:
[org.xerial/sqlite-jdbc "3.36.0"]
Скорее всего, на вашем компьютере установлен браузер Chrome или Firefox. Чтобы не создавать базу вручную, позаимствуем готовую из профиля браузера. Google Chrome хранит историю в файле History (без расширения). Путь к профилю отличается в зависимости от системы:
- Windows 10:
C:\Users\<username>\AppData\Local\Google\Chrome\User Data\Default - MacOS:
/Users/<username>/Library/Application Support/Google/Chrome/Default - Linux:
/home/<username>/.config/google-chrome/default
В нашем случае спека JDBC выглядит так:
(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "/Users/ivan/Library/Application Support/Google/Chrome/Default/History"})
Для доступа к базе Chrome должен быть выключен, иначе вы получите ошибку.
Для начала узнаем, какие таблицы содержит база. Прочитаем их из главной таблицы sqlite_master, в которой записаны все сущности (таблицы, индексы, триггеры и прочее).
(->> (jdbc/query db "SELECT name FROM sqlite_master WHERE type = 'table'")
(mapv :name))
Запрос вернет список строк:
["meta"
"urls"
"sqlite_sequence"
"visits"
"visit_source"
"keyword_search_terms"
"downloads"
"downloads_url_chains"
"downloads_slices"
"segments"
"segment_usage"
"typed_url_sync_metadata"
"content_annotations"
"context_annotations"
"clusters"
"clusters_and_visits"]
Чтобы увидеть последние сайты, что вы посещали, выполните запрос:
(jdbc/query db "SELECT * FROM urls order by last_visit_time desc limit 10")
Приведем один из элементов списка. В нем указан адрес страницы, ее заголовок, число посещений и время последнего посещения в формате наносекунд.
{:id 98,
:url "https://github.com/igrishaev",
:title "igrishaev (Ivan Grishaev)",
:visit_count 5,
:typed_count 0,
:last_visit_time 13261760285085441,
:hidden 0}
Таблица downloads хранит данные о загруженных файлах:
(jdbc/query db "SELECT * FROM downloads limit 10")
Очистка этих таблиц приведет к удалению истории в браузере. Когда вы нажимаете на кнопку “Очистить историю”, браузер выполняет запросы DELETE FROM ко всем таблицам базы.
Пользуясь случаем, исследуйте другие таблицы истории Chrome. Обратите внимание, насколько легко чужой программе получить доступ к истории браузера. Этим пользуются некоторые приложения: взамен на бесплатную установку они читают историю и передают фирмам, которые показывают рекламу.
База в памяти
При работе с SQLite в режиме in-memory возникают особенности. Для начала измените путь к файлу на строку :memory: Это особое значение, которое велит драйверу переключиться на другой тип хранилища. Далее вас ждет странность: если создать таблицу и записать в нее значение, получим ошибку, что таблицы не существует.
(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname ":memory:"})
(jdbc/execute! db "create table users (id integer)")
(jdbc/query db "select * from users")
;; [SQLITE_ERROR] SQL error or missing database (no such table: users)
Как мы помним, если в JDBC-спеке нет состояния, то каждый запрос протекает в новом соединении, которое тут же закрывается. Драйвер SQLite устроен так, что с закрытием соединения пропадает база, созданная в памяти. В примере выше мы создали и уничтожили две базы данных.
Чтобы все работало правильно, установим постоянное соединение. Для этого объявим переменную db* (со звездочкой), “заряженную” полем :connection:
(def db
{:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname ":memory:"})
(def db*
(assoc db :connection
(jdbc/get-connection db)))
Теперь запросы общаются с одной и той же базой:
(jdbc/execute! db* "create table users (id integer)")
(jdbc/insert! db* :users {:id 1})
(jdbc/query db* "select * from users")
Эта оптимизация справедлива и для файловой версии. Если ее не сделать, на каждый запрос драйвер открывает и закрывает файл, что замедлит программу.
Сложные типы
Когда драйвер читает данные, он приводит их типам языка, с которым вы работаете. Для простых типов, которые еще называют скалярными, эта связь очевидна: тип int становится условным java.lang.Integer, varchar и text становятся java.lang.String и так далее.
Гораздо интересней сложные типы, которые состоят из нескольких значений. К ним относятся точки на плоскости, географические объекты, хэш-таблицы, JSON. Нет четких правил о том, как сопоставить их представление в базе с типами Java, а если и есть, то они меняются от проекта к проекту.
Конечно, даже самый сложный объект можно хранить в виде строки. Для этого нужны две функции: привести объект к строке и восстановить его. Предположим, есть таблица платежей. У платежей может быть много полей: адреса отправителя и получателя, реквизиты банков и так далее. Чтобы не создавать колонку под каждое поле, обозначим только главные поля, а остальные данные запишем в виде JSON.
(jdbc/execute! db "CREATE TABLE payments (
id SERIAL PRIMARY KEY,
sum INTEGER,
meta TEXT
)")
В поле meta окажутся поля, для которых нет выделенных колонок. Подготовим функции для конвертации:
(require '[cheshire.core :as json])
(defn meta->str [meta-info]
(json/generate-string meta-info))
(defn str->meta [db-string]
(json/parse-string db-string keyword))
Запишем в базу платеж с произвольными метаданными:
(jdbc/insert!
db :payments
{:id 1
:sum 99
:meta (meta->str {:date "2021-03-10"
:from "test@test.com"
:BIK "332233"
:INN "8626235235"})})
Вот как восстановить поле :meta после выборки:
(let [result
(jdbc/query db "select * from payments")]
(for [row result]
(update row :meta str->meta)))
({:id 1
:sum 99
:meta {:date "2021-03-10"
:from "test@test.com"
:BIK "332233"
:INN "8626235235"}})
Недостаток в том, что функции meta->str и str->meta нужно вызывать каждый раз, когда мы пишем в базу объект или читаем его. Если забыть их, получим строку вместо объекта, что вызовет ошибку в дальнейшем коде. Кроме meta, в таблице могут быть другие поля с JSON, о которых нужно помнить.
Удачный подход в том, чтобы найти бутылочное горлышко, через которое проходят все данные от клиента в базу и обратно. Если изменить поведение там, функции query, insert! и другие сработают одинаково в любом месте проекта. JDBC предлагает такие горлышки для чтения и записи. Рассмотрим их на примере дат и JSON.
Проверим, что случится, если выбрать из базы дату. Для этого обратимся к переменной PostgreSQL с именем current_timestamp:
(-> (jdbc/query db "SELECT current_timestamp AS now")
first
:now
type)
;; java.sql.Timestamp
Тип значения будет Timestamp. Это класс-обертка над обычным java.util.Date, который учитывает особенности времени в SQL. Если мы хотим передать дату в параметрах, она тоже должна быть объектом Timestamp. Класс Date и другие вызовут ошибку:
(jdbc/query db ["select ? as now" (new java.util.Date)])
;; Can't infer the SQL type to use for an instance of java.util.Date.
;; Use setObject() with an explicit Types value to specify the type to use.
Класс Timestamp не совсем удобен в работе, особенно в бизнес-логике. Проекты на Java и Clojure используют более продвинутые типы времени, например библиотеку Joda Time. Рассмотрим, как подружить ее с JDBC.
Роль бутылочного горлышка играют протоколы IResultSetReadColumn, ISQLParameter и ISQLValue. Вместе они определяют, как обработать значение при чтении и записи. Протокол IResultSetReadColumn отвечает за чтение. Он реализует метод с тремя параметрами:
(result-set-read-column [val rsmeta idx])
Аргументы:
val: исходное значение из базы;rsmeta: метаданные результата, экземпляр классаResultSetMetaData. Содержит сводную информацию о полях, именах и типах;idx: индекс текущего поля (считается от нуля).
При чтении результата JDBC прогоняет все значения через этот метод. По умолчанию протокол реализует Object, главный класс в дереве наследования. Для него функция result-set-read-column просто вернет val. Если не задано иное, значения останутся в том виде, в котором пришли из драйвера JDBC.
Изменим протокол так, чтобы для типа Timestamp получился экземпляр DateTime из библиотеки Joda Time. Для этого импортируем класс и расширим протокол:
(:import org.joda.time.DateTime)
(extend-protocol jdbc/IResultSetReadColumn
java.sql.Timestamp
(result-set-read-column [val _rsmeta _idx]
(new DateTime (.getTime val))))
Поскольку параметры rsmeta и idx не нужны, предварим их подчеркиваниями. Из переменной val с типом Timestamp получим экземпляр DateTime. Проще всего это сделать передачей миллисекунд. Метод getTime класса Timestamp вернет их из val; далее передаем их в конструктор DateTime. Теперь любая дата, выбранная из базы, придет в новом виде. Проверим себя:
(first (jdbc/query db "SELECT current_timestamp AS now"))
{:now #object[org.joda.time.DateTime 0x49a269a4 "2021-07-22T10:23:51.136+03:00"]}
С той же легкостью работает установка параметра. Для этой цели служит протокол ISQLValue и его метод sql-value. Он принимает значение и должен вернуть его JDBC-совместимый вариант. Если мы передали DateTime, на выходе должен быть Timestamp. Расширим протокол с обратным преобразованием:
(extend-protocol jdbc/ISQLValue
DateTime
(sql-value [val]
(new java.sql.Timestamp (.getMillis val))))
Добавим в таблицу платежей поле с датой:
(jdbc/execute! db "ALTER TABLE payments ADD COLUMN created_at TIMESTAMP WITH TIME ZONE")
Запишем дату, передав экземпляр DateTime:
(jdbc/insert! db :payments
{:id 2 :sum 100 :created_at (DateTime/now)})
Теперь любая работа с датами подразумевает Joda Time. Если понадобится другой тип даты, измените протокол. Предположим, вместо Joda Time вы предпочитаете встроенный класс java.time.Instant. Перепишите код так, чтобы база возвращала и принимала его экземпляры.
Третий протокол ISQLParameter полезен в случаях, когда установка параметра требует особых действий. По умолчанию JDBC вызывает метод .setObject подготовленного выражения, чего хватает в большинстве случаев. В будущем мы рассмотрим передачу массива в параметры, где вместо .setObject должен быть другой метод. Там мы и познакомимся с этим протоколом.
Организация кода
Расширение протокола не должно переплетаться с бизнес-логикой, поэтому держите его в отдельном модуле. Это глобальное действие, которое затрагивает всю среду исполнения. Сопоставление типов базы и языка меняется от проекта к проекту. В случае с датами будет правильно создать модули project.time-joda и project.time-instant. В зависимости от того, какой тип мы используем в проекте, подключим нужный модуль.
Импорт одного из этих модулей должен быть в коде, который загружается всегда, например в .core-модуле проекта. Добавьте комментарий о том, что импорт расширяет протокол:
(ns project.core
(:require
project.time-joda ;; extends JDBC protocols
[cheshire.core :as json]
...))
Без комментария импорт станет неочевидным. Кажется, что модулем когда-то пользовались, но со временем забыли убрать из секции :require. Кто-то удалит импорт и столкнется со странным поведением базы. Если вы проверяете код линтером, добавьте пространство имен в список исключений. У линтера Joker опция называется :ignored-unused-namespaces:
{:ignored-unused-namespaces [project.time-joda
...]}
Теперь вы не получите предупреждения о том, что модуль project.time-joda подключен, но не используется в коде. Похожая настройка встречается в других линтерах.
В теле :require модули, которые изменяют состояние, обычно помещают без квадратных скобок и псевдонимов (имени после :as). Полезно отделить их пустой строкой от остальных, чтобы выделить на общем фоне.
Поддержка JSON
Как видно из примеров с датами, управлять типами в JDBC нетрудно. Теперь мы добавим мощную функциональность: построим связь между типом JSON в PostgreSQL и Clojure. Для начала вспомним, в чем особенность JSON и где его применяют.
JSON — это запись данных на языке, близком к JavaScript. Кроме простых типов вроде чисел и строк, стандарт поддерживает коллекции — массивы и объекты — и их произвольную вложенность. С помощью JSON данные передают по сети и хранят в файлах. Формат используют для конфигурации приложений.
JSON полезен, когда у данных сложная структура, например глубокая вложенность. Раскладывать такие данные по таблицам и собирать обратно тяжело. Иногда проще записать JSON одну в колонку и позже восстановить данные в приложении.
Этот сценарий привел к тому, что базы научились работать с JSON своими силами. Для них JSON уже не строка, а сложный тип. Для примера запишем один и тот же объект с разным порядком ключей. Если сравнить две строки в лоб, получим ложь из-за отличия в символах:
select '{"foo": 1, "bar": 2}' = '{"bar": 2, "foo": 1}';
-- false
Если же указать строкам тип jsonb, получим истину, потому что порядок ключей в объектах не важен.
select '{"foo": 1, "bar": 2}'::jsonb = '{"bar": 2, "foo": 1}'::jsonb;
-- true
Кроме сравнения, тип json(b) предлагает поиск по отдельному полю, объединение и пересечение объектов, массивов и многое другое. Почти любую операцию над JSON можно выполнить силами SQL, не прибегая к Clojure.
Предположим, поле attributes товара содержит JSON-объект с характеристиками, которые часто меняются:
create table items (id serial, title text, attrs jsonb);
insert into items values
(1, 'Cap', '{"size": "XL", "color": "red", "country": "China"}');
insert into items values
(2, 'Hoodie', '{"size": "L", "color": "black", "country": "Germany"}');
Чтобы выбрать товары красного цвета, добавьте условие в запрос как в примере ниже. Оператор ->> означает извлечь поле color из объекта в виде текста.
select id, title from items where attrs->>'color' = 'red';
id | title
----+-------
1 | Cap
PostgreSQL поддерживает индексы для полей объектов. Если вы часто ищете товары цвету, добавьте следующий индекс, и скорость поиска возрастет:
CREATE INDEX idx_items_color ON items((attrs->>'color'));
Тип json(b) полезен для хранения нечетких данных, чья структура меняется или от чего-то зависит. Один из примеров мы уже рассмотрели — это характеристики товаров. В зависимости от типа товара (техника, одежда, еда) у него могут быть самые разные характеристики. Если держать их в отдельной таблице, это породит лишние связи и таблицы-мосты, что усложнит запрос. Порой дешевле хранить их одним JSON-полем и проверять в приложении перед записью.
Другой случай, когда полезен JSON — в базе сложные и объемные документы. Это может быть бухгалтерский договор или история болезни. В виде JSON эти сущности занимают несколько экранов с глубокой вложенностью. Разложить их на отдельные таблицы стоит времени и сил; еще больше сил уйдет на запись и обновление. Проще хранить сущность как неделимый объект и изменять его целиком.
PostgreSQL поддерживает два типа JSON: одноименный (json) и с частичкой “b” на конце (jsonb). Последнее означает binary, двоичный JSON. Разница между типами в их устройстве. Тип json работает с данными наивно: он хранит исходную строку (с пробелами и переносами строк) и на каждую операцию разбирает ее. Наоборот, jsonb хранит данные в двоичном виде, что не требует разбора при обращении. Это снижает скорость записи, но ускоряет выборку. Скорее всего, вам подойдет jsonb: он поддерживает больше операций включая сравнение, что иногда необходимо.
Если выбрать из базы поле с типом JSON, получим загадочный PGObject:
(def attrs
(-> (jdbc/query db "SELECT * from items")
first
:attrs))
(type attrs)
;; org.postgresql.util.PGobject
Он служит для данных, свойственных PostgreSQL. У класса для поля: тип и значение в виде строки. Исследуем их:
(.getType attrs)
;; "jsonb"
(.getValue attrs)
;; "{\"size\": \"XL\", \"color\": \"red\", \"country\": \"China\"}"
Кроме json(b), в PGObject могут быть данные другого типа, например сетевой адрес:
(first (jdbc/query db "select '127.0.0.1'::inet as IP"))
{:ip #object[org.postgresql.util.PGobject 0x721ff1be "127.0.0.1"]}
Связь между PGObject и JDBC состоит из двух шагов. Во-первых, расширим протокол IResultSetReadColumn:
(extend-protocol jdbc/IResultSetReadColumn
org.postgresql.util.PGobject
(result-set-read-column [pg-obj _rsmeta _idx]
...))
Пока что не ясно, что следует за многоточием. Как мы выяснили, PGobject служит для разных типов. Проверим тип поля и для каждого варианта вызовем свой обработчик. Проще всего это сделать оператором case:
(extend-protocol jdbc/IResultSetReadColumn
org.postgresql.util.PGobject
(result-set-read-column [pg-obj _rsmeta _idx]
(let [pg-val (.getValue pg-obj)
pg-type (.getType pg-obj)]
(case pg-type
("json" "jsonb")
(json/parse-string pg-val keyword)
"inet"
(java.net.InetAddress/getByName pg-val)
;; else
pg-obj))))
Обратите внимание, что реализации json и jsonb совпадают: в обоих случаях база вернет строку, которую парсим библиотекой Chesire. Проверим теперь, что типы читаются верно:
(first (jdbc/query db "SELECT * from items"))
{:id 1, :title "Cap", :attrs {:size "XL", :color "red", :country "China"}}
Для сетевого адреса получим экземпляр Inet4Address:
(first (jdbc/query db "select '127.0.0.1'::inet as IP"))
{:ip #object[java.net.Inet4Address 0x38d6ba76 "/127.0.0.1"]}
Форма case выше не совсем удобна: если понадобится новый тип, придется дописывать что-то в хвост. Потребители вашего кода не смогут это сделать. Возможен более гибкий подход: мультиметод pg->clojure, чья функция-диспетчер возвращает тип поля:
(defmulti pg->clojure (fn [pg-obj]
(.getValue pg-obj)))
Теперь код result-set-read-column сводится к вызову мультиметода:
(result-set-read-column [pg-obj _rsmeta _idx]
(pg->clojure pg-obj))
Расширьте мультиметод типами json и сетевым адресом. Со временем мы добавим в него другие типы.
(defmethod pg->clojure "json"
[pg-obj] ...)
(defmethod pg->clojure "inet"
[pg-obj] ...)
Запись JSON
Теперь когда мы читаем JSON из базы, позаботимся о записи. Для этого расширим протокол jdbc/ISQLValue так, чтобы любая коллекция Clojure становилась объектом PGobject. Но сперва уточним, что имеем в виду под термином “любая коллекция”.
Протоколы в Clojure устроены на базе интерфейсов и иерархии классов. Если мы хотим, чтобы вектор и словарь отзывались на какое-то действие, нужно найти общий интерфейс и расширить его протоколом. Чтобы увидеть интерфейсы, которые реализует коллекция, выполните выражение:
(supers (type {}))
#{clojure.lang.IMeta
java.lang.Runnable
java.io.Serializable
...
clojure.lang.IPersistentCollection}
Вы получите множество интерфейсов Java. Среди прочих нас интересует последний элемент, IPersistentCollection. Он же встречается в supers для типа вектора, списка и последовательности:
(supers (type []))
(supers (type ()))
(supers (type (repeat 1)))
Расширим ISQLValue этим протоколом, чтобы Clojure-коллекция становилась объектом PGobject с типом jsonb:
(extend-protocol jdbc/ISQLValue
clojure.lang.IPersistentCollection
(sql-value [val]
(doto (new org.postgresql.util.PGobject)
(.setType "jsonb")
(.setValue (json/generate-string val)))))
Теперь атрибуты товара можно задать словарем:
(jdbc/insert! db :items
{:id 5
:title "The Catcher in the Rye"
:attrs {:author "J. D. Salinger"
:genre "novel"
:year 1951}})
То же самое методом execute!: поместим словарь в вектор SQL:
(jdbc/execute! db ["INSERT INTO items VALUES (?, ?, ?)"
5
"The Catcher in the Rye"
{:year 1951 :genre "novel" :author "J. D. Salinger"}])
Предположим, мы храним теги товара — короткие строки с его описанием. Проще всего это сделать полем tags с типом jsonb, в который запишем вектор строк:
alter table items add column tags jsonb;
Обновим теги у только что созданной книги:
(jdbc/update! db :items
{:tags ["book" "novel"]}
["id = ?" 5])
и прочитаем ее:
(jdbc/get-by-id db :items 5)
{:id 5
:title "The Catcher in the Rye"
:attrs {:year 1951 :genre "novel" :author "J. D. Salinger"}
:tags ("book" "novel")}
Технически возможно записать в теги словарь, а в атрибуты — список. С точки зрения базы у обоих значений тип json, поэтому ошибки нет. Чтобы этого избежать, проверяйте данные до записи в базу. Проще всего это сделать функцией insert-item, которая сверяет аргументы по спеке.
Для начала объявим спеку полей таблицы items:
(require '[clojure.spec.alpha :as s])
(s/def ::item-fields
(s/keys :req-un [::id ::title]
:opt-un [::attrs ::tags]))
Расширим поля атрибутов и тегов:
(s/def ::attrs
(s/map-of keyword? any?))
(s/def ::tags
(s/coll-of string?))
В функции insert-item мы задали pre-выражение, в котором проверяем поля спекой макросом s/assert.
(defn insert-item [db fields]
{:pre [(s/assert ::item-fields fields)]}
(jdbc/insert! db :items fields))
По умолчанию s/assert ничего не делает, потому что предназначен для отладки. Включите его выражением:
(s/check-asserts true)
Вызовите функцию с неправильными полями:
(insert-item db {:id 5
:title "The Catcher in the Rye"
:attrs {:author "J. D. Salinger"
:genre "novel"
:year 1951}
:tags ["book" "novel" nil]})
Вы получите ошибку спеки, и записи не произойдет. То же самое можно сделать макросом fdef и функцией instrument из модуля спеки. Мы подробно разобрали эти возможности во второй главе первой книги . Обратитесь к ней, если вам не понятны термины, о которых мы говорим.
Проблема nil в JSON
При записи JSON проверяйте значение на nil. Если этого не делать, произойдет следующее: значение nil станет строкой "null" и займет место в базе. Это чревато странным поведением в запросах. Предположим, вы ищете записи с пустыми атрибутами. Если выполнить запрос:
select id from items where attrs is null
, в результате не будет строк с полем attrs, равным "null", потому что это значение json(b), отличное от NULL. Другими словами, вы ввели новое пустое значение. С ним проверка выглядит сложнее:
select id from items where attrs is null or attrs = 'null';
Очевидно, первый запрос удобней, особенно если json(b) встречается в разных таблицах. Следите, чтобы пустое значение было выражено одним способом. Лучше всего для этого подходит стандартный NULL. С точки зрения Clojure это значит, что функция ->pg-object возвращает PGObject только если исходное значение не nil.
Для начала проверим текущее поведение в случае с nil:
(->pg-object nil)
#object[org.postgresql.util.PGobject 0x7c24e232 "null"]
Получим PGobject с "null", что нас не устраивает. С проверкой аргумента на some? получим nil:
(defn ->pg-object [data]
(when (some? data)
(doto (new PGobject)
(.setType "json")
(.setValue (json/generate-string data)))))
(->pg-object nil) ;; nil
Колонка в базе должна иметь атрибут NULL, установленный в истину.
Типизированные записи
Мы установили правило: при передаче параметров любая коллекция становится объектом JSON. Однако PostgreSQL предлагает другие сложные типы, например точки, линии и прямоугольники. Рассмотрим, что вернет база данных, если выбрать точку:
(first (jdbc/query db "select '(1, 2)'::point"))
{:point #object[org.postgresql.geometric.PGpoint 0x28ebf39e "(1.0,2.0)"]}
Получим объект класса PGpoint. Логично привести его в словарю {:x 1.0, :y 2.0}, чтобы упростить дальнейшую работу. Однако при записи точки словарь будет преобразован в JSON, из-за чего получим ошибку типов.
Наиболее простое решение в том, чтобы задать типизированную запись, или рéкорд. Запись повторяет свойства словаря и вдобавок объявляет Java-класс. Если расширить протокол JDBC этим классом, мы сохраним функциональность JSON, потому что класс стоит ниже протокола IPersistentCollection в цепочке наследников.
Объявим запись с двумя слотами координат:
(defrecord Point [x y])
Расширим протокол IResultSetReadColumn типом PGpoint. Импортируем его, чтобы не указывать весь путь. Метод result-set-read-column вернет экземпляр Point с заполненными слотами:
(import 'org.postgresql.geometric.PGpoint)
(extend-protocol jdbc/IResultSetReadColumn
PGpoint
(result-set-read-column [pg-point _rsmeta _idx]
(new Point (.-x pg-point) (.-y pg-point))))
Теперь точка приходит из базы в удобном виде. При печати она выглядит как словарь, но на самом деле это экземпляр записи.
(first (jdbc/query db "select '(1, 2)'::point"))
{:point {:x 1.0, :y 2.0}}
Расширим ISQLValue для перевода точки в тип JDBC. Обратите внимание: поскольку запись ведет себя как словарь, мы распаковали поля x и y на уровне сигнатуры.
(extend-protocol jdbc/ISQLValue
Point
(sql-value [{:keys [x y]}]
(new PGpoint x y)))
Передать точку в запрос можно несколькими способами. Первый — вызвать конструктор класса оператором new. Второй — получить запись из словаря функцией map-><RecordName>. Функция находится в том же пространстве имен, где объявлена запись. Третий способ — предварить словарь тегом с полным именем записи. Все три способа вернут экземпляр Point.
(jdbc/query db ["select ? as point" (new Point 1 2)])
(jdbc/query db ["select ? as point" (map->Point {:x 1 :y 2})])
(jdbc/query db ["select ? as point" #book.db.Point{:x 1 :y 2}])
По аналогии сопоставьте другие типы с записями Clojure, например линии и прямоугольники (line и box).
Проблемы SQL
Каждый, кто работает с базой данных, сталкивается с проблемой: длинный SQL затрудняет чтение кода. Блоки SQL разрывают логику приложения и порождают визуальный шум.
(let [events (get-events http-client)
sql "select * from users u
join profiles p on p.user_id = u.id
where u.is_active
and p.created_at > ..."
result (jdbc/query db sql)]
...)
Большие запросы в коде смотрятся чужеродно. При чтении приходится переключать контекст, чтобы понять, что происходит в запросе. Ситуация ухудшается с копированием: часто необходим такой же запрос, но с небольшими правками. Это усложнит поддержку, ведь однажды критерии выборки могут измениться для всех запросов.
Предположим, в какой-то момент в таблице появился флаг отложенного удаления. Пользователь закрыл учетную запись, и нужно удалить ее из системы. Теперь при выборке пользователя нужно добавить условие “не удален”:
SELECT ... FROM users WHERE ... AND NOT is_deleted
Если запрос многократно копирован по всему проекту, придется искать все случаи и дописывать каждому AND NOT is_deleted.
SQL-выражения со временем растут. На старте проекта в нем мало сущностей и таблиц, но с ростом бизнеса их число увеличивается. Например, выборка популярных товаров с их категориями, оценками и сортировкой может занять экран. Если вставить подобный SQL прямо в код, станет тяжелее поддерживать и код, и SQL.
Мы подводим читателя к вопросу о том, как управлять запросами в промышленных масштабах. В разных языках приняты разные подходы. Наиболее популярен способ с промежуточным слоем, который преобразует объекты в записи таблиц. Например, запрос к таблице users вернет список объектов User, который еще называют моделью. Если изменить поля модели и вызвать метод .save(), сработает либо запрос UPDATE (обновить текущую запись) или INSERT (добавить новую запись).
Сопоставление таблиц и объектов называется ORM (Object–relational mapping). Встречается его разновидность ActiveRecord (сокращенно AR). Как правило, любой современный язык предлагает библиотеки для ORM или AR.
Попытки создать ORM были и в Clojure, но они не получили широкого распространения. Практика показывает, что в Clojure легко работать с базой на низкому ровне. Причина в том, что коллекции Clojure уже обладают важными свойствами: они неизменяемы и на них действуют сотни функций стандартной библиотеки. В Clojure список словарей достаточен для дальнейшей работы, и нет смысла приводить их к другим сущностям.
Все-таки работу с SQL можно облегчить, подключив библиотеки. Мы рассмотрим построители запросов на шаблонах и данных: их особенности, преимущества и недостатки.
Шаблоны
Подход с шаблоном состоит из двух этапов. Сначала создают файл с расширением .sql и пишут в нем запросы. Каждый запрос предваряется шапкой из нескольких комментариев. В них указаны имя запроса, описание и характеристики. На месте параметров стоят именованные метки. Пример подобного файла:
-- :name list-users :?
-- :doc Get all the users
select * from users;
-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select * from users
where id = :id;
Специальный код читает этот файл. Для каждого запроса он порождает одноименную функцию, которая принимает JDBC-спеку и словарь параметров. Результат запроса станет результатом функции при вызове. Так, для запроса get-user-by-id получим функцию, вызвав которую с подключением и параметром, увидим результат:
(get-user-by-id db {:id 1})
{:id 1, :fname "Ivan" ...}
Способ с SQL, который порождает функции, несет несколько преимуществ. Прежде всего, в проекте единый источник SQL. Все запросы собраны в одном файле, и не нужно искать их по всей кодовой базе.
Некоторые фирмы проводят аудит запросов к базам данных, чтобы выявить узкие места. DBA-инженеру будет удобно посмотреть .sql файл и оптимизировать его. Если же вы обращаетесь к базе с помощью ORM, придется искать конечные запросы в логах базы. При этом запросы, составленные программой, читаются хуже, чем написанные человеком.
Если потребуется особый оператор, например слияние JSON-объектов или пересечение интервалов, вы напишете его явно. В случае с ORM придется расширить класс или писать какую-то обертку.
Запросы в исходном виде удобны для отладки. Если вы написали выборку с помощью ORM, то должны убедиться, что она порождает верный SQL. Нужно вызвать уловный объект QuerySet и проверить логи базы. С сырым SQL этот шаг отпадает: его можно выполнить в PGAdmin или схожей программе с минимальными усилиями.
Принцип “SQL на шаблонах” предлагает библиотека HugSQL. Подключите ее в проект (понадобится свежая версия tools.reader):
[org.clojure/tools.reader "1.3.6"]
[com.layerware/hugsql "0.5.1"]
Будем работать с пользователями. Создайте файл resources/sql/queries.sql с запросами:
-- :name list-users :?
-- :doc Get all the users
select * from users;
-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select * from users
where id = :id;
Параметр :name означает имя будущей функции. По желанию добавьте :doc с описанием, и тогда редактор покажет справку в месте вызова. Особенно интересны параметры :? и :1, что указаны после :name. Это флаги, которые определяют, как обработать запрос и его результат.
Знак вопроса означает, что запрос следует выполнить функцией jdbc/query. Библиотека JDBC отличает чтение от исполнения. Чтение предполагает обработку результата, в то время как для исполнения его может либо не быть, либо он нам не интересен. Поэтому указываем :? в запросах на чтение.
Флаги :* и :1 означают сколько записей прочитать из ответа: все или одну. Когда мы ищем заведомо одну запись, логично указать :1, чтобы получить либо ее, либо ничего. В противном случае вам придется оборачивать результат в (first...), что неудобно. Если не задана единица, по умолчанию получим все записи.
Преобразуем запросы в функции. Вызов def-db-fns принимает путь к файлу и наполняет текущее пространство новыми функциями.
(require '[hugsql.core :as hugsql])
(hugsql/def-db-fns "sql/queries.sql")
Выполним их:
(list-users db)
;; ({:id 1 :fname "Ivan", ...}, ...)
(get-user-by-id db {:id 1})
;; {:id 1 :fname "Ivan", ...}
Подготовим вставку пользователей:
-- :name create-user :i!
insert into users (fname, lname, email, age)
values (:fname, :lname, :email, :age);
После изменений в файле сгенерируйте функции еще раз. Появится новая функция create-user:
(create-user db {:fname "Ioann"
:lname "Smith"
:email "test@test.com"
:age 30})
;; {:id 76, :fname "Ioann", ...}
Флаг :i! служит для оператора INSERT. Он означает вернуть первичные ключи записей, которые произвела база данных. Схожий флаг :<! работает с выражением вида INSERT ... RETURNING ..., что значит “вставить и вернуть”. Она полезна, когда полям таблицы заданы значения по умолчанию.
Эти и другие флаги служат синтаксическим сахаром для параметра :command. Если вам трудно запомнить флаги, укажите их явно:
:command :query:command :insert:command :execute:command :returning-execute
Чтобы создать несколько записей в транзакции, обернем функции в макрос with-db-transaction и укажем tx первым аргументом:
(jdbc/with-db-transaction [tx db]
(create-user tx {:fname "User1" ...})
(create-user tx {:fname "User2" ...}))
HugSQL упрощает работу с параметрами. JDBC принимает вектор параметров, и чем их больше, тем сложнее понять, что означает каждый. HugSQL полагается на кейворды и словари в качестве параметров. По ключу легко понять семантику значения, и на него можно сослаться в разных местах запроса.
HugSQL предлагает множество полезных функций. Среди прочих отметим сниппеты — повторяющиеся участки кода. Если один и тот же фрагмент SQL встречается много раз, его задают отдельно и позже ссылаются на него. Предположим, мы хотим контролировать, какие поля пользователя вернет запрос. Объявим их тегом :snip:
-- :snip user-fields
id, fname, lname
Запрос на выборку пользователя изменится. Вместо звездочки (все поля) укажем :snip:user-fields:
-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select :snip:user-fields from users
where id = :id;
Чтобы выполнить запрос, передайте в параметр :user-fields вызов сниппета. Так мы получим только те поля, что объявили.
(get-user-by-id db {:id 1 :user-fields (user-fields)})
;; {:id 1, :fname "Ivan", :lname "Petrov"}
В сниппете может быть что угодно: набор WHERE-условий, постраничная навигация с помощью LIMIT и OFFSET и так далее. Однако помните, что чем больше взапросе сниппетов, тем менее он декларативен. С ними вы теряете преимущества чистого SQL, о которых мы говорили.
Выражения
Еще одна важная функция HugSQL — это шаблонная система в запросах. С помощью особого синтаксиса в запрос можно добавить код на Clojure, который “склеивает” его части. Прием полезен, когда запрос меняется в зависимости от параметров.
Предположим, мы пишем поиск пользователей. В отборе участвуют несколько полей: имя, город, год рождения, при этом обязательно только имя. В этом случае мы не можем составить запрос как в примере ниже:
-- :name find-users :?
SELECT * FROM users
WHERE fname = :name
AND city = :city
AND year_birth = :year-birth
LIMIT 10
Если не указать город или год рождения в параметрах, получим ошибку:
(find-users db {:name "Ivan"})
;; Execution error (ExceptionInfo)
;; at hugsql.core/validate-parameters! (core.clj:83).
;; Parameter Mismatch: :city parameter data not found.
Отбор по необязательным полям должен быть только если значения не пустые. Правильный запрос выглядит так:
-- :name find-users :?
select * from users
where fname = :name
--~ (when (:city params) "and city = :city")
--~ (when (:year-birth params) "and year_birth = :year-birth")
limit 10
Синтаксис --~ предваряет строку с кодом на Clojure. Он должен вернуть строку SQL или nil. Внутри доступна переменная params — словарь параметров, которые передали в запрос. Выше мы проверяем: если заданы поля с городом или возрастом, добавить к WHERE отбор по ним.
Выражение:
(find-users db {:name "Ivan" :city "Chita"})
порождает следующий запрос:
SELECT * FROM users
WHERE fname = $1
AND city = $2
LIMIT 10
parameters: $1 = 'Ivan', $2 = 'Chita'
Кроме однострочных выражений, HugSQL предлагает многострочные по следующим правилам:
/*~ (if ... */
SQL if true
/*~*/
SQL if false
/*~ ) ~*/
Форма /*~*/ означает переход к следующей форме Clojure, в данном случае от положительной ветви if к негативной. Обратите внимание, что последняя строка несет закрывающую скобку для if. Без нее получится ошибка синтаксиса.
Опробуем условный оператор в действии. Доработаем функцию get-user-by-id так, что если не задан сниппет полей, вернем их все. Для этого проверим :user-fields на пустоту и подставим либо :snip:user-fields, либо *.
-- :name get-user-by-id :? :1
-- :doc Get a single user by ID
select
/*~ (if (:user-fields params) */
:snip:user-fields
/*~*/
*
/*~ ) ~*/
from users
where id = :id;
Результат:
(get-user-by-id db {:id 1})
;; {:id 1 :fname "Ivan"
;; :lname "Petrov"
;; :email "...",
;; :age ...}
(get-user-by-id db {:id 1 :user-fields (user-fields)})
;; {:id 1
;; :fname "Ivan"
;; :lname "Petrov"}
Настоящие сложности возникают, когда управляют не отдельными полями, а таблицами. Предположим, в поиске пользователей появилось галочка “только с фотографией”. Снимки хранятся в отдельной таблице photos, которая связана с пользователями внешним ключом. Чтобы выбрать пользователей по наличию фото, в запросе должно быть внутреннее соединение таблиц (INNER JOIN). При этом не всегда, а только если установлен параметр. Все вместе дает нам шаблон:
-- :name find-users2 :?
select * from users u
/*~ (when (:with-photo? params) */
join photos p on p.user_id = u.id
/*~ ) ~*/
Если выполнить запрос с флагом, таблица photos окажется в запросе:
(find-users2 db {:with-photo? true})
;; select * from users u
;; join photos p on p.user_id = u.id
Сниппеты тоже принимают параметры. Добавим в user-fields следующее условие: если передан флаг :root?, вернуть все поля пользователя, а иначе только их часть.
-- :snip user-fields
/*~ (if (:root? params) */
*
/*~*/
id, fname, lname
/*~ ) ~*/
Теперь запрос с флагом вернет все поля. Обратите внимание, что параметр :root? передан именно сниппету, а не основному запросу. Это гарантирует, что одноименные поля запроса и сниппета не вступят в конфликт.
(get-user-by-id
db
{:id 1
:user-fields (user-fields {:root? true})})
HugSQL предлагает многие другие возможности. Ознакомьтесь с ними на странице проекта.
Завершая раздел, отметим похожие библиотеки. Yesql предлагает аналогичный подход: по файлу запросов библиотека строит функции Clojure. Отличие в том, что при генерации вы указываете JDBC-спеку по умолчанию. В этом случае ее не нужно передавать в каждую функцию явно. Для транзакций и особых случаев спеку передают в словаре опций. Yesql портирована на Python, Ruby, Golang и другие популярные языки.
Еще одна библиотека называется Conman. Это обертка над HugSQL с некоторыми упрощениями. Conman входит в состав фреймворка Luminus и является важной его частью. Можно использовать его отдельно от Luminus в проектах на другом стеке.
Построители
Существует иной подход к написанию SQL, известный как Query Builder или построитель запросов. Это объект, который хранит структуру запроса: список полей, таблиц, условий и так далее. Вызывая методы .addWhere, .addJoin и другие, мы наполняем его состояние. Метод .toString или .render построит SQL из накопленных частей. Ниже — пример построителя запросов на Java:
String sql = QueryBuilder.select("u.*", "p.*")
.from("users", "u")
.join("profiles", "p").on("p.user_id = u.id")
.where("not u.is_deleted")
.toString();
// SELECT u*, p* FROM users AS u
// JOIN profiles AS p ON p.user_id = u.id
// WHERE NOT u.is_deleted
Похоже устроены библиотеки SQLAlchemy и Knex.js в Python и JavaScript.
Принцип работает и в Clojure с той разницей, что вместо объекта используют привычный словарь. Специальная функция обходит его и строит SQL. Поскольку данные неизменяемы, каждая операция над словарем вернет новый словарь. Снижается риск ошибки, что мы случайно изменим запрос, на который используется в другом месте.
Библиотека HoneySql работает по принципу “SQL как данные”. Добавьте ее в проект:
[com.github.seancorfield/honeysql "2.0.0-rc5"]
Создайте простой запрос в виде словаря:
(def query
{:select [:*]
:from [:users]
:where [:= :id 1]})
Очевидно, ключи :select, :from и :where означают одноименные части SQL. Важно, что переменная query имеет структуру. Ей легко добавить новое поле или условие функциями assoc-in и update-in.
Функция sql/format принимает подобный словарь и возвращает SQL-вектор. Первый элемент этого вектора — строка запроса, остальные элементы — параметры. В нашем случае получим вектор:
(require '[honey.sql :as sql])
(sql/format query)
;; ["SELECT * FROM users WHERE id = ?" 1]
Обратите внимание, что значение 1 стало параметром, а не частью запроса. Другими словами, запрос SQL не оказался таким:
SELECT * FROM users WHERE id = 1
Как мы обсуждали в начале главы, значения в запросе чреваты ошибками. Скорей всего, единица приходит извне и может содержать махинации для инъекций.
В HoneySql любое значение, отличное от символа и кейворда, означает параметр. Числа, булево и строки станут параметрами, а в запросе на их месте будут вопросительные знаки. Параметры установит драйвер JDBC с учетом кавычек, типов и правил безопасности. Пример со вставкой пользователя:
(def query
{:insert-into :users
:columns [:id :fname :email]
:values [[99 "Ivan" "test@test.com"]]})
(sql/format query)
["INSERT INTO users (id, fname, email) VALUES (?, ?, ?)"
99
"Ivan"
"test@test.com"]
Передайте результат в jdbc/execute!, чтобы выполнить вставку:
(jdbc/execute! db (sql/format query))
Чтобы сослаться на другое поле в условии, поставьте на его место кейворд или символ. Предположим, мы храним бонусные баллы пользователя. Очередная покупка прибавляет сто баллов. Вот как начислить их пользователю с номером 99:
(def query
{:update :users
:set {:bonus_points [:+ :bonus_points 100]}
:where [[:= :id 99]]})
(sql/format query)
["UPDATE users SET bonus_points = bonus_points + ? WHERE (id = ?)" 100 99]
По аналогии работают операторы удаления, группировки, JOIN, UNION и другие. Для каждого из них служит одноименный ключ, который принимает другой словарь или вектор. Справку по всем ключам вы найдете на странице проекта.
Кроме словаря, библиотека предлагает другой способ составить запрос. В модуле honey.sql.helpers находятся функции, совместимые со стрелочными оператором. Каждая функция принимает предыдущий запрос и возвращает дополненный новым полем. Перепишем пример с бонусными баллами:
(require '[honey.sql.helpers :as h])
(def query
(-> (h/update :users)
(h/set {:bonus_points [:+ :bonus_points 100]})
(h/where [[:= :id 99]])))
Некоторые предпочитают такой способ записи, хотя технически он сводится к наполнению словаря.
Служебные функции
Выше мы проделываем одну и ту же работу: получаем SQL-вектор и передаем в JDBC. Совместим эти шаги в одной функции. Она принимает два словаря — запрос и параметры — и возвращает результат обращения к базе. Назовем ее map-query:
(defn map-query [db-spec map-sql & [map-params]]
(jdbc/query db-spec (sql/format map-sql {:params map-params})))
Пример с поиском ползователя по номеру:
(def query
{:select [:*]
:from [:users]
:where [:= :id :?id]})
(map-query db query {:id 13})
Обратите внимание на параметр :?id в секции :where: это кейворд со знаком вопроса впереди. HoneySQL считает его именным параметром. Значение параметра следует в словаре :params в аргументах sql/format.
С новой функцией код сократился. Для большего удобства выносите запросы в отдельный модуль, чтобы позже ссылаться на них. По аналогии напишите функцию map-execute, которая выполняет INSERT, UPDATE и другие операции, отличные от чтения.
Логирование и форматирование
Теперь когда появилась общая точка, через которую проходят все запросы, ее можно улучшить. Например, добавить необязательные параметры, логирование и многое другое.
Предположим, в режиме разработки мы бы хотели видеть все запросы, что посылаем к базе. Добавим отладочный лог:
(defn map-query [db-spec map-sql & [map-params]]
(let [sql-vec (sql/format map-sql {:params map-params})]
(log/infof "Query: %s" (first sql-vec))
(jdbc/query db-spec sql-vec)))
В консоли увидим запись:
(map-query db query {:id 1})
;; 2021-08-06 10:29:25,702 INFO book.db - Query: SELECT * FROM users WHERE id = ?
К сожалению, HoneySql не поддерживает “красивые” запросы с отступами и переносами строк. Для больших запросов это становится проблемой, потому что длинную строку SQL трудно читать. На время разработки подключим Java-библиотеку SqlFormatter для форматирования SQL. Добавьте зависимость в проект:
[com.github.vertical-blank/sql-formatter "2.0.1"]
Импортируйте одноименный класс:
(import 'com.github.vertical_blank.sqlformatter.SqlFormatter)
Чтобы получить форматированный запрос, передайте строку SQL в статический метод SqlFormatter/format. Доработаем логирование в функции map-query:
(defn map-query [db-spec map-sql & [map-params]]
(let [sql-vec (sql/format map-sql {:params map-params})]
(log/infof "Query:\n%s" (SqlFormatter/format (first sql-vec)))
(jdbc/query db-spec sql-vec)))
Теперь в консоли появится красиво оформленный SQL:
(map-query db query {:id 1})
;; 2021-08-06 10:40:24,062 INFO book.db - Query:
;; SELECT
;; *
;; FROM
;; users
;; WHERE
;; id = ?
По желанию его можно как-то выделить, например предварить каждую строку угловой скобкой или вертикальной чертой. Помните, что форматирование отнимает ресурсы и снижает скорость map-query. Сделайте так, чтобы логирование включалось по требованию или в зависимости от среды, например только при разработке.
Доработайте map-query так, чтобы в опциях можно было задать факт логирования, его уровень, признак форматирования и другие параметры.
Расширения
Honeysql легко расширить оператором, которого нет в поставке по умолчанию. Технически это значит вызвать функцию sql/register-clause!. Она меняет состояние библиотеки, где хранятся известные выражения. Функция принимает другую функцию, которая вернет вектор SQL по словарю.
Приведем пример с выражением CREATE INDEX для PostgreSQL. Это сложная команда со множеством полей и условий. Вот лишь малая часть ее синтаксиса:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ]
[ [ IF NOT EXISTS ] name ]
ON [ ONLY ] table
[ USING method ] ...
Наша задача — сделать так, чтобы индекс можно было задать словарем. Этим мы снизим риск ошибки при написании индексов вручную. Например:
(sql/format
{:create-index {:if-not-exists? true
:name "idx_user_lname"
:on-table :users
:on-field :lname}})
;; CREATE INDEX IF NOT EXISTS idx_user_lname ON users (id);
Сперва обработаем главные поля индекса: его имя, уникальность, имя таблицы и некоторые другие. Ниже — черновая версия кода, которая строит индекс:
(sql/register-clause!
:create-index
(fn [_ {idx-name :name
:keys [unique?
if-not-exists?
on-table
on-field
using]}]
[(clojure.string/join
" "
["CREATE"
(when unique? "UNIQUE")
"INDEX"
(when if-not-exists? "IF NOT EXISTS")
(name idx-name)
"ON"
(name on-table)
"(" (name on-field) ")"
(when using "USING")
(when using using)])])
nil)
Проверим, что вернет форматирование:
(sql/format {:create-index {:if-not-exists? true
:name "idx_user_lname"
:on-table :users
:on-field :lname}})
["CREATE INDEX IF NOT EXISTS idx_user_lname ON users ( lname )"]
Если передать этот вектор в jdbc/execute!, мы действительно получим индекс. Доработайте ключ :create-index так, чтобы он поддерживал больше полей и условий, в том числе специфичных для некоторых баз данных.
Кроме выражений, HoneySQL можно расширить операторами. Например, сделать так, чтобы условие:
{:where [:= [:json->> :attributes :color] "red"]}
становилось:
["... WHERE attributes ->> 'color' = ?" "red"]
С таким подходом запросы становятся гибче. Если вы пользуетесь редкими операторами, зарегистрируйте их в HoneySQL, чтобы не копировать код и оперативно править их в одном месте. Изучите примеры с вызовом sql/register-fn! и sql/register-op! из документации библиотеки.
Сырой SQL
HoneySql покрывает почти все операторы SQL, однако рано или поздно понадобится такой, которого нет в поставке. Для этого служит последнее средство — вкрапление сырого SQL. Ключ :raw принимает текст или вектор и внедряет его в запрос с минимальной обработкой. Приведем несколько примеров.
Выборка пользователей, созданных за последний день. В поле :raw следует строка SQL, а именно вычитание дня из текущей даты.
{:select [:*]
:from [:users]
:where [:>= :created_at [:raw "now() - interval '1 day'"]]}
Более сложный запрос, где атрибуты товаров дополняют цветом и размером. Оператор || объединяет два объекта JSON.
(map-query
db
{:update :items
:set {:attrs [:raw ["attrs || " [:param :new-attrs]]]}
:returning [:id]}
{:new-attrs {:color "red" :size "XL"}})
Во втором варианте в :raw указана не строка, а вектор элементов (тоже строк или векторов), которые позже объединяются в строку. С вектором проще поддерживать структуру запроса.
Будьте особенно осторожны с сырым SQL: не допускайте, чтобы в нем был ввод пользователя. Добавляйте его только ключом :raw, но ни в коем случае не конкатенацией строк. Сюда же относятся автозамена, разбиение и другие операции со строками. Все они будут ошибкой.
Повторное использование запросов
В разделе про SQL-шаблоны мы выяснили, как повторно использовать части запросов. В HugSQL мы использовали сниппеты — именованные выражения. В HoneySQL это делается проще, потому что сводится к работе с неизменяемыми коллекциями.
Вернемся к таблице пользователей. Приложение часто обращается к ним: читает всех пользователей, ищет по имени или городу, находит конкретного человека по номеру. У этих запросов общая часть: имя таблицы и список полей, которые получит клиент. Вынесем ее в переменную, на которую позже будем ссылаться. По-другому ее называют “тушкой” запроса:
(def user-base
{:select [:*]
:from [:users]})
Если выполнить “тушку”, получим всех пользователей. Чтобы задать отбор по имени и ограничить длину выборки, добавим эти критерии с помощью assoc:
(map-query db (assoc user-base
:where [:= :fname "Ivan"]
:limit 9))
Переменная user-base не пострадает от новых ключей и не повлияет на дальнейшие запросы.
Усложним задачу поиском по многим полям. В зависимости от того, заполнено поле или нет, мы должны добавить его в WHERE. Обратимся к макросу cond->, который отлично справится с задачей. Он пропускает данные через серию предикатов и форм, при этом каждая форма получает результат предыдущей.
Предположим, при поиске пользователя имя обязательно для заполнения, а почта и возраст – нет. В этом случае составим запрос query на основе user-base с отбором по имени. Затем, если заданы почта и возраст, дополним секцию WHERE:
(let [q-fname "Ivan" ;; (get params :name)
q-email "test@test.com" ;; (get params :email)
q-age nil ;; (get params :age)
query
(assoc user-base :where [:and [:= :fname q-fname]])]
(cond-> query
q-age
(update :where conj [:= :age q-age])
q-email
(update :where conj [:= :email q-email])))
Результат:
{:select [:*]
:from [:users]
:where [:and
[:= :fname "Ivan"]
[:= :email "test@test.com"]]}
Похоже работает условное соединение таблиц. Если задан флаг “с фотографией”, добавим в запрос ключ :join с таблицей photos:
(let [q-fname "Ivan" ;; (get params :name)
with-photo? true ;; (get params :with_photo)
query
(assoc user-base :where [:and [:= :fname q-fname]])]
(cond-> query
with-photo?
(assoc :join [:photos [:= :users.id :photos.user_id]])))
Форма выше вернет запрос:
SELECT * FROM users
INNER JOIN photos ON users.id = photos.user_id
WHERE (fname = 'Ivan')
Усложним поиск еще больше. До сих пор мы использовали равенство по полю, но на практике применяют диапазоны и частичные совпадения. Например, цена от и до при поиске товара или вхождение слова в полное название. Доработайте код так, чтобы для возраста можно было задать границы. Теперь мы ожидаем не одно, а два поля: age__min и age__max. Обратите внимание на двойное подчеркивание: оно отделяет имя поля (age) от оператора сравнения (min, больше или max, меньше). Для почты, города и других строковых полей напишите оператор вхождения contains: name__contains, city__contains и другие.
Аналоги
Коротко перечислим аналоги HoneySQL. Это SQLingvo, Toucan, Korma и другие. Все они так или иначе выражают SQL с помощью данных, которые позже становятся строкой. Познакомившись HoneySQL, вы без труда освоите эти библиотеки, потому что в них принят тот же подход.
Отдельно отметим фреймворк Korma: он делает шаг в сторону ORM. Вы объявляете сущности базы данных, по которым позже строятся запросы. Korma понравится тем, кто работал с фреймворками Django или Rails и теперь ищет что-то похожее.
Промежуточный итог
Мы рассмотрели два способа работы с SQL: шаблоны и данные. В первом случае в файле пишут сырой SQL, который позже становится функциями. Во втором случае запросы получают из словарей и списков. Что выбрать?
Вариант с шаблонами хорош тем, что мысленно вы остаетесь на уровне базы. Запрос легко скопировать в PGAdmin, выполнить и оценить результат; там же отладить его и перенести обратно с минимальными усилиями. Если в фирме есть специалист по базам данных, он сделает аудит запросов: найдет медленные выражения, не оптимальные операторы WHERE и JOIN.
Наоборот, когда запросы описаны словарями, за ними плохо видна логика SQL. Для больших и сложных запросов это становится проблемой. Иногда запросы пишут в исходном виде, долго тестируют и только затем приводят к “кложурному” виду. Получается двойная работа.
С другой стороны, запросы в виде данных легче использовать повторно. Если в нескольких запросах повторяется одно выражение, легко вынести его в переменную. Словари и списки удобней наращивать формами assoc-in и cond->, нежели добавлять код на Clojure в шаблоны SQL.
Оба способа равнозначны, и нельзя с уверенностью сказать, что лучше. Чтобы определиться, испытайте то и другое на практике.
Структура и группировка
Библиотеки, что мы рассмотрели выше, только составляют запрос, но не влияют на его обработку. Независимо от того, что вы предпочитаете – шаблоны или данные – вы столкнетесь с тем, что выполнить запрос еще не достаточно. Часто ответ базы нуждается в обработке из-за вложенных данных. Рассмотрим, что это за проблема и как ее решить.
В сердце реляционных баз данных лежат отношения между таблицами. Почти все операции над таблицами возвращают таблицы. Прибегая к аналогии, можно сказать, что из прямоугольных блоков можно выложить только прямоугольные формы, но не треугольные или круглые. В общем случае база хранит плоские данные и не может построить из них дерево.
На практике мы имеем дело с подчиненными сущностями. Например, у организации несколько филиалов и в каждом из них департаменты. У автора несколько статей, а к статьям пишут комментарии. Товары делятся на категории и подкатегории, заказ состоит из этапов и так далее. Это значит, не достаточно просто выбрать сущности по типу, например отдельно авторов и статьи:
;; authors
[{:id 1 :name "Ivan Petrov"}
{:id 2 :name "Ivan Rublev"}]
;; posts
[{:id :title "Introduction to Python" :author-id 1}
{:id :title "Thoughts on LISP" :author-id 2}]
Мы должны выразить их структуру:
[{:id 1
:name "Ivan Petrov"
:posts [{:id
:title "Introduction to Python"
:author-id 1}]}
{:id 2
:name "Ivan Rublev"
:posts [{:id
:title "Thoughts on LISP"
:author-id 2}]}]
На этом месте реляционные базы вступают в конфликт с требованиями. Без особых ухищрений нельзя получить ответ как в примере выше. Проверим это на практике: создадим таблицы и выберем из них данные.
Первый шаг, таблицы авторов и статей:
create table authors (
id serial primary key,
name text not null
);
create table posts (
id serial primary key,
title text not null,
author_id integer references authors(id)
);
Добавим минимальный набор данных. У каждого автора по две статьи:
INSERT INTO authors (id, name) VALUES
(1, 'Ivan Petrov'),
(2, 'Ivan Rublev');
INSERT INTO posts (id, author_id, title) VALUES
(10, 1, 'Introduction to Python'),
(20, 1, 'Thoughts on LISP'),
(30, 2, 'Learning Clojure'),
(40, 2, 'Working on my pet project');
Третий шаг: запрос на выборку авторов и их статей:
SELECT *
FROM authors a
JOIN posts p ON p.author_id = a.id;
Результат:
id | name | id | title | author_id
----+-------------+----+-----------------------------+-----------
1 | Ivan Petrov | 10 | Introduction to Python | 1
1 | Ivan Petrov | 20 | Thoughts on LISP | 1
2 | Ivan Rublev | 30 | Learning Clojure | 2
2 | Ivan Rublev | 40 | Working on my pet project | 2
Видим, что родительская сущность (автор) повторяется столько раз, столько у нее дочерних записей (статей). Результат нельзя выводить в плоском виде, потому что пользователь ожидает другую структуру. Наверняка он хочет увидеть вложенный список:
- Ivan Petrov
- Introduction to Python
- Thoughts on LISP
- Ivan Rublev
- Learning Clojure
- Working on my pet project
Ситуация ухудшается, если у дочерних записей есть другие дочерние, например оценки или комментарии. Это приведет к еще большему дублированию родителей.
Проблема структуры не так проста, какой кажется на первый взгляд. Она может серьезно задержать разработку. Как правило, клиенты ожидают вложенные данные, чтобы не перестраивать их перед выводом. Требуется общий подход к решению задачи. Мы рассмотрим несколько способов: на уровне кода и базы данных.
Выборка по слоям
Наиболее простой способ в том, чтобы выбрать главную и подчиненные сущности по отдельности. Предположим, мы знаем номер автора, которого нужно найти. Тогда первый запрос вернет его по первичному ключу:
(def author-id 1)
(jdbc/get-by-id db :authors author-id)
;; {:id 1 :name "Ivan Petrov"}
Дочерние записи получим запросом, где ссылка на автора равна его номеру:
(jdbc/find-by-keys db :posts {:author_id author-id})
({:id 10, :title "Introduction to Python", :author_id 1}
{:id 20, :title "Thoughts on LISP", :author_id 1})
Соедининим результаты, и ответ готов:
(let [author-id 1
author (jdbc/get-by-id ...)
posts (jdbc/find-by-keys ...)]
(assoc author :posts posts))
Хоть это и наивное решение, оно имеет право на жизнь. Мы производим строго два запроса, что не нагрузит базу данных. При желании их можно запустить параллельно.
Номер сущности известен не всегда. Порой записи ищут не по первичным ключам, а более общим признакам: имени, городу. В этом случае сперва выберем родительские записи (их будет несколько), а затем дочерние, что на них ссылаются.
Поиск авторов по имени:
(jdbc/query db ["SELECT * FROM authors WHERE name = ?" "Ivan"])
;; ({:id 1 ...} {:id 2 ...})
Получили несколько записей. Возникает вопрос, как найти дочерние. Если делать это в цикле для каждого родителя, как в примере ниже:
(for [author authors]
(let [{:keys [id]} author
posts (jdbc/find-by-keys db :posts {:author_id id})]
(assoc author :posts posts)))
, мы произведем столько запросов, сколько авторов. Это неэффективно: с ростом выборки число запросов будет расти линейно. Если запрос по имени вернул тысячу авторов, мы тысячу раз обратимся к базе.
Другой способ в том, чтобы получить номера авторов и составить запрос вида:
... FROM posts WHERE author_id IN (?, ?, ...)
, где знаков вопроса столько же, сколько авторов. Получится один запрос к базе. Если добавить индекс на поле posts.author_id, это ускорит выборку.
Заметим, что выражение IN (...) требует усилий в построении запроса. Это не один, а несколько параметров, разделенных запятой. Их позиция должна совпадать с позицией значения в векторе SQL. Если поиск по имени нашел десять авторов, вот как будет выглядеть вектор SQL с поиском статей:
(jdbc/query db ["SELECT * FROM posts WHERE author_id IN (?,?,?,?,?,?,?,?,?,?)"
1, 2, 3, 4, 5, 6, 7, 8, 9, 10])
Библиотеки пытаются облегчить подобную запись. В HugSQL для этого служит оператор where id in (:v*:ids) — частичка :v* означает вектор параметров с именем ids. HoneySQL предлагает форму :in с похожей семантикой.
В современных ORM такая выборка называется fetch related, извлечь связанные записи. Например, в Django ORM у объекта QuerySet есть одноименный метод prefetch_related. С его помощью данные извлекают послойно: сперва сущности первого порядка, затем те, что ссылаются на них, затем потомки потомков и так далее. Вы можете управлять глубиной вложенности и другими параметрами.
Напишем функцию на Clojure, которая выбирает данные схожим образом. Она ожидает подключение к базе, таблицу, имя внешнего ключа и список их значений.
(defn fetch-related
[db-spec table fk-name fk-vals]
(jdbc/query
db-spec
(sql/format
{:select [:*]
:from [table]
:where [[:in fk-name fk-vals]]})))
Пример ее вызова:
(fetch-related db :posts :author_id [1..10])
;; ["SELECT * FROM posts WHERE
;; (author_id IN (?, ?, ...))" 1 2 ...]
Номера авторов получим формой (mapv :id authors) из предыдущего результата.
Будьте осторожны с оператором IN. Когда параметров много, это плохо сказывается на производительности. Поиск авторов может вернуть сто тысяч записей, что породит запрос с таким же числом параметров. Трудно обозначить конкретный порог для IN, потому что он зависит от окружения. В одном из проектов, над которым работал автор, таким порогом было число 100. Другими словами, в оператор WHERE ... IN (...) разрешалось передать не более ста параметров.
Это условие меняет код. Мы не знаем заранее, сколько ключей получим из базы, поэтому приходиться делать запросы в цикле. Для этого разбиваем список ключей по сотням и наполняем единый результат. Очевидно, должна быть универсальная функция, которая проходит цикл. Если каждый раз копировать код, вы не избежите ошибок.
Проще всего сделать выборку через loop или reduce:
(defn by-chunks [coll n]
(partition n n [] coll))
(reduce
(fn [result ids-chunk]
(let [rows (fetch-related ... ids-chunk)]
(into result rows)))
[]
(by-chunks ids-all 100))
Опробуйте выборку в полупараллельном режиме через pmap. Функция строит ленивую коллекцию футур, которые выполняются по мере ее чтения. Окно коллекции (chunk size) равно числу ядер компьютера, умноженному на два.
(let [ids-chunks
(by-chunks ids-all 100)
futs
(pmap (fn [ids-chunk]
(fetch-related ... ids-chunk))
ids-chunks)]
(reduce
(fn [result fut]
(into result @fut))
[]
futs))
Оператор IN можно записать в виде условия ANY с массивом. Синтаксис читается как “поле id равно хотя бы одному элементу”:
SELECT * FROM authors WHERE id = ANY('{1,2,3,4,5}');
Следующий код показывает, как передать массив в параметры запроса. Для начала получим соединение с базой:
(def ^java.sql.Connection conn
(jdbc/get-connection db))
Соединение нужно для того, чтобы построить объект массива. Метод .createArrayOf принимает тип элемента и массив объектов Object с номерами сущностей.
(def array
(.createArrayOf conn "INTEGER" (object-array [1 2 3 4 5])))
Чтобы установить массив в параметр, расширим протокол ISQLParameter для типа java.sql.Array. По умолчанию JDBC вызывает метод .setObject, что не подходит для массива. Реализуем метод set-parameter так, что у подготовленного выражения вызывается метод .setArray:
(extend-protocol jdbc/ISQLParameter
java.sql.Array
(set-parameter [val ^java.sql.PreparedStatement stmt ix]
(.setArray stmt ix val)))
После всех подготовок выполните запрос с массивом. Он будет правильно передан в запрос:
(def array
(.createArrayOf conn "INTEGER" (object-array [1 2 3 4 5])))
(jdbc/query db ["select * from authors where id = ANY(?)" array])
;; ({:id 1 :name "Ivan Petrov"} {:id 2 :name "Ivan Rublev"})
Сократим код при помощи служебной функции make-db-array. По спеке, типу и вектору значений она вернет экземпляр java.sql.Array:
(defn make-db-array [db-spec db-type values]
(let [conn (jdbc/get-connection db)]
(.createArrayOf conn db-type (object-array values))))
(make-db-array db "integer" [1 2 3])
;; #object[org.postgresql.jdbc.PgArray 0x7d1d3233 "{\"1\",\"2\",\"3\"}"]
Ручная группировка
Выбрать смежные записи можно оператором JOIN. С ним мы получим данные одним запросом. Порой это выгодней обхода по слоям, который совершает серию запросов. Даже если выборка не обладает нужной структурой, ее обрабатывают в коде, в то время как база обслуживает других клиентов. С другой стороны, на больших таблицах оператор JOIN замедляется, особенно если условие связи не попадает в индекс. Производительность таких запросов зависит от окружения, оборудования и прочих факторов.
Пример запроса с JOIN, который вернет плоские данные. Обратите внимание на повторы в первых трех колонках (авторах):
SELECT *
FROM authors a
JOIN posts p ON p.author_id = a.id;
id | name | id | title | author_id
----+-------------+----+-----------------------------+-----------
1 | Ivan Petrov | 10 | Introduction to Python | 1
1 | Ivan Petrov | 20 | Thoughts on LISP | 1
2 | Ivan Rublev | 30 | Learning Clojure | 2
2 | Ivan Rublev | 40 | Working on my pet project | 2
Первый шаг к группировке — задать полям уникальные имена. В выборке два поля id, и может возникнуть путаница где чей номер. Чтобы этого избежать, укажем псевдонимы с пространством имен. Из-за символа / их нужно заключить в двойные кавычки.
SELECT
a.id as "author/id",
a.name as "author/name",
p.id as "post/id",
p.title as "post/title",
p.author_id as "post/author-id"
FROM authors a
JOIN posts p ON p.author_id = a.id;
Польза подобных имен в том, что мы получим словари с ключами :author/id, :post/id и другими, что удобно для работы в Clojure.
Каждая запись содержит “слипшиеся” сущности – автора и публикацию – и наша задача разделить их. Простой способ это сделать — написать функции get-author и get-post с явной выборкой ключей:
(defn get-author [db-row]
(select-keys db-row [:author/id :author/name]))
(defn get-post [db-row]
(select-keys db-row [:post/id :post/title :post/author-id]))
Проверка функций:
(def row
{:author/id 1
:author/name "Ivan Petrov"
:post/id 10
:post/title "Introduction to Python"
:post/author-id 1})
(get-author row)
;; #:author{:id 1, :name "Ivan Petrov"}
(get-post row)
;; #:post{:id 10, :title "Introduction to Python", :author-id 1}
Выполним обход всех записей. На каждом шаге разделяем запись на сущности и наполняем пустой словарь.
(reduce
(fn [result row]
(let [author (get-author row)
post (get-post row)
author-id (:author/id author)
post-id (:post/id post)]
(-> result
(update-in [:authors author-id] merge author)
(update-in [:authors author-id :author/posts post-id] merge post))))
{}
db-result)
Распечатаем результат. Видим, что статьи сгруппированы по авторам:
{:authors
{1 #:author{:id 1
:name "Ivan Petrov"
:posts {10 #:post{:id 10 :title "Introduction to Python" :author-id 1}
20 #:post{:id 20 :title "Thoughts on LISP" :author-id 1}}}
2 #:author{:id 2
:name "Ivan Rublev"
:posts {30 #:post{:id 30 :title "Learning Clojure" :author-id 2}
40 #:post{:id 40 :title "Working on my pet project" :author-id 2}}}}}
Можно отдать эти данные клиенту в виде JSON. Заметим, мы выполнили только один обход функцией reduce, что немаловажно. Если алгоритм требует два и более обходов выборки, с ее ростом код замедлится.
Расширим алгоритм под тройную вложенность. Предположим, к статьям оставляют комментарии и мы бы хотели видеть несколько последних. Для начала добавим комментарии в базу:
create table comments (
id serial primary key,
text text not null,
post_id integer not null references posts(id)
);
INSERT INTO comments (id, post_id, text) VALUES
(100, 10, 'Thanks for sharing this!'),
(200, 10, 'Nice reading, it was useful.'),
(300, 30, 'TL;DR: you must learn lisp');
Новый запрос включает авторов, статьи и комментарии к ним. Обратите внимание на разницу в операторах JOIN. Для таблицы posts это (INNER) JOIN. С ним мы пропустим авторов, у которых нет статей. Но для comments используем LEFT JOIN, чтобы оставить статьи без комментариев.
SELECT
a.id as "author/id",
a.name as "author/name",
p.id as "post/id",
p.title as "post/title",
p.author_id as "post/author-id",
c.id as "comment/id",
c.text as "comment/text"
FROM authors a
JOIN posts p ON p.author_id = a.id
LEFT JOIN comments c ON c.post_id = p.id;
Повторов станет еще больше:
author/id | author/name | post/id | post/title | post/author-id | comment/id | comment/text
-----------+-------------+---------+-----------------------------+----------------+------------+------------------------------
1 | Ivan Petrov | 10 | Introduction to Python | 1 | 100 | Thanks for sharing this!
1 | Ivan Petrov | 10 | Introduction to Python | 1 | 200 | Nice reading, it was useful.
2 | Ivan Rublev | 30 | Learning Clojure | 2 | 300 | TL;DR: you must learn lisp
1 | Ivan Petrov | 20 | Thoughts on LISP | 1 | |
2 | Ivan Rublev | 40 | Working on my pet project | 2 | |
Доработаем прошлый код. Прежде всего, в функциях get-author, get-post и других нет смысла. Поля сущности определяют по пространству, например, все ключи с пространством :author/ относятся к авторам. Напишем общую функцию get-entity, которая принимает пространство и запись базы данных.
(defn get-entity [entity db-row]
(reduce-kv
(fn [result k v]
(if (= (namespace k) entity)
(assoc result k v)
result))
{}
db-row))
С ней мы получим любую сущность:
(get-entity "author" row)
#:author{:id 1 :name "Ivan Petrov"}
(get-entity "post" row)
#:post{:id 10 :title "Introduction to Python" :author-id 1}
Более универсальное решение: напишем функцию, которая принимает запись и вернет словарь сущностей:
(defn row->entities [db-row]
(reduce-kv
(fn [result k v]
(assoc-in result [(namespace k) k] v))
{}
db-row))
Пример ее работы:
(row->entities row)
{"author" #:author{:id 1 :name "Ivan Petrov"}
"post" #:post{:id 10 :title "Introduction to Python" :author-id 1}}
Чтобы извлечь сущности в переменные, применим синтаксис :strs для разбиения словаря с ключами-строками. Это значительно сэкономит код.
(let [{:strs [author
post
comment]} (row->entities row)]
...)
Новый обход с учетом комментариев:
(reduce
(fn [result row]
(let [{:strs [author post comment]}
(row->entities row)
{author-id :author/id} author
{post-id :post/id} post
{comment-id :comment/id} comment]
(cond-> result
:then
(update-in [:authors
author-id] merge author)
:then
(update-in [:authors
author-id
:author/posts
post-id] merge post)
comment-id
(update-in [:authors
author-id
:author/posts
post-id
:post/comments
comment-id] merge comment))))
{}
db-result)
Обратите внимание: вместо линейного оператора -> мы используем условный cond->. С ним комментарий присоединяется к результату только если поле comment-id не пустое. Проверка необходима, потому что комментарий может быть пустым. Результат:
{:authors
{1 #:author{:id 1
:name "Ivan Petrov"
:posts {10 #:post{:id 10
:title "Introduction to Python"
:author-id 1
:comments {100 #:comment{:id 100
:text "Thanks for sharing this!"
:post-id 10}
200 #:comment{:id 200
:text "Nice reading, it was useful."
:post-id 10}}}
20 #:post{:id 20 :title "Thoughts on LISP" :author-id 1}}}
2 #:author{:id 2
:name "Ivan Rublev"
:posts {30 #:post{:id 30
:title "Learning Clojure"
:author-id 2
:comments {300 #:comment{:id 300
:text "TL;DR: you must learn lisp"
:post-id 30}}}
40 #:post{:id 40 :title "Working on my pet project" :author-id 2}}}}}
Из примеров напрашивается общая функция группировки. Они принимает результат базы и описание сущностей: их поля и связи между родителем и потомком. Функция обходит выборку согласно структуре и наполняет словарь результата. С учетом всех тонкостей код будет велик, поэтому оставим эту задачу в качестве упражнения.
Порядок записей
У нашего обхода один недостаток. Предположим, мы выбрали данные из базы в особом порядке: авторы по алфавиту, статьи — по дате публикации, комментарии — по оценке пользователями. Поскольку мы группируем сущности в словарях, порядок записей теряется. Если отправить данные из примера выше клиенту, он будет вынужден сортировать их заново, что неудобно.
Чтобы клиент был доволен, после группировки мы должны восстановить порядок записей. В техническом плане поля :authors, :author/posts и :post/comments должны стать списками, элементы которых идут в той же последовательности, что и в запросе. Для этого назначим сущностям поле :db/index с числом – позицию в выборке. Чтобы знать индекс на каждом шаге, напишем служебную функцию enumerate. Она превращает коллекцию в список пар, где первый элемент — индекс от нуля:
(def enumerate
(partial map-indexed vector))
(enumerate ["a" "b" "c"])
;; ([0 "a"] [1 "b"] [2 "c"])
Группировка сущностей тоже изменится: теперь она принимает индекс и добавляет его к каждой сущности.
(defn row->entities [idx db-row]
(reduce-kv
(fn [result k v]
(update result
(namespace k)
assoc
k v
:db/index idx))
{}
db-row))
Пример:
(row->entities 3 {:post/id 1 :author/id 2})
{"post" {:post/id 1 :db/index 3}
"author" {:author/id 2 :db/index 3}}
Новый обход выборки отличается тем, что reduce принимает результат enumerate. Индекс каждой записи передается в row->entities:
(reduce
(fn [result [idx row]]
(let [{:strs [author post comment]}
(row->entities idx row)]
...))
{}
(enumerate db-result))
Приведем малую часть результата:
{:authors
{1 {:author/id 1
:db/index 3
:author/posts
{10 {:post/id 10
:db/index 1
:post/comments
{100 {:comment/id 100
:db/index 0
:comment/post-id 10}
200 {:comment/id 200
:db/index 1
:comment/post-id 10}}}}}}}
Видим, что в каждой сущности появилось поле :db/index. Изменим дерево так, чтобы некоторые словари стали вектором, упорядоченным по этому полю. Проще всего это сделать модулем clojure.walk. Подготовим проверку на элемент словаря:
(def entry?
(partial instance? clojure.lang.MapEntry))
Укажем теги, которые нуждаются в сортировке:
(def nested-tags
#{:authors :author/posts :post/comments})
Вот как выглядит “ремаппинг” сущностей. Если текущая форма – элемент словаря, а ключ – один из тех, что нуждается в сортировке, то значение (вложенный словарь) приводится к вектору, элементы которого – значения вложенного словаря, упорядоченные по :db/index.
(defn remap-entities
[form]
(if (entry? form)
(let [[k v] form]
(if (contains? nested-tags k)
[k (->> v vals (sort-by :db/index) vec)]
form))
form))
Мы используем префикс remap- вместо sort- потому, что функция не только сортирует сущности, но и меняет структуру данных. Подключим модуль clojure.walk и вызовем prewalk с новой функцией и результатом группировки:
(require '[clojure.walk :as walk])
(:authors
(walk/prewalk remap-entities result-grouped))
Результат:
[{:author/id 1
:db/index 3
:author/name "Ivan Petrov"
:author/posts
[{:post/id 10
:db/index 1
:post/title "Introduction to Python"
:post/author-id 1
:post/comments
[{:comment/id 100
:db/index 0
:comment/text "Thanks for sharing this!"
:comment/post-id 10}
{:comment/id 200
:db/index 1
:comment/text "Nice reading, it was useful."
:comment/post-id 10}]}
{:post/id 20
:db/index 3
:post/title "Thoughts on LISP"
:post/author-id 1}]}
{:author/id 2
:db/index 4
:author/name "Ivan Rublev"
:author/posts
[{:post/id 30
:db/index 2
:post/title "Learning Clojure"
:post/author-id 2
:post/comments
[{:comment/id 300
:db/index 2
:comment/text "TL;DR: you must learn lisp"
:comment/post-id 30}]}
{:post/id 40
:db/index 4
:post/title "Working on my pet project"
:post/author-id 2}]}]
Эти данные устроят клиента, поскольку не нуждаются в обработке на стороне браузера. Разве что поле :db/index можно удалить: это технический артефакт, в котором больше нет смысла. Доработайте remap-entities так, чтобы после (sort-by :db/index) шла форма с удалением :db/index. Искушенные читатели могут объединить сортировку и удаление в трансдьюсер.
Итог
Подход fetch related означает, что записи извлекают по слоям. Сперва находят основные сущности, затем те, что ссылаются на них и так далее. Подобные запросы содержат оператор IN с набором первичных ключей. Чтобы не передать их огромное количество, дочерние записи накапливают в цикле с шагом в сто элементов. Для вашего проекта это число может быть другим.
Способ, когда из базы выбирают все за раз, называется select related. Одноименный метод в ORM добавляет в запрос оператор (LEFT) JOIN с дочерними таблицами, которые ссылаются на основную. Позже, когда вы обращаетесь к полям-ссылкам, система находит их из выборки без обращения к базе.
Каждый подход несет преимущества и недостатки. Select related снижает число обращений к базе, но требует код, который приведет плоскую выборку к дереву. Как правило, содержание базы стоит дороже, поэтому если можно снизить на нее нагрузку, создав больше приложений, так и поступают.
С другой стороны, желание выбрать все за раз может обернуться медленным запросом, особенно если участвуют много таблиц с оператором JOIN. В нагруженных проектах это даже невозможно из-за того, что таблицы разносят по разным базам. В этом случае подход fetch related остается одним вариантом.
Очевидно, нельзя сказать, какой способ предпочтительней. Важно понимать достоинства и недостатки каждого и верно применять их в текущих условиях.
Группировка в базе
Рассмотрим третий способ группировать данные — делать это запросом на уровне базы. С помощью функций json(b)_agg и json(b)_object_agg можно превратить часть полей во вложенный массив с типом json(b). Если расширить протоколы JDBC типом JSON, как описано выше, вызов jdbc/query вернет привычные коллекции Clojure.
Агрегация JSON не входит в стандарт SQL и поэтому доступна не во всех базах. Однако спрос на эту возможность привел к тому, что в том или ином виде она работает не только в PostgreSQL, но и в MySQL или Sqlite. Уточним, что далее по тексту речь идет именно о PostgreSQL; для других баз синтаксис будет иным.
Агрегация в базе несет преимущества и недостатки. Запрос можно составить так, что данным не нужна обработка — они сразу готовы к отправке. За счет этого уходит код, что мы писали для обхода и группировки. С другой стороны, агрегация занимает ресурсы базы, что может не подойти для нагруженных проектов. Усложняется синтаксис SQL: когда уровней вложенности больше двух, его трудно читать и поддерживать.
Функция jsonb_agg принимает колонку и возвращает массив jsonb со значениями этих колонок. Еще одна функция row_to_json принимает произвольную запись и возвращает JSON, где ключи — имена полей записи. Вместе эти функции группируют целые таблицы и подзапросы, а не только колонки.
Вернемся к примеру с авторами, статьями и комментариями. Для начала выберем статьи с комментариями:
SELECT
p.id as "post/id",
p.title as "post/title",
p.author_id as "post/author-id",
json_agg(row_to_json(c)) FILTER (WHERE c IS NOT NULL) as "post/comments"
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id;
Результат:
post/id | post/title | post/author-id | post/comments
---------+-----------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------
10 | Introduction to Python | 1 | [{"id":100,"text":"Thanks for sharing this!","post_id":10}, {"id":200,"text":"Nice reading, it was useful.","post_id":10}]
20 | Thoughts on LISP | 1 |
30 | Learning Clojure | 2 | [{"id":300,"text":"TL;DR: you must learn lisp","post_id":30}]
40 | Working on my pet project | 2 |
Статья, у которой нет комментариев, осталась в выборке. Поле post/comments носит тип json; внутри него массив объектов, где каждый объект представляет запись таблицы comments.
Выражение FILTER после json_agg отсекает пустые элементы. Если этого не сделать, получим массив с одним элементом NULL, что семантически неверно.
Теперь выберем авторов и присоединим к ним запрос выше со статьями и комментариями. Поместим его в подзапрос и сгруппируем еще раз по авторам. При наборе подобного SQL важны отступы, чтобы не запутаться с логическим уровнем.
SELECT
a.id as "author/id",
a.name as "author/name",
json_agg(row_to_json(posts)) as "author/posts"
FROM
authors a,
(SELECT
p.id as "post/id",
p.title as "post/title",
p.author_id as "post/author-id",
json_agg(row_to_json(c)) FILTER (WHERE c IS NOT NULL) as "post/comments"
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id
) AS posts
WHERE a.id = posts."post/author-id"
GROUP BY a.id;
Если выполнить запрос, получим данные как в примере ниже. Они готовы к отправке клиенту; не требуется сортировка или дополнительный обход.
[#:author{:id 1
:name "Ivan Petrov"
:posts [#:post{:id 10
:title "Introduction to Python"
:author-id 1
:comments [{:id 100 :text "Thanks for sharing this!" :post_id 10}
{:id 200
:text "Nice reading, it was useful."
:post_id 10}]}
#:post{:id 20
:title "Thoughts on LISP"
:author-id 1
:comments nil}]}
#:author{:id 2
:name "Ivan Rublev"
:posts [#:post{:id 30
:title "Learning Clojure"
:author-id 2
:comments [{:id 300 :text "TL;DR: you must learn lisp" :post_id 30}]}
#:post{:id 40
:title "Working on my pet project"
:author-id 2
:comments nil}]}]
Библиотеки HugSQL и HoneySQL помогают строить подобные запросы по частям. В обоих случаях можно вынести внутреннюю часть (статьи и комментарии) в отдельную сущность и сослаться на нее. В HugSQL мы бы вынесли подзапрос в сниппет, а в HoneySQL поместили бы его в переменную. С таким подходом главный запрос выглядит чище и удобней для чтения.
Рассмотрим другой пример группировки. Предположим, таблица хранит данные о товарах: артикул, название и так далее. У каждого товара могут быть десятки характеристик: жанр книги, размер талии или разъем питания. Чтобы бесконечно не добавлять столбцы к товарам, вынесем характеристики в отдельную таблицу с именем характеристики, значением и ссылкой на товар.
Подготовим таблицы:
CREATE TABLE goods (
id serial primary key,
title text not null
);
CREATE TABLE good_attrs (
id serial primary key,
good_id integer not null references goods(id),
attr text not null,
val jsonb
);
Добавим несколько товаров с характеристиками:
INSERT INTO goods (id, title)
VALUES (1, 'iPhone 99x'),
(2, 'Galaxy 33.plus'),
(3, 'G. Orwell 1984');
INSERT INTO good_attrs (good_id, attr, val)
VALUES (1, 'phone.display.diag', '145'),
(1, 'phone.wifi.support', 'true'),
(3, 'book.pages', '215'),
(3, 'book.genre', '"dystopia"');
Обратите внимание, что значение характеристики носит тип jsonb. Это значит, нам доступно все богатство его типов. Например, можно указать размер одежды буквой (L, M); число портов натуральным числом (2, 3); поддержку протокола (истина или ложь); список ссылок на сторонние ресурсы (массив строк).
Хотелось бы извлечь товары одним запросом, чтобы получить структуру:
[{:id 1
:title "iPhone 99x"
:attrs {:phone.display.diag 145
:phone.wifi.support true}}
{:id 2
:title "Galaxy 33.plus"
:attrs nil}
{:id 3
:title "G. Orwell 1984"
:attrs {:book.genre "dystopia"
:book.pages 215}}]
Однако при выборке в лоб через JOIN получим задвоение товаров, что требует обработки результата.
Функция json_object_agg принимает списки ключей и значений и строит словарь (в терминах JSON — объект). Напишем вложенный запрос, который группирует колонки attr и val таблицы good_attrs:
SELECT
ga.good_id,
jsonb_object_agg(ga.attr, ga.val) as attrs
FROM good_attrs ga
GROUP BY ga.good_id;
Результат:
good_id | attrs
---------+---------------------------------------------------------
3 | {"book.genre": "dystopia", "book.pages": 215}
1 | {"phone.display.diag": 145, "phone.wifi.support": true}
Поместим его в подзапрос и присоединим к товарам при помощи LEFT JOIN:
SELECT
g.id,
g.title,
a.attrs
FROM
goods g
LEFT JOIN (
SELECT
ga.good_id,
jsonb_object_agg(ga.attr, ga.val) as attrs
FROM good_attrs ga
GROUP BY ga.good_id
) a ON a.good_id = g.id;
id | title | attrs
----+----------------+---------------------------------------------------------
1 | iPhone 99x | {"phone.display.diag": 145, "phone.wifi.support": true}
2 | Galaxy 33.plus |
3 | G. Orwell 1984 | {"book.genre": "dystopia", "book.pages": 215}
Товар, у которого нет характеристик, остался в выборке. Если передать итоговый запрос в jdbc/query, получим именно ту структуру данных Clojure, на которую рассчитывали.
В этом преимущество json(b): PostgreSQL предлагает множество функций для этого типа, в том числе группировку. Любое поле json(b) будет прочитано в Clojure за счет протокола. Это может быть массив, словарь и любая их комбинация.
Еще один прием, связанный с группировкий — использовать row_to_json в сочетании с оператором UNION. Он объединяет несколько запросов по вертикали, при этом обязательно, чтобы состав полей (их число и типы) был одинаков во всех запросах. Пример с числом и строкой:
select 1 as id, 'foo' as name
UNION
select 2 as id, 'bar' as name;
id | name
----+------
1 | foo
2 | bar
Иногда в проекте бывает несколько похожих сущностей в разных таблицах. Например, договоры с юрлицами и населением; администраторы и пользователи; крупные и малые поставщики. Из-за разной структуры их нельзя объединить в один запрос. Но поскольку функция row_to_json приводит запись к json, можно объединить результаты любых записей.
Покажем это на примере. Предположим, администраторы системы хранятся отдельно от пользователей, но в какой-то момент нужно увидеть и тех, и других. Создадим таблицу администраторов:
CREATE TABLE admins (
id serial,
full_name text NOT NULL,
email text
);
и несколько записей:
INSERT INTO admins (full_name, email)
VALUES ('Petr Smirnov', 'petr@test.com'),
('Oleg Ivanov', 'oleg@test.com');
Ниже — запрос, который выберет обе сущности. Обратите внимание на поля id и type в обеих частях. Первичный ключ (id) нужен настолько часто, что логично вынести его на верхний уровень запроса. Поле type содержит строку с типом сущности: “user” для пользователя и “admin” для администратора. По этому полю легко понять, что находится в entity.
SELECT
u.id AS id,
'user' AS type,
row_to_json(u) AS entity
FROM
users u
UNION ALL
SELECT
a.id AS id,
'admin' AS type,
row_to_json(a) AS entity
FROM
admins a;
Результат в консоли psql:
id | type | entity
----+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------
1 | user | {"id":1,"fname":"Ivan","lname":"Petrov","email":"test@test.com","age":30,"city":null,"year_birth":null,"created_at":"2021-08-10T07:36:03.934029+00:00"}
3 | user | {"id":3,"fname":"Huan","lname":null,"email":null,"age":null,"city":null,"year_birth":null,"created_at":"2021-08-10T07:36:03.934029+00:00"}
1 | admin | {"id":1,"full_name":"Petr Smirnov","email":"petr@test.com"}
2 | admin | {"id":2,"full_name":"Oleg Ivanov","email":"oleg@test.com"}
и в Clojure:
[{:id 1
:type "user"
:entity
{:id 1
:fname "Ivan"
:lname "Petrov"
:email "test@test.com"
:age 30
:city nil
:year_birth nil
:created_at "2021-08-10T10:36:03.934029+03:00"}}
{:id 3
:type "user"
:entity
{:id 3
:fname "Huan"
:lname nil
:email nil
:age nil
:city nil
:year_birth nil
:created_at "2021-08-10T10:36:03.934029+03:00"}}
{:id 1
:type "admin"
:entity {:id 1
:full_name "Petr Smirnov"
:email "petr@test.com"}}
{:id 2
:type "admin"
:entity {:id 2
:full_name "Oleg Ivanov"
:email "oleg@test.com"}}]
При обходе выборки проверим поле type оператором case и вызовем нужный обработчик:
(doseq [{:keys [id type entity]} result]
(case type
"user" (process-user ...)
"admin" (process-admin ...)))
Еще одно замечание касается оператора UNION. Обратите внимание, что мы использовали его -ALL версию. Если опустить частицу ALL после UNION, база попытается убрать дубликаты записей. Чтобы проверить запись на повтор, ее сравнивают с уже накопленными записями. А поскольку тип json не поддерживает сравнение, получим ошибку:
SELECT
u.id AS id,
'user' AS type,
row_to_json(u) AS entity
FROM
users u
UNION
SELECT
a.id AS id,
'admin' AS type,
row_to_json(a) AS entity
FROM
admins a;
-- ERROR: could not identify an equality operator for type json
-- LINE 4: row_to_json(u) AS entity
Чтобы отсечь дубликаты, используйте to_jsonb вместо row_to_json. Эта функция вернет результат с типом jsonb, который поддерживает сравнение. Как следствие, оператор UNION ALL не вызовет ошибку для поля этого типа.
Группировка в базе может быть удачным решением, которое сэкономит время и код. Однако она зависит от объема данных и их структуры. Опробуйте выборку на больших данных (сотни тысяч записей) и оцените время и план исполнения. Если данных нет, сгенерируйте их спекой. В первой книге о Clojure мы рассмотрели, как это сделать модулем gen.
Миграции
С развитием проекта в базе появляются новые таблицы, индексы, триггеры. Чтобы контролировать изменения в базе, придумана концепция миграций. В этом разделе мы рассмотрим ее общие принципы и как они работают в Clojure.
Технически миграция выглядят как .sql-файл. В нем находится код, который создает таблицы и другие сущности базы. Различают up- и down-миграции, они же прямые и обратные. Как правило, прямые описывают новые сущности, например создают таблицу и индексы для нее. Down-миграция совершает обратное: удаляет индекс и таблицу. В редких случаях отмена не требуется, и тогда миграция состоит только из up-части.
Каждая миграция имеет уникальный идентификатор. Чаще всего это дата в формате ISO или время Unix. Идентификатор должен поддаваться сортировке, чтобы однозначно определить порядок миграций.
Миграции похожи на дерево коммитов: с ними базу можно “накатить” или “откатить” до определенной версии. Подобно тому, как с помощью git мы переключаемся на нужную версию кода, базу данных переключают на нужное состояние. Для этого нужно знать, какая миграция текущая и в какую сторону двигаться.
Сведения о миграциях хранится в базе. Как правило, движок миграций создает таблицу migrations примерно такой структуры:
create table migrations (
migration_id text primary key,
created_at timestamp,
description text
);
Таблица запоминает, какие миграции и когда мы применили. При запуске миграций программа читает таблицу и сканирует файлы на диске. Файлы называют по принципу <time/id>-<description>.<up/down>.sql. Объясним значения в угловых скобках:
<time/id>— время или идентификатор, по которому сортируются миграции;<description>— короткое описание миграции;<up/down>— признак прямой или обратной миграции.
Примеры файлов:
20210830075251-create-users-table.up.sql
20210830075251-create-users-table.down.sql
20210831064602-create-profiles-table.up.sql
20210831064602-create-profiles-table.down.sql
Движок группирует файлы примерно в такую структуру:
(def migrations
[{:id 20210830075251
:description "Create users table"
:up "20210830075251-create-users-table.up.sql"
:down "20210830075251-create-users-table.down.sql"}
{:id 20210831064602
:description "Create profiles table"
:up "20210831064602-create-profiles-table.up.sql"
:down "20210831064602-create-profiles-table.down.sql"}])
В зависимости от того, какая версия базы сейчас и что мы хотим сделать, сработают разные алгоритмы. Наиболее частый сценарий — migrate forward, то есть применить миграции, что еще не были обработаны. Технически это значит отфильтровать список миграций по id и выбрать файлы с частичкой up:
(->> migrations
(filter (fn [{:keys [id]}]
(> id current-id)))
(sort-by :id)
(map :up))
;; ("20210831064602-create-profiles-table.up.sql"...)
Каждуя миграция выполняется в транзакции. Если изменения прошли без ошибок, в таблицу migrations попадает номер миграции, которую только что выполнили. Даже если миграция “упала” где-то на полпути, не останется ее промежуточных следов.
При движении в обратную сторону стратегия меняется. Если нужно вернуться к прошлой версии базы, указывают нужный идентификатор, и программа применяет down-файлы, чей идентификатор находится между указанным и текущим. При этом порядок миграций обратный: от новых к старым.
(->> migrations
(filter (fn [{:keys [id]}]
(and (>= id target_id)
(< id current-id))))
(sort-by :id)
(reverse)
(map :down))
У миграций несколько преимуществ. Прежде всего, это автоматизация. Не нужно подключаться к базе и добавлять колонку вручную. Достаточно вызвать прогон миграций на удаленной машине.
При разработке приходится работать с разными версиями кода и, соответственно, базы. Переключать базу должно быть так же удобно, как и ветку в git.
Случается, что изменения в таблицах были неудачны, и это выяснилось после запуска в бою. В этом случае изменения открывают обратной миграцией. Конечно, лучше всего избежать этой ситуации, но иметь средство тоже не помешает.
Наконец, когда схема базы описана в файле, это означает контроль за ней. Проект легче запустить локально, а новичкам проще понять систему таблиц.
Clojure предлагает несколько библиотек для миграций. Мы рассмотрим две из них: Migratus и Ragtime.
Migratus
Проект Migratus состоит из двух частей: библиотеки и плагина к lein. Проще всего начать работу при помощи плагина. Добавьте его в проект:
:plugins [... [migratus-lein "0.7.3"]]
Напишем несколько миграций. В папке resources/migrations создайте пока что пустые файлы:
> mkdir -p resources/migrations
> cd resources/migrations
touch 20210830075251-create-users-table.up.sql
touch 20210830075251-create-users-table.down.sql
Теперь наполним их. Содержимое up-миграции:
CREATE TYPE user_status AS ENUM ('active', 'pending', 'blocked');
--;;
CREATE TABLE users(
id serial primary key,
name text not null,
status user_status not null,
email text
);
--;;
CREATE INDEX idx_users_name ON users(name);
Это прямая (up) половина первой миграции. Она создает тип-перечисление, таблицу users и индекс на имя пользователя. Чтобы откатить эти шаги, поместите в down-файл следующий код:
DROP INDEX idx_users_name;
--;;
DROP TABLE users;
--;;
DROP TYPE user_status;
Обратите внимание, что порядок удаления сущностей обратный. Нельзя удалить индекс и тип-перечисление, пока существует таблица.
Когда миграции готовы, применим их к базе. Для этого укажем параметры подключения. В файле project.clj добавьте ключ :migratus со словарем:
{:store :database
:migration-dir "migrations"
:db {:dbtype "postgresql"
:dbname "migration_test"
:host "127.0.0.1"
:user "book"
:password "book"}}
Ключ :migration-dir указывает папку относительно ресурсов, в которой хранятся миграции. В нашем случае это resources/migrations. В поле :db находится знакомая вам JDBC-спека. Выполните в терминале:
> lein migratus migrate
Вы увидите список миграций, которые плагин применил к базе данных. Подключитесь к ней с помощью psql или другой утилиты — появится таблица users. Кроме того, исследуйте таблицу миграций под названием schema_migrations:
SELECT * FROM schema_migrations;
В ней окажется запись с датой и именем миграции:
id | applied | description
----------------+-------------------------+-----------------------
20210830075251 | 2021-08-28 09:28:26.911 | create-users-table
Повторный запуск миграций ничего не даст, потому что миграция 20210830075251 уже обработана, а новых не появилось. Чтобы откатить изменения, выполните команду rollback:
> lein migratus rollback
Это действие означает откатить последнюю транзакцию. После нее не останется таблицы users и связанных с ней сущностей. Из таблицы schema_migrations исчезнут сведения о миграции 20210830075251, и ее можно будет применить снова. Применение и откат миграций можно повторять сколько угодно раз при условии, что откат удаляет все изменения прямой части.
Несколько запросов в миграции
Обратите внимание на строку --;; между запросами в файлах. С точки зрения SQL это обычный комментарий, однако здесь у него особая роль.
По умолчанию Migratus рассматривает файл как единый запрос и выполняет его с помощью jdbc/execute!. Но не всегда миграцию можно описать одним запросом. В примере выше понадобилось три выражения, чтобы создать перечисление, таблицу и индекс.
Для таких случаев строка --;; служит разделителем запросов. Migratus разбивает файл и выполняет запросы по одному в транзакции. За счет этого не нужно создавать несколько файлов на каждый запрос.
О разделителе часто забывают новички. Это тот случай, который придется запомнить.
Миграции с профилями
При запуске миграций в lein доступны все опции этого инструмента, в том числе профили. С их помощью легко нацелить Migratus на другую базу или папку с файлами. Например, по умолчанию команда
> lein migratus migrate
работает с локальной базой, но с профилем staging подключается к удаленной тестовой машине:
> lein with-profile staging migratus migrate
Для этого задайте иные настройки :migratus в профиле :staging. Обратите внимание, что необязательно копировать их все. Профили lein подлежат глубокому слиянию, поэтому достаточно только отличия от корневых настроек :migratus.
{:profiles
{:staging
{:migratus
{:db {:dbname "prod_db"
:host ~(System/getenv "DB_HOST")
:user ~(System/getenv "DB_USER")
:password ~(System/getenv "DB_PASSWORD")}}}}}
Когда приложение “выкатывают” на тестовой машине, в базе должны быть минимальные данные для проверки. Обычно это пользователь с известным паролем, несколько товаров, покупок и так далее. Поместим их в миграцию, которая выполняется только на удаленной машине. Создайте каталог env/staging/resources/migrations и файл в нем:
-- 20210830075415-add-test-data.up.sql
INSERT INTO users(id, name, status, email, ...)
VALUES
(1, 'Test1', 'active', 'test1@test.com', ...),
(2, 'Test2', 'pending', 'test2@test.com', ...),
(3, 'Test3', 'blocked', 'test3@test.com', ...);
Укажите профилю :staging дополнительный путь ресурсов. Он заменит текущий resources, и папка migrations будет взята из нового пути.
{:resource-paths ["env/staging/resources"]}
Важно понимать, что Migratus ищет файлы только в одной папке: невозможно сделать так, чтобы использовались одновременно resources/migrations и env/staging/resources/migrations. Поэтому миграции накатывают в два этапа: в первый раз основные, во второй – специфичные для текущего окружения, если это необходимо.
> lein migratus migrate
> lein with-profile staging migratus migrate
Параметры подключения
До сих пор мы указывали подключение к базе явно. Это подойдет для локальной разработки, но не промышленного запуска. Чтобы запустить миграции на удаленной базе, передайте ее параметры в переменных среды. Для этого изменим значения словаря :db на формы ~(System/getenv ...):
{:store :database
:migration-dir "migrations"
:db {:dbtype "postgresql"
:dbname ~(System/getenv "DB_NAME")
:host ~(System/getenv "DB_HOST")
:user ~(System/getenv "DB_USER")
:password ~(System/getenv "DB_PASSWORD")}}
Синтаксис ~<form> внутри макроса defproject означет вычислить форму. При запуске lein на месте ~(System/getenv "ENV_NAME") окажется значение одноименной переменной. Чтобы запустить миграции, укажите переменные процессу lein:
> DB_USER=book DB_PASSWORD=secret lein migratus migrate
Мы подробно разобрали конфигурацию и переменные среды в пятой главе первой книги.
Миграции в коде
Иногда миграциями управляют не командой lein, напрямую из Clojure. Для этого Migratus должен быть подключен не виде плагина к lein, а библиотекой:
{:dependencies [[migratus "1.3.5"]]}
Приведем минимальный код, чтобы запустить миграции в коде:
(ns ...
(:require
[migratus.core :as migratus]))
(def config
{:store :database
:migration-dir "migrations"
:db {...}})
(migratus/init config)
(migratus/migrate config)
(migratus/rollback config)
Процесс сводится к простым шагам: объявить конфигурацию, инициировать библиотеку и вызывать функции migrate или rollback с конфигурацией.
Запуск миграций помещают в начало работы программы. Если приложение запущено на нескольких машинах, выполнять миграции должен только один экземпляр. Проще всего задать этот признак переменной среды: всем ложь и только одной машине — истину.
Если проект основан на системе (библиотеки Component и Integrant), создайте компонент Migration, который зависит от базы данных. При запуске (метод start или init-key) он вызывает migratus/migrate с конфигурацией. В поле :db конфигурации указывают компонент базы.
Программные миграции
Как правило, изменения в базе данных описывают на языке SQL. Однако не все действия можно выполнить с его помощью. Предположим, нужно заполнить колонку данными из стороннего источника. В этом случае помогут программные миграции.
Технические подобные миграции — это файлы .edn со словарем. Ключи :up и :down указывают на функции Clojure. Функции принимают один параметр — конфигурацию — и выполняют действия с базой. Код функции остается на ваше усмотрение. С помощью библиотек вы можете читать данные из сети или файлов и записывать в базу. Обратная программная миграция удаляет то, что внесла ее прямая часть.
Пусть таблица requests собирает сведения о просмотрах страниц. У нас есть только IP-адрес пользователя, но хотелось бы знать страну, город и другие данные. Исходные поля таблицы:
SELECT * FROM requests;
id | ip | country_code
----+----------------+--------------
1 | 24.198.249.0 |
2 | 187.33.237.120 |
Создайте файл 20210831070224-update-country-by-ip.edn со словарем внутри:
{:ns book.migrations.update-country
:up-fn migrate-up
:down-fn migrate-down}
Поле :ns указывает на пространство, а :up-fn и :down-fn — на функции прямой и обратной миграций. Технически возможно держать несколько миграций в одном Clojure-модуле, но лучше резделить их.
В файл book/migrations/update_country.clj поместим модуль миграции. Начнем с шапки:
(ns book.migrations.update-country
(:require
[clojure.java.jdbc :as jdbc]
[clj-http.client :as client]))
Код страны получим с помощью сервиса IPLocation по HTTP API. Сервис принимает POST-запрос с адресом и выдает JSON с информацией о нем. Напишем функцию:
(defn get-ip-info [ip]
(:body
(client/post "https://iplocation.com"
{:form-params {:ip ip}
:as :json})))
Прямая миграция выбирает записи с пустым кодом страны и обходит в цикле. Для каждого IP-адреса она выполняет HTTP-запрос и обновляет запись с найденным кодом.
(defn migrate-up [{:keys [db]}]
(let [rows (jdbc/query db "select id, ip from requests where country_code is null")]
(doseq [{:keys [id ip]} rows]
(let [{:keys [country_code]} (get-ip-info ip)]
(jdbc/update! db :requests
{:country_code country_code}
["id = ?" id])))))
Обратная миграция сбрасывает коды стран в NULL:
(defn migrate-down [{:keys [db]}]
(jdbc/update! db :requests
{:country_code nil}
["true"]))
Запустите миграции в терминале. По окончании в таблице requests появятся коды стран:
SELECT * FROM requests;
id | ip | country_code
----+----------------+--------------
1 | 24.198.249.0 | US
2 | 187.33.237.120 | BR
При откате коды пропадут из таблицы.
Код миграции можно улучшить: добавить логирование и перехват ошибок, вести запросы параллельно при помощи pmap. В запрос SELECT дописать оператор FOR UPDATE, чтобы заблокировать запись на время транзакции. Предлагаем читателю поработать над кодом в свободное время.
Ragtime
Коротко рассмотрим вторую библиотеку под названием Ragtime. Ее принцип такой же, что мы рассмотрели выше. Это .sql-файлы в папке, их чтение и обход, таблица с именами обработанных миграций. В отличие от Migratus, Ragtime более гибок: если первый привязан к драйверу JDBC, то Ragtime основан на протоколах, которые легко адаптировать под любую базу, не обязательно реляционную.
Протоколы DataStore и Migration описывают хранилище данных и миграцию. Встроенный пакет ragtime.jdbc расширяет их для JDBC-совместимых баз, как это делает Migratus. За счет этого Ragtime можно применить к разным базам, например Cassandra, MongoDB или Datomic. Работая в Exoscale, автор написал библиотеку миграций для Cassandra на базе Ragtime. Присмотритесь к этой библиотеке, если в проекте участвуют не только JDBC-совместимые базы.
Мы не будем рассматривать Ragtime столь же подробно, как Migratus. К этому моменту читатель достаточно опытен, чтобы разобраться с библиотекой самостоятельно.
Next.JDBC
Библиотека Next.JDBC — это следующий этап в адаптации JDBC для Clojure. Проект ставит следующие цели:
- ускорить обработку выборки. Автор считает неоптимальным текущее проеобразование
ResultSetв коллекцию Clojure. На больших результатах алгоритм действительно замедляется.
-
Обеспечить более тесную связь с Clojure за счет протокола Datafy. Протокол появился в Clojure 1.11 и служит посредником между сущностями Java и Clojure.
-
Предложить более простой и понятный API. Прежняя библиотека различает функции
query,execute!,db-do-commandsи другие; вдобавок она обязывает помнить об их особенностях. Next.JDBC планирует сократить разнообразие API.
Коротко опробуем библиотеку. Подключим ее к проекту:
;; project.clj
[com.github.seancorfield/next.jdbc "1.2.709"]
;; module.clj
(ns ...
(:require
[next.jdbc :as jdbc]
[next.jdbc.sql :as jdbc.sql]
[next.jdbc.prepare :as jdbc.prepare]
[next.jdbc.result-set :as jdbc.rs]))
В отличии от предшественника, Next.JDBC не следует правилу “все в одном файле”. Смысловые части разнесены по отдельным модулям.
Роль источника данных играет объект datasource, который получают из JDBC-спеки. Однажды получив, его передают в функции первым аргументом.
(def db-spec {:dbtype "postgresql" :dbname "test" ...})
(def ds (jdbc/get-datasource db-spec))
Объект datasource хранит открытое соединение и не порождает новое на каждый запрос. Подобную ошибку легко совершить в clojure.java.jdbc, когда JDBC-спека не содержит поля :datasource или :connection, и обращение к базе замедляется на порядок. Содержимое datasource легко проверить функцией bean:
(bean ds)
{:class next.jdbc.connection$url_PLUS_etc$...
:connection #object[...PgConnection 0x352c5229...]
:loginTimeout 0}
Прочитаем таблицу пользователей:
(first (jdbc/execute! ds ["SELECT * FROM users"]))
#:users{:id 1
:fname "Ivan"
:lname "Petrov"
:email "test@test.com"
:age 30
:created_at #inst "2021-08-10T07:36:03.934029000-00:00"}
Обратите внимание, что ключи словарей квалифицированы, то есть содержат пространство с именем таблицы. За счет пространств легко понять, какой таблице принадлежит поле. Пространства работают в том числе для связанных таблиц. Убедимся в этом на примере соединения с профилями:
(first (jdbc/execute! ds ["SELECT * FROM users u, profiles p WHERE p.user_id = u.id"]))
{:users/id 1
:users/lname "Petrov"
:users/fname "Ivan"
:users/age 30
:users/email "test@test.com"
:users/created_at #inst "2021-08-10T07:36:03.934029000-00:00"
:profiles/id 1
:profiles/user_id 1
:profiles/avatar "kitten.jpg"}
Видим, что поля из разных таблиц обрели разные пространства. Это освобождает нас от расстановки псевдонимов вручную:
SELECT
u.id AS "user/id",
u.fname AS "user/fname",
...
p.id AS "profile/id",
...
Важно понимать, что поиск таблицы для поля не бесплатен, а требует отдельного запроса в базу. Пример выше с пользователем и профилем порождает следующий запрос:
SELECT c.oid, a.attnum, a.attname, c.relname, n.nspname,
a.attnotnull OR (t.typtype = 'd' AND t.typnotnull),
pg_catalog.pg_get_expr(d.adbin, d.adrelid) LIKE '%nextval(%' FROM
pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n
ON (c.relnamespace = n.oid) JOIN pg_catalog.pg_attribute a
ON (c.oid = a.attrelid) JOIN pg_catalog.pg_type t ON (a.atttypid
= t.oid) LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid =
a.attrelid AND d.adnum = a.attnum) JOIN (SELECT 32824 AS oid , 1
AS attnum UNION ALL SELECT 32824, 2 UNION ALL SELECT 32824, 3
UNION ALL SELECT 32824, 4 UNION ALL SELECT 32824, 5 UNION ALL
SELECT 32824, 6 UNION ALL SELECT 32824, 8 UNION ALL SELECT 32824,
9 UNION ALL SELECT 32835, 1 UNION ALL SELECT 32835, 2 UNION ALL
SELECT 32835, 3) vals ON (c.oid = vals.oid AND a.attnum =
vals.attnum)
Который, в свою очередь, дает сведения о колонках и таблицах:
oid | attnum | attname | relname | nspname | ?column? | ?column?
-------+--------+------------+----------+---------+----------+----------
32835 | 3 | avatar | profiles | public | f |
32835 | 2 | user_id | profiles | public | t |
32835 | 1 | id | profiles | public | t | t
32824 | 9 | created_at | users | public | f |
32824 | 8 | year_birth | users | public | f |
32824 | 6 | city | users | public | f |
32824 | 5 | age | users | public | f |
32824 | 4 | email | users | public | f |
32824 | 3 | lname | users | public | f |
32824 | 2 | fname | users | public | f |
32824 | 1 | id | users | public | t | t
Если подход кажется вам расточительным, отключите квалификацию полей параметром builder-fn:
(jdbc/execute!
ds
["SELECT * FROM users ..."]
{:builder-fn jdbc.rs/as-unqualified-maps})
Кроме функции as-unqualified-maps, вам доступны as-arrays для векторов и другие обработчики.
Вспомогательные функции get-by-id, find-by-keys, update!, insert! и аналоги перешли в модуль next.jdbc.sql. Они отличаются только тем, что принимают первым параметром datasource:
(jdbc.sql/get-by-id ds :users 1)
(jdbc.sql/insert! ds :users {:id 199 :fname "Test"})
(jdbc.sql/query ds ["select * from users"])
Макрос with-transaction тоже почти не изменился. Он принимает вектор, где первый элемент — символ, а второй — текущий datasource. С первым символом будет связан новый datasource с транзакционным соединением. В примере ниже мы открываем транзакцию с двумя запросами. Первый добавляет товар, второй записывает для него скидку 33%.
(jdbc/with-transaction [tx ds]
(let [{:items/keys [id]}
(jdbc.sql/insert! tx :items {...})]
(jdbc.sql/insert! tx :sales {:item_id id :ratio 0.66})))
Как мы выяснили, поддержка JSON открывает новые горизонты в работе с базой. Next.JDBC предлагает протоколы для чтения и установки нестандартных типов. Подготовим функцию для перевода PGobject в Clojure:
(defn pg->clj [^PGobject pg-obj]
(let [pg-val (.getValue pg-obj)
pg-type (.getType pg-obj)]
(case pg-type
("json" "jsonb")
(json/parse-string pg-val keyword)
;; else
pg-obj)))
и расширим с ее помощью протокол:
(extend-protocol jdbc.rs/ReadableColumn
PGobject
(read-column-by-label [v _]
(pg->clj v))
(read-column-by-index [v _ _]
(pg->clj v)))
Обратите внимание, что у протокола два метода: получить поле по индексу и имени. В нашем случае реализации совпадают. При чтении колонки с типом json(b) получим коллекцию Clojure:
(first (jdbc.sql/query ds ["select * from items"]))
#:items{:id 1
:title "Cap"
:attrs {:size "XL" :color "red" :country "China"}}
Расширим установку параметров. Любая коллекция Clojure станет объектом PGobject с типом json. Вспомогательная функция:
(defn ->pg-object [data]
(doto (new PGobject)
(.setType "json")
(.setValue (json/generate-string data))))
и протокол:
(extend-protocol jdbc.prepare/SettableParameter
clojure.lang.IPersistentCollection
(set-parameter [v ^java.sql.PreparedStatement pr-st idx]
(.setObject pr-st idx (->pg-object v))))
Вставка товара с JSON-атирибутами:
(jdbc.sql/insert! ds :items {:title "t-shirt" :attrs {:size "XXL" :brand "Abibas"}})
#:items{:id 1
:title "t-shirt"
:attrs {:size "XXL" :brand "Abibas"}}
Как видно, интерфейс Next.JDBC в целом похож на предшественника. Функции ведут себя так же, но дают больше контроля за происходящим. Если у вас долгий опыт работы с clojure.java.jdbc, перейти на Next.JDBC будет несложно. Тонкости перехода описаны в Wiki-документации проекта. Там же вы узнаете о других возможностях JDBC.next.
Заключение
Реляционные базы данных основаны на модели реляционной алгебры. Модель описывает отношения между сущностями и операции над ними: проекция, пересечение, объединение и другие. В базах данных модель представлена таблицами и операторами SQL. Как правило, оператор принимает несколько таблиц и возвращает табличный результат.
SQL — строго декларативный язык: в нем нет циклов, переменных и прочих возможностей, доступных в языках программирования. Говорят, что SQL не полон по Тьюрингу. Существуют несколько стандартов SQL, датированных годами: 92, 99, 2008 и другие. Кроме обычных возможностей, современные базы предлагают расширенные операторы и типы данных.
В мире Java доступ к базам обеспечивает библиотека JDBC. Это универсальный интерфейс, который учитывает особенности каждой базы и приводит их к общему виду. JDBC нуждается в драйвере — библиотеке для конкретной базы данных, которая связывает высокоуровневый API с вводом-выводом. JDBC-драйверы доступны не только для классических баз PostgreSQL или MySQL, но и экзотических решений вроде файлов CSV и Excel.
Clojure предлагает легковесную обертку над JDBC — пакет clojure.java.jdbc. В ней доступны емкие и удобные функции для выборки, вставки и других операций с базой. Макрос with-transaction радикально упрощает работу с транзакциями. Библиотека содержит функции для типовых случаев: найти запись по id, по ключам, вставить из словаря и другие.
Чтобы связать типы Clojure с типами базы, расширьте протоколы. С их помощью сделают так, чтобы значение Timestamp из базы становилось DateTime из пакета JodaTime. Полезно обеспечить бесшовную связь с JSON, потому что этот тип несет колоссальные преимущества. Связь типов работает в обе стороны. Для установки параметров служит отдельный протокол, которые приводит типы Clojure в PGObject и другие объекты, известные JDBC.
Наибольшая опасность в SQL исходит от инъекций. Так называют прием, когда в запрос попадает вредоносный текст, например чтение системных таблиц или всегда истинное условие. Вероятность инъекции высока, если вы строите запросы конкатенацией (“склейкой”) строк, а также внедряете параметры в тело запроса. Тем же опасен и текст с кавычками: их умелая расстановка может нарушить логику запроса. Чтобы обезопасить запрос от кавычек, библиотеки экранируют их на этапе установки параметров.
В боевых проектах вы столкнетесь с тем, что запросы занимают много места и затрудняют чтение кода. Кроме того, SQL неудобно строить по условию, когда заранее неизвестно, нужно ли делать отбор по некоторому полю или нет. Здесь вам помогут библиотеки. Некоторые из них выносят запросы в файл и порождают функции, которые выполняют эти запросы. Другие строят SQL из коллекций — комбинаций списков и словарей.
Как правило, реляционные базы возвращают данные в виде плоских таблиц. На практике нас интересуют вложенные данные, например магазин и его товары, публикации и комментарии к ним. Существует два способа сгруппировать данные. Первый — получить плоский ответ и обработать на стороне Clojure. Второй — выполнить группировку в базе функциями array_agg или аналогами. Выбор способа зависит от окружения, объема данных и ваших навыков: в какой среде вы чувствуете себя уверенней.
Миграциями называют историю изменений базы. Как правило, это sql-файлы, которые добавляют таблицы, поля или индексы. Специальный код обходит эти файлы и применяет к базе. Отдельная таблица хранит имена обработанных миграций, чтобы не выполнить их повторно. Миграции автоматизируют рутину, позволяют оперативно откатить неудачные изменения. В локальной разработке ими переключают схему в соответствии с версией кода.
Библиотека clojure.java.jdbc — не единственная для работы с базой данных в Clojure. Обратите внимание на ее преемника JDBC.next. Новая библиотека работает с данными эффективней, что заметно на больших выборках. JDBC.next тесно связана с Clojure за счет встроенного протокола Datafy. Ее функции дают больше контроля за тем, что происходит с базой.
Нашли ошибку? Выделите мышкой и нажмите Ctrl/⌘+Enter