|
Рубрика:
Разработка /
#10 лет назад
|
Facebook
Мой мир
Вконтакте
Одноклассники
Google+
|
 АНТОН ГРИШАН, ведущий программист крупного регистратора доменных имен
Доступ к данным
на основе хранимых процедур в веб-приложениях
 Большинство приложений вынуждено работать с базами данных, общаясь с СУБД на языке 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 стало возможно использовать хранимые процедуры;
- в большинстве пособий по программированию приведены примеры работы с БД, основанные на генерации запроса в теле скрипта, и начинающим программистам сложно отойти от книжных примеров;
- написание приложения с использованием хранимых процедур подразумевает умение программистов работать с хранимыми процедурами или наличие времени для изучения данной технологии (что не всегда допустимо в рамках конкретного проекта).
Преимущества использования хранимых процедур
Трудности работы с хранимыми процедурами
Существует масса преимуществ, говорящих за использование хранимых процедур. Однако не стоит думать, что хранимые процедуры, безусловно, оптимальное решение для любого проекта. Чтобы сделать осмысленный выбор, давайте рассмотрим отрицательные стороны данного метода.
- Проблема совместимости. Если необходимо обеспечить легкую переносимость приложения на максимальное количество СУБД, то, вероятно, стоит отдать предпочтение динамической генерации запросов, так как хранимые процедуры поддерживаются не всеми СУБД.
- Сложность внедрения хранимых процедур в существующий проект. Внедрение хранимых процедур в приложение, использующее динамическую генерацию запросов, приведет к полной реорганизации кода работы с БД. Необходимость такой реорганизации не всегда просто объяснить заказчику.
- Передача сложных типов данных. Иногда в качестве аргумента процедуре требуется передать не просто строку или число, а массив данных (или более сложный объект). В этом случае данные необходимо преобразовать в строку и втаком виде передавать хранимой процедуре, внутри которой происходит обратное преобразование. Построение запроса в теле приложения в этом случае проще.
Особенности работы с хранимыми процедурами
- Хранимые процедуры служат только для доступа к данным (извлечение/обновление/удаление) и ни для чего больше. Использование процедур в иных целях (проверка данных или генерация 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.
- Описание магического метода __call() – http://ru2.php.net/manual/ru/language.oop5.overloading.php.
- Описание расширения mysqli – http://ru2.php.net/manual/ru/ref.mysqli.php.
- Информация к размышлению – «Good and Evil in the Garden of Stored Procedures» (Jeremy D. Miller) – http://codebetter.com/blogs/jeremy.miller/archive/2005/07/05/130093.aspx.
Ключевые слова: СУБД, MySQL, хранимые процедуры, веб.
Facebook
Мой мир
Вконтакте
Одноклассники
Google+
|
Доступ к данным на основе хранимых процедур в веб-приложениях
