1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Lagdo\DbAdmin\Driver\PgSql\Db; |
4
|
|
|
|
5
|
|
|
use Lagdo\DbAdmin\Driver\Entity\TableEntity; |
6
|
|
|
use Lagdo\DbAdmin\Driver\Entity\IndexEntity; |
7
|
|
|
use Lagdo\DbAdmin\Driver\Entity\ForeignKeyEntity; |
8
|
|
|
use Lagdo\DbAdmin\Driver\Entity\TriggerEntity; |
9
|
|
|
|
10
|
|
|
use Lagdo\DbAdmin\Driver\Db\ConnectionInterface; |
11
|
|
|
|
12
|
|
|
use Lagdo\DbAdmin\Driver\Db\Table as AbstractTable; |
13
|
|
|
|
14
|
|
|
class Table extends AbstractTable |
15
|
|
|
{ |
16
|
|
|
use TableTrait; |
17
|
|
|
|
18
|
|
|
/** |
19
|
|
|
* @inheritDoc |
20
|
|
|
*/ |
21
|
|
|
public function tableStatus(string $table, bool $fast = false) |
22
|
|
|
{ |
23
|
|
|
$rows = $this->queryStatus($table); |
24
|
|
|
if (!($row = reset($rows))) { |
25
|
|
|
return null; |
26
|
|
|
} |
27
|
|
|
return $this->makeStatus($row); |
28
|
|
|
} |
29
|
|
|
|
30
|
|
|
/** |
31
|
|
|
* @inheritDoc |
32
|
|
|
*/ |
33
|
|
|
public function tableStatuses(bool $fast = false) |
34
|
|
|
{ |
35
|
|
|
$tables = []; |
36
|
|
|
$rows = $this->queryStatus(); |
37
|
|
|
foreach ($rows as $row) { |
38
|
|
|
$tables[$row["Name"]] = $this->makeStatus($row); |
39
|
|
|
} |
40
|
|
|
return $tables; |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* @inheritDoc |
45
|
|
|
*/ |
46
|
|
|
public function tableNames() |
47
|
|
|
{ |
48
|
|
|
$tables = []; |
49
|
|
|
$rows = $this->queryStatus(); |
50
|
|
|
foreach ($rows as $row) { |
51
|
|
|
$tables[] = $row["Name"]; |
52
|
|
|
} |
53
|
|
|
return $tables; |
54
|
|
|
} |
55
|
|
|
|
56
|
|
|
/** |
57
|
|
|
* @inheritDoc |
58
|
|
|
*/ |
59
|
|
|
public function isView(TableEntity $tableStatus) |
60
|
|
|
{ |
61
|
|
|
return in_array($tableStatus->engine, ["view", "materialized view"]); |
62
|
|
|
} |
63
|
|
|
|
64
|
|
|
/** |
65
|
|
|
* @inheritDoc |
66
|
|
|
*/ |
67
|
|
|
public function supportForeignKeys(TableEntity $tableStatus) |
68
|
|
|
{ |
69
|
|
|
return true; |
70
|
|
|
} |
71
|
|
|
|
72
|
|
|
/** |
73
|
|
|
* @inheritDoc |
74
|
|
|
*/ |
75
|
|
|
public function referencableTables(string $table) |
76
|
|
|
{ |
77
|
|
|
$fields = []; // table_name => [field] |
78
|
|
|
foreach ($this->tableNames() as $tableName) { |
79
|
|
|
if ($tableName === $table) { |
80
|
|
|
continue; |
81
|
|
|
} |
82
|
|
|
foreach ($this->fields($tableName) as $field) { |
83
|
|
|
if ($field->primary) { |
84
|
|
|
if (!isset($fields[$tableName])) { |
85
|
|
|
$fields[$tableName] = $field; |
86
|
|
|
} else { |
87
|
|
|
// No multi column primary key |
88
|
|
|
$fields[$tableName] = null; |
89
|
|
|
} |
90
|
|
|
} |
91
|
|
|
} |
92
|
|
|
} |
93
|
|
|
return array_filter($fields, function($field) { |
94
|
|
|
return $field !== null; |
95
|
|
|
}); |
96
|
|
|
} |
97
|
|
|
|
98
|
|
|
/** |
99
|
|
|
* @inheritDoc |
100
|
|
|
*/ |
101
|
|
|
public function fields(string $table) |
102
|
|
|
{ |
103
|
|
|
$fields = []; |
104
|
|
|
|
105
|
|
|
// Primary keys |
106
|
|
|
$primaryKeyColumns = $this->primaryKeyColumns($table); |
107
|
|
|
|
108
|
|
|
$identity_column = $this->driver->minVersion(10) ? 'a.attidentity' : '0'; |
109
|
|
|
$query = "SELECT a.attname AS field, format_type(a.atttypid, a.atttypmod) AS full_type, " . |
110
|
|
|
"pg_get_expr(d.adbin, d.adrelid) AS default, a.attnotnull::int, " . |
111
|
|
|
"col_description(c.oid, a.attnum) AS comment, $identity_column AS identity FROM pg_class c " . |
112
|
|
|
"JOIN pg_namespace n ON c.relnamespace = n.oid JOIN pg_attribute a ON c.oid = a.attrelid " . |
113
|
|
|
"LEFT JOIN pg_attrdef d ON c.oid = d.adrelid AND a.attnum = d.adnum WHERE c.relname = " . |
114
|
|
|
$this->driver->quote($table) . |
115
|
|
|
" AND n.nspname = current_schema() AND NOT a.attisdropped AND a.attnum > 0 ORDER BY a.attnum"; |
116
|
|
|
foreach ($this->driver->rows($query) as $row) |
117
|
|
|
{ |
118
|
|
|
$fields[$row["field"]] = $this->makeFieldEntity($row, $primaryKeyColumns); |
119
|
|
|
} |
120
|
|
|
return $fields; |
121
|
|
|
} |
122
|
|
|
|
123
|
|
|
/** |
124
|
|
|
* @inheritDoc |
125
|
|
|
*/ |
126
|
|
|
public function indexes(string $table) |
127
|
|
|
{ |
128
|
|
|
$indexes = []; |
129
|
|
|
$table_oid = $this->driver->result("SELECT oid FROM pg_class WHERE " . |
130
|
|
|
"relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = current_schema()) " . |
131
|
|
|
"AND relname = " . $this->driver->quote($table)); |
132
|
|
|
$columns = $this->driver->keyValues("SELECT attnum, attname FROM pg_attribute WHERE " . |
133
|
|
|
"attrelid = $table_oid AND attnum > 0"); |
134
|
|
|
$query = "SELECT relname, indisunique::int, indisprimary::int, indkey, indoption, " . |
135
|
|
|
"(indpred IS NOT NULL)::int as indispartial FROM pg_index i, pg_class ci " . |
136
|
|
|
"WHERE i.indrelid = $table_oid AND ci.oid = i.indexrelid"; |
137
|
|
|
foreach ($this->driver->rows($query) as $row) |
138
|
|
|
{ |
139
|
|
|
$indexes[$row["relname"]] = $this->makeIndexEntity($row, $columns); |
140
|
|
|
} |
141
|
|
|
return $indexes; |
142
|
|
|
} |
143
|
|
|
|
144
|
|
|
/** |
145
|
|
|
* @inheritDoc |
146
|
|
|
*/ |
147
|
|
|
public function foreignKeys(string $table) |
148
|
|
|
{ |
149
|
|
|
$foreignKeys = []; |
150
|
|
|
$query = "SELECT conname, condeferrable::int AS deferrable, pg_get_constraintdef(oid) " . |
151
|
|
|
"AS definition FROM pg_constraint WHERE conrelid = (SELECT pc.oid FROM pg_class AS pc " . |
152
|
|
|
"INNER JOIN pg_namespace AS pn ON (pn.oid = pc.relnamespace) WHERE pc.relname = " . |
153
|
|
|
$this->driver->quote($table) . |
154
|
|
|
" AND pn.nspname = current_schema()) AND contype = 'f'::char ORDER BY conkey, conname"; |
155
|
|
|
foreach ($this->driver->rows($query) as $row) { |
156
|
|
|
$foreignKey = $this->makeForeignKeyEntity($row); |
157
|
|
|
if ($foreignKey !== null) { |
158
|
|
|
$foreignKeys[$row['conname']] = $foreignKey; |
159
|
|
|
} |
160
|
|
|
} |
161
|
|
|
return $foreignKeys; |
162
|
|
|
} |
163
|
|
|
|
164
|
|
|
/** |
165
|
|
|
* @inheritDoc |
166
|
|
|
*/ |
167
|
|
|
public function trigger(string $name, string $table = '') |
168
|
|
|
{ |
169
|
|
|
if ($name == '') { |
170
|
|
|
return new TriggerEntity('', '', 'EXECUTE PROCEDURE ()'); |
171
|
|
|
} |
172
|
|
|
if ($table === '') { |
173
|
|
|
$table = $this->utils->input->getTable(); |
174
|
|
|
} |
175
|
|
|
$query = 'SELECT t.trigger_name AS "Trigger", t.action_timing AS "Timing", ' . |
176
|
|
|
'(SELECT STRING_AGG(event_manipulation, \' OR \') FROM information_schema.triggers ' . |
177
|
|
|
'WHERE event_object_table = t.event_object_table AND trigger_name = t.trigger_name ) AS "Events", ' . |
178
|
|
|
't.event_manipulation AS "Event", \'FOR EACH \' || t.action_orientation AS "Type", ' . |
179
|
|
|
't.action_statement AS "Statement" FROM information_schema.triggers t WHERE t.event_object_table = ' . |
180
|
|
|
$this->driver->quote($table) . ' AND t.trigger_name = ' . $this->driver->quote($name); |
181
|
|
|
$rows = $this->driver->rows($query); |
182
|
|
|
if (!($row = reset($rows))) { |
183
|
|
|
return null; |
184
|
|
|
} |
185
|
|
|
return new TriggerEntity($row['Timing'], $row['Event'], $row['Statement'], '', $row['Trigger']); |
186
|
|
|
} |
187
|
|
|
|
188
|
|
|
/** |
189
|
|
|
* @inheritDoc |
190
|
|
|
*/ |
191
|
|
|
public function triggers(string $table) |
192
|
|
|
{ |
193
|
|
|
$triggers = []; |
194
|
|
|
$query = "SELECT * FROM information_schema.triggers WHERE trigger_schema = current_schema() " . |
195
|
|
|
"AND event_object_table = " . $this->driver->quote($table); |
196
|
|
|
foreach ($this->driver->rows($query) as $row) { |
197
|
|
|
$triggers[$row["trigger_name"]] = new TriggerEntity($row["action_timing"], |
198
|
|
|
$row["event_manipulation"], '', '', $row["trigger_name"]); |
199
|
|
|
} |
200
|
|
|
return $triggers; |
201
|
|
|
} |
202
|
|
|
|
203
|
|
|
/** |
204
|
|
|
* @inheritDoc |
205
|
|
|
*/ |
206
|
|
|
public function triggerOptions() |
207
|
|
|
{ |
208
|
|
|
return [ |
209
|
|
|
"Timing" => ["BEFORE", "AFTER"], |
210
|
|
|
"Event" => ["INSERT", "UPDATE", "DELETE"], |
211
|
|
|
"Type" => ["FOR EACH ROW", "FOR EACH STATEMENT"], |
212
|
|
|
]; |
213
|
|
|
} |
214
|
|
|
|
215
|
|
|
/** |
216
|
|
|
* @inheritDoc |
217
|
|
|
*/ |
218
|
|
|
public function tableHelp(string $name) |
219
|
|
|
{ |
220
|
|
|
$links = [ |
221
|
|
|
"information_schema" => "infoschema", |
222
|
|
|
"pg_catalog" => "catalog", |
223
|
|
|
]; |
224
|
|
|
$link = $links[$this->driver->schema()]; |
225
|
|
|
if ($link) { |
226
|
|
|
return "$link-" . str_replace("_", "-", $name) . ".html"; |
227
|
|
|
} |
228
|
|
|
} |
229
|
|
|
} |
230
|
|
|
|