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