LegacyInspector::getTableFieldInformation() - Code Metrics - Inspection of "propose better InspectorPooler" - pomm-project/Foundation - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — inspector_ng ( 972652 )

by grégoire

created 2015-12-08 16:40 UTC

LegacyInspector::getTableFieldInformation() B

↳ Parent: LegacyInspector

Complexity

Conditions	1
Paths	1

Size

Total Lines	34
Code Lines	8

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	34
rs	8.8571
cc	1
eloc	8
nc	1
nop	1

<?php
/*
 * This file is part of the Pomm package.
 *
 * (c) 2014 - 2015 Grégoire HUBERT <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */
namespace PommProject\Foundation\Inspector;

use PommProject\Foundation\Exception\FoundationException;
use PommProject\Foundation\Client\Client;
use PommProject\Foundation\Where;

/**
 * LegacyInspector
 *
 * Pomm 2.0.x Database structure inspector.
 * This class is deprected to be split in several smaller and more specialized
 * clients.
 *
 * @package   Foundation
 * @copyright 2014 - 2015 Grégoire HUBERT
 * @author    Grégoire HUBERT
 * @license   X11 {@link http://opensource.org/licenses/mit-license.php}
 * @see       Client
 * @deprecated
 */
class LegacyInspector extends Client
{
    use InspectorTrait;

    /**
     * getClientType
     *
     * @see ClientInterface
     */
    public function getClientType()
    {
        return 'inspector';
    }

    /**
     * getClientIdentifier
     *
     * @see ClientInterface
     */
    public function getClientIdentifier()
    {
        return get_class($this);
    }

    /**
     * getSchemas
     *
     * Return a list of available schemas in the current database.
     *
     * @return \PommProject\Foundation\ConvertedResultIterator
     * @deprecated
     */
    public function getSchemas()
    {
        $sql = <<<SQL
select
    n.nspname     as "name",
    n.oid         as "oid",
    d.description as "comment",
    count(c)      as "relations"
from pg_catalog.pg_namespace n
    left join pg_catalog.pg_description d on n.oid = d.objoid
    left join pg_catalog.pg_class c on
        c.relnamespace = n.oid and c.relkind in ('r', 'v')
where {condition}
group by 1, 2, 3
order by 1;
SQL;
        $condition = new Where(
            'n.nspname !~ $* and n.nspname <> $*',
            ['^pg_', 'information_schema']
        );

        return $this->executeSql($sql, $condition);
    }

    /**
     * getTableOid
     *
     * Return the table oid from PostgreSQL catalog. If no table is found, null
     * is returned.
     *
     * @param  string   $schema
     * @param  string   $table
     * @return int|null
     * @deprecated
     */
    public function getTableOid($schema, $table)
    {
        $sql = <<<SQL
select
  c.oid as oid
from
    pg_catalog.pg_class c
        left join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where
{condition}
SQL;

        $where = Where::create('n.nspname =  $*', [$schema])
            ->andWhere('c.relname = $*', [$table])
            ;

        $iterator = $this->executeSql($sql, $where);

        return $iterator->isEmpty() ? null : $iterator->current()['oid'];
    }

    /**
     * getTableFieldInformation
     *
     * Get table's field information. If no fields are found, null is
     * returned.
     *
     * @param  int                 $oid
     * @return \PommProject\Foundation\ConvertedResultIterator|null
     * @deprecated
     */
    public function getTableFieldInformation($oid)
    {
        $sql = <<<SQL
select
    att.attname      as "name",
    case
        when name.nspname = 'pg_catalog' then typ.typname
        else format('%s.%s', name.nspname, typ.typname)
    end as "type",
    def.adsrc        as "default",
    att.attnotnull   as "is_notnull",
    dsc.description  as "comment",
    att.attnum       as "position",
    att.attnum = any(ind.indkey) as "is_primary"
from
  pg_catalog.pg_attribute att
    join pg_catalog.pg_type  typ  on att.atttypid = typ.oid
    join pg_catalog.pg_class cla  on att.attrelid = cla.oid
    left join pg_catalog.pg_description dsc on cla.oid = dsc.objoid and att.attnum = dsc.objsubid
    left join pg_catalog.pg_attrdef def     on att.attrelid = def.adrelid and att.attnum = def.adnum
    left join pg_catalog.pg_index ind       on cla.oid = ind.indrelid and ind.indisprimary
    left join pg_catalog.pg_namespace name  on typ.typnamespace = name.oid
where
{condition}
order by
    att.attnum
SQL;
        $where = Where::create('att.attrelid = $*', [$oid])
            ->andWhere('att.attnum > 0')
            ->andWhere('not att.attisdropped')
            ;

        return $this->executeSql($sql, $where);
    }

    /**
     * getSchemaOid
     *
     * Return the given schema oid, null if the schema is not found.
     *
     * @param  string   $schema
     * @param  Where    $where optional where clause.
     * @return int|null
     * @deprecated
     */
    public function getSchemaOid($schema, Where $where = null)
    {
        $condition =
            Where::create("s.nspname = $*", [$schema])
            ->andWhere($where)
            ;
        $sql = <<<SQL
select
    s.oid as oid
from
    pg_catalog.pg_namespace s
where
    {condition}
SQL;

        $iterator = $this->executeSql($sql, $condition);

        return $iterator->isEmpty() ? null : $iterator->current()['oid'];
    }

    /**
     * getPrimaryKey
     *
     * Get relation's primary key if any.
     *
     * @param  int        $table_oid
     * @return array|null
     * @deprecated
     */
    public function getPrimaryKey($table_oid)
    {
        $sql = <<<SQL
with
    pk_field as (
        select
            att.attname as field
        from
            pg_catalog.pg_attribute att
                join pg_catalog.pg_index ind on
                    att.attrelid = ind.indrelid and att.attnum = any(ind.indkey)
        where
            {condition}
        order by att.attnum asc
)
select array_agg(field) as fields from pk_field
SQL;
        $condition =
            Where::create('ind.indrelid = $*', [$table_oid])
            ->andWhere('ind.indisprimary')
            ;

        $pk = $this
            ->executeSql($sql, $condition)
            ->current()
            ;

        return $pk['fields'][0] === null ? [] : array_reverse($pk['fields']);
    }

    /**
     * getSchemaRelations
     *
     * Return information on relations in a given schema. An additional Where
     * condition can be passed to filter against other criteria.
     *
     * @param  int                     $schema_oid
     * @param  Where                   $where
     * @return \PommProject\Foundation\ConvertedResultIterator
     * @deprecated
     */
    public function getSchemaRelations($schema_oid, Where $where = null)
    {
        $condition = Where::create('relnamespace = $*', [$schema_oid])
            ->andWhere(Where::createWhereIn('relkind', ['r', 'v', 'm', 'f']))
            ->andWhere($where)
            ;

        $sql = <<<SQL
select
    cl.relname      as "name",
    case
        when cl.relkind = 'r' then 'table'
        when cl.relkind = 'v' then 'view'
        when cl.relkind = 'm' then 'materialized view'
        when cl.relkind = 'f' then 'foreign table'
        else 'other'
    end             as "type",
    cl.oid          as "oid",
    des.description as "comment"
from
    pg_catalog.pg_class cl
        left join pg_catalog.pg_description des on
            cl.oid = des.objoid and des.objsubid = 0
where {condition}
order by name asc
SQL;

        return $this->executeSql($sql, $condition);
    }

    /**
     * getTableComment
     *
     * Return the comment on a table if set. Null otherwise.
     *
     * @param  int         $table_oid
     * @return string|null
     * @deprecated
     */
    public function getTableComment($table_oid)
    {
        $sql      = <<<SQL
select description from pg_catalog.pg_description where {condition}
SQL;

        $where    = Where::create('objoid = $*', [$table_oid]);
        $iterator = $this->executeSql($sql, $where);

        return $iterator->isEmpty() ? null : $iterator->current()['description'];
    }

    /**
     * getTypeInformation
     *
     * Return the Oid of the given type name.
     * It Additionally returns the type category.
     *
     * @param  string     $type_name
     * @param  string     $type_schema
     * @return array|null
     * @deprecated
     */
    public function getTypeInformation($type_name, $type_schema = null)
    {
        $condition = Where::create("t.typname = $*", [$type_name]);
        $sql = <<<SQL
select
    t.oid as "oid",
    t.typcategory as "category"
from
    pg_catalog.pg_type t :join
where
    {condition}
SQL;

        if ($type_schema !== null) {
            $sql = strtr($sql, [':join' => 'join pg_namespace n on n.oid = t.typnamespace']);
            $condition->andWhere('n.nspname = $*', [$type_schema]);
        } else {
            $sql = strtr($sql, [':join' => '']);
        }

        $iterator = $this->executeSql($sql, $condition);

        return $iterator->isEmpty() ? null : $iterator->current();
    }

    /**
     * getTypeCategory
     *
     * Get type category.
     *
     * @param  int        $oid
     * @return array|null
     * @deprecated
     */
    public function getTypeCategory($oid)
    {
        $sql = <<<SQL
select
    case
        when n is null then t.type_name
        else n.nspname||'.'||t.type_name
    end as "name",
    t.typcategory as "category"
from
    pg_catalog.pg_type t
        left join pg_namespace n on n.oid = t.typnamespace
where
    {condition}
SQL;
        $iterator = $this->executeSql($sql, Where::create('t.oid = $*', [$oid]));

        return $iterator->isEmpty() ? null : $iterator->current();
    }

    /**
     * getTypeEnumValues
     *
     * Return all possible values from an enumerated type in its natural order.
     *
     * @param  int        $oid
     * @return array|null
     * @deprecated
     */
    public function getTypeEnumValues($oid)
    {
        $sql = <<<SQL
with
    enum_value as (
        select
            e.enumlabel as "label"
        from
            pg_catalog.pg_enum e
        where
            {condition}
    )
select array_agg(label) as labels from enum_value
SQL;

        $result = $this
            ->executeSql($sql, Where::create('e.enumtypid = $*', [$oid]))
            ->current()
            ;

        return $result['labels'];
    }

    /**
     * getCompositeInformation
     *
     * Return the structure of a composite row.
     *
     * @param  int                     $oid
     * @return \PommProject\Foundation\ConvertedResultIterator
     * @deprecated
     */
    public function getCompositeInformation($oid)
    {
        $sql = <<<SQL
select
    a.attname as "name",
    t.typname as "type"
from
    pg_type orig
        join pg_catalog.pg_class c      on orig.typrelid = c.oid
        join pg_catalog.pg_attribute a  on a.attrelid = c.oid and a.attnum > 0
        join pg_catalog.pg_type t       on t.oid = a.atttypid
where
    {condition}
SQL;

        return $this->executeSql($sql, Where::create('orig.oid = $*', [$oid]));
    }

    /**
     * getVersion
     *
     * Return server version.
     *
     * @throws  FoundationException if invalid string.
     * @return string
     * @deprecated
     */
    public function getVersion()
    {
        $row = $this
            ->executeSql("show server_version")
            ->current()
            ;

        return $row['server_version'];
    }
}


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

pomm-project / Foundation

Push — inspector_ng ( 972652 )

LegacyInspector::getTableFieldInformation() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like