2c239f9f2ee5e6a2f2d6547a75ce34e46ffe0237
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Connection.php
1 <?php
2 declare(strict_types = 1);
3 namespace TYPO3\CMS\Core\Database;
4
5 /*
6 * This file is part of the TYPO3 CMS project.
7 *
8 * It is free software; you can redistribute it and/or modify it under
9 * the terms of the GNU General Public License, either version 2
10 * of the License, or any later version.
11 *
12 * For the full copyright and license information, please read the
13 * LICENSE.txt file that was distributed with this source code.
14 *
15 * The TYPO3 project - inspiring people to share!
16 */
17
18 use Doctrine\Common\EventManager;
19 use Doctrine\DBAL\Configuration;
20 use Doctrine\DBAL\Driver;
21 use Doctrine\DBAL\Driver\Statement;
22 use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
23 use Doctrine\DBAL\Platforms\SQLServer2012Platform;
24 use Psr\Log\LoggerAwareInterface;
25 use Psr\Log\LoggerAwareTrait;
26 use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
27 use TYPO3\CMS\Core\Database\Query\QueryBuilder;
28 use TYPO3\CMS\Core\Utility\GeneralUtility;
29
30 class Connection extends \Doctrine\DBAL\Connection implements LoggerAwareInterface
31 {
32 use LoggerAwareTrait;
33
34 /**
35 * Represents a SQL NULL data type.
36 */
37 const PARAM_NULL = \PDO::PARAM_NULL; // 0
38
39 /**
40 * Represents a SQL INTEGER data type.
41 */
42 const PARAM_INT = \PDO::PARAM_INT; // 1
43
44 /**
45 * Represents a SQL CHAR, VARCHAR data type.
46 */
47 const PARAM_STR = \PDO::PARAM_STR; // 2
48
49 /**
50 * Represents a SQL large object data type.
51 */
52 const PARAM_LOB = \PDO::PARAM_LOB; // 3
53
54 /**
55 * Represents a recordset type. Not currently supported by any drivers.
56 */
57 const PARAM_STMT = \PDO::PARAM_STMT; // 4
58
59 /**
60 * Represents a boolean data type.
61 */
62 const PARAM_BOOL = \PDO::PARAM_BOOL; // 5
63
64 /**
65 * @var array
66 */
67 private $prepareConnectionCommands = [];
68
69 /**
70 * Prevents the duplicate registration of event handlers and types for this connection.
71 * If true the events will not be initialized any more in the getDatabasePlatform() method.
72 *
73 * @var bool
74 */
75 private $customConnectSetupExecuted = false;
76
77 /**
78 * Initializes a new instance of the Connection class.
79 *
80 * @param array $params The connection parameters.
81 * @param Driver $driver The driver to use.
82 * @param Configuration|null $config The configuration, optional.
83 * @param EventManager|null $em The event manager, optional.
84 *
85 * @throws \Doctrine\DBAL\DBALException
86 */
87 public function __construct(array $params, Driver $driver, Configuration $config = null, EventManager $em = null)
88 {
89 parent::__construct($params, $driver, $config, $em);
90 $this->_expr = GeneralUtility::makeInstance(ExpressionBuilder::class, $this);
91 }
92
93 /**
94 * Gets the DatabasePlatform for the connection and initializes custom types and event listeners.
95 *
96 * @return \Doctrine\DBAL\Platforms\AbstractPlatform
97 */
98 public function connect(): bool
99 {
100 // Early return if the connection is already open and custom setup has been done.
101 if ($this->customConnectSetupExecuted || !parent::connect()) {
102 return false;
103 }
104
105 foreach ($this->prepareConnectionCommands as $command) {
106 if ($this->executeUpdate($command) === false) {
107 $this->logger->critical('Could not initialize DB connection with query "' . $command . '": ' . $this->errorInfo());
108 }
109 }
110
111 $this->customConnectSetupExecuted = true;
112
113 return true;
114 }
115
116 /**
117 * Creates a new instance of a SQL query builder.
118 *
119 * @return \TYPO3\CMS\Core\Database\Query\QueryBuilder
120 */
121 public function createQueryBuilder(): QueryBuilder
122 {
123 return GeneralUtility::makeInstance(QueryBuilder::class, $this);
124 }
125
126 /**
127 * Quotes a string so it can be safely used as a table or column name, even if
128 * it is a reserved name.
129 * EXAMPLE: tableName.fieldName => "tableName"."fieldName"
130 *
131 * Delimiting style depends on the underlying database platform that is being used.
132 *
133 * @param string $identifier The name to be quoted.
134 *
135 * @return string The quoted name.
136 */
137 public function quoteIdentifier($identifier): string
138 {
139 if ($identifier === '*') {
140 return $identifier;
141 }
142
143 return parent::quoteIdentifier($identifier);
144 }
145
146 /**
147 * Quotes an array of column names so it can be safely used, even if the name is a reserved name.
148 *
149 * Delimiting style depends on the underlying database platform that is being used.
150 *
151 * @param array $input
152 *
153 * @return array
154 */
155 public function quoteIdentifiers(array $input): array
156 {
157 return array_map([$this, 'quoteIdentifier'], $input);
158 }
159
160 /**
161 * Quotes an associative array of column-value so the column names can be safely used, even
162 * if the name is a reserved name.
163 *
164 * Delimiting style depends on the underlying database platform that is being used.
165 *
166 * @param array $input
167 *
168 * @return array
169 */
170 public function quoteColumnValuePairs(array $input): array
171 {
172 return array_combine($this->quoteIdentifiers(array_keys($input)), array_values($input));
173 }
174
175 /**
176 * Detect if the column types are specified by column name or using
177 * positional information. In the first case quote the field names
178 * accordingly.
179 *
180 * @param array $input
181 * @return array
182 */
183 protected function quoteColumnTypes(array $input): array
184 {
185 if (!is_string(key($input))) {
186 return $input;
187 }
188
189 return $this->quoteColumnValuePairs($input);
190 }
191
192 /**
193 * Inserts a table row with specified data.
194 *
195 * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
196 * Table expression and columns are not escaped and are not safe for user-input.
197 *
198 * @param string $tableName The name of the table to insert data into.
199 * @param array $data An associative array containing column-value pairs.
200 * @param array $types Types of the inserted data.
201 *
202 * @return int The number of affected rows.
203 */
204 public function insert($tableName, array $data, array $types = []): int
205 {
206 return parent::insert(
207 $this->quoteIdentifier($tableName),
208 $this->quoteColumnValuePairs($data),
209 $this->quoteColumnTypes($types)
210 );
211 }
212
213 /**
214 * Bulk inserts table rows with specified data.
215 *
216 * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
217 * Table expression and columns are not escaped and are not safe for user-input.
218 *
219 * @param string $tableName The name of the table to insert data into.
220 * @param array $data An array containing associative arrays of column-value pairs.
221 * @param array $columns An array containing associative arrays of column-value pairs.
222 * @param array $types Types of the inserted data.
223 *
224 * @return int The number of affected rows.
225 */
226 public function bulkInsert(string $tableName, array $data, array $columns = [], array $types = []): int
227 {
228 $query = GeneralUtility::makeInstance(Query\BulkInsertQuery::class, $this, $tableName, $columns);
229 foreach ($data as $values) {
230 $query->addValues($values, $types);
231 }
232
233 return $query->execute();
234 }
235
236 /**
237 * Executes an SQL SELECT statement on a table.
238 *
239 * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
240 * Table expression and columns are not escaped and are not safe for user-input.
241 *
242 * @param string[] $columns The columns of the table which to select.
243 * @param string $tableName The name of the table on which to select.
244 * @param array $identifiers The selection criteria. An associative array containing column-value pairs.
245 * @param string[] $groupBy The columns to group the results by.
246 * @param array $orderBy Associative array of column name/sort directions pairs.
247 * @param int $limit The maximum number of rows to return.
248 * @param int $offset The first result row to select (when used with limit)
249 *
250 * @return Statement The executed statement.
251 */
252 public function select(
253 array $columns,
254 string $tableName,
255 array $identifiers = [],
256 array $groupBy = [],
257 array $orderBy = [],
258 int $limit = 0,
259 int $offset = 0
260 ): Statement {
261 $query = $this->createQueryBuilder();
262 $query->select(...$columns)
263 ->from($tableName);
264
265 foreach ($identifiers as $identifier => $value) {
266 $query->andWhere($query->expr()->eq($identifier, $query->createNamedParameter($value)));
267 }
268
269 foreach ($orderBy as $fieldName => $order) {
270 $query->addOrderBy($fieldName, $order);
271 }
272
273 if (!empty($groupBy)) {
274 $query->groupBy(...$groupBy);
275 }
276
277 if ($limit > 0) {
278 $query->setMaxResults($limit);
279 $query->setFirstResult($offset);
280 }
281
282 return $query->execute();
283 }
284
285 /**
286 * Executes an SQL UPDATE statement on a table.
287 *
288 * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
289 * Table expression and columns are not escaped and are not safe for user-input.
290 *
291 * @param string $tableName The name of the table to update.
292 * @param array $data An associative array containing column-value pairs.
293 * @param array $identifier The update criteria. An associative array containing column-value pairs.
294 * @param array $types Types of the merged $data and $identifier arrays in that order.
295 *
296 * @return int The number of affected rows.
297 */
298 public function update($tableName, array $data, array $identifier, array $types = []): int
299 {
300 return parent::update(
301 $this->quoteIdentifier($tableName),
302 $this->quoteColumnValuePairs($data),
303 $this->quoteColumnValuePairs($identifier),
304 $this->quoteColumnTypes($types)
305 );
306 }
307
308 /**
309 * Executes an SQL DELETE statement on a table.
310 *
311 * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
312 * Table expression and columns are not escaped and are not safe for user-input.
313 *
314 * @param string $tableName The name of the table on which to delete.
315 * @param array $identifier The deletion criteria. An associative array containing column-value pairs.
316 * @param array $types The types of identifiers.
317 *
318 * @return int The number of affected rows.
319 */
320 public function delete($tableName, array $identifier, array $types = []): int
321 {
322 return parent::delete(
323 $this->quoteIdentifier($tableName),
324 $this->quoteColumnValuePairs($identifier),
325 $this->quoteColumnTypes($types)
326 );
327 }
328
329 /**
330 * Executes an SQL TRUNCATE statement on a table.
331 *
332 * All SQL identifiers are expected to be unquoted and will be quoted when building the query.
333 * Table expression is not escaped and not safe for user-input.
334 *
335 * @param string $tableName The name of the table to truncate.
336 * @param bool $cascade Not supported on many platforms but would cascade the truncate by following foreign keys.
337 *
338 * @return int The number of affected rows. For a truncate this is unreliable as theres no meaningful information.
339 */
340 public function truncate(string $tableName, bool $cascade = false): int
341 {
342 return $this->executeUpdate(
343 $this->getDatabasePlatform()->getTruncateTableSQL(
344 $this->quoteIdentifier($tableName),
345 $cascade
346 )
347 );
348 }
349
350 /**
351 * Executes an SQL SELECT COUNT() statement on a table and returns the count result.
352 *
353 * @param string $item The column/expression of the table which to count
354 * @param string $tableName The name of the table on which to count.
355 * @param array $identifiers The selection criteria. An associative array containing column-value pairs.
356 *
357 * @return int The number of rows counted
358 */
359 public function count(string $item, string $tableName, array $identifiers): int
360 {
361 $query = $this->createQueryBuilder();
362 $query->count($item)
363 ->from($tableName);
364
365 foreach ($identifiers as $identifier => $value) {
366 $query->andWhere($query->expr()->eq($identifier, $query->createNamedParameter($value)));
367 }
368
369 return (int)$query->execute()->fetchColumn(0);
370 }
371
372 /**
373 * Returns the version of the current platform if applicable.
374 *
375 * If no version information is available only the platform name will be shown.
376 * If the platform name is unknown or unsupported the driver name will be shown.
377 *
378 * @return string
379 * @internal
380 */
381 public function getServerVersion(): string
382 {
383 $version = $this->getDatabasePlatform()->getName();
384 switch ($version) {
385 case 'mysql':
386 case 'pdo_mysql':
387 case 'drizzle_pdo_mysql':
388 $version = 'MySQL';
389 break;
390 case 'postgresql':
391 case 'pdo_postgresql':
392 $version = 'PostgreSQL';
393 break;
394 case 'oci8':
395 case 'pdo_oracle':
396 $version = 'Oracle';
397 break;
398 case 'sqlsrv':
399 case 'pdo_sqlsrv':
400 $version = 'SQLServer';
401 break;
402 }
403
404 // Driver does not support version specific platforms.
405 if (!$this->getDriver() instanceof \Doctrine\DBAL\VersionAwarePlatformDriver) {
406 return $version;
407 }
408
409 if ($this->getWrappedConnection() instanceof \Doctrine\DBAL\Driver\ServerInfoAwareConnection
410 && !$this->getWrappedConnection()->requiresQueryForServerVersion()
411 ) {
412 $version .= ' ' . $this->getWrappedConnection()->getServerVersion();
413 }
414
415 return $version;
416 }
417
418 /**
419 * Execute commands after initializing a new connection.
420 *
421 * @param string $commands
422 */
423 public function prepareConnection(string $commands)
424 {
425 if (empty($commands)) {
426 return;
427 }
428
429 $this->prepareConnectionCommands = GeneralUtility::trimExplode(
430 LF,
431 str_replace(
432 '\' . LF . \'',
433 LF,
434 $commands
435 ),
436 true
437 );
438 }
439
440 /**
441 * Returns the ID of the last inserted row or sequence value.
442 * If table and fieldname have been provided it tries to build
443 * the sequence name for PostgreSQL. For MySQL the parameters
444 * are not required / and only the table name is passed through.
445 *
446 * @param string|null $tableName
447 * @param string $fieldName
448 * @return string
449 */
450 public function lastInsertId($tableName = null, string $fieldName = 'uid'): string
451 {
452 $databasePlatform = $this->getDatabasePlatform();
453 if ($databasePlatform instanceof PostgreSqlPlatform) {
454 return parent::lastInsertId(trim(implode('_', [$tableName, $fieldName, 'seq']), '_'));
455 }
456 if ($databasePlatform instanceof SQLServer2012Platform) {
457 // lastInsertId() in mssql >2012 takes a sequence name and not the table name as
458 // argument. If no argument is given, last insert id of latest table is returned.
459 // https://docs.microsoft.com/de-de/sql/connect/php/pdo-lastinsertid?view=sql-server-2017
460 return (string)parent::lastInsertId();
461 }
462
463 return (string)parent::lastInsertId($tableName);
464 }
465 }