1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Lagdo\DbAdmin\Db\Facades; |
4
|
|
|
|
5
|
|
|
use Exception; |
6
|
|
|
use Lagdo\DbAdmin\Driver\Entity\TableEntity; |
7
|
|
|
use Lagdo\DbAdmin\Driver\Entity\TableFieldEntity; |
8
|
|
|
|
9
|
|
|
use function compact; |
10
|
|
|
use function preg_match; |
11
|
|
|
use function ksort; |
12
|
|
|
use function array_key_exists; |
13
|
|
|
use function array_map; |
14
|
|
|
use function implode; |
15
|
|
|
use function trim; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* Facade to table functions |
19
|
|
|
*/ |
20
|
|
|
class TableFacade extends AbstractFacade |
21
|
|
|
{ |
22
|
|
|
use Traits\TableTrait; |
|
|
|
|
23
|
|
|
|
24
|
|
|
/** |
25
|
|
|
* @var string |
26
|
|
|
*/ |
27
|
|
|
private $after = ''; |
28
|
|
|
|
29
|
|
|
/** |
30
|
|
|
* @var array |
31
|
|
|
*/ |
32
|
|
|
private $fields = []; |
33
|
|
|
|
34
|
|
|
/** |
35
|
|
|
* @var TableEntity |
36
|
|
|
*/ |
37
|
|
|
private $attrs; |
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* Get details about a table |
41
|
|
|
* |
42
|
|
|
* @param string $table The table name |
43
|
|
|
* |
44
|
|
|
* @return array |
45
|
|
|
*/ |
46
|
|
|
public function getTableInfo(string $table): array |
47
|
|
|
{ |
48
|
|
|
// From table.inc.php |
49
|
|
|
$status = $this->status($table); |
50
|
|
|
$name = $this->admin->tableName($status); |
51
|
|
|
$title = $this->utils->trans->lang('Table') . ': ' . ($name != '' ? $name : $this->utils->str->html($table)); |
52
|
|
|
|
53
|
|
|
$comment = $status->comment; |
54
|
|
|
|
55
|
|
|
$tabs = [ |
56
|
|
|
'fields' => $this->utils->trans->lang('Columns'), |
57
|
|
|
// 'indexes' => $this->utils->trans->lang('Indexes'), |
58
|
|
|
// 'foreign-keys' => $this->utils->trans->lang('Foreign keys'), |
59
|
|
|
// 'triggers' => $this->utils->trans->lang('Triggers'), |
60
|
|
|
]; |
61
|
|
|
if ($this->driver->isView($status)) { |
62
|
|
|
if ($this->driver->support('view_trigger')) { |
63
|
|
|
$tabs['triggers'] = $this->utils->trans->lang('Triggers'); |
64
|
|
|
} |
65
|
|
|
} else { |
66
|
|
|
if ($this->driver->support('indexes')) { |
67
|
|
|
$tabs['indexes'] = $this->utils->trans->lang('Indexes'); |
68
|
|
|
} |
69
|
|
|
if ($this->driver->supportForeignKeys($status)) { |
70
|
|
|
$tabs['foreign-keys'] = $this->utils->trans->lang('Foreign keys'); |
71
|
|
|
} |
72
|
|
|
if ($this->driver->support('trigger')) { |
73
|
|
|
$tabs['triggers'] = $this->utils->trans->lang('Triggers'); |
74
|
|
|
} |
75
|
|
|
} |
76
|
|
|
|
77
|
|
|
return compact('title', 'comment', 'tabs'); |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** |
81
|
|
|
* Get the fields of a table |
82
|
|
|
* |
83
|
|
|
* @param string $table The table name |
84
|
|
|
* |
85
|
|
|
* @return array |
86
|
|
|
* @throws Exception |
87
|
|
|
*/ |
88
|
|
|
public function getTableFields(string $table): array |
89
|
|
|
{ |
90
|
|
|
// From table.inc.php |
91
|
|
|
$fields = $this->driver->fields($table); |
92
|
|
|
if (empty($fields)) { |
93
|
|
|
throw new Exception($this->driver->error()); |
94
|
|
|
} |
95
|
|
|
|
96
|
|
|
$headers = [ |
97
|
|
|
$this->utils->trans->lang('Name'), |
98
|
|
|
$this->utils->trans->lang('Type'), |
99
|
|
|
$this->utils->trans->lang('Collation'), |
100
|
|
|
]; |
101
|
|
|
$hasComment = $this->driver->support('comment'); |
102
|
|
|
if ($hasComment) { |
103
|
|
|
$headers[] = $this->utils->trans->lang('Comment'); |
104
|
|
|
} |
105
|
|
|
|
106
|
|
|
$details = []; |
107
|
|
|
foreach ($fields as $field) { |
108
|
|
|
$detail = [ |
109
|
|
|
'name' => $this->utils->str->html($field->name), |
110
|
|
|
'type' => $this->getFieldType($field), |
111
|
|
|
'collation' => $this->utils->str->html($field->collation), |
112
|
|
|
]; |
113
|
|
|
if ($hasComment) { |
114
|
|
|
$detail['comment'] = $this->utils->str->html($field->comment); |
115
|
|
|
} |
116
|
|
|
$details[] = $detail; |
117
|
|
|
} |
118
|
|
|
|
119
|
|
|
return compact('headers', 'details'); |
120
|
|
|
} |
121
|
|
|
|
122
|
|
|
/** |
123
|
|
|
* Get the indexes of a table |
124
|
|
|
* |
125
|
|
|
* @param string $table The table name |
126
|
|
|
* |
127
|
|
|
* @return array|null |
128
|
|
|
*/ |
129
|
|
|
public function getTableIndexes(string $table): ?array |
130
|
|
|
{ |
131
|
|
|
if (!$this->driver->support('indexes')) { |
132
|
|
|
return null; |
133
|
|
|
} |
134
|
|
|
|
135
|
|
|
// From table.inc.php |
136
|
|
|
$indexes = $this->driver->indexes($table); |
137
|
|
|
|
138
|
|
|
$headers = [ |
139
|
|
|
$this->utils->trans->lang('Name'), |
140
|
|
|
$this->utils->trans->lang('Type'), |
141
|
|
|
$this->utils->trans->lang('Column'), |
142
|
|
|
]; |
143
|
|
|
|
144
|
|
|
$details = []; |
145
|
|
|
// From adminer.inc.php |
146
|
|
|
foreach ($indexes as $name => $index) { |
147
|
|
|
ksort($index->columns); // enforce correct columns order |
148
|
|
|
$print = []; |
149
|
|
|
foreach ($index->columns as $key => $val) { |
150
|
|
|
$value = '<i>' . $this->utils->str->html($val) . '</i>'; |
151
|
|
|
if (array_key_exists($key, $index->lengths)) { |
152
|
|
|
$value .= '(' . $index->lengths[$key] . ')'; |
153
|
|
|
} |
154
|
|
|
if (array_key_exists($key, $index->descs)) { |
155
|
|
|
$value .= ' DESC'; |
156
|
|
|
} |
157
|
|
|
$print[] = $value; |
158
|
|
|
} |
159
|
|
|
$details[] = [ |
160
|
|
|
'name' => $this->utils->str->html($name), |
161
|
|
|
'type' => $index->type, |
162
|
|
|
'desc' => implode(', ', $print), |
163
|
|
|
]; |
164
|
|
|
} |
165
|
|
|
|
166
|
|
|
return compact('headers', 'details'); |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
/** |
170
|
|
|
* Get the foreign keys of a table |
171
|
|
|
* |
172
|
|
|
* @param string $table The table name |
173
|
|
|
* |
174
|
|
|
* @return array|null |
175
|
|
|
*/ |
176
|
|
|
public function getTableForeignKeys(string $table): ?array |
177
|
|
|
{ |
178
|
|
|
$status = $this->status($table); |
179
|
|
|
if (!$this->driver->supportForeignKeys($status)) { |
180
|
|
|
return null; |
181
|
|
|
} |
182
|
|
|
|
183
|
|
|
$headers = [ |
184
|
|
|
$this->utils->trans->lang('Name'), |
185
|
|
|
$this->utils->trans->lang('Source'), |
186
|
|
|
$this->utils->trans->lang('Target'), |
187
|
|
|
$this->utils->trans->lang('ON DELETE'), |
188
|
|
|
$this->utils->trans->lang('ON UPDATE'), |
189
|
|
|
]; |
190
|
|
|
|
191
|
|
|
$foreignKeys = $this->driver->foreignKeys($table); |
192
|
|
|
$details = []; |
193
|
|
|
// From table.inc.php |
194
|
|
|
foreach ($foreignKeys as $name => $foreignKey) { |
195
|
|
|
$target = ''; |
196
|
|
|
if ($foreignKey->database != '') { |
197
|
|
|
$target .= '<b>' . $this->utils->str->html($foreignKey->database) . '</b>.'; |
198
|
|
|
} |
199
|
|
|
if ($foreignKey->schema != '') { |
200
|
|
|
$target .= '<b>' . $this->utils->str->html($foreignKey->schema) . '</b>.'; |
201
|
|
|
} |
202
|
|
|
$target = $this->utils->str->html($foreignKey->table) . |
203
|
|
|
'(' . implode(', ', array_map(function ($key) { |
204
|
|
|
return $this->utils->str->html($key); |
205
|
|
|
}, $foreignKey->target)) . ')'; |
206
|
|
|
$details[] = [ |
207
|
|
|
'name' => $this->utils->str->html($name), |
208
|
|
|
'source' => '<i>' . implode( |
209
|
|
|
'</i>, <i>', |
210
|
|
|
array_map(function ($key) { |
211
|
|
|
return $this->utils->str->html($key); |
212
|
|
|
}, $foreignKey->source) |
213
|
|
|
) . '</i>', |
214
|
|
|
'target' => $target, |
215
|
|
|
'onDelete' => $this->utils->str->html($foreignKey->onDelete), |
216
|
|
|
'onUpdate' => $this->utils->str->html($foreignKey->onUpdate), |
217
|
|
|
]; |
218
|
|
|
} |
219
|
|
|
|
220
|
|
|
return compact('headers', 'details'); |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
/** |
224
|
|
|
* Get the triggers of a table |
225
|
|
|
* |
226
|
|
|
* @param string $table The table name |
227
|
|
|
* |
228
|
|
|
* @return array|null |
229
|
|
|
*/ |
230
|
|
|
public function getTableTriggers(string $table): ?array |
231
|
|
|
{ |
232
|
|
|
if (!$this->driver->support('trigger')) { |
233
|
|
|
return null; |
234
|
|
|
} |
235
|
|
|
|
236
|
|
|
$headers = [ |
237
|
|
|
$this->utils->trans->lang('Name'), |
238
|
|
|
' ', |
239
|
|
|
' ', |
240
|
|
|
' ', |
241
|
|
|
]; |
242
|
|
|
|
243
|
|
|
$details = []; |
244
|
|
|
// From table.inc.php |
245
|
|
|
$triggers = $this->driver->triggers($table); |
246
|
|
|
foreach ($triggers as $name => $trigger) { |
247
|
|
|
$details[] = [ |
248
|
|
|
$this->utils->str->html($trigger->timing), |
249
|
|
|
$this->utils->str->html($trigger->event), |
250
|
|
|
$this->utils->str->html($name), |
251
|
|
|
$this->utils->trans->lang('Alter'), |
252
|
|
|
]; |
253
|
|
|
} |
254
|
|
|
|
255
|
|
|
return compact('headers', 'details'); |
256
|
|
|
} |
257
|
|
|
|
258
|
|
|
/** |
259
|
|
|
* Get required data for create/update on tables |
260
|
|
|
* |
261
|
|
|
* @param string $table The table name |
262
|
|
|
* |
263
|
|
|
* @return array |
264
|
|
|
* @throws Exception |
265
|
|
|
*/ |
266
|
|
|
public function getTableData(string $table = ''): array |
267
|
|
|
{ |
268
|
|
|
// From create.inc.php |
269
|
|
|
$status = []; |
270
|
|
|
$fields = []; |
271
|
|
|
if ($table !== '') { |
272
|
|
|
$status = $this->driver->tableStatus($table); |
273
|
|
|
if (!$status) { |
274
|
|
|
throw new Exception($this->utils->trans->lang('No tables.')); |
275
|
|
|
} |
276
|
|
|
$fields = $this->driver->fields($table); |
277
|
|
|
} |
278
|
|
|
|
279
|
|
|
$this->getForeignKeys($table); |
280
|
|
|
|
281
|
|
|
$hasAutoIncrement = false; |
282
|
|
|
foreach ($fields as &$field) { |
283
|
|
|
$hasAutoIncrement = $hasAutoIncrement || $field->autoIncrement; |
284
|
|
|
$field->hasDefault = $field->default !== null; |
285
|
|
|
if (preg_match('~^CURRENT_TIMESTAMP~i', $field->onUpdate)) { |
286
|
|
|
$field->onUpdate = 'CURRENT_TIMESTAMP'; |
287
|
|
|
} |
288
|
|
|
|
289
|
|
|
$type = $field->type; |
290
|
|
|
$field->types = $this->getFieldTypes($type); |
291
|
|
|
$field->lengthRequired = !$field->length && preg_match('~var(char|binary)$~', $type); |
292
|
|
|
$field->collationHidden = !preg_match('~(char|text|enum|set)$~', $type); |
293
|
|
|
$field->unsignedHidden = !(!$type || preg_match($this->driver->numberRegex(), $type)); |
294
|
|
|
$field->onUpdateHidden = !preg_match('~timestamp|datetime~', $type); |
295
|
|
|
$field->onDeleteHidden = !preg_match('~`~', $type); |
296
|
|
|
} |
297
|
|
|
$options = [ |
298
|
|
|
'hasAutoIncrement' => $hasAutoIncrement, |
299
|
|
|
'onUpdate' => ['CURRENT_TIMESTAMP' => 'CURRENT_TIMESTAMP'], |
300
|
|
|
'onDelete' => $this->driver->onActions(), |
301
|
|
|
]; |
302
|
|
|
|
303
|
|
|
$collations = $this->driver->collations(); |
304
|
|
|
$engines = $this->driver->engines(); |
305
|
|
|
$support = [ |
306
|
|
|
'columns' => $this->driver->support('columns'), |
307
|
|
|
'comment' => $this->driver->support('comment'), |
308
|
|
|
'partitioning' => $this->driver->support('partitioning'), |
309
|
|
|
'move_col' => $this->driver->support('move_col'), |
310
|
|
|
'drop_col' => $this->driver->support('drop_col'), |
311
|
|
|
]; |
312
|
|
|
|
313
|
|
|
$foreignKeys = $this->foreignKeys; |
314
|
|
|
$unsigned = $this->driver->unsigned(); |
315
|
|
|
// Give the var a better name |
316
|
|
|
$table = $status; |
317
|
|
|
return compact('table', 'foreignKeys', 'fields', |
318
|
|
|
'options', 'collations', 'engines', 'support', 'unsigned'); |
319
|
|
|
} |
320
|
|
|
|
321
|
|
|
/** |
322
|
|
|
* Get fields for a new column |
323
|
|
|
* |
324
|
|
|
* @return TableFieldEntity |
325
|
|
|
*/ |
326
|
|
|
public function getTableField(): TableFieldEntity |
327
|
|
|
{ |
328
|
|
|
$this->getForeignKeys(); |
329
|
|
|
$field = new TableFieldEntity(); |
330
|
|
|
$field->types = $this->getFieldTypes(); |
331
|
|
|
return $field; |
332
|
|
|
} |
333
|
|
|
|
334
|
|
|
/** |
335
|
|
|
* Create or alter a table |
336
|
|
|
* |
337
|
|
|
* @param array $values The table values |
338
|
|
|
* @param string $table The table name |
339
|
|
|
* |
340
|
|
|
* @return void |
341
|
|
|
*/ |
342
|
|
|
private function makeTableAttrs(array $values, string $table = '') |
343
|
|
|
{ |
344
|
|
|
// From create.inc.php |
345
|
|
|
if ($values['autoIncrementCol']) { |
346
|
|
|
$values['fields'][$values['autoIncrementCol']]['autoIncrement'] = true; |
347
|
|
|
} |
348
|
|
|
|
349
|
|
|
$this->attrs = new TableEntity(trim($values['name'])); |
350
|
|
|
$this->after = ' FIRST'; |
351
|
|
|
|
352
|
|
|
$this->getForeignKeys(); |
353
|
|
|
|
354
|
|
|
$this->fields = ($table !== '' ? $this->driver->fields($table) : []); |
355
|
|
|
foreach ($values['fields'] as $key => $field) { |
356
|
|
|
$orig = $field['orig']; |
357
|
|
|
$field = TableFieldEntity::make($field); |
358
|
|
|
$field->autoIncrement = ($key == $values['autoIncrementCol']); |
359
|
|
|
// Originally, deleted fields have the "field" field set to an empty string. |
360
|
|
|
// But in our implementation, the "name" field is not set. |
361
|
|
|
$this->addFieldToAttrs($field, $orig, $table); |
362
|
|
|
} |
363
|
|
|
|
364
|
|
|
// For now, partitioning is not implemented |
365
|
|
|
// $this->setPartitionAttr(); |
366
|
|
|
|
367
|
|
|
$this->setValueAttrs($values); |
368
|
|
|
} |
369
|
|
|
|
370
|
|
|
/** |
371
|
|
|
* Create a table |
372
|
|
|
* |
373
|
|
|
* @param array $values The table values |
374
|
|
|
* |
375
|
|
|
* @return array |
376
|
|
|
*/ |
377
|
|
|
public function createTable(array $values): array |
378
|
|
|
{ |
379
|
|
|
$this->makeTableAttrs($values); |
380
|
|
|
$success = $this->driver->createTable($this->attrs); |
381
|
|
|
$error = $this->driver->error(); |
382
|
|
|
$message = $this->utils->trans->lang('Table has been created.'); |
383
|
|
|
|
384
|
|
|
return compact('success', 'error', 'message'); |
385
|
|
|
} |
386
|
|
|
|
387
|
|
|
/** |
388
|
|
|
* Alter a table |
389
|
|
|
* |
390
|
|
|
* @param string $table The table name |
391
|
|
|
* @param array $values The table values |
392
|
|
|
* |
393
|
|
|
* @return array |
394
|
|
|
* @throws Exception |
395
|
|
|
*/ |
396
|
|
|
public function alterTable(string $table, array $values): array |
397
|
|
|
{ |
398
|
|
|
$this->tableStatus = $this->driver->tableStatus($table); |
399
|
|
|
if (!$this->tableStatus) { |
400
|
|
|
throw new Exception($this->utils->trans->lang('No tables.')); |
401
|
|
|
} |
402
|
|
|
|
403
|
|
|
$this->makeTableAttrs($values, $table); |
404
|
|
|
$success = $this->driver->alterTable($table, $this->attrs); |
405
|
|
|
$error = $this->driver->error(); |
406
|
|
|
$message = $this->utils->trans->lang('Table has been altered.'); |
407
|
|
|
|
408
|
|
|
return compact('success', 'error', 'message'); |
409
|
|
|
} |
410
|
|
|
|
411
|
|
|
/** |
412
|
|
|
* Drop a table |
413
|
|
|
* |
414
|
|
|
* @param string $table The table name |
415
|
|
|
* |
416
|
|
|
* @return array |
417
|
|
|
*/ |
418
|
|
|
public function dropTable(string $table): array |
419
|
|
|
{ |
420
|
|
|
$success = $this->driver->dropTables([$table]); |
421
|
|
|
$error = $this->driver->error(); |
422
|
|
|
$message = $this->utils->trans->lang('Table has been dropped.'); |
423
|
|
|
|
424
|
|
|
return compact('success', 'message', 'error'); |
425
|
|
|
} |
426
|
|
|
} |
427
|
|
|
|