interface/modules/zend_modules/library/Zend/Db/Metadata/Source/PostgresqlMetadata.php

   1 <?php
   2 /**
   3  * Zend Framework (http://framework.zend.com/)
   4  *
   5  * @link      http://github.com/zendframework/zf2 for the canonical source repository
   6  * @copyright Copyright (c) 2005-2013 Zend Technologies USA Inc. (http://www.zend.com)
   7  * @license   http://framework.zend.com/license/new-bsd New BSD License
   8  */
   9
  10 namespace Zend\Db\Metadata\Source;
  11
  12 use Zend\Db\Adapter\Adapter;
  13
  14 class PostgresqlMetadata extends AbstractSource
  15 {
  16
  17     protected function loadSchemaData()
  18     {
  19         if (isset($this->data['schemas'])) {
  20             return;
  21         }
  22         $this->prepareDataHierarchy('schemas');
  23
  24         $p = $this->adapter->getPlatform();
  25
  26         $sql = 'SELECT ' . $p->quoteIdentifier('schema_name')
  27             . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'schemata'))
  28             . ' WHERE ' . $p->quoteIdentifier('schema_name')
  29             . ' != \'information_schema\''
  30             . ' AND ' . $p->quoteIdentifier('schema_name') . " NOT LIKE 'pg_%'";
  31
  32         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
  33
  34         $schemas = array();
  35         foreach ($results->toArray() as $row) {
  36             $schemas[] = $row['schema_name'];
  37         }
  38
  39         $this->data['schemas'] = $schemas;
  40     }
  41
  42     protected function loadTableNameData($schema)
  43     {
  44         if (isset($this->data['table_names'][$schema])) {
  45             return;
  46         }
  47         $this->prepareDataHierarchy('table_names', $schema);
  48
  49         $p = $this->adapter->getPlatform();
  50
  51         $isColumns = array(
  52             array('t', 'table_name'),
  53             array('t', 'table_type'),
  54             array('v', 'view_definition'),
  55             array('v', 'check_option'),
  56             array('v', 'is_updatable'),
  57         );
  58
  59         array_walk($isColumns, function (&$c) use ($p) { $c = $p->quoteIdentifierChain($c); });
  60
  61         $sql = 'SELECT ' . implode(', ', $isColumns)
  62             . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'tables')) . ' t'
  63
  64             . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'views')) . ' v'
  65             . ' ON ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
  66             . '  = ' . $p->quoteIdentifierChain(array('v', 'table_schema'))
  67             . ' AND ' . $p->quoteIdentifierChain(array('t', 'table_name'))
  68             . '  = ' . $p->quoteIdentifierChain(array('v', 'table_name'))
  69
  70             . ' WHERE ' . $p->quoteIdentifierChain(array('t', 'table_type'))
  71             . ' IN (\'BASE TABLE\', \'VIEW\')';
  72
  73         if ($schema != self::DEFAULT_SCHEMA) {
  74             $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
  75                 . ' = ' . $p->quoteTrustedValue($schema);
  76         } else {
  77             $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
  78                 . ' != \'information_schema\'';
  79         }
  80
  81         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
  82
  83         $tables = array();
  84         foreach ($results->toArray() as $row) {
  85             $tables[$row['table_name']] = array(
  86                 'table_type' => $row['table_type'],
  87                 'view_definition' => $row['view_definition'],
  88                 'check_option' => $row['check_option'],
  89                 'is_updatable' => ('YES' == $row['is_updatable']),
  90             );
  91         }
  92
  93         $this->data['table_names'][$schema] = $tables;
  94     }
  95
  96     protected function loadColumnData($table, $schema)
  97     {
  98         if (isset($this->data['columns'][$schema][$table])) {
  99             return;
 100         }
 101
 102         $this->prepareDataHierarchy('columns', $schema, $table);
 103
 104         $platform = $this->adapter->getPlatform();
 105
 106         $isColumns = array(
 107             'table_name',
 108             'column_name',
 109             'ordinal_position',
 110             'column_default',
 111             'is_nullable',
 112             'data_type',
 113             'character_maximum_length',
 114             'character_octet_length',
 115             'numeric_precision',
 116             'numeric_scale',
 117         );
 118
 119         array_walk($isColumns, function (&$c) use ($platform) { $c = $platform->quoteIdentifier($c); });
 120
 121         $sql = 'SELECT ' . implode(', ', $isColumns)
 122             . ' FROM ' . $platform->quoteIdentifier('information_schema')
 123             . $platform->getIdentifierSeparator() . $platform->quoteIdentifier('columns')
 124             . ' WHERE ' . $platform->quoteIdentifier('table_schema')
 125             . ' != \'information\''
 126             . ' AND ' . $platform->quoteIdentifier('table_name')
 127             . ' = ' . $platform->quoteTrustedValue($table);
 128
 129         if ($schema != '__DEFAULT_SCHEMA__') {
 130             $sql .= ' AND ' . $platform->quoteIdentifier('table_schema')
 131                 . ' = ' . $platform->quoteTrustedValue($schema);
 132         }
 133
 134         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 135         $columns = array();
 136         foreach ($results->toArray() as $row) {
 137             $columns[$row['column_name']] = array(
 138                 'ordinal_position'          => $row['ordinal_position'],
 139                 'column_default'            => $row['column_default'],
 140                 'is_nullable'               => ('YES' == $row['is_nullable']),
 141                 'data_type'                 => $row['data_type'],
 142                 'character_maximum_length'  => $row['character_maximum_length'],
 143                 'character_octet_length'    => $row['character_octet_length'],
 144                 'numeric_precision'         => $row['numeric_precision'],
 145                 'numeric_scale'             => $row['numeric_scale'],
 146                 'numeric_unsigned'          => null,
 147                 'erratas'                   => array(),
 148             );
 149         }
 150
 151         $this->data['columns'][$schema][$table] = $columns;
 152     }
 153
 154     protected function loadConstraintData($table, $schema)
 155     {
 156         if (isset($this->data['constraints'][$schema][$table])) {
 157             return;
 158         }
 159
 160         $this->prepareDataHierarchy('constraints', $schema, $table);
 161
 162         $isColumns = array(
 163             array('t', 'table_name'),
 164             array('tc', 'constraint_name'),
 165             array('tc', 'constraint_type'),
 166             array('kcu', 'column_name'),
 167             array('cc', 'check_clause'),
 168             array('rc', 'match_option'),
 169             array('rc', 'update_rule'),
 170             array('rc', 'delete_rule'),
 171             array('referenced_table_schema' => 'kcu2', 'table_schema'),
 172             array('referenced_table_name' => 'kcu2', 'table_name'),
 173             array('referenced_column_name' => 'kcu2', 'column_name'),
 174         );
 175
 176         $p = $this->adapter->getPlatform();
 177
 178         array_walk($isColumns, function (&$c) use ($p) {
 179             $alias = key($c);
 180             $c = $p->quoteIdentifierChain($c);
 181             if (is_string($alias)) {
 182                 $c .= ' ' . $p->quoteIdentifier($alias);
 183             }
 184         });
 185
 186         $sql = 'SELECT ' . implode(', ', $isColumns)
 187              . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'tables')) . ' t'
 188
 189              . ' INNER JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'table_constraints')) . ' tc'
 190              . ' ON ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
 191              . '  = ' . $p->quoteIdentifierChain(array('tc', 'table_schema'))
 192              . ' AND ' . $p->quoteIdentifierChain(array('t', 'table_name'))
 193              . '  = ' . $p->quoteIdentifierChain(array('tc', 'table_name'))
 194
 195              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'key_column_usage')) . ' kcu'
 196              . ' ON ' . $p->quoteIdentifierChain(array('tc', 'table_schema'))
 197              . '  = ' . $p->quoteIdentifierChain(array('kcu', 'table_schema'))
 198              . ' AND ' . $p->quoteIdentifierChain(array('tc', 'table_name'))
 199              . '  = ' . $p->quoteIdentifierChain(array('kcu', 'table_name'))
 200              . ' AND ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
 201              . '  = ' . $p->quoteIdentifierChain(array('kcu', 'constraint_name'))
 202
 203              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'check_constraints')) . ' cc'
 204              . ' ON ' . $p->quoteIdentifierChain(array('tc', 'constraint_schema'))
 205              . '  = ' . $p->quoteIdentifierChain(array('cc', 'constraint_schema'))
 206              . ' AND ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
 207              . '  = ' . $p->quoteIdentifierChain(array('cc', 'constraint_name'))
 208
 209              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'referential_constraints')) . ' rc'
 210              . ' ON ' . $p->quoteIdentifierChain(array('tc', 'constraint_schema'))
 211              . '  = ' . $p->quoteIdentifierChain(array('rc', 'constraint_schema'))
 212              . ' AND ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
 213              . '  = ' . $p->quoteIdentifierChain(array('rc', 'constraint_name'))
 214
 215              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('information_schema', 'key_column_usage')) . ' kcu2'
 216              . ' ON ' . $p->quoteIdentifierChain(array('rc', 'unique_constraint_schema'))
 217              . '  = ' . $p->quoteIdentifierChain(array('kcu2', 'constraint_schema'))
 218              . ' AND ' . $p->quoteIdentifierChain(array('rc', 'unique_constraint_name'))
 219              . '  = ' . $p->quoteIdentifierChain(array('kcu2', 'constraint_name'))
 220              . ' AND ' . $p->quoteIdentifierChain(array('kcu', 'position_in_unique_constraint'))
 221              . '  = ' . $p->quoteIdentifierChain(array('kcu2', 'ordinal_position'))
 222
 223              . ' WHERE ' . $p->quoteIdentifierChain(array('t', 'table_name'))
 224              . ' = ' . $p->quoteTrustedValue($table)
 225              . ' AND ' . $p->quoteIdentifierChain(array('t', 'table_type'))
 226              . ' IN (\'BASE TABLE\', \'VIEW\')';
 227
 228         if ($schema != self::DEFAULT_SCHEMA) {
 229             $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
 230             . ' = ' . $p->quoteTrustedValue($schema);
 231         } else {
 232             $sql .= ' AND ' . $p->quoteIdentifierChain(array('t', 'table_schema'))
 233             . ' != \'information_schema\'';
 234         }
 235
 236         $sql .= ' ORDER BY CASE ' . $p->quoteIdentifierChain(array('tc', 'constraint_type'))
 237               . " WHEN 'PRIMARY KEY' THEN 1"
 238               . " WHEN 'UNIQUE' THEN 2"
 239               . " WHEN 'FOREIGN KEY' THEN 3"
 240               . " WHEN 'CHECK' THEN 4"
 241               . " ELSE 5 END"
 242               . ', ' . $p->quoteIdentifierChain(array('tc', 'constraint_name'))
 243               . ', ' . $p->quoteIdentifierChain(array('kcu', 'ordinal_position'));
 244
 245         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 246
 247         $name = null;
 248         $constraints = array();
 249         foreach ($results->toArray() as $row) {
 250             if ($row['constraint_name'] !== $name) {
 251                 $name = $row['constraint_name'];
 252                 $constraints[$name] = array(
 253                     'constraint_name' => $name,
 254                     'constraint_type' => $row['constraint_type'],
 255                     'table_name'      => $row['table_name'],
 256                 );
 257                 if ('CHECK' == $row['constraint_type']) {
 258                     $constraints[$name]['check_clause'] = $row['check_clause'];
 259                     continue;
 260                 }
 261                 $constraints[$name]['columns'] = array();
 262                 $isFK = ('FOREIGN KEY' == $row['constraint_type']);
 263                 if ($isFK) {
 264                     $constraints[$name]['referenced_table_schema'] = $row['referenced_table_schema'];
 265                     $constraints[$name]['referenced_table_name']   = $row['referenced_table_name'];
 266                     $constraints[$name]['referenced_columns']      = array();
 267                     $constraints[$name]['match_option']       = $row['match_option'];
 268                     $constraints[$name]['update_rule']        = $row['update_rule'];
 269                     $constraints[$name]['delete_rule']        = $row['delete_rule'];
 270                 }
 271             }
 272             $constraints[$name]['columns'][] = $row['column_name'];
 273             if ($isFK) {
 274                 $constraints[$name]['referenced_columns'][] = $row['referenced_column_name'];
 275             }
 276         }
 277
 278         $this->data['constraints'][$schema][$table] = $constraints;
 279     }
 280
 281     protected function loadTriggerData($schema)
 282     {
 283         if (isset($this->data['triggers'][$schema])) {
 284             return;
 285         }
 286
 287         $this->prepareDataHierarchy('triggers', $schema);
 288
 289         $p = $this->adapter->getPlatform();
 290
 291         $isColumns = array(
 292             'trigger_name',
 293             'event_manipulation',
 294             'event_object_catalog',
 295             'event_object_schema',
 296             'event_object_table',
 297             'action_order',
 298             'action_condition',
 299             'action_statement',
 300             'action_orientation',
 301             array('action_timing' => 'condition_timing'),
 302             array('action_reference_old_table' => 'condition_reference_old_table'),
 303             array('action_reference_new_table' => 'condition_reference_new_table'),
 304             'created',
 305         );
 306
 307         array_walk($isColumns, function (&$c) use ($p) {
 308             if (is_array($c)) {
 309                 $alias = key($c);
 310                 $c = $p->quoteIdentifierChain($c);
 311                 if (is_string($alias)) {
 312                     $c .= ' ' . $p->quoteIdentifier($alias);
 313                 }
 314             } else {
 315                 $c = $p->quoteIdentifier($c);
 316             }
 317         });
 318
 319         $sql = 'SELECT ' . implode(', ', $isColumns)
 320             . ' FROM ' . $p->quoteIdentifierChain(array('information_schema', 'triggers'))
 321             . ' WHERE ';
 322
 323         if ($schema != self::DEFAULT_SCHEMA) {
 324             $sql .= $p->quoteIdentifier('trigger_schema')
 325                 . ' = ' . $p->quoteTrustedValue($schema);
 326         } else {
 327             $sql .= $p->quoteIdentifier('trigger_schema')
 328                 . ' != \'information_schema\'';
 329         }
 330
 331         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 332
 333         $data = array();
 334         foreach ($results->toArray() as $row) {
 335             $row = array_change_key_case($row, CASE_LOWER);
 336             $row['action_reference_old_row'] = 'OLD';
 337             $row['action_reference_new_row'] = 'NEW';
 338             if (null !== $row['created']) {
 339                 $row['created'] = new \DateTime($row['created']);
 340             }
 341             $data[$row['trigger_name']] = $row;
 342         }
 343
 344         $this->data['triggers'][$schema] = $data;
 345     }
 346
 347
 348 }