1<?php 2/* 3 * $Id: Export.php 7653 2010-06-08 15:54:31Z jwage $ 4 * 5 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS 6 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT 7 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR 8 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT 9 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, 10 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT 11 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 12 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 13 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT 14 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 15 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 16 * 17 * This software consists of voluntary contributions made by many individuals 18 * and is licensed under the LGPL. For more information, see 19 * <http://www.doctrine-project.org>. 20 */ 21 22/** 23 * Doctrine_Export 24 * 25 * @package Doctrine 26 * @subpackage Export 27 * @author Konsta Vesterinen <kvesteri@cc.hut.fi> 28 * @author Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library) 29 * @license http://www.opensource.org/licenses/lgpl-license.php LGPL 30 * @link www.doctrine-project.org 31 * @since 1.0 32 * @version $Revision: 7653 $ 33 */ 34class Doctrine_Export extends Doctrine_Connection_Module 35{ 36 protected $valid_default_values = array( 37 'text' => '', 38 'boolean' => true, 39 'integer' => 0, 40 'decimal' => 0.0, 41 'float' => 0.0, 42 'timestamp' => '1970-01-01 00:00:00', 43 'time' => '00:00:00', 44 'date' => '1970-01-01', 45 'clob' => '', 46 'blob' => '', 47 'string' => '' 48 ); 49 50 /** 51 * drop an existing database 52 * (this method is implemented by the drivers) 53 * 54 * @param string $name name of the database that should be dropped 55 * @return void 56 */ 57 public function dropDatabase($database) 58 { 59 foreach ((array) $this->dropDatabaseSql($database) as $query) { 60 $this->conn->execute($query); 61 } 62 } 63 64 /** 65 * drop an existing database 66 * (this method is implemented by the drivers) 67 * 68 * @param string $name name of the database that should be dropped 69 * @return void 70 */ 71 public function dropDatabaseSql($database) 72 { 73 throw new Doctrine_Export_Exception('Drop database not supported by this driver.'); 74 } 75 76 /** 77 * dropTableSql 78 * drop an existing table 79 * 80 * @param string $table name of table that should be dropped from the database 81 * @return string 82 */ 83 public function dropTableSql($table) 84 { 85 return 'DROP TABLE ' . $this->conn->quoteIdentifier($table); 86 } 87 88 /** 89 * dropTable 90 * drop an existing table 91 * 92 * @param string $table name of table that should be dropped from the database 93 * @return void 94 */ 95 public function dropTable($table) 96 { 97 $this->conn->execute($this->dropTableSql($table)); 98 } 99 100 /** 101 * drop existing index 102 * 103 * @param string $table name of table that should be used in method 104 * @param string $name name of the index to be dropped 105 * @return void 106 */ 107 public function dropIndex($table, $name) 108 { 109 return $this->conn->exec($this->dropIndexSql($table, $name)); 110 } 111 112 /** 113 * dropIndexSql 114 * 115 * @param string $table name of table that should be used in method 116 * @param string $name name of the index to be dropped 117 * @return string SQL that is used for dropping an index 118 */ 119 public function dropIndexSql($table, $name) 120 { 121 $name = $this->conn->quoteIdentifier($this->conn->formatter->getIndexName($name)); 122 123 return 'DROP INDEX ' . $name; 124 } 125 126 /** 127 * drop existing constraint 128 * 129 * @param string $table name of table that should be used in method 130 * @param string $name name of the constraint to be dropped 131 * @param string $primary hint if the constraint is primary 132 * @return void 133 */ 134 public function dropConstraint($table, $name, $primary = false) 135 { 136 $table = $this->conn->quoteIdentifier($table); 137 $name = $this->conn->quoteIdentifier($name); 138 139 return $this->conn->exec('ALTER TABLE ' . $table . ' DROP CONSTRAINT ' . $name); 140 } 141 142 /** 143 * drop existing foreign key 144 * 145 * @param string $table name of table that should be used in method 146 * @param string $name name of the foreign key to be dropped 147 * @return void 148 */ 149 public function dropForeignKey($table, $name) 150 { 151 return $this->dropConstraint($table, $this->conn->formatter->getForeignKeyName($name)); 152 } 153 154 /** 155 * dropSequenceSql 156 * drop existing sequence 157 * (this method is implemented by the drivers) 158 * 159 * @throws Doctrine_Connection_Exception if something fails at database level 160 * @param string $sequenceName name of the sequence to be dropped 161 * @return void 162 */ 163 public function dropSequence($sequenceName) 164 { 165 $this->conn->exec($this->dropSequenceSql($sequenceName)); 166 } 167 168 /** 169 * dropSequenceSql 170 * drop existing sequence 171 * 172 * @throws Doctrine_Connection_Exception if something fails at database level 173 * @param string $sequenceName name of the sequence to be dropped 174 * @return void 175 */ 176 public function dropSequenceSql($sequenceName) 177 { 178 throw new Doctrine_Export_Exception('Drop sequence not supported by this driver.'); 179 } 180 181 /** 182 * create a new database 183 * (this method is implemented by the drivers) 184 * 185 * @param string $name name of the database that should be created 186 * @return void 187 */ 188 public function createDatabase($database) 189 { 190 $this->conn->execute($this->createDatabaseSql($database)); 191 } 192 193 /** 194 * create a new database 195 * (this method is implemented by the drivers) 196 * 197 * @param string $name name of the database that should be created 198 * @return string 199 */ 200 public function createDatabaseSql($database) 201 { 202 throw new Doctrine_Export_Exception('Create database not supported by this driver.'); 203 } 204 205 /** 206 * create a new table 207 * 208 * @param string $name Name of the database that should be created 209 * @param array $fields Associative array that contains the definition of each field of the new table 210 * The indexes of the array entries are the names of the fields of the table an 211 * the array entry values are associative arrays like those that are meant to be 212 * passed with the field definitions to get[Type]Declaration() functions. 213 * array( 214 * 'id' => array( 215 * 'type' => 'integer', 216 * 'unsigned' => 1 217 * 'notnull' => 1 218 * 'default' => 0 219 * ), 220 * 'name' => array( 221 * 'type' => 'text', 222 * 'length' => 12 223 * ), 224 * 'password' => array( 225 * 'type' => 'text', 226 * 'length' => 12 227 * ) 228 * ); 229 * @param array $options An associative array of table options: 230 * 231 * @return string 232 */ 233 public function createTableSql($name, array $fields, array $options = array()) 234 { 235 if ( ! $name) { 236 throw new Doctrine_Export_Exception('no valid table name specified'); 237 } 238 239 if (empty($fields)) { 240 throw new Doctrine_Export_Exception('no fields specified for table ' . $name); 241 } 242 243 $queryFields = $this->getFieldDeclarationList($fields); 244 245 246 if (isset($options['primary']) && ! empty($options['primary'])) { 247 $primaryKeys = array_map(array($this->conn, 'quoteIdentifier'), array_values($options['primary'])); 248 $queryFields .= ', PRIMARY KEY(' . implode(', ', $primaryKeys) . ')'; 249 } 250 251 if (isset($options['indexes']) && ! empty($options['indexes'])) { 252 foreach($options['indexes'] as $index => $definition) { 253 $indexDeclaration = $this->getIndexDeclaration($index, $definition); 254 // append only created index declarations 255 if ( ! is_null($indexDeclaration)) { 256 $queryFields .= ', '.$indexDeclaration; 257 } 258 } 259 } 260 261 $query = 'CREATE TABLE ' . $this->conn->quoteIdentifier($name, true) . ' (' . $queryFields; 262 263 $check = $this->getCheckDeclaration($fields); 264 265 if ( ! empty($check)) { 266 $query .= ', ' . $check; 267 } 268 269 $query .= ')'; 270 271 $sql[] = $query; 272 273 if (isset($options['foreignKeys'])) { 274 275 foreach ((array) $options['foreignKeys'] as $k => $definition) { 276 if (is_array($definition)) { 277 $sql[] = $this->createForeignKeySql($name, $definition); 278 } 279 } 280 } 281 return $sql; 282 } 283 284 /** 285 * create a new table 286 * 287 * @param string $name Name of the database that should be created 288 * @param array $fields Associative array that contains the definition of each field of the new table 289 * @param array $options An associative array of table options: 290 * @see Doctrine_Export::createTableSql() 291 * 292 * @return void 293 */ 294 public function createTable($name, array $fields, array $options = array()) 295 { 296 // Build array of the primary keys if any of the individual field definitions 297 // specify primary => true 298 $count = 0; 299 foreach ($fields as $fieldName => $field) { 300 if (isset($field['primary']) && $field['primary']) { 301 if ($count == 0) { 302 $options['primary'] = array(); 303 } 304 $count++; 305 $options['primary'][] = $fieldName; 306 } 307 } 308 309 $sql = (array) $this->createTableSql($name, $fields, $options); 310 311 foreach ($sql as $query) { 312 $this->conn->execute($query); 313 } 314 } 315 316 /** 317 * create sequence 318 * 319 * @throws Doctrine_Connection_Exception if something fails at database level 320 * @param string $seqName name of the sequence to be created 321 * @param string $start start value of the sequence; default is 1 322 * @param array $options An associative array of table options: 323 * array( 324 * 'comment' => 'Foo', 325 * 'charset' => 'utf8', 326 * 'collate' => 'utf8_unicode_ci', 327 * ); 328 * @return void 329 */ 330 public function createSequence($seqName, $start = 1, array $options = array()) 331 { 332 return $this->conn->execute($this->createSequenceSql($seqName, $start = 1, $options)); 333 } 334 335 /** 336 * return RDBMS specific create sequence statement 337 * (this method is implemented by the drivers) 338 * 339 * @throws Doctrine_Connection_Exception if something fails at database level 340 * @param string $seqName name of the sequence to be created 341 * @param string $start start value of the sequence; default is 1 342 * @param array $options An associative array of table options: 343 * array( 344 * 'comment' => 'Foo', 345 * 'charset' => 'utf8', 346 * 'collate' => 'utf8_unicode_ci', 347 * ); 348 * @return string 349 */ 350 public function createSequenceSql($seqName, $start = 1, array $options = array()) 351 { 352 throw new Doctrine_Export_Exception('Create sequence not supported by this driver.'); 353 } 354 355 /** 356 * create a constraint on a table 357 * 358 * @param string $table name of the table on which the constraint is to be created 359 * @param string $name name of the constraint to be created 360 * @param array $definition associative array that defines properties of the constraint to be created. 361 * Currently, only one property named FIELDS is supported. This property 362 * is also an associative with the names of the constraint fields as array 363 * constraints. Each entry of this array is set to another type of associative 364 * array that specifies properties of the constraint that are specific to 365 * each field. 366 * 367 * Example 368 * array( 369 * 'fields' => array( 370 * 'user_name' => array(), 371 * 'last_login' => array() 372 * ) 373 * ) 374 * @return void 375 */ 376 public function createConstraint($table, $name, $definition) 377 { 378 $sql = $this->createConstraintSql($table, $name, $definition); 379 380 return $this->conn->exec($sql); 381 } 382 383 /** 384 * create a constraint on a table 385 * 386 * @param string $table name of the table on which the constraint is to be created 387 * @param string $name name of the constraint to be created 388 * @param array $definition associative array that defines properties of the constraint to be created. 389 * Currently, only one property named FIELDS is supported. This property 390 * is also an associative with the names of the constraint fields as array 391 * constraints. Each entry of this array is set to another type of associative 392 * array that specifies properties of the constraint that are specific to 393 * each field. 394 * 395 * Example 396 * array( 397 * 'fields' => array( 398 * 'user_name' => array(), 399 * 'last_login' => array() 400 * ) 401 * ) 402 * @return void 403 */ 404 public function createConstraintSql($table, $name, $definition) 405 { 406 $table = $this->conn->quoteIdentifier($table); 407 $name = $this->conn->quoteIdentifier($this->conn->formatter->getIndexName($name)); 408 $query = 'ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $name; 409 410 if (isset($definition['primary']) && $definition['primary']) { 411 $query .= ' PRIMARY KEY'; 412 } elseif (isset($definition['unique']) && $definition['unique']) { 413 $query .= ' UNIQUE'; 414 } 415 416 $fields = array(); 417 foreach (array_keys($definition['fields']) as $field) { 418 $fields[] = $this->conn->quoteIdentifier($field, true); 419 } 420 $query .= ' ('. implode(', ', $fields) . ')'; 421 422 return $query; 423 } 424 425 /** 426 * Get the stucture of a field into an array 427 * 428 * @param string $table name of the table on which the index is to be created 429 * @param string $name name of the index to be created 430 * @param array $definition associative array that defines properties of the index to be created. 431 * Currently, only one property named FIELDS is supported. This property 432 * is also an associative with the names of the index fields as array 433 * indexes. Each entry of this array is set to another type of associative 434 * array that specifies properties of the index that are specific to 435 * each field. 436 * 437 * Currently, only the sorting property is supported. It should be used 438 * to define the sorting direction of the index. It may be set to either 439 * ascending or descending. 440 * 441 * Not all DBMS support index sorting direction configuration. The DBMS 442 * drivers of those that do not support it ignore this property. Use the 443 * function supports() to determine whether the DBMS driver can manage indexes. 444 * 445 * Example 446 * array( 447 * 'fields' => array( 448 * 'user_name' => array( 449 * 'sorting' => 'ascending' 450 * ), 451 * 'last_login' => array() 452 * ) 453 * ) 454 * @return void 455 */ 456 public function createIndex($table, $name, array $definition) 457 { 458 return $this->conn->execute($this->createIndexSql($table, $name, $definition)); 459 } 460 461 /** 462 * Get the stucture of a field into an array 463 * 464 * @param string $table name of the table on which the index is to be created 465 * @param string $name name of the index to be created 466 * @param array $definition associative array that defines properties of the index to be created. 467 * @see Doctrine_Export::createIndex() 468 * @return string 469 */ 470 public function createIndexSql($table, $name, array $definition) 471 { 472 $table = $this->conn->quoteIdentifier($table); 473 $name = $this->conn->quoteIdentifier($name); 474 $type = ''; 475 476 if (isset($definition['type'])) { 477 switch (strtolower($definition['type'])) { 478 case 'unique': 479 $type = strtoupper($definition['type']) . ' '; 480 break; 481 default: 482 throw new Doctrine_Export_Exception( 483 'Unknown type ' . $definition['type'] . ' for index ' . $name . ' in table ' . $table 484 ); 485 } 486 } 487 488 $query = 'CREATE ' . $type . 'INDEX ' . $name . ' ON ' . $table; 489 490 $fields = array(); 491 foreach ($definition['fields'] as $field) { 492 $fields[] = $this->conn->quoteIdentifier($field); 493 } 494 $query .= ' (' . implode(', ', $fields) . ')'; 495 496 return $query; 497 } 498 /** 499 * createForeignKeySql 500 * 501 * @param string $table name of the table on which the foreign key is to be created 502 * @param array $definition associative array that defines properties of the foreign key to be created. 503 * @return string 504 */ 505 public function createForeignKeySql($table, array $definition) 506 { 507 $table = $this->conn->quoteIdentifier($table); 508 $query = 'ALTER TABLE ' . $table . ' ADD ' . $this->getForeignKeyDeclaration($definition); 509 510 return $query; 511 } 512 513 /** 514 * createForeignKey 515 * 516 * @param string $table name of the table on which the foreign key is to be created 517 * @param array $definition associative array that defines properties of the foreign key to be created. 518 * @return string 519 */ 520 public function createForeignKey($table, array $definition) 521 { 522 $sql = $this->createForeignKeySql($table, $definition); 523 524 return $this->conn->execute($sql); 525 } 526 527 /** 528 * alter an existing table 529 * (this method is implemented by the drivers) 530 * 531 * @param string $name name of the table that is intended to be changed. 532 * @param array $changes associative array that contains the details of each type 533 * of change that is intended to be performed. The types of 534 * changes that are currently supported are defined as follows: 535 * 536 * name 537 * 538 * New name for the table. 539 * 540 * add 541 * 542 * Associative array with the names of fields to be added as 543 * indexes of the array. The value of each entry of the array 544 * should be set to another associative array with the properties 545 * of the fields to be added. The properties of the fields should 546 * be the same as defined by the MDB2 parser. 547 * 548 * 549 * remove 550 * 551 * Associative array with the names of fields to be removed as indexes 552 * of the array. Currently the values assigned to each entry are ignored. 553 * An empty array should be used for future compatibility. 554 * 555 * rename 556 * 557 * Associative array with the names of fields to be renamed as indexes 558 * of the array. The value of each entry of the array should be set to 559 * another associative array with the entry named name with the new 560 * field name and the entry named Declaration that is expected to contain 561 * the portion of the field declaration already in DBMS specific SQL code 562 * as it is used in the CREATE TABLE statement. 563 * 564 * change 565 * 566 * Associative array with the names of the fields to be changed as indexes 567 * of the array. Keep in mind that if it is intended to change either the 568 * name of a field and any other properties, the change array entries 569 * should have the new names of the fields as array indexes. 570 * 571 * The value of each entry of the array should be set to another associative 572 * array with the properties of the fields to that are meant to be changed as 573 * array entries. These entries should be assigned to the new values of the 574 * respective properties. The properties of the fields should be the same 575 * as defined by the MDB2 parser. 576 * 577 * Example 578 * array( 579 * 'name' => 'userlist', 580 * 'add' => array( 581 * 'quota' => array( 582 * 'type' => 'integer', 583 * 'unsigned' => 1 584 * ) 585 * ), 586 * 'remove' => array( 587 * 'file_limit' => array(), 588 * 'time_limit' => array() 589 * ), 590 * 'change' => array( 591 * 'name' => array( 592 * 'length' => '20', 593 * 'definition' => array( 594 * 'type' => 'text', 595 * 'length' => 20, 596 * ), 597 * ) 598 * ), 599 * 'rename' => array( 600 * 'sex' => array( 601 * 'name' => 'gender', 602 * 'definition' => array( 603 * 'type' => 'text', 604 * 'length' => 1, 605 * 'default' => 'M', 606 * ), 607 * ) 608 * ) 609 * ) 610 * 611 * @param boolean $check indicates whether the function should just check if the DBMS driver 612 * can perform the requested table alterations if the value is true or 613 * actually perform them otherwise. 614 * @return void 615 */ 616 public function alterTable($name, array $changes, $check = false) 617 { 618 $sql = $this->alterTableSql($name, $changes, $check); 619 620 if (is_string($sql) && $sql) { 621 $this->conn->execute($sql); 622 } 623 } 624 625 /** 626 * generates the sql for altering an existing table 627 * (this method is implemented by the drivers) 628 * 629 * @param string $name name of the table that is intended to be changed. 630 * @param array $changes associative array that contains the details of each type * 631 * @param boolean $check indicates whether the function should just check if the DBMS driver 632 * can perform the requested table alterations if the value is true or 633 * actually perform them otherwise. 634 * @see Doctrine_Export::alterTable() 635 * @return string 636 */ 637 public function alterTableSql($name, array $changes, $check = false) 638 { 639 throw new Doctrine_Export_Exception('Alter table not supported by this driver.'); 640 } 641 642 /** 643 * Get declaration of a number of field in bulk 644 * 645 * @param array $fields a multidimensional associative array. 646 * The first dimension determines the field name, while the second 647 * dimension is keyed with the name of the properties 648 * of the field being declared as array indexes. Currently, the types 649 * of supported field properties are as follows: 650 * 651 * length 652 * Integer value that determines the maximum length of the text 653 * field. If this argument is missing the field should be 654 * declared to have the longest length allowed by the DBMS. 655 * 656 * default 657 * Text value to be used as default for this field. 658 * 659 * notnull 660 * Boolean flag that indicates whether this field is constrained 661 * to not be set to null. 662 * charset 663 * Text value with the default CHARACTER SET for this field. 664 * collation 665 * Text value with the default COLLATION for this field. 666 * unique 667 * unique constraint 668 * 669 * @return string 670 */ 671 public function getFieldDeclarationList(array $fields) 672 { 673 foreach ($fields as $fieldName => $field) { 674 $query = $this->getDeclaration($fieldName, $field); 675 676 $queryFields[] = $query; 677 } 678 return implode(', ', $queryFields); 679 } 680 681 /** 682 * Obtain DBMS specific SQL code portion needed to declare a generic type 683 * field to be used in statements like CREATE TABLE. 684 * 685 * @param string $name name the field to be declared. 686 * @param array $field associative array with the name of the properties 687 * of the field being declared as array indexes. Currently, the types 688 * of supported field properties are as follows: 689 * 690 * length 691 * Integer value that determines the maximum length of the text 692 * field. If this argument is missing the field should be 693 * declared to have the longest length allowed by the DBMS. 694 * 695 * default 696 * Text value to be used as default for this field. 697 * 698 * notnull 699 * Boolean flag that indicates whether this field is constrained 700 * to not be set to null. 701 * 702 * charset 703 * Text value with the default CHARACTER SET for this field. 704 * 705 * collation 706 * Text value with the default COLLATION for this field. 707 * 708 * unique 709 * unique constraint 710 * 711 * check 712 * column check constraint 713 * 714 * @return string DBMS specific SQL code portion that should be used to 715 * declare the specified field. 716 */ 717 public function getDeclaration($name, array $field) 718 { 719 720 $default = $this->getDefaultFieldDeclaration($field); 721 722 $charset = (isset($field['charset']) && $field['charset']) ? 723 ' ' . $this->getCharsetFieldDeclaration($field['charset']) : ''; 724 725 $collation = (isset($field['collation']) && $field['collation']) ? 726 ' ' . $this->getCollationFieldDeclaration($field['collation']) : ''; 727 728 $notnull = $this->getNotNullFieldDeclaration($field); 729 730 $unique = (isset($field['unique']) && $field['unique']) ? 731 ' ' . $this->getUniqueFieldDeclaration() : ''; 732 733 $check = (isset($field['check']) && $field['check']) ? 734 ' ' . $field['check'] : ''; 735 736 $method = 'get' . $field['type'] . 'Declaration'; 737 738 try { 739 if (method_exists($this->conn->dataDict, $method)) { 740 return $this->conn->dataDict->$method($name, $field); 741 } else { 742 $dec = $this->conn->dataDict->getNativeDeclaration($field); 743 } 744 745 return $this->conn->quoteIdentifier($name, true) 746 . ' ' . $dec . $charset . $default . $notnull . $unique . $check . $collation; 747 } catch (Exception $e) { 748 throw new Doctrine_Exception('Around field ' . $name . ': ' . $e->getMessage()); 749 } 750 751 } 752 753 /** 754 * getDefaultDeclaration 755 * Obtain DBMS specific SQL code portion needed to set a default value 756 * declaration to be used in statements like CREATE TABLE. 757 * 758 * @param array $field field definition array 759 * @return string DBMS specific SQL code portion needed to set a default value 760 */ 761 public function getDefaultFieldDeclaration($field) 762 { 763 $default = ''; 764 765 if (array_key_exists('default', $field)) { 766 if ($field['default'] === '') { 767 $field['default'] = empty($field['notnull']) 768 ? null : $this->valid_default_values[$field['type']]; 769 770 if ($field['default'] === '' && 771 ($this->conn->getAttribute(Doctrine_Core::ATTR_PORTABILITY) & Doctrine_Core::PORTABILITY_EMPTY_TO_NULL)) { 772 $field['default'] = null; 773 } 774 } 775 776 if ($field['type'] === 'boolean') { 777 $field['default'] = $this->conn->convertBooleans($field['default']); 778 } 779 $default = ' DEFAULT ' . (is_null($field['default']) 780 ? 'NULL' 781 : $this->conn->quote($field['default'], $field['type'])); 782 } 783 784 return $default; 785 } 786 787 788 /** 789 * getNotNullFieldDeclaration 790 * Obtain DBMS specific SQL code portion needed to set a NOT NULL 791 * declaration to be used in statements like CREATE TABLE. 792 * 793 * @param array $field field definition array 794 * @return string DBMS specific SQL code portion needed to set a default value 795 */ 796 public function getNotNullFieldDeclaration(array $definition) 797 { 798 return (isset($definition['notnull']) && $definition['notnull']) ? ' NOT NULL' : ''; 799 } 800 801 802 /** 803 * Obtain DBMS specific SQL code portion needed to set a CHECK constraint 804 * declaration to be used in statements like CREATE TABLE. 805 * 806 * @param array $definition check definition 807 * @return string DBMS specific SQL code portion needed to set a CHECK constraint 808 */ 809 public function getCheckDeclaration(array $definition) 810 { 811 $constraints = array(); 812 foreach ($definition as $field => $def) { 813 if (is_string($def)) { 814 $constraints[] = 'CHECK (' . $def . ')'; 815 } else { 816 if (isset($def['min'])) { 817 $constraints[] = 'CHECK (' . $field . ' >= ' . $def['min'] . ')'; 818 } 819 820 if (isset($def['max'])) { 821 $constraints[] = 'CHECK (' . $field . ' <= ' . $def['max'] . ')'; 822 } 823 } 824 } 825 826 return implode(', ', $constraints); 827 } 828 829 /** 830 * Obtain DBMS specific SQL code portion needed to set an index 831 * declaration to be used in statements like CREATE TABLE. 832 * 833 * @param string $name name of the index 834 * @param array $definition index definition 835 * @return string DBMS specific SQL code portion needed to set an index 836 */ 837 public function getIndexDeclaration($name, array $definition) 838 { 839 $name = $this->conn->quoteIdentifier($name); 840 $type = ''; 841 842 if (isset($definition['type'])) { 843 if (strtolower($definition['type']) == 'unique') { 844 $type = strtoupper($definition['type']) . ' '; 845 } else { 846 throw new Doctrine_Export_Exception( 847 'Unknown type ' . $definition['type'] . ' for index ' . $name 848 ); 849 } 850 } 851 852 if ( ! isset($definition['fields']) || ! is_array($definition['fields'])) { 853 throw new Doctrine_Export_Exception('No columns given for index ' . $name); 854 } 855 856 $query = $type . 'INDEX ' . $name; 857 858 $query .= ' (' . $this->getIndexFieldDeclarationList($definition['fields']) . ')'; 859 860 return $query; 861 } 862 863 /** 864 * getIndexFieldDeclarationList 865 * Obtain DBMS specific SQL code portion needed to set an index 866 * declaration to be used in statements like CREATE TABLE. 867 * 868 * @return string 869 */ 870 public function getIndexFieldDeclarationList(array $fields) 871 { 872 $ret = array(); 873 foreach ($fields as $field => $definition) { 874 if (is_array($definition)) { 875 $ret[] = $this->conn->quoteIdentifier($field); 876 } else { 877 $ret[] = $this->conn->quoteIdentifier($definition); 878 } 879 } 880 return implode(', ', $ret); 881 } 882 883 /** 884 * A method to return the required SQL string that fits between CREATE ... TABLE 885 * to create the table as a temporary table. 886 * 887 * Should be overridden in driver classes to return the correct string for the 888 * specific database type. 889 * 890 * The default is to return the string "TEMPORARY" - this will result in a 891 * SQL error for any database that does not support temporary tables, or that 892 * requires a different SQL command from "CREATE TEMPORARY TABLE". 893 * 894 * @return string The string required to be placed between "CREATE" and "TABLE" 895 * to generate a temporary table, if possible. 896 */ 897 public function getTemporaryTableQuery() 898 { 899 return 'TEMPORARY'; 900 } 901 902 /** 903 * getForeignKeyDeclaration 904 * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint 905 * of a field declaration to be used in statements like CREATE TABLE. 906 * 907 * @param array $definition an associative array with the following structure: 908 * name optional constraint name 909 * 910 * local the local field(s) 911 * 912 * foreign the foreign reference field(s) 913 * 914 * foreignTable the name of the foreign table 915 * 916 * onDelete referential delete action 917 * 918 * onUpdate referential update action 919 * 920 * deferred deferred constraint checking 921 * 922 * The onDelete and onUpdate keys accept the following values: 923 * 924 * CASCADE: Delete or update the row from the parent table and automatically delete or 925 * update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. 926 * Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column 927 * in the parent table or in the child table. 928 * 929 * SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the 930 * child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier 931 * specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported. 932 * 933 * NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary 934 * key value is not allowed to proceed if there is a related foreign key value in the referenced table. 935 * 936 * RESTRICT: Rejects the delete or update operation for the parent table. NO ACTION and RESTRICT are the same as 937 * omitting the ON DELETE or ON UPDATE clause. 938 * 939 * SET DEFAULT 940 * 941 * @return string DBMS specific SQL code portion needed to set the FOREIGN KEY constraint 942 * of a field declaration. 943 */ 944 public function getForeignKeyDeclaration(array $definition) 945 { 946 $sql = $this->getForeignKeyBaseDeclaration($definition); 947 $sql .= $this->getAdvancedForeignKeyOptions($definition); 948 949 return $sql; 950 } 951 952 /** 953 * getAdvancedForeignKeyOptions 954 * Return the FOREIGN KEY query section dealing with non-standard options 955 * as MATCH, INITIALLY DEFERRED, ON UPDATE, ... 956 * 957 * @param array $definition foreign key definition 958 * @return string 959 */ 960 public function getAdvancedForeignKeyOptions(array $definition) 961 { 962 $query = ''; 963 if ( ! empty($definition['onUpdate'])) { 964 $query .= ' ON UPDATE ' . $this->getForeignKeyReferentialAction($definition['onUpdate']); 965 } 966 if ( ! empty($definition['onDelete'])) { 967 $query .= ' ON DELETE ' . $this->getForeignKeyReferentialAction($definition['onDelete']); 968 } 969 return $query; 970 } 971 972 /** 973 * getForeignKeyReferentialAction 974 * 975 * returns given referential action in uppercase if valid, otherwise throws 976 * an exception 977 * 978 * @throws Doctrine_Exception_Exception if unknown referential action given 979 * @param string $action foreign key referential action 980 * @param string foreign key referential action in uppercase 981 */ 982 public function getForeignKeyReferentialAction($action) 983 { 984 $upper = strtoupper($action); 985 switch ($upper) { 986 case 'CASCADE': 987 case 'SET NULL': 988 case 'NO ACTION': 989 case 'RESTRICT': 990 case 'SET DEFAULT': 991 return $upper; 992 break; 993 default: 994 throw new Doctrine_Export_Exception('Unknown foreign key referential action \'' . $upper . '\' given.'); 995 } 996 } 997 998 /** 999 * getForeignKeyBaseDeclaration 1000 * Obtain DBMS specific SQL code portion needed to set the FOREIGN KEY constraint 1001 * of a field declaration to be used in statements like CREATE TABLE. 1002 * 1003 * @param array $definition 1004 * @return string 1005 */ 1006 public function getForeignKeyBaseDeclaration(array $definition) 1007 { 1008 $sql = ''; 1009 if (isset($definition['name'])) { 1010 $sql .= 'CONSTRAINT ' . $this->conn->quoteIdentifier($this->conn->formatter->getForeignKeyName($definition['name'])) . ' '; 1011 } 1012 $sql .= 'FOREIGN KEY ('; 1013 1014 if ( ! isset($definition['local'])) { 1015 throw new Doctrine_Export_Exception('Local reference field missing from definition.'); 1016 } 1017 if ( ! isset($definition['foreign'])) { 1018 throw new Doctrine_Export_Exception('Foreign reference field missing from definition.'); 1019 } 1020 if ( ! isset($definition['foreignTable'])) { 1021 throw new Doctrine_Export_Exception('Foreign reference table missing from definition.'); 1022 } 1023 1024 if ( ! is_array($definition['local'])) { 1025 $definition['local'] = array($definition['local']); 1026 } 1027 if ( ! is_array($definition['foreign'])) { 1028 $definition['foreign'] = array($definition['foreign']); 1029 } 1030 1031 $sql .= implode(', ', array_map(array($this->conn, 'quoteIdentifier'), $definition['local'])) 1032 . ') REFERENCES ' 1033 . $this->conn->quoteIdentifier($definition['foreignTable']) . '(' 1034 . implode(', ', array_map(array($this->conn, 'quoteIdentifier'), $definition['foreign'])) . ')'; 1035 1036 return $sql; 1037 } 1038 1039 /** 1040 * Obtain DBMS specific SQL code portion needed to set the UNIQUE constraint 1041 * of a field declaration to be used in statements like CREATE TABLE. 1042 * 1043 * @return string DBMS specific SQL code portion needed to set the UNIQUE constraint 1044 * of a field declaration. 1045 */ 1046 public function getUniqueFieldDeclaration() 1047 { 1048 return 'UNIQUE'; 1049 } 1050 1051 /** 1052 * Obtain DBMS specific SQL code portion needed to set the CHARACTER SET 1053 * of a field declaration to be used in statements like CREATE TABLE. 1054 * 1055 * @param string $charset name of the charset 1056 * @return string DBMS specific SQL code portion needed to set the CHARACTER SET 1057 * of a field declaration. 1058 */ 1059 public function getCharsetFieldDeclaration($charset) 1060 { 1061 return ''; 1062 } 1063 1064 /** 1065 * Obtain DBMS specific SQL code portion needed to set the COLLATION 1066 * of a field declaration to be used in statements like CREATE TABLE. 1067 * 1068 * @param string $collation name of the collation 1069 * @return string DBMS specific SQL code portion needed to set the COLLATION 1070 * of a field declaration. 1071 */ 1072 public function getCollationFieldDeclaration($collation) 1073 { 1074 return ''; 1075 } 1076 1077 /** 1078 * exportSchema 1079 * method for exporting Doctrine_Record classes to a schema 1080 * 1081 * if the directory parameter is given this method first iterates 1082 * recursively trhough the given directory in order to find any model classes 1083 * 1084 * Then it iterates through all declared classes and creates tables for the ones 1085 * that extend Doctrine_Record and are not abstract classes 1086 * 1087 * @throws Doctrine_Connection_Exception if some error other than Doctrine_Core::ERR_ALREADY_EXISTS 1088 * occurred during the create table operation 1089 * @param string $directory optional directory parameter 1090 * @return void 1091 */ 1092 public function exportSchema($directory = null) 1093 { 1094 if ($directory !== null) { 1095 $models = Doctrine_Core::filterInvalidModels(Doctrine_Core::loadModels($directory)); 1096 } else { 1097 $models = Doctrine_Core::getLoadedModels(); 1098 } 1099 1100 $this->exportClasses($models); 1101 } 1102 1103 public function exportSortedClassesSql($classes, $groupByConnection = true) 1104 { 1105 $connections = array(); 1106 foreach ($classes as $class) { 1107 $connection = Doctrine_Manager::getInstance()->getConnectionForComponent($class); 1108 $connectionName = $connection->getName(); 1109 1110 if ( ! isset($connections[$connectionName])) { 1111 $connections[$connectionName] = array( 1112 'create_tables' => array(), 1113 'create_sequences' => array(), 1114 'create_indexes' => array(), 1115 'alters' => array(), 1116 'create_triggers' => array(), 1117 ); 1118 } 1119 1120 $sql = $connection->export->exportClassesSql(array($class)); 1121 1122 // Build array of all the creates 1123 // We need these to happen first 1124 foreach ($sql as $key => $query) { 1125 // If create table statement 1126 if (substr($query, 0, strlen('CREATE TABLE')) == 'CREATE TABLE') { 1127 $connections[$connectionName]['create_tables'][] = $query; 1128 1129 unset($sql[$key]); 1130 continue; 1131 } 1132 1133 // If create sequence statement 1134 if (substr($query, 0, strlen('CREATE SEQUENCE')) == 'CREATE SEQUENCE') { 1135 $connections[$connectionName]['create_sequences'][] = $query; 1136 1137 unset($sql[$key]); 1138 continue; 1139 } 1140 1141 // If create index statement 1142 if (preg_grep("/CREATE ([^ ]* )?INDEX/", array($query))) { 1143 $connections[$connectionName]['create_indexes'][] = $query; 1144 1145 unset($sql[$key]); 1146 continue; 1147 } 1148 1149 // If alter table statement or oracle anonymous block enclosing alter 1150 if (substr($query, 0, strlen('ALTER TABLE')) == 'ALTER TABLE' 1151 || substr($query, 0, strlen('DECLARE')) == 'DECLARE') { 1152 $connections[$connectionName]['alters'][] = $query; 1153 1154 unset($sql[$key]); 1155 continue; 1156 } 1157 1158 // If create trgger statement 1159 if (substr($query, 0, strlen('CREATE TRIGGER')) == 'CREATE TRIGGER') { 1160 $connections[$connectionName]['create_triggers'][] = $query; 1161 1162 unset($sql[$key]); 1163 continue; 1164 } 1165 1166 // If comment statement 1167 if (substr($query, 0, strlen('COMMENT ON')) == 'COMMENT ON') { 1168 $connections[$connectionName]['comments'][] = $query; 1169 1170 unset($sql[$key]); 1171 continue; 1172 } 1173 } 1174 } 1175 1176 // Loop over all the sql again to merge everything together so it is in the correct order 1177 $build = array(); 1178 foreach ($connections as $connectionName => $sql) { 1179 $build[$connectionName] = array_unique(array_merge($sql['create_tables'], $sql['create_sequences'], $sql['create_indexes'], $sql['alters'], $sql['create_triggers'])); 1180 } 1181 1182 if ( ! $groupByConnection) { 1183 $new = array(); 1184 foreach($build as $connectionname => $sql) { 1185 $new = array_unique(array_merge($new, $sql)); 1186 } 1187 $build = $new; 1188 } 1189 return $build; 1190 } 1191 1192 /** 1193 * exportClasses 1194 * method for exporting Doctrine_Record classes to a schema 1195 * 1196 * FIXME: This function has ugly hacks in it to make sure sql is inserted in the correct order. 1197 * 1198 * @throws Doctrine_Connection_Exception if some error other than Doctrine_Core::ERR_ALREADY_EXISTS 1199 * occurred during the create table operation 1200 * @param array $classes 1201 * @return void 1202 */ 1203 public function exportClasses(array $classes) 1204 { 1205 $queries = $this->exportSortedClassesSql($classes); 1206 1207 foreach ($queries as $connectionName => $sql) { 1208 $connection = Doctrine_Manager::getInstance()->getConnection($connectionName); 1209 1210 $connection->beginTransaction(); 1211 1212 foreach ($sql as $query) { 1213 try { 1214 $connection->exec($query); 1215 } catch (Doctrine_Connection_Exception $e) { 1216 // we only want to silence table already exists errors 1217 if ($e->getPortableCode() !== Doctrine_Core::ERR_ALREADY_EXISTS) { 1218 $connection->rollback(); 1219 throw new Doctrine_Export_Exception($e->getMessage() . '. Failing Query: ' . $query); 1220 } 1221 } 1222 } 1223 1224 $connection->commit(); 1225 } 1226 } 1227 1228 /** 1229 * exportClassesSql 1230 * method for exporting Doctrine_Record classes to a schema 1231 * 1232 * @throws Doctrine_Connection_Exception if some error other than Doctrine_Core::ERR_ALREADY_EXISTS 1233 * occurred during the create table operation 1234 * @param array $classes 1235 * @return void 1236 */ 1237 public function exportClassesSql(array $classes) 1238 { 1239 $models = Doctrine_Core::filterInvalidModels($classes); 1240 1241 $sql = array(); 1242 1243 foreach ($models as $name) { 1244 $record = new $name(); 1245 $table = $record->getTable(); 1246 $parents = $table->getOption('joinedParents'); 1247 1248 foreach ($parents as $parent) { 1249 $data = $table->getConnection()->getTable($parent)->getExportableFormat(); 1250 1251 $query = $this->conn->export->createTableSql($data['tableName'], $data['columns'], $data['options']); 1252 1253 $sql = array_merge($sql, (array) $query); 1254 } 1255 1256 // Don't export the tables with attribute EXPORT_NONE' 1257 if ($table->getAttribute(Doctrine_Core::ATTR_EXPORT) === Doctrine_Core::EXPORT_NONE) { 1258 continue; 1259 } 1260 1261 $data = $table->getExportableFormat(); 1262 1263 $query = $this->conn->export->createTableSql($data['tableName'], $data['columns'], $data['options']); 1264 1265 if (is_array($query)) { 1266 $sql = array_merge($sql, $query); 1267 } else { 1268 $sql[] = $query; 1269 } 1270 1271 if ($table->getAttribute(Doctrine_Core::ATTR_EXPORT) & Doctrine_Core::EXPORT_PLUGINS) { 1272 $sql = array_merge($sql, $this->exportGeneratorsSql($table)); 1273 } 1274 1275 // DC-474: Remove dummy $record from repository to not pollute it during export 1276 $table->getRepository()->evict($record->getOid()); 1277 unset($record); 1278 } 1279 1280 $sql = array_unique($sql); 1281 1282 rsort($sql); 1283 1284 return $sql; 1285 } 1286 1287 /** 1288 * fetches all generators recursively for given table 1289 * 1290 * @param Doctrine_Table $table table object to retrieve the generators from 1291 * @return array an array of Doctrine_Record_Generator objects 1292 */ 1293 public function getAllGenerators(Doctrine_Table $table) 1294 { 1295 $generators = array(); 1296 1297 foreach ($table->getGenerators() as $name => $generator) { 1298 if ($generator === null) { 1299 continue; 1300 } 1301 1302 $generators[] = $generator; 1303 1304 $generatorTable = $generator->getTable(); 1305 1306 if ($generatorTable instanceof Doctrine_Table) { 1307 $generators = array_merge($generators, $this->getAllGenerators($generatorTable)); 1308 } 1309 } 1310 1311 return $generators; 1312 } 1313 1314 /** 1315 * exportGeneratorsSql 1316 * exports plugin tables for given table 1317 * 1318 * @param Doctrine_Table $table the table in which the generators belong to 1319 * @return array an array of sql strings 1320 */ 1321 public function exportGeneratorsSql(Doctrine_Table $table) 1322 { 1323 $sql = array(); 1324 1325 foreach ($this->getAllGenerators($table) as $name => $generator) { 1326 $table = $generator->getTable(); 1327 1328 // Make sure plugin has a valid table 1329 if ($table instanceof Doctrine_Table) { 1330 $data = $table->getExportableFormat(); 1331 1332 $query = $this->conn->export->createTableSql($data['tableName'], $data['columns'], $data['options']); 1333 1334 $sql = array_merge($sql, (array) $query); 1335 } 1336 } 1337 1338 return $sql; 1339 } 1340 1341 /** 1342 * exportSql 1343 * returns the sql for exporting Doctrine_Record classes to a schema 1344 * 1345 * if the directory parameter is given this method first iterates 1346 * recursively trhough the given directory in order to find any model classes 1347 * 1348 * Then it iterates through all declared classes and creates tables for the ones 1349 * that extend Doctrine_Record and are not abstract classes 1350 * 1351 * @throws Doctrine_Connection_Exception if some error other than Doctrine_Core::ERR_ALREADY_EXISTS 1352 * occurred during the create table operation 1353 * @param string $directory optional directory parameter 1354 * @return void 1355 */ 1356 public function exportSql($directory = null) 1357 { 1358 if ($directory !== null) { 1359 $models = Doctrine_Core::filterInvalidModels(Doctrine_Core::loadModels($directory)); 1360 } else { 1361 $models = Doctrine_Core::getLoadedModels(); 1362 } 1363 1364 return $this->exportSortedClassesSql($models, false); 1365 } 1366 1367 /** 1368 * exportTable 1369 * exports given table into database based on column and option definitions 1370 * 1371 * @throws Doctrine_Connection_Exception if some error other than Doctrine_Core::ERR_ALREADY_EXISTS 1372 * occurred during the create table operation 1373 * @return boolean whether or not the export operation was successful 1374 * false if table already existed in the database 1375 */ 1376 public function exportTable(Doctrine_Table $table) 1377 { 1378 try { 1379 $data = $table->getExportableFormat(); 1380 1381 $this->conn->export->createTable($data['tableName'], $data['columns'], $data['options']); 1382 } catch(Doctrine_Connection_Exception $e) { 1383 // we only want to silence table already exists errors 1384 if ($e->getPortableCode() !== Doctrine_Core::ERR_ALREADY_EXISTS) { 1385 throw $e; 1386 } 1387 } 1388 } 1389} 1390