/**
* @return string
*/
public function __toString()
{
$conditions = $this->_where->getConditions();
if (count($conditions) === 0) {
return '';
}
return implode(' ', $conditions);
}
public function testChecarSeOSqlRetornaCorretoComWhere()
{
$update = new Update();
$where = new Where();
$where->set('id', '=', 1);
$update->where($where);
$fields = ['name' => 'Erik'];
$update->fields($fields);
$sql = $update->sql();
$this->assertEquals('UPDATE `users` SET `name`=:name WHERE `id`=:id;', $sql);
}
public function testSelectComWheresComOrEAnd()
{
$select = new Select();
$where = new Where();
$where->set('id', '=', 1);
$where->set('last_name', '=', 1, 'or');
$where->set('name', '=', 'Erik', 'or');
$select->table = 'pages';
$select->where($where);
$sql = $select->sql();
$this->assertEquals('SELECT * FROM pages WHERE `id`=:id and `last_name`=:last_name or `name`=:name;', $sql);
}
public function prepareBindings(array $options)
{
if (!$options) {
return [];
}
if (isset($options['HAVING'])) {
$where = new Where($options['HAVING']);
$this->bound = array_merge($this->bound, $where->getBindings());
$options['HAVING'] = $where;
}
return $options;
}
/**
* Get SQL string for statement
*
* @param null|PlatformInterface $platform If null, defaults to Sql92
* @return string
*/
public function getSqlString(PlatformInterface $platform = null)
{
$platform = $platform ?: $this->platform ?: new Sql92();
list($table, $schema) = $this->table->getAll();
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
$data = $this->data;
if (is_array($data)) {
$setSql = array();
foreach ($data as $column => $value) {
if ($value instanceof Expression) {
$expr = $this->processExpression($value, $platform);
$setSql[] = $platform->quoteIdentifier($column) . ' = ' . $expr;
} elseif ($value === null) {
$setSql[] = $platform->quoteIdentifier($column) . ' = NULL';
} else {
$setSql[] = $platform->quoteIdentifier($column) . ' = ' . $platform->quoteValue($value);
}
}
$data = implode(', ', $setSql);
}
$sql = sprintf($this->specifications[self::SPECIFICATION_UPDATE], $table, $data);
if ($this->where->count() > 0) {
$expr = $this->processExpression($this->where, $platform, null, 'where');
$sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $expr);
}
return $sql;
}
/**
* Returns the query options
*
* @return array query options
*/
public function getOptions()
{
$options = parent::getOptions();
$options['upsert'] = $this->upsert;
$options['multiple'] = $this->multiple;
return $options;
}
/**
* Retrieve query options.
*
* @return array query options.
*/
public function getOptions()
{
$conditions = parent::getOptions();
// Append remove specific options
$conditions['justOne'] = $this->justOne;
return $conditions;
}
protected function processWhere(PlatformInterface $platform, DriverInterface $driver = null, Parameters $parameters = null)
{
if ($this->where->count() == 0) {
return null;
}
$sql = $this->processExpression($this->where, $platform, $driver, $this->processInfo['paramPrefix'] . 'where', $parameters);
return array($sql);
}
/**
* Set the WHERE clause
*
* @param $where
* @return Delete
*/
public function where($where = null)
{
if (null !== $where) {
if ($where instanceof Where) {
$this->where = $where;
} else {
if (null === $this->where) {
$this->where = (new Where($this->sql))->add($where);
} else {
$this->where->add($where);
}
}
}
if (null === $this->where) {
$this->where = new Where($this->sql);
}
return $this;
}
public function matches($where)
{
$where = Where::create($where)->toArray();
foreach ($where as $clause) {
if (!$this->rowMatchesClause($clause)) {
return false;
}
}
return true;
}
/**
* Get the SQL string, based on the platform
* Platform defaults to Sql92 if none provided
*
* @param null|PlatformInterface $platform
* @return string
*/
public function getSqlString(PlatformInterface $platform = null)
{
$platform = $platform ?: $this->platform ?: new Sql92();
list($table, $schema) = $this->table->getAll();
$table = $platform->quoteIdentifier($table);
if ($schema) {
$table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table;
}
$sql = sprintf($this->specifications[self::SPECIFICATION_DELETE], $table);
if ($this->where->count() > 0) {
$part = $this->processExpression($this->where, $platform, null, 'where');
$sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $part);
}
return $sql;
}
/**
* Render the UPDATE statement
*
* @return string
*/
public function render()
{
// Start building the UPDATE statement
$sql = 'UPDATE ' . $this->sql->quoteId($this->sql->getTable()) . ' SET ';
$set = [];
$paramCount = 1;
$dbType = $this->sql->getDbType();
foreach ($this->columns as $column => $value) {
$colValue = strpos($column, '.') !== false ? substr($column, strpos($column, '.') + 1) : $column;
// Check for named parameters
if (':' . $colValue == substr($value, 0, strlen(':' . $colValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) {
if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) {
$value = '?';
} else {
if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) {
$value = '$' . $paramCount;
$paramCount++;
}
}
}
$val = null === $value ? 'NULL' : $this->sql->quote($value);
$set[] = $this->sql->quoteId($column) . ' = ' . $val;
}
$sql .= implode(', ', $set);
// Build any WHERE clauses
if (null !== $this->where) {
$sql .= ' WHERE ' . $this->where->render($paramCount);
}
// Build any ORDER BY clause
if (null !== $this->orderBy) {
$sql .= ' ORDER BY ' . $this->orderBy;
}
// Build any LIMIT clause
if (null !== $this->limit) {
$sql .= ' LIMIT ' . (int) $this->limit;
}
return $sql;
}
$insert->fields(array('field1', 'field2'))->values(array('fvalue1', 'fvalue2'));
//insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ;
$insert->values(array('fvalue11', 'fvalue22'));
//附加 前面的
//2.2.2 关联数组插入模式
//insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ;
$insert->kvInsert(array('field1' => 'fvalue1', 'field2' => 'fvalue2'))->values(array('fvalue11', 'fvalue22'));
//2.2.3 Duplicate Key Update模式
//需要注意dupFields和dupValues的关联性，弱耦合
$insert->enableDuplicate(true);
//开启自动Dup更新模式
//insert into test set field1='fvalue1', field2='fvalue2' on duplicate key update field1='newfvalue1', num=num+1 ;
$insert->fields(array('field1', 'field2'))->dupFields(array('field1', 'num'))->values(array('fvalue1', 'fvalue2'), array('field2' => 'newfvalue1', array('plain' => 'num=num+1')));
//2.2.4 通过row对象数组或rowset批量插入
$insert->batchSaveRows();
//2.3 更新部分
//获取更新对象
//update tableTest set field1='fvalue1', num=num+1 where (id='16') ;
$update = \HuiLib\Db\Query::update('tableTest');
//支持两种更新Set模式，KV模式和Plain模式
$update->sets(array('field1' => 'fvalue1', 'num' => array('plain' => 'num=num+1')));
//绑定条件
$update->where(Where::createPair('id', '16'));
//2.4 删除部分
//获取删除对象
//delete from tableTest where (id='2') limit 10 ;
$delete = \HuiLib\Db\Query::delete('tableTest');
//设置删除条件
$delete->where(Where::createPair('id', '2'));
//设置删除行数
$delete->limit(10);
private function whereToString(\b2\Quote $quote)
{
return $this->where->toString($quote);
}
public function Get()
{
if (!empty($this->queryString)) {
return $this->queryString;
}
$this->type = strtolower($this->type);
$result = $this->type . ' ';
if (!empty($this->queryMod)) {
$result .= $this->queryMod . ' ';
}
$table_result = '';
if (is_array($this->tables)) {
foreach ($this->tables as $table) {
$table_result .= $table . ', ';
}
$table_result = substr($table_result, 0, strlen($table_result) - 2);
} else {
$table_result = $this->tables;
}
switch ($this->type) {
case 'select':
$field_result = '';
if (is_array($this->fields)) {
foreach ($this->fields as $field) {
$field_result .= $field . ', ';
}
$field_result = substr($field_result, 0, strlen($field_result) - 2);
} else {
$field_result = $this->fields;
}
if ($this->calcRows) {
$result .= 'SQL_CALC_FOUND_ROWS ';
}
$result .= $field_result;
if (!empty($table_result)) {
$result .= ' from ' . $table_result;
}
break;
case 'delete':
$result .= ' from ' . $table_result;
break;
case 'insert':
$result .= ' into ';
$result .= $table_result . ' (';
$field_result = '';
if (is_array($this->fields)) {
foreach ($this->fields as $field) {
$field_result .= $field . ', ';
}
$field_result = substr($field_result, 0, strlen($field_result) - 2);
} else {
$field_result = $this->fields;
}
$result .= $field_result . ' ) values ( ';
if (is_array($this->values)) {
for ($i = 0; $i < count($this->values); $i++) {
if (strpos($this->values[$i], 'inline:') === false) {
$result .= "'" . addslashes($this->values[$i]) . "', ";
} else {
$this->values[$i] = preg_replace('/^inline:(.*)/', '\\1', $this->values[$i]);
$result .= $this->values[$i] . ", ";
}
}
$result = substr($result, 0, strlen($result) - 2);
}
$result .= ' )';
break;
case 'update':
$result .= $table_result . ' set ';
if (is_array($this->fields)) {
for ($i = 0; $i < count($this->fields); $i++) {
if (strpos($this->values[$i], 'inline:') === false) {
//$result .= $this->fields[$i]." = '".addslashes($this->values[$i])."', ";
$result .= $this->fields[$i] . " = :" . $this->fields[$i] . ", ";
} else {
$this->values[$i] = preg_replace('/^inline:(.*)/', '\\1', $this->values[$i]);
$result .= $this->fields[$i] . " = " . $this->values[$i] . ", ";
}
}
$result = substr($result, 0, strlen($result) - 2);
}
break;
}
if (!$this->where->isEmpty()) {
if ($this->policy) {
$this->where->policy = $this->policy;
}
$result .= ' where ' . $this->where->get();
$this->_params = array_merge($this->_params, $this->where->getParams());
}
if (!empty($this->groupBy)) {
$result .= ' group by ' . $this->groupBy;
}
if (!empty($this->having)) {
$result .= ' having ' . $this->having;
}
if (!empty($this->orderBy)) {
$result .= ' order by ' . $this->orderBy;
}
if (!empty($this->limit)) {
$result .= ' limit ' . $this->limit;
}
$first_union = true;
foreach ($this->union as $union) {
if ($first_union && empty($field_result) && empty($table_result)) {
$result = $union->get();
} else {
if ($this->unionAllFlag) {
$result .= ' UNION ALL ' . $union->Get();
} else {
$result .= ' union ' . $union->Get();
}
}
$first_union = false;
}
return $result;
}
function execute()
{
$platform = $this->getPlatform();
return $platform->bitwiseOr() . parent::execute();
}
function __construct($mainTable = null, $quoteCharacter = '"', $tablePrefix = '')
{
parent::__construct($mainTable, $quoteCharacter, $tablePrefix);
}
/**
* Generate the query.
*
* @return string
*/
public function __toString()
{
$out = 'UPDATE';
if (null !== $this->_or) {
$out .= ' OR ' . $this->_or;
}
$out .= ' ' . $this->enclose($this->_table);
$set = [];
foreach ($this->_set as $name => $value) {
$set[] = $this->enclose($name) . ' = ' . $value;
}
$out .= ' SET ' . implode(', ', $set);
return $out . parent::__toString();
}
/**
* (non-PHPdoc)
* @see \Koldy\Db\Select::getQuery()
*/
protected function getQuery()
{
if ($this->searchTerm !== null) {
// there is search term set, so we'll need to include this to where statements
// but, there might be already some where statements in the query, so we'll create
// new Where instance and we'll add that Where block with AND operator
$where = Where::init();
if ($this->searchFields !== null) {
foreach ($this->searchFields as $field) {
$where->orWhereLike($field, "%{$this->searchTerm}%");
}
} else {
foreach ($this->getFields() as $field) {
$where->orWhereLike($field['name'], "%{$this->searchTerm}%");
}
}
$this->where($where);
}
return parent::getQuery();
}
function __construct($mainTable = null, $quoteCharacter = '"', $tablePrefix = '', $execCallback = null, $dbType = null)
{
parent::__construct($mainTable, $quoteCharacter, $tablePrefix, $execCallback, $dbType);
}
/**
* Generate the query.
*
* @return string
*/
public function __toString()
{
return 'DELETE FROM ' . $this->enclose($this->_from) . parent::__toString();
}
/**
* Generate the query.
*
* @return string
*/
public function __toString()
{
$out = 'SELECT';
if (null !== $this->_distinctOrAll) {
$out .= ' ' . $this->_distinctOrAll;
}
if (!empty($this->_columns)) {
$out .= ' ' . implode(', ', $this->enclose($this->_columns));
} else {
$out .= ' *';
}
if (!empty($this->_from)) {
$out .= ' FROM ';
$handle = [];
foreach ($this->_from as $alias => $from) {
if (is_int($alias)) {
$handle[] = $this->enclose($from);
} else {
$handle[] = $this->enclose($from) . ' AS ' . $this->enclose($alias);
}
}
$out .= implode(', ', $handle);
}
$out .= parent::__toString();
if (!empty($this->_groupBy)) {
$out .= ' GROUP BY ' . implode(', ', $this->enclose($this->_groupBy));
if (!empty($this->_having)) {
$out .= ' HAVING ' . $this->_having;
}
}
return $out;
}
