SQL template engine (SQL шаблонизатор)

Реализация

/**
 * Шаблонизатор SQL
 * Документация: https://github.com/rin-nas/sql-template-engine/edit/master/README.md
 *
 * @param string $sql Шаблон SQL запроса с необязательными метками-заменителями и условными блоками
 * @param array $placeholders Ассоциативный массив, где ключи — это метки-заменители,
 * а значения, это данные (любые типы), которые нужно заквотировать
 * @param object $quotation Объект, отвечающий за квотирование. Должен иметь методы:
 * * quote() -- для квотирования данных
 * * quoteField() -- для квотирования идентификатора объекта БД (название таблицы, колонки, процедуры и т.п.)
 *
 * @return SqlExpression Объект, в котором хранится готовое sql выражение
 * Объект имеет метод __toString(), возвращающий готовое sql выражение
 * Объект позволяет вставлять готовые куски SQL друг в друга без повторного квотирования
 * @throws \Exception
 * @link http://php.net/manual/en/pdo.prepare.php
 */
public static function bind(string $sql, array $placeholders, $quotation) : SqlExpression

/**
 * Делает bind() для каждого элемента переданного массива
 *
 * @param string $sql Шаблон SQL запроса с метками-заменителями
 * @param array $values Ассоциативный массив
 * Для строковых ключей массива в SQL шаблоне зарезервирована метка-заменитель @key
 * Для значений массива в SQL шаблоне зарезервированы метки-заменители:
 * :key, :row, :row[], :value, :value[]
 * @param object $quotation Объект, отвечающий за квотирование. Должен иметь методы quote() и quoteField()
 *
 * @return SqlExpression[]
 * @throws \Exception
 */
public static function bindEach(string $sql, array $values, $quotation) : array

Замена именованных меток-заменителей

Обработка условных блоков

SELECT {?total COUNT(*)}
 {?fields 
 t.*
 {, EXISTS(SELECT r.col FROM r WHERE r.id = t.id AND r.exists_id = :exists_id) AS exists}
 }
 FROM t
 WHERE TRUE
 {AND @col = :val}
 {AND t.id IN (:ids[])}
{?fields
 ORDER BY t.name
 {LIMIT :limit}
 {OFFSET :offset}
}

Пример использования 1

$data = [
 [
 'person_id' => null,
 'created_at' => (new \DateTime()),
 'recipient' => 'test1@mail.ru',
 'scores' => 10,
 'reason' => 'Невозможно доставить сообщение',
 ],
 [
 'person_id' => 555,
 'created_at' => '2020年09月10日 10:30:00',
 'recipient' => 'test2@mail.ru',
 'scores' => 90,
 'reason' => 'Email не существует',
 ],
 //...
];
$sql = $this->db->bind(
 'INSERT INTO @table (@fields[])
 VALUES :rows[]
 ON CONFLICT (lower(@recipient)) 
 DO UPDATE SET 
 @scores = LEAST(@table.@scores + EXCLUDED.@scores, :maxScores), 
 @reason = EXCLUDED.@reason',
 [
 '@table' => self::_TABLE,
 '@fields[]' => array_keys(reset($data)),
 '@recipient' => self::RECIPIENT,
 '@scores' => self::SCORES,
 '@reason' => self::REASON,
 ':rows[]' => $this->db->bindEach('(:row[])', $data),
 ':maxScores' => 100,
 ]
);
$this->db->query($sql);

INSERT INTO "user_email_blacklist" ("person_id", "created_at", "recipient", "scores", "reason")
VALUES 
 (NULL, '2020年09月13日T11:08:40.597+03:00'::timestamptz, 'test1@mail.ru', 10, 'Невозможно доставить сообщение'), 
 (555, '2020年09月10日 10:30:00', 'test2@mail.ru', 90, 'Email не существует')
ON CONFLICT (lower("recipient")) 
DO UPDATE SET 
 "scores" = LEAST("user_email_blacklist"."scores" + EXCLUDED."scores", 100), 
 "reason" = EXCLUDED."reason"

Пример использования 2

/**
 *
 * @param mixed[] $filter
 * @param array $fields
 * @param string $sort
 * @param int $limit
 * @param int $offset
 * @param bool $isCountTotal
 *
 * @return array[]|int
 * @throws \Rdw\X\Db\Exceptions\DbException
 * @throws \Exception
 */
public function getByFilter(
 array $filter = [],
 array $fields = [],
 string $sort = '',
 int $limit = 20,
 int $offset = 0,
 bool $isCountTotal = false
) {
 if (count($fields) > 0) {
 $this->selectAll = false;
 }
 $this->select = $fields;
 $this->return = [];
 $placeholders = [
 '?total' => $isCountTotal,
 '?fields' => ! $isCountTotal,
 '?has_subway' => $this->fieldSelected(static::FIELD_HAS_SUBWAY),
 '?ids' => count($filter['ids'] ?? []) > 0,
 '?not_ids' => count($filter['not_ids'] ?? []) > 0,
 '?type_ids' => count($filter['type_ids'] ?? []) > 0,
 '?query' => strlen($filter['query'] ?? '') > 0,
 '?big_cities_only' => ($filter['big_cities_only'] ?? false),
 '?sort_name' => $sort === self::SORT_NAME,
 '?sort_order_num' => $sort === self::SORT_ORDER_NUMBER,
 '?sort_query_rank' => $sort === self::SORT_QUERY_RANK,
 '?country_ids' => !empty($filter['country_ids'] ?? []),
 ':specify' => in_array(static::FIELD_SPECIFY, $fields)
 ? $this->db->bind(self::getFieldSpecifySQL(), [])
 : Database::BIND_SKIP,
 ':ids[]' => ($filter['ids'] ?? Database::BIND_SKIP),
 ':not_ids[]' => ($filter['not_ids'] ?? Database::BIND_SKIP),
 ':type_ids[]' => ($filter['type_ids'] ?? Database::BIND_SKIP),
 ':query' => ($filter['query'] ?? Database::BIND_SKIP),
 ':country_ids[]' => ($filter['country_ids'] ?? Database::BIND_SKIP),
 ':limit' => $limit,
 ':offset' => $offset,
 ];
 $field = static::FIELD_PAID_MODEL_AVAILABLE;
 if (($filter[$field] ?? null) !== null) {
 $placeholders['?filter_paid_model'] = true;
 $placeholders['?filter_pm_condition'] = !$filter[$field];
 $placeholders[':link_type_auto_bind'] = V3CityPublishers::LINK_TYPE_AUTO_BIND;
 }
 $sql = <<<'SQL'
{?query 
WITH
 normalize AS (
 SELECT ltrim(REGEXP_REPLACE(LOWER(:query::text), '[^а-яёa-z0-9]+', ' ', 'gi')) AS query
 ),
 vars AS (
 SELECT CONCAT('%', REPLACE(quote_like(trim(normalize.query)), ' ', '_%'), '%') AS query_like,
 CONCAT(
 '(?<![а-яёa-z0-9])', 
 REPLACE(quote_regexp(normalize.query), ' ', '(?:[^а-яёa-z0-9]+|$)')
 ) AS query_regexp
 FROM normalize
 )
}
SELECT
 {?total COUNT(*)}
 {?fields
 r.*,
 COALESCE(r.is_big_city, false) as is_big_city,
 {?has_subway EXISTS(SELECT 1 
 FROM v3_metro AS ms
 INNER JOIN v3_metro_branch AS mb ON mb.id = ms.metro_branch_id 
 WHERE mb.region_id = r.id 
 LIMIT 1) AS has_subway,}
 {:specify AS specify,}
 r.id
 }
FROM v3_region AS r
INNER JOIN v3_region_type AS rt ON r.region_type_id = rt.id
INNER JOIN v3_country AS c ON c.id = r.country_id
{?query , vars, normalize}
WHERE true
 AND r.city_id IS NOT NULL
 AND r.id != 1954 -- "Дальний Восток" исключается, т.к. для РФ это территория из нескольких субъектов федерации!
 AND r.region_type_id != 12
 {?type_ids AND r.region_type_id IN (:type_ids[])}
 {?ids AND r.id IN (:ids[])}
 {?not_ids AND r.id NOT IN (:not_ids[])}
 {?big_cities_only AND r.is_big_city = true}
 {?filter_paid_model AND {?filter_pm_condition NOT }
 EXISTS(SELECT 1 FROM v3_city INNER JOIN v3_city_publisher cp ON v3_city.id = cp.city_id 
 WHERE r.city_id=v3_city.id AND cp.link_type=:link_type_auto_bind)
 }
 {?query
 AND length(rtrim(normalize.query)) > 0 -- для скорости
 AND lower(r.name) LIKE vars.query_like -- для скорости
 AND lower(r.name) ~* vars.query_regexp -- для точности
 }
 {?country_ids AND r.country_id IN (:country_ids[])}
{?fields
 {?sort_name ORDER BY r.name, r.order_num}
 {?sort_order_num ORDER BY r.order_num, NLEVEL(r.ltree_path), name}
 {?sort_query_rank ?query ORDER BY lower(r.name) LIKE RTRIM(normalize.query), LENGTH(r.name), r.name}
 LIMIT :limit
 OFFSET :offset
}
SQL;
 $sql = $this->db->bind($sql, $placeholders);
 if ($isCountTotal) {
 return $this->db->fetchOne($sql);
 }
 return $this->db->fetchAll($sql);
}

Замечания по безопасности

SELECT /* @col1, */ col2 FROM t

SELECT /* " */ ", */ col2 FROM t

История возникновения