public function testAssemble()
{
$clause = new LimitClause();
$clause->setLimit(10);
$this->assertEquals('LIMIT 10', QueryAssembler::stringify($clause));
$clause->setOffset(5);
$this->assertEquals('LIMIT 5,10', QueryAssembler::stringify($clause));
$stmt = QueryBuilder::select(AllSelectExpression::create())->limit(3);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * LIMIT ?', $assembler->getQuery());
$this->assertEquals([3], $assembler->getParameters());
$stmt = QueryBuilder::select(AllSelectExpression::create())->limitWithOffset(0, 3);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * LIMIT ?,?', $assembler->getQuery());
$this->assertEquals([0, 3], $assembler->getParameters());
$offset = NumericExpression::create(0);
$limit = NumericExpression::create(3);
$stmt = QueryBuilder::select(AllSelectExpression::create())->limitWithOffset($offset, $limit);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * LIMIT ?,?', $assembler->getQuery());
$this->assertEquals([0, 3], $assembler->getParameters());
$offset->setValue(25);
$limit->setValue(38);
$this->assertEquals('SELECT * LIMIT ?,?', $assembler->getQuery());
$this->assertEquals([25, 38], $assembler->getParameters());
}
public function testNull()
{
$expression = ValueExpression::create(null);
$this->assertNull($expression->getValue());
$this->assertEquals('NULL', QueryAssembler::stringify($expression));
$stmt = QueryBuilder::update('tbl', ['field' => $expression]);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('UPDATE tbl SET field = ?', $assembler->getQuery());
$this->assertEquals([null], $assembler->getParameters());
}
public function testAssemble()
{
$clause = new SelectClause();
$this->assertEquals('SELECT *', QueryAssembler::stringify($clause));
$clause->addExpression((new FieldSelectExpression())->setField('one'));
$this->assertEquals('SELECT one', QueryAssembler::stringify($clause));
$clause->addExpression((new FieldSelectExpression())->setField('two')->setAlias('three'));
$this->assertEquals('SELECT one, two AS three', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$clause->addExpression(new NowSelectExpression());
$this->assertEquals('SELECT NOW()', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$clause->addField('first');
$this->assertEquals('SELECT first', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$clause->addField(FieldSelectExpression::create('first'));
$this->assertEquals('SELECT first', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$clause->addField(new NowSelectExpression());
$this->assertEquals('SELECT NOW()', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$clause->addField(['full_name' => ['first', '" "', 'last']]);
$this->assertEquals('SELECT CONCAT(first," ",last) AS full_name', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$clause->addField('first');
$clause->setDistinct(true);
$this->assertEquals('SELECT DISTINCT first', QueryAssembler::stringify($clause));
$this->setExpectedException("InvalidArgumentException");
$clause->addField(new \stdClass());
}
public function testStatics()
{
$this->assertEquals('SUBSTRING(fieldname,0)', QueryAssembler::stringify(SubStringSelectExpression::create('fieldname')));
$this->assertEquals('SUBSTRING(fieldname,0) AS new', QueryAssembler::stringify(SubStringSelectExpression::createWithAlias('fieldname', 'new')));
$this->assertEquals('SUBSTRING(fieldname,10) AS new', QueryAssembler::stringify(SubStringSelectExpression::create('fieldname', 10, null, 'new')));
$this->assertEquals('SUBSTRING(fieldname,10,5) AS new', QueryAssembler::stringify(SubStringSelectExpression::create('fieldname', 10, 5, 'new')));
}
public function testStatics()
{
$this->assertEquals('(field_name T 5)', QueryAssembler::stringify(FinalAbstractArithmeticExpression::create(FieldExpression::create('field_name'), 5)));
$this->assertEquals('(field_name T 5)', QueryAssembler::stringify(FinalAbstractArithmeticExpression::create(FieldExpression::create('field_name'), StringExpression::create(5))));
$this->assertEquals('(tbl.field_name T 5)', QueryAssembler::stringify(FinalAbstractArithmeticExpression::create(FieldExpression::createWithTable('field_name', 'tbl'), 5)));
$this->assertEquals('(tbl.field_name T 5)', QueryAssembler::stringify(FinalAbstractArithmeticExpression::create(FieldExpression::createWithTable('field_name', 'tbl'), StringExpression::create(5))));
}
public function testDuplicateKey()
{
$clause = new DuplicateKeyClause();
$clause->addPredicate(EqualPredicate::create('field1', FieldExpression::create('field2')));
$clause->addPredicate(EqualPredicate::create('field3', 5));
$this->assertEquals('ON DUPLICATE KEY UPDATE field1 = field2, field3 = 5', QueryAssembler::stringify($clause));
}
public function testAssemble()
{
$selector = new MinSelectExpression();
$selector->setField('fieldname');
$this->assertEquals('MIN(fieldname)', QueryAssembler::stringify($selector));
$selector->setAlias('mn');
$this->assertEquals('MIN(fieldname) AS mn', QueryAssembler::stringify($selector));
}
public function testAssemble()
{
$selector = new FirstSelectExpression();
$selector->setField('fieldname');
$this->assertEquals('FIRST(fieldname)', QueryAssembler::stringify($selector));
$selector->setAlias('fst');
$this->assertEquals('FIRST(fieldname) AS fst', QueryAssembler::stringify($selector));
}
public function testAssemble()
{
$selector = new SumSelectExpression();
$selector->setField('fieldname');
$this->assertEquals('SUM(fieldname)', QueryAssembler::stringify($selector));
$selector->setAlias('cnt');
$this->assertEquals('SUM(fieldname) AS cnt', QueryAssembler::stringify($selector));
}
public function testAssemble()
{
$statement = QueryBuilder::insertInto('tbl');
$this->assertEquals('INSERT INTO tbl ()', QueryAssembler::stringify($statement));
$statement = QueryBuilder::insertInto('tbl', 'field');
$this->assertEquals('INSERT INTO tbl (field)', QueryAssembler::stringify($statement));
$statement->insertInto('tbl', FieldExpression::create('id'), 'name');
$this->assertEquals('INSERT INTO tbl (id, name)', QueryAssembler::stringify($statement));
$statement->values(null, ValueExpression::create('Test'));
$this->assertEquals('INSERT INTO tbl (id, name) ' . 'VALUES (NULL, "Test")', QueryAssembler::stringify($statement));
$statement->values('row2v1', 'row2v2');
$this->assertEquals('INSERT INTO tbl (id, name) ' . 'VALUES (NULL, "Test"), ("row2v1", "row2v2")', QueryAssembler::stringify($statement));
$statement = QueryBuilder::insertInto('tbl', 'field1', 'field2')->values('value1', 'value2')->onDuplicateKeyUpdate('field1', IncrementExpression::create('field2', 1));
$assembler = new QueryAssembler($statement);
$this->assertEquals('INSERT INTO tbl (field1, field2) VALUES (?, ?) ON DUPLICATE KEY UPDATE field1 = field2 + ?', $assembler->getQuery());
$this->assertEquals(['value1', 'value2', 1], $assembler->getParameters());
}
public function testAssemble()
{
$clause = new InsertClause();
$clause->setTable('tester');
$this->assertEquals('INSERT INTO tester ()', QueryAssembler::stringify($clause));
$clause->addField((new FieldExpression())->setField('id'));
$this->assertEquals('INSERT INTO tester (id)', QueryAssembler::stringify($clause));
}
public function testCreate()
{
$class = new FinalLimitTrait();
$class->limit(1);
$this->assertEquals('LIMIT 1', QueryAssembler::stringify($class));
$class->limitWithOffset(10, 1);
$this->assertEquals('LIMIT 10,1', QueryAssembler::stringify($class));
}
public function testAssemble()
{
$selector = new NowSelectExpression();
$selector->setField('fieldname');
$this->assertEquals('NOW()', QueryAssembler::stringify($selector));
$selector->setAlias('current');
$this->assertEquals('NOW() AS current', QueryAssembler::stringify($selector));
}
public function testAssemble()
{
$selector = new LowerCaseSelectExpression();
$selector->setField('fieldname');
$this->assertEquals('LCASE(fieldname)', QueryAssembler::stringify($selector));
$selector->setAlias('lowe');
$this->assertEquals('LCASE(fieldname) AS lowe', QueryAssembler::stringify($selector));
}
public function testAssemble()
{
$selector = new AverageSelectExpression();
$selector->setField('fieldname');
$this->assertEquals('AVG(fieldname)', QueryAssembler::stringify($selector));
$selector->setAlias('aver');
$this->assertEquals('AVG(fieldname) AS aver', QueryAssembler::stringify($selector));
}
public function testBooleanValue()
{
$expression = new ValueExpression();
$expression->setValue(true);
$this->assertEquals('true', QueryAssembler::stringify($expression));
$expression->setValue(false);
$this->assertEquals('false', QueryAssembler::stringify($expression));
}
public function testAssemble()
{
$expression = new IncrementExpression();
$expression->setField(FieldExpression::create('new_field'));
$this->assertEquals('new_field + 0', QueryAssembler::stringify($expression));
$expression->setIncrementValue('abc');
$this->assertEquals('new_field + 0', QueryAssembler::stringify($expression));
$expression->setIncrementValue('1');
$this->assertEquals('new_field + 1', QueryAssembler::stringify($expression));
$expression->setIncrementValue(1);
$this->assertEquals('new_field + 1', QueryAssembler::stringify($expression));
$stmt = QueryBuilder::update('tbl');
$stmt->set('inc_field', $expression);
$qa = new QueryAssembler($stmt);
$this->assertEquals('UPDATE tbl SET inc_field = new_field + ?', $qa->getQuery());
$this->assertEquals([1], $qa->getParameters());
}
public function setQuery(QueryStatement $query, $alias = null)
{
$this->_alias = $alias;
if ($this->_alias === null) {
$this->_alias = substr(md5(QueryAssembler::stringify($query)), 0, 6);
}
$this->_query = $query;
return $this;
}
public function testCreate()
{
$class = new FinalOrderByTrait();
$class->orderBy('one');
$this->assertEquals("ORDER BY one", QueryAssembler::stringify($this->_getClause($class)));
$class->orderBy('one', 'two');
$this->assertEquals("ORDER BY one, two", QueryAssembler::stringify($this->_getClause($class)));
$class->orderBy(['one', 'two', 'three' => 'ASC']);
$this->assertEquals("ORDER BY one, two, three ASC", QueryAssembler::stringify($this->_getClause($class)));
}
public function testCreate()
{
$class = new FinalWhereTrait();
$class->where(['username' => 'test', 'status' => 'pending']);
$this->assertEquals('WHERE username = "******" AND status = "pending"', QueryAssembler::stringify($class));
$class->orWhere(['username' => 'testing', 'status' => 'active']);
$this->assertEquals('WHERE (username = "******" AND status = "pending") ' . 'OR (username = "******" AND status = "active")', QueryAssembler::stringify($class));
$class->andWhere(['username' => 'tested', 'status' => 'disabled']);
$this->assertEquals('WHERE ((username = "******" AND status = "pending") ' . 'OR (username = "******" AND status = "active")) ' . 'AND (username = "******" AND status = "disabled")', QueryAssembler::stringify($class));
}
public function testAssemble()
{
$this->assertEquals('', QueryAssembler::stringify(PredicateSet::create()));
$eq = new EqualPredicate();
$eq->setField("first")->setExpression(ValueExpression::create('val1'));
$neq = new NotEqualPredicate();
$neq->setField("second")->setExpression(ValueExpression::create('val1'));
$set = PredicateSet::create($eq, $neq);
$this->assertEquals('(first = "val1" AND second <> "val1")', QueryAssembler::stringify($set));
}
public function testAssemble()
{
$expression = DecrementExpression::create(FieldSelectExpression::create('new_field'), 0);
$this->assertEquals('new_field - 0', QueryAssembler::stringify($expression));
$expression->setDecrementValue('abc');
$this->assertEquals('new_field - 0', QueryAssembler::stringify($expression));
$expression->setDecrementValue('1');
$this->assertEquals('new_field - 1', QueryAssembler::stringify($expression));
$expression->setDecrementValue(1);
$this->assertEquals('new_field - 1', QueryAssembler::stringify($expression));
}
public function testAssemble()
{
$clause = new TestFieldClause();
$clause->addField((new FieldExpression())->setField('first'));
$this->assertEquals('T first', QueryAssembler::stringify($clause));
$clause->addField((new FieldExpression())->setField('second'));
$this->assertEquals('T first, second', QueryAssembler::stringify($clause));
$clause->clearFields();
$clause->addField((new FieldExpression())->setField('third'));
$this->assertEquals('T third', QueryAssembler::stringify($clause));
}
public function testAssemble()
{
$clause = new ValuesClause();
$this->assertEquals('VALUES ()', QueryAssembler::stringify($clause));
$clause->addExpression((new StringExpression())->setValue('one'));
$this->assertEquals('VALUES ("one")', QueryAssembler::stringify($clause));
$clause->addExpression(new ValueExpression());
$this->assertEquals('VALUES ("one", NULL)', QueryAssembler::stringify($clause));
$clause->clearExpressions();
$this->assertEquals('VALUES ()', QueryAssembler::stringify($clause));
}
public function testAssemble()
{
$predicate = new LessThanOrEqualPredicate();
$predicate->setField('field');
$this->assertEquals('field <= NULL', QueryAssembler::stringify($predicate));
$predicate->setExpression((new NumericExpression())->setValue(1));
$this->assertEquals('field <= 1', QueryAssembler::stringify($predicate));
$predicate->setExpression((new NumericExpression())->setValue('1'));
$this->assertEquals('field <= 1', QueryAssembler::stringify($predicate));
$predicate->setExpression((new StringExpression())->setValue('abc'));
$this->assertEquals('field <= "abc"', QueryAssembler::stringify($predicate));
}
public function testAssemble()
{
$predicate = new NotInPredicate();
$predicate->setField('field');
$this->assertEquals('field NOT IN NULL', QueryAssembler::stringify($predicate));
$predicate->setExpression((new NumericExpression())->setValue(1));
$this->assertEquals('field NOT IN 1', QueryAssembler::stringify($predicate));
$predicate->setExpression(ArrayExpression::create(['1', 2, 3]));
$this->assertEquals('field NOT IN ("1",2,3)', QueryAssembler::stringify($predicate));
$predicate->setExpression(ValueExpression::create([4]));
$this->assertEquals('field NOT IN (4)', QueryAssembler::stringify($predicate));
}
public function testAssemble()
{
$predicate = new InPredicate();
$predicate->setField('field');
$this->assertEquals('field IN NULL', QueryAssembler::stringify($predicate));
$predicate->setExpression((new NumericExpression())->setValue(1));
$this->assertEquals('field IN 1', QueryAssembler::stringify($predicate));
$predicate->setExpression(ArrayExpression::create(['1', 'my "quoted" test', 3]));
$this->assertEquals('field IN ("1","my \\"quoted\\" test",3)', QueryAssembler::stringify($predicate));
$predicate->setExpression(ValueExpression::create([4]));
$this->assertEquals('field IN (4)', QueryAssembler::stringify($predicate));
$changeExpr = StringExpression::create('my "quoted" test');
$predicate->setExpression(ArrayExpression::create(['1', $changeExpr, 3]));
$stmt = QueryBuilder::select(AllSelectExpression::create());
$stmt->where($predicate);
$assembler = new QueryAssembler($stmt);
$this->assertEquals('SELECT * WHERE field IN (?,?,?)', $assembler->getQuery());
$this->assertEquals(['1', 'my "quoted" test', 3], $assembler->getParameters());
$changeExpr->setValue('changed text');
$this->assertEquals(['1', 'changed text', 3], $assembler->getParameters());
}
public function testAssemble()
{
$predicate = new FinalAbstractOperatorPredicateTest();
$predicate->setField('field');
$this->assertEquals('field T NULL', QueryAssembler::stringify($predicate));
$predicate->setExpression((new NumericExpression())->setValue(1));
$this->assertEquals('field T 1', QueryAssembler::stringify($predicate));
$predicate->setExpression((new NumericExpression())->setValue('1'));
$this->assertEquals('field T 1', QueryAssembler::stringify($predicate));
$predicate->setExpression((new StringExpression())->setValue('abc'));
$this->assertEquals('field T "abc"', QueryAssembler::stringify($predicate));
}
public function testAssemble()
{
$clause = new SetClause();
$eq = new EqualPredicate();
$eq->setField('one')->setExpression((new StringExpression())->setValue('val'));
$clause->addPredicate($eq);
$this->assertEquals('SET one = "val"', QueryAssembler::stringify($clause));
$inc = new EqualPredicate();
$inc->setField('two')->setExpression(IncrementExpression::create('two', 5));
$clause->addPredicate($inc);
$this->assertEquals('SET one = "val", two = two + 5', QueryAssembler::stringify($clause));
}
public function testAssemble()
{
$expression = new NumericExpression();
$expression->setValue(1);
$this->assertEquals('1', QueryAssembler::stringify($expression));
$expression->setValue('1a');
$this->assertEquals('1', QueryAssembler::stringify($expression));
$expression->setValue(1.2);
$this->assertEquals('1.2', QueryAssembler::stringify($expression));
$expression->setValue('abc');
$this->assertEquals('0', QueryAssembler::stringify($expression));
}