0 follower

CMssqlSchema

Package system.db.schema.mssql
Inheritance class CMssqlSchema » CDbSchema » CComponent
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

PropertyTypeDescriptionDefined By
columnTypes array the abstract column types mapped to physical column types. CMssqlSchema
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

MethodDescriptionDefined 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
addColumn() Builds a SQL statement for adding a new DB column. CDbSchema
addForeignKey() Builds a SQL statement for adding a foreign key constraint to an existing table. CDbSchema
addPrimaryKey() Builds a SQL statement for adding a primary key constraint to an existing table. CDbSchema
alterColumn() Builds a SQL statement for changing the definition of a column. CMssqlSchema
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
checkIntegrity() Enables or disables integrity check. CMssqlSchema
compareTableNames() Compares two table names. CMssqlSchema
createIndex() Builds a SQL statement for creating a new index. CDbSchema
createTable() Builds a SQL statement for creating a new DB table. CDbSchema
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
dropColumn() Builds a SQL statement for dropping a DB column. CDbSchema
dropForeignKey() Builds a SQL statement for dropping a foreign key constraint. CDbSchema
dropIndex() Builds a SQL statement for dropping an index. CDbSchema
dropPrimaryKey() Builds a SQL statement for removing a primary key constraint to an existing table. CDbSchema
dropTable() Builds a SQL statement for dropping a DB table. CDbSchema
enableBehavior() Enables an attached behavior. CComponent
enableBehaviors() Enables all behaviors attached to this component. CComponent
evaluateExpression() Evaluates a PHP expression or callback under the context of this component. CComponent
getColumnType() Converts an abstract column type into a physical column type. CDbSchema
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. CDbSchema
quoteSimpleColumnName() Quotes a column name for use in a query. CMssqlSchema
quoteSimpleTableName() Quotes a table name for use in a query. CMssqlSchema
quoteTableName() Quotes a table name for use in a query. CDbSchema
raiseEvent() Raises an event. CComponent
refresh() Refreshes the schema. CDbSchema
renameColumn() Builds a SQL statement for renaming a column. CMssqlSchema
renameTable() Builds a SQL statement for renaming a DB table. CMssqlSchema
resetSequence() Resets the sequence value of a table's primary key. CMssqlSchema
truncateTable() Builds a SQL statement for truncating a DB table. CDbSchema

Protected Methods

Hide inherited methods

MethodDescriptionDefined By
createColumn() Creates a table column. CMssqlSchema
createCommandBuilder() Creates a command builder for the database. 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
loadTable() Loads the metadata for the specified table. CMssqlSchema
resolveTableNames() Generates various kinds of table names. CMssqlSchema

Property Details

columnTypes property (available since v1.1.6)
public array $columnTypes;

the abstract column types mapped to physical column types.

Method Details

alterColumn() method (available since v1.1.6)
public string alterColumn(string $table, string $column, string $type)
$table string the table whose column is to be changed. The table name will be properly quoted by the method.
$column string the name of the column to be changed. The name will be properly quoted by the method.
$type string the new column type. The getColumnType method will be invoked to convert abstract column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
{return} string the SQL statement for changing the definition of a column.
Source Code: framework/db/schema/mssql/CMssqlSchema.php#433 (show)
public function alterColumn($table$column$type)
{
    
$type=$this->getColumnType($type);
    
$sql='ALTER TABLE ' $this->quoteTableName($table) . ' ALTER COLUMN '
        
$this->quoteColumnName($column) . ' '
        
$this->getColumnType($type);
    return 
$sql;
}

Builds a SQL statement for changing the definition of a column.

checkIntegrity() method (available since v1.1.6)
public void checkIntegrity(boolean $check=true, string $schema='')
$check boolean whether to turn on or off the integrity check.
$schema string the schema of the tables. Defaults to empty string, meaning the current or default schema.
Source Code: framework/db/schema/mssql/CMssqlSchema.php#116 (show)
public function checkIntegrity($check=true,$schema='')
{
    
$enable=$check 'CHECK' 'NOCHECK';
    if(!isset(
$this->_normalTables[$schema]))
        
$this->_normalTables[$schema]=$this->findTableNames($schema,false);
    
$db=$this->getDbConnection();
    foreach(
$this->_normalTables[$schema] as $tableName)
    {
        
$tableName=$this->quoteTableName($tableName);
        
$db->createCommand("ALTER TABLE $tableName $enable CONSTRAINT ALL")->execute();
    }
}

Enables or disables integrity check.

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#76 (show)
public function compareTableNames($name1,$name2)
{
    
$name1=str_replace(array('[',']'),'',$name1);
    
$name2=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#331 (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->autoIncrement=$column['IsIdentity']==1;
    
$c->comment=$column['Comment']===null '' $column['Comment'];

    
$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#393 (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

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#281 (show)
protected function findColumns($table)
{
    
$columnsTable="INFORMATION_SCHEMA.COLUMNS";
    
$where=array();
    
$where[]="t1.TABLE_NAME='".$table->name."'";
    if (isset(
$table->catalogName))
    {
        
$where[]="t1.TABLE_CATALOG='".$table->catalogName."'";
        
$columnsTable $table->catalogName.'.'.$columnsTable;
    }
    if (isset(
$table->schemaName))
        
$where[]="t1.TABLE_SCHEMA='".$table->schemaName."'";

    
$sql="SELECT t1.*, columnproperty(object_id(t1.table_schema+'.'+t1.table_name), t1.column_name, 'IsIdentity') AS IsIdentity, ".
         
"CONVERT(VARCHAR, t2.value) AS Comment FROM ".$this->quoteTableName($columnsTable)." AS t1 ".
         
"LEFT OUTER JOIN sys.extended_properties AS t2 ON t1.ORDINAL_POSITION = t2.minor_id AND ".
         
"object_name(t2.major_id) = t1.TABLE_NAME AND t2.class=1 AND t2.class_desc='OBJECT_OR_COLUMN' AND t2.name='MS_Description' ".
         
"WHERE ".join(' AND ',$where);
    try
    {
        
$columns=$this->getDbConnection()->createCommand($sql)->queryAll();
        if(empty(
$columns))
            return 
false;
    }
    catch(
Exception $e)
    {
        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 (
$c->autoIncrement && $table->sequenceName===null)
            
$table->sequenceName=$table->name;
    }
    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#228 (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#187 (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
protected array findTableNames(string $schema='', boolean $includeViews=true)
$schema string the schema of the tables. Defaults to empty string, meaning the current or default schema. If not empty, the returned table names will be prefixed with the schema name.
$includeViews boolean whether to include views in the result. Defaults to true.
{return} array all table names in the database.
Source Code: framework/db/schema/mssql/CMssqlSchema.php#361 (show)
protected function findTableNames($schema='',$includeViews=true)
{
    if(
$schema==='')
        
$schema=self::DEFAULT_SCHEMA;
    if(
$includeViews)
        
$condition="TABLE_TYPE in ('BASE TABLE','VIEW')";
    else
        
$condition="TABLE_TYPE='BASE TABLE'";
    
$sql=<<<EOD
SELECT TABLE_NAME FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_SCHEMA=:schema AND 
$condition
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_NAME'];
    }

    return 
$names;
}

Returns all table names in the database.

loadTable() method
protected CMssqlTableSchema loadTable(string $name)
$name string table name
{return} CMssqlTableSchema driver dependent table metadata. Null if the table does not exist.
Source Code: framework/db/schema/mssql/CMssqlSchema.php#134 (show)
protected function loadTable($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;
}

Loads the metadata for the specified table.

quoteSimpleColumnName() method (available since v1.1.6)
public string quoteSimpleColumnName(string $name)
$name string column name
{return} string the properly quoted column name
Source Code: framework/db/schema/mssql/CMssqlSchema.php#63 (show)
public function quoteSimpleColumnName($name)
{
    return 
'['.$name.']';
}

Quotes a column name for use in a query. A simple column name does not contain prefix.

quoteSimpleTableName() method (available since v1.1.6)
public string quoteSimpleTableName(string $name)
$name string table name
{return} string the properly quoted table name
Source Code: framework/db/schema/mssql/CMssqlSchema.php#51 (show)
public function quoteSimpleTableName($name)
{
    return 
'['.$name.']';
}

Quotes a table name for use in a query. A simple table name does not schema prefix.

renameColumn() method (available since v1.1.6)
public string renameColumn(string $table, string $name, string $newName)
$table string the table whose column is to be renamed. The name will be properly quoted by the method.
$name string the old name of the column. The name will be properly quoted by the method.
$newName string the new name of the column. The name will be properly quoted by the method.
{return} string the SQL statement for renaming a DB column.
Source Code: framework/db/schema/mssql/CMssqlSchema.php#418 (show)
public function renameColumn($table$name$newName)
{
    return 
"sp_rename '$table.$name', '$newName', 'COLUMN'";
}

Builds a SQL statement for renaming a column.

renameTable() method (available since v1.1.6)
public string renameTable(string $table, string $newName)
$table string the table to be renamed. The name will be properly quoted by the method.
$newName string the new table name. The name will be properly quoted by the method.
{return} string the SQL statement for renaming a DB table.
Source Code: framework/db/schema/mssql/CMssqlSchema.php#405 (show)
public function renameTable($table$newName)
{
    return 
"sp_rename '$table', '$newName'";
}

Builds a SQL statement for renaming a DB table.

resetSequence() method (available since v1.1.6)
public void resetSequence(CDbTableSchema $table, integer|null $value=NULL)
$table CDbTableSchema the table schema whose primary key sequence will be reset
$value integer|null the value for the primary key of the next new row inserted. If this is not set, the next new row's primary key will have the max value of a primary key plus one (i.e. sequence trimming).
Source Code: framework/db/schema/mssql/CMssqlSchema.php#93 (show)
public function resetSequence($table,$value=null)
{
    if(
$table->sequenceName===null)
        return;
    if(
$value!==null)
        
$value=(int)($value)-1;
    else
        
$value=(int)$this->getDbConnection()
            ->
createCommand("SELECT MAX([{$table->primaryKey}]) FROM {$table->rawName}")
            ->
queryScalar();
    
$name=strtr($table->rawName,array('['=>'',']'=>''));
    
$this->getDbConnection()
        ->
createCommand("DBCC CHECKIDENT ('$name',RESEED,$value)")
        ->
execute();
}

Resets the sequence value of a table's primary key. The sequence will be reset such that the primary key of the next new row inserted will have the specified value or max value of a primary key plus one (i.e. sequence trimming).

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#154 (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.