RelationInspector::getRelationsInSchema() - Code Metrics - Inspection of "add a function to inspect relation total size" - pomm-project/Foundation - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( 0e7e9d...eea054 )

by grégoire

created 2017-03-15 19:49 UTC

RelationInspector::getRelationsInSchema() A

↳ Parent: RelationInspector

Complexity

Conditions	1
Paths	1

Size

Total Lines	8
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
c	0
b	0
f	0
dl	0
loc	8
rs	9.4285
cc	1
eloc	4
nc	1
nop	2

<?php
/*
 * This file is part of the Pomm package.
 *
 * (c) 2014 - 2017 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\ConvertedResultIterator;
use PommProject\Foundation\Client\Client;
use PommProject\Foundation\Where;

/**
 * RelationInspector
 *
 * Relation inspector.
 *
 * @package     Pomm
 * @copyright   2017 Grégoire HUBERT
 * @author      Grégoire HUBERT
 * @license     X11 {@link http://opensource.org/licenses/mit-license.php}
 *
 * @see Client
 */
class RelationInspector extends Client
{
    use InspectorTrait;

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

    /**
     * getClientIdentifier
     *
     * @see ClientInterface
     */
    public function getClientIdentifier()
    {
        return 'relation';
    }

    /**
     * getRelations
     *
     * Return a list of relations. Be aware that if no conditions is given, it
     * will also return system tables and views.
     *
     * @param   Where $where
     * @return  ConvertedResultIterator
     */
    public function getRelations(Where $where = null)

    {
        $condition = 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",
    n.nspname       as "schema",
    cl.oid          as "oid",
    o.rolname       as "owner",
    case
        when cl.relkind = 'r' then pg_size_pretty(pg_relation_size(cl.oid::regclass))
        else null
    end             as "size",
    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
        join pg_catalog.pg_roles o on cl.relowner = o.oid
        join pg_catalog.pg_namespace n on cl.relnamespace = n.oid
where {condition}
order by name asc
SQL;

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

    /**
     * getRelationsInSchema
     *
     * Return the list of relations contained in the given schema.
     *
     * @param   string $schema_name
     * @param   Where $where
     * @return  ConvertedResultIterator
     */
    public function getRelationsInSchema($schema_name, Where $where = null)
    {
        $where = Where::create("n.nspname ~* $*", [$schema_name])
            ->andWhere($where)
            ;

        return $this->getRelations($where);
    }

    /**
     * getDatabaseRelations
     *
     * Return non system relations in the database.
     *
     * @param   Where $where
     * @return  ConvertedResultIterator
     */
    public function getDatabaseRelations(Where $where = null)

    {
        $where = Where::create('n.nspname !~* $*', ['^pg_'])
            ->andWhere('n.nspname != $*', ['information_schema'])
            ->andWhere($where)
            ;

        return $this->getRelations($where);
    }

    /**
     * getTableFieldInformationWhere
     *
     * Get table's field information. If no fields are found, null is
     * returned.
     *
     * @param  Where $where
     * @return ConvertedResultIterator
     */
    protected function getTableFieldInformationWhere(Where $where)

    {
        $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
    join pg_catalog.pg_namespace clns on cla.relnamespace = clns.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
            ->andWhere('att.attnum > 0')
            ->andWhere('not att.attisdropped')
            ;

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

    /**
     * getTableFieldInformation
     *
     * Return table fields information given the table oid.
     *
     * @param   int $oid
     * @return  ConvertedResultIterator
     */
    public function getTableFieldInformation($oid)
    {
        return $this
            ->getTableFieldInformationWhere(
                Where::create('att.attrelid = $*', [$oid])
            );
    }

    /**
     * getTableFieldInformationName
     *
     * A short description here
     *
     * @param   string $schema
     * @param   string $name
     * @return  ConvertedResultIterator
     */
    public function getTableFieldInformationName($schema, $name)
    {
        return $this
            ->getTableFieldInformationWhere(
                Where::create("cla.relname = $*", [$name])
                    ->andWhere("clns.nspname = $*", [$schema])
            );
    }

    /**
     * getTableTotalSizeOnDisk
     *
     * Return the total size of the relation on disk. This size includes
     * associated indexes size.
     *
     * @param   string $schema
     * @param   string $name
     * @return  string
     */
    public function getTableTotalSizeOnDisk($schema, $name)
    {
        $where = new Where(
            '',
            [
                sprintf(
                    '%s.%s',
                    $this->getSession()->getConnection()->escapeIdentifier($schema),
                    $this->getSession()->getConnection()->escapeIdentifier($name)
                )]
        );

        return $this
            ->executeSql(
                "select pg_total_relation_size($*::regclass) as total_size",
                $where
            )->current()['total_size'];
    }
}


1		<?php
2		/*
3		* This file is part of the Pomm package.
4		*
5		* (c) 2014 - 2017 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 2017 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)
		0 ignored issues – show Duplication introduced 2015-12-14 14:32 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. 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. Loading history...
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		case
82		when cl.relkind = 'r' then pg_size_pretty(pg_relation_size(cl.oid::regclass))
83		else null
84		end as "size",
85		des.description as "comment"
86		from
87		pg_catalog.pg_class cl
88		left join pg_catalog.pg_description des on
89		cl.oid = des.objoid and des.objsubid = 0
90		join pg_catalog.pg_roles o on cl.relowner = o.oid
91		join pg_catalog.pg_namespace n on cl.relnamespace = n.oid
92		where {condition}
93		order by name asc
94		SQL;
95
96		return $this->executeSql($sql, $condition);
97		}
98
99		/**
100		* getRelationsInSchema
101		*
102		* Return the list of relations contained in the given schema.
103		*
104		* @param string $schema_name
105		* @param Where $where
106		* @return ConvertedResultIterator
107		*/
108		public function getRelationsInSchema($schema_name, Where $where = null)
109		{
110		$where = Where::create("n.nspname ~* $*", [$schema_name])
111		->andWhere($where)
112		;
113
114		return $this->getRelations($where);
115		}
116
117		/**
118		* getDatabaseRelations
119		*
120		* Return non system relations in the database.
121		*
122		* @param Where $where
123		* @return ConvertedResultIterator
124		*/
125	View Code Duplication	public function getDatabaseRelations(Where $where = null)
		0 ignored issues – show Duplication introduced 2016-01-29 13:42 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. 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. Loading history...
126		{
127		$where = Where::create('n.nspname !~* $*', ['^pg_'])
128		->andWhere('n.nspname != $*', ['information_schema'])
129		->andWhere($where)
130		;
131
132		return $this->getRelations($where);
133		}
134
135		/**
136		* getTableFieldInformationWhere
137		*
138		* Get table's field information. If no fields are found, null is
139		* returned.
140		*
141		* @param Where $where
142		* @return ConvertedResultIterator
143		*/
144	View Code Duplication	protected function getTableFieldInformationWhere(Where $where)
		0 ignored issues – show Duplication introduced 2015-12-14 14:32 UTC by Report Bug Copy Issue Report This method seems to be duplicated in your project. 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. Loading history...
145		{
146		$sql = <<<SQL
147		select
148		att.attname as "name",
149		case
150		when name.nspname = 'pg_catalog' then typ.typname
151		else format('%s.%s', name.nspname, typ.typname)
152		end as "type",
153		def.adsrc as "default",
154		att.attnotnull as "is_notnull",
155		dsc.description as "comment",
156		att.attnum as "position",
157		att.attnum = any(ind.indkey) as "is_primary"
158		from
159		pg_catalog.pg_attribute att
160		join pg_catalog.pg_type typ on att.atttypid = typ.oid
161		join pg_catalog.pg_class cla on att.attrelid = cla.oid
162		join pg_catalog.pg_namespace clns on cla.relnamespace = clns.oid
163		left join pg_catalog.pg_description dsc on cla.oid = dsc.objoid and att.attnum = dsc.objsubid
164		left join pg_catalog.pg_attrdef def on att.attrelid = def.adrelid and att.attnum = def.adnum
165		left join pg_catalog.pg_index ind on cla.oid = ind.indrelid and ind.indisprimary
166		left join pg_catalog.pg_namespace name on typ.typnamespace = name.oid
167		where
168		{condition}
169		order by
170		att.attnum
171		SQL;
172
173		$where = $where
174		->andWhere('att.attnum > 0')
175		->andWhere('not att.attisdropped')
176		;
177
178		return $this->executeSql($sql, $where);
179		}
180
181		/**
182		* getTableFieldInformation
183		*
184		* Return table fields information given the table oid.
185		*
186		* @param int $oid
187		* @return ConvertedResultIterator
188		*/
189		public function getTableFieldInformation($oid)
190		{
191		return $this
192		->getTableFieldInformationWhere(
193		Where::create('att.attrelid = $*', [$oid])
194		);
195		}
196
197		/**
198		* getTableFieldInformationName
199		*
200		* A short description here
201		*
202		* @param string $schema
203		* @param string $name
204		* @return ConvertedResultIterator
205		*/
206		public function getTableFieldInformationName($schema, $name)
207		{
208		return $this
209		->getTableFieldInformationWhere(
210		Where::create("cla.relname = $*", [$name])
211		->andWhere("clns.nspname = $*", [$schema])
212		);
213		}
214
215		/**
216		* getTableTotalSizeOnDisk
217		*
218		* Return the total size of the relation on disk. This size includes
219		* associated indexes size.
220		*
221		* @param string $schema
222		* @param string $name
223		* @return string
224		*/
225		public function getTableTotalSizeOnDisk($schema, $name)
226		{
227		$where = new Where(
228		'',
229		[
230		sprintf(
231		'%s.%s',
232		$this->getSession()->getConnection()->escapeIdentifier($schema),
233		$this->getSession()->getConnection()->escapeIdentifier($name)
234		)]
235		);
236
237		return $this
238		->executeSql(
239		"select pg_total_relation_size($*::regclass) as total_size",
240		$where
241		)->current()['total_size'];
242		}
243		}
244

pomm-project / Foundation

Push — master ( 0e7e9d...eea054 )

RelationInspector::getRelationsInSchema() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like