Содержание


Sql


Sql, ЗФ2, Zend Framework 2, ZF2




Zend\Db\Sql представляет собой слой абстракции для SQL, позволяющий строить SQL запросы через объектно – ориентированный API. Результатом работы объекта Zend\Db\Sql  является контейнеры Statement (заявление) и Parameter (параметры) целевого запроса или полная строка, которая может быть непосредственно выполнена на платформе БД. Для совей работы Zend\Db\Sql необходим объект  Zend\Db\Adapter\Adapter.

 

Быстрый старт Zend\Db\Sql\Sql

 

Существует ряд основных задач, выполняемых при работе с БД. Это selecting, inserting, updating, deleting. Поэтому реализовано четыре основных обекта, которые разработчики могут использовать для построения запросов: Zend\Db\Sql\Select, Insert, Update, Delete. Пример использования:

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter);
$select = $sql->select(); // @return ZendDbSqlSelect
$insert = $sql->insert(); // @return ZendDbSqlInsert
$update = $sql->update(); // @return ZendDbSqlUpdate
$delete = $sql->delete(); // @return ZendDbSqlDelete

Как разработчик, теперь Вы можете использовать эти объекты для взаимодействия и построения запросов, как показано на примере выше. Как только они были заполнены необходимыми значениями они сразу могут быть использованы для prepared (подготовки) и executed (выполнения).

 

Пример prepared (подготовки). Используется объект Select.

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('foo');
$select->where(array('id' => 2));
 
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();

Пример executed (выполнения). Используется объект Select.

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter);
$select = $sql->select();
$select->from('foo');
$select->where(array('id' => 2));
 
$selectString = $sql->getSqlStringForSqlObject($select);
$results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);

Так же объекты Zend\Db\Sql\Sql могут быть привязаны к заданной таблице. Поэтому все действия с объектами select, insert, update, delete будут выполняются с одной таблицей.

use Zend\Db\Sql\Sql;
$sql = new Sql($adapter, 'foo');
$select = $sql->select();
$select->where(array('id' => 2)); // $select already has the from('foo')


Zend\Db\Sql Select, Insert, Update и Delete

 

Каждый из этих объектов предоставляет следующие интерфейсы:

interface PreparableSqlInterface {
     public function prepareStatement(Adapter $adapter, StatementInterface $statement);
}
interface SqlInterface {
     public function getSqlString(PlatformInterface $adapterPlatform = null);
}

Эти функции Вы можете вызывать как при  использовании prepared так и при использовании полной строки.

 


Zend\Db\Sql\Select

 

Основная роль Zend\Db\Sql\Select – это создание SQL запросов SELECT посредством предоставления универсального API для различных платформ. Этот класс может быть создан и использован без Zend\Db\Sql\Sql:

use Zend\Db\Sql\Select;
$select = new Select();
// or, to produce a $select bound to a specific table
$select = new Select('foo');

Если Вы указали имя таблицы в объекте Select, то уже нельзя будет вызвать метод from() для изменения имени таблицы.

 

Как только Вы получили валидный объект  Select, следующее API может быть использовано для определения различных пунктов заявления:

class Select extends AbstractSql implements SqlInterface, PreparableSqlInterface
{
    const JOIN_INNER = 'inner';
    const JOIN_OUTER = 'outer';
    const JOIN_LEFT = 'left';
    const JOIN_RIGHT = 'right';
    const SQL_STAR = '*';
    const ORDER_ASCENDING = 'ASC';
    const ORDER_DESENDING = 'DESC';
 
    public $where; // @param Where $where
 
    public function __construct($table = null);
    public function from($table);
    public function columns(array $columns, $prefixColumnsWithTable = true);
    public function join($name, $on, $columns = self::SQL_STAR, $type = self::JOIN_INNER);
    public function where($predicate, $combination = PredicatePredicateSet::OP_AND);
    public function group($group);
    public function having($predicate, $combination = PredicatePredicateSet::OP_AND);
    public function order($order);
    public function limit($limit);
    public function offset($offset);
}

from():

// as a string:
$select->from('foo');
 
// as an array to specify an alias:
// produces SELECT "t".* FROM "table" AS "t"
 
$select->from(array('t' => 'table'));
 
// using a SqlTableIdentifier:
// same output as above
 
$select->from(new TableIdentifier(array('t' => 'table')));

columns():

// as array of names
$select->columns(array('foo', 'bar'));
 
// as an associative array with aliases as the keys:
// produces 'bar' AS 'foo', 'bax' AS 'baz'
 
$select->columns(array('foo' => 'bar', 'baz' => 'bax'));

join():

$select->join(
     'foo' // table name,
     'id = bar.id', // expression to join on (will be quoted by platform object before insertion),
     array('bar', 'baz'), // (optional) list of columns, same requiremetns as columns() above
     $select::JOIN_OUTER // (optional), one of inner, outer, left, right also represtned by constants in the API
);
 
$select->from(array('f' => 'foo'))  // base table
    ->join(array('b' => 'bar'),     // join table with alias
    'f.foo_id = b.foo_id');         // join expression

where(), having():

 

Объект Zend\Db\Sql\Select предоставляет большую гибкость при выборе способов задания необходимых параметров для where() и having(). Синтаксис метода следующий:

/**
 * Create where clause
 *
 * @param  Where|Closure|string|array $predicate
 * @param  string $combination One of the OP_* constants from PredicatePredicateSet
 * @return Select
 */
public function where($predicate, $combination = Predicate\PredicateSet::OP_AND);

Как Вы смогли убедиться, существует множество различных путей для назначения параметров для where() и having().

 

Если Вы передаете объект Zend\Db\Sql\Where в метод where()  или оюъект Zend\Db\Sql\Having в метод having(), то внутренние объекты для Select будут полностью заменены. При выполнении метода where/having() он будет использован для построения секции WHERE или HAVING в заявлении запроса SELECT.

 

Если Вы используете замыкание (Closure) для where() или having(), то функция будет вызвана объектом Where с одним параметром. Таким образом, возможно следующее:

$spec = function (Where $where) {
    $where->like('username', 'ralph%');
};
 
$select->where($spec);

Если же Вы используете строку, то она будет использована ля создания экземпляра  объекта Zend\Db\Sql\Predicate\Expression и её содерживмое будет использоваться без обработки. Это означает, что определенные (необходимые) фрагменты запроса не будут заключены в кавычки.

 

Рассмотрим следующий код:

// SELECT "foo".* FROM "foo" WHERE x = 5 AND y = z
$select->from('foo')->where(array('x = 5', 'y = z'));

Если Вы используете массив, где ключам соответствуют строки запроса (значения), то эти значения будут обрабатываться следующим образом:

 

1) Если передано значение PHP NULL, то оно будет расценено как объект Predicate\IsNull.

2) Если передано значение PHP Array (массив), то оно будет произведено в объект Predicate\In.

3) Если передано значение PHP String (строка), то оно будет произведено в объект Predicate\Operator так, что ключ будет идентификатором, а значение будет соответствовать целевому значению.

 

Рассмотримследующийкод:

// SELECT "foo".* FROM "foo" WHERE "c1" IS NULL AND "c2" IN (?, ?, ?) AND "c3" IS NOT NULL
$select->from('foo')->where(array(
    'c1' => null,
    'c2' => array(1, 2, 3),
    new Zend\Db\Sql\Predicate\IsNotNull('c3')
));

order():

$select = new Select;
$select->order('id DESC'); // produces 'id' DESC
 
$select = new Select;
$select->order('id DESC')
     ->order('name ASC, age DESC'); // produces 'id' DESC, 'name' ASC, 'age' DESC
 
$select = new Select;
$select->order(array('name ASC', 'age DESC')); // produces 'name' ASC, 'age' DESC

limit() and offset():

$select = new Select;
$select->limit(5); // always takes an integer/numeric
$select->offset(10); // similarly takes an integer/numeric


Zend\Db\Sql\Insert

 

Insert API:

class Insert implements SqlInterface, PreparableSqlInterface
{
     const VALUES_MERGE = 'merge';
     const VALUES_SET   = 'set';
 
     public function __construct($table = null);
     public function into($table);
     public function columns(array $columns);
     public function values(array $values, $flag = self::VALUES_SET);
}

 

Так же как и в объекте Select, имя таблицы можно указывать в конструкторе или в методе into().

 

columns():

$insert->columns(array('foo', 'bar')); // set the valid columns

values():

// default behavior of values is to set the values
// succesive calls will not preserve values from previous calls
$insert->values(array( 
     'col_1' => 'value1',
     'col_2' => 'value2'
));
// merging values with previous calls
$insert->values(array('col_2' => 'value2'), $insert::VALUES);

Zend\Db\Sql\Update

 

Update API:

class Update
{
    const VALUES_MERGE = 'merge';
    const VALUES_SET   = 'set';
 
    public $where; // @param Where $where
    public function __construct($table = null);
    public function table($table);
    public function set(array $values, $flag = self::VALUES_SET);
    public function where($predicate, $combination = Predicate\PredicateSet::OP_AND);
}

set():

$update->set(array('foo' => 'bar', 'baz' => 'bax'));

where():

 

Так же как и в объекте Select.

 


ZendDbSqlDelete

class Delete
{
    public $where; // @param Where $where
    public function __construct($table = null);
    public function from($table);
    public function where($predicate, $combination = Predicate\PredicateSet::OP_AND);
}


Zend\Db\Sql\Where & Zend\Db\Sql\Having

 

Далее, говоря о Where, Having будем подразумевать, что они имеют одинаковый API.

 

Очевидно, что Where и Having наследуются от одного объекта - Predicate (and PredicateSet).Все части Where и Having которые могут быть соединены через «AND» и «OR» называются предикатами (predicates). Полный набор предикатов называется PredicateSet. Этот объект обычно содержит контейнер со значениями (идентификаторами) отдельно от фрагментов к которым они принадлежат до того момента, когда заявление должно быть подготовлено (prepared, параметризировано) или выполнено (executed).  ПР  подготовке (prepared, параметризации) параметры замещают заполнители (placeholder), а сами значения хранятся внутри Adapter\ParameterContainer. А при выполении (executed) значения будут интерпретированы в принадлежащие им фрагменты и заключены в кавычки если это необходимо.

 

Важно помнить, что в этом API есть различия между элементами являющимися идентификаторами (TYPE_IDENTIFIER) и значениями (TYPE_VALUE). Так же есть специальная методика использования для типа литерального типа - TYPE_LITERAL. Все выше перечисленное доступно через интерфейс Zend\Db\Sql\ExpressionInterface.

 

Zend\Db\Sql\Where (Predicate/PredicateSet) API:

// Where & Having:
class Predicate extends PredicateSet
{
     public $and;
     public $or;
     public $AND;
     public $OR;
     public $NEST;
     public $UNNSET;
 
     public function nest();
     public function setUnnest(Predicate $predicate);
     public function unnest();
     public function equalTo($left, $right, $leftType = self::TYPE_IDENTIFIER, $rightType = self::TYPE_VALUE);
     public function lessThan($left, $right, $leftType = self::TYPE_IDENTIFIER, $rightType = self::TYPE_VALUE);
     public function greaterThan($left, $right, $leftType = self::TYPE_IDENTIFIER, $rightType = self::TYPE_VALUE);
     public function lessThanOrEqualTo($left, $right, $leftType = self::TYPE_IDENTIFIER, $rightType = self::TYPE_VALUE);
     public function greaterThanOrEqualTo($left, $right, $leftType = self::TYPE_IDENTIFIER, $rightType = self::TYPE_VALUE);
     public function like($identifier, $like);
     public function literal($literal, $parameter);
     public function isNull($identifier);
     public function isNotNull($identifier);
     public function in($identifier, array $valueSet = array());
     public function between($identifier, $minValue, $maxValue);
 
 
     // Inherited From PredicateSet
 
     public function addPredicate(PredicateInterface $predicate, $combination = null);
     public function getPredicates();
     public function orPredicate(PredicateInterface $predicate);
     public function andPredicate(PredicateInterface $predicate);
     public function getExpressionData();
     public function count();
}

Каждый метод Where API производит соответствующий объект предиката (Predicate) одноименных типов, описанных ниже, в полном API.

 

equalTo(), lessThan(), greaterThan(), lessThanOrEqualTo(), greaterThanOrEqualTo():

$where->equalTo('id', 5);
 
// same as the following workflow
$where->addPredicate(
     new Predicate\Operator($left, Operator::OPERATOR_EQUAL_TO, $right, $leftType, $rightType)
);
 
class Operator implements PredicateInterface
{
    const OPERATOR_EQUAL_TO                  = '=';
    const OP_EQ                              = '=';
    const OPERATOR_NOT_EQUAL_TO              = '!=';
    const OP_NE                              = '!=';
    const OPERATOR_LESS_THAN                 = '<';
    const OP_LT                              = '<';
    const OPERATOR_LESS_THAN_OR_EQUAL_TO     = '<=';
    const OP_LTE                             = '<=';
    const OPERATOR_GREATER_THAN              = '>';
    const OP_GT                              = '>';
    const OPERATOR_GREATER_THAN_OR_EQUAL_TO  = '>=';
    const OP_GTE                             = '>=';
 
    public function __construct($left = null, $operator = self::OPERATOR_EQUAL_TO, $right = null, $leftType = self::TYPE_IDENTIFIER, $rightType = self::TYPE_VALUE);
    public function setLeft($left);
    public function getLeft();
    public function setLeftType($type);
    public function getLeftType();
    public function setOperator($operator);
    public function getOperator();
    public function setRight($value);
    public function getRight();
    public function setRightType($type);
    public function getRightType();
    public function getExpressionData();
}

like($identifier, $like):

$where->like($identifier, $like):
 
/(color:#000000;font-weight:boldspan style=color:#0color:#009900
    ;::/span/span/spanspan style=09900color:#000000;font-weight:boldspan style=/spancolor:#000000;font-weight:boldcolor:#000088/ same as
$where->addPredicate(
     new Predicate\Like($identifier, $like)
);
 
// full API
 
class Like implements PredicateInterface
{
    public function __construct($identifier = null, $like = null);
    public function setIdentifier($identifier);
    public function getIdentifier();
    public function setLike($like);
    public function /spanspan style=color:#000088color:#000000;font-weight:boldcolor:#000088/spanpublic
span style=/spancolor:#009900(getLike();
}

literal($literal, $parameter);

$where->literal($literal, $parameter);
 
// same as
$where->addPredicate(
    new Predicate\Expression($literal, $parameter)
);
 
// full API
class Expression implements ExpressionInterface, PredicateInterface
{
    const PLACEHOLDER = '?';
     public function __construct($expression = null, $valueParameter = null /*[, $valueParameter, ... ]*/);
    public function setExpression($expression);
    public function getExpression();
    public function setParameters($parameters);
    public function getParameters();
    public function setTypes(array $types);
    public function getTypes();
}

isNull($identifier);

$where->isNull($identifier);
 
// same as
$where->addPredicate(
    new Predicate\IsNull($identifier)
);
 
// full API
class IsNull implements PredicateInterface
{
    public function __construct($identifier = null);
    public function setIdentifier($identifier);
    public function getIdentifier();
}

isNotNull($identifier);

$where->isNotNull($identifier);
 
// same as
$where->addPredicate(
    new Predicate\IsNotNull($identifier)
);
 
// full API
class IsNotNull implements PredicateInterface
{
    public function __construct($identifier = null);
    public function setIdentifier($identifier);
    public function getIdentifier();
}

in($identifier, array $valueSet = array());

$where->in($identifier, array $valueSet = array());
 
// same as
$where->addPredicate(
    new Predicate\In($identifier, $valueSet)
);
 
// full API
class In implements PredicateInterface
{
    public function __construct($identifier = null, array $valueSet = array());
    public function setIdentifier($identifier);
    public function getIdentifier();
    public function setValueSet(array $valueSet);
    public function getValueSet();
}

between($identifier, $minValue, $maxValue);

$where->between($identifier, $minValue, $maxValue);
 
// same as
$where->addPredicate(
    new Predicate\Between($identifier, $minValue, $maxValue)
);
 
// full API
class Between implements PredicateInterface
{
    public function __construct($identifier = null, $minValue = null, $maxValue = null);
    public function setIdentifier($identifier);
    public function getIdentifier();
    public function setMinValue($minValue);
    public function getMinValue();
    public function setMaxValue($maxValue);
    public function getMaxValue();
    public function setSpecification($specification);
}




Автор статьи: DuB