Работа с базой данных

Весь обмен модуля с БД идёт через методы MELBIS()->Sql*. Прямых обращений к драйверу СУБД из кода модуля нет и быть не должно: методы парсера, помимо самого запроса, ведут статистику для отладчика, проверяют префиксы таблиц и метят изменённые таблицы для системы кеширования.

1. Общие правила

Три вещи повторяются в каждом вызове.

Первым аргументом всегда идёт __LINE__. Это не формальность: по номеру строки отладчик показывает, какой именно запрос модуля выполнился, сколько занял и с какими параметрами, а при ошибке SQL — где её искать.

Имя таблицы пишется через {DBNICK}. Реальный префикс базы подставит движок. Если написать префикс руками, парсер остановится с ошибкой «Wrong prefix in table name» — проверка стоит намеренно, потому что на префиксе завязано отслеживание изменений таблиц и сброс кеша.

Значения передаются параметрами, а не подстановкой в строку:

$command = "SELECT id, name
              FROM {DBNICK}_store
             WHERE topic_id = :TOPIC_ID
               AND price <= :PRICE_MAX
            ";
$param = [
    'topic_id'  => $topic_id,
    'price_max' => $price_max
    ];
$store = MELBIS()->SqlSelect(__LINE__, $command, $param);

Плейсхолдер в тексте запроса пишется заглавными буквами с двоеточием — :TOPIC_ID. Ключи массива параметров движок приводит к верхнему регистру сам, так что в PHP их можно писать как удобно. Запрос уходит подготовленным (prepared statement): значение попадает в базу отдельно от текста запроса, поэтому кавычка или точка с запятой внутри значения ничего не сломают.

Массив параметром передать нельзя — драйвер связывает только скаляры. Список для IN (...) собирается в PHP, с обязательным приведением типа:

$brand_ids = array_map('intval', $brand_set);
$brand_line = implode(',', $brand_ids);
$command = "SELECT id FROM {DBNICK}_store WHERE brand_id IN ($brand_line)";

Приведение здесь — не украшение: это единственное, что стоит между списком из запроса пользователя и текстом SQL.

2. Выборка данных

Методы отличаются только формой результата — текст запроса и параметры у всех устроены одинаково.

Метод Возвращает
SqlSelect($mLine, $mCommand, $mParams) массив всех записей
SqlSelectFlat($mLine, $mCommand, $mParams) одну запись плоским массивом (пустой массив, если ничего не нашлось)
SqlSelectValue($mLine, $mCommand, $mDefault, $mParams) значение столбцов одной записи; форму и запасное значение задаёт $mDefault (см. 2.1)
SqlSelectLimit($mLine, $mCommand, $mOffset, $mLimit, $mParams) страницу записей и общее число строк
SqlSelectPage($mLine, $mCommand, $mOrder, $mOffset, $mLimit, $mParams) то же самое, но отсекая строки на стороне сервера
SqlSelectEnum($mLine, $mCommand, $mKey, $mValue, $mParams) все записи, у которых поле $mKey равно $mValue
SqlSelectEnumFlat($mLine, $mCommand, $mKey, $mValue, $mParams) одну такую запись
SqlSelectEnumValue($mLine, $mCommand, $mDefault, $mKey, $mValue, $mParams) значение столбцов такой записи (как SqlSelectValue)
SqlSelectStatic / SqlSelectStaticFlat / SqlSelectStaticValue то же, что SqlSelect/SqlSelectFlat/SqlSelectValue, но с кешированием результата в памяти (см. «Кеширование»)

2.1. Значение вместо записи

SqlSelectValue — надстройка над SqlSelectFlat для запросов, где нужна не запись, а значение: счётчик, MAX(...), одно-два поля. Метод берёт первую строку и отдаёт значения её столбцов, а не хеш с именами полей.

Форму результата и поведение при пустой выборке задаёт $mDefault — обязательный аргумент сразу после запроса.

Скалярный $mDefault → возвращается значение первого столбца; если строки нет или значение столбца NULL — сам $mDefault:

$param_count = [
    'clann' => $clann
    ];
$command = "SELECT COUNT(*) FROM {DBNICK}_store WHERE clann = :clann";
$total = MELBIS()->SqlSelectValue(__LINE__, $command, 0, $param_count);

NULL из базы — это «значения нет», он тоже уходит в $mDefault. Это и есть главный случай: SUM, MAX, MIN, AVG на пустой выборке возвращают одну строку со значением NULL (а не ноль строк), поэтому дефолт 0 для них обязателен — иначе вернётся NULL, а не число.

$mDefault-массив → его длина задаёт число столбцов. Возвращается числовой массив (не хеш) — раскладывается через list(). Лишние столбцы отбрасываются, недостающие и NULL-столбцы берутся из $mDefault по позиции; при пустой выборке возвращается весь $mDefault:

$param_id = [
    'id' => $id
    ];
$command = "SELECT name, price FROM {DBNICK}_store WHERE id = :id";
list($name, $price) = MELBIS()->SqlSelectValue(__LINE__, $command, ['', 0], $param_id);

SqlSelectEnumValue делает то же поверх SqlSelectEnumFlat (см. 2.2), а SqlSelectStaticValue — кеширующий вариант (см. «Кеширование»).

2.2. Enum-выборки: один запрос вместо сотни

SqlSelectEnum и SqlSelectEnumFlat решают классическую задачу: на странице выводится сорок карточек товара, каждая — отдельный модуль, и каждому нужна своя запись из store. Прямолинейное решение даёт сорок запросов.

Эти методы делают запрос один раз, раскладывают весь результат по значению поля $mKey и держат в памяти до конца запроса. Второй и последующие вызовы с тем же текстом запроса и теми же параметрами в базу уже не идут — они забирают готовую запись из разложенного массива:

// Внутри модуля карточки: запрос написан на все товары сразу,
// а модуль забирает из результата только свой
$ids = MELBIS()->EnumGet('kStore', $id);
$command = "SELECT id, name, price
              FROM {DBNICK}_store
             WHERE id IN ( $ids )
            ";
$store = MELBIS()->SqlSelectEnumFlat(__LINE__, $command, 'id', $id);

Список идентификаторов для такого запроса готовит модуль-родитель через EnumSet, а EnumGet его отдаёт — эта пара описана в разделе «Кеширование».

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

2.3. Постраничный вывод

Оба метода возвращают массив с ключами total (всего строк), offset, limit и rows (записи страницы).

$goods = MELBIS()->SqlSelectLimit(__LINE__, $command, $offset, $limit, $param);

MELBIS()->TplAssign($tpl, [
    'GOODS' => $goods['rows'],
    'TOTAL' => $goods['total']
    ]);

SqlSelectLimit запрос не трогает: сервер отдаёт весь результат, метод берёт из него нужный срез уже на стороне PHP. Работает на любой версии MySQL. На выдаче в сотню строк разницы не видно; на нескольких тысячах широких записей это мегабайты, прогнанные по сети и развёрнутые в PHP-массивы ради девяти карточек на экране.

SqlSelectPage оборачивает запрос, добавляет LIMIT и получает общее число строк оконной функцией COUNT(*) OVER(). Сервер возвращает ровно страницу. Требует MySQL 8.0+ или MariaDB 10.2+.

Сортировка передаётся отдельным параметром — массивом «поле → направление»:

$order = [
    'status_pos'    => 'asc',
    'price'         => 'desc',
    's.id'          => 'asc'
    ];
$goods = MELBIS()->SqlSelectPage(__LINE__, $command, $order, $offset, $limit, $param);

Три требования к запросу, которые движок не проверяет:

Направление проверяется по списку (asc / desc) — здесь ничего постороннего не пройдёт. А вот имя поля проверяется только по набору символов, и набор этот намеренно широкий: он пропускает скобки и двоеточие, чтобы работали выражения вида RAND(:TOWN_HASH). Значит через имя поля можно протащить вызов функции или подзапрос — кавычки и запятые отсекаются, но (SELECT ...) состоит из разрешённых символов.

Отсюда правило: имя поля сортировки задаёт разработчик, а не посетитель. Пришедший из адресной строки ?order=price разбирайте через switch и подставляйте в массив своё имя колонки — никогда не отдавайте пользовательскую строку в ключ напрямую.

2.4. Ручной перебор результата

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

$query = MELBIS()->SqlQuery(__LINE__, $command, $param);
$rows = MELBIS()->SqlNumRows($query);

MELBIS()->SqlDataSeek($query, 100);
for ( $i = 1; $i <= 20; $i++ )
{
    $hash = MELBIS()->SqlFetchHash($query);
}

3. Запись данных

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

// Вставка
$fields = [
    'id'        => MELBIS()->SqlGenId('store_param'),
    'store_id'  => $store_id,
    'param_id'  => $param_id,
    'value_dec' => $value
    ];
MELBIS()->SqlInsert(__LINE__, '{DBNICK}_store_param', $fields);

// Изменение: последний аргумент — поле (или массив полей) условия
$fields = [
    'id'        => $store_id,
    'price'     => $price,
    'status_key'=> 'kExist'
    ];
MELBIS()->SqlUpdate(__LINE__, '{DBNICK}_store', $fields, 'id');

// Удаление
MELBIS()->SqlDelete(__LINE__, '{DBNICK}_store_param', 'store_id', $store_id);

У SqlUpdate поля условия перечисляются в том же массиве $fields: движок раскладывает массив сам — что названо в $mKeyField, уходит в WHERE, остальное в SET. Условие может быть составным: MELBIS()->SqlUpdate(__LINE__, $table, $fields, ['store_id', 'param_id']).

SqlDelete принимает либо пару «поле, значение», либо массив условий целиком: MELBIS()->SqlDelete(__LINE__, $table, ['store_id' => $id, 'param_id' => $param]).

Идентификаторы новых записей выдаёт SqlGenId, а не AUTO_INCREMENT:

$store_id = MELBIS()->SqlGenId('store');

Имя таблицы передаётся без префикса — так, как оно записано в таблице {DBNICK}_generator. Метод атомарно увеличивает счётчик и возвращает новое значение. Такая схема принята потому, что идентификаторы должны быть согласованы между сервером и нативным клиентом, работающим с той же базой.

Вспомогательные методы:

4. Запись сырым запросом и сброс кеша

Если запись идёт не через SqlInsert/SqlUpdate/SqlDelete, а собственным запросом через SqlQuery, движок не знает, какие таблицы изменились — и кеш модулей, читающих эти таблицы, останется устаревшим. Пометить таблицы нужно самостоятельно:

$command = "UPDATE {DBNICK}_store
               SET rating = rating + 1
             WHERE id = :ID
            ";
MELBIS()->SqlQuery(__LINE__, $command, ['id' => $id]);
MELBIS()->SqlTableChange(__LINE__, '{DBNICK}_store');

SqlTableChange($mLine, $mTables, $mCheckAffect = true) принимает имя таблицы или массив имён. По умолчанию метка ставится, только если последний запрос действительно что-то изменил, — если строк не затронуто, кеш сбрасывать незачем. Передайте третьим аргументом false, чтобы пометить таблицы безусловно.

Возвращает метку времени или false, если изменений не было.

Это, пожалуй, самый лёгкий для пропуска метод во всём API: забытый вызов ничего не ломает сразу — витрина просто продолжает показывать старые данные, пока кеш не истечёт по времени.

5. Транзакции

MELBIS()->SqlBegin(__LINE__);
try
{
    MELBIS()->SqlInsert(__LINE__, '{DBNICK}_orders', $order);
    MELBIS()->SqlInsert(__LINE__, '{DBNICK}_order_goods', $goods);

    MELBIS()->SqlCommit(__LINE__);
}
catch ( \Throwable $e )
{
    MELBIS()->SqlRollback(__LINE__);
}

Учтите, что таблицы должны быть на движке, поддерживающем транзакции (InnoDB), — на MyISAM вызовы отработают без ошибок, но отката не произойдёт.

6. Блокировка таблиц

SqlTableLock и SqlTableUnlock — это не LOCK TABLES уровня СУБД, а кооперативная блокировка через служебную таблицу {DBNICK}_oper_block. Она нужна для согласования с нативным клиентом: пока менеджер выполняет пакетную операцию над товарами, фоновая задача сервера не должна лезть в те же таблицы.

if ( !MELBIS()->SqlTableLock(__LINE__, ['{DBNICK}_store', '{DBNICK}_store_param']) )
{
    // Таблицы уже заняты другой операцией — выходим
    return '';
}

// ... длительная пакетная работа ...

MELBIS()->SqlTableUnlock(__LINE__, ['{DBNICK}_store', '{DBNICK}_store_param']);

SqlTableLock возвращает false, если хотя бы одна из таблиц уже заблокирована; в этом случае ничего не блокируется и работу надо отложить. Блокировка договорная: она ничего не запрещает физически, а работает лишь постольку, поскольку все участники её проверяют. Забыть SqlTableUnlock нельзя — таблицы останутся помеченными занятыми до ручной очистки.

7. Обслуживание таблиц

SqlTableOptimize($mLine, $mTables = false) выполняет OPTIMIZE TABLE и ANALYZE TABLE. Без второго аргумента метод сам обходит все таблицы базы, у которых есть незанятое место (Data_free > 0) и движок MyISAM или InnoDB:

$result = MELBIS()->SqlTableOptimize(__LINE__);

Операция тяжёлая и блокирующая — её место в ночном cron-модуле, а не в коде витрины. Возвращает отчёт СУБД по каждой обработанной таблице.