SQL::sequenceList() - Code Metrics - aviat4ion/Query - Measure and Improve Code Quality continuously with Scrutinizer

SQL::sequenceList() A
last analyzed 2019-12-10 17:36 UTC

↳ Parent: SQL

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
nc	1
nop	0
dl	0
loc	9
rs	9.9666
c	0
b	0
f	0

<?php declare(strict_types=1);
/**
 * Query
 *
 * SQL Query Builder / Database Abstraction Layer
 *
 * PHP version 7.1
 *
 * @package     Query
 * @author      Timothy J. Warren <[email protected]>
 * @copyright   2012 - 2018 Timothy J. Warren
 * @license     http://www.opensource.org/licenses/mit-license.html  MIT License
 * @link        https://git.timshomepage.net/aviat4ion/Query
 */
namespace Query\Drivers\Pgsql;

use Query\Drivers\AbstractSQL;

/**
 * PostgreSQL specific SQL
 */
class SQL extends AbstractSQL {

	/**
	 * Get the query plan for the sql query
	 *
	 * @param string $sql
	 * @return string
	 */
	public function explain(string $sql): string
	{
		return "EXPLAIN VERBOSE {$sql}";
	}

	/**
	 * Random ordering keyword
	 *
	 * @return string
	 */
	public function random(): string
	{
		return ' RANDOM()';
	}

	/**
	 * Returns sql to list other databases
	 *
	 * @return string
	 */
	public function dbList(): string
	{
		return <<<SQL
			SELECT "datname" FROM "pg_database"
			WHERE "datname" NOT IN ('template0','template1')
			ORDER BY "datname" ASC
SQL;
	}

	/**
	 * Returns sql to list tables
	 *
	 * @return string
	 */
	public function tableList(): string
	{
		return <<<SQL
			SELECT "table_name"
			FROM "information_schema"."tables"
			WHERE "table_type" = 'BASE TABLE'
			AND "table_schema" NOT IN
				('pg_catalog', 'information_schema');
SQL;
	}

	/**
	 * Returns sql to list system tables
	 *
	 * @return string
	 */
	public function systemTableList(): string
	{
		return <<<SQL
			SELECT "table_name"
			FROM "information_schema"."tables"
			WHERE "table_type" = 'BASE TABLE'
			AND "table_schema" IN
				('pg_catalog', 'information_schema');
SQL;
	}

	/**
	 * Returns sql to list views
	 *
	 * @return string
	 */
	public function viewList(): string
	{
		return <<<SQL
		 	SELECT "viewname" FROM "pg_views"
			WHERE "schemaname" NOT IN
				('pg_catalog', 'information_schema')
			AND "viewname" !~ '^pg_'
			ORDER BY "viewname" ASC
SQL;
	}

	/**
	 * Returns sql to list triggers
	 *
	 * @return string
	 */
	public function triggerList(): string
	{
		return <<<SQL
			SELECT *
			FROM "information_schema"."triggers"
			WHERE "trigger_schema" NOT IN
				('pg_catalog', 'information_schema')
SQL;
	}

	/**
	 * Return sql to list functions
	 *
	 * @return string
	 */
	public function functionList(): ?string
	{
		return NULL;
	}

	/**
	 * Return sql to list stored procedures
	 *
	 * @return string
	 */
	public function procedureList(): string
	{
		return <<<SQL
			SELECT "routine_name"
			FROM "information_schema"."routines"
			WHERE "specific_schema" NOT IN
				('pg_catalog', 'information_schema')
			AND "type_udt_name" != 'trigger';
SQL;
	}

	/**
	 * Return sql to list sequences
	 *
	 * @return string
	 */
	public function sequenceList(): string
	{
		return <<<SQL
			SELECT "c"."relname"
			FROM "pg_class" "c"
			WHERE "c"."relkind" = 'S'
			ORDER BY "relname" ASC
SQL;
	}

	/**
	 * Return sql to list columns of the specified table
	 *
	 * @param string $table
	 * @return string
	 */
	public function columnList(string $table): string
	{
		return <<<SQL
			SELECT ordinal_position,
				column_name,
				data_type,
				column_default,
				is_nullable,
				character_maximum_length,
				numeric_precision
			FROM information_schema.columns
			WHERE table_name = '{$table}'
			ORDER BY ordinal_position;
SQL;
	}

	/**
	 * SQL to show list of field types
	 *
	 * @return string
	 */
	public function typeList(): string
	{
		return <<<SQL
			SELECT "typname" FROM "pg_catalog"."pg_type"
			WHERE "typname" !~ '^pg_|_'
			AND "typtype" = 'b'
			ORDER BY "typname"
SQL;
	}

	/**
	 * Get the list of foreign keys for the current
	 * table
	 *
	 * @param string $table
	 * @return string
	 */
	public function fkList(string $table): string
	{
		return <<<SQL
			SELECT
				"att2"."attname" AS "child_column",
				"cl"."relname" AS "parent_table",
				"att"."attname" AS "parent_column",
				"con"."update" AS "update",
				"con"."update" AS "delete"
			FROM
				(SELECT
					unnest(con1.conkey) AS "parent",
					unnest(con1.confkey) AS "child",
					"con1"."confrelid",
					"con1"."conrelid",
					"con1"."confupdtype" as "update",
					"con1"."confdeltype" as "delete"
				FROM "pg_class" "cl"
				JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid"
				JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid"
				WHERE "cl"."relname" = '{$table}'
					AND "ns"."nspname" = 'public'
					AND "con1"."contype" = 'f'
				)
				"con"
				JOIN "pg_attribute" "att" ON
					"att"."attrelid" = "con"."confrelid"
					AND "att"."attnum" = "con"."child"
				JOIN "pg_class" "cl" ON
					"cl"."oid" = "con"."confrelid"
				JOIN "pg_attribute" "att2" ON
					"att2"."attrelid" = "con"."conrelid"
					AND "att2"."attnum" = "con"."parent"
SQL;
	}

	/**
	 * Get the list of indexes for the current table
	 *
	 * @param string $table
	 * @return string
	 */
	public function indexList(string $table): string
	{
		return <<<SQL
			SELECT
				t.relname AS table_name,
				i.relname AS index_name,
				array_to_string(array_agg(a.attname), ', ') AS column_names
			FROM
				pg_class t,
				pg_class i,
				pg_index ix,
				pg_attribute a
			WHERE
				t.oid = ix.indrelid
				AND i.oid = ix.indexrelid
				AND a.attrelid = t.oid
				AND a.attnum = ANY(ix.indkey)
				AND t.relkind = 'r'
				AND t.relname = '{$table}'
			GROUP BY
				t.relname,
				i.relname
			ORDER BY
				t.relname,
				i.relname;
SQL;
	}
}

1			<?php declare(strict_types=1);
2			/**
3			* Query
4			*
5			* SQL Query Builder / Database Abstraction Layer
6			*
7			* PHP version 7.1
8			*
9			* @package Query
10			* @author Timothy J. Warren <[email protected]>
11			* @copyright 2012 - 2018 Timothy J. Warren
12			* @license http://www.opensource.org/licenses/mit-license.html MIT License
13			* @link https://git.timshomepage.net/aviat4ion/Query
14			*/
15			namespace Query\Drivers\Pgsql;
16
17			use Query\Drivers\AbstractSQL;
18
19			/**
20			* PostgreSQL specific SQL
21			*/
22			class SQL extends AbstractSQL {
23
24			/**
25			* Get the query plan for the sql query
26			*
27			* @param string $sql
28			* @return string
29			*/
30			public function explain(string $sql): string
31			{
32			return "EXPLAIN VERBOSE {$sql}";
33			}
34
35			/**
36			* Random ordering keyword
37			*
38			* @return string
39			*/
40			public function random(): string
41			{
42			return ' RANDOM()';
43			}
44
45			/**
46			* Returns sql to list other databases
47			*
48			* @return string
49			*/
50			public function dbList(): string
51			{
52			return <<<SQL
53			SELECT "datname" FROM "pg_database"
54			WHERE "datname" NOT IN ('template0','template1')
55			ORDER BY "datname" ASC
56			SQL;
57			}
58
59			/**
60			* Returns sql to list tables
61			*
62			* @return string
63			*/
64			public function tableList(): string
65			{
66			return <<<SQL
67			SELECT "table_name"
68			FROM "information_schema"."tables"
69			WHERE "table_type" = 'BASE TABLE'
70			AND "table_schema" NOT IN
71			('pg_catalog', 'information_schema');
72			SQL;
73			}
74
75			/**
76			* Returns sql to list system tables
77			*
78			* @return string
79			*/
80			public function systemTableList(): string
81			{
82			return <<<SQL
83			SELECT "table_name"
84			FROM "information_schema"."tables"
85			WHERE "table_type" = 'BASE TABLE'
86			AND "table_schema" IN
87			('pg_catalog', 'information_schema');
88			SQL;
89			}
90
91			/**
92			* Returns sql to list views
93			*
94			* @return string
95			*/
96			public function viewList(): string
97			{
98			return <<<SQL
99			SELECT "viewname" FROM "pg_views"
100			WHERE "schemaname" NOT IN
101			('pg_catalog', 'information_schema')
102			AND "viewname" !~ '^pg_'
103			ORDER BY "viewname" ASC
104			SQL;
105			}
106
107			/**
108			* Returns sql to list triggers
109			*
110			* @return string
111			*/
112			public function triggerList(): string
113			{
114			return <<<SQL
115			SELECT *
116			FROM "information_schema"."triggers"
117			WHERE "trigger_schema" NOT IN
118			('pg_catalog', 'information_schema')
119			SQL;
120			}
121
122			/**
123			* Return sql to list functions
124			*
125			* @return string
126			*/
127			public function functionList(): ?string
128			{
129			return NULL;
130			}
131
132			/**
133			* Return sql to list stored procedures
134			*
135			* @return string
136			*/
137			public function procedureList(): string
138			{
139			return <<<SQL
140			SELECT "routine_name"
141			FROM "information_schema"."routines"
142			WHERE "specific_schema" NOT IN
143			('pg_catalog', 'information_schema')
144			AND "type_udt_name" != 'trigger';
145			SQL;
146			}
147
148			/**
149			* Return sql to list sequences
150			*
151			* @return string
152			*/
153			public function sequenceList(): string
154			{
155			return <<<SQL
156			SELECT "c"."relname"
157			FROM "pg_class" "c"
158			WHERE "c"."relkind" = 'S'
159			ORDER BY "relname" ASC
160			SQL;
161			}
162
163			/**
164			* Return sql to list columns of the specified table
165			*
166			* @param string $table
167			* @return string
168			*/
169			public function columnList(string $table): string
170			{
171			return <<<SQL
172			SELECT ordinal_position,
173			column_name,
174			data_type,
175			column_default,
176			is_nullable,
177			character_maximum_length,
178			numeric_precision
179			FROM information_schema.columns
180			WHERE table_name = '{$table}'
181			ORDER BY ordinal_position;
182			SQL;
183			}
184
185			/**
186			* SQL to show list of field types
187			*
188			* @return string
189			*/
190			public function typeList(): string
191			{
192			return <<<SQL
193			SELECT "typname" FROM "pg_catalog"."pg_type"
194			WHERE "typname" !~ '^pg_\|_'
195			AND "typtype" = 'b'
196			ORDER BY "typname"
197			SQL;
198			}
199
200			/**
201			* Get the list of foreign keys for the current
202			* table
203			*
204			* @param string $table
205			* @return string
206			*/
207			public function fkList(string $table): string
208			{
209			return <<<SQL
210			SELECT
211			"att2"."attname" AS "child_column",
212			"cl"."relname" AS "parent_table",
213			"att"."attname" AS "parent_column",
214			"con"."update" AS "update",
215			"con"."update" AS "delete"
216			FROM
217			(SELECT
218			unnest(con1.conkey) AS "parent",
219			unnest(con1.confkey) AS "child",
220			"con1"."confrelid",
221			"con1"."conrelid",
222			"con1"."confupdtype" as "update",
223			"con1"."confdeltype" as "delete"
224			FROM "pg_class" "cl"
225			JOIN "pg_namespace" "ns" ON "cl"."relnamespace" = "ns"."oid"
226			JOIN "pg_constraint" "con1" ON "con1"."conrelid" = "cl"."oid"
227			WHERE "cl"."relname" = '{$table}'
228			AND "ns"."nspname" = 'public'
229			AND "con1"."contype" = 'f'
230			)
231			"con"
232			JOIN "pg_attribute" "att" ON
233			"att"."attrelid" = "con"."confrelid"
234			AND "att"."attnum" = "con"."child"
235			JOIN "pg_class" "cl" ON
236			"cl"."oid" = "con"."confrelid"
237			JOIN "pg_attribute" "att2" ON
238			"att2"."attrelid" = "con"."conrelid"
239			AND "att2"."attnum" = "con"."parent"
240			SQL;
241			}
242
243			/**
244			* Get the list of indexes for the current table
245			*
246			* @param string $table
247			* @return string
248			*/
249			public function indexList(string $table): string
250			{
251			return <<<SQL
252			SELECT
253			t.relname AS table_name,
254			i.relname AS index_name,
255			array_to_string(array_agg(a.attname), ', ') AS column_names
256			FROM
257			pg_class t,
258			pg_class i,
259			pg_index ix,
260			pg_attribute a
261			WHERE
262			t.oid = ix.indrelid
263			AND i.oid = ix.indexrelid
264			AND a.attrelid = t.oid
265			AND a.attnum = ANY(ix.indkey)
266			AND t.relkind = 'r'
267			AND t.relname = '{$table}'
268			GROUP BY
269			t.relname,
270			i.relname
271			ORDER BY
272			t.relname,
273			i.relname;
274			SQL;
275			}
276			}

aviat4ion / Query

SQL::sequenceList() A last analyzed 2019-12-10 17:36 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SQL::sequenceList() A
last analyzed 2019-12-10 17:36 UTC