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());
}
/**
* Reset the query to a single select * FROM table
*
* @return $this
*/
public function resetQuery()
{
$builder = $this->_getQueryBuilder();
$dao = $this->createNewDao(false);
$this->_query = $builder::select(AllSelectExpression::create($dao->getTableName()))->from($dao->getTableName());
return $this;
}
public function testChangingParameters()
{
$val2 = ValueExpression::create(123);
$assembler = new QueryAssembler(QueryBuilder::select(AllSelectExpression::create())->from('tbl')->where(['field1' => 'val1', 'field2' => $val2]));
$this->assertEquals(['val1', 123], $assembler->getParameters());
$val2->setValue(456);
$this->assertEquals(['val1', 456], $assembler->getParameters());
}
public function testAssemble()
{
$selector = CountSelectExpression::create();
$this->assertEquals('COUNT(*)', QueryAssembler::stringify($selector));
$selector->setField(AllSelectExpression::create());
$this->assertEquals('COUNT(*)', QueryAssembler::stringify($selector));
$selector->setField('fieldname');
$this->assertEquals('COUNT(fieldname)', QueryAssembler::stringify($selector));
$selector->setAlias('cnt');
$this->assertEquals('COUNT(fieldname) AS cnt', QueryAssembler::stringify($selector));
$selector->setDistinct();
$this->assertEquals('COUNT(DISTINCT fieldname) AS cnt', QueryAssembler::stringify($selector));
}
public function testAssemble()
{
$expression = MatchExpression::create(FieldExpression::createWithTable('field1', 'table1'), StringExpression::create('this is a test search'));
$expression->addField(FieldExpression::createWithTable('field2', 'table2'));
$this->assertEquals('MATCH (`table1`.`field1`,`table2`.`field2`) AGAINST ("this is a test search")', MySQLAssembler::stringify($expression));
$expression->setSearchModifier(MatchExpression::BOOLEAN_MODE);
$this->assertEquals('MATCH (`table1`.`field1`,`table2`.`field2`) AGAINST ("this is a test search" IN BOOLEAN MODE)', MySQLAssembler::stringify($expression));
$expression->setSearchModifier(MatchExpression::WITH_QUERY_EXPANSION);
$this->assertEquals('MATCH (`table1`.`field1`,`table2`.`field2`) AGAINST ("this is a test search" WITH QUERY EXPANSION)', MySQLAssembler::stringify($expression));
$expression->setSearchModifier(MatchExpression::BOOLEAN_MODE);
$stmt = QueryBuilder::select(AllSelectExpression::create())->from('tbl')->where(GreaterThanPredicate::create($expression, 0))->limit(5);
$assembler = new MySQLAssembler($stmt);
$this->assertEquals('SELECT * FROM `tbl` WHERE MATCH (`table1`.`field1`,`table2`.`field2`) AGAINST (? IN BOOLEAN MODE) > ? LIMIT ?', $assembler->getQuery());
$this->assertEquals(['this is a test search', 0, 5], $assembler->getParameters());
}
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());
}
/**
* Hydrate a DAO from the data store
*
* @param IDao $dao
*
* @return IDao Loaded DAO
*
* @throws DaoNotFoundException
* @throws DataStoreException
*/
public function load(IDao $dao)
{
$dao = $this->_verifyDao($dao);
$qb = static::_getQueryBuilderClass();
//Limit the result set to 2, for validation against multiple results
$assembler = $this->_assemble($qb::select(AllSelectExpression::create())->from($dao->getTableName())->where($dao->getId(true))->limit(2));
$results = $this->_connectedConnection()->fetchQueryResults($assembler->getQuery(), $assembler->getParameters());
switch (count($results)) {
case 1:
$dao->hydrateDao(reset($results), true);
$dao->markDaoAsLoaded();
$dao->markDaoDatasetAsSaved();
break;
case 0:
throw new DaoNotFoundException("Unable to locate Dao");
default:
throw new TooManyResultsException("Too many results located");
}
return $dao;
}
public function assembleAllSelectExpression(AllSelectExpression $expr)
{
return $expr->hasTable() ? $this->escapeField($expr->getTable()) . '.*' : '*';
}
public function testStatics()
{
$this->assertEquals('*', QueryAssembler::stringify(AllSelectExpression::create()));
$this->assertEquals('table.*', QueryAssembler::stringify(AllSelectExpression::create('table')));
}
public function testLeftFullStmt()
{
$query = QueryBuilder::select(AllSelectExpression::create())->from(TableSelectExpression::createWithAlias('table_one', 't1'))->leftOuterJoin(TableSelectExpression::create('table_two'), 'myfield');
$this->assertEquals('SELECT * FROM table_one AS t1 LEFT OUTER JOIN table_two ON t1.myfield = table_two.myfield', QueryAssembler::stringify($query));
}
public function testAssemble()
{
$statement = CqlQueryBuilder::select(AllSelectExpression::create());
$statement->addClause(new AllowFilteringClause());
$this->assertEquals('SELECT * ALLOW FILTERING', CqlAssembler::stringify($statement));
}