Total Complexity | 234 |
Total Lines | 1587 |
Duplicated Lines | 0 % |
Changes | 0 |
Complex classes like DBTable often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use DBTable, and based on these observations, apply Extract Interface, too.
1 | <?php |
||
6 | class DBTable { |
||
7 | |||
8 | /** |
||
9 | * name of database table |
||
10 | */ |
||
11 | protected $table_name = ""; |
||
12 | |||
13 | /** |
||
14 | * database engine, like InnoDB |
||
15 | */ |
||
16 | protected $db_engine = ""; |
||
17 | |||
18 | /** |
||
19 | * default table charset |
||
20 | */ |
||
21 | protected $charset = "utf8"; |
||
22 | |||
23 | protected $temp_table = false; |
||
24 | |||
25 | protected $auto_increment = null; |
||
26 | |||
27 | /** |
||
28 | * list of supported database engines |
||
29 | */ |
||
30 | protected static $supported_engines = array( |
||
31 | "InnoDB", |
||
32 | "MyISAM", |
||
33 | "PERFORMANCE_SCHEMA", |
||
34 | "MRG_MYISAM", |
||
35 | "FEDERATED", |
||
36 | "CSV", |
||
37 | "MEMORY", |
||
38 | "ARCHIVE" |
||
39 | ); |
||
40 | |||
41 | /** |
||
42 | * coloums structure of database table |
||
43 | */ |
||
44 | protected $columns = array(); |
||
45 | |||
46 | /** |
||
47 | * indexes of table |
||
48 | */ |
||
49 | protected $indexes = array(); |
||
50 | |||
51 | protected $db_driver = null; |
||
52 | |||
53 | public function __construct (string $table_name, DBDriver &$db_driver) { |
||
54 | $this->table_name = $this->escape($table_name); |
||
55 | $this->db_driver = &$db_driver; |
||
56 | } |
||
57 | |||
58 | public function setEngine (string $engine_name) { |
||
59 | $found = false; |
||
60 | $founded_engine = ""; |
||
|
|||
61 | |||
62 | foreach (self::$supported_engines as $name) { |
||
63 | if (strcmp(strtolower($engine_name), strtolower($name)) == 0) { |
||
64 | //database engine is supported |
||
65 | $found = true; |
||
66 | $founded_engine = $name; |
||
67 | |||
68 | break; |
||
69 | } |
||
70 | } |
||
71 | |||
72 | if (!$found) { |
||
73 | throw new UnsupportedDBEngineException("Database engine " . $engine_name . " isnt in supported database engine list."); |
||
74 | } |
||
75 | |||
76 | //set database engine |
||
77 | $this->db_engine = $engine_name; |
||
78 | } |
||
79 | |||
80 | public function setCharset (string $charset) { |
||
82 | } |
||
83 | |||
84 | public function setTmpTable (bool $tmp_table) { |
||
85 | $this->temp_table = $tmp_table; |
||
86 | } |
||
87 | |||
88 | public function setAutoIncrementStartValue (int $value) { |
||
89 | $this->auto_increment = $value; |
||
90 | } |
||
91 | |||
92 | /** |
||
93 | * add integer column |
||
94 | * |
||
95 | * @param $name name of column |
||
96 | * @param $length length of column |
||
97 | * @param $unsigned unsigned value false / true |
||
98 | * @param $zerofill true, if values should be filled with 0, if value length isnt length of column |
||
99 | */ |
||
100 | public function addInt (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
101 | $this->columns[$name] = array( |
||
102 | 'type' => "int", |
||
103 | 'name' => $name, |
||
104 | 'not_null' => $not_null, |
||
105 | 'auto_increment' => $auto_increment, |
||
106 | 'default' => $default_value, |
||
107 | 'unsigned' => $unsigned, |
||
108 | 'zerofill' => $zerofill, |
||
109 | 'length' => $length |
||
110 | ); |
||
111 | } |
||
112 | |||
113 | /** |
||
114 | * addInteger() is an alias to addInt() |
||
115 | */ |
||
116 | public function addInteger (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
117 | $this->addInt($name, $length, $not_null, $auto_increment, $default_value, $unsigned, $zerofill); |
||
118 | } |
||
119 | |||
120 | /** |
||
121 | * add unsigned integer column |
||
122 | * |
||
123 | * @param $name name of column |
||
124 | * @param $length length of column |
||
125 | * @param $zerofill true, if values should be filled with 0, if value length isnt length of column |
||
126 | */ |
||
127 | public function addUnsignedInteger (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $zerofill = false) { |
||
128 | $this->columns[$name] = array( |
||
129 | 'type' => "int", |
||
130 | 'name' => $name, |
||
131 | 'not_null' => $not_null, |
||
132 | 'auto_increment' => $auto_increment, |
||
133 | 'default' => $default_value, |
||
134 | 'unsigned' => true, |
||
135 | 'zerofill' => $zerofill, |
||
136 | 'length' => $length |
||
137 | ); |
||
138 | } |
||
139 | |||
140 | public function addVarchar (string $name, int $length = 255, bool $not_null = false, string $default_value = null, bool $binary = false, string $charset = null) { |
||
141 | $this->columns[$name] = array( |
||
142 | 'type' => "varchar", |
||
143 | 'name' => $name, |
||
144 | 'not_null' => $not_null, |
||
145 | 'default' => $default_value, |
||
146 | 'charset' => $charset, |
||
147 | 'binary' => $binary, |
||
148 | 'length' => $length |
||
149 | ); |
||
150 | } |
||
151 | |||
152 | public function addBit (string $name, int $length = null, bool $not_null = false, string $default_value = null) { |
||
153 | $this->columns[$name] = array( |
||
154 | 'type' => "bit", |
||
155 | 'name' => $name, |
||
156 | 'not_null' => $not_null, |
||
157 | 'default' => $default_value, |
||
158 | 'length' => $length |
||
159 | ); |
||
160 | } |
||
161 | |||
162 | public function addBinary (string $name, int $length = null, bool $not_null = false, string $default_value = null) { |
||
163 | $this->columns[$name] = array( |
||
164 | 'type' => "binary", |
||
165 | 'name' => $name, |
||
166 | 'not_null' => $not_null, |
||
167 | 'default' => $default_value, |
||
168 | 'length' => $length |
||
169 | ); |
||
170 | } |
||
171 | |||
172 | public function addText (string $name, bool $not_null = false, string $default_value = null, bool $binary = false, string $charset = null) { |
||
173 | $this->columns[$name] = array( |
||
174 | 'type' => "text", |
||
175 | 'name' => $name, |
||
176 | 'not_null' => $not_null, |
||
177 | 'binary' => $binary, |
||
178 | 'charset' => $charset, |
||
179 | 'default' => $default_value |
||
180 | ); |
||
181 | } |
||
182 | |||
183 | public function addChar (string $name, int $length = 255, bool $not_null = false, string $default_value = null, bool $binary = false, string $charset = null) { |
||
184 | $this->columns[$name] = array( |
||
185 | 'type' => "char", |
||
186 | 'name' => $name, |
||
187 | 'not_null' => $not_null, |
||
188 | 'default' => $default_value, |
||
189 | 'charset' => $charset, |
||
190 | 'binary' => $binary, |
||
191 | 'length' => $length |
||
192 | ); |
||
193 | } |
||
194 | |||
195 | public function addTinyText (string $name, bool $not_null = false, string $default_value = null, bool $binary = false, string $charset = null) { |
||
196 | $this->columns[$name] = array( |
||
197 | 'type' => "tinytext", |
||
198 | 'name' => $name, |
||
199 | 'not_null' => $not_null, |
||
200 | 'binary' => $binary, |
||
201 | 'charset' => $charset, |
||
202 | 'default' => $default_value |
||
203 | ); |
||
204 | } |
||
205 | |||
206 | public function addMediumText (string $name, bool $not_null = false, string $default_value = null, bool $binary = false, string $charset = null) { |
||
207 | $this->columns[$name] = array( |
||
208 | 'type' => "mediumtext", |
||
209 | 'name' => $name, |
||
210 | 'not_null' => $not_null, |
||
211 | 'binary' => $binary, |
||
212 | 'charset' => $charset, |
||
213 | 'default' => $default_value |
||
214 | ); |
||
215 | } |
||
216 | |||
217 | public function addLongText (string $name, bool $not_null = false, string $default_value = null, bool $binary = false, string $charset = null) { |
||
218 | $this->columns[$name] = array( |
||
219 | 'type' => "longtext", |
||
220 | 'name' => $name, |
||
221 | 'not_null' => $not_null, |
||
222 | 'binary' => $binary, |
||
223 | 'charset' => $charset, |
||
224 | 'default' => $default_value |
||
225 | ); |
||
226 | } |
||
227 | |||
228 | public function addTinyInt (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
229 | $this->columns[$name] = array( |
||
230 | 'type' => "tinyint", |
||
231 | 'name' => $name, |
||
232 | 'not_null' => $not_null, |
||
233 | 'auto_increment' => $auto_increment, |
||
234 | 'default' => $default_value, |
||
235 | 'unsigned' => $unsigned, |
||
236 | 'zerofill' => $zerofill, |
||
237 | 'length' => $length |
||
238 | ); |
||
239 | } |
||
240 | |||
241 | public function addSmallInt (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
242 | $this->columns[$name] = array( |
||
243 | 'type' => "smallint", |
||
244 | 'name' => $name, |
||
245 | 'not_null' => $not_null, |
||
246 | 'auto_increment' => $auto_increment, |
||
247 | 'default' => $default_value, |
||
248 | 'unsigned' => $unsigned, |
||
249 | 'zerofill' => $zerofill, |
||
250 | 'length' => $length |
||
251 | ); |
||
252 | } |
||
253 | |||
254 | public function addMediumInt (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
255 | $this->columns[$name] = array( |
||
256 | 'type' => "mediumint", |
||
257 | 'name' => $name, |
||
258 | 'not_null' => $not_null, |
||
259 | 'auto_increment' => $auto_increment, |
||
260 | 'default' => $default_value, |
||
261 | 'unsigned' => $unsigned, |
||
262 | 'zerofill' => $zerofill, |
||
263 | 'length' => $length |
||
264 | ); |
||
265 | } |
||
266 | |||
267 | public function addBigInt (string $name, int $length = null, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
268 | $this->columns[$name] = array( |
||
269 | 'type' => "bigint", |
||
270 | 'name' => $name, |
||
271 | 'not_null' => $not_null, |
||
272 | 'auto_increment' => $auto_increment, |
||
273 | 'default' => $default_value, |
||
274 | 'unsigned' => $unsigned, |
||
275 | 'zerofill' => $zerofill, |
||
276 | 'length' => $length |
||
277 | ); |
||
278 | } |
||
279 | |||
280 | public function addDecimal (string $name, int $length = 5, int $decimals = 2, bool $not_null = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
281 | //DECIMAL doesnt support AUTO_INCREMENT |
||
282 | |||
283 | $this->columns[$name] = array( |
||
284 | 'type' => "decimal", |
||
285 | 'name' => $name, |
||
286 | 'decimals' => $decimals, |
||
287 | 'not_null' => $not_null, |
||
288 | 'default' => $default_value, |
||
289 | 'unsigned' => $unsigned, |
||
290 | 'zerofill' => $zerofill, |
||
291 | 'length' => $length |
||
292 | ); |
||
293 | } |
||
294 | |||
295 | public function addNumeric (string $name, int $length = 5, int $decimals = 2, bool $not_null = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
296 | //NUMERIC doesnt support AUTO_INCREMENT |
||
297 | |||
298 | $this->columns[$name] = array( |
||
299 | 'type' => "numeric", |
||
300 | 'name' => $name, |
||
301 | 'decimals' => $decimals, |
||
302 | 'not_null' => $not_null, |
||
303 | 'default' => $default_value, |
||
304 | 'unsigned' => $unsigned, |
||
305 | 'zerofill' => $zerofill, |
||
306 | 'length' => $length |
||
307 | ); |
||
308 | } |
||
309 | |||
310 | public function addDouble (string $name, int $length = 5, int $decimals = 2, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
311 | $this->columns[$name] = array( |
||
312 | 'type' => "double", |
||
313 | 'name' => $name, |
||
314 | 'decimals' => $decimals, |
||
315 | 'not_null' => $not_null, |
||
316 | 'auto_increment' => $auto_increment, |
||
317 | 'default' => $default_value, |
||
318 | 'unsigned' => $unsigned, |
||
319 | 'zerofill' => $zerofill, |
||
320 | 'length' => $length |
||
321 | ); |
||
322 | } |
||
323 | |||
324 | public function addFloat (string $name, int $length = 5, int $decimals = 2, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
325 | $this->columns[$name] = array( |
||
326 | 'type' => "float", |
||
327 | 'name' => $name, |
||
328 | 'decimals' => $decimals, |
||
329 | 'not_null' => $not_null, |
||
330 | 'auto_increment' => $auto_increment, |
||
331 | 'default' => $default_value, |
||
332 | 'unsigned' => $unsigned, |
||
333 | 'zerofill' => $zerofill, |
||
334 | 'length' => $length |
||
335 | ); |
||
336 | } |
||
337 | |||
338 | public function addReal (string $name, int $length = 5, int $decimals = 2, bool $not_null = false, bool $auto_increment = false, int $default_value = null, bool $unsigned = false, bool $zerofill = false) { |
||
339 | $this->columns[$name] = array( |
||
340 | 'type' => "real", |
||
341 | 'name' => $name, |
||
342 | 'decimals' => $decimals, |
||
343 | 'not_null' => $not_null, |
||
344 | 'auto_increment' => $auto_increment, |
||
345 | 'default' => $default_value, |
||
346 | 'unsigned' => $unsigned, |
||
347 | 'zerofill' => $zerofill, |
||
348 | 'length' => $length |
||
349 | ); |
||
350 | } |
||
351 | |||
352 | public function addBlob (string $name, bool $not_null = false, string $default_value = null) { |
||
353 | $this->columns[$name] = array( |
||
354 | 'type' => "blob", |
||
355 | 'name' => $name, |
||
356 | 'not_null' => $not_null, |
||
357 | 'default' => $default_value |
||
358 | ); |
||
359 | } |
||
360 | |||
361 | public function addTinyBlob (string $name, bool $not_null = false, string $default_value = null) { |
||
362 | $this->columns[$name] = array( |
||
363 | 'type' => "tinyblob", |
||
364 | 'name' => $name, |
||
365 | 'not_null' => $not_null, |
||
366 | 'default' => $default_value |
||
367 | ); |
||
368 | } |
||
369 | |||
370 | public function addMediumBlob (string $name, bool $not_null = false, string $default_value = null) { |
||
371 | $this->columns[$name] = array( |
||
372 | 'type' => "mediumblob", |
||
373 | 'name' => $name, |
||
374 | 'not_null' => $not_null, |
||
375 | 'default' => $default_value |
||
376 | ); |
||
377 | } |
||
378 | |||
379 | public function addLongBlob (string $name, bool $not_null = false, string $default_value = null) { |
||
380 | $this->columns[$name] = array( |
||
381 | 'type' => "longblob", |
||
382 | 'name' => $name, |
||
383 | 'not_null' => $not_null, |
||
384 | 'default' => $default_value |
||
385 | ); |
||
386 | } |
||
387 | |||
388 | public function addEnum (string $name, $values = array(), bool $not_null = false, string $default_value = null, string $charset = null) { |
||
389 | $this->columns[$name] = array( |
||
390 | 'type' => "enum", |
||
391 | 'name' => $name, |
||
392 | 'values' => $values, |
||
393 | 'not_null' => $not_null, |
||
394 | 'default' => $default_value, |
||
395 | 'charset' => $charset |
||
396 | ); |
||
397 | } |
||
398 | |||
399 | public function addSet (string $name, array $values = array(), bool $not_null = false, string $default_value = null, string $charset = null) { |
||
400 | $this->columns[$name] = array( |
||
401 | 'type' => "set", |
||
402 | 'name' => $name, |
||
403 | 'values' => $values, |
||
404 | 'not_null' => $not_null, |
||
405 | 'default' => $default_value, |
||
406 | 'charset' => $charset |
||
407 | ); |
||
408 | } |
||
409 | |||
410 | public function addDate (string $name, bool $not_null = false, string $default_value = null) { |
||
411 | $this->columns[$name] = array( |
||
412 | 'type' => "date", |
||
413 | 'name' => $name, |
||
414 | 'not_null' => $not_null, |
||
415 | 'default' => $default_value |
||
416 | ); |
||
417 | } |
||
418 | |||
419 | public function addTime (string $name, bool $not_null = false, string $default_value = null, int $fsp = null) { |
||
420 | $this->columns[$name] = array( |
||
421 | 'type' => "time", |
||
422 | 'name' => $name, |
||
423 | 'not_null' => $not_null, |
||
424 | 'fsp' => $fsp, |
||
425 | 'default' => $default_value |
||
426 | ); |
||
427 | } |
||
428 | |||
429 | public function addYear (string $name, bool $not_null = false, string $default_value = null) { |
||
430 | $this->columns[$name] = array( |
||
431 | 'type' => "year", |
||
432 | 'name' => $name, |
||
433 | 'not_null' => $not_null, |
||
434 | 'default' => $default_value |
||
435 | ); |
||
436 | } |
||
437 | |||
438 | public function addJSON (string $name, bool $not_null = false, string $default_value = null) { |
||
439 | $this->columns[$name] = array( |
||
440 | 'type' => "json", |
||
441 | 'name' => $name, |
||
442 | 'not_null' => $not_null, |
||
443 | 'default' => $default_value |
||
444 | ); |
||
445 | } |
||
446 | |||
447 | public function addTimestamp (string $name, bool $not_null = false, string $default_value = null, bool $on_update_current_timestamp = false, int $fsp = null) { |
||
448 | $this->columns[$name] = array( |
||
449 | 'type' => "timestamp", |
||
450 | 'name' => $name, |
||
451 | 'not_null' => $not_null, |
||
452 | 'on_update_current_timestamp' => $on_update_current_timestamp, |
||
453 | 'fsp' => $fsp, |
||
454 | 'default' => $default_value |
||
455 | ); |
||
456 | } |
||
457 | |||
458 | public function addDateTime (string $name, bool $not_null = false, string $default_value = null, int $fsp = null) { |
||
459 | $this->columns[$name] = array( |
||
460 | 'type' => "datetime", |
||
461 | 'name' => $name, |
||
462 | 'not_null' => $not_null, |
||
463 | 'fsp' => $fsp, |
||
464 | 'default' => $default_value |
||
465 | ); |
||
466 | } |
||
467 | |||
468 | public function addPrimaryKey ($columns) { |
||
469 | $this->indexes['primary'] = array( |
||
470 | 'type' => "primary", |
||
471 | 'columns' => $columns |
||
472 | ); |
||
473 | } |
||
474 | |||
475 | public function addIndex ($columns, string $index_name = null) { |
||
476 | if ($index_name == null) { |
||
477 | if (!is_array($columns)) { |
||
478 | $index_name = "ix_" . $columns; |
||
479 | } else { |
||
480 | throw new UnsupportedDataTypeException("Multi Column indexes require an name! addIndex(<columns>, <index index>)"); |
||
481 | //$index_name = "ix_" . md5(serialize($columns)) |
||
482 | } |
||
483 | } |
||
484 | |||
485 | $this->indexes[$index_name] = array( |
||
486 | 'type' => "index", |
||
487 | 'name' => $index_name, |
||
488 | 'columns' => $columns |
||
489 | ); |
||
490 | } |
||
491 | |||
492 | public function addUnique ($columns, string $index_name = null) { |
||
493 | if ($index_name == null) { |
||
494 | if (!is_array($columns)) { |
||
495 | $index_name = "uq_" . $columns; |
||
496 | } else { |
||
497 | throw new UnsupportedDataTypeException("Multi Column indexes require an name! addUnique(<columns>, <index index>)"); |
||
498 | //$index_name = "ix_" . md5(serialize($columns)) |
||
499 | } |
||
500 | } |
||
501 | |||
502 | $this->indexes[$index_name] = array( |
||
503 | 'type' => "unique", |
||
504 | 'name' => $index_name, |
||
505 | 'columns' => $columns |
||
506 | ); |
||
507 | } |
||
508 | |||
509 | public function addSpatial ($columns, string $index_name = null) { |
||
510 | if ($index_name == null) { |
||
511 | if (!is_array($columns)) { |
||
512 | $index_name = "sp_" . $columns; |
||
513 | } else { |
||
514 | throw new UnsupportedDataTypeException("Multi Column indexes require an name! addUnique(<columns>, <index index>)"); |
||
515 | //$index_name = "ix_" . md5(serialize($columns)) |
||
516 | } |
||
517 | } |
||
518 | |||
519 | $this->indexes[$index_name] = array( |
||
520 | 'type' => "spatial", |
||
521 | 'name' => $index_name, |
||
522 | 'columns' => $columns |
||
523 | ); |
||
524 | } |
||
525 | |||
526 | public function addFulltext ($columns, string $index_name = null) { |
||
527 | if ($index_name == null) { |
||
528 | if (!is_array($columns)) { |
||
529 | $index_name = "ix_" . $columns; |
||
530 | } else { |
||
531 | throw new UnsupportedDataTypeException("Multi Column indexes require an name! addFulltext(<columns>, <index index>)"); |
||
532 | //$index_name = "ix_" . md5(serialize($columns)) |
||
533 | } |
||
534 | } |
||
535 | |||
536 | $this->indexes[$index_name] = array( |
||
537 | 'type' => "fulltext", |
||
538 | 'name' => $index_name, |
||
539 | 'columns' => $columns |
||
540 | ); |
||
541 | } |
||
542 | |||
543 | public function addForeignKey ($columns, string $reference_table_name, $reference_columns, string $index_name = null, string $on_update = null, string $on_delete = null) { |
||
544 | if ($index_name == null) { |
||
545 | if (!is_array($columns)) { |
||
546 | $index_name = "ix_" . $columns; |
||
547 | } else { |
||
548 | throw new UnsupportedDataTypeException("Multi Column indexes require an name! addFulltext(<columns>, <index index>)"); |
||
549 | //$index_name = "ix_" . md5(serialize($columns)) |
||
550 | } |
||
551 | } |
||
552 | |||
553 | $this->indexes[$index_name] = array( |
||
554 | 'type' => "foreign", |
||
555 | 'name' => $index_name, |
||
556 | 'reference_table_name' => $reference_table_name, |
||
557 | 'reference_columns' => $reference_columns, |
||
558 | 'on_update' => $on_update, |
||
559 | 'on_delete' => $on_delete, |
||
560 | 'columns' => $columns |
||
561 | ); |
||
562 | } |
||
563 | |||
564 | public function generateCreateQuery () : string { |
||
565 | $tmp_str = ""; |
||
566 | |||
567 | if ($this->temp_table) { |
||
568 | $tmp_str = " TEMPORARY"; |
||
569 | } |
||
570 | |||
571 | //http://dev.mysql.com/doc/refman/5.7/en/create-table.html |
||
572 | $sql = "CREATE" . $tmp_str . " TABLE IF NOT EXISTS `{DBPRAEFIX}" . $this->escape($this->table_name) . "` (\r\n"; |
||
573 | |||
574 | //add coloums |
||
575 | $sql .= $this->generateColoumQuery(); |
||
576 | |||
577 | //add indexes |
||
578 | $sql .= $this->generateIndexQuery(); |
||
579 | |||
580 | $sql .= "\r\n)"; |
||
581 | |||
582 | if (!empty($this->db_engine)) { |
||
583 | //add database engine |
||
584 | $sql .= " ENGINE=" . $this->db_engine; |
||
585 | } |
||
586 | |||
587 | //add auto increment value |
||
588 | if ($this->auto_increment != null) { |
||
589 | $sql .= " AUTO_INCREMENT=" . (int) $this->auto_increment; |
||
590 | } |
||
591 | |||
592 | if (!empty($this->charset)) { |
||
593 | //add default charset |
||
594 | $sql .= " DEFAULT CHARSET=" . $this->charset; |
||
595 | } |
||
596 | |||
597 | $sql .= ";"; |
||
598 | |||
599 | return $sql; |
||
600 | } |
||
601 | |||
602 | protected function generateColoumQuery () : string { |
||
603 | //generate lines of coloum definitions |
||
604 | $lines = $this->getColoumLines(); |
||
605 | |||
606 | //build sql query string |
||
607 | return implode(",\r\n", $lines) . ""; |
||
608 | } |
||
609 | |||
610 | protected function generateIndexQuery () : string { |
||
611 | //check, if no index is set |
||
612 | if (count($this->indexes) == 0) { |
||
613 | return ""; |
||
614 | } |
||
615 | |||
616 | $lines = array(); |
||
617 | |||
618 | foreach ($this->indexes as $name=>$params) { |
||
619 | switch ($params['type']) { |
||
620 | case 'primary': |
||
621 | //PRIMARY KEY |
||
622 | $columns_str = ""; |
||
623 | |||
624 | if (is_array($params['columns'])) { |
||
625 | $columns = array(); |
||
626 | |||
627 | foreach ($params['columns'] as $column=>$params1) { |
||
628 | if (is_array($params1)) { |
||
629 | //column name with length |
||
630 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
631 | } else { |
||
632 | $columns[] = "`" . $params1 . "`"; |
||
633 | } |
||
634 | } |
||
635 | |||
636 | $columns_str = implode(", ", $columns); |
||
637 | } else { |
||
638 | //only 1 column |
||
639 | $columns_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
640 | } |
||
641 | |||
642 | $lines[] = "PRIMARY KEY (" . $columns_str . ")"; |
||
643 | |||
644 | break; |
||
645 | |||
646 | case 'index': |
||
647 | //INDEX |
||
648 | $columns_str = ""; |
||
649 | |||
650 | if (is_array($params['columns'])) { |
||
651 | $columns = array(); |
||
652 | |||
653 | foreach ($params['columns'] as $column=>$params1) { |
||
654 | if (is_array($params1)) { |
||
655 | //column name with length |
||
656 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
657 | } else { |
||
658 | $columns[] = "`" . $params1 . "`"; |
||
659 | } |
||
660 | } |
||
661 | |||
662 | $columns_str = implode(", ", $columns); |
||
663 | } else { |
||
664 | //only 1 column |
||
665 | $columns_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
666 | } |
||
667 | |||
668 | $lines[] = "INDEX `" . $params['name'] . "`(" . $columns_str . ")"; |
||
669 | |||
670 | break; |
||
671 | |||
672 | case 'unique': |
||
673 | //INDEX |
||
674 | $columns_str = ""; |
||
675 | |||
676 | if (is_array($params['columns'])) { |
||
677 | $columns = array(); |
||
678 | |||
679 | foreach ($params['columns'] as $column=>$params1) { |
||
680 | if (is_array($params1)) { |
||
681 | //column name with length |
||
682 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
683 | } else { |
||
684 | $columns[] = "`" . $params1 . "`"; |
||
685 | } |
||
686 | } |
||
687 | |||
688 | $columns_str = implode(", ", $columns); |
||
689 | } else { |
||
690 | //only 1 column |
||
691 | $columns_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
692 | } |
||
693 | |||
694 | if (empty($params['name'])) { |
||
695 | $lines[] = "UNIQUE (" . $columns_str . ")"; |
||
696 | } else { |
||
697 | $lines[] = "UNIQUE `" . $params['name'] . "`(" . $columns_str . ")"; |
||
698 | } |
||
699 | |||
700 | break; |
||
701 | |||
702 | case 'spatial': |
||
703 | //INDEX |
||
704 | $columns_str = ""; |
||
705 | |||
706 | if (is_array($params['columns'])) { |
||
707 | $columns = array(); |
||
708 | |||
709 | foreach ($params['columns'] as $column=>$params1) { |
||
710 | if (is_array($params1)) { |
||
711 | //column name with length |
||
712 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
713 | } else { |
||
714 | $columns[] = "`" . $params1 . "`"; |
||
715 | } |
||
716 | } |
||
717 | |||
718 | $columns_str = implode(", ", $columns); |
||
719 | } else { |
||
720 | //only 1 column |
||
721 | $columns_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
722 | } |
||
723 | |||
724 | if (empty($params['name'])) { |
||
725 | $lines[] = "SPATIAL (" . $columns_str . ")"; |
||
726 | } else { |
||
727 | $lines[] = "SPATIAL `" . $params['name'] . "`(" . $columns_str . ")"; |
||
728 | } |
||
729 | |||
730 | break; |
||
731 | |||
732 | case 'fulltext': |
||
733 | //INDEX |
||
734 | $columns_str = ""; |
||
735 | |||
736 | if (is_array($params['columns'])) { |
||
737 | $columns = array(); |
||
738 | |||
739 | foreach ($params['columns'] as $column=>$params1) { |
||
740 | if (is_array($params1)) { |
||
741 | //column name with length |
||
742 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
743 | } else { |
||
744 | $columns[] = "`" . $params1 . "`"; |
||
745 | } |
||
746 | } |
||
747 | |||
748 | $columns_str = implode(", ", $columns); |
||
749 | } else { |
||
750 | //only 1 column |
||
751 | $columns_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
752 | } |
||
753 | |||
754 | if (empty($params['name'])) { |
||
755 | $lines[] = "FULLTEXT (" . $columns_str . ")"; |
||
756 | } else { |
||
757 | $lines[] = "FULLTEXT `" . $params['name'] . "`(" . $columns_str . ")"; |
||
758 | } |
||
759 | |||
760 | break; |
||
761 | |||
762 | case 'foreign': |
||
763 | //INDEX |
||
764 | $columns_str = ""; |
||
765 | $references_str = ""; |
||
766 | |||
767 | if (is_array($params['columns'])) { |
||
768 | $columns = array(); |
||
769 | |||
770 | foreach ($params['columns'] as $column=>$params1) { |
||
771 | if (is_array($params1)) { |
||
772 | //column name with length |
||
773 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
774 | } else { |
||
775 | $columns[] = "`" . $params1 . "`"; |
||
776 | } |
||
777 | } |
||
778 | |||
779 | $columns_str = implode(", ", $columns); |
||
780 | } else { |
||
781 | //only 1 column |
||
782 | $columns_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
783 | } |
||
784 | |||
785 | if (is_array($params['columns'])) { |
||
786 | $columns = array(); |
||
787 | |||
788 | foreach ($params['reference_columns'] as $column=>$params1) { |
||
789 | if (is_array($params1)) { |
||
790 | //column name with length |
||
791 | $columns[] = "`" . $params1['column'] . "`(" . (int) $params1['length'] . ")"; |
||
792 | } else { |
||
793 | $columns[] = "`" . $params1 . "`"; |
||
794 | } |
||
795 | } |
||
796 | |||
797 | $references_str = implode(", ", $columns); |
||
798 | } else { |
||
799 | //only 1 column |
||
800 | $references_str = "`" . utf8_encode(htmlentities($params['columns'])) . "`"; |
||
801 | } |
||
802 | |||
803 | $on_update_str = ""; |
||
804 | $on_delete_str = ""; |
||
805 | |||
806 | if ($params['on_update'] != null) { |
||
807 | $on_update_str = " ON UPDATE " . strtoupper($params['on_update']); |
||
808 | } |
||
809 | |||
810 | if ($params['on_delete'] != null) { |
||
811 | $on_update_str = " ON DELETE " . strtoupper($params['on_delete']); |
||
812 | } |
||
813 | |||
814 | if (empty($params['name'])) { |
||
815 | $lines[] = "FOREIGN KEY (" . $columns_str . ") REFERENCES `" . $params['reference_table_name'] . "`(" . $references_str . ")" . $on_delete_str . $on_update_str; |
||
816 | } else { |
||
817 | $lines[] = "FOREIGN KEY `" . $params['name'] . "`(" . $columns_str . ") REFERENCES `" . $params['reference_table_name'] . "`(" . $references_str . ")" . $on_delete_str . $on_update_str; |
||
818 | } |
||
819 | |||
820 | break; |
||
821 | |||
822 | default: |
||
823 | throw new UnsupportedDataTypeException("index / key isnt supported. " . serialize($params)); |
||
824 | |||
825 | break; |
||
826 | } |
||
827 | } |
||
828 | |||
829 | return ",\r\n" . implode(",\r\n", $lines); |
||
830 | } |
||
831 | |||
832 | protected function getColoumLines () : array { |
||
833 | $lines = array(); |
||
834 | |||
835 | foreach ($this->columns as $column) { |
||
836 | $line = "`" . $column['name'] . "` "; |
||
837 | |||
838 | $length_str = ""; |
||
839 | $not_null_str = ""; |
||
840 | $default_str = ""; |
||
841 | |||
842 | if (isset($column['length']) && $column['length'] != null) { |
||
843 | $length_str = "(" . (int) $column['length'] . ")"; |
||
844 | } |
||
845 | |||
846 | if (isset($column['not_null']) && $column['not_null'] == true) { |
||
847 | $not_null_str = " NOT NULL"; |
||
848 | } |
||
849 | |||
850 | if (isset($column['default']) && $column['default'] != null) { |
||
851 | if ($column['default'] === "CURRENT_TIMESTAMP") { |
||
852 | $default_str = " DEFAULT CURRENT_TIMESTAMP"; |
||
853 | } else { |
||
854 | $default_str = " DEFAULT '" . $column['default'] . "'"; |
||
855 | } |
||
856 | } |
||
857 | |||
858 | switch ($column['type']) { |
||
859 | //INT |
||
860 | case 'int': |
||
861 | $line .= "INT" . $length_str . $not_null_str; |
||
862 | |||
863 | //add AUTO_INCREMENT if neccessary |
||
864 | if ($column['auto_increment'] == true) { |
||
865 | $line .= " AUTO_INCREMENT"; |
||
866 | } |
||
867 | |||
868 | //add DEFAULT '<value>' if neccessary |
||
869 | $line .= $default_str; |
||
870 | |||
871 | if ($column['unsigned'] == true) { |
||
872 | $line .= " UNSIGNED"; |
||
873 | } |
||
874 | |||
875 | if ($column['zerofill'] == true) { |
||
876 | $line .= " ZEROFILL"; |
||
877 | } |
||
878 | |||
879 | break; |
||
880 | |||
881 | //VARCHAR |
||
882 | case 'varchar': |
||
883 | $line .= "VARCHAR" . $length_str . $not_null_str . $default_str; |
||
884 | |||
885 | if ($column['binary'] == true) { |
||
886 | $line .= " BINARY"; |
||
887 | } |
||
888 | |||
889 | if ($column['charset'] != null) { |
||
890 | $line .= " CHARACTER SET " . $column['charset']; |
||
891 | } |
||
892 | |||
893 | break; |
||
894 | |||
895 | //TEXT |
||
896 | case 'text': |
||
897 | $line .= "TEXT" . $not_null_str . $default_str; |
||
898 | |||
899 | if ($column['binary'] == true) { |
||
900 | $line .= " BINARY"; |
||
901 | } |
||
902 | |||
903 | if ($column['charset'] != null) { |
||
904 | $line .= " CHARACTER SET " . $column['charset']; |
||
905 | } |
||
906 | |||
907 | break; |
||
908 | |||
909 | //CHAR |
||
910 | case 'char': |
||
911 | $line .= "CHAR" . $length_str . $not_null_str . $default_str; |
||
912 | |||
913 | if ($column['binary'] == true) { |
||
914 | $line .= " BINARY"; |
||
915 | } |
||
916 | |||
917 | if ($column['charset'] != null) { |
||
918 | $line .= " CHARACTER SET " . $column['charset']; |
||
919 | } |
||
920 | |||
921 | break; |
||
922 | |||
923 | //BIT |
||
924 | case 'bit': |
||
925 | $line .= "BIT" . $length_str . $not_null_str . $default_str; |
||
926 | break; |
||
927 | |||
928 | //BINARY |
||
929 | case 'binary': |
||
930 | $line .= "BINARY" . $length_str . $not_null_str . $default_str; |
||
931 | break; |
||
932 | |||
933 | //TINYINT |
||
934 | case 'tinyint': |
||
935 | $line .= "TINYINT" . $length_str . $not_null_str; |
||
936 | |||
937 | //add AUTO_INCREMENT if neccessary |
||
938 | if ($column['auto_increment'] == true) { |
||
939 | $line .= " AUTO_INCREMENT"; |
||
940 | } |
||
941 | |||
942 | //add DEFAULT '<value>' if neccessary |
||
943 | $line .= $default_str; |
||
944 | |||
945 | if ($column['unsigned'] == true) { |
||
946 | $line .= " UNSIGNED"; |
||
947 | } |
||
948 | |||
949 | if ($column['zerofill'] == true) { |
||
950 | $line .= " ZEROFILL"; |
||
951 | } |
||
952 | |||
953 | break; |
||
954 | |||
955 | //SMALLINT |
||
956 | case 'smallint': |
||
957 | $line .= "SMALLINT" . $length_str . $not_null_str; |
||
958 | |||
959 | //add AUTO_INCREMENT if neccessary |
||
960 | if ($column['auto_increment'] == true) { |
||
961 | $line .= " AUTO_INCREMENT"; |
||
962 | } |
||
963 | |||
964 | //add DEFAULT '<value>' if neccessary |
||
965 | $line .= $default_str; |
||
966 | |||
967 | if ($column['unsigned'] == true) { |
||
968 | $line .= " UNSIGNED"; |
||
969 | } |
||
970 | |||
971 | if ($column['zerofill'] == true) { |
||
972 | $line .= " ZEROFILL"; |
||
973 | } |
||
974 | |||
975 | break; |
||
976 | |||
977 | //MEDIUMINT |
||
978 | case 'mediumint': |
||
979 | $line .= "SMALLINT" . $length_str . $not_null_str; |
||
980 | |||
981 | //add AUTO_INCREMENT if neccessary |
||
982 | if ($column['auto_increment'] == true) { |
||
983 | $line .= " AUTO_INCREMENT"; |
||
984 | } |
||
985 | |||
986 | //add DEFAULT '<value>' if neccessary |
||
987 | $line .= $default_str; |
||
988 | |||
989 | if ($column['unsigned'] == true) { |
||
990 | $line .= " UNSIGNED"; |
||
991 | } |
||
992 | |||
993 | if ($column['zerofill'] == true) { |
||
994 | $line .= " ZEROFILL"; |
||
995 | } |
||
996 | |||
997 | break; |
||
998 | |||
999 | //BIGINT |
||
1000 | case 'bigint': |
||
1001 | $line .= "BIGINT" . $length_str . $not_null_str; |
||
1002 | |||
1003 | //add AUTO_INCREMENT if neccessary |
||
1004 | if ($column['auto_increment'] == true) { |
||
1005 | $line .= " AUTO_INCREMENT"; |
||
1006 | } |
||
1007 | |||
1008 | //add DEFAULT '<value>' if neccessary |
||
1009 | $line .= $default_str; |
||
1010 | |||
1011 | if ($column['unsigned'] == true) { |
||
1012 | $line .= " UNSIGNED"; |
||
1013 | } |
||
1014 | |||
1015 | if ($column['zerofill'] == true) { |
||
1016 | $line .= " ZEROFILL"; |
||
1017 | } |
||
1018 | |||
1019 | break; |
||
1020 | |||
1021 | //DECIMAL |
||
1022 | case 'decimal': |
||
1023 | $line .= "DECIMAL(" . (int) $column['length'] . ", " . (int) $column['decimals'] . ")" . $not_null_str . $default_str; |
||
1024 | |||
1025 | if ($column['unsigned'] == true) { |
||
1026 | $line .= " UNSIGNED"; |
||
1027 | } |
||
1028 | |||
1029 | if ($column['zerofill'] == true) { |
||
1030 | $line .= " ZEROFILL"; |
||
1031 | } |
||
1032 | |||
1033 | break; |
||
1034 | |||
1035 | //NUMERIC |
||
1036 | case 'numeric': |
||
1037 | $line .= "NUMERIC(" . (int) $column['length'] . ", " . (int) $column['decimals'] . ")" . $not_null_str . $default_str; |
||
1038 | |||
1039 | if ($column['unsigned'] == true) { |
||
1040 | $line .= " UNSIGNED"; |
||
1041 | } |
||
1042 | |||
1043 | if ($column['zerofill'] == true) { |
||
1044 | $line .= " ZEROFILL"; |
||
1045 | } |
||
1046 | |||
1047 | break; |
||
1048 | |||
1049 | //DOUBLE |
||
1050 | case 'double': |
||
1051 | $line .= "DOUBLE(" . (int) $column['length'] . ", " . (int) $column['decimals'] . ")" . $not_null_str; |
||
1052 | |||
1053 | //add AUTO_INCREMENT if neccessary |
||
1054 | if ($column['auto_increment'] == true) { |
||
1055 | $line .= " AUTO_INCREMENT"; |
||
1056 | } |
||
1057 | |||
1058 | //add DEFAULT '<value>' if neccessary |
||
1059 | $line .= $default_str; |
||
1060 | |||
1061 | if ($column['unsigned'] == true) { |
||
1062 | $line .= " UNSIGNED"; |
||
1063 | } |
||
1064 | |||
1065 | if ($column['zerofill'] == true) { |
||
1066 | $line .= " ZEROFILL"; |
||
1067 | } |
||
1068 | |||
1069 | break; |
||
1070 | |||
1071 | //FLOAT |
||
1072 | case 'float': |
||
1073 | $line .= "FLOAT(" . (int) $column['length'] . ", " . (int) $column['decimals'] . ")" . $not_null_str; |
||
1074 | |||
1075 | //add AUTO_INCREMENT if neccessary |
||
1076 | if ($column['auto_increment'] == true) { |
||
1077 | $line .= " AUTO_INCREMENT"; |
||
1078 | } |
||
1079 | |||
1080 | //add DEFAULT '<value>' if neccessary |
||
1081 | $line .= $default_str; |
||
1082 | |||
1083 | if ($column['unsigned'] == true) { |
||
1084 | $line .= " UNSIGNED"; |
||
1085 | } |
||
1086 | |||
1087 | if ($column['zerofill'] == true) { |
||
1088 | $line .= " ZEROFILL"; |
||
1089 | } |
||
1090 | |||
1091 | break; |
||
1092 | |||
1093 | //REAL |
||
1094 | case 'real': |
||
1095 | $line .= "REAL(" . (int) $column['length'] . ", " . (int) $column['decimals'] . ")" . $not_null_str; |
||
1096 | |||
1097 | //add AUTO_INCREMENT if neccessary |
||
1098 | if ($column['auto_increment'] == true) { |
||
1099 | $line .= " AUTO_INCREMENT"; |
||
1100 | } |
||
1101 | |||
1102 | //add DEFAULT '<value>' if neccessary |
||
1103 | $line .= $default_str; |
||
1104 | |||
1105 | if ($column['unsigned'] == true) { |
||
1106 | $line .= " UNSIGNED"; |
||
1107 | } |
||
1108 | |||
1109 | if ($column['zerofill'] == true) { |
||
1110 | $line .= " ZEROFILL"; |
||
1111 | } |
||
1112 | |||
1113 | break; |
||
1114 | |||
1115 | //BLOB |
||
1116 | case 'blob': |
||
1117 | $line .= "BLOB" . $not_null_str . $default_str; |
||
1118 | |||
1119 | break; |
||
1120 | |||
1121 | //TINYTEXT |
||
1122 | case 'tinytext': |
||
1123 | $line .= "TINYTEXT" . $not_null_str . $default_str; |
||
1124 | |||
1125 | if ($column['binary'] == true) { |
||
1126 | $line .= " BINARY"; |
||
1127 | } |
||
1128 | |||
1129 | if ($column['charset'] != null) { |
||
1130 | $line .= " CHARACTER SET " . $column['charset']; |
||
1131 | } |
||
1132 | |||
1133 | break; |
||
1134 | |||
1135 | //MEDIUMTEXT |
||
1136 | case 'mediumtext': |
||
1137 | $line .= "MEDIUMTEXT" . $not_null_str . $default_str; |
||
1138 | |||
1139 | if ($column['binary'] == true) { |
||
1140 | $line .= " BINARY"; |
||
1141 | } |
||
1142 | |||
1143 | if ($column['charset'] != null) { |
||
1144 | $line .= " CHARACTER SET " . $column['charset']; |
||
1145 | } |
||
1146 | |||
1147 | break; |
||
1148 | |||
1149 | //LONGTEXT |
||
1150 | case 'longtext': |
||
1151 | $line .= "LONGTEXT" . $not_null_str . $default_str; |
||
1152 | |||
1153 | if ($column['binary'] == true) { |
||
1154 | $line .= " BINARY"; |
||
1155 | } |
||
1156 | |||
1157 | if ($column['charset'] != null) { |
||
1158 | $line .= " CHARACTER SET " . $column['charset']; |
||
1159 | } |
||
1160 | |||
1161 | break; |
||
1162 | |||
1163 | //TINYBLOB |
||
1164 | case 'tinyblob': |
||
1165 | $line .= "TINYBLOB" . $not_null_str . $default_str; |
||
1166 | |||
1167 | break; |
||
1168 | |||
1169 | //MEDIUMBLOB |
||
1170 | case 'mediumblob': |
||
1171 | $line .= "MEDIUMBLOB" . $not_null_str . $default_str; |
||
1172 | |||
1173 | break; |
||
1174 | |||
1175 | //LONGBLOB |
||
1176 | case 'longblob': |
||
1177 | $line .= "LONGBLOB" . $not_null_str . $default_str; |
||
1178 | |||
1179 | break; |
||
1180 | |||
1181 | //ENUM |
||
1182 | case 'enum': |
||
1183 | $options = array(); |
||
1184 | |||
1185 | foreach ($column['values'] as $value) { |
||
1186 | $options[] = "'" . $value . "'"; |
||
1187 | } |
||
1188 | |||
1189 | $options_str = implode(",", $options); |
||
1190 | |||
1191 | $line .= "ENUM(" . $options_str . ")" . $not_null_str . $default_str; |
||
1192 | |||
1193 | if ($column['charset'] != null) { |
||
1194 | $line .= " CHARACTER SET " . $column['charset']; |
||
1195 | } |
||
1196 | |||
1197 | break; |
||
1198 | |||
1199 | //SET |
||
1200 | case 'set': |
||
1201 | $options = array(); |
||
1202 | |||
1203 | foreach ($column['values'] as $value) { |
||
1204 | $options[] = "'" . $value . "'"; |
||
1205 | } |
||
1206 | |||
1207 | $options_str = implode(",", $options); |
||
1208 | |||
1209 | $line .= "SET(" . $options_str . ")" . $not_null_str . $default_str; |
||
1210 | |||
1211 | if ($column['charset'] != null) { |
||
1212 | $line .= " CHARACTER SET " . $column['charset']; |
||
1213 | } |
||
1214 | |||
1215 | break; |
||
1216 | |||
1217 | //DATE |
||
1218 | case 'date': |
||
1219 | $line .= "DATE" . $not_null_str . $default_str; |
||
1220 | |||
1221 | break; |
||
1222 | |||
1223 | //TIME |
||
1224 | case 'time': |
||
1225 | $fsp_str = ""; |
||
1226 | |||
1227 | if ($column['fsp'] != null) { |
||
1228 | $fsp_str = "(" . $column['fsp'] . ")"; |
||
1229 | } |
||
1230 | |||
1231 | $line .= "TIME" . $fsp_str . $not_null_str . $default_str; |
||
1232 | |||
1233 | break; |
||
1234 | |||
1235 | //YEAR |
||
1236 | case 'year': |
||
1237 | $line .= "YEAR" . $not_null_str . $default_str; |
||
1238 | |||
1239 | break; |
||
1240 | |||
1241 | //JSON |
||
1242 | case 'json': |
||
1243 | $line .= "JSON" . $not_null_str . $default_str; |
||
1244 | |||
1245 | break; |
||
1246 | |||
1247 | //TIMESTAMP |
||
1248 | case 'timestamp': |
||
1249 | $fsp_str = ""; |
||
1250 | |||
1251 | if ($column['fsp'] != null) { |
||
1252 | $fsp_str = "(" . $column['fsp'] . ")"; |
||
1253 | } |
||
1254 | |||
1255 | $line .= "TIMESTAMP" . $fsp_str . $not_null_str . $default_str; |
||
1256 | |||
1257 | if ($column['on_update_current_timestamp'] == true) { |
||
1258 | $line .= " ON UPDATE CURRENT_TIMESTAMP"; |
||
1259 | } |
||
1260 | |||
1261 | break; |
||
1262 | |||
1263 | //DATETIME |
||
1264 | case 'datetime': |
||
1265 | $fsp_str = ""; |
||
1266 | |||
1267 | if ($column['fsp'] != null) { |
||
1268 | $fsp_str = "(" . $column['fsp'] . ")"; |
||
1269 | } |
||
1270 | |||
1271 | $line .= "DATETIME" . $fsp_str . $not_null_str . $default_str; |
||
1272 | |||
1273 | break; |
||
1274 | |||
1275 | default: |
||
1276 | throw new UnsupportedDataTypeException("MySQL data type " . $column['type'] . " isnt supported yet."); |
||
1277 | } |
||
1278 | |||
1279 | $lines[] = $line; |
||
1280 | } |
||
1281 | |||
1282 | return $lines; |
||
1283 | } |
||
1284 | |||
1285 | /** |
||
1286 | * create table structure in database, if table not exists |
||
1287 | */ |
||
1288 | public function create () { |
||
1289 | //create table |
||
1290 | $this->db_driver->execute($this->generateCreateQuery()); |
||
1291 | } |
||
1292 | |||
1293 | /** |
||
1294 | * upgrades table structure in database, or if table not exists, creates table |
||
1295 | */ |
||
1296 | public function upgrade () { |
||
1297 | if (!$this->existsTable()) { |
||
1298 | //create table structure |
||
1299 | $this->create(); |
||
1300 | } else { |
||
1301 | var_dump($this->detectTableChanges()); |
||
1302 | |||
1303 | //TODO: add code here |
||
1304 | //throw new Exception("Upgrading of tables isnt supported yet."); |
||
1305 | } |
||
1306 | } |
||
1307 | |||
1308 | public function listColumnsFromDatabase () : array { |
||
1309 | $columns = array(); |
||
1310 | |||
1311 | //get columns from database |
||
1312 | $rows = self::getTableStructure($this->table_name, $this->db_driver); |
||
1313 | |||
1314 | //iterate through rows |
||
1315 | foreach ($rows as $row) { |
||
1316 | $name = $row['Field']; |
||
1317 | $type = $row['Type']; |
||
1318 | $length = null; |
||
1319 | $decimals = null; |
||
1320 | $not_null = false; |
||
1321 | $default = null; |
||
1322 | $extra = $row['Extra']; |
||
1323 | $auto_increment = false; |
||
1324 | $on_update_current_timestamp = false; |
||
1325 | $unsigned = false; |
||
1326 | $zerofill = false; |
||
1327 | |||
1328 | $datatype = ""; |
||
1329 | $values = array(); |
||
1330 | |||
1331 | $array1 = explode("(", $type); |
||
1332 | $datatype = strtolower($array1[0]); |
||
1333 | |||
1334 | if (count($array1) > 1) { |
||
1335 | $array2 = explode(")", $array1[1]); |
||
1336 | $array3 = explode(",", $array2[0]); |
||
1337 | |||
1338 | if (strcmp($datatype, "enum") == 0 || strcmp($datatype, "set") == 0) { |
||
1339 | //ENUM or SET, options are available |
||
1340 | |||
1341 | //get options |
||
1342 | foreach ($array3 as $value) { |
||
1343 | $value = str_replace("'", "", $value); |
||
1344 | |||
1345 | $values[] = $value; |
||
1346 | } |
||
1347 | } else { |
||
1348 | if (count($array3) > 1) { |
||
1349 | //length and decimals are available |
||
1350 | $length = (int) $array3[0]; |
||
1351 | $decimals = (int) $array3[1]; |
||
1352 | } else { |
||
1353 | //only length is available |
||
1354 | $length = (int) $array3[0]; |
||
1355 | } |
||
1356 | } |
||
1357 | } else { |
||
1358 | //no length set |
||
1359 | } |
||
1360 | |||
1361 | if (strcmp($row['Null'], "YES") == 0) { |
||
1362 | $not_null = false; |
||
1363 | } else { |
||
1364 | $not_null = true; |
||
1365 | } |
||
1366 | |||
1367 | if (strcmp($row['Default'], "NULL") == 0) { |
||
1368 | $default = null; |
||
1369 | } else { |
||
1370 | $default = $row['Default']; |
||
1371 | } |
||
1372 | |||
1373 | //check for AUTO_INCREMENT |
||
1374 | if (strpos($extra, 'auto_increment') !== false) { |
||
1375 | $auto_increment = true; |
||
1376 | } |
||
1377 | |||
1378 | //check for on update CURRENT_TIMESTAMP |
||
1379 | if (strpos($extra, 'on update CURRENT_TIMESTAMP') !== false) { |
||
1380 | $on_update_current_timestamp = true; |
||
1381 | } |
||
1382 | |||
1383 | //check for unsigned and zerofill |
||
1384 | $datatype_attributes = explode(" ", $type); |
||
1385 | |||
1386 | if (count($datatype_attributes) > 1) { |
||
1387 | //there are other attributes available |
||
1388 | |||
1389 | //iterate through datatype attributes, for example: int(10) unsigned zerofill |
||
1390 | for ($i = 1; $i < count($datatype_attributes); $i++) { |
||
1391 | switch ($datatype_attributes[$i]) { |
||
1392 | case 'unsigned': |
||
1393 | $unsigned = true; |
||
1394 | break; |
||
1395 | |||
1396 | case 'zerofill': |
||
1397 | $zerofill = true; |
||
1398 | break; |
||
1399 | } |
||
1400 | } |
||
1401 | } |
||
1402 | |||
1403 | $columns[$name] = array( |
||
1404 | 'type' => $datatype, |
||
1405 | 'name' => $name, |
||
1406 | 'length' => $length, |
||
1407 | 'decimals' => $decimals, |
||
1408 | 'not_null' => $not_null, |
||
1409 | 'values' => $values,//only for enum and set |
||
1410 | 'auto_increment' => $auto_increment, |
||
1411 | 'on_update_current_timestamp' => $on_update_current_timestamp, |
||
1412 | 'unsigned' => $unsigned, |
||
1413 | 'zerofill' => $zerofill, |
||
1414 | 'default' => $default |
||
1415 | ); |
||
1416 | } |
||
1417 | |||
1418 | //return column array |
||
1419 | return $columns; |
||
1420 | } |
||
1421 | |||
1422 | protected function detectTableChanges () : array { |
||
1423 | //columns |
||
1424 | $changed_columns = array(); |
||
1425 | $added_columns = array(); |
||
1426 | $removed_columns = array(); |
||
1427 | |||
1428 | //indexes |
||
1429 | $changed_indexes = array(); |
||
1430 | $added_indexes = array(); |
||
1431 | $removed_indexes = array(); |
||
1432 | |||
1433 | |||
1434 | //compare current state with should state |
||
1435 | $current_columns = $this->listColumnsFromDatabase(); |
||
1436 | $should_columns = $this->columns; |
||
1437 | |||
1438 | //check for added columns |
||
1439 | foreach ($should_columns as $name=>$column_data) { |
||
1440 | if (!isset($current_columns[$name])) { |
||
1441 | //new column found |
||
1442 | $added_columns[$name] = $should_columns[$name]; |
||
1443 | } |
||
1444 | } |
||
1445 | |||
1446 | //check for removed columns |
||
1447 | foreach ($current_columns as $name=>$column_data) { |
||
1448 | if (!isset($should_columns[$name])) { |
||
1449 | //removed column found |
||
1450 | $removed_columns[$name] = $current_columns[$name]; |
||
1451 | } |
||
1452 | } |
||
1453 | |||
1454 | //check for changed columns |
||
1455 | foreach ($should_columns as $name=>$column_data) { |
||
1456 | //we dont have to check this column, if the column was added |
||
1457 | if (isset($added_columns[$name])) { |
||
1458 | continue; |
||
1459 | } |
||
1460 | |||
1461 | //we dont have to check this column, if the column was removed |
||
1462 | if (isset($removed_columns[$name])) { |
||
1463 | continue; |
||
1464 | } |
||
1465 | |||
1466 | //check for differences |
||
1467 | foreach ($should_columns[$name] as $key=>$value) { |
||
1468 | if (!isset($should_columns[$name][$key]) && !@is_null($should_columns[$name][$key])) { |
||
1469 | echo "Column '" . $key . "' not found.\n\n"; |
||
1470 | |||
1471 | echo "should columns:\n"; |
||
1472 | var_dump($should_columns); |
||
1473 | |||
1474 | echo "\n\ncurrent columns:\n"; |
||
1475 | var_dump($current_columns); |
||
1476 | |||
1477 | echo "\n\n"; |
||
1478 | } |
||
1479 | |||
1480 | if (strcmp($name, "charset") && @$current_columns[$name][$key] == "NULL") { |
||
1481 | continue; |
||
1482 | } |
||
1483 | |||
1484 | if (strcmp($name, "bool(false)")) { |
||
1485 | continue; |
||
1486 | } |
||
1487 | |||
1488 | if (!isset($current_columns[$name][$key]) && !@is_null($current_columns[$name][$key])) { |
||
1489 | echo "$" . "current_columns['" . $name . "']['" . $key . "'] not found:\n"; |
||
1490 | var_dump($current_columns); |
||
1491 | |||
1492 | echo "\n\nshould columns:\n"; |
||
1493 | var_dump($should_columns); |
||
1494 | } |
||
1495 | |||
1496 | if ($current_columns[$name][$key] != $value) { |
||
1497 | $changed_columns[$name] = $should_columns[$name]; |
||
1498 | } |
||
1499 | } |
||
1500 | } |
||
1501 | |||
1502 | //TODO: check for changed indexes / keys |
||
1503 | |||
1504 | //TODO: change database engine if neccessary |
||
1505 | |||
1506 | //TODO: change charset if neccessary |
||
1507 | |||
1508 | return array( |
||
1509 | 'added_columns' => $added_columns, |
||
1510 | "removed_columns" => $removed_columns, |
||
1511 | "changed_columns" => $changed_columns, |
||
1512 | "added_indexes" => $added_indexes, |
||
1513 | "removed_indexes" => $removed_indexes, |
||
1514 | "changed_indexes" => $changed_indexes |
||
1515 | ); |
||
1516 | } |
||
1517 | |||
1518 | /** |
||
1519 | * backup table |
||
1520 | * |
||
1521 | * @param $output_file file where sql query should be written in |
||
1522 | */ |
||
1523 | public function backup (string $output_file) : void { |
||
1524 | //TODO: implement this feature |
||
1525 | } |
||
1526 | |||
1527 | public function truncate () { |
||
1528 | $this->db_driver->query("TRUNCATE `" . $this->table_name . "`; "); |
||
1529 | } |
||
1530 | |||
1531 | /** |
||
1532 | * alias to truncate() |
||
1533 | */ |
||
1534 | public function cleanUp () { |
||
1535 | $this->truncate(); |
||
1536 | } |
||
1537 | |||
1538 | public function check () { |
||
1541 | } |
||
1542 | |||
1543 | public function analyze () { |
||
1544 | //check table |
||
1545 | return $this->db_driver->getRow("ANALYZE TABLE `{DBPRAEFIX}" . $this->table_name . "`; "); |
||
1546 | } |
||
1547 | |||
1548 | public function optimize () { |
||
1549 | //optimize table |
||
1550 | return $this->db_driver->listRows("OPTIMIZE TABLE `{DBPRAEFIX}" . $this->table_name . "`; "); |
||
1551 | } |
||
1552 | |||
1553 | public function flush () { |
||
1554 | //flush table |
||
1555 | $this->db_driver->query("FLUSH TABLE `{DBPRAEFIX}" . $this->table_name . "`; "); |
||
1556 | } |
||
1557 | |||
1558 | public function drop () { |
||
1561 | } |
||
1562 | |||
1563 | public function existsTable () : bool { |
||
1565 | } |
||
1566 | |||
1567 | public function escape (string $str) { |
||
1568 | return utf8_encode(htmlentities($str)); |
||
1569 | } |
||
1570 | |||
1571 | public static function listTables (DBDriver &$dbDriver) { |
||
1572 | return $dbDriver->listRows("SHOW TABLES; "); |
||
1573 | } |
||
1574 | |||
1575 | public static function getTableStructure (string $table_name, DBDriver &$dbDriver) { |
||
1578 | } |
||
1579 | |||
1580 | public static function getTableStructureByInformationSchema (string $table_name, DBDriver &$dbDriver) { |
||
1581 | //https://makandracards.com/makandra/2531-show-the-character-set-and-the-collation-of-your-mysql-tables |
||
1582 | |||
1583 | //http://stackoverflow.com/questions/8662906/getting-mysql-to-display-the-encoding-used-for-a-particular-column |
||
1584 | |||
1585 | echo "SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = '" . $dbDriver->getDatabaseName() . "' AND `TABLE_NAME` = `{DBPRAEFIX}" . $table_name . "`; "; |
||
1586 | |||
1587 | //https://dev.mysql.com/doc/refman/5.5/en/creating-tables.html |
||
1588 | return $dbDriver->listRows("SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = '" . $dbDriver->getDatabaseName() . "' AND `TABLE_NAME` = `{DBPRAEFIX}" . $table_name . "`; ", array(), true); |
||
1589 | } |
||
1590 | |||
1591 | public static function listIndexes (string $table_name, DBDriver &$dbDriver) { |
||
1593 | } |
||
1594 | |||
1595 | } |