e02ba58691a69fab48a9328d51b2ccc0ac83f24d
[Packages/TYPO3.CMS.git] / typo3 / sysext / core / Classes / Database / Query / BulkInsertQuery.php
1 <?php
2 declare(strict_types=1);
3 namespace TYPO3\CMS\Core\Database\Query;
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\DBAL\Connection;
19
20 /**
21 * Provides functionality to generate and execute row based bulk INSERT statements.
22 *
23 * Based on work by Steve Müller <st.mueller@dzh-online.de> for the Doctrine project,
24 * licensend under the MIT license.
25 *
26 * This class will be removed from core and the functionality will be provided by
27 * the upstream implemention once the pull request has been merged into Doctrine DBAL.
28 *
29 * @see https://github.com/doctrine/dbal/pull/682
30 * @internal
31 */
32 class BulkInsertQuery
33 {
34 /**
35 * @var string[]
36 */
37 protected $columns;
38
39 /**
40 * @var Connection
41 */
42 protected $connection;
43
44 /**
45 * @var string
46 */
47 protected $table;
48
49 /**
50 * @var array
51 */
52 protected $parameters = [];
53
54 /**
55 * @var array
56 */
57 protected $types = [];
58
59 /**
60 * @var array
61 */
62 protected $values = [];
63
64 /**
65 * Constructor.
66 *
67 * @param Connection $connection The connection to use for query execution.
68 * @param string $table The name of the table to insert rows into.
69 * @param string[] $columns The names of the columns to insert values into.
70 * Can be left empty to allow arbitrary row inserts based on the table's column order.
71 */
72 public function __construct(Connection $connection, string $table, array $columns = [])
73 {
74 $this->connection = $connection;
75 $this->table = $connection->quoteIdentifier($table);
76 $this->columns = $columns;
77 }
78
79 /**
80 * Render the bulk insert statement as string.
81 *
82 * @return string
83 */
84 public function __toString(): string
85 {
86 return $this->getSQL();
87 }
88
89 /**
90 * Adds a set of values to the bulk insert query to be inserted as a row into the specified table.
91 *
92 * @param array $values The set of values to be inserted as a row into the table.
93 * If no columns have been specified for insertion, this can be
94 * an arbitrary list of values to be inserted into the table.
95 * Otherwise the values' keys have to match either one of the
96 * specified column names or indexes.
97 * @param array $types The types for the given values to bind to the query.
98 * If no columns have been specified for insertion, the types'
99 * keys will be matched against the given values' keys.
100 * Otherwise the types' keys will be matched against the
101 * specified column names and indexes.
102 * Non-matching keys will be discarded, missing keys will not
103 * be bound to a specific type.
104 *
105 * @throws \InvalidArgumentException if columns were specified for this query
106 * and either no value for one of the specified
107 * columns is given or multiple values are given
108 * for a single column (named and indexed) or
109 * multiple types are given for a single column
110 * (named and indexed).
111 */
112 public function addValues(array $values, array $types = [])
113 {
114 $valueSet = [];
115
116 if (empty($this->columns)) {
117 foreach ($values as $index => $value) {
118 $this->parameters[] = $value;
119 $this->types[] = isset($types[$index]) ? $types[$index] : null;
120 $valueSet[] = '?';
121 }
122
123 $this->values[] = $valueSet;
124
125 return;
126 }
127
128 foreach ($this->columns as $index => $column) {
129 $namedValue = isset($values[$column]) || array_key_exists($column, $values);
130 $positionalValue = isset($values[$index]) || array_key_exists($index, $values);
131
132 if (!$namedValue && !$positionalValue) {
133 throw new \InvalidArgumentException(
134 sprintf('No value specified for column %s (index %d).', $column, $index),
135 1476049651
136 );
137 }
138
139 if ($namedValue && $positionalValue && $values[$column] !== $values[$index]) {
140 throw new \InvalidArgumentException(
141 sprintf('Multiple values specified for column %s (index %d).', $column, $index),
142 1476049652
143 );
144 }
145
146 $this->parameters[] = $namedValue ? $values[$column] : $values[$index];
147 $valueSet[] = '?';
148
149 $namedType = isset($types[$column]);
150 $positionalType = isset($types[$index]);
151
152 if ($namedType && $positionalType && $types[$column] !== $types[$index]) {
153 throw new \InvalidArgumentException(
154 sprintf('Multiple types specified for column %s (index %d).', $column, $index),
155 1476049653
156 );
157 }
158
159 if ($namedType) {
160 $this->types[] = $types[$column];
161
162 continue;
163 }
164
165 if ($positionalType) {
166 $this->types[] = $types[$index];
167
168 continue;
169 }
170
171 $this->types[] = null;
172 }
173
174 $this->values[] = $valueSet;
175 }
176
177 /**
178 * Executes this INSERT query using the bound parameters and their types.
179 *
180 * @return int The number of affected rows.
181 *
182 * @throws \LogicException if this query contains more rows than acceptable
183 * for a single INSERT statement by the underlying platform.
184 */
185 public function execute(): int
186 {
187 $platform = $this->connection->getDatabasePlatform();
188 $insertMaxRows = $this->getInsertMaxRows();
189
190 if ($insertMaxRows > 0 && count($this->values) > $insertMaxRows) {
191 throw new \LogicException(
192 sprintf(
193 'You can only insert %d rows in a single INSERT statement with platform "%s".',
194 $insertMaxRows,
195 $platform->getName()
196 ),
197 1476049654
198 );
199 }
200
201 return $this->connection->executeUpdate($this->getSQL(), $this->parameters, $this->types);
202 }
203
204 /**
205 * Return the maximum number of rows that can be inserted at the same time.
206 *
207 * @return int
208 */
209 protected function getInsertMaxRows(): int
210 {
211 $platform = $this->connection->getDatabasePlatform();
212 if ($platform->getName() === 'mssql' && $platform->getReservedKeywordsList()->isKeyword('MERGE')) {
213 return 1000;
214 }
215
216 return 0;
217 }
218
219 /**
220 * Returns the parameters for this INSERT query being constructed indexed by parameter index.
221 *
222 * @return array
223 */
224 public function getParameters(): array
225 {
226 return $this->parameters;
227 }
228
229 /**
230 * Returns the parameter types for this INSERT query being constructed indexed by parameter index.
231 *
232 * @return array
233 */
234 public function getParameterTypes(): array
235 {
236 return $this->types;
237 }
238
239 /**
240 * Returns the SQL formed by the current specifications of this INSERT query.
241 *
242 * @return string
243 *
244 * @throws \LogicException if no values have been specified yet.
245 */
246 public function getSQL(): string
247 {
248 if (empty($this->values)) {
249 throw new \LogicException(
250 'You need to add at least one set of values before generating the SQL.',
251 1476049702
252 );
253 }
254
255 $connection = $this->connection;
256 $columnList = '';
257
258 if (!empty($this->columns)) {
259 $columnList = sprintf(
260 ' (%s)',
261 implode(
262 ', ',
263 array_map(
264 function ($column) use ($connection) {
265 return $connection->quoteIdentifier($column);
266 },
267 $this->columns
268 )
269 )
270 );
271 }
272
273 return sprintf(
274 'INSERT INTO %s%s VALUES (%s)',
275 $this->table,
276 $columnList,
277 implode(
278 '), (',
279 array_map(
280 function (array $valueSet) {
281 return implode(', ', $valueSet);
282 },
283 $this->values
284 )
285 )
286 );
287 }
288 }