|
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
|
|
|
|