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\Client\Client; |
14
|
|
|
use PommProject\Foundation\Where; |
15
|
|
|
|
16
|
|
|
/** |
17
|
|
|
* LegacyInspector |
18
|
|
|
* |
19
|
|
|
* Pomm 2.0.x Database structure inspector. |
20
|
|
|
* This class is deprected to be split in several smaller and more specialized |
21
|
|
|
* clients. |
22
|
|
|
* |
23
|
|
|
* @package Foundation |
24
|
|
|
* @copyright 2014 - 2015 Grégoire HUBERT |
25
|
|
|
* @author Grégoire HUBERT |
26
|
|
|
* @license X11 {@link http://opensource.org/licenses/mit-license.php} |
27
|
|
|
* @see Client |
28
|
|
|
* @deprecated |
29
|
|
|
*/ |
30
|
|
|
class LegacyInspector extends Client |
31
|
|
|
{ |
32
|
|
|
use InspectorTrait; |
33
|
|
|
|
34
|
|
|
/** |
35
|
|
|
* getClientType |
36
|
|
|
* |
37
|
|
|
* @see ClientInterface |
38
|
|
|
*/ |
39
|
|
|
public function getClientType() |
40
|
|
|
{ |
41
|
|
|
return 'inspector'; |
42
|
|
|
} |
43
|
|
|
|
44
|
|
|
/** |
45
|
|
|
* getClientIdentifier |
46
|
|
|
* |
47
|
|
|
* @see ClientInterface |
48
|
|
|
*/ |
49
|
|
|
public function getClientIdentifier() |
50
|
|
|
{ |
51
|
|
|
return get_class($this); |
52
|
|
|
} |
53
|
|
|
|
54
|
|
|
/** |
55
|
|
|
* getSchemas |
56
|
|
|
* |
57
|
|
|
* Return a list of available schemas in the current database. |
58
|
|
|
* |
59
|
|
|
* @return \PommProject\Foundation\ConvertedResultIterator |
60
|
|
|
* @deprecated |
61
|
|
|
*/ |
62
|
|
|
public function getSchemas() |
63
|
|
|
{ |
64
|
|
|
$sql = <<<SQL |
65
|
|
|
select |
66
|
|
|
n.nspname as "name", |
67
|
|
|
n.oid as "oid", |
68
|
|
|
d.description as "comment", |
69
|
|
|
count(c) as "relations" |
70
|
|
|
from pg_catalog.pg_namespace n |
71
|
|
|
left join pg_catalog.pg_description d on n.oid = d.objoid |
72
|
|
|
left join pg_catalog.pg_class c on |
73
|
|
|
c.relnamespace = n.oid and c.relkind in ('r', 'v') |
74
|
|
|
where {condition} |
75
|
|
|
group by 1, 2, 3 |
76
|
|
|
order by 1; |
77
|
|
|
SQL; |
78
|
|
|
$condition = new Where( |
79
|
|
|
'n.nspname !~ $* and n.nspname <> $*', |
80
|
|
|
['^pg_', 'information_schema'] |
81
|
|
|
); |
82
|
|
|
|
83
|
|
|
return $this->executeSql($sql, $condition); |
84
|
|
|
} |
85
|
|
|
|
86
|
|
|
/** |
87
|
|
|
* getTableOid |
88
|
|
|
* |
89
|
|
|
* Return the table oid from PostgreSQL catalog. If no table is found, null |
90
|
|
|
* is returned. |
91
|
|
|
* |
92
|
|
|
* @param string $schema |
93
|
|
|
* @param string $table |
94
|
|
|
* @return int|null |
95
|
|
|
* @deprecated |
96
|
|
|
*/ |
97
|
|
View Code Duplication |
public function getTableOid($schema, $table) |
98
|
|
|
{ |
99
|
|
|
$sql = <<<SQL |
100
|
|
|
select |
101
|
|
|
c.oid as oid |
102
|
|
|
from |
103
|
|
|
pg_catalog.pg_class c |
104
|
|
|
left join pg_catalog.pg_namespace n on n.oid = c.relnamespace |
105
|
|
|
where |
106
|
|
|
{condition} |
107
|
|
|
SQL; |
108
|
|
|
|
109
|
|
|
$where = Where::create('n.nspname = $*', [$schema]) |
110
|
|
|
->andWhere('c.relname = $*', [$table]) |
111
|
|
|
; |
112
|
|
|
|
113
|
|
|
$iterator = $this->executeSql($sql, $where); |
114
|
|
|
|
115
|
|
|
return $iterator->isEmpty() ? null : $iterator->current()['oid']; |
116
|
|
|
} |
117
|
|
|
|
118
|
|
|
/** |
119
|
|
|
* getTableFieldInformation |
120
|
|
|
* |
121
|
|
|
* Get table's field information. If no fields are found, null is |
122
|
|
|
* returned. |
123
|
|
|
* |
124
|
|
|
* @param int $oid |
125
|
|
|
* @return \PommProject\Foundation\ConvertedResultIterator|null |
126
|
|
|
* @deprecated |
127
|
|
|
*/ |
128
|
|
View Code Duplication |
public function getTableFieldInformation($oid) |
|
|
|
|
129
|
|
|
{ |
130
|
|
|
$sql = <<<SQL |
131
|
|
|
select |
132
|
|
|
att.attname as "name", |
133
|
|
|
case |
134
|
|
|
when name.nspname = 'pg_catalog' then typ.typname |
135
|
|
|
else format('%s.%s', name.nspname, typ.typname) |
136
|
|
|
end as "type", |
137
|
|
|
def.adsrc as "default", |
138
|
|
|
att.attnotnull as "is_notnull", |
139
|
|
|
dsc.description as "comment", |
140
|
|
|
att.attnum as "position", |
141
|
|
|
att.attnum = any(ind.indkey) as "is_primary" |
142
|
|
|
from |
143
|
|
|
pg_catalog.pg_attribute att |
144
|
|
|
join pg_catalog.pg_type typ on att.atttypid = typ.oid |
145
|
|
|
join pg_catalog.pg_class cla on att.attrelid = cla.oid |
146
|
|
|
left join pg_catalog.pg_description dsc on cla.oid = dsc.objoid and att.attnum = dsc.objsubid |
147
|
|
|
left join pg_catalog.pg_attrdef def on att.attrelid = def.adrelid and att.attnum = def.adnum |
148
|
|
|
left join pg_catalog.pg_index ind on cla.oid = ind.indrelid and ind.indisprimary |
149
|
|
|
left join pg_catalog.pg_namespace name on typ.typnamespace = name.oid |
150
|
|
|
where |
151
|
|
|
{condition} |
152
|
|
|
order by |
153
|
|
|
att.attnum |
154
|
|
|
SQL; |
155
|
|
|
$where = Where::create('att.attrelid = $*', [$oid]) |
156
|
|
|
->andWhere('att.attnum > 0') |
157
|
|
|
->andWhere('not att.attisdropped') |
158
|
|
|
; |
159
|
|
|
|
160
|
|
|
return $this->executeSql($sql, $where); |
161
|
|
|
} |
162
|
|
|
|
163
|
|
|
/** |
164
|
|
|
* getSchemaOid |
165
|
|
|
* |
166
|
|
|
* Return the given schema oid, null if the schema is not found. |
167
|
|
|
* |
168
|
|
|
* @param string $schema |
169
|
|
|
* @param Where $where optional where clause. |
170
|
|
|
* @return int|null |
171
|
|
|
* @deprecated |
172
|
|
|
*/ |
173
|
|
View Code Duplication |
public function getSchemaOid($schema, Where $where = null) |
174
|
|
|
{ |
175
|
|
|
$condition = |
176
|
|
|
Where::create("s.nspname = $*", [$schema]) |
177
|
|
|
->andWhere($where) |
178
|
|
|
; |
179
|
|
|
$sql = <<<SQL |
180
|
|
|
select |
181
|
|
|
s.oid as oid |
182
|
|
|
from |
183
|
|
|
pg_catalog.pg_namespace s |
184
|
|
|
where |
185
|
|
|
{condition} |
186
|
|
|
SQL; |
187
|
|
|
|
188
|
|
|
$iterator = $this->executeSql($sql, $condition); |
189
|
|
|
|
190
|
|
|
return $iterator->isEmpty() ? null : $iterator->current()['oid']; |
191
|
|
|
} |
192
|
|
|
|
193
|
|
|
/** |
194
|
|
|
* getPrimaryKey |
195
|
|
|
* |
196
|
|
|
* Get relation's primary key if any. |
197
|
|
|
* |
198
|
|
|
* @param int $table_oid |
199
|
|
|
* @return array|null |
200
|
|
|
* @deprecated |
201
|
|
|
*/ |
202
|
|
|
public function getPrimaryKey($table_oid) |
203
|
|
|
{ |
204
|
|
|
$sql = <<<SQL |
205
|
|
|
with |
206
|
|
|
pk_field as ( |
207
|
|
|
select |
208
|
|
|
att.attname as field |
209
|
|
|
from |
210
|
|
|
pg_catalog.pg_attribute att |
211
|
|
|
join pg_catalog.pg_index ind on |
212
|
|
|
att.attrelid = ind.indrelid and att.attnum = any(ind.indkey) |
213
|
|
|
where |
214
|
|
|
{condition} |
215
|
|
|
order by att.attnum asc |
216
|
|
|
) |
217
|
|
|
select array_agg(field) as fields from pk_field |
218
|
|
|
SQL; |
219
|
|
|
$condition = |
220
|
|
|
Where::create('ind.indrelid = $*', [$table_oid]) |
221
|
|
|
->andWhere('ind.indisprimary') |
222
|
|
|
; |
223
|
|
|
|
224
|
|
|
$pk = $this |
225
|
|
|
->executeSql($sql, $condition) |
226
|
|
|
->current() |
227
|
|
|
; |
228
|
|
|
|
229
|
|
|
return $pk['fields'][0] === null ? [] : array_reverse($pk['fields']); |
230
|
|
|
} |
231
|
|
|
|
232
|
|
|
/** |
233
|
|
|
* getSchemaRelations |
234
|
|
|
* |
235
|
|
|
* Return information on relations in a given schema. An additional Where |
236
|
|
|
* condition can be passed to filter against other criteria. |
237
|
|
|
* |
238
|
|
|
* @param int $schema_oid |
239
|
|
|
* @param Where $where |
240
|
|
|
* @return \PommProject\Foundation\ConvertedResultIterator |
241
|
|
|
* @deprecated |
242
|
|
|
*/ |
243
|
|
View Code Duplication |
public function getSchemaRelations($schema_oid, Where $where = null) |
|
|
|
|
244
|
|
|
{ |
245
|
|
|
$condition = Where::create('relnamespace = $*', [$schema_oid]) |
246
|
|
|
->andWhere(Where::createWhereIn('relkind', ['r', 'v', 'm', 'f'])) |
247
|
|
|
->andWhere($where) |
248
|
|
|
; |
249
|
|
|
|
250
|
|
|
$sql = <<<SQL |
251
|
|
|
select |
252
|
|
|
cl.relname as "name", |
253
|
|
|
case |
254
|
|
|
when cl.relkind = 'r' then 'table' |
255
|
|
|
when cl.relkind = 'v' then 'view' |
256
|
|
|
when cl.relkind = 'm' then 'materialized view' |
257
|
|
|
when cl.relkind = 'f' then 'foreign table' |
258
|
|
|
else 'other' |
259
|
|
|
end as "type", |
260
|
|
|
cl.oid as "oid", |
261
|
|
|
des.description as "comment" |
262
|
|
|
from |
263
|
|
|
pg_catalog.pg_class cl |
264
|
|
|
left join pg_catalog.pg_description des on |
265
|
|
|
cl.oid = des.objoid and des.objsubid = 0 |
266
|
|
|
where {condition} |
267
|
|
|
order by name asc |
268
|
|
|
SQL; |
269
|
|
|
|
270
|
|
|
return $this->executeSql($sql, $condition); |
271
|
|
|
} |
272
|
|
|
|
273
|
|
|
/** |
274
|
|
|
* getTableComment |
275
|
|
|
* |
276
|
|
|
* Return the comment on a table if set. Null otherwise. |
277
|
|
|
* |
278
|
|
|
* @param int $table_oid |
279
|
|
|
* @return string|null |
280
|
|
|
* @deprecated |
281
|
|
|
*/ |
282
|
|
View Code Duplication |
public function getTableComment($table_oid) |
283
|
|
|
{ |
284
|
|
|
$sql = <<<SQL |
285
|
|
|
select description from pg_catalog.pg_description where {condition} |
286
|
|
|
SQL; |
287
|
|
|
|
288
|
|
|
$where = Where::create('objoid = $*', [$table_oid]); |
289
|
|
|
$iterator = $this->executeSql($sql, $where); |
290
|
|
|
|
291
|
|
|
return $iterator->isEmpty() ? null : $iterator->current()['description']; |
292
|
|
|
} |
293
|
|
|
|
294
|
|
|
/** |
295
|
|
|
* getTypeInformation |
296
|
|
|
* |
297
|
|
|
* Return the Oid of the given type name. |
298
|
|
|
* It Additionally returns the type category. |
299
|
|
|
* |
300
|
|
|
* @param string $type_name |
301
|
|
|
* @param string $type_schema |
302
|
|
|
* @return array|null |
303
|
|
|
* @deprecated |
304
|
|
|
*/ |
305
|
|
|
public function getTypeInformation($type_name, $type_schema = null) |
306
|
|
|
{ |
307
|
|
|
$condition = Where::create("t.typname = $*", [$type_name]); |
308
|
|
|
$sql = <<<SQL |
309
|
|
|
select |
310
|
|
|
t.oid as "oid", |
311
|
|
|
t.typcategory as "category" |
312
|
|
|
from |
313
|
|
|
pg_catalog.pg_type t :join |
314
|
|
|
where |
315
|
|
|
{condition} |
316
|
|
|
SQL; |
317
|
|
|
|
318
|
|
|
if ($type_schema !== null) { |
319
|
|
|
$sql = strtr($sql, [':join' => 'join pg_namespace n on n.oid = t.typnamespace']); |
320
|
|
|
$condition->andWhere('n.nspname = $*', [$type_schema]); |
321
|
|
|
} else { |
322
|
|
|
$sql = strtr($sql, [':join' => '']); |
323
|
|
|
} |
324
|
|
|
|
325
|
|
|
$iterator = $this->executeSql($sql, $condition); |
326
|
|
|
|
327
|
|
|
return $iterator->isEmpty() ? null : $iterator->current(); |
328
|
|
|
} |
329
|
|
|
|
330
|
|
|
/** |
331
|
|
|
* getTypeCategory |
332
|
|
|
* |
333
|
|
|
* Get type category. |
334
|
|
|
* |
335
|
|
|
* @param int $oid |
336
|
|
|
* @return array|null |
337
|
|
|
* @deprecated |
338
|
|
|
*/ |
339
|
|
View Code Duplication |
public function getTypeCategory($oid) |
340
|
|
|
{ |
341
|
|
|
$sql = <<<SQL |
342
|
|
|
select |
343
|
|
|
case |
344
|
|
|
when n is null then t.type_name |
345
|
|
|
else n.nspname||'.'||t.type_name |
346
|
|
|
end as "name", |
347
|
|
|
t.typcategory as "category" |
348
|
|
|
from |
349
|
|
|
pg_catalog.pg_type t |
350
|
|
|
left join pg_namespace n on n.oid = t.typnamespace |
351
|
|
|
where |
352
|
|
|
{condition} |
353
|
|
|
SQL; |
354
|
|
|
$iterator = $this->executeSql($sql, Where::create('t.oid = $*', [$oid])); |
355
|
|
|
|
356
|
|
|
return $iterator->isEmpty() ? null : $iterator->current(); |
357
|
|
|
} |
358
|
|
|
|
359
|
|
|
/** |
360
|
|
|
* getTypeEnumValues |
361
|
|
|
* |
362
|
|
|
* Return all possible values from an enumerated type in its natural order. |
363
|
|
|
* |
364
|
|
|
* @param int $oid |
365
|
|
|
* @return array|null |
366
|
|
|
* @deprecated |
367
|
|
|
*/ |
368
|
|
|
public function getTypeEnumValues($oid) |
369
|
|
|
{ |
370
|
|
|
$sql = <<<SQL |
371
|
|
|
with |
372
|
|
|
enum_value as ( |
373
|
|
|
select |
374
|
|
|
e.enumlabel as "label" |
375
|
|
|
from |
376
|
|
|
pg_catalog.pg_enum e |
377
|
|
|
where |
378
|
|
|
{condition} |
379
|
|
|
) |
380
|
|
|
select array_agg(label) as labels from enum_value |
381
|
|
|
SQL; |
382
|
|
|
|
383
|
|
|
$result = $this |
384
|
|
|
->executeSql($sql, Where::create('e.enumtypid = $*', [$oid])) |
385
|
|
|
->current() |
386
|
|
|
; |
387
|
|
|
|
388
|
|
|
return $result['labels']; |
389
|
|
|
} |
390
|
|
|
|
391
|
|
|
/** |
392
|
|
|
* getCompositeInformation |
393
|
|
|
* |
394
|
|
|
* Return the structure of a composite row. |
395
|
|
|
* |
396
|
|
|
* @param int $oid |
397
|
|
|
* @return \PommProject\Foundation\ConvertedResultIterator |
398
|
|
|
* @deprecated |
399
|
|
|
*/ |
400
|
|
|
public function getCompositeInformation($oid) |
401
|
|
|
{ |
402
|
|
|
$sql = <<<SQL |
403
|
|
|
select |
404
|
|
|
a.attname as "name", |
405
|
|
|
t.typname as "type" |
406
|
|
|
from |
407
|
|
|
pg_type orig |
408
|
|
|
join pg_catalog.pg_class c on orig.typrelid = c.oid |
409
|
|
|
join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0 |
410
|
|
|
join pg_catalog.pg_type t on t.oid = a.atttypid |
411
|
|
|
where |
412
|
|
|
{condition} |
413
|
|
|
SQL; |
414
|
|
|
|
415
|
|
|
return $this->executeSql($sql, Where::create('orig.oid = $*', [$oid])); |
416
|
|
|
} |
417
|
|
|
|
418
|
|
|
/** |
419
|
|
|
* getVersion |
420
|
|
|
* |
421
|
|
|
* Return server version. |
422
|
|
|
* |
423
|
|
|
* @throws FoundationException if invalid string. |
424
|
|
|
* @return string |
425
|
|
|
* @deprecated |
426
|
|
|
*/ |
427
|
|
|
public function getVersion() |
428
|
|
|
{ |
429
|
|
|
$row = $this |
430
|
|
|
->executeSql("show server_version") |
431
|
|
|
->current() |
432
|
|
|
; |
433
|
|
|
|
434
|
|
|
return $row['server_version']; |
435
|
|
|
} |
436
|
|
|
} |
437
|
|
|
|
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.