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

SQL::table_list() A
last analyzed 2016-07-19 19:25 UTC

↳ Parent: SQL

Complexity

Conditions	1
Paths	1

Size

Total Lines	10
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes	2
Bugs	1	Features	0

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

<?php
/**
 * Query
 *
 * Free Query Builder / Database Abstraction Layer
 *
 * @package		Query
 * @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\Firebird;

/**
 * Firebird Specific SQL
 *
 * @package Query
 * @subpackage Drivers
 */
class SQL extends \Query\AbstractSQL {

	/**
	 * Limit clause
	 *
	 * @param string $sql
	 * @param int $limit
	 * @param int|bool $offset
	 * @return string
	 */
	public function limit($sql, $limit, $offset=FALSE)
	{
		// Keep the current sql string safe for a moment
		$orig_sql = $sql;

		$sql = 'FIRST '. (int) $limit;

		if ($offset > 0)
		{
			$sql .= ' SKIP '. (int) $offset;
		}

		$sql = preg_replace("`SELECT`i", "SELECT {$sql}", $orig_sql);

		return $sql;
	}

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

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

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

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


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

	/**
	 * Returns sql to list other databases
	 *
	 * @return NULL
	 */
	public function db_list()
	{
		return NULL;
	}

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

	/**
	 * Returns sql to list tables
	 *
	 * @return string
	 */
	public function table_list()
	{
		return <<<SQL
			SELECT TRIM("RDB\$RELATION_NAME")
			FROM "RDB\$RELATIONS"
			WHERE "RDB\$SYSTEM_FLAG"=0
			AND "RDB\$VIEW_BLR" IS NULL
			ORDER BY "RDB\$RELATION_NAME" ASC
SQL;
	}

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

	/**
	 * Returns sql to list system tables
	 *
	 * @return string
	 */
	public function system_table_list()
	{
		return <<<SQL
			SELECT TRIM("RDB\$RELATION_NAME")
			FROM "RDB\$RELATIONS"
			WHERE "RDB\$SYSTEM_FLAG"=1
			ORDER BY "RDB\$RELATION_NAME" ASC
SQL;
	}

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

	/**
	 * Returns sql to list views
	 *
	 * @return string
	 */
	public function view_list()
	{
		return <<<SQL
			SELECT DISTINCT TRIM("RDB\$VIEW_NAME")
			FROM "RDB\$VIEW_RELATIONS"
SQL;
	}

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

	/**
	 * Returns sql to list triggers
	 *
	 * @return string
	 */
	public function trigger_list()
	{
		return <<<SQL
			SELECT * FROM "RDB\$FUNCTIONS"
			WHERE "RDB\$SYSTEM_FLAG" = 0
SQL;
	}

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

	/**
	 * Return sql to list functions
	 *
	 * @return string
	 */
	public function function_list()
	{
		return 'SELECT * FROM "RDB$FUNCTIONS"';
	}

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

	/**
	 * Return sql to list stored procedures
	 *
	 * @return string
	 */
	public function procedure_list()
	{
		return <<<SQL
			SELECT "RDB\$PROCEDURE_NAME",
				"RDB\$PROCEDURE_ID",
				"RDB\$PROCEDURE_INPUTS",
				"RDB\$PROCEDURE_OUTPUTS",
				"RDB\$DESCRIPTION",
				"RDB\$PROCEDURE_SOURCE",
				"RDB\$SECURITY_CLASS",
				"RDB\$OWNER_NAME",
				"RDB\$RUNTIME",
				"RDB\$SYSTEM_FLAG",
				"RDB\$PROCEDURE_TYPE",
				"RDB\$VALID_BLR"
			FROM "RDB\$PROCEDURES"
			ORDER BY "RDB\$PROCEDURE_NAME" ASC
SQL;

	}

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

	/**
	 * Return sql to list sequences
	 *
	 * @return string
	 */
	public function sequence_list()
	{
		return <<<SQL
			SELECT TRIM("RDB\$GENERATOR_NAME")
			FROM "RDB\$GENERATORS"
			WHERE "RDB\$SYSTEM_FLAG" = 0
SQL;
	}

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

	/**
	 * Return sql to list columns of the specified table
	 *
	 * @param string $table
	 * @return string
	 */
	public function column_list($table)
	{
		return <<<SQL
			SELECT r.RDB\$FIELD_NAME AS field_name,
				r.RDB\$DESCRIPTION AS field_description,
				r.RDB\$DEFAULT_VALUE AS field_default_value,
				r.RDB\$NULL_FLAG AS field_not_null_constraint,
				f.RDB\$FIELD_LENGTH AS field_length,
				f.RDB\$FIELD_PRECISION AS field_precision,
				f.RDB\$FIELD_SCALE AS field_scale,
				CASE f.RDB\$FIELD_TYPE
					WHEN 261 THEN 'BLOB'
					WHEN 14 THEN 'CHAR'
					WHEN 40 THEN 'CSTRING'
					WHEN 11 THEN 'D_FLOAT'
					WHEN 27 THEN 'DOUBLE'
					WHEN 10 THEN 'FLOAT'
					WHEN 16 THEN 'INT64'
					WHEN 8 THEN 'INTEGER'
					WHEN 9 THEN 'QUAD'
					WHEN 7 THEN 'SMALLINT'
					WHEN 12 THEN 'DATE'
					WHEN 13 THEN 'TIME'
					WHEN 35 THEN 'TIMESTAMP'
					WHEN 37 THEN 'VARCHAR'
				ELSE 'UNKNOWN'
				END AS field_type,
				f.RDB\$FIELD_SUB_TYPE AS field_subtype,
				coll.RDB\$COLLATION_NAME AS field_collation,
				cset.RDB\$CHARACTER_SET_NAME AS field_charset
			FROM RDB\$RELATION_FIELDS r
			LEFT JOIN RDB\$FIELDS f ON r.RDB\$FIELD_SOURCE = f.RDB\$FIELD_NAME
			LEFT JOIN RDB\$COLLATIONS coll ON f.RDB\$COLLATION_ID = coll.RDB\$COLLATION_ID
			LEFT JOIN RDB\$CHARACTER_SETS cset ON f.RDB\$CHARACTER_SET_ID = cset.RDB\$CHARACTER_SET_ID
			WHERE r.RDB\$RELATION_NAME='{$table}'
			ORDER BY r.RDB\$FIELD_POSITION
SQL;
	}

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

	/**
	 * SQL to show list of field types
	 *
	 * @return string
	 */
	public function type_list()
	{
		return <<<SQL
			SELECT "RDB\$TYPE_NAME", "RDB\$FIELD_NAME" FROM "RDB\$TYPES"
			WHERE "RDB\$FIELD_NAME" IN ('RDB\$FIELD_TYPE', 'RDB\$FIELD_SUB_TYPE')
			ORDER BY "RDB\$FIELD_NAME" DESC, "RDB\$TYPE_NAME" ASC
SQL;
	}

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

	/**
	 * Get the list of foreign keys for the current
	 * table
	 *
	 * @param string $table
	 * @return string
	 */
	public function fk_list($table)
	{
		return <<<SQL
		SELECT DISTINCT
			TRIM(d1.RDB\$FIELD_NAME) AS "child_column",
			TRIM(d2.RDB\$DEPENDED_ON_NAME) AS "parent_table",
			TRIM(d2.RDB\$FIELD_NAME) AS "parent_column",
			TRIM(refc.RDB\$UPDATE_RULE) AS "update",
			TRIM(refc.RDB\$DELETE_RULE) AS "delete"
		FROM RDB\$RELATION_CONSTRAINTS AS rc
		LEFT JOIN RDB\$REF_CONSTRAINTS refc ON rc.RDB\$CONSTRAINT_NAME = refc.RDB\$CONSTRAINT_NAME
		LEFT JOIN RDB\$DEPENDENCIES d1 ON d1.RDB\$DEPENDED_ON_NAME = rc.RDB\$RELATION_NAME
		LEFT JOIN RDB\$DEPENDENCIES d2 ON d1.RDB\$DEPENDENT_NAME = d2.RDB\$DEPENDENT_NAME
		WHERE rc.RDB\$CONSTRAINT_TYPE = 'FOREIGN KEY'
			AND d1.RDB\$DEPENDED_ON_NAME <> d2.RDB\$DEPENDED_ON_NAME
			AND d1.RDB\$FIELD_NAME <> d2.RDB\$FIELD_NAME
			AND rc.RDB\$RELATION_NAME = '{$table}'  -- table name
SQL;
	}

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

	/**
	 * Get the list of indexes for the current table
	 *
	 * @param string $table
	 * @return array
	 */
	public function index_list($table)
	{
		return <<<SQL
			SELECT "RDB\$INDEX_NAME", "RDB\$UNIQUE_FLAG", "RDB\$FOREIGN_KEY"
			FROM "RDB\$INDICES"
			WHERE "RDB\$RELATION_NAME"='{$table}'
SQL;
	}
}
//End of firebird_sql.php

1			<?php
2			/**
3			* Query
4			*
5			* Free Query Builder / Database Abstraction Layer
6			*
7			* @package Query
8			* @author Timothy J. Warren
9			* @copyright Copyright (c) 2012 - 2015
10			* @link https://github.com/aviat4ion/Query
11			* @license http://philsturgeon.co.uk/code/dbad-license
12			*/
13
14			// --------------------------------------------------------------------------
15
16			namespace Query\Drivers\Firebird;
17
18			/**
19			* Firebird Specific SQL
20			*
21			* @package Query
22			* @subpackage Drivers
23			*/
24			class SQL extends \Query\AbstractSQL {
25
26			/**
27			* Limit clause
28			*
29			* @param string $sql
30			* @param int $limit
31			* @param int\|bool $offset
32			* @return string
33			*/
34			public function limit($sql, $limit, $offset=FALSE)
35			{
36			// Keep the current sql string safe for a moment
37			$orig_sql = $sql;
38
39			$sql = 'FIRST '. (int) $limit;
40
41			if ($offset > 0)
42			{
43			$sql .= ' SKIP '. (int) $offset;
44			}
45
46			$sql = preg_replace("`SELECT`i", "SELECT {$sql}", $orig_sql);
47
48			return $sql;
49			}
50
51			// --------------------------------------------------------------------------
52
53			/**
54			* Get the query plan for the sql query
55			*
56			* @param string $sql
57			* @return string
58			*/
59			public function explain($sql)
60			{
61			return $sql;
62			}
63
64			// --------------------------------------------------------------------------
65
66			/**
67			* Random ordering keyword
68			*
69			* @return string
70			*/
71			public function random()
72			{
73			return NULL;
74			}
75
76
77			// --------------------------------------------------------------------------
78
79			/**
80			* Returns sql to list other databases
81			*
82			* @return NULL
83			*/
84			public function db_list()
85			{
86			return NULL;
87			}
88
89			// --------------------------------------------------------------------------
90
91			/**
92			* Returns sql to list tables
93			*
94			* @return string
95			*/
96			public function table_list()
97			{
98			return <<<SQL
99			SELECT TRIM("RDB\$RELATION_NAME")
100			FROM "RDB\$RELATIONS"
101			WHERE "RDB\$SYSTEM_FLAG"=0
102			AND "RDB\$VIEW_BLR" IS NULL
103			ORDER BY "RDB\$RELATION_NAME" ASC
104			SQL;
105			}
106
107			// --------------------------------------------------------------------------
108
109			/**
110			* Returns sql to list system tables
111			*
112			* @return string
113			*/
114			public function system_table_list()
115			{
116			return <<<SQL
117			SELECT TRIM("RDB\$RELATION_NAME")
118			FROM "RDB\$RELATIONS"
119			WHERE "RDB\$SYSTEM_FLAG"=1
120			ORDER BY "RDB\$RELATION_NAME" ASC
121			SQL;
122			}
123
124			// --------------------------------------------------------------------------
125
126			/**
127			* Returns sql to list views
128			*
129			* @return string
130			*/
131			public function view_list()
132			{
133			return <<<SQL
134			SELECT DISTINCT TRIM("RDB\$VIEW_NAME")
135			FROM "RDB\$VIEW_RELATIONS"
136			SQL;
137			}
138
139			// --------------------------------------------------------------------------
140
141			/**
142			* Returns sql to list triggers
143			*
144			* @return string
145			*/
146			public function trigger_list()
147			{
148			return <<<SQL
149			SELECT * FROM "RDB\$FUNCTIONS"
150			WHERE "RDB\$SYSTEM_FLAG" = 0
151			SQL;
152			}
153
154			// --------------------------------------------------------------------------
155
156			/**
157			* Return sql to list functions
158			*
159			* @return string
160			*/
161			public function function_list()
162			{
163			return 'SELECT * FROM "RDB$FUNCTIONS"';
164			}
165
166			// --------------------------------------------------------------------------
167
168			/**
169			* Return sql to list stored procedures
170			*
171			* @return string
172			*/
173			public function procedure_list()
174			{
175			return <<<SQL
176			SELECT "RDB\$PROCEDURE_NAME",
177			"RDB\$PROCEDURE_ID",
178			"RDB\$PROCEDURE_INPUTS",
179			"RDB\$PROCEDURE_OUTPUTS",
180			"RDB\$DESCRIPTION",
181			"RDB\$PROCEDURE_SOURCE",
182			"RDB\$SECURITY_CLASS",
183			"RDB\$OWNER_NAME",
184			"RDB\$RUNTIME",
185			"RDB\$SYSTEM_FLAG",
186			"RDB\$PROCEDURE_TYPE",
187			"RDB\$VALID_BLR"
188			FROM "RDB\$PROCEDURES"
189			ORDER BY "RDB\$PROCEDURE_NAME" ASC
190			SQL;
191
192			}
193
194			// --------------------------------------------------------------------------
195
196			/**
197			* Return sql to list sequences
198			*
199			* @return string
200			*/
201			public function sequence_list()
202			{
203			return <<<SQL
204			SELECT TRIM("RDB\$GENERATOR_NAME")
205			FROM "RDB\$GENERATORS"
206			WHERE "RDB\$SYSTEM_FLAG" = 0
207			SQL;
208			}
209
210			// --------------------------------------------------------------------------
211
212			/**
213			* Return sql to list columns of the specified table
214			*
215			* @param string $table
216			* @return string
217			*/
218			public function column_list($table)
219			{
220			return <<<SQL
221			SELECT r.RDB\$FIELD_NAME AS field_name,
222			r.RDB\$DESCRIPTION AS field_description,
223			r.RDB\$DEFAULT_VALUE AS field_default_value,
224			r.RDB\$NULL_FLAG AS field_not_null_constraint,
225			f.RDB\$FIELD_LENGTH AS field_length,
226			f.RDB\$FIELD_PRECISION AS field_precision,
227			f.RDB\$FIELD_SCALE AS field_scale,
228			CASE f.RDB\$FIELD_TYPE
229			WHEN 261 THEN 'BLOB'
230			WHEN 14 THEN 'CHAR'
231			WHEN 40 THEN 'CSTRING'
232			WHEN 11 THEN 'D_FLOAT'
233			WHEN 27 THEN 'DOUBLE'
234			WHEN 10 THEN 'FLOAT'
235			WHEN 16 THEN 'INT64'
236			WHEN 8 THEN 'INTEGER'
237			WHEN 9 THEN 'QUAD'
238			WHEN 7 THEN 'SMALLINT'
239			WHEN 12 THEN 'DATE'
240			WHEN 13 THEN 'TIME'
241			WHEN 35 THEN 'TIMESTAMP'
242			WHEN 37 THEN 'VARCHAR'
243			ELSE 'UNKNOWN'
244			END AS field_type,
245			f.RDB\$FIELD_SUB_TYPE AS field_subtype,
246			coll.RDB\$COLLATION_NAME AS field_collation,
247			cset.RDB\$CHARACTER_SET_NAME AS field_charset
248			FROM RDB\$RELATION_FIELDS r
249			LEFT JOIN RDB\$FIELDS f ON r.RDB\$FIELD_SOURCE = f.RDB\$FIELD_NAME
250			LEFT JOIN RDB\$COLLATIONS coll ON f.RDB\$COLLATION_ID = coll.RDB\$COLLATION_ID
251			LEFT JOIN RDB\$CHARACTER_SETS cset ON f.RDB\$CHARACTER_SET_ID = cset.RDB\$CHARACTER_SET_ID
252			WHERE r.RDB\$RELATION_NAME='{$table}'
253			ORDER BY r.RDB\$FIELD_POSITION
254			SQL;
255			}
256
257			// --------------------------------------------------------------------------
258
259			/**
260			* SQL to show list of field types
261			*
262			* @return string
263			*/
264			public function type_list()
265			{
266			return <<<SQL
267			SELECT "RDB\$TYPE_NAME", "RDB\$FIELD_NAME" FROM "RDB\$TYPES"
268			WHERE "RDB\$FIELD_NAME" IN ('RDB\$FIELD_TYPE', 'RDB\$FIELD_SUB_TYPE')
269			ORDER BY "RDB\$FIELD_NAME" DESC, "RDB\$TYPE_NAME" ASC
270			SQL;
271			}
272
273			// --------------------------------------------------------------------------
274
275			/**
276			* Get the list of foreign keys for the current
277			* table
278			*
279			* @param string $table
280			* @return string
281			*/
282			public function fk_list($table)
283			{
284			return <<<SQL
285			SELECT DISTINCT
286			TRIM(d1.RDB\$FIELD_NAME) AS "child_column",
287			TRIM(d2.RDB\$DEPENDED_ON_NAME) AS "parent_table",
288			TRIM(d2.RDB\$FIELD_NAME) AS "parent_column",
289			TRIM(refc.RDB\$UPDATE_RULE) AS "update",
290			TRIM(refc.RDB\$DELETE_RULE) AS "delete"
291			FROM RDB\$RELATION_CONSTRAINTS AS rc
292			LEFT JOIN RDB\$REF_CONSTRAINTS refc ON rc.RDB\$CONSTRAINT_NAME = refc.RDB\$CONSTRAINT_NAME
293			LEFT JOIN RDB\$DEPENDENCIES d1 ON d1.RDB\$DEPENDED_ON_NAME = rc.RDB\$RELATION_NAME
294			LEFT JOIN RDB\$DEPENDENCIES d2 ON d1.RDB\$DEPENDENT_NAME = d2.RDB\$DEPENDENT_NAME
295			WHERE rc.RDB\$CONSTRAINT_TYPE = 'FOREIGN KEY'
296			AND d1.RDB\$DEPENDED_ON_NAME <> d2.RDB\$DEPENDED_ON_NAME
297			AND d1.RDB\$FIELD_NAME <> d2.RDB\$FIELD_NAME
298			AND rc.RDB\$RELATION_NAME = '{$table}' -- table name
299			SQL;
300			}
301
302			// --------------------------------------------------------------------------
303
304			/**
305			* Get the list of indexes for the current table
306			*
307			* @param string $table
308			* @return array
309			*/
310			public function index_list($table)
311			{
312			return <<<SQL
313			SELECT "RDB\$INDEX_NAME", "RDB\$UNIQUE_FLAG", "RDB\$FOREIGN_KEY"
314			FROM "RDB\$INDICES"
315			WHERE "RDB\$RELATION_NAME"='{$table}'
316			SQL;
317			}
318			}
319			//End of firebird_sql.php

timw4mail / Query

SQL::table_list() A last analyzed 2016-07-19 19:25 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SQL::table_list() A
last analyzed 2016-07-19 19:25 UTC