[ Index ]

PHP Cross Reference of phpBB-3.3.0-deutsch

title

Body

[close]

/phpbb/db/tools/ -> tools.php (source)

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  namespace phpbb\db\tools;
  15  
  16  /**
  17  * Database Tools for handling cross-db actions such as altering columns, etc.
  18  * Currently not supported is returning SQL for creating tables.
  19  */
  20  class tools implements tools_interface
  21  {
  22      /**
  23      * Current sql layer
  24      */
  25      var $sql_layer = '';
  26  
  27      /**
  28      * @var object DB object
  29      */
  30      var $db = null;
  31  
  32      /**
  33      * The Column types for every database we support
  34      * @var array
  35      */
  36      var $dbms_type_map = array();
  37  
  38      /**
  39      * Get the column types for every database we support
  40      *
  41      * @return array
  42      */
  43  	static public function get_dbms_type_map()
  44      {
  45          return array(
  46              'mysql_41'    => array(
  47                  'INT:'        => 'int(%d)',
  48                  'BINT'        => 'bigint(20)',
  49                  'ULINT'        => 'INT(10) UNSIGNED',
  50                  'UINT'        => 'mediumint(8) UNSIGNED',
  51                  'UINT:'        => 'int(%d) UNSIGNED',
  52                  'TINT:'        => 'tinyint(%d)',
  53                  'USINT'        => 'smallint(4) UNSIGNED',
  54                  'BOOL'        => 'tinyint(1) UNSIGNED',
  55                  'VCHAR'        => 'varchar(255)',
  56                  'VCHAR:'    => 'varchar(%d)',
  57                  'CHAR:'        => 'char(%d)',
  58                  'XSTEXT'    => 'text',
  59                  'XSTEXT_UNI'=> 'varchar(100)',
  60                  'STEXT'        => 'text',
  61                  'STEXT_UNI'    => 'varchar(255)',
  62                  'TEXT'        => 'text',
  63                  'TEXT_UNI'    => 'text',
  64                  'MTEXT'        => 'mediumtext',
  65                  'MTEXT_UNI'    => 'mediumtext',
  66                  'TIMESTAMP'    => 'int(11) UNSIGNED',
  67                  'DECIMAL'    => 'decimal(5,2)',
  68                  'DECIMAL:'    => 'decimal(%d,2)',
  69                  'PDECIMAL'    => 'decimal(6,3)',
  70                  'PDECIMAL:'    => 'decimal(%d,3)',
  71                  'VCHAR_UNI'    => 'varchar(255)',
  72                  'VCHAR_UNI:'=> 'varchar(%d)',
  73                  'VCHAR_CI'    => 'varchar(255)',
  74                  'VARBINARY'    => 'varbinary(255)',
  75              ),
  76  
  77              'oracle'    => array(
  78                  'INT:'        => 'number(%d)',
  79                  'BINT'        => 'number(20)',
  80                  'ULINT'        => 'number(10)',
  81                  'UINT'        => 'number(8)',
  82                  'UINT:'        => 'number(%d)',
  83                  'TINT:'        => 'number(%d)',
  84                  'USINT'        => 'number(4)',
  85                  'BOOL'        => 'number(1)',
  86                  'VCHAR'        => 'varchar2(255)',
  87                  'VCHAR:'    => 'varchar2(%d)',
  88                  'CHAR:'        => 'char(%d)',
  89                  'XSTEXT'    => 'varchar2(1000)',
  90                  'STEXT'        => 'varchar2(3000)',
  91                  'TEXT'        => 'clob',
  92                  'MTEXT'        => 'clob',
  93                  'XSTEXT_UNI'=> 'varchar2(300)',
  94                  'STEXT_UNI'    => 'varchar2(765)',
  95                  'TEXT_UNI'    => 'clob',
  96                  'MTEXT_UNI'    => 'clob',
  97                  'TIMESTAMP'    => 'number(11)',
  98                  'DECIMAL'    => 'number(5, 2)',
  99                  'DECIMAL:'    => 'number(%d, 2)',
 100                  'PDECIMAL'    => 'number(6, 3)',
 101                  'PDECIMAL:'    => 'number(%d, 3)',
 102                  'VCHAR_UNI'    => 'varchar2(765)',
 103                  'VCHAR_UNI:'=> array('varchar2(%d)', 'limit' => array('mult', 3, 765, 'clob')),
 104                  'VCHAR_CI'    => 'varchar2(255)',
 105                  'VARBINARY'    => 'raw(255)',
 106              ),
 107  
 108              'sqlite3'    => array(
 109                  'INT:'        => 'INT(%d)',
 110                  'BINT'        => 'BIGINT(20)',
 111                  'ULINT'        => 'INTEGER UNSIGNED',
 112                  'UINT'        => 'INTEGER UNSIGNED',
 113                  'UINT:'        => 'INTEGER UNSIGNED',
 114                  'TINT:'        => 'TINYINT(%d)',
 115                  'USINT'        => 'INTEGER UNSIGNED',
 116                  'BOOL'        => 'INTEGER UNSIGNED',
 117                  'VCHAR'        => 'VARCHAR(255)',
 118                  'VCHAR:'    => 'VARCHAR(%d)',
 119                  'CHAR:'        => 'CHAR(%d)',
 120                  'XSTEXT'    => 'TEXT(65535)',
 121                  'STEXT'        => 'TEXT(65535)',
 122                  'TEXT'        => 'TEXT(65535)',
 123                  'MTEXT'        => 'MEDIUMTEXT(16777215)',
 124                  'XSTEXT_UNI'=> 'TEXT(65535)',
 125                  'STEXT_UNI'    => 'TEXT(65535)',
 126                  'TEXT_UNI'    => 'TEXT(65535)',
 127                  'MTEXT_UNI'    => 'MEDIUMTEXT(16777215)',
 128                  'TIMESTAMP'    => 'INTEGER UNSIGNED', //'int(11) UNSIGNED',
 129                  'DECIMAL'    => 'DECIMAL(5,2)',
 130                  'DECIMAL:'    => 'DECIMAL(%d,2)',
 131                  'PDECIMAL'    => 'DECIMAL(6,3)',
 132                  'PDECIMAL:'    => 'DECIMAL(%d,3)',
 133                  'VCHAR_UNI'    => 'VARCHAR(255)',
 134                  'VCHAR_UNI:'=> 'VARCHAR(%d)',
 135                  'VCHAR_CI'    => 'VARCHAR(255)',
 136                  'VARBINARY'    => 'BLOB',
 137              ),
 138          );
 139      }
 140  
 141      /**
 142      * A list of types being unsigned for better reference in some db's
 143      * @var array
 144      */
 145      var $unsigned_types = array('ULINT', 'UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP');
 146  
 147      /**
 148      * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
 149      * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command.
 150      */
 151      var $return_statements = false;
 152  
 153      /**
 154      * Constructor. Set DB Object and set {@link $return_statements return_statements}.
 155      *
 156      * @param \phpbb\db\driver\driver_interface    $db                    Database connection
 157      * @param bool        $return_statements    True if only statements should be returned and no SQL being executed
 158      */
 159  	public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
 160      {
 161          $this->db = $db;
 162          $this->return_statements = $return_statements;
 163  
 164          $this->dbms_type_map = self::get_dbms_type_map();
 165  
 166          // Determine mapping database type
 167          switch ($this->db->get_sql_layer())
 168          {
 169              case 'mysqli':
 170                  $this->sql_layer = 'mysql_41';
 171              break;
 172  
 173              default:
 174                  $this->sql_layer = $this->db->get_sql_layer();
 175              break;
 176          }
 177      }
 178  
 179      /**
 180      * Setter for {@link $return_statements return_statements}.
 181      *
 182      * @param bool $return_statements True if SQL should not be executed but returned as strings
 183      * @return null
 184      */
 185  	public function set_return_statements($return_statements)
 186      {
 187          $this->return_statements = $return_statements;
 188      }
 189  
 190      /**
 191       * {@inheritDoc}
 192       */
 193  	function sql_list_tables()
 194      {
 195          switch ($this->db->get_sql_layer())
 196          {
 197              case 'mysqli':
 198                  $sql = 'SHOW TABLES';
 199              break;
 200  
 201              case 'sqlite3':
 202                  $sql = 'SELECT name
 203                      FROM sqlite_master
 204                      WHERE type = "table"
 205                          AND name <> "sqlite_sequence"';
 206              break;
 207  
 208              case 'oracle':
 209                  $sql = 'SELECT table_name
 210                      FROM USER_TABLES';
 211              break;
 212          }
 213  
 214          $result = $this->db->sql_query($sql);
 215  
 216          $tables = array();
 217          while ($row = $this->db->sql_fetchrow($result))
 218          {
 219              $name = current($row);
 220              $tables[$name] = $name;
 221          }
 222          $this->db->sql_freeresult($result);
 223  
 224          return $tables;
 225      }
 226  
 227      /**
 228       * {@inheritDoc}
 229       */
 230  	function sql_table_exists($table_name)
 231      {
 232          $this->db->sql_return_on_error(true);
 233          $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1);
 234          $this->db->sql_return_on_error(false);
 235  
 236          if ($result)
 237          {
 238              $this->db->sql_freeresult($result);
 239              return true;
 240          }
 241  
 242          return false;
 243      }
 244  
 245      /**
 246       * {@inheritDoc}
 247       */
 248  	function sql_create_table($table_name, $table_data)
 249      {
 250          // holds the DDL for a column
 251          $columns = $statements = array();
 252  
 253          if ($this->sql_table_exists($table_name))
 254          {
 255              return $this->_sql_run_sql($statements);
 256          }
 257  
 258          // Begin transaction
 259          $statements[] = 'begin';
 260  
 261          // Determine if we have created a PRIMARY KEY in the earliest
 262          $primary_key_gen = false;
 263  
 264          // Determine if the table requires a sequence
 265          $create_sequence = false;
 266  
 267          // Begin table sql statement
 268          $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n";
 269  
 270          // Iterate through the columns to create a table
 271          foreach ($table_data['COLUMNS'] as $column_name => $column_data)
 272          {
 273              // here lies an array, filled with information compiled on the column's data
 274              $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 275  
 276              if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "$column_name}_gen"
 277              {
 278                  trigger_error("Index name '$column_name}_gen' on table '$table_name' is too long. The maximum auto increment column length is 26 characters.", E_USER_ERROR);
 279              }
 280  
 281              // here we add the definition of the new column to the list of columns
 282              $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql'];
 283  
 284              // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
 285              if (!$primary_key_gen)
 286              {
 287                  $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
 288              }
 289  
 290              // create sequence DDL based off of the existence of auto incrementing columns
 291              if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
 292              {
 293                  $create_sequence = $column_name;
 294              }
 295          }
 296  
 297          // this makes up all the columns in the create table statement
 298          $table_sql .= implode(",\n", $columns);
 299  
 300          // we have yet to create a primary key for this table,
 301          // this means that we can add the one we really wanted instead
 302          if (!$primary_key_gen)
 303          {
 304              // Write primary key
 305              if (isset($table_data['PRIMARY_KEY']))
 306              {
 307                  if (!is_array($table_data['PRIMARY_KEY']))
 308                  {
 309                      $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
 310                  }
 311  
 312                  switch ($this->sql_layer)
 313                  {
 314                      case 'mysql_41':
 315                      case 'sqlite3':
 316                          $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
 317                      break;
 318  
 319                      case 'oracle':
 320                          $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')';
 321                      break;
 322                  }
 323              }
 324          }
 325  
 326          // close the table
 327          switch ($this->sql_layer)
 328          {
 329              case 'mysql_41':
 330                  // make sure the table is in UTF-8 mode
 331                  $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;";
 332                  $statements[] = $table_sql;
 333              break;
 334  
 335              case 'sqlite3':
 336                  $table_sql .= "\n);";
 337                  $statements[] = $table_sql;
 338              break;
 339  
 340              case 'oracle':
 341                  $table_sql .= "\n)";
 342                  $statements[] = $table_sql;
 343  
 344                  // do we need to add a sequence and a tigger for auto incrementing columns?
 345                  if ($create_sequence)
 346                  {
 347                      // create the actual sequence
 348                      $statements[] = "CREATE SEQUENCE {$table_name}_seq";
 349  
 350                      // the trigger is the mechanism by which we increment the counter
 351                      $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n";
 352                      $trigger .= "BEFORE INSERT ON {$table_name}\n";
 353                      $trigger .= "FOR EACH ROW WHEN (\n";
 354                      $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n";
 355                      $trigger .= ")\n";
 356                      $trigger .= "BEGIN\n";
 357                      $trigger .= "\tSELECT {$table_name}_seq.nextval\n";
 358                      $trigger .= "\tINTO :new.{$create_sequence}\n";
 359                      $trigger .= "\tFROM dual;\n";
 360                      $trigger .= "END;";
 361  
 362                      $statements[] = $trigger;
 363                  }
 364              break;
 365          }
 366  
 367          // Write Keys
 368          if (isset($table_data['KEYS']))
 369          {
 370              foreach ($table_data['KEYS'] as $key_name => $key_data)
 371              {
 372                  if (!is_array($key_data[1]))
 373                  {
 374                      $key_data[1] = array($key_data[1]);
 375                  }
 376  
 377                  $old_return_statements = $this->return_statements;
 378                  $this->return_statements = true;
 379  
 380                  $key_stmts = ($key_data[0] == 'UNIQUE') ? $this->sql_create_unique_index($table_name, $key_name, $key_data[1]) : $this->sql_create_index($table_name, $key_name, $key_data[1]);
 381  
 382                  foreach ($key_stmts as $key_stmt)
 383                  {
 384                      $statements[] = $key_stmt;
 385                  }
 386  
 387                  $this->return_statements = $old_return_statements;
 388              }
 389          }
 390  
 391          // Commit Transaction
 392          $statements[] = 'commit';
 393  
 394          return $this->_sql_run_sql($statements);
 395      }
 396  
 397      /**
 398       * {@inheritDoc}
 399       */
 400  	function perform_schema_changes($schema_changes)
 401      {
 402          if (empty($schema_changes))
 403          {
 404              return;
 405          }
 406  
 407          $statements = array();
 408          $sqlite = false;
 409  
 410          // For SQLite we need to perform the schema changes in a much more different way
 411          if ($this->db->get_sql_layer() == 'sqlite3' && $this->return_statements)
 412          {
 413              $sqlite_data = array();
 414              $sqlite = true;
 415          }
 416  
 417          // Drop tables?
 418          if (!empty($schema_changes['drop_tables']))
 419          {
 420              foreach ($schema_changes['drop_tables'] as $table)
 421              {
 422                  // only drop table if it exists
 423                  if ($this->sql_table_exists($table))
 424                  {
 425                      $result = $this->sql_table_drop($table);
 426                      if ($this->return_statements)
 427                      {
 428                          $statements = array_merge($statements, $result);
 429                      }
 430                  }
 431              }
 432          }
 433  
 434          // Add tables?
 435          if (!empty($schema_changes['add_tables']))
 436          {
 437              foreach ($schema_changes['add_tables'] as $table => $table_data)
 438              {
 439                  $result = $this->sql_create_table($table, $table_data);
 440                  if ($this->return_statements)
 441                  {
 442                      $statements = array_merge($statements, $result);
 443                  }
 444              }
 445          }
 446  
 447          // Change columns?
 448          if (!empty($schema_changes['change_columns']))
 449          {
 450              foreach ($schema_changes['change_columns'] as $table => $columns)
 451              {
 452                  foreach ($columns as $column_name => $column_data)
 453                  {
 454                      // If the column exists we change it, else we add it ;)
 455                      if ($column_exists = $this->sql_column_exists($table, $column_name))
 456                      {
 457                          $result = $this->sql_column_change($table, $column_name, $column_data, true);
 458                      }
 459                      else
 460                      {
 461                          $result = $this->sql_column_add($table, $column_name, $column_data, true);
 462                      }
 463  
 464                      if ($sqlite)
 465                      {
 466                          if ($column_exists)
 467                          {
 468                              $sqlite_data[$table]['change_columns'][] = $result;
 469                          }
 470                          else
 471                          {
 472                              $sqlite_data[$table]['add_columns'][] = $result;
 473                          }
 474                      }
 475                      else if ($this->return_statements)
 476                      {
 477                          $statements = array_merge($statements, $result);
 478                      }
 479                  }
 480              }
 481          }
 482  
 483          // Add columns?
 484          if (!empty($schema_changes['add_columns']))
 485          {
 486              foreach ($schema_changes['add_columns'] as $table => $columns)
 487              {
 488                  foreach ($columns as $column_name => $column_data)
 489                  {
 490                      // Only add the column if it does not exist yet
 491                      if ($column_exists = $this->sql_column_exists($table, $column_name))
 492                      {
 493                          continue;
 494                          // This is commented out here because it can take tremendous time on updates
 495  //                        $result = $this->sql_column_change($table, $column_name, $column_data, true);
 496                      }
 497                      else
 498                      {
 499                          $result = $this->sql_column_add($table, $column_name, $column_data, true);
 500                      }
 501  
 502                      if ($sqlite)
 503                      {
 504                          if ($column_exists)
 505                          {
 506                              continue;
 507  //                            $sqlite_data[$table]['change_columns'][] = $result;
 508                          }
 509                          else
 510                          {
 511                              $sqlite_data[$table]['add_columns'][] = $result;
 512                          }
 513                      }
 514                      else if ($this->return_statements)
 515                      {
 516                          $statements = array_merge($statements, $result);
 517                      }
 518                  }
 519              }
 520          }
 521  
 522          // Remove keys?
 523          if (!empty($schema_changes['drop_keys']))
 524          {
 525              foreach ($schema_changes['drop_keys'] as $table => $indexes)
 526              {
 527                  foreach ($indexes as $index_name)
 528                  {
 529                      if (!$this->sql_index_exists($table, $index_name) && !$this->sql_unique_index_exists($table, $index_name))
 530                      {
 531                          continue;
 532                      }
 533  
 534                      $result = $this->sql_index_drop($table, $index_name);
 535  
 536                      if ($this->return_statements)
 537                      {
 538                          $statements = array_merge($statements, $result);
 539                      }
 540                  }
 541              }
 542          }
 543  
 544          // Drop columns?
 545          if (!empty($schema_changes['drop_columns']))
 546          {
 547              foreach ($schema_changes['drop_columns'] as $table => $columns)
 548              {
 549                  foreach ($columns as $column)
 550                  {
 551                      // Only remove the column if it exists...
 552                      if ($this->sql_column_exists($table, $column))
 553                      {
 554                          $result = $this->sql_column_remove($table, $column, true);
 555  
 556                          if ($sqlite)
 557                          {
 558                              $sqlite_data[$table]['drop_columns'][] = $result;
 559                          }
 560                          else if ($this->return_statements)
 561                          {
 562                              $statements = array_merge($statements, $result);
 563                          }
 564                      }
 565                  }
 566              }
 567          }
 568  
 569          // Add primary keys?
 570          if (!empty($schema_changes['add_primary_keys']))
 571          {
 572              foreach ($schema_changes['add_primary_keys'] as $table => $columns)
 573              {
 574                  $result = $this->sql_create_primary_key($table, $columns, true);
 575  
 576                  if ($sqlite)
 577                  {
 578                      $sqlite_data[$table]['primary_key'] = $result;
 579                  }
 580                  else if ($this->return_statements)
 581                  {
 582                      $statements = array_merge($statements, $result);
 583                  }
 584              }
 585          }
 586  
 587          // Add unique indexes?
 588          if (!empty($schema_changes['add_unique_index']))
 589          {
 590              foreach ($schema_changes['add_unique_index'] as $table => $index_array)
 591              {
 592                  foreach ($index_array as $index_name => $column)
 593                  {
 594                      if ($this->sql_unique_index_exists($table, $index_name))
 595                      {
 596                          continue;
 597                      }
 598  
 599                      $result = $this->sql_create_unique_index($table, $index_name, $column);
 600  
 601                      if ($this->return_statements)
 602                      {
 603                          $statements = array_merge($statements, $result);
 604                      }
 605                  }
 606              }
 607          }
 608  
 609          // Add indexes?
 610          if (!empty($schema_changes['add_index']))
 611          {
 612              foreach ($schema_changes['add_index'] as $table => $index_array)
 613              {
 614                  foreach ($index_array as $index_name => $column)
 615                  {
 616                      if ($this->sql_index_exists($table, $index_name))
 617                      {
 618                          continue;
 619                      }
 620  
 621                      $result = $this->sql_create_index($table, $index_name, $column);
 622  
 623                      if ($this->return_statements)
 624                      {
 625                          $statements = array_merge($statements, $result);
 626                      }
 627                  }
 628              }
 629          }
 630  
 631          if ($sqlite)
 632          {
 633              foreach ($sqlite_data as $table_name => $sql_schema_changes)
 634              {
 635                  // Create temporary table with original data
 636                  $statements[] = 'begin';
 637  
 638                  $sql = "SELECT sql
 639                      FROM sqlite_master
 640                      WHERE type = 'table'
 641                          AND name = '{$table_name}'
 642                      ORDER BY type DESC, name;";
 643                  $result = $this->db->sql_query($sql);
 644  
 645                  if (!$result)
 646                  {
 647                      continue;
 648                  }
 649  
 650                  $row = $this->db->sql_fetchrow($result);
 651                  $this->db->sql_freeresult($result);
 652  
 653                  // Create a backup table and populate it, destroy the existing one
 654                  $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']);
 655                  $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
 656                  $statements[] = 'DROP TABLE ' . $table_name;
 657  
 658                  // Get the columns...
 659                  preg_match('#\((.*)\)#s', $row['sql'], $matches);
 660  
 661                  $plain_table_cols = trim($matches[1]);
 662                  $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols);
 663                  $column_list = array();
 664  
 665                  foreach ($new_table_cols as $declaration)
 666                  {
 667                      $entities = preg_split('#\s+#', trim($declaration));
 668                      if ($entities[0] == 'PRIMARY')
 669                      {
 670                          continue;
 671                      }
 672                      $column_list[] = $entities[0];
 673                  }
 674  
 675                  // note down the primary key notation because sqlite only supports adding it to the end for the new table
 676                  $primary_key = false;
 677                  $_new_cols = array();
 678  
 679                  foreach ($new_table_cols as $key => $declaration)
 680                  {
 681                      $entities = preg_split('#\s+#', trim($declaration));
 682                      if ($entities[0] == 'PRIMARY')
 683                      {
 684                          $primary_key = $declaration;
 685                          continue;
 686                      }
 687                      $_new_cols[] = $declaration;
 688                  }
 689  
 690                  $new_table_cols = $_new_cols;
 691  
 692                  // First of all... change columns
 693                  if (!empty($sql_schema_changes['change_columns']))
 694                  {
 695                      foreach ($sql_schema_changes['change_columns'] as $column_sql)
 696                      {
 697                          foreach ($new_table_cols as $key => $declaration)
 698                          {
 699                              $entities = preg_split('#\s+#', trim($declaration));
 700                              if (strpos($column_sql, $entities[0] . ' ') === 0)
 701                              {
 702                                  $new_table_cols[$key] = $column_sql;
 703                              }
 704                          }
 705                      }
 706                  }
 707  
 708                  if (!empty($sql_schema_changes['add_columns']))
 709                  {
 710                      foreach ($sql_schema_changes['add_columns'] as $column_sql)
 711                      {
 712                          $new_table_cols[] = $column_sql;
 713                      }
 714                  }
 715  
 716                  // Now drop them...
 717                  if (!empty($sql_schema_changes['drop_columns']))
 718                  {
 719                      foreach ($sql_schema_changes['drop_columns'] as $column_name)
 720                      {
 721                          // Remove from column list...
 722                          $new_column_list = array();
 723                          foreach ($column_list as $key => $value)
 724                          {
 725                              if ($value === $column_name)
 726                              {
 727                                  continue;
 728                              }
 729  
 730                              $new_column_list[] = $value;
 731                          }
 732  
 733                          $column_list = $new_column_list;
 734  
 735                          // Remove from table...
 736                          $_new_cols = array();
 737                          foreach ($new_table_cols as $key => $declaration)
 738                          {
 739                              $entities = preg_split('#\s+#', trim($declaration));
 740                              if (strpos($column_name . ' ', $entities[0] . ' ') === 0)
 741                              {
 742                                  continue;
 743                              }
 744                              $_new_cols[] = $declaration;
 745                          }
 746                          $new_table_cols = $_new_cols;
 747                      }
 748                  }
 749  
 750                  // Primary key...
 751                  if (!empty($sql_schema_changes['primary_key']))
 752                  {
 753                      $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')';
 754                  }
 755                  // Add a new one or the old primary key
 756                  else if ($primary_key !== false)
 757                  {
 758                      $new_table_cols[] = $primary_key;
 759                  }
 760  
 761                  $columns = implode(',', $column_list);
 762  
 763                  // create a new table and fill it up. destroy the temp one
 764                  $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');';
 765                  $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
 766                  $statements[] = 'DROP TABLE ' . $table_name . '_temp';
 767  
 768                  $statements[] = 'commit';
 769              }
 770          }
 771  
 772          if ($this->return_statements)
 773          {
 774              return $statements;
 775          }
 776      }
 777  
 778      /**
 779       * {@inheritDoc}
 780       */
 781  	function sql_list_columns($table_name)
 782      {
 783          $columns = array();
 784  
 785          switch ($this->sql_layer)
 786          {
 787              case 'mysql_41':
 788                  $sql = "SHOW COLUMNS FROM $table_name";
 789              break;
 790  
 791              case 'oracle':
 792                  $sql = "SELECT column_name
 793                      FROM user_tab_columns
 794                      WHERE LOWER(table_name) = '" . strtolower($table_name) . "'";
 795              break;
 796  
 797              case 'sqlite3':
 798                  $sql = "SELECT sql
 799                      FROM sqlite_master
 800                      WHERE type = 'table'
 801                          AND name = '{$table_name}'";
 802  
 803                  $result = $this->db->sql_query($sql);
 804  
 805                  if (!$result)
 806                  {
 807                      return false;
 808                  }
 809  
 810                  $row = $this->db->sql_fetchrow($result);
 811                  $this->db->sql_freeresult($result);
 812  
 813                  preg_match('#\((.*)\)#s', $row['sql'], $matches);
 814  
 815                  $cols = trim($matches[1]);
 816                  $col_array = preg_split('/,(?![\s\w]+\))/m', $cols);
 817  
 818                  foreach ($col_array as $declaration)
 819                  {
 820                      $entities = preg_split('#\s+#', trim($declaration));
 821                      if ($entities[0] == 'PRIMARY')
 822                      {
 823                          continue;
 824                      }
 825  
 826                      $column = strtolower($entities[0]);
 827                      $columns[$column] = $column;
 828                  }
 829  
 830                  return $columns;
 831              break;
 832          }
 833  
 834          $result = $this->db->sql_query($sql);
 835  
 836          while ($row = $this->db->sql_fetchrow($result))
 837          {
 838              $column = strtolower(current($row));
 839              $columns[$column] = $column;
 840          }
 841          $this->db->sql_freeresult($result);
 842  
 843          return $columns;
 844      }
 845  
 846      /**
 847       * {@inheritDoc}
 848       */
 849  	function sql_column_exists($table_name, $column_name)
 850      {
 851          $columns = $this->sql_list_columns($table_name);
 852  
 853          return isset($columns[$column_name]);
 854      }
 855  
 856      /**
 857       * {@inheritDoc}
 858       */
 859  	function sql_index_exists($table_name, $index_name)
 860      {
 861          switch ($this->sql_layer)
 862          {
 863              case 'mysql_41':
 864                  $sql = 'SHOW KEYS
 865                      FROM ' . $table_name;
 866                  $col = 'Key_name';
 867              break;
 868  
 869              case 'oracle':
 870                  $sql = "SELECT index_name
 871                      FROM user_indexes
 872                      WHERE table_name = '" . strtoupper($table_name) . "'
 873                          AND generated = 'N'
 874                          AND uniqueness = 'NONUNIQUE'";
 875                  $col = 'index_name';
 876              break;
 877  
 878              case 'sqlite3':
 879                  $sql = "PRAGMA index_list('" . $table_name . "');";
 880                  $col = 'name';
 881              break;
 882          }
 883  
 884          $result = $this->db->sql_query($sql);
 885          while ($row = $this->db->sql_fetchrow($result))
 886          {
 887              if ($this->sql_layer == 'mysql_41' && !$row['Non_unique'])
 888              {
 889                  continue;
 890              }
 891  
 892              switch ($this->sql_layer)
 893              {
 894                  // These DBMS prefix index name with the table name
 895                  case 'oracle':
 896                  case 'sqlite3':
 897                      $new_index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name, false);
 898                  break;
 899                  default:
 900                      $new_index_name = $this->check_index_name_length($table_name, $index_name, false);
 901                  break;
 902              }
 903  
 904              if (strtolower($row[$col]) == strtolower($new_index_name))
 905              {
 906                  $this->db->sql_freeresult($result);
 907                  return true;
 908              }
 909          }
 910          $this->db->sql_freeresult($result);
 911  
 912          return false;
 913      }
 914  
 915      /**
 916       * {@inheritDoc}
 917       */
 918  	function sql_unique_index_exists($table_name, $index_name)
 919      {
 920          switch ($this->sql_layer)
 921          {
 922              case 'mysql_41':
 923                  $sql = 'SHOW KEYS
 924                      FROM ' . $table_name;
 925                  $col = 'Key_name';
 926              break;
 927  
 928              case 'oracle':
 929                  $sql = "SELECT index_name, table_owner
 930                      FROM user_indexes
 931                      WHERE table_name = '" . strtoupper($table_name) . "'
 932                          AND generated = 'N'
 933                          AND uniqueness = 'UNIQUE'";
 934                  $col = 'index_name';
 935              break;
 936  
 937              case 'sqlite3':
 938                  $sql = "PRAGMA index_list('" . $table_name . "');";
 939                  $col = 'name';
 940              break;
 941          }
 942  
 943          $result = $this->db->sql_query($sql);
 944          while ($row = $this->db->sql_fetchrow($result))
 945          {
 946              if ($this->sql_layer == 'mysql_41' && ($row['Non_unique'] || $row[$col] == 'PRIMARY'))
 947              {
 948                  continue;
 949              }
 950  
 951              if ($this->sql_layer == 'sqlite3' && !$row['unique'])
 952              {
 953                  continue;
 954              }
 955  
 956              // These DBMS prefix index name with the table name
 957              switch ($this->sql_layer)
 958              {
 959                  case 'oracle':
 960                      // Two cases here... prefixed with U_[table_owner] and not prefixed with table_name
 961                      if (strpos($row[$col], 'U_') === 0)
 962                      {
 963                          $row[$col] = substr($row[$col], strlen('U_' . $row['table_owner']) + 1);
 964                      }
 965                      else if (strpos($row[$col], strtoupper($table_name)) === 0)
 966                      {
 967                          $row[$col] = substr($row[$col], strlen($table_name) + 1);
 968                      }
 969                  break;
 970  
 971                  case 'sqlite3':
 972                      $row[$col] = substr($row[$col], strlen($table_name) + 1);
 973                  break;
 974              }
 975  
 976              if (strtolower($row[$col]) == strtolower($index_name))
 977              {
 978                  $this->db->sql_freeresult($result);
 979                  return true;
 980              }
 981          }
 982          $this->db->sql_freeresult($result);
 983  
 984          return false;
 985      }
 986  
 987      /**
 988      * Private method for performing sql statements (either execute them or return them)
 989      * @access private
 990      */
 991  	function _sql_run_sql($statements)
 992      {
 993          if ($this->return_statements)
 994          {
 995              return $statements;
 996          }
 997  
 998          // We could add error handling here...
 999          foreach ($statements as $sql)
1000          {
1001              if ($sql === 'begin')
1002              {
1003                  $this->db->sql_transaction('begin');
1004              }
1005              else if ($sql === 'commit')
1006              {
1007                  $this->db->sql_transaction('commit');
1008              }
1009              else
1010              {
1011                  $this->db->sql_query($sql);
1012              }
1013          }
1014  
1015          return true;
1016      }
1017  
1018      /**
1019      * Function to prepare some column information for better usage
1020      * @access private
1021      */
1022  	function sql_prepare_column_data($table_name, $column_name, $column_data)
1023      {
1024          if (strlen($column_name) > 30)
1025          {
1026              trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
1027          }
1028  
1029          // Get type
1030          list($column_type) = $this->get_column_type($column_data[0]);
1031  
1032          // Adjust default value if db-dependent specified
1033          if (is_array($column_data[1]))
1034          {
1035              $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
1036          }
1037  
1038          $sql = '';
1039  
1040          $return_array = array();
1041  
1042          switch ($this->sql_layer)
1043          {
1044              case 'mysql_41':
1045                  $sql .= " {$column_type} ";
1046  
1047                  // For hexadecimal values do not use single quotes
1048                  if (!is_null($column_data[1]) && substr($column_type, -4) !== 'text' && substr($column_type, -4) !== 'blob')
1049                  {
1050                      $sql .= (strpos($column_data[1], '0x') === 0) ? "DEFAULT {$column_data[1]} " : "DEFAULT '{$column_data[1]}' ";
1051                  }
1052  
1053                  if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment'))
1054                  {
1055                      $sql .= 'NOT NULL';
1056                  }
1057                  else
1058                  {
1059                      $sql .= 'NULL';
1060                  }
1061  
1062                  if (isset($column_data[2]))
1063                  {
1064                      if ($column_data[2] == 'auto_increment')
1065                      {
1066                          $sql .= ' auto_increment';
1067                      }
1068                      else if ($this->sql_layer === 'mysql_41' && $column_data[2] == 'true_sort')
1069                      {
1070                          $sql .= ' COLLATE utf8_unicode_ci';
1071                      }
1072                  }
1073  
1074                  if (isset($column_data['after']))
1075                  {
1076                      $return_array['after'] = $column_data['after'];
1077                  }
1078  
1079              break;
1080  
1081              case 'oracle':
1082                  $sql .= " {$column_type} ";
1083                  $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}' " : '';
1084  
1085                  // In Oracle empty strings ('') are treated as NULL.
1086                  // Therefore in oracle we allow NULL's for all DEFAULT '' entries
1087                  // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields)
1088                  if (!preg_match('/number/i', $column_type))
1089                  {
1090                      $sql .= ($column_data[1] === '' || $column_data[1] === null) ? '' : 'NOT NULL';
1091                  }
1092  
1093                  $return_array['auto_increment'] = false;
1094                  if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1095                  {
1096                      $return_array['auto_increment'] = true;
1097                  }
1098  
1099              break;
1100  
1101              case 'sqlite3':
1102                  $return_array['primary_key_set'] = false;
1103                  if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
1104                  {
1105                      $sql .= ' INTEGER PRIMARY KEY AUTOINCREMENT';
1106                      $return_array['primary_key_set'] = true;
1107                  }
1108                  else
1109                  {
1110                      $sql .= ' ' . $column_type;
1111                  }
1112  
1113                  if (!is_null($column_data[1]))
1114                  {
1115                      $sql .= ' NOT NULL ';
1116                      $sql .= "DEFAULT '{$column_data[1]}'";
1117                  }
1118  
1119              break;
1120          }
1121  
1122          $return_array['column_type_sql'] = $sql;
1123  
1124          return $return_array;
1125      }
1126  
1127      /**
1128      * Get the column's database type from the type map
1129      *
1130      * @param string $column_map_type
1131      * @return array        column type for this database
1132      *                    and map type without length
1133      */
1134  	function get_column_type($column_map_type)
1135      {
1136          $column_type = '';
1137          if (strpos($column_map_type, ':') !== false)
1138          {
1139              list($orig_column_type, $column_length) = explode(':', $column_map_type);
1140              if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']))
1141              {
1142                  $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'], $column_length);
1143              }
1144              else
1145              {
1146                  if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule']))
1147                  {
1148                      switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][0])
1149                      {
1150                          case 'div':
1151                              $column_length /= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['rule'][1];
1152                              $column_length = ceil($column_length);
1153                              $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1154                          break;
1155                      }
1156                  }
1157  
1158                  if (isset($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit']))
1159                  {
1160                      switch ($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][0])
1161                      {
1162                          case 'mult':
1163                              $column_length *= $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][1];
1164                              if ($column_length > $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][2])
1165                              {
1166                                  $column_type = $this->dbms_type_map[$this->sql_layer][$orig_column_type . ':']['limit'][3];
1167                              }
1168                              else
1169                              {
1170                                  $column_type = sprintf($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'][0], $column_length);
1171                              }
1172                          break;
1173                      }
1174                  }
1175              }
1176              $orig_column_type .= ':';
1177          }
1178          else
1179          {
1180              $orig_column_type = $column_map_type;
1181              $column_type = $this->dbms_type_map[$this->sql_layer][$column_map_type];
1182          }
1183  
1184          return array($column_type, $orig_column_type);
1185      }
1186  
1187      /**
1188       * {@inheritDoc}
1189       */
1190  	function sql_column_add($table_name, $column_name, $column_data, $inline = false)
1191      {
1192          $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1193          $statements = array();
1194  
1195          switch ($this->sql_layer)
1196          {
1197              case 'mysql_41':
1198                  $after = (!empty($column_data['after'])) ? ' AFTER ' . $column_data['after'] : '';
1199                  $statements[] = 'ALTER TABLE `' . $table_name . '` ADD COLUMN `' . $column_name . '` ' . $column_data['column_type_sql'] . $after;
1200              break;
1201  
1202              case 'oracle':
1203                  // Does not support AFTER, only through temporary table
1204                  $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1205              break;
1206  
1207              case 'sqlite3':
1208                  if ($inline && $this->return_statements)
1209                  {
1210                      return $column_name . ' ' . $column_data['column_type_sql'];
1211                  }
1212  
1213                  $statements[] = 'ALTER TABLE ' . $table_name . ' ADD ' . $column_name . ' ' . $column_data['column_type_sql'];
1214              break;
1215          }
1216  
1217          return $this->_sql_run_sql($statements);
1218      }
1219  
1220      /**
1221       * {@inheritDoc}
1222       */
1223  	function sql_column_remove($table_name, $column_name, $inline = false)
1224      {
1225          $statements = array();
1226  
1227          switch ($this->sql_layer)
1228          {
1229              case 'mysql_41':
1230                  $statements[] = 'ALTER TABLE `' . $table_name . '` DROP COLUMN `' . $column_name . '`';
1231              break;
1232  
1233              case 'oracle':
1234                  $statements[] = 'ALTER TABLE ' . $table_name . ' DROP COLUMN ' . $column_name;
1235              break;
1236  
1237              case 'sqlite3':
1238  
1239                  if ($inline && $this->return_statements)
1240                  {
1241                      return $column_name;
1242                  }
1243  
1244                  $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name, $column_name);
1245                  if (empty($recreate_queries))
1246                  {
1247                      break;
1248                  }
1249  
1250                  $statements[] = 'begin';
1251  
1252                  $sql_create_table = array_shift($recreate_queries);
1253  
1254                  // Create a backup table and populate it, destroy the existing one
1255                  $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1256                  $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1257                  $statements[] = 'DROP TABLE ' . $table_name;
1258  
1259                  preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1260  
1261                  $new_table_cols = trim($matches[1]);
1262                  $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1263                  $column_list = array();
1264  
1265                  foreach ($old_table_cols as $declaration)
1266                  {
1267                      $entities = preg_split('#\s+#', trim($declaration));
1268                      if ($entities[0] == 'PRIMARY' || $entities[0] === $column_name)
1269                      {
1270                          continue;
1271                      }
1272                      $column_list[] = $entities[0];
1273                  }
1274  
1275                  $columns = implode(',', $column_list);
1276  
1277                  $new_table_cols = trim(preg_replace('/' . $column_name . '\b[^,]+(?:,|$)/m', '', $new_table_cols));
1278                  if (substr($new_table_cols, -1) === ',')
1279                  {
1280                      // Remove the comma from the last entry again
1281                      $new_table_cols = substr($new_table_cols, 0, -1);
1282                  }
1283  
1284                  // create a new table and fill it up. destroy the temp one
1285                  $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ');';
1286                  $statements = array_merge($statements, $recreate_queries);
1287  
1288                  $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1289                  $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1290  
1291                  $statements[] = 'commit';
1292              break;
1293          }
1294  
1295          return $this->_sql_run_sql($statements);
1296      }
1297  
1298      /**
1299       * {@inheritDoc}
1300       */
1301  	function sql_index_drop($table_name, $index_name)
1302      {
1303          $statements = array();
1304  
1305          switch ($this->sql_layer)
1306          {
1307              case 'mysql_41':
1308                  $index_name = $this->check_index_name_length($table_name, $index_name, false);
1309                  $statements[] = 'DROP INDEX ' . $index_name . ' ON ' . $table_name;
1310              break;
1311  
1312              case 'oracle':
1313              case 'sqlite3':
1314                  $index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name, false);
1315                  $statements[] = 'DROP INDEX ' . $index_name;
1316              break;
1317          }
1318  
1319          return $this->_sql_run_sql($statements);
1320      }
1321  
1322      /**
1323       * {@inheritDoc}
1324       */
1325  	function sql_table_drop($table_name)
1326      {
1327          $statements = array();
1328  
1329          if (!$this->sql_table_exists($table_name))
1330          {
1331              return $this->_sql_run_sql($statements);
1332          }
1333  
1334          // the most basic operation, get rid of the table
1335          $statements[] = 'DROP TABLE ' . $table_name;
1336  
1337          switch ($this->sql_layer)
1338          {
1339              case 'oracle':
1340                  $sql = 'SELECT A.REFERENCED_NAME
1341                      FROM USER_DEPENDENCIES A, USER_TRIGGERS B
1342                      WHERE A.REFERENCED_TYPE = \'SEQUENCE\'
1343                          AND A.NAME = B.TRIGGER_NAME
1344                          AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'";
1345                  $result = $this->db->sql_query($sql);
1346  
1347                  // any sequences ref'd to this table's triggers?
1348                  while ($row = $this->db->sql_fetchrow($result))
1349                  {
1350                      $statements[] = "DROP SEQUENCE {$row['referenced_name']}";
1351                  }
1352                  $this->db->sql_freeresult($result);
1353              break;
1354          }
1355  
1356          return $this->_sql_run_sql($statements);
1357      }
1358  
1359      /**
1360       * {@inheritDoc}
1361       */
1362  	function sql_create_primary_key($table_name, $column, $inline = false)
1363      {
1364          $statements = array();
1365  
1366          switch ($this->sql_layer)
1367          {
1368              case 'mysql_41':
1369                  $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';
1370              break;
1371  
1372              case 'oracle':
1373                  $statements[] = 'ALTER TABLE ' . $table_name . ' add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')';
1374              break;
1375  
1376              case 'sqlite3':
1377  
1378                  if ($inline && $this->return_statements)
1379                  {
1380                      return $column;
1381                  }
1382  
1383                  $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
1384                  if (empty($recreate_queries))
1385                  {
1386                      break;
1387                  }
1388  
1389                  $statements[] = 'begin';
1390  
1391                  $sql_create_table = array_shift($recreate_queries);
1392  
1393                  // Create a backup table and populate it, destroy the existing one
1394                  $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1395                  $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1396                  $statements[] = 'DROP TABLE ' . $table_name;
1397  
1398                  preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1399  
1400                  $new_table_cols = trim($matches[1]);
1401                  $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1402                  $column_list = array();
1403  
1404                  foreach ($old_table_cols as $declaration)
1405                  {
1406                      $entities = preg_split('#\s+#', trim($declaration));
1407                      if ($entities[0] == 'PRIMARY')
1408                      {
1409                          continue;
1410                      }
1411                      $column_list[] = $entities[0];
1412                  }
1413  
1414                  $columns = implode(',', $column_list);
1415  
1416                  // create a new table and fill it up. destroy the temp one
1417                  $statements[] = 'CREATE TABLE ' . $table_name . ' (' . $new_table_cols . ', PRIMARY KEY (' . implode(', ', $column) . '));';
1418                  $statements = array_merge($statements, $recreate_queries);
1419  
1420                  $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1421                  $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1422  
1423                  $statements[] = 'commit';
1424              break;
1425          }
1426  
1427          return $this->_sql_run_sql($statements);
1428      }
1429  
1430      /**
1431       * {@inheritDoc}
1432       */
1433  	function sql_create_unique_index($table_name, $index_name, $column)
1434      {
1435          $statements = array();
1436  
1437          switch ($this->sql_layer)
1438          {
1439              case 'oracle':
1440              case 'sqlite3':
1441                  $index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name);
1442                  $statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1443              break;
1444  
1445              case 'mysql_41':
1446                  $index_name = $this->check_index_name_length($table_name, $index_name);
1447                  $statements[] = 'ALTER TABLE ' . $table_name . ' ADD UNIQUE INDEX ' . $index_name . '(' . implode(', ', $column) . ')';
1448              break;
1449          }
1450  
1451          return $this->_sql_run_sql($statements);
1452      }
1453  
1454      /**
1455       * {@inheritDoc}
1456       */
1457  	function sql_create_index($table_name, $index_name, $column)
1458      {
1459          $statements = array();
1460  
1461          $column = preg_replace('#:.*$#', '', $column);
1462  
1463          switch ($this->sql_layer)
1464          {
1465              case 'oracle':
1466              case 'sqlite3':
1467                  $index_name = $this->check_index_name_length($table_name, $table_name . '_' . $index_name);
1468                  $statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ')';
1469              break;
1470  
1471              case 'mysql_41':
1472                  $index_name = $this->check_index_name_length($table_name, $index_name);
1473                  $statements[] = 'ALTER TABLE ' . $table_name . ' ADD INDEX ' . $index_name . ' (' . implode(', ', $column) . ')';
1474              break;
1475          }
1476  
1477          return $this->_sql_run_sql($statements);
1478      }
1479  
1480      /**
1481       * Check whether the index name is too long
1482       *
1483       * @param string $table_name
1484       * @param string $index_name
1485       * @param bool $throw_error
1486       * @return string    The index name, shortened if too long
1487       */
1488  	protected function check_index_name_length($table_name, $index_name, $throw_error = true)
1489      {
1490          $max_index_name_length = $this->get_max_index_name_length();
1491          if (strlen($index_name) > $max_index_name_length)
1492          {
1493              // Try removing the table prefix if it's at the beginning
1494              $table_prefix = substr(CONFIG_TABLE, 0, -6); // strlen(config)
1495              if (strpos($index_name, $table_prefix) === 0)
1496              {
1497                  $index_name = substr($index_name, strlen($table_prefix));
1498                  return $this->check_index_name_length($table_name, $index_name, $throw_error);
1499              }
1500  
1501              // Try removing the remaining suffix part of table name then
1502              $table_suffix = substr($table_name, strlen($table_prefix));
1503              if (strpos($index_name, $table_suffix) === 0)
1504              {
1505                  // Remove the suffix and underscore separator between table_name and index_name
1506                  $index_name = substr($index_name, strlen($table_suffix) + 1);
1507                  return $this->check_index_name_length($table_name, $index_name, $throw_error);
1508              }
1509  
1510              if ($throw_error)
1511              {
1512                  trigger_error("Index name '$index_name' on table '$table_name' is too long. The maximum is $max_index_name_length characters.", E_USER_ERROR);
1513              }
1514          }
1515  
1516          return $index_name;
1517      }
1518  
1519      /**
1520       * Get maximum index name length. Might vary depending on db type
1521       *
1522       * @return int Maximum index name length
1523       */
1524  	protected function get_max_index_name_length()
1525      {
1526          return 30;
1527      }
1528  
1529      /**
1530       * {@inheritDoc}
1531       */
1532  	function sql_list_index($table_name)
1533      {
1534          $index_array = array();
1535  
1536          switch ($this->sql_layer)
1537          {
1538              case 'mysql_41':
1539                  $sql = 'SHOW KEYS
1540                      FROM ' . $table_name;
1541                  $col = 'Key_name';
1542                  break;
1543  
1544              case 'oracle':
1545                  $sql = "SELECT index_name
1546                      FROM user_indexes
1547                      WHERE table_name = '" . strtoupper($table_name) . "'
1548                          AND generated = 'N'
1549                          AND uniqueness = 'NONUNIQUE'";
1550                  $col = 'index_name';
1551                  break;
1552  
1553              case 'sqlite3':
1554                  $sql = "PRAGMA index_info('" . $table_name . "');";
1555                  $col = 'name';
1556                  break;
1557          }
1558  
1559          $result = $this->db->sql_query($sql);
1560          while ($row = $this->db->sql_fetchrow($result))
1561          {
1562              if ($this->sql_layer == 'mysql_41' && !$row['Non_unique'])
1563              {
1564                  continue;
1565              }
1566  
1567              switch ($this->sql_layer)
1568              {
1569                  case 'oracle':
1570                  case 'sqlite3':
1571                      $row[$col] = substr($row[$col], strlen($table_name) + 1);
1572                      break;
1573              }
1574  
1575              $index_array[] = $row[$col];
1576          }
1577          $this->db->sql_freeresult($result);
1578  
1579          return array_map('strtolower', $index_array);
1580      }
1581  
1582      /**
1583       * Removes table_name from the index_name if it is at the beginning
1584       *
1585       * @param $table_name
1586       * @param $index_name
1587       * @return string
1588       */
1589  	protected function strip_table_name_from_index_name($table_name, $index_name)
1590      {
1591          return (strpos(strtoupper($index_name), strtoupper($table_name)) === 0) ? substr($index_name, strlen($table_name) + 1) : $index_name;
1592      }
1593  
1594      /**
1595       * {@inheritDoc}
1596       */
1597  	function sql_column_change($table_name, $column_name, $column_data, $inline = false)
1598      {
1599          $original_column_data = $column_data;
1600          $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
1601          $statements = array();
1602  
1603          switch ($this->sql_layer)
1604          {
1605              case 'mysql_41':
1606                  $statements[] = 'ALTER TABLE `' . $table_name . '` CHANGE `' . $column_name . '` `' . $column_name . '` ' . $column_data['column_type_sql'];
1607              break;
1608  
1609              case 'oracle':
1610                  // We need the data here
1611                  $old_return_statements = $this->return_statements;
1612                  $this->return_statements = true;
1613  
1614                  // Get list of existing indexes
1615                  $indexes = $this->get_existing_indexes($table_name, $column_name);
1616                  $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true);
1617  
1618                  // Drop any indexes
1619                  if (!empty($indexes) || !empty($unique_indexes))
1620                  {
1621                      $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes));
1622                      foreach ($drop_indexes as $index_name)
1623                      {
1624                          $result = $this->sql_index_drop($table_name, $this->strip_table_name_from_index_name($table_name, $index_name));
1625                          $statements = array_merge($statements, $result);
1626                      }
1627                  }
1628  
1629                  $temp_column_name = 'temp_' . substr(md5($column_name), 0, 25);
1630                  // Add a temporary table with the new type
1631                  $result = $this->sql_column_add($table_name, $temp_column_name, $original_column_data);
1632                  $statements = array_merge($statements, $result);
1633  
1634                  // Copy the data to the new column
1635                  $statements[] = 'UPDATE ' . $table_name . ' SET ' . $temp_column_name . ' = ' . $column_name;
1636  
1637                  // Drop the original column
1638                  $result = $this->sql_column_remove($table_name, $column_name);
1639                  $statements = array_merge($statements, $result);
1640  
1641                  // Recreate the original column with the new type
1642                  $result = $this->sql_column_add($table_name, $column_name, $original_column_data);
1643                  $statements = array_merge($statements, $result);
1644  
1645                  if (!empty($indexes))
1646                  {
1647                      // Recreate indexes after we changed the column
1648                      foreach ($indexes as $index_name => $index_data)
1649                      {
1650                          $result = $this->sql_create_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data);
1651                          $statements = array_merge($statements, $result);
1652                      }
1653                  }
1654  
1655                  if (!empty($unique_indexes))
1656                  {
1657                      // Recreate unique indexes after we changed the column
1658                      foreach ($unique_indexes as $index_name => $index_data)
1659                      {
1660                          $result = $this->sql_create_unique_index($table_name, $this->strip_table_name_from_index_name($table_name, $index_name), $index_data);
1661                          $statements = array_merge($statements, $result);
1662                      }
1663                  }
1664  
1665                  // Copy the data to the original column
1666                  $statements[] = 'UPDATE ' . $table_name . ' SET ' . $column_name . ' = ' . $temp_column_name;
1667  
1668                  // Drop the temporary column again
1669                  $result = $this->sql_column_remove($table_name, $temp_column_name);
1670                  $statements = array_merge($statements, $result);
1671  
1672                  $this->return_statements = $old_return_statements;
1673              break;
1674  
1675              case 'sqlite3':
1676  
1677                  if ($inline && $this->return_statements)
1678                  {
1679                      return $column_name . ' ' . $column_data['column_type_sql'];
1680                  }
1681  
1682                  $recreate_queries = $this->sqlite_get_recreate_table_queries($table_name);
1683                  if (empty($recreate_queries))
1684                  {
1685                      break;
1686                  }
1687  
1688                  $statements[] = 'begin';
1689  
1690                  $sql_create_table = array_shift($recreate_queries);
1691  
1692                  // Create a temp table and populate it, destroy the existing one
1693                  $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $sql_create_table);
1694                  $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name;
1695                  $statements[] = 'DROP TABLE ' . $table_name;
1696  
1697                  preg_match('#\((.*)\)#s', $sql_create_table, $matches);
1698  
1699                  $new_table_cols = trim($matches[1]);
1700                  $old_table_cols = preg_split('/,(?![\s\w]+\))/m', $new_table_cols);
1701                  $column_list = array();
1702  
1703                  foreach ($old_table_cols as $key => $declaration)
1704                  {
1705                      $declaration = trim($declaration);
1706  
1707                      // Check for the beginning of the constraint section and stop
1708                      if (preg_match('/[^\(]*\s*PRIMARY KEY\s+\(/', $declaration) ||
1709                          preg_match('/[^\(]*\s*UNIQUE\s+\(/', $declaration) ||
1710                          preg_match('/[^\(]*\s*FOREIGN KEY\s+\(/', $declaration) ||
1711                          preg_match('/[^\(]*\s*CHECK\s+\(/', $declaration))
1712                      {
1713                          break;
1714                      }
1715  
1716                      $entities = preg_split('#\s+#', $declaration);
1717                      $column_list[] = $entities[0];
1718                      if ($entities[0] == $column_name)
1719                      {
1720                          $old_table_cols[$key] = $column_name . ' ' . $column_data['column_type_sql'];
1721                      }
1722                  }
1723  
1724                  $columns = implode(',', $column_list);
1725  
1726                  // Create a new table and fill it up. destroy the temp one
1727                  $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $old_table_cols) . ');';
1728                  $statements = array_merge($statements, $recreate_queries);
1729  
1730                  $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;';
1731                  $statements[] = 'DROP TABLE ' . $table_name . '_temp';
1732  
1733                  $statements[] = 'commit';
1734  
1735              break;
1736          }
1737  
1738          return $this->_sql_run_sql($statements);
1739      }
1740  
1741      /**
1742      * Get a list with existing indexes for the column
1743      *
1744      * @param string $table_name
1745      * @param string $column_name
1746      * @param bool $unique Should we get unique indexes or normal ones
1747      * @return array        Array with Index name => columns
1748      */
1749  	public function get_existing_indexes($table_name, $column_name, $unique = false)
1750      {
1751          switch ($this->sql_layer)
1752          {
1753              case 'mysql_41':
1754              case 'sqlite3':
1755                  // Not supported
1756                  throw new \Exception('DBMS is not supported');
1757              break;
1758          }
1759  
1760          $sql = '';
1761          $existing_indexes = array();
1762  
1763          switch ($this->sql_layer)
1764          {
1765              case 'oracle':
1766                  $sql = "SELECT ix.index_name  AS phpbb_index_name, ix.uniqueness AS is_unique
1767                      FROM all_ind_columns ixc, all_indexes ix
1768                      WHERE ix.index_name = ixc.index_name
1769                          AND ixc.table_name = '" . strtoupper($table_name) . "'
1770                          AND ixc.column_name = '" . strtoupper($column_name) . "'";
1771              break;
1772          }
1773  
1774          $result = $this->db->sql_query($sql);
1775          while ($row = $this->db->sql_fetchrow($result))
1776          {
1777              if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE'))
1778              {
1779                  $existing_indexes[$row['phpbb_index_name']] = array();
1780              }
1781          }
1782          $this->db->sql_freeresult($result);
1783  
1784          if (empty($existing_indexes))
1785          {
1786              return array();
1787          }
1788  
1789          switch ($this->sql_layer)
1790          {
1791              case 'oracle':
1792                  $sql = "SELECT index_name AS phpbb_index_name, column_name AS phpbb_column_name
1793                      FROM all_ind_columns
1794                      WHERE table_name = '" . strtoupper($table_name) . "'
1795                          AND " . $this->db->sql_in_set('index_name', array_keys($existing_indexes));
1796              break;
1797          }
1798  
1799          $result = $this->db->sql_query($sql);
1800          while ($row = $this->db->sql_fetchrow($result))
1801          {
1802              $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
1803          }
1804          $this->db->sql_freeresult($result);
1805  
1806          return $existing_indexes;
1807      }
1808  
1809      /**
1810      * Returns the Queries which are required to recreate a table including indexes
1811      *
1812      * @param string $table_name
1813      * @param string $remove_column    When we drop a column, we remove the column
1814      *                                from all indexes. If the index has no other
1815      *                                column, we drop it completly.
1816      * @return array
1817      */
1818  	protected function sqlite_get_recreate_table_queries($table_name, $remove_column = '')
1819      {
1820          $queries = array();
1821  
1822          $sql = "SELECT sql
1823              FROM sqlite_master
1824              WHERE type = 'table'
1825                  AND name = '{$table_name}'";
1826          $result = $this->db->sql_query($sql);
1827          $sql_create_table = $this->db->sql_fetchfield('sql');
1828          $this->db->sql_freeresult($result);
1829  
1830          if (!$sql_create_table)
1831          {
1832              return array();
1833          }
1834          $queries[] = $sql_create_table;
1835  
1836          $sql = "SELECT sql
1837              FROM sqlite_master
1838              WHERE type = 'index'
1839                  AND tbl_name = '{$table_name}'";
1840          $result = $this->db->sql_query($sql);
1841          while ($sql_create_index = $this->db->sql_fetchfield('sql'))
1842          {
1843              if ($remove_column)
1844              {
1845                  $match = array();
1846                  preg_match('#(?:[\w ]+)\((.*)\)#', $sql_create_index, $match);
1847                  if (!isset($match[1]))
1848                  {
1849                      continue;
1850                  }
1851  
1852                  // Find and remove $remove_column from the index
1853                  $columns = explode(', ', $match[1]);
1854                  $found_column = array_search($remove_column, $columns);
1855                  if ($found_column !== false)
1856                  {
1857                      unset($columns[$found_column]);
1858  
1859                      // If the column list is not empty add the index to the list
1860                      if (!empty($columns))
1861                      {
1862                          $queries[] = str_replace($match[1], implode(', ', $columns), $sql_create_index);
1863                      }
1864                  }
1865                  else
1866                  {
1867                      $queries[] = $sql_create_index;
1868                  }
1869              }
1870              else
1871              {
1872                  $queries[] = $sql_create_index;
1873              }
1874          }
1875          $this->db->sql_freeresult($result);
1876  
1877          return $queries;
1878      }
1879  }


Generated: Tue Apr 7 19:44:41 2020 Cross-referenced by PHPXref 0.7.1