Chris@76: <?php
Chris@76: 
Chris@76: /**
Chris@76:  * Simple Machines Forum (SMF)
Chris@76:  *
Chris@76:  * @package SMF
Chris@76:  * @author Simple Machines http://www.simplemachines.org
Chris@76:  * @copyright 2011 Simple Machines
Chris@76:  * @license http://www.simplemachines.org/about/smf/license.php BSD
Chris@76:  *
Chris@76:  * @version 2.0
Chris@76:  */
Chris@76: 
Chris@76: if (!defined('SMF'))
Chris@76: 	die('Hacking attempt...');
Chris@76: 
Chris@76: /*	This file contains database functionality specifically designed for packages to utilize.
Chris@76: 
Chris@76: 	bool smf_db_create_table(string table_name, array columns, array indexes = array(),
Chris@76: 		array parameters = array(), string if_exists = 'ignore')
Chris@76: 		- Can be used to create a table without worrying about schema compatabilities.
Chris@76: 		- If the table exists will, by default, do nothing.
Chris@76: 		- Builds table with columns as passed to it - at least one column must be sent.
Chris@76: 		  The columns array should have one sub-array for each column - these sub arrays contain:
Chris@76: 			+ 'name' = Column name
Chris@76: 			+ 'type' = Type of column - values from (smallint,mediumint,int,text,varchar,char,tinytext,mediumtext,largetext)
Chris@76: 			+ 'size' => Size of column (If applicable) - for example 255 for a large varchar, 10 for an int etc. If not
Chris@76: 						set SMF will pick a size.
Chris@76: 			+ 'default' = Default value - do not set if no default required.
Chris@76: 			+ 'null' => Can it be null (true or false) - if not set default will be false.
Chris@76: 			+ 'auto' => Set to true to make it an auto incrementing column. Set to a numerical value to set
Chris@76: 						from what it should begin counting.
Chris@76: 		- Adds indexes as specified within indexes parameter. Each index should be a member of $indexes. Values are:
Chris@76: 			+ 'name' => Index name (If left empty SMF will generate).
Chris@76: 			+ 'type' => Type of index. Choose from 'primary', 'unique' or 'index'. If not set will default to 'index'.
Chris@76: 			+ 'columns' => Array containing columns that form part of key - in the order the index is to be created.
Chris@76: 		- parameters: (None yet)
Chris@76: 		- if_exists values:
Chris@76: 			+ 'ignore' will do nothing if the table exists. (And will return true)
Chris@76: 			+ 'overwrite' will drop any existing table of the same name.
Chris@76: 			+ 'error' will return false if the table already exists.
Chris@76: 
Chris@76: */
Chris@76: 
Chris@76: // Add the file functions to the $smcFunc array.
Chris@76: function db_packages_init()
Chris@76: {
Chris@76: 	global $smcFunc, $reservedTables, $db_package_log, $db_prefix;
Chris@76: 
Chris@76: 	if (!isset($smcFunc['db_create_table']) || $smcFunc['db_create_table'] != 'smf_db_create_table')
Chris@76: 	{
Chris@76: 		$smcFunc += array(
Chris@76: 			'db_add_column' => 'smf_db_add_column',
Chris@76: 			'db_add_index' => 'smf_db_add_index',
Chris@76: 			'db_calculate_type' => 'smf_db_calculate_type',
Chris@76: 			'db_change_column' => 'smf_db_change_column',
Chris@76: 			'db_create_table' => 'smf_db_create_table',
Chris@76: 			'db_drop_table' => 'smf_db_drop_table',
Chris@76: 			'db_table_structure' => 'smf_db_table_structure',
Chris@76: 			'db_list_columns' => 'smf_db_list_columns',
Chris@76: 			'db_list_indexes' => 'smf_db_list_indexes',
Chris@76: 			'db_remove_column' => 'smf_db_remove_column',
Chris@76: 			'db_remove_index' => 'smf_db_remove_index',
Chris@76: 		);
Chris@76: 		$db_package_log = array();
Chris@76: 	}
Chris@76: 
Chris@76: 	// We setup an array of SMF tables we can't do auto-remove on - in case a mod writer cocks it up!
Chris@76: 	$reservedTables = array('admin_info_files', 'approval_queue', 'attachments', 'ban_groups', 'ban_items',
Chris@76: 		'board_permissions', 'boards', 'calendar', 'calendar_holidays', 'categories', 'collapsed_categories',
Chris@76: 		'custom_fields', 'group_moderators', 'log_actions', 'log_activity', 'log_banned', 'log_boards',
Chris@76: 		'log_digest', 'log_errors', 'log_floodcontrol', 'log_group_requests', 'log_karma', 'log_mark_read',
Chris@76: 		'log_notify', 'log_online', 'log_packages', 'log_polls', 'log_reported', 'log_reported_comments',
Chris@76: 		'log_scheduled_tasks', 'log_search_messages', 'log_search_results', 'log_search_subjects',
Chris@76: 		'log_search_topics', 'log_topics', 'mail_queue', 'membergroups', 'members', 'message_icons',
Chris@76: 		'messages', 'moderators', 'package_servers', 'permission_profiles', 'permissions', 'personal_messages',
Chris@76: 		'pm_recipients', 'poll_choices', 'polls', 'scheduled_tasks', 'sessions', 'settings', 'smileys',
Chris@76: 		'themes', 'topics');
Chris@76: 	foreach ($reservedTables as $k => $table_name)
Chris@76: 		$reservedTables[$k] = strtolower($db_prefix . $table_name);
Chris@76: 
Chris@76: 	// We in turn may need the extra stuff.
Chris@76: 	db_extend('extra');
Chris@76: }
Chris@76: 
Chris@76: // Create a table.
Chris@76: function smf_db_create_table($table_name, $columns, $indexes = array(), $parameters = array(), $if_exists = 'ignore', $error = 'fatal')
Chris@76: {
Chris@76: 	global $reservedTables, $smcFunc, $db_package_log, $db_prefix;
Chris@76: 
Chris@76: 	// Strip out the table name, we might not need it in some cases
Chris@76: 	$real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76: 
Chris@76: 	// With or without the database name, the fullname looks like this.
Chris@76: 	$full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// First - no way do we touch SMF tables.
Chris@76: 	if (in_array(strtolower($table_name), $reservedTables))
Chris@76: 		return false;
Chris@76: 
Chris@76: 	// Log that we'll want to remove this on uninstall.
Chris@76: 	$db_package_log[] = array('remove_table', $table_name);
Chris@76: 
Chris@76: 	// This... my friends... is a function in a half - let's start by checking if the table exists!
Chris@76: 	$tables = $smcFunc['db_list_tables']();
Chris@76: 	if (in_array($full_table_name, $tables))
Chris@76: 	{
Chris@76: 		// This is a sad day... drop the table? If not, return false (error) by default.
Chris@76: 		if ($if_exists == 'overwrite')
Chris@76: 			$smcFunc['db_drop_table']($table_name);
Chris@76: 		else
Chris@76: 			return $if_exists == 'ignore';
Chris@76: 	}
Chris@76: 
Chris@76: 	// If we've got this far - good news - no table exists. We can build our own!
Chris@76: 	$smcFunc['db_transaction']('begin');
Chris@76: 	$table_query = 'CREATE TABLE ' . $table_name . "\n" . '(';
Chris@76: 	foreach ($columns as $column)
Chris@76: 	{
Chris@76: 		// If we have an auto increment do it!
Chris@76: 		if (!empty($column['auto']))
Chris@76: 		{
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				CREATE SEQUENCE ' . $table_name . '_seq',
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 			$default = 'default nextval(\'' . $table_name . '_seq\')';
Chris@76: 		}
Chris@76: 		elseif (isset($column['default']) && $column['default'] !== null)
Chris@76: 			$default = 'default \'' . $smcFunc['db_escape_string']($column['default']) . '\'';
Chris@76: 		else
Chris@76: 			$default = '';
Chris@76: 
Chris@76: 		// Sort out the size...
Chris@76: 		$column['size'] = isset($column['size']) && is_numeric($column['size']) ? $column['size'] : null;
Chris@76: 		list ($type, $size) = $smcFunc['db_calculate_type']($column['type'], $column['size']);
Chris@76: 		if ($size !== null)
Chris@76: 			$type = $type . '(' . $size . ')';
Chris@76: 
Chris@76: 		// Now just put it together!
Chris@76: 		$table_query .= "\n\t\"" . $column['name'] . '" ' . $type . ' ' . (!empty($column['null']) ? '' : 'NOT NULL') . ' ' . $default . ',';
Chris@76: 	}
Chris@76: 
Chris@76: 	// Loop through the indexes a sec...
Chris@76: 	$index_queries = array();
Chris@76: 	foreach ($indexes as $index)
Chris@76: 	{
Chris@76: 		$columns = implode(',', $index['columns']);
Chris@76: 
Chris@76: 		// Primary goes in the table...
Chris@76: 		if (isset($index['type']) && $index['type'] == 'primary')
Chris@76: 			$table_query .= "\n\t" . 'PRIMARY KEY (' . implode(',', $index['columns']) . '),';
Chris@76: 		else
Chris@76: 		{
Chris@76: 			if (empty($index['name']))
Chris@76: 				$index['name'] = implode('_', $index['columns']);
Chris@76: 			$index_queries[] = 'CREATE ' . (isset($index['type']) && $index['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $table_name . '_' . $index['name'] . ' ON ' . $table_name . ' (' . $columns . ')';
Chris@76: 		}
Chris@76: 	}
Chris@76: 
Chris@76: 	// No trailing commas!
Chris@76: 	if (substr($table_query, -1) == ',')
Chris@76: 		$table_query = substr($table_query, 0, -1);
Chris@76: 
Chris@76: 	$table_query .= ')';
Chris@76: 
Chris@76: 	// Create the table!
Chris@76: 	$smcFunc['db_query']('', $table_query,
Chris@76: 		array(
Chris@76: 			'security_override' => true,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	// And the indexes...
Chris@76: 	foreach ($index_queries as $query)
Chris@76: 		$smcFunc['db_query']('', $query,
Chris@76: 		array(
Chris@76: 			'security_override' => true,
Chris@76: 		)
Chris@76: 	);
Chris@76: 
Chris@76: 	// Go, go power rangers!
Chris@76: 	$smcFunc['db_transaction']('commit');
Chris@76: }
Chris@76: 
Chris@76: // Drop a table.
Chris@76: function smf_db_drop_table($table_name, $parameters = array(), $error = 'fatal')
Chris@76: {
Chris@76: 	global $reservedTables, $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	// After stripping away the database name, this is what's left.
Chris@76: 	$real_prefix = preg_match('~^("?)(.+?)\\1\\.(.*?)$~', $db_prefix, $match) === 1 ? $match[3] : $db_prefix;
Chris@76: 
Chris@76: 	// Get some aliases.
Chris@76: 	$full_table_name = str_replace('{db_prefix}', $real_prefix, $table_name);
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// God no - dropping one of these = bad.
Chris@76: 	if (in_array(strtolower($table_name), $reservedTables))
Chris@76: 		return false;
Chris@76: 
Chris@76: 	// Does it exist?
Chris@76: 	if (in_array($full_table_name, $smcFunc['db_list_tables']()))
Chris@76: 	{
Chris@76: 		// We can then drop the table.
Chris@76: 		$smcFunc['db_transaction']('begin');
Chris@76: 
Chris@76: 		// the table
Chris@76: 		$table_query = 'DROP TABLE ' . $table_name;
Chris@76: 
Chris@76: 		// and the assosciated sequence, if any
Chris@76: 		$sequence_query = 'DROP SEQUENCE IF EXISTS ' . $table_name . '_seq';
Chris@76: 
Chris@76: 		// drop them
Chris@76: 		$smcFunc['db_query']('',
Chris@76: 			$table_query,
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 		$smcFunc['db_query']('',
Chris@76: 			$sequence_query,
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 
Chris@76: 		$smcFunc['db_transaction']('commit');
Chris@76: 
Chris@76: 		return true;
Chris@76: 	}
Chris@76: 
Chris@76: 	// Otherwise do 'nout.
Chris@76: 	return false;
Chris@76: }
Chris@76: 
Chris@76: // Add a column.
Chris@76: function smf_db_add_column($table_name, $column_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76: {
Chris@76: 	global $smcFunc, $db_package_log, $txt, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// Log that we will want to uninstall this!
Chris@76: 	$db_package_log[] = array('remove_column', $table_name, $column_info['name']);
Chris@76: 
Chris@76: 	// Does it exist - if so don't add it again!
Chris@76: 	$columns = $smcFunc['db_list_columns']($table_name, false);
Chris@76: 	foreach ($columns as $column)
Chris@76: 		if ($column == $column_info['name'])
Chris@76: 		{
Chris@76: 			// If we're going to overwrite then use change column.
Chris@76: 			if ($if_exists == 'update')
Chris@76: 				return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
Chris@76: 			else
Chris@76: 				return false;
Chris@76: 		}
Chris@76: 
Chris@76: 	// Get the specifics...
Chris@76: 	$column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
Chris@76: 	list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
Chris@76: 	if ($size !== null)
Chris@76: 		$type = $type . '(' . $size . ')';
Chris@76: 
Chris@76: 	// Now add the thing!
Chris@76: 	$query = '
Chris@76: 		ALTER TABLE ' . $table_name . '
Chris@76: 		ADD COLUMN ' . $column_info['name'] . ' ' . $type;
Chris@76: 	$smcFunc['db_query']('', $query,
Chris@76: 		array(
Chris@76: 			'security_override' => true,
Chris@76: 		)
Chris@76: 	);
Chris@76: 
Chris@76: 	// If there's more attributes they need to be done via a change on PostgreSQL.
Chris@76: 	unset($column_info['type'], $column_info['size']);
Chris@76: 
Chris@76: 	if (count($column_info) != 1)
Chris@76: 		return $smcFunc['db_change_column']($table_name, $column_info['name'], $column_info);
Chris@76: 	else
Chris@76: 		return true;
Chris@76: }
Chris@76: 
Chris@76: // Remove a column.
Chris@76: function smf_db_remove_column($table_name, $column_name, $parameters = array(), $error = 'fatal')
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// Does it exist?
Chris@76: 	$columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76: 	foreach ($columns as $column)
Chris@76: 		if ($column['name'] == $column_name)
Chris@76: 		{
Chris@76: 			// If there is an auto we need remove it!
Chris@76: 			if ($column['auto'])
Chris@76: 				$smcFunc['db_query']('',
Chris@76: 					'DROP SEQUENCE ' . $table_name . '_seq',
Chris@76: 					array(
Chris@76: 						'security_override' => true,
Chris@76: 					)
Chris@76: 				);
Chris@76: 
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				ALTER TABLE ' . $table_name . '
Chris@76: 				DROP COLUMN ' . $column_name,
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 
Chris@76: 			return true;
Chris@76: 		}
Chris@76: 
Chris@76: 	// If here we didn't have to work - joy!
Chris@76: 	return false;
Chris@76: }
Chris@76: 
Chris@76: // Change a column.
Chris@76: function smf_db_change_column($table_name, $old_column, $column_info, $parameters = array(), $error = 'fatal')
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// Check it does exist!
Chris@76: 	$columns = $smcFunc['db_list_columns']($table_name, true);
Chris@76: 	$old_info = null;
Chris@76: 	foreach ($columns as $column)
Chris@76: 		if ($column['name'] == $old_column)
Chris@76: 			$old_info = $column;
Chris@76: 
Chris@76: 	// Nothing?
Chris@76: 	if ($old_info == null)
Chris@76: 		return false;
Chris@76: 
Chris@76: 	// Now we check each bit individually and ALTER as required.
Chris@76: 	if (isset($column_info['name']) && $column_info['name'] != $old_column)
Chris@76: 	{
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			RENAME COLUMN ' . $old_column . ' TO ' . $column_info['name'],
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 	}
Chris@76: 	// Different default?
Chris@76: 	if (isset($column_info['default']) && $column_info['default'] != $old_info['default'])
Chris@76: 	{
Chris@76: 		$action = $column_info['default'] !== null ? 'SET DEFAULT \'' . $smcFunc['db_escape_string']($column_info['default']) . '\'' : 'DROP DEFAULT';
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			ALTER COLUMN ' . $column_info['name'] . ' ' . $action,
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 	}
Chris@76: 	// Is it null - or otherwise?
Chris@76: 	if (isset($column_info['null']) && $column_info['null'] != $old_info['null'])
Chris@76: 	{
Chris@76: 		$action = $column_info['null'] ? 'DROP' : 'SET';
Chris@76: 		$smcFunc['db_transaction']('begin');
Chris@76: 		if (!$column_info['null'])
Chris@76: 		{
Chris@76: 			// We have to set it to something if we are making it NOT NULL.
Chris@76: 			$setTo = isset($column_info['default']) ? $column_info['default'] : '';
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				UPDATE ' . $table_name . '
Chris@76: 				SET ' . $column_info['name'] . ' = \'' . $setTo . '\'
Chris@76: 				WHERE ' . $column_info['name'] . ' = NULL',
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 		}
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			ALTER COLUMN ' . $column_info['name'] . ' ' . $action . ' NOT NULL',
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 		$smcFunc['db_transaction']('commit');
Chris@76: 	}
Chris@76: 	// What about a change in type?
Chris@76: 	if (isset($column_info['type']) && ($column_info['type'] != $old_info['type'] || (isset($column_info['size']) && $column_info['size'] != $old_info['size'])))
Chris@76: 	{
Chris@76: 		$column_info['size'] = isset($column_info['size']) && is_numeric($column_info['size']) ? $column_info['size'] : null;
Chris@76: 		list ($type, $size) = $smcFunc['db_calculate_type']($column_info['type'], $column_info['size']);
Chris@76: 		if ($size !== null)
Chris@76: 			$type = $type . '(' . $size . ')';
Chris@76: 
Chris@76: 		// The alter is a pain.
Chris@76: 		$smcFunc['db_transaction']('begin');
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			ADD COLUMN ' . $column_info['name'] . '_tempxx ' . $type,
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			UPDATE ' . $table_name . '
Chris@76: 			SET ' . $column_info['name'] . '_tempxx = CAST(' . $column_info['name'] . ' AS ' . $type . ')',
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			DROP COLUMN ' . $column_info['name'],
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			RENAME COLUMN ' . $column_info['name'] . '_tempxx TO ' . $column_info['name'],
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 		$smcFunc['db_transaction']('commit');
Chris@76: 	}
Chris@76: 	// Finally - auto increment?!
Chris@76: 	if (isset($column_info['auto']) && $column_info['auto'] != $old_info['auto'])
Chris@76: 	{
Chris@76: 		// Are we removing an old one?
Chris@76: 		if ($old_info['auto'])
Chris@76: 		{
Chris@76: 			// Alter the table first - then drop the sequence.
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				ALTER TABLE ' . $table_name . '
Chris@76: 				ALTER COLUMN ' . $column_info['name'] . ' SET DEFAULT \'0\'',
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				DROP SEQUENCE ' . $table_name . '_seq',
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 		}
Chris@76: 		// Otherwise add it!
Chris@76: 		else
Chris@76: 		{
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				CREATE SEQUENCE ' . $table_name . '_seq',
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				ALTER TABLE ' . $table_name . '
Chris@76: 				ALTER COLUMN ' . $column_info['name'] . ' SET DEFAULT nextval(\'' . $table_name . '_seq\')',
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 		}
Chris@76: 	}
Chris@76: }
Chris@76: 
Chris@76: // Add an index.
Chris@76: function smf_db_add_index($table_name, $index_info, $parameters = array(), $if_exists = 'update', $error = 'fatal')
Chris@76: {
Chris@76: 	global $smcFunc, $db_package_log, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// No columns = no index.
Chris@76: 	if (empty($index_info['columns']))
Chris@76: 		return false;
Chris@76: 	$columns = implode(',', $index_info['columns']);
Chris@76: 
Chris@76: 	// No name - make it up!
Chris@76: 	if (empty($index_info['name']))
Chris@76: 	{
Chris@76: 		// No need for primary.
Chris@76: 		if (isset($index_info['type']) && $index_info['type'] == 'primary')
Chris@76: 			$index_info['name'] = '';
Chris@76: 		else
Chris@76: 			$index_info['name'] = $table_name . implode('_', $index_info['columns']);
Chris@76: 	}
Chris@76: 	else
Chris@76: 		$index_info['name'] = $table_name . $index_info['name'];
Chris@76: 
Chris@76: 	// Log that we are going to want to remove this!
Chris@76: 	$db_package_log[] = array('remove_index', $table_name, $index_info['name']);
Chris@76: 
Chris@76: 	// Let's get all our indexes.
Chris@76: 	$indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76: 	// Do we already have it?
Chris@76: 	foreach ($indexes as $index)
Chris@76: 	{
Chris@76: 		if ($index['name'] == $index_info['name'] || ($index['type'] == 'primary' && isset($index_info['type']) && $index_info['type'] == 'primary'))
Chris@76: 		{
Chris@76: 			// If we want to overwrite simply remove the current one then continue.
Chris@76: 			if ($if_exists != 'update' || $index['type'] == 'primary')
Chris@76: 				return false;
Chris@76: 			else
Chris@76: 				$smcFunc['db_remove_index']($table_name, $index_info['name']);
Chris@76: 		}
Chris@76: 	}
Chris@76: 
Chris@76: 	// If we're here we know we don't have the index - so just add it.
Chris@76: 	if (!empty($index_info['type']) && $index_info['type'] == 'primary')
Chris@76: 	{
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE ' . $table_name . '
Chris@76: 			ADD PRIMARY KEY (' . $columns . ')',
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 	}
Chris@76: 	else
Chris@76: 	{
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			CREATE ' . (isset($index_info['type']) && $index_info['type'] == 'unique' ? 'UNIQUE' : '') . ' INDEX ' . $index_info['name'] . ' ON ' . $table_name . ' (' . $columns . ')',
Chris@76: 			array(
Chris@76: 				'security_override' => true,
Chris@76: 			)
Chris@76: 		);
Chris@76: 	}
Chris@76: }
Chris@76: 
Chris@76: // Remove an index.
Chris@76: function smf_db_remove_index($table_name, $index_name, $parameters = array(), $error = 'fatal')
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	// Better exist!
Chris@76: 	$indexes = $smcFunc['db_list_indexes']($table_name, true);
Chris@76: 	if ($index_name != 'primary')
Chris@76: 		$index_name = $table_name . '_' . $index_name;
Chris@76: 
Chris@76: 	foreach ($indexes as $index)
Chris@76: 	{
Chris@76: 		// If the name is primary we want the primary key!
Chris@76: 		if ($index['type'] == 'primary' && $index_name == 'primary')
Chris@76: 		{
Chris@76: 			// Dropping primary key is odd...
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				ALTER TABLE ' . $table_name . '
Chris@76: 				DROP CONSTRAINT ' . $index['name'],
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 
Chris@76: 			return true;
Chris@76: 		}
Chris@76: 		if ($index['name'] == $index_name)
Chris@76: 		{
Chris@76: 			// Drop the bugger...
Chris@76: 			$smcFunc['db_query']('', '
Chris@76: 				DROP INDEX ' . $index_name,
Chris@76: 				array(
Chris@76: 					'security_override' => true,
Chris@76: 				)
Chris@76: 			);
Chris@76: 
Chris@76: 			return true;
Chris@76: 		}
Chris@76: 	}
Chris@76: 
Chris@76: 	// Not to be found ;(
Chris@76: 	return false;
Chris@76: }
Chris@76: 
Chris@76: // Get the schema formatted name for a type.
Chris@76: function smf_db_calculate_type($type_name, $type_size = null, $reverse = false)
Chris@76: {
Chris@76: 	// Generic => Specific.
Chris@76: 	if (!$reverse)
Chris@76: 	{
Chris@76: 		$types = array(
Chris@76: 			'varchar' => 'character varying',
Chris@76: 			'char' => 'character',
Chris@76: 			'mediumint' => 'int',
Chris@76: 			'tinyint' => 'smallint',
Chris@76: 			'tinytext' => 'character varying',
Chris@76: 			'mediumtext' => 'text',
Chris@76: 			'largetext' => 'text',
Chris@76: 		);
Chris@76: 	}
Chris@76: 	else
Chris@76: 	{
Chris@76: 		$types = array(
Chris@76: 			'character varying' => 'varchar',
Chris@76: 			'character' => 'char',
Chris@76: 			'integer' => 'int',
Chris@76: 		);
Chris@76: 	}
Chris@76: 
Chris@76: 	// Got it? Change it!
Chris@76: 	if (isset($types[$type_name]))
Chris@76: 	{
Chris@76: 		if ($type_name == 'tinytext')
Chris@76: 			$type_size = 255;
Chris@76: 		$type_name = $types[$type_name];
Chris@76: 	}
Chris@76: 	// Numbers don't have a size.
Chris@76: 	if (strpos($type_name, 'int') !== false)
Chris@76: 			$type_size = null;
Chris@76: 
Chris@76: 	return array($type_name, $type_size);
Chris@76: }
Chris@76: 
Chris@76: // Get table structure.
Chris@76: function smf_db_table_structure($table_name, $parameters = array())
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	return array(
Chris@76: 		'name' => $table_name,
Chris@76: 		'columns' => $smcFunc['db_list_columns']($table_name, true),
Chris@76: 		'indexes' => $smcFunc['db_list_indexes']($table_name, true),
Chris@76: 	);
Chris@76: }
Chris@76: 
Chris@76: // Return column information for a table.
Chris@76: function smf_db_list_columns($table_name, $detail = false, $parameters = array())
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SELECT column_name, column_default, is_nullable, data_type, character_maximum_length
Chris@76: 		FROM information_schema.columns
Chris@76: 		WHERE table_name = \'' . $table_name . '\'
Chris@76: 		ORDER BY ordinal_position',
Chris@76: 		array(
Chris@76: 			'security_override' => true,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	$columns = array();
Chris@76: 	while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76: 	{
Chris@76: 		if (!$detail)
Chris@76: 		{
Chris@76: 			$columns[] = $row['column_name'];
Chris@76: 		}
Chris@76: 		else
Chris@76: 		{
Chris@76: 			$auto = false;
Chris@76: 			// What is the default?
Chris@76: 			if (preg_match('~nextval\(\'(.+?)\'(.+?)*\)~i', $row['column_default'], $matches) != 0)
Chris@76: 			{
Chris@76: 				$default = null;
Chris@76: 				$auto = true;
Chris@76: 			}
Chris@76: 			elseif (trim($row['column_default']) != '')
Chris@76: 				$default = strpos($row['column_default'], '::') === false ? $row['column_default'] : substr($row['column_default'], 0, strpos($row['column_default'], '::'));
Chris@76: 			else
Chris@76: 				$default = null;
Chris@76: 
Chris@76: 			// Make the type generic.
Chris@76: 			list ($type, $size) = $smcFunc['db_calculate_type']($row['data_type'], $row['character_maximum_length'], true);
Chris@76: 
Chris@76: 			$columns[$row['column_name']] = array(
Chris@76: 				'name' => $row['column_name'],
Chris@76: 				'null' => $row['is_nullable'] ? true : false,
Chris@76: 				'default' => $default,
Chris@76: 				'type' => $type,
Chris@76: 				'size' => $size,
Chris@76: 				'auto' => $auto,
Chris@76: 			);
Chris@76: 		}
Chris@76: 	}
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	return $columns;
Chris@76: }
Chris@76: 
Chris@76: // What about some index information?
Chris@76: function smf_db_list_indexes($table_name, $detail = false, $parameters = array())
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table_name = str_replace('{db_prefix}', $db_prefix, $table_name);
Chris@76: 
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SELECT CASE WHEN i.indisprimary THEN 1 ELSE 0 END AS is_primary,
Chris@76: 			CASE WHEN i.indisunique THEN 1 ELSE 0 END AS is_unique,
Chris@76: 			c2.relname AS name,
Chris@76: 			pg_get_indexdef(i.indexrelid) AS inddef
Chris@76: 		FROM pg_class AS c, pg_class AS c2, pg_index AS i
Chris@76: 		WHERE c.relname = \'' . $table_name . '\'
Chris@76: 			AND c.oid = i.indrelid
Chris@76: 			AND i.indexrelid = c2.oid',
Chris@76: 		array(
Chris@76: 			'security_override' => true,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	$indexes = array();
Chris@76: 	while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76: 	{
Chris@76: 		// Try get the columns that make it up.
Chris@76: 		if (preg_match('~\(([^\)]+?)\)~i', $row['inddef'], $matches) == 0)
Chris@76: 			continue;
Chris@76: 
Chris@76: 		$columns = explode(',', $matches[1]);
Chris@76: 
Chris@76: 		if (empty($columns))
Chris@76: 			continue;
Chris@76: 
Chris@76: 		foreach ($columns as $k => $v)
Chris@76: 			$columns[$k] = trim($v);
Chris@76: 
Chris@76: 		// Fix up the name to be consistent cross databases
Chris@76: 		if (substr($row['name'], -5) == '_pkey' && $row['is_primary'] == 1)
Chris@76: 			$row['name'] = 'PRIMARY';
Chris@76: 		else
Chris@76: 			$row['name'] = str_replace($table_name . '_', '', $row['name']);
Chris@76: 
Chris@76: 		if (!$detail)
Chris@76: 			$indexes[] = $row['name'];
Chris@76: 		else
Chris@76: 		{
Chris@76: 			$indexes[$row['name']] = array(
Chris@76: 				'name' => $row['name'],
Chris@76: 				'type' => $row['is_primary'] ? 'primary' : ($row['is_unique'] ? 'unique' : 'index'),
Chris@76: 				'columns' => $columns,
Chris@76: 			);
Chris@76: 		}
Chris@76: 	}
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	return $indexes;
Chris@76: }
Chris@76: 
Chris@76: ?>