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>";
