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());
 }
Example #2
0
 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 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()
 {
     $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()
 {
     $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 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()
 {
     $statement = QueryBuilder::update('tbl', ['field1' => 'value1']);
     $this->assertEquals('UPDATE tbl SET field1 = "value1"', QueryAssembler::stringify($statement));
     $statement->where(['NOT' => ['username' => null]]);
     $this->assertEquals('UPDATE tbl SET field1 = "value1" WHERE username IS NOT NULL', QueryAssembler::stringify($statement));
     $statement->set('username', 'john');
     $statement->andWhere((new LikePredicate())->setField('name')->setExpression(StartsWithExpression::create('Joh')));
     $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******" ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%"', QueryAssembler::stringify($statement));
     $statement->set('bob', null);
     $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%"', QueryAssembler::stringify($statement));
     $statement->orderBy(['username' => 'asc']);
     $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ORDER BY username ASC', QueryAssembler::stringify($statement));
     $statement->orderBy(['username' => 'desc', 'field1' => 'asc']);
     $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'ORDER BY username DESC, field1 ASC', QueryAssembler::stringify($statement));
     $statement->limit(2);
     $this->assertEquals('UPDATE tbl SET field1 = "value1", username = "******", bob = NULL ' . 'WHERE username IS NOT NULL AND name LIKE "Joh%" ' . 'ORDER BY username DESC, field1 ASC LIMIT 2', QueryAssembler::stringify($statement));
 }
 public function testBoolean()
 {
     $expression = new BooleanExpression();
     $expression->setValue(true);
     $this->assertEquals('true', QueryAssembler::stringify($expression));
     $expression->setValue(1);
     $this->assertEquals('true', QueryAssembler::stringify($expression));
     $expression->setValue('abc');
     $this->assertEquals('true', QueryAssembler::stringify($expression));
     $expression->setValue(false);
     $this->assertEquals('false', QueryAssembler::stringify($expression));
     $expression->setValue(0);
     $this->assertEquals('false', QueryAssembler::stringify($expression));
     $expression->setValue('');
     $this->assertEquals('false', QueryAssembler::stringify($expression));
     $stmt = QueryBuilder::update('tbl', ['field' => $expression]);
     $assembler = new QueryAssembler($stmt);
     $this->assertEquals('UPDATE tbl SET field = ?', $assembler->getQuery());
     $this->assertEquals([false], $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());
 }
Example #10
0
 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 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))));
 }
Example #13
0
<?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;
}
 public function testDelete()
 {
     $query = QueryBuilder::deleteFrom('tablename', ['x' => 'y', 'a' => 'b']);
     $this->assertEquals('DELETE FROM tablename WHERE x = "y" AND a = "b"', QueryAssembler::stringify($query));
 }
 /**
  * @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]));
 }
Example #16
0
 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));
 }
Example #17
0
 public function testExecute()
 {
     $datastore = new MockQlDataStore();
     $connection = new MockAbstractQlDataConnection();
     $datastore->setConnection($connection);
     $dao = new MockQlDao();
     $dao->id = 4;
     $dao->display = 'John Smith';
     $dao->boolTest = false;
     $datastore->save($dao);
     $datastore->execute(QueryBuilder::deleteFrom($dao->getTableName(), (new EqualPredicate())->setField('id')->setExpression(NumericExpression::create(4))));
     $this->assertEquals('DELETE FROM `mock_ql_daos` WHERE `id` = ?', $connection->getExecutedQuery());
     $this->assertEquals([4], $connection->getExecutedQueryValues());
 }
Example #18
0
 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));
 }
Example #19
0
 /**
  * @param $table
  * @param $keyValues
  *
  * @return CqlUpdateStatement
  */
 public static function update($table, array $keyValues = null)
 {
     return parent::update($table, $keyValues);
 }