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