![(please configure the [header_logo] section in trac.ini)](/wow/chrome/site/your_project_logo.png){kind=logo}
Changeset 702 for trunk/forum/includes/db/db_tools.php
- Timestamp:
- Mar 31, 2010, 6:32:40 PM (14 years ago)
- File:
-
- 1 edited
-
trunk/forum/includes/db/db_tools.php (modified) (38 diffs)
Legend:
- Unmodified
- Added
- Removed
-
trunk/forum/includes/db/db_tools.php
r400 r702 3 3 * 4 4 * @package dbal 5 * @version $Id : db_tools.php 8814 2008-09-04 12:01:47Z acydburn$5 * @version $Id$ 6 6 * @copyright (c) 2007 phpBB Group 7 7 * @license http://opensource.org/licenses/gpl-license.php GNU Public License … … 31 31 var $sql_layer = ''; 32 32 33 /** 34 * @var object DB object 35 */ 36 var $db = NULL; 37 38 /** 39 * The Column types for every database we support 40 * @var array 41 */ 33 42 var $dbms_type_map = array( 34 43 'mysql_41' => array( … … 243 252 ); 244 253 245 // A list of types being unsigned for better reference in some db's 254 /** 255 * A list of types being unsigned for better reference in some db's 256 * @var array 257 */ 246 258 var $unsigned_types = array('UINT', 'UINT:', 'USINT', 'BOOL', 'TIMESTAMP'); 259 260 /** 261 * A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules. 262 * @var array 263 */ 247 264 var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite'); 248 265 249 266 /** 250 * Set this to true if you only want to return the 'to-be-executed' SQL statement(s) (as an array). 267 * This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array). 268 * This mode has no effect on some methods (inserting of data for example). This is expressed within the methods command. 251 269 */ 252 270 var $return_statements = false; 253 271 254 272 /** 255 */ 256 function phpbb_db_tools(&$db) 273 * Constructor. Set DB Object and set {@link $return_statements return_statements}. 274 * 275 * @param phpbb_dbal $db DBAL object 276 * @param bool $return_statements True if only statements should be returned and no SQL being executed 277 */ 278 function phpbb_db_tools(&$db, $return_statements = false) 257 279 { 258 280 $this->db = $db; 281 $this->return_statements = $return_statements; 259 282 260 283 // Determine mapping database type … … 289 312 break; 290 313 } 314 } 315 316 /** 317 * Check if table exists 318 * 319 * 320 * @param string $table_name The table name to check for 321 * @return bool true if table exists, else false 322 */ 323 function sql_table_exists($table_name) 324 { 325 $this->db->sql_return_on_error(true); 326 $result = $this->db->sql_query_limit('SELECT * FROM ' . $table_name, 1); 327 $this->db->sql_return_on_error(false); 328 329 if ($result) 330 { 331 $this->db->sql_freeresult($result); 332 return true; 333 } 334 335 return false; 336 } 337 338 /** 339 * Create SQL Table 340 * 341 * @param string $table_name The table name to create 342 * @param array $table_data Array containing table data. 343 * @return array Statements if $return_statements is true. 344 */ 345 function sql_create_table($table_name, $table_data) 346 { 347 // holds the DDL for a column 348 $columns = $statements = array(); 349 350 if ($this->sql_table_exists($table_name)) 351 { 352 return $this->_sql_run_sql($statements); 353 } 354 355 // Begin transaction 356 $statements[] = 'begin'; 357 358 // Determine if we have created a PRIMARY KEY in the earliest 359 $primary_key_gen = false; 360 361 // Determine if the table must be created with TEXTIMAGE 362 $create_textimage = false; 363 364 // Determine if the table requires a sequence 365 $create_sequence = false; 366 367 // Begin table sql statement 368 switch ($this->sql_layer) 369 { 370 case 'mssql': 371 $table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n"; 372 break; 373 374 default: 375 $table_sql = 'CREATE TABLE ' . $table_name . ' (' . "\n"; 376 break; 377 } 378 379 // Iterate through the columns to create a table 380 foreach ($table_data['COLUMNS'] as $column_name => $column_data) 381 { 382 // here lies an array, filled with information compiled on the column's data 383 $prepared_column = $this->sql_prepare_column_data($table_name, $column_name, $column_data); 384 385 // here we add the definition of the new column to the list of columns 386 switch ($this->sql_layer) 387 { 388 case 'mssql': 389 $columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default']; 390 break; 391 392 default: 393 $columns[] = "\t {$column_name} " . $prepared_column['column_type_sql']; 394 break; 395 } 396 397 // see if we have found a primary key set due to a column definition if we have found it, we can stop looking 398 if (!$primary_key_gen) 399 { 400 $primary_key_gen = isset($prepared_column['primary_key_set']) && $prepared_column['primary_key_set']; 401 } 402 403 // create textimage DDL based off of the existance of certain column types 404 if (!$create_textimage) 405 { 406 $create_textimage = isset($prepared_column['textimage']) && $prepared_column['textimage']; 407 } 408 409 // create sequence DDL based off of the existance of auto incrementing columns 410 if (!$create_sequence && isset($prepared_column['auto_increment']) && $prepared_column['auto_increment']) 411 { 412 $create_sequence = $column_name; 413 } 414 } 415 416 // this makes up all the columns in the create table statement 417 $table_sql .= implode(",\n", $columns); 418 419 // Close the table for two DBMS and add to the statements 420 switch ($this->sql_layer) 421 { 422 case 'firebird': 423 $table_sql .= "\n);"; 424 $statements[] = $table_sql; 425 break; 426 427 case 'mssql': 428 $table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : ''); 429 $statements[] = $table_sql; 430 break; 431 } 432 433 // we have yet to create a primary key for this table, 434 // this means that we can add the one we really wanted instead 435 if (!$primary_key_gen) 436 { 437 // Write primary key 438 if (isset($table_data['PRIMARY_KEY'])) 439 { 440 if (!is_array($table_data['PRIMARY_KEY'])) 441 { 442 $table_data['PRIMARY_KEY'] = array($table_data['PRIMARY_KEY']); 443 } 444 445 switch ($this->sql_layer) 446 { 447 case 'mysql_40': 448 case 'mysql_41': 449 case 'postgres': 450 case 'sqlite': 451 $table_sql .= ",\n\t PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 452 break; 453 454 case 'firebird': 455 case 'mssql': 456 // We need the data here 457 $old_return_statements = $this->return_statements; 458 $this->return_statements = true; 459 460 $primary_key_stmts = $this->sql_create_primary_key($table_name, $table_data['PRIMARY_KEY']); 461 foreach ($primary_key_stmts as $pk_stmt) 462 { 463 $statements[] = $pk_stmt; 464 } 465 466 $this->return_statements = $old_return_statements; 467 break; 468 469 case 'oracle': 470 $table_sql .= ",\n\t CONSTRAINT pk_{$table_name} PRIMARY KEY (" . implode(', ', $table_data['PRIMARY_KEY']) . ')'; 471 break; 472 } 473 } 474 } 475 476 // close the table 477 switch ($this->sql_layer) 478 { 479 case 'mysql_41': 480 // make sure the table is in UTF-8 mode 481 $table_sql .= "\n) CHARACTER SET `utf8` COLLATE `utf8_bin`;"; 482 $statements[] = $table_sql; 483 break; 484 485 case 'mysql_40': 486 case 'sqlite': 487 $table_sql .= "\n);"; 488 $statements[] = $table_sql; 489 break; 490 491 case 'postgres': 492 // do we need to add a sequence for auto incrementing columns? 493 if ($create_sequence) 494 { 495 $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; 496 } 497 498 $table_sql .= "\n);"; 499 $statements[] = $table_sql; 500 break; 501 502 case 'oracle': 503 $table_sql .= "\n);"; 504 $statements[] = $table_sql; 505 506 // do we need to add a sequence and a tigger for auto incrementing columns? 507 if ($create_sequence) 508 { 509 // create the actual sequence 510 $statements[] = "CREATE SEQUENCE {$table_name}_seq"; 511 512 // the trigger is the mechanism by which we increment the counter 513 $trigger = "CREATE OR REPLACE TRIGGER t_{$table_name}\n"; 514 $trigger .= "BEFORE INSERT ON {$table_name}\n"; 515 $trigger .= "FOR EACH ROW WHEN (\n"; 516 $trigger .= "\tnew.{$create_sequence} IS NULL OR new.{$create_sequence} = 0\n"; 517 $trigger .= ")\n"; 518 $trigger .= "BEGIN\n"; 519 $trigger .= "\tSELECT {$table_name}_seq.nextval\n"; 520 $trigger .= "\tINTO :new.{$create_sequence}\n"; 521 $trigger .= "\tFROM dual\n"; 522 $trigger .= "END;"; 523 524 $statements[] = $trigger; 525 } 526 break; 527 528 case 'firebird': 529 if ($create_sequence) 530 { 531 $statements[] = "CREATE SEQUENCE {$table_name}_seq;"; 532 } 533 break; 534 } 535 536 // Write Keys 537 if (isset($table_data['KEYS'])) 538 { 539 foreach ($table_data['KEYS'] as $key_name => $key_data) 540 { 541 if (!is_array($key_data[1])) 542 { 543 $key_data[1] = array($key_data[1]); 544 } 545 546 $old_return_statements = $this->return_statements; 547 $this->return_statements = true; 548 549 $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]); 550 551 foreach ($key_stmts as $key_stmt) 552 { 553 $statements[] = $key_stmt; 554 } 555 556 $this->return_statements = $old_return_statements; 557 } 558 } 559 560 // Commit Transaction 561 $statements[] = 'commit'; 562 563 return $this->_sql_run_sql($statements); 291 564 } 292 565 … … 309 582 * ) 310 583 * 311 * For more information have a look at /develop/create_schema_files.php (only available through CVS)584 * For more information have a look at /develop/create_schema_files.php (only available through SVN) 312 585 */ 313 586 function perform_schema_changes($schema_changes) … … 319 592 320 593 $statements = array(); 594 $sqlite = false; 595 596 // For SQLite we need to perform the schema changes in a much more different way 597 if ($this->db->sql_layer == 'sqlite' && $this->return_statements) 598 { 599 $sqlite_data = array(); 600 $sqlite = true; 601 } 321 602 322 603 // Change columns? … … 327 608 foreach ($columns as $column_name => $column_data) 328 609 { 329 $result = $this->sql_column_change($table, $column_name, $column_data); 330 331 if ($this->return_statements) 610 // If the column exists we change it, else we add it ;) 611 if ($column_exists = $this->sql_column_exists($table, $column_name)) 612 { 613 $result = $this->sql_column_change($table, $column_name, $column_data, true); 614 } 615 else 616 { 617 $result = $this->sql_column_add($table, $column_name, $column_data, true); 618 } 619 620 if ($sqlite) 621 { 622 if ($column_exists) 623 { 624 $sqlite_data[$table]['change_columns'][] = $result; 625 } 626 else 627 { 628 $sqlite_data[$table]['add_columns'][] = $result; 629 } 630 } 631 else if ($this->return_statements) 332 632 { 333 633 $statements = array_merge($statements, $result); … … 344 644 foreach ($columns as $column_name => $column_data) 345 645 { 346 // Only add the column if it does not exist yet 347 if (!$this->sql_column_exists($table, $column_name)) 348 { 349 $result = $this->sql_column_add($table, $column_name, $column_data); 350 351 if ($this->return_statements) 646 // Only add the column if it does not exist yet, else change it (to be consistent) 647 if ($column_exists = $this->sql_column_exists($table, $column_name)) 648 { 649 $result = $this->sql_column_change($table, $column_name, $column_data, true); 650 } 651 else 652 { 653 $result = $this->sql_column_add($table, $column_name, $column_data, true); 654 } 655 656 if ($sqlite) 657 { 658 if ($column_exists) 659 { 660 $sqlite_data[$table]['change_columns'][] = $result; 661 } 662 else 663 { 664 $sqlite_data[$table]['add_columns'][] = $result; 665 } 666 } 667 else if ($this->return_statements) 668 { 669 $statements = array_merge($statements, $result); 670 } 671 } 672 } 673 } 674 675 // Remove keys? 676 if (!empty($schema_changes['drop_keys'])) 677 { 678 foreach ($schema_changes['drop_keys'] as $table => $indexes) 679 { 680 foreach ($indexes as $index_name) 681 { 682 $result = $this->sql_index_drop($table, $index_name); 683 684 if ($this->return_statements) 685 { 686 $statements = array_merge($statements, $result); 687 } 688 } 689 } 690 } 691 692 // Drop columns? 693 if (!empty($schema_changes['drop_columns'])) 694 { 695 foreach ($schema_changes['drop_columns'] as $table => $columns) 696 { 697 foreach ($columns as $column) 698 { 699 // Only remove the column if it exists... 700 if ($this->sql_column_exists($table, $column)) 701 { 702 $result = $this->sql_column_remove($table, $column, true); 703 704 if ($sqlite) 705 { 706 $sqlite_data[$table]['drop_columns'][] = $result; 707 } 708 else if ($this->return_statements) 352 709 { 353 710 $statements = array_merge($statements, $result); … … 358 715 } 359 716 360 // Remove keys?361 if (!empty($schema_changes['drop_keys']))362 {363 foreach ($schema_changes['drop_keys'] as $table => $indexes)364 {365 foreach ($indexes as $index_name)366 {367 $result = $this->sql_index_drop($table, $index_name);368 369 if ($this->return_statements)370 {371 $statements = array_merge($statements, $result);372 }373 }374 }375 }376 377 // Drop columns?378 if (!empty($schema_changes['drop_columns']))379 {380 foreach ($schema_changes['drop_columns'] as $table => $columns)381 {382 foreach ($columns as $column)383 {384 $result = $this->sql_column_remove($table, $column);385 386 if ($this->return_statements)387 {388 $statements = array_merge($statements, $result);389 }390 }391 }392 }393 394 717 // Add primary keys? 395 718 if (!empty($schema_changes['add_primary_keys'])) … … 397 720 foreach ($schema_changes['add_primary_keys'] as $table => $columns) 398 721 { 399 $result = $this->sql_create_primary_key($table, $columns); 400 401 if ($this->return_statements) 722 $result = $this->sql_create_primary_key($table, $columns, true); 723 724 if ($sqlite) 725 { 726 $sqlite_data[$table]['primary_key'] = $result; 727 } 728 else if ($this->return_statements) 402 729 { 403 730 $statements = array_merge($statements, $result); … … 440 767 } 441 768 769 if ($sqlite) 770 { 771 foreach ($sqlite_data as $table_name => $sql_schema_changes) 772 { 773 // Create temporary table with original data 774 $statements[] = 'begin'; 775 776 $sql = "SELECT sql 777 FROM sqlite_master 778 WHERE type = 'table' 779 AND name = '{$table_name}' 780 ORDER BY type DESC, name;"; 781 $result = $this->db->sql_query($sql); 782 783 if (!$result) 784 { 785 continue; 786 } 787 788 $row = $this->db->sql_fetchrow($result); 789 $this->db->sql_freeresult($result); 790 791 // Create a backup table and populate it, destroy the existing one 792 $statements[] = preg_replace('#CREATE\s+TABLE\s+"?' . $table_name . '"?#i', 'CREATE TEMPORARY TABLE ' . $table_name . '_temp', $row['sql']); 793 $statements[] = 'INSERT INTO ' . $table_name . '_temp SELECT * FROM ' . $table_name; 794 $statements[] = 'DROP TABLE ' . $table_name; 795 796 // Get the columns... 797 preg_match('#\((.*)\)#s', $row['sql'], $matches); 798 799 $plain_table_cols = trim($matches[1]); 800 $new_table_cols = preg_split('/,(?![\s\w]+\))/m', $plain_table_cols); 801 $column_list = array(); 802 803 foreach ($new_table_cols as $declaration) 804 { 805 $entities = preg_split('#\s+#', trim($declaration)); 806 if ($entities[0] == 'PRIMARY') 807 { 808 continue; 809 } 810 $column_list[] = $entities[0]; 811 } 812 813 // note down the primary key notation because sqlite only supports adding it to the end for the new table 814 $primary_key = false; 815 $_new_cols = array(); 816 817 foreach ($new_table_cols as $key => $declaration) 818 { 819 $entities = preg_split('#\s+#', trim($declaration)); 820 if ($entities[0] == 'PRIMARY') 821 { 822 $primary_key = $declaration; 823 continue; 824 } 825 $_new_cols[] = $declaration; 826 } 827 828 $new_table_cols = $_new_cols; 829 830 // First of all... change columns 831 if (!empty($sql_schema_changes['change_columns'])) 832 { 833 foreach ($sql_schema_changes['change_columns'] as $column_sql) 834 { 835 foreach ($new_table_cols as $key => $declaration) 836 { 837 $entities = preg_split('#\s+#', trim($declaration)); 838 if (strpos($column_sql, $entities[0] . ' ') === 0) 839 { 840 $new_table_cols[$key] = $column_sql; 841 } 842 } 843 } 844 } 845 846 if (!empty($sql_schema_changes['add_columns'])) 847 { 848 foreach ($sql_schema_changes['add_columns'] as $column_sql) 849 { 850 $new_table_cols[] = $column_sql; 851 } 852 } 853 854 // Now drop them... 855 if (!empty($sql_schema_changes['drop_columns'])) 856 { 857 foreach ($sql_schema_changes['drop_columns'] as $column_name) 858 { 859 // Remove from column list... 860 $new_column_list = array(); 861 foreach ($column_list as $key => $value) 862 { 863 if ($value === $column_name) 864 { 865 continue; 866 } 867 868 $new_column_list[] = $value; 869 } 870 871 $column_list = $new_column_list; 872 873 // Remove from table... 874 $_new_cols = array(); 875 foreach ($new_table_cols as $key => $declaration) 876 { 877 $entities = preg_split('#\s+#', trim($declaration)); 878 if (strpos($column_name . ' ', $entities[0] . ' ') === 0) 879 { 880 continue; 881 } 882 $_new_cols[] = $declaration; 883 } 884 $new_table_cols = $_new_cols; 885 } 886 } 887 888 // Primary key... 889 if (!empty($sql_schema_changes['primary_key'])) 890 { 891 $new_table_cols[] = 'PRIMARY KEY (' . implode(', ', $sql_schema_changes['primary_key']) . ')'; 892 } 893 // Add a new one or the old primary key 894 else if ($primary_key !== false) 895 { 896 $new_table_cols[] = $primary_key; 897 } 898 899 $columns = implode(',', $column_list); 900 901 // create a new table and fill it up. destroy the temp one 902 $statements[] = 'CREATE TABLE ' . $table_name . ' (' . implode(',', $new_table_cols) . ');'; 903 $statements[] = 'INSERT INTO ' . $table_name . ' (' . $columns . ') SELECT ' . $columns . ' FROM ' . $table_name . '_temp;'; 904 $statements[] = 'DROP TABLE ' . $table_name . '_temp'; 905 906 $statements[] = 'commit'; 907 } 908 } 909 442 910 if ($this->return_statements) 443 911 { … … 448 916 /** 449 917 * Check if a specified column exist 918 * 919 * @param string $table Table to check the column at 920 * @param string $column_name The column to check 921 * 450 922 * @return bool True if column exists, else false 451 923 */ … … 520 992 $sql = "SELECT column_name 521 993 FROM user_tab_columns 522 WHERE table_name = '{$table}'";994 WHERE LOWER(table_name) = '" . strtolower($table) . "'"; 523 995 $result = $this->db->sql_query($sql); 524 996 while ($row = $this->db->sql_fetchrow($result)) … … 538 1010 $sql = "SELECT RDB\$FIELD_NAME as FNAME 539 1011 FROM RDB\$RELATION_FIELDS 540 WHERE RDB\$RELATION_NAME = ' {$table}'";1012 WHERE RDB\$RELATION_NAME = '" . strtoupper($table) . "'"; 541 1013 $result = $this->db->sql_query($sql); 542 1014 while ($row = $this->db->sql_fetchrow($result)) … … 633 1105 { 634 1106 list($orig_column_type, $column_length) = explode(':', $column_data[0]); 635 636 1107 if (!is_array($this->dbms_type_map[$this->sql_layer][$orig_column_type . ':'])) 637 1108 { … … 692 1163 case 'firebird': 693 1164 $sql .= " {$column_type} "; 1165 $return_array['column_type_sql_type'] = " {$column_type} "; 694 1166 695 1167 if (!is_null($column_data[1])) 696 1168 { 697 1169 $sql .= 'DEFAULT ' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' '; 1170 $return_array['column_type_sql_default'] = ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ' '; 698 1171 } 699 1172 … … 704 1177 { 705 1178 $sql .= ' COLLATE UNICODE'; 1179 } 1180 1181 $return_array['auto_increment'] = false; 1182 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1183 { 1184 $return_array['auto_increment'] = true; 706 1185 } 707 1186 … … 718 1197 if (strpos($column_data[1], '0x') === 0) 719 1198 { 720 $sql_default .= 'DEFAULT (' . $column_data[1] . ') '; 1199 $return_array['default'] = 'DEFAULT (' . $column_data[1] . ') '; 1200 $sql_default .= $return_array['default']; 721 1201 } 722 1202 else 723 1203 { 724 $sql_default .= 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; 725 } 726 } 1204 $return_array['default'] = 'DEFAULT (' . ((is_numeric($column_data[1])) ? $column_data[1] : "'{$column_data[1]}'") . ') '; 1205 $sql_default .= $return_array['default']; 1206 } 1207 } 1208 1209 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1210 { 1211 // $sql .= 'IDENTITY (1, 1) '; 1212 $sql_default .= 'IDENTITY (1, 1) '; 1213 } 1214 1215 $return_array['textimage'] = $column_type === '[text]'; 727 1216 728 1217 $sql .= 'NOT NULL'; … … 730 1219 731 1220 $return_array['column_type_sql_default'] = $sql_default; 1221 732 1222 break; 733 1223 … … 764 1254 // Therefore in oracle we allow NULL's for all DEFAULT '' entries 765 1255 // Oracle does not like setting NOT NULL on a column that is already NOT NULL (this happens only on number fields) 766 if ( preg_match('/number/i', $column_type))1256 if (!preg_match('/number/i', $column_type)) 767 1257 { 768 1258 $sql .= ($column_data[1] === '') ? '' : 'NOT NULL'; 769 1259 } 1260 1261 $return_array['auto_increment'] = false; 1262 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 1263 { 1264 $return_array['auto_increment'] = true; 1265 } 1266 770 1267 break; 771 1268 … … 775 1272 $sql .= " {$column_type} "; 776 1273 1274 $return_array['auto_increment'] = false; 777 1275 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 778 1276 { 779 1277 $default_val = "nextval('{$table_name}_seq')"; 1278 $return_array['auto_increment'] = true; 780 1279 } 781 1280 else if (!is_null($column_data[1])) … … 796 1295 $sql .= " CHECK ({$column_name} >= 0)"; 797 1296 } 1297 798 1298 break; 799 1299 800 1300 case 'sqlite': 1301 $return_array['primary_key_set'] = false; 801 1302 if (isset($column_data[2]) && $column_data[2] == 'auto_increment') 802 1303 { 803 1304 $sql .= ' INTEGER PRIMARY KEY'; 1305 $return_array['primary_key_set'] = true; 804 1306 } 805 1307 else … … 810 1312 $sql .= ' NOT NULL '; 811 1313 $sql .= (!is_null($column_data[1])) ? "DEFAULT '{$column_data[1]}'" : ''; 1314 812 1315 break; 813 1316 } … … 821 1324 * Add new column 822 1325 */ 823 function sql_column_add($table_name, $column_name, $column_data )1326 function sql_column_add($table_name, $column_name, $column_data, $inline = false) 824 1327 { 825 1328 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); … … 829 1332 { 830 1333 case 'firebird': 831 $statements[] = 'ALTER TABLE "' . $table_name . '" ADD "' . $column_name. '" ' . $column_data['column_type_sql'];1334 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD "' . strtoupper($column_name) . '" ' . $column_data['column_type_sql']; 832 1335 break; 833 1336 … … 846 1349 847 1350 case 'postgres': 848 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql']; 1351 if (version_compare($this->db->sql_server_info(true), '8.0', '>=')) 1352 { 1353 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type_sql']; 1354 } 1355 else 1356 { 1357 // old versions cannot add columns with default and null information 1358 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD COLUMN "' . $column_name . '" ' . $column_data['column_type'] . ' ' . $column_data['constraint']; 1359 1360 if (isset($column_data['null'])) 1361 { 1362 if ($column_data['null'] == 'NOT NULL') 1363 { 1364 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET NOT NULL'; 1365 } 1366 } 1367 1368 if (isset($column_data['default'])) 1369 { 1370 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN ' . $column_name . ' SET DEFAULT ' . $column_data['default']; 1371 } 1372 } 1373 849 1374 break; 850 1375 851 1376 case 'sqlite': 1377 1378 if ($inline && $this->return_statements) 1379 { 1380 return $column_name . ' ' . $column_data['column_type_sql']; 1381 } 1382 852 1383 if (version_compare(sqlite_libversion(), '3.0') == -1) 853 1384 { … … 914 1445 * Drop column 915 1446 */ 916 function sql_column_remove($table_name, $column_name )1447 function sql_column_remove($table_name, $column_name, $inline = false) 917 1448 { 918 1449 $statements = array(); … … 921 1452 { 922 1453 case 'firebird': 923 $statements[] = 'ALTER TABLE "' . $table_name . '" DROP "' . $column_name. '"';1454 $statements[] = 'ALTER TABLE ' . $table_name . ' DROP "' . strtoupper($column_name) . '"'; 924 1455 break; 925 1456 … … 942 1473 943 1474 case 'sqlite': 1475 1476 if ($inline && $this->return_statements) 1477 { 1478 return $column_name; 1479 } 1480 944 1481 if (version_compare(sqlite_libversion(), '3.0') == -1) 945 1482 { … … 984 1521 $columns = implode(',', $column_list); 985 1522 986 $new_table_cols = $new_table_cols =preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols);1523 $new_table_cols = preg_replace('/' . $column_name . '[^,]+(?:,|$)/m', '', $new_table_cols); 987 1524 988 1525 // create a new table and fill it up. destroy the temp one … … 1033 1570 1034 1571 /** 1035 * Add primary key1036 */ 1037 function sql_ create_primary_key($table_name, $column)1572 * Drop Table 1573 */ 1574 function sql_table_drop($table_name) 1038 1575 { 1039 1576 $statements = array(); 1040 1577 1578 if (!$this->sql_table_exists($table_name)) 1579 { 1580 return $this->_sql_run_sql($statements); 1581 } 1582 1583 // the most basic operation, get rid of the table 1584 $statements[] = 'DROP TABLE ' . $table_name; 1585 1586 switch ($this->sql_layer) 1587 { 1588 case 'firebird': 1589 $sql = 'SELECT RDB$GENERATOR_NAME as gen 1590 FROM RDB$GENERATORS 1591 WHERE RDB$SYSTEM_FLAG = 0 1592 AND RDB$GENERATOR_NAME = \'' . strtoupper($table_name) . "_GEN'"; 1593 $result = $this->db->sql_query($sql); 1594 1595 // does a generator exist? 1596 if ($row = $this->db->sql_fetchrow($result)) 1597 { 1598 $statements[] = "DROP GENERATOR {$row['gen']};"; 1599 } 1600 $this->db->sql_freeresult($result); 1601 break; 1602 1603 case 'oracle': 1604 $sql = 'SELECT A.REFERENCED_NAME 1605 FROM USER_DEPENDENCIES A, USER_TRIGGERS B 1606 WHERE A.REFERENCED_TYPE = \'SEQUENCE\' 1607 AND A.NAME = B.TRIGGER_NAME 1608 AND B.TABLE_NAME = \'' . strtoupper($table_name) . "'"; 1609 $result = $this->db->sql_query($sql); 1610 1611 // any sequences ref'd to this table's triggers? 1612 while ($row = $this->db->sql_fetchrow($result)) 1613 { 1614 $statements[] = "DROP SEQUENCE {$row['referenced_name']}"; 1615 } 1616 $this->db->sql_freeresult($result); 1617 1618 case 'postgres': 1619 // PGSQL does not "tightly" bind sequences and tables, we must guess... 1620 $sql = "SELECT relname 1621 FROM pg_class 1622 WHERE relkind = 'S' 1623 AND relname = '{$table_name}_seq'"; 1624 $result = $this->db->sql_query($sql); 1625 1626 // We don't even care about storing the results. We already know the answer if we get rows back. 1627 if ($this->db->sql_fetchrow($result)) 1628 { 1629 $statements[] = "DROP SEQUENCE {$table_name}_seq;\n"; 1630 } 1631 $this->db->sql_freeresult($result); 1632 break; 1633 } 1634 1635 return $this->_sql_run_sql($statements); 1636 } 1637 1638 /** 1639 * Add primary key 1640 */ 1641 function sql_create_primary_key($table_name, $column, $inline = false) 1642 { 1643 $statements = array(); 1644 1041 1645 switch ($this->sql_layer) 1042 1646 { 1043 1647 case 'firebird': 1044 1648 case 'postgres': 1649 case 'mysql_40': 1650 case 'mysql_41': 1045 1651 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')'; 1046 1652 break; … … 1055 1661 break; 1056 1662 1057 case 'mysql_40':1058 case 'mysql_41':1059 $statements[] = 'ALTER TABLE ' . $table_name . ' ADD PRIMARY KEY (' . implode(', ', $column) . ')';1060 break;1061 1062 1663 case 'oracle': 1063 1664 $statements[] = 'ALTER TABLE ' . $table_name . 'add CONSTRAINT pk_' . $table_name . ' PRIMARY KEY (' . implode(', ', $column) . ')'; … … 1065 1666 1066 1667 case 'sqlite': 1668 1669 if ($inline && $this->return_statements) 1670 { 1671 return $column; 1672 } 1673 1067 1674 $sql = "SELECT sql 1068 1675 FROM sqlite_master … … 1205 1812 $sql = "SELECT LOWER(RDB\$INDEX_NAME) as index_name 1206 1813 FROM RDB\$INDICES 1207 WHERE RDB\$RELATION_NAME = " . strtoupper($table_name) . "1814 WHERE RDB\$RELATION_NAME = '" . strtoupper($table_name) . "' 1208 1815 AND RDB\$UNIQUE_FLAG IS NULL 1209 1816 AND RDB\$FOREIGN_KEY IS NULL"; … … 1232 1839 $sql = "SELECT index_name 1233 1840 FROM user_indexes 1234 WHERE table_name = '" . $table_name . "' 1235 AND generated = 'N'"; 1841 WHERE table_name = '" . strtoupper($table_name) . "' 1842 AND generated = 'N' 1843 AND uniqueness = 'NONUNIQUE'"; 1844 $col = 'index_name'; 1236 1845 break; 1237 1846 … … 1271 1880 * Change column type (not name!) 1272 1881 */ 1273 function sql_column_change($table_name, $column_name, $column_data )1882 function sql_column_change($table_name, $column_name, $column_data, $inline = false) 1274 1883 { 1275 1884 $column_data = $this->sql_prepare_column_data($table_name, $column_name, $column_data); … … 1280 1889 case 'firebird': 1281 1890 // Change type... 1282 $statements[] = 'ALTER TABLE "' . $table_name . '" ALTER COLUMN "' . $column_name . '" TYPE ' . ' ' . $column_data['column_type_sql']; 1891 if (!empty($column_data['column_type_sql_default'])) 1892 { 1893 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type']; 1894 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" SET DEFAULT ' . ' ' . $column_data['column_type_sql_default']; 1895 } 1896 else 1897 { 1898 $statements[] = 'ALTER TABLE ' . $table_name . ' ALTER COLUMN "' . strtoupper($column_name) . '" TYPE ' . ' ' . $column_data['column_type_sql_type']; 1899 } 1283 1900 break; 1284 1901 1285 1902 case 'mssql': 1286 1903 $statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql']; 1904 1905 if (!empty($column_data['default'])) 1906 { 1907 // Using TRANSACT-SQL for this statement because we do not want to have colliding data if statements are executed at a later stage 1908 $statements[] = "DECLARE @drop_default_name VARCHAR(100), @cmd VARCHAR(1000) 1909 SET @drop_default_name = 1910 (SELECT so.name FROM sysobjects so 1911 JOIN sysconstraints sc ON so.id = sc.constid 1912 WHERE object_name(so.parent_obj) = '{$table_name}' 1913 AND so.xtype = 'D' 1914 AND sc.colid = (SELECT colid FROM syscolumns 1915 WHERE id = object_id('{$table_name}') 1916 AND name = '{$column_name}')) 1917 IF @drop_default_name <> '' 1918 BEGIN 1919 SET @cmd = 'ALTER TABLE [{$table_name}] DROP CONSTRAINT [' + @drop_default_name + ']' 1920 EXEC(@cmd) 1921 END 1922 SET @cmd = 'ALTER TABLE [{$table_name}] ADD CONSTRAINT [DF_{$table_name}_{$column_name}_1] {$column_data['default']} FOR [{$column_name}]' 1923 EXEC(@cmd)"; 1924 } 1287 1925 break; 1288 1926 … … 1361 1999 case 'sqlite': 1362 2000 2001 if ($inline && $this->return_statements) 2002 { 2003 return $column_name . ' ' . $column_data['column_type_sql']; 2004 } 2005 1363 2006 $sql = "SELECT sql 1364 2007 FROM sqlite_master
Note:
See TracChangeset
for help on using the changeset viewer.
