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 |
7d739af0 | 9 | * @copyright 2001-2016 WoltLab GmbH |
11ade432 AE |
10 | * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php> |
11 | * @package com.woltlab.wcf | |
12 | * @subpackage system.database.editor | |
9f959ced | 13 | * @category Community Framework |
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 = []; |
14d48464 | 35 | $regex = new Regex('([a-z]+)\(([0-9]+)\)', 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 | |
058cbd6a | 44 | $columns[] = ['name' => $row['Field'], 'data' => [ |
77bb36ca | 45 | 'type' => ((empty($typeMatches)) ? $row['Type'] : $typeMatches[1]), |
46 | 'length' => ((empty($typeMatches)) ? '' : $typeMatches[2]), | |
14d48464 | 47 | 'notNull' => (($row['Null'] == 'YES') ? false : true), |
5fe135db K |
48 | 'key' => (($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : '')), |
49 | 'default' => $row['Default'], | |
50 | 'autoIncrement' => ($row['Extra'] == 'auto_increment' ? true : false) | |
058cbd6a | 51 | ]]; |
ebe8e825 | 52 | } |
bf27dc91 | 53 | |
9f959ced | 54 | return $columns; |
11ade432 AE |
55 | } |
56 | ||
57 | /** | |
0fcfe5f6 | 58 | * @inheritDoc |
11ade432 AE |
59 | */ |
60 | public function getIndices($tableName) { | |
058cbd6a | 61 | $indices = []; |
4f2c4eb8 | 62 | $sql = "SHOW INDEX FROM `".$tableName."`"; |
11ade432 AE |
63 | $statement = $this->dbObj->prepareStatement($sql); |
64 | $statement->execute(); | |
65 | while ($row = $statement->fetchArray()) { | |
9f959ced MS |
66 | $indices[] = $row['Key_name']; |
67 | } | |
68 | ||
69 | return $indices; | |
11ade432 AE |
70 | } |
71 | ||
72 | /** | |
0fcfe5f6 | 73 | * @inheritDoc |
11ade432 | 74 | */ |
058cbd6a | 75 | public function createTable($tableName, $columns, $indices = []) { |
11ade432 AE |
76 | $columnDefinition = $indexDefinition = ''; |
77 | ||
78 | // build column definition | |
79 | foreach ($columns as $column) { | |
80 | if (!empty($columnDefinition)) $columnDefinition .= ','; | |
81 | $columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']); | |
82 | } | |
83 | ||
84 | // build index definition | |
85 | $hasFulltextIndex = false; | |
86 | foreach ($indices as $index) { | |
87 | if (!empty($indexDefinition)) $indexDefinition .= ','; | |
88 | $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']); | |
89 | if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true; | |
90 | } | |
91 | ||
92 | // create table | |
4f2c4eb8 | 93 | $sql = "CREATE TABLE `".$tableName."` ( |
11ade432 AE |
94 | ".$columnDefinition." |
95 | ".(!empty($indexDefinition) ? ',' : '')." | |
96 | ".$indexDefinition." | |
288fb832 | 97 | ) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci"; |
11ade432 AE |
98 | $statement = $this->dbObj->prepareStatement($sql); |
99 | $statement->execute(); | |
100 | } | |
101 | ||
102 | /** | |
0fcfe5f6 | 103 | * @inheritDoc |
11ade432 AE |
104 | */ |
105 | public function dropTable($tableName) { | |
4f2c4eb8 | 106 | $sql = "DROP TABLE IF EXISTS `".$tableName."`"; |
11ade432 AE |
107 | $statement = $this->dbObj->prepareStatement($sql); |
108 | $statement->execute(); | |
109 | } | |
110 | ||
111 | /** | |
0fcfe5f6 | 112 | * @inheritDoc |
11ade432 AE |
113 | */ |
114 | public function addColumn($tableName, $columnName, $columnData) { | |
4f2c4eb8 | 115 | $sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData); |
11ade432 AE |
116 | $statement = $this->dbObj->prepareStatement($sql); |
117 | $statement->execute(); | |
118 | } | |
119 | ||
120 | /** | |
0fcfe5f6 | 121 | * @inheritDoc |
11ade432 AE |
122 | */ |
123 | public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) { | |
4f2c4eb8 | 124 | $sql = "ALTER TABLE `".$tableName."` CHANGE COLUMN `".$oldColumnName."` ".$this->buildColumnDefinition($newColumnName, $newColumnData); |
11ade432 AE |
125 | $statement = $this->dbObj->prepareStatement($sql); |
126 | $statement->execute(); | |
127 | } | |
128 | ||
129 | /** | |
0fcfe5f6 | 130 | * @inheritDoc |
11ade432 AE |
131 | */ |
132 | public function dropColumn($tableName, $columnName) { | |
4f2c4eb8 | 133 | $sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`"; |
11ade432 AE |
134 | $statement = $this->dbObj->prepareStatement($sql); |
135 | $statement->execute(); | |
136 | } | |
137 | ||
138 | /** | |
0fcfe5f6 | 139 | * @inheritDoc |
11ade432 AE |
140 | */ |
141 | public function addIndex($tableName, $indexName, $indexData) { | |
4f2c4eb8 | 142 | $sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData); |
11ade432 AE |
143 | $statement = $this->dbObj->prepareStatement($sql); |
144 | $statement->execute(); | |
145 | } | |
146 | ||
147 | /** | |
0fcfe5f6 | 148 | * @inheritDoc |
11ade432 AE |
149 | */ |
150 | public function addForeignKey($tableName, $indexName, $indexData) { | |
4f2c4eb8 | 151 | $sql = "ALTER TABLE `".$tableName."` ADD"; |
11ade432 AE |
152 | |
153 | // add index name | |
96017679 | 154 | if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`"; |
11ade432 AE |
155 | |
156 | // add columns | |
4f2c4eb8 | 157 | $sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)"; |
11ade432 AE |
158 | |
159 | // add referenced table name | |
4f2c4eb8 | 160 | $sql .= " REFERENCES `".$indexData['referencedTable']."`"; |
11ade432 AE |
161 | |
162 | // add referenced columns | |
4f2c4eb8 | 163 | $sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)"; |
11ade432 AE |
164 | |
165 | // add operation and action | |
166 | if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action']; | |
281ac362 TD |
167 | if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE']; |
168 | if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE']; | |
11ade432 AE |
169 | |
170 | $statement = $this->dbObj->prepareStatement($sql); | |
171 | $statement->execute(); | |
172 | } | |
173 | ||
174 | /** | |
0fcfe5f6 | 175 | * @inheritDoc |
11ade432 AE |
176 | */ |
177 | public function dropIndex($tableName, $indexName) { | |
4f2c4eb8 | 178 | $sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`"; |
11ade432 AE |
179 | $statement = $this->dbObj->prepareStatement($sql); |
180 | $statement->execute(); | |
181 | } | |
182 | ||
b6ae7d04 | 183 | /** |
0fcfe5f6 | 184 | * @inheritDoc |
b6ae7d04 MW |
185 | */ |
186 | public function dropForeignKey($tableName, $indexName) { | |
4f2c4eb8 | 187 | $sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`"; |
b6ae7d04 MW |
188 | $statement = $this->dbObj->prepareStatement($sql); |
189 | $statement->execute(); | |
190 | } | |
191 | ||
11ade432 AE |
192 | /** |
193 | * Builds a column definition for execution in a create table or alter table statement. | |
194 | * | |
195 | * @param string $columnName | |
196 | * @param array $columnData | |
71952a87 | 197 | * @return string |
11ade432 AE |
198 | */ |
199 | protected function buildColumnDefinition($columnName, $columnData) { | |
200 | // column name | |
4f2c4eb8 | 201 | $definition = "`".$columnName."`"; |
11ade432 AE |
202 | // column type |
203 | $definition .= " ".$columnData['type']; | |
204 | // column length and decimals | |
205 | if (!empty($columnData['length'])) { | |
206 | $definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")"; | |
207 | } | |
208 | // enum / set | |
209 | if ($columnData['type'] == 'enum' && !empty($columnData['values'])) { | |
210 | $definition .= "(".$columnData['values'].")"; | |
211 | } | |
212 | // not null / null | |
213 | if (!empty($columnData['notNull'])) $definition .= " NOT NULL"; | |
214 | // default | |
215 | if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default']; | |
216 | // auto_increment | |
217 | if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT"; | |
218 | // key | |
219 | if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY"; | |
220 | ||
221 | return $definition; | |
222 | } | |
223 | ||
224 | /** | |
225 | * Builds a index definition for execution in a create table or alter table statement. | |
226 | * | |
227 | * @param string $indexName | |
228 | * @param array $indexData | |
71952a87 | 229 | * @return string |
11ade432 AE |
230 | */ |
231 | protected function buildIndexDefinition($indexName, $indexData) { | |
11ade432 AE |
232 | // index type |
233 | if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY"; | |
234 | else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY"; | |
235 | else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY"; | |
236 | else $definition = "KEY"; | |
237 | ||
238 | // index name | |
4f2c4eb8 | 239 | if (!empty($indexName)) $definition .= " `".$indexName."`"; |
11ade432 | 240 | // columns |
4f2c4eb8 | 241 | $definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)"; |
11ade432 AE |
242 | |
243 | return $definition; | |
244 | } | |
245 | } |