public function unite(Identifiable $object, Identifiable $old) { Assert::isNotNull($object->getId()); Assert::isTypelessEqual($object->getId(), $old->getId(), 'cannot merge different objects'); $query = OSQL::update($this->getTable()); foreach ($this->getProtoClass()->getPropertyList() as $property) { $getter = $property->getGetter(); if ($property->getClassName() === null) { $changed = $old->{$getter}() !== $object->{$getter}(); } else { /** * way to skip pointless update and hack for recursive * comparsion. **/ $changed = $old->{$getter}() !== $object->{$getter}() || $old->{$getter}() != $object->{$getter}(); } if ($changed) { $property->fillQuery($query, $object); } } if (!$query->getFieldsCount()) { return $object; } $this->targetizeUpdateQuery($query, $object); return $this->doInject($query, $object); }
public static function increment(DAOConnected &$object, array $fields, $refreshCurrent = true, $query = null) { $objectDao = $object->dao(); if ($query) { $updateQuery = $query; } else { $updateQuery = OSQL::update()->setTable($objectDao->getTable())->where(Expression::eqId('id', $object)); } $mapping = $objectDao->getProtoClass()->getMapping(); foreach ($mapping as $field => $column) { if (isset($fields[$field])) { $updateQuery->set($column, Expression::add($column, $fields[$field])); } } $updateCount = DBPool::getByDao($objectDao)->queryCount($updateQuery); if ($query) { $objectDao->uncacheLists(); } else { $objectDao->uncacheById($object->getId()); } if ($refreshCurrent && !$query) { $object = $objectDao->getById($object->getId()); } return $updateCount; }
public function dropList() { $dao = $this->container->getDao(); DBPool::getByDao($dao)->queryNull(OSQL::delete()->from($this->container->getHelperTable())->where(Expression::eq($this->container->getParentIdField(), $this->container->getParentObject()->getId()))); $dao->uncacheLists(); return $this; }
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\')' . ')'); }
/** * @param bool $clean * @return DBTestCreator * @throws DatabaseException */ public function dropDB($clean = false) { foreach ($this->pool->getPool() as $name => $db) { /* @var $db DB */ foreach ($this->schema->getTableNames() as $name) { try { $db->queryRaw(OSQL::dropTable($name, true)->toDialectString($db->getDialect())); } catch (DatabaseException $e) { if (!$clean) { throw $e; } } if ($db->hasSequences()) { foreach ($this->schema->getTableByName($name)->getColumns() as $columnName => $column) { try { if ($column->isAutoincrement()) { $db->queryRaw("DROP SEQUENCE {$name}_id;"); } } catch (DatabaseException $e) { if (!$clean) { throw $e; } } } } } } return $this; }
public function unite(Identifiable $object, Identifiable $old) { $query = $this->getProtoClass()->fillQuery(OSQL::update($this->getTable()), $object, $old); if (!$query->getFieldsCount()) { return $object; } return $this->doInject($this->targetizeUpdateQuery($query, $object), $object); }
public function testUpdateQueryByUserWithSameValueObject() { //if value object same for both main objects - we'll update all fields from value object $contactExt = $this->spawnContactValueExt(); $oldUser = $this->spawnUserWithContactExt(array('contactExt' => $contactExt)); $user = $this->spawnUserWithContactExt(array('contactExt' => $contactExt)); $updateUser = $user->proto()->fillQuery(OSQL::update(), $user, $oldUser); $this->assertEquals('UPDATE SET email = foo@bar.com, icq = 12345678, ' . 'phone = 89012345678, city_id = NULL, ' . 'web = https://www.github.com/, skype = github', $updateUser->toDialectString(ImaginaryDialect::me())); }
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 testQuery() { $query = OSQL::delete()->from('pity_table'); $dialect = ImaginaryDialect::me(); try { $query->toDialectString($dialect); $this->fail(); } catch (WrongArgumentException $e) { /* pass */ } $query->where(Expression::eq(1, 2)); $this->assertEquals($query->toDialectString($dialect), 'DELETE FROM pity_table WHERE (1 = 2)'); $query->andWhere(Expression::notEq('a', 'b')); $this->assertEquals($query->toDialectString($dialect), 'DELETE FROM pity_table WHERE (1 = 2) AND (a != b)'); }
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 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 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); } }
public function drop() { $pool = DBTestPool::me()->getPool(); foreach ($pool as $name => $db) { foreach ($this->schema->getTableNames() as $name) { $db->queryRaw(OSQL::dropTable($name, true)->toDialectString($db->getDialect())); if ($db->hasSequences()) { foreach ($this->schema->getTableByName($name)->getColumns() as $columnName => $column) { if ($column->isAutoincrement()) { $db->queryRaw("DROP SEQUENCE {$name}_id;"); } } } } } return $this; }
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`;'); }
/** * @return OneToManyLinkedFull **/ public function sync($insert, $update = array(), $delete) { $uc = $this->container; $dao = $uc->getDao(); if ($delete) { DBPool::getByDao($dao)->queryNull(OSQL::delete()->from($dao->getTable())->where(Expression::eq(new DBField($uc->getParentIdField()), $uc->getParentObject()->getId()))->andWhere(Expression::in($uc->getChildIdField(), ArrayUtils::getIdsArray($delete)))); $dao->uncacheByIds(ArrayUtils::getIdsArray($delete)); } if ($insert) { for ($i = 0, $size = count($insert); $i < $size; ++$i) { $dao->add($insert[$i]); } } if ($update) { for ($i = 0, $size = count($update); $i < $size; ++$i) { $dao->save($update[$i]); } } return $this; }
public function testQuery() { $pgDialect = $this->getDbByType('PgSQL')->getDialect(); $myDialect = $this->getDbByType('MySQLim')->getDialect(); $liteDialect = $this->getDbByType('SQLitePDO')->getDialect(); $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($pgDialect), 'TRUNCATE TABLE "single_table";'); $this->assertEquals($query->toDialectString($liteDialect), 'DELETE FROM "single_table";'); $this->assertEquals($query->toDialectString($myDialect), '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($pgDialect), 'TRUNCATE TABLE "foo", "bar", "bleh";'); $this->assertEquals($query->toDialectString($liteDialect), 'DELETE FROM "foo"; DELETE FROM "bar"; DELETE FROM "bleh";'); $this->assertEquals($query->toDialectString($myDialect), 'TRUNCATE TABLE `foo`; TRUNCATE TABLE `bar`; TRUNCATE TABLE `bleh`;'); }
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; }
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; }
/** * @return UpdateQuery **/ private function makeMassUpdateQuery($ids) { $uc = $this->container; return OSQL::update($uc->getDao()->getTable())->set($uc->getParentIdField(), null)->where(Expression::in($uc->getChildIdField(), $ids)); }
public function toDialectString(Dialect $dialect) { return OSQL::createTable($this)->toDialectString($dialect); }
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; }
/** * only unlinking, we don't want to drop original object * * @return DeleteQuery **/ protected function makeDeleteQuery($delete) { $uc = $this->container; return OSQL::delete()->from($uc->getHelperTable())->where(Expression::eq(new DBField($uc->getParentIdField()), new DBValue($uc->getParentObject()->getId())))->andWhere(Expression::in($uc->getChildIdField(), $delete)); }
public function dropByIds(array $ids) { $result = DBPool::getByDao($this->dao)->queryCount(OSQL::delete()->from($this->dao->getTable())->where(Expression::in($this->dao->getIdName(), $ids))); $this->dao->uncacheByIds($ids); return $result; }
/** * @return SelectQuery **/ public function makeTotalCountQuery() { return OSQL::select()->get(SQLFunction::create('count', DBValue::create('*')))->from($this->getTable()); }
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; }
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>";