Ключевые слова:database, sql, function, mysql, php, (найти похожие документы)
From: Антон Гришан
Date: Mon, 20 Mar 2009 17:02:14 +0000 (UTC)
Subject: Доступ к данным на основе хранимых процедур в веб-приложениях
Материал предоставлен редакцией журнала Системный администратор.
Опубликовано в журнале "Системный администратор" N 1 2009
Большинство приложений вынуждено работать с базами данных, общаясь с
СУБД на языке SQL-запросов. Иными словами, одни программы на языках
высокого уровня составляют другие программы на SQL. Это выглядит
привычным - поэтому кажется логичным и удобным, но так ли это на самом
деле?
Рассмотрим два способа взаимодействия приложения и базы данных. Первый
и на данный момент более популярный способ - генерация SQL-запроса в
теле скрипта (здесь и далее приводятся примеры для PHP5 + MySQL 5 с
установленным расширением mysqli):
/* Выбрать все города в стране с заданным кодом, динамическая генерация запроса */
$countryCode = 'RU';
$result = mysqli_query ($db, "SELECT Cities.name FROM Countries, Cities WHERE Cities.countryId = Countries.id \
AND Countries.code = '". mysqli_real_escape_string($db, $countryCode)."'");
Второй способ - доступ к данным через хранимые процедуры. Хранимая
процедура - объект базы данных, представляющий собой набор
скомпилированных SQL-инструкций. Скрипт вызывает процедуру со списком
параметров и обрабатывает полученный результат(ы).
Приведенный выше запрос (SELECT Cities.name FROM Countries, Cities
WHERE Cities.countryId = Countries.id AND Countries.code = 'RU') можно
сохранить в виде процедуры - getCities, с одним входным параметром -
код страны (countryCode char(2)).
Приведу пример создания хранимой процедуры:
DELIMITER $$
CREATE PROCEDURE `getCities`(countryCode char(2))
BEGIN
SELECT Cities.name FROM Countries, Cities WHERE
Cities.countryId = Countries.id AND Countries.code = countryCode;
END$$
DELIMITER ;
В момент вызова хранимой процедуры СУБД подставляет значение параметра
в тело запроса и возвращает результат выборки данных.
Для вызова хранимой процедуры "getCities" на стороне сервера БД
необходимо выполнить следующую команду:
CALL getCities('RU');
Вызвать процедуру getCities из PHP-приложения можно следующим образом:
/* Выбрать все города в стране с заданным кодом, вызов хранимой процедуры */
$countryCode = 'RU';
mysqli_multi_query($db, "CALL getCities('".mysqli_real_escape_string($db, $countryCode)."');");
Работа с БД через хранимые процедуры в настоящий момент встречается
реже, чем генерация SQL-запроса в теле программы. На мой взгляд, это
происходит по следующим причинам:
- наиболее популярная связка для написания веб-приложений - PHP +
MySQL, однако только в MySQL 5 стало возможно использовать хранимые
процедуры;
- в большинстве пособий по программированию приведены примеры работы с
БД, основанные на генерации запроса в теле скрипта, и начинающим
программистам сложно отойти от книжных примеров;
- написание приложения с использованием хранимых процедур
подразумевает умение программистов работать с хранимыми процедурами или
наличие времени для изучения данной технологии (что не всегда допустимо
в рамках конкретного проекта).
Преимущества использования хранимых процедур
- Повышение скорости работы БД. Процедуры хранятся в скомпилированном
виде, а значит, СУБД не тратит время на компиляцию запроса при каждом
его исполнении. Приложению не требуется тратить время на генерацию
запроса. Команда для вызова хранимой процедуры значительно короче, чем
запрос, содержащийся в теле процедуры, поэтому требуется меньше времени
и трафика на передачу команд на сервер БД.
- Большая степень свободы. Хранимые процедуры поддерживают: входные и
выходные параметры, локальные переменные, операторы условного
ветвления, циклы, вызовы встроенных команд и других процедур,
исполнение DDL-операторов. Во многом хранимые процедуры похожи на
процедуры языков программирования высокого уровня.
- Упрощение кода приложения. В приложении нет SQL-запросов, а значит,
программисту не нужно писать код для их генерации. Для вызова хранимой
процедуры необходимо знать только имя и список параметров (аналогично
вызову обычных функций/методов в теле приложения). Такой подход
сокращает размер кода и улучшает его читабельность, что положительно
влияет на качество конечного продукта.
- Безопасность. Использование хранимых процедур позволяет значительно
снизить угрозу возникновения уязвимости типа SQL-injection. Кроме того,
можно устанавливать права доступа к объектам базы данных для каждой
хранимой процедуры, что также способствует повышению уровня
безопасности приложения.
- Защита приложения от изменений структуры БД. В процессе развития
проекта может возникнуть необходимость в изменении структуры БД,
например, добавить/удалить/переименовать таблицу или столбец. Если
приложение генерирует SQL-запросы, то необходимо внести изменения во
все фрагменты кода, отвечающие за генерацию запросов. Организация
доступа через хранимые процедуры не требует внесения изменений в код
приложения до тех пор, пока имя хранимой процедуры и список параметров
(а также ожидаемый результат) остаются прежними.
- Снижение количества ошибок и упрощение отладки. Чаще всего ошибки в
работе приложения с БД возникают по следующим причинам:
* приложение использует некорректные значения для генерации
SQL-запроса;
* SQL-выражение некорректно описывает ожидаемый результат (т.е. ошибка
в запросе);
* фрагмент кода приложения, отвечающий за генерацию SQL-запроса,
содержит ошибку и не способен правильно построить нужный запрос.
Если доступ к БД построен на основе хранимых процедур, то:
- легко узнать, какие значения попадают в хранимую процедуру,
достаточно распечатать список аргументов в момент вызова хранимой
процедуры;
- вам не нужно гадать по коду приложения, какой именно запрос должен
получиться в том или ином месте программы, достаточно посмотреть на
тело хранимой процедуры, что значительно упрощает процесс отладки;
- этой ошибки возникнуть не может, так как приложение вообще не
генерирует SQL-запросов, все запросы находятся в БД в виде хранимых
процедур.
Трудности работы с хранимыми процедурами
Существует масса преимуществ, говорящих за использование хранимых
процедур. Однако не стоит думать, что хранимые процедуры, безусловно,
оптимальное решение для любого проекта. Чтобы сделать осмысленный
выбор, давайте рассмотрим отрицательные стороны данного метода.
- Проблема совместимости. Если необходимо обеспечить легкую
переносимость приложения на максимальное количество СУБД, то, вероятно,
стоит отдать предпочтение динамической генерации запросов, так как
хранимые процедуры поддерживаются не всеми СУБД.
- Сложность внедрения хранимых процедур в существующий проект.
Внедрение хранимых процедур в приложение, использующее динамическую
генерацию запросов, приведет к полной реорганизации кода работы с БД.
Необходимость такой реорганизации не всегда просто объяснить заказчику.
- Передача сложных типов данных. Иногда в качестве аргумента процедуре
требуется передать не просто строку или число, а массив данных (или
более сложный объект). В этом случае данные необходимо преобразовать в
строку и в таком виде передавать хранимой процедуре, внутри которой
происходит обратное преобразование. Построение запроса в теле
приложения в этом случае проще.
Особенности работы с хранимыми процедурами
- Хранимые процедуры служат только для доступа к данным
(извлечение/обновление/удаление) и ни для чего больше. Использование
процедур в иных целях (проверка данных или генерация HTML) является
ошибкой.
- Процедура может вернуть более одного результата. В коде вызова
хранимой процедуры необходимо делать итерацию по всем возвращаемым
результатам и обрабатывать каждый из них в отдельности.
- Достаточно сложно передать в хранимую процедуру массив значений.
Наиболее популярным решением является передача массива в хранимую
процедуру в виде строки, содержащей элементы массива, разделенные
специальным символом (вертикальная черта - "|"), далее параметр
анализируется в теле хранимой процедуры.
- Если в теле хранимой процедуры необходимо динамически генерировать
SQL-запрос (старайтесь всеми возможными способами избегать написания
подобных процедур), не забывайте экранировать кавычки и спецсимволы во
всех переданных в процедуру параметрах, участвующих в построении
запроса, иначе процедура будет содержать потенциальную уязвимость типа
SQL-injection.
Пример класса для работы с БД через хранимые процедуры
Как мы уже заметили, хранимые процедуры очень похожи на функции, наш
класс будет реализовывать прозрачную работу с процедурами таким
образом, чтобы с точки зрения приложения не было разницы между обычными
функциями и хранимыми процедурами.
Чтобы стало понятно, о чем идет речь, начнем с примера:
<?php
include 'DBaccess.php';
// Создаем объект доступа к БД
$db = new DBaccess('main_db' , 'localhost', 3306, 'login', 'password', 'utf8');
// Вызываем хранимую процедуру GetCities и передаем в качестве параметра RU
$result = $db->getCities('RU');
// Обрабатываем полученный результат (массив данных)
var_dump($result);
?>
В приведенном выше примере для вызова хранимой процедуры getCities с
параметром RU (код страны) используется объект класса DBaccess. С точки
зрения приложения такой вызов процедуры выглядит как вызов
обыкновенного метода, что позволяет отказаться от логики написания
программы, которая в свою очередь составляет другую программу на SQL.
Приведу далее код класса DBaccess (для работы класса требуется PHP5 c
mysqli расширением, MySQL 5):
<?php
class DBConnection {
private $db = null;
public function __construct($dbName, $host, $port, $login, $password, $charset) {
$this->db = new mysqli($host, $login, $password, $dbName, $port);
$this->db->set_charset($charset);
}
public function __call($storeProcedureName, $params) {
$quotedParams = array();
foreach($params as $param) {
array_push($quotedParams, $param === null ? 'NULL' : '\''.$this->db->escape_string($param).'\'');
}
$sql = 'CALL '.$storeProcedureName.'('.implode(',', $quotedParams).');';
$this->db->multi_query($sql);
$results = array();
do {
if ($result = $this->db->store_result()) {
$rows = array();
while ($row = $result->fetch_assoc()) {
array_push($rows, $row);
}
$result->close();
array_push($results, $rows);
}
} while ($this->db->more_results() && $this->db->next_result());
return($results);
}
}
?>
Внутри класса DBaccess не объявлен метод getCities, однако мы можем
вызывать хранимую процедуру как $db->getCities('RU');.
Это достигается за счет использования магического метода __call()
(данная возможность появилась в PHP5), который работает следующим
образом: при вызове метода, не объявленного в классе, имя вызываемого
метода и список аргументов передается в __call($metodName, $params) для
обработки (если метод объявлен).
Таким образом, мы можем вызывать любую хранимую процедуру на сервере,
например "$db->loadUser($email, $password);".
В приведенном выше коде класса DBaccess отсутствуют необходимые
проверки на ошибки, возможность работы с несколькими БД, проверка
стабильности соединения с сервером, автоматическое переподключение и
множество других полезных функций. Это сделано намеренно, дабы
проиллюстрировать основную идею с использованием минимального
количества кода.
1. Полнофункциональная версия класса "DBaccess" - http://www.vipidn.com/dbaccess.zip.
2. Описание магического метода __call() - http://ru2.php.net/manual/ru/language.oop5.overloading.php.
3. Описание расширения mysqli - http://ru2.php.net/manual/ru/ref.mysqli.php.
4. Информация к размышлению - "Good and Evil in the Garden of Stored
Procedures" (Jeremy D. Miller) -
http://codebetter.com/blogs/jeremy.miller/archive/2005/07/05/130093.aspx
По-моему автор не ставил задачей "открытие хранимых процедур", а сделал сравнительную характеристику и выделил +/- использования хранимых процедур. Статья ориентирована на начинающих или "второго уровня" программистов, хотя думаю найдутся и опытные кому будет что черпнуть из данной статьи.
P.S. "ломать легче, чем стороить", это аналогия к "хаять", кому не понятно.
Ну. чем хвалить или хаять эту статью, приложили бы пару нормальных решений по данному направлению. я вот тоже начинающий в этом плане. хотелось бы увидеть более сложные примеры хранимок и вызова их из PHP, с использованием нескольких параметров при вызове процедуры.
Относительно вызова хранимых процедур с множеством параметров.
Дело в том, что в статье приведен пример класса, который позволяет осуществлять вызов хранимых процедур с любым кол-вом параметров. Пример:
$db->loadUser($email, $password);
Выполнение данного кода приведет к вызову хранимой процедуры "loadUser" c двумя параметрами $email и $password
Доброе время суток!
Вполне достаточная статья для рассмотрения, изучения и практического применения.
Помогла ускорить работу пользовательского web приложения до 1 и 3 десятых секунды на обновление данных на странице(в таблицах).
Автору респект.
