- This is well tested and should work, it is not pretty but it does finally bring parity across versions. Tested is RC1->RC2->RC3->RC4-dev, RC2->RC3->RC4-dev and RC3->RC4-dev

git-svn-id: file:///svn/phpbb/trunk@7870 89ea8834-ac86-4346-8a33-228a782c2dd0
author: David M <davidmj@users.sourceforge.net> 2007-07-11 22:25:53 +0000
committer: David M <davidmj@users.sourceforge.net> 2007-07-11 22:25:53 +0000
commit: 2ca512113323d9eb73a5c168b375d1081c12c8cf (patch)
tree: 76f3354484a224067643bed26695666b4748ecff /phpBB/install
parent: 932a164d78afc394d6febb2a68cd534101fc328e (diff)
download: forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar
forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar.gz
forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar.bz2
forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar.xz
forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.zip
1 files changed, 216 insertions, 14 deletions
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index 75924ed10f..6346f6bccf 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -342,6 +342,16 @@ $database_update_info = array(
 			),
 		),
 	),
+	// Changes from 3.0.RC3 to the next version
+	'3.0.RC3'			=> array(
+		// Remove the following keys
+		'change_columns'		=> array(
+			BANLIST_TABLE	=> array(
+				'ban_reason'		=> array('VCHAR_UNI', ''),
+				'ban_give_reason'	=> array('VCHAR_UNI', ''),
+			),
+		),
+	),
 );
 
 // Determine mapping database type
@@ -681,6 +691,76 @@ if (version_compare($current_version, '3.0.RC3', '<='))
 
 		$no_updates = false;
 	}
+
+	// we check for:
+	// ath_opt_id
+	// ath_op_id
+	// ACL_ROLES_DATA_TABLE_ath_opt_id
+	// we want ACL_ROLES_DATA_TABLE_ath_op_id
+
+	$table_index_fix = array(
+		ACL_ROLES_DATA_TABLE => array(
+			'ath_opt_id'							=> 'ath_op_id',
+			'ath_op_id'								=> 'ath_op_id',
+			ACL_ROLES_DATA_TABLE . '_ath_opt_id'	=> 'ath_op_id'
+		),
+		STYLES_IMAGESET_DATA_TABLE => array(
+			'i_id'									=> 'i_d',
+			'i_d'									=> 'i_d',
+			STYLES_IMAGESET_DATA_TABLE . '_i_id'	=> 'i_d'
+		)
+	);
+
+	// we need to create some indicies...
+	$needed_creation = array();
+
+	foreach ($table_index_fix as $table_name => $index_info)
+	{
+		$index_list = sql_list_fake($map_dbms, $table_name);
+		foreach ($index_info as $bad_index => $good_index)
+		{
+			if (in_array($bad_index, $index_list))
+			{
+				// mysql is actually OK, it won't get a hand in this crud
+				switch ($map_dbms)
+				{
+					// last version, mssql had issues with index removal
+					case 'mssql':
+						$sql = 'DROP INDEX ' . $table_name . '\.' . $bad_index;
+						_sql($sql, $errored, $error_ary);
+
+						$no_updates = false;
+					break;
+
+					// last version, firebird, oracle, postgresql and sqlite all got bad index names
+					// we got kinda lucky, tho: they all support the same syntax
+					case 'firebird':
+					case 'oracle':
+					case 'postgres':
+					case 'sqlite':
+						$sql = 'DROP INDEX ' . $bad_index;
+						_sql($sql, $errored, $error_ary);
+
+						$no_updates = false;
+					break;
+				}
+
+				$needed_creation[$table_name][$good_index] = 1;
+			}
+		}
+	}
+
+	$new_index_defs = array('ath_op_id' => array('auth_option_id'), 'i_d' => array('imageset_id'));
+
+	foreach ($needed_creation as $bad_table => $index_repair_list)
+	{
+		foreach ($index_repair_list as $new_index => $garbage)
+		{
+			sql_create_index($map_dbms, $new_index, $bad_table, $new_index_defs[$new_index]);
+			$no_updates = false;
+		}
+	}
+
 }
 
 _write_result($no_updates, $errored, $error_ary);
@@ -941,9 +1021,9 @@ function column_exists($dbms, $table, $column_name)
 		break;
 
 		case 'firebird':
-			$sql = "SELECT RDB$FIELD_NAME as FNAME
-				FROM RDB$RELATION_FIELDS
-				WHERE RDB$RELATION_NAME = '{$table}'";
+			$sql = "SELECT RDB\$FIELD_NAME as FNAME
+				FROM RDB\$RELATION_FIELDS
+				WHERE RDB\$RELATION_NAME = '{$table}'";
 			$result = $db->sql_query($sql);
 			while ($row = $db->sql_fetchrow($result))
 			{
@@ -1384,7 +1464,7 @@ function sql_index_drop($dbms, $index_name, $table_name)
 	switch ($dbms)
 	{
 		case 'mssql':
-			$sql = 'DROP INDEX ' . $table_name . '\.' . $index_name . ' ON ' . $table_name;
+			$sql = 'DROP INDEX ' . $table_name . '\.' . $index_name;
 			_sql($sql, $errored, $error_ary);
 		break;
 
@@ -1496,10 +1576,14 @@ function sql_create_unique_index($dbms, $index_name, $table_name, $column)
 	{
 		case 'firebird':
 		case 'postgres':
-		case 'mysql_40':
-		case 'mysql_41':
 		case 'oracle':
 		case 'sqlite':
+			$sql = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
+			_sql($sql, $errored, $error_ary);
+		break;
+
+		case 'mysql_40':
+		case 'mysql_41':
 			$sql = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 			_sql($sql, $errored, $error_ary);
 		break;
@@ -1520,10 +1604,14 @@ function sql_create_index($dbms, $index_name, $table_name, $column)
 	{
 		case 'firebird':
 		case 'postgres':
-		case 'mysql_40':
-		case 'mysql_41':
 		case 'oracle':
 		case 'sqlite':
+			$sql = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
+			_sql($sql, $errored, $error_ary);
+		break;
+
+		case 'mysql_40':
+		case 'mysql_41':
 			$sql = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
 			_sql($sql, $errored, $error_ary);
 		break;
@@ -1564,11 +1652,11 @@ function sql_list_index($dbms, $table_name)
 		switch ($dbms)
 		{
 			case 'firebird':
-				$sql = "SELECT LOWER(RDB$INDEX_NAME) as index_name
-					FROM RDB$INDICES
-					WHERE RDB$RELATION_NAME = " . strtoupper($table_name) . "
-						AND RDB$UNIQUE_FLAG IS NULL
-						AND RDB$FOREIGN_KEY IS NULL";
+				$sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
+					FROM RDB\$INDICES
+					WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
+						AND RDB\$UNIQUE_FLAG IS NULL
+						AND RDB\$FOREIGN_KEY IS NULL";
 				$col = 'index_name';
 			break;
 
@@ -1629,6 +1717,90 @@ function sql_list_index($dbms, $table_name)
 	return array_map('strtolower', $index_array);
 }
 
+// This is totally fake, never use it
+// it exists only to mend bad update functions introduced
+// * UNIQUE indices
+// * PRIMARY keys
+function sql_list_fake($dbms, $table_name)
+{
+	global $dbms_type_map, $db;
+	global $errored, $error_ary;
+
+	$index_array = array();
+
+	if ($dbms == 'mssql')
+	{
+		$sql = "EXEC sp_statistics '$table_name'";
+		$result = $db->sql_query($sql);
+		while ($row = $db->sql_fetchrow($result))
+		{
+			if ($row['TYPE'] == 3)
+			{
+				$index_array[] = $row['INDEX_NAME'];
+			}
+		}
+		$db->sql_freeresult($result);
+	}
+	else
+	{
+		switch ($dbms)
+		{
+			case 'firebird':
+				$sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
+					FROM RDB\$INDICES
+					WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
+						AND RDB\$UNIQUE_FLAG IS NULL
+						AND RDB\$FOREIGN_KEY IS NULL";
+				$col = 'index_name';
+			break;
+
+			case 'postgres':
+				$sql = "SELECT ic.relname as index_name
+					FROM pg_class bc, pg_class ic, pg_index i
+					WHERE (bc.oid = i.indrelid)
+						AND (ic.oid = i.indexrelid)
+						AND (bc.relname = '" . $table_name . "')
+						AND (i.indisunique != 't')
+						AND (i.indisprimary != 't')";
+				$col = 'index_name';
+			break;
+
+			case 'mysql_40':
+			case 'mysql_41':
+				$sql = 'SHOW KEYS
+					FROM ' . $table_name;
+				$col = 'Key_name';
+			break;
+
+			case 'oracle':
+				$sql = "SELECT index_name
+					FROM user_indexes
+					WHERE table_name = '" . $table_name . "'
+						AND generated = 'N'";
+			break;
+
+			case 'sqlite':
+				$sql = "PRAGMA index_info('" . $table_name . "');";
+				$col = 'name';
+			break;
+		}
+
+		$result = $db->sql_query($sql);
+		while ($row = $db->sql_fetchrow($result))
+		{
+			if (($dbms == 'mysql_40' || $dbms == 'mysql_41') && !$row['Non_unique'])
+			{
+				continue;
+			}
+
+			$index_array[] = $row[$col];
+		}
+		$db->sql_freeresult($result);
+	}
+
+	return array_map('strtolower', $index_array);
+}
+
 /**
 * Change column type (not name!)
 */
@@ -1683,9 +1855,39 @@ function sql_column_change($dbms, $table_name, $column_name, $column_data)
 				$sql_array[] = 'ALTER COLUMN ' . $column_name . " SET DEFAULT '" . $column_data['default'] . "'";
 			}
 
+			// we don't want to double up on constraints if we change different number data types
 			if (isset($column_data['constraint']))
 			{
-				$sql_array[] = 'ALTER COLUMN ' . $column_name . " ADD '" . $column_data['constraint'] . "'";
+				$constraint_sql = "SELECT consrc as constraint_data
+							FROM pg_constraint, pg_class bc
+							WHERE conrelid = bc.oid
+								AND bc.relname = '{$table_name}'
+								AND NOT EXISTS (
+									SELECT *
+										FROM pg_constraint as c, pg_inherits as i
+										WHERE i.inhrelid = pg_constraint.conrelid
+											AND c.conname = pg_constraint.conname
+											AND c.consrc = pg_constraint.consrc
+											AND c.conrelid = i.inhparent
+								)";
+
+				$constraint_exists = false;
+
+				$result = $db->sql_query($constraint_sql);
+				while ($row = $db->sql_fetchrow($result))
+				{
+					if (trim($row['constraint_data']) == trim($column_data['constraint']))
+					{
+						$constraint_exists = true;
+						break;
+					}
+				}
+				$db->sql_freeresult($result);
+
+				if (!$constraint_exists)
+				{
+					$sql_array[] = "ADD '" . $column_data['constraint'] . "'";
+				}
 			}
 
 			$sql .= implode(', ', $sql_array);
author	David M <davidmj@users.sourceforge.net>	2007-07-11 22:25:53 +0000
committer	David M <davidmj@users.sourceforge.net>	2007-07-11 22:25:53 +0000
commit	2ca512113323d9eb73a5c168b375d1081c12c8cf (patch)
tree	76f3354484a224067643bed26695666b4748ecff /phpBB/install
parent	932a164d78afc394d6febb2a68cd534101fc328e (diff)
download	forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar.gz forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar.bz2 forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.tar.xz forums-2ca512113323d9eb73a5c168b375d1081c12c8cf.zip