Как управлять SQL-запросами

Лучший способ действий для вас будет зависеть от того, как вы приближаетесь к вашему доступу к данным. Существует три подхода:

• Использовать хранимые процедуры
• Сохраняйте запросы в коде (но включите все свои запросы в функции и исправьте все, чтобы использовать PDO для параметров, как упоминалось ранее)
• Используйте инструмент ORM

Если вы хотите передать свой собственный исходный SQL в механизм базы данных, тогда хранимые процедуры будут способом идти, если все, что вы хотите сделать, это получить исходный SQL из вашего PHP-кода, но сохраните его относительно без изменений. Хранимые процедуры против необработанных SQL-дискуссий – это битва святой войны, но К. Скотт Аллен делает превосходный момент – пусть и одноразовый – в статье о версиях баз данных :

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

Я склоняюсь к тому, чтобы не использовать хранимые процедуры. Я работал над проектами, в которых БД имеет API, открытый через хранимые процедуры, но хранимые процедуры могут налагать некоторые свои ограничения, и эти проекты в той или иной степени используют динамически генерируемый необработанный SQL-код для доступа к БД.

Наличие уровня API в БД дает лучшее разграничение обязанностей между командой БД и командой Dev за счет некоторой гибкости, которую вы имели бы, если бы запрос содержался в коде, однако проекты PHP менее склонны к значительным достаточно команд, чтобы извлечь выгоду из этого разграничения.

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

Независимо от того, выбираете ли вы не использовать хранимые процедуры, вы должны, по крайней мере, обеспечить, чтобы каждая операция базы данных хранилась в независимой функции, а не была встроена в каждый скрипт вашей страницы – по существу уровень API для вашей БД, который поддерживается и версируется с вашим кодом. Если вы используете хранимые процедуры, это будет эффективно означать, что у вас есть два уровня API для вашей БД: один с кодом и один с БД, что может казаться излишне усложняющим, если ваш проект не имеет отдельных команд. Конечно.

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

 // UserManager.php: class UserManager { function getUsers() { $pdo = new PDO(...); $stmt = $pdo->prepare(' SELECT u.userId as id, u.userName, g.groupId, g.groupName FROM user u INNER JOIN group g ON u.groupId = g.groupId ORDER BY u.userName, g.groupName '); // iterate over result and prepare return value } function getUser($id) { // db code here } } // index.php: require_once("UserManager.php"); $um = new UserManager; $users = $um->getUsers(); foreach ($users as $user) echo $user['name']; 

Однако, если ваши запросы очень похожи, но у вас есть огромное количество перестановок в ваших условиях запроса, таких как сложный пейджинг, сортировка, фильтрация и т. Д., Инструмент Object / Relational mapper – это, вероятно, путь, хотя процесс капитального ремонта вашего существующего кода использовать инструмент можно было бы довольно сложно.

Если вы решите исследовать инструменты ORM, вы должны посмотреть на Propel , компонент ActiveRecord Yii или PHP-ORM, Doctrine . Каждый из них дает вам возможность программно создавать запросы к вашей базе данных со всей сложной логикой. Doctrine является наиболее полнофункциональной версией, позволяющей вам создавать шаблоны своей базы данных с такими элементами, как шаблон дерева вложенных наборов .

С точки зрения производительности, хранимые процедуры являются самыми быстрыми, но обычно не намного выше raw sql. Инструменты ORM могут оказывать значительное влияние на производительность несколькими способами – неэффективное или избыточное выполнение запросов, огромный файл ввода-вывода при загрузке библиотек ORM для каждого запроса, динамическое генерирование SQL для каждого запроса … все эти вещи могут иметь влияние, но использование инструмента ORM может значительно увеличить доступную вам мощность с гораздо меньшим количеством кода, чем создание собственного уровня БД с помощью ручных запросов.

Гэри Ричардсон абсолютно прав, хотя, если вы собираетесь продолжать использовать SQL в своем коде, вы всегда должны использовать подготовленные инструкции PDO для обработки параметров независимо от того, используете ли вы запрос или хранимую процедуру. Санитация ввода выполняется для вас PDO.

 // optional $attrs = array(PDO::ATTR_PERSISTENT => true); // create the PDO object $pdo = new PDO("mysql:host=localhost;dbname=test", "user", "pass", $attrs); // also optional, but it makes PDO raise exceptions instead of // PHP errors which are far more useful for debugging $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $pdo->prepare('INSERT INTO venue(venueName, regionId) VALUES(:venueName, :regionId)'); $stmt->bindValue(":venueName", "test"); $stmt->bindValue(":regionId", 1); $stmt->execute(); $lastInsertId = $pdo->lastInsertId(); var_dump($lastInsertId); 

Предостережение: при условии, что идентификатор равен 1, приведенный выше сценарий будет выводить string(1) "1" . PDO->lastInsertId() возвращает идентификатор в виде строки независимо от того, является ли фактический столбец целым или нет. Это, вероятно, никогда не будет проблемой для вас, поскольку PHP автоматически выполняет литье строк в целые числа.

На следующем выводится bool(true) :

 // regular equality test var_dump($lastInsertId == 1); 

но если у вас есть код, ожидающий, что значение будет целым числом, например is_int или PHP, «действительно, действительно, на 100% равно» оператору:

 var_dump(is_int($lastInsertId)); var_dump($lastInsertId === 1); 

вы можете столкнуться с некоторыми проблемами.

Редактирование: некоторые полезные обсуждения хранимых процедур здесь

Прежде всего, вы должны использовать заполнители в своем запросе, а не напрямую интерполировать переменные. PDO / MySQLi позволяет вам писать ваши запросы:

 SELECT * FROM sometable WHERE iUser = ? 

API будет безопасно подставлять значения в запрос.

Я также предпочитаю иметь свои запросы в коде вместо базы данных. Гораздо проще работать с RCS, когда запросы связаны с вашим кодом.

У меня есть правило для работы с ORM: если я работаю с одним объектом за раз, я буду использовать интерфейс. Если я сообщаю / работаю с записями в совокупности, я обычно пишу SQL-запросы для этого. Это означает, что в моем коде очень мало запросов.

Мне пришлось очистить проект, в котором много (дублирующихся / похожих) запросов, пронизанных уязвимостями инъекций. Первые шаги, которые я предпринял, заключались в использовании заполнителей и маркировке каждого запроса с помощью объекта / метода и исходной строки, в которой был создан запрос. (Вставьте PHP-константы METHOD и LINE в строку комментариев SQL)

Это выглядело примерно так:

– @Line: 151 UserClass :: getuser ():

 SELECT * FROM USERS; 

Занесение всех запросов в течение короткого времени предоставило мне некоторые отправные точки, по которым необходимо объединить запросы. (И где!)

Я бы переместил весь SQL в отдельный модуль Perl (.pm). Многие запросы могли повторно использовать одни и те же функции с несколько разными параметрами.

Общей ошибкой для разработчиков является погружение в библиотеки ORM, параметризованные запросы и хранимые процедуры. Затем мы работаем несколько месяцев подряд, чтобы сделать код «лучше», но он только «лучше» в развитии. Вы не делаете никаких новых функций!

Используйте сложность кода, чтобы удовлетворить потребности клиентов.

Используйте пакет ORM, любой полупристойный пакет позволит вам

1. Получить простые наборы результатов
2. Держите сложный SQL близко к модели данных

Если у вас очень сложный SQL, то представления также приятно сделать его более презентабельным для разных уровней вашего приложения.

Одновременно мы были в подобном затруднительном положении. Мы запросили конкретную таблицу различными способами, более 50+.

В результате мы создали единую хранимую процедуру Fetch, которая включает значение параметра для WhereClause. Конструктор WhereClause был создан в объекте Provider, мы использовали шаблон проектирования Facade, где мы могли бы счистить его для любых атак SQL-инъекций.

Что касается технического обслуживания, его легко модифицировать. SQL Server также довольно пригодится и кэширует планы выполнения динамических запросов, поэтому общая производительность довольно хороша.

Вам придется определять недостатки производительности на основе вашей собственной системы и потребностей, но все и все это очень хорошо работает для нас.

В PEAR есть несколько библиотек, таких как MDB2, которые делают запрос немного проще и безопаснее.

К сожалению, они могут быть немного многословными для настройки, и вам иногда приходится передавать им одну и ту же информацию дважды. Я использовал MDB2 в нескольких проектах, и я имел тенденцию писать тонкий шпон вокруг него, особенно для указания типов полей. Обычно я создаю объект, который знает о конкретной таблице и ее столбцах, а затем вспомогательную функцию, которая заполняет типы полей для меня, когда я вызываю функцию запроса MDB2.

Например:

 function MakeTableTypes($TableName, $FieldNames) { $Types = array(); foreach ($FieldNames as $FieldName => $FieldValue) { $Types[] = $this->Tables[$TableName]['schema'][$FieldName]['type']; } return $Types; } 

Очевидно, что этот объект имеет карту имен таблиц -> схемы, о которых он знает, и просто извлекает типы указанных вами полей и возвращает массив соответствующего типа, подходящий для использования с запросом MDB2.

MDB2 (и аналогичные библиотеки) затем обрабатывают подстановку параметров для вас, поэтому для запросов update / insert вы просто создаете хэш / карту из имени столбца для значения и используете функции autoExecute для построения и выполнения соответствующего запроса.

Например:

 function UpdateArticle($Article) { $Types = $this->MakeTableTypes($table_name, $Article); $res = $this->MDB2->extended->autoExecute($table_name, $Article, MDB2_AUTOQUERY_UPDATE, 'id = '.$this->MDB2->quote($Article['id'], 'integer'), $Types); } 

и MDB2 построит запрос, полностью справится и т. д.

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

Как я уже сказал, накладные расходы на установку кажутся сложными, но как только это будет сделано, запросы могут быть проще / более символичными для записи и (особенно) изменения. Я думаю, что MDB2 должен знать немного больше о вашей схеме, которая бы упрощала некоторые из часто используемых вызовов API, но вы можете уменьшить раздражение этого, инкапсулируя схему самостоятельно, как я уже упоминал выше, и предоставляя простые функции доступа, которые генерируют массивы MDB2 должны выполнять эти запросы.

Конечно, вы можете просто выполнять плоские SQL-запросы в виде строки, используя функцию query (), если хотите, поэтому вы не должны переходить на полный путь «MDB2» – вы можете попробовать это по частям и посмотреть, ненавижу это или нет.

Этот другой вопрос также содержит некоторые полезные ссылки в нем …

Используйте инфраструктуру ORM, такую ​​как QCodo, – вы можете легко сопоставить существующую базу данных

Я пытаюсь использовать довольно общие функции и просто передавать их различия. Таким образом, у вас есть только одна функция для обработки большей части вашей базы данных SELECT. Очевидно, вы можете создать еще одну функцию для обработки всех ваших ВСТАВКИ.

например.

 function getFromDB($table, $wherefield=null, $whereval=null, $orderby=null) { if($wherefield != null) { $q = "SELECT * FROM $table WHERE $wherefield = '$whereval'"; } else { $q = "SELECT * FROM $table"; } if($orderby != null) { $q .= " ORDER BY ".$orderby; } $result = mysql_query($q)) or die("ERROR: ".mysql_error()); while($row = mysql_fetch_assoc($result)) { $records[] = $row; } return $records; } 

Это как раз у меня в голове, но вы поняли. Чтобы использовать его, просто передайте функции необходимые параметры:

например.

 $blogposts = getFromDB('myblog', 'author', 'Lewis', 'date DESC'); 

В этом случае $ blogposts будет массивом массивов, которые представляют каждую строку таблицы. Затем вы можете просто использовать foreach или напрямую обращаться к массиву:

 echo $blogposts[0]['title'];