0 follower

COciSchema

Package	system.db.schema.oci
Inheritance	class COciSchema » CDbSchema » CComponent
Since	1.0.5
Version	$Id$
Source Code	framework/db/schema/oci/COciSchema.php

COciSchema is the class for retrieving metadata information from an Oracle 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
defaultSchema	string	default schema.	COciSchema
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.	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
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
getDefaultSchema()	Returns default schema.	COciSchema
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.	COciSchema
quoteTableName()	Quotes a table name for use in a query.	COciSchema
raiseEvent()	Raises an event.	CComponent
refresh()	Refreshes the schema.	CDbSchema
setDefaultSchema()	Sets default schema.	COciSchema

Protected Methods

Hide inherited methods

Method	Description	Defined By
createColumn()	Creates a table column.	COciSchema
createCommandBuilder()	Creates a command builder for the database.	COciSchema
createTable()	Creates a table instance representing the metadata for the named table.	COciSchema
findColumns()	Collects the table column metadata.	COciSchema
findConstraints()	Collects the primary and foreign key column details for the given table.	COciSchema
findTableNames()	Returns all table names in the database.	COciSchema
getSchemaTableName()	Returns tuple as ($schemaName,$tableName)	COciSchema
resolveTableNames()	Generates various kinds of table names.	COciSchema

Property Details

defaultSchema property

public string getDefaultSchema()
public void setDefaultSchema(string $schema)

default schema.

Method Details

createColumn() method

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

Source Code: framework/db/schema/oci/COciSchema.php#197 (show)


protected function createColumn($column)
{
    $c=new COciColumnSchema;
    $c->name=$column['COLUMN_NAME'];
    $c->rawName=$this->quoteColumnName($c->name);
    $c->allowNull=$column['NULLABLE']==='Y';
    $c->isPrimaryKey=strpos($column['KEY'],'P')!==false;
    $c->isForeignKey=false;
    $c->init($column['DATA_TYPE'],$column['DATA_DEFAULT']);

    return $c;
}

Creates a table column.

createCommandBuilder() method

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

Source Code: framework/db/schema/oci/COciSchema.php#49 (show)


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

Creates a command builder for the database. This method may be overridden by child classes to create a DBMS-specific command builder.

createTable() method

protected CDbTableSchema createTable($name)
$name
{return}	CDbTableSchema	driver dependent table metadata.

Source Code: framework/db/schema/oci/COciSchema.php#92 (show)


protected function createTable($name)
{
    $table=new COciTableSchema;
    $this->resolveTableNames($table,$name);

    if(!$this->findColumns($table))
        return null;
    $this->findConstraints($table);

    return $table;
}

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

findColumns() method

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

Source Code: framework/db/schema/oci/COciSchema.php#136 (show)


protected function findColumns($table)
{
    list($schemaName,$tableName) = $this->getSchemaTableName($table->name);

    $sql=<<<EOD
SELECT a.column_name, a.data_type ||
case
    when data_precision is not null
        then '(' || a.data_precision ||
                case when a.data_scale > 0 then ',' || a.data_scale else '' end
            || ')'
    when data_type = 'DATE' then ''
    else '(' || to_char(a.data_length) || ')'
end as data_type,
a.nullable, a.data_default,
(   SELECT D.constraint_type
    FROM ALL_CONS_COLUMNS C
    inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
    WHERE C.OWNER = B.OWNER
       and C.table_name = B.object_name
       and C.column_name = A.column_name
       and D.constraint_type = 'P') as Key
FROM ALL_TAB_COLUMNS A
inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)
WHERE
a.owner = '{$schemaName}'
and (b.object_type = 'TABLE' or b.object_type = 'VIEW')
and b.object_name = '{$tableName}'
ORDER by a.column_id
EOD;

    $command=$this->getDbConnection()->createCommand($sql);

    if(($columns=$command->queryAll())===array()){
        return false;
    }

    foreach($columns as $column)
    {
        $c=$this->createColumn($column);

        $table->columns[$c->name]=$c;
        if($c->isPrimaryKey)
        {
            if($table->primaryKey===null)
                $table->primaryKey=$c->name;
            else if(is_string($table->primaryKey))
                $table->primaryKey=array($table->primaryKey,$c->name);
            else
                $table->primaryKey[]=$c->name;
            $table->sequenceName='';
        }
    }
    return true;
}

Collects the table column metadata.

findConstraints() method

protected void findConstraints(COciTableSchema $table)
$table	COciTableSchema	the table metadata

Source Code: framework/db/schema/oci/COciSchema.php#214 (show)


protected function findConstraints($table)
{
    $sql=<<<EOD
    SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
            E.table_name as table_ref, f.column_name as column_ref
    FROM ALL_CONS_COLUMNS C
    inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
    left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
    left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
    WHERE C.OWNER = '{$table->schemaName}'
       and C.table_name = '{$table->name}'
       and D.constraint_type <> 'P'
    order by d.constraint_name, c.position
EOD;
    $command=$this->getDbConnection()->createCommand($sql);
    foreach($command->queryAll() as $row)
    {
        if($row['CONSTRAINT_TYPE']==='R')   // foreign key
        {
            $name = $row["COLUMN_NAME"];
            $table->foreignKeys[$name]=array($row["TABLE_REF"], $row["COLUMN_REF"]);
            if(isset($table->columns[$name]))
                $table->columns[$name]->isForeignKey=true;
        }

    }
}

Collects the primary and foreign key column details for the given table.

findTableNames() method

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

Source Code: framework/db/schema/oci/COciSchema.php#247 (show)


protected function findTableNames($schema='')
{
    if($schema==='')
    {
        $sql=<<<EOD
SELECT table_name, '{$schema}' as table_schema FROM user_tables
EOD;
        $command=$this->getDbConnection()->createCommand($sql);
    }
    else
    {
        $sql=<<<EOD
SELECT object_name as table_name, owner as table_schema FROM all_objects
WHERE object_type = 'TABLE' AND owner=:schema
EOD;
        $command=$this->getDbConnection()->createCommand($sql);
        $command->bindParam(':schema',$schema);
    }

    $rows=$command->queryAll();
    $names=array();
    foreach($rows as $row)
    {
        if($schema===$this->getDefaultSchema())
            $names[]=$row['TABLE_NAME'];
        else
            $names[]=$row['SCHEMA_NAME'].'.'.$row['TABLE_NAME'];
    }
    return $names;
}

Returns all table names in the database.

getDefaultSchema() method

public string getDefaultSchema()
{return}	string	default schema.

Source Code: framework/db/schema/oci/COciSchema.php#65 (show)


public function getDefaultSchema()
{
    if (!strlen($this->_defaultSchema))
    {
        $this->setDefaultSchema(strtoupper($this->getDbConnection()->username));
    }

    return $this->_defaultSchema;
}

getSchemaTableName() method

protected array getSchemaTableName(string $table)
$table	string	table name with optional schema name prefix, uses default schema name prefix is not provided.
{return}	array	tuple as ($schemaName,$tableName)

Source Code: framework/db/schema/oci/COciSchema.php#79 (show)


protected function getSchemaTableName($table)
{
    $table = strtoupper($table);
    if(count($parts= explode('.', str_replace('"','',$table))) > 1)
        return array($parts[0], $parts[1]);
    else
        return array($this->getDefaultSchema(),$parts[0]);
}

quoteColumnName() method

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

Source Code: framework/db/schema/oci/COciSchema.php#39 (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/oci/COciSchema.php#29 (show)


public function quoteTableName($name)
{
    return $name;
}

Quotes a table name for use in a query.

resolveTableNames() method

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

Source Code: framework/db/schema/oci/COciSchema.php#109 (show)


protected function resolveTableNames($table,$name)
{
    $parts=explode('.',str_replace('"','',$name));
    if(isset($parts[1]))
    {
        $schemaName=$parts[0];
        $tableName=$parts[1];
    }
    else
    {
        $schemaName=$this->getDefaultSchema();
        $tableName=$parts[0];
    }

    $table->name=$tableName;
    $table->schemaName=$schemaName;
    if($schemaName===$this->getDefaultSchema())
        $table->rawName=$this->quoteTableName($tableName);
    else
        $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName);
}

Generates various kinds of table names.

setDefaultSchema() method

public void setDefaultSchema(string $schema)
$schema	string	default schema.

Source Code: framework/db/schema/oci/COciSchema.php#57 (show)


public function setDefaultSchema($schema)
{
    $this->_defaultSchema=$schema;
}