0 follower

CMssqlSchema

Package	system.db.schema.mssql
Inheritance	class CMssqlSchema » CDbSchema » CComponent
Since	1.0.4
Version	$Id$
Source Code	framework/db/schema/mssql/CMssqlSchema.php

CMssqlSchema is the class for retrieving metadata information from a MS SQL Server database.

Public Properties

Hide inherited properties

Property	Type	Description	Defined By
commandBuilder	CDbCommandBuilder	the SQL command builder for this connection.	CDbSchema
dbConnection	CDbConnection	database connection.	CDbSchema
tableNames	array	Returns all table names in the database.	CDbSchema
tables	array	Returns the metadata for all tables in the database.	CDbSchema

Public Methods

Hide inherited methods

Method	Description	Defined By
__call()	Calls the named method which is not a class method.	CComponent
__construct()	Constructor.	CDbSchema
__get()	Returns a property value, an event handler list or a behavior based on its name.	CComponent
__isset()	Checks if a property value is null.	CComponent
__set()	Sets value of a component property.	CComponent
__unset()	Sets a component property to be null.	CComponent
asa()	Returns the named behavior object.	CComponent
attachBehavior()	Attaches a behavior to this component.	CComponent
attachBehaviors()	Attaches a list of behaviors to the component.	CComponent
attachEventHandler()	Attaches an event handler to an event.	CComponent
canGetProperty()	Determines whether a property can be read.	CComponent
canSetProperty()	Determines whether a property can be set.	CComponent
compareTableNames()	Compares two table names.	CMssqlSchema
detachBehavior()	Detaches a behavior from the component.	CComponent
detachBehaviors()	Detaches all behaviors from the component.	CComponent
detachEventHandler()	Detaches an existing event handler.	CComponent
disableBehavior()	Disables an attached behavior.	CComponent
disableBehaviors()	Disables all behaviors attached to this component.	CComponent
enableBehavior()	Enables an attached behavior.	CComponent
enableBehaviors()	Enables all behaviors attached to this component.	CComponent
getCommandBuilder()	Returns the SQL command builder for this connection.	CDbSchema
getDbConnection()	Returns database connection. The connection is active.	CDbSchema
getEventHandlers()	Returns the list of attached event handlers for an event.	CComponent
getTable()	Obtains the metadata for the named table.	CDbSchema
getTableNames()	Returns all table names in the database.	CDbSchema
getTables()	Returns the metadata for all tables in the database.	CDbSchema
hasEvent()	Determines whether an event is defined.	CComponent
hasEventHandler()	Checks whether the named event has attached handlers.	CComponent
hasProperty()	Determines whether a property is defined.	CComponent
quoteColumnName()	Quotes a column name for use in a query.	CMssqlSchema
quoteTableName()	Quotes a table name for use in a query.	CMssqlSchema
raiseEvent()	Raises an event.	CComponent
refresh()	Refreshes the schema.	CDbSchema

Protected Methods

Hide inherited methods

Method	Description	Defined By
createColumn()	Creates a table column.	CMssqlSchema
createCommandBuilder()	Creates a command builder for the database.	CMssqlSchema
createTable()	Creates a table instance representing the metadata for the named table.	CMssqlSchema
findColumns()	Collects the table column metadata.	CMssqlSchema
findForeignKeys()	Gets foreign relationship constraint keys and table name	CMssqlSchema
findPrimaryKey()	Gets the primary key column(s) details for the given table.	CMssqlSchema
findTableNames()	Returns all table names in the database.	CMssqlSchema
resolveTableNames()	Generates various kinds of table names.	CMssqlSchema

Method Details

compareTableNames() method

public boolean compareTableNames(string $name1, string $name2)
$name1	string	table name 1
$name2	string	table name 2
{return}	boolean	whether the two table names refer to the same table.

Source Code: framework/db/schema/mssql/CMssqlSchema.php#59 (show)


public function compareTableNames($name1,$name2)
{
    $name1=str_replace(array('[',']'),'',$name1);
    $name1=str_replace(array('[',']'),'',$name2);
    return parent::compareTableNames(strtolower($name1),strtolower($name2));
}

Compares two table names. The table names can be either quoted or unquoted. This method will consider both cases.

createColumn() method

protected CDbColumnSchema createColumn(array $column)
$column	array	column metadata
{return}	CDbColumnSchema	normalized column metadata

Source Code: framework/db/schema/mssql/CMssqlSchema.php#252 (show)


protected function createColumn($column)
{
    $c=new CMssqlColumnSchema;
    $c->name=$column['COLUMN_NAME'];
    $c->rawName=$this->quoteColumnName($c->name);
    $c->allowNull=$column['IS_NULLABLE']=='YES';
    if ($column['NUMERIC_PRECISION_RADIX']!==null)
    {
        // We have a numeric datatype
        $c->size=$c->precision=$column['NUMERIC_PRECISION']!==null?(int)$column['NUMERIC_PRECISION']:null;
        $c->scale=$column['NUMERIC_SCALE']!==null?(int)$column['NUMERIC_SCALE']:null;
    }
    elseif ($column['DATA_TYPE']=='image' || $column['DATA_TYPE']=='text')
        $c->size=$c->precision=null;
    else
        $c->size=$c->precision=($column['CHARACTER_MAXIMUM_LENGTH']!== null)?(int)$column['CHARACTER_MAXIMUM_LENGTH']:null;

    $c->init($column['DATA_TYPE'],$column['COLUMN_DEFAULT']);
    return $c;
}

Creates a table column.

createCommandBuilder() method

protected CDbCommandBuilder createCommandBuilder()
{return}	CDbCommandBuilder	command builder instance

Source Code: framework/db/schema/mssql/CMssqlSchema.php#306 (show)


protected function createCommandBuilder()
{
    return new CMssqlCommandBuilder($this);
}

Creates a command builder for the database. This method overrides parent implementation in order to create a MSSQL specific command builder

createTable() method

protected CMssqlTableSchema createTable($name)
$name
{return}	CMssqlTableSchema	driver dependent table metadata. Null if the table does not exist.

Source Code: framework/db/schema/mssql/CMssqlSchema.php#70 (show)


protected function createTable($name)
{
    $table=new CMssqlTableSchema;
    $this->resolveTableNames($table,$name);
    //if (!in_array($table->name, $this->tableNames)) return null;
    $table->primaryKey=$this->findPrimaryKey($table);
    $table->foreignKeys=$this->findForeignKeys($table);
    if($this->findColumns($table))
    {
        return $table;
    }
    else
        return null;
}

Creates a table instance representing the metadata for the named table.

findColumns() method

protected boolean findColumns(CMssqlTableSchema $table)
$table	CMssqlTableSchema	the table metadata
{return}	boolean	whether the table exists in the database

Source Code: framework/db/schema/mssql/CMssqlSchema.php#217 (show)


protected function findColumns($table)
{
    $where=array();
    $where[]="TABLE_NAME='".$table->name."'";
    if (isset($table->catalogName))
        $where[]="TABLE_CATALOG='".$table->catalogName."'";
    if (isset($table->schemaName))
        $where[]="TABLE_SCHEMA='".$table->schemaName."'";
    $sql="SELECT *, columnproperty(object_id(table_schema+'.'+table_name), column_name, 'IsIdentity') as IsIdentity ".
         "FROM INFORMATION_SCHEMA.COLUMNS WHERE ".join(' AND ',$where);
    if (($columns=$this->getDbConnection()->createCommand($sql)->queryAll())===array())
        return false;

    foreach($columns as $column)
    {
        $c=$this->createColumn($column);
        if (is_array($table->primaryKey))
            $c->isPrimaryKey=in_array($c->name, $table->primaryKey);
        else
            $c->isPrimaryKey=strcasecmp($c->name,$table->primaryKey)===0;

        $c->isForeignKey=isset($table->foreignKeys[$c->name]);
        $table->columns[$c->name]=$c;
        if ($column['IsIdentity']==1 && $table->sequenceName===null)
            $table->sequenceName='';

    }
    return true;
}

Collects the table column metadata.

findForeignKeys() method

protected array findForeignKeys(CMssqlTableSchema $table)
$table	CMssqlTableSchema	table
{return}	array	foreign relationship table name and keys.

Source Code: framework/db/schema/mssql/CMssqlSchema.php#164 (show)


protected function findForeignKeys($table)
{
    $rc='INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS';
    $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
    if (isset($table->catalogName))
    {
        $kcu=$table->catalogName.'.'.$kcu;
        $rc=$table->catalogName.'.'.$rc;
    }

    //From https://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx
    $sql = <<<EOD
    SELECT
         KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
       , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
       , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
       , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
       , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
       , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
       , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
       , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
    FROM {$this->quoteTableName($rc)} RC
    JOIN {$this->quoteTableName($kcu)} KCU1
    ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
       AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
       AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
    JOIN {$this->quoteTableName($kcu)} KCU2
    ON KCU2.CONSTRAINT_CATALOG =
    RC.UNIQUE_CONSTRAINT_CATALOG
       AND KCU2.CONSTRAINT_SCHEMA =
    RC.UNIQUE_CONSTRAINT_SCHEMA
       AND KCU2.CONSTRAINT_NAME =
    RC.UNIQUE_CONSTRAINT_NAME
       AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
    WHERE KCU1.TABLE_NAME = :table
EOD;
    $command = $this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table', $table->name);
    $fkeys=array();
    foreach($command->queryAll() as $info)
    {
        $fkeys[$info['FK_COLUMN_NAME']]=array($info['UQ_TABLE_NAME'],$info['UQ_COLUMN_NAME'],);

    }
    return $fkeys;
}

Gets foreign relationship constraint keys and table name

findPrimaryKey() method

protected mixed findPrimaryKey(CMssqlTableSchema $table)
$table	CMssqlTableSchema	table
{return}	mixed	primary keys (null if no pk, string if only 1 column pk, or array if composite pk)

Source Code: framework/db/schema/mssql/CMssqlSchema.php#123 (show)


protected function findPrimaryKey($table)
{
    $kcu='INFORMATION_SCHEMA.KEY_COLUMN_USAGE';
    $tc='INFORMATION_SCHEMA.TABLE_CONSTRAINTS';
    if (isset($table->catalogName))
    {
        $kcu=$table->catalogName.'.'.$kcu;
        $tc=$table->catalogName.'.'.$tc;
    }

    $sql = <<<EOD
    SELECT k.column_name field_name
        FROM {$this->quoteTableName($kcu)} k
        LEFT JOIN {$this->quoteTableName($tc)} c
          ON k.table_name = c.table_name
         AND k.constraint_name = c.constraint_name
       WHERE c.constraint_type ='PRIMARY KEY'
               AND k.table_name = :table
            AND k.table_schema = :schema
EOD;
    $command = $this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table', $table->name);
    $command->bindValue(':schema', $table->schemaName);
    $primary=$command->queryColumn();
    switch (count($primary))
    {
        case 0: // No primary key on table
            $primary=null;
            break;
        case 1: // Only 1 primary key
            $primary=$primary[0];
            break;
    }
    return $primary;
}

Gets the primary key column(s) details for the given table.

findTableNames() method (available since v1.0.4)

protected array findTableNames($schema='')
$schema
{return}	array	all table names in the database.

Source Code: framework/db/schema/mssql/CMssqlSchema.php#278 (show)


protected function findTableNames($schema='')
{
    if($schema==='')
        $schema=self::DEFAULT_SCHEMA;
    $sql=<<<EOD
SELECT TABLE_NAME, TABLE_SCHEMA FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA=:schema
EOD;
    $command=$this->getDbConnection()->createCommand($sql);
    $command->bindParam(":schema", $schema);
    $rows=$command->queryAll();
    $names=array();
    foreach ($rows as $row)
    {
        if ($schema == self::DEFAULT_SCHEMA)
            $names[]=$row['TABLE_NAME'];
        else
            $names[]=$schema.'.'.$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME'];
    }

    return $names;
}

Returns all table names in the database.

quoteColumnName() method

public string quoteColumnName(string $name)
$name	string	column name
{return}	string	the properly quoted column name

Source Code: framework/db/schema/mssql/CMssqlSchema.php#46 (show)


public function quoteColumnName($name)
{
    return '['.$name.']';
}

Quotes a column name for use in a query.

quoteTableName() method

public string quoteTableName(string $name)
$name	string	table name
{return}	string	the properly quoted table name

Source Code: framework/db/schema/mssql/CMssqlSchema.php#31 (show)


public function quoteTableName($name)
{
    if (strpos($name,'.')===false)
        return '['.$name.']';
    $names=explode('.',$name);
    foreach ($names as &$n)
        $n = '['.$n.']';
    return implode('.',$names);
}

Quotes a table name for use in a query.

resolveTableNames() method

protected void resolveTableNames(CMssqlTableSchema $table, string $name)
$table	CMssqlTableSchema	the table instance
$name	string	the unquoted table name

Source Code: framework/db/schema/mssql/CMssqlSchema.php#90 (show)


protected function resolveTableNames($table,$name)
{
    $parts=explode('.',str_replace(array('[',']'),'',$name));
    if(($c=count($parts))==3)
    {
        // Catalog name, schema name and table name provided
        $table->catalogName=$parts[0];
        $table->schemaName=$parts[1];
        $table->name=$parts[2];
        $table->rawName=$this->quoteTableName($table->catalogName).'.'.$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
    }
    elseif ($c==2)
    {
        // Only schema name and table name provided
        $table->name=$parts[1];
        $table->schemaName=$parts[0];
        $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
    }
    else
    {
        // Only the name given, we need to get at least the schema name
        //if (empty($this->_schemaNames)) $this->findTableNames();
        $table->name=$parts[0];
        $table->schemaName=self::DEFAULT_SCHEMA;
        $table->rawName=$this->quoteTableName($table->schemaName).'.'.$this->quoteTableName($table->name);
    }
}

Generates various kinds of table names.