Commit | Line | Data |
---|---|---|
11ade432 AE |
1 | <?php |
2 | namespace wcf\system\database\editor; | |
82a7de7f | 3 | use wcf\system\Regex; |
11ade432 AE |
4 | |
5 | /** | |
a17de04e MS |
6 | * Database editor implementation for MySQL4.1 or higher. |
7 | * | |
11ade432 | 8 | * @author Marcel Werk |
7b7b9764 | 9 | * @copyright 2001-2019 WoltLab GmbH |
11ade432 | 10 | * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php> |
e71525e4 | 11 | * @package WoltLabSuite\Core\System\Database\Editor |
11ade432 AE |
12 | */ |
13 | class MySQLDatabaseEditor extends DatabaseEditor { | |
14 | /** | |
0fcfe5f6 | 15 | * @inheritDoc |
11ade432 AE |
16 | */ |
17 | public function getTableNames() { | |
058cbd6a | 18 | $existingTables = []; |
11ade432 AE |
19 | $sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`"; |
20 | $statement = $this->dbObj->prepareStatement($sql); | |
21 | $statement->execute(); | |
22 | while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) { | |
23 | $existingTables[] = $row[0]; | |
24 | } | |
25 | return $existingTables; | |
26 | } | |
27 | ||
28 | /** | |
0fcfe5f6 | 29 | * @inheritDoc |
11ade432 AE |
30 | */ |
31 | public function getColumns($tableName) { | |
058cbd6a | 32 | $columns = []; |
4d2bd2ec | 33 | $regex = new Regex('([a-z]+)\((.+)\)', Regex::CASE_INSENSITIVE); |
77bb36ca | 34 | |
4f2c4eb8 | 35 | $sql = "SHOW COLUMNS FROM `".$tableName."`"; |
11ade432 AE |
36 | $statement = $this->dbObj->prepareStatement($sql); |
37 | $statement->execute(); | |
38 | while ($row = $statement->fetchArray()) { | |
14d48464 | 39 | $regex->match($row['Type']); |
40 | $typeMatches = $regex->getMatches(); | |
77bb36ca | 41 | |
4d2bd2ec MS |
42 | $type = $row['Type']; |
43 | $length = ''; | |
44 | $decimals = ''; | |
45 | $enumValues = ''; | |
46 | if (!empty($typeMatches)) { | |
47 | $type = $typeMatches[1]; | |
48 | ||
49 | switch ($type) { | |
50 | case 'enum': | |
51 | case 'set': | |
52 | $enumValues = $typeMatches[2]; | |
53 | break; | |
54 | ||
55 | case 'decimal': | |
56 | case 'double': | |
57 | case 'float': | |
58 | $pieces = explode(',', $typeMatches[2]); | |
59 | switch (count($pieces)) { | |
60 | case 1: | |
61 | $length = $pieces[0]; | |
62 | break; | |
63 | ||
64 | case 2: | |
65 | list($length, $decimals) = $pieces; | |
66 | break; | |
67 | } | |
68 | ||
69 | break; | |
70 | ||
71 | default: | |
72 | if ($typeMatches[2] == (int)$typeMatches[2]) { | |
73 | $length = $typeMatches[2]; | |
74 | } | |
75 | break; | |
76 | } | |
77 | } | |
78 | ||
058cbd6a | 79 | $columns[] = ['name' => $row['Field'], 'data' => [ |
4d2bd2ec MS |
80 | 'type' => $type, |
81 | 'length' => $length, | |
82 | 'notNull' => $row['Null'] == 'YES' ? false : true, | |
63b9817b | 83 | 'key' => ($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : ''), |
5fe135db | 84 | 'default' => $row['Default'], |
4d2bd2ec MS |
85 | 'autoIncrement' => $row['Extra'] == 'auto_increment' ? true : false, |
86 | 'enumValues' => $enumValues, | |
87 | 'decimals' => $decimals | |
058cbd6a | 88 | ]]; |
ebe8e825 | 89 | } |
bf27dc91 | 90 | |
9f959ced | 91 | return $columns; |
11ade432 AE |
92 | } |
93 | ||
94 | /** | |
0fcfe5f6 | 95 | * @inheritDoc |
11ade432 AE |
96 | */ |
97 | public function getIndices($tableName) { | |
058cbd6a | 98 | $indices = []; |
4f2c4eb8 | 99 | $sql = "SHOW INDEX FROM `".$tableName."`"; |
11ade432 AE |
100 | $statement = $this->dbObj->prepareStatement($sql); |
101 | $statement->execute(); | |
102 | while ($row = $statement->fetchArray()) { | |
9f959ced MS |
103 | $indices[] = $row['Key_name']; |
104 | } | |
105 | ||
106 | return $indices; | |
11ade432 AE |
107 | } |
108 | ||
109 | /** | |
0fcfe5f6 | 110 | * @inheritDoc |
11ade432 | 111 | */ |
058cbd6a | 112 | public function createTable($tableName, $columns, $indices = []) { |
11ade432 AE |
113 | $columnDefinition = $indexDefinition = ''; |
114 | ||
115 | // build column definition | |
116 | foreach ($columns as $column) { | |
117 | if (!empty($columnDefinition)) $columnDefinition .= ','; | |
118 | $columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']); | |
119 | } | |
120 | ||
121 | // build index definition | |
122 | $hasFulltextIndex = false; | |
123 | foreach ($indices as $index) { | |
124 | if (!empty($indexDefinition)) $indexDefinition .= ','; | |
125 | $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']); | |
126 | if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true; | |
127 | } | |
128 | ||
129 | // create table | |
4f2c4eb8 | 130 | $sql = "CREATE TABLE `".$tableName."` ( |
11ade432 AE |
131 | ".$columnDefinition." |
132 | ".(!empty($indexDefinition) ? ',' : '')." | |
133 | ".$indexDefinition." | |
ac677ff6 | 134 | ) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"; |
11ade432 AE |
135 | $statement = $this->dbObj->prepareStatement($sql); |
136 | $statement->execute(); | |
137 | } | |
138 | ||
139 | /** | |
0fcfe5f6 | 140 | * @inheritDoc |
11ade432 AE |
141 | */ |
142 | public function dropTable($tableName) { | |
4f2c4eb8 | 143 | $sql = "DROP TABLE IF EXISTS `".$tableName."`"; |
11ade432 AE |
144 | $statement = $this->dbObj->prepareStatement($sql); |
145 | $statement->execute(); | |
146 | } | |
147 | ||
148 | /** | |
0fcfe5f6 | 149 | * @inheritDoc |
11ade432 AE |
150 | */ |
151 | public function addColumn($tableName, $columnName, $columnData) { | |
4f2c4eb8 | 152 | $sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData); |
11ade432 AE |
153 | $statement = $this->dbObj->prepareStatement($sql); |
154 | $statement->execute(); | |
155 | } | |
156 | ||
157 | /** | |
0fcfe5f6 | 158 | * @inheritDoc |
11ade432 AE |
159 | */ |
160 | public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) { | |
4f2c4eb8 | 161 | $sql = "ALTER TABLE `".$tableName."` CHANGE COLUMN `".$oldColumnName."` ".$this->buildColumnDefinition($newColumnName, $newColumnData); |
11ade432 AE |
162 | $statement = $this->dbObj->prepareStatement($sql); |
163 | $statement->execute(); | |
164 | } | |
165 | ||
166 | /** | |
0fcfe5f6 | 167 | * @inheritDoc |
11ade432 AE |
168 | */ |
169 | public function dropColumn($tableName, $columnName) { | |
4f2c4eb8 | 170 | $sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`"; |
11ade432 AE |
171 | $statement = $this->dbObj->prepareStatement($sql); |
172 | $statement->execute(); | |
173 | } | |
174 | ||
175 | /** | |
0fcfe5f6 | 176 | * @inheritDoc |
11ade432 AE |
177 | */ |
178 | public function addIndex($tableName, $indexName, $indexData) { | |
4f2c4eb8 | 179 | $sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData); |
11ade432 AE |
180 | $statement = $this->dbObj->prepareStatement($sql); |
181 | $statement->execute(); | |
182 | } | |
183 | ||
184 | /** | |
0fcfe5f6 | 185 | * @inheritDoc |
11ade432 AE |
186 | */ |
187 | public function addForeignKey($tableName, $indexName, $indexData) { | |
4f2c4eb8 | 188 | $sql = "ALTER TABLE `".$tableName."` ADD"; |
11ade432 AE |
189 | |
190 | // add index name | |
96017679 | 191 | if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`"; |
11ade432 AE |
192 | |
193 | // add columns | |
4f2c4eb8 | 194 | $sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)"; |
11ade432 AE |
195 | |
196 | // add referenced table name | |
4f2c4eb8 | 197 | $sql .= " REFERENCES `".$indexData['referencedTable']."`"; |
11ade432 AE |
198 | |
199 | // add referenced columns | |
4f2c4eb8 | 200 | $sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)"; |
11ade432 AE |
201 | |
202 | // add operation and action | |
203 | if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action']; | |
281ac362 TD |
204 | if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE']; |
205 | if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE']; | |
11ade432 AE |
206 | |
207 | $statement = $this->dbObj->prepareStatement($sql); | |
208 | $statement->execute(); | |
209 | } | |
210 | ||
211 | /** | |
0fcfe5f6 | 212 | * @inheritDoc |
11ade432 AE |
213 | */ |
214 | public function dropIndex($tableName, $indexName) { | |
4f2c4eb8 | 215 | $sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`"; |
11ade432 AE |
216 | $statement = $this->dbObj->prepareStatement($sql); |
217 | $statement->execute(); | |
218 | } | |
219 | ||
42a1e71f | 220 | /** |
6b7d6653 | 221 | * @inheritDoc |
42a1e71f | 222 | */ |
223 | public function dropPrimaryKey($tableName) { | |
224 | $sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY"; | |
225 | $statement = $this->dbObj->prepareStatement($sql); | |
226 | $statement->execute(); | |
227 | } | |
228 | ||
b6ae7d04 | 229 | /** |
0fcfe5f6 | 230 | * @inheritDoc |
b6ae7d04 MW |
231 | */ |
232 | public function dropForeignKey($tableName, $indexName) { | |
4f2c4eb8 | 233 | $sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`"; |
b6ae7d04 MW |
234 | $statement = $this->dbObj->prepareStatement($sql); |
235 | $statement->execute(); | |
236 | } | |
237 | ||
11ade432 AE |
238 | /** |
239 | * Builds a column definition for execution in a create table or alter table statement. | |
240 | * | |
241 | * @param string $columnName | |
242 | * @param array $columnData | |
71952a87 | 243 | * @return string |
11ade432 AE |
244 | */ |
245 | protected function buildColumnDefinition($columnName, $columnData) { | |
246 | // column name | |
4f2c4eb8 | 247 | $definition = "`".$columnName."`"; |
11ade432 AE |
248 | // column type |
249 | $definition .= " ".$columnData['type']; | |
250 | // column length and decimals | |
251 | if (!empty($columnData['length'])) { | |
252 | $definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")"; | |
253 | } | |
254 | // enum / set | |
255 | if ($columnData['type'] == 'enum' && !empty($columnData['values'])) { | |
256 | $definition .= "(".$columnData['values'].")"; | |
257 | } | |
258 | // not null / null | |
259 | if (!empty($columnData['notNull'])) $definition .= " NOT NULL"; | |
260 | // default | |
261 | if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default']; | |
262 | // auto_increment | |
263 | if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT"; | |
264 | // key | |
265 | if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY"; | |
266 | ||
267 | return $definition; | |
268 | } | |
269 | ||
270 | /** | |
271 | * Builds a index definition for execution in a create table or alter table statement. | |
272 | * | |
273 | * @param string $indexName | |
274 | * @param array $indexData | |
71952a87 | 275 | * @return string |
11ade432 AE |
276 | */ |
277 | protected function buildIndexDefinition($indexName, $indexData) { | |
11ade432 AE |
278 | // index type |
279 | if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY"; | |
280 | else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY"; | |
281 | else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY"; | |
282 | else $definition = "KEY"; | |
283 | ||
284 | // index name | |
4f2c4eb8 | 285 | if (!empty($indexName)) $definition .= " `".$indexName."`"; |
11ade432 | 286 | // columns |
4f2c4eb8 | 287 | $definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)"; |
11ade432 AE |
288 | |
289 | return $definition; | |
290 | } | |
291 | } |