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