Issues in DbExtra-postgresql.php - New Issues - Inspection of "Merge pull request #4238 from albertlast/SearchFas..." - albertlast/SMF2.1 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — release-2.1 ( 6f6d35...abeae7 )

by Mathias

created 2017-08-23 04:36 UTC

Sources/DbExtra-postgresql.php (1 issue)

Labels

Duplication 1

Severity

Informational 1

Upgrade to new PHP Analysis Engine

These results are based on our legacy PHP analysis, consider migrating to our new PHP analysis engine instead. Learn more

<?php

/**
 * This file contains rarely used extended database functionality.
 *
 * Simple Machines Forum (SMF)
 *
 * @package SMF
 * @author Simple Machines http://www.simplemachines.org
 * @copyright 2017 Simple Machines and individual contributors
 * @license http://www.simplemachines.org/about/smf/license.php BSD
 *
 * @version 2.1 Beta 4
 */

if (!defined('SMF'))
	die('No direct access...');

/**
 * Add the functions implemented in this file to the $smcFunc array.
 */
function db_extra_init()
{
	global $smcFunc;

	if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')

		$smcFunc += array(
			'db_backup_table' => 'smf_db_backup_table',
			'db_optimize_table' => 'smf_db_optimize_table',
			'db_table_sql' => 'smf_db_table_sql',
			'db_list_tables' => 'smf_db_list_tables',
			'db_get_version' => 'smf_db_get_version',
			'db_get_engine' => 'smf_db_get_engine',
		);
}

/**
 * Backup $table to $backup_table.
 * @param string $table The name of the table to backup
 * @param string $backup_table The name of the backup table for this table
 * @return resource -the request handle to the table creation query
 */
function smf_db_backup_table($table, $backup_table)
{
	global $smcFunc, $db_prefix;

	$table = str_replace('{db_prefix}', $db_prefix, $table);

	// Do we need to drop it first?
	$tables = smf_db_list_tables(false, $backup_table);
	if (!empty($tables))
		$smcFunc['db_query']('', '
			DROP TABLE {raw:backup_table}',
			array(
				'backup_table' => $backup_table,
			)
		);

	/**
	 * @todo Should we create backups of sequences as well?
	 */
	$smcFunc['db_query']('', '
		CREATE TABLE {raw:backup_table}
		(
			LIKE {raw:table}
			INCLUDING DEFAULTS
		)',
		array(
			'backup_table' => $backup_table,
			'table' => $table,
		)
	);
	$smcFunc['db_query']('', '
		INSERT INTO {raw:backup_table}
		SELECT * FROM {raw:table}',
		array(
			'backup_table' => $backup_table,
			'table' => $table,
		)
	);
}

/**
 * This function optimizes a table.
 * @param string $table The table to be optimized
 * @return int How much space was gained
 */
function smf_db_optimize_table($table)
{
	global $smcFunc, $db_prefix;

	$table = str_replace('{db_prefix}', $db_prefix, $table);

	$pg_tables = array('pg_catalog','information_schema');

	$request = $smcFunc['db_query']('', '
		SELECT pg_relation_size(C.oid) AS "size"
		FROM pg_class C
		LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
		WHERE nspname NOT IN ({array_string:pg_tables})
		and relname = {string:table}',
		array(
			'table' => $table,
			'pg_tables' => $pg_tables,
		)
	);

	$row = $smcFunc['db_fetch_assoc']($request);
	$smcFunc['db_free_result']($request);

	$old_size = $row['size'];

	$request = $smcFunc['db_query']('', '
			VACUUM FULL ANALYZE {raw:table}',
			array(
				'table' => $table,
			)
		);

	if (!$request)
		return -1;

	$request = $smcFunc['db_query']('', '
		SELECT pg_relation_size(C.oid) AS "size"
		FROM pg_class C
		LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
		WHERE nspname NOT IN ({array_string:pg_tables})
		and relname = {string:table}',
		array(
			'table' => $table,
			'pg_tables' => $pg_tables,
		)
	);


	$row = $smcFunc['db_fetch_assoc']($request);
	$smcFunc['db_free_result']($request);

	if (isset($row['size']))
			return ($old_size - $row['size']) / 1024;
	else
		return 0;
}

/**
 * This function lists all tables in the database.
 * The listing could be filtered according to $filter.
 *
 * @param string|boolean $db string The database name or false to use the current DB
 * @param string|boolean $filter String to filter by or false to list all tables
 * @return array An array of table names
 */
function smf_db_list_tables($db = false, $filter = false)
{
	global $smcFunc;

	$request = $smcFunc['db_query']('', '
		SELECT tablename
		FROM pg_tables
		WHERE schemaname = {string:schema_public}' . ($filter == false ? '' : '
			AND tablename LIKE {string:filter}') . '
		ORDER BY tablename',
		array(
			'schema_public' => 'public',
			'filter' => $filter,
		)
	);

	$tables = array();
	while ($row = $smcFunc['db_fetch_row']($request))
		$tables[] = $row[0];
	$smcFunc['db_free_result']($request);

	return $tables;
}

/**
 * Dumps the schema (CREATE) for a table.
 * @todo why is this needed for?
 * @param string $tableName The name of the table
 * @return string The "CREATE TABLE" SQL string for this table
 */
function smf_db_table_sql($tableName)
{
	global $smcFunc, $db_prefix;

	$tableName = str_replace('{db_prefix}', $db_prefix, $tableName);

	// This will be needed...
	$crlf = "\r\n";

	// Drop it if it exists.
	$schema_create = 'DROP TABLE IF EXISTS ' . $tableName . ';' . $crlf . $crlf;

	// Start the create table...
	$schema_create .= 'CREATE TABLE ' . $tableName . ' (' . $crlf;
	$index_create = '';
	$seq_create = '';

	// Find all the fields.
	$result = $smcFunc['db_query']('', '
		SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
		FROM information_schema.columns
		WHERE table_name = {string:table}
		ORDER BY ordinal_position',
		array(
			'table' => $tableName,
		)
	);
	while ($row = $smcFunc['db_fetch_assoc']($result))
	{
		if ($row['data_type'] == 'character varying')
			$row['data_type'] = 'varchar';
		elseif ($row['data_type'] == 'character')
			$row['data_type'] = 'char';
		if ($row['character_maximum_length'])
			$row['data_type'] .= '(' . $row['character_maximum_length'] . ')';

		// Make the CREATE for this column.
		$schema_create .= ' "' . $row['column_name'] . '" ' . $row['data_type'] . ($row['is_nullable'] != 'YES' ? ' NOT NULL' : '');

		// Add a default...?
		if (trim($row['column_default']) != '')
		{
			$schema_create .= ' default ' . $row['column_default'] . '';

			// Auto increment?
			if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
			{
				// Get to find the next variable first!
				$count_req = $smcFunc['db_query']('', '
					SELECT MAX("{raw:column}")
					FROM {raw:table}',
					array(
						'column' => $row['column_name'],
						'table' => $tableName,
					)
				);
				list ($max_ind) = $smcFunc['db_fetch_row']($count_req);
				$smcFunc['db_free_result']($count_req);
				// Get the right bloody start!
				$seq_create .= 'CREATE SEQUENCE ' . $matches[1] . ' START WITH ' . ($max_ind + 1) . ';' . $crlf . $crlf;
			}
		}

		$schema_create .= ',' . $crlf;
	}
	$smcFunc['db_free_result']($result);

	// Take off the last comma.
	$schema_create = substr($schema_create, 0, -strlen($crlf) - 1);

	$result = $smcFunc['db_query']('', '
		SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary, pg_get_indexdef(i.indexrelid) AS inddef
		FROM pg_class AS c
			INNER JOIN pg_index AS i ON (i.indrelid = c.oid)
			INNER JOIN pg_class AS c2 ON (c2.oid = i.indexrelid)
		WHERE c.relname = {string:table}',
		array(
			'table' => $tableName,
		)
	);

	while ($row = $smcFunc['db_fetch_assoc']($result))
	{
		if ($row['is_primary'])
		{
			if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0)
				continue;

			$index_create .= $crlf . 'ALTER TABLE ' . $tableName . ' ADD PRIMARY KEY ("' . $matches[1] . '");';
		}
		else
			$index_create .= $crlf . $row['inddef'] . ';';
	}
	$smcFunc['db_free_result']($result);

	// Finish it off!
	$schema_create .= $crlf . ');';

	return $seq_create . $schema_create . $index_create;
}

/**
 *  Get the version number.
 *  @return string The version
 */
function smf_db_get_version()
{
	static $ver;

	if(!empty($ver))
		return $ver;

	global $smcFunc;

	$request = $smcFunc['db_query']('', '
		SHOW server_version',
		array(
		)
	);
	list ($ver) = $smcFunc['db_fetch_row']($request);
	$smcFunc['db_free_result']($request);

	return $ver;
}

/**
 * Return PostgreSQL
 *
 * @return string The database engine we are using
*/
function smf_db_get_engine()
{
	return 'PostgreSQL';
}

?>

1		<?php
2
3		/**
4		* This file contains rarely used extended database functionality.
5		*
6		* Simple Machines Forum (SMF)
7		*
8		* @package SMF
9		* @author Simple Machines http://www.simplemachines.org
10		* @copyright 2017 Simple Machines and individual contributors
11		* @license http://www.simplemachines.org/about/smf/license.php BSD
12		*
13		* @version 2.1 Beta 4
14		*/
15
16		if (!defined('SMF'))
17		die('No direct access...');
18
19		/**
20		* Add the functions implemented in this file to the $smcFunc array.
21		*/
22		function db_extra_init()
23		{
24		global $smcFunc;
25
26	View Code Duplication	if (!isset($smcFunc['db_backup_table']) \|\| $smcFunc['db_backup_table'] != 'smf_db_backup_table')
		0 ignored issues – show Duplication introduced 2017-01-02 07:35 UTC by Report Bug Copy Issue Report Show Similar Issues like this This code seems to be duplicated across 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...
27		$smcFunc += array(
28		'db_backup_table' => 'smf_db_backup_table',
29		'db_optimize_table' => 'smf_db_optimize_table',
30		'db_table_sql' => 'smf_db_table_sql',
31		'db_list_tables' => 'smf_db_list_tables',
32		'db_get_version' => 'smf_db_get_version',
33		'db_get_engine' => 'smf_db_get_engine',
34		);
35		}
36
37		/**
38		* Backup $table to $backup_table.
39		* @param string $table The name of the table to backup
40		* @param string $backup_table The name of the backup table for this table
41		* @return resource -the request handle to the table creation query
42		*/
43		function smf_db_backup_table($table, $backup_table)
44		{
45		global $smcFunc, $db_prefix;
46
47		$table = str_replace('{db_prefix}', $db_prefix, $table);
48
49		// Do we need to drop it first?
50		$tables = smf_db_list_tables(false, $backup_table);
51		if (!empty($tables))
52		$smcFunc['db_query']('', '
53		DROP TABLE {raw:backup_table}',
54		array(
55		'backup_table' => $backup_table,
56		)
57		);
58
59		/**
60		* @todo Should we create backups of sequences as well?
61		*/
62		$smcFunc['db_query']('', '
63		CREATE TABLE {raw:backup_table}
64		(
65		LIKE {raw:table}
66		INCLUDING DEFAULTS
67		)',
68		array(
69		'backup_table' => $backup_table,
70		'table' => $table,
71		)
72		);
73		$smcFunc['db_query']('', '
74		INSERT INTO {raw:backup_table}
75		SELECT * FROM {raw:table}',
76		array(
77		'backup_table' => $backup_table,
78		'table' => $table,
79		)
80		);
81		}
82
83		/**
84		* This function optimizes a table.
85		* @param string $table The table to be optimized
86		* @return int How much space was gained
87		*/
88		function smf_db_optimize_table($table)
89		{
90		global $smcFunc, $db_prefix;
91
92		$table = str_replace('{db_prefix}', $db_prefix, $table);
93
94		$pg_tables = array('pg_catalog','information_schema');
95
96		$request = $smcFunc['db_query']('', '
97		SELECT pg_relation_size(C.oid) AS "size"
98		FROM pg_class C
99		LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
100		WHERE nspname NOT IN ({array_string:pg_tables})
101		and relname = {string:table}',
102		array(
103		'table' => $table,
104		'pg_tables' => $pg_tables,
105		)
106		);
107
108		$row = $smcFunc['db_fetch_assoc']($request);
109		$smcFunc['db_free_result']($request);
110
111		$old_size = $row['size'];
112
113		$request = $smcFunc['db_query']('', '
114		VACUUM FULL ANALYZE {raw:table}',
115		array(
116		'table' => $table,
117		)
118		);
119
120		if (!$request)
121		return -1;
122
123		$request = $smcFunc['db_query']('', '
124		SELECT pg_relation_size(C.oid) AS "size"
125		FROM pg_class C
126		LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
127		WHERE nspname NOT IN ({array_string:pg_tables})
128		and relname = {string:table}',
129		array(
130		'table' => $table,
131		'pg_tables' => $pg_tables,
132		)
133		);
134
135
136		$row = $smcFunc['db_fetch_assoc']($request);
137		$smcFunc['db_free_result']($request);
138
139		if (isset($row['size']))
140		return ($old_size - $row['size']) / 1024;
141		else
142		return 0;
143		}
144
145		/**
146		* This function lists all tables in the database.
147		* The listing could be filtered according to $filter.
148		*
149		* @param string\|boolean $db string The database name or false to use the current DB
150		* @param string\|boolean $filter String to filter by or false to list all tables
151		* @return array An array of table names
152		*/
153		function smf_db_list_tables($db = false, $filter = false)
154		{
155		global $smcFunc;
156
157		$request = $smcFunc['db_query']('', '
158		SELECT tablename
159		FROM pg_tables
160		WHERE schemaname = {string:schema_public}' . ($filter == false ? '' : '
161		AND tablename LIKE {string:filter}') . '
162		ORDER BY tablename',
163		array(
164		'schema_public' => 'public',
165		'filter' => $filter,
166		)
167		);
168
169		$tables = array();
170		while ($row = $smcFunc['db_fetch_row']($request))
171		$tables[] = $row[0];
172		$smcFunc['db_free_result']($request);
173
174		return $tables;
175		}
176
177		/**
178		* Dumps the schema (CREATE) for a table.
179		* @todo why is this needed for?
180		* @param string $tableName The name of the table
181		* @return string The "CREATE TABLE" SQL string for this table
182		*/
183		function smf_db_table_sql($tableName)
184		{
185		global $smcFunc, $db_prefix;
186
187		$tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
188
189		// This will be needed...
190		$crlf = "\r\n";
191
192		// Drop it if it exists.
193		$schema_create = 'DROP TABLE IF EXISTS ' . $tableName . ';' . $crlf . $crlf;
194
195		// Start the create table...
196		$schema_create .= 'CREATE TABLE ' . $tableName . ' (' . $crlf;
197		$index_create = '';
198		$seq_create = '';
199
200		// Find all the fields.
201		$result = $smcFunc['db_query']('', '
202		SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
203		FROM information_schema.columns
204		WHERE table_name = {string:table}
205		ORDER BY ordinal_position',
206		array(
207		'table' => $tableName,
208		)
209		);
210		while ($row = $smcFunc['db_fetch_assoc']($result))
211		{
212		if ($row['data_type'] == 'character varying')
213		$row['data_type'] = 'varchar';
214		elseif ($row['data_type'] == 'character')
215		$row['data_type'] = 'char';
216		if ($row['character_maximum_length'])
217		$row['data_type'] .= '(' . $row['character_maximum_length'] . ')';
218
219		// Make the CREATE for this column.
220		$schema_create .= ' "' . $row['column_name'] . '" ' . $row['data_type'] . ($row['is_nullable'] != 'YES' ? ' NOT NULL' : '');
221
222		// Add a default...?
223		if (trim($row['column_default']) != '')
224		{
225		$schema_create .= ' default ' . $row['column_default'] . '';
226
227		// Auto increment?
228		if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
229		{
230		// Get to find the next variable first!
231		$count_req = $smcFunc['db_query']('', '
232		SELECT MAX("{raw:column}")
233		FROM {raw:table}',
234		array(
235		'column' => $row['column_name'],
236		'table' => $tableName,
237		)
238		);
239		list ($max_ind) = $smcFunc['db_fetch_row']($count_req);
240		$smcFunc['db_free_result']($count_req);
241		// Get the right bloody start!
242		$seq_create .= 'CREATE SEQUENCE ' . $matches[1] . ' START WITH ' . ($max_ind + 1) . ';' . $crlf . $crlf;
243		}
244		}
245
246		$schema_create .= ',' . $crlf;
247		}
248		$smcFunc['db_free_result']($result);
249
250		// Take off the last comma.
251		$schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
252
253		$result = $smcFunc['db_query']('', '
254		SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary, pg_get_indexdef(i.indexrelid) AS inddef
255		FROM pg_class AS c
256		INNER JOIN pg_index AS i ON (i.indrelid = c.oid)
257		INNER JOIN pg_class AS c2 ON (c2.oid = i.indexrelid)
258		WHERE c.relname = {string:table}',
259		array(
260		'table' => $tableName,
261		)
262		);
263
264		while ($row = $smcFunc['db_fetch_assoc']($result))
265		{
266		if ($row['is_primary'])
267		{
268		if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0)
269		continue;
270
271		$index_create .= $crlf . 'ALTER TABLE ' . $tableName . ' ADD PRIMARY KEY ("' . $matches[1] . '");';
272		}
273		else
274		$index_create .= $crlf . $row['inddef'] . ';';
275		}
276		$smcFunc['db_free_result']($result);
277
278		// Finish it off!
279		$schema_create .= $crlf . ');';
280
281		return $seq_create . $schema_create . $index_create;
282		}
283
284		/**
285		* Get the version number.
286		* @return string The version
287		*/
288		function smf_db_get_version()
289		{
290		static $ver;
291
292		if(!empty($ver))
293		return $ver;
294
295		global $smcFunc;
296
297		$request = $smcFunc['db_query']('', '
298		SHOW server_version',
299		array(
300		)
301		);
302		list ($ver) = $smcFunc['db_fetch_row']($request);
303		$smcFunc['db_free_result']($request);
304
305		return $ver;
306		}
307
308		/**
309		* Return PostgreSQL
310		*
311		* @return string The database engine we are using
312		*/
313		function smf_db_get_engine()
314		{
315		return 'PostgreSQL';
316		}
317
318		?>

albertlast / SMF2.1

Push — release-2.1 ( 6f6d35...abeae7 )

Sources/DbExtra-postgresql.php (1 issue)

Labels

Severity

Introduced By

Upgrade to new PHP Analysis Engine

Duplication Side-by-Side

Filter issues like