Connection.php 15.4 KB
Newer Older
1
<?php
2

3
declare(strict_types=1);
4
5
6
7
8
9
10
11
12
13
14
15
16
17

/*
 * This file is part of the TYPO3 CMS project.
 *
 * It is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, either version 2
 * of the License, or any later version.
 *
 * For the full copyright and license information, please read the
 * LICENSE.txt file that was distributed with this source code.
 *
 * The TYPO3 project - inspiring people to share!
 */

18
19
namespace TYPO3\CMS\Core\Database;

20
21
22
use Doctrine\Common\EventManager;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\Driver;
23
use Doctrine\DBAL\Driver\ServerInfoAwareConnection;
24
use Doctrine\DBAL\Driver\Statement;
25
use Doctrine\DBAL\Platforms\PostgreSQL94Platform as PostgreSqlPlatform;
26
use Doctrine\DBAL\Platforms\SQLServer2012Platform;
27
use Doctrine\DBAL\VersionAwarePlatformDriver;
28
29
use Psr\Log\LoggerAwareInterface;
use Psr\Log\LoggerAwareTrait;
30
use TYPO3\CMS\Core\Database\Query\BulkInsertQuery;
31
32
33
34
use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;
use TYPO3\CMS\Core\Utility\GeneralUtility;

35
class Connection extends \Doctrine\DBAL\Connection implements LoggerAwareInterface
36
{
37
38
    use LoggerAwareTrait;

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
    /**
     * Represents a SQL NULL data type.
     */
    const PARAM_NULL = \PDO::PARAM_NULL; // 0

    /**
     * Represents a SQL INTEGER data type.
     */
    const PARAM_INT = \PDO::PARAM_INT; // 1

    /**
     * Represents a SQL CHAR, VARCHAR data type.
     */
    const PARAM_STR = \PDO::PARAM_STR; // 2

    /**
     * Represents a SQL large object data type.
     */
    const PARAM_LOB = \PDO::PARAM_LOB; // 3

    /**
     * Represents a recordset type. Not currently supported by any drivers.
     */
    const PARAM_STMT = \PDO::PARAM_STMT; // 4

    /**
     * Represents a boolean data type.
     */
    const PARAM_BOOL = \PDO::PARAM_BOOL; // 5

69
70
71
72
73
    /**
     * @var array
     */
    private $prepareConnectionCommands = [];

74
75
76
77
78
79
80
81
    /**
     * Initializes a new instance of the Connection class.
     *
     * @param array $params The connection parameters.
     * @param Driver $driver The driver to use.
     * @param Configuration|null $config The configuration, optional.
     * @param EventManager|null $em The event manager, optional.
     *
82
     * @throws \Doctrine\DBAL\Exception
83
84
85
86
87
88
89
     */
    public function __construct(array $params, Driver $driver, Configuration $config = null, EventManager $em = null)
    {
        parent::__construct($params, $driver, $config, $em);
        $this->_expr = GeneralUtility::makeInstance(ExpressionBuilder::class, $this);
    }

90
    /**
91
     * Gets the DatabasePlatform for the connection and initializes custom types and event listeners.
92
     *
93
     * @return bool
94
95
96
97
     */
    public function connect(): bool
    {
        // Early return if the connection is already open and custom setup has been done.
98
        if (!parent::connect()) {
99
100
101
            return false;
        }

102
103
104
105
106
107
        foreach ($this->prepareConnectionCommands as $command) {
            if ($this->executeUpdate($command) === false) {
                $this->logger->critical('Could not initialize DB connection with query "' . $command . '": ' . $this->errorInfo());
            }
        }

108
109
110
        return true;
    }

111
112
113
114
115
    /**
     * Creates a new instance of a SQL query builder.
     *
     * @return \TYPO3\CMS\Core\Database\Query\QueryBuilder
     */
116
    public function createQueryBuilder(): QueryBuilder
117
    {
118
        return GeneralUtility::makeInstance(QueryBuilder::class, $this);
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
    }

    /**
     * Quotes a string so it can be safely used as a table or column name, even if
     * it is a reserved name.
     * EXAMPLE: tableName.fieldName => "tableName"."fieldName"
     *
     * Delimiting style depends on the underlying database platform that is being used.
     *
     * @param string $identifier The name to be quoted.
     *
     * @return string The quoted name.
     */
    public function quoteIdentifier($identifier): string
    {
        if ($identifier === '*') {
            return $identifier;
        }

        return parent::quoteIdentifier($identifier);
    }

    /**
     * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
     *
     * Delimiting style depends on the underlying database platform that is being used.
     *
     * @param array $input
     *
     * @return array
     */
    public function quoteIdentifiers(array $input): array
    {
        return array_map([$this, 'quoteIdentifier'], $input);
    }

    /**
     * Quotes an associative array of column-value so the column names can be safely used, even
     * if the name is a reserved name.
     *
     * Delimiting style depends on the underlying database platform that is being used.
     *
     * @param array $input
     *
     * @return array
     */
    public function quoteColumnValuePairs(array $input): array
    {
        return array_combine($this->quoteIdentifiers(array_keys($input)), array_values($input));
    }

170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
    /**
     * Detect if the column types are specified by column name or using
     * positional information. In the first case quote the field names
     * accordingly.
     *
     * @param array $input
     * @return array
     */
    protected function quoteColumnTypes(array $input): array
    {
        if (!is_string(key($input))) {
            return $input;
        }

        return $this->quoteColumnValuePairs($input);
    }

187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
    /**
     * Inserts a table row with specified data.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     * Table expression and columns are not escaped and are not safe for user-input.
     *
     * @param string $tableName The name of the table to insert data into.
     * @param array $data An associative array containing column-value pairs.
     * @param array $types Types of the inserted data.
     *
     * @return int The number of affected rows.
     */
    public function insert($tableName, array $data, array $types = []): int
    {
        return parent::insert(
            $this->quoteIdentifier($tableName),
            $this->quoteColumnValuePairs($data),
204
            $this->quoteColumnTypes($types)
205
206
207
208
209
210
211
212
213
214
        );
    }

    /**
     * Bulk inserts table rows with specified data.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     * Table expression and columns are not escaped and are not safe for user-input.
     *
     * @param string $tableName The name of the table to insert data into.
215
216
     * @param array $data An array containing associative arrays of column-value pairs or just the values to be inserted.
     * @param array $columns An array containing the column names of the data which should be inserted.
217
218
219
220
221
222
     * @param array $types Types of the inserted data.
     *
     * @return int The number of affected rows.
     */
    public function bulkInsert(string $tableName, array $data, array $columns = [], array $types = []): int
    {
223
        $query = GeneralUtility::makeInstance(BulkInsertQuery::class, $this, $tableName, $columns);
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
        foreach ($data as $values) {
            $query->addValues($values, $types);
        }

        return $query->execute();
    }

    /**
     * Executes an SQL SELECT statement on a table.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     * Table expression and columns are not escaped and are not safe for user-input.
     *
     * @param string[] $columns The columns of the table which to select.
     * @param string $tableName The name of the table on which to select.
     * @param array $identifiers The selection criteria. An associative array containing column-value pairs.
     * @param string[] $groupBy The columns to group the results by.
     * @param array $orderBy Associative array of column name/sort directions pairs.
     * @param int $limit The maximum number of rows to return.
     * @param int $offset The first result row to select (when used with limit)
     *
     * @return Statement The executed statement.
     */
    public function select(
        array $columns,
        string $tableName,
        array $identifiers = [],
        array $groupBy = [],
        array $orderBy = [],
        int $limit = 0,
        int $offset = 0
    ): Statement {
        $query = $this->createQueryBuilder();
        $query->select(...$columns)
            ->from($tableName);

        foreach ($identifiers as $identifier => $value) {
            $query->andWhere($query->expr()->eq($identifier, $query->createNamedParameter($value)));
        }

        foreach ($orderBy as $fieldName => $order) {
            $query->addOrderBy($fieldName, $order);
        }

        if (!empty($groupBy)) {
            $query->groupBy(...$groupBy);
        }

        if ($limit > 0) {
            $query->setMaxResults($limit);
            $query->setFirstResult($offset);
        }

        return $query->execute();
    }

    /**
     * Executes an SQL UPDATE statement on a table.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     * Table expression and columns are not escaped and are not safe for user-input.
     *
     * @param string $tableName The name of the table to update.
     * @param array $data An associative array containing column-value pairs.
     * @param array $identifier The update criteria. An associative array containing column-value pairs.
     * @param array $types Types of the merged $data and $identifier arrays in that order.
     *
     * @return int The number of affected rows.
     */
    public function update($tableName, array $data, array $identifier, array $types = []): int
    {
        return parent::update(
            $this->quoteIdentifier($tableName),
            $this->quoteColumnValuePairs($data),
            $this->quoteColumnValuePairs($identifier),
299
            $this->quoteColumnTypes($types)
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
        );
    }

    /**
     * Executes an SQL DELETE statement on a table.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     * Table expression and columns are not escaped and are not safe for user-input.
     *
     * @param string $tableName The name of the table on which to delete.
     * @param array $identifier The deletion criteria. An associative array containing column-value pairs.
     * @param array $types The types of identifiers.
     *
     * @return int The number of affected rows.
     */
    public function delete($tableName, array $identifier, array $types = []): int
    {
        return parent::delete(
            $this->quoteIdentifier($tableName),
            $this->quoteColumnValuePairs($identifier),
320
            $this->quoteColumnTypes($types)
321
322
323
324
325
326
327
328
329
330
331
332
        );
    }

    /**
     * Executes an SQL TRUNCATE statement on a table.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     * Table expression is not escaped and not safe for user-input.
     *
     * @param string $tableName The name of the table to truncate.
     * @param bool $cascade Not supported on many platforms but would cascade the truncate by following foreign keys.
     *
333
     * @return int The number of affected rows. For a truncate this is unreliable as there is no meaningful information.
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
     */
    public function truncate(string $tableName, bool $cascade = false): int
    {
        return $this->executeUpdate(
            $this->getDatabasePlatform()->getTruncateTableSQL(
                $this->quoteIdentifier($tableName),
                $cascade
            )
        );
    }

    /**
     * Executes an SQL SELECT COUNT() statement on a table and returns the count result.
     *
     * @param string $item The column/expression of the table which to count
     * @param string $tableName The name of the table on which to count.
     * @param array $identifiers The selection criteria. An associative array containing column-value pairs.
     *
     * @return int The number of rows counted
     */
    public function count(string $item, string $tableName, array $identifiers): int
    {
        $query = $this->createQueryBuilder();
        $query->count($item)
            ->from($tableName);

        foreach ($identifiers as $identifier => $value) {
            $query->andWhere($query->expr()->eq($identifier, $query->createNamedParameter($value)));
        }

364
        return (int)$query->execute()->fetchColumn(0);
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
    }

    /**
     * Returns the version of the current platform if applicable.
     *
     * If no version information is available only the platform name will be shown.
     * If the platform name is unknown or unsupported the driver name will be shown.
     *
     * @return string
     * @internal
     */
    public function getServerVersion(): string
    {
        $version = $this->getDatabasePlatform()->getName();
        switch ($version) {
            case 'mysql':
            case 'pdo_mysql':
            case 'drizzle_pdo_mysql':
                $version = 'MySQL';
                break;
            case 'postgresql':
            case 'pdo_postgresql':
                $version = 'PostgreSQL';
                break;
            case 'oci8':
            case 'pdo_oracle':
                $version = 'Oracle';
                break;
            case 'sqlsrv':
            case 'pdo_sqlsrv':
                $version = 'SQLServer';
                break;
        }

        // Driver does not support version specific platforms.
400
        if (!$this->getDriver() instanceof VersionAwarePlatformDriver) {
401
402
403
            return $version;
        }

404
        if ($this->getWrappedConnection() instanceof ServerInfoAwareConnection
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
            && !$this->getWrappedConnection()->requiresQueryForServerVersion()
        ) {
            $version .= ' ' . $this->getWrappedConnection()->getServerVersion();
        }

        return $version;
    }

    /**
     * Execute commands after initializing a new connection.
     *
     * @param string $commands
     */
    public function prepareConnection(string $commands)
    {
        if (empty($commands)) {
            return;
        }

424
        $this->prepareConnectionCommands = GeneralUtility::trimExplode(
425
426
427
428
429
430
431
432
433
            LF,
            str_replace(
                '\' . LF . \'',
                LF,
                $commands
            ),
            true
        );
    }
434
435
436
437
438
439
440
441

    /**
     * Returns the ID of the last inserted row or sequence value.
     * If table and fieldname have been provided it tries to build
     * the sequence name for PostgreSQL. For MySQL the parameters
     * are not required / and only the table name is passed through.
     *
     * @param string|null $tableName
442
     * @param string $fieldName
443
444
445
446
     * @return string
     */
    public function lastInsertId($tableName = null, string $fieldName = 'uid'): string
    {
447
448
        $databasePlatform = $this->getDatabasePlatform();
        if ($databasePlatform instanceof PostgreSqlPlatform) {
449
450
            return parent::lastInsertId(trim(implode('_', [$tableName, $fieldName, 'seq']), '_'));
        }
451
452
453
454
455
456
        if ($databasePlatform instanceof SQLServer2012Platform) {
            // lastInsertId() in mssql >2012 takes a sequence name and not the table name as
            // argument. If no argument is given, last insert id of latest table is returned.
            // https://docs.microsoft.com/de-de/sql/connect/php/pdo-lastinsertid?view=sql-server-2017
            return (string)parent::lastInsertId();
        }
457
458
459

        return (string)parent::lastInsertId($tableName);
    }
460
}