phpBB/install/database_update.php

   1 <?php
   2 /**
   3 *
   4 * @package install
   5 * @version $Id$
   6 * @copyright (c) 2006 phpBB Group
   7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License
   8 *
   9 */
  10
  11 $updates_to_version = '3.0.3';
  12
  13 // Return if we "just include it" to find out for which version the database update is responsuble for
  14 if (defined('IN_PHPBB') && defined('IN_INSTALL'))
  15 {
  16         return;
  17 }
  18
  19 /**
  20 */
  21 define('IN_PHPBB', true);
  22 define('IN_INSTALL', true);
  23
  24 if (!defined('PHPBB_ROOT_PATH')) define('PHPBB_ROOT_PATH', './../');
  25 if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1));
  26
  27 // Report all errors, except notices
  28 //error_reporting(E_ALL ^ E_NOTICE);
  29 error_reporting(E_ALL);
  30
  31 @set_time_limit(0);
  32
  33 // Include essential scripts
  34 include(PHPBB_ROOT_PATH . 'config.' . PHP_EXT);
  35
  36 if (!isset($dbms))
  37 {
  38         die("Please read: <a href='../docs/INSTALL.html'>INSTALL.html</a> before attempting to update.");
  39 }
  40
  41 // Load Extensions
  42 if (!empty($load_extensions))
  43 {
  44         $load_extensions = explode(',', $load_extensions);
  45
  46         foreach ($load_extensions as $extension)
  47         {
  48                 @dl(trim($extension));
  49         }
  50 }
  51
  52 // Include files
  53 require(PHPBB_ROOT_PATH . 'includes/acm/acm_' . $acm_type . '.' . PHP_EXT);
  54 require(PHPBB_ROOT_PATH . 'includes/cache.' . PHP_EXT);
  55 require(PHPBB_ROOT_PATH . 'includes/template.' . PHP_EXT);
  56 require(PHPBB_ROOT_PATH . 'includes/session.' . PHP_EXT);
  57 require(PHPBB_ROOT_PATH . 'includes/auth.' . PHP_EXT);
  58
  59 require(PHPBB_ROOT_PATH . 'includes/functions.' . PHP_EXT);
  60
  61 if (file_exists(PHPBB_ROOT_PATH . 'includes/functions_content.' . PHP_EXT))
  62 {
  63         require(PHPBB_ROOT_PATH . 'includes/functions_content.' . PHP_EXT);
  64 }
  65
  66 require(PHPBB_ROOT_PATH . 'includes/functions_admin.' . PHP_EXT);
  67 require(PHPBB_ROOT_PATH . 'includes/constants.' . PHP_EXT);
  68 require(PHPBB_ROOT_PATH . 'includes/db/' . $dbms . '.' . PHP_EXT);
  69 require(PHPBB_ROOT_PATH . 'includes/utf/utf_tools.' . PHP_EXT);
  70
  71 // If we are on PHP >= 6.0.0 we do not need some code
  72 if (version_compare(PHP_VERSION, '6.0.0-dev', '>='))
  73 {
  74         /**
  75         * @ignore
  76         */
  77         define('STRIP', false);
  78 }
  79 else
  80 {
  81         @set_magic_quotes_runtime(0);
  82         define('STRIP', (get_magic_quotes_gpc()) ? true : false);
  83 }
  84
  85 $user = new user();
  86 $cache = new acm();
  87 $db = new $sql_db();
  88
  89 // Add own hook handler, if present. :o
  90 if (file_exists(PHPBB_ROOT_PATH . 'includes/hooks/index.' . PHP_EXT))
  91 {
  92         require(PHPBB_ROOT_PATH . 'includes/hooks/index.' . PHP_EXT);
  93         $phpbb_hook = new phpbb_hook(array('exit_handler', 'phpbb_user_session_handler', 'append_sid', array('template', 'display')));
  94
  95         foreach (cache::obtain_hooks() as $hook)
  96         {
  97                 @include(PHPBB_ROOT_PATH . 'includes/hooks/' . $hook . '.' . PHP_EXT);
  98         }
  99 }
 100 else
 101 {
 102         $phpbb_hook = false;
 103 }
 104
 105 // Connect to DB
 106 $db->sql_connect($dbhost, $dbuser, $dbpasswd, $dbname, $dbport, false, false);
 107
 108 // We do not need this any longer, unset for safety purposes
 109 unset($dbpasswd);
 110
 111 $user->ip = (!empty($_SERVER['REMOTE_ADDR'])) ? htmlspecialchars($_SERVER['REMOTE_ADDR']) : '';
 112
 113 $sql = "SELECT config_value
 114         FROM " . CONFIG_TABLE . "
 115         WHERE config_name = 'default_lang'";
 116 $result = $db->sql_query($sql);
 117 $row = $db->sql_fetchrow($result);
 118 $db->sql_freeresult($result);
 119
 120 $language = basename(request_var('language', ''));
 121
 122 if (!$language)
 123 {
 124         $language = $row['config_value'];
 125 }
 126
 127 if (!file_exists(PHPBB_ROOT_PATH . 'language/' . $language))
 128 {
 129         die('No language found!');
 130 }
 131
 132 // And finally, load the relevant language files
 133 include(PHPBB_ROOT_PATH . 'language/' . $language . '/common.' . PHP_EXT);
 134 include(PHPBB_ROOT_PATH . 'language/' . $language . '/acp/common.' . PHP_EXT);
 135 include(PHPBB_ROOT_PATH . 'language/' . $language . '/install.' . PHP_EXT);
 136
 137 // Set PHP error handler to ours
 138 //set_error_handler('msg_handler');
 139
 140 // Define some variables for the database update
 141 $inline_update = (request_var('type', 0)) ? true : false;
 142
 143 // Database column types mapping
 144 $dbms_type_map = array(
 145         'mysql'         => array(
 146                 'INT:'          => 'int(%d)',
 147                 'BINT'          => 'bigint(20)',
 148                 'UINT'          => 'mediumint(8) UNSIGNED',
 149                 'UINT:'         => 'int(%d) UNSIGNED',
 150                 'TINT:'         => 'tinyint(%d)',
 151                 'USINT'         => 'smallint(4) UNSIGNED',
 152                 'BOOL'          => 'tinyint(1) UNSIGNED',
 153                 'VCHAR'         => 'varchar(255)',
 154                 'VCHAR:'        => 'varchar(%d)',
 155                 'CHAR:'         => 'char(%d)',
 156                 'XSTEXT'        => 'text',
 157                 'XSTEXT_UNI'=> 'varchar(100)',
 158                 'STEXT'         => 'text',
 159                 'STEXT_UNI'     => 'varchar(255)',
 160                 'TEXT'          => 'text',
 161                 'TEXT_UNI'      => 'text',
 162                 'MTEXT'         => 'mediumtext',
 163                 'MTEXT_UNI'     => 'mediumtext',
 164                 'TIMESTAMP'     => 'int(11) UNSIGNED',
 165                 'DECIMAL'       => 'decimal(5,2)',
 166                 'VCHAR_UNI'     => 'varchar(255)',
 167                 'VCHAR_UNI:'=> 'varchar(%d)',
 168                 'VCHAR_CI'      => 'varchar(255)',
 169                 'VARBINARY'     => 'varbinary(255)',
 170         ),
 171
 172         'firebird'      => array(
 173                 'INT:'          => 'INTEGER',
 174                 'BINT'          => 'DOUBLE PRECISION',
 175                 'UINT'          => 'INTEGER',
 176                 'UINT:'         => 'INTEGER',
 177                 'TINT:'         => 'INTEGER',
 178                 'USINT'         => 'INTEGER',
 179                 'BOOL'          => 'INTEGER',
 180                 'VCHAR'         => 'VARCHAR(255) CHARACTER SET NONE',
 181                 'VCHAR:'        => 'VARCHAR(%d) CHARACTER SET NONE',
 182                 'CHAR:'         => 'CHAR(%d) CHARACTER SET NONE',
 183                 'XSTEXT'        => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 184                 'STEXT'         => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 185                 'TEXT'          => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 186                 'MTEXT'         => 'BLOB SUB_TYPE TEXT CHARACTER SET NONE',
 187                 'XSTEXT_UNI'=> 'VARCHAR(100) CHARACTER SET UTF8',
 188                 'STEXT_UNI'     => 'VARCHAR(255) CHARACTER SET UTF8',
 189                 'TEXT_UNI'      => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
 190                 'MTEXT_UNI'     => 'BLOB SUB_TYPE TEXT CHARACTER SET UTF8',
 191                 'TIMESTAMP'     => 'INTEGER',
 192                 'DECIMAL'       => 'DOUBLE PRECISION',
 193                 'VCHAR_UNI'     => 'VARCHAR(255) CHARACTER SET UTF8',
 194                 'VCHAR_UNI:'=> 'VARCHAR(%d) CHARACTER SET UTF8',
 195                 'VCHAR_CI'      => 'VARCHAR(255) CHARACTER SET UTF8',
 196                 'VARBINARY'     => 'CHAR(255) CHARACTER SET NONE',
 197         ),
 198
 199         'mssql'         => array(
 200                 'INT:'          => '[int]',
 201                 'BINT'          => '[float]',
 202                 'UINT'          => '[int]',
 203                 'UINT:'         => '[int]',
 204                 'TINT:'         => '[int]',
 205                 'USINT'         => '[int]',
 206                 'BOOL'          => '[int]',
 207                 'VCHAR'         => '[varchar] (255)',
 208                 'VCHAR:'        => '[varchar] (%d)',
 209                 'CHAR:'         => '[char] (%d)',
 210                 'XSTEXT'        => '[varchar] (1000)',
 211                 'STEXT'         => '[varchar] (3000)',
 212                 'TEXT'          => '[varchar] (8000)',
 213                 'MTEXT'         => '[text]',
 214                 'XSTEXT_UNI'=> '[varchar] (100)',
 215                 'STEXT_UNI'     => '[varchar] (255)',
 216                 'TEXT_UNI'      => '[varchar] (4000)',
 217                 'MTEXT_UNI'     => '[text]',
 218                 'TIMESTAMP'     => '[int]',
 219                 'DECIMAL'       => '[float]',
 220                 'VCHAR_UNI'     => '[varchar] (255)',
 221                 'VCHAR_UNI:'=> '[varchar] (%d)',
 222                 'VCHAR_CI'      => '[varchar] (255)',
 223                 'VARBINARY'     => '[varchar] (255)',
 224         ),
 225
 226         'oracle'        => array(
 227                 'INT:'          => 'number(%d)',
 228                 'BINT'          => 'number(20)',
 229                 'UINT'          => 'number(8)',
 230                 'UINT:'         => 'number(%d)',
 231                 'TINT:'         => 'number(%d)',
 232                 'USINT'         => 'number(4)',
 233                 'BOOL'          => 'number(1)',
 234                 'VCHAR'         => 'varchar2(255 char)',
 235                 'VCHAR:'        => 'varchar2(%d char)',
 236                 'CHAR:'         => 'char(%d char)',
 237                 'XSTEXT'        => 'varchar2(1000 char)',
 238                 'STEXT'         => 'varchar2(3000 char)',
 239                 'TEXT'          => 'clob',
 240                 'MTEXT'         => 'clob',
 241                 'XSTEXT_UNI'=> 'varchar2(100 char)',
 242                 'STEXT_UNI'     => 'varchar2(255 char)',
 243                 'TEXT_UNI'      => 'clob',
 244                 'MTEXT_UNI'     => 'clob',
 245                 'TIMESTAMP'     => 'number(11)',
 246                 'DECIMAL'       => 'number(5, 2)',
 247                 'DECIMAL:'      => 'number(%d, 2)',
 248                 'PDECIMAL'      => 'number(6, 3)',
 249                 'PDECIMAL:'     => 'number(%d, 3)',
 250                 'VCHAR_UNI'     => 'varchar2(255 char)',
 251                 'VCHAR_UNI:'=> 'varchar2(%d char)',
 252                 'VCHAR_CI'      => 'varchar2(255 char)',
 253                 'VARBINARY'     => 'raw(255)',
 254         ),
 255
 256         'db2'           => array(
 257                 'INT:'          => 'integer',
 258                 'BINT'          => 'float',
 259                 'UINT'          => 'integer',
 260                 'UINT:'         => 'integer',
 261                 'TINT:'         => 'smallint',
 262                 'USINT'         => 'smallint',
 263                 'BOOL'          => 'smallint',
 264                 'VCHAR'         => 'varchar(255)',
 265                 'VCHAR:'        => 'varchar(%d)',
 266                 'CHAR:'         => 'char(%d)',
 267                 'XSTEXT'        => 'clob(65K)',
 268                 'STEXT'         => 'varchar(3000)',
 269                 'TEXT'          => 'clob(65K)',
 270                 'MTEXT'         => 'clob(16M)',
 271                 'XSTEXT_UNI'=> 'varchar(100)',
 272                 'STEXT_UNI'     => 'varchar(255)',
 273                 'TEXT_UNI'      => 'clob(65K)',
 274                 'MTEXT_UNI'     => 'clob(16M)',
 275                 'TIMESTAMP'     => 'integer',
 276                 'DECIMAL'       => 'float',
 277                 'VCHAR_UNI'     => 'varchar(255)',
 278                 'VCHAR_UNI:'=> 'varchar(%d)',
 279                 'VCHAR_CI'      => 'varchar(255)',
 280                 'VARBINARY'     => 'varchar(255)',
 281         ),
 282
 283         'sqlite'        => array(
 284                 'INT:'          => 'int(%d)',
 285                 'BINT'          => 'bigint(20)',
 286                 'UINT'          => 'INTEGER UNSIGNED', //'mediumint(8) UNSIGNED',
 287                 'UINT:'         => 'INTEGER UNSIGNED', // 'int(%d) UNSIGNED',
 288                 'TINT:'         => 'tinyint(%d)',
 289                 'USINT'         => 'INTEGER UNSIGNED', //'mediumint(4) UNSIGNED',
 290                 'BOOL'          => 'INTEGER UNSIGNED', //'tinyint(1) UNSIGNED',
 291                 'VCHAR'         => 'varchar(255)',
 292                 'VCHAR:'        => 'varchar(%d)',
 293                 'CHAR:'         => 'char(%d)',
 294                 'XSTEXT'        => 'text(65535)',
 295                 'STEXT'         => 'text(65535)',
 296                 'TEXT'          => 'text(65535)',
 297                 'MTEXT'         => 'mediumtext(16777215)',
 298                 'XSTEXT_UNI'=> 'text(65535)',
 299                 'STEXT_UNI'     => 'text(65535)',
 300                 'TEXT_UNI'      => 'text(65535)',
 301                 'MTEXT_UNI'     => 'mediumtext(16777215)',
 302                 'TIMESTAMP'     => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
 303                 'DECIMAL'       => 'decimal(5,2)',
 304                 'VCHAR_UNI'     => 'varchar(255)',
 305                 'VCHAR_UNI:'=> 'varchar(%d)',
 306                 'VCHAR_CI'      => 'varchar(255)',
 307                 'VARBINARY'     => 'blob',
 308         ),
 309
 310         'postgres'      => array(
 311                 'INT:'          => 'INT4',
 312                 'BINT'          => 'INT8',
 313                 'UINT'          => 'INT4', // unsigned
 314                 'UINT:'         => 'INT4', // unsigned
 315                 'USINT'         => 'INT2', // unsigned
 316                 'BOOL'          => 'boolean', // unsigned
 317                 'TINT:'         => 'INT2',
 318                 'VCHAR'         => 'varchar(255)',
 319                 'VCHAR:'        => 'varchar(%d)',
 320                 'CHAR:'         => 'char(%d)',
 321                 'XSTEXT'        => 'varchar(1000)',
 322                 'STEXT'         => 'varchar(3000)',
 323                 'TEXT'          => 'varchar(8000)',
 324                 'MTEXT'         => 'TEXT',
 325                 'XSTEXT_UNI'=> 'varchar(100)',
 326                 'STEXT_UNI'     => 'varchar(255)',
 327                 'TEXT_UNI'      => 'varchar(4000)',
 328                 'MTEXT_UNI'     => 'TEXT',
 329                 'TIMESTAMP'     => 'INT4', // unsigned
 330                 'DECIMAL'       => 'decimal(5,2)',
 331                 'VCHAR_UNI'     => 'varchar(255)',
 332                 'VCHAR_UNI:'=> 'varchar(%d)',
 333                 'VCHAR_CI'      => 'varchar(255)',
 334                 'VARBINARY'     => 'bytea',
 335         ),
 336 );
 337
 338 // A list of types being unsigned for better reference in some db's
 339 $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
 340
 341 // Only an example, but also commented out
 342 $database_update_info = array(
 343
 344         // No changes from 3.0.3-RC1 to 3.0.3
 345         '3.0.3-RC1'             => array(),
 346 );
 347
 348 // Determine mapping database type
 349 $map_dbms = $db->dbms_type;
 350
 351 $error_ary = array();
 352 $errored = false;
 353
 354 header('Content-type: text/html; charset=UTF-8');
 355
 356 ?>
 357 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
 358 <html xmlns="http://www.w3.org/1999/xhtml" dir="<?php echo $lang['DIRECTION']; ?>" lang="<?php echo $lang['USER_LANG']; ?>" xml:lang="<?php echo $lang['USER_LANG']; ?>">
 359 <head>
 360
 361 <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
 362 <meta http-equiv="content-language" content="<?php echo $lang['USER_LANG']; ?>" />
 363 <meta http-equiv="content-style-type" content="text/css" />
 364 <meta http-equiv="imagetoolbar" content="no" />
 365
 366 <title><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></title>
 367
 368 <link href="../adm/style/admin.css" rel="stylesheet" type="text/css" media="screen" />
 369
 370 </head>
 371
 372 <body>
 373 <div id="wrap">
 374         <div id="page-header">&nbsp;</div>
 375
 376         <div id="page-body">
 377                 <div id="acp">
 378                 <div class="panel">
 379                         <span class="corners-top"><span></span></span>
 380                                 <div id="content">
 381                                         <div id="main">
 382
 383         <h1><?php echo $lang['UPDATING_TO_LATEST_STABLE']; ?></h1>
 384
 385         <br />
 386
 387         <p><?php echo $lang['DATABASE_TYPE']; ?> :: <strong><?php echo $db->sql_layer; ?></strong><br />
 388 <?php
 389
 390 // To let set_config() calls succeed, we need to make the config array available globally
 391 $config = array();
 392 $sql = 'SELECT *
 393         FROM ' . CONFIG_TABLE;
 394 $result = $db->sql_query($sql);
 395
 396 while ($row = $db->sql_fetchrow($result))
 397 {
 398         $config[$row['config_name']] = $row['config_value'];
 399 }
 400 $db->sql_freeresult($result);
 401
 402 echo $lang['PREVIOUS_VERSION'] . ' :: <strong>' . $config['version'] . '</strong><br />';
 403 echo $lang['UPDATED_VERSION'] . ' :: <strong>' . $updates_to_version . '</strong></p>';
 404
 405 $current_version = str_replace('rc', 'RC', strtolower($config['version']));
 406 $latest_version = str_replace('rc', 'RC', strtolower($updates_to_version));
 407 $orig_version = $config['version'];
 408
 409 // If the latest version and the current version are 'unequal', we will update the version_update_from, else we do not update anything.
 410 if ($inline_update)
 411 {
 412         if ($current_version !== $latest_version)
 413         {
 414                 set_config('version_update_from', $orig_version);
 415         }
 416 }
 417 else
 418 {
 419         // If not called from the update script, we will actually remove the traces
 420         $db->sql_query('DELETE FROM ' . CONFIG_TABLE . " WHERE config_name = 'version_update_from'");
 421 }
 422
 423 // Checks/Operations that have to be completed prior to starting the update itself
 424 $exit = false;
 425
 426 if ($exit)
 427 {
 428 ?>
 429
 430                                         </div>
 431                                 </div>
 432                         <span class="corners-bottom"><span></span></span>
 433                 </div>
 434                 </div>
 435         </div>
 436
 437         <div id="page-footer">
 438                 Powered by <a href="http://www.phpbb.com/">phpBB</a> &copy; 2000, 2002, 2005, 2007 phpBB Group
 439         </div>
 440 </div>
 441
 442 </body>
 443 </html>
 444
 445 <?php
 446         if (function_exists('exit_handler'))
 447         {
 448                 exit_handler();
 449         }
 450 }
 451
 452 // Schema updates
 453 ?>
 454         <br /><br />
 455
 456         <h1><?php echo $lang['UPDATE_DATABASE_SCHEMA']; ?></h1>
 457
 458         <br />
 459         <p><?php echo $lang['PROGRESS']; ?> :: <strong>
 460
 461 <?php
 462
 463 flush();
 464
 465 // We go through the schema changes from the lowest to the highest version
 466 // We try to also include versions 'in-between'...
 467 $no_updates = true;
 468 $versions = array_keys($database_update_info);
 469 for ($i = 0; $i < sizeof($versions); $i++)
 470 {
 471         $version = $versions[$i];
 472         $schema_changes = $database_update_info[$version];
 473
 474         $next_version = (isset($versions[$i + 1])) ? $versions[$i + 1] : $updates_to_version;
 475
 476         if (!sizeof($schema_changes))
 477         {
 478                 continue;
 479         }
 480
 481         // If the installed version to be updated to is < than the current version, and if the current version is >= as the version to be updated to next, we will skip the process
 482         if (version_compare($version, $current_version, '<') && version_compare($current_version, $next_version, '>='))
 483         {
 484                 continue;
 485         }
 486
 487         $no_updates = false;
 488
 489         // Change columns?
 490         if (!empty($schema_changes['change_columns']))
 491         {
 492                 foreach ($schema_changes['change_columns'] as $table => $columns)
 493                 {
 494                         foreach ($columns as $column_name => $column_data)
 495                         {
 496                                 sql_column_change($map_dbms, $table, $column_name, $column_data);
 497                         }
 498                 }
 499         }
 500
 501         // Add columns?
 502         if (!empty($schema_changes['add_columns']))
 503         {
 504                 foreach ($schema_changes['add_columns'] as $table => $columns)
 505                 {
 506                         foreach ($columns as $column_name => $column_data)
 507                         {
 508                                 // Only add the column if it does not exist yet
 509                                 if (!column_exists($map_dbms, $table, $column_name))
 510                                 {
 511                                         sql_column_add($map_dbms, $table, $column_name, $column_data);
 512                                 }
 513                         }
 514                 }
 515         }
 516
 517         // Remove keys?
 518         if (!empty($schema_changes['drop_keys']))
 519         {
 520                 foreach ($schema_changes['drop_keys'] as $table => $indexes)
 521                 {
 522                         foreach ($indexes as $index_name)
 523                         {
 524                                 sql_index_drop($map_dbms, $index_name, $table);
 525                         }
 526                 }
 527         }
 528
 529         // Drop columns?
 530         if (!empty($schema_changes['drop_columns']))
 531         {
 532                 foreach ($schema_changes['drop_columns'] as $table => $columns)
 533                 {
 534                         foreach ($columns as $column)
 535                         {
 536                                 sql_column_remove($map_dbms, $table, $column);
 537                         }
 538                 }
 539         }
 540
 541         // Add primary keys?
 542         if (!empty($schema_changes['add_primary_keys']))
 543         {
 544                 foreach ($schema_changes['add_primary_keys'] as $table => $columns)
 545                 {
 546                         sql_create_primary_key($map_dbms, $table, $columns);
 547                 }
 548         }
 549
 550         // Add unqiue indexes?
 551         if (!empty($schema_changes['add_unique_index']))
 552         {
 553                 foreach ($schema_changes['add_unique_index'] as $table => $index_array)
 554                 {
 555                         foreach ($index_array as $index_name => $column)
 556                         {
 557                                 sql_create_unique_index($map_dbms, $index_name, $table, $column);
 558                         }
 559                 }
 560         }
 561
 562         // Add indexes?
 563         if (!empty($schema_changes['add_index']))
 564         {
 565                 foreach ($schema_changes['add_index'] as $table => $index_array)
 566                 {
 567                         foreach ($index_array as $index_name => $column)
 568                         {
 569                                 sql_create_index($map_dbms, $index_name, $table, $column);
 570                         }
 571                 }
 572         }
 573 }
 574
 575 _write_result($no_updates, $errored, $error_ary);
 576
 577 // Data updates
 578 $error_ary = array();
 579 $errored = $no_updates = false;
 580
 581 ?>
 582
 583 <br /><br />
 584 <h1><?php echo $lang['UPDATING_DATA']; ?></h1>
 585 <br />
 586 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
 587
 588 <?php
 589
 590 flush();
 591
 592 $no_updates = true;
 593
 594 $versions = array(
 595 );
 596
 597 // some code magic
 598 for ($i = 0; $i < sizeof($versions); $i++)
 599 {
 600         $version = $versions[$i];
 601         $next_version = (isset($versions[$i + 1])) ? $versions[$i + 1] : $updates_to_version;
 602
 603         // If the installed version to be updated to is < than the current version, and if the current version is >= as the version to be updated to next, we will skip the process
 604         if (version_compare($version, $current_version, '<') && version_compare($current_version, $next_version, '>='))
 605         {
 606                 continue;
 607         }
 608
 609         $no_updates = false;
 610         change_database_data($version);
 611 }
 612
 613 _write_result($no_updates, $errored, $error_ary);
 614
 615 $error_ary = array();
 616 $errored = $no_updates = false;
 617
 618 ?>
 619
 620 <br /><br />
 621 <h1><?php echo $lang['UPDATE_VERSION_OPTIMIZE']; ?></h1>
 622 <br />
 623 <p><?php echo $lang['PROGRESS']; ?> :: <strong>
 624
 625 <?php
 626
 627 flush();
 628
 629 // update the version
 630 $sql = "UPDATE " . CONFIG_TABLE . "
 631         SET config_value = '$updates_to_version'
 632         WHERE config_name = 'version'";
 633 _sql($sql, $errored, $error_ary);
 634
 635 // Reset permissions
 636 $sql = 'UPDATE ' . USERS_TABLE . "
 637         SET user_permissions = '',
 638                 user_perm_from = 0";
 639 _sql($sql, $errored, $error_ary);
 640
 641 /* Optimize/vacuum analyze the tables where appropriate
 642 // this should be done for each version in future along with
 643 // the version number update
 644 switch ($db->dbms_type)
 645 {
 646         case 'mysql':
 647                 $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';
 648                 _sql($sql, $errored, $error_ary);
 649         break;
 650
 651         case 'postgresql':
 652                 _sql("VACUUM ANALYZE", $errored, $error_ary);
 653         break;
 654 }
 655 */
 656
 657 _write_result($no_updates, $errored, $error_ary);
 658
 659 ?>
 660
 661 <br />
 662 <h1><?php echo $lang['UPDATE_COMPLETED']; ?></h1>
 663
 664 <br />
 665
 666 <?php
 667
 668 if (!$inline_update)
 669 {
 670         // Purge the cache...
 671         $cache->purge();
 672 ?>
 673
 674         <p style="color:red"><?php echo $lang['UPDATE_FILES_NOTICE']; ?></p>
 675
 676         <p><?php echo $lang['COMPLETE_LOGIN_TO_BOARD']; ?></p>
 677
 678 <?php
 679 }
 680 else
 681 {
 682 ?>
 683
 684         <p><?php echo ((isset($lang['INLINE_UPDATE_SUCCESSFUL'])) ? $lang['INLINE_UPDATE_SUCCESSFUL'] : 'The database update was successful. Now you need to continue the update process.'); ?></p>
 685
 686         <p><a href="<?php echo append_sid('install/index', "mode=update&amp;sub=file_check&amp;lang=$language"); ?>" class="button1"><?php echo (isset($lang['CONTINUE_UPDATE_NOW'])) ? $lang['CONTINUE_UPDATE_NOW'] : 'Continue the update process now'; ?></a></p>
 687
 688 <?php
 689 }
 690
 691 // Add database update to log
 692 add_log('admin', 'LOG_UPDATE_DATABASE', $orig_version, $updates_to_version);
 693
 694 // Now we purge the session table as well as all cache files
 695 $cache->purge();
 696
 697 ?>
 698
 699                                         </div>
 700                                 </div>
 701                         <span class="corners-bottom"><span></span></span>
 702                 </div>
 703                 </div>
 704         </div>
 705
 706         <div id="page-footer">
 707                 Powered by phpBB &copy; 2000, 2002, 2005, 2007 <a href="http://www.phpbb.com/">phpBB Group</a>
 708         </div>
 709 </div>
 710
 711 </body>
 712 </html>
 713
 714 <?php
 715
 716 garbage_collection();
 717
 718 if (function_exists('exit_handler'))
 719 {
 720         exit_handler();
 721 }
 722
 723 /**
 724 * Function where all data changes are executed
 725 */
 726 function change_database_data($version)
 727 {
 728         global $db, $map_dbms, $errored, $error_ary, $config;
 729
 730         switch ($version)
 731         {
 732                 default:
 733                 break;
 734         }
 735 }
 736
 737 /**
 738 * Function for triggering an sql statement
 739 */
 740 function _sql($sql, &$errored, &$error_ary, $echo_dot = true)
 741 {
 742         global $db;
 743
 744         if (defined('DEBUG_EXTRA'))
 745         {
 746                 echo "<br />\n{$sql}\n<br />";
 747         }
 748
 749         $db->sql_return_on_error(true);
 750
 751         $result = $db->sql_query($sql);
 752         if ($db->sql_error_triggered)
 753         {
 754                 $errored = true;
 755                 $error_ary['sql'][] = $db->sql_error_sql;
 756                 $error_ary['error_code'][] = $db->_sql_error();
 757         }
 758
 759         $db->sql_return_on_error(false);
 760
 761         if ($echo_dot)
 762         {
 763                 echo ". \n";
 764                 flush();
 765         }
 766
 767         return $result;
 768 }
 769
 770 function _write_result($no_updates, $errored, $error_ary)
 771 {
 772         global $lang;
 773
 774         if ($no_updates)
 775         {
 776                 echo ' ' . $lang['NO_UPDATES_REQUIRED'] . '</strong></p>';
 777         }
 778         else
 779         {
 780                 echo ' <span class="success">' . $lang['DONE'] . '</span></strong><br />' . $lang['RESULT'] . ' :: ';
 781
 782                 if ($errored)
 783                 {
 784                         echo ' <strong>' . $lang['SOME_QUERIES_FAILED'] . '</strong> <ul>';
 785
 786                         for ($i = 0; $i < sizeof($error_ary['sql']); $i++)
 787                         {
 788                                 echo '<li>' . $lang['ERROR'] . ' :: <strong>' . htmlspecialchars($error_ary['error_code'][$i]['message']) . '</strong><br />';
 789                                 echo $lang['SQL'] . ' :: <strong>' . htmlspecialchars($error_ary['sql'][$i]) . '</strong><br /><br /></li>';
 790                         }
 791
 792                         echo '</ul> <br /><br />' . $lang['SQL_FAILURE_EXPLAIN'] . '</p>';
 793                 }
 794                 else
 795                 {
 796                         echo '<strong>' . $lang['NO_ERRORS'] . '</strong></p>';
 797                 }
 798         }
 799 }
 800
 801 /**
 802 * Check if a specified column exist
 803 */
 804 function column_exists($dbms, $table, $column_name)
 805 {
 806         global $db;
 807
 808         switch ($dbms)
 809         {
 810                 case 'mysql':
 811                         $sql = "SHOW COLUMNS
 812                                 FROM $table";
 813                         $result = $db->sql_query($sql);
 814                         while ($row = $db->sql_fetchrow($result))
 815                         {
 816                                 // lower case just in case
 817                                 if (strtolower($row['Field']) == $column_name)
 818                                 {
 819                                         $db->sql_freeresult($result);
 820                                         return true;
 821                                 }
 822                         }
 823                         $db->sql_freeresult($result);
 824                         return false;
 825                 break;
 826
 827                 // PostgreSQL has a way of doing this in a much simpler way but would
 828                 // not allow us to support all versions of PostgreSQL
 829                 case 'postgres':
 830                         $sql = "SELECT a.attname
 831                                 FROM pg_class c, pg_attribute a
 832                                 WHERE c.relname = '{$table}'
 833                                         AND a.attnum > 0
 834                                         AND a.attrelid = c.oid";
 835                         $result = $db->sql_query($sql);
 836                         while ($row = $db->sql_fetchrow($result))
 837                         {
 838                                 // lower case just in case
 839                                 if (strtolower($row['attname']) == $column_name)
 840                                 {
 841                                         $db->sql_freeresult($result);
 842                                         return true;
 843                                 }
 844                         }
 845                         $db->sql_freeresult($result);
 846                         return false;
 847                 break;
 848
 849                 // same deal with PostgreSQL, we must perform more complex operations than
 850                 // we technically could
 851                 case 'mssql':
 852                         $sql = "SELECT c.name
 853                                 FROM syscolumns c
 854                                 LEFT JOIN sysobjects o ON c.id = o.id
 855                                 WHERE o.name = '{$table}'";
 856                         $result = $db->sql_query($sql);
 857                         while ($row = $db->sql_fetchrow($result))
 858                         {
 859                                 // lower case just in case
 860                                 if (strtolower($row['name']) == $column_name)
 861                                 {
 862                                         $db->sql_freeresult($result);
 863                                         return true;
 864                                 }
 865                         }
 866                         $db->sql_freeresult($result);
 867                         return false;
 868                 break;
 869
 870                 case 'oracle':
 871                         $sql = "SELECT column_name
 872                                 FROM user_tab_columns
 873                                 WHERE table_name = '{$table}'";
 874                         $result = $db->sql_query($sql);
 875                         while ($row = $db->sql_fetchrow($result))
 876                         {
 877                                 // lower case just in case
 878                                 if (strtolower($row['column_name']) == $column_name)
 879                                 {
 880                                         $db->sql_freeresult($result);
 881                                         return true;
 882                                 }
 883                         }
 884                         $db->sql_freeresult($result);
 885                         return false;
 886                 break;
 887
 888                 case 'firebird':
 889                         $sql = "SELECT RDB\$FIELD_NAME as FNAME
 890                                 FROM RDB\$RELATION_FIELDS
 891                                 WHERE RDB\$RELATION_NAME = '{$table}'";
 892                         $result = $db->sql_query($sql);
 893                         while ($row = $db->sql_fetchrow($result))
 894                         {
 895                                 // lower case just in case
 896                                 if (strtolower($row['fname']) == $column_name)
 897                                 {
 898                                         $db->sql_freeresult($result);
 899                                         return true;
 900                                 }
 901                         }
 902                         $db->sql_freeresult($result);
 903                         return false;
 904                 break;
 905
 906                 // ugh, SQLite
 907                 case 'sqlite':
 908                         $sql = "SELECT sql
 909                                 FROM sqlite_master
 910                                 WHERE type = 'table'
 911                                         AND name = '{$table}'";
 912                         $result = $db->sql_query($sql);
 913
 914                         if (!$result)
 915                         {
 916                                 return false;
 917                         }
 918
 919                         $row = $db->sql_fetchrow($result);
 920                         $db->sql_freeresult($result);
 921
 922                         preg_match('#\((.*)\)#s', $row['sql'], $matches);
 923
 924                         $cols = trim($matches[1]);
 925                         $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
 926
 927                         foreach ($col_array as $declaration)
 928                         {
 929                                 $entities = preg_split('#\s+#', trim($declaration));
 930                                 if ($entities[0] == 'PRIMARY')
 931                                 {
 932                                         continue;
 933                                 }
 934
 935                                 if (strtolower($entities[0]) == $column_name)
 936                                 {
 937                                         return true;
 938                                 }
 939                         }
 940                         return false;
 941                 break;
 942         }
 943 }
 944
 945 /**
 946 * Function to prepare some column information for better usage
 947 */
 948 function prepare_column_data($dbms, $column_data, $table_name, $column_name)
 949 {
 950         global $dbms_type_map, $unsigned_types;
 951
 952         // Get type
 953         if (strpos($column_data[0], ':') !== false)
 954         {
 955                 list($orig_column_type, $column_length) = explode(':', $column_data[0]);
 956
 957                 if (!is_array($dbms_type_map[$dbms][$orig_column_type . ':']))
 958                 {
 959                         $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'], $column_length);
 960                 }
 961                 else
 962                 {
 963                         if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['rule']))
 964                         {
 965                                 switch ($dbms_type_map[$dbms][$orig_column_type . ':']['rule'][0])
 966                                 {
 967                                         case 'div':
 968                                                 $column_length /= $dbms_type_map[$dbms][$orig_column_type . ':']['rule'][1];
 969                                                 $column_length = ceil($column_length);
 970                                                 $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
 971                                         break;
 972                                 }
 973                         }
 974
 975                         if (isset($dbms_type_map[$dbms][$orig_column_type . ':']['limit']))
 976                         {
 977                                 switch ($dbms_type_map[$dbms][$orig_column_type . ':']['limit'][0])
 978                                 {
 979                                         case 'mult':
 980                                                 $column_length *= $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][1];
 981                                                 if ($column_length > $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][2])
 982                                                 {
 983                                                         $column_type = $dbms_type_map[$dbms][$orig_column_type . ':']['limit'][3];
 984                                                 }
 985                                                 else
 986                                                 {
 987                                                         $column_type = sprintf($dbms_type_map[$dbms][$orig_column_type . ':'][0], $column_length);
 988                                                 }
 989                                         break;
 990                                 }
 991                         }
 992                 }
 993                 $orig_column_type .= ':';
 994         }
 995         else
 996         {
 997                 $orig_column_type = $column_data[0];
 998                 $column_type = $dbms_type_map[$dbms][$column_data[0]];
 999         }
1000
1001         // Adjust default value if db-dependant specified
1002         if (is_array($column_data[1]))
1003         {
1004                 $column_data[1] = (isset($column_data[1][$dbms])) ? $column_data[1][$dbms] : $column_data[1]['default'];
1005         }
1006
1007         $sql = '';
1008         $return_array = array();
1009
1010         switch ($dbms)
1011         {
1012                 case 'firebird':
1013                         $sql .= " {$column_type} ";
1014
1015                         if (!is_null($column_data[1]))
1016                         {
1017                                 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' ';
1018                         }
1019
1020                         $sql .= 'NOT NULL';
1021
1022                         // This is a UNICODE column and thus should be given it's fair share
1023                         if (preg_match('/^X?STEXT_UNI|VCHAR_(CI|UNI:?)/', $column_data[0]))
1024                         {
1025                                 $sql .= ' COLLATE UNICODE';
1026                         }
1027
1028                 break;
1029
1030                 case 'mssql':
1031                         $sql .= " {$column_type} ";
1032                         $sql_default = " {$column_type} ";
1033
1034                         // For adding columns we need the default definition
1035                         if (!is_null($column_data[1]))
1036                         {
1037                                 // For hexadecimal values do not use single quotes
1038                                 if (strpos($column_data[1], '0x') === 0)
1039                                 {
1040                                         $sql_default .= 'DEFAULT (' . $column_data[1] . ') ';
1041                                 }
1042                                 else
1043                                 {
1044                                         $sql_default .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
1045                                 }
1046                         }
1047
1048                         $sql .= 'NOT NULL';
1049                         $sql_default .= 'NOT NULL';
1050
1051                         $return_array['column_type_sql_default'] = $sql_default;
1052                 break;
1053
1054                 case 'mysql':
1055                         $sql .= " {$column_type} ";
1056
1057                         // For hexadecimal values do not use single quotes
1058                         if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
1059                         {
1060                                 $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
1061                         }
1062                         $sql .= 'NOT NULL';
1063
1064                         if (isset($column_data[2]))
1065                         {
1066                                 if ($column_data[2] == 'auto_increment')
1067                                 {
1068                                         $sql .= ' auto_increment';
1069                                 }
1070                                 else if ($column_data[2] == 'true_sort')
1071                                 {
1072                                         $sql .= ' COLLATE utf8_unicode_ci';
1073                                 }
1074                         }
1075
1076                 break;
1077
1078                 case 'oracle':
1079                         $sql .= " {$column_type} ";
1080                         $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
1081
1082                         // In Oracle empty strings ('') are treated as NULL.
1083                         // Therefore in oracle we allow NULL's for all DEFAULT '' entries
1084                         // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
1085                         if (preg_match('/number/i', $column_type))
1086                         {
1087                                 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL';
1088                         }
1089                 break;
1090
1091                 case 'postgres':
1092                         $return_array['column_type'] = $column_type;
1093
1094                         $sql .= " {$column_type} ";
1095
1096                         if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1097                         {
1098                                 $default_val = "nextval('{$table_name}_seq')";
1099                         }
1100                         else if (!is_null($column_data[1]))
1101                         {
1102                                 $default_val = "'" . $column_data[1] . "'";
1103                                 $return_array['null'] = 'NOT NULL';
1104                                 $sql .= 'NOT NULL ';
1105                         }
1106
1107                         $return_array['default'] = $default_val;
1108
1109                         $sql .= "DEFAULT {$default_val}";
1110
1111                         // Unsigned? Then add a CHECK contraint
1112                         if (in_array($orig_column_type, $unsigned_types))
1113                         {
1114                                 $return_array['constraint'] = "CHECK ({$column_name} >= 0)";
1115                                 $sql .= " CHECK ({$column_name} >= 0)";
1116                         }
1117                 break;
1118
1119                 case 'sqlite':
1120                         if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1121                         {
1122                                 $sql .= ' INTEGER PRIMARY KEY';
1123                         }
1124                         else
1125                         {
1126                                 $sql .= ' ' . $column_type;
1127                         }
1128
1129                         $sql .= ' NOT NULL ';
1130                         $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : '';
1131                 break;
1132         }
1133
1134         $return_array['column_type_sql'] = $sql;
1135
1136         return $return_array;
1137 }
1138
1139 /**
1140 * Add new column
1141 */
1142 function sql_column_add($dbms, $table_name, $column_name, $column_data)
1143 {
1144         global $errored, $error_ary;
1145
1146         $column_data = prepare_column_data($dbms, $column_data, $table_name, $column_name);
1147
1148         switch ($dbms)
1149         {
1150                 case 'firebird':
1151                         $sql = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name . '" ' . $column_data['column_type_sql'];
1152                         _sql($sql, $errored, $error_ary);
1153                 break;
1154
1155                 case 'mssql':
1156                         $sql = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
1157                         _sql($sql, $errored, $error_ary);
1158                 break;
1159
1160                 case 'mysql':
1161                         $sql = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'];
1162                         _sql($sql, $errored, $error_ary);
1163                 break;
1164
1165                 case 'oracle':
1166                         $sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1167                         _sql($sql, $errored, $error_ary);
1168                 break;
1169
1170                 case 'postgres':
1171                         $sql = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql'];
1172                         _sql($sql, $errored, $error_ary);
1173                 break;
1174
1175                 case 'sqlite':
1176                         if (version_compare(sqlite_libversion(), '3.0') == -1)
1177                         {
1178                                 global $db;
1179                                 $sql = "SELECT sql
1180                                         FROM sqlite_master
1181                                         WHERE type = 'table'
1182                                                 AND name = '{$table_name}'
1183                                         ORDER BY type DESC, name;";
1184                                 $result = $db->sql_query($sql);
1185
1186                                 if (!$result)
1187                                 {
1188                                         break;
1189                                 }
1190
1191                                 $row = $db->sql_fetchrow($result);
1192                                 $db->sql_freeresult($result);
1193
1194                                 $db->sql_transaction('begin');
1195
1196                                 // Create a backup table and populate it, destroy the existing one
1197                                 $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
1198                                 $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
1199                                 $db->sql_query('DROP TABLE ' . $table_name);
1200
1201                                 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1202
1203                                 $new_table_cols = trim($matches[1]);
1204                                 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1205                                 $column_list = array();
1206
1207                                 foreach ($old_table_cols as $declaration)
1208                                 {
1209                                         $entities = preg_split('#\s+#', trim($declaration));
1210                                         if ($entities[0] == 'PRIMARY')
1211                                         {
1212                                                 continue;
1213                                         }
1214                                         $column_list[] = $entities[0];
1215                                 }
1216
1217                                 $columns = implode(',', $column_list);
1218
1219                                 $new_table_cols = $column_name . ' ' . $column_data['column_type_sql'] . ',' . $new_table_cols;
1220
1221                                 // create a new table and fill it up. destroy the temp one
1222                                 $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');');
1223                                 $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
1224                                 $db->sql_query('DROP TABLE ' . $table_name . '_temp');
1225
1226                                 $db->sql_transaction('commit');
1227                         }
1228                         else
1229                         {
1230                                 $sql = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' [' . $column_data['column_type_sql'] . ']';
1231                                 _sql($sql, $errored, $error_ary);
1232                         }
1233                 break;
1234         }
1235 }
1236
1237 /**
1238 * Drop column
1239 */
1240 function sql_column_remove($dbms, $table_name, $column_name)
1241 {
1242         global $errored, $error_ary;
1243
1244         switch ($dbms)
1245         {
1246                 case 'firebird':
1247                         $sql = 'ALTER TABLE "' . $table_name . '" DROP "' . $column_name . '"';
1248                         _sql($sql, $errored, $error_ary);
1249                 break;
1250
1251                 case 'mssql':
1252                         $sql = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
1253                         _sql($sql, $errored, $error_ary);
1254                 break;
1255
1256                 case 'mysql':
1257                         $sql = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
1258                         _sql($sql, $errored, $error_ary);
1259                 break;
1260
1261                 case 'oracle':
1262                         $sql = 'ALTER TABLE ' . $table_name . ' DROP ' . $column_name;
1263                         _sql($sql, $errored, $error_ary);
1264                 break;
1265
1266                 case 'postgres':
1267                         $sql = 'ALTER TABLE ' . $table_name . ' DROP COLUMN "' . $column_name . '"';
1268                         _sql($sql, $errored, $error_ary);
1269                 break;
1270
1271                 case 'sqlite':
1272                         if (version_compare(sqlite_libversion(), '3.0') == -1)
1273                         {
1274                                 global $db;
1275                                 $sql = "SELECT sql
1276                                         FROM sqlite_master
1277                                         WHERE type = 'table'
1278                                                 AND name = '{$table_name}'
1279                                         ORDER BY type DESC, name;";
1280                                 $result = $db->sql_query($sql);
1281
1282                                 if (!$result)
1283                                 {
1284                                         break;
1285                                 }
1286
1287                                 $row = $db->sql_fetchrow($result);
1288                                 $db->sql_freeresult($result);
1289
1290                                 $db->sql_transaction('begin');
1291
1292                                 // Create a backup table and populate it, destroy the existing one
1293                                 $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
1294                                 $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
1295                                 $db->sql_query('DROP TABLE ' . $table_name);
1296
1297                                 preg_match('#\((.*)\)#s', $row['sql'], $matches);
1298
1299                                 $new_table_cols = trim($matches[1]);
1300                                 $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1301                                 $column_list = array();
1302
1303                                 foreach ($old_table_cols as $declaration)
1304                                 {
1305                                         $entities = preg_split('#\s+#', trim($declaration));
1306                                         if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
1307                                         {
1308                                                 continue;
1309                                         }
1310                                         $column_list[] = $entities[0];
1311                                 }
1312
1313                                 $columns = implode(',', $column_list);
1314
1315                                 $new_table_cols = $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);
1316
1317                                 // create a new table and fill it up. destroy the temp one
1318                                 $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');');
1319                                 $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
1320                                 $db->sql_query('DROP TABLE ' . $table_name . '_temp');
1321
1322                                 $db->sql_transaction('commit');
1323                         }
1324                         else
1325                         {
1326                                 $sql = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
1327                                 _sql($sql, $errored, $error_ary);
1328                         }
1329                 break;
1330         }
1331 }
1332
1333 function sql_index_drop($dbms, $index_name, $table_name)
1334 {
1335         global $dbms_type_map, $db;
1336         global $errored, $error_ary;
1337
1338         switch ($dbms)
1339         {
1340                 case 'mssql':
1341                         $sql = 'DROP INDEX ' . $table_name . '.' . $index_name;
1342                         _sql($sql, $errored, $error_ary);
1343                 break;
1344
1345                 case 'mysql':
1346                         $sql = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1347                         _sql($sql, $errored, $error_ary);
1348                 break;
1349
1350                 case 'firebird':
1351                 case 'oracle':
1352                 case 'postgres':
1353                 case 'sqlite':
1354                         $sql = 'DROP INDEX ' . $table_name . '_' . $index_name;
1355                         _sql($sql, $errored, $error_ary);
1356                 break;
1357         }
1358 }
1359
1360 function sql_create_primary_key($dbms, $table_name, $column)
1361 {
1362         global $dbms_type_map, $db;
1363         global $errored, $error_ary;
1364
1365         switch ($dbms)
1366         {
1367                 case 'firebird':
1368                 case 'postgres':
1369                         $sql = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1370                         _sql($sql, $errored, $error_ary);
1371                 break;
1372
1373                 case 'mssql':
1374                         $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
1375                         $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY  CLUSTERED (";
1376                         $sql .= '[' . implode("],\n\t\t[", $column) . ']';
1377                         $sql .= ') ON [PRIMARY]';
1378                         _sql($sql, $errored, $error_ary);
1379                 break;
1380
1381                 case 'mysql':
1382                         $sql = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1383                         _sql($sql, $errored, $error_ary);
1384                 break;
1385
1386                 case 'oracle':
1387                         $sql = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
1388                         _sql($sql, $errored, $error_ary);
1389                 break;
1390
1391                 case 'sqlite':
1392                         $sql = "SELECT sql
1393                                 FROM sqlite_master
1394                                 WHERE type = 'table'
1395                                         AND name = '{$table_name}'
1396                                 ORDER BY type DESC, name;";
1397                         $result = _sql($sql, $errored, $error_ary);
1398
1399                         if (!$result)
1400                         {
1401                                 break;
1402                         }
1403
1404                         $row = $db->sql_fetchrow($result);
1405                         $db->sql_freeresult($result);
1406
1407                         $db->sql_transaction('begin');
1408
1409                         // Create a backup table and populate it, destroy the existing one
1410                         $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
1411                         $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
1412                         $db->sql_query('DROP TABLE ' . $table_name);
1413
1414                         preg_match('#\((.*)\)#s', $row['sql'], $matches);
1415
1416                         $new_table_cols = trim($matches[1]);
1417                         $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1418                         $column_list = array();
1419
1420                         foreach ($old_table_cols as $declaration)
1421                         {
1422                                 $entities = preg_split('#\s+#', trim($declaration));
1423                                 if ($entities[0] == 'PRIMARY')
1424                                 {
1425                                         continue;
1426                                 }
1427                                 $column_list[] = $entities[0];
1428                         }
1429
1430                         $columns = implode(',', $column_list);
1431
1432                         // create a new table and fill it up. destroy the temp one
1433                         $db->sql_query('CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));');
1434                         $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
1435                         $db->sql_query('DROP TABLE ' . $table_name . '_temp');
1436
1437                         $db->sql_transaction('commit');
1438                 break;
1439         }
1440 }
1441
1442 function sql_create_unique_index($dbms, $index_name, $table_name, $column)
1443 {
1444         global $dbms_type_map, $db;
1445         global $errored, $error_ary;
1446
1447         switch ($dbms)
1448         {
1449                 case 'firebird':
1450                 case 'postgres':
1451                 case 'oracle':
1452                 case 'sqlite':
1453                         $sql = 'CREATE UNIQUE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1454                         _sql($sql, $errored, $error_ary);
1455                 break;
1456
1457                 case 'mysql':
1458                         $sql = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1459                         _sql($sql, $errored, $error_ary);
1460                 break;
1461
1462                 case 'mssql':
1463                         $sql = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1464                         _sql($sql, $errored, $error_ary);
1465                 break;
1466         }
1467 }
1468
1469 function sql_create_index($dbms, $index_name, $table_name, $column)
1470 {
1471         global $dbms_type_map, $db;
1472         global $errored, $error_ary;
1473
1474         switch ($dbms)
1475         {
1476                 case 'firebird':
1477                 case 'postgres':
1478                 case 'oracle':
1479                 case 'sqlite':
1480                         $sql = 'CREATE INDEX ' . $table_name . '_' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1481                         _sql($sql, $errored, $error_ary);
1482                 break;
1483
1484                 case 'mysql':
1485                         $sql = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1486                         _sql($sql, $errored, $error_ary);
1487                 break;
1488
1489                 case 'mssql':
1490                         $sql = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
1491                         _sql($sql, $errored, $error_ary);
1492                 break;
1493         }
1494 }
1495
1496 // List all of the indices that belong to a table,
1497 // does not count:
1498 // * UNIQUE indices
1499 // * PRIMARY keys
1500 function sql_list_index($dbms, $table_name)
1501 {
1502         global $dbms_type_map, $db;
1503         global $errored, $error_ary;
1504
1505         $index_array = array();
1506
1507         if ($dbms == 'mssql')
1508         {
1509                 $sql = "EXEC sp_statistics '$table_name'";
1510                 $result = $db->sql_query($sql);
1511                 while ($row = $db->sql_fetchrow($result))
1512                 {
1513                         if ($row['TYPE'] == 3)
1514                         {
1515                                 $index_array[] = $row['INDEX_NAME'];
1516                         }
1517                 }
1518                 $db->sql_freeresult($result);
1519         }
1520         else
1521         {
1522                 switch ($dbms)
1523                 {
1524                         case 'firebird':
1525                                 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
1526                                         FROM RDB\$INDICES
1527                                         WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
1528                                                 AND RDB\$UNIQUE_FLAG IS NULL
1529                                                 AND RDB\$FOREIGN_KEY IS NULL";
1530                                 $col = 'index_name';
1531                         break;
1532
1533                         case 'postgres':
1534                                 $sql = "SELECT ic.relname as index_name
1535                                         FROM pg_class bc, pg_class ic, pg_index i
1536                                         WHERE (bc.oid = i.indrelid)
1537                                                 AND (ic.oid = i.indexrelid)
1538                                                 AND (bc.relname = '" . $table_name . "')
1539                                                 AND (i.indisunique != 't')
1540                                                 AND (i.indisprimary != 't')";
1541                                 $col = 'index_name';
1542                         break;
1543
1544                         case 'mysql':
1545                                 $sql = 'SHOW KEYS
1546                                         FROM ' . $table_name;
1547                                 $col = 'Key_name';
1548                         break;
1549
1550                         case 'oracle':
1551                                 $sql = "SELECT index_name
1552                                         FROM user_indexes
1553                                         WHERE table_name = '" . $table_name . "'
1554                                                 AND generated = 'N'";
1555                         break;
1556
1557                         case 'sqlite':
1558                                 $sql = "PRAGMA index_info('" . $table_name . "');";
1559                                 $col = 'name';
1560                         break;
1561                 }
1562
1563                 $result = $db->sql_query($sql);
1564                 while ($row = $db->sql_fetchrow($result))
1565                 {
1566                         if ($dbms == 'mysql' && !$row['Non_unique'])
1567                         {
1568                                 continue;
1569                         }
1570
1571                         switch ($dbms)
1572                         {
1573                                 case 'firebird':
1574                                 case 'oracle':
1575                                 case 'postgres':
1576                                 case 'sqlite':
1577                                         $row[$col] = substr($row[$col], strlen($table_name) + 1);
1578                                 break;
1579                         }
1580
1581                         $index_array[] = $row[$col];
1582                 }
1583                 $db->sql_freeresult($result);
1584         }
1585
1586         return array_map('strtolower', $index_array);
1587 }
1588
1589 // This is totally fake, never use it
1590 // it exists only to mend bad update functions introduced
1591 // * UNIQUE indices
1592 // * PRIMARY keys
1593 function sql_list_fake($dbms, $table_name)
1594 {
1595         global $dbms_type_map, $db;
1596         global $errored, $error_ary;
1597
1598         $index_array = array();
1599
1600         if ($dbms == 'mssql')
1601         {
1602                 $sql = "EXEC sp_statistics '$table_name'";
1603                 $result = $db->sql_query($sql);
1604                 while ($row = $db->sql_fetchrow($result))
1605                 {
1606                         if ($row['TYPE'] == 3)
1607                         {
1608                                 $index_array[] = $row['INDEX_NAME'];
1609                         }
1610                 }
1611                 $db->sql_freeresult($result);
1612         }
1613         else
1614         {
1615                 switch ($dbms)
1616                 {
1617                         case 'firebird':
1618                                 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name
1619                                         FROM RDB\$INDICES
1620                                         WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "
1621                                                 AND RDB\$UNIQUE_FLAG IS NULL
1622                                                 AND RDB\$FOREIGN_KEY IS NULL";
1623                                 $col = 'index_name';
1624                         break;
1625
1626                         case 'postgres':
1627                                 $sql = "SELECT ic.relname as index_name
1628                                         FROM pg_class bc, pg_class ic, pg_index i
1629                                         WHERE (bc.oid = i.indrelid)
1630                                                 AND (ic.oid = i.indexrelid)
1631                                                 AND (bc.relname = '" . $table_name . "')
1632                                                 AND (i.indisunique != 't')
1633                                                 AND (i.indisprimary != 't')";
1634                                 $col = 'index_name';
1635                         break;
1636
1637                         case 'mysql':
1638                                 $sql = 'SHOW KEYS
1639                                         FROM ' . $table_name;
1640                                 $col = 'Key_name';
1641                         break;
1642
1643                         case 'oracle':
1644                                 $sql = "SELECT index_name
1645                                         FROM user_indexes
1646                                         WHERE table_name = '" . $table_name . "'
1647                                                 AND generated = 'N'";
1648                         break;
1649
1650                         case 'sqlite':
1651                                 $sql = "PRAGMA index_info('" . $table_name . "');";
1652                                 $col = 'name';
1653                         break;
1654                 }
1655
1656                 $result = $db->sql_query($sql);
1657                 while ($row = $db->sql_fetchrow($result))
1658                 {
1659                         if ($dbms == 'mysql' && !$row['Non_unique'])
1660                         {
1661                                 continue;
1662                         }
1663
1664                         $index_array[] = $row[$col];
1665                 }
1666                 $db->sql_freeresult($result);
1667         }
1668
1669         return array_map('strtolower', $index_array);
1670 }
1671
1672 /**
1673 * Change column type (not name!)
1674 */
1675 function sql_column_change($dbms, $table_name, $column_name, $column_data)
1676 {
1677         global $dbms_type_map, $db;
1678         global $errored, $error_ary;
1679
1680         $column_data = prepare_column_data($dbms, $column_data, $table_name, $column_name);
1681
1682         switch ($dbms)
1683         {
1684                 case 'firebird':
1685                         // Change type...
1686                         $sql = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql'];
1687                         _sql($sql, $errored, $error_ary);
1688                 break;
1689
1690                 case 'mssql':
1691                         $sql = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
1692                         _sql($sql, $errored, $error_ary);
1693                 break;
1694
1695                 case 'mysql':
1696                         $sql = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
1697                         _sql($sql, $errored, $error_ary);
1698                 break;
1699
1700                 case 'oracle':
1701                         $sql = 'ALTER TABLE ' . $table_name . ' MODIFY ' . $column_name . ' ' . $column_data['column_type_sql'];
1702                         _sql($sql, $errored, $error_ary);
1703                 break;
1704
1705                 case 'postgres':
1706                         $sql = 'ALTER TABLE ' . $table_name . ' ';
1707
1708                         $sql_array = array();
1709                         $sql_array[] = 'ALTER COLUMN ' . $column_name . ' TYPE ' . $column_data['column_type'];
1710
1711                         if (isset($column_data['null']))
1712                         {
1713                                 if ($column_data['null'] == 'NOT NULL')
1714                                 {
1715                                         $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET NOT NULL';
1716                                 }
1717                                 else if ($column_data['null'] == 'NULL')
1718                                 {
1719                                         $sql_array[] = 'ALTER COLUMN ' . $column_name . ' DROP NOT NULL';
1720                                 }
1721                         }
1722
1723                         if (isset($column_data['default']))
1724                         {
1725                                 $sql_array[] = 'ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default'];
1726                         }
1727
1728                         // we don't want to double up on constraints if we change different number data types
1729                         if (isset($column_data['constraint']))
1730                         {
1731                                 $constraint_sql = "SELECT consrc as constraint_data
1732                                                         FROM pg_constraint, pg_class bc
1733                                                         WHERE conrelid = bc.oid
1734                                                                 AND bc.relname = '{$table_name}'
1735                                                                 AND NOT EXISTS (
1736                                                                         SELECT *
1737                                                                                 FROM pg_constraint as c, pg_inherits as i
1738                                                                                 WHERE i.inhrelid = pg_constraint.conrelid
1739                                                                                         AND c.conname = pg_constraint.conname
1740                                                                                         AND c.consrc = pg_constraint.consrc
1741                                                                                         AND c.conrelid = i.inhparent
1742                                                                 )";
1743
1744                                 $constraint_exists = false;
1745
1746                                 $result = $db->sql_query($constraint_sql);
1747                                 while ($row = $db->sql_fetchrow($result))
1748                                 {
1749                                         if (trim($row['constraint_data']) == trim($column_data['constraint']))
1750                                         {
1751                                                 $constraint_exists = true;
1752                                                 break;
1753                                         }
1754                                 }
1755                                 $db->sql_freeresult($result);
1756
1757                                 if (!$constraint_exists)
1758                                 {
1759                                         $sql_array[] = 'ADD ' . $column_data['constraint'];
1760                                 }
1761                         }
1762
1763                         $sql .= implode(', ', $sql_array);
1764
1765                         _sql($sql, $errored, $error_ary);
1766                 break;
1767
1768                 case 'sqlite':
1769
1770                         $sql = "SELECT sql
1771                                 FROM sqlite_master
1772                                 WHERE type = 'table'
1773                                         AND name = '{$table_name}'
1774                                 ORDER BY type DESC, name;";
1775                         $result = _sql($sql, $errored, $error_ary);
1776
1777                         if (!$result)
1778                         {
1779                                 break;
1780                         }
1781
1782                         $row = $db->sql_fetchrow($result);
1783                         $db->sql_freeresult($result);
1784
1785                         $db->sql_transaction('begin');
1786
1787                         // Create a temp table and populate it, destroy the existing one
1788                         $db->sql_query(preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']));
1789                         $db->sql_query('INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name);
1790                         $db->sql_query('DROP TABLE ' . $table_name);
1791
1792                         preg_match('#\((.*)\)#s', $row['sql'], $matches);
1793
1794                         $new_table_cols = trim($matches[1]);
1795                         $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1796                         $column_list = array();
1797
1798                         foreach ($old_table_cols as $key => $declaration)
1799                         {
1800                                 $entities = preg_split('#\s+#', trim($declaration));
1801                                 $column_list[] = $entities[0];
1802                                 if ($entities[0] == $column_name)
1803                                 {
1804                                         $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
1805                                 }
1806                         }
1807
1808                         $columns = implode(',', $column_list);
1809
1810                         // create a new table and fill it up. destroy the temp one
1811                         $db->sql_query('CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');');
1812                         $db->sql_query('INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;');
1813                         $db->sql_query('DROP TABLE ' . $table_name . '_temp');
1814
1815                         $db->sql_transaction('commit');
1816
1817                 break;
1818         }
1819 }
1820
1821 ?>