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_backup' => 'smf_db_get_backup',
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: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SELECT sql
Chris@76: 		FROM sqlite_master
Chris@76: 		WHERE type = {string:txttable}
Chris@76: 			AND name = {string:table}',
Chris@76: 		array(
Chris@76: 			'table' => $table,
Chris@76: 			'txttable' => '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: 	$auto_inc = '';
Chris@76: 
Chris@76: 	// Remove the first line and check to see if the second one contain useless info.
Chris@76: 	unset($create[0]);
Chris@76: 	if (trim($create[1]) == '(')
Chris@76: 		unset($create[1]);
Chris@76: 	if (trim($create[count($create)]) == ')')
Chris@76: 		unset($create[count($create)]);
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, 'primary') || strpos($l, 'PRIMARY'))
Chris@76: 			$auto_inc = trim($l);
Chris@76: 
Chris@76: 		// Skip everything but keys...
Chris@76: 		if ((strpos($l, 'KEY') !== false && strpos($l, 'PRIMARY KEY') === false) || strpos($l, $table) !== false || strpos(trim($l), 'PRIMARY KEY') === 0)
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: 	// Is there an extra junk at the end?
Chris@76: 	if (substr($create, -2, 1) == ',')
Chris@76: 		$create = substr($create, 0, -2) . ')';
Chris@76: 	if (substr($create, -2) == '))')
Chris@76: 		$create = substr($create, 0, -1);
Chris@76: 
Chris@76: 	$smcFunc['db_query']('', '
Chris@76: 		DROP TABLE {raw:backup_table}',
Chris@76: 		array(
Chris@76: 			'backup_table' => $backup_table,
Chris@76: 			'db_error_skip' => true,
Chris@76: 		)
Chris@76: 	);
Chris@76: 
Chris@76: 	$request = $smcFunc['db_quote']('
Chris@76: 		CREATE TABLE {raw:backup_table} {raw:create}',
Chris@76: 		array(
Chris@76: 			'backup_table' => $backup_table,
Chris@76: 			'create' => $create,
Chris@76: 	));
Chris@76: 
Chris@76: 	$smcFunc['db_query']('', '
Chris@76: 		CREATE TABLE {raw:backup_table} {raw:create}',
Chris@76: 		array(
Chris@76: 			'backup_table' => $backup_table,
Chris@76: 			'create' => $create,
Chris@76: 	));
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: 	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_prefix;
Chris@76: 
Chris@76: 	$table = str_replace('{db_prefix}', $db_prefix, $table);
Chris@76: 
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 		VACUUM {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: 	$row = $smcFunc['db_fetch_assoc']($request);
Chris@76: 	$smcFunc['db_free_result']($request);
Chris@76: 
Chris@76: 	// The function returns nothing.
Chris@76: 	return 0;
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 $smcFunc;
Chris@76: 
Chris@76: 	$filter = $filter == false ? '' : ' AND name LIKE \'' . str_replace("\_", "_", $filter) . '\'';
Chris@76: 
Chris@76: 	$request = $smcFunc['db_query']('', '
Chris@76: 		SELECT name
Chris@76: 		FROM sqlite_master
Chris@76: 		WHERE type = {string:type}
Chris@76: 		{raw:filter}
Chris@76: 		ORDER BY name',
Chris@76: 		array(
Chris@76: 			'type' => 'table',
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 *
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: 
Chris@76: 	if ($num_rows == 0)
Chris@76: 		return '';
Chris@76: 
Chris@76: 	$fields = array_keys($smcFunc['db_fetch_assoc']($result));
Chris@76: 
Chris@76: 	// SQLite fetches an array so we need to filter out the numberic index for the columns.
Chris@76: 	foreach ($fields as $key => $name)
Chris@76: 		if (is_numeric($name))
Chris@76: 			unset($fields[$key]);
Chris@76: 
Chris@76: 	$smcFunc['db_data_seek']($result, 0);
Chris@76: 
Chris@76: 	// Start it off with the basic INSERT INTO.
Chris@76: 	$data = 'BEGIN TRANSACTION;' . $crlf;
Chris@76: 
Chris@76: 	// Loop through each row.
Chris@76: 	while ($row = $smcFunc['db_fetch_row']($result))
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: 		$data .= 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $field_list) . ');' . $crlf;
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 . 'COMMIT;' . $crlf;
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: 	// Start the create table...
Chris@76: 	$schema_create = '';
Chris@76: 	$index_create = '';
Chris@76: 
Chris@76: 	// Let's get the create statement directly from SQLite.
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SELECT sql
Chris@76: 		FROM sqlite_master
Chris@76: 		WHERE type = {string:type}
Chris@76: 			AND name = {string:table_name}',
Chris@76: 		array(
Chris@76: 			'type' => 'table',
Chris@76: 			'table_name' => $tableName,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	list ($schema_create) = $smcFunc['db_fetch_row']($result);
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	// Now the indexes.
Chris@76: 	$result = $smcFunc['db_query']('', '
Chris@76: 		SELECT sql
Chris@76: 		FROM sqlite_master
Chris@76: 		WHERE type = {string:type}
Chris@76: 			AND tbl_name = {string:table_name}',
Chris@76: 		array(
Chris@76: 			'type' => 'index',
Chris@76: 			'table_name' => $tableName,
Chris@76: 		)
Chris@76: 	);
Chris@76: 	$indexes = array();
Chris@76: 	while ($row = $smcFunc['db_fetch_assoc']($result))
Chris@76: 		if (trim($row['sql']) != '')
Chris@76: 			$indexes[] = $row['sql'];
Chris@76: 	$smcFunc['db_free_result']($result);
Chris@76: 
Chris@76: 	$index_create .= implode(';' . $crlf, $indexes);
Chris@76: 	$schema_create = empty($indexes) ? rtrim($schema_create) : $schema_create . ';' . $crlf . $crlf;
Chris@76: 
Chris@76: 	return $schema_create . $index_create;
Chris@76: }
Chris@76: 
Chris@76: // Get the version number.
Chris@76: function smf_db_get_version()
Chris@76: {
Chris@76: 	return sqlite_libversion();
Chris@76: }
Chris@76: 
Chris@76: // Simple return the database - and die!
Chris@76: function smf_db_get_backup()
Chris@76: {
Chris@76: 	global $db_name;
Chris@76: 
Chris@76: 	$db_file = substr($db_name, -3) === '.db' ? $db_name : $db_name . '.db';
Chris@76: 
Chris@76: 	// Add more info if zipped...
Chris@76: 	$ext = '';
Chris@76: 	if (isset($_REQUEST['compress']) && function_exists('gzencode'))
Chris@76: 		$ext = '.gz';
Chris@76: 
Chris@76: 	// Do the remaining headers.
Chris@76: 	header('Content-Disposition: attachment; filename="' . $db_file . $ext . '"');
Chris@76: 	header('Cache-Control: private');
Chris@76: 	header('Connection: close');
Chris@76: 
Chris@76: 	// Literally dump the contents.  Try reading the file first.
Chris@76: 	if (@readfile($db_file) == null)
Chris@76: 		echo file_get_contents($db_file);
Chris@76: 
Chris@76: 	obExit(false);
Chris@76: }
Chris@76: 
Chris@76: ?>