public function testWithObjects()
{
$criteria = Criteria::create(TestUser::dao())->add(Expression::containsIp(IpRange::create('192.168.1.1-192.168.1.255'), 'ip'))->addProjection(Projection::property('id'));
$this->assertEquals($criteria->toDialectString(PostgresDialect::me()), 'SELECT "test_user"."id" FROM "test_user" WHERE "test_user"."ip" <<= \'192.168.1.1-192.168.1.255\'');
$criteria = Criteria::create(TestInternetProvider::dao())->add(Expression::containsIp('range', IpAddress::create('42.42.42.42')))->addProjection(Projection::property('id'));
$this->assertEquals($criteria->toDialectString(PostgresDialect::me()), 'SELECT "test_internet_provider"."id" FROM "test_internet_provider" WHERE \'42.42.42.42\' <<= "test_internet_provider"."range"');
}
public function testInsertFromSelect()
{
$dialect = PostgresDialect::me();
$select = OSQL::select()->from('test_table2')->get('field3')->get('field_7')->andWhere(Expression::gt('field2', DBValue::create('33')));
$insert = OSQL::insert()->setSelect($select)->into('test_table')->set('field2', 2)->set('field16', 3);
$this->assertEquals($insert->toDialectString($dialect), 'INSERT INTO "test_table" ("field2", "field16") (' . 'SELECT "test_table2"."field3", "test_table2"."field_7" ' . 'FROM "test_table2" WHERE ("field2" > \'33\')' . ')');
}
/**
* @return OqlSelectTest
**/
private function assertCriteria($query, Criteria $criteria, $bindings = null)
{
$oqlQuery = OQL::select($query);
if (is_array($bindings)) {
$oqlQuery->bindAll($bindings);
}
$dialect = PostgresDialect::me();
$this->assertEquals($oqlQuery->toCriteria()->toDialectString($dialect), $criteria->toDialectString($dialect));
return $this;
}
public function testDelete()
{
$query = OSQL::delete()->from('pity_table');
$dialect = PostgresDialect::me();
try {
$query->toDialectString($dialect);
$this->fail();
} catch (WrongArgumentException $e) {
//pass
}
$query->where(Expression::eq('count', 2))->returning('id');
$this->assertEquals($query->toDialectString($dialect), 'DELETE FROM "pity_table" WHERE ("count" = \'2\') RETURNING "pity_table"."id"');
}
public function testPostgresql()
{
$dialect = PostgresDialect::me();
$this->assertEquals($this->singleUnion->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" UNION SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable"');
$this->assertEquals($this->singleUnionAll->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" UNION ALL SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable"');
$this->assertEquals($this->singleIntersect->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" INTERSECT SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable"');
$this->assertEquals($this->singleIntersectAll->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" INTERSECT ALL SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable"');
$this->assertEquals($this->singleExcept->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" EXCEPT SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable"');
$this->assertEquals($this->singleExceptAll->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" EXCEPT ALL SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable"');
$this->assertEquals($this->blockUnion->toDialectString($dialect), '(SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" UNION SELECT "middleTable"."a", "middleTable"."c" FROM "middleTable" UNION SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable")');
$this->assertEquals($this->blockUnionAll->toDialectString($dialect), '(SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" UNION ALL SELECT "middleTable"."a", "middleTable"."c" FROM "middleTable" UNION ALL SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable")');
$this->assertEquals($this->limitedOrderedUnion->toDialectString($dialect), 'SELECT "leftTable"."a", "leftTable"."b" AS "c" FROM "leftTable" UNION SELECT "rightTable"."d" AS "a", "rightTable"."c" FROM "rightTable" ORDER BY "a" LIMIT 2 OFFSET 3');
}
public function testQuery()
{
$query = OSQL::delete()->from('pity_table');
$dialect = PostgresDialect::me();
try {
$query->toDialectString($dialect);
$this->fail();
} catch (WrongArgumentException $e) {
/* pass */
}
$query->where(Expression::eq('count', 2));
$this->assertEquals($query->toDialectString($dialect), 'DELETE FROM "pity_table" WHERE ("count" = \'2\')');
$query->andWhere(Expression::notEq('a', '2'));
$this->assertEquals($query->toDialectString($dialect), 'DELETE FROM "pity_table" WHERE ("count" = \'2\') AND ("a" != \'2\')');
}
public function testQuery()
{
$query = OSQL::truncate('single_table');
try {
OSQL::truncate()->toDialectString(ImaginaryDialect::me());
$this->fail();
} catch (WrongArgumentException $e) {
/* pass */
}
$this->assertEquals($query->toDialectString(ImaginaryDialect::me()), 'DELETE FROM single_table;');
$this->assertEquals($query->toDialectString(PostgresDialect::me()), 'TRUNCATE TABLE "single_table";');
$this->assertEquals($query->toDialectString(LiteDialect::me()), 'DELETE FROM "single_table";');
$this->assertEquals($query->toDialectString(MyDialect::me()), 'TRUNCATE TABLE `single_table`;');
$query = OSQL::truncate(array('foo', 'bar', 'bleh'));
$this->assertEquals($query->toDialectString(ImaginaryDialect::me()), 'DELETE FROM foo; DELETE FROM bar; DELETE FROM bleh;');
$this->assertEquals($query->toDialectString(PostgresDialect::me()), 'TRUNCATE TABLE "foo", "bar", "bleh";');
$this->assertEquals($query->toDialectString(LiteDialect::me()), 'DELETE FROM "foo"; DELETE FROM "bar"; DELETE FROM "bleh";');
$this->assertEquals($query->toDialectString(MyDialect::me()), 'TRUNCATE TABLE `foo`; TRUNCATE TABLE `bar`; TRUNCATE TABLE `bleh`;');
}
public function testSelectSubqueryGet()
{
$dialect = PostgresDialect::me();
$query = OSQL::select()->from('test_table')->get('field1')->get(OSQL::select()->from('test_table1')->setName('foo1')->get('id'));
$this->assertEquals($query->toDialectString($dialect), 'SELECT ' . '"test_table"."field1", ' . '(SELECT "test_table1"."id" FROM "test_table1") AS "foo1" ' . 'FROM "test_table"');
}
public static function setRankFunction($rank)
{
self::$rankFunction = $rank;
}
/**
* @return PostgresDialect
**/
public static function getDialect()
{
return PostgresDialect::me();
}
/**
* @return OqlSelectClauseTest
**/
private function assertCriteria(OqlQuery $query, Criteria $criteria)
{
$dialect = PostgresDialect::me();
$this->assertEquals($query->toCriteria()->toDialectString($dialect), $criteria->toDialectString($dialect));
return $this;
}
public function testPgGeneration()
{
$dialect = PostgresDialect::me();
$this->assertRegExp('/^\\(\\(\\(\\(\'asdf\' = "b"\\) (AND|and) \\("e" != \\("i" \\/ \'123\'\\)\\) (AND|and) \\(\\(lower\\("a"\\) += +lower\\("b"\\)\\) ((IS TRUE)|(is true))\\) (AND|and) \\("g" = \'12\'\\) (AND|and) \\("j" (BETWEEN|between) \'3\' (AND|and) "p"\\)\\) (OR|or) \\("table"\\."c" ((IS NOT NULL)|(is not null))\\)\\) (AND|and) \\("sometable"\\."a" ((not in)|(NOT IN)) \\(\'q\', \'qwer\', \'xcvzxc\', \'wer\'\\)\\)\\)$/', Expression::expAnd(Expression::expOr(Expression::andBlock(Expression::eq(new DBValue('asdf'), new DBField('b')), Expression::notEq(new DBField('e'), Expression::div(new DBField('i'), new DBValue(123))), Expression::isTrue(Expression::eqLower(new DBField('a'), new DBField('b'))), Expression::eq(new DBField('g'), new DBValue(12)), Expression::between('j', new DBValue(3), new DBField('p'))), Expression::notNull(new DBField('c', 'table'))), Expression::notIn(new DBField('a', 'sometable'), array('q', 'qwer', 'xcvzxc', 'wer')))->toDialectString($dialect));
}
