diff options
| author | Meik Sievertsen <acydburn@phpbb.com> | 2006-09-02 13:53:50 +0000 |
|---|---|---|
| committer | Meik Sievertsen <acydburn@phpbb.com> | 2006-09-02 13:53:50 +0000 |
| commit | 609744041be8d00ab705b1cc4eec24fe717e15d9 (patch) | |
| tree | 23ce162570b455a19f67fa1fde589c9a73100710 /phpBB | |
| parent | f3b545bb593b61611883084dc91d279b93d8e21c (diff) | |
| download | forums-609744041be8d00ab705b1cc4eec24fe717e15d9.tar forums-609744041be8d00ab705b1cc4eec24fe717e15d9.tar.gz forums-609744041be8d00ab705b1cc4eec24fe717e15d9.tar.bz2 forums-609744041be8d00ab705b1cc4eec24fe717e15d9.tar.xz forums-609744041be8d00ab705b1cc4eec24fe717e15d9.zip | |
our new update_to_latest.php file replacement. ;)
Please note that even if the file is now in CVS it does not mean we provide an update path from Beta2 to any other version.
git-svn-id: file:///svn/phpbb/trunk@6348 89ea8834-ac86-4346-8a33-228a782c2dd0
Diffstat (limited to 'phpBB')
| -rw-r--r-- | phpBB/install/database_update.php | 752 |
1 files changed, 752 insertions, 0 deletions
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php new file mode 100644 index 0000000000..29272964f9 --- /dev/null +++ b/phpBB/install/database_update.php @@ -0,0 +1,752 @@ +<?php +/** +* +* @package install +* @version $Id$ +* @copyright (c) 2006 phpBB Group +* @license http://opensource.org/licenses/gpl-license.php GNU Public License +* +*/ + +$updates_to_version = '3.0.B2'; + +if (defined('IN_PHPBB') && defined('IN_INSTALL')) +{ + return; +} + +/** +*/ +define('IN_PHPBB', true); +define('IN_INSTALL', true); + +$phpbb_root_path = './../'; +$phpEx = substr(strrchr(__FILE__, '.'), 1); + +// Report all errors, except notices +//error_reporting(E_ALL ^ E_NOTICE); +error_reporting(E_ALL); + +@set_time_limit(120); + +// Include essential scripts +include($phpbb_root_path . 'config.' . $phpEx); + +if (!isset($dbms)) +{ + die("Please read: <a href='../docs/INSTALL.html'>INSTALL.html</a> before attempting to update."); +} + +// Load Extensions +if (!empty($load_extensions)) +{ + $load_extensions = explode(',', $load_extensions); + + foreach ($load_extensions as $extension) + { + @dl(trim($extension)); + } +} + +// Include files +require($phpbb_root_path . 'includes/acm/acm_' . $acm_type . '.' . $phpEx); +require($phpbb_root_path . 'includes/cache.' . $phpEx); +require($phpbb_root_path . 'includes/template.' . $phpEx); +require($phpbb_root_path . 'includes/session.' . $phpEx); +require($phpbb_root_path . 'includes/auth.' . $phpEx); +require($phpbb_root_path . 'includes/functions.' . $phpEx); +require($phpbb_root_path . 'includes/functions_admin.' . $phpEx); +require($phpbb_root_path . 'includes/constants.' . $phpEx); +require($phpbb_root_path . 'includes/db/' . $dbms . '.' . $phpEx); + +$cache = new cache(); +$db = new $sql_db(); + +// Connect to DB +$db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false); + +// We do not need this any longer, unset for safety purposes +unset($dbpasswd); + +$sql = "SELECT config_value + FROM " . CONFIG_TABLE . " + WHERE config_name = 'default_lang'"; +$result = $db->sql_query($sql); +$row = $db->sql_fetchrow($result); +$db->sql_freeresult($result); + +// And finally, load the relevant language files +include($phpbb_root_path . 'language/' . $row['config_value'] . '/common.' . $phpEx); +include($phpbb_root_path . 'language/' . $row['config_value'] . '/acp/common.' . $phpEx); +include($phpbb_root_path . 'language/' . $row['config_value'] . '/install.' . $phpEx); + +// Set PHP error handler to ours +//set_error_handler('msg_handler'); + +// Define some variables for the database update + +// Database column types mapping +$dbms_type_map = array( + 'mysql' => array('INT:' => 'int(%d)', 'BINT' => 'bigint(20)', 'UINT' => 'mediumint(8) UNSIGNED', 'UINT:' => 'int(%d) UNSIGNED', 'TINT:' => 'tinyint(%d)', 'USINT' => 'smallint(4) UNSIGNED', 'BOOL' => 'tinyint(1) UNSIGNED', 'VCHAR' => 'varchar(255)', 'VCHAR:' => 'varchar(%d)', 'CHAR:' => 'char(%d)', 'XSTEXT' => 'text', 'STEXT' => 'text', 'TEXT' => 'text', 'MTEXT' => 'mediumtext', 'TIMESTAMP' => 'int(11) UNSIGNED', 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252) /*!40101 CHARACTER SET utf8 */ BINARY', 'VCHAR_CI' => 'varchar(252)', 'VARBINARY' => 'varbinary(255)'), + 'firebird' => array('INT:' => 'INTEGER', 'BINT' => 'DOUBLE PRECISION', 'UINT' => 'INTEGER', 'UINT:' => 'INTEGER', 'TINT:' => 'INTEGER', 'USINT' => 'INTEGER', 'BOOL' => 'INTEGER', 'VCHAR' => 'VARCHAR(255)', 'VCHAR:' => 'VARCHAR(%d)', 'CHAR:' => 'CHAR(%d)', 'XSTEXT' => 'BLOB SUB_TYPE TEXT', 'STEXT' => 'BLOB SUB_TYPE TEXT','TEXT' => 'BLOB SUB_TYPE TEXT', 'MTEXT' => 'BLOB SUB_TYPE TEXT', 'TIMESTAMP' => 'INTEGER', 'DECIMAL' => 'DOUBLE PRECISION','VCHAR_BIN' => 'VARCHAR(84) CHARACTER SET UNICODE_FSS', 'VCHAR_CI' => 'VARCHAR(252)', 'VARBINARY' => 'CHAR(255)'), + 'mssql' => array('INT:' => '[int]', 'BINT' => '[float]', 'UINT' => '[int]', 'UINT:' => '[int]', 'TINT:' => '[int]', 'USINT' => '[int]', 'BOOL' => '[int]', 'VCHAR' => '[varchar] (255)', 'VCHAR:' => '[varchar] (%d)', 'CHAR:' => '[char] (%d)', 'XSTEXT' => '[varchar] (1000)', 'STEXT' => '[varchar] (3000)', 'TEXT' => '[varchar] (8000)', 'MTEXT' => '[text]', 'TIMESTAMP' => '[int]', 'DECIMAL' => '[float]', 'VCHAR_BIN' => '[nvarchar] (252)', 'VCHAR_CI' => '[varchar] (252)','VARBINARY' => '[varbinary] (255)'), + 'oracle' => array('INT:' => 'number(%d)','BINT' => 'number(20)', 'UINT' => 'number(8)', 'UINT:' => 'number(%d)', 'TINT:' => 'number(%d)', 'USINT' => 'number(4)', 'BOOL' => 'number(1)', 'VCHAR' => 'varchar2(255)', 'VCHAR:' => 'varchar2(%d)', 'CHAR:' => 'char(%d)', 'XSTEXT' => 'varchar2(1000)', 'STEXT' => 'varchar2(3000)', 'TEXT' => 'clob', 'MTEXT' => 'clob', 'TIMESTAMP' => 'number(11)', 'DECIMAL' => 'number(5, 2)', 'VCHAR_BIN' => 'nvarchar2(252)', 'VCHAR_CI' => 'varchar2(252)', 'VARBINARY' => 'raw(255)'), + 'sqlite' => array('INT:' => 'int(%d)', 'BINT' => 'bigint(20)', 'UINT' => 'INTEGER UNSIGNED', 'UINT:' => 'INTEGER UNSIGNED', 'TINT:' => 'tinyint(%d)', 'USINT' => 'INTEGER UNSIGNED', 'BOOL' => 'INTEGER UNSIGNED', 'VCHAR' => 'varchar(255)', 'VCHAR:' => 'varchar(%d)', 'CHAR:' => 'char(%d)', 'XSTEXT' => 'text(65535)', 'STEXT' => 'text(65535)', 'TEXT' => 'text(65535)', 'MTEXT' => 'mediumtext(16777215)', 'TIMESTAMP' => 'INTEGER UNSIGNED', 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'nvarchar(252)', 'VCHAR_CI' => 'varchar(252)', 'VARBINARY' => 'blob'), + 'postgres' => array('INT:' => 'INT4', 'BINT' => 'INT8', 'UINT' => 'INT4', 'UINT:' => 'INT4', 'TINT:' => 'INT2', 'USINT' => 'INT2', 'BOOL' => 'INT2', 'VCHAR' => 'varchar(255)', 'VCHAR:' => 'varchar(%d)', 'CHAR:' => 'char(%d)', 'XSTEXT' => 'varchar(1000)', 'STEXT' => 'varchar(3000)', 'TEXT' => 'varchar(8000)', 'MTEXT' => 'TEXT', 'TIMESTAMP' => 'INT4', 'DECIMAL' => 'decimal(5,2)', 'VCHAR_BIN' => 'varchar(252)', 'VCHAR_CI' => 'varchar_ci', 'VARBINARY' => 'bytea'), +); + +// A list of types being unsigned for better reference in some db's +$unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP'); + +// Only an example, but also commented out +$database_update_info = array( + // Changes within this version + '3.0.b3' => array( +/* + // Change the following columns... + 'change_columns' => array( + USERS_TABLE => array( + 'user_avatar_width' => array('USINT', 0), + 'user_avatar_height' => array('USINT', 0), + ), + ), + // Add the following columns + 'add_columns' => array( + TOPICS_TABLE => array( + 'topic_first_poster_colour' => array('VCHAR:6', ''), + 'topic_last_poster_colour' => array('VCHAR:6', ''), + ), + FORUMS_TABLE => array( + 'forum_last_poster_colour' => array('VCHAR:6', ''), + ), + ), +*/ + ), + // Latest version + '3.0.0' => array(), +); + +// Determine mapping database type +$map_dbms = SQL_LAYER; + +switch (SQL_LAYER) +{ + case 'mysql': + case 'mysql4': + case 'mysqli': + $map_dbms = 'mysql'; + break; + + case 'mssql': + case 'mssql_odbc': + $map_dbms = 'mssql'; + break; +} + +$error_ary = array(); +$errored = false; + +?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> +<html xmlns="http://www.w3.org/1999/xhtml" dir="<?php echo $lang['DIRECTION']; ?>"> +<head> + +<meta http-equiv="Content-Type" content="text/html; charset=<?php echo $lang['DIRECTION']; ?>" /> +<meta http-equiv="Content-Style-Type" content="text/css" /> +<meta http-equiv="imagetoolbar" content="no" /> +<title><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></title> + +<link href="../adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" /> + +</head> + +<body> +<div id="wrap"> + <div id="page-header"> </div> + + <div id="page-body"> + <div class="panel"> + <span class="corners-top"><span></span></span> + <div id="content"> + <div id="main"> + + <h1><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></h1> + + <br /> + + <p><?php echo $lang['DATABASE_TYPE']; ?> :: <strong><?php echo SQL_LAYER; ?></strong><br /> +<?php + +$sql = "SELECT config_value + FROM " . CONFIG_TABLE . " + WHERE config_name = 'version'"; +$result = $db->sql_query($sql); +$row = $db->sql_fetchrow($result); +$db->sql_freeresult($result); + +echo $lang['PREVIOUS_VERSION'] . ' :: <strong>' . $row['config_value'] . '</strong><br />'; +echo $lang['UPDATED_VERSION'] . ' :: <strong>' . $updates_to_version . '</strong>'; + +$current_version = strtolower($row['config_value']); +$latest_version = strtolower($updates_to_version); + +// Schema updates +?> + </p><br /><br /> + + <h1><?php echo $lang['UPDATE_DATABASE_SCHEMA']; ?></h1> + + <br /> + <p><?php echo $lang['PROGRESS']; ?> :: <strong> + +<?php + +flush(); + +// We go through the schema changes from the lowest to the highest version +// We skip those versions older than the current version +$no_updates = true; +foreach ($database_update_info as $version => $schema_changes) +{ + if (version_compare($version, $current_version, '<')) + { + continue; + } + + if (!sizeof($schema_changes)) + { + continue; + } + + $no_updates = false; + + // Change columns? + if (!empty($schema_changes['change_columns'])) + { + foreach ($schema_changes['change_columns'] as $table => $columns) + { + foreach ($columns as $column_name => $column_data) + { + sql_column_change($map_dbms, $table, $column_name, $column_data); + } + } + } + + // Add columns? + if (!empty($schema_changes['add_columns'])) + { + foreach ($schema_changes['add_columns'] as $table => $columns) + { + foreach ($columns as $column_name => $column_data) + { + // Only add the column if it does not exist yet + if (!column_exists($map_dbms, $table, $column_name)) + { + sql_column_add($map_dbms, $table, $column_name, $column_data); + } + } + } + } +} + +_write_result($no_updates, $errored, $error_ary); + +// Data updates +$error_ary = array(); +$errored = $no_updates = false; + +?> + +<br /><br /> +<h1><?php echo $lang['UPDATING_DATA']; ?></h1> +<br /> +<p><?php echo $lang['PROGRESS']; ?> :: <strong> + +<?php + +flush(); + +switch ($current_version) +{ + case '3.0.b3': +/* + $sql = 'SELECT forum_id + FROM ' . FORUMS_TABLE; + $result = _sql($sql, $errored, $error_ary); + + $forum_ids = array(); + while ($row = $db->sql_fetchrow($result)) + { + $forum_ids[] = $row['forum_id']; + } + $db->sql_freeresult($result); + + if (sizeof($forum_ids)) + { + // Since we changed the last post informations we need to sync a bit... + sync('forum', 'forum_id', $forum_ids, true); + + // Sync topics + sync('topic', 'forum_id', $forum_ids, true); + } +*/ + // No need to change here, before no break should appear + break; + + case '3.0.0': + default: + $no_updates = true; + break; +} + +_write_result($no_updates, $errored, $error_ary); + +$error_ary = array(); +$errored = $no_updates = false; + +?> + +<br /><br /> +<h1><?php echo $lang['UPDATE_VERSION_OPTIMIZE']; ?></h1> +<br /> +<p><?php echo $lang['PROGRESS']; ?> :: <strong> + +<?php + +flush(); + + +/* update the version + +$sql = "UPDATE " . CONFIG_TABLE . " + SET config_value = '$updates_to_version' + WHERE config_name = 'version'"; +_sql($sql, $errored, $error_ary); + +// Optimize/vacuum analyze the tables where appropriate +// this should be done for each version in future along with +// the version number update +switch (SQL_LAYER) +{ + case 'mysql': + case 'mysql4': + $sql = 'OPTIMIZE TABLE ' . $table_prefix . 'auth_access, ' . $table_prefix . 'banlist, ' . $table_prefix . 'categories, ' . $table_prefix . 'config, ' . $table_prefix . 'disallow, ' . $table_prefix . 'forum_prune, ' . $table_prefix . 'forums, ' . $table_prefix . 'groups, ' . $table_prefix . 'posts, ' . $table_prefix . 'posts_text, ' . $table_prefix . 'privmsgs, ' . $table_prefix . 'privmsgs_text, ' . $table_prefix . 'ranks, ' . $table_prefix . 'search_results, ' . $table_prefix . 'search_wordlist, ' . $table_prefix . 'search_wordmatch, ' . $table_prefix . 'sessions_keys' . $table_prefix . 'smilies, ' . $table_prefix . 'themes, ' . $table_prefix . 'themes_name, ' . $table_prefix . 'topics, ' . $table_prefix . 'topics_watch, ' . $table_prefix . 'user_group, ' . $table_prefix . 'users, ' . $table_prefix . 'vote_desc, ' . $table_prefix . 'vote_results, ' . $table_prefix . 'vote_voters, ' . $table_prefix . 'words'; + _sql($sql, $errored, $error_ary); + break; + + case 'postgresql': + _sql("VACUUM ANALYZE", $errored, $error_ary); + break; +} +*/ + +_write_result($no_updates, $errored, $error_ary); + +?> + +<br /> +<h1><?php echo $lang['UPDATE_COMPLETED']; ?></h1> + +<br /> + +<p style="color:red"><?php echo $lang['UPDATE_FILES_NOTICE']; ?></p> + +<p><?php echo $lang['COMPLETE_LOGIN_TO_BOARD']; ?></p> + +<?php + +// Now we purge the session table as well as all cache files +$cache->purge(); + +?> + + </div> + </div> + <span class="corners-bottom"><span></span></span> + </div> + </div> + + <div id="page-footer"> + Powered by phpBB © 2006 <a href="http://www.phpbb.com/">phpBB Group</a> + </div> +</div> + +</body> +</html> + +<?php + +/** +* Function for triggering an sql statement +*/ +function _sql($sql, &$errored, &$error_ary, $echo_dot = true) +{ + global $db; + + $db->sql_return_on_error(true); + + $result = $db->sql_query($sql); + + if ($db->sql_error_triggered) + { + $errored = true; + $error_ary['sql'][] = $db->sql_error_sql; + $error_ary['error_code'][] = $db->_sql_error(); + } + + $db->sql_return_on_error(false); + + if ($echo_dot) + { + echo ". \n"; + flush(); + } + + return $result; +} + +function _write_result($no_updates, $errored, $error_ary) +{ + global $lang; + + if ($no_updates) + { + echo ' ' . $lang['NO_UPDATES_REQUIRED'] . '</strong></p>'; + } + else + { + echo ' <span class="success">' . $lang['DONE'] . '</span></strong><br />' . $lang['RESULT'] . ' :: '; + + if ($errored) + { + echo ' <strong>' . $lang['SOME_QUERIES_FAILED'] . '</strong> <ul>'; + + for ($i = 0; $i < sizeof($error_ary['sql']); $i++) + { + echo '<li>' . $lang['ERROR'] . ' :: <strong>' . $error_ary['error_code'][$i]['message'] . '</strong><br />'; + echo $lang['SQL'] . ' :: <strong>' . $error_ary['sql'][$i] . '</strong><br /><br /></li>'; + } + + echo '</ul> <br /><br />' . $lang['SQL_FAILURE_EXPLAIN'] . '</p>'; + } + else + { + echo '<strong>' . $lang['NO_ERRORS'] . '</strong></p>'; + } + } +} + +/** +* Check if a specified column exist +*/ +function column_exists($dbms, $table, $column_name) +{ + global $db; + + $db->sql_return_on_error(true); + + $sql = "SELECT $column_name FROM $table"; + $result = $db->sql_query_limit($sql, 1); + $row = $db->sql_fetchrow($result); + $db->sql_freeresult($result); + + $error = ($db->sql_error_triggered) ? true : false; + + $db->sql_return_on_error(false); + + return (!$error) ? true : false; +} + +/** +* Function to prepare some column informations for better usage +*/ +function prepare_column_data($dbms, $column_data) +{ + global $dbms_type_map, $unsigned_types; + + // Get final column type... + if (strpos($column_data[0], ':') !== false) + { + list($orig_column_type, $column_length) = explode(':', $column_data[0]); + + $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length); + $orig_column_type .= ':'; + } + else + { + $orig_column_type = $column_data[0]; + $column_type = $dbms_type_map[$dbms][$column_data[0]]; + } + + // Adjust default value if db-dependant specified + if (is_array($column_data[1])) + { + $column_data[1] = (isset($column_data[1][$dbms])) ? $column_data[1][$dbms] : $column_data[1]['default']; + } + + $sql = ''; + + switch ($dbms) + { + case 'firebird': + $sql .= " {$column_type} "; + + if (!is_null($column_data[1])) + { + $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' '; + } + + $sql .= "NOT NULL"; + break; + + case 'mssql': + $sql .= " {$column_type} "; + + if (!is_null($column_data[1])) + { + // For hexadecimal values do not use single quotes + if (strpos($column_data[1], '0x') === 0) + { + $sql .= 'DEFAULT (' . $column_data[1] . ') '; + } + else + { + $sql .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; + } + } + + $sql .= 'NOT NULL'; + break; + + case 'mysql': + $sql .= " {$column_type} "; + + // For hexadecimal values do not use single quotes + if (!is_null($column_data[1])) + { + $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' "; + } + $sql .= 'NOT NULL'; + break; + + case 'oracle': + $sql .= " {$column_type} "; + $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; + + // In Oracle empty strings ('') are treated as NULL. + // Therefore in oracle we allow NULL's for all DEFAULT '' entries + $sql .= ($column_data[1] === '') ? '' : 'NOT NULL'; + break; + + case 'postgres': + $sql .= " {$column_type} "; + + $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : ''; + $sql .= 'NOT NULL'; + + // Unsigned? Then add a CHECK contraint + if (in_array($orig_column_type, $unsigned_types)) + { + $sql .= " CHECK ({$column_name} >= 0)"; + } + break; + + case 'sqlite': + $sql .= ' ' . $column_type . ' NOT NULL '; + $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : ''; + break; + } + + return array( + 'column_type_sql' => $sql, + ); +} + +/** +* Add new column +*/ +function sql_column_add($dbms, $table_name, $column_name, $column_data) +{ + global $errored, $error_ary; + + $column_data = prepare_column_data($dbms, $column_data); + + switch ($dbms) + { + case 'firebird': + $sql = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'mssql': + $sql = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'mysql': + $sql = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'oracle': + $sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $coumn_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'postgres': + $sql = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'sqlite': + if (version_compare(sqlite_libversion(), '3.0') == -1) + { + $sql = "SELECT sql + FROM sqlite_master + WHERE type = 'table' + AND name = '{$table_name}' + ORDER BY type DESC, name;"; + $result = _sql($sql, $errored, $error_ary); + + if (!$result) + { + break; + } + + $row = $db->sql_fetchrow($result); + $db->sql_freeresult($result); + + $db->sql_transaction('begin'); + + // Create a backup table and populate it, destroy the existing one + $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql'])); + $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name); + $db->sql_query('DROP TABLE ' . $table_name); + + preg_match('#\((.*)\)#s', $row['sql'], $matches); + + $new_table_cols = trim($matches[1]); + $old_table_cols = preg_split('/,(?=[\\sa-z])/im', $new_table_cols); + $column_list = array(); + + foreach ($old_table_cols as $declaration) + { + $entities = preg_split('#\s+#', trim($declaration)); + if ($entities == 'PRIMARY') + { + continue; + } + $column_list[] = $entities[0]; + } + + $columns = implode(',', $column_list); + + $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols; + + // create a new table and fill it up. destroy the temp one + $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');'); + $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'); + $db->sql_query('DROP TABLE ' . $table_name . '_temp'); + + $db->sql_transaction('commit'); + } + else + { + $sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $colum_data['column_type_sql'] . ']'; + _sql($sql, $errored, $error_ary); + } + break; + } +} + +/** +* Change column type (not name!) +*/ +function sql_column_change($dbms, $table_name, $column_name, $column_data) +{ + global $dbms_type_map, $db; + + $column_data = prepare_column_data($dbms, $column_data); + + switch ($dbms) + { + case 'firebird': + // Change type... + $sql = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'mssql': + $sql = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'mysql': + $sql = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'oracle': + $sql = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'postgres': + $sql = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET ' . $column_data['column_type_sql']; + _sql($sql, $errored, $error_ary); + break; + + case 'sqlite': + + $sql = "SELECT sql + FROM sqlite_master + WHERE type = 'table' + AND name = '{$table_name}' + ORDER BY type DESC, name;"; + $result = _sql($sql, $errored, $error_ary); + + if (!$result) + { + break; + } + + $row = $db->sql_fetchrow($result); + $db->sql_freeresult($result); + + $db->sql_transaction('begin'); + + // Create a temp table and populate it, destroy the existing one + $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql'])); + $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name); + $db->sql_query('DROP TABLE ' . $table_name); + + preg_match('#\((.*)\)#s', $row['sql'], $matches); + + $new_table_cols = trim($matches[1]); + $old_table_cols = preg_split('/,(?=[\\sa-z])/im', $new_table_cols); + $column_list = array(); + + foreach ($old_table_cols as $key => $declaration) + { + $entities = preg_split('#\s+#', trim($declaration)); + $column_list[] = $entities[0]; + if ($entities[0] == $column_name) + { + $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql']; + } + } + + $columns = implode(',', $column_list); + + // create a new table and fill it up. destroy the temp one + $db->sql_query('CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');'); + $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'); + $db->sql_query('DROP TABLE ' . $table_name . '_temp'); + + $db->sql_transaction('commit'); + + break; + } +} + +?>
\ No newline at end of file |