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