PostgresqlMetadata.php

<?php
/**
 * Zend Framework (http://framework.zend.com/)
 *
 * @link      http://github.com/zendframework/zf2 for the canonical source repository
 * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
 * @license   http://framework.zend.com/license/new-bsd New BSD License
 * @package   Zend_Db
 */

namespace Zend\Db\Metadata\Source;

use Zend\Db\Adapter\Adapter;

/**
 * @category   Zend
 * @package    Zend_Db
 * @subpackage Metadata
 */
class PostgresqlMetadata extends AbstractSource
{

    protected function loadSchemaData()
    {
        if (isset($this->data['schemas'])) {
            return;
        }
        $this->prepareDataHierarchy('schemas');

        $p = $this->adapter->getPlatform();

        $sql = 'SELECT ' . $p->quoteIdentifier('schema_name')
            . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'schemata'))
            . ' WHERE ' . $p->quoteIdentifier('schema_name')
            . ' != ' . $p->quoteValue('information_schema')
            . ' AND ' . $p->quoteIdentifier('schema_name') . " NOT LIKE 'pg_%'";

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);

        $schemas = array();
        foreach ($results->toArray() as $row) {
            $schemas[] = $row['schema_name'];
        }

        $this->data['schemas'] = $schemas;
    }

    protected function loadTableNameData($schema)
    {
        if (isset($this->data['table_names'][$schema])) {
            return;
        }
        $this->prepareDataHierarchy('table_names', $schema);

        $p = $this->adapter->getPlatform();

        $isColumns = array(
            array('t','table_name'),
            array('t','table_type'),
            array('v','view_definition'),
            array('v','check_option'),
            array('v','is_updatable'),
        );

        array_walk($isColumns, function (&$c) use ($p) { $c = $p->quoteIdentifierChain($c); });

        $sql = 'SELECT ' . implode(', ', $isColumns)
            . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'tables')) . ' t'

            . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'views')) . ' v'
            . ' ON ' . $p->quoteIdentifierChain(array('t','table_schema'))
            . '  = ' . $p->quoteIdentifierChain(array('v','table_schema'))
            . ' AND ' . $p->quoteIdentifierChain(array('t','table_name'))
            . '  = ' . $p->quoteIdentifierChain(array('v','table_name'))

            . ' WHERE ' . $p->quoteIdentifierChain(array('t','table_type'))
            . ' IN (' . $p->quoteValueList(array('BASE TABLE', 'VIEW')) . ')';

        if ($schema != self::DEFAULT_SCHEMA) {
            $sql .= ' AND ' . $p->quoteIdentifierChain(array('t','table_schema'))
                . ' = ' . $p->quoteValue($schema);
        } else {
            $sql .= ' AND ' . $p->quoteIdentifierChain(array('t','table_schema'))
                . ' != ' . $p->quoteValue('information_schema');
        }

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);

        $tables = array();
        foreach ($results->toArray() as $row) {
            $tables[$row['table_name']] = array(
                'table_type' => $row['table_type'],
                'view_definition' => $row['view_definition'],
                'check_option' => $row['check_option'],
                'is_updatable' => ('YES' == $row['is_updatable']),
            );
        }

        $this->data['table_names'][$schema] = $tables;
    }

    protected function loadColumnData($table, $schema)
    {
        if (isset($this->data['columns'][$schema][$table])) {
            return;
        }

        $this->prepareDataHierarchy('columns', $schema, $table);

        $platform = $this->adapter->getPlatform();

        $isColumns = array(
            'table_name',
            'column_name',
            'ordinal_position',
            'column_default',
            'is_nullable',
            'data_type',
            'character_maximum_length',
            'character_octet_length',
            'numeric_precision',
            'numeric_scale',
        );

        array_walk($isColumns, function (&$c) use ($platform) { $c = $platform->quoteIdentifier($c); });

        $sql = 'SELECT ' . implode(', ', $isColumns)
            . ' FROM ' . $platform->quoteIdentifier('information_schema')
            . $platform->getIdentifierSeparator() . $platform->quoteIdentifier('columns')
            . ' WHERE ' . $platform->quoteIdentifier('table_schema')
            . ' != ' . $platform->quoteValue('information')
            . ' AND ' . $platform->quoteIdentifier('table_name')
            . ' = ' . $platform->quoteValue($table);

        if ($schema != '__DEFAULT_SCHEMA__') {
            $sql .= ' AND ' . $platform->quoteIdentifier('table_schema')
                . ' = ' . $platform->quoteValue($schema);
        }

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
        $columns = array();
        foreach ($results->toArray() as $row) {
            $columns[$row['column_name']] = array(
                'ordinal_position'          => $row['ordinal_position'],
                'column_default'            => $row['column_default'],
                'is_nullable'               => ('YES' == $row['is_nullable']),
                'data_type'                 => $row['data_type'],
                'character_maximum_length'  => $row['character_maximum_length'],
                'character_octet_length'    => $row['character_octet_length'],
                'numeric_precision'         => $row['numeric_precision'],
                'numeric_scale'             => $row['numeric_scale'],
                'numeric_unsigned'          => null,
                'erratas'                   => array(),
            );
        }

        $this->data['columns'][$schema][$table] = $columns;
    }

    protected function loadConstraintData($table, $schema)
    {
        if (isset($this->data['constraints'][$schema][$table])) {
            return;
        }

        $this->prepareDataHierarchy('constraints', $schema, $table);

        $isColumns = array(
            array('t','table_name'),
            array('tc','constraint_name'),
            array('tc','constraint_type'),
            array('kcu','column_name'),
            array('cc','check_clause'),
            array('rc','match_option'),
            array('rc','update_rule'),
            array('rc','delete_rule'),
            array('referenced_table_schema' => 'kcu2','table_schema'),
            array('referenced_table_name' => 'kcu2','table_name'),
            array('referenced_column_name' => 'kcu2','column_name'),
        );

        $p = $this->adapter->getPlatform();

        array_walk($isColumns, function (&$c) use ($p) {
            $alias = key($c);
            $c = $p->quoteIdentifierChain($c);
            if (is_string($alias)) {
                $c .= ' ' . $p->quoteIdentifier($alias);
            }
        });

        $sql = 'SELECT ' . implode(', ', $isColumns)
             . ' FROM ' . $p->quoteIdentifierChain(array('information_schema','tables')) . ' t'

             . ' INNER JOIN ' . $p->quoteIdentifierChain(array('information_schema','table_constraints')) . ' tc'
             . ' ON ' . $p->quoteIdentifierChain(array('t','table_schema'))
             . '  = ' . $p->quoteIdentifierChain(array('tc','table_schema'))
             . ' AND ' . $p->quoteIdentifierChain(array('t','table_name'))
             . '  = ' . $p->quoteIdentifierChain(array('tc','table_name'))

             . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema','key_column_usage')) . ' kcu'
             . ' ON ' . $p->quoteIdentifierChain(array('tc','table_schema'))
             . '  = ' . $p->quoteIdentifierChain(array('kcu','table_schema'))
             . ' AND ' . $p->quoteIdentifierChain(array('tc','table_name'))
             . '  = ' . $p->quoteIdentifierChain(array('kcu','table_name'))
             . ' AND ' . $p->quoteIdentifierChain(array('tc','constraint_name'))
             . '  = ' . $p->quoteIdentifierChain(array('kcu','constraint_name'))

             . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema','check_constraints')) . ' cc'
             . ' ON ' . $p->quoteIdentifierChain(array('tc','constraint_schema'))
             . '  = ' . $p->quoteIdentifierChain(array('cc','constraint_schema'))
             . ' AND ' . $p->quoteIdentifierChain(array('tc','constraint_name'))
             . '  = ' . $p->quoteIdentifierChain(array('cc','constraint_name'))

             . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema','referential_constraints')) . ' rc'
             . ' ON ' . $p->quoteIdentifierChain(array('tc','constraint_schema'))
             . '  = ' . $p->quoteIdentifierChain(array('rc','constraint_schema'))
             . ' AND ' . $p->quoteIdentifierChain(array('tc','constraint_name'))
             . '  = ' . $p->quoteIdentifierChain(array('rc','constraint_name'))

             . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema','key_column_usage')) . ' kcu2'
             . ' ON ' . $p->quoteIdentifierChain(array('rc','unique_constraint_schema'))
             . '  = ' . $p->quoteIdentifierChain(array('kcu2','constraint_schema'))
             . ' AND ' . $p->quoteIdentifierChain(array('rc','unique_constraint_name'))
             . '  = ' . $p->quoteIdentifierChain(array('kcu2','constraint_name'))
             . ' AND ' . $p->quoteIdentifierChain(array('kcu','position_in_unique_constraint'))
             . '  = ' . $p->quoteIdentifierChain(array('kcu2','ordinal_position'))

             . ' WHERE ' . $p->quoteIdentifierChain(array('t','table_name'))
             . ' = ' . $p->quoteValue($table)
             . ' AND ' . $p->quoteIdentifierChain(array('t','table_type'))
             . ' IN (' . $p->quoteValueList(array('BASE TABLE', 'VIEW')) . ')';

        if ($schema != self::DEFAULT_SCHEMA) {
            $sql .= ' AND ' . $p->quoteIdentifierChain(array('t','table_schema'))
            . ' = ' . $p->quoteValue($schema);
        } else {
            $sql .= ' AND ' . $p->quoteIdentifierChain(array('t','table_schema'))
            . ' != ' . $p->quoteValue('information_schema');
        }

        $sql .= ' ORDER BY CASE ' . $p->quoteIdentifierChain(array('tc','constraint_type'))
              . " WHEN 'PRIMARY KEY' THEN 1"
              . " WHEN 'UNIQUE' THEN 2"
              . " WHEN 'FOREIGN KEY' THEN 3"
              . " WHEN 'CHECK' THEN 4"
              . " ELSE 5 END"
              . ', ' . $p->quoteIdentifierChain(array('tc','constraint_name'))
              . ', ' . $p->quoteIdentifierChain(array('kcu','ordinal_position'));

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);

        $name = null;
        $constraints = array();
        foreach ($results->toArray() as $row) {
            if ($row['constraint_name'] !== $name) {
                $name = $row['constraint_name'];
                $constraints[$name] = array(
                    'constraint_name' => $name,
                    'constraint_type' => $row['constraint_type'],
                    'table_name'      => $row['table_name'],
                );
                if ('CHECK' == $row['constraint_type']) {
                    $constraints[$name]['check_clause'] = $row['check_clause'];
                    continue;
                }
                $constraints[$name]['columns'] = array();
                $isFK = ('FOREIGN KEY' == $row['constraint_type']);
                if ($isFK) {
                    $constraints[$name]['referenced_table_schema'] = $row['referenced_table_schema'];
                    $constraints[$name]['referenced_table_name']   = $row['referenced_table_name'];
                    $constraints[$name]['referenced_columns']      = array();
                    $constraints[$name]['match_option']       = $row['match_option'];
                    $constraints[$name]['update_rule']        = $row['update_rule'];
                    $constraints[$name]['delete_rule']        = $row['delete_rule'];
                }
            }
            $constraints[$name]['columns'][] = $row['column_name'];
            if ($isFK) {
                $constraints[$name]['referenced_columns'][] = $row['referenced_column_name'];
            }
        }

        $this->data['constraints'][$schema][$table] = $constraints;
    }

    protected function loadTriggerData($schema)
    {
        if (isset($this->data['triggers'][$schema])) {
            return;
        }

        $this->prepareDataHierarchy('triggers', $schema);

        $p = $this->adapter->getPlatform();

        $isColumns = array(
            'trigger_name',
            'event_manipulation',
            'event_object_catalog',
            'event_object_schema',
            'event_object_table',
            'action_order',
            'action_condition',
            'action_statement',
            'action_orientation',
            array('action_timing' => 'condition_timing'),
            array('action_reference_old_table' => 'condition_reference_old_table'),
            array('action_reference_new_table' => 'condition_reference_new_table'),
            'created',
        );

        array_walk($isColumns, function (&$c) use ($p) {
            if (is_array($c)) {
                $alias = key($c);
                $c = $p->quoteIdentifierChain($c);
                if (is_string($alias)) {
                    $c .= ' ' . $p->quoteIdentifier($alias);
                }
            } else {
                $c = $p->quoteIdentifier($c);
            }
        });

        $sql = 'SELECT ' . implode(', ', $isColumns)
            . ' FROM ' . $p->quoteIdentifierChain(array('information_schema','triggers'))
            . ' WHERE ';

        if ($schema != self::DEFAULT_SCHEMA) {
            $sql .= $p->quoteIdentifier('trigger_schema')
                . ' = ' . $p->quoteValue($schema);
        } else {
            $sql .= $p->quoteIdentifier('trigger_schema')
                . ' != ' . $p->quoteValue('information_schema');
        }

        $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);

        $data = array();
        foreach ($results->toArray() as $row) {
            $row = array_change_key_case($row, CASE_LOWER);
            $row['action_reference_old_row'] = 'OLD';
            $row['action_reference_new_row'] = 'NEW';
            if (null !== $row['created']) {
                $row['created'] = new \DateTime($row['created']);
            }
            $data[$row['trigger_name']] = $row;
        }

        $this->data['triggers'][$schema] = $data;
    }


}