Inserts multiple values into a table
| public insertMulti ( string $table, array $fields, array $values ) : boolean | ||
| $table | string | The table being inserted into. |
| $fields | array | The array of field/column names being inserted. |
| $values | array | The array of values to insert. The values should be an array of rows. Each row should have values keyed by the column name. Each row must have the values in the same order as $fields. |
| return | boolean | |
/**
* Inserts multiple values into a table
*
* @param string $table
* @param string $fields
* @param array $values
* @access protected
*/
function insertMulti($table, $fields, $values)
{
$primaryKey = $this->_getPrimaryKey($table);
$hasPrimaryKey = $primaryKey != null && (is_array($fields) && in_array($primaryKey, $fields) || is_string($fields) && strpos($fields, $this->startQuote . $primaryKey . $this->endQuote) !== false);
if ($hasPrimaryKey) {
$this->_execute('SET IDENTITY_INSERT ' . $this->fullTableName($table) . ' ON');
}
parent::insertMulti($table, $fields, $values);
if ($hasPrimaryKey) {
$this->_execute('SET IDENTITY_INSERT ' . $this->fullTableName($table) . ' OFF');
}
}
public function testResetSequence()
{
$model = new Article();
$table = $this->Dbo->fullTableName($model, false);
$fields = array('id', 'user_id', 'title', 'body', 'published');
$values = array(array(1, 1, 'test', 'first post', false), array(2, 1, 'test 2', 'second post post', false));
$this->Dbo->insertMulti($table, $fields, $values);
$sequence = $this->Dbo->getSequence($table);
$result = $this->Dbo->rawQuery("SELECT nextval('{$sequence}')");
$original = $result->fetch(PDO::FETCH_ASSOC);
$this->assertTrue($this->Dbo->resetSequence($table, 'id'));
$result = $this->Dbo->rawQuery("SELECT currval('{$sequence}')");
$new = $result->fetch(PDO::FETCH_ASSOC);
$this->assertTrue($new['currval'] > $original['nextval'], 'Sequence did not update');
}
/**
* Run before each tests is executed, should return a set of SQL statements to insert records for the table
* of this fixture could be executed successfully.
*
* @param DboSource $db An instance of the database into which the records will be inserted
* @return bool on success or if there are no records to insert, or false on failure
* @throws CakeException if counts of values and fields do not match.
*/
public function insert($db)
{
if (!isset($this->_insert)) {
$values = array();
if (isset($this->records) && !empty($this->records)) {
$fields = array();
foreach ($this->records as $record) {
$fields = array_merge($fields, array_keys(array_intersect_key($record, $this->fields)));
}
$fields = array_unique($fields);
$default = array_fill_keys($fields, null);
foreach ($this->records as $record) {
$merge = array_values(array_merge($default, $record));
if (count($fields) !== count($merge)) {
throw new CakeException('Fixture invalid: Count of fields does not match count of values in ' . get_class($this));
}
$values[] = $merge;
}
$nested = $db->useNestedTransactions;
$db->useNestedTransactions = false;
$result = $db->insertMulti($this->table, $fields, $values);
if ($this->primaryKey && isset($this->fields[$this->primaryKey]['type']) && in_array($this->fields[$this->primaryKey]['type'], array('integer', 'biginteger'))) {
$db->resetSequence($this->table, $this->primaryKey);
}
$db->useNestedTransactions = $nested;
return $result;
}
return true;
}
}
/**
* testInsertMulti
*
* @return void
* @access public
*/
function testInsertMulti()
{
$fields = array('id', 'name', 'login');
$values = array('(1, \'Larry\', \'PhpNut\')', '(2, \'Renan\', \'renan.saddam\')');
$this->db->simulated = array();
$this->db->insertMulti($this->model, $fields, $values);
$result = $this->db->simulated;
$expected = array('SET IDENTITY_INSERT [mssql_test_models] ON', 'INSERT INTO [mssql_test_models] ([id], [name], [login]) VALUES (1, \'Larry\', \'PhpNut\')', 'INSERT INTO [mssql_test_models] ([id], [name], [login]) VALUES (2, \'Renan\', \'renan.saddam\')', 'SET IDENTITY_INSERT [mssql_test_models] OFF');
$this->assertEqual($result, $expected);
$fields = array('name', 'login');
$values = array('(\'Larry\', \'PhpNut\')', '(\'Renan\', \'renan.saddam\')');
$this->db->simulated = array();
$this->db->insertMulti($this->model, $fields, $values);
$result = $this->db->simulated;
$expected = array('INSERT INTO [mssql_test_models] ([name], [login]) VALUES (\'Larry\', \'PhpNut\')', 'INSERT INTO [mssql_test_models] ([name], [login]) VALUES (\'Renan\', \'renan.saddam\')');
$this->assertEqual($result, $expected);
}
/**
* Run before each tests is executed, should return a set of SQL statements to insert records for the table
* of this fixture could be executed successfully.
*
* @param DboSource $db An instance of the database into which the records will be inserted
* @return boolean on success or if there are no records to insert, or false on failure
*/
public function insert($db)
{
if (!isset($this->_insert)) {
$values = array();
if (isset($this->records) && !empty($this->records)) {
$fields = array();
foreach ($this->records as $record) {
$fields = array_merge($fields, array_keys(array_intersect_key($record, $this->fields)));
}
$fields = array_unique($fields);
$default = array_fill_keys($fields, null);
foreach ($this->records as $record) {
$fields = array_keys($record);
$values[] = array_values(array_merge($default, $record));
}
$nested = $db->useNestedTransactions;
$db->useNestedTransactions = false;
$result = $db->insertMulti($this->table, $fields, $values);
if ($this->primaryKey && in_array($this->fields[$this->primaryKey]['type'], array('integer', 'biginteger'))) {
$db->resetSequence($this->table, $this->primaryKey);
}
$db->useNestedTransactions = $nested;
return $result;
}
return true;
}
}
/**
* testInsertMulti
*
* @return void
*/
public function testInsertMulti()
{
$this->db->describe = $this->model->schema();
$fields = array('id', 'name', 'login');
$values = array(array(1, 'Larry', 'PhpNut'), array(2, 'Renan', 'renan.saddam'));
$this->db->simulated = array();
$this->db->insertMulti($this->model, $fields, $values);
$result = $this->db->simulated;
$expected = array('SET IDENTITY_INSERT [sqlserver_test_models] ON', "INSERT INTO [sqlserver_test_models] ([id], [name], [login]) VALUES (1, N'Larry', N'PhpNut')", "INSERT INTO [sqlserver_test_models] ([id], [name], [login]) VALUES (2, N'Renan', N'renan.saddam')", 'SET IDENTITY_INSERT [sqlserver_test_models] OFF');
$this->assertEqual($expected, $result);
$fields = array('name', 'login');
$values = array(array('Larry', 'PhpNut'), array('Renan', 'renan.saddam'));
$this->db->simulated = array();
$this->db->insertMulti($this->model, $fields, $values);
$result = $this->db->simulated;
$expected = array("INSERT INTO [sqlserver_test_models] ([name], [login]) VALUES (N'Larry', N'PhpNut')", "INSERT INTO [sqlserver_test_models] ([name], [login]) VALUES (N'Renan', N'renan.saddam')");
$this->assertEqual($expected, $result);
}
/**
* Run before each tests is executed, should return a set of SQL statements to insert records for the table
* of this fixture could be executed successfully.
*
* @param DboSource $db An instance of the database into which the records will be inserted
*
* @return bool on success or if there are no records to insert, or false on failure
* @throws CakeException if counts of values and fields do not match.
*/
public function insert($db)
{
if (!isset($this->_insert)) {
$values = array();
if (isset($this->records) && !empty($this->records)) {
$fields = array();
foreach ($this->records as $record) {
$fields = array_merge($fields, array_keys(array_intersect_key($record, $this->fields)));
}
$fields = array_unique($fields);
$default = array_fill_keys($fields, NULL);
foreach ($this->records as $record) {
$mergeData = array_merge($default, $record);
$merge = array_values($mergeData);
if (count($fields) !== count($merge)) {
$mergeFields = array_diff_key(array_keys($mergeData), $fields);
$message = 'Fixture invalid: Count of fields does not match count of values in ' . get_class($this) . "\n";
foreach ($mergeFields as $field) {
$message .= "The field '" . $field . "' is in the data fixture but not in the schema." . "\n";
}
throw new CakeException($message);
}
$values[] = $merge;
}
$nested = $db->useNestedTransactions;
$db->useNestedTransactions = FALSE;
$result = $db->insertMulti($this->table, $fields, $values);
if ($this->primaryKey && isset($this->fields[$this->primaryKey]['type']) && in_array($this->fields[$this->primaryKey]['type'], array('integer', 'biginteger'))) {
$db->resetSequence($this->table, $this->primaryKey);
}
$db->useNestedTransactions = $nested;
return $result;
}
return TRUE;
}
}
