Руководство Melbis Shop: оглавление

2. Руководство разработчика › 2.5 Данные › Работа с базой данных

Весь обмен модуля с БД идёт через методы 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. Выборка данных

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

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);

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

• ORDER BY внутри запроса быть не должно. Оставленный внутри будет молча проигнорирован: порядок строк производной таблицы стандартом не гарантируется, и сервер вправе его отбросить.
• Все поля сортировки должны быть в списке полей запроса. Сортировка применяется снаружи, где видны только выбранные колонки. Забытое поле — ошибка SQL при первом же запуске.
• Имена полей не должны повторяться (SELECT s.id, p.id ...) — производная таблица такого не примет.

Направление проверяется по списку (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);
}

• SqlNumRows($query) — число строк в результате.
• SqlFetchHash($query) — очередная запись ассоциативным массивом.
• SqlFetchRow($query) — то же, но с числовыми индексами.
• SqlDataSeek($query, $pos) — перевести указатель на позицию.

2.5. Карта символьных имён

Условие в запросе пишется по идентификатору, а разработчик знает символьное имя: id в каждом магазине свой, skey один и тот же. Карту skey → id по любой справочной таблице отдаёт SysTableSkey('advert') — метод из другой семьи, разобран в разделе «Системные двери и утилиты». Там же и причина, по которой к валютам, поставщикам, языкам, группам покупателей и параметрам товаров он не нужен: у них есть свои двери, и id лежит в каждой строке.

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. Метод атомарно увеличивает счётчик и возвращает новое значение. Такая схема принята потому, что идентификаторы должны быть согласованы между сервером и нативным клиентом, работающим с той же базой. Когда записей много, счётчик двигают не по одной — см. 3.1.

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

• SqlLastInsertId() — последний идентификатор, выданный самой СУБД. С аргументом — SqlLastInsertId($id) — наоборот, задаёт его, как LAST_INSERT_ID(expr) в MySQL: следующий вызов без аргумента вернёт это значение.
• SqlAffectedRows() — сколько строк затронул последний запрос.

3.1. Пакетная запись

Импорт, пересчёт, перенос — работа, где записей не одна, а тысяча. Цикл из SqlGenId и SqlInsert обходится на ней в три запроса на каждую строку; блочные методы делают то же самое за считанные запросы.

SqlGenIdBlock($mTableName, $mCount) захватывает диапазон идентификаторов и возвращает их готовым массивом:

$ids = MELBIS()->SqlGenIdBlock('store_param', count($values));

Счётчик сдвигается одним запросом независимо от размера блока, так что тысяча идентификаторов стоит столько же, сколько один. Пустой запрос ($mCount меньше единицы) возвращает пустой массив и в базу не идёт — проверять размер набора перед вызовом не нужно. Имя таблицы, как и у SqlGenId, пишется без префикса; если такой строки в {DBNICK}_generator нет, разбор останавливается с ошибкой.

SqlInsertBlock($mLine, $mTableName, $mRows) вставляет массив записей одним запросом:

$rows = [];
foreach ( $values as $param_id => $value )
{
    $rows[] = [
        'id'        => array_shift($ids),
        'store_id'  => $store_id,
        'param_id'  => $param_id,
        'value_dec' => $value
        ];
}
$count = MELBIS()->SqlInsertBlock(__LINE__, '{DBNICK}_store_param', $rows);

Что нужно знать про набор строк:

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

Длинный набор движок режет на части сам — в подготовленный запрос помещается ограниченное число значений, а в сетевой пакет ограниченный объём. Части уходят отдельными запросами, поэтому «всё или ничего» блок сам по себе не обещает: если запись должна быть неделимой, оберните вызов в транзакцию (см. 5) или возьмите таблицы блокировкой (см. 6).

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__);
}

• SqlBegin($mLine) — начать транзакцию. Вложенные транзакции не поддерживаются: попытка начать вторую при активной первой останавливает разбор с ошибкой.
• SqlCommit($mLine) — зафиксировать.
• SqlRollback($mLine) — откатить.
• SqlTransactionActive() — идёт ли транзакция прямо сейчас. Полезно в библиотечной функции, которая не знает, вызвали её внутри транзакции или нет.

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

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

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

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

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

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

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

Занятые таблицы метод ждёт сам. Полная сигнатура — SqlTableLock($mLine, $mTables, $mUserId = 0, $mTries = false, $mPause = false): число попыток и пауза между ними в миллисекундах; false значит 10 попыток с паузой 500 мс. С умолчаниями false приходит после десяти попыток за пять секунд — короткую чужую работу метод пересиживает, не донося отказ до человека. Кому нужен прежний мгновенный ответ — передаёт одну попытку:

$taken = MELBIS()->SqlTableLock(__LINE__, '{DBNICK}_store', $user_id, 1);

6.1. Владелец блокировки

У обоих методов есть третий аргумент — $mUserId, сотрудник, для кого взята таблица. Он виден в списке блокировок программы, и по нему же блокировка снимается.

$taken = MELBIS()->SqlTableLock(__LINE__, '{DBNICK}_store', $user_id);

// ... работа ...

$gone = MELBIS()->SqlTableUnlock(__LINE__, '{DBNICK}_store', $user_id);

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

Занятость от владельца не зависит. Занятую таблицу не возьмёт никто, включая того, кто её держит: повторный SqlTableLock с тем же $mUserId тоже ответит false. Владелец решает не «кому можно взять», а «кто может снять».

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

if ( MELBIS()->SqlTableUnlock(__LINE__, '{DBNICK}_store', $user_id) === 0 )
{
    // Взяли не тем именем: таблица так и стоит занятой
}

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

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

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

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