database/tests/Database/Table/Selection.insert().phpt at 338fa527e5d84da60a93cd1a2715da4100d23d49 · nette/database · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
<?php

/**
 * Test: Nette\Database\Table\Selection: Insert operations
 * @dataProvider? ../databases.ini
 */

use Tester\Assert;

require __DIR__ . '/../connect.inc.php'; // create $connection

Nette\Database\Helpers::loadFromFile($connection, __DIR__ . "/../files/{$driverName}-nette_test1.sql");


$book = $context->table('author')->insert([
	'name' => $context->literal('LOWER(?)', 'Eddard Stark'),
	'web' => 'http://example.com',
	'born' => new \DateTime('2011-11-11'),
]);  // INSERT INTO `author` (`name`, `web`) VALUES (LOWER('Eddard Stark'), 'http://example.com', '2011-11-11 00:00:00')
// id = 14

Assert::equal('eddard stark', $book->name);
Assert::equal(new Nette\Utils\DateTime('2011-11-11'), $book->born);


$books = $context->table('book');

$book1 = $books->get(1);  // SELECT * FROM `book` WHERE (`id` = ?)
Assert::same('Jakub Vrana', $book1->author->name);  // SELECT * FROM `author` WHERE (`author`.`id` IN (11))

$book2 = $books->insert([
	'title' => 'Dragonstone',
	'author_id' => $context->table('author')->get(14),  // SELECT * FROM `author` WHERE (`id` = ?)
]);  // INSERT INTO `book` (`title`, `author_id`) VALUES ('Dragonstone', 14)

Assert::same('eddard stark', $book2->author->name);  // SELECT * FROM `author` WHERE (`author`.`id` IN (11, 15))


// SQL Server throw PDOException because does not allow insert explicit value for IDENTITY column.
// This exception is about primary key violation.
if ($driverName !== 'sqlsrv') {
	Assert::exception(function () use ($context) {
		$context->table('author')->insert([
			'id' => 14,
			'name' => 'Jon Snow',
			'web' => 'http://example.com',
		]);
	}, PDOException::class);
}


// SQL Server 2008 doesn't know CONCAT()
if ($driverName !== 'sqlsrv') {
	switch ($driverName) {
		case 'mysql':
			$selection = $context->table('author')->select('NULL, id, NULL, CONCAT(?, name), NULL',  'Biography: ');
			break;
		case 'pgsql':
			$selection = $context->table('author')->select('nextval(?), id, NULL, ? || name, NULL', 'book_id_seq', 'Biography: ');
			break;
		case 'sqlite':
			$selection = $context->table('author')->select('NULL, id, NULL, ? || name, NULL', 'Biography: ');
			break;
		case 'sqlsrv':
			$selection = $context->table('author')->select('id, NULL, CONCAT(?, name), NULL', 'Biography: ');
			break;
		default:
			Assert::fail("Unsupported driver $driverName");
	}
	$context->table('book')->insert($selection);
	Assert::equal(4, $context->table('book')->where('title LIKE', 'Biography%')->count('*'));
}


// Insert into table without primary key
$context = new Nette\Database\Context(
	$connection,
	$structure,
	new Nette\Database\Conventions\DiscoveredConventions($structure)
);

$inserted = $context->table('note')->insert([
	'book_id' => 1,
	'note' => 'Good one!',
]);
Assert::equal(1, $inserted);

$affected = $context->table('note')->insert([
	'book_id' => 2,
	'note' => 'Second one!',
], FALSE);
Assert::equal(1, $affected);