Проблема
Когда я прихожу на новое место работы, то первым делом я смотрю на то, как организован слой для работы с СУБД. Я не работал с иными фреймворками помимо первого Yii, волею судьбы я попадаю на самописные решения, но везде я вижу практически одно и тоже — просто отвратительная реализация кода доступа к базам данных.
Все знают, что такое подготавливаемые запросы в PHP. Концепция, заложенная в них — это т.н. заполнители, маркеры или псевдопеременные вида "?", которые пишутся в строке запроса вместо реальных значений-аргументов, которые необходимо подставить в запрос:
SELECT * FROM `table` WHERE id = ?php.net так описывает данный механизм работы:
Во время запуска запроса клиент привязывает к псевдопеременным реальные значения и посылает их на сервер. Сервер, в свою очередь, подставляет их в шаблон и запускает уже готовый запрос на выполнение.Т.е. сначала уходит запрос, анализируется, и если всё хорошо — уходят наши данные, которыми и заменяются соответствующие метки:
SELECT * FROM `table` WHERE id = 123Преимущество данного подхода:
- Безопасность и предотвращение SQL-инъекций
- Подготовленный запрос можно запускать многократно. Сам текст запроса повторно не анализируется, равно как и не отсылается повторно шаблон запроса.
Однако, пользоваться из «коробки» абстракцией PDO или mysqli адаптером чертовски неудобно, необходимо вызвать как минимум 5 методов и плодить код:
/* создаем подготавливаемый запрос */
$stmt = $mysqli->stmt_init();
if ($stmt->prepare("SELECT District FROM City WHERE Name=?")) {
/* привязываем переменные к параметрам */
$stmt->bind_param("s", $city);
/* выполняем запрос */
$stmt->execute();
/* привязываем результаты к переменным */
$stmt->bind_result($district);
/* выбираем данные из результата */
$stmt->fetch();
printf("%s находится в округе %s\n", $city, $district);
/* закрываем запрос */
$stmt->close();
}
Далее. В больших проектах — большие SQL-выражения. Один раз я пытался разобраться в SQL-запросе на 500 строк кода, который, естественно, формировался «на лету» в зависимости от логики программы. SQL выполнялся как раз то ли через PDO, то ли через mysqli через подготавливаемые запросы. Как только я допускал логическую или синтаксическую ошибку, то приходилось писать на PHP «костыль», в которой я передавал и запрос и значения псевдопеременных для получения конечного, сформированного SQL с уже подставленными в его тело параметрами. Такие извращения я делал лишь для того, что бы взять SQL и запустить его в целях отладки.
Как видим, подготавливаемые запросы в «сыром» виде — неэффективны с точки зрения быстрой и удобной разработки. Они не позволяют получить сформированный SQL-запрос для отладки и имеют весьма неудобную для клиента реализацию. Кроме того, «использование подготавливаемых запросов не всегда приводит к повышению эффективности. Если параметризованный запрос запускается лишь раз, это приводит к большему количеству клиент-серверных обменов данными, нежели при выполнении простого запроса» — php.net.
Решение
Теперь делюсь своим решением — Database — класс для работы с MySql
- Избавляет от многословности — вместо 3 и более строк кода для исполнения одного запроса при использовании «родной» библиотеки, вы пишите всего 1
- Экранирует все параметры, идущие в тело запроса, согласно указанному типу заполнителей — надежная защита от SQL-инъекций.
- Не замещает функциональность «родного» mysqli адаптера, а просто дополняет его.
Прошу заметить — в данном решении я не сделал ничего лишнего. Большинство оберток под различные драйверы баз данных являются нагромождением бесполезного кода с отвратительной архитектурой. Их авторы, сами не понимая практической цели своих оберток, превращают их в подобие построителей запросов (sql builder), ActiveRecord библиотек и прочих ORM-решений. Библиотека Database не является ничем из перечисленных. Это лишь удобный инструмент для работы с обычным SQL в рамках СУБД MySQL — и не более!
В чем же преимущество данного решения? Давайте попробуем немного попрограммировать:
Экранирование
<?php
$db = Database_Mysql::create("localhost", "root", "password")
// Выбор базы данных
->setDatabaseName("test")
// Выбор кодировки
->setCharset("utf8");
// Получение объекта результата Database_Mysql_Statement
// Database_Mysql_Statement - "обертка" над "родным" объектом mysqli_result
$result = $db->query("SELECT `age` FROM `users` WHERE `name` = '?s'", "Петя");
// Получаем данные (в виде ассоциативного массива, например)
$data = $result->getOne();
В данном примере анализатор:
- Определяет маркер ?s как маркер строкового типа (s — string)
- Делает экранирование строкового параметра «Петя» посредством метода mysqli::real_escape_string
- Если больше не найдено маркеров и их количество соответствует количеству аргументов метода — выполняет запрос.
Для наглядности приведу пример, демонстрирующий экранирование строковых параметров с символом, который необходимо экранировать в SQL:
$db->query("SELECT '?s'", "- Милая хозяюшка, - сказал д'Артаньян");
SQL, который будет выполнен:
SELECT '- Милая хозяюшка, - сказал д\'Артаньян'Предусмотрен маркер и для экранирования данных при LIKE-поиске:
$db->query("SELECT `m` from `t` WHERE `m` LIKE '?S'", "Привет, %_username_%");
SQL-запрос после преобразования шаблона:
SELECT `advert_text` from `advert` WHERE `advert_text` LIKE 'Привет, \%\_username\_\%'Режимы работы библиотеки
Существует два режима работы библиотеки:
- Database_Mysql::MODE_STRICT — строгий режим соответствия типа заполнителя и типа аргумента. В режиме MODE_STRICT аргументы должны соответствовать типу заполнителя. Например, попытка передать в качестве аргумента значение 55.5 или '55.5' для заполнителя целочисленного типа ?i приведет к выбросу исключения:
// это выражение не будет исполнено, будет выброшено исключение: // Попытка указать для заполнителя типа int значение типа double в шаблоне запроса SELECT ?i $db->query('SELECT ?i', 55.5); - Database_Mysql::MODE_TRANSFORM — режим преобразования аргумента к типу заполнителя при несовпадении типа заполнителя и типа аргумента. Режим MODE_TRANSFORM установлен по-умолчанию и является «толерантным» режимом — при несоответствии типа заполнителя и типа аргумента не генерирует исключение, а пытается преобразовать аргумент к нужному типу заполнителя посредством самого языка PHP:
$db->query("SELECT ?i", '123.7');
SQL-запрос после преобразования шаблона:
SELECT 123
Допускаются следующие преобразования:
- К типу
int(заполнитель?i) приводятся
- числа с плавающей точкой, представленные как
stringили типdouble boolTRUE преобразуется вint(1), FALSE преобразуется вint(0)- null преобразуется в
int(0)
- числа с плавающей точкой, представленные как
- К типу
double(заполнитель?d) приводятся
- целые числа, представленные как
stringили типint boolTRUE преобразуется вfloat(1), FALSE преобразуется вfloat(0)nullпреобразуется вfloat(0)
- целые числа, представленные как
- К типу
string(заполнитель?s) приводятсяboolTRUE преобразуется вstring(1) "1", FALSE преобразуется вstring(1) "0". Это поведение отличается от приведения типаboolкintв PHP, т.к. зачастую, на практике, булев тип записывается в MySql именно как число.- значение типа
numericпреобразуется в строку согласно правилам преобразования PHP NULLпреобразуется вstring(0) ""
- К типу
null(заполнитель?n) приводятся- любые аргументы
Методы выборки
Что касательно методов выборки: существует два основных метода — query() и queryArguments(). Первый метод, как мы уже видели выше, принимает строку SQL-запроса и неограниченное количество параметров — аргументов для подстановки в маркеры запроса. Второй метод принимает строку SQL-запроса и массив параметров:
$sql = 'SELECT * FROM `users` WHERE `name` = "?s" OR `name` = "?s"';
$arguments[] = "Петр Первый";
$arguments[] = "Д'Артаньян";
$result = $db->queryArguments($sql, $arguments);
// Получим количество рядов в результате
$result->getNumRows(); // 2
SQL-запрос после преобразования шаблона:
SELECT * FROM `users` WHERE `name` = "Петр Первый" OR `name` = "Д\'Артаньян"
Методы получения результата
Методы аналогичны методам получения результата из объекта mysqli_stmt:
$result = $db->query('SELECT * FROM `t` LIMIT 0, ?i', 10);
while ($data = $result->fetch_assoc()) {
print_r($data);
}
а можно и всё сразу:
$result = $db->query('SELECT * FROM `t` LIMIT 0, ?i', 10);
$data = $result->fetch_assoc_array();
print_r($data);
а можно и одно значение:
$result = $db->query('SELECT 1 + ?i', 10);
echo $result->getOne(); // 11
Подсчитаем кол-во рядов в запросе:
$result = $db->query('SELECT * FROM `t`');
echo $result->getNumRows(); // 100500
Отладка запросов
Отлаживать неработающие запросы с помощью библиотеки одно удовольствие:
$result = $db->query('SELECT * FROM `t` WHERE a = ?i AND ', 100);
в исключении будет строка вида:
SQL: SELECT * FROM `t` WHERE a = 100 AND
История запросов
Вам доступна история всех запросов текущего соединения (рекомендуется выключать на боевом):
$db->query('SELECT 1 + ?i', 1);
$db->query('SELECT 1 + ?i', 2);
$db->query('SELECT 1 + ?i', 3);
print_r($db->getQueries());
Array
(
[SELECT 1 + 1] => SELECT 1 + ?i
[SELECT 1 + 2] => SELECT 1 + ?i
[SELECT 1 + 3] => SELECT 1 + ?i
)
Можно получить SQL до и после преобразования
$db->query('SELECT "?s"', 'Привет, Петя!');
echo $db->getQueryString(); // SELECT "Привет, Петя!"
echo $db->getOriginalQueryString(); // SELECT "?s"
Примеры работы с маркерами (их много, см. документацию)
Простая вставка данных через заполнители разных типов:
$db->query("INSERT INTO `users` VALUES (?n, '?s', ?i)", null, 'Иоанн Грозный', '54');
SQL-запрос после преобразования шаблона:
INSERT INTO `users` VALUES (NULL, 'Иоанн Грозный', 54)
Вставка значений через заполнитель ассоциативного множества типа string:
$user = array('name' => 'Пётр', 'age' => '30', 'adress' => "ООО 'Рога и Копыта'");
$db->query('INSERT INTO `users` SET ?As', $user);
SQL-запрос после преобразования шаблона:
INSERT INTO `users` SET `name` = "Пётр", `age` = "30", `adress` = "ООО \'Рога и Копыта\'"
Вставка значений через заполнитель ассоциативного множества с явным указанием типа и количества аргументов:
$user = array('name' => "Д'Артаньян", 'age' => '19', 'adress' => 'замок Кастельмор');
$db->query('INSERT INTO `users` SET ?A["?s", ?i, "?s"]', $user);
SQL-запрос после преобразования шаблона:
INSERT INTO `users` SET `name` = "Д\'Артаньян",`age` = 19,`adress` = "замок Кастельмор"
Это примерный список возможностей данного решения. Остальные возможности библиотеки вы найдете в документации.
Комментарии (99)
MetaDone
07.10.2017 00:10+1Автору следует открыть для себя https://github.com/auraphp/Aura.SqlQuery и не мучаться
Ну или попробовать почитать документацию по инструменту с которым работаешь
PravdorubMSK Автор
07.10.2017 00:12-3Причем тут конструктор запросов?
MetaDone
07.10.2017 00:30+1потому что просто «выполнятель» запросов можно сделать еще проще
https://gist.github.com/MetaDone/2fcf6e85c221469e5f37819fb47d83cePravdorubMSK Автор
07.10.2017 01:45-4Те же яйца, только в профиль — обертка над громоздки, неудобным PDO.
Никакой возможности отладки/получения Sql-кода. А она ох как часто бывает нужна.
Отсутствие полезны заполнителей. Максимум что дает PDO — подставить в запрос значение. Даже с массивами PDO не умеет работать и сделать что-то вроде:
DbQueryExec::getInstance()->fetchQuery("select * from users where id in (?) limit 1", [666, 777] );MetaDone
07.10.2017 08:29PDO умеет работать с массивами. И вот пример для наколеночной обертки
$data = [666,777,888,999]; $in = str_repeat('?,', count(data) - 1) . '?'; $sql = "SELECT * FROM users WHERE id in ($in)"; DbQueryExec::getInstance()->fetchQuery($sql, $data);
Никакой возможности отладки/получения Sql-кода
Не читать документацию — это плохая привычка
В общем используйте что вам удобно, только вашу библиотеку придется заменить/адаптировать если попадется новый проект с postgres к примеруPravdorubMSK Автор
07.10.2017 11:22-2И вот пример для наколеночной обертки
ну я об этом и говорил — PDO очень сырая. Очевидно в виду универсальности.MetaDone
07.10.2017 14:18и в чем же сырость? а писать
$db->query('SELECT * FROM `t` WHERE a = ?i AND ', 100);
и заставлять пользователей учить свой причудливый синтаксис — это не сырость?
Если вам удобно использовать ваше творение в проектах мне остается только порадоваться за вас, посочувствовать тем кому это достанется на поддержку и позлорадствовать когда будете использовать postgres/sqlitePravdorubMSK Автор
07.10.2017 16:48Причудливый синтаксис состоит из нескольких меток-заполнителей и ОДНА строка для выполнения запроса вместо 5 в PDO или mysqli.
vintage
07.10.2017 09:20+1Я когда-то делал такую обёртку над PDO:
$db->query( "select * from users where id in (" , [666, 777] , ") limit 1" )->fetchAll( \PDO::FETCH_ASSOC );
queryпросто нечётные параметры конкатенирует как есть, а чётные — экранирует. Очень удобно.
nukker7
07.10.2017 01:38+5Очень сильно напомнило DbSimple от Котерова, но простите, с того момента прошло уже больше 10 лет.
PravdorubMSK Автор
07.10.2017 01:40-4А причем тут время? С того времени ничего не поменялось — над СУБД лежит как правило самый первый слой. В идеале — как я описал — это удобный механизм для работы с Sql. Без нужды писать по 5 строк кода или использовать явно функции экранирования данных. Вы никуда от этого не уйдете, пока подобное не будет составлять часть ядра.
saggid
07.10.2017 07:49-1Ну, нынче люди обычно используют фреймворки и orm/query builder'ы. Вам тоже можно начать использовать) это удобно)
redfs
07.10.2017 10:16+1нынче люди обычно используют фреймворки и orm/query builder'ы. Вам тоже можно начать
Не сдержусь все-таки.
В вашей фразе слова «нынче», «обычно» и «можно начать» вообще неуместны в контексте данной статьи (в общем-то — действительно сильно спорной) и дискуссии.
Вообще, ваша фраза сейчас — модная, плюсуемая, но на самом деле говорит лишь о том, что c SQL вы работали на уровне «select id, title from articles»saggid
07.10.2017 11:08-2Вообще, это ваше сообщение говорит лишь о том, что вы пишете сюда только чтобы поспорить и видимо не знаете чем ещё занять себя из полезных дел.
PravdorubMSK Автор
07.10.2017 11:20Ещё раз — не надо путать мягкое и теплое.
Данный класс не имеет ни малейшего отношения ни к ORM, ни к билдеру.
Но во всех этих случаях всегда лежит как первый слой — слой работы с БД.
Над ним — билдер.
И потом это обрабатывается механизмами ORM.saggid
07.10.2017 12:08Ну это понятно, что он не имеет отношения ни к тому, ни к другому. Но это моего коммента не отменяет.
Библиотека не станет заменой doctrine/dbal. В проекте её лучше не использовать, ибо не пишут сегодня сырые SQL запросы в проектах, кому это нужно. Поверх неё не напишешь ORM / Query Builder, ибо слишком узкая область покрытия проблем у библиотеки — только одна СУБД, и плюс отсутствие массы других функций типа менеджера схем, кеширования, других типов данных (тот же json) — что предоставит уже сформировавшаяся и известная doctrine/dbal, к примеру.
В общем, как я и писал: лучше, наверное, вместо этой библиотеки в проекте использовать orm или query builder, который в свою очередь, да — будет основан на doctrine/dbal или каком-то другом слое для работы с БД, решающем проблемы, которые предполагает решать эта библиотека. И не заниматься написанием своих низкоуровневых абстракций над mysqli модулем, оно уже было сделано другими людьми, и вроде работает хорошо и успешно используется в мире.
PravdorubMSK Автор
07.10.2017 13:56ибо не пишут сегодня сырые SQL запросы в проектах
да ну, правда что ли? Отчет по финансам для бухгалтерии вы как будите делать? Если там JOIN на 20 таблиц?saggid
07.10.2017 15:08-1Дело в том, что как раз таки ORM и Query Builder'ы и упрощают подобные ситуации с 20-тью джойнами, позволяя разбивать весь процесс получения данных на подключение ясно определённых сущностей.
Если бы меня спросили, как бы я делал это, то я бы естественно для всех сущностей проекта создал по модели. Скорее всего, я бы использовал Laravel, так как в последние годы сижу на нём и сильно полюбил его.
Я бы создал отдельный класс, который бы инкапсулировал в себе всю логику расчёта данных для отчёта.
Я бы, естественно, определил связи между сущностями через механизм определения связей. И впоследствии я бы, скорее всего, подгружал необходимые зависимые данные даже вовсе без всяких join'ов (далеко не всегда обязательно подгружать данные именно таким образом), вместо этого, скорее всего, я бы использовал механизм быстрой подгрузки зависимостей, который решает проблему подгрузки данных в N + 1 запросов.
Скорее всего, для получения каких-то данных я бы также использовал Query Scopes, чтобы сделать ещё немного более красивым код выборки нужных мне данных.
Я не писал отчёты для бухгалтерии, так как, по воле судьбы, в разработке таких проектов я в последнее время не участвовал. Но я сейчас работаю над образовательным проектом, в котором нам нужно было реализовать выгрузку статистики прохождения мастер-тестов, созданных для группы лекций. Приведу немного кода из класса, решающего эту задачу:
/** * Получим из БД всю статистику прохождения теста * @param mixed $test */ private function getResults($test) { return TestResult::query() ->filterByObject($test) ->with('user') ->with(['answers' => function($answersQuery) { $answersQuery->orderBy('started_at'); $answersQuery->with(['question' => function($questionQuery) { $questionQuery ->withTexts() ->with('answers'); }]); }]) ->orderBy('started_at') ->get() ; }
Вот такой вот метод присутствует в этом классе. Он получает из БД всю необходимую информацию и, при этом, он практически не использует операцию join, вместо этого используя механизм горячей подгрузки зависимостей. Если вы внимательно изучите этот код, вы заметите, что здесь подгружаются следующие зависимости к основному набору сущностей результатов прохождения теста:
- пользователь, проходивший тест
- ответы пользователя, проходившего тест, у каждого из которых подгружаются:
- вопрос, на который был дан ответ, у которого подгружаются:
- текстовые данные вопроса на одном из языков (у нас мультиязычный проект, по умолчанию подгружается на русском)
- все доступные возможные варианты ответов на этот вопрос
- вопрос, на который был дан ответ, у которого подгружаются:
После этого уже в этом же классе другие методы обрабатывают всю эту информацию. Вот примерно так я на данный момент пишу свои проекты. Надеюсь, вам всё было понятно.
Fesor
07.10.2017 15:24+1то я бы естественно для всех сущностей проекта создал по модели.
даже если вам надо из базы только читать? Например репорты агрегировать?
который решает проблему подгрузки данных в N + 1 запросов.
это ж и есть джойн, просто неявный.
он практически не использует операцию join, вместо этого используя механизм горячей подгрузки зависимостей.
Простите, но как по вашему это работает?
После этого уже в этом же классе другие методы обрабатывают всю эту информацию.
предположим такой сценарий. Вечер вторника (что бы не понедельник и не ближе к пятнице), и к вам подходят и спрашивают — "слушай, тут митинг утром будет важный, и нам для нашего образовательного проекта нужно выгрузить метрики по быстренькому, что бы я графички для презентации смог быстро впилить". И даже если вы за час напишите свой этот класс, в базе оказалось столько данных что репорт генерился еще 2 часа.
В то же время можно было бы написать простенький sql и выгрузить в csv прямо из консольного клиента.
и sql этот сохранить (вместо всех этих ваших классов) потому что если штука полезная, потом можно быстренько впилить репорты по требованию что бы вас больше не дергали по пустякам.
SQL — крайне удобный инструмент для формирования репортов. Если можно сделать репорт целиком и полностью на SQL — то лучше так и сделать. Тут другой вопрос что можно упереться в возможности СУБД. То есть я знаю кейсы когда с mysql придется все же пилить пост обработку, в то время как с postgresql — это решается красиво и с меньшей когнитивной нагрузкой для тех, кто потом это будет поддерживать.
zoryamba
10.10.2017 11:43Простите, но как по вашему это работает?
в laravel-е генерится второй запрос видаselect * from table where id in (...)
id-шки берутся из результата первой выборки.Fesor
10.10.2017 13:21то есть это способ упростить гидрацию *-to-many ассоциаций, так? Что бы уменьшить необходимость фильтровать дубли в результатах. В той же доктрине подобный подход практикуют.
По факту это тот же джойн просто выполненный на стороне приложения.
Fesor
07.10.2017 15:27+2То есть мысль которую я хочу донести. ORM — прекрасный инструмент для упрощения записи (но не active record, он как раз что-то компромисное для чтения если надо мэпить на объекты), идеально для OLTP. В случае же с репортами или списочками всякими — простой SQL удобнее. Ну и писать и читать в одну и ту же штуку — тоже не очень то и удобно.
saggid
07.10.2017 15:37-1Честно говоря… Ну да, есть ещё такой вариант. Но я не могу согласиться, что во всех ситуациях он является более правильным. Скорее вы описали частный случай и возможное решение к нему.
Если всё будет именно как вы описали — я действительно, возможно, просто напишу SQL запрос и передам его человеку с инструкцией о том, как потом из него получить csv-файл с данными. Тут уже всё зависит от ситуации.
Самый прикол в том, что мы снова ушли в своём обсуждении туда, где данный класс из статьи для работы с БД становится ненужным. Вытащить csv-файл из sql-запроса можно кучей всяких разных программ, в конце концов можно просто утилитку свою написать для этого и передать её отделу статистики.
Fesor
08.10.2017 02:16что во всех ситуациях он является более правильным.
"правильно" — это баланс между плюсами и минусами в контексте конкретной задачи.
как потом из него получить csv-файл с данными.
\copy (your sql) To '/path/to.csv' With CSV. Зачем человека напрягать то? Я бы еще результат в гугл драйв сразу залил.
где данный класс из статьи для работы с БД становится ненужным.
я и не говорил что он нужен. В статье автор затронул один щепетильный вопрос — отладка prepared statemens и т.д. которая в случае с php весьма убого сделана. Но это проще фиксить на уровне pdo. Вот тут я был бы рад да.
saggid
08.10.2017 05:25Зачем человека напрягать то?
Чтобы он мог сам всегда актуальные данные получить без моей помощи. Иногда вы немного начинаете спорить о таких вещах, где можно было бы самому понять логику собеседника)
MetaDone
08.10.2017 10:07+1В статье автор затронул один щепетильный вопрос — отладка prepared statemens и т.д. которая в случае с php весьма убого сделана
Кажись в версии 7.2 как раз исправят на уровне PDO
https://wiki.php.net/rfc/debugging_pdo_prepared_statement_emulation_v2
bgBrother
07.10.2017 02:34Спасибо за публикацию, иногда полезно глянуть чужой код.
Заметил популярный недостаток — отсутствуют unit-тесты. ;)bgBrother
07.10.2017 11:08@$this->mysqli->close();
Подавление ошибок уже давно «не в моде».PravdorubMSK Автор
07.10.2017 11:14-3Иного способа нет. Насколько я помню, при ошибке не кидается исключение.
bgBrother
07.10.2017 11:22Какие именно у Вас там ошибки возникают? Разве нельзя проверить по результату функции, через mysqli::error или error_get_last, раз уж через исключения нельзя?
PravdorubMSK Автор
07.10.2017 13:55-1mysqli->close() вываливает warning или notice в ряде случаев. Я точно не помню. Поэтому ошибка так и глушится в коде.
L2jLiga
07.10.2017 11:15Я тоже писал подобный костыль, только не нашлось ему применения, кроме как на одном маленьком проекте, в более крупных масштабах нужно что-то более функциональное и универсальное. Как минимум миграции никто не отменял.
kraso4niy
07.10.2017 11:15Автору советую почитать php.net/manual/ru/pdostatement.execute.php и советую использовать возможности языка.
Если есть возможность то использовать doctrine2
В doctrine есть sql logger из коробки.
www.doctrine-project.org/api/dbal/2.4/class-Doctrine.DBAL.Logging.EchoSQLLogger.html
К тому же в доктрине довольно просто реализовать свой логгер.
Ну и на sql сервере можно включить query log что очень полезно в dev режиме.
А так во времена php4 ваше решение было бы норм.
bgBrother
07.10.2017 11:18Автор, откройте на Github раздел Issues. Скорее всего у Вас SQL-Injection…
PravdorubMSK Автор
07.10.2017 17:17Действительно, пример на php.net, котрый я там и отыскал, оказался здесь не рабочим. Спасибо!
FatalStrike
07.10.2017 13:23+4Перейдите уже на laravel/symfony и пожалуйста, я вас прошу, перестаньте позорить php сообщество. Когда уже вымрут эти динозавры, из-за которых о php разработчиках сложилось плохое мнение.
PravdorubMSK Автор
07.10.2017 13:51-3Давайте начнем с того, что laravel/symfony — это фреймворки.
Здесь я ничего не писал про фреймворки.
По теме есть что сказать?FatalStrike
07.10.2017 14:59+4Я всё сказал. Не позорьте php, из-за таких как вы страдают нормальные php разработчики. А так могу посоветовать — учитесь, пока вы уровень джуниора, сложно будет что-то объяснить, поэтому поверьте пока что наслово, здесь большинство вам говорят о том что ваше решение неудачно.
PravdorubMSK Автор
07.10.2017 16:45-4Мой уровень соответствующий, что бы написать то, что Вы в жизни не напишите. Но какая разница? Вы же сюда пришли не библиотеку смотреть, а рассказать что Вы используете laravel. У вас же, таких как вы, один ответ на все — фрейморк. Вы даже не обратили внимание на суть бибилотеки — не зря туча комментирующщих припрела сюда зачем-то ORM тему, хотя в статье об ORM нет ни слова! Вам это НЕ интересно. Вы приходите сюда для того, что бы не обсуждать решения, а критиковать и тешить своё ЧСВ.
FatalStrike
07.10.2017 18:34+1Действительно, такой ужас, что вы написали, мне бы даже в голову не пришло написать. Это даже кодом нельзя назвать.
saggid
07.10.2017 19:23+1Дело в том, что, даже если в вашей статье нет ни слова про ORM, это не значит, что проблемы этого мира в области работы с базами данных не следует решать с помощью ORM.
Я честно смотрю на это просто как на дикость. Вот получу я такой код, как ваш. И мне придётся сидеть и ковырять все эти сырые sql-запросы длиной в 500 строк? Боже упаси от работы в коллективе разработчиков, мыслящих подобным образом. Скорее всего, на ORM в ларавел это дело можно было бы сократить в 10-50 строк кода с одинаковым профитом.
Я же вроде мягко и подробно объяснил вам наверху, как было бы можно было альтернативным образом решить вашу проблему. Вы поймите, если вы сейчас в какой-то организации сидите и шлёпаете без зазрения совести такой код, и никто вам ничего плохого на это не говорит — то это вряд ли так будет всегда.
Или давайте так: объясните пожалуйста, у вас есть опыт разработки проектов на основе Laravel с более-менее полноценным использованием orm, relationships? Вам что-то не понравилось, что-то не устроило?
PravdorubMSK Автор
08.10.2017 13:10Дело в том, что, даже если в вашей статье нет ни слова про ORM, это не значит, что проблемы этого мира в области работы с базами данных не следует решать с помощью ORM.
Простите, но Вы просто дикость несете.
ORM — хорошая вещь. Я не спорю с этим. Вообще с этим не спорю. Но ORM нужен для трансляции реляционного представления в объектное. CRUD и все дела.
Но писать отчет на 500 строк SQL с кучей join, подзапросов, union и т.д. на ORM — это просто ненормально. В чем профит? SQL сам по себе — прекрасный инструмент. Доступный для всех разработчиков, понятный и прозрачный. НЕ МОЖЕТ, не сможет никогда громоздкая обертка заменить удобство нативного SQL. Я уж не говорю о том, что за эту реализацию нужно быть по рукам — это никому не нужно — транслировать 500-строчный запрос в тот кошмар, что выше приводили.michael_vostrikov
08.10.2017 13:15А приведите пожалуйста тот запрос? Можете названия таблиц и полей поменять, если бизнес-тайна.
PravdorubMSK Автор
08.10.2017 13:17-3Какой тот запрос? на 500 строк? У меня его нет. Если интересно — устройтесь в компанию Биглион. Уверяю, Вам будет очень увлекательно посмотреть, какие запросы и какой код живет на проектах подобного уровня.
michael_vostrikov
08.10.2017 13:23Ну другой приведите, который по вашему мнению сложнее написать иначе чем сырым SQL. У вас ведь есть опыт работы с ними.
redfs
08.10.2017 18:40Михаил, а как насчет вашего простого запроса в несколько строк кода? :)
На самом деле ни количество строк кода, ни количество джоинов в запросе не является определяющим фактором при выборе инструментария, но дискутировать с автором статьи на эту тему почему-то не хочется.michael_vostrikov
08.10.2017 20:12Подловили) Так бы и сделал, хотя все-таки убрал бы GROUP_CONCAT и UNION. С другой стороны, если строк порядка миллиона и будет выполняться редко, то можно и в приложении сделать. Это все же проще в поддержке. Мне вот сейчас пара минут потребовалась, чтобы сообразить, как оно работает.
Вы правильно говорите, просто я к тому, что таких случаев сильно меньше, чем думает автор.
evnuh
07.10.2017 13:40+4И ведь это с серьёзным лицом пишет тот человек, который вчера выложил самый обсуждаемый пост с кучей негатива к хирургу.
Теперь немного понятно, почему автор считает невозможным быстро развиваться в должности разработчика, да потому что он в 2017 пишет самописный доступ к MySql и работал только с Yii на PHP. И это после 10 лет работы разработчиком???
PravdorubMSK Автор
07.10.2017 13:54«Самописный» доступ к MySQl нужен ВСЕГДА (PDO, mysqli, фрагменты фреймворков). Даже если используется ORM. Т.к. зачастую в серьезных проектах пишутся большие SQL-портянки, под которые не подходят ORM решения.
В таких случаях SQL пишется напрямую. Вопрос как удобнее это делать лежит ВНЕ фреймворков.
Alexmaru
07.10.2017 14:0190% интернетов это всё ещё wordpress с другими CMS-ками. Я имею ввиду обычных интернетов, а не энтерпрайзов и "я написал стартап". По моему опыту, там всё застыло в прошлом (потому что будущее увеличивает срок разработки, и срок подготовки к разработке), и только когда берёшь что-то типа laravel, можно расслабиться.
bgBrother
07.10.2017 14:02Лично мое мнение — если и писать свою библиотеку для БД, то с головой, изучая все нюансы языков.
Вряд ли этот код будет использоваться где-то кроме проектов самого автора, учитывая сколько подобных библиотек с более хорошим функционалом, поддержкой есть в Интернете.
michael_vostrikov
07.10.2017 15:54+3волею судьбы я попадаю на самописные решения
Потому что с такими подходами к разработке вас в другие места вряд ли возьмут.
Однако, пользоваться из «коробки» абстракцией PDO или mysqli адаптером чертовски неудобно
Как только я допускал логическую или синтаксическую ошибку, то приходилось писать на PHP «костыль»
Как видим, подготавливаемые запросы в «сыром» виде — неэффективны с точки зрения быстрой и удобной разработки.Пользоваться вашим способом тоже чертовски неудобно.
Поэтому лучше пользоваться фреймворками, в которых средства отладки уже написаны за вас.
Еще более быстро и удобно — это не писать SQL-запросы вручную. И да, для отчетов можно пару раз и вручную написать. Отчеты это не всё приложение.
Избавляет от многословности
Экранирует все параметрыЕсть менее многословные решения.
Экранирование по сравнению с именованными параметрами не является плюсом.
Давайте попробуем немного попрограммировать
Давайте.
$users = User::query()->where('name', 'Петя')->get(); $users = User::query()->whereIn('id', [1, 2, 3])->get(); $count = User::query()->where('name', 'Петр Первый')->orWhere('name', "Д'Артаньян")->count(); $user = User::query()->with('profile')->where('id', 1)->first(); $userData = ['name' => 'Пётр', 'age' => '30', 'adress' => "ООО 'Рога и Копыта'"]; $user = new User(); $user->fill($userData)->save(); $results = DB::select(DB::raw("SELECT * FROM some_table WHERE some_col = :somevariable"), ['somevariable' => $someVariable]); $data = User::find()->joinWith('profile')->joinWith('account')->joinWith('account.payments')->where(...)->asArray()->all(); // при этом, если в SQL синтаксическая ошибка, // выбрасывается исключение с текстом запроса и отображается на странице
Вывод — у вашего способа нет никаких преимуществ перед другими решениями.
PravdorubMSK Автор
07.10.2017 16:40-3Мне не интересно с вами спорить. В этой теме НЕТ построителя запросов. Это низкоуровневая библиотека для удобного написания SQL. И ВСЕ!
Еще более быстро и удобно — это не писать SQL-запросы вручную
Это Вы на Хабре можете рассказывать. Уверен, Вы не писали никогда отчеты. Когда SQL доходит до 500 строк и выше — только сумасшедший будет использовать для этого билдеры. Это НИКОМУ не нужно. Построители запросов нужны для другого — когда SQL формируется на основе некой логики программы. Тогда — да. Они нужны. Что бы не делать конкатенацию.michael_vostrikov
07.10.2017 17:01+2Но предлагаете-то вы ее использовать в бизнес-логике приложения. Те примеры, которые вы привели для демонстрации, эффективнее делать по-другому.
Когда SQL доходит до 500 строк и выше — только сумасшедший будет использовать для этого билдеры.
Во-первых, прочитайте следующее предложение. Во-вторых, обратите внимание на последние 2 примера. И да, иногда 500 строк SQL получаются из-за ограничений SQL. Вот неплохой пример.
когда SQL формируется на основе некой логики программы
Любой SQL формируется на основе некой логики программы. Сложная фильтрация для отчетов это как раз типичный пример.
lair
08.10.2017 01:59+1Когда SQL доходит до 500 строк и выше — только сумасшедший будет использовать для этого билдеры. Это НИКОМУ не нужно.
… про удобство работы со статически проверяемым кодом вы тоже не слышали, наверное? С другой стороны, если речь только о PHP...
saggid
07.10.2017 20:49User::find()->joinWith('profile')
Извините, это ведь ларавел, судя по семантике. Но такого метода не существует в нём, как я знаю. Это вы свой собственный скоуп такой написали? Или как оно у вас работает в таком виде? Можно узнать?
michael_vostrikov
07.10.2017 23:17Последний пример из Yii 2) Мне кстати ActiveRecord в нем больше нравится, чем в Laravel.
saggid
08.10.2017 11:59А какие ещё интересные фишки есть интересные в Yii по сравнению в Laravel?) Может расскажете.
А то я вот уже ишью создал разрабам ларавел на тему
joinWith.michael_vostrikov
08.10.2017 13:41Да сложно сказать. В Laravel можно отметить роутинг, middleware, удобность для API, в Yii работу с БД, behaviors, компоненты для UI, кодогенерацию.
Про joinWith я встречал какой-то вопрос, кажется на Laracasts, там было мол если хотите, делайте родительскую модель и добавляйте туда. Может на этот раз и передумают.
europanzer
07.10.2017 16:39+1Человек решал задачу написания запросов с базе. Ему начали говорить про ORM. Темы в общем-то параллельные. Не понимаю, что вызвало столько негатива.
redfs
07.10.2017 17:12+1Попробую кратко сформулировать пару основных (на мой взгляд) недостатков вашего решения.
1) Вы не только делайте обёртку над mysqli, но еще и (как верно отметил выше MetaDone ) — модифицируйте синтаксис самого языка запросов. Решение кмк тупиковое со всех точек зрения.
2) Как следствие — подготовленные запросы, которые эмулирует ваша библиотека (см. комментарий Chaak — на него стоит обратить внимание). Вы пишитеЭтот эскейпинг занимает доли секунд. Не то место для оптимизации.
и это ошибка. Зависит от задачи и объема обрабатываемых данных. Например, у меня некоторые статистические отчеты готовятся десятки минут с использованием множества промежуточных временных таблиц. Эффект применения подготовленных выражений на таких больших задачах весьма ощутим.PravdorubMSK Автор
07.10.2017 17:14-1модифицируйте синтаксис самого языка запросо
чем метка ?iотличается от метки ? в PDO?redfs
07.10.2017 17:22+1Ну, тем, что "?i" придумано вами, а "?" используется везде. И в mysqli, кcтати, тоже (не понял, что вы имели в виду, говоря о pdo). Грубо говоря, вы модифицировали синтаксис сиквела, и без вашей библиотеки ваши запросы жить не смогут.
PravdorubMSK Автор
07.10.2017 17:25вы модифицировали стандартный синтаксис сиквела
ерунду не говорите. никакой модификации SQL нет.
Ну, тем, что "?i" придумано вами
нет, не мной. Я увидел идею параметризованных заполнителей как минимум из phpfaq.ru/safemysql, много лет назад.redfs
07.10.2017 21:33никакой модификации SQL нет
С вами сложно дискутировать, потому что несмотря на то, что вы утверждаете, что в этой теме нет построителя запросов, на самом деле вы написали свой query builder. Но этого не понимаете.
Еще в статье вы пишете, что ваша разработка «не замещает функциональность «родного» mysqli адаптера, а просто дополняет его», а на самом деле вы вырубаете функциональность подготовленных выражений родного адаптера, и заменяете её своей, причем с потерей производительности. Хоть с этим то вы согласны?PravdorubMSK Автор
07.10.2017 21:44-1на самом деле вы написали свой query builder
где вы у меня увидели построитель запросов? Вы хоть раз видели построитель запросов? Вы что за дичь то несете?
и заменяете её своей, причем с потерей производительности
Нет не согласен. Вам даже php.net пишет, что подготовленные запросы создают большую нагрузку.redfs
07.10.2017 22:02+1Вы принимаете на входе некий шаблон запроса и преобразуете его в реальный запрос, который может уже без ругани проглотить и обработать mysqli. Ну назовите это «преобразователь» или как нибудь более благозвучно, если query builder вызывает у вас идиосикразию и ассоциируется только c какой-нибудь doctrine.
Ваши знания о подготовленных запросах лишь подтверждает невозможность продолжения дискуссии.
stychos
07.10.2017 23:21+2Нет, я тоже чертовски консервативен и застрял в двухтысячных. Я тоже использую собственный фреймворк и собственный слой абстракции к базам данных. Я тоже не понимаю, зачем мне тащить сотни мегабайт сторонних фреймворков и учить эти их причудливые, нелогичные, мутные аннотации и прочие забавные механизмы, когда я знаю каждый байт своего собственного и могу в любой момент разобраться в любой проблеме. Но автор, без именованных параметров это не годится.
akadex
08.10.2017 12:48Хочется поддержать автора… Действительно, все решения уместны, но в разных ситуациях. Если говорить о фреймворках, да тем паче таких как laravel, то они не для ресуроемких приложений. Последним был пример — проект федерального масштаба (не буду называть). Делали его ребята по всем канонам текущих тенденций. По итогу это привело к тому, что быстродействие приложения стало… просто никаким, объем правильного кода стал непомерным, как и издержки на его обслуживания. (грубо говоря вместо 1 разработчика 5 надо). И я лично видел много раз такие примеры. Люди видимо думают, что сейчас возьмут composer, и соединив вместе половину интернета :) обретут счастье. Выходит немного не так, точнее это приводит тому, что работает все критически медленно, а себестоимость обслуживания проекта серьёзно увеличивается. И вот тут могу сказать, что прилично сделанное самописное решение с вменяемой логикой, которая эффективно подходит для конкретной задачи — в 100 раз лучше. Как по мне, то пусть все будет, мир прекрасен своим многообразием. Адепты типовых фреймворков почему-то очень навязчивы. Надо с уважением относится к тем, кто пишет свой код. Всем удач!
lair
08.10.2017 12:52И вот тут могу сказать, что прилично сделанное самописное решение с вменяемой логикой, которая эффективно подходит для конкретной задачи — в 100 раз лучше.
Главное, чтобы это самописное решение покрывало все нужные кейсы и обладало нужным запасом расширяемости на будущее.
alexhott
08.10.2017 20:42-1Недавно пришлось обновить сервер freebsd + apache + php
до последней версии фряхи чтобы tls1.2 запустить.
Беда была в том, что использовалась mySQL и MSSQL
Под логику сайта были написаны пару классов и все обращения к бд решались запросами типа
$query='select param1 from table where a=".getparam($a)."';
то есть запрос собирался по месту и всегда можно посомтреть что там насобиралось. getparam- спец функции для проверки чтобы ничего лишнего не запихнули в параметр.
Покувыркаться пришлось с соединением с MS SQL.
В ПХП 7 остался один способ — PDO. И ничего страшного через пару дней удалось дописать сущестующий класс чтобы сайт заработал как и прежде. PDO обрабатывает весь запрос как есть если ему один пустой параметр подсунуть типа 1=1.
AntiPravdorub
10.10.2017 11:43-1Что напали то? Вечно всё усложняете, что легче
<?php echo 'Hello world';
или
<?php class Hello { public $hello; public function __construct() { $this->hello = 'Hello world'; } } class Singleton { private static $classHello = null; public static function getHelloClass() { if(!is_null(self::$classHello) { return self::$classHello; } return self::$classHello = new Hello(); } } echo Singleton::getHelloClass()->hello;
и не говорите что лучше второй способ…
Chaak
welcome to 2006
а там, где нужно есть doctrine/eloquent
PravdorubMSK Автор
> Doctrine — объектно-реляционный проектор (ORM)
> ORM Eloquent — красивая и простая реализация паттерна ActiveRecord
Не путайте мягкое с теплым.
VEnis
А вы про Doctrine что-то слышали кроме doctrine/orm? А то есть еще и doctrine/dbal уровнем ниже который можно использовать отдельно.
DOC_tr
Мне кажется, вы не совсем верно поняли паттерн ActiveRecord, весь по сути, ActiveRecord и есть ORM, во всяком случае очень близок к нему.
В вашей же реализации объекты из ActiveRecord представляют собой ни что иное как
PravdorubMSK Автор
Я смотрю хабраюзеры вообще не читают ничего…
У меня НЕТ никакой реализации ORM в этом решении.
DOC_tr
Да, вы правы. Неверно истолковал ваш комментарий.
Но в таком случае новый вопрос
И вы привели первый пример из мана по mysqli. Про PDO ни слова, хотя в нем по сути нужно только
В связи с чем вопрос — почему не просто сделать обертку PDO?
crmMaster
У человека тяжелый консерватизм головного мозга, он же сам признается:
> Я не работал с иными фреймворками помимо первого Yii
Так что вернитесь в 2008, там такое было норм.
PravdorubMSK Автор
Фреймворк дает ORM, билдер запросов. Не всегда фреймворк дает удобный механизм для работы с нативным SQL.
PravdorubMSK Автор
bgBrother
Напишите, если вспомните, пожалуйста. Действительно интересно когда возможности PDO не хватает. На будущее, так сказать.
Chaak
Как минимум ваше решение не использует преимущество prepared statement'ов на полную: они не кэшируются драйвером никак.
Правильнее было бы сначала генерировать SQL запросы с плейсхолдерами, а затем уже исполнять с параметрами.
Когда требуется выполнить 1 запрос n раз, ваше решение каждый раз делает эскейпинг вместо простой подстановки в скомпилированное выражение.
Ваше решение, извините, никуда не годится.
PravdorubMSK Автор
Этот эскейпинг занимает доли секунд. Не то место для оптимизации.
Fesor
Зависит от задачи. С другой стороны для таких задач уж точно эту библиотеку брать никто не будет.