public function testBasicUpdate()
{
$query = SQLUpdate::create()->setTable('"SQLUpdateTestBase"')->assign('"Description"', 'Description 1a')->addWhere(array('"Title" = ?' => 'Object 1'));
$sql = $query->sql($parameters);
// Check SQL
$this->assertSQLEquals('UPDATE "SQLUpdateTestBase" SET "Description" = ? WHERE ("Title" = ?)', $sql);
$this->assertEquals(array('Description 1a', 'Object 1'), $parameters);
// Check affected rows
$query->execute();
$this->assertEquals(1, DB::affected_rows());
// Check item updated
$item = DataObject::get_one('SQLUpdateTestBase', array('"Title"' => 'Object 1'));
$this->assertEquals('Description 1a', $item->Description);
}
public function testAffectedRows()
{
if (!DB::get_connector() instanceof PDOConnector) {
$this->markTestSkipped('This test requires the current DB connector is PDO');
}
$query = new SQLUpdate('MySQLDatabaseTest_Data');
$query->setAssignments(array('Title' => 'New Title'));
// Test update which affects no rows
$query->setWhere(array('Title' => 'Bob'));
$result = $query->execute();
$this->assertInstanceOf('SilverStripe\\ORM\\Connect\\PDOQuery', $result);
$this->assertEquals(0, DB::affected_rows());
// Test update which affects some rows
$query->setWhere(array('Title' => 'First Item'));
$result = $query->execute();
$this->assertInstanceOf('SilverStripe\\ORM\\Connect\\PDOQuery', $result);
$this->assertEquals(1, DB::affected_rows());
}
/**
* Execute a complex manipulation on the database.
* A manipulation is an array of insert / or update sequences. The keys of the array are table names,
* and the values are map containing 'command' and 'fields'. Command should be 'insert' or 'update',
* and fields should be a map of field names to field values, NOT including quotes.
*
* The field values could also be in paramaterised format, such as
* array('MAX(?,?)' => array(42, 69)), allowing the use of raw SQL values such as
* array('NOW()' => array()).
*
* @see SQLWriteExpression::addAssignments for syntax examples
*
* @param array $manipulation
*/
public function manipulate($manipulation)
{
if (empty($manipulation)) {
return;
}
foreach ($manipulation as $table => $writeInfo) {
if (empty($writeInfo['fields'])) {
continue;
}
// Note: keys of $fieldValues are not escaped
$fieldValues = $writeInfo['fields'];
// Switch command type
switch ($writeInfo['command']) {
case "update":
// Build update
$query = new SQLUpdate("\"{$table}\"", $this->escapeColumnKeys($fieldValues));
// Set best condition to use
if (!empty($writeInfo['where'])) {
$query->addWhere($writeInfo['where']);
} elseif (!empty($writeInfo['id'])) {
$query->addWhere(array('"ID"' => $writeInfo['id']));
}
// Test to see if this update query shouldn't, in fact, be an insert
if ($query->toSelect()->count()) {
$query->execute();
break;
}
// ...if not, we'll skip on to the insert code
// ...if not, we'll skip on to the insert code
case "insert":
// Ensure that the ID clause is given if possible
if (!isset($fieldValues['ID']) && isset($writeInfo['id'])) {
$fieldValues['ID'] = $writeInfo['id'];
}
// Build insert
$query = new SQLInsert("\"{$table}\"", $this->escapeColumnKeys($fieldValues));
$query->execute();
break;
default:
user_error("SS_Database::manipulate() Can't recognise command '{$writeInfo['command']}'", E_USER_ERROR);
}
}
}
/**
* Return the UPDATE clause ready for inserting into a query.
*
* @param SQLExpression $query The expression object to build from
* @param array $parameters Out parameter for the resulting query parameters
* @return string Completed from part of statement
*/
public function buildUpdateFragment(SQLUpdate $query, array &$parameters)
{
$table = $query->getTable();
$text = "UPDATE {$table}";
// Join SET components together, considering parameters
$parts = array();
foreach ($query->getAssignments() as $column => $assignment) {
// Assigment is a single item array, expand with a loop here
foreach ($assignment as $assignmentSQL => $assignmentParameters) {
$parts[] = "{$column} = {$assignmentSQL}";
$parameters = array_merge($parameters, $assignmentParameters);
break;
}
}
$nl = $this->getSeparator();
$text .= "{$nl}SET " . implode(', ', $parts);
return $text;
}
/**
* Set foreign keys of has_many objects to 0 where those objects were
* disowned as a result of a partial publish / unpublish.
* I.e. this object and its owned objects were recently written to $targetStage,
* but deleted objects were not.
*
* Note that this operation does not create any new Versions
*
* @param string $sourceStage Objects in this stage will not be unlinked.
* @param string $targetStage Objects which exist in this stage but not $sourceStage
* will be unlinked.
*/
public function unlinkDisownedObjects($sourceStage, $targetStage)
{
$owner = $this->owner;
// after publishing, objects which used to be owned need to be
// dis-connected from this object (set ForeignKeyID = 0)
$owns = $owner->config()->owns;
$hasMany = $owner->config()->has_many;
if (empty($owns) || empty($hasMany)) {
return;
}
$ownedHasMany = array_intersect($owns, array_keys($hasMany));
foreach ($ownedHasMany as $relationship) {
// Find metadata on relationship
$joinClass = $owner->hasManyComponent($relationship);
$joinField = $owner->getRemoteJoinField($relationship, 'has_many', $polymorphic);
$idField = $polymorphic ? "{$joinField}ID" : $joinField;
$joinTable = DataObject::getSchema()->tableForField($joinClass, $idField);
// Generate update query which will unlink disowned objects
$targetTable = $this->stageTable($joinTable, $targetStage);
$disowned = new SQLUpdate("\"{$targetTable}\"");
$disowned->assign("\"{$idField}\"", 0);
$disowned->addWhere(array("\"{$targetTable}\".\"{$idField}\"" => $owner->ID));
// Build exclusion list (items to owned objects we need to keep)
$sourceTable = $this->stageTable($joinTable, $sourceStage);
$owned = new SQLSelect("\"{$sourceTable}\".\"ID\"", "\"{$sourceTable}\"");
$owned->addWhere(array("\"{$sourceTable}\".\"{$idField}\"" => $owner->ID));
// Apply class condition if querying on polymorphic has_one
if ($polymorphic) {
$disowned->assign("\"{$joinField}Class\"", null);
$disowned->addWhere(array("\"{$targetTable}\".\"{$joinField}Class\"" => get_class($owner)));
$owned->addWhere(array("\"{$sourceTable}\".\"{$joinField}Class\"" => get_class($owner)));
}
// Merge queries and perform unlink
$ownedSQL = $owned->sql($ownedParams);
$disowned->addWhere(array("\"{$targetTable}\".\"ID\" NOT IN ({$ownedSQL})" => $ownedParams));
$owner->extend('updateDisownershipQuery', $disowned, $sourceStage, $targetStage, $relationship);
$disowned->execute();
}
}
