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 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()
{
$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());
}
public function testAdvancedWhere()
{
$query = QueryBuilder::select('id', 'name');
$query->from('companies');
$query->where(['OR' => [['name' => 'acme'], ['type' => 'internal']], ['OR' => [['status' => 'active'], 'NOT' => [['status' => ['inactive', 'suspended']]]]]]);
$this->assertEquals('SELECT id, name' . ' FROM companies' . ' WHERE' . ' (name = "acme" OR type = "internal")' . ' AND' . ' (status = "active" OR status NOT IN ("inactive","suspended"))', QueryAssembler::stringify($query));
}
public function testExpressionSelectExpression()
{
$this->assertEquals('(SUM(field1) + SUM(field2)) AS sum_field', QueryAssembler::stringify(ExpressionSelectExpression::createWithAlias(AdditionExpression::create(SumSelectExpression::create('field1'), SumSelectExpression::create('field2')), 'sum_field')));
$this->assertEquals('SELECT (SUM(field1) + SUM(field2)) AS sum_field FROM tbl HAVING sum_field >= 5', QueryAssembler::stringify(QueryBuilder::select(ExpressionSelectExpression::createWithAlias(AdditionExpression::create(SumSelectExpression::create('field1'), SumSelectExpression::create('field2')), 'sum_field'))->from('tbl')->having(GreaterThanOrEqualPredicate::create('sum_field', 5))));
}
<?php
use Packaged\QueryBuilder\Assembler\QueryAssembler;
use Packaged\QueryBuilder\Builder\QueryBuilder;
use Packaged\QueryBuilder\Predicate\EqualPredicate;
use Packaged\QueryBuilder\SelectExpression\AllSelectExpression;
include_once 'vendor/autoload.php';
for ($i = 0; $i < 100; $i++) {
$start = microtime(true);
$query = QueryBuilder::select(new AllSelectExpression());
$query->from('table');
$query->where(['a' => 'b', 'c' => 'd']);
$query->andWhere([EqualPredicate::create('e', 'f'), EqualPredicate::create('g', 'h')]);
QueryAssembler::stringify($query);
echo "Completed In: " . (microtime(true) - $start);
echo PHP_EOL;
}
/**
* @expectedException \Packaged\QueryBuilder\Exceptions\Assembler\CqlAssemblerException
* @expectedExceptionMessage Null is not available in CQL Queries
*/
public function testEmptySelect()
{
CqlAssembler::stringify(QueryBuilder::select()->from('table')->where(['id' => null]));
}
public function testGetData()
{
$datastore = new MockCqlDataStore();
$connection = new MockCqlConnection();
$connection->setConfig('keyspace', 'packaged_dal');
$datastore->setConnection($connection);
$connection->setResolver(new DalResolver());
$dao = new MockCqlDao();
$dao->id = 'test1';
$dao->id2 = 5555;
$dao->username = '******';
$datastore->save($dao);
$eq = new EqualPredicate();
$eq->setField('id');
$eq->setExpression(ValueExpression::create('test1'));
$d = $datastore->getData(QueryBuilder::select()->from($dao->getTableName())->where($eq));
$testDao = new MockCqlDao();
$testDao->hydrateDao($d[0], true);
$testDao->markDaoAsLoaded();
$testDao->markDaoDatasetAsSaved();
$this->assertEquals($dao, $testDao);
}
public function testSubquery()
{
$subQuery = SubQuerySelectExpression::create(QueryBuilder::select('myField')->from('myTable')->limitWithOffset(5, 10), '_');
$stmt = QueryBuilder::select(CountSelectExpression::create())->from($subQuery);
$this->assertEquals('SELECT COUNT(*) FROM (SELECT myField FROM myTable LIMIT 5,10) AS _', QueryAssembler::stringify($stmt));
}
/**
* @param ...$expressions
*
* @return CqlQueryStatement
*/
public static function select(...$expressions)
{
return parent::select(...$expressions);
}
public function testGetData()
{
$dao = new MockQlDao();
$datastore = new MockQlDataStore();
$connection = new MockAbstractQlDataConnection();
$datastore->setConnection($connection);
$datastore->getData(QueryBuilder::select()->from($dao->getTableName()));
$this->assertEquals('SELECT * FROM `mock_ql_daos`', $connection->getExecutedQuery());
}
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));
}
