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\')' . ')'); }
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"'); }
/** * @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"'); }
/** * @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)); }