/**
* @param QueryPartInterface $setClass
* @param string $setOperation
* @param $glue
*
* @return string
*/
protected function abstractWrite(QueryPartInterface $setClass, $setOperation, $glue)
{
$selects = [];
foreach ($setClass->{$setOperation}() as $select) {
$selects[] = $this->writer->write($select);
}
return \implode("\n" . $glue . "\n", $selects);
}
/**
* @param Delete $delete
*
* @return string
*/
public function write(Delete $delete)
{
$table = $this->writer->writeTable($delete->getTable());
$parts = array("DELETE FROM {$table}");
AbstractBaseWriter::writeWhereCondition($delete, $this->writer, $this->placeholderWriter, $parts);
AbstractBaseWriter::writeLimitCondition($delete, $this->placeholderWriter, $parts);
$comment = AbstractBaseWriter::writeQueryComment($delete);
return $comment . implode(" ", $parts);
}
/**
* @test
*/
public function itShouldWriteUnionAllFromGenericBuilder()
{
$minus = $this->writer->minus(new Select('user'), new Select('user_email'));
$expected = <<<SQL
SELECT user.* FROM user
MINUS
SELECT user_email.* FROM user_email
SQL;
$this->assertEquals($expected, $this->writer->write($minus));
}
/**
* @test
*/
public function itShouldBeAbleToWriteCommentInQuery()
{
$valueArray = array('user_id' => 1, 'name' => 'Nil', 'contact' => '*****@*****.**');
$this->query->setTable('user')->setComment('This is a comment')->setValues($valueArray);
$expected = "-- This is a comment\n" . 'REPLACE INTO user (user.user_id, user.name, user.contact) VALUES (:v1, :v2, :v3)';
$this->assertSame($expected, $this->writer->write($this->query));
$this->assertEquals(\array_values($valueArray), \array_values($this->query->getValues()));
$expected = array(':v1' => 1, ':v2' => 'Nil', ':v3' => '*****@*****.**');
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @test
*/
public function itShouldWriteIntersectFromGenericBuilder()
{
$intersect = $this->writer->intersect();
$intersect->add(new Select('user'));
$intersect->add(new Select('user_email'));
$expected = <<<SQL
SELECT user.* FROM user
INTERSECT
SELECT user_email.* FROM user_email
SQL;
$this->assertEquals($expected, $this->writer->write($intersect));
}
/**
* @test
*/
public function itShouldWriteUnionAllFromGenericBuilder()
{
$unionAll = $this->writer->union();
$unionAll->add(new Select('user'));
$unionAll->add(new Select('user_email'));
$expected = <<<SQL
SELECT user.* FROM user
UNION
SELECT user_email.* FROM user_email
SQL;
$this->assertEquals($expected, $this->writer->write($unionAll));
}
/**
* @param Insert $insert
*
* @throws QueryException
*
* @return string
*/
public function write(Insert $insert)
{
$columns = $insert->getColumns();
if (empty($columns)) {
throw new QueryException('No columns were defined for the current schema.');
}
$columns = $this->writeQueryColumns($columns);
$values = $this->writeQueryValues($insert->getValues());
$table = $this->writer->writeTable($insert->getTable());
$comment = AbstractBaseWriter::writeQueryComment($insert);
return $comment . "INSERT INTO {$table} ({$columns}) VALUES ({$values})";
}
/**
* @test
*/
public function itShouldWriteDeleteRowWithWhereConditionAndLimit1()
{
$this->query->setTable('user');
$conditions = $this->query->where();
$conditions->equals('user_id', 10)->equals('user_id', 20)->equals('user_id', 30);
$this->query->limit(1);
$expected = <<<SQL
DELETE FROM user WHERE (user.user_id = :v1) AND (user.user_id = :v2) AND (user.user_id = :v3) LIMIT :v4
SQL;
$this->assertSame($expected, $this->writer->write($this->query));
$expected = array(':v1' => 10, ':v2' => 20, ':v3' => 30, ':v4' => 1);
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @test
*/
public function itShouldWriteJoin()
{
$this->query->isJoin(true)->setTable('user')->on()->equals('user_id', 1);
$expected = 'JOIN user ON (user.user_id = :v1)';
$this->assertSame($expected, $this->writer->write($this->query));
$expected = array(':v1' => 1);
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @test
*/
public function itShouldAllowWhereConditionAsLiteral()
{
$this->query->setTable('user')->where()->asLiteral("(username is not null and status=:status)")->notEquals('name', '%N%');
$expected = "SELECT user.* FROM user WHERE (username is not null and status=:status) AND (user.name <> :v1)";
$this->assertSame($expected, $this->writer->write($this->query));
$expected = array(':v1' => '%N%');
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @test
*/
public function itShouldBeAbleToWriteColumnAsAFuncWithoutBracketsStatement()
{
$this->query->setTable('user')->setColumns(array('user_id', 'username'))->setFunctionAsColumn('CURRENT_TIMESTAMP', array(), 'server_time')->where()->equals('user_id', 1);
$expected = 'SELECT user.user_id, user.username, CURRENT_TIMESTAMP AS \'server_time\' FROM user WHERE (user.user_id = :v1)';
$this->assertSame($expected, $this->writer->write($this->query));
$expected = array(':v1' => 1);
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @param Column $column
*
* @return string
*/
public function writeColumn(Column $column)
{
$alias = $column->getTable()->getAlias();
$table = $alias ? $this->writer->writeTableAlias($alias) : $this->writer->writeTable($column->getTable());
$columnString = empty($table) ? '' : "{$table}.";
$columnString .= $this->writer->writeColumnName($column);
return $columnString;
}
/**
* @test
*/
public function itShouldWriteUpdateQueryWithWhereConstrainAndLimit1()
{
$this->query->setTable('user')->setValues($this->valueArray)->where()->equals('user_id', 1);
$this->query->limit(1);
$expected = 'UPDATE user SET user.user_id = :v1, user.name = :v2, user.contact = :v3 WHERE (user.user_id = :v4) LIMIT :v5';
$this->assertSame($expected, $this->writer->write($this->query));
$expected = array(':v1' => 1, ':v2' => 'Nil', ':v3' => '*****@*****.**', ':v4' => 1, ':v5' => 1);
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @test
*/
public function itShouldBeAbleToDoWhereNotExists()
{
$select = new Select('banned_user');
$select->where()->equals('user_id', 1);
$this->query->setTable('user')->setColumns(array('user_id', 'role_id'))->where()->notExists($select)->equals('user', 'Nil');
$expected = 'SELECT user.user_id, user.role_id FROM user WHERE (user.user = :v1) AND ' . 'NOT EXISTS (SELECT banned_user.* FROM banned_user WHERE (banned_user.user_id = :v2))';
$this->assertSame($expected, $this->writer->write($this->query));
$expected = array(':v1' => 'Nil', ':v2' => 1);
$this->assertEquals($expected, $this->writer->getValues());
}
/**
* @test
*/
public function itShouldWriteSqlWhenCastToString()
{
$query = $this->writer->select()->setTable('user');
$expected = $this->writer->write($query);
$this->assertSame($expected, (string) $query);
}
/**
* {@inheritdoc}
*
* @param $alias
*
* @return string
*/
public function writeTableAlias($alias)
{
return $this->wrapper(parent::writeTableAlias($alias));
}
<?php
use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder;
$builder = new GenericBuilder();
$query = $builder->select()->setTable('user');
echo $builder->write($query);
/**
* @param Minus $minus
*
* @return string
*/
public function write(Minus $minus)
{
$first = $this->writer->write($minus->getFirst());
$second = $this->writer->write($minus->getSecond());
return $first . "\n" . Minus::MINUS . "\n" . $second;
}