View source
<?php
declare (strict_types=1);
namespace Drupal\KernelTests\Core\Database;
use Drupal\Core\Database\IntegrityConstraintViolationException;
class InsertTest extends DatabaseTestBase {
public function testSimpleInsert() {
$num_records_before = $this->connection
->query('SELECT COUNT(*) FROM {test}')
->fetchField();
$query = $this->connection
->insert('test');
$query
->fields([
'name' => 'Yoko',
'age' => '29',
]);
$this
->assertCount(1, $query, 'One record is queued for insertion.');
$query
->execute();
$num_records_after = $this->connection
->query('SELECT COUNT(*) FROM {test}')
->fetchField();
$this
->assertSame($num_records_before + 1, (int) $num_records_after, 'Record inserts correctly.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Yoko',
])
->fetchField();
$this
->assertSame('29', $saved_age, 'Can retrieve after inserting.');
}
public function testMultiInsert() {
$num_records_before = (int) $this->connection
->query('SELECT COUNT(*) FROM {test}')
->fetchField();
$query = $this->connection
->insert('test');
$query
->fields([
'name' => 'Larry',
'age' => '30',
]);
$query
->values([
'age' => '31',
'name' => 'Curly',
]);
$this
->assertCount(2, $query, 'Two records are queued for insertion.');
$query
->values([
'Moe',
'32',
]);
$this
->assertCount(3, $query, 'Three records are queued for insertion.');
$query
->execute();
$num_records_after = (int) $this->connection
->query('SELECT COUNT(*) FROM {test}')
->fetchField();
$this
->assertSame($num_records_before + 3, $num_records_after, 'Record inserts correctly.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Larry',
])
->fetchField();
$this
->assertSame('30', $saved_age, 'Can retrieve after inserting.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Curly',
])
->fetchField();
$this
->assertSame('31', $saved_age, 'Can retrieve after inserting.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Moe',
])
->fetchField();
$this
->assertSame('32', $saved_age, 'Can retrieve after inserting.');
}
public function testRepeatedInsert() {
$num_records_before = $this->connection
->query('SELECT COUNT(*) FROM {test}')
->fetchField();
$query = $this->connection
->insert('test');
$query
->fields([
'name' => 'Larry',
'age' => '30',
]);
$this
->assertCount(1, $query, 'One record is queued for insertion.');
$query
->execute();
$query
->values([
'age' => '31',
'name' => 'Curly',
]);
$this
->assertCount(1, $query, 'One record is queued for insertion.');
$query
->execute();
$query
->values([
'Moe',
'32',
]);
$this
->assertCount(1, $query, 'One record is queued for insertion.');
$query
->execute();
$num_records_after = $this->connection
->query('SELECT COUNT(*) FROM {test}')
->fetchField();
$this
->assertSame((int) $num_records_before + 3, (int) $num_records_after, 'Record inserts correctly.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Larry',
])
->fetchField();
$this
->assertSame('30', $saved_age, 'Can retrieve after inserting.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Curly',
])
->fetchField();
$this
->assertSame('31', $saved_age, 'Can retrieve after inserting.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Moe',
])
->fetchField();
$this
->assertSame('32', $saved_age, 'Can retrieve after inserting.');
}
public function testInsertFieldOnlyDefinition() {
$this->connection
->insert('test')
->fields([
'name',
'age',
])
->values([
'Larry',
'30',
])
->values([
'Curly',
'31',
])
->values([
'Moe',
'32',
])
->execute();
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Larry',
])
->fetchField();
$this
->assertSame('30', $saved_age, 'Can retrieve after inserting.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Curly',
])
->fetchField();
$this
->assertSame('31', $saved_age, 'Can retrieve after inserting.');
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Moe',
])
->fetchField();
$this
->assertSame('32', $saved_age, 'Can retrieve after inserting.');
}
public function testInsertLastInsertID() {
$id = $this->connection
->insert('test')
->fields([
'name' => 'Larry',
'age' => '30',
])
->execute();
$this
->assertSame('5', $id, 'Auto-increment ID returned successfully.');
}
public function testInsertSelectFields() {
$query = $this->connection
->select('test_people', 'tp');
$query
->addExpression('[tp].[age]', 'age');
$query
->fields('tp', [
'name',
'job',
])
->condition('tp.name', 'Meredith');
$this->connection
->insert('test')
->from($query)
->execute();
$saved_age = $this->connection
->query('SELECT [age] FROM {test} WHERE [name] = :name', [
':name' => 'Meredith',
])
->fetchField();
$this
->assertSame('30', $saved_age, 'Can retrieve after inserting.');
}
public function testInsertSelectAll() {
$query = $this->connection
->select('test_people', 'tp')
->fields('tp')
->condition('tp.name', 'Meredith');
$this->connection
->insert('test_people_copy')
->from($query)
->execute();
$saved_age = $this->connection
->query('SELECT [age] FROM {test_people_copy} WHERE [name] = :name', [
':name' => 'Meredith',
])
->fetchField();
$this
->assertSame('30', $saved_age, 'Can retrieve after inserting.');
}
public function testSpecialColumnInsert() {
$this->connection
->insert('select')
->fields([
'id' => 2,
'update' => 'Update value 2',
])
->execute();
$saved_value = $this->connection
->query('SELECT [update] FROM {select} WHERE [id] = :id', [
':id' => 2,
])
->fetchField();
$this
->assertEquals('Update value 2', $saved_value);
}
public function testInsertIntegrityViolation() {
$this->connection
->schema()
->changeField('test', 'age', 'age', [
'description' => "The person's age",
'type' => 'int',
'unsigned' => TRUE,
'not null' => TRUE,
]);
$this
->expectException(IntegrityConstraintViolationException::class);
$this->connection
->insert('test')
->fields([
'name',
])
->values([
'name' => 'Elvis',
])
->execute();
}
}