Commit | Line | Data |
---|---|---|
11ade432 AE |
1 | <?php |
2 | namespace wcf\system\database\editor; | |
3 | use wcf\system\database\Database; | |
4 | ||
5 | /** | |
6 | * This is the database editor implementation for MySQL4.1 or higher. | |
7 | * | |
8 | * @author Marcel Werk | |
9 | * @copyright 2001-2011 WoltLab GmbH | |
10 | * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php> | |
11 | * @package com.woltlab.wcf | |
12 | * @subpackage system.database.editor | |
13 | * @category Community Framework | |
14 | */ | |
15 | class MySQLDatabaseEditor extends DatabaseEditor { | |
16 | /** | |
17 | * @see DatabaseEditor::getTableNames() | |
18 | */ | |
19 | public function getTableNames() { | |
20 | $existingTables = array(); | |
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 | /** | |
31 | * @see DatabaseEditor::getColumns() | |
32 | */ | |
33 | public function getColumns($tableName) { | |
34 | $columns = array(); | |
35 | $sql = "SHOW COLUMNS FROM ".$tableName; | |
36 | $statement = $this->dbObj->prepareStatement($sql); | |
37 | $statement->execute(); | |
38 | while ($row = $statement->fetchArray()) { | |
39 | $columns[] = $row['Field']; | |
40 | } | |
41 | return $columns; | |
42 | } | |
43 | ||
44 | /** | |
45 | * @see DatabaseEditor::getIndices() | |
46 | */ | |
47 | public function getIndices($tableName) { | |
48 | $indices = array(); | |
49 | $sql = "SHOW INDEX FROM ".$tableName; | |
50 | $statement = $this->dbObj->prepareStatement($sql); | |
51 | $statement->execute(); | |
52 | while ($row = $statement->fetchArray()) { | |
53 | $indices[] = $index['Key_name']; | |
54 | } | |
55 | ||
56 | return $indices; | |
57 | } | |
58 | ||
59 | /** | |
60 | * @see DatabaseEditor::createTable() | |
61 | */ | |
62 | public function createTable($tableName, $columns, $indices = array()) { | |
63 | $columnDefinition = $indexDefinition = ''; | |
64 | ||
65 | // build column definition | |
66 | foreach ($columns as $column) { | |
67 | if (!empty($columnDefinition)) $columnDefinition .= ','; | |
68 | $columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']); | |
69 | } | |
70 | ||
71 | // build index definition | |
72 | $hasFulltextIndex = false; | |
73 | foreach ($indices as $index) { | |
74 | if (!empty($indexDefinition)) $indexDefinition .= ','; | |
75 | $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']); | |
76 | if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true; | |
77 | } | |
78 | ||
79 | // create table | |
80 | $sql = "CREATE TABLE ".$tableName." ( | |
81 | ".$columnDefinition." | |
82 | ".(!empty($indexDefinition) ? ',' : '')." | |
83 | ".$indexDefinition." | |
84 | ) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8"; | |
85 | $statement = $this->dbObj->prepareStatement($sql); | |
86 | $statement->execute(); | |
87 | } | |
88 | ||
89 | /** | |
90 | * @see DatabaseEditor::dropTable() | |
91 | */ | |
92 | public function dropTable($tableName) { | |
93 | $sql = "DROP TABLE IF EXISTS ".$tableName; | |
94 | $statement = $this->dbObj->prepareStatement($sql); | |
95 | $statement->execute(); | |
96 | } | |
97 | ||
98 | /** | |
99 | * @see DatabaseEditor::addColumn() | |
100 | */ | |
101 | public function addColumn($tableName, $columnName, $columnData) { | |
102 | $sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData); | |
103 | $statement = $this->dbObj->prepareStatement($sql); | |
104 | $statement->execute(); | |
105 | } | |
106 | ||
107 | /** | |
108 | * @see DatabaseEditor::alterColumn() | |
109 | */ | |
110 | public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) { | |
111 | $sql = "ALTER TABLE ".$tableName." CHANGE COLUMN ".$oldColumnName." ".$this->buildColumnDefinition($newColumnName, $newColumnData); | |
112 | $statement = $this->dbObj->prepareStatement($sql); | |
113 | $statement->execute(); | |
114 | } | |
115 | ||
116 | /** | |
117 | * @see DatabaseEditor::dropColumn() | |
118 | */ | |
119 | public function dropColumn($tableName, $columnName) { | |
120 | $sql = "ALTER TABLE ".$tableName." DROP COLUMN ".$columnName; | |
121 | $statement = $this->dbObj->prepareStatement($sql); | |
122 | $statement->execute(); | |
123 | } | |
124 | ||
125 | /** | |
126 | * @see DatabaseEditor::addIndex() | |
127 | */ | |
128 | public function addIndex($tableName, $indexName, $indexData) { | |
129 | $sql = "ALTER TABLE ".$tableName." ADD ".$this->buildIndexDefinition($indexName, $indexData); | |
130 | $statement = $this->dbObj->prepareStatement($sql); | |
131 | $statement->execute(); | |
132 | } | |
133 | ||
134 | /** | |
135 | * @see DatabaseEditor::addIndex() | |
136 | */ | |
137 | public function addForeignKey($tableName, $indexName, $indexData) { | |
138 | $sql = "ALTER TABLE ".$tableName." ADD"; | |
139 | ||
140 | // add index name | |
141 | if (!empty($indexName)) $sql .= " CONSTRAINT ".$indexName; | |
142 | ||
143 | // add columns | |
144 | $sql .= " FOREIGN KEY (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")"; | |
145 | ||
146 | // add referenced table name | |
147 | $sql .= " REFERENCES ".$indexData['referencedTable']; | |
148 | ||
149 | // add referenced columns | |
150 | $sql .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['referencedColumns'])).")"; | |
151 | ||
152 | // add operation and action | |
153 | if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action']; | |
154 | ||
155 | $statement = $this->dbObj->prepareStatement($sql); | |
156 | $statement->execute(); | |
157 | } | |
158 | ||
159 | /** | |
160 | * @see DatabaseEditor::dropIndex() | |
161 | */ | |
162 | public function dropIndex($tableName, $indexName) { | |
163 | $sql = "ALTER TABLE ".$tableName." DROP INDEX ".$indexName; | |
164 | $statement = $this->dbObj->prepareStatement($sql); | |
165 | $statement->execute(); | |
166 | } | |
167 | ||
168 | /** | |
169 | * Builds a column definition for execution in a create table or alter table statement. | |
170 | * | |
171 | * @param string $columnName | |
172 | * @param array $columnData | |
173 | * @param string | |
174 | */ | |
175 | protected function buildColumnDefinition($columnName, $columnData) { | |
176 | // column name | |
177 | $definition = $columnName; | |
178 | // column type | |
179 | $definition .= " ".$columnData['type']; | |
180 | // column length and decimals | |
181 | if (!empty($columnData['length'])) { | |
182 | $definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")"; | |
183 | } | |
184 | // enum / set | |
185 | if ($columnData['type'] == 'enum' && !empty($columnData['values'])) { | |
186 | $definition .= "(".$columnData['values'].")"; | |
187 | } | |
188 | // not null / null | |
189 | if (!empty($columnData['notNull'])) $definition .= " NOT NULL"; | |
190 | // default | |
191 | if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default']; | |
192 | // auto_increment | |
193 | if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT"; | |
194 | // key | |
195 | if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY"; | |
196 | ||
197 | return $definition; | |
198 | } | |
199 | ||
200 | /** | |
201 | * Builds a index definition for execution in a create table or alter table statement. | |
202 | * | |
203 | * @param string $indexName | |
204 | * @param array $indexData | |
205 | * @param string | |
206 | */ | |
207 | protected function buildIndexDefinition($indexName, $indexData) { | |
208 | $definition = ""; | |
209 | // index type | |
210 | if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY"; | |
211 | else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY"; | |
212 | else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY"; | |
213 | else $definition = "KEY"; | |
214 | ||
215 | // index name | |
216 | if (!empty($indexName)) $definition .= " ".$indexName.""; | |
217 | // columns | |
218 | $definition .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")"; | |
219 | ||
220 | return $definition; | |
221 | } | |
222 | } | |
223 | ?> |