[ Index ]

PHP Cross Reference of phpBB-3.3.9-deutsch

title

Body

[close]

/phpbb/db/tools/ -> mssql.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 mssql extends tools
  21  {
  22      /**
  23       * Is the used MS SQL Server a SQL Server 2000?
  24       * @var bool
  25       */
  26      protected $is_sql_server_2000;
  27  
  28      /**
  29       * Get the column types for mssql based databases
  30       *
  31       * @return array
  32       */
  33  	static public function get_dbms_type_map()
  34      {
  35          return array(
  36              'mssql'        => array(
  37                  'INT:'        => '[int]',
  38                  'BINT'        => '[float]',
  39                  'ULINT'        => '[int]',
  40                  'UINT'        => '[int]',
  41                  'UINT:'        => '[int]',
  42                  'TINT:'        => '[int]',
  43                  'USINT'        => '[int]',
  44                  'BOOL'        => '[int]',
  45                  'VCHAR'        => '[varchar] (255)',
  46                  'VCHAR:'    => '[varchar] (%d)',
  47                  'CHAR:'        => '[char] (%d)',
  48                  'XSTEXT'    => '[varchar] (1000)',
  49                  'STEXT'        => '[varchar] (3000)',
  50                  'TEXT'        => '[varchar] (8000)',
  51                  'MTEXT'        => '[text]',
  52                  'XSTEXT_UNI'=> '[nvarchar] (100)',
  53                  'STEXT_UNI'    => '[nvarchar] (255)',
  54                  'TEXT_UNI'    => '[nvarchar] (4000)',
  55                  'MTEXT_UNI'    => '[ntext]',
  56                  'TIMESTAMP'    => '[int]',
  57                  'DECIMAL'    => '[float]',
  58                  'DECIMAL:'    => '[float]',
  59                  'PDECIMAL'    => '[float]',
  60                  'PDECIMAL:'    => '[float]',
  61                  'VCHAR_UNI'    => '[nvarchar] (255)',
  62                  'VCHAR_UNI:'=> '[nvarchar] (%d)',
  63                  'VCHAR_CI'    => '[nvarchar] (255)',
  64                  'VARBINARY'    => '[varchar] (255)',
  65              ),
  66  
  67              'mssqlnative'    => array(
  68                  'INT:'        => '[int]',
  69                  'BINT'        => '[float]',
  70                  'ULINT'        => '[int]',
  71                  'UINT'        => '[int]',
  72                  'UINT:'        => '[int]',
  73                  'TINT:'        => '[int]',
  74                  'USINT'        => '[int]',
  75                  'BOOL'        => '[int]',
  76                  'VCHAR'        => '[varchar] (255)',
  77                  'VCHAR:'    => '[varchar] (%d)',
  78                  'CHAR:'        => '[char] (%d)',
  79                  'XSTEXT'    => '[varchar] (1000)',
  80                  'STEXT'        => '[varchar] (3000)',
  81                  'TEXT'        => '[varchar] (8000)',
  82                  'MTEXT'        => '[text]',
  83                  'XSTEXT_UNI'=> '[nvarchar] (100)',
  84                  'STEXT_UNI'    => '[nvarchar] (255)',
  85                  'TEXT_UNI'    => '[nvarchar] (4000)',
  86                  'MTEXT_UNI'    => '[ntext]',
  87                  'TIMESTAMP'    => '[int]',
  88                  'DECIMAL'    => '[float]',
  89                  'DECIMAL:'    => '[float]',
  90                  'PDECIMAL'    => '[float]',
  91                  'PDECIMAL:'    => '[float]',
  92                  'VCHAR_UNI'    => '[nvarchar] (255)',
  93                  'VCHAR_UNI:'=> '[nvarchar] (%d)',
  94                  'VCHAR_CI'    => '[nvarchar] (255)',
  95                  'VARBINARY'    => '[varchar] (255)',
  96              ),
  97          );
  98      }
  99  
 100      /**
 101      * Constructor. Set DB Object and set {@link $return_statements return_statements}.
 102      *
 103      * @param \phpbb\db\driver\driver_interface    $db                    Database connection
 104      * @param bool        $return_statements    True if only statements should be returned and no SQL being executed
 105      */
 106  	public function __construct(\phpbb\db\driver\driver_interface $db, $return_statements = false)
 107      {
 108          parent::__construct($db, $return_statements);
 109  
 110          // Determine mapping database type
 111          switch ($this->db->get_sql_layer())
 112          {
 113              case 'mssql_odbc':
 114                  $this->sql_layer = 'mssql';
 115              break;
 116  
 117              case 'mssqlnative':
 118                  $this->sql_layer = 'mssqlnative';
 119              break;
 120          }
 121  
 122          $this->dbms_type_map = self::get_dbms_type_map();
 123      }
 124  
 125      /**
 126       * {@inheritDoc}
 127       */
 128  	function sql_list_tables()
 129      {
 130          $sql = "SELECT name
 131              FROM sysobjects
 132              WHERE type='U'";
 133          $result = $this->db->sql_query($sql);
 134  
 135          $tables = array();
 136          while ($row = $this->db->sql_fetchrow($result))
 137          {
 138              $name = current($row);
 139              $tables[$name] = $name;
 140          }
 141          $this->db->sql_freeresult($result);
 142  
 143          return $tables;
 144      }
 145  
 146      /**
 147       * {@inheritDoc}
 148       */
 149  	function sql_create_table($table_name, $table_data)
 150      {
 151          // holds the DDL for a column
 152          $columns = $statements = array();
 153  
 154          if ($this->sql_table_exists($table_name))
 155          {
 156              return $this->_sql_run_sql($statements);
 157          }
 158  
 159          // Begin transaction
 160          $statements[] = 'begin';
 161  
 162          // Determine if we have created a PRIMARY KEY in the earliest
 163          $primary_key_gen = false;
 164  
 165          // Determine if the table requires a sequence
 166          $create_sequence = false;
 167  
 168          // Begin table sql statement
 169          $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
 170  
 171          if (!isset($table_data['PRIMARY_KEY']))
 172          {
 173              $table_data['COLUMNS']['mssqlindex'] = array('UINT', null, 'auto_increment');
 174              $table_data['PRIMARY_KEY'] = 'mssqlindex';
 175          }
 176  
 177          // Iterate through the columns to create a table
 178          foreach ($table_data['COLUMNS'] as $column_name => $column_data)
 179          {
 180              // here lies an array, filled with information compiled on the column's data
 181              $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 182  
 183              if (isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'] && strlen($column_name) > 26) // "$column_name}_gen"
 184              {
 185                  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);
 186              }
 187  
 188              // here we add the definition of the new column to the list of columns
 189              $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
 190  
 191              // see if we have found a primary key set due to a column definition if we have found it, we can stop looking
 192              if (!$primary_key_gen)
 193              {
 194                  $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set'];
 195              }
 196  
 197              // create sequence DDL based off of the existence of auto incrementing columns
 198              if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment'])
 199              {
 200                  $create_sequence = $column_name;
 201              }
 202          }
 203  
 204          // this makes up all the columns in the create table statement
 205          $table_sql .= implode(",\n", $columns);
 206  
 207          // Close the table for two DBMS and add to the statements
 208          $table_sql .= "\n);";
 209          $statements[] = $table_sql;
 210  
 211          // we have yet to create a primary key for this table,
 212          // this means that we can add the one we really wanted instead
 213          if (!$primary_key_gen)
 214          {
 215              // Write primary key
 216              if (isset($table_data['PRIMARY_KEY']))
 217              {
 218                  if (!is_array($table_data['PRIMARY_KEY']))
 219                  {
 220                      $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']);
 221                  }
 222  
 223                  // We need the data here
 224                  $old_return_statements = $this->return_statements;
 225                  $this->return_statements = true;
 226  
 227                  $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']);
 228                  foreach ($primary_key_stmts as $pk_stmt)
 229                  {
 230                      $statements[] = $pk_stmt;
 231                  }
 232  
 233                  $this->return_statements = $old_return_statements;
 234              }
 235          }
 236  
 237          // Write Keys
 238          if (isset($table_data['KEYS']))
 239          {
 240              foreach ($table_data['KEYS'] as $key_name => $key_data)
 241              {
 242                  if (!is_array($key_data[1]))
 243                  {
 244                      $key_data[1] = array($key_data[1]);
 245                  }
 246  
 247                  $old_return_statements = $this->return_statements;
 248                  $this->return_statements = true;
 249  
 250                  $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]);
 251  
 252                  foreach ($key_stmts as $key_stmt)
 253                  {
 254                      $statements[] = $key_stmt;
 255                  }
 256  
 257                  $this->return_statements = $old_return_statements;
 258              }
 259          }
 260  
 261          // Commit Transaction
 262          $statements[] = 'commit';
 263  
 264          return $this->_sql_run_sql($statements);
 265      }
 266  
 267      /**
 268       * {@inheritDoc}
 269       */
 270  	function sql_list_columns($table_name)
 271      {
 272          $columns = array();
 273  
 274          $sql = "SELECT c.name
 275              FROM syscolumns c
 276              LEFT JOIN sysobjects o ON c.id = o.id
 277              WHERE o.name = '{$table_name}'";
 278          $result = $this->db->sql_query($sql);
 279  
 280          while ($row = $this->db->sql_fetchrow($result))
 281          {
 282              $column = strtolower(current($row));
 283              $columns[$column] = $column;
 284          }
 285          $this->db->sql_freeresult($result);
 286  
 287          return $columns;
 288      }
 289  
 290      /**
 291       * {@inheritDoc}
 292       */
 293  	function sql_index_exists($table_name, $index_name)
 294      {
 295          $sql = "EXEC sp_statistics '$table_name'";
 296          $result = $this->db->sql_query($sql);
 297  
 298          while ($row = $this->db->sql_fetchrow($result))
 299          {
 300              if ($row['TYPE'] == 3)
 301              {
 302                  if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
 303                  {
 304                      $this->db->sql_freeresult($result);
 305                      return true;
 306                  }
 307              }
 308          }
 309          $this->db->sql_freeresult($result);
 310  
 311          return false;
 312      }
 313  
 314      /**
 315       * {@inheritDoc}
 316       */
 317  	function sql_unique_index_exists($table_name, $index_name)
 318      {
 319          $sql = "EXEC sp_statistics '$table_name'";
 320          $result = $this->db->sql_query($sql);
 321  
 322          while ($row = $this->db->sql_fetchrow($result))
 323          {
 324              // Usually NON_UNIQUE is the column we want to check, but we allow for both
 325              if ($row['TYPE'] == 3)
 326              {
 327                  if (strtolower($row['INDEX_NAME']) == strtolower($index_name))
 328                  {
 329                      $this->db->sql_freeresult($result);
 330                      return true;
 331                  }
 332              }
 333          }
 334          $this->db->sql_freeresult($result);
 335  
 336          return false;
 337      }
 338  
 339      /**
 340       * {@inheritDoc}
 341      */
 342  	function sql_prepare_column_data($table_name, $column_name, $column_data)
 343      {
 344          if (strlen($column_name) > 30)
 345          {
 346              trigger_error("Column name '$column_name' on table '$table_name' is too long. The maximum is 30 characters.", E_USER_ERROR);
 347          }
 348  
 349          // Get type
 350          list($column_type, ) = $this->get_column_type($column_data[0]);
 351  
 352          // Adjust default value if db-dependent specified
 353          if (is_array($column_data[1]))
 354          {
 355              $column_data[1] = (isset($column_data[1][$this->sql_layer])) ? $column_data[1][$this->sql_layer] : $column_data[1]['default'];
 356          }
 357  
 358          $sql = '';
 359  
 360          $return_array = array();
 361  
 362          $sql .= " {$column_type} ";
 363          $sql_default = " {$column_type} ";
 364  
 365          // For adding columns we need the default definition
 366          if (!is_null($column_data[1]))
 367          {
 368              // For hexadecimal values do not use single quotes
 369              if (strpos($column_data[1], '0x') === 0)
 370              {
 371                  $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') ';
 372                  $sql_default .= $return_array['default'];
 373              }
 374              else
 375              {
 376                  $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') ';
 377                  $sql_default .= $return_array['default'];
 378              }
 379          }
 380  
 381          if (isset($column_data[2]) && $column_data[2] == 'auto_increment')
 382          {
 383              // $sql .= 'IDENTITY (1, 1) ';
 384              $sql_default .= 'IDENTITY (1, 1) ';
 385          }
 386  
 387          $return_array['textimage'] = $column_type === '[text]';
 388  
 389          if (!is_null($column_data[1]) || (isset($column_data[2]) && $column_data[2] == 'auto_increment'))
 390          {
 391              $sql .= 'NOT NULL';
 392              $sql_default .= 'NOT NULL';
 393          }
 394          else
 395          {
 396              $sql .= 'NULL';
 397              $sql_default .= 'NULL';
 398          }
 399  
 400          $return_array['column_type_sql_default'] = $sql_default;
 401  
 402          $return_array['column_type_sql'] = $sql;
 403  
 404          return $return_array;
 405      }
 406  
 407      /**
 408       * {@inheritDoc}
 409       */
 410  	function sql_column_add($table_name, $column_name, $column_data, $inline = false)
 411      {
 412          $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 413          $statements = array();
 414  
 415          // Does not support AFTER, only through temporary table
 416          $statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
 417  
 418          return $this->_sql_run_sql($statements);
 419      }
 420  
 421      /**
 422       * {@inheritDoc}
 423       */
 424  	function sql_column_remove($table_name, $column_name, $inline = false)
 425      {
 426          $statements = array();
 427  
 428          // We need the data here
 429          $old_return_statements = $this->return_statements;
 430          $this->return_statements = true;
 431  
 432          $indexes = $this->get_existing_indexes($table_name, $column_name);
 433          $indexes = array_merge($indexes, $this->get_existing_indexes($table_name, $column_name, true));
 434  
 435          // Drop any indexes
 436          $recreate_indexes = array();
 437          if (!empty($indexes))
 438          {
 439              foreach ($indexes as $index_name => $index_data)
 440              {
 441                  $result = $this->sql_index_drop($table_name, $index_name);
 442                  $statements = array_merge($statements, $result);
 443                  if (count($index_data) > 1)
 444                  {
 445                      // Remove this column from the index and recreate it
 446                      $recreate_indexes[$index_name] = array_diff($index_data, array($column_name));
 447                  }
 448              }
 449          }
 450  
 451          // Drop primary keys depending on this column
 452          $result = $this->mssql_get_drop_default_primary_key_queries($table_name, $column_name);
 453          $statements = array_merge($statements, $result);
 454  
 455          // Drop default value constraint
 456          $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
 457          $statements = array_merge($statements, $result);
 458  
 459          // Remove the column
 460          $statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
 461  
 462          if (!empty($recreate_indexes))
 463          {
 464              // Recreate indexes after we removed the column
 465              foreach ($recreate_indexes as $index_name => $index_data)
 466              {
 467                  $result = $this->sql_create_index($table_name, $index_name, $index_data);
 468                  $statements = array_merge($statements, $result);
 469              }
 470          }
 471  
 472          $this->return_statements = $old_return_statements;
 473  
 474          return $this->_sql_run_sql($statements);
 475      }
 476  
 477      /**
 478       * {@inheritDoc}
 479       */
 480  	function sql_index_drop($table_name, $index_name)
 481      {
 482          $statements = array();
 483  
 484          $statements[] = 'DROP INDEX [' . $table_name . '].[' . $index_name . ']';
 485  
 486          return $this->_sql_run_sql($statements);
 487      }
 488  
 489      /**
 490       * {@inheritDoc}
 491       */
 492  	function sql_table_drop($table_name)
 493      {
 494          $statements = array();
 495  
 496          if (!$this->sql_table_exists($table_name))
 497          {
 498              return $this->_sql_run_sql($statements);
 499          }
 500  
 501          // the most basic operation, get rid of the table
 502          $statements[] = 'DROP TABLE ' . $table_name;
 503  
 504          return $this->_sql_run_sql($statements);
 505      }
 506  
 507      /**
 508       * {@inheritDoc}
 509       */
 510  	function sql_create_primary_key($table_name, $column, $inline = false)
 511      {
 512          $statements = array();
 513  
 514          $sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
 515          $sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY  CLUSTERED (";
 516          $sql .= '[' . implode("],\n\t\t[", $column) . ']';
 517          $sql .= ')';
 518  
 519          $statements[] = $sql;
 520  
 521          return $this->_sql_run_sql($statements);
 522      }
 523  
 524      /**
 525       * {@inheritDoc}
 526       */
 527  	function sql_create_unique_index($table_name, $index_name, $column)
 528      {
 529          $statements = array();
 530  
 531          if ($this->mssql_is_sql_server_2000())
 532          {
 533              $this->check_index_name_length($table_name, $index_name);
 534          }
 535  
 536          $statements[] = 'CREATE UNIQUE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
 537  
 538          return $this->_sql_run_sql($statements);
 539      }
 540  
 541      /**
 542       * {@inheritDoc}
 543       */
 544  	function sql_create_index($table_name, $index_name, $column)
 545      {
 546          $statements = array();
 547  
 548          $this->check_index_name_length($table_name, $index_name);
 549  
 550          // remove index length
 551          $column = preg_replace('#:.*$#', '', $column);
 552  
 553          $statements[] = 'CREATE INDEX [' . $index_name . '] ON [' . $table_name . ']([' . implode('], [', $column) . '])';
 554  
 555          return $this->_sql_run_sql($statements);
 556      }
 557  
 558      /**
 559       * {@inheritdoc}
 560       */
 561  	protected function get_max_index_name_length()
 562      {
 563          if ($this->mssql_is_sql_server_2000())
 564          {
 565              return parent::get_max_index_name_length();
 566          }
 567          else
 568          {
 569              return 128;
 570          }
 571      }
 572  
 573      /**
 574       * {@inheritDoc}
 575       */
 576  	function sql_list_index($table_name)
 577      {
 578          $index_array = array();
 579          $sql = "EXEC sp_statistics '$table_name'";
 580          $result = $this->db->sql_query($sql);
 581          while ($row = $this->db->sql_fetchrow($result))
 582          {
 583              if ($row['TYPE'] == 3)
 584              {
 585                  $index_array[] = strtolower($row['INDEX_NAME']);
 586              }
 587          }
 588          $this->db->sql_freeresult($result);
 589  
 590          return $index_array;
 591      }
 592  
 593      /**
 594       * {@inheritDoc}
 595       */
 596  	function sql_column_change($table_name, $column_name, $column_data, $inline = false)
 597      {
 598          $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data);
 599          $statements = array();
 600  
 601          // We need the data here
 602          $old_return_statements = $this->return_statements;
 603          $this->return_statements = true;
 604  
 605          $indexes = $this->get_existing_indexes($table_name, $column_name);
 606          $unique_indexes = $this->get_existing_indexes($table_name, $column_name, true);
 607  
 608          // Drop any indexes
 609          if (!empty($indexes) || !empty($unique_indexes))
 610          {
 611              $drop_indexes = array_merge(array_keys($indexes), array_keys($unique_indexes));
 612              foreach ($drop_indexes as $index_name)
 613              {
 614                  $result = $this->sql_index_drop($table_name, $index_name);
 615                  $statements = array_merge($statements, $result);
 616              }
 617          }
 618  
 619          // Drop default value constraint
 620          $result = $this->mssql_get_drop_default_constraints_queries($table_name, $column_name);
 621          $statements = array_merge($statements, $result);
 622  
 623          // Change the column
 624          $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 625  
 626          if (!empty($column_data['default']) && !$this->mssql_is_column_identity($table_name, $column_name))
 627          {
 628              // Add new default value constraint
 629              $statements[] = 'ALTER TABLE [' . $table_name . '] ADD CONSTRAINT [DF_' . $table_name . '_' . $column_name . '_1] ' . $column_data['default'] . ' FOR [' . $column_name . ']';
 630          }
 631  
 632          if (!empty($indexes))
 633          {
 634              // Recreate indexes after we changed the column
 635              foreach ($indexes as $index_name => $index_data)
 636              {
 637                  $result = $this->sql_create_index($table_name, $index_name, $index_data);
 638                  $statements = array_merge($statements, $result);
 639              }
 640          }
 641  
 642          if (!empty($unique_indexes))
 643          {
 644              // Recreate unique indexes after we changed the column
 645              foreach ($unique_indexes as $index_name => $index_data)
 646              {
 647                  $result = $this->sql_create_unique_index($table_name, $index_name, $index_data);
 648                  $statements = array_merge($statements, $result);
 649              }
 650          }
 651  
 652          $this->return_statements = $old_return_statements;
 653  
 654          return $this->_sql_run_sql($statements);
 655      }
 656  
 657      /**
 658      * Get queries to drop the default constraints of a column
 659      *
 660      * We need to drop the default constraints of a column,
 661      * before being able to change their type or deleting them.
 662      *
 663      * @param string $table_name
 664      * @param string $column_name
 665      * @return array        Array with SQL statements
 666      */
 667  	protected function mssql_get_drop_default_constraints_queries($table_name, $column_name)
 668      {
 669          $statements = array();
 670          if ($this->mssql_is_sql_server_2000())
 671          {
 672              // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 673              // Deprecated in SQL Server 2005
 674              $sql = "SELECT so.name AS def_name
 675                  FROM sysobjects so
 676                  JOIN sysconstraints sc ON so.id = sc.constid
 677                  WHERE object_name(so.parent_obj) = '{$table_name}'
 678                      AND so.xtype = 'D'
 679                      AND sc.colid = (SELECT colid FROM syscolumns
 680                          WHERE id = object_id('{$table_name}')
 681                              AND name = '{$column_name}')";
 682          }
 683          else
 684          {
 685              $sql = "SELECT dobj.name AS def_name
 686                  FROM sys.columns col
 687                      LEFT OUTER JOIN sys.objects dobj ON (dobj.object_id = col.default_object_id AND dobj.type = 'D')
 688                  WHERE col.object_id = object_id('{$table_name}')
 689                      AND col.name = '{$column_name}'
 690                      AND dobj.name IS NOT NULL";
 691          }
 692  
 693          $result = $this->db->sql_query($sql);
 694          while ($row = $this->db->sql_fetchrow($result))
 695          {
 696              $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $row['def_name'] . ']';
 697          }
 698          $this->db->sql_freeresult($result);
 699  
 700          return $statements;
 701      }
 702  
 703      /**
 704       * Get queries to drop the primary keys depending on the specified column
 705       *
 706       * We need to drop primary keys depending on this column before being able
 707       * to delete them.
 708       *
 709       * @param string $table_name
 710       * @param string $column_name
 711       * @return array        Array with SQL statements
 712       */
 713  	protected function mssql_get_drop_default_primary_key_queries($table_name, $column_name)
 714      {
 715          $statements = array();
 716  
 717          $sql = "SELECT ccu.CONSTRAINT_NAME, ccu.COLUMN_NAME
 718              FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 719                  JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
 720              WHERE tc.TABLE_NAME = '{$table_name}'
 721                  AND tc.CONSTRAINT_TYPE = 'Primary Key'
 722                  AND ccu.COLUMN_NAME = '{$column_name}'";
 723  
 724          $result = $this->db->sql_query($sql);
 725  
 726          while ($primary_key = $this->db->sql_fetchrow($result))
 727          {
 728              $statements[] = 'ALTER TABLE [' . $table_name . '] DROP CONSTRAINT [' . $primary_key['CONSTRAINT_NAME'] . ']';
 729          }
 730          $this->db->sql_freeresult($result);
 731  
 732          return $statements;
 733      }
 734  
 735      /**
 736       * Checks to see if column is an identity column
 737       *
 738       * Identity columns cannot have defaults set for them.
 739       *
 740       * @param string $table_name
 741       * @param string $column_name
 742       * @return bool        true if identity, false if not
 743       */
 744  	protected function mssql_is_column_identity($table_name, $column_name)
 745      {
 746          if ($this->mssql_is_sql_server_2000())
 747          {
 748              // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 749              // Deprecated in SQL Server 2005
 750              $sql = "SELECT COLUMNPROPERTY(object_id('{$table_name}'), '{$column_name}', 'IsIdentity') AS is_identity";
 751          }
 752          else
 753          {
 754              $sql = "SELECT is_identity FROM sys.columns
 755                      WHERE object_id = object_id('{$table_name}')
 756                      AND name = '{$column_name}'";
 757          }
 758  
 759          $result = $this->db->sql_query($sql);
 760          $is_identity = $this->db->sql_fetchfield('is_identity');
 761          $this->db->sql_freeresult($result);
 762  
 763          return (bool) $is_identity;
 764      }
 765  
 766      /**
 767      * Get a list with existing indexes for the column
 768      *
 769      * @param string $table_name
 770      * @param string $column_name
 771      * @param bool $unique Should we get unique indexes or normal ones
 772      * @return array        Array with Index name => columns
 773      */
 774  	public function get_existing_indexes($table_name, $column_name, $unique = false)
 775      {
 776          $existing_indexes = array();
 777          if ($this->mssql_is_sql_server_2000())
 778          {
 779              // http://msdn.microsoft.com/en-us/library/aa175912%28v=sql.80%29.aspx
 780              // Deprecated in SQL Server 2005
 781              $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
 782                  FROM sysindexes ix
 783                  INNER JOIN sysindexkeys ixc
 784                      ON ixc.id = ix.id
 785                          AND ixc.indid = ix.indid
 786                  INNER JOIN syscolumns cols
 787                      ON cols.colid = ixc.colid
 788                          AND cols.id = ix.id
 789                  WHERE ix.id = object_id('{$table_name}')
 790                      AND cols.name = '{$column_name}'
 791                      AND INDEXPROPERTY(ix.id, ix.name, 'IsUnique') = " . ($unique ? '1' : '0');
 792          }
 793          else
 794          {
 795              $sql = "SELECT DISTINCT ix.name AS phpbb_index_name
 796                  FROM sys.indexes ix
 797                  INNER JOIN sys.index_columns ixc
 798                      ON ixc.object_id = ix.object_id
 799                          AND ixc.index_id = ix.index_id
 800                  INNER JOIN sys.columns cols
 801                      ON cols.column_id = ixc.column_id
 802                          AND cols.object_id = ix.object_id
 803                  WHERE ix.object_id = object_id('{$table_name}')
 804                      AND cols.name = '{$column_name}'
 805                      AND ix.is_primary_key = 0
 806                      AND ix.is_unique = " . ($unique ? '1' : '0');
 807          }
 808  
 809          $result = $this->db->sql_query($sql);
 810          while ($row = $this->db->sql_fetchrow($result))
 811          {
 812              if (!isset($row['is_unique']) || ($unique && $row['is_unique'] == 'UNIQUE') || (!$unique && $row['is_unique'] == 'NONUNIQUE'))
 813              {
 814                  $existing_indexes[$row['phpbb_index_name']] = array();
 815              }
 816          }
 817          $this->db->sql_freeresult($result);
 818  
 819          if (empty($existing_indexes))
 820          {
 821              return array();
 822          }
 823  
 824          if ($this->mssql_is_sql_server_2000())
 825          {
 826              $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
 827                  FROM sysindexes ix
 828                  INNER JOIN sysindexkeys ixc
 829                      ON ixc.id = ix.id
 830                          AND ixc.indid = ix.indid
 831                  INNER JOIN syscolumns cols
 832                      ON cols.colid = ixc.colid
 833                          AND cols.id = ix.id
 834                  WHERE ix.id = object_id('{$table_name}')
 835                      AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
 836          }
 837          else
 838          {
 839              $sql = "SELECT DISTINCT ix.name AS phpbb_index_name, cols.name AS phpbb_column_name
 840                  FROM sys.indexes ix
 841                  INNER JOIN sys.index_columns ixc
 842                      ON ixc.object_id = ix.object_id
 843                          AND ixc.index_id = ix.index_id
 844                  INNER JOIN sys.columns cols
 845                      ON cols.column_id = ixc.column_id
 846                          AND cols.object_id = ix.object_id
 847                  WHERE ix.object_id = object_id('{$table_name}')
 848                      AND " . $this->db->sql_in_set('ix.name', array_keys($existing_indexes));
 849          }
 850  
 851          $result = $this->db->sql_query($sql);
 852          while ($row = $this->db->sql_fetchrow($result))
 853          {
 854              $existing_indexes[$row['phpbb_index_name']][] = $row['phpbb_column_name'];
 855          }
 856          $this->db->sql_freeresult($result);
 857  
 858          return $existing_indexes;
 859      }
 860  
 861      /**
 862      * Is the used MS SQL Server a SQL Server 2000?
 863      *
 864      * @return bool
 865      */
 866  	protected function mssql_is_sql_server_2000()
 867      {
 868          if ($this->is_sql_server_2000 === null)
 869          {
 870              $sql = "SELECT CAST(SERVERPROPERTY('productversion') AS VARCHAR(25)) AS mssql_version";
 871              $result = $this->db->sql_query($sql);
 872              $properties = $this->db->sql_fetchrow($result);
 873              $this->db->sql_freeresult($result);
 874              $this->is_sql_server_2000 = $properties['mssql_version'][0] == '8';
 875          }
 876  
 877          return $this->is_sql_server_2000;
 878      }
 879  
 880  }


Generated: Wed Dec 7 15:09:22 2022 Cross-referenced by PHPXref 0.7.1