public function testInsertFromSelect() { $dialect = $this->getDbByType('PgSQL')->getDialect(); $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 toSelectQuery() { if (!$this->range || !$this->interval) { throw new WrongStateException('define time range and interval units first'); } if (!$this->range->getStart() || !$this->range->getEnd()) { throw new WrongArgumentException('cannot operate with unlimited range'); } $firstIntervalStart = $this->interval->truncate($this->range->getStart(), !$this->overlapped); $maxIntervals = $this->interval->countInRange($this->range, $this->overlapped) - 1; $generator = $this->getSeriesGenerator(0, $maxIntervals); $result = OSQL::select()->from($generator, self::ITERATOR_ALIAS)->get(Expression::add(DBValue::create($firstIntervalStart->toString())->castTo(DataType::create(DataType::TIMESTAMP)->getName()), Expression::mul(DBValue::create("1 {$this->interval->getName()}")->castTo(DataType::create(DataType::INTERVAL)->getName()), DBField::create(self::ITERATOR_ALIAS))), $this->field); return $result; }
public function setUp() { $left = OSQL::select()->from('leftTable')->get('a')->get('b', 'c'); $middle = OSQL::select()->from('middleTable')->get('a')->get('c'); $right = OSQL::select()->from('rightTable')->get('d', 'a')->get('c'); $this->singleUnion = CombineQuery::union($left, $right); $this->singleUnionAll = CombineQuery::unionAll($left, $right); $this->singleIntersect = CombineQuery::intersect($left, $right); $this->singleIntersectAll = CombineQuery::intersectAll($left, $right); $this->singleExcept = CombineQuery::except($left, $right); $this->singleExceptAll = CombineQuery::exceptAll($left, $right); $this->blockUnion = CombineQuery::unionBlock($left, $middle, $right); $this->blockUnionAll = CombineQuery::unionAllBlock($left, $middle, $right); $this->limitedOrderedUnion = CombineQuery::union($left, $right)->orderBy('a')->limit(2, 3); }
public function testIpAddressProperty() { foreach (DBTestPool::me()->getPool() as $db) { DBPool::me()->setDefault($db); $city = TestCity::create()->setName('Khimki'); TestCity::dao()->add($city); $userWithIp = TestUser::create()->setCredentials(Credentials::create()->setNickName('postgreser')->setPassword(sha1('postgreser')))->setLastLogin(Timestamp::makeNow())->setRegistered(Timestamp::makeNow())->setCity($city)->setIp(IpAddress::create('127.0.0.1')); TestUser::dao()->add($userWithIp); $this->assertTrue($userWithIp->getId() >= 1); $this->assertTrue($userWithIp->getIp() instanceof IpAddress); $plainIp = DBPool::me()->getByDao(TestUser::dao())->queryColumn(OSQL::select()->get('ip')->from(TestUser::dao()->getTable())->where(Expression::eq('id', $userWithIp->getId()))); $this->assertEquals($plainIp[0], $userWithIp->getIp()->toString()); $count = Criteria::create(TestUser::dao())->add(Expression::eq('ip', IpAddress::create('127.0.0.1')))->addProjection(Projection::count('*', 'count'))->getCustom('count'); $this->assertEquals($count, 1); } }
private function getBaseJoinSelect() { return OSQL::select()->from('table1')->get(DBField::create('field1', 'table1'))->get(DBField::create('field2', 'table2')); }
private function checkEnumerationReferentialIntegrity(Enumeration $enumeration, $tableName) { $updateQueries = null; $db = DBPool::me()->getLink(); $class = get_class($enumeration); $ids = array(); $list = $enumeration->getObjectList(); foreach ($list as $enumerationObject) { $ids[$enumerationObject->getId()] = $enumerationObject->getName(); } $rows = $db->querySet(OSQL::select()->from($tableName)->multiGet('id', 'name')); echo "\n"; foreach ($rows as $row) { if (!isset($ids[$row['id']])) { echo "Class '{$class}', strange id: {$row['id']} found. \n"; } else { if ($ids[$row['id']] != $row['name']) { echo "Class '{$class}',id: {$row['id']} sync names. \n"; $updateQueries .= OSQL::update($tableName)->set('name', $ids[$row['id']])->where(Expression::eq('id', $row['id']))->toDialectString($db->getDialect()) . ";\n"; } unset($ids[$row['id']]); } } foreach ($ids as $id => $name) { echo "Class '{$class}', id: {$id} not present in database. \n"; } echo $updateQueries; return $this; }
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 InsertOrUpdateQuery **/ protected function addReturning(InsertOrUpdateQuery $query) { $query->returning(DBField::create('field1', 'test_table'), 'alias1')->returning('field2')->returning(SQLFunction::create('count', DBField::create('field5', 'test_table'))->setAlias('alias5'))->returning(OSQL::select()->from('test_table1')->setName('foo1')->get('id')); return $query; }
private function checkEnumerationReferentialIntegrity($enumeration, $tableName) { Assert::isTrue($enumeration instanceof Enumeration || $enumeration instanceof Enum, 'argument enumeation must be instacne of Enumeration or Enum! gived, "' . gettype($enumeration) . '"'); $updateQueries = null; $db = DBPool::me()->getLink(); $class = get_class($enumeration); $ids = array(); if ($enumeration instanceof Enumeration) { $list = $enumeration->getList(); } elseif ($enumeration instanceof Enum) { $list = ClassUtils::callStaticMethod($class . '::getList'); } foreach ($list as $enumerationObject) { $ids[$enumerationObject->getId()] = $enumerationObject->getName(); } $rows = $db->querySet(OSQL::select()->from($tableName)->multiGet('id', 'name')); echo "\n"; foreach ($rows as $row) { if (!isset($ids[$row['id']])) { echo "Class '{$class}', strange id: {$row['id']} found. \n"; } else { if ($ids[$row['id']] != $row['name']) { echo "Class '{$class}',id: {$row['id']} sync names. \n"; $updateQueries .= OSQL::update($tableName)->set('name', $ids[$row['id']])->where(Expression::eq('id', $row['id']))->toDialectString($db->getDialect()) . ";\n"; } unset($ids[$row['id']]); } } foreach ($ids as $id => $name) { echo "Class '{$class}', id: {$id} not present in database. \n"; } echo $updateQueries; return $this; }
/** * @return SelectQuery **/ public function makeTotalCountQuery() { return OSQL::select()->get(SQLFunction::create('count', DBValue::create('*')))->from($this->getTable()); }
echo "</pre>"; // example Chain $queryUnion2 = CombineQuery::chain(); $queryUnion2->union($query1); $queryUnion2->intersect($query2); $queryUnion2->except($query3); echo "<pre>"; print_r($queryUnion2->toDialectString($db->getDialect())); echo "</pre>"; // example Block $queryUnion3 = CombineQuery::exceptBlock($query1, $query2, $query3); echo "<pre>"; print_r($queryUnion3->toDialectString($db->getDialect())); echo "</pre>"; // example composite $queryUnion4 = CombineQuery::union($query1, $queryUnion3); echo "<pre>"; print_r($queryUnion4->toDialectString($db->getDialect())); echo "</pre>"; $query5 = OSQL::select()->get(new DBField('id', 'foo'))->from($queryUnion3, "foo"); echo "<pre>"; print_r($query5->toDialectString($db->getDialect())); echo "</pre>"; $query6 = OSQL::select()->get(new DBField('id', 'messages'))->from('messages')->where(Expression::in('id', $queryUnion3)); echo "<pre>"; print_r($query6->toDialectString($db->getDialect())); echo "</pre>"; $query7 = OSQL::select()->get(new DBField('id', 'messages'))->from(CombineQuery::union($query1, $query2), 'foo'); echo "<pre>"; print_r($query7->toDialectString($db->getDialect())); echo "</pre>";
public function fetchCollections(array $collections, array $list) { Assert::isNotEmptyArray($list); $ids = ArrayUtils::getIdsArray($list); $mainId = DBField::create($this->getIdName(), $this->getTable()); foreach ($collections as $path => $info) { $lazy = $info['lazy']; $query = OSQL::select()->get($mainId)->from($this->getTable()); $proto = reset($list)->proto(); $this->processPath($proto, $path, $query, $this->getTable()); if ($criteria = $info['criteria']) { $query = $criteria->setDao($this)->fillSelectQuery($query); } $query->andWhere(Expression::in($mainId, $ids)); $propertyPath = $info['propertyPath']; $property = $propertyPath->getFinalProperty(); $proto = $propertyPath->getFinalProto(); $dao = $propertyPath->getFinalDao(); $selfName = $this->getObjectName(); $self = new $selfName(); $getter = 'get' . ucfirst($property->getName()); Assert::isTrue($property->getRelationId() == MetaRelation::ONE_TO_MANY || $property->getRelationId() == MetaRelation::MANY_TO_MANY); $table = $dao->getJoinName($property->getColumnName()); $id = $this->getIdName(); $collection = array(); if ($lazy) { if ($property->getRelationId() == MetaRelation::MANY_TO_MANY) { $childId = $self->{$getter}()->getChildIdField(); } else { $childId = $dao->getIdName(); } $alias = 'cid'; // childId, collectionId, whatever $field = DBField::create($childId, $self->{$getter}()->getHelperTable()); $query->get($field, $alias); if (!$property->isRequired()) { $query->andWhere(Expression::notNull($field)); } try { $rows = $dao->getCustomList($query); foreach ($rows as $row) { if (!empty($row[$alias])) { $collection[$row[$id]][] = $row[$alias]; } } } catch (ObjectNotFoundException $e) { /*_*/ } } else { $prefix = $table . '_'; foreach ($dao->getFields() as $field) { $query->get(DBField::create($field, $table), $prefix . $field); } if (!$property->isRequired()) { $query->andWhere(Expression::notNull(DBField::create($dao->getIdName(), $table))); } try { // otherwise we don't know which object // belongs to which collection $rows = $dao->getCustomList($query); foreach ($rows as $row) { $collection[$row[$id]][] = $dao->makeObject($row, $prefix); } } catch (ObjectNotFoundException $e) { /*_*/ } } $suffix = ucfirst($property->getName()); $fillMethod = 'fill' . $suffix; $getMethod = 'get' . $suffix; Assert::isTrue(method_exists(reset($list), $fillMethod), 'can not find filler'); Assert::isTrue(method_exists(reset($list), $getMethod), 'can not find getter'); foreach ($list as $object) { if (!empty($collection[$object->getId()])) { $object->{$fillMethod}($collection[$object->getId()], $lazy); } else { $object->{$getMethod}()->mergeList(array()); } } } return $list; }