Connection.php 15.5 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\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
    /** @var ExpressionBuilder */
    protected $_expr;

72
73
74
75
76
    /**
     * @var array
     */
    private $prepareConnectionCommands = [];

77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
    /**
     * 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.
     *
     * @throws \Doctrine\DBAL\DBALException
     */
    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);
    }

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

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

111
112
113
        return true;
    }

114
115
116
117
118
    /**
     * Creates a new instance of a SQL query builder.
     *
     * @return \TYPO3\CMS\Core\Database\Query\QueryBuilder
     */
119
    public function createQueryBuilder(): QueryBuilder
120
    {
121
        return GeneralUtility::makeInstance(QueryBuilder::class, $this);
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
170
171
172
    }

    /**
     * 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));
    }

173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
    /**
     * 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);
    }

190
191
192
193
194
195
196
197
198
199
200
201
    /**
     * Quotes like wildcards for given string value.
     *
     * @param string $value The value to be quoted.
     *
     * @return string The quoted value.
     */
    public function escapeLikeWildcards(string $value): string
    {
        return addcslashes($value, '_%');
    }

202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
    /**
     * Inserts a table row with specified data.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     *
     * @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),
218
            $this->quoteColumnTypes($types)
219
220
221
222
223
224
225
226
227
        );
    }

    /**
     * Bulk inserts table rows with specified data.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     *
     * @param string $tableName The name of the table to insert data into.
228
229
     * @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.
230
231
232
233
234
235
     * @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
    {
236
        $query = GeneralUtility::makeInstance(BulkInsertQuery::class, $this, $tableName, $columns);
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
        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.
     *
     * @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)
     *
257
     * @return Statement|\Doctrine\DBAL\ForwardCompatibility\Result|\Doctrine\DBAL\Driver\ResultStatement  The executed statement.
258
259
260
261
262
263
264
265
266
     */
    public function select(
        array $columns,
        string $tableName,
        array $identifiers = [],
        array $groupBy = [],
        array $orderBy = [],
        int $limit = 0,
        int $offset = 0
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
299
300
301
302
303
304
305
306
307
308
309
        $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.
     *
     * @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),
310
            $this->quoteColumnTypes($types)
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
        );
    }

    /**
     * Executes an SQL DELETE statement on a table.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     *
     * @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),
330
            $this->quoteColumnTypes($types)
331
332
333
334
335
336
337
338
339
340
341
        );
    }

    /**
     * Executes an SQL TRUNCATE statement on a table.
     *
     * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
     *
     * @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.
     *
342
     * @return int The number of affected rows. For a truncate this is unreliable as there is no meaningful information.
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
     */
    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)));
        }

373
        return (int)$query->execute()->fetchColumn(0);
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
400
401
402
403
404
405
406
407
408
    }

    /**
     * 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.
409
        if (!$this->getDriver() instanceof VersionAwarePlatformDriver) {
410
411
412
            return $version;
        }

413
        if ($this->getWrappedConnection() instanceof ServerInfoAwareConnection
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
            && !$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;
        }

433
        $this->prepareConnectionCommands = GeneralUtility::trimExplode(
434
435
436
437
438
439
440
441
442
            LF,
            str_replace(
                '\' . LF . \'',
                LF,
                $commands
            ),
            true
        );
    }
443
444
445
446
447
448
449
450

    /**
     * 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
451
     * @param string $fieldName
452
453
454
455
     * @return string
     */
    public function lastInsertId($tableName = null, string $fieldName = 'uid'): string
    {
456
457
        $databasePlatform = $this->getDatabasePlatform();
        if ($databasePlatform instanceof PostgreSqlPlatform) {
458
459
            return parent::lastInsertId(trim(implode('_', [$tableName, $fieldName, 'seq']), '_'));
        }
460
461
462
463
464
465
        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();
        }
466
467
468

        return (string)parent::lastInsertId($tableName);
    }
469
470
471
472
473
474
475
476
477
478

    /**
     * Gets the ExpressionBuilder for the connection.
     *
     * @return ExpressionBuilder
     */
    public function getExpressionBuilder()
    {
        return $this->_expr;
    }
479
}