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

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

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

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

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

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

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

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

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

Если проблема заключается в аккуратности кода, есть способы сделать код с застрявшим в нем SQL более презентабельным, и класс UserManager, показанный ниже, является хорошим способом для начала - класс содержит только запросы, которые относятся к таблице "пользователь", каждый запрос имеет свой собственный метод в классе, и запросы вставляются в инструкции prepare и форматируются так, как вы бы отформатировали их в таблице "пользователь". хранимая процедура.

// 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'];

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

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

С точки зрения производительности хранимые процедуры являются самыми быстрыми, но, как правило, не намного превосходят необработанный 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);

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

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