[ Index ]

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


Generated: Wed Nov 11 20:33:01 2020 Cross-referenced by PHPXref 0.7.1