Table::change_column() - Code Metrics - elkarte/Elkarte - Measure and Improve Code Quality continuously with Scrutinizer

Table::change_column() F
last analyzed 2025-12-05 00:18 UTC

↳ Parent: Table

Complexity

Conditions	22
Paths	1135

Size

Total Lines	111
Code Lines	55

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	506

Importance

Changes

Metric	Value
cc	22
eloc	55
nc	1135
nop	4
dl	0
loc	111
ccs	0
cts	87
cp	0
crap	506
rs	0
c	0
b	0
f	0

How to fix Long Method Complexity

<?php

/**
 * This class implements functionality related to table structure.
 * Intended in particular for addons to change it to suit their needs.
 *
 * @package   ElkArte Forum
 * @copyright ElkArte Forum contributors
 * @license   BSD http://opensource.org/licenses/BSD-3-Clause (see accompanying LICENSE.txt file)
 *
 * This file contains code covered by:
 * copyright: 2011 Simple Machines (http://www.simplemachines.org)
 *
 * @version 2.0 dev
 *
 */

namespace ElkArte\Database\Postgresql;

use ElkArte\Database\AbstractTable;
use ElkArte\Exceptions\Exception;

/**
 * Adds PostgreSQL table level functionality,
 * Table creation / dropping, column adding / removing
 * Most often used during install and Upgrades of the forum and addons
 */
class Table extends AbstractTable
{
	/** @var Table Holds this instance of the table interface */
	protected static $_tbl;

	/** @var string[] Any index to create when a table is created */
	protected $_indexes = [];

	/**
	 * {@inheritDoc}
	 */
	public function drop_table($table_name, $force = false)
	{
		// Get some aliases.
		$full_table_name = str_replace('{db_prefix}', $this->_real_prefix(), $table_name);
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		// God no - dropping one of these = bad.
		if (in_array(strtolower($table_name), $this->_reservedTables, true))
		{
			return false;
		}

		// Does it exist?
		if ($force || $this->table_exists($full_table_name))
		{
			// We can then drop the table.
			$this->_db->transaction('begin');

			if ($force)
			{
				$this->_db->skip_next_error();
			}

			$table_query = 'DROP TABLE ' . $table_name;

			$this->_db->query('',
				$table_query,
				[
					'security_override' => true,
				]
			);

			$this->_db->transaction('commit');

			// and the associated sequence, if any
			if ($force)
			{
				$this->_db->skip_next_error();
			}

			$sequence_query = 'DROP SEQUENCE IF EXISTS ' . $table_name . '_seq';
			$this->_db->query('',
				$sequence_query,
				[
					'security_override' => true,
				]
			);

			return true;
		}

		// Otherwise do 'nout.
		return false;
	}

	/**
	 * {@inheritDoc}
	 */
	protected function _real_prefix()
	{
		return preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $this->_db_prefix, $match) === 1 ? $match[3] : $this->_db_prefix;
	}

	/**
	 * {@inheritDoc}
	 */
	public function add_column($table_name, $column_info, $parameters = [], $if_exists = 'update')
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		// Log that we will want to uninstall this!
		$this->_package_log[] = ['remove_column', $table_name, $column_info['name']];

		// Does it exist - if so don't add it again!
		if ($this->_get_column_info($table_name, $column_info['name']))
		{
			// If we're going to overwrite then use change column.
			if ($if_exists === 'update')
			{
				return $this->change_column($table_name, $column_info['name'], $column_info);
			}

			return false;
		}

		// Get the specifics...
		$column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
		[$type, $size] = $this->calculate_type($column_info['type'], $column_info['size']);
		if ($size !== null)
		{
			$type .= '(' . $size . ')';
		}

		// Now add the thing!
		$this->_alter_table($table_name, '
			ADD COLUMN ' . $column_info['name'] . ' ' . $type);

		// If there's more attributes they need to be done via a change on PostgreSQL.
		unset($column_info['type'], $column_info['size']);

		if (count($column_info) !== 1)
		{
			return $this->change_column($table_name, $column_info['name'], $column_info);
		}

		return true;
	}

	/**
	 * {@inheritDoc}
	 */
	public function change_column($table_name, $old_column, $column_info, $parameters = [])
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		// Check it does exist!
		$old_info = $this->_get_column_info($table_name, $old_column);

		// Nothing?
		if ($old_info === false)
		{
			return false;
		}

		// Now we check each bit individually and ALTER as required.
		if (isset($column_info['name']) && $column_info['name'] != $old_column)
		{
			$this->_alter_table($table_name, '
				RENAME COLUMN ' . $old_column . ' TO ' . $column_info['name']);
		}

		// Different default?
		if (isset($column_info['default']) && $column_info['default'] !== $old_info['default'])
		{
			$action = $column_info['default'] !== null ? "SET DEFAULT '" . $this->_db->escape_string($column_info['default']) . "'" : 'DROP DEFAULT';
			$this->_alter_table($table_name, '
				ALTER COLUMN ' . $column_info['name'] . ' ' . $action);
		}

		// Is it null - or otherwise?
		if (isset($column_info['null']) && $column_info['null'] !== $old_info['null'])
		{
			$action = $column_info['null'] ? 'DROP' : 'SET';
			$this->_db->transaction('begin');
			if (!$column_info['null'])
			{
				// We have to set it to something if we are making it NOT NULL. And we must comply with the current column format.
				$setTo = $column_info['default'] ?? (strpos($old_info['type'], 'int') !== false ? 0 : '');
				$this->_db->query('', '
					UPDATE ' . $table_name . '
					SET ' . $column_info['name'] . " = '" . $setTo . '\'
					WHERE ' . $column_info['name'] . ' IS NULL',
					[
						'security_override' => true,
					]
				);
			}

			$this->_alter_table($table_name, '
				ALTER COLUMN ' . $column_info['name'] . ' ' . $action . ' NOT NULL');
			$this->_db->transaction('commit');
		}

		// What about a change in type?
		if (isset($column_info['type']) && ($column_info['type'] !== $old_info['type'] || (isset($column_info['size']) && $column_info['size'] !== $old_info['size'])))
		{
			$column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
			[$type, $size] = $this->calculate_type($column_info['type'], $column_info['size']);
			if ($size !== null)
			{
				$type .= '(' . $size . ')';
			}

			// The alter is a pain.
			$this->_db->transaction('begin');
			$this->_alter_table($table_name, '
				ADD COLUMN ' . $column_info['name'] . '_tempxx ' . $type);
			$this->_db->query('', '
				UPDATE ' . $table_name . '
				SET ' . $column_info['name'] . '_tempxx = CAST(' . $column_info['name'] . ' AS ' . $type . ')',
				[
					'security_override' => true,
				]
			);
			$this->_alter_table($table_name, '
				DROP COLUMN ' . $column_info['name']);
			$this->_alter_table($table_name, '
				RENAME COLUMN ' . $column_info['name'] . '_tempxx TO ' . $column_info['name']);
			$this->_db->transaction('commit');
		}

		// Finally - auto increment?!
		if (isset($column_info['auto']) && $column_info['auto'] !== $old_info['auto'])
		{
			// Are we removing an old one?
			if ($old_info['auto'])
			{
				// Alter the table first - then drop the sequence.
				$this->_alter_table($table_name, '
					ALTER COLUMN ' . $column_info['name'] . " SET DEFAULT '0'");
				$this->_db->query('', '
					DROP SEQUENCE ' . $table_name . '_seq',
					[
						'security_override' => true,
					]
				);
			}
			// Otherwise add it!
			else
			{
				$this->_db->query('', '
					CREATE SEQUENCE ' . $table_name . '_seq',
					[
						'security_override' => true,
					]
				);
				$this->_alter_table($table_name, '
					ALTER COLUMN ' . $column_info['name'] . " SET DEFAULT nextval('" . $table_name . "_seq')");
			}
		}

		return true;
	}

	/**
	 * {@inheritDoc}
	 */
	public function calculate_type($type_name, $type_size = null, $reverse = false)
	{
		// Let's be sure it's lowercase MySQL likes both, others no.
		$type_name = strtolower($type_name);

		// Generic => Specific.
		if (!$reverse)
		{
			$types = [
				'varchar' => 'character varying',
				'char' => 'character',
				'mediumint' => 'int',
				'tinyint' => 'smallint',
				'tinytext' => 'character varying',
				'mediumtext' => 'text',
				'largetext' => 'text',
			];
		}
		else
		{
			$types = [
				'character varying' => 'varchar',
				'character' => 'char',
				'integer' => 'int',
			];
		}

		// Got it? Change it!
		if (isset($types[$type_name]))
		{
			if ($type_name === 'tinytext')
			{
				$type_size = 255;
			}

			$type_name = $types[$type_name];
		}

		// Numbers don't have a size.
		if (strpos($type_name, 'int') !== false)
		{
			$type_size = null;
		}

		return [$type_name, $type_size];
	}

	/**
	 * {@inheritDoc}
	 */
	public function remove_column($table_name, $column_name, $parameters = [])
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		// Does it exist?
		$column = $this->_get_column_info($table_name, $column_name);
		if ($column !== false)
		{
			// If there is an auto we need remove it!
			if ($column['auto'])
			{
				$this->_db->query('',
					'DROP SEQUENCE ' . $table_name . '_seq',
					[
						'security_override' => true,
					]
				);
			}

			$this->_alter_table($table_name, '
				DROP COLUMN ' . $column_name);

			return true;
		}

		// If here we didn't have to work - joy!
		return false;
	}

	/**
	 * {@inheritDoc}
	 */
	public function add_index($table_name, $index_info, $parameters = [], $if_exists = 'update')
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		// No columns = no index.
		if (empty($index_info['columns']))
		{
			return false;
		}

		// MySQL supports a length argument, postgre no
		foreach ($index_info['columns'] as $id => $col)
		{
			if (strpos($col, '(') !== false)
			{
				$index_info['columns'][$id] = substr($col, 0, strpos($col, '('));
			}
		}

		$columns = implode(',', $index_info['columns']);

		// No name - make it up!
		if (empty($index_info['name']))
		{
			// No need for primary.
			if (isset($index_info['type']) && $index_info['type'] === 'primary')
			{
				$index_info['name'] = '';
			}
			else
			{
				$index_info['name'] = $table_name . implode('_', $index_info['columns']);
			}
		}
		else
		{
			$index_info['name'] = $table_name . $index_info['name'];
		}

		// Log that we are going to want to remove this!
		$this->_package_log[] = ['remove_index', $table_name, $index_info['name']];

		// Let's get all our indexes.
		$indexes = $this->list_indexes($table_name, true);

		// Do we already have it?
		foreach ($indexes as $index)
		{
			if ($index['name'] === $index_info['name'] || ($index['type'] === 'primary' && isset($index_info['type']) && $index_info['type'] === 'primary'))
			{
				// If we want to overwrite simply remove the current one then continue.
				if ($if_exists !== 'update' || $index['type'] === 'primary')
				{
					return false;
				}
				$this->remove_index($table_name, $index_info['name']);
			}
		}

		// If we're here we know we don't have the index - so just add it.
		if (!empty($index_info['type']) && $index_info['type'] === 'primary')
		{
			$this->_alter_table($table_name, '
				ADD PRIMARY KEY (' . $columns . ')');
		}
		else
		{
			$this->_db->query('', '
				CREATE ' . (isset($index_info['type']) && $index_info['type'] === 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
				[
					'security_override' => true,
				]
			);
		}

		return true;
	}

	/**
	 * {@inheritDoc}
	 */
	public function list_indexes($table_name, $detail = false, $parameters = [])
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		$result = $this->_db->fetchQuery('
			SELECT 
				CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary,
				CASE WHEN i.indisunique THEN 1 ELSE 0 END AS is_unique,
				c2.relname AS name,
				pg_get_indexdef(i.indexrelid) AS inddef
			FROM pg_class AS c, pg_class AS c2, pg_index AS i
			WHERE c.relname = \'' . $table_name . '\'
				AND c.oid = i.indrelid
				AND i.indexrelid = c2.oid',
			[
				'security_override' => true,
			]
		);
		$indexes = [];
		while (($row = $result->fetch_assoc()))
		{
			// Try get the columns that make it up.
			if (preg_match('~\(([^)]+?)\)~i', $row['inddef'], $matches) !== 1)
			{
				continue;
			}

			$columns = explode(',', $matches[1]);

			if (empty($columns))
			{
				continue;
			}

			foreach ($columns as $k => $v)
			{
				$columns[$k] = trim($v);
			}

			// Fix up the name to be consistent cross databases
			if (substr($row['name'], -5) === '_pkey' && (int) $row['is_primary'] === 1)
			{
				$row['name'] = 'PRIMARY';
			}
			else
			{
				$row['name'] = str_replace($table_name . '_', '', $row['name']);
			}

			if (!$detail)
			{
				$indexes[] = $row['name'];
			}
			else
			{
				$indexes[$row['name']] = [
					'name' => $row['name'],
					'type' => $row['is_primary'] ? 'primary' : ($row['is_unique'] ? 'unique' : 'index'),
					'columns' => $columns,
				];
			}
		}

		$result->free_result();

		return $indexes;
	}

	/**
	 * {@inheritDoc}
	 */
	public function remove_index($table_name, $index_name, $parameters = [])
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		// Better exist!
		$indexes = $this->list_indexes($table_name, true);
		if ($index_name !== 'primary')
		{
			$index_name = $table_name . '_' . $index_name;
		}

		foreach ($indexes as $index)
		{
			// If the name is primary we want the primary key!
			if ($index['type'] === 'primary' && $index_name === 'primary')
			{
				// Dropping primary key is odd...
				$this->_alter_table($table_name, '
					DROP CONSTRAINT ' . $index['name']);

				return true;
			}

			if ($index['name'] === $index_name)
			{
				// Drop the bugger...
				$this->_db->query('', '
					DROP INDEX ' . $index_name,
					[
						'security_override' => true,
					]
				);

				return true;
			}
		}

		// Not to be found ;(
		return false;
	}

	/**
	 * {@inheritDoc}
	 */
	public function table_structure($table_name)
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		return [
			'name' => $table_name,
			'columns' => $this->list_columns($table_name, true),
			'indexes' => $this->list_indexes($table_name, true),
		];
	}

	/**
	 * {@inheritDoc}
	 */
	public function list_columns($table_name, $detail = false, $parameters = [])
	{
		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);

		$result = $this->_db->fetchQuery('
			SELECT 
				column_name, column_default, is_nullable, data_type, character_maximum_length
			FROM information_schema.columns
			WHERE table_name = \'' . $table_name . '\'
			ORDER BY ordinal_position',
			[
				'security_override' => true,
			]
		);
		$columns = [];
		while (($row = $result->fetch_assoc()))
		{
			if (!$detail)
			{
				$columns[] = $row['column_name'];
			}
			else
			{
				$auto = false;

				// What is the default?
				if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
				{
					$default = null;
					$auto = true;
				}
				elseif (trim($row['column_default']) !== '')
				{
					$default = strpos($row['column_default'], '::') === false ? $row['column_default'] : substr($row['column_default'], 0, strpos($row['column_default'], '::'));
				}
				else
				{
					$default = null;
				}

				// Make the type generic.
				[$type, $size] = $this->calculate_type($row['data_type'], $row['character_maximum_length'], true);

				$columns[$row['column_name']] = [
					'name' => $row['column_name'],
					'null' => (bool) $row['is_nullable'],
					'default' => $default,
					'type' => $type,
					'size' => $size,
					'auto' => $auto,
				];
			}
		}

		$result->free_result();

		return $columns;
	}

	/**
	 * {@inheritDoc}
	 */
	public function optimize($table)
	{
		$table = str_replace('{db_prefix}', $this->_db_prefix, $table);

		$request = $this->_db->fetchQuery('
			VACUUM ANALYZE {raw:table}',
			[
				'table' => $table,
			]
		);
		if (!$request)
		{
			return -1;
		}

		$row = $request->fetch_assoc();
		$request->free_result();

		if (isset($row['Data_free']))
		{
			return $row['Data_free'] / 1024;
		}

		return 0;
	}

	/**
	 * {@inheritDoc}
	 */
	public function package_log()
	{
		return $this->_package_log;
	}

	/**
	 * {@inheritDoc}
	 */
	protected function _build_indexes()
	{
		foreach ($this->_indexes as $query)
		{
			$this->_db->query('', $query,
				[
					'security_override' => true,
				]
			);
		}
	}

	/**
	 * {@inheritDoc}
	 */
	protected function _close_table_query($temporary)
	{
		return ')';
	}

	/**
	 * {@inheritDoc}
	 */
	protected function _create_query_indexes($indexes, $table_name)
	{
		// Loop through the indexes next...
		$this->_indexes = [];
		$table_query = '';
		foreach ($indexes as $index)
		{
			$index['columns'] = $this->_clean_indexes($index['columns']);

			$columns = implode(',', $index['columns']);

			// Primary goes in the table...
			if (isset($index['type']) && $index['type'] == 'primary')
			{
				$table_query .= "\n\t" . 'PRIMARY KEY (' . $columns . '),';
			}
			else
			{
				if (empty($index['name']))
				{
					$index['name'] = implode('_', $index['columns']);
				}

				$this->_indexes[] = 'CREATE ' . (isset($index['type']) && $index['type'] === 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
			}
		}

		return $table_query;
	}

	/**
	 * {@inheritDoc}
	 */
	protected function _clean_indexes($columns)
	{
		// MySQL supports a length argument, postgre no
		foreach ($columns as $id => $col)
		{
			if (strpos($col, '(') !== false)
			{
				$columns[$id] = substr($col, 0, strpos($col, '('));
			}
		}

		return $columns;
	}

	/**
	 * Creates a query for a column
	 *
	 * @param array $column
	 * @param string $table_name
	 *
	 * @return string
	 * @throws Exception
	 */
	protected function _db_create_query_column($column, $table_name): string
	{
		// If we have an auto increment do it!
		if (!empty($column['auto']))
		{
			$this->_db->query('', '
				CREATE SEQUENCE ' . $table_name . '_seq',
				[
					'security_override' => true,
				]
			);
			$default = "default nextval('" . $table_name . "_seq')";
		}
		elseif (isset($column['default']) && $column['default'] !== null)
		{
			$default = "default '" . $this->_db->escape_string($column['default']) . "'";
		}
		else
		{
			$default = '';
		}

		// Sort out the size...
		$column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
		[$type, $size] = $this->calculate_type($column['type'], $column['size']);
		if ($size !== null)
		{
			$type .= '(' . $size . ')';
		}

		// Now just put it together!
		return '"' . $column['name'] . '" ' . $type . ' ' . (empty($column['null']) ? 'NOT NULL' : '') . ' ' . $default;
	}
}


1		<?php
2
3		/**
4		* This class implements functionality related to table structure.
5		* Intended in particular for addons to change it to suit their needs.
6		*
7		* @package ElkArte Forum
8		* @copyright ElkArte Forum contributors
9		* @license BSD http://opensource.org/licenses/BSD-3-Clause (see accompanying LICENSE.txt file)
10		*
11		* This file contains code covered by:
12		* copyright: 2011 Simple Machines (http://www.simplemachines.org)
13		*
14		* @version 2.0 dev
15		*
16		*/
17
18		namespace ElkArte\Database\Postgresql;
19
20		use ElkArte\Database\AbstractTable;
21		use ElkArte\Exceptions\Exception;
22
23		/**
24		* Adds PostgreSQL table level functionality,
25		* Table creation / dropping, column adding / removing
26		* Most often used during install and Upgrades of the forum and addons
27		*/
28		class Table extends AbstractTable
29		{
30		/** @var Table Holds this instance of the table interface */
31		protected static $_tbl;
32
33		/** @var string[] Any index to create when a table is created */
34		protected $_indexes = [];
35
36		/**
37		* {@inheritDoc}
38		*/
39		public function drop_table($table_name, $force = false)
40		{
41		// Get some aliases.
42		$full_table_name = str_replace('{db_prefix}', $this->_real_prefix(), $table_name);
43		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
44
45		// God no - dropping one of these = bad.
46	1	if (in_array(strtolower($table_name), $this->_reservedTables, true))
47		{
48		return false;
49	1	}
50	1
51		// Does it exist?
52		if ($force \|\| $this->table_exists($full_table_name))
53	1	{
54		// We can then drop the table.
55		$this->_db->transaction('begin');
56
57		if ($force)
58		{
59	1	$this->_db->skip_next_error();
60		}
61
62	1	$table_query = 'DROP TABLE ' . $table_name;
63
64	1	$this->_db->query('',
65		$table_query,
66	1	[
67		'security_override' => true,
68	1	]
69		);
70	1
71		$this->_db->transaction('commit');
72
73	1	// and the associated sequence, if any
74		if ($force)
75		{
76		$this->_db->skip_next_error();
77	1	}
78
79		$sequence_query = 'DROP SEQUENCE IF EXISTS ' . $table_name . '_seq';
80	1	$this->_db->query('',
81		$sequence_query,
82	1	[
83		'security_override' => true,
84		]
85	1	);
86	1
87		return true;
88		}
89	1
90		// Otherwise do 'nout.
91		return false;
92		}
93	1
94		/**
95		* {@inheritDoc}
96		*/
97		protected function _real_prefix()
98		{
99		return preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $this->_db_prefix, $match) === 1 ? $match[3] : $this->_db_prefix;
100		}
101
102		/**
103	1	* {@inheritDoc}
104		*/
105	1	public function add_column($table_name, $column_info, $parameters = [], $if_exists = 'update')
106		{
107		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
108
109		// Log that we will want to uninstall this!
110		$this->_package_log[] = ['remove_column', $table_name, $column_info['name']];
111
112		// Does it exist - if so don't add it again!
113		if ($this->_get_column_info($table_name, $column_info['name']))
114		{
115		// If we're going to overwrite then use change column.
116		if ($if_exists === 'update')
117		{
118		return $this->change_column($table_name, $column_info['name'], $column_info);
119		}
120
121		return false;
122		}
123
124		// Get the specifics...
125		$column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
126		[$type, $size] = $this->calculate_type($column_info['type'], $column_info['size']);
127		if ($size !== null)
128		{
129		$type .= '(' . $size . ')';
130		}
131
132		// Now add the thing!
133		$this->_alter_table($table_name, '
134		ADD COLUMN ' . $column_info['name'] . ' ' . $type);
135
136		// If there's more attributes they need to be done via a change on PostgreSQL.
137		unset($column_info['type'], $column_info['size']);
138
139		if (count($column_info) !== 1)
140		{
141		return $this->change_column($table_name, $column_info['name'], $column_info);
142		}
143
144		return true;
145		}
146
147		/**
148		* {@inheritDoc}
149		*/
150		public function change_column($table_name, $old_column, $column_info, $parameters = [])
151		{
152		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
153
154		// Check it does exist!
155		$old_info = $this->_get_column_info($table_name, $old_column);
156
157		// Nothing?
158		if ($old_info === false)
159		{
160		return false;
161		}
162
163		// Now we check each bit individually and ALTER as required.
164		if (isset($column_info['name']) && $column_info['name'] != $old_column)
165		{
166		$this->_alter_table($table_name, '
167		RENAME COLUMN ' . $old_column . ' TO ' . $column_info['name']);
168		}
169
170		// Different default?
171		if (isset($column_info['default']) && $column_info['default'] !== $old_info['default'])
172		{
173		$action = $column_info['default'] !== null ? "SET DEFAULT '" . $this->_db->escape_string($column_info['default']) . "'" : 'DROP DEFAULT';
174		$this->_alter_table($table_name, '
175		ALTER COLUMN ' . $column_info['name'] . ' ' . $action);
176		}
177
178		// Is it null - or otherwise?
179		if (isset($column_info['null']) && $column_info['null'] !== $old_info['null'])
180		{
181		$action = $column_info['null'] ? 'DROP' : 'SET';
182		$this->_db->transaction('begin');
183		if (!$column_info['null'])
184		{
185		// We have to set it to something if we are making it NOT NULL. And we must comply with the current column format.
186		$setTo = $column_info['default'] ?? (strpos($old_info['type'], 'int') !== false ? 0 : '');
187		$this->_db->query('', '
188		UPDATE ' . $table_name . '
189		SET ' . $column_info['name'] . " = '" . $setTo . '\'
190		WHERE ' . $column_info['name'] . ' IS NULL',
191		[
192		'security_override' => true,
193		]
194		);
195		}
196
197		$this->_alter_table($table_name, '
198		ALTER COLUMN ' . $column_info['name'] . ' ' . $action . ' NOT NULL');
199		$this->_db->transaction('commit');
200		}
201
202		// What about a change in type?
203		if (isset($column_info['type']) && ($column_info['type'] !== $old_info['type'] \|\| (isset($column_info['size']) && $column_info['size'] !== $old_info['size'])))
204		{
205		$column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
206		[$type, $size] = $this->calculate_type($column_info['type'], $column_info['size']);
207		if ($size !== null)
208		{
209		$type .= '(' . $size . ')';
210		}
211
212		// The alter is a pain.
213		$this->_db->transaction('begin');
214		$this->_alter_table($table_name, '
215		ADD COLUMN ' . $column_info['name'] . '_tempxx ' . $type);
216		$this->_db->query('', '
217		UPDATE ' . $table_name . '
218		SET ' . $column_info['name'] . '_tempxx = CAST(' . $column_info['name'] . ' AS ' . $type . ')',
219		[
220		'security_override' => true,
221		]
222		);
223		$this->_alter_table($table_name, '
224		DROP COLUMN ' . $column_info['name']);
225		$this->_alter_table($table_name, '
226		RENAME COLUMN ' . $column_info['name'] . '_tempxx TO ' . $column_info['name']);
227		$this->_db->transaction('commit');
228		}
229
230		// Finally - auto increment?!
231		if (isset($column_info['auto']) && $column_info['auto'] !== $old_info['auto'])
232		{
233		// Are we removing an old one?
234		if ($old_info['auto'])
235		{
236		// Alter the table first - then drop the sequence.
237		$this->_alter_table($table_name, '
238		ALTER COLUMN ' . $column_info['name'] . " SET DEFAULT '0'");
239		$this->_db->query('', '
240		DROP SEQUENCE ' . $table_name . '_seq',
241		[
242		'security_override' => true,
243		]
244		);
245		}
246		// Otherwise add it!
247		else
248		{
249		$this->_db->query('', '
250		CREATE SEQUENCE ' . $table_name . '_seq',
251		[
252		'security_override' => true,
253		]
254		);
255		$this->_alter_table($table_name, '
256		ALTER COLUMN ' . $column_info['name'] . " SET DEFAULT nextval('" . $table_name . "_seq')");
257		}
258		}
259
260		return true;
261		}
262
263		/**
264		* {@inheritDoc}
265		*/
266		public function calculate_type($type_name, $type_size = null, $reverse = false)
267		{
268		// Let's be sure it's lowercase MySQL likes both, others no.
269		$type_name = strtolower($type_name);
270
271		// Generic => Specific.
272		if (!$reverse)
273	1	{
274		$types = [
275		'varchar' => 'character varying',
276	1	'char' => 'character',
277		'mediumint' => 'int',
278		'tinyint' => 'smallint',
279	1	'tinytext' => 'character varying',
280		'mediumtext' => 'text',
281		'largetext' => 'text',
282	1	];
283		}
284		else
285		{
286		$types = [
287		'character varying' => 'varchar',
288		'character' => 'char',
289		'integer' => 'int',
290		];
291		}
292
293		// Got it? Change it!
294		if (isset($types[$type_name]))
295		{
296		if ($type_name === 'tinytext')
297		{
298		$type_size = 255;
299		}
300
301	1	$type_name = $types[$type_name];
302		}
303	1
304		// Numbers don't have a size.
305		if (strpos($type_name, 'int') !== false)
306		{
307	1	$type_size = null;
308		}
309
310		return [$type_name, $type_size];
311	1	}
312
313	1	/**
314		* {@inheritDoc}
315		*/
316	1	public function remove_column($table_name, $column_name, $parameters = [])
317		{
318		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
319
320		// Does it exist?
321		$column = $this->_get_column_info($table_name, $column_name);
322		if ($column !== false)
323		{
324		// If there is an auto we need remove it!
325		if ($column['auto'])
326		{
327		$this->_db->query('',
328		'DROP SEQUENCE ' . $table_name . '_seq',
329		[
330		'security_override' => true,
331		]
332		);
333		}
334
335		$this->_alter_table($table_name, '
336		DROP COLUMN ' . $column_name);
337
338		return true;
339		}
340
341		// If here we didn't have to work - joy!
342		return false;
343		}
344
345		/**
346		* {@inheritDoc}
347		*/
348		public function add_index($table_name, $index_info, $parameters = [], $if_exists = 'update')
349		{
350		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
351
352		// No columns = no index.
353		if (empty($index_info['columns']))
354		{
355		return false;
356		}
357
358		// MySQL supports a length argument, postgre no
359		foreach ($index_info['columns'] as $id => $col)
360		{
361		if (strpos($col, '(') !== false)
362		{
363		$index_info['columns'][$id] = substr($col, 0, strpos($col, '('));
364		}
365		}
366
367		$columns = implode(',', $index_info['columns']);
368
369		// No name - make it up!
370		if (empty($index_info['name']))
371		{
372		// No need for primary.
373		if (isset($index_info['type']) && $index_info['type'] === 'primary')
374		{
375		$index_info['name'] = '';
376		}
377		else
378		{
379		$index_info['name'] = $table_name . implode('_', $index_info['columns']);
380		}
381		}
382		else
383		{
384		$index_info['name'] = $table_name . $index_info['name'];
385		}
386
387		// Log that we are going to want to remove this!
388		$this->_package_log[] = ['remove_index', $table_name, $index_info['name']];
389
390		// Let's get all our indexes.
391		$indexes = $this->list_indexes($table_name, true);
392
393		// Do we already have it?
394		foreach ($indexes as $index)
395		{
396		if ($index['name'] === $index_info['name'] \|\| ($index['type'] === 'primary' && isset($index_info['type']) && $index_info['type'] === 'primary'))
397		{
398		// If we want to overwrite simply remove the current one then continue.
399		if ($if_exists !== 'update' \|\| $index['type'] === 'primary')
400		{
401		return false;
402		}
403		$this->remove_index($table_name, $index_info['name']);
404		}
405		}
406
407		// If we're here we know we don't have the index - so just add it.
408		if (!empty($index_info['type']) && $index_info['type'] === 'primary')
409		{
410		$this->_alter_table($table_name, '
411		ADD PRIMARY KEY (' . $columns . ')');
412		}
413		else
414		{
415		$this->_db->query('', '
416		CREATE ' . (isset($index_info['type']) && $index_info['type'] === 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
417		[
418		'security_override' => true,
419		]
420		);
421		}
422
423		return true;
424		}
425
426		/**
427		* {@inheritDoc}
428		*/
429		public function list_indexes($table_name, $detail = false, $parameters = [])
430		{
431		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
432
433		$result = $this->_db->fetchQuery('
434		SELECT
435		CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary,
436		CASE WHEN i.indisunique THEN 1 ELSE 0 END AS is_unique,
437		c2.relname AS name,
438		pg_get_indexdef(i.indexrelid) AS inddef
439		FROM pg_class AS c, pg_class AS c2, pg_index AS i
440		WHERE c.relname = \'' . $table_name . '\'
441		AND c.oid = i.indrelid
442		AND i.indexrelid = c2.oid',
443		[
444		'security_override' => true,
445		]
446		);
447		$indexes = [];
448		while (($row = $result->fetch_assoc()))
449		{
450		// Try get the columns that make it up.
451		if (preg_match('~\(([^)]+?)\)~i', $row['inddef'], $matches) !== 1)
452		{
453		continue;
454		}
455
456		$columns = explode(',', $matches[1]);
457
458		if (empty($columns))
459		{
460		continue;
461		}
462
463		foreach ($columns as $k => $v)
464		{
465		$columns[$k] = trim($v);
466		}
467
468		// Fix up the name to be consistent cross databases
469		if (substr($row['name'], -5) === '_pkey' && (int) $row['is_primary'] === 1)
470		{
471		$row['name'] = 'PRIMARY';
472		}
473		else
474		{
475		$row['name'] = str_replace($table_name . '_', '', $row['name']);
476		}
477
478		if (!$detail)
479		{
480		$indexes[] = $row['name'];
481		}
482		else
483		{
484		$indexes[$row['name']] = [
485		'name' => $row['name'],
486		'type' => $row['is_primary'] ? 'primary' : ($row['is_unique'] ? 'unique' : 'index'),
487		'columns' => $columns,
488		];
489		}
490		}
491
492		$result->free_result();
493
494		return $indexes;
495		}
496
497		/**
498		* {@inheritDoc}
499		*/
500		public function remove_index($table_name, $index_name, $parameters = [])
501		{
502		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
503
504		// Better exist!
505		$indexes = $this->list_indexes($table_name, true);
506		if ($index_name !== 'primary')
507		{
508		$index_name = $table_name . '_' . $index_name;
509		}
510
511		foreach ($indexes as $index)
512		{
513		// If the name is primary we want the primary key!
514		if ($index['type'] === 'primary' && $index_name === 'primary')
515		{
516		// Dropping primary key is odd...
517		$this->_alter_table($table_name, '
518		DROP CONSTRAINT ' . $index['name']);
519
520		return true;
521		}
522
523		if ($index['name'] === $index_name)
524		{
525		// Drop the bugger...
526		$this->_db->query('', '
527		DROP INDEX ' . $index_name,
528		[
529		'security_override' => true,
530		]
531		);
532
533		return true;
534		}
535		}
536
537		// Not to be found ;(
538		return false;
539		}
540
541		/**
542		* {@inheritDoc}
543		*/
544		public function table_structure($table_name)
545		{
546		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
547
548		return [
549		'name' => $table_name,
550		'columns' => $this->list_columns($table_name, true),
551		'indexes' => $this->list_indexes($table_name, true),
552		];
553		}
554
555		/**
556		* {@inheritDoc}
557		*/
558		public function list_columns($table_name, $detail = false, $parameters = [])
559		{
560		$table_name = str_replace('{db_prefix}', $this->_db_prefix, $table_name);
561
562		$result = $this->_db->fetchQuery('
563		SELECT
564		column_name, column_default, is_nullable, data_type, character_maximum_length
565		FROM information_schema.columns
566		WHERE table_name = \'' . $table_name . '\'
567		ORDER BY ordinal_position',
568		[
569		'security_override' => true,
570		]
571		);
572		$columns = [];
573		while (($row = $result->fetch_assoc()))
574		{
575		if (!$detail)
576		{
577		$columns[] = $row['column_name'];
578		}
579		else
580		{
581		$auto = false;
582
583		// What is the default?
584		if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
585		{
586		$default = null;
587		$auto = true;
588		}
589		elseif (trim($row['column_default']) !== '')
590		{
591		$default = strpos($row['column_default'], '::') === false ? $row['column_default'] : substr($row['column_default'], 0, strpos($row['column_default'], '::'));
592		}
593		else
594		{
595		$default = null;
596		}
597
598		// Make the type generic.
599		[$type, $size] = $this->calculate_type($row['data_type'], $row['character_maximum_length'], true);
600
601		$columns[$row['column_name']] = [
602		'name' => $row['column_name'],
603		'null' => (bool) $row['is_nullable'],
604		'default' => $default,
605		'type' => $type,
606		'size' => $size,
607		'auto' => $auto,
608		];
609		}
610		}
611
612		$result->free_result();
613
614		return $columns;
615		}
616
617		/**
618		* {@inheritDoc}
619		*/
620		public function optimize($table)
621		{
622		$table = str_replace('{db_prefix}', $this->_db_prefix, $table);
623
624		$request = $this->_db->fetchQuery('
625		VACUUM ANALYZE {raw:table}',
626		[
627		'table' => $table,
628		]
629		);
630		if (!$request)
631		{
632		return -1;
633		}
634
635		$row = $request->fetch_assoc();
636		$request->free_result();
637
638		if (isset($row['Data_free']))
639		{
640		return $row['Data_free'] / 1024;
641		}
642
643		return 0;
644		}
645
646		/**
647		* {@inheritDoc}
648		*/
649		public function package_log()
650		{
651		return $this->_package_log;
652		}
653
654		/**
655		* {@inheritDoc}
656		*/
657		protected function _build_indexes()
658		{
659		foreach ($this->_indexes as $query)
660		{
661		$this->_db->query('', $query,
662		[
663		'security_override' => true,
664	1	]
665		);
666	1	}
667		}
668
669		/**
670		* {@inheritDoc}
671		*/
672		protected function _close_table_query($temporary)
673		{
674	1	return ')';
675		}
676
677		/**
678		* {@inheritDoc}
679	1	*/
680		protected function _create_query_indexes($indexes, $table_name)
681	1	{
682		// Loop through the indexes next...
683		$this->_indexes = [];
684		$table_query = '';
685		foreach ($indexes as $index)
686		{
687	1	$index['columns'] = $this->_clean_indexes($index['columns']);
688
689		$columns = implode(',', $index['columns']);
690	1
691	1	// Primary goes in the table...
692	1	if (isset($index['type']) && $index['type'] == 'primary')
693		{
694	1	$table_query .= "\n\t" . 'PRIMARY KEY (' . $columns . '),';
695		}
696	1	else
697		{
698		if (empty($index['name']))
699	1	{
700		$index['name'] = implode('_', $index['columns']);
701	1	}
702
703		$this->_indexes[] = 'CREATE ' . (isset($index['type']) && $index['type'] === 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
704		}
705		}
706
707		return $table_query;
708		}
709
710		/**
711		* {@inheritDoc}
712		*/
713	1	protected function _clean_indexes($columns)
714		{
715		// MySQL supports a length argument, postgre no
716		foreach ($columns as $id => $col)
717		{
718		if (strpos($col, '(') !== false)
719	1	{
720		$columns[$id] = substr($col, 0, strpos($col, '('));
721		}
722	1	}
723
724	1	return $columns;
725		}
726
727		/**
728		* Creates a query for a column
729		*
730	1	* @param array $column
731		* @param string $table_name
732		*
733		* @return string
734		* @throws Exception
735		*/
736		protected function _db_create_query_column($column, $table_name): string
737		{
738		// If we have an auto increment do it!
739		if (!empty($column['auto']))
740		{
741		$this->_db->query('', '
742	1	CREATE SEQUENCE ' . $table_name . '_seq',
743		[
744		'security_override' => true,
745	1	]
746		);
747		$default = "default nextval('" . $table_name . "_seq')";
748		}
749		elseif (isset($column['default']) && $column['default'] !== null)
750		{
751		$default = "default '" . $this->_db->escape_string($column['default']) . "'";
752		}
753		else
754		{
755	1	$default = '';
756		}
757	1
758		// Sort out the size...
759		$column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
760		[$type, $size] = $this->calculate_type($column['type'], $column['size']);
761		if ($size !== null)
762		{
763		$type .= '(' . $size . ')';
764		}
765	1
766	1	// Now just put it together!
767	1	return '"' . $column['name'] . '" ' . $type . ' ' . (empty($column['null']) ? 'NOT NULL' : '') . ' ' . $default;
768		}
769		}
770

elkarte / Elkarte

Table::change_column() F last analyzed 2025-12-05 00:18 UTC

Complexity

Size

Duplication

Code Coverage

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like

Table::change_column() F
last analyzed 2025-12-05 00:18 UTC