[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4   *  $Id: MssqlDDLBuilder.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   * The SQL DDL-building class for MS SQL Server.
  27   *
  28   *
  29   * @author Hans Lellelid <hans@xmpl.org>
  30   * @package propel.engine.builder.sql.pgsql
  31   */
  32  class MssqlDDLBuilder extends DDLBuilder {
  33  
  34      private static $dropCount = 0;
  35  
  36      /**
  37       *
  38       * @see parent::addDropStatement()
  39       */
  40  	protected function addDropStatements(&$script)
  41      {
  42          $table = $this->getTable();
  43          $platform = $this->getPlatform();
  44  
  45          foreach ($table->getForeignKeys() as $fk) {
  46              $script .= "
  47  IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='".$fk->getName()."')
  48      ALTER TABLE ".$this->quoteIdentifier($table->getName())." DROP CONSTRAINT ".$this->quoteIdentifier($fk->getName()).";
  49  ";
  50          }
  51  
  52  
  53          self::$dropCount++;
  54  
  55          $script .= "
  56  IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = '".$table->getName()."')
  57  BEGIN
  58       DECLARE @reftable_".self::$dropCount." nvarchar(60), @constraintname_".self::$dropCount." nvarchar(60)
  59       DECLARE refcursor CURSOR FOR
  60       select reftables.name tablename, cons.name constraintname
  61        from sysobjects tables,
  62             sysobjects reftables,
  63             sysobjects cons,
  64             sysreferences ref
  65         where tables.id = ref.rkeyid
  66           and cons.id = ref.constid
  67           and reftables.id = ref.fkeyid
  68           and tables.name = '".$table->getName()."'
  69       OPEN refcursor
  70       FETCH NEXT from refcursor into @reftable_".self::$dropCount.", @constraintname_".self::$dropCount."
  71       while @@FETCH_STATUS = 0
  72       BEGIN
  73         exec ('alter table '+@reftable_".self::$dropCount."+' drop constraint '+@constraintname_".self::$dropCount.")
  74         FETCH NEXT from refcursor into @reftable_".self::$dropCount.", @constraintname_".self::$dropCount."
  75       END
  76       CLOSE refcursor
  77       DEALLOCATE refcursor
  78       DROP TABLE ".$this->quoteIdentifier($table->getName())."
  79  END
  80  ";
  81      }
  82  
  83      /**
  84       * @see parent::addColumns()
  85       */
  86  	protected function addTable(&$script)
  87      {
  88          $table = $this->getTable();
  89          $platform = $this->getPlatform();
  90  
  91          $script .= "
  92  /* ---------------------------------------------------------------------- */
  93  /* ".$table->getName()."                                            */
  94  /* ---------------------------------------------------------------------- */
  95  
  96  ";
  97  
  98          $this->addDropStatements($script);
  99  
 100          $script .= "
 101  
 102  CREATE TABLE ".$this->quoteIdentifier($table->getName())."
 103  (
 104      ";
 105  
 106          $lines = array();
 107  
 108          foreach ($table->getColumns() as $col) {
 109              $lines[] = $this->getColumnDDL($col);
 110          }
 111  
 112          if ($table->hasPrimaryKey()) {
 113              $lines[] = "CONSTRAINT ".$this->quoteIdentifier($table->getName())."_PK PRIMARY KEY (".$this->getColumnList($table->getPrimaryKey()).")";
 114          }
 115  
 116          foreach ($table->getUnices() as $unique ) {
 117              $lines[] = "UNIQUE (".$this->getColumnList($unique->getColumns()).")";
 118          }
 119  
 120          $sep = ",
 121      ";
 122          $script .= implode($sep, $lines);
 123          $script .= "
 124  );
 125  ";
 126      }
 127  
 128      /**
 129       * Adds CREATE INDEX statements for this table.
 130       * @see parent::addIndices()
 131       */
 132  	protected function addIndices(&$script)
 133      {
 134          $table = $this->getTable();
 135          $platform = $this->getPlatform();
 136  
 137          foreach ($table->getIndices() as $index) {
 138              $script .= "
 139  CREATE ";
 140              if($index->getIsUnique()) {
 141                  $script .= "UNIQUE";
 142              }
 143              $script .= "INDEX ".$this->quoteIdentifier($index->getName())." ON ".$this->quoteIdentifier($table->getName())." (".$this->getColumnList($index->getColumns()).");
 144  ";
 145          }
 146      }
 147  
 148      /**
 149       *
 150       * @see parent::addForeignKeys()
 151       */
 152  	protected function addForeignKeys(&$script)
 153      {
 154          $table = $this->getTable();
 155          $platform = $this->getPlatform();
 156  
 157          foreach ($table->getForeignKeys() as $fk) {
 158              $script .= "
 159  BEGIN
 160  ALTER TABLE ".$this->quoteIdentifier($table->getName())." ADD CONSTRAINT ".$this->quoteIdentifier($fk->getName())." FOREIGN KEY (".$this->getColumnList($fk->getLocalColumns()) .") REFERENCES ".$this->quoteIdentifier($fk->getForeignTableName())." (".$this->getColumnList($fk->getForeignColumns()).")";
 161              if ($fk->hasOnUpdate()) {
 162                  if ($fk->getOnUpdate() == ForeignKey::SETNULL) { // there may be others that also won't work
 163                      // we have to skip this because it's unsupported.
 164                      $this->warn("MSSQL doesn't support the 'SET NULL' option for ON UPDATE (ignoring for ".$this->getColumnList($fk->getLocalColumns())." fk).");
 165                  } else {
 166                      $script .= " ON UPDATE ".$fk->getOnUpdate();
 167                  }
 168  
 169              }
 170              if ($fk->hasOnDelete()) {
 171                  if ($fk->getOnDelete() == ForeignKey::SETNULL) { // there may be others that also won't work
 172                      // we have to skip this because it's unsupported.
 173                      $this->warn("MSSQL doesn't support the 'SET NULL' option for ON DELETE (ignoring for ".$this->getColumnList($fk->getLocalColumns())." fk).");
 174                  } else {
 175                      $script .= " ON DELETE ".$fk->getOnDelete();
 176                  }
 177              }
 178              $script .= "
 179  END
 180  ;
 181  ";
 182          }
 183      }
 184  
 185  }