· 7 years ago · Nov 28, 2018, 06:42 AM
1<?php
2
3class Migrasi extends LandaDb {
4 private $db_source;
5 private $db_target;
6 private $old_table;
7 private $new_table;
8 private $columns;
9 private $update_columns;
10 private $add_columns;
11
12 public function __construct($source, $target){
13 parent::__construct();
14 $this->db_target = $target;
15 $this->db_source = $source;
16 }
17
18 public function all_table(){
19 $source = LandaDb::findAll("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='{$this->db_source}'");
20 $target = LandaDb::findAll("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='{$this->db_target}'");
21 $model = (object) [
22 'source' => $source,
23 'target' => $target
24 ];
25 return $model;
26 }
27
28 public function all_column(){
29 $tables = $this->all_table();
30 $source = array();
31 foreach ($tables->source as $key => $value) {
32 $source[$key]['TABLE_NAME'] = $value->TABLE_NAME;
33 $source[$key]['COLUMNS'] = LandaDb::findAll("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->db_source}' AND TABLE_NAME='{$value->TABLE_NAME}'");
34 }
35 $target = array();
36 foreach ($tables->target as $key => $value) {
37 $target[$key]['TABLE_NAME'] = $value->TABLE_NAME;
38 $target[$key]['COLUMNS'] = LandaDb::findAll("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->db_target}' AND TABLE_NAME='{$value->TABLE_NAME}'");
39 }
40 return (object)[
41 'source' => $source,
42 'target' => $target
43 ];
44 }
45
46 public function get_column($TABLE_NAME = NULL, $DEST = NULL){
47 if ($TABLE_NAME === NULL) {
48 return (object)[
49 'message' => "Parameter Table tidak boleh kosong",
50 'error' => TRUE
51 ];
52 }
53
54 if ($DEST == "source") {
55 return LandaDb::findAll("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->db_source}' AND TABLE_NAME='{$TABLE_NAME}'");
56 } elseif ($DEST == "target") {
57 return LandaDb::findAll("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->db_target}' AND TABLE_NAME='{$TABLE_NAME}'");
58 } else {
59 return (object)[
60 'source' => LandaDb::findAll("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->db_source}' AND TABLE_NAME='{$TABLE_NAME}'"),
61 'target' => LandaDb::findAll("SELECT COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$this->db_target}' AND TABLE_NAME='{$TABLE_NAME}'")
62 ];
63 }
64 }
65
66 public function compare_tables(){
67 $tables = $this->all_table();
68 $old_table = array();
69 foreach ($tables->source as $key => $value) {
70 foreach ($tables->target as $key2 => $value2) {
71 if ($value->TABLE_NAME == $value2->TABLE_NAME) {
72 $old_table[$key]['TABLE_NAME'] = $value2->TABLE_NAME;
73 }
74 }
75 }
76
77 $new_table = array();
78 foreach ($tables->source as $key => $value) {
79 $new_table[$key]['TABLE_NAME'] = $value->TABLE_NAME;
80 foreach ($old_table as $key2 => $value2) {
81 if ($new_table[$key]['TABLE_NAME'] == $value2['TABLE_NAME']) {
82 unset($new_table[$key]);
83 goto next;
84 }
85 }
86 next:
87 }
88
89 /** init **/
90 $this->old_table = $old_table;
91 $this->new_table = $new_table;
92 return $this;
93 }
94
95 private function old_table(){
96 $tables = $this->all_table();
97 $old_table = array();
98 foreach ($tables->source as $key => $value) {
99 foreach ($tables->target as $key2 => $value2) {
100 if ($value->TABLE_NAME == $value2->TABLE_NAME) {
101 $old_table[$key]['TABLE_NAME'] = $value2->TABLE_NAME;
102 }
103 }
104 }
105 /** init **/
106 return $old_table;
107 }
108
109 public function compare_columns(){
110 $tables = ($this->old_table == NULL) ? $this->old_table() : $this->old_table;
111
112 $columns = array();
113 foreach ($tables as $key => $value) {
114 $source_columns = LandaDb::select("COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT")
115 ->from("INFORMATION_SCHEMA.COLUMNS")
116 ->where("=", "TABLE_SCHEMA", $this->db_source)
117 ->andWhere("=", "TABLE_NAME", $value['TABLE_NAME'])
118 ->findAll();
119
120 $target_columns = LandaDb::select("COLUMN_NAME, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_COMMENT")
121 ->from("INFORMATION_SCHEMA.COLUMNS")
122 ->where("=", "TABLE_SCHEMA", $this->db_target)
123 ->andWhere("=", "TABLE_NAME", $value['TABLE_NAME'])
124 ->findAll();
125
126 $old_columns = array();
127 foreach ($source_columns as $key_source => $val_source) {
128 foreach ($target_columns as $key_target => $val_target) {
129 if ($val_source->COLUMN_NAME == $val_target->COLUMN_NAME) {
130 $old_columns[$key_source] = (array) $val_target;
131 }
132 }
133 }
134
135 $add_columns = array();
136 foreach ($source_columns as $key_source => $val_source) {
137 $add_columns[$key_source] = (array) $val_source;
138 foreach ($old_columns as $key_old => $val_old) {
139 if ($add_columns[$key_source]['COLUMN_NAME'] == $val_old['COLUMN_NAME']) {
140 unset($add_columns[$key_source]);
141 goto next;
142 }
143 }
144 next:
145 }
146
147 $data_update_column = array();
148 foreach ($old_columns as $key_old => $val_old) {
149 $data_update_column[$key_old] = (array) $val_old;
150 foreach ($add_columns as $key_add => $val_add) {
151 if ($data_update_column[$key_old]['COLUMN_NAME'] == $val_add['COLUMN_NAME']) {
152 unset($data_update_column[$key_old]);
153 goto jump;
154 }
155 }
156 jump:
157 }
158
159 $update_columns = array();
160 foreach ($source_columns as $key_source => $val_source) {
161 foreach ($data_update_column as $key_columns => $val_columns) {
162 if ($val_source->COLUMN_NAME !== $val_columns['COLUMN_NAME']) {
163 goto loop;
164 }
165 $DATA_COLUMN['COLUMN_NAME'] = ($val_source->COLUMN_NAME === $val_columns['COLUMN_NAME']) ? NULL : $val_source->COLUMN_NAME;
166 $DATA_COLUMN['COLUMN_DEFAULT'] = ($val_source->COLUMN_DEFAULT === $val_columns['COLUMN_DEFAULT']) ? NULL : $val_source->COLUMN_DEFAULT;
167 $DATA_COLUMN['IS_NULLABLE'] = ($val_source->IS_NULLABLE === $val_columns['IS_NULLABLE']) ? NULL : $val_source->IS_NULLABLE;
168 $DATA_COLUMN['COLUMN_TYPE'] = ($val_source->COLUMN_TYPE === $val_columns['COLUMN_TYPE']) ? NULL : $val_source->COLUMN_TYPE;
169 $DATA_COLUMN['COLUMN_COMMENT'] = ($val_source->COLUMN_COMMENT === $val_columns['COLUMN_COMMENT']) ? NULL : $val_source->COLUMN_COMMENT;
170
171 $COLUMN = array();
172 foreach ($DATA_COLUMN as $key_column => $val_column) {
173 if(isset($DATA_COLUMN[$key_column])){
174 $COLUMN['COLUMN_NAME'] = $val_source->COLUMN_NAME;
175 $COLUMN['COLUMN_DEFAULT'] = $val_source->COLUMN_DEFAULT;
176 $COLUMN['IS_NULLABLE'] = $val_source->IS_NULLABLE;
177 $COLUMN['COLUMN_TYPE'] = $val_source->COLUMN_TYPE;
178 $COLUMN['COLUMN_COMMENT'] = $val_source->COLUMN_COMMENT;
179 // $COLUMN[$key_column] = $DATA_COLUMN[$key_column];
180 }
181 }
182
183 if (!empty($COLUMN)) {
184 $update_columns[$key_source] = $COLUMN;
185 }
186 loop:
187 }
188 }
189
190 $columns[$key] = (array) $value;
191 $columns[$key]['update_columns'] = empty($update_columns) ? [] : $update_columns;
192 $columns[$key]['add_columns'] = empty($add_columns) ? [] : $add_columns;
193 }
194
195 $update_columns = array();
196 foreach ($columns as $key => $value) {
197 foreach ($value['update_columns'] as $key2 => $value2) {
198 $update_columns[] = array('TABLE_NAME' => $value['TABLE_NAME'], 'COLUMNS' => $value2);
199 }
200 }
201
202 $add_columns = array();
203 foreach ($columns as $key => $value) {
204 foreach ($value['add_columns'] as $key2 => $value2) {
205 $add_columns[] = array('TABLE_NAME' => $value['TABLE_NAME'], 'COLUMNS' => $value2);
206 }
207 }
208
209 $this->columns = (object)['add_columns' => $add_columns, 'update_columns' => $update_columns];
210 $this->add_columns = $add_columns;
211 $this->update_columns = $update_columns;
212 return $this;
213 }
214
215 public function create_table($params){
216 $data = array();
217 foreach ($params as $key => $value) {
218 $source_columns = $this->get_column($value['TABLE_NAME'], "source");
219 $data[$key]['TABLE_NAME'] = $value['TABLE_NAME'];
220 $data[$key]['COLUMNS'] = (array) $source_columns;
221 }
222
223 $scripts = array();
224 foreach ($data as $key => $value) {
225 $script = "CREATE TABLE IF NOT EXISTS {$value['TABLE_NAME']} ";
226 $script .= "(";
227 foreach ($value['COLUMNS'] as $key2 => $value2) {
228 $DEFAULT_NULL = ($value2->IS_NULLABLE == "YES") ? "DEFAULT NULL" : "NOT NULL";
229 $AUTO_INCREMENTS = ($value2->COLUMN_NAME === "id") ? "AUTO_INCREMENT" : "";
230 $COLUMN_COMMENT = (!empty($value2->COLUMN_COMMENT)) ? "COMMENT '$value2->COLUMN_COMMENT'" : "";
231 $COLUMN_DEFAULT = ($value2->COLUMN_DEFAULT !== NULL) ? "DEFAULT '{$value2->COLUMN_DEFAULT}'" : $DEFAULT_NULL;
232 $script .= " {$value2->COLUMN_NAME} {$value2->COLUMN_TYPE} {$COLUMN_DEFAULT} {$COLUMN_COMMENT} {$AUTO_INCREMENTS}, ";
233 }
234 $script .= "PRIMARY KEY (id)";
235 $script .= ")";
236 $scripts[] = $script;
237 }
238
239 $scripts = implode(";<br>", $scripts);
240
241 return (object)['scripts' => $scripts, 'data' => $data];
242 }
243
244 public function add_column($params){
245 $scripts = array();
246 foreach ($params as $key => $value) {
247 $script = "ALTER TABLE ";
248 $script .= "'{$value['TABLE_NAME']}'";
249 $script .= " ADD ";
250 $DEFAULT_NULL = ($value['COLUMNS']['IS_NULLABLE'] == "YES") ? "DEFAULT NULL" : "NOT NULL";
251 $AUTO_INCREMENTS = ($value['COLUMNS']['COLUMN_NAME'] === "id") ? "AUTO_INCREMENT" : "";
252 $COLUMN_COMMENT = (!empty($value['COLUMNS']['COLUMN_COMMENT'])) ? "COMMENT '{$value['COLUMNS']['COLUMN_COMMENT']}'" : "";
253 $COLUMN_DEFAULT = ($value['COLUMNS']['COLUMN_DEFAULT'] !== NULL) ? "DEFAULT '{$value['COLUMNS']['COLUMN_DEFAULT']}'" : $DEFAULT_NULL;
254 $script .= " '{$value['COLUMNS']['COLUMN_NAME']}' {$value['COLUMNS']['COLUMN_TYPE']} {$COLUMN_DEFAULT} {$COLUMN_COMMENT} {$AUTO_INCREMENTS}";
255 $scripts[] = $script;
256 }
257
258 $scripts = implode(";<br>", $scripts);
259 return (object)['scripts' => $scripts, 'data' => $params];
260 }
261
262 public function update_column($params){
263 $scripts = array();
264 foreach ($params as $key => $value) {
265 $script = "ALTER TABLE ";
266 $script .= "{$value['TABLE_NAME']}";
267 $script .= " MODIFY ";
268 $DEFAULT_NULL = ($value['COLUMNS']['IS_NULLABLE'] == "YES") ? "DEFAULT NULL" : "NOT NULL";
269 $AUTO_INCREMENTS = ($value['COLUMNS']['COLUMN_NAME'] === "id") ? "AUTO_INCREMENT" : "";
270 $COLUMN_COMMENT = (!empty($value['COLUMNS']['COLUMN_COMMENT'])) ? "COMMENT '{$value['COLUMNS']['COLUMN_COMMENT']}'" : "";
271 $COLUMN_DEFAULT = ($value['COLUMNS']['COLUMN_DEFAULT'] !== NULL) ? "DEFAULT '{$value['COLUMNS']['COLUMN_DEFAULT']}'" : $DEFAULT_NULL;
272 $script .= " {$value['COLUMNS']['COLUMN_NAME']} {$value['COLUMNS']['COLUMN_TYPE']} {$COLUMN_DEFAULT} {$COLUMN_COMMENT} {$AUTO_INCREMENTS}";
273 $scripts[] = $script;
274 }
275
276 $scripts = implode(";<br>", $scripts);
277 return (object)['scripts' => $scripts, 'data' => $params];
278 }
279
280 public function get_query(){
281 $model['columns'] = $this->columns;
282 $model['new_table'] = $this->new_table;
283
284 if ($this->old_table == NULL) {
285 $add_columns = $this->add_column($this->add_columns)->scripts;
286 $update_columns = $this->update_column($this->update_columns)->scripts;
287 $return = (object) [
288 'all' => "{$update_columns}; <br><br><br> {$add_columns};",
289 'update' => "{$update_columns};",
290 'add' => "{$add_columns};",
291 ];
292 return $return;
293 }
294
295 $create_table = $this->create_table($model['new_table'])->scripts;
296 if (!empty($model['columns'])) {
297 $add_columns = $this->add_column($this->add_columns)->scripts;
298 $update_columns = $this->update_column($this->update_columns)->scripts;
299 $return = (object) [
300 'all' => "{$create_table}; <br><br><br> {$update_columns}; <br><br><br> {$add_columns};",
301 'update_columns' => "{$update_columns};",
302 'add_columns' => "{$add_columns};",
303 'create_table' => "{$create_table};;"
304
305 ];
306 return $return;
307 } else {
308 return "{$create_table};";
309 }
310 }
311
312}
313
314?>