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