| [ Index ] |
PHP Cross Reference of phpBB-3.2.11-deutsch |
[Summary view] [Print] [Text view]
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 public static 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 existance 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 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Wed Nov 11 20:33:01 2020 | Cross-referenced by PHPXref 0.7.1 |