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 rarely used extended database functionality.
Chris@76: 
Chris@76: 	void db_extra_init()
Chris@76: 		- add this file's functions to the $smcFunc array.
Chris@76: 
Chris@76: 	resource smf_db_backup_table($table, $backup_table)
Chris@76: 		- backup $table to $backup_table.
Chris@76: 		- returns the request handle to the table creation query
Chris@76: 
Chris@76: 	string function smf_db_get_version()
Chris@76: 		- get the version number.
Chris@76: 
Chris@76: 	string db_insert_sql(string table_name)
Chris@76: 		- gets all the necessary INSERTs for the table named table_name.
Chris@76: 		- goes in 250 row segments.
Chris@76: 		- returns the query to insert the data back in.
Chris@76: 		- returns an empty string if the table was empty.
Chris@76: 
Chris@76: 	array smf_db_list_tables($db = false, $filter = false)
Chris@76: 		- lists all tables in the database
Chris@76: 		- could be filtered according to $filter
Chris@76: 		- returns an array of table names. (strings)
Chris@76: 
Chris@76: 	float smf_db_optimize_table($table)
Chris@76: 		- optimize a table
Chris@76: 		- $table - the table to be optimized
Chris@76: 		- returns how much it was gained
Chris@76: 
Chris@76: 	string db_table_sql(string table_name)
Chris@76: 		- dumps the CREATE for the specified table. (by table_name.)
Chris@76: 		- returns the CREATE statement.
Chris@76: 
Chris@76: */
Chris@76: 
Chris@76: // Add the file functions to the $smcFunc array.
Chris@76: function db_extra_init()
Chris@76: {
Chris@76: 	global $smcFunc;
Chris@76: 
Chris@76: 	if (!isset($smcFunc['db_backup_table']) || $smcFunc['db_backup_table'] != 'smf_db_backup_table')
Chris@76: 		$smcFunc += array(
Chris@76: 			'db_backup_table' => 'smf_db_backup_table',
Chris@76: 			'db_optimize_table' => 'smf_db_optimize_table',
Chris@76: 			'db_insert_sql' => 'smf_db_insert_sql',
Chris@76: 			'db_table_sql' => 'smf_db_table_sql',
Chris@76: 			'db_list_tables' => 'smf_db_list_tables',
Chris@76: 			'db_get_version' => 'smf_db_get_version',
Chris@76: 		);
Chris@76: }
Chris@76: 
Chris@76: // Backup $table to $backup_table.
Chris@76: function smf_db_backup_table($table, $backup_table)
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76: 
Chris@76: 	// First, get rid of the old table.
Chris@76: 	$smcFunc['db_query']('', '
Chris@76: 		DROP TABLE IF EXISTS {raw:backup_table}',
Chris@76: 		array(
Chris@76: 			'backup_table' => $backup_table,
Chris@76: 		)
Chris@76: 	);
Chris@76: 
Chris@76: 	// Can we do this the quick way?
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		CREATE TABLE {raw:backup_table} LIKE {raw:table}',
Chris@76: 		array(
Chris@76: 			'backup_table' => $backup_table,
Chris@76: 			'table' => $table
Chris@76: 	));
Chris@76: 	// If this failed, we go old school.
Chris@76: 	if ($result)
Chris@76: 	{
Chris@76: 		$request = $smcFunc['db_query']('', '
Chris@76: 			INSERT INTO {raw:backup_table}
Chris@76: 			SELECT *
Chris@76: 			FROM {raw:table}',
Chris@76: 			array(
Chris@76: 				'backup_table' => $backup_table,
Chris@76: 				'table' => $table
Chris@76: 			));
Chris@76: 
Chris@76: 		// Old school or no school?
Chris@76: 		if ($request)
Chris@76: 			return $request;
Chris@76: 	}
Chris@76: 
Chris@76: 	// At this point, the quick method failed.
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SHOW CREATE TABLE {raw:table}',
Chris@76: 		array(
Chris@76: 			'table' => $table,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	list (, $create) = $smcFunc['db_fetch_row']($result);
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	$create = preg_split('/[\n\r]/', $create);
Chris@76: 
Chris@76: 	$auto_inc = '';
Chris@76: 	// Default engine type.
Chris@76: 	$engine = 'MyISAM';
Chris@76: 	$charset = '';
Chris@76: 	$collate = '';
Chris@76: 
Chris@76: 	foreach ($create as $k => $l)
Chris@76: 	{
Chris@76: 		// Get the name of the auto_increment column.
Chris@76: 		if (strpos($l, 'auto_increment'))
Chris@76: 			$auto_inc = trim($l);
Chris@76: 
Chris@76: 		// For the engine type, see if we can work out what it is.
Chris@76: 		if (strpos($l, 'ENGINE') !== false || strpos($l, 'TYPE') !== false)
Chris@76: 		{
Chris@76: 			// Extract the engine type.
Chris@76: 			preg_match('~(ENGINE|TYPE)=(\w+)(\sDEFAULT)?(\sCHARSET=(\w+))?(\sCOLLATE=(\w+))?~', $l, $match);
Chris@76: 
Chris@76: 			if (!empty($match[1]))
Chris@76: 				$engine = $match[1];
Chris@76: 
Chris@76: 			if (!empty($match[2]))
Chris@76: 				$engine = $match[2];
Chris@76: 
Chris@76: 			if (!empty($match[5]))
Chris@76: 				$charset = $match[5];
Chris@76: 
Chris@76: 			if (!empty($match[7]))
Chris@76: 				$collate = $match[7];
Chris@76: 		}
Chris@76: 
Chris@76: 		// Skip everything but keys...
Chris@76: 		if (strpos($l, 'KEY') === false)
Chris@76: 			unset($create[$k]);
Chris@76: 	}
Chris@76: 
Chris@76: 	if (!empty($create))
Chris@76: 		$create = '(
Chris@76: 			' . implode('
Chris@76: 			', $create) . ')';
Chris@76: 	else
Chris@76: 		$create = '';
Chris@76: 
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 		CREATE TABLE {raw:backup_table} {raw:create}
Chris@76: 		ENGINE={raw:engine}' . (empty($charset) ? '' : ' CHARACTER SET {raw:charset}' . (empty($collate) ? '' : ' COLLATE {raw:collate}')) . '
Chris@76: 		SELECT *
Chris@76: 		FROM {raw:table}',
Chris@76: 		array(
Chris@76: 			'backup_table' => $backup_table,
Chris@76: 			'table' => $table,
Chris@76: 			'create' => $create,
Chris@76: 			'engine' => $engine,
Chris@76: 			'charset' => empty($charset) ? '' : $charset,
Chris@76: 			'collate' => empty($collate) ? '' : $collate,
Chris@76: 		)
Chris@76: 	);
Chris@76: 
Chris@76: 	if ($auto_inc != '')
Chris@76: 	{
Chris@76: 		if (preg_match('~\`(.+?)\`\s~', $auto_inc, $match) != 0 && substr($auto_inc, -1, 1) == ',')
Chris@76: 			$auto_inc = substr($auto_inc, 0, -1);
Chris@76: 
Chris@76: 		$smcFunc['db_query']('', '
Chris@76: 			ALTER TABLE {raw:backup_table}
Chris@76: 			CHANGE COLUMN {raw:column_detail} {raw:auto_inc}',
Chris@76: 			array(
Chris@76: 				'backup_table' => $backup_table,
Chris@76: 				'column_detail' => $match[1],
Chris@76: 				'auto_inc' => $auto_inc,
Chris@76: 			)
Chris@76: 		);
Chris@76: 	}
Chris@76: 
Chris@76: 	return $request;
Chris@76: }
Chris@76: 
Chris@76: // Optimize a table - return data freed!
Chris@76: function smf_db_optimize_table($table)
Chris@76: {
Chris@76: 	global $smcFunc, $db_name, $db_prefix;
Chris@76: 
Chris@76: 	$table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76: 
Chris@76: 	// Get how much overhead there is.
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 			SHOW TABLE STATUS LIKE {string:table_name}',
Chris@76: 			array(
Chris@76: 				'table_name' => str_replace('_', '\_', $table),
Chris@76: 			)
Chris@76: 		);
Chris@76: 	$row = $smcFunc['db_fetch_assoc']($request);
Chris@76: 	$smcFunc['db_free_result']($request);
Chris@76: 
Chris@76: 	$data_before = isset($row['Data_free']) ? $row['Data_free'] : 0;
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 			OPTIMIZE TABLE `{raw:table}`',
Chris@76: 			array(
Chris@76: 				'table' => $table,
Chris@76: 			)
Chris@76: 		);
Chris@76: 	if (!$request)
Chris@76: 		return -1;
Chris@76: 
Chris@76: 	// How much left?
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 			SHOW TABLE STATUS LIKE {string:table}',
Chris@76: 			array(
Chris@76: 				'table' => str_replace('_', '\_', $table),
Chris@76: 			)
Chris@76: 		);
Chris@76: 	$row = $smcFunc['db_fetch_assoc']($request);
Chris@76: 	$smcFunc['db_free_result']($request);
Chris@76: 
Chris@76: 	$total_change = isset($row['Data_free']) && $data_before > $row['Data_free'] ? $data_before / 1024 : 0;
Chris@76: 
Chris@76: 	return $total_change;
Chris@76: }
Chris@76: 
Chris@76: // List all the tables in the database.
Chris@76: function smf_db_list_tables($db = false, $filter = false)
Chris@76: {
Chris@76: 	global $db_name, $smcFunc;
Chris@76: 
Chris@76: 	$db = $db == false ? $db_name : $db;
Chris@76: 	$db = trim($db);
Chris@76: 	$filter = $filter == false ? '' : ' LIKE \'' . $filter . '\'';
Chris@76: 
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 		SHOW TABLES
Chris@76: 		FROM `{raw:db}`
Chris@76: 		{raw:filter}',
Chris@76: 		array(
Chris@76: 			'db' => $db[0] == '`' ? strtr($db, array('`' => '')) : $db,
Chris@76: 			'filter' => $filter,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	$tables = array();
Chris@76: 	while ($row = $smcFunc['db_fetch_row']($request))
Chris@76: 		$tables[] = $row[0];
Chris@76: 	$smcFunc['db_free_result']($request);
Chris@76: 
Chris@76: 	return $tables;
Chris@76: }
Chris@76: 
Chris@76: // Get the content (INSERTs) for a table.
Chris@76: function smf_db_insert_sql($tableName)
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76: 
Chris@76: 	// This will be handy...
Chris@76: 	$crlf = "\r\n";
Chris@76: 
Chris@76: 	// Get everything from the table.
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SELECT /*!40001 SQL_NO_CACHE */ *
Chris@76: 		FROM `{raw:table}`',
Chris@76: 		array(
Chris@76: 			'table' => $tableName,
Chris@76: 		)
Chris@76: 	);
Chris@76: 
Chris@76: 	// The number of rows, just for record keeping and breaking INSERTs up.
Chris@76: 	$num_rows = $smcFunc['db_num_rows']($result);
Chris@76: 	$current_row = 0;
Chris@76: 
Chris@76: 	if ($num_rows == 0)
Chris@76: 		return '';
Chris@76: 
Chris@76: 	$fields = array_keys($smcFunc['db_fetch_assoc']($result));
Chris@76: 	$smcFunc['db_data_seek']($result, 0);
Chris@76: 
Chris@76: 	// Start it off with the basic INSERT INTO.
Chris@76: 	$data = 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
Chris@76: 
Chris@76: 	// Loop through each row.
Chris@76: 	while ($row = $smcFunc['db_fetch_row']($result))
Chris@76: 	{
Chris@76: 		$current_row++;
Chris@76: 
Chris@76: 		// Get the fields in this row...
Chris@76: 		$field_list = array();
Chris@76: 		for ($j = 0; $j < $smcFunc['db_num_fields']($result); $j++)
Chris@76: 		{
Chris@76: 			// Try to figure out the type of each field. (NULL, number, or 'string'.)
Chris@76: 			if (!isset($row[$j]))
Chris@76: 				$field_list[] = 'NULL';
Chris@76: 			elseif (is_numeric($row[$j]) && (int) $row[$j] == $row[$j])
Chris@76: 				$field_list[] = $row[$j];
Chris@76: 			else
Chris@76: 				$field_list[] = '\'' . $smcFunc['db_escape_string']($row[$j]) . '\'';
Chris@76: 		}
Chris@76: 
Chris@76: 		// 'Insert' the data.
Chris@76: 		$data .= '(' . implode(', ', $field_list) . ')';
Chris@76: 
Chris@76: 		// All done!
Chris@76: 		if ($current_row == $num_rows)
Chris@76: 			$data .= ';' . $crlf;
Chris@76: 		// Start a new INSERT statement after every 250....
Chris@76: 		elseif ($current_row > 249 && $current_row % 250 == 0)
Chris@76: 			$data .= ';' . $crlf . 'INSERT INTO `' . $tableName . '`' . $crlf . "\t" . '(`' . implode('`, `', $fields) . '`)' . $crlf . 'VALUES ';
Chris@76: 		// Otherwise, go to the next line.
Chris@76: 		else
Chris@76: 			$data .= ',' . $crlf . "\t";
Chris@76: 	}
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	// Return an empty string if there were no rows.
Chris@76: 	return $num_rows == 0 ? '' : $data;
Chris@76: }
Chris@76: 
Chris@76: // Get the schema (CREATE) for a table.
Chris@76: function smf_db_table_sql($tableName)
Chris@76: {
Chris@76: 	global $smcFunc, $db_prefix;
Chris@76: 
Chris@76: 	$tableName = str_replace('{db_prefix}', $db_prefix, $tableName);
Chris@76: 
Chris@76: 	// This will be needed...
Chris@76: 	$crlf = "\r\n";
Chris@76: 
Chris@76: 	// Drop it if it exists.
Chris@76: 	$schema_create = 'DROP TABLE IF EXISTS `' . $tableName . '`;' . $crlf . $crlf;
Chris@76: 
Chris@76: 	// Start the create table...
Chris@76: 	$schema_create .= 'CREATE TABLE `' . $tableName . '` (' . $crlf;
Chris@76: 
Chris@76: 	// Find all the fields.
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SHOW FIELDS
Chris@76: 		FROM `{raw:table}`',
Chris@76: 		array(
Chris@76: 			'table' => $tableName,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76: 	{
Chris@76: 		// Make the CREATE for this column.
Chris@76: 		$schema_create .= ' `' . $row['Field'] . '` ' . $row['Type'] . ($row['Null'] != 'YES' ? ' NOT NULL' : '');
Chris@76: 
Chris@76: 		// Add a default...?
Chris@76: 		if (!empty($row['Default']) || $row['Null'] !== 'YES')
Chris@76: 		{
Chris@76: 			// Make a special case of auto-timestamp.
Chris@76: 			if ($row['Default'] == 'CURRENT_TIMESTAMP')
Chris@76: 				$schema_create .= ' /*!40102 NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP */';
Chris@76: 			// Text shouldn't have a default.
Chris@76: 			elseif ($row['Default'] !== null)
Chris@76: 			{
Chris@76: 				// If this field is numeric the default needs no escaping.
Chris@76: 				$type = strtolower($row['Type']);
Chris@76: 				$isNumericColumn = strpos($type, 'int') !== false || strpos($type, 'bool') !== false || strpos($type, 'bit') !== false || strpos($type, 'float') !== false || strpos($type, 'double') !== false || strpos($type, 'decimal') !== false;
Chris@76: 
Chris@76: 				$schema_create .= ' default ' . ($isNumericColumn ? $row['Default'] : '\'' . $smcFunc['db_escape_string']($row['Default']) . '\'');
Chris@76: 			}
Chris@76: 		}
Chris@76: 
Chris@76: 		// And now any extra information. (such as auto_increment.)
Chris@76: 		$schema_create .= ($row['Extra'] != '' ? ' ' . $row['Extra'] : '') . ',' . $crlf;
Chris@76: 	}
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	// Take off the last comma.
Chris@76: 	$schema_create = substr($schema_create, 0, -strlen($crlf) - 1);
Chris@76: 
Chris@76: 	// Find the keys.
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SHOW KEYS
Chris@76: 		FROM `{raw:table}`',
Chris@76: 		array(
Chris@76: 			'table' => $tableName,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	$indexes = array();
Chris@76: 	while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76: 	{
Chris@76: 		// IS this a primary key, unique index, or regular index?
Chris@76: 		$row['Key_name'] = $row['Key_name'] == 'PRIMARY' ? 'PRIMARY KEY' : (empty($row['Non_unique']) ? 'UNIQUE ' : ($row['Comment'] == 'FULLTEXT' || (isset($row['Index_type']) && $row['Index_type'] == 'FULLTEXT') ? 'FULLTEXT ' : 'KEY ')) . '`' . $row['Key_name'] . '`';
Chris@76: 
Chris@76: 		// Is this the first column in the index?
Chris@76: 		if (empty($indexes[$row['Key_name']]))
Chris@76: 			$indexes[$row['Key_name']] = array();
Chris@76: 
Chris@76: 		// A sub part, like only indexing 15 characters of a varchar.
Chris@76: 		if (!empty($row['Sub_part']))
Chris@76: 			$indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`(' . $row['Sub_part'] . ')';
Chris@76: 		else
Chris@76: 			$indexes[$row['Key_name']][$row['Seq_in_index']] = '`' . $row['Column_name'] . '`';
Chris@76: 	}
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	// Build the CREATEs for the keys.
Chris@76: 	foreach ($indexes as $keyname => $columns)
Chris@76: 	{
Chris@76: 		// Ensure the columns are in proper order.
Chris@76: 		ksort($columns);
Chris@76: 
Chris@76: 		$schema_create .= ',' . $crlf . ' ' . $keyname . ' (' . implode($columns, ', ') . ')';
Chris@76: 	}
Chris@76: 
Chris@76: 	// Now just get the comment and type... (MyISAM, etc.)
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SHOW TABLE STATUS
Chris@76: 		LIKE {string:table}',
Chris@76: 		array(
Chris@76: 			'table' => strtr($tableName, array('_' => '\\_', '%' => '\\%')),
Chris@76: 		)
Chris@76: 	);
Chris@76: 	$row = $smcFunc['db_fetch_assoc']($result);
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	// Probably MyISAM.... and it might have a comment.
Chris@76: 	$schema_create .= $crlf . ') ENGINE=' . (isset($row['Type']) ? $row['Type'] : $row['Engine']) . ($row['Comment'] != '' ? ' COMMENT="' . $row['Comment'] . '"' : '');
Chris@76: 
Chris@76: 	return $schema_create;
Chris@76: }
Chris@76: 
Chris@76: // Get the version number.
Chris@76: function smf_db_get_version()
Chris@76: {
Chris@76: 	global $smcFunc;
Chris@76: 
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 		SELECT VERSION()',
Chris@76: 		array(
Chris@76: 		)
Chris@76: 	);
Chris@76: 	list ($ver) = $smcFunc['db_fetch_row']($request);
Chris@76: 	$smcFunc['db_free_result']($request);
Chris@76: 
Chris@76: 	return $ver;
Chris@76: }
Chris@76: 
Chris@76: ?>