0 follower

CPgsqlSchema

Package system.db.schema.pgsql
Inheritance class CPgsqlSchema » CDbSchema » CComponent
Since 1.0
Version $Id$
Source Code framework/db/schema/pgsql/CPgsqlSchema.php
CPgsqlSchema is the class for retrieving metadata information from a PostgreSQL database.

Public Properties

Hide inherited properties

PropertyTypeDescriptionDefined 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

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
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
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
quoteTableName() Quotes a table name for use in a query. CPgsqlSchema
raiseEvent() Raises an event. CComponent
refresh() Refreshes the schema. CDbSchema

Protected Methods

Hide inherited methods

MethodDescriptionDefined By
createColumn() Creates a table column. CPgsqlSchema
createCommandBuilder() Creates a command builder for the database. CDbSchema
createTable() Creates a table instance representing the metadata for the named table. CPgsqlSchema
findColumns() Collects the table column metadata. CPgsqlSchema
findConstraints() Collects the primary and foreign key column details for the given table. CPgsqlSchema
findForeignKey() Collects foreign key information. CPgsqlSchema
findPrimaryKey() Collects primary key information. CPgsqlSchema
findTableNames() Returns all table names in the database. CPgsqlSchema
resolveTableNames() Generates various kinds of table names. CPgsqlSchema

Method Details

createColumn() method
protected CDbColumnSchema createColumn(array $column)
$column array column metadata
{return} CDbColumnSchema normalized column metadata
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#133 (show)
protected function createColumn($column)
{
    $c=new CPgsqlColumnSchema;
    $c->name=$column['attname'];
    $c->rawName=$this->quoteColumnName($c->name);
    $c->allowNull=!$column['attnotnull'];
    $c->isPrimaryKey=false;
    $c->isForeignKey=false;

    $c->init($column['type'],$column['atthasdef'] ? $column['adsrc'] : null);

    return $c;
}

Creates a table column.

createTable() method
protected CDbTableSchema createTable($name)
$name
{return} CDbTableSchema driver dependent table metadata.
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#38 (show)
protected function createTable($name)
{
    $table=new CPgsqlTableSchema;
    $this->resolveTableNames($table,$name);
    if(!$this->findColumns($table))
        return null;
    $this->findConstraints($table);

    if(is_string($table->primaryKey) && isset($this->_sequences[$table->primaryKey]))
        $table->sequenceName=$this->_sequences[$table->primaryKey];
    else if(is_array($table->primaryKey))
    {
        foreach($table->primaryKey as $pk)
        {
            if(isset($this->_sequences[$pk]))
            {
                $table->sequenceName=$this->_sequences[$pk];
                break;
            }
        }
    }

    return $table;
}

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

findColumns() method
protected boolean findColumns(CPgsqlTableSchema $table)
$table CPgsqlTableSchema the table metadata
{return} boolean whether the table exists in the database
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#95 (show)
protected function findColumns($table)
{
    $sql=<<<EOD
SELECT a.attname, LOWER(format_type(a.atttypid, a.atttypmod)) AS type, d.adsrc, a.attnotnull, a.atthasdef
FROM pg_attribute a LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
WHERE a.attnum > 0 AND NOT a.attisdropped
AND a.attrelid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
    AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace WHERE nspname = :schema))
ORDER BY a.attnum
EOD;
    $command=$this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table',$table->name);
    $command->bindValue(':schema',$table->schemaName);

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

    foreach($columns as $column)
    {
        $c=$this->createColumn($column);
        $table->columns[$c->name]=$c;

        if(stripos($column['adsrc'],'nextval')===0 && preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$column['adsrc'],$matches))
        {
            if(strpos($matches[1],'.')!==false || $table->schemaName===self::DEFAULT_SCHEMA)
                $this->_sequences[$c->name]=$matches[1];
            else
                $this->_sequences[$c->name]=$table->schemaName.'.'.$matches[1];
        }
    }
    return true;
}

Collects the table column metadata.

findConstraints() method
protected void findConstraints(CPgsqlTableSchema $table)
$table CPgsqlTableSchema the table metadata
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#151 (show)
protected function findConstraints($table)
{
    $sql=<<<EOD
SELECT conname, consrc, contype, indkey FROM (
SELECT
    conname,
    CASE WHEN contype='f' THEN
        pg_catalog.pg_get_constraintdef(oid)
    ELSE
        'CHECK (' || consrc || ')'
    END AS consrc,
    contype,
    conrelid AS relid,
    NULL AS indkey
FROM
    pg_catalog.pg_constraint
WHERE
    contype IN ('f', 'c')
UNION ALL
SELECT
    pc.relname,
    NULL,
    CASE WHEN indisprimary THEN
            'p'
    ELSE
            'u'
    END,
    pi.indrelid,
    indkey
FROM
    pg_catalog.pg_class pc,
    pg_catalog.pg_index pi
WHERE
    pc.oid=pi.indexrelid
    AND EXISTS (
        SELECT 1 FROM pg_catalog.pg_depend d JOIN pg_catalog.pg_constraint c
        ON (d.refclassid = c.tableoid AND d.refobjid = c.oid)
        WHERE d.classid = pc.tableoid AND d.objid = pc.oid AND d.deptype = 'i' AND c.contype IN ('u', 'p')
)
) AS sub
WHERE relid = (SELECT oid FROM pg_catalog.pg_class WHERE relname=:table
AND relnamespace = (SELECT oid FROM pg_catalog.pg_namespace
WHERE nspname=:schema))
EOD;
    $command=$this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table',$table->name);
    $command->bindValue(':schema',$table->schemaName);
    foreach($command->queryAll() as $row)
    {
        if($row['contype']==='p') // primary key
            $this->findPrimaryKey($table,$row['indkey']);
        else if($row['contype']==='f') // foreign key
            $this->findForeignKey($table,$row['consrc']);
    }
}

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

findForeignKey() method
protected void findForeignKey(CPgsqlTableSchema $table, string $src)
$table CPgsqlTableSchema the table metadata
$src string pgsql foreign key definition
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#248 (show)
protected function findForeignKey($table,$src)
{
    $matches=array();
    $brackets='\(([^\)]+)\)';
    $pattern="/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i";
    if(preg_match($pattern,str_replace('"','',$src),$matches))
    {
        $keys=preg_split('/,\s+/', $matches[1]);
        $tableName=$matches[2];
        $fkeys=preg_split('/,\s+/', $matches[3]);
        foreach($keys as $i=>$key)
        {
            $table->foreignKeys[$key]=array($tableName,$fkeys[$i]);
            if(isset($table->columns[$key]))
                $table->columns[$key]->isForeignKey=true;
        }
    }
}

Collects foreign key information.

findPrimaryKey() method
protected void findPrimaryKey(CPgsqlTableSchema $table, string $indices)
$table CPgsqlTableSchema the table metadata
$indices string pgsql primary key index list
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#212 (show)
protected function findPrimaryKey($table,$indices)
{
    $indices=implode(', ',preg_split('/\s+/',$indices));
    $sql=<<<EOD
SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE
attrelid=(
    SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=(
        SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema
    )
)
AND attnum IN ({$indices})
EOD;
    $command=$this->getDbConnection()->createCommand($sql);
    $command->bindValue(':table',$table->name);
    $command->bindValue(':schema',$table->schemaName);
    foreach($command->queryAll() as $row)
    {
        $name=$row['attname'];
        if(isset($table->columns[$name]))
        {
            $table->columns[$name]->isPrimaryKey=true;
            if($table->primaryKey===null)
                $table->primaryKey=$name;
            else if(is_string($table->primaryKey))
                $table->primaryKey=array($table->primaryKey,$name);
            else
                $table->primaryKey[]=$name;
        }
    }
}

Collects primary key information.

findTableNames() method (available since v1.0.2)
protected array findTableNames($schema='')
$schema
{return} array all table names in the database.
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#272 (show)
protected function findTableNames($schema='')
{
    if($schema==='')
        $schema=self::DEFAULT_SCHEMA;
    $sql=<<<EOD
SELECT table_name, table_schema FROM information_schema.tables
WHERE 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[]=$row['table_schema'].'.'.$row['table_name'];
    }
    return $names;
}

Returns all table names in the database.

quoteTableName() method
public string quoteTableName(string $name)
$name string table name
{return} string the properly quoted table name
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#29 (show)
public function quoteTableName($name)
{
    return '"'.$name.'"';
}

Quotes a table name for use in a query.

resolveTableNames() method
protected void resolveTableNames(CPgsqlTableSchema $table, string $name)
$table CPgsqlTableSchema the table instance
$name string the unquoted table name
Source Code: framework/db/schema/pgsql/CPgsqlSchema.php#68 (show)
protected function resolveTableNames($table,$name)
{
    $parts=explode('.',str_replace('"','',$name));
    if(isset($parts[1]))
    {
        $schemaName=$parts[0];
        $tableName=$parts[1];
    }
    else
    {
        $schemaName=self::DEFAULT_SCHEMA;
        $tableName=$parts[0];
    }

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

Generates various kinds of table names.