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