public function testUpdateSupportsColumnReferencing()
{
$q = new Doctrine_Query();
$q->update('User u')->set('u.id', 'u.id + 1');
$this->assertEqual($q->getQuery(), "UPDATE entity SET id = id + 1 WHERE (type = 0)");
}
public function testTicket()
{
$q = new Doctrine_Query();
try {
// simple query with deep relations
$q->update('Email')->set('address', '?', '*****@*****.**')->where('address = ?', '*****@*****.**')->execute();
} catch (Exception $e) {
$this->fail('Query :: set do not support values containing dot. Exception: ' . $e->getMessage());
}
}
public function testTicket()
{
try {
$q = new Doctrine_Query();
$q->update('EnumUpdateBug')->set('bla_id', '?', 5)->set('separator', '?', 'pipe')->where('id = 1')->execute();
} catch (Exception $e) {
$this->fail($e->getMessage());
}
$q = new Doctrine_Query();
$row = $q->select('a.*')->from('EnumUpdateBug a')->where('a.id = 1')->fetchOne();
$this->assertEqual($row->bla_id, 5);
}
public function testDqlUpdate()
{
$query = new Doctrine_Query($this->connection);
$query->update('EnumTest2 u')->set('u.status', '?', 'verified');
$this->assertEqual($query->getSqlQuery(), 'UPDATE enum_test2 SET status = ?');
$query->execute();
try {
$query = new Doctrine_Query($this->connection);
$ret = $query->query("FROM EnumTest2 WHERE EnumTest2.status = 'verified'");
$this->assertEqual(count($ret), 1);
} catch (Exception $e) {
$this->fail();
}
}
public function linkInDb($alias, $ids)
{
$identifier = array_values($this->identifier());
$identifier = array_shift($identifier);
$rel = $this->getTable()->getRelation($alias);
if ($rel instanceof Doctrine_Relation_Association) {
$modelClassName = $rel->getAssociationTable()->getComponentName();
$localFieldName = $rel->getLocalFieldName();
$localFieldDef = $rel->getAssociationTable()->getColumnDefinition($localFieldName);
if ($localFieldDef['type'] == 'integer') {
$identifier = (int) $identifier;
}
$foreignFieldName = $rel->getForeignFieldName();
$foreignFieldDef = $rel->getAssociationTable()->getColumnDefinition($foreignFieldName);
if ($foreignFieldDef['type'] == 'integer') {
for ($i = 0, $l = count($ids); $i < $l; $i++) {
$ids[$i] = (int) $ids[$i];
}
}
foreach ($ids as $id) {
$record = new $modelClassName();
$record[$localFieldName] = $identifier;
$record[$foreignFieldName] = $id;
$record->save();
}
} else {
if ($rel instanceof Doctrine_Relation_ForeignKey) {
$q = new Doctrine_Query();
$q->update($rel->getTable()->getComponentName())->set($rel->getForeign(), '?', array_values($this->identifier()));
if (count($ids) > 0) {
$q->whereIn($rel->getTable()->getIdentifier(), $ids);
}
$q->execute();
} else {
if ($rel instanceof Doctrine_Relation_LocalKey) {
$q = new Doctrine_Query();
$q->update($this->getTable()->getComponentName())->set($rel->getLocalFieldName(), '?', $ids);
if (count($ids) > 0) {
$q->whereIn($rel->getTable()->getIdentifier(), array_values($this->identifier()));
}
$q->execute();
}
}
}
return $this;
}
public function testUpdateSupportsComplexExpressions()
{
$q = new Doctrine_Query();
$q->update('User u')->set('u.name', "CONCAT(?, CONCAT(':', SUBSTRING(u.name, LOCATE(':', u.name)+1, LENGTH(u.name) - LOCATE(':', u.name)+1)))", array('gblanco'))->where('u.id IN (SELECT u2.id FROM User u2 WHERE u2.name = ?) AND u.email_id = ?', array('guilhermeblanco', 5));
$this->assertEqual($q->getSqlQuery(), "UPDATE entity SET name = CONCAT(?, CONCAT(':', SUBSTRING(name, LOCATE(':', name)+1, LENGTH(name) - LOCATE(':', name)+1))) WHERE (id IN (SELECT e2.id AS e2__id FROM entity e2 WHERE (e2.name = ? AND (e2.type = 0))) AND email_id = ?) AND (type = 0)");
}
/**
* adds '$delta' to all Left and Right values that are >= '$first'. '$delta' can also be negative.
*
* Note: This method does wrap its database queries in a transaction. This should be done
* by the invoking code.
*
* @param int $first First node to be shifted
* @param int $delta Value to be shifted by, can be negative
*/
private function shiftRlValues($first, $delta, $rootId = 1)
{
$qLeft = new Doctrine_Query();
$qRight = new Doctrine_Query();
// shift left columns
$componentName = $this->_tree->getBaseComponent();
$qLeft = $qLeft->update($componentName)->set($componentName . '.lft', $componentName . '.lft + ?', $delta)->where($componentName . '.lft >= ?', $first);
$qLeft = $this->_tree->returnQueryWithRootId($qLeft, $rootId);
$resultLeft = $qLeft->execute();
// shift right columns
$resultRight = $qRight->update($componentName)->set($componentName . '.rgt', $componentName . '.rgt + ?', $delta)->where($componentName . '.rgt >= ?', $first);
$qRight = $this->_tree->returnQueryWithRootId($qRight, $rootId);
$resultRight = $qRight->execute();
}
/**
* inserts node as parent of dest record
*
* @return bool
* @todo Wrap in transaction
*/
public function insertAsParentOf(Doctrine_Record $dest)
{
// cannot insert a node that has already has a place within the tree
if ($this->isValidNode()) {
return false;
}
// cannot insert as parent of root
if ($dest->getNode()->isRoot()) {
return false;
}
// cannot insert as parent of itself
if ($dest === $this->record || $dest->exists() && $this->record->exists() && $dest->identifier() === $this->record->identifier()) {
throw new Doctrine_Tree_Exception("Cannot insert node as parent of itself");
return false;
}
$newLeft = $dest->getNode()->getLeftValue();
$newRight = $dest->getNode()->getRightValue() + 2;
$newRoot = $dest->getNode()->getRootValue();
$newLevel = $dest->getNode()->getLevel();
$conn = $this->record->getTable()->getConnection();
try {
$conn->beginInternalTransaction();
// Make space for new node
$this->shiftRLValues($dest->getNode()->getRightValue() + 1, 2, $newRoot);
// Slide child nodes over one and down one to allow new parent to wrap them
$componentName = $this->_tree->getBaseComponent();
$q = new Doctrine_Query();
$q->update($componentName);
$q->set("{$componentName}.lft", "{$componentName}.lft + 1");
$q->set("{$componentName}.rgt", "{$componentName}.rgt + 1");
$q->set("{$componentName}.level", "{$componentName}.level + 1");
$q->where("{$componentName}.lft >= ? AND {$componentName}.rgt <= ?", array($newLeft, $newRight));
$q = $this->_tree->returnQueryWithRootId($q, $newRoot);
$q->execute();
$this->record['level'] = $newLevel;
$this->insertNode($newLeft, $newRight, $newRoot);
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
throw $e;
}
return true;
}
public function testUpdateQuerySupportsIdentifierQuoting8()
{
$q = new Doctrine_Query();
$q->update('User u')->set('u.id', 'u.id + 1')->where('u.name = ?');
$this->assertEqual($q->getSqlQuery(), 'UPDATE "entity" SET "id" = "id" + 1 WHERE ("name" = ? AND ("type" = 0))');
$this->conn->setAttribute(Doctrine_Core::ATTR_QUOTE_IDENTIFIER, false);
}
/**
* adds '$delta' to all Left and Right values that are >= '$first'. '$delta' can also be negative.
*
* @param int $first First node to be shifted
* @param int $delta Value to be shifted by, can be negative
*/
private function shiftRlValues($first, $delta, $rootId = 1)
{
$qLeft = new Doctrine_Query();
$qRight = new Doctrine_Query();
// TODO: Wrap in transaction
// shift left columns
$componentName = $this->record->getTable()->getComponentName();
$qLeft = $qLeft->update($componentName)->set($componentName . '.lft', 'lft + ' . $delta)->where($componentName . '.lft >= ?', $first);
$qLeft = $this->record->getTable()->getTree()->returnQueryWithRootId($qLeft, $rootId);
$resultLeft = $qLeft->execute();
// shift right columns
$resultRight = $qRight->update($componentName)->set($componentName . '.rgt', 'rgt + ' . $delta)->where($componentName . '.rgt >= ?', $first);
$qRight = $this->record->getTable()->getTree()->returnQueryWithRootId($qRight, $rootId);
$resultRight = $qRight->execute();
}
/**
* unlink
* removes links from this record to given records
* if no ids are given, it removes all links
*
* @param string $alias related component alias
* @param array $ids the identifiers of the related records
* @return Doctrine_Record this object
*/
public function unlink($alias, $ids = array())
{
$ids = (array) $ids;
$q = new Doctrine_Query();
$rel = $this->getTable()->getRelation($alias);
if ($rel instanceof Doctrine_Relation_Association) {
$q->delete()->from($rel->getAssociationTable()->getComponentName())->where($rel->getLocal() . ' = ?', array_values($this->identifier()));
if (count($ids) > 0) {
$q->whereIn($rel->getForeign(), $ids);
}
$q->execute();
} else {
if ($rel instanceof Doctrine_Relation_ForeignKey) {
$q->update($rel->getTable()->getComponentName())->set($rel->getForeign(), '?', array(null))->addWhere($rel->getForeign() . ' = ?', array_values($this->identifier()));
if (count($ids) > 0) {
$q->whereIn($rel->getTable()->getIdentifier(), $ids);
}
$q->execute();
}
}
if (isset($this->_references[$alias])) {
foreach ($this->_references[$alias] as $k => $record) {
if (in_array(current($record->identifier()), $ids)) {
$this->_references[$alias]->remove($k);
}
}
$this->_references[$alias]->takeSnapshot();
}
return $this;
}
