Inheritance diagram for MDB2_Driver_Manager_pgsql:

Collaboration diagram for MDB2_Driver_Manager_pgsql:

Public Member Functions
	createDatabase ($name)
	create a new database
	dropDatabase ($name)
	drop an existing database
	alterTable ($name, $changes, $check)
	alter an existing table
	listDatabases ()
	list all databases
	listUsers ()
	list all users
	listViews ()
	list all views in the current database
	listTableViews ($table)
	list the views in the database that reference a given table
	listFunctions ()
	list all functions in the current database
	listTableTriggers ($table=null)
	list all triggers in the database that reference a given table
	listTables ()
	list all tables in the current database
	listTableFields ($table)
	list all fields in a table in the current database
	listTableIndexes ($table)
	list all indexes in a table
	listTableConstraints ($table)
	list all constraints in a table
	createSequence ($seq_name, $start=1)
	create sequence
	dropSequence ($seq_name)
	drop existing sequence
	listSequences ()
	list all sequences in the current database
Public Member Functions inherited from MDB2_Driver_Manager_Common
	getFieldDeclarationList ($fields)
	Get declaration of a number of field in bulk.
	_fixSequenceName ($sqn, $check=false)
	Removes any formatting in an sequence name using the 'seqname_format' option.
	_fixIndexName ($idx)
	Removes any formatting in an index name using the 'idxname_format' option.
	_getCreateTableQuery ($name, $fields, $options=array())
	Create a basic SQL query for a new table creation.
	_getTemporaryTableQuery ()
	A method to return the required SQL string that fits between CREATE ...
	createTable ($name, $fields, $options=array())
	create a new table
	dropTable ($name)
	drop an existing table
	listViews ($database=null)
	list all views in the current database
	listTables ($database=null)
	list all tables in the current database
	createIndex ($table, $name, $definition)
	Get the stucture of a field into an array.
	dropIndex ($table, $name)
	drop existing index
	createConstraint ($table, $name, $definition)
	create a constraint on a table
	dropConstraint ($table, $name, $primary=false)
	drop existing constraint
	listSequences ($database=null)
	list all sequences in the current database
Public Member Functions inherited from MDB2_Module_Common
	__construct ($db_index)
	Constructor.
	MDB2_Module_Common ($db_index)
	PHP 4 Constructor.
&	getDBInstance ()
	Get the instance of MDB2 associated with the module instance.

Additional Inherited Members
Data Fields inherited from MDB2_Module_Common
	$db_index

Detailed Description

Definition at line 56 of file pgsql.php.

Member Function Documentation

MDB2_Driver_Manager_pgsql::alterTable	(	$name,
		$changes,
		$check
	)

alter an existing table

Parameters

string	$name	name of the table that is intended to be changed.
array	$changes	associative array that contains the details of each type of change that is intended to be performed. The types of changes that are currently supported are defined as follows:

name

New name for the table.

add

Associative array with the names of fields to be added as
 indexes of the array. The value of each entry of the array
 should be set to another associative array with the properties
 of the fields to be added. The properties of the fields should
 be the same as defined by the MDB2 parser.

remove

Associative array with the names of fields to be removed as indexes
 of the array. Currently the values assigned to each entry are ignored.
 An empty array should be used for future compatibility.

rename

Associative array with the names of fields to be renamed as indexes
 of the array. The value of each entry of the array should be set to
 another associative array with the entry named name with the new
 field name and the entry named Declaration that is expected to contain
 the portion of the field declaration already in DBMS specific SQL code
 as it is used in the CREATE TABLE statement.

change

Associative array with the names of the fields to be changed as indexes
 of the array. Keep in mind that if it is intended to change either the
 name of a field and any other properties, the change array entries
 should have the new names of the fields as array indexes.

The value of each entry of the array should be set to another associative
 array with the properties of the fields to that are meant to be changed as
 array entries. These entries should be assigned to the new values of the
 respective properties. The properties of the fields should be the same
 as defined by the MDB2 parser.

Example array( 'name' => 'userlist', 'add' => array( 'quota' => array( 'type' => 'integer', 'unsigned' => 1 ) ), 'remove' => array( 'file_limit' => array(), 'time_limit' => array() ), 'change' => array( 'name' => array( 'length' => '20', 'definition' => array( 'type' => 'text', 'length' => 20, ), ) ), 'rename' => array( 'sex' => array( 'name' => 'gender', 'definition' => array( 'type' => 'text', 'length' => 1, 'default' => 'M', ), ) ) )

Parameters

boolean $check indicates whether the function should just check if the DBMS driver can perform the requested table alterations if the value is true or actually perform them otherwise. public

Returns: mixed MDB2_OK on success, a MDB2 error on failure

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 192 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), MDB2_ERROR_CANNOT_ALTER, and MDB2_OK.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        foreach ($changes as $change_name => $change) {
            switch ($change_name) {
            case 'add':
            case 'remove':
            case 'change':
            case 'name':
            case 'rename':
                break;
            default:
                return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
                    'change type "'.$change_name.'\" not yet supported', __FUNCTION__);
            }
        }
        if ($check) {
            return MDB2_OK;
        }
        if (!empty($changes['add']) && is_array($changes['add'])) {
            foreach ($changes['add'] as $field_name => $field) {
                $query = 'ADD ' . $db->getDeclaration($field['type'], $field_name, $field);
                $result = $db->exec("ALTER TABLE $name $query");
                if (PEAR::isError($result)) {
                    return $result;
                }
            }
        }
        if (!empty($changes['remove']) && is_array($changes['remove'])) {
            foreach ($changes['remove'] as $field_name => $field) {
                $field_name = $db->quoteIdentifier($field_name, true);
                $query = 'DROP ' . $field_name;
                $result = $db->exec("ALTER TABLE $name $query");
                if (PEAR::isError($result)) {
                    return $result;
                }
            }
        }
        if (!empty($changes['change']) && is_array($changes['change'])) {
            foreach ($changes['change'] as $field_name => $field) {
                $field_name = $db->quoteIdentifier($field_name, true);
                if (!empty($field['type'])) {
                    $server_info = $db->getServerVersion();
                    if (PEAR::isError($server_info)) {
                        return $server_info;
                    }
                    if (is_array($server_info) && $server_info['major'] < 8) {
                        return $db->raiseError(MDB2_ERROR_CANNOT_ALTER, null, null,
                            'changing column type for "'.$change_name.'\" requires PostgreSQL 8.0 or above', __FUNCTION__);
                    }
                    $db->loadModule('Datatype', null, true);
                    $query = "ALTER $field_name TYPE ".$db->datatype->getTypeDeclaration($field['definition']);
                    $result = $db->exec("ALTER TABLE $name $query");
                    if (PEAR::isError($result)) {
                        return $result;
                    }
                }
                if (array_key_exists('default', $field)) {
                    $query = "ALTER $field_name SET DEFAULT ".$db->quote($field['definition']['default'], $field['definition']['type']);
                    $result = $db->exec("ALTER TABLE $name $query");
                    if (PEAR::isError($result)) {
                        return $result;
                    }
                }
                if (!empty($field['notnull'])) {
                    $query = "ALTER $field_name ".($field['definition']['notnull'] ? "SET" : "DROP").' NOT NULL';
                    $result = $db->exec("ALTER TABLE $name $query");
                    if (PEAR::isError($result)) {
                        return $result;
                    }
                }
            }
        }
        if (!empty($changes['rename']) && is_array($changes['rename'])) {
            foreach ($changes['rename'] as $field_name => $field) {
                $field_name = $db->quoteIdentifier($field_name, true);
                $result = $db->exec("ALTER TABLE $name RENAME COLUMN $field_name TO ".$db->quoteIdentifier($field['name'], true));
                if (PEAR::isError($result)) {
                    return $result;
                }
            }
        }
        $name = $db->quoteIdentifier($name, true);
        if (!empty($changes['name'])) {
            $change_name = $db->quoteIdentifier($changes['name'], true);
            $result = $db->exec("ALTER TABLE $name RENAME TO ".$change_name);
            if (PEAR::isError($result)) {
                return $result;
            }
        }
        return MDB2_OK;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::createDatabase ( $name )

create a new database

Parameters

string $name name of the database that should be created

Returns: mixed MDB2_OK on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 67 of file pgsql.php.

References MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $name = $db->quoteIdentifier($name, true);
        return $db->standaloneQuery("CREATE DATABASE $name", null, true);
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::createSequence	(	$seq_name,
		$start = `1`
	)

create sequence

Parameters

string	$seq_name	name of the sequence to be created
string	$start	start value of the sequence; default is 1

Returns: mixed MDB2_OK on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 658 of file pgsql.php.

References MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
        return $db->exec("CREATE SEQUENCE $sequence_name INCREMENT 1".
            ($start < 1 ? " MINVALUE $start" : '')." START $start");
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::dropDatabase ( $name )

drop an existing database

Parameters

string $name name of the database that should be dropped

Returns: mixed MDB2_OK on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 88 of file pgsql.php.

References MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $name = $db->quoteIdentifier($name, true);
        return $db->standaloneQuery("DROP DATABASE $name", null, true);
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::dropSequence ( $seq_name )

drop existing sequence

Parameters

string $seq_name name of the sequence to be dropped

Returns: mixed MDB2_OK on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 680 of file pgsql.php.

References MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
        return $db->exec("DROP SEQUENCE $sequence_name");
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listDatabases ( )

list all databases

Returns: mixed array of database names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 305 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), MDB2\isResultCommon(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = 'SELECT datname FROM pg_database';
        $result2 = $db->standaloneQuery($query, array('text'), false);
        if (!MDB2::isResultCommon($result2)) {
            return $result2;
        }
        $result = $result2->fetchCol();
        $result2->free();
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listFunctions ( )

list all functions in the current database

Returns: mixed array of function names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 424 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = "
            SELECT
                proname
            FROM
                pg_proc pr,
                pg_type tp
            WHERE
                tp.oid = pr.prorettype
                AND pr.proisagg = FALSE
                AND tp.typname <> 'trigger'
                AND pr.pronamespace IN
                    (SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
        $result = $db->queryCol($query);
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listSequences ( )

list all sequences in the current database

Returns: mixed array of sequence names on success, a MDB2 error on failure public

Definition at line 700 of file pgsql.php.

References $query, $result, MDB2_Driver_Manager_Common\_fixSequenceName(), MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = "SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN";
        $query.= "(SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema')";
        $table_names = $db->queryCol($query);
        if (PEAR::isError($table_names)) {
            return $table_names;
        }
        $result = array();
        foreach ($table_names as $table_name) {
            $result[] = $this->_fixSequenceName($table_name);
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listTableConstraints ( $table )

list all constraints in a table

Parameters

string $table name of table that should be used in method

Returns: mixed array of constraint names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 615 of file pgsql.php.

References $query, $result, MDB2_Driver_Manager_Common\_fixIndexName(), MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $table = $db->quote($table, 'text');
        $subquery = "SELECT indexrelid FROM pg_index, pg_class";
        $subquery.= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND (indisunique = 't' OR indisprimary = 't')";
        $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
        $constraints = $db->queryCol($query);
        if (PEAR::isError($constraints)) {
            return $constraints;
        }
        $result = array();
        foreach ($constraints as $constraint) {
            $constraint = $this->_fixIndexName($constraint);
            if (!empty($constraint)) {
                $result[$constraint] = true;
            }
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE
            && $db->options['field_case'] == CASE_LOWER
        ) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }
        return array_keys($result);
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listTableFields ( $table )

list all fields in a table in the current database

Parameters

string $table name of table that should be used in method

Returns: mixed array of field names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 544 of file pgsql.php.

References $result, MDB2_Module_Common\getDBInstance(), and PEAR\isError().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $table = $db->quoteIdentifier($table, true);
        $db->setLimit(1);
        $result2 = $db->query("SELECT * FROM $table");
        if (PEAR::isError($result2)) {
            return $result2;
        }
        $result = $result2->getColumnNames();
        $result2->free();
        if (PEAR::isError($result)) {
            return $result;
        }
        return array_flip($result);
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listTableIndexes ( $table )

list all indexes in a table

Parameters

string $table name of table that should be used in method

Returns: mixed array of index names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 575 of file pgsql.php.

References $query, $result, MDB2_Driver_Manager_Common\_fixIndexName(), MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $table = $db->quote($table, 'text');
        $subquery = "SELECT indexrelid FROM pg_index, pg_class";
        $subquery.= " WHERE pg_class.relname=$table AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't'";
        $query = "SELECT relname FROM pg_class WHERE oid IN ($subquery)";
        $indexes = $db->queryCol($query, 'text');
        if (PEAR::isError($indexes)) {
            return $indexes;
        }
        $result = array();
        foreach ($indexes as $index) {
            $index = $this->_fixIndexName($index);
            if (!empty($index)) {
                $result[$index] = true;
            }
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }
        return array_keys($result);
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listTables ( )

list all tables in the current database

Returns: mixed array of table names on success, a MDB2 error on failure public

Definition at line 497 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
        $query = 'SELECT c.relname AS "Name"'
            . ' FROM pg_class c, pg_user u'
            . ' WHERE c.relowner = u.usesysid'
            . " AND c.relkind = 'r'"
            . ' AND NOT EXISTS'
            . ' (SELECT 1 FROM pg_views'
            . '  WHERE viewname = c.relname)'
            . " AND c.relname !~ '^(pg_|sql_)'"
            . ' UNION'
            . ' SELECT c.relname AS "Name"'
            . ' FROM pg_class c'
            . " WHERE c.relkind = 'r'"
            . ' AND NOT EXISTS'
            . ' (SELECT 1 FROM pg_views'
            . '  WHERE viewname = c.relname)'
            . ' AND NOT EXISTS'
            . ' (SELECT 1 FROM pg_user'
            . '  WHERE usesysid = c.relowner)'
            . " AND c.relname !~ '^pg_'";
        $result = $db->queryCol($query);
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listTableTriggers ( $table = null )

list all triggers in the database that reference a given table

Parameters

string table for which all referenced triggers should be found

Returns: mixed array of trigger names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 463 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = 'SELECT trg.tgname AS trigger_name
                    FROM pg_trigger trg,
                         pg_class tbl
                   WHERE trg.tgrelid = tbl.oid';
        if (!is_null($table)) {
            $table = $db->quote(strtoupper($table), 'text');
            $query .= " AND tbl.relname = $table";
        }
        $result = $db->queryCol($query);
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listTableViews ( $table )

list the views in the database that reference a given table

Parameters

string table for which all referenced views should be found

Returns: mixed array of view names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 396 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = 'SELECT viewname FROM pg_views NATURAL JOIN pg_tables';
        $query.= ' WHERE tablename ='.$db->quote($table, 'text');
        $result = $db->queryCol($query);
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listUsers ( )

list all users

Returns: mixed array of user names on success, a MDB2 error on failure public

Reimplemented from MDB2_Driver_Manager_Common.

Definition at line 338 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2\isResultCommon().

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = 'SELECT usename FROM pg_user';
        $result2 = $db->standaloneQuery($query, array('text'), false);
        if (!MDB2::isResultCommon($result2)) {
            return $result2;
        }
        $result = $result2->fetchCol();
        $result2->free();
        return $result;
    }

Here is the call graph for this function:

MDB2_Driver_Manager_pgsql::listViews ( )

list all views in the current database

Returns: mixed array of view names on success, a MDB2 error on failure public

Definition at line 365 of file pgsql.php.

References $query, $result, MDB2_Module_Common\getDBInstance(), PEAR\isError(), and MDB2_PORTABILITY_FIX_CASE.

    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        $query = "SELECT viewname
                    FROM pg_views
                   WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
                     AND viewname !~ '^pg_'";
        $result = $db->queryCol($query);
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

Here is the call graph for this function:

The documentation for this class was generated from the following file:

Services/PEAR/lib/MDB2/Driver/Manager/pgsql.php

Public Member Functions

Additional Inherited Members

Detailed Description

Member Function Documentation