[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: MSSQLTableInfo.php,v 1.14 2006/01/17 19:44:39 hlellelid Exp $
   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 please see
  19   * <http://creole.phpdb.org>.
  20   */
  21  
  22  require_once 'creole/CreoleTypes.php';
  23  require_once 'creole/metadata/TableInfo.php';
  24  
  25  /**
  26   * MSSQL implementation of TableInfo.
  27   * 
  28   * @author    Hans Lellelid <hans@xmpl.org>
  29   * @version   $Revision: 1.14 $
  30   * @package   creole.drivers.mssql.metadata
  31   */
  32  class MSSQLTableInfo extends TableInfo {    
  33      
  34      /**
  35       * Loads the columns for this table.
  36       * @return void
  37       */                          
  38      protected function initColumns() 
  39      {    
  40          include_once 'creole/metadata/ColumnInfo.php';
  41          include_once 'creole/drivers/mssql/MSSQLTypes.php';
  42          
  43          if (!@mssql_select_db($this->dbname, $this->conn->getResource())) {
  44              throw new SQLException('No database selected');
  45          }
  46           
  47          $res = mssql_query("sp_columns ".$this->name, $this->conn->getResource());
  48          if (!$res) {
  49              throw new SQLException('Could not get column names', mssql_get_last_message());
  50          }
  51          
  52          while ($row = mssql_fetch_array($res)) {
  53              $name = $row['COLUMN_NAME'];
  54              $type = $row['TYPE_NAME'];
  55              $length = $row['LENGTH'];
  56              $is_nullable = $row['NULLABLE'];
  57              $default = $row['COLUMN_DEF'];
  58              $precision = $row['PRECISION'];
  59              $scale = $row['SCALE'];
  60              $identity = false;
  61              if (strtolower($type) == "int identity") {
  62                  $identity = true;
  63              }
  64              $this->columns[$name] = new ColumnInfo($this, $name, MSSQLTypes::getType($type), $type, $length, $precision, $scale, $is_nullable, $default, $identity);
  65          }
  66                  
  67          $this->colsLoaded = true;
  68      }
  69  
  70      /**
  71       * Loads the indexes for this table.
  72       * @return void
  73       */      
  74      protected function initIndexes()
  75      {
  76          // columns have to be loaded first
  77          if (!$this->colsLoaded) $this->initColumns();
  78          include_once 'creole/metadata/IndexInfo.php';
  79          
  80          if (!@mssql_select_db($this->dbname, $this->conn->getResource())) {
  81              throw new SQLException('No database selected');
  82          } 
  83          
  84          $res = mssql_query("sp_indexes_rowset ".$this->name, $this->conn->getResource());
  85          
  86          while ($row = mssql_fetch_array($res)) {
  87              $name = $row['INDEX_NAME'];            
  88              // All primary keys are indexes (right...?)
  89              if (!isset($this->indexes[$name])) {
  90                  $this->indexes[$name] = new IndexInfo($name);
  91              }
  92              $this->indexes[$name]->addColumn($this->columns[ $row['COLUMN_NAME'] ]);
  93          }
  94          
  95          $this->indexesLoaded = true;        
  96      }
  97  
  98      /**
  99       * Loads the foreign keys for this table.
 100       * @return void
 101       */      
 102      protected function initForeignKeys()
 103      {
 104          // columns have to be loaded first
 105          if (!$this->colsLoaded) $this->initColumns();
 106          include_once 'creole/metadata/ForeignKeyInfo.php';
 107          
 108          if (!@mssql_select_db($this->dbname, $this->conn->getResource())) {
 109              throw new SQLException('No database selected');
 110          } 
 111          
 112          $res = mssql_query("SELECT     ccu1.TABLE_NAME, ccu1.COLUMN_NAME, ccu2.TABLE_NAME AS FK_TABLE_NAME, ccu2.COLUMN_NAME AS FK_COLUMN_NAME
 113                              FROM         INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1 INNER JOIN
 114                                        INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1 ON tc1.CONSTRAINT_NAME = ccu1.CONSTRAINT_NAME AND 
 115                                        CONSTRAINT_TYPE = 'Foreign Key' INNER JOIN
 116                                        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON rc1.CONSTRAINT_NAME = tc1.CONSTRAINT_NAME INNER JOIN
 117                                        INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON ccu2.CONSTRAINT_NAME = rc1.UNIQUE_CONSTRAINT_NAME
 118                              WHERE     (ccu1.table_name = '".$this->name."')", $this->conn->getResource());
 119          
 120          while($row = mssql_fetch_array($res)) {
 121              $name = $row['COLUMN_NAME'];
 122              $ftbl = $row['FK_TABLE_NAME'];
 123              $fcol = $row['FK_COLUMN_NAME'];
 124  
 125              if (!isset($this->foreignKeys[$name])) {
 126                  $this->foreignKeys[$name] = new ForeignKeyInfo($name);
 127  
 128                  if ($this->database->hasTable($ftbl)) {
 129                      $foreignTable = $this->database->getTable($ftbl);
 130                  } else {                
 131                      $foreignTable = new TableInfo($ltbl);
 132                      $this->database->addTable($foreignTable);
 133                  }
 134  
 135                  if ($foreignTable->hasColumn($fcol)) {
 136                      $foreignCol = $foreignTable->getColumn($fcol);
 137                  } else {                
 138                      $foreignCol = new ColumnInfo($foreignTable, $fcol);
 139                      $foreignTable->addColumn($foreignCol);
 140                  }
 141                                  
 142                  $this->foreignKeys[$name]->addReference($this->columns[$name], $foreignCol);
 143              }
 144          }
 145          
 146          $this->fksLoaded = true;
 147      }
 148  
 149      /**
 150       * Loads the primary key info for this table.
 151       * @return void
 152       */      
 153      protected function initPrimaryKey()
 154      {
 155          // columns have to be loaded first
 156          if (!$this->colsLoaded) $this->initColumns();
 157          include_once 'creole/metadata/PrimaryKeyInfo.php';
 158          
 159          if (!@mssql_select_db($this->dbname, $this->conn->getResource())) {
 160              throw new SQLException('No database selected');
 161          } 
 162          
 163          $res = mssql_query("SELECT COLUMN_NAME 
 164                          FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 165                                  INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ON 
 166                        INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name
 167                          WHERE     (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY') AND 
 168                        (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = '".$this->name."')", $this->conn->getResource());
 169          
 170          // Loop through the returned results, grouping the same key_name together.
 171          // name of the primary key will be the first column name in the key.
 172          while($row = mssql_fetch_row($res)) {
 173              $name = $row[0];            
 174              if (!isset($this->primaryKey)) {
 175                  $this->primaryKey = new PrimaryKeyInfo($name);
 176              }
 177              $this->primaryKey->addColumn($this->columns[ $name ]);
 178          }        
 179          
 180          $this->pkLoaded = true;
 181      }    
 182      
 183  }