Метод для обновления в базе нескольких записей сразу с помощью PDO. Покритикуйте.

[Placido]

Есть текстовый онлайн-редактор с поддержкой замены текста по регулярному выражению. Выражения оформляются правилами, у каждого из правил есть своя группа, у группы правил есть своя категория. Применить можно как одно правило (=регулярное выражение), так и группу правил сразу. Обычно используется второй вариант (применение группы правил). Возникла необходимость подсчитать, сколько раз было применено то или иное правило (все случаи замены) и когда это было в последний раз, а потом занести эти данные в таблицу с полями `id`, ...(здесь много других полей)..., `used_at`, `times_used`. Можно, конечно, заносить в базу информацию после применения каждого отдельного правила, но если совпадений будет несколько сотен (вполне реальный случай), то будет и несколько сотен запросов (а также и несколько сотен обращений к базе) вида

'UPDATE `rules` SET `used_at` = NOW(), `times_used` = `times_used` + 1 WHERE `id` = номер правила;

Хотелось бы сделать это одним запросом после применения всех правил.
Для этого при применении группы правил создается массив, в который заносится номер правила и количество применений. Массив выглядит следующим образом:

$count = array(1 => 10, 2 =>13, 5 => 123, 17 => 43 и т.д.)

где 1, 2, 5 и 17 - номера правил (id правила в БД), 10, 13, 123 и 43 - количество совпадений этого правила (регулярного выражения).
Создал метод saveCount, в котором создается запрос вида

'UPDATE `rules`
SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id`
WHEN 1 THEN 10
WHEN 2 THEN 13
WHEN 5 THEN 123
WHEN 17 THEN 43
и т.д.
END
WHERE id IN(1, 2, 5, 17)

Вот его код:

public static function saveCount($count)
{
    $length = count($count);
    
    $case_thens = str_repeat("WHEN ? THEN ? ", $length);//формируется  строка WHEN ? THEN ? 
                                                        //                    ... 
                                                        //                    WHEN ? THEN ?
    $questionmarks = str_repeat("?,", $length - 1) . "?";//это вопросителные знаки - IN(?, ?, ?, ?)
    
    $conn = Connection::getInstance();
    
    $stmt = $conn->prepare(
            'UPDATE `rules`
                SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id`
                                    ' . $case_thens . ' END
                WHERE id IN(' . $questionmarks. ')');

    $i = 1;
    
    //Подставление значений в WHEN ? THEN ?
    foreach ($count as $rule_id => $rule_count) {
        $stmt->bindValue($i, $rule_id, PDO::PARAM_INT);
        $i++;
        $stmt->bindValue($i, $rule_count, PDO::PARAM_INT);
        $i++;
    }
    //Подставление значений в IN(?, ?, ?, ?)
    foreach ($count as $rule_id => $rule_count) {
        $stmt->bindValue($i, $rule_id, PDO::PARAM_INT);
        $i++;
    }

    $stmt->execute();
}

Все работает, но хотел бы услышать критику, может, можно сделать это проще или "правильнее". Заранее спасибо за комментарии.

 

[Фанат]

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

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

Моим классом это будет примерно так

$when = '';
foreach($count as $rule_id => $rule_count) {
  $when .= $db->parse("WHEN ?i THEN ?i ",$rule_id, $rule_count);
}
$sql = "UPDATE `rules` SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id` $when END WHERE id IN(?a)");
$db->query($sql,array_keys($count));

Даже са мудивился, насколько компактнее получилось.

 

[Absinthe]

Моим классом это будет примерно так

Можно ссылку?

 

[Фанат]

Он тока вылупился
А точнее - отлупился от корпоративного огромного класса в самостоятельный компактный вариант.
Сам-то код используется уже давно, но вот в самостоятельном варианте ещё толком не тестировал, но пару опечаток уже отловил.
Я хочу на выходные по-быстрому переписать пхпфак на нем, чтобы потестить в боевом режиме, и если все ок - выкладывать.
Я обязательно напишу об этом отдельным потсом

 

[Redjik]

Фанат
на гитхаб пожалуйста =)

кстати немного оффтопа, у меня ученик статью твою прочитал - понял процентов 30 - это нормально? =)

 

[Фанат]

Для ученика нормально, для статьи плохо, конечно.
Она плохо структурирована.
В частности, потому, что нацелена сразу на несколько категорий читателей - и на совсем новичков, и на ламеров-всезнаек, и на больших мальчиков.
Для всех нужен свой подход.
Я буду пытаться учитывать это в описании класса.
На самом деле хренову тучу вещей надо объяснять, как выяснилось. причем самого разного плана

 

[ksnk]

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

    function test_multySql()
    {
        $tpl = 'UPDATE `rules`
SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id`
{{?|format("WHEN %d THEN %d","
")}}
END
WHERE id IN({{?1|keys|join(",")}})';

        $sql = 'UPDATE `rules`
SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id`
WHEN 1 THEN 10
WHEN 2 THEN 13
WHEN 5 THEN 123
WHEN 17 THEN 43
END
WHERE id IN(1,2,5,17)';

        $data = array(1 => 10, 2 => 13, 5 => 123, 17 => 43);

        $func = $this->getSql('sql_child')->parse($tpl);
        $this->assertEquals($sql, $func('',$data));
    }

Это кусочек unit-теста. Проект https://github.com/Ksnk/sql_template. Сам проект сыроват, класс-обертка для базы данных, вероятнее всего будет переписываться, однако парсер работает довольно стабильно.

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

create_function("$_1,$_2","return 'UPDATE `rules`
SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id`
'.sql_template::_runtime_format($_2,\"WHEN %d THEN %d\",\"
\").'
END
WHERE id IN('.sql_template::_runtime_join(array_keys($_2),\",\").')';
"}

которая же и вызывается в нужный момент.
Довольно быстро парсится... Второй раз тот-же sql берется из кэша.

Язык в чем-то напоминает twig - двойные фигурные и фильтры. ? - это следующий по порядку параметр, ?N - N-тый по порядку параметр.
format - sprintf'ом обработать каждую пару ключ-значение и array_join.
keys - сделать array_keys с параметром
join - сделать array_join с массивом.

 

[radioheaded]

Откуда вы такие выражения берете?) Если я все правильно понял, то тут просто batch insert + update on duplicate key.

 

[serglt]

WHERE id IN({{?1|keys|join(",")}})';

Блииин, что это?? А способ реализации? А что же потом с этой конструкцией делать стороннему разработчику, который будет поддерживать код?
Я понимаю еще конструкции select * from t where x = ? или и их производные (insert into t set ?#), когда по запросу понятно хотя бы чего ожидать от заменителя, но тут же ...дец на лицо.

Посмотрел код, дык это ваще ппц, походу запрос будет быстрее выполняться, чем его генератор.
Смысл то в таких конструкциях?

 

[AmdY]

бля, fixxxer понарожал тут, sql в шаблонах. 2-й случай болезни за неделю, не считая заряжённого.
ksnk спасибо за ссылку на гитхаб. очень интересные идеии, завтра протрезвлею и обяъсню какой вы негодяй. нуууууу. если смогу.

 

[Placido]

И пошел срач...

Откуда вы такие выражения берете?) Если я все правильно понял, то тут просто batch insert + update on duplicate key.

Все данные приведены. Структура тоже (`id` (AUTO_INCREMENT) и `times_used` - INT, `used_at` - DATETIME). Не понимаю, как при заданных условиях использовать INSERT ... ON DUPLICATE KEY UPDATE

 

[Placido]

Фанату и ksnk спасибо за примеры. Кстати, громких "нет" против моего варианта не было...

 

[ksnk]

Кстати, громких "нет" против моего варианта не было...

Как можно заметить - все мастерят собственные велосипеды PDO под такие вещи довольно криво приспособлен, так что и такой вариант вполне годится.

А что же потом с этой конструкцией делать стороннему разработчику, который будет поддерживать код?
Я понимаю еще конструкции select * from t where x = ? или и их производные (insert into t set ?#), когда по запросу понятно хотя бы чего ожидать от заменителя, но тут же ...дец на лицо.

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

Посмотрел код, дык это ваще ппц, походу запрос будет быстрее выполняться, чем его генератор.

Конструктивнее, pls. Там в конце доки есть результаты бенчмаркинга по сравнению с PDO. C prepare и моим кэшированием и без препаре и с полным репарсингом. Результаты меня удовлетворяют.

 

[Redjik]

бля, fixxxer понарожал тут, sql в шаблонах. 2-й случай болезни за неделю, не считая заряжённого.
ksnk спасибо за ссылку на гитхаб. очень интересные идеии, завтра протрезвлею и обяъсню какой вы негодяй. нуууууу. если смогу.

Сразу вспомнилось =)

— Ну-ка, дыхни, — сказала Готтфрид.
Я повиновался.
— Ром, вишневая настойка и абсент, — сказал он. — Пил абсент, свинья! (с) "Три товарища"

 

[Фанат]

в запоминании пары

Неужели ты не видишь, что занимаешься самообманом?
неужели не понимаешь, что начав изобретать новый язык, ты не можешь остановиться на "паре"?
что придётся доизобретать новые "фильтры" под каждый новый случай?

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

 

[ksnk]

ты не можешь остановиться на "паре"?
что придётся доизобретать новые "фильтры" под каждый новый случай?

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

Я бы на твоем месте подумал о котеровском механизме фигурных скобок. Это единственная вещь, которая мне всё ещё нравится в его библиотеке.

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

 

[Фанат]

Фанату и ksnk спасибо за примеры. Кстати, громких "нет" против моего варианта не было...

Офигеть. Надо было топать ногами и срать кирпичами?

Ну давай я задам тебе вопроы, который задаю всем нубам, которые приходят с проблемой "запихнуть 100500 запросов в один":

Какова реальная необходимость в таком коде? Ты уже пробовал писать по отдельности? Замерял нагрузку? Каковы реальные цифры? Какова реальная необходимость пихать всё в один, городя тонны неочевидного говнокода?
Индекс на поле таймс юзед стоит?
Ты понимаешь, что этот случай - не единственный? И ПДО, в реальной жизни, в отличии от рекламы - неудобный говномонстр. И над ним в любом случае надо делать собственный враппер, как над любым другим API? Чтобы не плодить говнокод, пример которого приведён в сообщении выше?
Ты понимаешь, что единственная причина упираться в нативные препейреды в твоем случае как раз отсутствует - код исполняется ровно 1 раз?

 

[radioheaded]

И пошел срач...

Все данные приведены. Структура тоже (`id` (AUTO_INCREMENT) и `times_used` - INT, `used_at` - DATETIME). Не понимаю, как при заданных условиях использовать INSERT ... ON DUPLICATE KEY UPDATE

Стоп-стоп-стоп. Во-первых, структуры я не видел, видел только вот такой пример запроса

'UPDATE `rules`
SET `used_at` = NOW(), `times_used` = `times_used` + CASE `id`
WHEN 1 THEN 10
WHEN 2 THEN 13
WHEN 5 THEN 123
WHEN 17 THEN 43
и т.д.
END
WHERE id IN(1, 2, 5, 17)

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

Как использовать?

INSERT INTO rules (id, times_used, used_at)
VALUES (1, 10, now()), (2, 13, now()), (5, 123, now()), (17, 43, now())
ON DUPLICATE KEY UPDATE set times_used=times_used+VALUES(times_used)

Только я предпочитаю дату вычислять заранее и подставлять в запрос, чтобы этих now стопицот не было. Ну или могли бы поле сделать ON UPDATE CURRENT TIMESTAMP.

То есть, нужно сформировать лишь строку VALUES и все.

 

[ksnk]

То есть, нужно сформировать лишь строку VALUES и все.

Тем самым, on duplicate key обновится только время, а значения останутся прежними. Нет?

 

[radioheaded]

Тем самым, on duplicate key обновится только время, а значения останутся прежними. Нет?

Задает мне вопрос человек, написавший собственный велосипед-шаблонизатор SQL-запросов ) Конструкция «set times_used=times_used+VALUES(times_used)» вам ни на что не намекает?