Inspector::getPrimaryKey() - Code Metrics - Inspection of "enable telemetry extension on CI builds" - pomm-project/Foundation - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 2.0 (#75)

by Julien

created 2016-07-01 16:47 UTC

Inspector::getPrimaryKey() B

↳ Parent: Inspector

Complexity

Conditions	2
Paths	2

Size

Total Lines	26
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	2
eloc	9
c	1
b	0
f	0
nc	2
nop	1
dl	0
loc	26
rs	8.8571

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

/**
 * Inspector
 *
 * Database structure inspector.
 *
 * @package   Foundation
 * @copyright 2014 - 2015 Grégoire HUBERT
 * @author    Grégoire HUBERT
 * @license   X11 {@link http://opensource.org/licenses/mit-license.php}
 * @see       Client
 */
class Inspector extends Client
{
    /**
     * 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.
     *
     * @access public
     * @return \PommProject\Foundation\ConvertedResultIterator
     */
    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.
     *
     * @access public
     * @param  string   $schema
     * @param  string   $table
     * @return int|null
     */
    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.
     *
     * @access public
     * @param  int                 $oid
     * @return \PommProject\Foundation\ConvertedResultIterator|null
     */
    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.
     *
     * @access public
     * @param  string   $schema
     * @param  Where    $where optional where clause.
     * @return int|null
     */
    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.
     *
     * @access public
     * @param  int        $table_oid
     * @return array|null
     */
    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.
     *
     * @access public
     * @param  int                     $schema_oid
     * @param  Where                   $where
     * @return \PommProject\Foundation\ConvertedResultIterator
     */
    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.
     *
     * @access public
     * @param  int         $table_oid
     * @return string|null
     */
    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.
     *
     * @access public
     * @param  string     $type_name
     * @param  string     $type_schema
     * @return array|null
     */
    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.
     *
     * @access public
     * @param  int        $oid
     * @return array|null
     */
    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.
     *
     * @access public
     * @param  int        $oid
     * @return array|null
     */
    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.
     *
     * @access public
     * @param  int                     $oid
     * @return \PommProject\Foundation\ConvertedResultIterator
     */
    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.
     *
     * @access public
     * @throws  FoundationException if invalid string.
     * @return string
     */
    public function getVersion()
    {
        $row = $this
            ->executeSql("show server_version")
            ->current()
            ;

        return $row['server_version'];
    }

    /**
     * executeSql
     *
     * Launch query execution.
     *
     * @access protected
     * @param  string         $sql
     * @param  Where          $condition
     * @return \PommProject\Foundation\ConvertedResultIterator
     */
    protected function executeSql($sql, Where $condition = null)
    {
        $condition = (new Where())->andWhere($condition);
        $sql = strtr($sql, [':condition' => $condition]);

        return $this
            ->getSession()
            ->getClientUsingPooler('query_manager', '\PommProject\Foundation\PreparedQuery\PreparedQueryManager')
            ->query($sql, $condition->getValues())
            ;
    }
}


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

pomm-project / Foundation

Pull Request — 2.0 (#75)

Inspector::getPrimaryKey() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like