README

Подготовка и построение SQL-запросов из шаблонов с маркерами-заполнителями и значениями параметров для этих заполнителей.

Для простых запросов, в которых передаются данные только простых скалярных типов: int, float, string и bool, а так же типа NULL, заполнитель в SQL-шаблоне достаточно пометить двоеточием : или знаком вопроса ?. Заполнители замещаются значениями переданных скалярных значений в соответствии с правилами для каждого типа.

Примеры простых выражений.

SELECT * FROM `example` WHERE example_id = : AND title = ?;

INSERT INTO `example` (`example_id`, `target_id`, `user_id`, `title`, `status`) VALUES (?, :, :, ?, :);

Отличие заполнителя : от заполнителя ? заключается в различиях обработки значения NULL. При передаче в шаблон NULL, заполнитель : будет заменен пустой строкой '', а заполнитель ? константой NULL (без кавычек).

Если в запросе нужно указать сами символы : и ?, не в качестве заполнителей, а как часть строки SQL-выражения, то их необходимо удвоить: ::, ??.

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

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

INSERT INTO `example` (`example_id`, `target_id`, `user_id`, `title`, `status`) VALUES ({?}, {:}, {:}, {?}, {:});

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

Например, 0:, 3?, user_id:, status?. Эти заполнители так же можно заключать в фигурные скобки: {0:}, {3?}, {user_id:}, {status?}. Оба варианта эквивалентны.

Один шаблон SQL-запроса одновременно может содержать как именованные, так и нумерованные заполнители. Однако нужно учитывать, что для запросов с именованными заполнителями доступно только два метода их заполнения.

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

Например: ?i, :Q, firstname?S, 4:A, 2:k, user_id:N.

Спецификаторы скалярных типов.

Все спецификаторы скалярных типов записанные в верхнем регистре требуют строгого соответствия типа входного значения типу заполнителя.

Если тип значения не соответствует типу заполнителя, выбрасывается исключение.

Строго типизированные заполнители с вопросительным знаком позволяют передавать в них значение NULL и преобразуют его в строковую константу NULL.

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

Строковые спецификаторы.

:S, ?S, :s, ?s.

Все заполнители, специфицированные как строки, в тексте запроса заключаются в одинарные кавычки. Спецсимволы в строках экранируются функцией-замыканием, указанной при инициализации объекта класса Query.

UPDATE `example` SET `title` = ?S WHERE `example_id` = :

Приведение типов.

Для спецификаторов :s и ?s применяется приведение типов.

При приведение числовых типов к строке, число заключается в кавычки.

Логические типы TRUE и FALSE приводятся к строкам '1' и '0', соответственно.

Значение с типом NULL для заполнителя :s приводится к пустой строке '', для заполнителя ?s к строковой константе NULL.

Целочисленные спецификаторы.

:I, ?I, :i, ?i — любое целое число.

:U, ?U, :u, ?u — целое беззнаковое число. Значение числа должно быть положительно. Дополнительно проверяется беззнаковость значения, иначе выбрасывается исключение.

:N, ?N, :n, ?n — натуральное положительное число. Значение целого числа должно быть больше нуля, в противном случае выбрасывается исключение.

Приведение типов.

Для спецификаторов :i, ?i, :u, ?u, :n, ?n применяется приведение типов.

Строки приводятся к целым числам, если они являются числовыми и целочисленными.

Если строка не числовая или содержит число с плавающей точкой, то выбрасывается соответствующее исключение. Если полученное число не входит в числовой ряд, также выбрасывается исключение.

Логические типы TRUE и FALSE приводятся к числам 1 и 0, соответственно, кроме заполнителей :n и ?n. Для заполнителей :n и ?n только значение TRUE приводится к числу 1, при появлении FALSE выбрасывается исключение.

Значение с типом NULL для заполнителей :i и :u приводится к значению 0, для заполнителя :n выбрасывается исключение, для заполнителей ?i, ?u и ?n NULL приводится к строковой константе NULL.

Спецификаторы чисел с плавающей точкой.

:D, ?D, :d, ?d.

Приведение типов.

Для спецификаторов :d и ?d применяется приведение типов.

Строки приводятся к числам, если они являются числовыми. Если строка не числовая то выбрасывается исключение. Целочисленные значения и числовые строки с целочисленными значениями ошибкой не считаются.

Логические типы TRUE и FALSE приводятся к числам 1 и 0, соответственно.

Значение с типом NULL для заполнителя :d приводится к значению 0, для заполнителя ?d к строковой константе NULL.

Логические спецификаторы.

:B, ?B, :b, ?b — логическое значени, если позволяет движок базы данных.

В базах данных, таких как PostgreSQL, поддерживающих логические типы, логические значения могут передаваться в заполнители в виде строковых констант TRUE и FALSE, о чем нужно сообщить объекту класса Query при его инициализации. В противном случае заполнители :B, ?B, :b и ?b трактуются как целочисленные, ожидающие целое число от 0 до 1.

Приведение типов.

Для спецификаторов :b и ?b применяется приведение типов.

Число 0, строка '0' и пустая строка '' приводятся к значению FALSE. Значение NULL для заполнителя :b также приводится к значению FALSE, для заполнителя ?b к строковой константе NULL. Остальные значения приводятся к TRUE.

Спецификаторы констант и квалификаторов объектов базы данных.

:C, ?C — константные строки.

Константные строки нужны для вставки в запрос константных выражений и системных переменных, таких как CURRENT_TIMESTAMP, @@GLOBAL.sql_mode, IS NOT NULL, LAST_INSERT_ID(), @MY_VARIABLE и т.п.

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

По умолчанию, константой считается строка, удовлетворяющая регулярному выражению /^@{0,2}[^\W\d]([\w\.\s,]*(\w|\)))?$/. Назначить другое регулярное выражение для констант можно при инициализации объекта класса Query.

:Q, :q — имена объектов базы данных.

Заполнитель :Q (в верхнем регистре), проверяет перед вставкой переданное значение на корректность, заполнитель :q (в нижнем регистре), после проверки добавляет к именам указанные при инициализации объекта класса Query экранирующие кавычки или скобки. По умолчанию используется опостров `, применяемый в MySQL и SQLite.

Экранирование выполняетсяя прозрачно для каждого имени объекта БД, то есть значение my_db.my_table.my_field будет экранировано как `my_db`.`my_table`.`my_field`.

По умолчанию, квалификатором считается строка, удовлетворяющая регулярному выражению /^[^\W\d]([\w\.]*\w)?$/u.

Задать иное регулярное выражение для квалификаторов можно при инициализации объекта класса Query.

Спецификаторы наборов значений.

:L, ?L — список значений.

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

:A, ?A, :a, ?a — ассоциативный массив.

Заполнитель ожидает ассоциативный массив, в котором ключи являются именами квалификаторов.

Массив преобразуется в строку вида `Q1` = 'V1', `Q2` = 'V2', .... `Qn` = 'Vn'. Ключи будут заключены в соответствующие типу БД экранирующие символы, кавычки, апострофы или скобки, которые нужно указать при инициализации объекта класса Query.

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

:K, :k — ключи массива, как квалификаторы.

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

Каждый ключ экранируется в соответствующие типу БД экранирующие символы, кавычки, апострофы или скобки, которые нужно указать при инициализации объекта класса Query.

Если экранирование не требуется, то метку нужно записать в верхнем регистре :K.

:V, :v — значения массива, как квалификаторы.

Заполнитель ожидает массив строк, эти значения будут использоваться в качестве имен квалификаторов. Список значений конвертируется в строку, в которой значения экранированы в соответствии с требованиями БД и перечислены через запятую.

Если экранирование не требуется, то метку нужно записать в верхнем регистре :V.

Условные подзапросы.

В запрос можно помещать условные вставки, заключенные в квадратные скобки.

Если заключенная между скобками [] часть строки содержит переменную-заполнитель и если хотя бы для одного из заполнителей в подстроке не передано значение, то такая подстрока будет изъята из строки запроса включая скобки [].

Так как квалификаторы БД в запросах могут заключаться в квадратные скобки, а значения могут содержать строки JSON или массивы, то к квадратным скобкам можно добавлять дополнительные маркеры, и передавать их вместе с шаблоном запроса в методы Query::stmt() и Query::attach().

Спецификатор условных подзапросов.

:f.

Если требуется сделать условный блок, явно не содержащий заполнителей, например, такой

SELECT * FROM books WHERE status = :b[ AND author_last_name IS NOT NULL]

нужно поместить внутрь квадратных скобок фейковый заполнитель, помеченный спецификатором :f.

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

Где размещать заполнитель, специфицированный маркером :f внутри блока [] не важно.

Запрос представленный выше можно записать как-то так:

SELECT * FROM books WHERE status = :b[:f AND author_last_name IS NOT NULL];

или

SELECT * FROM books WHERE status = :b[ AND author_last_name{:f} IS NOT NULL];

или

SELECT * FROM books WHERE status = :b[ AND author_last_name IS NOT NULL{:f}];

В последних двух случаях заполнитель необходимо заключать в фигурные скобки, иначе author_last_name:f и NULL:f будут трактоваться, как именованные заполнители.

Порядок заполнителей и ссылки.

По умолчанию, при подготовке SQL-выражения безымянным заполнителям присваиваются числовые индексы в порядке их появления в строке начиная с индекса 0.

Запрос

SELECT :q, :q, :q FROM :q WHERE :q = ?N[:f AND :q IS NOT NULL];

неявно будет трактоваться как

SELECT 0:q, 1:q, 2:q FROM 3:q WHERE 4:q = 5:N[6:f AND 7:q IS NOT NULL];

Эту последовательность можно изменить явно указав конкретный индекс для целевого заполнителя. Присваивать индексы всем заполнителям не обязательно. Например, если заполнитель с индексом 5 из предыдущего примера будет получать значение в методе заполнения первым, а остальные заполнители в том порядке в каком перечислены, то нужно явно присвоить шестому заполнителю с индексом 5 индекс 0.

SELECT :q, :q, :q FROM :q WHERE :q = 0?N[:f AND :q IS NOT NULL];

Это будет эквивалентно выражению:

SELECT 1:q, 2:q, 3:q FROM 4:q WHERE 5:q = 0:N[6:f AND 7:q IS NOT NULL];

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

Например, если в примере выше второе поле в выражении SELECT то же самое, что и первое поле из выражения WHERE, а третье поле из выражения SELECT то же самое, что и поле из условного блока выражения WHERE, то всё SQL выражение можно записать так:

SELECT :q, :q, :q FROM :q WHERE {1} = ?N[:f AND {2} IS NOT NULL];

Эквивалентно выражению:

SELECT 0:q, 1:q, 2:q FROM 3:q WHERE 1:q = 4?N[5:f AND 2:q IS NOT NULL];

Теперь заполнитель, ранее имевший индекс 5, будет под индексом 4. Если его нужно поставить первым в списке заполнителей, то есть присвоить ему индекс 0, то нужно не забывать изменить индексы ссылок:

SELECT :q, :q, :q FROM :q WHERE {2} = 0?N[:f AND {3} IS NOT NULL];

Эквивалентно выражению:

SELECT 1:q, 2:q, 3:q FROM 4:q WHERE 2:q = 0?N[5:f AND 3:q IS NOT NULL];

Выбирая вместо ссылок явную запись индексов и имен заполнителей, нужно помнить, что нельзя допускать изменение типа и спецификатора повторного заполнителя с тем же индексом или именем. В случае обнаружения смены типа заполнителя будет выброшено исключение.

Установка и требования.

composer require ultra/data-query

Для работы библиотеки требуется PHP версии 8.4 или выше.

Методы класса Query.

Класс Query предоставляет четыре метода замены заполнителей значениями: Query::list(), Query::map(), Query::share(), Query::join(). В индексированные числами заполнители значения можно передавать любым из этих методов.

Если заполнителям в SQL-выражении присвоены имена, то для передачи значений можно использовать только Query::map() и Query::join().

Конструктор Query::__construct()

public Query::__construct(Closure $escape, string $quotes = '`', bool $booleans = false, string|null $constants = null, string|null $qualifiers = null);

Создаёт новый объект класса Query.

Список параметров

escape — Функция экранирования специальных символов в строке SQL.

Тип Closure. Обязательный параметр.

quotes — Символ или символы кавычек, в которые будут заключаться названия схем, баз данных, таблиц, полей таблиц и другие SQL квалификаторы.

Тип string. Опциональный параметр, по умолчанию имеет значение '`', стандартно применяемое в MySQL, MariaDB и SQLite.

В некоторых базах данных, например, в SQLite и Microsoft SQL Server, квалификаторы могут заключаться в парные квадратные скобки []. В таком случае, в качестве значения параметра нужно передать строку '[]'.

booleans — Логическая опция, указывающая методам класса Query, что запросы готовятся для использования в базах данных с поддержкой булевых типов данных, таких как PostgreSQL.

Тип bool. Опциональный параметр, по умолчанию имеет значение FALSE.

constants — Регулярное выражение, которое будет использоваться для проверки константных строк.

Тип string. Опциональный параметр, значение по умолчанию '/^@{0,2}[^\W\d]([\w\.$\s]*(\w|$))?$/'.

qualifiers — Регулярное выражение, которое будет использоваться для проверки строк имен квалификаторов.

Тип string. Опциональный параметр, значение по умолчанию '/^[^\W\d]([\w\.]*\w)?$/u'.

Пример

Query::stmt()

public Query::stmt(string $statement, string $marker = ''): void;

Принимает на сохранение SQL-выражение и готовит его для вставки значений в заполнители. Ранее сохраненное SQL-выражение заменяется.

Подготовленное SQL-выражение будет храниться и может использоваться многократно, пока не будет заменено новым SQL-выражением через метод Query::stmt() или Query::attach().

Список параметров

statement — Выражение SQL, в которое нужно вставить значения.

Тип string. Обязательный параметр.

marker — Дополнительный маркер условных блоков SQL-выражения.

Тип string. Опциональный параметр, по умолчанию пустая строка.

Условные блоки по умолчанию заключаются в квдратные скобки [], но в некоторых случаях квадратные скобки могут быть частью запроса. Например, квадратные скобки используются для экранирования квалификаторов в некоторых БД, в запросе могут присутствовать массивы или строки JSON. В этом случае необходимо добавить маркер к тем скобкам, которые являются частью условных блоков.

Пример

Query::attach()

public Query::attach(string $statement, string $marker = ''): void;

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

Список параметров

statement — Выражение SQL, в которое нужно добавить к уже сушествующему SQL выражению.

Тип string. Обязательный параметр.

marker — Дополнительный маркер условных блоков SQL-выражения.

Тип string. Опциональный параметр, по умолчанию пустая строка.

Пример

Query::list()

public Query::list(string|int|float|bool|Closure|array|null ...$variables): string;

Преобразует текущее подготовленное SQL-выражение (шаблон) в готовый SQL-запрос, заменяя метки-заполнители SQL-выражения значениями из списка параметров.

Список параметров

variables — список значений переменного размера и типа.

Тип значения в списке должен соответствовать спецификатору заполнителя, указанному в SQL-выражении. Допускаются следующие типы значений: string, int, float, bool, Closure, array, null. Если значением является замыкане, то в сигнатуре замыкания не должно быть обязательных параметров, так же замыкание должно возвращать значение одного из следующих типов: string, int, float, bool, array, null.

SQL-выражение не должно содержать именованные заполнители.

Количество передаваемых значений не должно быть меньше количества заполнителей в основной части строки SQL-выражения. Требование не распространяется на заполнители SQL-выражения, содержащиеся в условных блоках.

Возвращаемое значение

Метод вернёт строку с готовым к использованию SQL-запросом. В случае ошибки будет выброшено исключение.

Query::map()

public Query::map(array $options): string;

Преобразует текущее подготовленное SQL-выражение в готовый SQL-запрос, заменяя метки-заполнители SQL-шаблона значениями массива. Ключм массива будут сопоставлены с числовыми индексами или строковыми именами заполнителей и соответствующие значения массива будут

Список параметров

Query::share()

public Query::share(array $shared, string|int|float|bool|Closure|array|null ...$variables): string;

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

Список параметров

Query::join()

public Query::join(array $options): string;

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

Список параметров

options — массив значений.

Тип array. Обязательный параметр.

Первый элемент массива используется одновременно двумя заполнителями с индексами 0 и 1. Спецификатор заполнителей может различаться.

ultra / data-query

Maintainers

Package info

Statistics

Security

README

Примеры простых выражений.

Спецификаторы скалярных типов.

Строковые спецификаторы.

Приведение типов.

Целочисленные спецификаторы.

Приведение типов.

Спецификаторы чисел с плавающей точкой.

Приведение типов.

Логические спецификаторы.

Приведение типов.

Спецификаторы констант и квалификаторов объектов базы данных.

Спецификаторы наборов значений.

Условные подзапросы.

Спецификатор условных подзапросов.

Порядок заполнителей и ссылки.

Установка и требования.

Методы класса Query.

Конструктор Query::__construct()

Список параметров

Пример

Query::stmt()

Список параметров

Пример

Query::attach()

Список параметров

Пример

Query::list()

Список параметров

Возвращаемое значение

Query::map()

Список параметров

Query::share()

Список параметров

Query::join()

Список параметров

Пример