Commit | Line | Data |
---|---|---|
11ade432 AE |
1 | <?php |
2 | namespace wcf\system\database\editor; | |
bf64fe54 | 3 | use wcf\system\database\exception\DatabaseQueryExecutionException; |
770f5de3 | 4 | use wcf\system\database\util\PreparedStatementConditionBuilder; |
82a7de7f | 5 | use wcf\system\Regex; |
11ade432 AE |
6 | |
7 | /** | |
a17de04e MS |
8 | * Database editor implementation for MySQL4.1 or higher. |
9 | * | |
11ade432 | 10 | * @author Marcel Werk |
7b7b9764 | 11 | * @copyright 2001-2019 WoltLab GmbH |
11ade432 | 12 | * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php> |
e71525e4 | 13 | * @package WoltLabSuite\Core\System\Database\Editor |
11ade432 AE |
14 | */ |
15 | class MySQLDatabaseEditor extends DatabaseEditor { | |
16 | /** | |
0fcfe5f6 | 17 | * @inheritDoc |
11ade432 AE |
18 | */ |
19 | public function getTableNames() { | |
058cbd6a | 20 | $existingTables = []; |
11ade432 AE |
21 | $sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`"; |
22 | $statement = $this->dbObj->prepareStatement($sql); | |
23 | $statement->execute(); | |
24 | while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) { | |
25 | $existingTables[] = $row[0]; | |
26 | } | |
27 | return $existingTables; | |
28 | } | |
29 | ||
30 | /** | |
0fcfe5f6 | 31 | * @inheritDoc |
11ade432 AE |
32 | */ |
33 | public function getColumns($tableName) { | |
058cbd6a | 34 | $columns = []; |
ef5d8ae6 | 35 | $regex = new Regex('([a-z]+)\((.+)\)', Regex::CASE_INSENSITIVE); |
77bb36ca | 36 | |
4f2c4eb8 | 37 | $sql = "SHOW COLUMNS FROM `".$tableName."`"; |
11ade432 AE |
38 | $statement = $this->dbObj->prepareStatement($sql); |
39 | $statement->execute(); | |
40 | while ($row = $statement->fetchArray()) { | |
14d48464 | 41 | $regex->match($row['Type']); |
42 | $typeMatches = $regex->getMatches(); | |
77bb36ca | 43 | |
4d2bd2ec MS |
44 | $type = $row['Type']; |
45 | $length = ''; | |
46 | $decimals = ''; | |
47 | $enumValues = ''; | |
48 | if (!empty($typeMatches)) { | |
49 | $type = $typeMatches[1]; | |
50 | ||
51 | switch ($type) { | |
52 | case 'enum': | |
53 | case 'set': | |
54 | $enumValues = $typeMatches[2]; | |
55 | break; | |
56 | ||
57 | case 'decimal': | |
58 | case 'double': | |
59 | case 'float': | |
60 | $pieces = explode(',', $typeMatches[2]); | |
61 | switch (count($pieces)) { | |
62 | case 1: | |
63 | $length = $pieces[0]; | |
64 | break; | |
65 | ||
66 | case 2: | |
67 | list($length, $decimals) = $pieces; | |
68 | break; | |
69 | } | |
70 | ||
71 | break; | |
72 | ||
73 | default: | |
74 | if ($typeMatches[2] == (int)$typeMatches[2]) { | |
75 | $length = $typeMatches[2]; | |
76 | } | |
77 | break; | |
78 | } | |
79 | } | |
80 | ||
058cbd6a | 81 | $columns[] = ['name' => $row['Field'], 'data' => [ |
4d2bd2ec MS |
82 | 'type' => $type, |
83 | 'length' => $length, | |
84 | 'notNull' => $row['Null'] == 'YES' ? false : true, | |
63b9817b | 85 | 'key' => ($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : ''), |
5fe135db | 86 | 'default' => $row['Default'], |
4d2bd2ec MS |
87 | 'autoIncrement' => $row['Extra'] == 'auto_increment' ? true : false, |
88 | 'enumValues' => $enumValues, | |
ef5d8ae6 | 89 | 'decimals' => $decimals |
058cbd6a | 90 | ]]; |
ebe8e825 | 91 | } |
bf27dc91 | 92 | |
9f959ced | 93 | return $columns; |
11ade432 AE |
94 | } |
95 | ||
dc62342b MS |
96 | /** |
97 | * @inheritDoc | |
98 | */ | |
99 | public function getForeignKeys($tableName) { | |
770f5de3 MS |
100 | $sql = "SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE |
101 | FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS | |
102 | WHERE CONSTRAINT_SCHEMA = ? | |
103 | AND TABLE_NAME = ?"; | |
dc62342b MS |
104 | $statement = $this->dbObj->prepareStatement($sql); |
105 | $statement->execute([ | |
106 | $this->dbObj->getDatabaseName(), | |
107 | $tableName | |
108 | ]); | |
770f5de3 | 109 | $referentialConstraints = $statement->fetchAll(\PDO::FETCH_ASSOC); |
dc62342b MS |
110 | |
111 | $validActions = ['CASCADE', 'SET NULL', 'NO ACTION']; | |
112 | ||
113 | $foreignKeys = []; | |
770f5de3 MS |
114 | foreach ($referentialConstraints as $information) { |
115 | $foreignKeys[$information['CONSTRAINT_NAME']] = [ | |
116 | 'columns' => [], | |
117 | 'referencedColumns' => [], | |
118 | 'ON DELETE' => in_array($information['DELETE_RULE'], $validActions) ? $information['DELETE_RULE'] : null, | |
119 | 'ON UPDATE' => in_array($information['UPDATE_RULE'], $validActions) ? $information['UPDATE_RULE'] : null | |
120 | ]; | |
121 | } | |
122 | ||
123 | if (empty($foreignKeys)) { | |
124 | return []; | |
125 | } | |
126 | ||
127 | $conditionBuilder = new PreparedStatementConditionBuilder(); | |
128 | $conditionBuilder->add('CONSTRAINT_NAME IN (?)', [array_keys($foreignKeys)]); | |
129 | $conditionBuilder->add('TABLE_SCHEMA = ?', [$this->dbObj->getDatabaseName()]); | |
130 | $conditionBuilder->add('TABLE_NAME = ?', [$tableName]); | |
131 | ||
132 | $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME | |
133 | FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE | |
134 | " . $conditionBuilder; | |
135 | $statement = $this->dbObj->prepareStatement($sql); | |
136 | $statement->execute($conditionBuilder->getParameters()); | |
137 | $keyColumnUsage = $statement->fetchAll(\PDO::FETCH_ASSOC); | |
138 | ||
139 | foreach ($keyColumnUsage as $information) { | |
140 | $foreignKeys[$information['CONSTRAINT_NAME']]['columns'][] = $information['COLUMN_NAME']; | |
141 | $foreignKeys[$information['CONSTRAINT_NAME']]['referencedColumns'][] = $information['REFERENCED_COLUMN_NAME']; | |
142 | $foreignKeys[$information['CONSTRAINT_NAME']]['referencedTable'] = $information['REFERENCED_TABLE_NAME']; | |
dc62342b MS |
143 | } |
144 | ||
145 | foreach ($foreignKeys as $keyName => $keyData) { | |
146 | $foreignKeys[$keyName]['columns'] = array_unique($foreignKeys[$keyName]['columns']); | |
147 | $foreignKeys[$keyName]['referencedColumns'] = array_unique($foreignKeys[$keyName]['referencedColumns']); | |
148 | } | |
149 | ||
150 | return $foreignKeys; | |
151 | } | |
152 | ||
153 | /** | |
154 | * @inheritDoc | |
155 | */ | |
156 | public function getIndexInformation($tableName) { | |
157 | $sql = "SHOW INDEX | |
158 | FROM `".$tableName."`"; | |
159 | $statement = $this->dbObj->prepareStatement($sql); | |
160 | $statement->execute(); | |
161 | $indices = $statement->fetchAll(\PDO::FETCH_ASSOC); | |
162 | ||
163 | $indexInformation = []; | |
164 | foreach ($indices as $index) { | |
165 | if (!isset($indexInformation[$index['Key_name']])) { | |
166 | $type = null; | |
167 | if ($index['Index_type'] === 'FULLTEXT') { | |
168 | $type = 'FULLTEXT'; | |
169 | } | |
170 | else if ($index['Key_name'] === 'PRIMARY') { | |
171 | $type = 'PRIMARY'; | |
172 | } | |
173 | else if ($index['Non_unique'] == 0) { | |
174 | $type = 'UNIQUE'; | |
175 | } | |
176 | ||
177 | $indexInformation[$index['Key_name']] = [ | |
178 | 'columns' => [$index['Column_name']], | |
179 | 'type' => $type | |
180 | ]; | |
181 | } | |
182 | else { | |
183 | $indexInformation[$index['Key_name']]['columns'][] = $index['Column_name']; | |
184 | } | |
185 | } | |
186 | ||
187 | return $indexInformation; | |
188 | } | |
189 | ||
11ade432 | 190 | /** |
0fcfe5f6 | 191 | * @inheritDoc |
11ade432 AE |
192 | */ |
193 | public function getIndices($tableName) { | |
058cbd6a | 194 | $indices = []; |
4f2c4eb8 | 195 | $sql = "SHOW INDEX FROM `".$tableName."`"; |
11ade432 AE |
196 | $statement = $this->dbObj->prepareStatement($sql); |
197 | $statement->execute(); | |
198 | while ($row = $statement->fetchArray()) { | |
9f959ced MS |
199 | $indices[] = $row['Key_name']; |
200 | } | |
201 | ||
f62c045d | 202 | return array_unique($indices); |
11ade432 AE |
203 | } |
204 | ||
205 | /** | |
0fcfe5f6 | 206 | * @inheritDoc |
11ade432 | 207 | */ |
058cbd6a | 208 | public function createTable($tableName, $columns, $indices = []) { |
11ade432 AE |
209 | $columnDefinition = $indexDefinition = ''; |
210 | ||
211 | // build column definition | |
212 | foreach ($columns as $column) { | |
213 | if (!empty($columnDefinition)) $columnDefinition .= ','; | |
214 | $columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']); | |
215 | } | |
216 | ||
217 | // build index definition | |
218 | $hasFulltextIndex = false; | |
219 | foreach ($indices as $index) { | |
220 | if (!empty($indexDefinition)) $indexDefinition .= ','; | |
221 | $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']); | |
222 | if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true; | |
223 | } | |
224 | ||
225 | // create table | |
4f2c4eb8 | 226 | $sql = "CREATE TABLE `".$tableName."` ( |
11ade432 AE |
227 | ".$columnDefinition." |
228 | ".(!empty($indexDefinition) ? ',' : '')." | |
229 | ".$indexDefinition." | |
ac677ff6 | 230 | ) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"; |
11ade432 AE |
231 | $statement = $this->dbObj->prepareStatement($sql); |
232 | $statement->execute(); | |
233 | } | |
234 | ||
235 | /** | |
0fcfe5f6 | 236 | * @inheritDoc |
11ade432 AE |
237 | */ |
238 | public function dropTable($tableName) { | |
4f2c4eb8 | 239 | $sql = "DROP TABLE IF EXISTS `".$tableName."`"; |
11ade432 AE |
240 | $statement = $this->dbObj->prepareStatement($sql); |
241 | $statement->execute(); | |
242 | } | |
243 | ||
244 | /** | |
0fcfe5f6 | 245 | * @inheritDoc |
11ade432 AE |
246 | */ |
247 | public function addColumn($tableName, $columnName, $columnData) { | |
4f2c4eb8 | 248 | $sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData); |
11ade432 AE |
249 | $statement = $this->dbObj->prepareStatement($sql); |
250 | $statement->execute(); | |
251 | } | |
252 | ||
253 | /** | |
0fcfe5f6 | 254 | * @inheritDoc |
11ade432 AE |
255 | */ |
256 | public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) { | |
4f2c4eb8 | 257 | $sql = "ALTER TABLE `".$tableName."` CHANGE COLUMN `".$oldColumnName."` ".$this->buildColumnDefinition($newColumnName, $newColumnData); |
11ade432 AE |
258 | $statement = $this->dbObj->prepareStatement($sql); |
259 | $statement->execute(); | |
260 | } | |
261 | ||
f6e43f2f MS |
262 | /** |
263 | * @inheritDoc | |
264 | */ | |
265 | public function alterColumns($tableName, $alterData) { | |
266 | $queries = ""; | |
267 | foreach ($alterData as $columnName => $data) { | |
268 | switch ($data['action']) { | |
269 | case 'add': | |
270 | $queries .= "ADD COLUMN {$this->buildColumnDefinition($columnName, $data['data'])},"; | |
271 | break; | |
272 | ||
273 | case 'alter': | |
1a024c65 MS |
274 | $newColumnName = $columnName; |
275 | if (isset($data['oldColumnName'])) { | |
276 | /** | |
277 | * @deprecated 5.4 `oldColumnName` was an incorrect name for the index | |
278 | * that is kept for backwards compatibility for now | |
279 | */ | |
280 | $newColumnName = $data['oldColumnName']; | |
281 | } | |
282 | else if (isset($data['newColumnName'])) { | |
283 | $newColumnName = $data['newColumnName']; | |
284 | } | |
285 | ||
286 | $queries .= "CHANGE COLUMN `{$columnName}` {$this->buildColumnDefinition($newColumnName, $data['data'])},"; | |
f6e43f2f MS |
287 | break; |
288 | ||
289 | case 'drop': | |
290 | $queries .= "DROP COLUMN `{$columnName}`,"; | |
291 | break; | |
292 | } | |
293 | } | |
294 | ||
295 | $this->dbObj->prepareStatement("ALTER TABLE `{$tableName}` " . rtrim($queries, ','))->execute(); | |
296 | } | |
297 | ||
11ade432 | 298 | /** |
0fcfe5f6 | 299 | * @inheritDoc |
11ade432 AE |
300 | */ |
301 | public function dropColumn($tableName, $columnName) { | |
bf64fe54 TD |
302 | try { |
303 | $sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`"; | |
304 | $statement = $this->dbObj->prepareStatement($sql); | |
305 | $statement->execute(); | |
306 | } | |
307 | catch (DatabaseQueryExecutionException $e) { | |
308 | if ($e->getCode() != '42000') { | |
309 | throw $e; | |
310 | } | |
311 | if (in_array($columnName, array_column($this->getColumns($tableName), 'name'))) { | |
312 | throw $e; | |
313 | } | |
314 | } | |
11ade432 AE |
315 | } |
316 | ||
317 | /** | |
0fcfe5f6 | 318 | * @inheritDoc |
11ade432 AE |
319 | */ |
320 | public function addIndex($tableName, $indexName, $indexData) { | |
4f2c4eb8 | 321 | $sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData); |
11ade432 AE |
322 | $statement = $this->dbObj->prepareStatement($sql); |
323 | $statement->execute(); | |
324 | } | |
325 | ||
326 | /** | |
0fcfe5f6 | 327 | * @inheritDoc |
11ade432 AE |
328 | */ |
329 | public function addForeignKey($tableName, $indexName, $indexData) { | |
4f2c4eb8 | 330 | $sql = "ALTER TABLE `".$tableName."` ADD"; |
11ade432 AE |
331 | |
332 | // add index name | |
96017679 | 333 | if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`"; |
11ade432 AE |
334 | |
335 | // add columns | |
4f2c4eb8 | 336 | $sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)"; |
11ade432 AE |
337 | |
338 | // add referenced table name | |
4f2c4eb8 | 339 | $sql .= " REFERENCES `".$indexData['referencedTable']."`"; |
11ade432 AE |
340 | |
341 | // add referenced columns | |
4f2c4eb8 | 342 | $sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)"; |
11ade432 AE |
343 | |
344 | // add operation and action | |
345 | if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action']; | |
281ac362 TD |
346 | if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE']; |
347 | if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE']; | |
11ade432 AE |
348 | |
349 | $statement = $this->dbObj->prepareStatement($sql); | |
350 | $statement->execute(); | |
351 | } | |
352 | ||
353 | /** | |
0fcfe5f6 | 354 | * @inheritDoc |
11ade432 AE |
355 | */ |
356 | public function dropIndex($tableName, $indexName) { | |
bf64fe54 TD |
357 | try { |
358 | $sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`"; | |
359 | $statement = $this->dbObj->prepareStatement($sql); | |
360 | $statement->execute(); | |
361 | } | |
362 | catch (DatabaseQueryExecutionException $e) { | |
363 | if ($e->getCode() != '42000') { | |
364 | throw $e; | |
365 | } | |
366 | if (in_array($indexName, $this->getIndices($tableName))) { | |
367 | throw $e; | |
368 | } | |
369 | } | |
11ade432 AE |
370 | } |
371 | ||
42a1e71f | 372 | /** |
6b7d6653 | 373 | * @inheritDoc |
42a1e71f | 374 | */ |
375 | public function dropPrimaryKey($tableName) { | |
bf64fe54 TD |
376 | try { |
377 | $sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY"; | |
378 | $statement = $this->dbObj->prepareStatement($sql); | |
379 | $statement->execute(); | |
380 | } | |
381 | catch (DatabaseQueryExecutionException $e) { | |
382 | if ($e->getCode() != '42000') { | |
383 | throw $e; | |
384 | } | |
385 | if (in_array("PRIMARY", $this->getIndices($tableName))) { | |
386 | throw $e; | |
387 | } | |
388 | } | |
42a1e71f | 389 | } |
390 | ||
b6ae7d04 | 391 | /** |
0fcfe5f6 | 392 | * @inheritDoc |
b6ae7d04 MW |
393 | */ |
394 | public function dropForeignKey($tableName, $indexName) { | |
bf64fe54 TD |
395 | try { |
396 | $sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`"; | |
397 | $statement = $this->dbObj->prepareStatement($sql); | |
398 | $statement->execute(); | |
399 | } | |
400 | catch (DatabaseQueryExecutionException $e) { | |
401 | if ($e->getCode() != '42000') { | |
402 | throw $e; | |
403 | } | |
404 | if (in_array($indexName, array_keys($this->getForeignKeys($tableName)))) { | |
405 | throw $e; | |
406 | } | |
407 | } | |
b6ae7d04 MW |
408 | } |
409 | ||
11ade432 AE |
410 | /** |
411 | * Builds a column definition for execution in a create table or alter table statement. | |
412 | * | |
413 | * @param string $columnName | |
414 | * @param array $columnData | |
71952a87 | 415 | * @return string |
11ade432 AE |
416 | */ |
417 | protected function buildColumnDefinition($columnName, $columnData) { | |
418 | // column name | |
4f2c4eb8 | 419 | $definition = "`".$columnName."`"; |
11ade432 AE |
420 | // column type |
421 | $definition .= " ".$columnData['type']; | |
f6e43f2f | 422 | |
11ade432 AE |
423 | // column length and decimals |
424 | if (!empty($columnData['length'])) { | |
425 | $definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")"; | |
426 | } | |
427 | // enum / set | |
428 | if ($columnData['type'] == 'enum' && !empty($columnData['values'])) { | |
429 | $definition .= "(".$columnData['values'].")"; | |
430 | } | |
431 | // not null / null | |
432 | if (!empty($columnData['notNull'])) $definition .= " NOT NULL"; | |
433 | // default | |
434 | if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default']; | |
435 | // auto_increment | |
436 | if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT"; | |
437 | // key | |
438 | if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY"; | |
439 | ||
440 | return $definition; | |
441 | } | |
442 | ||
443 | /** | |
444 | * Builds a index definition for execution in a create table or alter table statement. | |
445 | * | |
446 | * @param string $indexName | |
447 | * @param array $indexData | |
71952a87 | 448 | * @return string |
11ade432 AE |
449 | */ |
450 | protected function buildIndexDefinition($indexName, $indexData) { | |
11ade432 AE |
451 | // index type |
452 | if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY"; | |
453 | else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY"; | |
454 | else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY"; | |
455 | else $definition = "KEY"; | |
456 | ||
457 | // index name | |
4f2c4eb8 | 458 | if (!empty($indexName)) $definition .= " `".$indexName."`"; |
11ade432 | 459 | // columns |
4f2c4eb8 | 460 | $definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)"; |
11ade432 AE |
461 | |
462 | return $definition; | |
463 | } | |
464 | } |