~quam-plures-core/quam-plures/install_bug_fix

<?php
/**
 * This file implements upgrading of DB tables
 *
 * @author {@link http://wonderwinds.com/ Ed Bennett}
 * @author {@link http://fplanque.net/ Francois PLANQUE}
 * @copyright (c) 2009 by {@link http://quamplures.net/ the Quam Plures project}
 * @license http://www.gnu.org/licenses/gpl.txt GNU General Public License v3
 * @package install
 */
if(!defined('QP_MAIN_INIT')) die('fail');

load_funcs( '_core/_param.funcs.php' );

/**
 * Converts languages in a given table into according locales
 *
 * @param string name of the table
 * @param string name of the column where lang is stored
 * @param string name of the table's ID column
 */
function convert_lang_to_locale( $table, $columnlang, $columnID )
{
	global $DB;
	global $default_locale;
	global $locales;

	if( !preg_match('/[a-z]{2}-[A-Z]{2}(-.{1,14})?/', $default_locale) )
	{
		// we want a valid locale
		$default_locale = 'en-EU';
	}
	echo 'Converting langs to locales for '. $table. '...<br />';
	// query given languages in $table
	$query = "SELECT $columnID, $columnlang FROM $table";
	$languagestoconvert = array();
	foreach( $DB->get_results( $query, ARRAY_A ) as $row )
	{
		// remember the ID for that locale
		$languagestoconvert[$row[$columnlang]][] = $row[$columnID];
	}
	foreach( $languagestoconvert as $lkey => $lIDs)
	{
		// converting the languages we've found
		$converted = false;
		echo '&nbsp; Converting lang \''. $lkey. '\' '; // (with IDs: '. implode( ', ', $lIDs ). ').. ';

		if( preg_match('/[a-z]{2}-[A-Z]{2}(-.{1,14})?/', $lkey) )
		{
			// Already valid
			echo 'nothing to update, already valid!<br />';
			continue;
		}
		if( ( strlen( $lkey ) == 2 ) && ( substr( $default_locale, 0, 2 ) != $lkey ) )
		{
			// we have an old two letter lang code to convert and it doesn't match the default locale
			foreach( $locales as $newlkey => $v )
			{
				// loop given locales
				if( substr( $newlkey, 0, 2 ) == strtolower( $lkey ) ) # TODO: check if valid/suitable
				{
					// if language matches, update
					$converted = $DB->query( "UPDATE $table SET $columnlang = '$newlkey' WHERE $columnlang = '$lkey'" );
					echo 'to locale \''. $newlkey. '\'<br />';
					break;
				}
			}
		}
		if( ! $converted )
		{
			// we have nothing converted yet, setting default:
			$DB->query( "UPDATE $table SET $columnlang = '$default_locale' WHERE $columnlang = '$lkey'" );
			echo 'forced to default locale \''. $default_locale. '\'<br />';
		}
	}
	echo "\n";
}


/**
 * ALTER TABLE - ADD A COLUMN
 * @param string Name of the table involved
 * @param string Name of the column you are adding
 * @param string Name of the column the new column follows
 * @param string Description of the new column
 * @return boolean True if the column has been added, False if not.
 */
function db_add_col( $table, $col_name, $add_after, $col_desc )
{
	global $DB;

	if( $DB->query( 'ALTER TABLE '.$table.' ADD COLUMN '.$col_name.' '.$col_desc ) === false )
	{
		return false;
	}
	return true;
}


/**
 * ALTER TABLE - CHANGE COLUMN NAME
 * @param string Name of the table involved
 * @param string Name of the column you are change
 * @param string New name of the column
 * @param string New (or existing!) description of the column
 * @return boolean True if the column has been added, False if not.
 */
function db_change_col( $table, $col_name, $new_name, $col_desc )
{
	global $DB;

	if( $DB->query( 'ALTER TABLE '.$table.' CHANGE '.$col_name.' '.$new_name.' '.$col_desc ) === false )
	{
		return false;
	}
	return true;
}


/**
 * ALTER TABLE - MODIFY COLUMN DEFINITION
 * @param string Name of the table involved
 * @param string Name of the column you are modifying
 * @param string New description of the column
 * @return boolean True if the column has been modified, False if not.
 */
function db_mod_col( $table, $col_name, $col_desc )
{
	global $DB;

	if( $DB->query( 'ALTER TABLE '.$table.' MODIFY COLUMN '.$col_name.' '.$col_desc ) === false )
	{
		return false;
	}
	return true;
}


/**
 * ALTER TABLE - ADD INDEX
 * @param string Name of the table involved
 * @param string Name of your new index
 * @param string Definition of your new index
 * @return boolean True if the index has been added, false if not.
 */
function db_add_index( $table, $name, $def )
{
	global $DB;

	if( $DB->query( 'ALTER TABLE '.$table.' ADD INDEX '.$name.' ('.$def.')' ) === false )
	{
		return false;
	}
	return true;
}


/**
 * ALTER TABLE - DROP INDEX
 * @param string Name of the table involved
 * @param string Name of the index you are dropping
 * @return boolean True if the index has been dropped, false if not.
 */
function db_drop_index( $table, $name )
{
	global $DB;

	if( $DB->query( 'ALTER TABLE '.$table.' DROP INDEX '.$name ) === false )
	{
		return false;
	}
	return true;
}


/**
 * @param string Table name
 * @param array Column name
 * @return boolean Does a given column name exist in DB?
 */
function db_cols_exist( $table, $col_name )
{
	global $DB;

	foreach( $DB->get_results('SHOW COLUMNS FROM '.$table) as $row )
	{
		if( $row->Field === $col_name )
		{
			return true;
		}
	}

	return false;
}


/**
 * Drops a column, if it exists.
 */
function db_drop_col( $table, $col_name )
{
	global $DB;

	if( ! db_cols_exist( $table, $col_name ) )
	{
		return false;
	}
	if( $DB->query( 'ALTER TABLE '.$table.' DROP COLUMN '.$col_name ) === false )
	{
		return false;
	}
	return true;
}


/**
 * @return boolean Does a given index key name exist in DB?
 */
function db_index_exists( $table, $index_name )
{
	global $DB;

	$index_name = strtolower( $index_name );
	foreach( $DB->get_results('SHOW INDEX FROM '.$table) as $row )
	{
		if( strtolower( $row->Key_name ) == $index_name )
		{
			return true;
		}
	}
	return false;
}


/**
 * Create a DB version checkpoint
 *
 * This increments table.db_upgrade during an upgrade cycle. It starts matching
 * table.db_version and end matching $app_db_version. If the upgrader crashes
 * this value lets us pick up exactly where we left off :)
 * @param string version of DB at checkpoint
 */
function set_upgrade_checkpoint( $version )
{
	global $DB;
	global $locale;
	global $script_start_time;

	$DB->query( "UPDATE T_settings SET set_value = '{$version}' WHERE set_name = 'db_upgrade'" );
	$max_exe_time = ini_get( 'max_execution_time' );
	$elapsed_time = time() - $script_start_time;
	if( $max_exe_time && $elapsed_time > $max_exe_time - 10 )
	{
		// Max exe time not disabled and we're reaching the end
		echo 'We are reaching the time limit for this script. Please click <a href="index.php?locale='.$locale.'&amp;action=upgradedb">continue</a>...';
		// Dirty temporary solution
		exit(0);
	}
	echo $version.' ... ';
	task_end();
}


/**
 * Upgrade database tables
 *
 * This is a process. Life is a process. Therefore this is life.
 */
function upgrade_dbase_tables()
{
	global $app_db_config;
	global $app_db_tableprefix;
	global $app_db_version;
	global $app_name;
	global $DB;
	global $db_storage_charset;
	global $old_db_version;
	global $schema_queries;

	// Load DB schema from modules
	load_db_schema();
	load_funcs( '_core/model/db/_upgrade.funcs.php' );

	// BEGIN installation status checks...
	echo '<p>'.T_('Checking DB schema version...').' ';

	// are we not installed?
	$old_db_version = get_db_version( 'version' );
	if( $old_db_version === NULL )
	{
		printf( T_( '<strong>OOPS! %s doesn\'t seem to be installed yet.</strong></p>' ), $app_name );
		return;
	}

	// are we too old to rock and roll or too young to die?
	echo $old_db_version, ' : ';
	if( $old_db_version < 0 )
	{
		debug_die( T_('This version is too old!') );
	}
	if( $old_db_version > $app_db_version )
	{
		debug_die( T_('This version is too recent! We cannot downgrade to the version you are trying to install...') );
	}

	// did we crash while upgrading?
	$cur_db_version = get_db_version( 'upgrade' );
	if( $cur_db_version === NULL )
	{
		// this is not a crash: first upgrade from Quam Plures v0.0.0
		$cur_db_version = $old_db_version;
		$DB->query( "INSERT INTO T_settings (set_name, set_value) VALUES ('db_upgrade', '{$cur_db_version}')" );
	}
	elseif( $cur_db_version != $old_db_version )
	{
		// this is a crash :(
		echo '<br /><strong>'.T_('It looks like we crashed while upgrading.').'</strong> '.T_('We will try to pick up where we left off').' ';
		echo sprintf( T_('You had version %s and crashed at %s on the way to %s.'), $old_db_version, $cur_db_version, $app_db_version ).' ... ';
		if( $cur_db_version === '0' )
		{
			// dammit! I can't for the life of me figure out how it is storing a zero :(
			debug_die( T_('SERIOUS crash problem: $cur_db_version is ZERO.') );
		}
	}

	echo "OK.<br />\n";

	// Try to obtain some serious time to do some serious processing (5 minutes and 57 seconds)
	@set_time_limit( 357 );

	// ---------------------------------------------------------------------------
	// BEGIN UPGRADES FROM V0.0.0, $app_db_version = 0
	// ---------------------------------------------------------------------------

	if( $cur_db_version < 1 )
	{
		$cur_db_version++;
		echo 'Modifying template_type in the templates__template table ... ';
		if( db_mod_col( $app_db_tableprefix."templates__template", "template_type", "ENUM('xhtml', 'normal', 'feed', 'other') NOT NULL DEFAULT 'xhtml'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 2 )
	{
		$cur_db_version++;
		echo 'Updating template_type in the templates__template table (1 of 2) ... ';
		if( $DB->query( "UPDATE ".$app_db_tableprefix."templates__template SET template_type = REPLACE( template_type, 'normal', 'xhtml' )" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 3 )
	{
		$cur_db_version++;
		echo 'Updating template_type in the templates__template table (2 of 2) ... ';
		if( $DB->query( "UPDATE ".$app_db_tableprefix."templates__template SET template_type = 'other' WHERE template_name = 'Sitemap'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 4 )
	{
		$cur_db_version++;
		echo 'Modifying template_type in the templates__template table ... ';
		if( db_mod_col( $app_db_tableprefix."templates__template", "template_type", "ENUM('xhtml', 'feed', 'other') NOT NULL DEFAULT 'xhtml'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 5 )
	{
		$cur_db_version++;
		echo 'Modifying aspm_source in the antispam table ... ';
		if( db_mod_col( $app_db_tableprefix."antispam", "aspm_source", "ENUM('core','comment','entered','referer','shared','local','reported','central') NOT NULL DEFAULT 'core'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 6 )
	{
		$cur_db_version++;
		echo 'Updating aspm_source in the antispam table (1 of 3) ... ';
		if( $DB->query( "UPDATE ".$app_db_tableprefix."antispam SET aspm_source = REPLACE( aspm_source, 'local', 'core' )" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 7 )
	{
		$cur_db_version++;
		echo 'Updating aspm_source in the antispam table (2 of 3) ... ';
		if( $DB->query( "UPDATE ".$app_db_tableprefix."antispam SET aspm_source = REPLACE( aspm_source, 'reported', 'core' )" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 8 )
	{
		$cur_db_version++;
		echo 'Updating aspm_source in the antispam table (3 of 3) ... ';
		if( $DB->query( "UPDATE ".$app_db_tableprefix."antispam SET aspm_source = REPLACE( aspm_source, 'central', 'core' )" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 9 )
	{
		$cur_db_version++;
		echo 'Modifying aspm_source in the antispam table ... ';
		if( db_mod_col( $app_db_tableprefix."antispam", "aspm_source", "ENUM('core','comment','entered','referer','shared') NOT NULL DEFAULT 'core'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 10 )
	{
		$cur_db_version++;
		echo 'Adding aspm_shortened to the antispam table ... ';
		if( db_add_col( $app_db_tableprefix."antispam", "aspm_shortened", "aspm_source", "TINYINT(1) NOT NULL default 0" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 11 )
	{
		$cur_db_version++;
		echo 'Adding aspm_added_on to the antispam table ... ';
		if( db_add_col( $app_db_tableprefix."antispam", "aspm_added_on", "aspm_shortened", "DATETIME NOT NULL DEFAULT '2011-12-20 00:00:00'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 12 )
	{
		$cur_db_version++;
		echo 'Adding aspm_lastseen to the antispam table ... ';
		if( db_add_col( $app_db_tableprefix."antispam", "aspm_lastseen", "aspm_added_on", "DATETIME NOT NULL DEFAULT '2011-12-20 00:00:00'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 13 )
	{
		$cur_db_version++;
		echo 'Adding aspm_comments to the antispam table ... ';
		if( db_add_col( $app_db_tableprefix."antispam", "aspm_comments", "aspm_lastseen", "int(11) unsigned NOT NULL default '0'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 14 )
	{
		$cur_db_version++;
		echo 'Adding aspm_messages to the antispam table ... ';
		if( db_add_col( $app_db_tableprefix."antispam", "aspm_messages", "aspm_comments", "int(11) unsigned NOT NULL default '0'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 15 )
	{
		$cur_db_version++;
		echo 'Adding aspm_referers to the antispam table ... ';
		if( db_add_col( $app_db_tableprefix."antispam", "aspm_referers", "aspm_messages", "int(11) unsigned NOT NULL default '0'" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 16 )
	{
		$cur_db_version++;
		echo 'Adding user_avatar_ID to the users table ... ';
		if( db_add_col( $app_db_tableprefix."users", "user_avatar_ID", "user_validated", "int(10) NULL" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

	if( $cur_db_version < 17 )
	{
		$cur_db_version++;
		echo 'Adding user_biography to the users table ... ';
		if( db_add_col( $app_db_tableprefix."users", "user_biography", "user_avatar_ID", "TEXT NULL" ) !== false )
		{
			set_upgrade_checkpoint( $cur_db_version );
		}
		else debug_die( T_('Upgrader crashed doing step# ').$cur_db_version );
	}

// -----------------------------------------------------------------------------------------
// Remember changing the DB schema, too! Check out these files:
// - qp_inc/_core/model/__core.install.php
// - qp_inc/sessions/model/_sessions.install.php
// -----------------------------------------------------------------------------------------
// Remember to update $app_db_version in _application.php
// -----------------------------------------------------------------------------------------

	// get our last upgrade version number
	$end_of_upgrade = get_db_version( 'upgrade' );
	// double-check our upgrade against our target
	if( $end_of_upgrade != $app_db_version )
	{
		// This is a serious problem that should never reach production!
		// It means that our branch-work wasn't done well, so fix it :)
		debug_die( 'The end of the upgrade process did not get us where we needed to be! '.$end_of_upgrade.' vs '.$app_db_version );
	}
	// set our db_version to match upgrade number (and therefore target)
	$DB->query( "UPDATE T_settings SET set_value = '$end_of_upgrade' WHERE set_name = 'db_version'" );

	// Check to make sure the DB schema is up to date:
	$upgrade_db_deltas = array(); // This holds changes to make, if any (just all queries)
	foreach( $schema_queries as $table => $query_info )
	{
		// For each table in the schema, check diffs...
		foreach( db_delta( $query_info[1], array('drop_column', 'drop_index') ) as $table => $queries )
		{
			foreach( $queries as $qinfo )
			{
				foreach( $qinfo['queries'] as $query )
				{
					// subqueries for this query (usually one, but may include required other queries)
					$upgrade_db_deltas[] = $query;
				}
			}
		}
	}

	if( empty( $upgrade_db_deltas ) )
	{
		echo '<p>'.T_('The database schema is up to date.').'</p>';
	}
	else
	{
		// delta queries have to be confirmed or executed now..
		$confirmed_db_upgrade = param('confirmed', 'integer', 0); // force confirmation
		$upgrade_db_deltas_confirm_md5 = param( 'upgrade_db_deltas_confirm_md5', 'string', '' );

		if( ! $confirmed_db_upgrade )
		{
			if( ! empty( $upgrade_db_deltas_confirm_md5 ) )
			{
				// received confirmation from form
				if( $upgrade_db_deltas_confirm_md5 != md5( implode('', $upgrade_db_deltas) ) )
				{
					// unlikely to happen
					echo '<p class="error">'
						.T_('The DB schema has been changed since confirmation.')
						.'</p>';
				}
				else
				{
					$confirmed_db_upgrade = true;
				}
			}
		}

		if( ! $confirmed_db_upgrade )
		{
			global $action, $locale;
			$Form = new Form( NULL, '', 'post' );
			$Form->begin_form( 'fform', T_('Upgrade database') );
			$Form->begin_fieldset();
			$Form->hidden( 'upgrade_db_deltas_confirm_md5', md5(implode( '', $upgrade_db_deltas )) );
			$Form->hidden( 'action', $action );
			$Form->hidden( 'locale', $locale );
			echo '<p>'.T_('The version number is correct, but we have detected changes in the database schema. This can happen with <abbr title="Version Control System">VCS</abbr> versions...').'</p>';
			echo '<p>'.T_('The following database changes will be carried out. If you are not sure what this means, it will probably be alright.').'</p>';
			echo '<ul>';
			foreach( $upgrade_db_deltas as $l_delta )
			{
				#echo '<li><code>'.nl2br( $l_delta ).'</code></li>';
				echo '<li><pre>'.str_replace( "\t", '  ', $l_delta ).'</pre></li>';
			}
			echo '</ul>';
			$Form->submit( array( '', T_('Upgrade database!'), 'ActionButton' ) );
			$Form->end_form();

			return false;
		}

		// Alter DB to match DB schema:
		install_make_db_schema_current( true );
	}

	return true;
}

?>