13 634
правки
Изменения
Funq
,Массовая правка: замена Категория:Разработка на Категория:Архив, замена Category:Разработка на Категория:Архив
'''Funq - ''' — концепция околофункционального, гибкого язык языка запросов к реляционным базам данных, чемчуть-то чуть похожего на LINQ и призванного заменить SQL для разработчиковсложных запросов, оптимизируя их ещё на стадии построения, и давая возможность наложить любое преобразование на запрос в любой момент, к тому же, скрывая диалекты, свойственные отдельным СУБД.
== Идея ==
Сейчас стандартом языка запросов к реляционным базам данных является SQL - SQL — громоздкий, некрасивый и тяжёлый язык. SQL - SQL — декларативный язык, но он не оперирует сущностями каких-то явных типов, а синтаксис команд очень жёсткий, то есть, негибкий. Например, чтобы автоматически наложить на уже созданный запрос какое-то ограничение, нужно сначала понять, в какое место и в каком виде его добавлять, а способа адекватно и надёжно сделать это без синтаксического анализа запроса не существует.
Таким образом, было бы неплохо создать ''не SQL-подобный'' язык запросов, который был бы к тому же элегантен и удобен для использования в клиентских приложениях. Например, можно максимально приблизить формирование запросов к заданию выражения над таблицами, как алгебраическими объектами, с использованием различных операций реляционного исчисления - исчисления — JOIN-ов, ограничений, проекций и т.д, а также других нереляционных преобразований - преобразований — они необходимы потому, что возможностей построения запросов разработчики различных СУБД создали великое множество, а на замену SQL годится по меньшей мере равномощный ему язык. Сразу оговоримся, что основные проблемы написания запросов к базам данных связаны с запросами выборки. Запросы удаления/обновления часто используют базу просто как хеш-таблицу, а запросы вставки и [[wikipedia:DDL|DDL]]-запросы просто имеют достаточно бедный синтаксис. Их также нужно реализовать, как часть языка запросов, но основной интерес представляют запросы выборки. Ими-то мы в первую очередь и займёмся.
Например, рассмотрим вот такой SQL-запрос. Можно заметить, что это не чистый SQL - SQL — здесь ещё присутствуют подстановки имён таблиц из хеша <code>$t</code> и имён полей <code>$child</code> и <code>$parent</code>.
<source lang="sql">
.<span style='color:#00f; '>order</span> (t0.ord <span style='color:#080; '>desc</span>, t0.name) <br/>
.<span style='color:#00f; '>limit</span> (<span style='color:#f80; '><strong>?offset</strong></span>, <span style='color:#f80; '><strong>?limit</strong></span>)
</code></html>
Заметно две основных идеи: во-первых, логически после применения каждого преобразования .something() к отношению мы снова получаем отношение, имеющее все те же самые методы; и во-вторых, язык должен уметь интерполировать различные конструкции в различные места запросов.
Вообще-то, внезапно можно осознать, что <code>relation.something().something().something() - </code> — функциональный стиль программирования, а точнее, что это очень похоже на [http://www.martinfowler.com/bliki/FluentInterface.html Fluent Interfaces]. Собственно, они и сами являются пародией на ФП, хотя и довольно жалкой.
А ещё, раз уж мы вспомнили про LINQ, можно сразу заметить, что описанная идея позволяет легко реализовать «язык запросов» на объектном интерфейсе в родном синтаксисе многих языков программирования. Конкретно автора (меня — [[Участник:VitaliyFilippov|VitaliyFilippov]] 13:00, 13 июля 2009 (UTC)) интересует язык [[Perl]], он-то и будет рассмотрен в данной статье. Нужно отметить, что формирование запроса в родном синтаксисе языка имеет и недостаток - недостаток — затруднение кэширования скомпилированных запросов. Из-за этого может пострадать производительность; однако, если объекты запросов кэшируются самим приложением, этого не произойдёт. Этот недостаток говорит нам о том, что имеет смысл реализовать одновременно и объектный подход к формированию запроса, и просто трансляцию из Funq-кода.
=== Другие нужные фичи ===
* Автоматическая трансляция имён таблиц, и вообще запросов к таблицам. Это значит, что всегда можно переопределить <source lang="perl">$dbh->query("some_table")</source> так, что на самом деле этот конструктор будет возвращать не новый, девственно чистый, объект запроса к таблице some_table, а некоторый другой объект запроса - запроса — с заданными дополнительными ограничениями, к другой таблице, или вообще результат сложного запроса (хотя так делать, наверное, не надо). То есть, по сути, "программные представления"«программные представления».* Именованные параметры (placeholder'ыplaceholder’ы) - — задаются как '''<code>?name''' - </code> — и автоматическое определение имени из контекста в простых выражениях. Например, параметр из условия '''<code>config=?''' </code> будет, очевидно, называться ''<code>config''</code>, если только уже не задан другой параметр с таким именем. Кроме того, один и тот же явно именованный параметр можно использовать в различных местах выражения и даже в разных операторах в процессе формирования запроса.* Персонализация условий заданием имени таблицы (или имён таблиц) - — к примеру, если в запросе записано условие <code>exif.?placeholder</code>, а значение placeholder-а равняется <code>.file=? and .config=?</code>, то в запрос будет подставлено преобразованное условие <code>exif.file=? and exif.config=?</code>. Если аналогично требуется сделать для нескольких таблиц (ситуация редкая), синтаксис усложняется - усложняется — теперь условие в запросе, например, может звучать как <code>[exif, configs].?placeholder</code>, а в placeholder-е, например, как <code>_1.file=? and _1.config=_2.id</code>. При этом _1 будет заменено на имя первой таблицы, _2 - _2 — на имя второй, и так далее.* Наличие хинта (подсказки) для последовательного выполнения подзапросов, т.е. то есть выполнения сначала внутреннего запроса, а потом внешнего.* Автоматическое кэширование компилируемых из Funq-кода (т.е. то есть **не** формируемых объектно) запросов. Кэширование детализированных условиями и порядками сортировки/группировки запросов. Следует отметить, что если в процессе выполнения появляется слишком много (неограниченное относительно вариации входных данных количество) детализированных условиями и порядками запросов, значит, что-то //"Defective «Defective By Design"Design»//, а точнее, кто-то забыл про placeholder'ы...placeholder’ы…
* Автоматическая проверка соединения, переподключение к БД и пересоздание кэшированных SQL-объектов запроса в случае необходимости.
* Поддержка разделения соединённых отношений на отдельные хеши в результатах запроса.
== Статус реализации ==
На данный момент из запланированных идей реализованы ([http://svn.yourcmc.ru/viewvc.py/{{SVN|vitaphoto/head/lib-sway/Funq/ }} обзор исходных кодов]):
* Собственно работающий компилятор запросов ;) начал с 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 * в список полей, а только логически передавать во внешний запрос.
* Простые методы вставки (наподобие [{{SVN|vitaphoto/solstice/lib-sway/VMX/Common.pm|markup}} VMX::Common::insertall_hashref]) / мульти-обновления (для MySQL, INSERT … ON DUPLICATE KEY SET …).
* Все TODO и FIXME из кода.
== Синтаксис Funq ==
Рассмотрим [[Funq]] на примере его Perl-реализации: модулей [{{SVN|vitaphoto/solstice/lib-sway/Funq/DBI.pm}} Funq::DBI] (объект соединения) и [{{SVN|vitaphoto/solstice/lib-sway/Funq/Query.pm}} Funq::Query] (объект запроса). Под <code>$dbh</code> везде понимается объект соединения, а под $query — объект запроса.
=== Формирование запроса ===
Все методы новый объект запроса. Объекты по сути своей немутабельны.
; <code>$dbh->query($table[, $alias])</code>: Конструирует и возвращает новый объект запроса. Параметр $table может сам быть объектом (под)запроса.
; <code>$query->clone</code>: Возвращает клон объекта запроса <code>$query</code>. «Такой же, только другой», не имеющий никаких ссылок на старый объект или его части.
==== Унарные преобразования ====
; <code>$query->where($condition[, $condition2, ...])</code>: Добавляет все переданные ограничения (в виде строк) к объекту запроса <code>$query</code>.
; <code>$query->select($field_list)</code>: Задаёт список столбцов для объекта запроса. Может включать вычислимые столбцы и placeholder’ы.
; <code>$query->group($order_expression)</code>: Группировка отношения <code>$query</code> по выражению порядка <code>$order_expression</code>.
; <code>$query->order($order_expression)</code>: Сортировка отношения <code>$query</code> по выражению порядка <code>$order_expression</code>.
; <code>$query->limit([$offset, ]$limit)</code>: Выбор максимум <code>$limit</code> строк из отношения <code>$query</code>, начиная с <code>$offset</code> или с первой же строки.
; <code>$query->hint($hint)</code>: Задаёт «подсказку» к выполнению запроса транслятору и самой СУБД. Регистр значения не имеет.
; <code>$query->distinct</code>: Наложение на <code>$query</code> ограничения уникальности строк.
; <code>$query->count</code>: Возвращение количества строк в отношении <code>$query</code> вместо самих строк (SELECT COUNT(*)).
; <code>$query->value</code>: Выборка только первого столбца первой строки из <code>$query</code>.
; <code>$query->curry(\%bind)</code>: Задаёт значения некоторых (или всех) placeholder’ов, «детализируя» запрос. Именованные placeholder’ы с одним и тем же явно заданным именем подставляются во всю историю формирования запроса. Вместо <code>curry</code> можно использовать слово <code>set</code> или <code>bind</code>.
; <code>$query->update(@exp)</code>: Превращает запрос из запроса выборки в запрос обновления. Передаваемый массив <code>@exp</code> содержит части выражения, содержащего новые значения полей. При выполнении запроса непустые части соединяются через запятую. Отдельное выражение может содержать в себе placeholder’ы или, что важно, само являться одним placeholder’ом, например, «?values». Массив <code>@exp</code> может вовсе не содержать ни одного значения, в этом случае («по умолчанию») в качестве update-выражения принимается значение параметра <code>?update</code>;
; <code>$query->delete(@tables)</code>: Превращает запрос из запроса выборки в запрос удаления. Если запрос содержит несколько соединённых таблиц, то необязательно удалять подходящие строки из всех этих таблиц — с помощью параметра <code>@tables</code> можно выбрать лишь несколько из них;
; <code>$query->insert_into($table)</code>: Превращает запрос в запрос вставки выбранных строк в таблицу <code>$table</code>. Параметр <code>$table</code> обязательно должен быть именем таблицы. В зависимости от СУБД, может принимать дополнительные параметры — в случае с MySQL это может быть LOW_PRIORITY или DELAYED (см. [http://dev.mysql.com/doc/refman/5.0/en/insert.html|Синтаксис INSERT MySQL]);
; <code>$query->replace_into($table)</code>: ([http://www.mysql.com/ MySQL]). Превращает запрос в запрос замены выбранных строк в таблицу <code>$table</code>: Аналогично insert_to, но при этом все имеющиеся в таблице записи со значениями полей, входящих в первичный или уникальный ключ, равными значениям одной из вставляемых строк, предварительно удаляются (см. [http://dev.mysql.com/doc/refman/5.0/en/replace.html|Синтаксис REPLACE MySQL]).
<span style="color:red">Внимание!</span> Методы update и delete могут быть применены к запросу только один раз, не могут сочетаться, не могут быть применены после insert_into или replace_into, и аннулируют действие на запрос методов select, group, order, distinct, count, value, включая их предыдущие применения к запросу <span style="color:#f00">(!)</span>. Кроме того, невозможно применение методов update и delete к запросам, имеющим подзапросы в качестве derived table. При попытке любого из таких запрещённых действий генерируются предупреждения, а вызов игнорируется.
==== Бинарные преобразования ====
; <code>$query1->union($query2)</code>: Конструирует объединение запросов <code>$query1</code> и <code>$query2</code>. Не путать с соединением.
; <code>$query1->union_all($query2)</code>: Конструирует неуникальное объединение запросов <code>$query1</code> и <code>$query2</code>. Не путать с соединением.
; <code>$query1->intersect($query2)</code>: Конструирует пересечение запросов <code>$query1</code> и <code>$query2</code>.
; <code>$query1->intersect_all($query2)</code>: Конструирует неуникальное пересечение запросов <code>$query1</code> и <code>$query2</code>.
; <code>$query1->except($query2)</code>: Конструирует разность запросов <code>$query1</code> и <code>$query2</code>. <span style="color:red">Не реализовано в MySQL</span>.
; <code>$query1->except_all($query2)</code>: Конструирует неуникальную разность запросов <code>$query1</code> и <code>$query2</code>. <span style="color:red">Не реализовано в MySQL</span>.
; <code>$query1->join($query2)</code>: Конструирует внутреннее соединение (Inner Join) запросов <code>$query1</code> и <code>$query2</code>.
; <code>$query1->leftjoin($query2)</code>: Конструирует левое соединение (Left Join) запросов <code>$query1</code> и <code>$query2</code>.
; <code>$query1->rightjoin($query2)</code>: Конструирует правое соединение (Right Join) запросов <code>$query1</code> и <code>$query2</code>.
=== Выполнение запроса ===
Следующее семейство функций предназначено для выполнения запроса. Их разница в формате возврата строк.
Если %bind не детализирует запрос полностью, то есть так, чтобы незаданных placeholder’ов не оставалось, все функции возвращают undef или пустой список, при этом '''объект запроса не изменяется'''. Здесь идея следующая — если передан неполный хеш, значит, кто-то ошибся. Значит, нужно дать ему возможность исправиться и выполнить запрос ещё раз, не создавая объект запроса заново.
Для выполнения запроса нужно сначала выполнить следующую функцию запроса:
; <code>$stmt = $query->stmt(\%chain_bind)</code>: Компилирует запрос и получает его «полуфабрикат». При передаче в %chain_bind каких-либо параметров выполняет подзапросы и подставляет результаты их выполнения в сам запрос.
Описание функций (по убыванию популярности):
; <code>$stmt->hasharray(\%bind, $max_rows)</code>: Выполняет запрос и возвращает ссылку на массив хешей.
; <code>$stmt->arraylist(\%bind, $max_rows)</code>: Выполняет запрос и возвращает ссылку на массив массивов.
; <code>@row = $stmt->row(\%bind)</code>: Выполняет запрос и возвращает первую строку в виде списка.
; <code>$stmt->do(\%bind)</code>: Просто выполняет запрос.
; <code>$stmt->scalar(\%bind)</code>: Выполняет запрос и возвращает значение первой колонки первой строки в виде скаляра.
; <code>$stmt->arrayref(\%bind, $slice, $max_rows)</code>: Выполняет запрос и возвращает результат в виде ссылки на массив. Параметры <code>$slice</code> и <code>$max_rows</code> работают так же, как в {{CPAN|DBI}}::selectall_arrayref.
; <code>@col = $stmt->col(\%bind)</code>: Выполняет запрос и возвращает первую ''колонку'' в виде списка или ссылки на массив, в зависимости от контекста вызова (wantarray или нет).
; <code>$hash = $stmt->hash(\%bind, $key)</code>: Выполняет запрос и возвращает хеш хешей. Вложенные хеши размещаются по ключам, имеющим значения колонок, указанных в <code>$key</code>. Если <code>$key = [ "a", "b" ]</code>, то верно, что <code>$hash->{$row->{a}}->{$row->{b}} = $row</code>.
; <code>$stmt->multihasharray(\%bind, $multi, $separator)</code>: Выполняет запрос и возвращает ссылку на массив массивов хешей или массив хешей хешей. Предназначено для выборки нескольких наборов полей в одном запросе. Эти наборы нужно разделять колонкой-разделителем со специальным именем <code>$separator</code>, по умолчанию равный «_» (символу подчёркивания). Параметр <code>$multi</code> может быть либо '''ссылкой на массив строк''' — тогда multihasharray() вернёт ссылку на массив хешей хешей, где соответствующий элемент внешнего хеша будет иметь ключ, равный соответствующему элементу массива <code>$multi</code>, либо '''не ссылкой на массив вообще''' и тогда multihasharray() вернёт ссылку на массив массивов хешей.
; <code>$row = $stmt->rowhash(\%bind)</code>: Выполняет запрос и возвращает первую строку в виде ссылки на хеш.
; <code>($sql, @bind) = $stmt->sql(\%bind)</code>: Выполняет запрос и возвращает либо массив, либо ссылку на массив (в зависимости от контекста вызова), из двух элементов — строки транслированного в SQL запроса и массива bind-значений для подстановки на места '?' в тексте запроса.
=== Ручное чтение результата ===
Результат выполнения запроса также можно читать построчно:
; <code>$res = $stmt->execute(\%bind)</code>: Выполняет запрос и возвращает объект результата для получения из него строк.
; <code>$res->fetchrow_array()</code>: Получает следующую строку результата в виде списка.
; <code>$res->fetchrow_arrayref()</code>: Получает следующую строку результата в виде ссылки на массив.
; <code>$res->fetchrow_hashref()</code>: Получает следующую строку результата в виде ссылки на хеш.
; <code>$res->finish()</code>: Завершат выборку из объекта результата.
== Детали реализации ==
Ниже приведены некоторые размышления, а также некоторые детали внутреннего устройства [[Funq]].
=== Использование подзапросов ===
В родном синтаксисе языка возникает небольшая проблема с подзапросами — внутри строк подзапросы задать невозможно. Подзапросы могут возникать в трёх местах:
* в SELECT как поле,
* в FROM как таблица, из которой делается запрос («derived table»),
* в WHERE как часть условия — скаляр или список значений для IN / NOT IN.
Нужно, кстати, отметить, что для некоторых последовательностей преобразований сам транслятор, очевидно, должен генерировать дополнительные подзапросы. Для некоторых других он же, наоборот, может оптимизировать запрос, удалив из него подзапрос.
С синтаксисом п.2 (derived tables) проблем не возникает: нужно только передать в конструктор нового запроса не имя таблицы, а объект запроса. Но зато с п.2 возникает первая проблема трансляции — при перемещении условий, порядков сортировки и т. п. вовнутрь/наружу подзапроса из derived table нужно подменять имена полей, которые, кроме того, могут ещё и дублироваться, называясь одинаково у разных таблиц — если во внешнем запросе такое дублирование MySQL разрешает, то во внутренних — нет. Отсюда следует, что явное или неявное использование подзапроса с выборкой всех полей из нескольких таблиц требует от транслятора знания схемы БД и явного перечисления всех этих полей.
Пп.1 и 3 имеют общую черту — в обоих пунктах в случае скаляра мы имеем проблему интерполяции подзапроса в выражение, задаваемое строкой. Решение проблемы следующее: использование параметров (placeholder-ов) — замена подзапросов на '?' с последующим наложением дополнительного условия на объект запроса (в некотором роде [[rupedia:Карринг|каррирование]] (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 не решается — а решается только через дополнительное соединение с той же таблицей и дополнительную группировку (см. [[#Запрос типа "первые N для всех"|пример]]). По крайней мере, всё это применимо к 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 — тоже многоарные. Причём запросы с ними уникальны в том смысле, что для оптимизации нужно не вытаскивать наружу «общие» части таких запросов, а наоборот, дублировать внешние условия в каждом из подзапросов.
Итак, таблица правил группировки (строки — внешние операции, столбцы — внутренние операции):
<tab sep=bar style="text-align: center; width: 400px" class="tableborder1black" head="topleft">
— | '''J''' | '''U''' | '''I''' | '''E''' | '''W''' | '''S''' | '''G''' | '''O''' | '''L''' | '''D'''
'''J''' | + | — | — | — | + | * | — | * | — | —
'''U''' | + | + | + | + | — | — | — | — | — | —
'''I''' | + | + | + | + | — | — | — | — | — | —
'''E''' | + | + | + | + | — | — | — | — | — | —
'''W''' | + | * | * | * | + | + | * | + | ? | +
'''S''' | + | * | * | * | + | * | + | + | + | +
'''G''' | + | — | — | — | + | * | — | — | ? | +
'''O''' | + | — | — | — | + | + | + | O | ? | +
'''L''' | + | + | + | + | + | + | + | + | * | +
'''D''' | + | — | — | — | + | + | + | + | ? | O
'''C''' | + | — | — | — | + | O | — | O | O | +
</tab>
Пояснения к таблице:
; 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''' остаётся для внутреннего использования транслятором. А «условно совместимые» пары преобразований, то есть пары, поведение которых зависит от того, есть между ними подзапрос или нет, отслеживается с помощью переменной «предпочтения подзапроса».
Далее, существует отдельный класс «группа преобразований» ([{{SVN|vitaphoto/solstice/lib-sway/Funq/mysql/Group.pm}} Funq::mysql::Group] для [http://www.mysql.org/ 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 (нерелевантный синтаксис вырезан, полная версия: [[http://dev.mysql.com/doc/refman/5.0/en/select.html|SELECT syntax]]):
<source lang="sql">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]]
</source>
<pre>
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] ...
</pre>
==== Подсказки MySQL ====
Итак, подсказками для передачи hint() могут быть:
* HIGH_PRIORITY,
* STRAIGHT_JOIN,
* SMALL_RESULT или BIG_RESULT,
* BUFFER_RESULT,
* CACHE или NO_CACHE,
* CALC_FOUND_ROWS,
* FOR_UPDATE или SHARED_LOCK.
== Примеры запросов ==
=== Пример 1: Сложный запрос ===
Вот, например, запрос из начала статьи на Perl:
<source lang="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")
</source>
Вот пример его выполнения:
<source lang="perl">
$rows = $query->stmt->hasharray({
parent => $parent,
pid => $pid,
t0where => "config=?config",
config => 1,
offset => 0,
limit => 20,
});
</source>
=== Пример 2: Запрос попроще ===
Запрос попроще:
<source lang="perl">
$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")
</source>
=== Пример 3: Очень простой ===
Или совсем простой:
<source lang="perl">
$dbh->query("exif")->where("file=?")
</source>
=== Пример 4: Подзапрос ===
А вот подзапрос:
<source lang="perl">
$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
)
</source>
=== Пример 5: Вложенный подзапрос ===
Или даже два вложенных подзапроса:
<source lang="perl">
$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
)
</source>
=== Пример 6: Запрос из DBIx::Class::Cookbook ===
SQL-запрос:
<source lang="sql">
SELECT name, COUNT( cds.cdid ) FROM artist me
LEFT JOIN cd cds ON ( cds.artist = me.artistid )
GROUP BY name
</source>
Теперь на [[Funq]]:
<source lang="perl">$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')
</source>
Вроде бы не сильно проще. Но вся прелесть в том, что вместо artist можно подставить любой Funq-запрос, и получаемый запрос также можно использовать как часть другого.