Sql

Материал из Eludia
Перейти к: навигация, поиск

Содержание

Данная процедура предназначена для того, чтобы минимизировать количество SQL-кода в исходных текстах приложения, упростить формирование запросов к БД в подавляющем большинстве стандартных ситуаций и поспособствовать использованию максимально эффективных схем данных. В некоторой степени аналогичный подход применяется в модуле SQL::Abstract.

Введение

Прежде чем описывать различные варианты вызова функции sql и формат соответствующих результатов, оговорим некоторые ограничения на рассматриваемое множество запросов, а также введём некоторые понятия, которые будут использоваться ниже.

Отказ от множественных операций

Функция sql не поддерживает и, вероятно, никогда не будет поддерживать синтаксические конструкции UNION, INTERSECT, MINUS и им подобных. Мы убеждены, что необходимость применения данных возможностей языка SQL в приложениях (в всяком случае, при вводе данных) в подавляющем большинстве случаев связана с ошибками проектирования схемы БД, так что приветствовать их использование путём какой-либо автоматизации не стоит. Поясним нашу мысль на примере UNION из 2 подзапросов.

Рассмотрим вначале ситуацию, в которой выражения FROM обоих слагаемых совпадают. В этом случае запросы, скорее всего, различаются условиями WHERE, и их объединение идентично результату одного запроса с тем же FROM и OR-комбинацией условий WHERE. По требованиям к ресурсам UNION, скорее всего, будет по крайней мере не оптимальнее OR-запроса (а может быть, и хуже).

Если же производится объединение записей из разных таблиц, то, скорее всего, модель данных составлена неверно: в действительности извлекаемые данные должны храниться в одной таблице.

Корневая таблица

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

SELECT ... FROM t1, t2,... tn

это всегда, условно говоря,

foreach $r1 (@t1) {
 foreach $r2 (@t2) {
  ...
  foreach $rn (@tn) {
   ...
   if (...) {
    push @r, ...; 
   }    
  }
  ...
 }
}

за которым, возможно, следует

@r = sort {...} @r; # дополнительная сортировка

Теоретически (в особенности если писать условия связи в раздел WHERE, как делалось с древнейших времён, а в Oracle, например, так до выхода 9-й версии в 2001 г.) все таблицы выглядят равноправными, а выборку можно считать результатом фильтрации их декартова произведения.

Однако на практике условия связи таблиц могут учитываться не в самом глубоко вложенном теле, а при формировании списка для каждого очередного foreach

foreach $r1 (@t1) {
 unless (...) {next}
 foreach $r2 (t2 ($r1)) {
  unless (...) {next}
  ...
  foreach $rn (tn ($r1, $r2,...)) {
   unless (...) {next}
   push @r, ...; 
  }
  ...
 }
}

что влияет на производительность самым серьёзным образом. Дабы минимизировать число итераций, необходимо добиваться того, чтобы условия на подмножества @t2, ... @tn были как можно более ограничительными. В идеале эти подмножества должны содержать не более 1 элемента. Тогда вложенные циклы вообще исчезают:

foreach $r1 (@t1) {
 unless (...) {next}
 $r2 = t2 ($r1);
 $r3 = t3 ($r1, $r2);
 ...
 $rn = tn ($r1, $r2,...);
 push @r, ...; 
}

Так и происходит, если @t1 (назовём её корневой таблицей) — таблица фактов (например, договоров), а прочие таблицы — справочники (типы договоров, статусы, контрагенты, страны, города и пр.). При этом общее число итераций за время выполнения запроса равно числу просмотренных записей корневой таблицы.

Если Вы задублировали в таблице фактов всю информацию, необходимую для поиска и сортировки, привели WHERE и ORDER BY к той же корневой таблице (о WHERE-фильтрах см. также здесь) и грамотно построили индексы, то запрос будет выполнен за 1 проход по B-дереву без дополнительной сортировки, то есть оптимально. К этому необходимо стремиться. С точки зрения скорости запросов, идеально на каждую комбинацию условий с порядком сортировки иметь по индексу, но это может снизить общую эффективность уже за счёт замедления операций записи и раздувания БД.

Первые версии функции sql поддерживали только запросы описанной, "звёздчатой", структуры, где все фильтры приведены к корню. В настоящее время это ограничение снято, хотя понятие корневой таблицы по-прежнему имеет смысл.

Выборка списков записей

Перейдём, наконец, к описанию функции sql. Вначале рассмотрим не самый частый, но хорошо иллюстрирующий основные используемые опции случай.

Список дочерних записей

Допустим, вы разрабатываете экран просмотра для сущности "Поручения" (tasks). Текущая запись из tasks доступна как $data. С поручением связан список реплик (task_notes), связанный с родительской записью по полю id_task. Требуется вывести список последних 15 реплик в порядке убывания их id. Соответствующий вызов sql выглядит так:

sql ($data, task_notes => [
 [ id_task => $data -> {id}],
 [ ORDER   => 'id DESC'],
 [ LIMIT   => [0 + $_REQUEST {start}, 15] ],
]);

Для сравнения приведём эквивалентный вызов sql_select_all_cnt:

($data -> {task_notes}, $data -> {cnt}) = sql_select_all_cnt (<<EOS, $data -> {id}, {fake => 'task_notes'});
 SELECT
 	task_notes.*
 FROM
 	task_notes
 WHERE
 	task_notes.id_task = ?
 ORDER BY
 	task_notes.id DESC
 LIMIT
 	$_REQUEST{start}, 15
EOS

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

sql ($data, task_notes => ...);

($data -> {task_notes}, $data -> {cnt}) = sql ($data, task_notes => ...);

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

Привязка справочников

Комфорт ощущается сильнее тогда, когда мы вспоминаем, что task_notes ссылается на справочники. Допустим, там есть 3 ссылки:

  • id_voc_status на voc_status;
  • id_user на users;
  • id_author опять на users.

Чтобы прикрепить соответствующие записи из справочников, можно написать:

sql ($data, task_notes => [
 [ id_task => $data -> {id}],
 [ ORDER   => 'id DESC'],
 [ LIMIT   => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');

Снова приведём аналог с полным текстом SQL:

($data -> {task_notes}, $data -> {cnt}) = sql_select_all_cnt (<<EOS, $data -> {id}, {fake => 'task_notes'});
 SELECT
 	task_notes.*
 	, voc_status.label AS voc_status_label
 	, users.label      AS user_label
 	, authors.label    AS author_label
 FROM
 	task_notes
 	LEFT JOIN voc_status       ON task_notes.id_voc_status = voc_status.id
 	LEFT JOIN users            ON task_notes.id_user = users.id
 	LEFT JOIN users AS authors ON task_notes.id_author = authors.id
 WHERE
 	task_notes.id_task = ?
 ORDER BY
 	task_notes.id DESC
 LIMIT
 	$_REQUEST{start}, 15
EOS

Ссылающиеся поля определяются автоматически, по именам таблиц или синонимов. При этом для вычисления единственного числа применяется процедура en_unplural. В частности, она принимает во внимание тот факт, что английское слово status во множественном числе пишется как status.

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

'users', 'roles'

тогда будет установлен

LEFT JOIN roles ON users.id_role = roles.id
Доступ к данным справочников

В вышеприведённом примере результаты работы sql_select_all_cnt и sql будут немного отличаться: то, что в первом случае доступно как

$data -> {task_notes} -> [0] -> {author_label}

во втором случае пишется

$data -> {task_notes} -> [0] -> {author} -> {label}

Таким образом, поля, соответствующие справочникам, находятся не на уровне полей корневой таблицы, а собраны в хэши 2-го уровня. По умолчанию каждый такой хэш содержит поля id и label. Но это легко переопределить. Например, если Вам требуется достать код иконки, соответствующей статусу (voc_status.icon), это будет выглядеть так:

sql ($data, task_notes => [
 [ id_task => $data -> {id}],
 [ ORDER   => 'id DESC'],
 [ LIMIT   => [0 + $_REQUEST {start}, 15] ],
], 'voc_status (id, label, icon)', 'users', 'users AS authors');

или вообще

sql ($data, task_notes => [
 [ id_task => $data -> {id}],
 [ ORDER   => 'id DESC'],
 [ LIMIT   => [0 + $_REQUEST {start}, 15] ],
], 'voc_status (*)', 'users', 'users AS authors');

Фильтры и псевдофильтры

Теперь обратимся к строкам 2-4 нашего примера. Их смысл очевиден, однако настало время уточнить: 2-я строка определяет фильтр (одно из условий WHERE), её 1-й элемент соответствует имени столбца таблицы, таких строк может быть сколько угодно. О фильтрах мы подробнее расскажем чуть ниже. А строки 3 и 4 соответствуют выражениям ORDER BY и LIMIT и встречаются в одном вызове не более одного раза.

Псевдофильтр ORDER

Данная опция определяет порядок сортировки выборки. По умолчанию принимается 'label', если такое поле упомянуто в описании схемы данных, в противном случае — 'id'. Таким образом, если, например, требуется список всех дочерних (по полю parent) подразделений текущего, можно не указывать ни LIMIT, ни ORDER:

sql ($data, departments => [
 [parent => $data -> {id}],
]);

Значение аргумента псевдофильтра ORDER подставляется в результирующий SQL не напрямую, а обрабатывается функцией order. Что можно использовать при управлении сортировкой примерно таким образом:

sql ($data, goods => [
 ...
 [ORDER => ['label',
  'price' => 'price, label ASC!',
 ]]
]);

В приведённом примере при $_REQUEST {order} eq 'price' имеет место упорядочение сначала по цене (по возрастанию или убыванию в зависимости от $_REQUEST {desc}), а потом по наименованию (всегда в прямом алфавитном порядке). По умолчанию же выборка сортируется только по наименованию.

Псевдофильтр LIMIT

Данная опция определяет ограничение на количество записей в выборке. Предусмотрено 2 принципиально разных варианта её использования.

Первый вариант описан выше, при этом указывается 2 аргумента: начало и объём выборки. Если LIMIT указан с 2 аргументами, то функция sql, как и sql_select_all_cnt, возвращает 2 результата: выборку (список хэшей) и количество записей без учёта ограничителя.

my ($ad_users, $cnt) = sql (
 ad_users => [
  'id_ad_server',
  ['(label LIKE %?% OR login LIKE %?%)'  => [$_REQUEST {q}, $_REQUEST {q}]],
  ['ISNULL(id_user)' => $_REQUEST {empty}],
  [ LIMIT => [0 + $_REQUEST {start}, 50]],
 ],   
);

Значения для псевдофильтра LIMIT в приведённом запросе можно указать в форме:

 [LIMIT => 'start, 50'],

тогда они будут развёрнуты в массив автоматически.

Если же аргументом является число 1:

 [LIMIT => 1],

то результатом sql является единственная ссылка на хэш (не список!) При этом функция sql используется в режиме выборки отдельных записей. Данная форма вызова sql характерна для запросов типа "последнее действие такого-то типа":

my $last_log = sql (log => [
 [ type    => $type], 
 [ ORDER   => 'id DESC'],
 [ LIMIT   => 1],
], 'users');

Такой фильтр имеет сокращённую форму записи:

 [1]

удобную для однострочников типа

my $id = sql ('departments(id)' => [[label => 'Руководство'], [1]]);

Отметим, что в последнем случае sql возвращает не хэш, а скаляр. Так происходит, когда, помимо фильтра на единственность записи, указано единственное поле для выборки.

LIMIT с указанием ORDER

Ограничение выборки, как правило, имеет смысл только при фиксированном порядке сортировки (в PostgreSQL обратное вообще считается ошибкой). Отсюда возникает естественная мысль: включить поля для ORDER BY в псевдофильтр LIMIT, а не тратить на них целую строку:

[LIMIT => 'start, 50 BY code'],

После BY указывается то, что будет подставлено в ORDER BY: список полей, которые можно сопровождать модификаторами DESC и, при желании, ASC. Если требуется сортировка по единственному полю, то ключевое слово DESC смотрится несколько громоздко. Но вместо него можно указать отрицательное значение для размера выборки. Например, последняя по id запись в таблице log выбирается вот так:

my $last_log = sql (log => [ ['-1 BY id'] ]);

(мнемоника: -1-й элемент в массиве log).

Список общего вида

Выше мы рассмотрели использование функции sql с фильтром по ссылке на родительский объект. Обычно такие выборки (дочерних объектов) встречаются на формах редактирования и просмотра отдельных записей. Выборки для экранов-списков формируются совершенно аналогично, только вместо одного обязательного фильтра у них, как правило, множество необязательных (подробнее о необязательности — чуть ниже):

sql (tasks => [
 [id_task_type => $_REQUEST {id_task_type}],
 [id_author    => $_REQUEST {id_author}],
 ...
 [ LIMIT      => [0 + $_REQUEST {start}, 15] ],
]);

Ещё раз напомним, что все определённые здесь условия могут относиться к полям только одной — корневой — таблицы.

В случае экрана-карточки извлечение данных начинается с хэша, соответствующего текущей записи в тематической таблице. Тогда, как было показано, sql может добавлять свои результаты в виде дополнительных компонент. У экрана-списка такой записи нет, но начальный хэш задать всё равно удобно:

return sql ({}, tasks => [...], ...);

Как правило, экран-список использует несколько словарей данных, порождаемых функцией add_vocabularies. Если воспользоваться её результатом в качестве стартового хэша, то можно оформить извлечение всех данных для экрана-списка как один вызов:

sql (add_vocabularies (...), tasks => [...], ...);

Именно такой вариант заложен в стандартный шаблон StEludio для экрана-списка.

Подробнее о фильтрах

Заголовок фильтра, помимо имени поля (вообще говоря, левой части условия) может содержать оператор сравнения. Собственно, он содержит его всегда, просто по умолчанию справа приписывается ' ='.

Аналогичным образом к коду добавляется placeholder ('?'), если пользователь ни разу не указал его явно.

sql (tasks => [
 ...
 ['total >='                 => $_REQUEST {total_from}],
 ['duration BETWEEN ? AND ?' => [$_REQUEST {duration_from}, $_REQUEST {duration_to}]],
 ...
], 'voc_status', 'users', 'users AS authors');

Вообще в левой части фильтра можно приводить любые логические выражения от полей корневой таблицы, пригодные для подстановки в WHERE. При этом необходимо проставлять все необходимые placeholders и в правой части приводить соответствующее число значений:

sql (orgs => [
 ...
 ['inn = ? OR ogrn = ?' => [$_REQUEST {code}, $_REQUEST {code}]],
 ...
], 'voc_status', 'users', 'users AS authors');
Пустые значения как выключатели фильтров

В подавляющем большинстве случаев пустое значение фильтрующего параметра (HTTP-) запроса соответствует не требованию поставить условие "IS NULL", а наоборот, вовсе не использовать соответствующее поле в отборе записей. Именно так и работают фильтры в sql. Значение "0" пустым не считается. Итак, фильтр

 [id_task_type => $_REQUEST {id_task_type}],

повлияет на сгенерированный SQL только в том случае, если id_task_type установлен. Обычно подобные параметры передаются select-списками с верхней панели при таблице, у которых по умолчанию выбрана первая строка, определённая опцией empty: "[Все типы]" или что-то в этом роде.

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

NULL в запросах

Итак, значение undef никогда не подставляется в запрос в качестве параметра — вместо этого оно отключает условие фильтрации в целом. Если же вам действительно требуется использовать SQL-предикат IS NULL, его можно привести в явном виде без параметра:

['dt_finish IS NULL'],

Аналогичным образом оформляется условие IS NOT NULL.

Имя переменной в приведённом примере стандартно для поля "Дата окончания периода". Пустое значение такого поля обычно соответствует формулировке "по текущее время", с гипотетическим продолжением до бесконечности. Рассмотрим стандартную задачу: найти все интервалы, даты окончания которых находятся в будущем относительно $dt. Поскольку интервалы могут быть как закрытыми, так и открытыми, на чистом SQL данное условие формулируется как

dt_finish >= '$dt' OR dt_finish IS NULL

Функция SQL предлагает для такой ситуации (OR IS NULL) компактный синтаксис фильтра:

['dt_finish... >= ' => $dt],

(отточие примерно соответствует фразе: "Э-э-э... Или там вообще пусто...")

Ту же задачу можно решить при помощи функции IFNULL:

['IFNULL(dt_finish, '$dt') >= ' => $dt],

однако в плане оптимизации запроса это всегда хуже. Да и в наглядности выигрыша никакого.

Автоимпорт из %_REQUEST

Зачастую фильтруемое поле совпадает по имени с параметром запроса, содержащим требуемое значение. В таких случаях значение можно не указывать: оно будет добыто из %_REQUEST автоматически.

sql (tasks => [
 'id_task_type',
 'id_author',
 [ LIMIT      => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');

Фильтр по полю fake

Как показано выше, sql учитывает корневую таблицу в качестве значения опции fake процедур sql_select_all и sql_select_all_cnt, то есть добавляет в WHERE условие на поле fake, исходя из значения параметра $_REQUEST {fake}. По умолчанию извлекаются записи с fake = 0, то есть актуальные.

LIKE

Для LIKE предусмотрен специальный синтаксический элемент, в соответствии с которым к значению параметра приписываются спецсимволы '%': справа (?%) или с обеих сторон (%?%).

sql (tasks => [
 'id_task_type',
 'id_author',
 'duration >=',
 ['label LIKE %?%' => $_REQUEST {q}],
 [ LIMIT      => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');
Даты/время

Даты вводятся в поля форм и попадают в %_REQUEST в национальном формате, однако передавать их в sql необходимо в формате ISO (как и везде в Eludia), для чего следует использовать функцию dt_iso.

Значение '0000-00-00' считается пустым и отключает фильтр.

"Следующий день" в параметрах

Имеет место следующая проблема: если в поле, скажем, "Когда создано поручение" фиксируется и дата, и время, то значение поля (например, "2008-09-07 17:15:27") превосходит то, которое соответствует дню как таковому (соответственно, "2008-09-07"). При этом в запросе вилка дат "Создано с ... по ..." указывается, естественно, без минут и секунд, так что значение с ненулевым временем как бы выходит за рамки своего дня.

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

Для этой цели в sql введён специальный оператор сравнения: "меньше + 1 день"

sql (tasks => [
 ...
 
 ['dt_created >=' => dt_iso ($_REQUEST {dt_from})],
 ['dt_created <+' => dt_iso ($_REQUEST {dt_to})],
 
 [ LIMIT      => [0 + $_REQUEST {start}, 15] ],
], 'voc_status', 'users', 'users AS authors');
Поиск интервалов

Выше приведён пример поиска записей, у которых значение заданного поля попадает в нужный интервал. Часто при работе с датами возникает двойственная задача: найти записи, для которых заданная константа ("сегодня") попадает в интервал между значениями двух полей ("начало" и "окончание"). На SQL это выглядит так:

AND dt_start  <= $now
AND dt_finish >= $now

для функции sql можно использовать фильтр вида:

['dt_start .. dt_finish' => $now],

Если ситуация dt_finish IS NULL соответствует окончанию в бесконечности (незакрытый интервал), то можно поставить отточие после dt_finish:

['dt_start .. dt_finish...' => $now],

Это породит SQL вида

AND dt_start    <= $now
AND ((dt_finish >= $now) OR (dt_finish IS NULL))

Интервалы можно искать не только по отдельным точкам, но и по условию пересечения с заданными интервалами. Выглядит это просто:

['dt_start .. dt_finish...' => [$from, $to]],

Такой фильтр порождает условие, где начала и окончания периодов сопоставляются "крест-накрест":

AND dt_start    <= $to
AND ((dt_finish >= $from) OR (dt_finish IS NULL))
Подмножества / списки id (IN, NOT IN)

Если Вы накладываете все условия WHERE на одну таблицу (что всячески приветствуется sql), то зачастую возникает необходимость определить фильтр по принадлежности или, наоборот, не принадлежности к некоторому подмножеству. В простом случае, когда множество известно как список констант (например, [1, 2, 5]) в точке вызова sql, этот фильтр выглядит так:

 [id_type => [1, 2, 5] ],

что является сокращённой формой для

 ['id_type IN' => [1, 2, 5] ]

Обратный фильтр (непринадлежность) записывается так:

 ['id_type NOT IN' => [1, 2, 5] ],

Если же список id зависит от параметров запроса и/или состояния БД, то для формирования фильтра рекомендуется снова воспользоваться функцией sql. Например, фильтр "тип задач — один из относящихся к текущему пользователю" запишется таким образом:

 [id_type => 
 	sql ('users_task_types(id_task_type)' => [
 		[id_user => $_USER -> {id}],
 	])
 ],

Здесь у корневой таблицы указан список выбора из единственного поля (id_task_type). В таком режиме функция sql возвращает ссылку не на список и не на хэш, а на скаляр. При конкатенации этот скаляр ведёт себя как строка с непустым списком id (по аналогии с результатом sql_select_ids), однако он ещё несёт дополнительную информацию о том SQL-запросе, которым был создан, и его параметрах.

Делается это для того, чтобы иметь возможность подставить в скобки после IN либо список цифр через запятую (для MySQL), либо SQL-код подзапроса (для остальных СУБД). Это необходимо, поскольку реализация подзапросов в MySQL недопустимо плоха (оптимизатор не умеет пользоваться некоррелированностью), зато отлично работают IN-списки, а в Oracle, скажем, всё в порядке с подзапросами, но крайне нежелательно раздувать SQL-код длинными списками констант, да и накладные расходы на каждый вызов ощутимые.

Фильтры по подмножеству можно использовать в качестве фильтров по полям присоединённых таблиц. Например, если вы ищете платежи, относящиеся к товарам (voc_goods), названия которых начинаются на $_REQUEST {q}, фильтр запишется следующим образом:

 [id_voc_goods => 
 	sql ('voc_goods(id)' => [
 		['label LIKE ?%' => $_REQUEST {q}],
 	])
 ],

Внимание! При пустом $_REQUEST {q} (вообще sql на единственный явно обозначенный столбец и без единого актуального фильтра) вложенный вызов вернёт undef — специально для того, чтобы отключить внешнее IN-условие. Это может создать недоразумение, если Вы используете внутренний вызов сам по себе. Его легко избежать, если взять за правило всегда выбирать хотя бы 2 поля.

Привязка не-справочников

Всюду выше предполагалось, что ссылки идут в одном направлении: от корневой таблицы к присоединённым, то есть на одну запись корневой таблицы приходится не более одной записи результирующей выборки. Сейчас мы научимся преодолевать это ограничение — в результате получатся запросы, где к корневой таблице присоединяются множественные дочерние записи (возможно, в несколько каскадов).

Допустим, нам необходимо составить выборку из таблиц договоров contrats и платежей pays (условие связи pays.id_contrat = contrats.id), где будут присутствовать все договора, удовлетворяющие некоторым условиям, плюс для каждого из них все платежи (возможно, ни одного). Соответствующий вызов выглядит так:

sql (contrats => [
 ... # фильтры
], ['pays']);

Как нетрудно убедиться, всё отличие заключается в том, что имя присоединённой таблицы приведено не в виде строки, а заключено в список. В результате функция sql не будет искать ссылку contrats.id_pay, а установит связь по pays.id_contrat.

JOIN с локальными фильтрами

Список здесь применён не случайно. Вторым его элементом может быть список фильтров, совершенно аналогичный тому, что имеется у основного запроса. Например, все актуальные (неудалённые) платежи с суммой в заданной вилке будут показаны для

sql (contrats => [
 ... # фильтры
], [pays => [
 [ fake => 0],
 ['total BETWEEN ? AND ?' => [$_REQUEST {from}, $_REQUEST {to}]],
]]);

При непустых значениях $_REQUEST {from} и $_REQUEST {to} соответствующий запрос будет содержать фрагмент

FROM contrats
LEFT JOIN pays ON (pays.id_contrat = contrats.id
 AND pays.fake = 0
 AND pays.total BETWEEN ? AND ?
)

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

INNER JOIN

Всюду выше показаны примеры того, как строить запросы с необязательным присоединением дополнительных таблиц к корневой, то есть LEFT JOIN. Иногда имеет смысл использовать жёсткое соединение: INNER JOIN. Чтобы сгенерировать такой код, к имени таблицы следует приписать префикс "-". Если так поступить в последнем примере:

sql (contrats => [
 ... # фильтры
], [-pays => [
 [ fake => 0],
 ['total BETWEEN ? AND ?' => [$_REQUEST {from}, $_REQUEST {to}]],
]]);

то результирующий запрос

FROM contrats
INNER JOIN pays ON (pays.id_contrat = contrats.id
 AND pays.fake = 0
 AND pays.total BETWEEN ? AND ?
)

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

Итак, sql предоставляет 2 способа наложить на выборку условие по присоединённой таблице: IN-фильтр и фильтр при INNER JOIN. Первый вариант хорош, если выбираемое подмножество гарантированно мало (скажем, если присоединяемая таблица — это справочник всего на десяток строк или связная таблица "сотрудник — рабочая группа", где для любому пользователю соответствует максимум строки 3), второй может оказаться оптимальным, если, наоборот, отфильтрованное множество id содержит, допустим, сотни тысяч элементов.

JOIN по явно указанному условию

В sql делается всё, чтобы условия связи таблиц вычислялись автоматически. Однако существуют ситуации, в которых явно указать join condition проще и нагляднее, нежели изобретать магическую параметризацию.

Рассмотрим пример. Допустим, в схеме определены таблицы

  • dm_representatives (торговые представители)
  • dm_supervisors (их начальники)

dm_representatives имеет ссылку id_dm_supervisor (естественно, на dm_supervisors), кроме того, обе таблицы ссылаются на users полями с одинаковыми стандартными наименованиями: id_user (пустая ссылка соответствует вакантному месту). Требуется достать карточку представителя вместе с его начальником, присоединив обе записи из users. Делается это так:

my $data = sql (dm_representatives => [[id => $_REQUEST {id}]]
 , 'dm_supervisors'
 , 'users AS representatives ON dm_representatives.id_user'
 , 'users AS supervisors     ON dm_supervisors.id_user'
);

Заявлять AS-псевдоним в обоих случаях не обязательно. Вообще говоря, для N вхождений одной таблицы необходимо N-1 псевдонимов.

В приведённом примере после ON упомянуто только ссылающееся поле: в этом случае sql приписывает " = $alias.id" автоматически. Впрочем, Вы можете указать здесь произвольные условия, корректные с точки зрения SQL: если там встретится хотя бы один пробельный символ, то ON-выражение будет подставлено в SQL как есть.

INNER JOIN, как и в случае с автоматическим определением связи, задаётся префиксом '-'.

Вот какой вид может принять наш пример, если добавить условие актуальности карточки пользователя-начальника и назвать её $data -> {user}:

my $data = sql (dm_representatives => [[id => $_REQUEST {id}]]
 , 'dm_supervisors'
 , 'users AS representatives ON dm_representatives.id_user'
 , '-users ON dm_supervisors.id_user = users.id AND users.fake = 0'
);

NOT EXISTS (ANTI-JOIN)

В некоторых запросах встречаются условия, связанные с тем, чтобы для каждой интересующей записи в определённой таблице не было найдено ни одной дочерней строки. Например: найти карточки всех сотрудников (users), которые сейчас не в отпуске (schedule: id_user, is_vacation, dt_start, dt_finish).

Люди, привыкшие писать много вложенных подзапросов, часто употребляют в таких случаях конструкцию NOT EXISTS (SELECT ...). С точки зрения производительности это либо просто плохо, либо совершенно недопустимо: в зависимости от СУБД. Эффективно данная задача решается при помощи комбинации LEFT JOIN и условия IS NULL на поле id присоединяемой таблицы. Это называется ANTI-JOIN.

Функция sql предлагает для такого случая специальный синтаксис аргумента: он выглядит, как NOT EXISTS:

my $users = sql (users => [
	
 ...

], ["NOT EXISTS schedule" => [

 [is_vacation => 1],
 ['dt_start .. dt_finish' => $dt],
		
]]);

но вместо подзапроса порождает следующее:

SELECT
 ...
FROM
 users
 LEFT JOIN schedule ON (
  schedule.id_user = users.id
  AND schedule.dt_start  <= ?
  AND schedule.dt_finish >= ?
 )
WHERE
 schedule.id IS NULL AND
 ...

Вообще "NOT EXISTS" смотрится страшно коряво, и вполне можно позволить себе грамотную форму "DOES NOT EXIST" или сокращённую "DOESN'T EXIST".

Параллельно поддерживается предикат EXISTS, но он особого самостоятельного смысла не имеет, так как равносилен INNER JOIN.

Поточный режим

Если вы собираетесь пройти выборку один раз, вычислить значение некоторых переменных и в дальнейшем использовать их вместо исходного списка записей, имеет смысл определить callback-процедуру в духе sql_select_loop:

my $nos = ;

sql (pays => [
 [ id_contrat => $data -> {id} ],
 [ ORDER => 'no'],
], sub {
  $nos .= '; ' if $nos;
  $nos = $i -> {no};
});

Процедура приписывается в конец списка параметров sql и распознаётся там по типу (ссылка на код). Текущая запись доступна внутри callback-процедуры как глобальная переменная $i.

Выборка отдельных записей

Выше мы вкратце коснулись случая, когда функция sql возвращает ссылку не на список, а на хэш. Происходит это в том случае, когда по построению запроса очевидно, что запись может быть лишь одна: при LIMIT => 1. То же самое мы имеем при указании фильтра по id (это поле в Eludia всегда является первичным ключом):

my $data = sql (tasks => [[id => $id]]);

Если $id == $_REQUEST {id}, то есть на экранах-карточках и в обработчиках действий, то фильтры можно не указывать вовсе:

my $data = sql ('tasks');

Здесь вызов sql выглядит полностью аналогично sql_select_hash. Однако если требуется одновременно извлечь несколько связанных записей, то минимализмом приходится чуть-чуть пожертвовать. Впрочем, это окупается: ведь

my $data = sql (docs => ['id'], 'departments', 'factories', 'groups');

эквивалентно

my $data = sql_select_hash ('docs');
$data -> {department} = sql_select_hash (departments => $data -> {id_department});
$data -> {factory}    = sql_select_hash (factories   => $data -> {department} -> {id_factory});
$data -> {group}      = sql_select_hash (groups      => $data -> {factory} -> {id_group});

Заметим, что в данном случае (то есть в режиме извлечения отдельной записи) у привязанных таблиц по умолчанию извлекаются все поля, так что нам не приходится явно заказывать, скажем departments(*) или departments(id, label, id_factory), как пришлось бы делать в режиме выборки списков записей. Это предусмотрено специально для экранов просмотра записей, содержащих сведения об охватывающих объектах (скажем, для карточки платежа — реквизиты договора, приказа о его заключении и т. п.)

Выражения как поля выборки

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

sql ("users (id, f || ' ' || LEFT(i, 1) || '. ' || LEFT(o, 1) || '.' AS fio)");

Если AS-опция не задана, то имя поля синтезируется автоматически. Например, в результате

sql ('users (UPPER(label))');

единственное поле будет называться upper_label. Вообще имена генерируются так: в выражении находятся все фрагменты, похожие на идентификаторы, после чего они при водятся к нижнему регистру и склеиваются через символ '_'.

Агрегатные функции и GROUP BY

В выражениях можно использовать агрегатные функции: такие, как COUNT, SUM, MAX. При этом, если запрашиваются одни лишь агрегаты, то выборка содержит только одну запись:

my $cnt   = sql ('users (COUNT(*))');                      # возвращается скаляр-число

my $stats = sql ('pays (SUM(total_rur), SUM(total_usd))'); # возвращается ссылка на хэш

А если наряду с агрегатными запрашиваются и обычные скалярные выражения, то по списку последних выполняется GROUP BY и функция sql возвращает список хэшей, аналогично sql_select_all, либо, при указании callback-функции, производит итерацию по выборке, как sql_select_loop:

sql ('users_workgroups(COUNT(*) AS cnt)' => [], 'workgroups(label)', sub {...});

В этом случае по умолчанию ORDER BY совпадает с GROUP BY. При необходимости его можно переопределить псевдофильтром ORDER.

Выражения с агрегатными функциями можно использовать не только как компоненты выборки, но и в фильтрах. Такие фильтры порождают условия не в WHERE-, а в HAVING-части запроса. Например, вот как можно получить список имён рабочих групп с количеством участников более 10:

my $big_groups = sql ('users_workgroups(COUNT(id))' => [
 ['COUNT(id) >' => 10],
], 'workgroups(label)');

Для сравнения приведём эквивалентный вызов sql_select_all:

my $big_groups = sql_select_all (q {
 SELECT
  COUNT(users_workgroups.id) AS count_users_workgroups_id
  , workgroups.label
 FROM
  users_workgroups
  LEFT JOIN workgroups ON users_workgroups.id_workgroup = workgroups.id
 WHERE
  users_workgroups.fake = 0
 GROUP BY
  workgroups.label
 HAVING
  COUNT(users_workgroups.id) > ?
 ORDER BY
  workgroups.label
}, 10);

Модификация данных

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

Добавление записей

В настоящее время поддерживается 3 таких режима, наборы параметров характеризуются тем, что 2-й аргумент является ссылкой на хэш. Для сравнения: в случае выборки данных он либо отсутствует, либо является ссылкой на массив, либо строкой (которая превращается в ссылку на массив).

Если аргументов ровно 2, то вызов sql эквивалентен вызову sql_do_insert с теми же аргументами:

my $id_doc = sql (docs => {

 id_author => $_USER -> {id},

});

Если аргументов более 2 и 3-й из них не является ссылкой, то с тем же набором параметров вызывается sql_clone:

my $clone = sql (users_templates => $prototype, 

 $field => $value

);

Если же 3-й параметр — ссылка (в настоящее время он может быть ссылкой только на массив), то вызывается sql_select_id:

my $id_org = sql (orgs => {

 -label => $label,

  ogrn  => $ogrn,

  inn   => $inn,
  kpp   => $kpp,

}, ['ogrn'], ['inn', 'kpp']);

Удаление записей

Вызов sql для формирования DELETE-запроса выглядит почти так же, как для SELECT, единственное отличие — псевдофильтр 'DELETE' без параметра. Вот реальный жизненный пример: запрос на удаление всех элементов расписания в заданном временном интервале для пользователей с заданными id:

sql (users_schedule => [
	
 DELETE,
		
 [ id_user       => $_REQUEST {_users}   ], # параметр векторный, так что формируется условие IN (...)
 ['start_dt >= ' => $_REQUEST {_start_dt}],
 ['end_dt   <= ' => $_REQUEST {_end_dt}  ], # может быть пустым — тогда фильтр отключается

]);

Редактирование записей

Предусмотрен также псевдофильтр UPDATE, аналогичный DELETE, но с обязательным параметром: списком присвоений. Каждый его элемент является списком, который содержит либо 1, либо 2 элемента.

Списки из 1 элемента подставляются в SET как есть: предполагается, что они должны содержать SQL-выражения, в том числе зависящие от полей входящих в JOIN.

Списки из 2 элементов порождают фрагменты вида "field_name = ?", соответствующее значение (заданное 2-м элементом списка) передаётся как параметр при исполнении запроса.

sql (schedule => [

 [id => $ids],

 [UPDATE => [
  ['dt_updated = NOW()'],
  [id_template => $j -> {id}],
 ]],

]);

Краткая сводка

В заключение приведём все описанные варианты вызова функции sql и соответствующие форматы результатов.

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

Если аргументов более одного и 2-й из них является ссылкой на хэш, то производится вызов:

sql_do_insert
когда аргументов ровно 2
sql_clone
когда 3-й аргумент — строка
sql_select_id
когда 3-й аргумент — ссылка

с тем же набором параметров. Значение первого параметра в этом случае должно совпадать с именем одной из таблиц схемы.

Если указан псевдофильтр UPDATE, то формируется и исполняется UPDATE-запрос, аналогично для псевдофильтра DELETE.

Если для корневой таблицы указан список выбора из единственного элемента, отличного от '*', а также фильтр по полю id или LIMIT => 1 то возвращается скаляр (аналогично sql_select_scalar). То же происходит в случае, когда выбирается лишь одно поле и оно является агрегатным (COUNT, SUM и т. п.)

Если для корневой таблицы указан список выбора из единственного неагрегатного элемента, отличного от '*', то возвращается ссылка на скаляр: список id через запятую (аналогично sql_select_ids).

Если указан фильтр по полю id или LIMIT => 1, либо если все поля выборки являются агрегатами, то возвращается ссылка на хэш: единственную запись (аналогично sql_select_hash).

Если в качестве последнего параметра передана ссылка на процедуру, то она вызывается для каждой записи в выборке, аналогично sql_select_loop.

Если указан LIMIT с 2 аргументами (старт и порция), то возвращается ссылка на список (выборка) и дополнительно скаляр: объём выборки без ограничителя (аналогично sql_select_all_cnt).

В остальных случаях возвращается ссылка на список (аналогично sql_select_all).

И последнее: опция $preconf -> {core_debug_sql} включает вывод отладочной информации sql в STDERR. Туда печатаются аргументы вызова и сгенерированные по ним SQL-запросы со списками параметров.

Персональные инструменты
Пространства имён

Варианты
Действия
Навигация
Разработчику
Администратору
Инструменты