Где-то полтора года назад я начал заниматься web разработкой. Начинал с функционального программирования. Примерно пол года назад я перешел на ООП и стал использовать MVC архитектуру проектирования. Недавно появилась задача оптимизировать работу с базой данных, т. к. вся связь и работа с базой осуществлялась через один класс. Это было неудобно потому, что все время приходилось вручную писать SQL — запросы. Задача была разбита на 2 этапа:
- Написать класс для подключения к базе данных
- Написать класс модели для работы с данными
Первая задача решилась очень быстро. Для ее реализации я воспользовался паттерном проектирования Singleton.
Для реализации второй задачи мне понадобилось немного больше времени. За основу был взят простой модуль управления новостями на сайте. Обязанности этого модуля включали в себя стандартный набор функций: выборка, создание, удаление и обновление записей. Класс модели наследует класс DataBase, который собственно и создает подключение к базе данных. Также этот класс отвечает за генерацию sql кода для DML операций. Класс 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.
Некоторые входные параметры повторяются, поэтому я не будут повторно их описывать за исключение тех случаев, когда это действительно потребуется.
Метод getRecords
Этот метод позволяет получить набор записей, которые подходят под условие нашего SQL.
- $what — передает массив полей, которые нужно выбрать из таблицы
- $where — передает ассоциативный массив ключей в виде array('Поле'=>array('знак','значение')). Это дает нам возможность более гибко использовать предикат WHERE
- $limit — передает ассоциативный массив ключей в виде array('начальная запись', 'количество записей'). Такая структура дает нам возможность реализовать постраничный вывод или вывод ограниченного количества записей
- $order — ассоциативный массив array('поле'=>'вид сортировки'). Дает возможность сортировки по любому количеству столбцов.
- $join — ассоциативный массив array('Тип связи', array('Таблица1', 'Таблица2'), array('Алиас1', 'Алиас2'), array('поле1','поле2')). Тип связи: LEFT, INNER, RIGHT, OUTER; Таблица1 и Таблица2: таблицы между которыми устанавливается связь; Аллиас1 и Аллиас2 — псевдонимы для таблицы; Поле1 и Поле2 — это соотв. PK и FK таблиц
- $degub — этот параметр нужен для сохранения в свойства класса уже созданного sql запроса, а также параметров, которые нужно если мы используем prepare statement в PDO
Метод addRecord
Этот метод позволяет добавить запись в таблицу.
- $data — ассоциативный массив параметров в виде: 'поле'=>'значение', которые будут вставляться в таблицу
Метод deleteRecords
Этот метод позволяет удалить запись (-и) из таблицы.
- $table — название таблицы из которой будут удаляться данные
Метод setRecords
Этот метод позволяет обновить запись (-и) в таблице по заданному условию.
- $what — В этом случает этот параметр передает массив в виде: 'поле'=>'значение', которые будут использоваться с оператором SET
Метод query
Этот метод позволяет выполнять нестандартные запросы. Для этого нужно просто передать нужный sql запрос в качестве параметра метода.
- $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 были созданы методы, которые являются едиными не только для модуля «Новости», как в нашем случае, но и для любого другого модуля.
Рассмотрим эти методы подробнее.
Метод checkWhat
Единственным входным параметром этого метода является ассоциативный массив значений, которые либо нужно выбрать с помощью SELECT или нужно обновить с помощью UPDATE.
Параметр $what описан тут
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;
}
Думаю тут все довольно ясно. Проверка на массив, обход всех элементов массива и построение части строки запроса.
Метод checkJoin
Параметр $join описан тут. Также предусмотрена ситуация, когда нужно объединить больше двух таблиц, тогда параметр $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;
}
Метод checkWhere
Метод проверяет наличие параметров для WHERE составляющей запроса. Параметр $where описан тут
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;
}
Метод checkLimit
Генерация предикаты 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;
}
Метод checkOrder
Генерация предикаты 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. Для этой задачи я написал еще один метод
Метод checkParams
Входящими параметрами являются два массива. Массив параметров 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
Рассмотрим эти методы подробнее.
Метод prepareSelectSQL
Параметры этого метода совпадают с параметрами метода getRecords. Это $what, $where, $limit, $order, $join, $debug. Эти параметры описаны тут
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 и его параметры.
}
Метод prepareInsertSQL
Этот метод проще, т.к. используется ограниченный набор предикат и параметров
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);
}
Метод prepareDeleteSQL
Запрос для удаления записей. Используем название таблицы и набор параметров для предикаты 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);
}
Метод prepareUpdateSQL
Генерируем 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
