Funq — различия между версиями

Текущая версия на 15:42, 20 июня 2016

Funq — концепция околофункционального, гибкого языка запросов к реляционным базам данных, чуть-чуть похожего на LINQ и призванного заменить SQL для сложных запросов, оптимизируя их ещё на стадии построения, и давая возможность наложить любое преобразование на запрос в любой момент, к тому же, скрывая диалекты, свойственные отдельным СУБД.

Идея

Сейчас стандартом языка запросов к реляционным базам данных является SQL — громоздкий, некрасивый и тяжёлый язык. SQL — декларативный язык, но он не оперирует сущностями каких-то явных типов, а синтаксис команд очень жёсткий, то есть, негибкий. Например, чтобы автоматически наложить на уже созданный запрос какое-то ограничение, нужно сначала понять, в какое место и в каком виде его добавлять, а способа адекватно и надёжно сделать это без синтаксического анализа запроса не существует.

Таким образом, было бы неплохо создать не SQL-подобный язык запросов, который был бы к тому же элегантен и удобен для использования в клиентских приложениях. Например, можно максимально приблизить формирование запросов к заданию выражения над таблицами, как алгебраическими объектами, с использованием различных операций реляционного исчисления — JOIN-ов, ограничений, проекций и т.д, а также других нереляционных преобразований — они необходимы потому, что возможностей построения запросов разработчики различных СУБД создали великое множество, а на замену SQL годится по меньшей мере равномощный ему язык. Сразу оговоримся, что основные проблемы написания запросов к базам данных связаны с запросами выборки. Запросы удаления/обновления часто используют базу просто как хеш-таблицу, а запросы вставки и DDL-запросы просто имеют достаточно бедный синтаксис. Их также нужно реализовать, как часть языка запросов, но основной интерес представляют запросы выборки. Ими-то мы в первую очередь и займёмся.

Например, рассмотрим вот такой SQL-запрос. Можно заметить, что это не чистый SQL — здесь ещё присутствуют подстановки имён таблиц из хеша $t и имён полей $child и $parent.

SELECT t0.*, (t3.`$parent` IS NULL) AS `_direct`
FROM `$t->{album2album}` AS t1
INNER JOIN `$t->{albums}` AS t0 ON t0.`id`=t1.`$child` AND $t0_where
LEFT JOIN (`$t->{album2album}` AS t3
INNER JOIN `$t->{album2album}` AS t4)
ON t3.`pid`=? AND t4.`pid`=? AND t3.`$parent`=t1.`$parent`
AND t3.`$child`=t4.`$parent` AND t4.`$child`=t1.`$child`
WHERE t1.`$parent`=? AND t1.`pid`=?
GROUP BY t0.`id`
HAVING `_direct`=1
ORDER BY t0.`ord` DESC, t0.`name`
LIMIT ?, ?

А теперь попробуем представить, как этот запрос должен был бы выглядеть на языке Funq:

(album2album as t1)
.where (t1.?parent=? and t1.pid=?pid)
.join ((albums as t0).where(t0.id=t1.?child and t0.?t0where))
.join[left] ((album2album as t3).where(t3.?parent=t1.?parent and t3.pid=?pid))
.join ((album2album as t4).where(t4.?parent=t3.?child and t4.?child=t1.?child and t4.pid=?pid))
.select (t0.*, (t3.?parent is null) as t1._direct)
.hint[calc-found-rows]
.group (t0.id)
.where (t1._direct=1)
.order (t0.ord desc, t0.name)
.limit (?offset, ?limit)

Заметно две основных идеи: во-первых, логически после применения каждого преобразования .something() к отношению мы снова получаем отношение, имеющее все те же самые методы; и во-вторых, язык должен уметь интерполировать различные конструкции в различные места запросов.

Вообще-то, внезапно можно осознать, что relation.something().something().something() — функциональный стиль программирования, а точнее, что это очень похоже на Fluent Interfaces. Собственно, они и сами являются пародией на ФП, хотя и довольно жалкой.

Ещё можно осознать, что по сути Funq по сути нужен только для тех СУБД, которые не могут хорошо оптимизировать бесконечно длинные цепочки подзапросов. Есть, правда, подозрение, что во многих случаях это ровно ВСЕ СУБД, но в любом случае к таким относится как минимум MySQL.

Кроме того, можно вспомнить язык LINQ (Language INtegrated Query) от Microsoft. Если в LINQ пользоваться только объектным стилем задания запроса, то LINQ тоже станет похож на Funq. Другое дело, что на практике делать именно так в LINQ практически невозможно и в любом случае неудобно. Важно также, что и цель у создателей LINQ изначально была другая — встроить язык запросов в .NET с помощью обычных его средств и научить языки .NET работать через одни и те же классы и интерфейсы с базами данных, XML-файлами, да и вообще любыми источниками данных, вплоть до обычных массивов.

А ещё, раз уж мы вспомнили про LINQ, можно сразу заметить, что описанная идея позволяет легко реализовать «язык запросов» на объектном интерфейсе в родном синтаксисе многих языков программирования. Конкретно автора (меня — VitaliyFilippov 13:00, 13 июля 2009 (UTC)) интересует язык Perl, он-то и будет рассмотрен в данной статье.

Нужно отметить, что формирование запроса в родном синтаксисе языка имеет и недостаток — затруднение кэширования скомпилированных запросов. Из-за этого может пострадать производительность; однако, если объекты запросов кэшируются самим приложением, этого не произойдёт. Этот недостаток говорит нам о том, что имеет смысл реализовать одновременно и объектный подход к формированию запроса, и просто трансляцию из Funq-кода.

Другие нужные фичи

• Автоматическая трансляция имён таблиц, и вообще запросов к таблицам. Это значит, что всегда можно переопределить

  $dbh->query("some_table")

  так, что на самом деле этот конструктор будет возвращать не новый, девственно чистый, объект запроса к таблице some_table, а некоторый другой объект запроса — с заданными дополнительными ограничениями, к другой таблице, или вообще результат сложного запроса (хотя так делать, наверное, не надо). То есть, по сути, «программные представления».
• Именованные параметры (placeholder’ы) — задаются как ?name — и автоматическое определение имени из контекста в простых выражениях. Например, параметр из условия config=? будет, очевидно, называться config, если только уже не задан другой параметр с таким именем. Кроме того, один и тот же явно именованный параметр можно использовать в различных местах выражения и даже в разных операторах в процессе формирования запроса.
• Персонализация условий заданием имени таблицы (или имён таблиц) — к примеру, если в запросе записано условие exif.?placeholder, а значение placeholder-а равняется .file=? and .config=?, то в запрос будет подставлено преобразованное условие exif.file=? and exif.config=?. Если аналогично требуется сделать для нескольких таблиц (ситуация редкая), синтаксис усложняется — теперь условие в запросе, например, может звучать как [exif, configs].?placeholder, а в placeholder-е, например, как _1.file=? and _1.config=_2.id. При этом _1 будет заменено на имя первой таблицы, _2 — на имя второй, и так далее.
• Наличие хинта (подсказки) для последовательного выполнения подзапросов, то есть выполнения сначала внутреннего запроса, а потом внешнего.
• Автоматическое кэширование компилируемых из Funq-кода (то есть **не** формируемых объектно) запросов. Кэширование детализированных условиями и порядками сортировки/группировки запросов. Следует отметить, что если в процессе выполнения появляется слишком много (неограниченное относительно вариации входных данных количество) детализированных условиями и порядками запросов, значит, что-то //«Defective By Design»//, а точнее, кто-то забыл про placeholder’ы…
• Автоматическая проверка соединения, переподключение к БД и пересоздание кэшированных SQL-объектов запроса в случае необходимости.
• Поддержка разделения соединённых отношений на отдельные хеши в результатах запроса.
• Поддержка различных диалектов языка SQL.
• Поддержка возможности тихо игнорировать заданные при вставках/обновлениях несуществующие поля.

Статус реализации

На данный момент из запланированных идей реализованы (обзор исходных кодов):

• Собственно работающий компилятор запросов ;) начал с MySQL. Компилятор с полной поддержкой синтаксиса SELECT, с корректной оптимизацией / добавлением подзапросов, разрешением спорных ситуаций по семантике, поддержкой хинтов типа CALC_FOUND_ROWS, SQL_CACHE и т. п., заменой имён таблиц и прочими.
• Связывание именованных параметров (?name) со скалярами, с массивами или массивами массивов значений, с выражениями или подзапросами.
• Корректное кэширование всех артефактов трансляции, чтобы не мучаться этим постоянно.
• «Аппликация» имён таблиц к выражениям (замена `_` на имя нужной таблицы при подстановке выражения вместо `table`.?expression.
• Автоматическое контекстное именование параметров, если имя не указано.
• Возможность разделения каждой строки на несколько хешей, чтобы выбирать отдельные строки одновременно из нескольких таблиц.
• Удобные функции получения результатов запросов (в основном аналогично DBI): успешность выполнения / массив хешей / массив разделённых хешей / массив массивов / хеш хешей / одна строка / одно значение. Или ручками — тоже можно.
• Разделение функционала на «ядро» и «драйверы».
• Передача доп. информации типа SELECT FOUND_ROWS() — в случае MySQL это функции соединения last_insert_id(), thread_id(), found_rows(), affected_rows().
• Поддержка запросов вставки/обновления/удаления.
• Возможность программного последовательного выполнения подзапросов — сначала внутреннего, а потом внешнего, с программной передачей результата первого во второй. В MySQL бывают ситуации, когда это ускоряет выполнение запроса в разы.

Чего пока нет даже в списке TODO:

• Автоматической генерации алиасов, например, для подзапросов, если они не задаются явно.
• Поддержки подстановки хешей в текст запроса (например, в виде field=value AND field=value).
• Нет ORM’а, и даже не обдуман. А возможно, стоит. А возможно, и не стоит.
• Поддержки операции вставки в отношения, выраженные посредством Funq-запросов.

Список TODO:

• Поддержка автоматического удаления join’ов на «ненужные» таблицы, если ни одно из их полей не указано в запросе. Полезно, например, для какой-нибудь Багзиллы, где есть таблица bugs с огромной кучей полей, которые по-разному хранятся. Соответственно, можно будет просто определить «супертаблицу» со всеми-всеми полями и только указывать, какие мы хотим, а какие не хотим, и Funq бы само удалило лишние join’ы.
• Тесты (!)
• Source-фильтр чтобы писать $dbh->do(funq(table->where(«config=?»)->order()), { arg => value, arg => value, … }), хотя полную функциональность так не реализуешь, так как неизвестен тип подставляемых значений.
• Найти места с неочевидным синтаксисом типа передачи параметров-ссылок на скаляр в качестве выражений и т. п., устранить все такие неочевидности введением методов.
• Возможно, не делать трансляцию SELECT * в список полей, а только логически передавать во внешний запрос.
• Простые методы вставки (наподобие VMX::Common::insertall_hashref) / мульти-обновления (для MySQL, INSERT … ON DUPLICATE KEY SET …).
• Все TODO и FIXME из кода.

Синтаксис Funq

Рассмотрим Funq на примере его Perl-реализации: модулей Funq::DBI (объект соединения) и Funq::Query (объект запроса). Под $dbh везде понимается объект соединения, а под $query — объект запроса.

Формирование запроса

Все методы новый объект запроса. Объекты по сути своей немутабельны.

$dbh->query($table[, $alias])

Конструирует и возвращает новый объект запроса. Параметр $table может сам быть объектом (под)запроса.

$query->clone

Возвращает клон объекта запроса $query. «Такой же, только другой», не имеющий никаких ссылок на старый объект или его части.

Унарные преобразования

$query->where($condition[, $condition2, ...])

Добавляет все переданные ограничения (в виде строк) к объекту запроса $query.

$query->select($field_list)

Задаёт список столбцов для объекта запроса. Может включать вычислимые столбцы и placeholder’ы.

$query->group($order_expression)

Группировка отношения $query по выражению порядка $order_expression.

$query->order($order_expression)

Сортировка отношения $query по выражению порядка $order_expression.

$query->limit([$offset, ]$limit)

Выбор максимум $limit строк из отношения $query, начиная с $offset или с первой же строки.

$query->hint($hint)

Задаёт «подсказку» к выполнению запроса транслятору и самой СУБД. Регистр значения не имеет.

$query->distinct

Наложение на $query ограничения уникальности строк.

$query->count

Возвращение количества строк в отношении $query вместо самих строк (SELECT COUNT(*)).

$query->value

Выборка только первого столбца первой строки из $query.

$query->curry(\%bind)

Задаёт значения некоторых (или всех) placeholder’ов, «детализируя» запрос. Именованные placeholder’ы с одним и тем же явно заданным именем подставляются во всю историю формирования запроса. Вместо curry можно использовать слово set или bind.

$query->update(@exp)

Превращает запрос из запроса выборки в запрос обновления. Передаваемый массив @exp содержит части выражения, содержащего новые значения полей. При выполнении запроса непустые части соединяются через запятую. Отдельное выражение может содержать в себе placeholder’ы или, что важно, само являться одним placeholder’ом, например, «?values». Массив @exp может вовсе не содержать ни одного значения, в этом случае («по умолчанию») в качестве update-выражения принимается значение параметра ?update;

$query->delete(@tables)

Превращает запрос из запроса выборки в запрос удаления. Если запрос содержит несколько соединённых таблиц, то необязательно удалять подходящие строки из всех этих таблиц — с помощью параметра @tables можно выбрать лишь несколько из них;

$query->insert_into($table)

Превращает запрос в запрос вставки выбранных строк в таблицу $table. Параметр $table обязательно должен быть именем таблицы. В зависимости от СУБД, может принимать дополнительные параметры — в случае с MySQL это может быть LOW_PRIORITY или DELAYED (см. INSERT MySQL);

$query->replace_into($table)

(MySQL). Превращает запрос в запрос замены выбранных строк в таблицу $table: Аналогично insert_to, но при этом все имеющиеся в таблице записи со значениями полей, входящих в первичный или уникальный ключ, равными значениям одной из вставляемых строк, предварительно удаляются (см. REPLACE MySQL).

Внимание! Методы update и delete могут быть применены к запросу только один раз, не могут сочетаться, не могут быть применены после insert_into или replace_into, и аннулируют действие на запрос методов select, group, order, distinct, count, value, включая их предыдущие применения к запросу (!). Кроме того, невозможно применение методов update и delete к запросам, имеющим подзапросы в качестве derived table. При попытке любого из таких запрещённых действий генерируются предупреждения, а вызов игнорируется.

Бинарные преобразования

$query1->union($query2)

Конструирует объединение запросов $query1 и $query2. Не путать с соединением.

$query1->union_all($query2)

Конструирует неуникальное объединение запросов $query1 и $query2. Не путать с соединением.

$query1->intersect($query2)

Конструирует пересечение запросов $query1 и $query2.

$query1->intersect_all($query2)

Конструирует неуникальное пересечение запросов $query1 и $query2.

$query1->except($query2)

Конструирует разность запросов $query1 и $query2. Не реализовано в MySQL.

$query1->except_all($query2)

Конструирует неуникальную разность запросов $query1 и $query2. Не реализовано в MySQL.

$query1->join($query2)

Конструирует внутреннее соединение (Inner Join) запросов $query1 и $query2.

$query1->leftjoin($query2)

Конструирует левое соединение (Left Join) запросов $query1 и $query2.

$query1->rightjoin($query2)

Конструирует правое соединение (Right Join) запросов $query1 и $query2.

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

Следующее семейство функций предназначено для выполнения запроса. Их разница в формате возврата строк.

Если %bind не детализирует запрос полностью, то есть так, чтобы незаданных placeholder’ов не оставалось, все функции возвращают undef или пустой список, при этом объект запроса не изменяется. Здесь идея следующая — если передан неполный хеш, значит, кто-то ошибся. Значит, нужно дать ему возможность исправиться и выполнить запрос ещё раз, не создавая объект запроса заново.

Для выполнения запроса нужно сначала выполнить следующую функцию запроса:

$stmt = $query->stmt(\%chain_bind)

Компилирует запрос и получает его «полуфабрикат». При передаче в %chain_bind каких-либо параметров выполняет подзапросы и подставляет результаты их выполнения в сам запрос.

Описание функций (по убыванию популярности):

$stmt->hasharray(\%bind, $max_rows)

Выполняет запрос и возвращает ссылку на массив хешей.

$stmt->arraylist(\%bind, $max_rows)

Выполняет запрос и возвращает ссылку на массив массивов.

@row = $stmt->row(\%bind)

Выполняет запрос и возвращает первую строку в виде списка.

$stmt->do(\%bind)

Просто выполняет запрос.

$stmt->scalar(\%bind)

Выполняет запрос и возвращает значение первой колонки первой строки в виде скаляра.

$stmt->arrayref(\%bind, $slice, $max_rows)

Выполняет запрос и возвращает результат в виде ссылки на массив. Параметры $slice и $max_rows работают так же, как в DBI::selectall_arrayref.

@col = $stmt->col(\%bind)

Выполняет запрос и возвращает первую колонку в виде списка или ссылки на массив, в зависимости от контекста вызова (wantarray или нет).

$hash = $stmt->hash(\%bind, $key)

Выполняет запрос и возвращает хеш хешей. Вложенные хеши размещаются по ключам, имеющим значения колонок, указанных в $key. Если $key = [ "a", "b" ], то верно, что $hash->{$row->{a}}->{$row->{b}} = $row.

$stmt->multihasharray(\%bind, $multi, $separator)

Выполняет запрос и возвращает ссылку на массив массивов хешей или массив хешей хешей. Предназначено для выборки нескольких наборов полей в одном запросе. Эти наборы нужно разделять колонкой-разделителем со специальным именем $separator, по умолчанию равный «_» (символу подчёркивания). Параметр $multi может быть либо ссылкой на массив строк — тогда multihasharray() вернёт ссылку на массив хешей хешей, где соответствующий элемент внешнего хеша будет иметь ключ, равный соответствующему элементу массива $multi, либо не ссылкой на массив вообще и тогда multihasharray() вернёт ссылку на массив массивов хешей.

$row = $stmt->rowhash(\%bind)

Выполняет запрос и возвращает первую строку в виде ссылки на хеш.

($sql, @bind) = $stmt->sql(\%bind)

Выполняет запрос и возвращает либо массив, либо ссылку на массив (в зависимости от контекста вызова), из двух элементов — строки транслированного в SQL запроса и массива bind-значений для подстановки на места '?' в тексте запроса.

Ручное чтение результата

Результат выполнения запроса также можно читать построчно:

$res = $stmt->execute(\%bind)

Выполняет запрос и возвращает объект результата для получения из него строк.

$res->fetchrow_array()

Получает следующую строку результата в виде списка.

$res->fetchrow_arrayref()

Получает следующую строку результата в виде ссылки на массив.

$res->fetchrow_hashref()

Получает следующую строку результата в виде ссылки на хеш.

$res->finish()

Завершат выборку из объекта результата.

Детали реализации

Ниже приведены некоторые размышления, а также некоторые детали внутреннего устройства Funq.

Использование подзапросов

В родном синтаксисе языка возникает небольшая проблема с подзапросами — внутри строк подзапросы задать невозможно. Подзапросы могут возникать в трёх местах:

• в SELECT как поле,
• в FROM как таблица, из которой делается запрос («derived table»),
• в WHERE как часть условия — скаляр или список значений для IN / NOT IN.

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

С синтаксисом п.2 (derived tables) проблем не возникает: нужно только передать в конструктор нового запроса не имя таблицы, а объект запроса. Но зато с п.2 возникает первая проблема трансляции — при перемещении условий, порядков сортировки и т. п. вовнутрь/наружу подзапроса из derived table нужно подменять имена полей, которые, кроме того, могут ещё и дублироваться, называясь одинаково у разных таблиц — если во внешнем запросе такое дублирование MySQL разрешает, то во внутренних — нет. Отсюда следует, что явное или неявное использование подзапроса с выборкой всех полей из нескольких таблиц требует от транслятора знания схемы БД и явного перечисления всех этих полей.

Пп.1 и 3 имеют общую черту — в обоих пунктах в случае скаляра мы имеем проблему интерполяции подзапроса в выражение, задаваемое строкой. Решение проблемы следующее: использование параметров (placeholder-ов) — замена подзапросов на '?' с последующим наложением дополнительного условия на объект запроса (в некотором роде каррирование (currying) запроса). Условие заключается в том, что на место этого '?' надо подставить передаваемый объект подзапроса.

Программные представления

Что такое представления (views)? Реально это представление подзапросом некоторой логической таблицы, прозрачно подставляемое во все места её использования. Ясно, что проще всего сформировать подзапрос и подставлять его во все места использования представления. Однако этот путь очень неоптимален — во многих случаях использование подзапроса не требуется. Получается, что суть трансляции представлений — оптимизация путём удаления подзапросов к реальным таблицам. А теперь — опа! Посмотрим шире: вообще говоря, всю работу транслятора языка Funq можно рассматривать как оптимизацию цепочки подзапросов, ведь каждое следующее преобразование применяется так, как если бы это был простой запрос из предшествующей ему цепочки таких же простых подзапросов. А весь процесс оптимизации этой цепочки сводится к рассмотрению всех подзапросов, начиная с самых внутренних, и исследования следующего вопроса: можно ли объединить подзапрос и внешний по отношению к нему в один?

Порядок преобразований

В SQL синтаксис жёсткий — каждая часть запроса должна стоять именно на своём месте. В Funq синтаксис гибкий — любое преобразование может быть применено к запросу в любой момент. Поэтому Funq имеет проблему, которой не имеет SQL: порядок вызова методов и его последствия — неявные подзапросы, генерируемые транслятором.

То есть, нам нужно решить вопрос: в каких ситуациях транслятор должен сгенерировать неявный подзапрос? Во-первых, очевидно, что все преобразования, кроме join, where, group, order и limit, логически имеют характер задания объекту запроса некоторого флага или значения некоторого свойства. А вот с преобразованиями всё интереснее — в зависимости от их порядка (рассмотрим их попарные сочетания) может быть три результата:

• Порядок вызова не имеет значения. Это справедливо для пар <where,where> (условия умножаются), <where,join>, <where,group> и <where,order> (момент применения условия where не играет роли), <order,order> и <limit,limit> (второй вызов затирает первый).
• Существует только один разрешённый порядок вызова, а другой сразу приводит к неявному подзапросу. Это справедливо для пары <group,order>, так как есть разница между сортировкой агрегированных строк и группировкой отсортированного отношения, а в SQL-запросах ORDER BY обязан идти после GROUP BY.
• Требуется дополнительная информация, чтобы транслятор смог точно понять смысл последовательности вызовов функций. Это справедливо для пар <where,limit>, <group,limit> и <order,limit> (where/group/order после limit — имелись ли ввиду преобразования ограниченного отношения?), а также пары <group,group> (имелась ли ввиду повторная группировка?).

Отдельно отметим пары с join — в отличие от остальных преобразований, join выполняется над несколькими отношениями.

<join,order> — здесь, если порядок задан только у одного из отношений, участвующих в соединении, то порядок вызова join и order роли не играет. Если же порядки заданы у обоих отношений, то семантически они должны конкатенироваться — то есть сортировка сначала должна идти по одному порядку, а потом по другому. Однако по какому из них сортировка должна идти сначала — в случае с обычным (не левым/правым) соединением неизвестно, отношения-то равноправные! Таким образом, это тоже требует уточнения.

<join,group> — выполнение соединения с агрегированным отношением эквивалентно агрегированию соединения отношений. Если задуматься, здесь можно было и проблему встретить — если вспомнить стандарт SQL, то в нём любое сравнение двух NULL-значений даёт снова NULL, то есть пустые значения несравнимы. Казалось бы, из этого могло последовать и аналогичное поведение GROUP BY: все строки с NULL в колонке, по которой идёт группировка, могли бы попасть в отдельные группы. Однако именно для GROUP BY стандарт вводит исключение: именно в GROUP BY все NULL-значения признаются равными. Отсюда следует, что группировка до соединения семантически эквивалентна группировке после соединения. Отсюда вытекает и заключение: если группировка задана у одного из группируемых отношений, проблем нет. Если группировка задана у обоих отношений, мы получаем проблему порядка применения группировок, не говоря уже о том, что становится необходимым использование подзапроса — группировки-то две.

<join,limit> — вообще говоря, экзотика. Не очень понятно, зачем это (выполнение соединения с ограниченным отношением) может потребоваться, и что разработчик таким образом может хотеть выразить. С одной стороны, если рассматривать соединение с ограниченным отношением в терминах реляционного исчисления, всё банально — просто следует сначала удалить лишние строки, а потом выполнить обычную операцию соединения. Но если мыслить в терминах Funq, можно заметить, что синтаксис запроса, участвующего в правой части join-а, аналогичен синтаксису подзапроса! Действительно, в некотором смысле join можно представить себе как «подзапрос», только возвращающий несколько строк одного отношения для каждой строки другого отношения. Если размышлять именно так, то выражение, участвующее в операции соединения, представляет собой именно список строк одного отношения для каждой одиночной строки другого отношения! А тогда получается, что ограничения внутри соединений означают запросы вроде «выбрать первые 5 (по алфавиту) городов каждой страны». Такая задача нормальными средствами в SQL не решается — а решается только через дополнительное соединение с той же таблицей и дополнительную группировку (см. пример). По крайней мере, всё это применимо к MySQL — в продвинутых СУБД типа Oracle и PostgreSQL наверняка можно придумать какой-либо обходной манёвр, вроде выборки вложенной таблицы как отдельного поля каждой строки.

Сочетания преобразований

Выбрано следующее поведение пар преобразований:

• query.order().group() приводит к подзапросу и группировке отсортированного отношения;
• query.group().group() приводит к подзапросу и двойной группировке;
• query.limit().where() ≡ query.where().limit(). чтобы отсортировать результаты limit(), нужно либо воспользоваться подзапросом, либо использовать запись query.limit().wrap.where();
• query.limit().order() ≡ query.order().limit() — аналогично п.3;
• query.limit().group() ≡ query.group().limit() — аналогично п.3;
• join(query1.order(order_1), query2.order(order_2)) ≡ join(query1,query2).order(order_1,order_2);
• join(query1.group(group_1), query2.group(group_2)) ≡ join(query1,query2).group(group_1).group(group_2);
• join(query1.where(where_1), query2.where(where_2)) ≡ join(query1,query2).where(where_1 and where_2);
• join(query1.limit(limit_1), query2.limit(limit_2)) соединяет ограниченные отношения, так как всё-таки задача выбора «первых 5 городов для всех стран» на SQL решается неоптимально;
• query.where(where_1).where(where_2) ≡ query.where(where_1 and where_2);
• query.group().where() ≡ query.where().group();
• query.order().where() ≡ query.where().order();
• query.order(order_1).order(order_2) ≡ query.order(order_2);
• query.limit(a, b).limit(c, d) ≡ query.limit(a+c, min(b-c, d)).

Оптимизация подзапросов

Теперь рассмотрим работу оптимизатора как оптимизацию последовательности подзапросов. Вернее, последовательности вложенных подзапросов и join’ов. Имеются ввиду только подзапросы типа «derived table» — подзапросы, выступающие в ролях вычисляемых полей, а также участвующие в сравнениях подзапросы оптимизируются как отдельные запросы.

Подзапросы бывают явные и неявные. Явные подзапросы — это либо представления, либо явно указанные подзапросы. Неявные подзапросы всегда анонимные, явные запросы — не обязательно. Анонимный подзапрос — это подзапрос, не имеющий собственного псевдонима в Funq-коде. В SQL-коде псевдонимы есть у всех подзапросов, но для анонимных они генерируются автоматически, а для именованных — задаются разработчиком. Подстановки имён таблиц в коде запроса транслятор осуществляет автоматически как для анонимных, так и для именованных подзапросов.

Любой Funq-запрос — последовательность подзапросов и соединений. Все унарные операции (преобразования) Funq-запросов относятся к подзапросам, а единственной многоарной (а на самом деле, бинарной) операцией является соединение (join). Задача транслятора — минимизировать количество подзапросов в генерируемом SQL-коде. Задача решается с помощью описания формальных правил перестановки Funq-подзапросов и их группировки в отдельные SQL-запросы.

Для имён операций ниже используются следующие сокращения: J (join), U (union), I (intersect), E (except), W (where), S (select), G (group), O (order), L (limit), D (distinct), C (count).

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

Правила группировки пар могут иметь следующий смысл (в скобках — символы легенды):

• группировка возможна (+)
• группировка может быть возможна, но требуются дополнительные действия транслятора (*)
• группировка возможна, при этом одна из операций заменяет другую (O)
• группировка невозможна (—)
• спорная ситуация (?)

Деление подзапросов на явные и неявные служит для разрешения неоднозначных ситуаций при трансляции. Пусть рассматривается последовательность двух подзапросов, и её семантика неоднозначна — один вариант предполагает ликвидацию подзапроса, а второй требует его сохранения. Тогда, если внутренний подзапрос явный, транслятор предпочитает второй вариант поведения и подзапрос сохраняется. Если же внутренний подзапрос неявный, транслятор предпочитает первый вариант поведения и группирует подзапросы, создавая из них один SQL-запрос. Применение каких-либо преобразований после count смысла не имеет, так как известно, что оно всегда возвращает отношение 1x1. В принципе, count можно было вообще не рассматривать, так как query.count есть синоним для query.group(1).select(count(*)), однако «пусть будет».

Операция соединения — бинарная. Поэтому здесь правила имеют следующий характер: пусть задана пара операций, присутствующих в левой и правой частях подзапроса. Можно ли переместить эти операции изнутри соединения наружу? Если задуматься, становится понятно, что таблицу здесь задавать не надо, а достаточно только задать операции, которые можно переставлять с операцией соединения. Такими операциями являются where, select и order.

Теоретико-множественные операции UNION, INTERSECT и EXCEPT — тоже многоарные. Причём запросы с ними уникальны в том смысле, что для оптимизации нужно не вытаскивать наружу «общие» части таких запросов, а наоборот, дублировать внешние условия в каждом из подзапросов.

Итак, таблица правил группировки (строки — внешние операции, столбцы — внутренние операции):

Пояснения к таблице:

JS

может потребоваться подстановка выражений вычисляемых полей в операции внутри JOIN.

JO

в случае соединения двух отсортированных таблиц требуется соединение их порядков сортировки слева направо в один.

WU, WI

внешний WHERE должен быть дублирован во все внутренние подзапросы.

WE

достаточно переместить внешний WHERE только в первый подзапрос.

WG

where, указанный после group, фильтрует агрегированные строки, то есть становится HAVING.

WL

имеется ли ввиду фильтрация ограниченного отношения? — ситуация странная, потому и спорная.

SU, SI, SE

внешний SELECT должен быть дублирован во все внутренние подзапросы.

SS

может потребоваться подстановка выражений вычисляемых полей внутреннего select’а во внешний.

GS

может потребоваться подстановка выражений вычисляемых полей select’а в GROUP BY.

GL

имеется ли ввиду группировка ограниченного отношения? — ситуация странная, потому и спорная.

OO

внутренний order by смысла не имеет.

OL

имеется ли ввиду сортировка ограниченного отношения? — ситуация странная, потому и спорная.

LL

требуется подстановка выражений на место границ.

DL

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

DD

внутренний distinct смысла не имеет.

OU, OI, OE

не совместимо, хотя MySQL и позволяет делать «общий ORDER» без подзапроса. Проблема здесь следующая: (..) UNION (..) не присваивается алиас, поэтому непонятно, как ссылаться на его поля в ORDER’е. LIMIT же на поля не ссылается, поэтому плевать.

Подзапросы и параметры

При трансляции подзапроса все параметры внутренних подзапросов «вытаскиваются» наружу. По умолчанию параметры внутренних и внешнего запросов, имеющие одинаковые имена, отождествляются. Однако, если запрос содержит два подзапроса, и у этих подзапросов заданы разные значения параметра с одним и тем же именем, этот параметр у внутренних запросов переименовывается так, чтобы имена не совпадали ни у данного параметра внешнего запроса, ни у данного параметра внутренних запросов. Иными словами, в этом случае параметры более не отождествляются, а становятся независимыми. Вообще говоря, такая мера рассчитана не на нормальное поведение, а на исключительные ситуации, которых нужно стараться избегать в нормальном коде.

Типы параметров

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

Скаляр

Передаётся как обычное значение, как скаляр. Подставляется вместо реальных параметров '?' в SQL-код (как правило, самой СУБД).

Массив

Передаётся как ссылка на массив. Интерполируется как длинный список '?'-ов через запятую, а в СУБД для подстановки передаются значения.

Подзапрос

Передаётся как объект запроса. Может возвращать одну или несколько строк, интерполируется транслятором прямо в SQL-код запроса.

Выражение

Передаётся как ссылка на строку. К этому типу, кроме настоящих выражений, предназначенных для подстановки в предикаты и выражения вычисляемых полей, также относятся имена полей и таблиц, а также определения порядков сортировки. Имена полей и таблиц в выражениях должны либо состоять только из символов [a-zA-Z0-9_], либо экранироваться `обратными кавычками`. Интерполируются прямо в код запроса, но специальным образом — названия таблиц и полей заменяются на «адекватные данной части запроса». Здесь дело в том, что при добавлении/удалении подзапросов в/из запроса соответственно должны меняться и названия таблиц, через которые идёт обращение к полям. Кроме того, по этой же причине может требоваться переименование полей. А кроме того, ещё существует возможность «логического переименования» таблиц и полей таблиц, которая тоже требует подстановок.

Трансляция

Что должен делать транслятор:

• Отслеживать и заменять логические имена таблиц и полей на реальные;
• Отслеживать совместимость цепочек подзапросов и вставлять подзапросы в тех местах, где они нужны;
• Оптимизировать подзапросы, удаляя их оттуда, где они не нужны;
• Собирать сами SQL-запросы из объектной структуры.

Так вот, эти операции удобно выполнять рекурсивно и неудобно выполнять нерекурсивно.

Поэтому в процессе создания запрос сводится к наиболее упрощённой рекурсивной объектной структуре:

• Один объект равен одному преобразованию и включает в себя ссылку на объект(ы) запросов или имена таблиц, над которыми делается преобразование;
• Каждое преобразование либо унарное, либо бинарное; N-арное преобразование эквивалентно серии бинарных;
• Для каждого преобразования существует собственный класс;
• Совместимость цепочек подзапросов отслеживается уже на стадии создания объекта запроса (конструктором);
• Сопоставление логических и реальных имён таблиц и полей также отслеживается на стадии создания объекта запроса;

А такого понятия, как оптимизация подзапросов, здесь просто не существует, она здесь не нужна! Потому что программист всегда видит перед собой цепочку преобразований такую, что логически каждое следующее преобразование — это подзапрос из предыдущего. Следовательно, использование явных подзапросов не нужно вообще — если где-то нужен подзапрос, транслятор его вставит сам, а если не нужен, то зачем париться и писать лишний вызов, раз это не меняет результат?! Хотя само преобразование Wrap остаётся для внутреннего использования транслятором. А «условно совместимые» пары преобразований, то есть пары, поведение которых зависит от того, есть между ними подзапрос или нет, отслеживается с помощью переменной «предпочтения подзапроса».

Далее, существует отдельный класс «группа преобразований» (Funq::mysql::Group для MySQL), логически описывающий ОДИН SQL-запрос/подзапросу, и НЕ описывающий его подзапросы. У каждого объекта Funq-преобразования есть метод compile, который в передаваемую ему группу преобразований записывает информацию о себе. В группу преобразований попадают, во-первых, только совместимые преобразования, а во-вторых, только выражения с реальными именами таблиц и полей. То есть, трансляцией имён таблиц и полей «группа преобразований» не занимается.

На выходе транслятора получается специальное представление «полуфабриката SQL-запроса» — связный список, элементы которого могут быть:

• Строками — это готовые части запроса,
• Ссылками на параметры связывания, то есть, placeholder’ы, подставляемые в запрос перед выполнением,
• Хешами с информацией о подменах имён таблиц и заменах полей на выражения. Они обрабатываются как стек — берётся последний встреченный хеш, не «pop»-нутый после этого из стека.

Funq должна поддерживать различные диалекты языка SQL, поэтому существует такое понятие, как «Funq-драйвер». Драйвер задаёт особенности диалекта для Funq, а также генерирует собственно SQL-код из оптимизированного представления — при этом на вход драйверу даются готовые ссылки на таблицы или транслированные подзапросы и список преобразований этих таблиц/подзапросов — причём, очищенный от несовместимых преобразований — а на выходе ожидается получение списка, аналогичного по формату «полуфабрикату» запроса.

Особенности диалекта, задаваемые драйвером — это:

• Информация о том, какие преобразования совместимы в рамках одного подзапроса, какие несовместимы, а какие совместимы условно — то есть разделяются подзапросом в случае, если дано явное указание необходимости разделения;
• Информация о том, какие преобразования могут включать в себя ссылки на вычисляемые поля из выражения select, а какие не могут;
• Функции получения списков полей в таблицах базы данных;
• Функция ref_field, генерирующая запись обращения к полю таблицы в необходимом синтаксисе;
• Функция ref_array, генерирующая n-местный placeholder для подстановки значений массива;
• Функция ref_placeholder, генерирующая одноместный placeholder для подстановки скаляра.

Во что транслируем?

Ясно, что пока что Funq необходимо транслировать в SQL — это будет самый простой способ его реализации, и первый на очереди реализации диалект SQL — MySQL:

Синтаксис SELECT MySQL

Рассмотрим интересующую нас часть синтаксиса SELECT-запроса MySQL (нерелевантный синтаксис вырезан, полная версия: [syntax]):

SELECT
  [DISTINCT]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT | SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
  select_expr, ...
  [FROM table_references
  [WHERE where_condition]
  [GROUP BY {col_name | expr} 
    [ASC | DESC], ... [WITH ROLLUP]]
  [HAVING where_condition]
  [ORDER BY {col_name | expr | POSITION}
    [ASC | DESC], ...]
  [LIMIT {[offset,] ROW_COUNT}]
  [FOR UPDATE | LOCK IN SHARE MODE]]

table_references: table_reference [, table_reference] ...
table_reference: table_factor | join_table
  
table_factor:
    tbl_name [AS alias] [index_hint]
  | table_subquery AS alias
  | ( table_references )

join_table:
    table_reference JOIN table_factor [ON conditional_expr]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr
  | table_reference {LEFT|RIGHT} JOIN table_reference ON conditional_expr
  | table_reference NATURAL [{LEFT|RIGHT}] JOIN table_factor

index_hint: {USE|IGNORE|FORCE} KEY (index_list)
index_list: index_name [, index_name] ...

Подсказки MySQL

Итак, подсказками для передачи hint() могут быть:

• HIGH_PRIORITY,
• STRAIGHT_JOIN,
• SMALL_RESULT или BIG_RESULT,
• BUFFER_RESULT,
• CACHE или NO_CACHE,
• CALC_FOUND_ROWS,
• FOR_UPDATE или SHARED_LOCK.

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

Пример 1: Сложный запрос

Вот, например, запрос из начала статьи на Perl:

$query = $funq
->query(album2album => "t1")->where("t1.parent=? and t1.pid=?pid")
->join($funq->query(albums => "t0")->where("t0.id=t1.child and t0.?t0where"))
->leftjoin($funq->query(album2album => "t3")->where("t3.parent=t1.parent and t3.pid=?pid"))
->join($funq->query(album2album => "t4")->where("t4.parent=t3.child and t4.child=t1.child and t4.pid=?pid"))
->select("t0.*, (t3.parent is null) as t1._direct")
->hint('calc_found_rows')
->group("t0.id")
->where("t1._direct=1")
->order("t0.ord desc, t0.name")
->limit("?offset", "?limit")

Вот пример его выполнения:

$rows = $query->stmt->hasharray({
    parent  => $parent,
    pid     => $pid,
    t0where => "config=?config",
    config  => 1,
    offset  => 0,
    limit   => 20,
});

Пример 2: Запрос попроще

Запрос попроще:

$dbh->query(tag2entity_cl => "t1")->where("t1.pid=? and t1.tag=?")
->join($dbh->query(entity => "t0")->where("t0.id=t1.eid and t0.config=? and t0.?t0where"))
->select("t0.*")
->order("t0.?t0order")
->limit("?offset", "?limit")

Пример 3: Очень простой

Или совсем простой:

$dbh->query("exif")->where("file=?")

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

А вот подзапрос:

$dbh->query(entity => "t0")
->where("t0.?t0where and ?subq in ?tagcount")
->select("t0.*, ?subq as t0.tag_count")
->set(subq => $dbh
    ->query(tag2entity => "t1")
    ->where("t1.pid=t0.pid and t1.eid=t0.eid")
    ->count->value
)

Пример 5: Вложенный подзапрос

Или даже два вложенных подзапроса:

$funq->query(entity => "t0")
->where("t0.?t0where and ?subq in ?tagcount")
->select("t0.*, ?subq as t0.tag_count")
->set(subq => $funq
    ->query(tag2entity => "t1")
    ->where("t1.pid=t0.pid and t1.eid=t0.eid")
    ->where("t1.config in ?list")
    ->set(list => $funq->query("configs"))
    ->count->value
)

Пример 6: Запрос из DBIx::Class::Cookbook

SQL-запрос:

SELECT name, COUNT( cds.cdid ) FROM artist me
LEFT JOIN cd cds ON ( cds.artist = me.artistid )
GROUP BY name

Теперь на Funq:

$funq->query(artist => 'a')
->leftjoin($funq
  ->query(cds => 'c')
  ->where('c.artist = a.artistid')
)->group('a.name')
->select('a.name, COUNT(c.cdid) AS cd_count')

Вроде бы не сильно проще. Но вся прелесть в том, что вместо artist можно подставить любой Funq-запрос, и получаемый запрос также можно использовать как часть другого.