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

SQL::fk_list() B
last analyzed 2016-07-19 19:25 UTC

↳ Parent: SQL

Complexity

Conditions	1
Paths	1

Size

Total Lines	35
Code Lines	5

Duplication

Lines	0
Ratio	0 %

Importance

Changes	3
Bugs	2	Features	0

Metric	Value
c	3
b	2
f	0
dl	0
loc	35
rs	8.8571
cc	1
eloc	5
nc	1
nop	1

<?php
/**
 * Query
 *
 * Free Query Builder / Database Abstraction Layer
 *
 * @author 		Timothy J. Warren
 * @copyright	Copyright (c) 2012 - 2015
 * @link 		https://github.com/aviat4ion/Query
 * @license 	http://philsturgeon.co.uk/code/dbad-license
 */

// --------------------------------------------------------------------------

namespace Query\Drivers\Pgsql;

/**
 * PostgreSQL specifc SQL
 *
 * @package Query
 * @subpackage Drivers
 */
class SQL extends \Query\AbstractSQL {

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

	// --------------------------------------------------------------------------

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

	// --------------------------------------------------------------------------

	/**
	 * Returns sql to list other databases
	 *
	 * @return string
	 */
	public function db_list()
	{
		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 table_list()
	{
		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 system_table_list()
	{
		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 view_list()
	{
		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 trigger_list()
	{
		return <<<SQL
			SELECT *
			FROM "information_schema"."triggers"
			WHERE "trigger_schema" NOT IN
				('pg_catalog', 'information_schema')
SQL;
	}

	// --------------------------------------------------------------------------

	/**
	 * Return sql to list functions
	 *
	 * @return NULL
	 */
	public function function_list()
	{
		return NULL;
	}

	// --------------------------------------------------------------------------

	/**
	 * Return sql to list stored procedures
	 *
	 * @return string
	 */
	public function procedure_list()
	{
		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 sequence_list()
	{
		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 column_list($table)
	{
		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 type_list()
	{
		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 fk_list($table)
	{
		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 array
	 */
	public function index_list($table)
	{
		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;
	}
}
//End of pgsql_sql.php

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

timw4mail / Query

SQL::fk_list() B last analyzed 2016-07-19 19:25 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SQL::fk_list() B
last analyzed 2016-07-19 19:25 UTC