WriteTest.php 12.2 KB
Newer Older
1 2
<?php

3
namespace Doctrine\DBAL\Tests\Functional;
4

Sergei Morozov's avatar
Sergei Morozov committed
5
use DateTime;
6
use Doctrine\DBAL\Driver\Exception;
7
use Doctrine\DBAL\ParameterType;
Sergei Morozov's avatar
Sergei Morozov committed
8 9
use Doctrine\DBAL\Schema\Sequence;
use Doctrine\DBAL\Schema\Table;
10
use Doctrine\DBAL\Statement;
11
use Doctrine\DBAL\Tests\FunctionalTestCase;
12
use Doctrine\DBAL\Types\Type;
Sergei Morozov's avatar
Sergei Morozov committed
13
use Throwable;
14

15 16
use function array_filter;
use function strtolower;
17

18
class WriteTest extends FunctionalTestCase
19
{
20
    protected function setUp(): void
21 22 23 24
    {
        parent::setUp();

        try {
Sergei Morozov's avatar
Sergei Morozov committed
25 26
            $table = new Table('write_table');
            $table->addColumn('id', 'integer', ['autoincrement' => true]);
27
            $table->addColumn('test_int', 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
28 29
            $table->addColumn('test_string', 'string', ['notnull' => false]);
            $table->setPrimaryKey(['id']);
30

Sergei Morozov's avatar
Sergei Morozov committed
31
            $this->connection->getSchemaManager()->createTable($table);
Sergei Morozov's avatar
Sergei Morozov committed
32
        } catch (Throwable $e) {
33
        }
Grégoire Paris's avatar
Grégoire Paris committed
34

Sergei Morozov's avatar
Sergei Morozov committed
35
        $this->connection->executeUpdate('DELETE FROM write_table');
36 37
    }

38 39 40
    /**
     * @group DBAL-80
     */
41
    public function testExecuteUpdateFirstTypeIsNull(): void
42
    {
Sergei Morozov's avatar
Sergei Morozov committed
43
        $sql = 'INSERT INTO write_table (test_string, test_int) VALUES (?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
44
        $this->connection->executeUpdate($sql, ['text', 1111], [null, ParameterType::INTEGER]);
45

Sergei Morozov's avatar
Sergei Morozov committed
46
        $sql = 'SELECT * FROM write_table WHERE test_string = ? AND test_int = ?';
47
        self::assertTrue((bool) $this->connection->fetchFirstColumn($sql, ['text', 1111]));
48 49
    }

50
    public function testExecuteUpdate(): void
51
    {
Sergei Morozov's avatar
Sergei Morozov committed
52 53
        $sql      = 'INSERT INTO write_table (test_int) VALUES ( ' . $this->connection->quote(1) . ')';
        $affected = $this->connection->executeUpdate($sql);
54

Sergei Morozov's avatar
Sergei Morozov committed
55
        self::assertEquals(1, $affected, 'executeUpdate() should return the number of affected rows!');
56 57
    }

58
    public function testExecuteUpdateWithTypes(): void
59
    {
Sergei Morozov's avatar
Sergei Morozov committed
60
        $sql      = 'INSERT INTO write_table (test_int, test_string) VALUES (?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
61
        $affected = $this->connection->executeUpdate(
62
            $sql,
Sergei Morozov's avatar
Sergei Morozov committed
63 64
            [1, 'foo'],
            [ParameterType::INTEGER, ParameterType::STRING]
65
        );
66

Sergei Morozov's avatar
Sergei Morozov committed
67
        self::assertEquals(1, $affected, 'executeUpdate() should return the number of affected rows!');
68 69
    }

70
    public function testPrepareRowCountReturnsAffectedRows(): void
71
    {
Sergei Morozov's avatar
Sergei Morozov committed
72
        $sql  = 'INSERT INTO write_table (test_int, test_string) VALUES (?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
73
        $stmt = $this->connection->prepare($sql);
74 75

        $stmt->bindValue(1, 1);
Sergei Morozov's avatar
Sergei Morozov committed
76
        $stmt->bindValue(2, 'foo');
77

78
        self::assertEquals(1, $stmt->execute()->rowCount());
79 80
    }

81
    public function testPrepareWithPrimitiveTypes(): void
82
    {
Sergei Morozov's avatar
Sergei Morozov committed
83
        $sql  = 'INSERT INTO write_table (test_int, test_string) VALUES (?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
84
        $stmt = $this->connection->prepare($sql);
85

86
        $stmt->bindValue(1, 1, ParameterType::INTEGER);
Sergei Morozov's avatar
Sergei Morozov committed
87
        $stmt->bindValue(2, 'foo', ParameterType::STRING);
88

89
        self::assertEquals(1, $stmt->execute()->rowCount());
90 91
    }

92
    public function testPrepareWithDoctrineMappingTypes(): void
93
    {
Sergei Morozov's avatar
Sergei Morozov committed
94
        $sql  = 'INSERT INTO write_table (test_int, test_string) VALUES (?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
95
        $stmt = $this->connection->prepare($sql);
96

97
        self::assertInstanceOf(Statement::class, $stmt);
98
        $stmt->bindValue(1, 1, Type::getType('integer'));
Sergei Morozov's avatar
Sergei Morozov committed
99
        $stmt->bindValue(2, 'foo', Type::getType('string'));
100

101
        self::assertEquals(1, $stmt->execute()->rowCount());
102 103
    }

104
    public function testPrepareWithDoctrineMappingTypeNames(): void
105
    {
Sergei Morozov's avatar
Sergei Morozov committed
106
        $sql  = 'INSERT INTO write_table (test_int, test_string) VALUES (?, ?)';
Sergei Morozov's avatar
Sergei Morozov committed
107
        $stmt = $this->connection->prepare($sql);
108

109
        self::assertInstanceOf(Statement::class, $stmt);
110
        $stmt->bindValue(1, 1, 'integer');
Sergei Morozov's avatar
Sergei Morozov committed
111
        $stmt->bindValue(2, 'foo', 'string');
112

113
        self::assertEquals(1, $stmt->execute()->rowCount());
114 115
    }

116
    public function insertRows(): void
117
    {
Sergei Morozov's avatar
Sergei Morozov committed
118 119
        self::assertEquals(1, $this->connection->insert('write_table', ['test_int' => 1, 'test_string' => 'foo']));
        self::assertEquals(1, $this->connection->insert('write_table', ['test_int' => 2, 'test_string' => 'bar']));
120 121
    }

122
    public function testInsert(): void
123 124 125 126
    {
        $this->insertRows();
    }

127
    public function testDelete(): void
128 129 130
    {
        $this->insertRows();

Sergei Morozov's avatar
Sergei Morozov committed
131
        self::assertEquals(1, $this->connection->delete('write_table', ['test_int' => 2]));
132
        self::assertCount(1, $this->connection->fetchAllAssociative('SELECT * FROM write_table'));
133

Sergei Morozov's avatar
Sergei Morozov committed
134
        self::assertEquals(1, $this->connection->delete('write_table', ['test_int' => 1]));
135
        self::assertCount(0, $this->connection->fetchAllAssociative('SELECT * FROM write_table'));
136 137
    }

138
    public function testUpdate(): void
139 140 141
    {
        $this->insertRows();

Sergei Morozov's avatar
Sergei Morozov committed
142 143 144
        self::assertEquals(1, $this->connection->update('write_table', ['test_string' => 'bar'], ['test_string' => 'foo']));
        self::assertEquals(2, $this->connection->update('write_table', ['test_string' => 'baz'], ['test_string' => 'bar']));
        self::assertEquals(0, $this->connection->update('write_table', ['test_string' => 'baz'], ['test_string' => 'bar']));
145
    }
146

147
    public function testLastInsertId(): void
148
    {
Sergei Morozov's avatar
Sergei Morozov committed
149
        if (! $this->connection->getDatabasePlatform()->prefersIdentityColumns()) {
150
            self::markTestSkipped('Test only works on platforms with identity columns.');
151 152
        }

Sergei Morozov's avatar
Sergei Morozov committed
153
        self::assertEquals(1, $this->connection->insert('write_table', ['test_int' => 2, 'test_string' => 'bar']));
154
        $num = $this->lastInsertId();
155

Sergei Morozov's avatar
Sergei Morozov committed
156 157
        self::assertNotNull($num, 'LastInsertId() should not be null.');
        self::assertGreaterThan(0, $num, 'LastInsertId() should be non-negative number.');
158
    }
159

160
    public function testLastInsertIdSequence(): void
161
    {
Sergei Morozov's avatar
Sergei Morozov committed
162
        if (! $this->connection->getDatabasePlatform()->supportsSequences()) {
163
            self::markTestSkipped('Test only works on platforms with sequences.');
164 165
        }

Sergei Morozov's avatar
Sergei Morozov committed
166
        $sequence = new Sequence('write_table_id_seq');
167
        try {
Sergei Morozov's avatar
Sergei Morozov committed
168
            $this->connection->getSchemaManager()->createSequence($sequence);
Sergei Morozov's avatar
Sergei Morozov committed
169
        } catch (Throwable $e) {
170 171
        }

Sergei Morozov's avatar
Sergei Morozov committed
172
        $sequences = $this->connection->getSchemaManager()->listSequences();
173
        self::assertCount(1, array_filter($sequences, static function ($sequence): bool {
174
            return strtolower($sequence->getName()) === 'write_table_id_seq';
Gabriel Caruso's avatar
Gabriel Caruso committed
175
        }));
176

177 178
        $result          = $this->connection->query($this->connection->getDatabasePlatform()->getSequenceNextValSQL('write_table_id_seq'));
        $nextSequenceVal = $result->fetchOne();
179

180
        $lastInsertId = $this->lastInsertId('write_table_id_seq');
181

Gabriel Caruso's avatar
Gabriel Caruso committed
182
        self::assertGreaterThan(0, $lastInsertId);
183
        self::assertEquals($nextSequenceVal, $lastInsertId);
184 185
    }

186
    public function testLastInsertIdNoSequenceGiven(): void
187
    {
Sergei Morozov's avatar
Sergei Morozov committed
188
        if (! $this->connection->getDatabasePlatform()->supportsSequences() || $this->connection->getDatabasePlatform()->supportsIdentityColumns()) {
189
            self::markTestSkipped("Test only works consistently on platforms that support sequences and don't support identity columns.");
190 191
        }

192
        self::assertFalse($this->lastInsertId());
193
    }
194 195 196 197

    /**
     * @group DBAL-445
     */
198
    public function testInsertWithKeyValueTypes(): void
199
    {
Sergei Morozov's avatar
Sergei Morozov committed
200
        $testString = new DateTime('2013-04-14 10:10:10');
201

Sergei Morozov's avatar
Sergei Morozov committed
202
        $this->connection->insert(
203
            'write_table',
Sergei Morozov's avatar
Sergei Morozov committed
204 205
            ['test_int' => '30', 'test_string' => $testString],
            ['test_string' => 'datetime', 'test_int' => 'integer']
206 207
        );

208
        $data = $this->connection->fetchOne('SELECT test_string FROM write_table WHERE test_int = 30');
209

Sergei Morozov's avatar
Sergei Morozov committed
210
        self::assertEquals($testString->format($this->connection->getDatabasePlatform()->getDateTimeFormatString()), $data);
211 212 213 214 215
    }

    /**
     * @group DBAL-445
     */
216
    public function testUpdateWithKeyValueTypes(): void
217
    {
Sergei Morozov's avatar
Sergei Morozov committed
218
        $testString = new DateTime('2013-04-14 10:10:10');
219

Sergei Morozov's avatar
Sergei Morozov committed
220
        $this->connection->insert(
221
            'write_table',
Sergei Morozov's avatar
Sergei Morozov committed
222 223
            ['test_int' => '30', 'test_string' => $testString],
            ['test_string' => 'datetime', 'test_int' => 'integer']
224 225
        );

Sergei Morozov's avatar
Sergei Morozov committed
226
        $testString = new DateTime('2013-04-15 10:10:10');
227

Sergei Morozov's avatar
Sergei Morozov committed
228
        $this->connection->update(
229
            'write_table',
Sergei Morozov's avatar
Sergei Morozov committed
230 231 232
            ['test_string' => $testString],
            ['test_int' => '30'],
            ['test_string' => 'datetime', 'test_int' => 'integer']
233 234
        );

235
        $data = $this->connection->fetchOne('SELECT test_string FROM write_table WHERE test_int = 30');
236

Sergei Morozov's avatar
Sergei Morozov committed
237
        self::assertEquals($testString->format($this->connection->getDatabasePlatform()->getDateTimeFormatString()), $data);
238 239 240 241 242
    }

    /**
     * @group DBAL-445
     */
243
    public function testDeleteWithKeyValueTypes(): void
244
    {
Sergei Morozov's avatar
Sergei Morozov committed
245
        $val = new DateTime('2013-04-14 10:10:10');
Sergei Morozov's avatar
Sergei Morozov committed
246
        $this->connection->insert(
247
            'write_table',
Sergei Morozov's avatar
Sergei Morozov committed
248 249
            ['test_int' => '30', 'test_string' => $val],
            ['test_string' => 'datetime', 'test_int' => 'integer']
250 251
        );

Sergei Morozov's avatar
Sergei Morozov committed
252
        $this->connection->delete('write_table', ['test_int' => 30, 'test_string' => $val], ['test_string' => 'datetime', 'test_int' => 'integer']);
253

254
        $data = $this->connection->fetchOne('SELECT test_string FROM write_table WHERE test_int = 30');
255

256
        self::assertFalse($data);
257
    }
258

259
    public function testEmptyIdentityInsert(): void
260
    {
Sergei Morozov's avatar
Sergei Morozov committed
261
        $platform = $this->connection->getDatabasePlatform();
262

Sergei Morozov's avatar
Sergei Morozov committed
263
        if (! ($platform->supportsIdentityColumns() || $platform->usesSequenceEmulatedIdentityColumns())) {
264
            self::markTestSkipped(
265 266 267 268
                'Test only works on platforms with identity columns or sequence emulated identity columns.'
            );
        }

Sergei Morozov's avatar
Sergei Morozov committed
269 270 271
        $table = new Table('test_empty_identity');
        $table->addColumn('id', 'integer', ['autoincrement' => true]);
        $table->setPrimaryKey(['id']);
272 273

        try {
Sergei Morozov's avatar
Sergei Morozov committed
274
            $this->connection->getSchemaManager()->dropTable($table->getQuotedName($platform));
Sergei Morozov's avatar
Sergei Morozov committed
275 276
        } catch (Throwable $e) {
        }
277 278

        foreach ($platform->getCreateTableSQL($table) as $sql) {
Sergei Morozov's avatar
Sergei Morozov committed
279
            $this->connection->exec($sql);
280 281 282 283 284 285 286 287
        }

        $seqName = $platform->usesSequenceEmulatedIdentityColumns()
            ? $platform->getIdentitySequenceName('test_empty_identity', 'id')
            : null;

        $sql = $platform->getEmptyIdentityInsertSQL('test_empty_identity', 'id');

Sergei Morozov's avatar
Sergei Morozov committed
288
        $this->connection->exec($sql);
289

290
        $firstId = $this->lastInsertId($seqName);
291

Sergei Morozov's avatar
Sergei Morozov committed
292
        $this->connection->exec($sql);
293

294
        $secondId = $this->lastInsertId($seqName);
295

Gabriel Caruso's avatar
Gabriel Caruso committed
296
        self::assertGreaterThan($firstId, $secondId);
297 298
    }

299 300 301
    /**
     * @group DBAL-2688
     */
302
    public function testUpdateWhereIsNull(): void
303
    {
Sergei Morozov's avatar
Sergei Morozov committed
304
        $this->connection->insert(
305 306 307 308 309
            'write_table',
            ['test_int' => '30', 'test_string' => null],
            ['test_string' => 'string', 'test_int' => 'integer']
        );

310
        $data = $this->connection->fetchAllAssociative('SELECT * FROM write_table WHERE test_int = 30');
311

312
        self::assertCount(1, $data);
313

Sergei Morozov's avatar
Sergei Morozov committed
314
        $this->connection->update('write_table', ['test_int' => 10], ['test_string' => null], ['test_string' => 'string', 'test_int' => 'integer']);
315

316
        $data = $this->connection->fetchAllAssociative('SELECT * FROM write_table WHERE test_int = 30');
317

318
        self::assertCount(0, $data);
319 320
    }

321
    public function testDeleteWhereIsNull(): void
322
    {
Sergei Morozov's avatar
Sergei Morozov committed
323
        $this->connection->insert(
324 325 326 327 328
            'write_table',
            ['test_int' => '30', 'test_string' => null],
            ['test_string' => 'string', 'test_int' => 'integer']
        );

329
        $data = $this->connection->fetchAllAssociative('SELECT * FROM write_table WHERE test_int = 30');
330

331
        self::assertCount(1, $data);
332

Sergei Morozov's avatar
Sergei Morozov committed
333
        $this->connection->delete('write_table', ['test_string' => null], ['test_string' => 'string']);
334

335
        $data = $this->connection->fetchAllAssociative('SELECT * FROM write_table WHERE test_int = 30');
336

337
        self::assertCount(0, $data);
338
    }
339 340 341 342 343

    /**
     * Returns the ID of the last inserted row or skips the test if the currently used driver
     * doesn't support this feature
     *
344
     * @return string|false
345
     *
346
     * @throws Exception
347 348 349 350 351
     */
    private function lastInsertId(?string $name = null)
    {
        try {
            return $this->connection->lastInsertId($name);
352
        } catch (Exception $e) {
353
            if ($e->getSQLState() === 'IM001') {
354
                self::markTestSkipped($e->getMessage());
355 356 357 358 359
            }

            throw $e;
        }
    }
360
}