1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace vakata\database\schema; |
4
|
|
|
|
5
|
|
|
use \vakata\database\DBException; |
6
|
|
|
|
7
|
|
|
/** |
8
|
|
|
* A table definition |
9
|
|
|
*/ |
10
|
|
|
class Table |
11
|
|
|
{ |
12
|
|
|
protected $data = []; |
13
|
|
|
/** |
14
|
|
|
* @var TableRelation[] |
15
|
|
|
*/ |
16
|
|
|
protected $relations = []; |
17
|
|
|
|
18
|
|
|
/** |
19
|
|
|
* Create a new instance |
20
|
|
|
* @param string $name the table name |
21
|
|
|
*/ |
22
|
1 |
|
public function __construct(string $name) |
23
|
|
|
{ |
24
|
1 |
|
$this->data = [ |
25
|
1 |
|
'name' => $name, |
26
|
|
|
'columns' => [], |
27
|
|
|
'primary' => [], |
28
|
1 |
|
'comment' => '' |
29
|
|
|
]; |
30
|
1 |
|
} |
31
|
|
|
/** |
32
|
|
|
* Get the table comment |
33
|
|
|
* @return string the table comment |
34
|
|
|
*/ |
35
|
|
|
public function getComment() |
36
|
|
|
{ |
37
|
|
|
return $this->data['comment']; |
38
|
|
|
} |
39
|
|
|
/** |
40
|
|
|
* Set the table comment |
41
|
|
|
* @param string $comment the table comment |
42
|
|
|
* @return $this |
43
|
|
|
*/ |
44
|
1 |
|
public function setComment(string $comment) |
45
|
|
|
{ |
46
|
1 |
|
$this->data['comment'] = $comment; |
47
|
1 |
|
return $this; |
48
|
|
|
} |
49
|
|
|
/** |
50
|
|
|
* Add a column to the definition |
51
|
|
|
* @param string $column the column name |
52
|
|
|
* @param array $definition optional array of data associated with the column |
53
|
|
|
* @return self |
54
|
|
|
*/ |
55
|
1 |
|
public function addColumn(string $column, array $definition = []) : Table |
56
|
|
|
{ |
57
|
1 |
|
$this->data['columns'][$column] = TableColumn::fromArray($column, $definition); |
58
|
1 |
|
return $this; |
59
|
|
|
} |
60
|
|
|
/** |
61
|
|
|
* Add columns to the definition |
62
|
|
|
* @param array $columns key - value pairs, where each key is a column name and each value - array of info |
63
|
|
|
* @return self |
64
|
|
|
*/ |
65
|
1 |
|
public function addColumns(array $columns) : Table |
66
|
|
|
{ |
67
|
1 |
|
foreach ($columns as $column => $definition) { |
68
|
1 |
|
if (is_numeric($column) && is_string($definition)) { |
69
|
|
|
$this->addColumn($definition, []); |
70
|
|
|
} else { |
71
|
1 |
|
$this->addColumn($column, $definition); |
72
|
|
|
} |
73
|
|
|
} |
74
|
1 |
|
return $this; |
75
|
|
|
} |
76
|
|
|
/** |
77
|
|
|
* Set the primary key |
78
|
|
|
* @param array|string $column either a single column name or an array of column names |
79
|
|
|
* @return self |
80
|
|
|
*/ |
81
|
1 |
|
public function setPrimaryKey($column) : Table |
82
|
|
|
{ |
83
|
1 |
|
if (!is_array($column)) { |
84
|
|
|
$column = [ $column ]; |
85
|
|
|
} |
86
|
1 |
|
$this->data['primary'] = $column; |
87
|
1 |
|
return $this; |
88
|
|
|
} |
89
|
|
|
/** |
90
|
|
|
* Get the table name |
91
|
|
|
* @return string the table name |
92
|
|
|
*/ |
93
|
13 |
|
public function getName() |
94
|
|
|
{ |
95
|
13 |
|
return $this->data['name']; |
96
|
|
|
} |
97
|
|
|
/** |
98
|
|
|
* Get a column definition |
99
|
|
|
* @param string $column the column name to search for |
100
|
|
|
* @return array|null the column details or `null` if the column does not exist |
101
|
|
|
*/ |
102
|
13 |
|
public function getColumn($column) |
103
|
|
|
{ |
104
|
13 |
|
return $this->data['columns'][$column] ?? null; |
105
|
|
|
} |
106
|
|
|
/** |
107
|
|
|
* Get all column names |
108
|
|
|
* @return array array of strings, where each element is a column name |
109
|
|
|
*/ |
110
|
13 |
|
public function getColumns() |
111
|
|
|
{ |
112
|
13 |
|
return array_keys($this->data['columns']); |
113
|
|
|
} |
114
|
|
|
/** |
115
|
|
|
* Get all column definitions |
116
|
|
|
* @return array key - value pairs, where each key is a column name and each value - the column data |
117
|
|
|
*/ |
118
|
2 |
|
public function getFullColumns() |
119
|
|
|
{ |
120
|
2 |
|
return $this->data['columns']; |
121
|
|
|
} |
122
|
|
|
/** |
123
|
|
|
* Get the primary key columns |
124
|
|
|
* @return array array of column names |
125
|
|
|
*/ |
126
|
13 |
|
public function getPrimaryKey() |
127
|
|
|
{ |
128
|
13 |
|
return $this->data['primary']; |
129
|
|
|
} |
130
|
|
|
/** |
131
|
|
|
* Create a relation where each record has zero or one related rows in another table |
132
|
|
|
* @param Table $toTable the related table definition |
133
|
|
|
* @param string|null $name the name of the relation (defaults to the related table name) |
134
|
|
|
* @param string|array|null $toTableColumn the remote columns pointing to the PK in the current table |
135
|
|
|
* @param string|null $sql additional where clauses to use, default to null |
136
|
|
|
* @param array|null $par parameters for the above statement, defaults to null |
137
|
|
|
* @return $this |
138
|
|
|
*/ |
139
|
|
|
public function hasOne( |
140
|
|
|
Table $toTable, |
141
|
|
|
string $name = null, |
142
|
|
|
$toTableColumn = null, |
143
|
|
|
string $sql = null, |
144
|
|
|
array $par = [] |
145
|
|
|
) : Table { |
146
|
|
|
$columns = $toTable->getColumns(); |
147
|
|
|
|
148
|
|
|
$keymap = []; |
149
|
|
|
if (!isset($toTableColumn)) { |
150
|
|
|
$toTableColumn = []; |
151
|
|
|
} |
152
|
|
|
if (!is_array($toTableColumn)) { |
153
|
|
|
$toTableColumn = [$toTableColumn]; |
154
|
|
|
} |
155
|
|
|
foreach ($this->getPrimaryKey() as $k => $pkField) { |
156
|
|
|
if (isset($toTableColumn[$pkField])) { |
157
|
|
|
$key = $toTableColumn[$pkField]; |
158
|
|
|
} elseif (isset($toTableColumn[$k])) { |
159
|
|
|
$key = $toTableColumn[$k]; |
160
|
|
|
} else { |
161
|
|
|
$key = $this->getName().'_'.$pkField; |
162
|
|
|
} |
163
|
|
|
if (!in_array($key, $columns)) { |
164
|
|
|
throw new DBException('Missing foreign key mapping'); |
165
|
|
|
} |
166
|
|
|
$keymap[$pkField] = $key; |
167
|
|
|
} |
168
|
|
|
|
169
|
|
|
if (!isset($name)) { |
170
|
|
|
$name = $toTable->getName() . '_' . implode('_', array_keys($keymap)); |
171
|
|
|
} |
172
|
|
|
$this->addRelation(new TableRelation( |
173
|
|
|
$name, |
174
|
|
|
$toTable, |
175
|
|
|
$keymap, |
176
|
|
|
false, |
177
|
|
|
null, |
178
|
|
|
null, |
179
|
|
|
$sql, |
180
|
|
|
$par |
181
|
|
|
)); |
182
|
|
|
return $this; |
183
|
|
|
} |
184
|
|
|
/** |
185
|
|
|
* Create a relation where each record has zero, one or more related rows in another table |
186
|
|
|
* @param Table $toTable the related table definition |
187
|
|
|
* @param string|null $name the name of the relation (defaults to the related table name) |
188
|
|
|
* @param string|array|null $toTableColumn the remote columns pointing to the PK in the current table |
189
|
|
|
* @param string|null $sql additional where clauses to use, default to null |
190
|
|
|
* @param array|null $par parameters for the above statement, defaults to null |
191
|
|
|
* @return $this |
192
|
|
|
*/ |
193
|
|
|
public function hasMany( |
194
|
|
|
Table $toTable, |
195
|
|
|
string $name = null, |
196
|
|
|
$toTableColumn = null, |
197
|
|
|
$sql = null, |
198
|
|
|
array $par = [] |
199
|
|
|
) : Table { |
200
|
|
|
$columns = $toTable->getColumns(); |
201
|
|
|
|
202
|
|
|
$keymap = []; |
203
|
|
|
if (!isset($toTableColumn)) { |
204
|
|
|
$toTableColumn = []; |
205
|
|
|
} |
206
|
|
|
if (!is_array($toTableColumn)) { |
207
|
|
|
$toTableColumn = [$toTableColumn]; |
208
|
|
|
} |
209
|
|
|
foreach ($this->getPrimaryKey() as $k => $pkField) { |
210
|
|
|
if (isset($toTableColumn[$pkField])) { |
211
|
|
|
$key = $toTableColumn[$pkField]; |
212
|
|
|
} elseif (isset($toTableColumn[$k])) { |
213
|
|
|
$key = $toTableColumn[$k]; |
214
|
|
|
} else { |
215
|
|
|
$key = $this->getName().'_'.$pkField; |
216
|
|
|
} |
217
|
|
|
if (!in_array($key, $columns)) { |
218
|
|
|
throw new DBException('Missing foreign key mapping'); |
219
|
|
|
} |
220
|
|
|
$keymap[$pkField] = $key; |
221
|
|
|
} |
222
|
|
|
|
223
|
|
|
if (!isset($name)) { |
224
|
|
|
$name = $toTable->getName().'_'.implode('_', array_keys($keymap)); |
225
|
|
|
} |
226
|
|
|
$this->addRelation(new TableRelation( |
227
|
|
|
$name, |
228
|
|
|
$toTable, |
229
|
|
|
$keymap, |
230
|
|
|
true, |
231
|
|
|
null, |
232
|
|
|
null, |
233
|
|
|
$sql, |
234
|
|
|
$par |
235
|
|
|
)); |
236
|
|
|
return $this; |
237
|
|
|
} |
238
|
|
|
/** |
239
|
|
|
* Create a relation where each record belongs to another row in another table |
240
|
|
|
* @param Table $toTable the related table definition |
241
|
|
|
* @param string|null $name the name of the relation (defaults to the related table name) |
242
|
|
|
* @param string|array|null $localColumn the local columns pointing to the PK of the related table |
243
|
|
|
* @param string|null $sql additional where clauses to use, default to null |
244
|
|
|
* @param array|null $par parameters for the above statement, defaults to null |
245
|
|
|
* @return $this |
246
|
|
|
*/ |
247
|
|
|
public function belongsTo( |
248
|
|
|
Table $toTable, |
249
|
|
|
string $name = null, |
250
|
|
|
$localColumn = null, |
251
|
|
|
$sql = null, |
252
|
|
|
array $par = [] |
253
|
|
|
) : Table { |
254
|
|
|
$columns = $this->getColumns(); |
255
|
|
|
|
256
|
|
|
$keymap = []; |
257
|
|
|
if (!isset($localColumn)) { |
258
|
|
|
$localColumn = []; |
259
|
|
|
} |
260
|
|
|
if (!is_array($localColumn)) { |
261
|
|
|
$localColumn = [$localColumn]; |
262
|
|
|
} |
263
|
|
|
foreach ($toTable->getPrimaryKey() as $k => $pkField) { |
264
|
|
|
if (isset($localColumn[$pkField])) { |
265
|
|
|
$key = $localColumn[$pkField]; |
266
|
|
|
} elseif (isset($localColumn[$k])) { |
267
|
|
|
$key = $localColumn[$k]; |
268
|
|
|
} else { |
269
|
|
|
$key = $toTable->getName().'_'.$pkField; |
270
|
|
|
} |
271
|
|
|
if (!in_array($key, $columns)) { |
272
|
|
|
throw new DBException('Missing foreign key mapping'); |
273
|
|
|
} |
274
|
|
|
$keymap[$key] = $pkField; |
275
|
|
|
} |
276
|
|
|
|
277
|
|
|
if (!isset($name)) { |
278
|
|
|
$name = $toTable->getName().'_'.implode('_', array_keys($keymap)); |
279
|
|
|
} |
280
|
|
|
$this->addRelation(new TableRelation( |
281
|
|
|
$name, |
282
|
|
|
$toTable, |
283
|
|
|
$keymap, |
284
|
|
|
false, |
285
|
|
|
null, |
286
|
|
|
null, |
287
|
|
|
$sql, |
288
|
|
|
$par |
289
|
|
|
)); |
290
|
|
|
return $this; |
291
|
|
|
} |
292
|
|
|
/** |
293
|
|
|
* Create a relation where each record has many linked records in another table but using a liking table |
294
|
|
|
* @param Table $toTable the related table definition |
295
|
|
|
* @param Table $pivot the pivot table definition |
296
|
|
|
* @param string|null $name the name of the relation (defaults to the related table name) |
297
|
|
|
* @param string|array|null $toTableColumn the local columns pointing to the pivot table |
298
|
|
|
* @param string|array|null $localColumn the pivot columns pointing to the related table PK |
299
|
|
|
* @return $this |
300
|
|
|
*/ |
301
|
|
|
public function manyToMany( |
302
|
|
|
Table $toTable, |
303
|
|
|
Table $pivot, |
304
|
|
|
$name = null, |
305
|
|
|
$toTableColumn = null, |
306
|
|
|
$localColumn = null |
307
|
|
|
) : Table { |
308
|
|
|
$pivotColumns = $pivot->getColumns(); |
309
|
|
|
|
310
|
|
|
$keymap = []; |
311
|
|
|
if (!isset($toTableColumn)) { |
312
|
|
|
$toTableColumn = []; |
313
|
|
|
} |
314
|
|
|
if (!is_array($toTableColumn)) { |
315
|
|
|
$toTableColumn = [$toTableColumn]; |
316
|
|
|
} |
317
|
|
|
foreach ($this->getPrimaryKey() as $k => $pkField) { |
318
|
|
|
if (isset($toTableColumn[$pkField])) { |
319
|
|
|
$key = $toTableColumn[$pkField]; |
320
|
|
|
} elseif (isset($toTableColumn[$k])) { |
321
|
|
|
$key = $toTableColumn[$k]; |
322
|
|
|
} else { |
323
|
|
|
$key = $this->getName().'_'.$pkField; |
324
|
|
|
} |
325
|
|
|
if (!in_array($key, $pivotColumns)) { |
326
|
|
|
throw new DBException('Missing foreign key mapping'); |
327
|
|
|
} |
328
|
|
|
$keymap[$pkField] = $key; |
329
|
|
|
} |
330
|
|
|
|
331
|
|
|
$pivotKeymap = []; |
332
|
|
|
if (!isset($localColumn)) { |
333
|
|
|
$localColumn = []; |
334
|
|
|
} |
335
|
|
|
if (!is_array($localColumn)) { |
336
|
|
|
$localColumn = [$localColumn]; |
337
|
|
|
} |
338
|
|
|
foreach ($toTable->getPrimaryKey() as $k => $pkField) { |
339
|
|
|
if (isset($localColumn[$pkField])) { |
340
|
|
|
$key = $localColumn[$pkField]; |
341
|
|
|
} elseif (isset($localColumn[$k])) { |
342
|
|
|
$key = $localColumn[$k]; |
343
|
|
|
} else { |
344
|
|
|
$key = $toTable->getName().'_'.$pkField; |
345
|
|
|
} |
346
|
|
|
if (!in_array($key, $pivotColumns)) { |
347
|
|
|
throw new DBException('Missing foreign key mapping'); |
348
|
|
|
} |
349
|
|
|
$pivotKeymap[$key] = $pkField; |
350
|
|
|
} |
351
|
|
|
|
352
|
|
|
if (!isset($name)) { |
353
|
|
|
$name = $toTable->getName().'_'.implode('_', array_keys($keymap)); |
354
|
|
|
} |
355
|
|
|
$this->addRelation(new TableRelation( |
356
|
|
|
$name, |
357
|
|
|
$toTable, |
358
|
|
|
$keymap, |
359
|
|
|
true, |
360
|
|
|
$pivot, |
361
|
|
|
$pivotKeymap |
362
|
|
|
)); |
363
|
|
|
return $this; |
364
|
|
|
} |
365
|
|
|
/** |
366
|
|
|
* Create an advanced relation using the internal array format |
367
|
|
|
* @param TableRelation $relation the relation definition |
368
|
|
|
* @param string|null $name optional name of the relation (defaults to the related table name) |
369
|
|
|
* @return $this |
370
|
|
|
*/ |
371
|
1 |
|
public function addRelation(TableRelation $relation, string $name = null) |
372
|
|
|
{ |
373
|
1 |
|
$name = $name ?? $relation->name; |
374
|
1 |
|
$relation->name = $name; |
375
|
1 |
|
$this->relations[$name] = $relation; |
376
|
1 |
|
return $this; |
377
|
|
|
} |
378
|
|
|
/** |
379
|
|
|
* Does the definition have related tables |
380
|
|
|
* @return boolean |
381
|
|
|
*/ |
382
|
|
|
public function hasRelations() : bool |
383
|
|
|
{ |
384
|
|
|
return count($this->relations) > 0; |
385
|
|
|
} |
386
|
|
|
/** |
387
|
|
|
* Get all relation definitions |
388
|
|
|
* @return TableRelation[] the relation definitions |
389
|
|
|
*/ |
390
|
13 |
|
public function getRelations() : array |
391
|
|
|
{ |
392
|
13 |
|
return $this->relations; |
393
|
|
|
} |
394
|
|
|
/** |
395
|
|
|
* Check if a named relation exists |
396
|
|
|
* @param string $name the name to search for |
397
|
|
|
* @return boolean does the relation exist |
398
|
|
|
*/ |
399
|
3 |
|
public function hasRelation(string $name) : bool |
400
|
|
|
{ |
401
|
3 |
|
return isset($this->relations[$name]); |
402
|
|
|
} |
403
|
|
|
/** |
404
|
|
|
* Get a relation by name |
405
|
|
|
* @param string $name the name to search for |
406
|
|
|
* @return TableRelation|null the relation definition |
407
|
|
|
*/ |
408
|
5 |
|
public function getRelation(string $name) |
409
|
|
|
{ |
410
|
5 |
|
return $this->relations[$name] ?? null; |
411
|
|
|
} |
412
|
|
|
/** |
413
|
|
|
* Rename a relation |
414
|
|
|
* @param string $name the name to search for |
415
|
|
|
* @param string $new the new name for the relation |
416
|
|
|
* @return TableRelation the relation definition |
417
|
|
|
*/ |
418
|
|
|
public function renameRelation(string $name, string $new) : array |
419
|
|
|
{ |
420
|
|
|
if (!isset($this->relations[$name])) { |
421
|
|
|
throw new DBException("Relation not found"); |
422
|
|
|
} |
423
|
|
|
if (isset($this->relations[$new])) { |
424
|
|
|
throw new DBException("A relation with that name already exists"); |
425
|
|
|
} |
426
|
|
|
$temp = $this->relations[$name]; |
427
|
|
|
$temp->name = $new; |
428
|
|
|
$this->relations[$new] = $temp; |
429
|
|
|
unset($this->relations[$name]); |
430
|
|
|
return $this->relations[$new] ?? null; |
431
|
|
|
} |
432
|
1 |
|
public function toLowerCase() |
433
|
|
|
{ |
434
|
1 |
|
$temp = []; |
435
|
1 |
|
foreach ($this->data['columns'] as $k => $v) { |
436
|
1 |
|
$temp[strtolower($k)] = $v; |
437
|
1 |
|
$v->setName(strtolower($k)); |
438
|
|
|
} |
439
|
1 |
|
$this->data['columns'] = $temp; |
440
|
1 |
|
$this->data['primary'] = array_map("strtolower", $this->data['primary']); |
441
|
1 |
|
$temp = []; |
442
|
1 |
|
foreach ($this->relations as $k => $v) { |
443
|
1 |
|
$t = []; |
444
|
1 |
|
foreach ($v->keymap as $kk => $vv) { |
445
|
1 |
|
$t[strtolower($kk)] = strtolower($vv); |
446
|
|
|
} |
447
|
1 |
|
$v->keymap = $t; |
448
|
1 |
|
if ($v->pivot_keymap !== null) { |
449
|
1 |
|
$t = []; |
450
|
1 |
|
foreach ($v->pivot_keymap as $kk => $vv) { |
451
|
1 |
|
$t[strtolower($kk)] = strtolower($vv); |
452
|
|
|
} |
453
|
1 |
|
$v->pivot_keymap = $t; |
454
|
|
|
} |
455
|
1 |
|
$temp[strtolower($k)] = $v; |
456
|
|
|
} |
457
|
1 |
|
$this->relations = $temp; |
458
|
1 |
|
return $this; |
459
|
|
|
} |
460
|
|
|
} |
461
|
|
|
|