- PVSM.RU - https://www.pvsm.ru -
Где-то полтора года назад я начал заниматься web разработкой. Начинал с функционального программирования. Примерно пол года назад я перешел на ООП и стал использовать MVC архитектуру проектирования. Недавно появилась задача оптимизировать работу с базой данных, т. к. вся связь и работа с базой осуществлялась через один класс. Это было неудобно потому, что все время приходилось вручную писать SQL — запросы. Задача была разбита на 2 этапа:
Первая задача решилась очень быстро. Для ее реализации я воспользовался паттерном проектирования Singleton.
Для реализации второй задачи мне понадобилось немного больше времени. За основу был взят простой модуль управления новостями на сайте. Обязанности этого модуля включали в себя стандартный набор функций: выборка, создание, удаление и обновление записей. Класс модели наследует класс DataBase, который собственно и создает подключение к базе данных. Также этот класс отвечает за генерацию sql кода для DML операций [1]. Класс DataBase является абстрактным, что обязывает нас во всех дочерних классах реализовывать DML методы.
Ниже представлен набор абстрактных методов, которые отвечают за управление модулем «Новости».
//Получаем запись
abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false);
//Добавляем запись
abstract function addRecord($data=array(),$debug=false);
//Удаляем запись(-и)
abstract function deleteRecords($table, $where=NULL,$debug=false);
//Обновляем запись(-и)
abstract function setRecords($table,$what,$where,$debug=false);
//Нестандартный запрос
abstract function query($sql);
Теперь подробнее о каждом абстрактном методе. Реализация этих методов будет представлена ниже, когда я буду описывать класс модели News.
Некоторые входные параметры повторяются, поэтому я не будут повторно их описывать за исключение тех случаев, когда это действительно потребуется.
Этот метод позволяет получить набор записей, которые подходят под условие нашего SQL.
Этот метод позволяет добавить запись в таблицу.
Этот метод позволяет удалить запись (-и) из таблицы.
Этот метод позволяет обновить запись (-и) в таблице по заданному условию.
Этот метод позволяет выполнять нестандартные запросы. Для этого нужно просто передать нужный sql запрос в качестве параметра метода.
Думаю многие знают, что DML тип запросов состоит из 4 видов: SELECT, INSERT, UPDATE, DELETE. Для генерации эти запросов нам нужно их разделить условно на несколько частей.
1) SELECT запрос делится на: WHAT, JOIN, WHERE, ORDER, LIMIT, GROUP, HAVING.
2) INSERT запрос: WHAT
3) UPDATE запрос: WHAT, WHERE
4) DELETE запрос: WHERE.
Получается нам всего лишь нужно генерировать отдельно эти части запроса, а потом склеить их воедино. Для генерации этих частей в классе DataBase были созданы методы, которые являются едиными не только для модуля «Новости», как в нашем случае, но и для любого другого модуля.
Рассмотрим эти методы подробнее.
Единственным входным параметром этого метода является ассоциативный массив значений, которые либо нужно выбрать с помощью SELECT или нужно обновить с помощью UPDATE.
Параметр $what описан тут [2]
protected function checkWhat($what)
{
if (is_array($what))
{
foreach ($what as $k=>$v)
{
if (!is_numeric($k)) // Проверяем является ли наш массив ассоциативным. Если да, то готовим prepare statement, а если нет, то перечисляем все столбцы через запятую для SELECT запроса
{
$result['column'][]=$k."=?";
$result['params'][]=$v;
}
else {
$result['column'][]=$v;
}
}
$result['column']=implode(",",$result['column']);
}
return $result;
}
Думаю тут все довольно ясно. Проверка на массив, обход всех элементов массива и построение части строки запроса.
Параметр $join описан тут [3]. Также предусмотрена ситуация, когда нужно объединить больше двух таблиц, тогда параметр $join может быть представлен в виде array($join1, $join2,.....,$joinN)
//Проверяем наличие Join составляющей запроса.
protected function checkJoin($join)
{
if (is_array($join) && count($join)>0)
{
if (!is_array($join[0])) // Если это массив, то генерируем только один join
{
$res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]);
}
else { // Если это массив массивов, то генерируем несколько join
$res=$this->addJoinArray($join);
}
return implode(" ",$res);
}
else {
return false;
}
}
//Генерация join
protected function addJoin($type=' INNER ',$tables,$pseudoName,$rows)
{
if ($type!=='' && is_array($tables) && is_array($rows))
{
$t0=$tables[0];
$t1=$tables[1];
if (is_array($pseudoName) && count($pseudoName)>0) // если есть аллиасы, то применяем их
{
$t0=$pseudoName[0];
$t1=$pseudoName[1];
}
return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`";
}
else {
return false;
}
}
// Генерация массива join`ов
protected function addJoinArray($join)
{
if (is_array($join))
{
foreach ($join as $j)
{
$res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]);
}
}
return $res;
}
Метод проверяет наличие параметров для WHERE составляющей запроса. Параметр $where описан тут [4]
protected function checkWhere($where)
{
if (!is_null($where) && is_array($where))
{
foreach ($where as $k=>$v)
{
$part=$k.$v[0]; // сначала соединяем часть запроса 'поле' и 'знак'
if (!is_array($v[1])) //если не массив, то готовим prepare statement
{
$part.="?";
$params[]=$v[1]; // добавляем параметры в массив
}
else { // если массив, то вероятно мы используем IN (array)
$part.="(".implode(",",$v[1]).")";
}
$res[]=$part;
}
$result['column']="WHERE ".implode(" AND ",$res);
$result['params']=$params;
}
return $result;
}
Генерация предикаты LIMIT запроса. Тут все довольно просто.
protected function checkLimit($limit)
{
$res=false;
if (is_array($limit) && count($limit)>0)
{
$res=" LIMIT ".$limit['start'];
if (isset($limit['count']) && $limit['count']!=='') // проверяем наличие второго параметра для постраничного вывода
{
$res.=", ".$limit['count'];
}
}
return $res;
}
Генерация предикаты ORDER.
protected function checkOrder($order)
{
if (is_array($order) && count($order)>0)
{
foreach ($order as $row=>$dir)
{
$res[]=$row." ".$dir;
}
return "ORDER BY ".implode(",",$res);
}
else {
return false;
}
}
Вот и все методы, которые нужны для генерации основных частей запроса. Но т.к. мы используем prepare statement в таких частях запроса как WHERE и WHAT, то нам нужно объединить параметры эти частей, чтобы передать в PDO. Для этой задачи я написал еще один метод
Входящими параметрами являются два массива. Массив параметров WHAT и WHERE.
protected function checkParams($what,$where)
{
if (!isset($what) || !is_array($what))
{
$params=$where;
}
else if (!isset($where) && !is_array($where))
{
$params=$what;
}
else {
$params=array_merge($what,$where);
}
return $params;
}
Следующим этапом построения SQL запросов является итоговая генерация sql кода. Для этого я создал 4 метода: prepareSelectSQL, prepareInsertSQL, prepareDeleteSQL, prepareUpdateSQL
Рассмотрим эти методы подробнее.
Параметры этого метода совпадают с параметрами метода getRecords. Это $what, $where, $limit, $order, $join, $debug. Эти параметры описаны тут [5]
protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false)
{
$what=$this->checkWhat($what);
$where=$this->checkWhere($where);
$limit=$this->checkLimit($limit);
$order=$this->checkOrder($order);
$j=$this->checkJoin($join);
$sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit;
$params=$this->checkParams($what['params'],$where['params']);
if ($debug) // если true, то передаем sql и его параметры в свойства класса.
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params); // возвращаем готовый sql и его параметры.
}
Этот метод проще, т.к. используется ограниченный набор предикат и параметров
protected function prepareInsertSQL($data,$table,$debug=false)
{
$params=$values=$column=array();
foreach ($data as $c=>$p)
{
$column[]=$c; // поля для вставки
$values[]="?"; // параметры для prepare statement
$params[]=$p; //массив реальных значений параметров
}
$sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")";
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
Запрос для удаления записей. Используем название таблицы и набор параметров для предикаты WHERE.
protected function prepareDeleteSQL($table,$where,$debug=false)
{
$where=$this->checkWhere($where);
$sql="DELETE FROM `".$table."` ".$where['column'];
$params=$this->checkParams($what,$where['params']);
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
Генерируем sql запрос для обновления записей в таблице.
protected function prepareUpdateSQL($table,$what,$where,$debug=false)
{
$what=$this->checkWhat($what);
$where=$this->checkWhere($where);
$sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column'];
$params=$this->checkParams($what['params'],$where['params']);
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
Выше был описан класс DataBase, который отвечает за подключение к базе данных и генерацию DML sql запросов. Ниже приведен полный код этого класса.
<?
abstract class DataBase {
static private $_db=NULL;
public $sql='';
public $params=array();
/*
* Блокируем __construct и __clone для того,
* чтобы невозможно было создать новый объект через new.
* */
private function __construct()
{
return false;
}
private function __clone()
{
return false;
}
//Получаем записи
abstract public function getRecords($what='*',$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false);
//Добавляем запись
abstract function addRecord($data=array(),$debug=false);
//Удаляем запись(-и)
abstract function deleteRecords($table, $where=NULL,$debug=false);
//Обновляем запись(-и)
abstract function setRecords($table,$what,$where,$debug=false);
//Нестандартный запрос
abstract function query($sql);
/*
* Синглтон подключения к базе данных. Если объект уже создан,
* то просто возвращается экземпляр объекта, если нет,
* то создается новое подключение к базн данных.
* Можно напрямую пользоваться классом PDOChild
* */
public static function getInstance($registry)
{
if (is_null(self::$_db))
{
self::$_db=new PDOchild($registry);
}
return self::$_db;
}
/*
* Добавляем join к запросу.
* type - тип нужного join
* tables - массив таблиц которые будут связываться
* pseudoName - псевдонимы для таблиц
* row - поля по которым производится связь
* */
protected function addJoin($type=' INNER ',$tables,$pseudoName,$rows)
{
if ($type!=='' && is_array($tables) && is_array($rows))
{
$t0=$tables[0];
$t1=$tables[1];
if (is_array($pseudoName) && count($pseudoName)>0)
{
$t0=$pseudoName[0];
$t1=$pseudoName[1];
}
return $type." JOIN `".$tables[1]."` `".$pseudoName[1]."` ON `".$t0."`.`".$rows[0]."`=`".$t1."`.`".$rows[1]."`";
}
else {
return false;
}
}
/*
* Добавляем несколько join к запросу
* join - массив массивов join array(join,join)
* */
protected function addJoinArray($join)
{
if (is_array($join))
{
foreach ($join as $j)
{
$res[]=$this->addJoin($j[0],$j[1],$j[2],$j[3]);
}
}
return $res;
}
/*
* Генерируем SELECT sql
* what- поля которые нужно выбрать в виде массива
* where- условие выбора в виде массива array(поле=>array(знак=,значение))
* limit-лимит записей в виде массива array(начальная запись, количество)
* order- сортировка array (поле=>направление)
* join- массив join
* debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
* */
protected function prepareSelectSQL($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false)
{
$what=$this->checkWhat($what);
$where=$this->checkWhere($where);
$limit=$this->checkLimit($limit);
$order=$this->checkOrder($order);
$j=$this->checkJoin($join);
$sql="SELECT ".$what['column']." FROM `".$this->table."` `tb` ".$j." ".$where['column']." ".$order." ".$limit;
$params=$this->checkParams($what['params'],$where['params']);
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
/*
* Генерируем Insert sql
* data- массив пар поле-значение для вставки
* table- таблица куда вставляется значение
* debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
* */
protected function prepareInsertSQL($data,$table,$debug=false)
{
foreach ($data as $c=>$p)
{
$column[]=$c;
$values[]="?";
$params[]=$p;
}
$sql=" INSERT INTO `".$table."` (".implode(",",$column).") VALUES (".implode(',',$values).")";
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
/*
* Генерируем Delete sql
* where- Условие для удаления
* table- таблица из которой удаляются записи
* debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
* */
protected function prepareDeleteSQL($table,$where,$debug=false)
{
$where=$this->checkWhere($where);
$sql="DELETE FROM `".$table."` ".$where['column'];
$params=$this->checkParams($what,$where['params']);
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
/*
* Генерируем Update sql
* table- таблица из которой удаляются записи
* what - массив поле значение для обновления
* where- Условие для обновления
* debug- если true то в свойство класса sql записывается текущий sql запрос и в свойство params записываются параметры
*/
protected function prepareUpdateSQL($table,$what,$where,$debug=false)
{
$what=$this->checkWhat($what);
$where=$this->checkWhere($where);
$sql="UPDATE `".$table."` SET ".$what['column']." ".$where['column'];
$params=$this->checkParams($what['params'],$where['params']);
if ($debug)
{
$this->sql=$sql;
$this->params=$params;
}
return array('sql'=>$sql,'params'=>$params);
}
/*
* Проверяем наличие параметра join
* Если он есть, то проверяем является ли он единственным, если да то addJoin
* если нет, то addJoinArray
* Если join нет, то ничего не возвращаем
* */
protected function checkJoin($join)
{
if (is_array($join) && count($join)>0)
{
if (!is_array($join[0]))
{
$res[]=$this->addJoin($join[0],$join[1],$join[2],$join[3]);
}
else {
$res=$this->addJoinArray($join);
}
return implode(" ",$res);
}
else {
return false;
}
}
/*
* Проверяем наличие параметра what
* Если этот параметр явл. массивом,
* то генерируем массив поле=>? и массив параметров для prepare SQL
* */
protected function checkWhat($what)
{
if (is_array($what))
{
foreach ($what as $k=>$v)
{
if (!is_numeric($k))
{
$result['column'][]=$k."=?";
$result['params'][]=$v;
}
else {
$result['column'][]=$v;
}
}
$result['column']=implode(",",$result['column']);
}
return $result;
}
/*
* Проверяем наличие параметра Where
* Если этот параметр явл массивом,
* то генерируем массив поле=>? и массив параметров для prepare SQL
* если v[0](sign)= IN и значение value это массив, то можно сгенерировать IN (array);
* Можно также генерировать условие LIKE, но не тестил.
* Возвращает массив полей и параметров для sql
* */
protected function checkWhere($where)
{
if (!is_null($where) && is_array($where))
{
foreach ($where as $k=>$v)
{
$part=$k.$v[0];
if (!is_array($v[1]))
{
$part.="?";
$params[]=$v[1];
}
else {
$part.="(".implode(",",$v[1]).")";
}
$res[]=$part;
}
$result['column']="WHERE ".implode(" AND ",$res);
$result['params']=$params;
}
return $result;
}
/*
* Проверяем наличие параметра Limit
* Если этот параметр явл массивом,
* то генерируем LIMIT для SQL
* Возвращает строку LIMIT с разбиением на страницы или без него
* */
protected function checkLimit($limit)
{
if (is_array($limit) && count($limit)>0)
{
$res=" LIMIT ".$limit['start'];
if (isset($limit['count']) && $limit['count']!=='')
{
$res.=", ".$limit['count'];
}
}
return $res;
}
/*
* Проверяем наличие параметра Order
* Если этот параметр явл массивом,
* то генерируем ORDER для SQL
* Возвращает массив ORDER
* */
protected function checkOrder($order)
{
if (is_array($order) && count($order)>0)
{
foreach ($order as $row=>$dir)
{
$res[]=$row." ".$dir;
}
return "ORDER BY ".implode(",",$res);
}
else {
return '';
}
}
/*
* Проверяем наличие параметров для prepare sql
* Параметры состоят из массива параметров WHAT и массива параметров WHERE.
* Это нужно для того, чтобы prepare sql
* работал и с update, select, delete, insert
* Объединяет два массива what и where
* */
protected function checkParams($what,$where)
{
if (!isset($what) || !is_array($what))
{
$params=$where;
}
else if (!isset($where) && !is_array($where))
{
$params=$what;
}
else {
$params=array_merge($what,$where);
}
return $params;
}
}
?>
Теперь настало время описать класс модели News. Этот класс реализует все абстрактные методы класса-родителя DataBase и статический метод getObject. Этот метод возвращает экземпляр объекта этого класс. Этот метод был создан для того, чтобы отпала необходимость в создании объекта класса News путем использования ключевого слова new. Вот как это выглядит:
$news=News::getObject()->getRecords(params);
Каждый метод этого класса вызывает нужный ему генератор sql запроса и передает итоговый запрос и параметры в PDO для выполнения запроса. Ниже приведен полный код класса модели News.
<?
class News extends DataBase
{
public $table='news'; //Главная талица
public $id_row='id'; // primary key
public function __construct()
{
$registry=new Registry(); //параметры подключения к базе данных
$this->db=parent::getInstance($registry);
}
// для доступа к классу через статический метод
public static function getObject()
{
return new News();
}
// получаем записи из таблицы.
public function getRecords($what=array('*'),$where=NULL, $limit=NULL, $order=NULL,$join=NULL,$debug=false)
{
$data=$this->prepareSelectSQL($what,$where, $limit, $order,$join,$debug);
$res=$this->db->prepare($data['sql']);
$res->execute($data['params']);
$result=$query->fetchAll(PDO::FETCH_OBJ);
return $result;
}
public function addRecord($data=array(),$debug=false)
{
$data=$this->prepareInsertSQL($data,$this->table,$debug);
$query=$this->db->prepare($data['sql']);
return $query->execute($data['params']));
}
public function deleteRecords($table, $where=NULL,$debug=false)
{
$data=$this->prepareDeleteSQL($table,$where,$debug);
$query=$this->db->prepare($data['sql']);
$result=$query->execute($data['params']);
return $result;
}
public function setRecords($table,$what,$where,$debug=false)
{
$data=$this->prepareUpdateSQL($table,$what,$where,$debug);
$query=$this->db->prepare($data['sql']);
$result=$query->execute($data['params']);
return $result;
}
public function query($sql)
{
$query=$this->db->prepare($sql);
$query->execute();
$result=$query->fetchAll(PDO::FETCH_OBJ);
return $result;
}
}
?>
В принципе на этом можно завершать. Конечно же, можно было еще добавить генерацию предикаты GROUP BY и HAVING, но я решил этого не делать. Думаю, что принцип построения запросов я изложил ясно и проблем с использованием не возникнет. В итоге мы получили механизм построения sql запросов, который не завязан на конкретной структуре таблицы в БД и может применяться к разным типам DML SQL запросов. Если нужно будет, могу создать репозиторий на github.
Буду рад услышать критику и предложения по улучшению метода.
Автор: farw
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/php-2/16880
Ссылки в тексте:
[1] DML операций: http://ru.wikipedia.org/wiki/DML
[2] тут: #what
[3] тут: #join
[4] тут: #where
[5] тут: #params
Нажмите здесь для печати.