[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4   *  $Id: MysqlDDLBuilder.php 1310 2006-05-04 07:36:54Z fabien $
   5   *
   6   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   7   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   8   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   9   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  10   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  11   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  12   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  13   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  14   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  15   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  16   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  17   *
  18   * This software consists of voluntary contributions made by many individuals
  19   * and is licensed under the LGPL. For more information please see
  20   * <http://propel.phpdb.org>.
  21   */
  22  
  23  require_once 'propel/engine/builder/sql/DDLBuilder.php';
  24  
  25  /**
  26   * DDL Builder class for MySQL.
  27   *
  28   * @author David Zülke
  29   * @author Hans Lellelid <hans@xmpl.org>
  30   * @package propel.engine.builder.sql.mysql
  31   */
  32  class MysqlDDLBuilder extends DDLBuilder {
  33  
  34      /**
  35       * Returns some header SQL that disables foreign key checking.
  36       * @return string DDL
  37       */
  38  	public static function getDatabaseStartDDL()
  39      {
  40          $ddl = "
  41  # This is a fix for InnoDB in MySQL >= 4.1.x
  42  # It \"suspends judgement\" for fkey relationships until are tables are set.
  43  SET FOREIGN_KEY_CHECKS = 0;
  44  ";
  45          return $ddl;
  46      }
  47  
  48      /**
  49       * Returns some footer SQL that re-enables foreign key checking.
  50       * @return string DDL
  51       */
  52  	public static function getDatabaseEndDDL()
  53      {
  54          $ddl = "
  55  # This restores the fkey checks, after having unset them earlier
  56  SET FOREIGN_KEY_CHECKS = 1;
  57  ";
  58          return $ddl;
  59      }
  60  
  61  
  62      /**
  63       *
  64       * @see parent::addDropStatement()
  65       */
  66  	protected function addDropStatements(&$script)
  67      {
  68          $script .= "
  69  DROP TABLE IF EXISTS ".$this->quoteIdentifier($this->getTable()->getName()).";
  70  ";
  71      }
  72  
  73      /**
  74       * Builds the SQL for current table and returns it as a string.
  75       *
  76       * This is the main entry point and defines a basic structure that classes should follow.
  77       * In most cases this method will not need to be overridden by subclasses.
  78       *
  79       * @return string The resulting SQL DDL.
  80       */
  81  	public function build()
  82      {
  83          $script = "";
  84          $this->addTable($script);
  85          return $script;
  86      }
  87  
  88      /**
  89       *
  90       * @see parent::addColumns()
  91       */
  92  	protected function addTable(&$script)
  93      {
  94          $table = $this->getTable();
  95          $platform = $this->getPlatform();
  96  
  97          $script .= "
  98  #-----------------------------------------------------------------------------
  99  #-- ".$table->getName()."
 100  #-----------------------------------------------------------------------------
 101  ";
 102  
 103          $this->addDropStatements($script);
 104  
 105          $script .= "
 106  
 107  CREATE TABLE ".$this->quoteIdentifier($table->getName())."
 108  (
 109      ";
 110  
 111          $lines = array();
 112  
 113          foreach ($table->getColumns() as $col) {
 114              $entry = $this->getColumnDDL($col);
 115              if ($col->getDescription()) {
 116                  $entry .= " COMMENT '".$platform->escapeText($col->getDescription())."'";
 117              }
 118              $lines[] = $entry;
 119          }
 120  
 121          if ($table->hasPrimaryKey()) {
 122              $lines[] = "PRIMARY KEY (".$this->getColumnList($table->getPrimaryKey()).")";
 123          }
 124  
 125          $this->addIndicesLines($lines);
 126          $this->addForeignKeysLines($lines);
 127  
 128          $sep = ",
 129      ";
 130          $script .= implode($sep, $lines);
 131  
 132          $script .= "
 133  )";
 134  
 135          $mysqlTableType = $this->getBuildProperty("mysqlTableType");
 136          if (!$mysqlTableType) {
 137              $vendorSpecific = $table->getVendorSpecificInfo();
 138              if(isset($vendorSpecific['Type'])) {
 139                  $mysqlTableType = $vendorSpecific['Type'];
 140              } else {
 141                  $mysqlTableType = 'MyISAM';
 142              }
 143          }
 144  
 145          $script .= "Type=$mysqlTableType";
 146          if($table->getDescription()) {
 147              $script .= " COMMENT='".$platform->escapeText($table->getDescription())."'";
 148          }
 149          $script .= ";
 150  ";
 151      }
 152  
 153      /**
 154       * Creates a comma-separated list of column names for the index.
 155       * For MySQL unique indexes there is the option of specifying size, so we cannot simply use
 156       * the getColumnsList() method.
 157       * @param Index $index
 158       * @return string
 159       */
 160  	private function getIndexColumnList(Index $index)
 161      {
 162          $platform = $this->getPlatform();
 163  
 164          $cols = $index->getColumns();
 165          $list = array();
 166          foreach($cols as $col) {
 167              $list[] = $this->quoteIdentifier($col) . ($index->hasColumnSize($col) ? '(' . $index->getColumnSize($col) . ')' : '');
 168          }
 169          return implode(', ', $list);
 170      }
 171  
 172      /**
 173       * Adds indexes
 174       */
 175  	protected function addIndicesLines(&$lines)
 176      {
 177          $table = $this->getTable();
 178          $platform = $this->getPlatform();
 179  
 180          foreach ($table->getUnices() as $unique) {
 181              $lines[] = "UNIQUE KEY ".$this->quoteIdentifier($unique->getName())." (".$this->getIndexColumnList($unique).")";
 182          }
 183  
 184          foreach ($table->getIndices() as $index ) {
 185              $vendor = $index->getVendorSpecificInfo();
 186              $lines[] .= (($vendor && $vendor['Index_type'] == 'FULLTEXT') ? 'FULLTEXT ' : '') . "KEY " . $this->quoteIdentifier($index->getName()) . "(" . $this->getIndexColumnList($index) . ")";
 187          }
 188  
 189      }
 190  
 191      /**
 192       * Adds foreign key declarations & necessary indexes for mysql (if they don't exist already).
 193       * @see parent::addForeignKeys()
 194       */
 195  	protected function addForeignKeysLines(&$lines)
 196      {
 197          $table = $this->getTable();
 198          $platform = $this->getPlatform();
 199  
 200  
 201          $_indices = array();
 202          $_previousColumns = array();
 203  
 204          // we're building an array of indices here which is smart about multi-column indices.
 205          // for example, if we have to indices foo(ColA) and bar(ColB, ColC), we have actually three indices already defined:
 206          // ColA, ColB+ColC, and ColB (but not ColC!). This is because of the way SQL multi-column indices work.
 207          // we will later match found, defined foreign key and referenced column definitions against this array to know
 208          // whether we should create a new index for mysql or not
 209          foreach($table->getPrimaryKey() as $_primaryKeyColumn) {
 210              // do the above for primary keys
 211              $_previousColumns[] = $this->quoteIdentifier($_primaryKeyColumn->getName());
 212              $_indices[] = implode(',', $_previousColumns);
 213          }
 214  
 215          $_tableIndices = array_merge($table->getIndices(), $table->getUnices());
 216          foreach($_tableIndices as $_index) {
 217              // same procedure, this time for unices and indices
 218              $_previousColumns = array();
 219              $_indexColumns = $_index->getColumns();
 220              foreach($_indexColumns as $_indexColumn) {
 221                  $_previousColumns[] = $this->quoteIdentifier($_indexColumn);
 222                  $_indices[] = implode(',', $_previousColumns);
 223              }
 224          }
 225  
 226          // we're determining which tables have foreign keys that point to this table, since MySQL needs an index on
 227          // any column that is referenced by another table (yep, MySQL _is_ a PITA)
 228          $counter = 0;
 229          $allTables = $table->getDatabase()->getTables();
 230          foreach($allTables as $_table) {
 231              foreach($_table->getForeignKeys() as $_foreignKey) {
 232                  if($_foreignKey->getForeignTableName() == $table->getName()) {
 233                      if(!in_array($this->getColumnList($_foreignKey->getForeignColumns()), $_indices)) {
 234                          // no matching index defined in the schema, so we have to create one
 235                          $lines[] = "INDEX ".$this->quoteIdentifier("I_referenced_".$_foreignKey->getName()."_".(++$counter))." (" .$this->getColumnList($_foreignKey->getForeignColumns()).")";
 236                      }
 237                  }
 238              }
 239          }
 240  
 241          foreach ($table->getForeignKeys() as $fk) {
 242  
 243              $indexName = $this->quoteIdentifier(substr_replace($fk->getName(), 'FI_',  strrpos($fk->getName(), 'FK_'), 3));
 244  
 245              if(!in_array($this->getColumnList($fk->getLocalColumns()), $_indices)) {
 246                  // no matching index defined in the schema, so we have to create one. MySQL needs indices on any columns that serve as foreign keys. these are not auto-created prior to 4.1.2
 247                  $lines[] = "INDEX $indexName (".$this->getColumnList($fk->getLocalColumns()).")";
 248              }
 249              $str = "CONSTRAINT ".$this->quoteIdentifier($fk->getName())."
 250          FOREIGN KEY (".$this->getColumnList($fk->getLocalColumns()).")
 251          REFERENCES ".$this->quoteIdentifier($fk->getForeignTableName()) . " (".$this->getColumnList($fk->getForeignColumns()).")";
 252              if ($fk->hasOnUpdate()) {
 253                  $str .= "
 254          ON UPDATE ".$fk->getOnUpdate();
 255              }
 256              if ($fk->hasOnDelete()) {
 257                  $str .= "
 258          ON DELETE ".$fk->getOnDelete();
 259              }
 260              $lines[] = $str;
 261          }
 262  
 263      }
 264  
 265      /**
 266       * Checks whether passed-in array of Column objects contains a column with specified name.
 267       * @param array Column[] or string[]
 268       * @param string $searchcol Column name to search for
 269       */
 270  	private function containsColname($columns, $searchcol)
 271      {
 272          foreach($columns as $col) {
 273              if ($col instanceof Column) {
 274                  $col = $col->getName();
 275              }
 276              if ($col == $searchcol) {
 277                  return true;
 278              }
 279          }
 280          return false;
 281      }
 282  
 283      /**
 284       * Not used for MySQL since foreign keys are declared inside table declaration.
 285       * @see addForeignKeysLines()
 286       */
 287  	protected function addForeignKeys(&$script)
 288      {
 289      }
 290  
 291      /**
 292       * Not used for MySQL since indexes are declared inside table declaration.
 293       * @see addIndicesLines()
 294       */
 295  	protected function addIndices(&$script)
 296      {
 297      }
 298  
 299  }