public function testBaseSqlGeneration()
{
$dialect = ImaginaryDialect::me();
$pgDialect = $this->getDbByType('PgSQL')->getDialect();
$this->assertRegExp('/^\\(a (AND|and) b\\)$/', Expression::expAnd('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a (OR|or) b\\)$/', Expression::expOr('a', 'b')->toDialectString($dialect));
$this->assertEquals(Expression::eq('a', 'b')->toDialectString($dialect), '(a = b)');
$some = IdentifiableObject::wrap(123);
$this->assertEquals(Expression::eqId('a', $some)->toDialectString($dialect), '(a = 123)');
$this->assertEquals(Expression::notEq('a', 'b')->toDialectString($dialect), '(a != b)');
$this->assertEquals(Expression::gt('a', 'b')->toDialectString($dialect), '(a > b)');
$this->assertEquals(Expression::gtEq('a', 'b')->toDialectString($dialect), '(a >= b)');
$this->assertEquals(Expression::lt('a', 'b')->toDialectString($dialect), '(a < b)');
$this->assertEquals(Expression::ltEq('a', 'b')->toDialectString($dialect), '(a <= b)');
$this->assertRegExp('/^\\(a ((IS NOT NULL)|(is not null)) *\\)$/', Expression::notNull('a')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((IS NULL)|(is null)) *\\)$/', Expression::isNull('a')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((IS TRUE)|(is true)) *\\)$/', Expression::isTrue('a')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((IS FALSE)|(is false)) *\\)$/', Expression::isFalse('a')->toDialectString($dialect));
$this->assertRegExp('/^\\(a (LIKE|like) b\\)$/', Expression::like('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((NOT LIKE)|(not like)) b\\)$/', Expression::notLike('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a (ILIKE|ilike) b\\)$/', Expression::ilike('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((NOT ILIKE)|(not like)) b\\)$/', Expression::notIlike('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((SIMILAR TO)|(similar to)) b\\)$/', Expression::similar('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((NOT SIMILAR TO)|(not similar to)) b\\)$/', Expression::notSimilar('a', 'b')->toDialectString($dialect));
$this->assertEquals('(lower(a) = b)', Expression::eqLower('a', 'b')->toDialectString($dialect));
$this->assertEquals('(lower(a) = lower(b))', Expression::eqLower(new DBValue('a'), new DBValue('b'))->toDialectString($dialect));
$this->assertEquals('(lower(\'a\') = lower(\'b\'))', Expression::eqLower(new DBValue('a'), new DBValue('b'))->toDialectString($pgDialect));
$this->assertEquals('(lower(\'a\') = lower("b"))', Expression::eqLower(new DBValue('a'), new DBField('b'))->toDialectString($pgDialect));
$this->assertEquals('(lower("a") = lower(\'b\'))', Expression::eqLower(new DBField('a'), new DBValue('b'))->toDialectString($pgDialect));
$this->assertRegExp('/^\\(a (BETWEEN|between) b (AND|and) c\\)$/', Expression::between('a', 'b', 'c')->toDialectString($dialect));
$this->assertEquals('(a = 123)', Expression::in('a', 123)->toDialectString($dialect));
$this->assertEquals('(a = 123)', Expression::in('a', array(123))->toDialectString($dialect));
$this->assertRegExp('/^\\(a (in|IN) \\(123, 456\\)\\)$/', Expression::in('a', array(123, 456))->toDialectString($dialect));
$this->assertEquals('(a != 123)', Expression::notIn('a', 123)->toDialectString($dialect));
$this->assertEquals('(a != 123)', Expression::notIn('a', array(123))->toDialectString($dialect));
$this->assertRegExp('/^\\(a ((not in)|(NOT IN)) \\(123, 456\\)\\)$/', Expression::notIn('a', array(123, 456))->toDialectString($dialect));
$this->assertEquals('(a + b)', Expression::add('a', 'b')->toDialectString($dialect));
$this->assertEquals('(a - b)', Expression::sub('a', 'b')->toDialectString($dialect));
$this->assertEquals('(a * b)', Expression::mul('a', 'b')->toDialectString($dialect));
$this->assertEquals('(a / b)', Expression::div('a', 'b')->toDialectString($dialect));
$this->assertRegExp('/^\\(a (between|BETWEEN) b (and|AND) c\\)$/', Expression::between('a', 'b', 'c')->toDialectString($dialect));
$this->assertEquals('(-1 IS NULL)', Expression::isNull(-1)->toDialectString($dialect));
$this->assertEquals('(NOT a)', Expression::not('a')->toDialectString($dialect));
$this->assertEquals('(- a)', Expression::minus('a')->toDialectString($dialect));
try {
Expression::eq('id', null)->toDialectString($dialect);
$this->fail();
} catch (WrongArgumentException $e) {
//it's Ok
}
}
public function testProperties()
{
$query = OQL::select('from TestUser');
$criteria = Criteria::create(TestUser::dao());
$this->assertCriteria($query, $criteria);
$this->assertCriteria($query->addProperties(OQL::properties('id, count(id) as count')), $criteria->addProjection(Projection::property('id'))->addProjection(Projection::count('id', 'count')));
$this->assertCriteria($query->addProperties(OQL::properties('city.id')), $criteria->addProjection(Projection::property('city.id')));
$properties = OQL::properties('id');
$this->assertFalse($properties->isDistinct());
$this->assertEquals($properties->toProjection(), Projection::chain()->add(Projection::property('id')));
$properties = OQL::properties('id, distinct name');
$this->assertTrue($properties->isDistinct());
$this->assertEquals($properties->toProjection(), Projection::chain()->add(Projection::property('id'))->add(Projection::property('name')));
$properties = OQL::properties('$1')->bind(1, 'foo');
$this->assertEquals($properties->toProjection(), Projection::chain()->add(Projection::property('foo')));
$properties->bind(1, 'bar');
$this->assertEquals($properties->toProjection(), Projection::chain()->add(Projection::property('bar')));
$this->assertCriteria(OQL::select('from TestUser')->addProperties($properties->bind(1, 'foo'))->bind(1, 'bar'), Criteria::create(TestUser::dao())->addProjection(Projection::property('bar')));
$properties = OQL::properties('id, count(distinct city.id + $0), avg(some) as someAverage, ' . 'name not like "%Ы%", foo and (bar or baz), $1 / $2, ' . 'a in ($3, $0)')->bindAll(array(1, 2, 'num', 'test'));
$this->assertFalse($properties->isDistinct());
$this->assertEquals($properties->toProjection(), Projection::chain()->add(Projection::property('id'))->add(Projection::distinctCount(Expression::add('city.id', 1)))->add(Projection::avg('some', 'someAverage'))->add(Projection::property(Expression::notLike('name', '%Ы%')))->add(Projection::property(Expression::expAnd('foo', Expression::expOr('bar', 'baz'))))->add(Projection::property(Expression::div(2, 'num')))->add(Projection::property(Expression::in('a', array('test', 1)))));
}
public function testWhere()
{
$userId = 1;
$user = TestUser::create()->setId($userId);
$this->assertCriteria('from TestUser where id = $1 or id = $2 or $2 = id or $1 = $2', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::expOr(Expression::expOr(Expression::eqId('id', $user), Expression::eq('id', $userId)), Expression::eq($userId, 'id')), Expression::eq($userId, $userId))), array(1 => $user, 2 => $userId))->assertCriteria('from TestUser where id = 1 or id >= 1 or id <= 1 or id <> 1 or id != 1', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::expOr(Expression::expOr(Expression::expOr(Expression::eq('id', 1), Expression::gtEq('id', 1)), Expression::ltEq('id', 1)), Expression::notEq('id', 1)), Expression::notEq('id', 1))))->assertCriteria('from TestUser where id = 1 and Name = "some" ' . 'or Name = "any" or id = 1 > 2 = id * 2 + 1', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::expOr(Expression::expAnd(Expression::eq('id', 1), Expression::eq('Name', 'some')), Expression::eq('Name', 'any')), Expression::gt(Expression::eq('id', 1), Expression::eq(2, Expression::add(Expression::mul('id', 2), 1))))))->assertCriteria('from TestUser where (id = 1 and (Name = "some" or Name = "any"))', Criteria::create(TestUser::dao())->add(Expression::expAnd(Expression::eq('id', 1), Expression::expOr(Expression::eq('Name', 'some'), Expression::eq('Name', 'any')))))->assertCriteria('from TestUser where ((Name = "some" or Name = "any")) and (id = 1)', Criteria::create(TestUser::dao())->add(Expression::expAnd(Expression::expOr(Expression::eq('Name', 'some'), Expression::eq('Name', 'any')), Expression::eq('id', 1))))->assertCriteria('from TestUser where (id = 1) != ((1 = id) = (id >= 2))', Criteria::create(TestUser::dao())->add(Expression::notEq(Expression::eq('id', 1), Expression::eq(Expression::eq(1, 'id'), Expression::gtEq('id', 2)))))->assertCriteria('from TestUser where not (not not id = 1 and not id > 1)', Criteria::create(TestUser::dao())->add(Expression::not(Expression::expAnd(Expression::not(Expression::not(Expression::eq('id', 1))), Expression::not(Expression::gt('id', 1))))))->assertCriteria('from TestUser where id is null or id is not null or id is true or id is false', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::expOr(Expression::expOr(Expression::isNull('id'), Expression::notNull('id')), Expression::isTrue('id')), Expression::isFalse('id'))))->assertCriteria('from TestUser where id in (1) or id not in (1, "2", $1, true)', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::in('id', array(1)), Expression::notIn('id', array(1, '2', true, true)))), array(1 => true))->assertCriteria('from TestUser where id in ($1)', Criteria::create(TestUser::dao())->add(Expression::in('id', Criteria::create(TestUser::dao())->setProjection(Projection::property('id')))), array(1 => OQL::select('id from TestUser')->toCriteria()))->assertCriteria('from TestUser where id in ($1)', Criteria::create(TestUser::dao())->add(Expression::in('id', array(1, 2))), array(1 => array(1, 2)))->assertCriteria('from TestUser where id like $1 or id not like "Ы%" ' . 'or id ilike $2 or id not ilike "ы%" ' . 'or Name similar to "s" or Name not similar to $3', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::expOr(Expression::expOr(Expression::expOr(Expression::expOr(Expression::like('id', 'ы'), Expression::notLike('id', 'Ы%')), Expression::ilike('id', 'Ы')), Expression::notIlike('id', 'ы%')), Expression::similar('Name', 's')), Expression::notSimilar('Name', 'S'))), array(1 => 'ы', 2 => 'Ы', 3 => 'S'))->assertCriteria('from TestUser where created between "2008-08-06 10:00" and $1 ' . 'or id between id and 10', Criteria::create(TestUser::dao())->add(Expression::expOr(Expression::between('created', '2008-08-06 10:00', SQLFunction::create('now')), Expression::between('id', 'id', 10))), array(1 => SQLFunction::create('now')))->assertCriteria('from TestUser where (2 + -id --1) / 2 = id', Criteria::create(TestUser::dao())->add(Expression::eq(Expression::div(Expression::sub(Expression::add(2, Expression::minus('id')), -1), 2), 'id')), array(1 => 'id'));
}
