DbTable_MySQL::optimize() - Code Metrics - Inspection of "An a few more fixes for 1.1.7" - elkarte/Elkarte - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — patch_1-1-7 (#3469)

by Spuds

created 2021-02-21 22:35 UTC

DbTable_MySQL::optimize() B

↳ Parent: DbTable_MySQL

Complexity

Conditions	7
Paths	11

Size

Total Lines	47
Code Lines	23

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	1	Features	0

Metric	Value
cc	7
eloc	23
c	1
b	1
f	0
nc	11
nop	1
dl	0
loc	47
rs	8.6186

<?php

/**
 * This class implements functionality related to table structure.
 * Intended in particular for addons to change it to suit their needs.
 *
 * @name      ElkArte Forum
 * @copyright ElkArte Forum contributors
 * @license   BSD http://opensource.org/licenses/BSD-3-Clause
 *
 * This file contains code covered by:
 * copyright:	2011 Simple Machines (http://www.simplemachines.org)
 * license:  	BSD, See included LICENSE.TXT for terms and conditions.
 *
 * @version 1.1.7
 *
 */

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

	/**
	 * DbTable_MySQL::construct
	 *
	 * @param object $db - A Database_MySQL object
	 */
	private function __construct($db)
	{
		global $db_prefix;

		// We won't do any remove on these
		$this->_reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
			'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
			'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
			'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
			'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
			'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
			'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
			'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
			'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
			'themes', 'topics');

		foreach ($this->_reservedTables as $k => $table_name)
			$this->_reservedTables[$k] = strtolower($db_prefix . $table_name);

		// let's be sure.
		$this->_package_log = array();

		// This executes queries and things
		$this->_db = $db;
	}

	/**
	 * This function can be used to create a table without worrying about schema
	 *  compatibilities across supported database systems.
	 *  - If the table exists will, by default, do nothing.
	 *  - Builds table with columns as passed to it - at least one column must be sent.
	 *  The columns array should have one sub-array for each column - these sub arrays contain:
	 *    'name' = Column name
	 *    'type' = Type of column - values from (smallint, mediumint, int, text, varchar, char, tinytext, mediumtext, largetext)
	 *    'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc.
	 *      If not set it will pick a size.
	 *    - 'default' = Default value - do not set if no default required.
	 *    - 'null' => Can it be null (true or false) - if not set default will be false.
	 *    - 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set from what
	 *      it should begin counting.
	 *  - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
	 *    - 'name' => Index name (If left empty it will be generated).
	 *    - 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
	 *    - 'columns' => Array containing columns that form part of key - in the order the index is to be created.
	 *  - parameters: (None yet)
	 *  - if_exists values:
	 *    - 'ignore' will do nothing if the table exists. (And will return true)
	 *    - 'overwrite' will drop any existing table of the same name.
	 *    - 'error' will return false if the table already exists.
	 *
	 * @param string $table_name
	 * @param mixed[] $columns in the format specified.
	 * @param mixed[] $indexes default array(), in the format specified.
	 * @param mixed[] $parameters default array()
	 * @param string $if_exists default 'ignore'
	 * @param string $error default 'fatal'
	 */
	public function db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
	{
		global $db_prefix;

		// Strip out the table name, we might not need it in some cases
		$real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;

		// With or without the database name, the fullname looks like this.
		$full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
		$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);

		// First - no way do we touch our tables.
		if (in_array(strtolower($table_name), $this->_reservedTables))
			return false;

		// Log that we'll want to remove this on uninstall.
		$this->_package_log[] = array('remove_table', $table_name);

		// Slightly easier on MySQL than the others...
		if ($this->table_exists($full_table_name))
		{
			// This is a sad day... drop the table? If not, return false (error) by default.
			if ($if_exists == 'overwrite')
				$this->db_drop_table($table_name);
			else
				return $if_exists == 'ignore';
		}

		// Righty - let's do the damn thing!
		$table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
		foreach ($columns as $column)
			$table_query .= "\n\t" . $this->_db_create_query_column($column) . ',';

		// Loop through the indexes next...
		foreach ($indexes as $index)
		{
			$columns = implode(',', $index['columns']);

			// Is it the primary?
			if (isset($index['type']) && $index['type'] == 'primary')
				$table_query .= "\n\t" . 'PRIMARY KEY (' . $columns . '),';
			else
			{
				if (empty($index['name']))
					$index['name'] = implode('_', $index['columns']);
				$table_query .= "\n\t" . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : 'KEY') . ' ' . $index['name'] . ' (' . $columns . '),';
			}
		}

		// No trailing commas!
		if (substr($table_query, -1) == ',')
			$table_query = substr($table_query, 0, -1);

		$table_query .= ') DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci';

		// Create the table!
		$this->_db->query('', $table_query,
			array(
				'security_override' => true,
			)
		);

		return true;
	}

	/**
	 * Drop a table.
	 *
	 * @param string $table_name
	 * @param mixed[] $parameters default array()
	 * @param string $error default 'fatal'
	 */
	public function db_drop_table($table_name, $parameters = array(), $error = 'fatal')
	{
		global $db_prefix;

		// After stripping away the database name, this is what's left.
		$real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;

		// Get some aliases.
		$full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
		$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);

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

		// Does it exist?
		if ($this->table_exists($full_table_name))
		{
			$query = 'DROP TABLE ' . $table_name;
			$this->_db->query('',
				$query,
				array(
					'security_override' => true,
				)
			);

			return true;
		}

		// Otherwise do 'nout.
		return false;
	}

	/**
	 * This function adds a column.
	 *
	 * @param string $table_name the name of the table
	 * @param mixed[] $column_info with column information
	 * @param mixed[] $parameters default array()
	 * @param string $if_exists default 'update'
	 * @param string $error default 'fatal'
	 */
	public function db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
	{
		global $db_prefix;

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

		// Log that we will want to uninstall this!
		$this->_package_log[] = array('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->db_change_column($table_name, $column_info['name'], $column_info);
			else
				return false;
		}

		// Now add the thing!
		$this->_alter_table($table_name, '
			ADD ' . $this->_db_create_query_column($column_info) . (empty($column_info['auto']) ? '' : ' primary key'));

		return true;
	}

	/**
	 * Removes a column.
	 *
	 * @param string $table_name
	 * @param string $column_name
	 * @param mixed[] $parameters default array()
	 * @param string $error default 'fatal'
	 */
	public function db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
	{
		global $db_prefix;

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

		// Does it exist?
		$column = $this->_get_column_info($table_name, $column_name);
		if ($column !== false)
		{
			$this->_alter_table($table_name, '
				DROP COLUMN ' . $column_name);

			return true;
		}

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

	/**
	 * Change a column.
	 *
	 * @param string $table_name
	 * @param string $old_column
	 * @param mixed[] $column_info
	 * @param mixed[] $parameters default array()
	 * @param string $error default 'fatal'
	 */
	public function db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
	{
		global $db_prefix;

		$table_name = str_replace('{db_prefix}', $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;

		// Get the right bits.
		if (!isset($column_info['name']))
			$column_info['name'] = $old_column;
		if (!isset($column_info['default']))
			$column_info['default'] = $old_info['default'];
		if (!isset($column_info['null']))
			$column_info['null'] = $old_info['null'];
		if (!isset($column_info['auto']))
			$column_info['auto'] = $old_info['auto'];
		if (!isset($column_info['type']))
			$column_info['type'] = $old_info['type'];
		if (!isset($column_info['size']) || !is_numeric($column_info['size']))
			$column_info['size'] = $old_info['size'];
		if (!isset($column_info['unsigned']) || !in_array($column_info['type'], array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')))
			$column_info['unsigned'] = '';

		$this->_alter_table($table_name, '
			CHANGE COLUMN `' . $old_column . '` ' . $this->_db_create_query_column($column_info));
	}

	/**
	 * Add an index.
	 *
	 * @param string $table_name
	 * @param mixed[] $index_info
	 * @param mixed[] $parameters default array()
	 * @param string $if_exists default 'update'
	 * @param string $error default 'fatal'
	 */
	public function db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
	{
		global $db_prefix;

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

		// No columns = no index.
		if (empty($index_info['columns']))
			return false;
		$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'] = implode('_', $index_info['columns']);
		}

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

		// Let's get all our indexes.
		$indexes = $this->db_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;
				else
					$this->db_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->_alter_table($table_name, '
				ADD ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : 'INDEX') . ' ' . $index_info['name'] . ' (' . $columns . ')');
		}
	}

	/**
	 * Remove an index.
	 *
	 * @param string $table_name
	 * @param string $index_name
	 * @param mixed[] $parameters default array()
	 * @param string $error default 'fatal'
	 */
	public function db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
	{
		global $db_prefix;

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

		// Better exist!
		$indexes = $this->db_list_indexes($table_name, true);

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

				return true;
			}

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

				return true;
			}
		}

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

	/**
	 * Get the schema formatted name for a type.
	 *
	 * @param string $type_name
	 * @param int|null $type_size
	 * @param boolean $reverse
	 */
	public function db_calculate_type($type_name, $type_size = null, $reverse = false)
	{
		// MySQL is actually the generic baseline.
		return array($type_name, $type_size);
	}

	/**
	 * Get table structure.
	 *
	 * @param string $table_name
	 * @param mixed[] $parameters default array()
	 */
	public function db_table_structure($table_name, $parameters = array())
	{
		global $db_prefix;

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

		return array(
			'name' => $table_name,
			'columns' => $this->db_list_columns($table_name, true),
			'indexes' => $this->db_list_indexes($table_name, true),
		);
	}

	/**
	 * Return column information for a table.
	 *
	 * @param string $table_name
	 * @param bool $detail
	 * @param mixed[] $parameters default array()
	 * @return mixed
	 */
	public function db_list_columns($table_name, $detail = false, $parameters = array())
	{
		global $db_prefix;

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

		$result = $this->_db->query('', '
			SHOW FIELDS
			FROM {raw:table_name}',
			array(
				'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
			)
		);
		$columns = array();
		while ($row = $this->_db->fetch_assoc($result))
		{
			if (!$detail)
			{
				$columns[] = $row['Field'];
			}
			else
			{
				// Is there an auto_increment?
				$auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false;

				// Can we split out the size?
				if (preg_match('~(.+?)\s*\((\d+)\)(?:(?:\s*)?(unsigned))?~i', $row['Type'], $matches) === 1)
				{
					$type = $matches[1];
					$size = $matches[2];
					if (!empty($matches[3]) && $matches[3] == 'unsigned')
						$unsigned = true;
				}
				else
				{
					$type = $row['Type'];
					$size = null;
				}

				$columns[$row['Field']] = array(
					'name' => $row['Field'],
					'null' => $row['Null'] != 'YES' ? false : true,
					'default' => isset($row['Default']) ? $row['Default'] : null,
					'type' => $type,
					'size' => $size,
					'auto' => $auto,
				);

				if (isset($unsigned))
				{
					$columns[$row['Field']]['unsigned'] = $unsigned;
					unset($unsigned);
				}
			}
		}
		$this->_db->free_result($result);

		return $columns;
	}

	/**
	 * Get index information.
	 *
	 * @param string $table_name
	 * @param bool $detail
	 * @param mixed[] $parameters
	 * @return mixed
	 */
	public function db_list_indexes($table_name, $detail = false, $parameters = array())
	{
		global $db_prefix;

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

		$result = $this->_db->query('', '
			SHOW KEYS
			FROM {raw:table_name}',
			array(
				'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
			)
		);
		$indexes = array();
		while ($row = $this->_db->fetch_assoc($result))
		{
			if (!$detail)
				$indexes[] = $row['Key_name'];
			else
			{
				// What is the type?
				if ($row['Key_name'] == 'PRIMARY')
					$type = 'primary';
				elseif (empty($row['Non_unique']))
					$type = 'unique';
				elseif (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT')
					$type = 'fulltext';
				else
					$type = 'index';

				// This is the first column we've seen?
				if (empty($indexes[$row['Key_name']]))
				{
					$indexes[$row['Key_name']] = array(
						'name' => $row['Key_name'],
						'type' => $type,
						'columns' => array(),
					);
				}

				// Is it a partial index?
				if (!empty($row['Sub_part']))
					$indexes[$row['Key_name']]['columns'][] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
				else
					$indexes[$row['Key_name']]['columns'][] = $row['Column_name'];
			}
		}
		$this->_db->free_result($result);

		return $indexes;
	}

	/**
	 * Creates a query for a column
	 *
	 * @param mixed[] $column
	 */
	private function _db_create_query_column($column)
	{
		// Auto increment is easy here!
		if (!empty($column['auto']))
		{
			$default = 'auto_increment';
		}
		elseif (isset($column['default']) && $column['default'] !== null)
			$default = 'default \'' . $this->_db->escape_string($column['default']) . '\'';
		else
			$default = '';

		// Sort out the size... and stuff...
		$column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
		list ($type, $size) = $this->db_calculate_type($column['type'], $column['size']);

		// Allow unsigned integers (mysql only)
		$unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint', 'float')) && !empty($column['unsigned']) ? 'unsigned ' : '';

		if ($size !== null)
			$type = $type . '(' . $size . ')';

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

	/**
	 * This function optimizes a table.
	 *
	 * @param string $table - the table to be optimized
	 *
	 * @return int how much it was gained
	 */
	public function optimize($table)
	{
		global $db_prefix;

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

		// Get how much overhead there is.
		$request = $this->_db->query('', '
			SHOW TABLE STATUS LIKE {string:table_name}',
			array(
				'table_name' => str_replace('_', '\_', $table),
			)
		);
		$row = $this->_db->fetch_assoc($request);
		$this->_db->free_result($request);

		// Optimize tables that will benefit from this operation.
		if (isset($row['Engine']) && $row['Engine'] === 'MyISAM')
		{
			$data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
			$request = $this->_db->query('', '
				OPTIMIZE TABLE `{raw:table}`',
				array(
					'table' => $table,
				)
			);
			if (!$request)
				return -1;

			// How much left?
			$request = $this->_db->query('', '
				SHOW TABLE STATUS LIKE {string:table}',
				array(
					'table' => str_replace('_', '\_', $table),
				)
			);
			$row = $this->_db->fetch_assoc($request);
			$this->_db->free_result($request);

			$total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
		}
		else
		{
			$total_change = 0;
		}

		return $total_change;
	}

	/**
	 * Return a copy of this instance package log
	 */
	public function package_log()
	{
		return $this->_package_log;
	}

	/**
	 * Static method that allows to retrieve or create an instance of this class.
	 *
	 * @param object $db - A Database_MySQL object
	 * @return DbTable_MySQL - A DbTable_MySQL object
	 */
	public static function db_table($db)
	{
		if (is_null(self::$_tbl))
			self::$_tbl = new DbTable_MySQL($db);
		return self::$_tbl;
	}
}

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			* @name ElkArte Forum
8			* @copyright ElkArte Forum contributors
9			* @license BSD http://opensource.org/licenses/BSD-3-Clause
10			*
11			* This file contains code covered by:
12			* copyright: 2011 Simple Machines (http://www.simplemachines.org)
13			* license: BSD, See included LICENSE.TXT for terms and conditions.
14			*
15			* @version 1.1.7
16			*
17			*/
18
19			/**
20			* Adds MySQL table level functionality,
21			* Table creation / dropping, column adding / removing
22			* Most often used during install and Upgrades of the forum and addons
23			*/
24			class DbTable_MySQL extends DbTable
25			{
26			/**
27			* Holds this instance of the table interface
28			* @var DbTable_MySQL
29			*/
30			private static $_tbl = null;
31
32			/**
33			* DbTable_MySQL::construct
34			*
35			* @param object $db - A Database_MySQL object
36			*/
37			private function __construct($db)
38			{
39			global $db_prefix;
40
41			// We won't do any remove on these
42			$this->_reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
43			'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
44			'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
45			'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
46			'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
47			'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
48			'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
49			'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
50			'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
51			'themes', 'topics');
52
53			foreach ($this->_reservedTables as $k => $table_name)
54			$this->_reservedTables[$k] = strtolower($db_prefix . $table_name);
55
56			// let's be sure.
57			$this->_package_log = array();
58
59			// This executes queries and things
60			$this->_db = $db;
61			}
62
63			/**
64			* This function can be used to create a table without worrying about schema
65			* compatibilities across supported database systems.
66			* - If the table exists will, by default, do nothing.
67			* - Builds table with columns as passed to it - at least one column must be sent.
68			* The columns array should have one sub-array for each column - these sub arrays contain:
69			* 'name' = Column name
70			* 'type' = Type of column - values from (smallint, mediumint, int, text, varchar, char, tinytext, mediumtext, largetext)
71			* 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc.
72			* If not set it will pick a size.
73			* - 'default' = Default value - do not set if no default required.
74			* - 'null' => Can it be null (true or false) - if not set default will be false.
75			* - 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set from what
76			* it should begin counting.
77			* - Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
78			* - 'name' => Index name (If left empty it will be generated).
79			* - 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
80			* - 'columns' => Array containing columns that form part of key - in the order the index is to be created.
81			* - parameters: (None yet)
82			* - if_exists values:
83			* - 'ignore' will do nothing if the table exists. (And will return true)
84			* - 'overwrite' will drop any existing table of the same name.
85			* - 'error' will return false if the table already exists.
86			*
87			* @param string $table_name
88			* @param mixed[] $columns in the format specified.
89			* @param mixed[] $indexes default array(), in the format specified.
90			* @param mixed[] $parameters default array()
91			* @param string $if_exists default 'ignore'
92			* @param string $error default 'fatal'
93			*/
94			public function db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
95			{
96			global $db_prefix;
97
98			// Strip out the table name, we might not need it in some cases
99			$real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
100
101			// With or without the database name, the fullname looks like this.
102			$full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
103			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
104
105			// First - no way do we touch our tables.
106			if (in_array(strtolower($table_name), $this->_reservedTables))
107			return false;
108
109			// Log that we'll want to remove this on uninstall.
110			$this->_package_log[] = array('remove_table', $table_name);
111
112			// Slightly easier on MySQL than the others...
113			if ($this->table_exists($full_table_name))
114			{
115			// This is a sad day... drop the table? If not, return false (error) by default.
116			if ($if_exists == 'overwrite')
117			$this->db_drop_table($table_name);
118			else
119			return $if_exists == 'ignore';
120			}
121
122			// Righty - let's do the damn thing!
123			$table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
124			foreach ($columns as $column)
125			$table_query .= "\n\t" . $this->_db_create_query_column($column) . ',';
126
127			// Loop through the indexes next...
128			foreach ($indexes as $index)
129			{
130			$columns = implode(',', $index['columns']);
131
132			// Is it the primary?
133			if (isset($index['type']) && $index['type'] == 'primary')
134			$table_query .= "\n\t" . 'PRIMARY KEY (' . $columns . '),';
135			else
136			{
137			if (empty($index['name']))
138			$index['name'] = implode('_', $index['columns']);
139			$table_query .= "\n\t" . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : 'KEY') . ' ' . $index['name'] . ' (' . $columns . '),';
140			}
141			}
142
143			// No trailing commas!
144			if (substr($table_query, -1) == ',')
145			$table_query = substr($table_query, 0, -1);
146
147			$table_query .= ') DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci';
148
149			// Create the table!
150			$this->_db->query('', $table_query,
151			array(
152			'security_override' => true,
153			)
154			);
155
156			return true;
157			}
158
159			/**
160			* Drop a table.
161			*
162			* @param string $table_name
163			* @param mixed[] $parameters default array()
164			* @param string $error default 'fatal'
165			*/
166			public function db_drop_table($table_name, $parameters = array(), $error = 'fatal')
167			{
168			global $db_prefix;
169
170			// After stripping away the database name, this is what's left.
171			$real_prefix = preg_match('~^(`?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
172
173			// Get some aliases.
174			$full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
175			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
176
177			// God no - dropping one of these = bad.
178			if (in_array(strtolower($table_name), $this->_reservedTables))
179			return false;
180
181			// Does it exist?
182			if ($this->table_exists($full_table_name))
183			{
184			$query = 'DROP TABLE ' . $table_name;
185			$this->_db->query('',
186			$query,
187			array(
188			'security_override' => true,
189			)
190			);
191
192			return true;
193			}
194
195			// Otherwise do 'nout.
196			return false;
197			}
198
199			/**
200			* This function adds a column.
201			*
202			* @param string $table_name the name of the table
203			* @param mixed[] $column_info with column information
204			* @param mixed[] $parameters default array()
205			* @param string $if_exists default 'update'
206			* @param string $error default 'fatal'
207			*/
208			public function db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
209			{
210			global $db_prefix;
211
212			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
213
214			// Log that we will want to uninstall this!
215			$this->_package_log[] = array('remove_column', $table_name, $column_info['name']);
216
217			// Does it exist - if so don't add it again!
218			if ($this->_get_column_info($table_name, $column_info['name']))
219			{
220			// If we're going to overwrite then use change column.
221			if ($if_exists == 'update')
222			return $this->db_change_column($table_name, $column_info['name'], $column_info);
223			else
224			return false;
225			}
226
227			// Now add the thing!
228			$this->_alter_table($table_name, '
229			ADD ' . $this->_db_create_query_column($column_info) . (empty($column_info['auto']) ? '' : ' primary key'));
230
231			return true;
232			}
233
234			/**
235			* Removes a column.
236			*
237			* @param string $table_name
238			* @param string $column_name
239			* @param mixed[] $parameters default array()
240			* @param string $error default 'fatal'
241			*/
242			public function db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
243			{
244			global $db_prefix;
245
246			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
247
248			// Does it exist?
249			$column = $this->_get_column_info($table_name, $column_name);
250			if ($column !== false)
251			{
252			$this->_alter_table($table_name, '
253			DROP COLUMN ' . $column_name);
254
255			return true;
256			}
257
258			// If here we didn't have to work - joy!
259			return false;
260			}
261
262			/**
263			* Change a column.
264			*
265			* @param string $table_name
266			* @param string $old_column
267			* @param mixed[] $column_info
268			* @param mixed[] $parameters default array()
269			* @param string $error default 'fatal'
270			*/
271			public function db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
272			{
273			global $db_prefix;
274
275			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
276
277			// Check it does exist!
278			$old_info = $this->_get_column_info($table_name, $old_column);
279
280			// Nothing?
281			if ($old_info === false)
282			return false;
283
284			// Get the right bits.
285			if (!isset($column_info['name']))
286			$column_info['name'] = $old_column;
287			if (!isset($column_info['default']))
288			$column_info['default'] = $old_info['default'];
289			if (!isset($column_info['null']))
290			$column_info['null'] = $old_info['null'];
291			if (!isset($column_info['auto']))
292			$column_info['auto'] = $old_info['auto'];
293			if (!isset($column_info['type']))
294			$column_info['type'] = $old_info['type'];
295			if (!isset($column_info['size']) \|\| !is_numeric($column_info['size']))
296			$column_info['size'] = $old_info['size'];
297			if (!isset($column_info['unsigned']) \|\| !in_array($column_info['type'], array('int', 'tinyint', 'smallint', 'mediumint', 'bigint')))
298			$column_info['unsigned'] = '';
299
300			$this->_alter_table($table_name, '
301			CHANGE COLUMN `' . $old_column . '` ' . $this->_db_create_query_column($column_info));
302			}
303
304			/**
305			* Add an index.
306			*
307			* @param string $table_name
308			* @param mixed[] $index_info
309			* @param mixed[] $parameters default array()
310			* @param string $if_exists default 'update'
311			* @param string $error default 'fatal'
312			*/
313			public function db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
314			{
315			global $db_prefix;
316
317			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
318
319			// No columns = no index.
320			if (empty($index_info['columns']))
321			return false;
322			$columns = implode(',', $index_info['columns']);
323
324			// No name - make it up!
325			if (empty($index_info['name']))
326			{
327			// No need for primary.
328			if (isset($index_info['type']) && $index_info['type'] == 'primary')
329			$index_info['name'] = '';
330			else
331			$index_info['name'] = implode('_', $index_info['columns']);
332			}
333
334			// Log that we are going to want to remove this!
335			$this->_package_log[] = array('remove_index', $table_name, $index_info['name']);
336
337			// Let's get all our indexes.
338			$indexes = $this->db_list_indexes($table_name, true);
339
340			// Do we already have it?
341			foreach ($indexes as $index)
342			{
343			if ($index['name'] == $index_info['name'] \|\| ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
344			{
345			// If we want to overwrite simply remove the current one then continue.
346			if ($if_exists != 'update' \|\| $index['type'] == 'primary')
347			return false;
348			else
349			$this->db_remove_index($table_name, $index_info['name']);
350			}
351			}
352
353			// If we're here we know we don't have the index - so just add it.
354			if (!empty($index_info['type']) && $index_info['type'] == 'primary')
355			{
356			$this->_alter_table($table_name, '
357			ADD PRIMARY KEY (' . $columns . ')');
358			}
359			else
360			{
361			$this->_alter_table($table_name, '
362			ADD ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : 'INDEX') . ' ' . $index_info['name'] . ' (' . $columns . ')');
363			}
364			}
365
366			/**
367			* Remove an index.
368			*
369			* @param string $table_name
370			* @param string $index_name
371			* @param mixed[] $parameters default array()
372			* @param string $error default 'fatal'
373			*/
374			public function db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
375			{
376			global $db_prefix;
377
378			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
379
380			// Better exist!
381			$indexes = $this->db_list_indexes($table_name, true);
382
383			foreach ($indexes as $index)
384			{
385			// If the name is primary we want the primary key!
386			if ($index['type'] == 'primary' && $index_name == 'primary')
387			{
388			// Dropping primary key?
389			$this->_alter_table($table_name, '
390			DROP PRIMARY KEY');
391
392			return true;
393			}
394
395			if ($index['name'] == $index_name)
396			{
397			// Drop the bugger...
398			$this->_alter_table($table_name, '
399			DROP INDEX ' . $index_name);
400
401			return true;
402			}
403			}
404
405			// Not to be found ;(
406			return false;
407			}
408
409			/**
410			* Get the schema formatted name for a type.
411			*
412			* @param string $type_name
413			* @param int\|null $type_size
414			* @param boolean $reverse
415			*/
416			public function db_calculate_type($type_name, $type_size = null, $reverse = false)
417			{
418			// MySQL is actually the generic baseline.
419			return array($type_name, $type_size);
420			}
421
422			/**
423			* Get table structure.
424			*
425			* @param string $table_name
426			* @param mixed[] $parameters default array()
427			*/
428			public function db_table_structure($table_name, $parameters = array())
429			{
430			global $db_prefix;
431
432			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
433
434			return array(
435			'name' => $table_name,
436			'columns' => $this->db_list_columns($table_name, true),
437			'indexes' => $this->db_list_indexes($table_name, true),
438			);
439			}
440
441			/**
442			* Return column information for a table.
443			*
444			* @param string $table_name
445			* @param bool $detail
446			* @param mixed[] $parameters default array()
447			* @return mixed
448			*/
449			public function db_list_columns($table_name, $detail = false, $parameters = array())
450			{
451			global $db_prefix;
452
453			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
454
455			$result = $this->_db->query('', '
456			SHOW FIELDS
457			FROM {raw:table_name}',
458			array(
459			'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
460			)
461			);
462			$columns = array();
463			while ($row = $this->_db->fetch_assoc($result))
464			{
465			if (!$detail)
466			{
467			$columns[] = $row['Field'];
468			}
469			else
470			{
471			// Is there an auto_increment?
472			$auto = strpos($row['Extra'], 'auto_increment') !== false ? true : false;
473
474			// Can we split out the size?
475			if (preg_match('~(.+?)\s\((\d+)\)(?:(?:\s)?(unsigned))?~i', $row['Type'], $matches) === 1)
476			{
477			$type = $matches[1];
478			$size = $matches[2];
479			if (!empty($matches[3]) && $matches[3] == 'unsigned')
480			$unsigned = true;
481			}
482			else
483			{
484			$type = $row['Type'];
485			$size = null;
486			}
487
488			$columns[$row['Field']] = array(
489			'name' => $row['Field'],
490			'null' => $row['Null'] != 'YES' ? false : true,
491			'default' => isset($row['Default']) ? $row['Default'] : null,
492			'type' => $type,
493			'size' => $size,
494			'auto' => $auto,
495			);
496
497			if (isset($unsigned))
498			{
499			$columns[$row['Field']]['unsigned'] = $unsigned;
500			unset($unsigned);
501			}
502			}
503			}
504			$this->_db->free_result($result);
505
506			return $columns;
507			}
508
509			/**
510			* Get index information.
511			*
512			* @param string $table_name
513			* @param bool $detail
514			* @param mixed[] $parameters
515			* @return mixed
516			*/
517			public function db_list_indexes($table_name, $detail = false, $parameters = array())
518			{
519			global $db_prefix;
520
521			$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
522
523			$result = $this->_db->query('', '
524			SHOW KEYS
525			FROM {raw:table_name}',
526			array(
527			'table_name' => substr($table_name, 0, 1) == '`' ? $table_name : '`' . $table_name . '`',
528			)
529			);
530			$indexes = array();
531			while ($row = $this->_db->fetch_assoc($result))
532			{
533			if (!$detail)
534			$indexes[] = $row['Key_name'];
535			else
536			{
537			// What is the type?
538			if ($row['Key_name'] == 'PRIMARY')
539			$type = 'primary';
540			elseif (empty($row['Non_unique']))
541			$type = 'unique';
542			elseif (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT')
543			$type = 'fulltext';
544			else
545			$type = 'index';
546
547			// This is the first column we've seen?
548			if (empty($indexes[$row['Key_name']]))
549			{
550			$indexes[$row['Key_name']] = array(
551			'name' => $row['Key_name'],
552			'type' => $type,
553			'columns' => array(),
554			);
555			}
556
557			// Is it a partial index?
558			if (!empty($row['Sub_part']))
559			$indexes[$row['Key_name']]['columns'][] = $row['Column_name'] . '(' . $row['Sub_part'] . ')';
560			else
561			$indexes[$row['Key_name']]['columns'][] = $row['Column_name'];
562			}
563			}
564			$this->_db->free_result($result);
565
566			return $indexes;
567			}
568
569			/**
570			* Creates a query for a column
571			*
572			* @param mixed[] $column
573			*/
574			private function _db_create_query_column($column)
575			{
576			// Auto increment is easy here!
577			if (!empty($column['auto']))
578			{
579			$default = 'auto_increment';
580			}
581			elseif (isset($column['default']) && $column['default'] !== null)
582			$default = 'default \'' . $this->_db->escape_string($column['default']) . '\'';
583			else
584			$default = '';
585
586			// Sort out the size... and stuff...
587			$column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
588			list ($type, $size) = $this->db_calculate_type($column['type'], $column['size']);
589
590			// Allow unsigned integers (mysql only)
591			$unsigned = in_array($type, array('int', 'tinyint', 'smallint', 'mediumint', 'bigint', 'float')) && !empty($column['unsigned']) ? 'unsigned ' : '';
592
593			if ($size !== null)
594			$type = $type . '(' . $size . ')';
595
596			// Now just put it together!
597			return '`' . $column['name'] . '` ' . $type . ' ' . (!empty($unsigned) ? $unsigned : '') . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default;
598			}
599
600			/**
601			* This function optimizes a table.
602			*
603			* @param string $table - the table to be optimized
604			*
605			* @return int how much it was gained
606			*/
607			public function optimize($table)
608			{
609			global $db_prefix;
610
611			$table = str_replace('{db_prefix}', $db_prefix, $table);
612
613			// Get how much overhead there is.
614			$request = $this->_db->query('', '
615			SHOW TABLE STATUS LIKE {string:table_name}',
616			array(
617			'table_name' => str_replace('_', '\_', $table),
618			)
619			);
620			$row = $this->_db->fetch_assoc($request);
621			$this->_db->free_result($request);
622
623			// Optimize tables that will benefit from this operation.
624			if (isset($row['Engine']) && $row['Engine'] === 'MyISAM')
625			{
626			$data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
627			$request = $this->_db->query('', '
628			OPTIMIZE TABLE `{raw:table}`',
629			array(
630			'table' => $table,
631			)
632			);
633			if (!$request)
634			return -1;
635
636			// How much left?
637			$request = $this->_db->query('', '
638			SHOW TABLE STATUS LIKE {string:table}',
639			array(
640			'table' => str_replace('_', '\_', $table),
641			)
642			);
643			$row = $this->_db->fetch_assoc($request);
644			$this->_db->free_result($request);
645
646			$total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
647			}
648			else
649			{
650			$total_change = 0;
651			}
652
653			return $total_change;
654			}
655
656			/**
657			* Return a copy of this instance package log
658			*/
659			public function package_log()
660			{
661			return $this->_package_log;
662			}
663
664			/**
665			* Static method that allows to retrieve or create an instance of this class.
666			*
667			* @param object $db - A Database_MySQL object
668			* @return DbTable_MySQL - A DbTable_MySQL object
669			*/
670			public static function db_table($db)
671			{
672			if (is_null(self::$_tbl))
673			self::$_tbl = new DbTable_MySQL($db);
674			return self::$_tbl;
675			}
676			}

elkarte / Elkarte

Pull Request — patch_1-1-7 (#3469)

DbTable_MySQL::optimize() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like