Home | Classes | Modules | Annotated | Functions | Files

MSSQLTableInfo.php

Go to the documentation of this file.

<?php
/*
 *  $Id: MSSQLTableInfo.php,v 1.1 2004/05/02 21:45:52 micha Exp $
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
 * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
 * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
 * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
 * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * This software consists of voluntary contributions made by many individuals
 * and is licensed under the LGPL. For more information please see
 * <http://creole.phpdb.org>.
 */

require_once 'creole/CreoleTypes.php';
require_once 'creole/metadata/TableInfo.php';

class MSSQLTableInfo extends TableInfo 
{    
  function initColumns() 
  {    
    include_once 'creole/metadata/ColumnInfo.php';
    include_once 'creole/drivers/mssql/MSSQLTypes.php';
    
    if (!@mssql_select_db($this->dbname, $this->dblink)) {
      return new SQLException(CREOLE_ERROR_NODBSELECTED, 'No database selected');
    }
     
    $res = mssql_query("sp_columns ".$this->name, $this->dblink);
    if (!$res) {
      return new SQLException(CREOLE_ERROR, 'Could not get column names', mssql_get_last_message());
    }
    
    while ($row = mssql_fetch_array($res)) {
      $name = $row['COLUMN_NAME'];
      $type = $row['TYPE_NAME'];
      $length = $row['LENGTH'];
      $is_nullable = $row['NULLABLE'];
      $default = $row['COLUMN_DEF'];
      $precision = $row['PRECISION'];
      $this->columns[$name] =& new ColumnInfo($this, $name, MSSQLTypes::getType($type), $type, $length, $precision, $is_nullable, $default);
    }
            
    $this->colsLoaded = true;
    return true;
  }

  function initIndexes()
  {
    // columns have to be loaded first
    if (!$this->colsLoaded) $this->initColumns();
    include_once 'creole/metadata/IndexInfo.php';
    
    if (!@mssql_select_db($this->dbname, $this->dblink)) {
      return new SQLException(CREOLE_ERROR_NODBSELECTED, 'No database selected');
    } 
    
    $res = mssql_query("sp_indexes_rowset ".$this->name, $this->dblink);
    
    while ($row = mssql_fetch_array($res)) {
      $name = $row['INDEX_NAME'];            
      // All primary keys are indexes (right...?)
      if (!isset($this->indexes[$name])) {
        $this->indexes[$name] =& new IndexInfo($name);
      }
      $this->indexes[$name]->addColumn($this->columns[ $row['COLUMN_NAME'] ]);
    }
    
    $this->indexesLoaded = true;        
    return true;
  }

  function initForeignKeys()
  {
    // columns have to be loaded first
    if (!$this->colsLoaded) {
      if (($e = $this->initColumns()) !== true) {
        return $e;
      }
    }
    
    include_once 'creole/metadata/ForeignKeyInfo.php';
    
    if (!@mssql_select_db($this->dbname, $this->dblink)) {
      return new SQLException(CREOLE_ERROR_NODBSELECTED, 'No database selected');
    } 
    
    $res = mssql_query("SELECT     ccu1.TABLE_NAME, ccu1.COLUMN_NAME, ccu2.TABLE_NAME AS FK_TABLE_NAME, ccu2.COLUMN_NAME AS FK_COLUMN_NAME
                        FROM         INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1 INNER JOIN
                                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1 ON tc1.CONSTRAINT_NAME = ccu1.CONSTRAINT_NAME AND 
                                  CONSTRAINT_TYPE = 'Foreign Key' INNER JOIN
                                  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON rc1.CONSTRAINT_NAME = tc1.CONSTRAINT_NAME INNER JOIN
                                  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON ccu2.CONSTRAINT_NAME = rc1.UNIQUE_CONSTRAINT_NAME
                        WHERE     (ccu1.table_name = '".$this->name."')", $this->dblink);
    
    while($row = mssql_fetch_array($res)) 
    {
      $name = $row['COLUMN_NAME'];
      $ftbl = $row['FK_TABLE_NAME'];
      $fcol = $row['FK_COLUMN_NAME'];

      if (!isset($this->foreignKeys[$name])) 
      {
        $this->foreignKeys[$name] =& new ForeignKeyInfo($name);
        
        /* TODO: when does getTable() return null ? */
        if (($foreignTable =& $this->database->getTable($ftbl)) === null) {
          $foreignTable =& new TableInfo($ltbl);
          $this->database->addTable($foreignTable);
        }
        
        if (Creole::isError($foreignTable)) { return $foreignTable; }
        
        /* TODO: when does getColumn() return null ? */
        if (($foreignCol = $foreignTable->getColumn($name)) === null) {
          $foreignCol = new ColumnInfo($foreignTable, $name);
          $foreignTable->addColumn($foreignCol);
        }
                        
        if (Creole::isError($foreignCol)) { return $foreignCol; }
        
        $this->foreignKeys[$name]->addReference($this->columns[$name], $foreignCol);
      }
    }
    
    $this->fksLoaded = true;
    return true;
  }

  function initPrimaryKey()
  {
    // columns have to be loaded first
    if (!$this->colsLoaded) {
      if (($e = $this->initColumns()) !== true) {
        return $e;
      }
    }
    
    include_once 'creole/metadata/PrimaryKeyInfo.php';
    
    if (!@mssql_select_db($this->dbname, $this->dblink)) {
      return new SQLException(CREOLE_ERROR_NODBSELECTED, 'No database selected');
    } 
    
    $res = mssql_query("SELECT COLUMN_NAME 
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                            INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ON 
                  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME = INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.constraint_name
                    WHERE     (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_TYPE = 'PRIMARY KEY') AND 
                  (INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = '".$this->name."')", $this->dblink);
    
    // Loop through the returned results, grouping the same key_name together.
    // name of the primary key will be the first column name in the key.
    while($row = mssql_fetch_row($res)) {
      $name = $row[0];            
      if (!isset($this->primaryKey)) {
        $this->primaryKey =& new PrimaryKeyInfo($name);
      }
      $this->primaryKey->addColumn($this->columns[ $name ]);
    }        
    
    $this->pkLoaded = true;
    return true;
  }    
  
}

---

This file is part of the Creole[php4] library.