1
|
|
|
<?php |
2
|
|
|
/* |
3
|
|
|
* This file is part of the Pomm package. |
4
|
|
|
* |
5
|
|
|
* (c) 2014 - 2015 Grégoire HUBERT <[email protected]> |
6
|
|
|
* |
7
|
|
|
* For the full copyright and license information, please view the LICENSE |
8
|
|
|
* file that was distributed with this source code. |
9
|
|
|
*/ |
10
|
|
|
namespace PommProject\Foundation\Inspector; |
11
|
|
|
|
12
|
|
|
use PommProject\Foundation\Exception\FoundationException; |
13
|
|
|
use PommProject\Foundation\ConvertedResultIterator; |
14
|
|
|
use PommProject\Foundation\Client\Client; |
15
|
|
|
use PommProject\Foundation\Where; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* RelationInspector |
19
|
|
|
* |
20
|
|
|
* Relation inspector. |
21
|
|
|
* |
22
|
|
|
* @package Pomm |
23
|
|
|
* @copyright 2015 Grégoire HUBERT |
24
|
|
|
* @author Grégoire HUBERT |
25
|
|
|
* @license X11 {@link http://opensource.org/licenses/mit-license.php} |
26
|
|
|
* |
27
|
|
|
* @see Client |
28
|
|
|
*/ |
29
|
|
|
class RelationInspector extends Client |
30
|
|
|
{ |
31
|
|
|
use InspectorTrait; |
32
|
|
|
|
33
|
|
|
/** |
34
|
|
|
* getClientType |
35
|
|
|
* |
36
|
|
|
* @see ClientInterface |
37
|
|
|
*/ |
38
|
|
|
public function getClientType() |
39
|
|
|
{ |
40
|
|
|
return 'inspector'; |
41
|
|
|
} |
42
|
|
|
|
43
|
|
|
/** |
44
|
|
|
* getClientIdentifier |
45
|
|
|
* |
46
|
|
|
* @see ClientInterface |
47
|
|
|
*/ |
48
|
|
|
public function getClientIdentifier() |
49
|
|
|
{ |
50
|
|
|
return 'relation'; |
51
|
|
|
} |
52
|
|
|
|
53
|
|
|
/** |
54
|
|
|
* getRelations |
55
|
|
|
* |
56
|
|
|
* Return a list of relations. Be aware that if no conditions is given, it |
57
|
|
|
* will also return system tables and views. |
58
|
|
|
* |
59
|
|
|
* @param Where $where |
60
|
|
|
* @return ConvertedResultIterator |
61
|
|
|
*/ |
62
|
|
View Code Duplication |
public function getRelations(Where $where = null) |
|
|
|
|
63
|
|
|
{ |
64
|
|
|
$condition = Where::createWhereIn('relkind', ['r', 'v', 'm', 'f']) |
65
|
|
|
->andWhere($where) |
66
|
|
|
; |
67
|
|
|
|
68
|
|
|
$sql = <<<SQL |
69
|
|
|
select |
70
|
|
|
cl.relname as "name", |
71
|
|
|
case |
72
|
|
|
when cl.relkind = 'r' then 'table' |
73
|
|
|
when cl.relkind = 'v' then 'view' |
74
|
|
|
when cl.relkind = 'm' then 'materialized view' |
75
|
|
|
when cl.relkind = 'f' then 'foreign table' |
76
|
|
|
else 'other' |
77
|
|
|
end as "type", |
78
|
|
|
n.nspname as "schema", |
79
|
|
|
cl.oid as "oid", |
80
|
|
|
o.rolname as "owner", |
81
|
|
|
des.description as "comment" |
82
|
|
|
from |
83
|
|
|
pg_catalog.pg_class cl |
84
|
|
|
left join pg_catalog.pg_description des on |
85
|
|
|
cl.oid = des.objoid and des.objsubid = 0 |
86
|
|
|
join pg_catalog.pg_roles o on cl.relowner = o.oid |
87
|
|
|
join pg_catalog.pg_namespace n on cl.relnamespace = n.oid |
88
|
|
|
where {condition} |
89
|
|
|
order by name asc |
90
|
|
|
SQL; |
91
|
|
|
|
92
|
|
|
return $this->executeSql($sql, $condition); |
93
|
|
|
} |
94
|
|
|
|
95
|
|
|
/** |
96
|
|
|
* getRelationsInSchema |
97
|
|
|
* |
98
|
|
|
* Return the list of relations contained in the given schema. |
99
|
|
|
* |
100
|
|
|
* @param string $schema_name |
101
|
|
|
* @param Where $where |
102
|
|
|
* @return ConvertedResultIterator |
103
|
|
|
*/ |
104
|
|
|
public function getRelationsInSchema($schema_name, Where $where = null) |
105
|
|
|
{ |
106
|
|
|
$where = Where::create("n.nspname ~* $*", [$schema_name]) |
107
|
|
|
->andWhere($where) |
108
|
|
|
; |
109
|
|
|
|
110
|
|
|
return $this->getRelations($where); |
111
|
|
|
} |
112
|
|
|
|
113
|
|
|
/** |
114
|
|
|
* getDatabaseRelations |
115
|
|
|
* |
116
|
|
|
* Return non system relations in the database. |
117
|
|
|
* |
118
|
|
|
* @param Where $where |
119
|
|
|
* @return ConvertedResultIterator |
120
|
|
|
*/ |
121
|
|
View Code Duplication |
public function getDatabaseRelations(Where $where = null) |
|
|
|
|
122
|
|
|
{ |
123
|
|
|
$where = Where::create('n.nspname !~* $*', ['^pg_']) |
124
|
|
|
->andWhere('n.nspname != $*', ['information_schema']) |
125
|
|
|
->andWhere($where) |
126
|
|
|
; |
127
|
|
|
|
128
|
|
|
return $this->getRelations($where); |
129
|
|
|
} |
130
|
|
|
|
131
|
|
|
/** |
132
|
|
|
* getTableFieldInformationWhere |
133
|
|
|
* |
134
|
|
|
* Get table's field information. If no fields are found, null is |
135
|
|
|
* returned. |
136
|
|
|
* |
137
|
|
|
* @param Where $where |
138
|
|
|
* @return ConvertedResultIterator |
139
|
|
|
*/ |
140
|
|
View Code Duplication |
protected function getTableFieldInformationWhere(Where $where) |
|
|
|
|
141
|
|
|
{ |
142
|
|
|
$sql = <<<SQL |
143
|
|
|
select |
144
|
|
|
att.attname as "name", |
145
|
|
|
case |
146
|
|
|
when name.nspname = 'pg_catalog' then typ.typname |
147
|
|
|
else format('%s.%s', name.nspname, typ.typname) |
148
|
|
|
end as "type", |
149
|
|
|
def.adsrc as "default", |
150
|
|
|
att.attnotnull as "is_notnull", |
151
|
|
|
dsc.description as "comment", |
152
|
|
|
att.attnum as "position", |
153
|
|
|
att.attnum = any(ind.indkey) as "is_primary" |
154
|
|
|
from |
155
|
|
|
pg_catalog.pg_attribute att |
156
|
|
|
join pg_catalog.pg_type typ on att.atttypid = typ.oid |
157
|
|
|
join pg_catalog.pg_class cla on att.attrelid = cla.oid |
158
|
|
|
join pg_catalog.pg_namespace clns on cla.relnamespace = clns.oid |
159
|
|
|
left join pg_catalog.pg_description dsc on cla.oid = dsc.objoid and att.attnum = dsc.objsubid |
160
|
|
|
left join pg_catalog.pg_attrdef def on att.attrelid = def.adrelid and att.attnum = def.adnum |
161
|
|
|
left join pg_catalog.pg_index ind on cla.oid = ind.indrelid and ind.indisprimary |
162
|
|
|
left join pg_catalog.pg_namespace name on typ.typnamespace = name.oid |
163
|
|
|
where |
164
|
|
|
{condition} |
165
|
|
|
order by |
166
|
|
|
att.attnum |
167
|
|
|
SQL; |
168
|
|
|
|
169
|
|
|
$where = $where |
170
|
|
|
->andWhere('att.attnum > 0') |
171
|
|
|
->andWhere('not att.attisdropped') |
172
|
|
|
; |
173
|
|
|
|
174
|
|
|
return $this->executeSql($sql, $where); |
175
|
|
|
} |
176
|
|
|
|
177
|
|
|
/** |
178
|
|
|
* getTableFieldInformation |
179
|
|
|
* |
180
|
|
|
* Return table fields information given the table oid. |
181
|
|
|
* |
182
|
|
|
* @param int $oid |
183
|
|
|
* @return ConvertedResultIterator |
184
|
|
|
*/ |
185
|
|
|
public function getTableFieldInformation($oid) |
186
|
|
|
{ |
187
|
|
|
return $this |
188
|
|
|
->getTableFieldInformationWhere( |
189
|
|
|
Where::create('att.attrelid = $*', [$oid]) |
190
|
|
|
); |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
/** |
194
|
|
|
* getTableFieldInformationName |
195
|
|
|
* |
196
|
|
|
* A short description here |
197
|
|
|
* |
198
|
|
|
* @param string $schema |
199
|
|
|
* @param string $name |
200
|
|
|
* @return ConvertedResultIterator |
201
|
|
|
*/ |
202
|
|
|
public function getTableFieldInformationName($schema, $name) |
203
|
|
|
{ |
204
|
|
|
return $this |
205
|
|
|
->getTableFieldInformationWhere( |
206
|
|
|
Where::create("cla.relname = $*", [$name]) |
207
|
|
|
->andWhere("clns.nspname = $*", [$schema]) |
208
|
|
|
); |
209
|
|
|
} |
210
|
|
|
} |
211
|
|
|
|
Duplicated code is one of the most pungent code smells. If you need to duplicate the same code in three or more different places, we strongly encourage you to look into extracting the code into a single class or operation.
You can also find more detailed suggestions in the “Code” section of your repository.