public function testMultipleRowInsert()
{
$query = SQLInsert::create('"SQLInsertTestBase"');
$query->addRow(array('"Title"' => 'First Object', '"Age"' => 10, '"Description"' => 'First the worst'));
$query->addRow(array('"Title"' => 'Second object', '"Age"' => 12));
$sql = $query->sql($parameters);
// Only test this case if using the default query builder
if (get_class(DB::get_conn()->getQueryBuilder()) === 'SilverStripe\\ORM\\Connect\\DBQueryBuilder') {
$this->assertSQLEquals('INSERT INTO "SQLInsertTestBase" ("Title", "Age", "Description") VALUES (?, ?, ?), (?, ?, ?)', $sql);
}
$this->assertEquals(array('First Object', 10, 'First the worst', 'Second object', 12, null), $parameters);
$query->execute();
$this->assertEquals(2, DB::affected_rows());
// Check inserted objects are correct
$firstObject = DataObject::get_one('SQLInsertTestBase', array('"Title"' => 'First Object'), false);
$this->assertNotEmpty($firstObject);
$this->assertEquals($firstObject->Title, 'First Object');
$this->assertEquals($firstObject->Age, 10);
$this->assertEquals($firstObject->Description, 'First the worst');
$secondObject = DataObject::get_one('SQLInsertTestBase', array('"Title"' => 'Second object'), false);
$this->assertNotEmpty($secondObject);
$this->assertEquals($secondObject->Title, 'Second object');
$this->assertEquals($secondObject->Age, 12);
$this->assertEmpty($secondObject->Description);
}
/**
* Ensures that a blank base record exists with the basic fixed fields for this dataobject
*
* Does nothing if an ID is already assigned for this record
*
* @param string $baseTable Base table
* @param string $now Timestamp to use for the current time
*/
protected function writeBaseRecord($baseTable, $now)
{
// Generate new ID if not specified
if ($this->isInDB()) {
return;
}
// Perform an insert on the base table
$insert = new SQLInsert('"' . $baseTable . '"');
$insert->assign('"Created"', $now)->execute();
$this->changed['ID'] = self::CHANGE_VALUE;
$this->record['ID'] = DB::get_generated_id($baseTable);
}
/**
* 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);
}
}
}
/**
* Writes the fixture into the database directly using a database manipulation.
* Does not use blueprints. Only supports tables with a primary key.
*
* @param String $table Existing database table name
* @param String $identifier Unique identifier for this fixture type
* @param Array $data Map of properties
* @return Int Database identifier
*/
public function createRaw($table, $identifier, $data)
{
$fields = array();
foreach ($data as $fieldName => $fieldVal) {
$fields["\"{$fieldName}\""] = $this->parseValue($fieldVal);
}
$insert = new SQLInsert("\"{$table}\"", $fields);
$insert->execute();
$id = DB::get_generated_id($table);
$this->fixtures[$table][$identifier] = $id;
return $id;
}
/**
* Builds a query from a SQLInsert expression
*
* @param SQLInsert $query The expression object to build from
* @param array $parameters Out parameter for the resulting query parameters
* @return string Completed SQL string
*/
protected function buildInsertQuery(SQLInsert $query, array &$parameters)
{
$nl = $this->getSeparator();
$into = $query->getInto();
// Column identifiers
$columns = $query->getColumns();
$sql = "INSERT INTO {$into}{$nl}(" . implode(', ', $columns) . ")";
// Values
$sql .= "{$nl}VALUES";
// Build all rows
$rowParts = array();
foreach ($query->getRows() as $row) {
// Build all columns in this row
$assignments = $row->getAssignments();
// Join SET components together, considering parameters
$parts = array();
foreach ($columns as $column) {
// Check if this column has a value for this row
if (isset($assignments[$column])) {
// Assigment is a single item array, expand with a loop here
foreach ($assignments[$column] as $assignmentSQL => $assignmentParameters) {
$parts[] = $assignmentSQL;
$parameters = array_merge($parameters, $assignmentParameters);
break;
}
} else {
// This row is missing a value for a column used by another row
$parts[] = '?';
$parameters[] = null;
}
}
$rowParts[] = '(' . implode(', ', $parts) . ')';
}
$sql .= $nl . implode(",{$nl}", $rowParts);
return $sql;
}
