1<?php 2 3/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */ 4 5/** 6 * Creates, checks or alters tables from DB_Table definitions. 7 * 8 * DB_Table_Manager provides database automated table creation 9 * facilities. 10 * 11 * PHP versions 4 and 5 12 * 13 * LICENSE: 14 * 15 * Copyright (c) 1997-2007, Paul M. Jones <pmjones@php.net> 16 * David C. Morse <morse@php.net> 17 * Mark Wiesemann <wiesemann@php.net> 18 * All rights reserved. 19 * 20 * Redistribution and use in source and binary forms, with or without 21 * modification, are permitted provided that the following conditions 22 * are met: 23 * 24 * * Redistributions of source code must retain the above copyright 25 * notice, this list of conditions and the following disclaimer. 26 * * Redistributions in binary form must reproduce the above copyright 27 * notice, this list of conditions and the following disclaimer in the 28 * documentation and/or other materials provided with the distribution. 29 * * The names of the authors may not be used to endorse or promote products 30 * derived from this software without specific prior written permission. 31 * 32 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS 33 * IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 34 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR 35 * PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 36 * CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, 37 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, 38 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR 39 * PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY 40 * OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING 41 * NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS 42 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 43 * 44 * @category Database 45 * @package DB_Table 46 * @author Paul M. Jones <pmjones@php.net> 47 * @author David C. Morse <morse@php.net> 48 * @author Mark Wiesemann <wiesemann@php.net> 49 * @license http://opensource.org/licenses/bsd-license.php New BSD License 50 * @version CVS: $Id: Manager.php,v 1.40 2008/12/25 19:56:35 wiesemann Exp $ 51 * @link http://pear.php.net/package/DB_Table 52 */ 53 54require_once 'DB/Table.php'; 55 56 57/** 58* Valid types for the different data types in the different DBMS. 59*/ 60$GLOBALS['_DB_TABLE']['valid_type'] = array( 61 'fbsql' => array( // currently not supported 62 'boolean' => '', 63 'char' => '', 64 'varchar' => '', 65 'smallint' => '', 66 'integer' => '', 67 'bigint' => '', 68 'decimal' => '', 69 'single' => '', 70 'double' => '', 71 'clob' => '', 72 'date' => '', 73 'time' => '', 74 'timestamp' => '' 75 ), 76 'ibase' => array( 77 'boolean' => array('char', 'integer', 'real', 'smallint'), 78 'char' => array('char', 'varchar'), 79 'varchar' => 'varchar', 80 'smallint' => array('integer', 'smallint'), 81 'integer' => 'integer', 82 'bigint' => array('bigint', 'integer'), 83 'decimal' => 'numeric', 84 'single' => array('double precision', 'float'), 85 'double' => 'double precision', 86 'clob' => 'blob', 87 'date' => 'date', 88 'time' => 'time', 89 'timestamp' => 'timestamp' 90 ), 91 'mssql' => array( // currently not supported 92 'boolean' => '', 93 'char' => '', 94 'varchar' => '', 95 'smallint' => '', 96 'integer' => '', 97 'bigint' => '', 98 'decimal' => '', 99 'single' => '', 100 'double' => '', 101 'clob' => '', 102 'date' => '', 103 'time' => '', 104 'timestamp' => '' 105 ), 106 'mysql' => array( 107 'boolean' => array('char', 'decimal', 'int', 'real', 'tinyint'), 108 'char' => array('char', 'string', 'varchar'), 109 'varchar' => array('char', 'string', 'varchar'), 110 'smallint' => array('smallint', 'int'), 111 'integer' => 'int', 112 'bigint' => array('int', 'bigint'), 113 'decimal' => array('decimal', 'real'), 114 'single' => array('double', 'real'), 115 'double' => array('double', 'real'), 116 'clob' => array('blob', 'longtext', 'tinytext', 'text', 'mediumtext'), 117 'date' => array('char', 'date', 'string'), 118 'time' => array('char', 'string', 'time'), 119 'timestamp' => array('char', 'datetime', 'string') 120 ), 121 'mysqli' => array( 122 'boolean' => array('char', 'decimal', 'tinyint'), 123 'char' => array('char', 'varchar'), 124 'varchar' => array('char', 'varchar'), 125 'smallint' => array('smallint', 'int'), 126 'integer' => 'int', 127 'bigint' => array('int', 'bigint'), 128 'decimal' => 'decimal', 129 'single' => array('double', 'float'), 130 'double' => 'double', 131 'clob' => array('blob', 'longtext', 'tinytext', 'text', 'mediumtext'), 132 'date' => array('char', 'date', 'varchar'), 133 'time' => array('char', 'time', 'varchar'), 134 'timestamp' => array('char', 'datetime', 'varchar') 135 ), 136 'oci8' => array( 137 'boolean' => 'number', 138 'char' => array('char', 'varchar2'), 139 'varchar' => 'varchar2', 140 'smallint' => 'number', 141 'integer' => 'number', 142 'bigint' => 'number', 143 'decimal' => 'number', 144 'single' => array('float', 'number'), 145 'double' => array('float', 'number'), 146 'clob' => 'clob', 147 'date' => array('char', 'date'), 148 'time' => array('char', 'date'), 149 'timestamp' => array('char', 'date') 150 ), 151 'pgsql' => array( 152 'boolean' => array('bool', 'numeric'), 153 'char' => array('bpchar', 'varchar'), 154 'varchar' => 'varchar', 155 'smallint' => array('int2', 'int4'), 156 'integer' => 'int4', 157 'bigint' => array('int4', 'int8'), 158 'decimal' => 'numeric', 159 'single' => array('float4', 'float8'), 160 'double' => 'float8', 161 'clob' => array('oid', 'text'), 162 'date' => array('bpchar', 'date'), 163 'time' => array('bpchar', 'time'), 164 'timestamp' => array('bpchar', 'timestamp') 165 ), 166 'sqlite' => array( 167 'boolean' => 'boolean', 168 'char' => 'char', 169 'varchar' => array('char', 'varchar'), 170 'smallint' => array('int', 'smallint'), 171 'integer' => array('int', 'integer'), 172 'bigint' => array('int', 'bigint'), 173 'decimal' => array('decimal', 'numeric'), 174 'single' => array('double', 'float'), 175 'double' => 'double', 176 'clob' => array('clob', 'longtext'), 177 'date' => 'date', 178 'time' => 'time', 179 'timestamp' => array('datetime', 'timestamp') 180 ), 181); 182 183/** 184* Mapping between DB_Table and MDB2 data types. 185*/ 186$GLOBALS['_DB_TABLE']['mdb2_type'] = array( 187 'boolean' => 'boolean', 188 'char' => 'text', 189 'varchar' => 'text', 190 'smallint' => 'integer', 191 'integer' => 'integer', 192 'bigint' => 'integer', 193 'decimal' => 'decimal', 194 'single' => 'float', 195 'double' => 'float', 196 'clob' => 'clob', 197 'date' => 'date', 198 'time' => 'time', 199 'timestamp' => 'timestamp' 200); 201 202/** 203 * Creates, checks or alters tables from DB_Table definitions. 204 * 205 * DB_Table_Manager provides database automated table creation 206 * facilities. 207 * 208 * @category Database 209 * @package DB_Table 210 * @author Paul M. Jones <pmjones@php.net> 211 * @author David C. Morse <morse@php.net> 212 * @author Mark Wiesemann <wiesemann@php.net> 213 * @version Release: 1.5.6 214 * @link http://pear.php.net/package/DB_Table 215 */ 216class DB_Table_Manager { 217 218 219 /** 220 * 221 * Create the table based on DB_Table column and index arrays. 222 * 223 * @static 224 * 225 * @access public 226 * 227 * @param object &$db A PEAR DB/MDB2 object. 228 * 229 * @param string $table The table name to connect to in the database. 230 * 231 * @param mixed $column_set A DB_Table $this->col array. 232 * 233 * @param mixed $index_set A DB_Table $this->idx array. 234 * 235 * @return mixed Boolean false if there was no attempt to create the 236 * table, boolean true if the attempt succeeded, and a PEAR_Error if 237 * the attempt failed. 238 * 239 */ 240 241 function create(&$db, $table, $column_set, $index_set) 242 { 243 if (is_subclass_of($db, 'db_common')) { 244 $backend = 'db'; 245 } elseif (is_subclass_of($db, 'mdb2_driver_common')) { 246 $backend = 'mdb2'; 247 $db->loadModule('Manager'); 248 } 249 $phptype = $db->phptype; 250 251 // columns to be created 252 $column = array(); 253 254 // max. value for scope (only used with MDB2 as backend) 255 $max_scope = 0; 256 257 // indexes to be created 258 $indexes = array(); 259 260 // check the table name 261 $name_check = DB_Table_Manager::_validateTableName($table); 262 if (PEAR::isError($name_check)) { 263 return $name_check; 264 } 265 266 267 // ------------------------------------------------------------- 268 // 269 // validate each column mapping and build the individual 270 // definitions, and note column indexes as we go. 271 // 272 273 if (is_null($column_set)) { 274 $column_set = array(); 275 } 276 277 foreach ($column_set as $colname => $val) { 278 279 $colname = trim($colname); 280 281 // check the column name 282 $name_check = DB_Table_Manager::_validateColumnName($colname); 283 if (PEAR::isError($name_check)) { 284 return $name_check; 285 } 286 287 288 // prepare variables 289 $type = (isset($val['type'])) ? $val['type'] : null; 290 $size = (isset($val['size'])) ? $val['size'] : null; 291 $scope = (isset($val['scope'])) ? $val['scope'] : null; 292 $require = (isset($val['require'])) ? $val['require'] : null; 293 $default = (isset($val['default'])) ? $val['default'] : null; 294 295 if ($backend == 'mdb2') { 296 297 // get the declaration string 298 $result = DB_Table_Manager::getDeclareMDB2($type, 299 $size, $scope, $require, $default, $max_scope); 300 301 // did it work? 302 if (PEAR::isError($result)) { 303 $result->userinfo .= " ('$colname')"; 304 return $result; 305 } 306 307 // add the declaration to the array of all columns 308 $column[$colname] = $result; 309 310 } else { 311 312 // get the declaration string 313 $result = DB_Table_Manager::getDeclare($phptype, $type, 314 $size, $scope, $require, $default); 315 316 // did it work? 317 if (PEAR::isError($result)) { 318 $result->userinfo .= " ('$colname')"; 319 return $result; 320 } 321 322 // add the declaration to the array of all columns 323 $column[] = "$colname $result"; 324 325 } 326 327 } 328 329 330 // ------------------------------------------------------------- 331 // 332 // validate the indexes. 333 // 334 335 if (is_null($index_set)) { 336 $index_set = array(); 337 } 338 339 $count_primary_keys = 0; 340 341 foreach ($index_set as $idxname => $val) { 342 343 list($type, $cols) = DB_Table_Manager::_getIndexTypeAndColumns($val, $idxname); 344 345 $newIdxName = ''; 346 347 // check the index definition 348 $index_check = DB_Table_Manager::_validateIndexName($idxname, 349 $table, $phptype, $type, $cols, $column_set, $newIdxName); 350 if (PEAR::isError($index_check)) { 351 return $index_check; 352 } 353 354 // check number of primary keys (only one is allowed) 355 if ($type == 'primary') { 356 // SQLite does not support primary keys 357 if ($phptype == 'sqlite') { 358 return DB_Table::throwError(DB_TABLE_ERR_DECLARE_PRIM_SQLITE); 359 } 360 $count_primary_keys++; 361 } 362 if ($count_primary_keys > 1) { 363 return DB_Table::throwError(DB_TABLE_ERR_DECLARE_PRIMARY); 364 } 365 366 // create index entry 367 if ($backend == 'mdb2') { 368 369 // array with column names as keys 370 $idx_cols = array(); 371 foreach ($cols as $col) { 372 $idx_cols[$col] = array(); 373 } 374 375 switch ($type) { 376 case 'primary': 377 $indexes['primary'][$newIdxName] = 378 array('fields' => $idx_cols, 379 'primary' => true); 380 break; 381 case 'unique': 382 $indexes['unique'][$newIdxName] = 383 array('fields' => $idx_cols, 384 'unique' => true); 385 break; 386 case 'normal': 387 $indexes['normal'][$newIdxName] = 388 array('fields' => $idx_cols); 389 break; 390 } 391 392 } else { 393 394 $indexes[] = DB_Table_Manager::getDeclareForIndex($phptype, 395 $type, $newIdxName, $table, $cols); 396 397 } 398 399 } 400 401 402 // ------------------------------------------------------------- 403 // 404 // now for the real action: create the table and indexes! 405 // 406 if ($backend == 'mdb2') { 407 408 // save user defined 'decimal_places' option 409 $decimal_places = $db->getOption('decimal_places'); 410 $db->setOption('decimal_places', $max_scope); 411 412 // attempt to create the table 413 $result = $db->manager->createTable($table, $column); 414 // restore user defined 'decimal_places' option 415 $db->setOption('decimal_places', $decimal_places); 416 if (PEAR::isError($result)) { 417 return $result; 418 } 419 420 } else { 421 422 // build the CREATE TABLE command 423 $cmd = "CREATE TABLE $table (\n\t"; 424 $cmd .= implode(",\n\t", $column); 425 $cmd .= "\n)"; 426 427 // attempt to create the table 428 $result = $db->query($cmd); 429 if (PEAR::isError($result)) { 430 return $result; 431 } 432 433 } 434 435 $result = DB_Table_Manager::_createIndexesAndContraints($db, $backend, 436 $table, $indexes); 437 if (PEAR::isError($result)) { 438 return $result; 439 } 440 441 // we're done! 442 return true; 443 } 444 445 446 /** 447 * 448 * Verify whether the table and columns exist, whether the columns 449 * have the right type and whether the indexes exist. 450 * 451 * @static 452 * 453 * @access public 454 * 455 * @param object &$db A PEAR DB/MDB2 object. 456 * 457 * @param string $table The table name to connect to in the database. 458 * 459 * @param mixed $column_set A DB_Table $this->col array. 460 * 461 * @param mixed $index_set A DB_Table $this->idx array. 462 * 463 * @return mixed Boolean true if the verification was successful, and a 464 * PEAR_Error if verification failed. 465 * 466 */ 467 468 function verify(&$db, $table, $column_set, $index_set) 469 { 470 if (is_subclass_of($db, 'db_common')) { 471 $backend = 'db'; 472 $reverse =& $db; 473 $table_info_mode = DB_TABLEINFO_FULL; 474 $table_info_error = DB_ERROR_NEED_MORE_DATA; 475 } elseif (is_subclass_of($db, 'mdb2_driver_common')) { 476 $backend = 'mdb2'; 477 $reverse =& $this->db->loadModule('Reverse'); 478 $table_info_mode = MDB2_TABLEINFO_FULL; 479 $table_info_error = MDB2_ERROR_NEED_MORE_DATA; 480 } 481 $phptype = $db->phptype; 482 483 // check #1: does the table exist? 484 485 // check the table name 486 $name_check = DB_Table_Manager::_validateTableName($table); 487 if (PEAR::isError($name_check)) { 488 return $name_check; 489 } 490 491 // get table info 492 $tableInfo = $reverse->tableInfo($table, $table_info_mode); 493 if (PEAR::isError($tableInfo)) { 494 if ($tableInfo->getCode() == $table_info_error) { 495 return DB_Table::throwError( 496 DB_TABLE_ERR_VER_TABLE_MISSING, 497 "(table='$table')" 498 ); 499 } 500 return $tableInfo; 501 } 502 $tableInfoOrder = array_change_key_case($tableInfo['order'], CASE_LOWER); 503 504 if (is_null($column_set)) { 505 $column_set = array(); 506 } 507 508 foreach ($column_set as $colname => $val) { 509 $colname = strtolower(trim($colname)); 510 511 // check the column name 512 $name_check = DB_Table_Manager::_validateColumnName($colname); 513 if (PEAR::isError($name_check)) { 514 return $name_check; 515 } 516 517 // check #2: do all columns exist? 518 $column_exists = DB_Table_Manager::_columnExists($colname, 519 $tableInfoOrder, 'verify'); 520 if (PEAR::isError($column_exists)) { 521 return $column_exists; 522 } 523 524 // check #3: do all columns have the right type? 525 526 // check whether the column type is a known type 527 $type_check = DB_Table_Manager::_validateColumnType($phptype, $val['type']); 528 if (PEAR::isError($type_check)) { 529 return $type_check; 530 } 531 532 // check whether the column has the right type 533 $type_check = DB_Table_Manager::_checkColumnType($phptype, 534 $colname, $val['type'], $tableInfoOrder, $tableInfo, 'verify'); 535 if (PEAR::isError($type_check)) { 536 return $type_check; 537 } 538 539 } 540 541 // check #4: do all indexes exist? 542 $table_indexes = DB_Table_Manager::getIndexes($db, $table); 543 if (PEAR::isError($table_indexes)) { 544 return $table_indexes; 545 } 546 547 if (is_null($index_set)) { 548 $index_set = array(); 549 } 550 551 foreach ($index_set as $idxname => $val) { 552 553 list($type, $cols) = DB_Table_Manager::_getIndexTypeAndColumns($val, $idxname); 554 555 $newIdxName = ''; 556 557 // check the index definition 558 $index_check = DB_Table_Manager::_validateIndexName($idxname, 559 $table, $phptype, $type, $cols, $column_set, $newIdxName); 560 if (PEAR::isError($index_check)) { 561 return $index_check; 562 } 563 564 // check whether the index has the right type and has all 565 // specified columns 566 $index_check = DB_Table_Manager::_checkIndex($idxname, $newIdxName, 567 $type, $cols, $table_indexes, 'verify'); 568 if (PEAR::isError($index_check)) { 569 return $index_check; 570 } 571 572 } 573 574 return true; 575 } 576 577 578 /** 579 * 580 * Alter columns and indexes of a table based on DB_Table column and index 581 * arrays. 582 * 583 * @static 584 * 585 * @access public 586 * 587 * @param object &$db A PEAR DB/MDB2 object. 588 * 589 * @param string $table The table name to connect to in the database. 590 * 591 * @param mixed $column_set A DB_Table $this->col array. 592 * 593 * @param mixed $index_set A DB_Table $this->idx array. 594 * 595 * @return bool|object True if altering was successful or a PEAR_Error on 596 * failure. 597 * 598 */ 599 600 function alter(&$db, $table, $column_set, $index_set) 601 { 602 $phptype = $db->phptype; 603 604 if (is_subclass_of($db, 'db_common')) { 605 $backend = 'db'; 606 $reverse =& $db; 607 // workaround for missing index and constraint information methods 608 // in PEAR::DB ==> use adopted code from MDB2's driver classes 609 require_once 'DB/Table/Manager/' . $phptype . '.php'; 610 $classname = 'DB_Table_Manager_' . $phptype; 611 $dbtm =& new $classname(); 612 $dbtm->_db =& $db; // pass database instance to the 'workaround' class 613 $manager =& $dbtm; 614 $table_info_mode = DB_TABLEINFO_FULL; 615 $ok_const = DB_OK; 616 } elseif (is_subclass_of($db, 'mdb2_driver_common')) { 617 $backend = 'mdb2'; 618 $db->loadModule('Reverse'); 619 $manager =& $db->manager; 620 $reverse =& $db->reverse; 621 $table_info_mode = MDB2_TABLEINFO_FULL; 622 $ok_const = MDB2_OK; 623 } 624 625 // get table info 626 $tableInfo = $reverse->tableInfo($table, $table_info_mode); 627 if (PEAR::isError($tableInfo)) { 628 return $tableInfo; 629 } 630 $tableInfoOrder = array_change_key_case($tableInfo['order'], CASE_LOWER); 631 632 // emulate MDB2 Reverse extension for PEAR::DB as backend 633 if (is_subclass_of($db, 'db_common')) { 634 $reverse =& $dbtm; 635 } 636 637 // check (and alter) columns 638 if (is_null($column_set)) { 639 $column_set = array(); 640 } 641 642 foreach ($column_set as $colname => $val) { 643 $colname = strtolower(trim($colname)); 644 645 // check the column name 646 $name_check = DB_Table_Manager::_validateColumnName($colname); 647 if (PEAR::isError($name_check)) { 648 return $name_check; 649 } 650 651 // check the column's existence 652 $column_exists = DB_Table_Manager::_columnExists($colname, 653 $tableInfoOrder, 'alter'); 654 if (PEAR::isError($column_exists)) { 655 return $column_exists; 656 } 657 if ($column_exists === false) { // add the column 658 $definition = DB_Table_Manager::_getColumnDefinition($backend, 659 $phptype, $val); 660 if (PEAR::isError($definition)) { 661 return $definition; 662 } 663 $changes = array('add' => array($colname => $definition)); 664 if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) { 665 echo "(alter) New table field will be added ($colname):\n"; 666 var_dump($changes); 667 echo "\n"; 668 } 669 $result = $manager->alterTable($table, $changes, false); 670 if (PEAR::isError($result)) { 671 return $result; 672 } 673 continue; 674 } 675 676 // check whether the column type is a known type 677 $type_check = DB_Table_Manager::_validateColumnType($phptype, $val['type']); 678 if (PEAR::isError($type_check)) { 679 return $type_check; 680 } 681 682 // check whether the column has the right type 683 $type_check = DB_Table_Manager::_checkColumnType($phptype, 684 $colname, $val['type'], $tableInfoOrder, $tableInfo, 'alter'); 685 if (PEAR::isError($type_check)) { 686 return $type_check; 687 } 688 if ($type_check === false) { // change the column type 689 $definition = DB_Table_Manager::_getColumnDefinition($backend, 690 $phptype, $val); 691 if (PEAR::isError($definition)) { 692 return $definition; 693 } 694 $changes = array('change' => 695 array($colname => array('type' => null, 696 'definition' => $definition))); 697 if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) { 698 echo "(alter) Table field's type will be changed ($colname):\n"; 699 var_dump($changes); 700 echo "\n"; 701 } 702 $result = $manager->alterTable($table, $changes, false); 703 if (PEAR::isError($result)) { 704 return $result; 705 } 706 continue; 707 } 708 709 } 710 711 // get information about indexes / constraints 712 $table_indexes = DB_Table_Manager::getIndexes($db, $table); 713 if (PEAR::isError($table_indexes)) { 714 return $table_indexes; 715 } 716 717 // check (and alter) indexes / constraints 718 if (is_null($index_set)) { 719 $index_set = array(); 720 } 721 722 foreach ($index_set as $idxname => $val) { 723 724 list($type, $cols) = DB_Table_Manager::_getIndexTypeAndColumns($val, $idxname); 725 726 $newIdxName = ''; 727 728 // check the index definition 729 $index_check = DB_Table_Manager::_validateIndexName($idxname, 730 $table, $phptype, $type, $cols, $column_set, $newIdxName); 731 if (PEAR::isError($index_check)) { 732 return $index_check; 733 } 734 735 // check whether the index has the right type and has all 736 // specified columns 737 $index_check = DB_Table_Manager::_checkIndex($idxname, $newIdxName, 738 $type, $cols, $table_indexes, 'alter'); 739 if (PEAR::isError($index_check)) { 740 return $index_check; 741 } 742 if ($index_check === false) { // (1) drop wrong index/constraint 743 // (2) add right index/constraint 744 if ($backend == 'mdb2') { 745 // save user defined 'idxname_format' option 746 $idxname_format = $db->getOption('idxname_format'); 747 $db->setOption('idxname_format', '%s'); 748 } 749 // drop index/constraint only if it exists 750 foreach (array('normal', 'unique', 'primary') as $idx_type) { 751 if (array_key_exists(strtolower($newIdxName), 752 $table_indexes[$idx_type])) { 753 if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) { 754 echo "(alter) Index/constraint will be deleted (name: '$newIdxName', type: '$idx_type').\n"; 755 } 756 if ($idx_type == 'normal') { 757 $result = $manager->dropIndex($table, $newIdxName); 758 } else { 759 $result = $manager->dropConstraint($table, $newIdxName); 760 } 761 if (PEAR::isError($result)) { 762 if ($backend == 'mdb2') { 763 // restore user defined 'idxname_format' option 764 $db->setOption('idxname_format', $idxname_format); 765 } 766 return $result; 767 } 768 break; 769 } 770 } 771 772 // prepare index/constraint definition 773 $indexes = array(); 774 if ($backend == 'mdb2') { 775 776 // array with column names as keys 777 $idx_cols = array(); 778 foreach ($cols as $col) { 779 $idx_cols[$col] = array(); 780 } 781 782 switch ($type) { 783 case 'primary': 784 $indexes['primary'][$newIdxName] = 785 array('fields' => $idx_cols, 786 'primary' => true); 787 break; 788 case 'unique': 789 $indexes['unique'][$newIdxName] = 790 array('fields' => $idx_cols, 791 'unique' => true); 792 break; 793 case 'normal': 794 $indexes['normal'][$newIdxName] = 795 array('fields' => $idx_cols); 796 break; 797 } 798 799 } else { 800 801 $indexes[] = DB_Table_Manager::getDeclareForIndex($phptype, 802 $type, $newIdxName, $table, $cols); 803 804 } 805 806 // create index/constraint 807 if (array_key_exists('debug', $GLOBALS['_DB_TABLE'])) { 808 echo "(alter) New index/constraint will be created (name: '$newIdxName', type: '$type'):\n"; 809 var_dump($indexes); 810 echo "\n"; 811 } 812 $result = DB_Table_Manager::_createIndexesAndContraints( 813 $db, $backend, $table, $indexes); 814 if ($backend == 'mdb2') { 815 // restore user defined 'idxname_format' option 816 $db->setOption('idxname_format', $idxname_format); 817 } 818 if (PEAR::isError($result)) { 819 return $result; 820 } 821 822 continue; 823 } 824 825 } 826 827 return true; 828 } 829 830 831 /** 832 * 833 * Check whether a table exists. 834 * 835 * @static 836 * 837 * @access public 838 * 839 * @param object &$db A PEAR DB/MDB2 object. 840 * 841 * @param string $table The table name that should be checked. 842 * 843 * @return bool|object True if the table exists, false if not, or a 844 * PEAR_Error on failure. 845 * 846 */ 847 848 function tableExists(&$db, $table) 849 { 850 if (is_subclass_of($db, 'db_common')) { 851 $list = $db->getListOf('tables'); 852 } elseif (is_subclass_of($db, 'mdb2_driver_common')) { 853 $db->loadModule('Manager'); 854 $list = $db->manager->listTables(); 855 } 856 if (PEAR::isError($list)) { 857 return $list; 858 } 859 array_walk($list, create_function('&$value,$key', 860 '$value = trim(strtolower($value));')); 861 return in_array(strtolower($table), $list); 862 } 863 864 865 /** 866 * 867 * Get the column declaration string for a DB_Table column. 868 * 869 * @static 870 * 871 * @access public 872 * 873 * @param string $phptype The DB/MDB2 phptype key. 874 * 875 * @param string $coltype The DB_Table column type. 876 * 877 * @param int $size The size for the column (needed for string and 878 * decimal). 879 * 880 * @param int $scope The scope for the column (needed for decimal). 881 * 882 * @param bool $require True if the column should be NOT NULL, false 883 * allowed to be NULL. 884 * 885 * @param string $default The SQL calculation for a default value. 886 * 887 * @return string|object A declaration string on success, or a 888 * PEAR_Error on failure. 889 * 890 */ 891 892 function getDeclare($phptype, $coltype, $size = null, $scope = null, 893 $require = null, $default = null) 894 { 895 // validate char/varchar/decimal type declaration 896 $validation = DB_Table_Manager::_validateTypeDeclaration($coltype, $size, 897 $scope); 898 if (PEAR::isError($validation)) { 899 return $validation; 900 } 901 902 // map of column types and declarations for this RDBMS 903 $map = $GLOBALS['_DB_TABLE']['type'][$phptype]; 904 905 // is it a recognized column type? 906 $types = array_keys($map); 907 if (! in_array($coltype, $types)) { 908 return DB_Table::throwError( 909 DB_TABLE_ERR_DECLARE_TYPE, 910 "('$coltype')" 911 ); 912 } 913 914 // basic declaration 915 switch ($coltype) { 916 917 case 'char': 918 case 'varchar': 919 $declare = $map[$coltype] . "($size)"; 920 break; 921 922 case 'decimal': 923 $declare = $map[$coltype] . "($size,$scope)"; 924 break; 925 926 default: 927 $declare = $map[$coltype]; 928 break; 929 930 } 931 932 // set the "NULL"/"NOT NULL" portion 933 $null = ' NULL'; 934 if ($phptype == 'ibase') { // Firebird does not like 'NULL' 935 $null = ''; // in CREATE TABLE 936 } 937 if ($phptype == 'pgsql') { // PostgreSQL does not like 'NULL' 938 $null = ''; // in ALTER TABLE 939 } 940 $declare .= ($require) ? ' NOT NULL' : $null; 941 942 // set the "DEFAULT" portion 943 if ($default) { 944 switch ($coltype) { 945 case 'char': 946 case 'varchar': 947 case 'clob': 948 $declare .= " DEFAULT '$default'"; 949 break; 950 951 default: 952 $declare .= " DEFAULT $default"; 953 break; 954 } 955 } 956 957 // done 958 return $declare; 959 } 960 961 962 /** 963 * 964 * Get the column declaration string for a DB_Table column. 965 * 966 * @static 967 * 968 * @access public 969 * 970 * @param string $coltype The DB_Table column type. 971 * 972 * @param int $size The size for the column (needed for string and 973 * decimal). 974 * 975 * @param int $scope The scope for the column (needed for decimal). 976 * 977 * @param bool $require True if the column should be NOT NULL, false 978 * allowed to be NULL. 979 * 980 * @param string $default The SQL calculation for a default value. 981 * 982 * @param int $max_scope The maximal scope for all table column 983 * (pass-by-reference). 984 * 985 * @return string|object A MDB2 column definition array on success, or a 986 * PEAR_Error on failure. 987 * 988 */ 989 990 function getDeclareMDB2($coltype, $size = null, $scope = null, 991 $require = null, $default = null, &$max_scope) 992 { 993 // validate char/varchar/decimal type declaration 994 $validation = DB_Table_Manager::_validateTypeDeclaration($coltype, $size, 995 $scope); 996 if (PEAR::isError($validation)) { 997 return $validation; 998 } 999 1000 // map of MDB2 column types 1001 $map = $GLOBALS['_DB_TABLE']['mdb2_type']; 1002 1003 // is it a recognized column type? 1004 $types = array_keys($map); 1005 if (! in_array($coltype, $types)) { 1006 return DB_Table::throwError( 1007 DB_TABLE_ERR_DECLARE_TYPE, 1008 "('$coltype')" 1009 ); 1010 } 1011 1012 // build declaration array 1013 $new_column = array( 1014 'type' => $map[$coltype], 1015 'notnull' => $require 1016 ); 1017 1018 if ($size) { 1019 $new_column['length'] = $size; 1020 } 1021 1022 // determine integer length to be used in MDB2 1023 if (in_array($coltype, array('smallint', 'integer', 'bigint'))) { 1024 switch ($coltype) { 1025 case 'smallint': 1026 $new_column['length'] = 2; 1027 break; 1028 case 'integer': 1029 $new_column['length'] = 4; 1030 break; 1031 case 'bigint': 1032 $new_column['length'] = 5; 1033 break; 1034 } 1035 } 1036 1037 if ($scope) { 1038 $max_scope = max($max_scope, $scope); 1039 } 1040 1041 if ($default) { 1042 $new_column['default'] = $default; 1043 } 1044 1045 return $new_column; 1046 } 1047 1048 1049 /** 1050 * 1051 * Get the index declaration string for a DB_Table index. 1052 * 1053 * @static 1054 * 1055 * @access public 1056 * 1057 * @param string $phptype The DB phptype key. 1058 * 1059 * @param string $type The index type. 1060 * 1061 * @param string $idxname The index name. 1062 * 1063 * @param string $table The table name. 1064 * 1065 * @param mixed $cols Array with the column names for the index. 1066 * 1067 * @return string A declaration string. 1068 * 1069 */ 1070 1071 function getDeclareForIndex($phptype, $type, $idxname, $table, $cols) 1072 { 1073 // string of column names 1074 $colstring = implode(', ', $cols); 1075 1076 switch ($type) { 1077 1078 case 'primary': 1079 switch ($phptype) { 1080 case 'ibase': 1081 case 'oci8': 1082 case 'pgsql': 1083 $declare = "ALTER TABLE $table ADD"; 1084 $declare .= " CONSTRAINT $idxname"; 1085 $declare .= " PRIMARY KEY ($colstring)"; 1086 break; 1087 case 'mysql': 1088 case 'mysqli': 1089 $declare = "ALTER TABLE $table ADD PRIMARY KEY"; 1090 $declare .= " ($colstring)"; 1091 break; 1092 case 'sqlite': 1093 // currently not possible 1094 break; 1095 } 1096 break; 1097 1098 case 'unique': 1099 $declare = "CREATE UNIQUE INDEX $idxname ON $table ($colstring)"; 1100 break; 1101 1102 case 'normal': 1103 $declare = "CREATE INDEX $idxname ON $table ($colstring)"; 1104 break; 1105 1106 } 1107 1108 return $declare; 1109 } 1110 1111 1112 /** 1113 * 1114 * Return the definition array for a column. 1115 * 1116 * @access private 1117 * 1118 * @param string $backend The name of the backend ('db' or 'mdb2'). 1119 * 1120 * @param string $phptype The DB/MDB2 phptype key. 1121 * 1122 * @param mixed $column A single DB_Table column definition array. 1123 * 1124 * @return mixed|object Declaration string (DB), declaration array (MDB2) or a 1125 * PEAR_Error with a description about the invalidity, otherwise. 1126 * 1127 */ 1128 1129 function _getColumnDefinition($backend, $phptype, $column) 1130 { 1131 static $max_scope; 1132 1133 // prepare variables 1134 $type = (isset($column['type'])) ? $column['type'] : null; 1135 $size = (isset($column['size'])) ? $column['size'] : null; 1136 $scope = (isset($column['scope'])) ? $column['scope'] : null; 1137 $require = (isset($column['require'])) ? $column['require'] : null; 1138 $default = (isset($column['default'])) ? $column['default'] : null; 1139 1140 if ($backend == 'db') { 1141 return DB_Table_Manager::getDeclare($phptype, $type, 1142 $size, $scope, $require, $default); 1143 } else { 1144 return DB_Table_Manager::getDeclareMDB2($type, 1145 $size, $scope, $require, $default, $max_scope); 1146 } 1147 } 1148 1149 1150 /** 1151 * 1152 * Check char/varchar/decimal type declarations for validity. 1153 * 1154 * @access private 1155 * 1156 * @param string $coltype The DB_Table column type. 1157 * 1158 * @param int $size The size for the column (needed for string and 1159 * decimal). 1160 * 1161 * @param int $scope The scope for the column (needed for decimal). 1162 * 1163 * @return bool|object Boolean true if the type declaration is valid or a 1164 * PEAR_Error with a description about the invalidity, otherwise. 1165 * 1166 */ 1167 1168 function _validateTypeDeclaration($coltype, $size, $scope) 1169 { 1170 // validate char and varchar: does it have a size? 1171 if (($coltype == 'char' || $coltype == 'varchar') && 1172 ($size < 1 || $size > 255) ) { 1173 return DB_Table::throwError( 1174 DB_TABLE_ERR_DECLARE_STRING, 1175 "(size='$size')" 1176 ); 1177 } 1178 1179 // validate decimal: does it have a size and scope? 1180 if ($coltype == 'decimal' && 1181 ($size < 1 || $size > 255 || $scope < 0 || $scope > $size)) { 1182 return DB_Table::throwError( 1183 DB_TABLE_ERR_DECLARE_DECIMAL, 1184 "(size='$size' scope='$scope')" 1185 ); 1186 } 1187 1188 return true; 1189 } 1190 1191 1192 /** 1193 * 1194 * Check a table name for validity. 1195 * 1196 * @access private 1197 * 1198 * @param string $tablename The table name. 1199 * 1200 * @return bool|object Boolean true if the table name is valid or a 1201 * PEAR_Error with a description about the invalidity, otherwise. 1202 * 1203 */ 1204 1205 function _validateTableName($tablename) 1206 { 1207 // is the table name too long? 1208 if ( $GLOBALS['_DB_TABLE']['disable_length_check'] === false 1209 && strlen($tablename) > 30 1210 ) { 1211 return DB_Table::throwError( 1212 DB_TABLE_ERR_TABLE_STRLEN, 1213 " ('$tablename')" 1214 ); 1215 } 1216 1217 return true; 1218 } 1219 1220 1221 /** 1222 * 1223 * Check a column name for validity. 1224 * 1225 * @access private 1226 * 1227 * @param string $colname The column name. 1228 * 1229 * @return bool|object Boolean true if the column name is valid or a 1230 * PEAR_Error with a description about the invalidity, otherwise. 1231 * 1232 */ 1233 1234 function _validateColumnName($colname) 1235 { 1236 // column name cannot be a reserved keyword 1237 $reserved = in_array( 1238 strtoupper($colname), 1239 $GLOBALS['_DB_TABLE']['reserved'] 1240 ); 1241 1242 if ($reserved) { 1243 return DB_Table::throwError( 1244 DB_TABLE_ERR_DECLARE_COLNAME, 1245 " ('$colname')" 1246 ); 1247 } 1248 1249 // column name must be no longer than 30 chars 1250 if ( $GLOBALS['_DB_TABLE']['disable_length_check'] === false 1251 && strlen($colname) > 30 1252 ) { 1253 return DB_Table::throwError( 1254 DB_TABLE_ERR_DECLARE_STRLEN, 1255 "('$colname')" 1256 ); 1257 } 1258 1259 return true; 1260 } 1261 1262 1263 /** 1264 * 1265 * Check whether a column exists. 1266 * 1267 * @access private 1268 * 1269 * @param string $colname The column name. 1270 * 1271 * @param mixed $tableInfoOrder Array with columns in the table (result 1272 * from tableInfo(), shortened to key 'order'). 1273 * 1274 * @param string $mode The name of the calling function, this can be either 1275 * 'verify' or 'alter'. 1276 * 1277 * @return bool|object Boolean true if the column exists. 1278 * Otherwise, either boolean false (case 'alter') or a PEAR_Error 1279 * (case 'verify'). 1280 * 1281 */ 1282 1283 function _columnExists($colname, $tableInfoOrder, $mode) 1284 { 1285 if (array_key_exists($colname, $tableInfoOrder)) { 1286 return true; 1287 } 1288 1289 switch ($mode) { 1290 1291 case 'alter': 1292 return false; 1293 1294 case 'verify': 1295 return DB_Table::throwError( 1296 DB_TABLE_ERR_VER_COLUMN_MISSING, 1297 "(column='$colname')" 1298 ); 1299 1300 } 1301 } 1302 1303 1304 /** 1305 * 1306 * Check whether a column type is a known type. 1307 * 1308 * @access private 1309 * 1310 * @param string $phptype The DB/MDB2 phptype key. 1311 * 1312 * @param string $type The column type. 1313 * 1314 * @return bool|object Boolean true if the column type is a known type 1315 * or a PEAR_Error, otherwise. 1316 * 1317 */ 1318 1319 function _validateColumnType($phptype, $type) 1320 { 1321 // map of valid types for the current RDBMS 1322 $map = $GLOBALS['_DB_TABLE']['valid_type'][$phptype]; 1323 1324 // is it a recognized column type? 1325 $types = array_keys($map); 1326 if (!in_array($type, $types)) { 1327 return DB_Table::throwError( 1328 DB_TABLE_ERR_DECLARE_TYPE, 1329 "('" . $type . "')" 1330 ); 1331 } 1332 1333 return true; 1334 } 1335 1336 1337 /** 1338 * 1339 * Check whether a column has the right type. 1340 * 1341 * @access private 1342 * 1343 * @param string $phptype The DB/MDB2 phptype key. 1344 * 1345 * @param string $colname The column name. 1346 * 1347 * @param string $coltype The column type. 1348 * 1349 * @param mixed $tableInfoOrder Array with columns in the table (result 1350 * from tableInfo(), shortened to key 'order'). 1351 * 1352 * @param mixed $tableInfo Array with information about the table (result 1353 * from tableInfo()). 1354 * 1355 * @param string $mode The name of the calling function, this can be either 1356 * 'verify' or 'alter'. 1357 * 1358 * @return bool|object Boolean true if the column has the right type. 1359 * Otherwise, either boolean false (case 'alter') or a PEAR_Error 1360 * (case 'verify'). 1361 * 1362 */ 1363 1364 function _checkColumnType($phptype, $colname, $coltype, $tableInfoOrder, 1365 $tableInfo, $mode) 1366 { 1367 // map of valid types for the current RDBMS 1368 $map = $GLOBALS['_DB_TABLE']['valid_type'][$phptype]; 1369 1370 // get the column type from tableInfo() 1371 $colindex = $tableInfoOrder[$colname]; 1372 $type = strtolower($tableInfo[$colindex]['type']); 1373 1374 // workaround for possibly wrong detected column type (taken from MDB2) 1375 if ($type == 'unknown' && ($phptype == 'mysql' || $phptype == 'mysqli')) { 1376 $type = 'decimal'; 1377 } 1378 1379 // strip size information (e.g. NUMERIC(9,2) => NUMERIC) if given 1380 if (($pos = strpos($type, '(')) !== false) { 1381 $type = substr($type, 0, $pos); 1382 } 1383 1384 // is the type valid for the given DB_Table column type? 1385 if (in_array($type, (array)$map[$coltype])) { 1386 return true; 1387 } 1388 1389 switch ($mode) { 1390 1391 case 'alter': 1392 return false; 1393 1394 case 'verify': 1395 return DB_Table::throwError( 1396 DB_TABLE_ERR_VER_COLUMN_TYPE, 1397 "(column='$colname', type='$type')" 1398 ); 1399 1400 } 1401 } 1402 1403 1404 /** 1405 * 1406 * Return the index type and the columns belonging to this index. 1407 * 1408 * @access private 1409 * 1410 * @param mixed $idx_def The index definition. 1411 * 1412 * @return mixed Array with the index type and the columns belonging to 1413 * this index. 1414 * 1415 */ 1416 1417 function _getIndexTypeAndColumns($idx_def, $idxname) 1418 { 1419 $type = ''; 1420 $cols = ''; 1421 if (is_string($idx_def)) { 1422 // shorthand for index names: colname => index_type 1423 $type = trim($idx_def); 1424 $cols = trim($idxname); 1425 } elseif (is_array($idx_def)) { 1426 // normal: index_name => array('type' => ..., 'cols' => ...) 1427 $type = (isset($idx_def['type'])) ? $idx_def['type'] : 'normal'; 1428 $cols = (isset($idx_def['cols'])) ? $idx_def['cols'] : null; 1429 } 1430 1431 return array($type, $cols); 1432 } 1433 1434 1435 /** 1436 * 1437 * Check an index name for validity. 1438 * 1439 * @access private 1440 * 1441 * @param string $idxname The index name. 1442 * 1443 * @param string $table The table name. 1444 * 1445 * @param string $phptype The DB/MDB2 phptype key. 1446 * 1447 * @param string $type The index type. 1448 * 1449 * @param mixed $cols The column names for the index. Will become an array 1450 * if it is not an array. 1451 * 1452 * @param mixed $column_set A DB_Table $this->col array. 1453 * 1454 * @param string $newIdxName The new index name (prefixed with the table 1455 * name, suffixed with '_idx'). 1456 * 1457 * @return bool|object Boolean true if the index name is valid or a 1458 * PEAR_Error with a description about the invalidity, otherwise. 1459 * 1460 */ 1461 1462 function _validateIndexName($idxname, $table, $phptype, $type, &$cols, 1463 $column_set, &$newIdxName) 1464 { 1465 // index name cannot be a reserved keyword 1466 $reserved = in_array( 1467 strtoupper($idxname), 1468 $GLOBALS['_DB_TABLE']['reserved'] 1469 ); 1470 1471 if ($reserved && !($type == 'primary' && $idxname == 'PRIMARY')) { 1472 return DB_Table::throwError( 1473 DB_TABLE_ERR_DECLARE_IDXNAME, 1474 "('$idxname')" 1475 ); 1476 } 1477 1478 // are there any columns for the index? 1479 if (! $cols) { 1480 return DB_Table::throwError( 1481 DB_TABLE_ERR_IDX_NO_COLS, 1482 "('$idxname')" 1483 ); 1484 } 1485 1486 // are there any CLOB columns, or any columns that are not 1487 // in the schema? 1488 settype($cols, 'array'); 1489 $valid_cols = array_keys($column_set); 1490 foreach ($cols as $colname) { 1491 1492 if (! in_array($colname, $valid_cols)) { 1493 return DB_Table::throwError( 1494 DB_TABLE_ERR_IDX_COL_UNDEF, 1495 "'$idxname' ('$colname')" 1496 ); 1497 } 1498 1499 if ($column_set[$colname]['type'] == 'clob') { 1500 return DB_Table::throwError( 1501 DB_TABLE_ERR_IDX_COL_CLOB, 1502 "'$idxname' ('$colname')" 1503 ); 1504 } 1505 1506 } 1507 1508 // we prefix all index names with the table name, 1509 // and suffix all index names with '_idx'. this 1510 // is to soothe PostgreSQL, which demands that index 1511 // names not collide, even when they indexes are on 1512 // different tables. 1513 $newIdxName = $table . '_' . $idxname . '_idx'; 1514 1515 // MySQL requires the primary key to be named 'primary', therefore let's 1516 // ignore the user defined name 1517 if (($phptype == 'mysql' || $phptype == 'mysqli') && $type == 'primary') { 1518 $newIdxName = 'primary'; 1519 } 1520 1521 // now check the length; must be under 30 chars to 1522 // soothe Oracle. 1523 if ( $GLOBALS['_DB_TABLE']['disable_length_check'] === false 1524 && strlen($newIdxName) > 30 1525 ) { 1526 return DB_Table::throwError( 1527 DB_TABLE_ERR_IDX_STRLEN, 1528 "'$idxname' ('$newIdxName')" 1529 ); 1530 } 1531 1532 // check index type 1533 if ($type != 'primary' && $type != 'unique' && $type != 'normal') { 1534 return DB_Table::throwError( 1535 DB_TABLE_ERR_IDX_TYPE, 1536 "'$idxname' ('$type')" 1537 ); 1538 } 1539 1540 return true; 1541 } 1542 1543 1544 /** 1545 * 1546 * Return all indexes for a table. 1547 * 1548 * @access public 1549 * 1550 * @param object &$db A PEAR DB/MDB2 object. 1551 * 1552 * @param string $table The table name. 1553 * 1554 * @return mixed Array with all indexes or a PEAR_Error when an error 1555 * occured. 1556 * 1557 */ 1558 1559 function getIndexes(&$db, $table) 1560 { 1561 if (is_subclass_of($db, 'db_common')) { 1562 $backend = 'db'; 1563 // workaround for missing index and constraint information methods 1564 // in PEAR::DB ==> use adopted code from MDB2's driver classes 1565 require_once 'DB/Table/Manager/' . $db->phptype . '.php'; 1566 $classname = 'DB_Table_Manager_' . $db->phptype; 1567 $dbtm =& new $classname(); 1568 $dbtm->_db =& $db; // pass database instance to the 'workaround' class 1569 $manager =& $dbtm; 1570 $reverse =& $dbtm; 1571 } elseif (is_subclass_of($db, 'mdb2_driver_common')) { 1572 $backend = 'mdb2'; 1573 $manager =& $db->manager; 1574 $reverse =& $db->reverse; 1575 } 1576 1577 $indexes = array('normal' => array(), 1578 'primary' => array(), 1579 'unique' => array() 1580 ); 1581 1582 // save user defined 'idxname_format' option (MDB2 only) 1583 if ($backend == 'mdb2') { 1584 $idxname_format = $db->getOption('idxname_format'); 1585 $db->setOption('idxname_format', '%s'); 1586 } 1587 1588 // get table constraints 1589 $table_indexes_tmp = $manager->listTableConstraints($table); 1590 if (PEAR::isError($table_indexes_tmp)) { 1591 // restore user defined 'idxname_format' option (MDB2 only) 1592 if ($backend == 'mdb2') { 1593 $db->setOption('idxname_format', $idxname_format); 1594 } 1595 return $table_indexes_tmp; 1596 } 1597 1598 // get fields of table constraints 1599 foreach ($table_indexes_tmp as $table_idx_tmp) { 1600 $index_fields = $reverse->getTableConstraintDefinition($table, 1601 $table_idx_tmp); 1602 if (PEAR::isError($index_fields)) { 1603 // restore user defined 'idxname_format' option (MDB2 only) 1604 if ($backend == 'mdb2') { 1605 $db->setOption('idxname_format', $idxname_format); 1606 } 1607 return $index_fields; 1608 } 1609 // get the first key of $index_fields that has boolean true value 1610 foreach ($index_fields as $index_type => $value) { 1611 if ($value === true) { 1612 break; 1613 } 1614 } 1615 $indexes[$index_type][$table_idx_tmp] = array_keys($index_fields['fields']); 1616 } 1617 1618 // get table indexes 1619 $table_indexes_tmp = $manager->listTableIndexes($table); 1620 if (PEAR::isError($table_indexes_tmp)) { 1621 // restore user defined 'idxname_format' option (MDB2 only) 1622 if ($backend == 'mdb2') { 1623 $db->setOption('idxname_format', $idxname_format); 1624 } 1625 return $table_indexes_tmp; 1626 } 1627 1628 // get fields of table indexes 1629 foreach ($table_indexes_tmp as $table_idx_tmp) { 1630 $index_fields = $reverse->getTableIndexDefinition($table, 1631 $table_idx_tmp); 1632 if (PEAR::isError($index_fields)) { 1633 // restore user defined 'idxname_format' option (MDB2 only) 1634 if ($backend == 'mdb2') { 1635 $db->setOption('idxname_format', $idxname_format); 1636 } 1637 return $index_fields; 1638 } 1639 $indexes['normal'][$table_idx_tmp] = array_keys($index_fields['fields']); 1640 } 1641 1642 // restore user defined 'idxname_format' option (MDB2 only) 1643 if ($backend == 'mdb2') { 1644 $db->setOption('idxname_format', $idxname_format); 1645 } 1646 1647 return $indexes; 1648 } 1649 1650 1651 /** 1652 * 1653 * Check whether an index has the right type and has all specified columns. 1654 * 1655 * @access private 1656 * 1657 * @param string $idxname The index name. 1658 * 1659 * @param string $newIdxName The prefixed and suffixed index name. 1660 * 1661 * @param string $type The index type. 1662 * 1663 * @param mixed $cols The column names for the index. 1664 * 1665 * @param mixed $table_indexes Array with all indexes of the table. 1666 * 1667 * @param string $mode The name of the calling function, this can be either 1668 * 'verify' or 'alter'. 1669 * 1670 * @return bool|object Boolean true if the index has the right type and all 1671 * specified columns. Otherwise, either boolean false (case 'alter') or a 1672 * PEAR_Error (case 'verify'). 1673 * 1674 */ 1675 1676 function _checkIndex($idxname, $newIdxName, $type, $cols, &$table_indexes, $mode) 1677 { 1678 $index_found = false; 1679 1680 foreach ($table_indexes[$type] as $index_name => $index_fields) { 1681 if (strtolower($index_name) == strtolower($newIdxName)) { 1682 $index_found = true; 1683 array_walk($cols, create_function('&$value,$key', 1684 '$value = trim(strtolower($value));')); 1685 array_walk($index_fields, create_function('&$value,$key', 1686 '$value = trim(strtolower($value));')); 1687 foreach ($index_fields as $index_field) { 1688 if (($key = array_search($index_field, $cols)) !== false) { 1689 unset($cols[$key]); 1690 } 1691 } 1692 break; 1693 } 1694 } 1695 1696 if (!$index_found) { 1697 return ($mode == 'alter') ? false : DB_Table::throwError( 1698 DB_TABLE_ERR_VER_IDX_MISSING, 1699 "'$idxname' ('$newIdxName')" 1700 ); 1701 } 1702 1703 if (count($cols) > 0) { 1704 // string of column names 1705 $colstring = implode(', ', $cols); 1706 return ($mode == 'alter') ? false : DB_Table::throwError( 1707 DB_TABLE_ERR_VER_IDX_COL_MISSING, 1708 "'$idxname' ($colstring)" 1709 ); 1710 } 1711 1712 return true; 1713 } 1714 1715 1716 /** 1717 * 1718 * Create indexes and contraints. 1719 * 1720 * @access private 1721 * 1722 * @param object &$db A PEAR DB/MDB2 object. 1723 * 1724 * @param string $backend The name of the backend ('db' or 'mdb2'). 1725 * 1726 * @param string $table The table name. 1727 * 1728 * @param mixed $indexes An array with index and constraint definitions. 1729 * 1730 * @return bool|object Boolean true on success or a PEAR_Error with a 1731 * description about the invalidity, otherwise. 1732 * 1733 */ 1734 1735 function _createIndexesAndContraints($db, $backend, $table, $indexes) 1736 { 1737 if ($backend == 'mdb2') { 1738 1739 // save user defined 'idxname_format' option 1740 $idxname_format = $db->getOption('idxname_format'); 1741 $db->setOption('idxname_format', '%s'); 1742 1743 // attempt to create the primary key 1744 if (!array_key_exists('primary', $indexes)) { 1745 $indexes['primary'] = array(); 1746 } 1747 foreach ($indexes['primary'] as $name => $definition) { 1748 $result = $db->manager->createConstraint($table, $name, $definition); 1749 if (PEAR::isError($result)) { 1750 // restore user defined 'idxname_format' option 1751 $db->setOption('idxname_format', $idxname_format); 1752 return $result; 1753 } 1754 } 1755 1756 // attempt to create the unique indexes / constraints 1757 if (!array_key_exists('unique', $indexes)) { 1758 $indexes['unique'] = array(); 1759 } 1760 foreach ($indexes['unique'] as $name => $definition) { 1761 $result = $db->manager->createConstraint($table, $name, $definition); 1762 if (PEAR::isError($result)) { 1763 // restore user defined 'idxname_format' option 1764 $db->setOption('idxname_format', $idxname_format); 1765 return $result; 1766 } 1767 } 1768 1769 // attempt to create the normal indexes 1770 if (!array_key_exists('normal', $indexes)) { 1771 $indexes['normal'] = array(); 1772 } 1773 foreach ($indexes['normal'] as $name => $definition) { 1774 $result = $db->manager->createIndex($table, $name, $definition); 1775 if (PEAR::isError($result)) { 1776 // restore user defined 'idxname_format' option 1777 $db->setOption('idxname_format', $idxname_format); 1778 return $result; 1779 } 1780 } 1781 1782 // restore user defined 'idxname_format' option 1783 $db->setOption('idxname_format', $idxname_format); 1784 1785 } else { 1786 1787 // attempt to create the indexes 1788 foreach ($indexes as $cmd) { 1789 $result = $db->query($cmd); 1790 if (PEAR::isError($result)) { 1791 return $result; 1792 } 1793 } 1794 1795 } 1796 1797 return true; 1798 1799 } 1800 1801} 1802 1803 1804/** 1805* List of all reserved words for all supported databases. Yes, this is a 1806* monster of a list. 1807*/ 1808if (! isset($GLOBALS['_DB_TABLE']['reserved'])) { 1809 $GLOBALS['_DB_TABLE']['reserved'] = array( 1810 '_ROWID_', 1811 'ABSOLUTE', 1812 'ACCESS', 1813 'ACTION', 1814 'ADD', 1815 'ADMIN', 1816 'AFTER', 1817 'AGGREGATE', 1818 'ALIAS', 1819 'ALL', 1820 'ALLOCATE', 1821 'ALTER', 1822 'ANALYSE', 1823 'ANALYZE', 1824 'AND', 1825 'ANY', 1826 'ARE', 1827 'ARRAY', 1828 'AS', 1829 'ASC', 1830 'ASENSITIVE', 1831 'ASSERTION', 1832 'AT', 1833 'AUDIT', 1834 'AUTHORIZATION', 1835 'AUTO_INCREMENT', 1836 'AVG', 1837 'BACKUP', 1838 'BDB', 1839 'BEFORE', 1840 'BEGIN', 1841 'BERKELEYDB', 1842 'BETWEEN', 1843 'BIGINT', 1844 'BINARY', 1845 'BIT', 1846 'BIT_LENGTH', 1847 'BLOB', 1848 'BOOLEAN', 1849 'BOTH', 1850 'BREADTH', 1851 'BREAK', 1852 'BROWSE', 1853 'BULK', 1854 'BY', 1855 'CALL', 1856 'CASCADE', 1857 'CASCADED', 1858 'CASE', 1859 'CAST', 1860 'CATALOG', 1861 'CHANGE', 1862 'CHAR', 1863 'CHAR_LENGTH', 1864 'CHARACTER', 1865 'CHARACTER_LENGTH', 1866 'CHECK', 1867 'CHECKPOINT', 1868 'CLASS', 1869 'CLOB', 1870 'CLOSE', 1871 'CLUSTER', 1872 'CLUSTERED', 1873 'COALESCE', 1874 'COLLATE', 1875 'COLLATION', 1876 'COLUMN', 1877 'COLUMNS', 1878 'COMMENT', 1879 'COMMIT', 1880 'COMPLETION', 1881 'COMPRESS', 1882 'COMPUTE', 1883 'CONDITION', 1884 'CONNECT', 1885 'CONNECTION', 1886 'CONSTRAINT', 1887 'CONSTRAINTS', 1888 'CONSTRUCTOR', 1889 'CONTAINS', 1890 'CONTAINSTABLE', 1891 'CONTINUE', 1892 'CONVERT', 1893 'CORRESPONDING', 1894 'COUNT', 1895 'CREATE', 1896 'CROSS', 1897 'CUBE', 1898 'CURRENT', 1899 'CURRENT_DATE', 1900 'CURRENT_PATH', 1901 'CURRENT_ROLE', 1902 'CURRENT_TIME', 1903 'CURRENT_TIMESTAMP', 1904 'CURRENT_USER', 1905 'CURSOR', 1906 'CYCLE', 1907 'DATA', 1908 'DATABASE', 1909 'DATABASES', 1910 'DATE', 1911 'DAY', 1912 'DAY_HOUR', 1913 'DAY_MICROSECOND', 1914 'DAY_MINUTE', 1915 'DAY_SECOND', 1916 'DBCC', 1917 'DEALLOCATE', 1918 'DEC', 1919 'DECIMAL', 1920 'DECLARE', 1921 'DEFAULT', 1922 'DEFERRABLE', 1923 'DEFERRED', 1924 'DELAYED', 1925 'DELETE', 1926 'DENY', 1927 'DEPTH', 1928 'DEREF', 1929 'DESC', 1930 'DESCRIBE', 1931 'DESCRIPTOR', 1932 'DESTROY', 1933 'DESTRUCTOR', 1934 'DETERMINISTIC', 1935 'DIAGNOSTICS', 1936 'DICTIONARY', 1937 'DISCONNECT', 1938 'DISK', 1939 'DISTINCT', 1940 'DISTINCTROW', 1941 'DISTRIBUTED', 1942 'DIV', 1943 'DO', 1944 'DOMAIN', 1945 'DOUBLE', 1946 'DROP', 1947 'DUMMY', 1948 'DUMP', 1949 'DYNAMIC', 1950 'EACH', 1951 'ELSE', 1952 'ELSEIF', 1953 'ENCLOSED', 1954 'END', 1955 'END-EXEC', 1956 'EQUALS', 1957 'ERRLVL', 1958 'ESCAPE', 1959 'ESCAPED', 1960 'EVERY', 1961 'EXCEPT', 1962 'EXCEPTION', 1963 'EXCLUSIVE', 1964 'EXEC', 1965 'EXECUTE', 1966 'EXISTS', 1967 'EXIT', 1968 'EXPLAIN', 1969 'EXTERNAL', 1970 'EXTRACT', 1971 'FALSE', 1972 'FETCH', 1973 'FIELDS', 1974 'FILE', 1975 'FILLFACTOR', 1976 'FIRST', 1977 'FLOAT', 1978 'FOR', 1979 'FORCE', 1980 'FOREIGN', 1981 'FOUND', 1982 'FRAC_SECOND', 1983 'FREE', 1984 'FREETEXT', 1985 'FREETEXTTABLE', 1986 'FREEZE', 1987 'FROM', 1988 'FULL', 1989 'FULLTEXT', 1990 'FUNCTION', 1991 'GENERAL', 1992 'GET', 1993 'GLOB', 1994 'GLOBAL', 1995 'GO', 1996 'GOTO', 1997 'GRANT', 1998 'GROUP', 1999 'GROUPING', 2000 'HAVING', 2001 'HIGH_PRIORITY', 2002 'HOLDLOCK', 2003 'HOST', 2004 'HOUR', 2005 'HOUR_MICROSECOND', 2006 'HOUR_MINUTE', 2007 'HOUR_SECOND', 2008 'IDENTIFIED', 2009 'IDENTITY', 2010 'IDENTITY_INSERT', 2011 'IDENTITYCOL', 2012 'IF', 2013 'IGNORE', 2014 'ILIKE', 2015 'IMMEDIATE', 2016 'IN', 2017 'INCREMENT', 2018 'INDEX', 2019 'INDICATOR', 2020 'INFILE', 2021 'INITIAL', 2022 'INITIALIZE', 2023 'INITIALLY', 2024 'INNER', 2025 'INNODB', 2026 'INOUT', 2027 'INPUT', 2028 'INSENSITIVE', 2029 'INSERT', 2030 'INT', 2031 'INTEGER', 2032 'INTERSECT', 2033 'INTERVAL', 2034 'INTO', 2035 'IO_THREAD', 2036 'IS', 2037 'ISNULL', 2038 'ISOLATION', 2039 'ITERATE', 2040 'JOIN', 2041 'KEY', 2042 'KEYS', 2043 'KILL', 2044 'LANGUAGE', 2045 'LARGE', 2046 'LAST', 2047 'LATERAL', 2048 'LEADING', 2049 'LEAVE', 2050 'LEFT', 2051 'LESS', 2052 'LEVEL', 2053 'LIKE', 2054 'LIMIT', 2055 'LINENO', 2056 'LINES', 2057 'LOAD', 2058 'LOCAL', 2059 'LOCALTIME', 2060 'LOCALTIMESTAMP', 2061 'LOCATOR', 2062 'LOCK', 2063 'LONG', 2064 'LONGBLOB', 2065 'LONGTEXT', 2066 'LOOP', 2067 'LOW_PRIORITY', 2068 'LOWER', 2069 'MAIN', 2070 'MAP', 2071 'MASTER_SERVER_ID', 2072 'MATCH', 2073 'MAX', 2074 'MAXEXTENTS', 2075 'MEDIUMBLOB', 2076 'MEDIUMINT', 2077 'MEDIUMTEXT', 2078 'MIDDLEINT', 2079 'MIN', 2080 'MINUS', 2081 'MINUTE', 2082 'MINUTE_MICROSECOND', 2083 'MINUTE_SECOND', 2084 'MLSLABEL', 2085 'MOD', 2086 'MODE', 2087 'MODIFIES', 2088 'MODIFY', 2089 'MODULE', 2090 'MONTH', 2091 'NAMES', 2092 'NATIONAL', 2093 'NATURAL', 2094 'NCHAR', 2095 'NCLOB', 2096 'NEW', 2097 'NEXT', 2098 'NO', 2099 'NO_WRITE_TO_BINLOG', 2100 'NOAUDIT', 2101 'NOCHECK', 2102 'NOCOMPRESS', 2103 'NONCLUSTERED', 2104 'NONE', 2105 'NOT', 2106 'NOTNULL', 2107 'NOWAIT', 2108 'NULL', 2109 'NULLIF', 2110 'NUMBER', 2111 'NUMERIC', 2112 'OBJECT', 2113 'OCTET_LENGTH', 2114 'OF', 2115 'OFF', 2116 'OFFLINE', 2117 'OFFSET', 2118 'OFFSETS', 2119 'OID', 2120 'OLD', 2121 'ON', 2122 'ONLINE', 2123 'ONLY', 2124 'OPEN', 2125 'OPENDATASOURCE', 2126 'OPENQUERY', 2127 'OPENROWSET', 2128 'OPENXML', 2129 'OPERATION', 2130 'OPTIMIZE', 2131 'OPTION', 2132 'OPTIONALLY', 2133 'OR', 2134 'ORDER', 2135 'ORDINALITY', 2136 'OUT', 2137 'OUTER', 2138 'OUTFILE', 2139 'OUTPUT', 2140 'OVER', 2141 'OVERLAPS', 2142 'PAD', 2143 'PARAMETER', 2144 'PARAMETERS', 2145 'PARTIAL', 2146 'PATH', 2147 'PCTFREE', 2148 'PERCENT', 2149 'PLACING', 2150 'PLAN', 2151 'POSITION', 2152 'POSTFIX', 2153 'PRECISION', 2154 'PREFIX', 2155 'PREORDER', 2156 'PREPARE', 2157 'PRESERVE', 2158 'PRIMARY', 2159 'PRINT', 2160 'PRIOR', 2161 'PRIVILEGES', 2162 'PROC', 2163 'PROCEDURE', 2164 'PUBLIC', 2165 'PURGE', 2166 'RAISERROR', 2167 'RAW', 2168 'READ', 2169 'READS', 2170 'READTEXT', 2171 'REAL', 2172 'RECONFIGURE', 2173 'RECURSIVE', 2174 'REF', 2175 'REFERENCES', 2176 'REFERENCING', 2177 'REGEXP', 2178 'RELATIVE', 2179 'RENAME', 2180 'REPEAT', 2181 'REPLACE', 2182 'REPLICATION', 2183 'REQUIRE', 2184 'RESOURCE', 2185 'RESTORE', 2186 'RESTRICT', 2187 'RESULT', 2188 'RETURN', 2189 'RETURNS', 2190 'REVOKE', 2191 'RIGHT', 2192 'RLIKE', 2193 'ROLE', 2194 'ROLLBACK', 2195 'ROLLUP', 2196 'ROUTINE', 2197 'ROW', 2198 'ROWCOUNT', 2199 'ROWGUIDCOL', 2200 'ROWID', 2201 'ROWNUM', 2202 'ROWS', 2203 'RULE', 2204 'SAVE', 2205 'SAVEPOINT', 2206 'SCHEMA', 2207 'SCOPE', 2208 'SCROLL', 2209 'SEARCH', 2210 'SECOND', 2211 'SECOND_MICROSECOND', 2212 'SECTION', 2213 'SELECT', 2214 'SENSITIVE', 2215 'SEPARATOR', 2216 'SEQUENCE', 2217 'SESSION', 2218 'SESSION_USER', 2219 'SET', 2220 'SETS', 2221 'SETUSER', 2222 'SHARE', 2223 'SHOW', 2224 'SHUTDOWN', 2225 'SIMILAR', 2226 'SIZE', 2227 'SMALLINT', 2228 'SOME', 2229 'SONAME', 2230 'SPACE', 2231 'SPATIAL', 2232 'SPECIFIC', 2233 'SPECIFICTYPE', 2234 'SQL', 2235 'SQL_BIG_RESULT', 2236 'SQL_CALC_FOUND_ROWS', 2237 'SQL_SMALL_RESULT', 2238 'SQL_TSI_DAY', 2239 'SQL_TSI_FRAC_SECOND', 2240 'SQL_TSI_HOUR', 2241 'SQL_TSI_MINUTE', 2242 'SQL_TSI_MONTH', 2243 'SQL_TSI_QUARTER', 2244 'SQL_TSI_SECOND', 2245 'SQL_TSI_WEEK', 2246 'SQL_TSI_YEAR', 2247 'SQLCODE', 2248 'SQLERROR', 2249 'SQLEXCEPTION', 2250 'SQLITE_MASTER', 2251 'SQLITE_TEMP_MASTER', 2252 'SQLSTATE', 2253 'SQLWARNING', 2254 'SSL', 2255 'START', 2256 'STARTING', 2257 'STATE', 2258 'STATEMENT', 2259 'STATIC', 2260 'STATISTICS', 2261 'STRAIGHT_JOIN', 2262 'STRIPED', 2263 'STRUCTURE', 2264 'SUBSTRING', 2265 'SUCCESSFUL', 2266 'SUM', 2267 'SYNONYM', 2268 'SYSDATE', 2269 'SYSTEM_USER', 2270 'TABLE', 2271 'TABLES', 2272 'TEMPORARY', 2273 'TERMINATE', 2274 'TERMINATED', 2275 'TEXTSIZE', 2276 'THAN', 2277 'THEN', 2278 'TIME', 2279 'TIMESTAMP', 2280 'TIMESTAMPADD', 2281 'TIMESTAMPDIFF', 2282 'TIMEZONE_HOUR', 2283 'TIMEZONE_MINUTE', 2284 'TINYBLOB', 2285 'TINYINT', 2286 'TINYTEXT', 2287 'TO', 2288 'TOP', 2289 'TRAILING', 2290 'TRAN', 2291 'TRANSACTION', 2292 'TRANSLATE', 2293 'TRANSLATION', 2294 'TREAT', 2295 'TRIGGER', 2296 'TRIM', 2297 'TRUE', 2298 'TRUNCATE', 2299 'TSEQUAL', 2300 'UID', 2301 'UNDER', 2302 'UNDO', 2303 'UNION', 2304 'UNIQUE', 2305 'UNKNOWN', 2306 'UNLOCK', 2307 'UNNEST', 2308 'UNSIGNED', 2309 'UPDATE', 2310 'UPDATETEXT', 2311 'UPPER', 2312 'USAGE', 2313 'USE', 2314 'USER', 2315 'USER_RESOURCES', 2316 'USING', 2317 'UTC_DATE', 2318 'UTC_TIME', 2319 'UTC_TIMESTAMP', 2320 'VALIDATE', 2321 'VALUE', 2322 'VALUES', 2323 'VARBINARY', 2324 'VARCHAR', 2325 'VARCHAR2', 2326 'VARCHARACTER', 2327 'VARIABLE', 2328 'VARYING', 2329 'VERBOSE', 2330 'VIEW', 2331 'WAITFOR', 2332 'WHEN', 2333 'WHENEVER', 2334 'WHERE', 2335 'WHILE', 2336 'WITH', 2337 'WITHOUT', 2338 'WORK', 2339 'WRITE', 2340 'WRITETEXT', 2341 'XOR', 2342 'YEAR', 2343 'YEAR_MONTH', 2344 'ZEROFILL', 2345 'ZONE', 2346 ); 2347} 2348 2349?> 2350