interface/modules/zend_modules/library/Zend/Db/Metadata/Source/MysqlMetadata.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 MysqlMetadata extends AbstractSource
  15 {
  16     protected function loadSchemaData()
  17     {
  18         if (isset($this->data['schemas'])) {
  19             return;
  20         }
  21         $this->prepareDataHierarchy('schemas');
  22
  23         $p = $this->adapter->getPlatform();
  24
  25         $sql = 'SELECT ' . $p->quoteIdentifier('SCHEMA_NAME')
  26              . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'SCHEMATA'))
  27              . ' WHERE ' . $p->quoteIdentifier('SCHEMA_NAME')
  28              . ' != \'INFORMATION_SCHEMA\'';
  29
  30         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
  31
  32         $schemas = array();
  33         foreach ($results->toArray() as $row) {
  34             $schemas[] = $row['SCHEMA_NAME'];
  35         }
  36
  37         $this->data['schemas'] = $schemas;
  38     }
  39
  40     protected function loadTableNameData($schema)
  41     {
  42         if (isset($this->data['table_names'][$schema])) {
  43             return;
  44         }
  45         $this->prepareDataHierarchy('table_names', $schema);
  46
  47         $p = $this->adapter->getPlatform();
  48
  49         $isColumns = array(
  50             array('T', 'TABLE_NAME'),
  51             array('T', 'TABLE_TYPE'),
  52             array('V', 'VIEW_DEFINITION'),
  53             array('V', 'CHECK_OPTION'),
  54             array('V', 'IS_UPDATABLE'),
  55         );
  56
  57         array_walk($isColumns, function (&$c) use ($p) { $c = $p->quoteIdentifierChain($c); });
  58
  59         $sql = 'SELECT ' . implode(', ', $isColumns)
  60              . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLES')) . 'T'
  61
  62              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'VIEWS')) . ' V'
  63              . ' ON ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
  64              . '  = ' . $p->quoteIdentifierChain(array('V', 'TABLE_SCHEMA'))
  65              . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
  66              . '  = ' . $p->quoteIdentifierChain(array('V', 'TABLE_NAME'))
  67
  68              . ' WHERE ' . $p->quoteIdentifierChain(array('T', 'TABLE_TYPE'))
  69              . ' IN (\'BASE TABLE\', \'VIEW\')';
  70
  71         if ($schema != self::DEFAULT_SCHEMA) {
  72             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
  73                   . ' = ' . $p->quoteTrustedValue($schema);
  74         } else {
  75             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
  76                   . ' != \'INFORMATION_SCHEMA\'';
  77         }
  78
  79         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
  80
  81         $tables = array();
  82         foreach ($results->toArray() as $row) {
  83             $tables[$row['TABLE_NAME']] = array(
  84                 'table_type' => $row['TABLE_TYPE'],
  85                 'view_definition' => $row['VIEW_DEFINITION'],
  86                 'check_option' => $row['CHECK_OPTION'],
  87                 'is_updatable' => ('YES' == $row['IS_UPDATABLE']),
  88             );
  89         }
  90
  91         $this->data['table_names'][$schema] = $tables;
  92     }
  93
  94     protected function loadColumnData($table, $schema)
  95     {
  96         if (isset($this->data['columns'][$schema][$table])) {
  97             return;
  98         }
  99         $this->prepareDataHierarchy('columns', $schema, $table);
 100         $p = $this->adapter->getPlatform();
 101
 102         $isColumns = array(
 103             array('C', 'ORDINAL_POSITION'),
 104             array('C', 'COLUMN_DEFAULT'),
 105             array('C', 'IS_NULLABLE'),
 106             array('C', 'DATA_TYPE'),
 107             array('C', 'CHARACTER_MAXIMUM_LENGTH'),
 108             array('C', 'CHARACTER_OCTET_LENGTH'),
 109             array('C', 'NUMERIC_PRECISION'),
 110             array('C', 'NUMERIC_SCALE'),
 111             array('C', 'COLUMN_NAME'),
 112             array('C', 'COLUMN_TYPE'),
 113         );
 114
 115         array_walk($isColumns, function (&$c) use ($p) { $c = $p->quoteIdentifierChain($c); });
 116
 117         $sql = 'SELECT ' . implode(', ', $isColumns)
 118              . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLES')) . 'T'
 119              . ' INNER JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'COLUMNS')) . 'C'
 120              . ' ON ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 121              . '  = ' . $p->quoteIdentifierChain(array('C', 'TABLE_SCHEMA'))
 122              . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 123              . '  = ' . $p->quoteIdentifierChain(array('C', 'TABLE_NAME'))
 124              . ' WHERE ' . $p->quoteIdentifierChain(array('T', 'TABLE_TYPE'))
 125              . ' IN (\'BASE TABLE\', \'VIEW\')'
 126              . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 127              . '  = ' . $p->quoteTrustedValue($table);
 128
 129         if ($schema != self::DEFAULT_SCHEMA) {
 130             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 131                   . ' = ' . $p->quoteTrustedValue($schema);
 132         } else {
 133             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 134                   . ' != \'INFORMATION_SCHEMA\'';
 135         }
 136
 137         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 138         $columns = array();
 139         foreach ($results->toArray() as $row) {
 140             $erratas = array();
 141             $matches = array();
 142             if (preg_match('/^(?:enum|set)\((.+)\)$/i', $row['COLUMN_TYPE'], $matches)) {
 143                 $permittedValues = $matches[1];
 144                 if (preg_match_all("/\\s*'((?:[^']++|'')*+)'\\s*(?:,|\$)/", $permittedValues, $matches, PREG_PATTERN_ORDER)) {
 145                     $permittedValues = str_replace("''", "'", $matches[1]);
 146                 } else {
 147                     $permittedValues = array($permittedValues);
 148                 }
 149                 $erratas['permitted_values'] = $permittedValues;
 150             }
 151             $columns[$row['COLUMN_NAME']] = array(
 152                 'ordinal_position'          => $row['ORDINAL_POSITION'],
 153                 'column_default'            => $row['COLUMN_DEFAULT'],
 154                 'is_nullable'               => ('YES' == $row['IS_NULLABLE']),
 155                 'data_type'                 => $row['DATA_TYPE'],
 156                 'character_maximum_length'  => $row['CHARACTER_MAXIMUM_LENGTH'],
 157                 'character_octet_length'    => $row['CHARACTER_OCTET_LENGTH'],
 158                 'numeric_precision'         => $row['NUMERIC_PRECISION'],
 159                 'numeric_scale'             => $row['NUMERIC_SCALE'],
 160                 'numeric_unsigned'          => (false !== strpos($row['COLUMN_TYPE'], 'unsigned')),
 161                 'erratas'                   => $erratas,
 162             );
 163         }
 164
 165         $this->data['columns'][$schema][$table] = $columns;
 166     }
 167
 168     protected function loadConstraintData($table, $schema)
 169     {
 170         if (isset($this->data['constraints'][$schema][$table])) {
 171             return;
 172         }
 173
 174         $this->prepareDataHierarchy('constraints', $schema, $table);
 175
 176         $isColumns = array(
 177             array('T', 'TABLE_NAME'),
 178             array('TC', 'CONSTRAINT_NAME'),
 179             array('TC', 'CONSTRAINT_TYPE'),
 180             array('KCU', 'COLUMN_NAME'),
 181             array('RC', 'MATCH_OPTION'),
 182             array('RC', 'UPDATE_RULE'),
 183             array('RC', 'DELETE_RULE'),
 184             array('KCU', 'REFERENCED_TABLE_SCHEMA'),
 185             array('KCU', 'REFERENCED_TABLE_NAME'),
 186             array('KCU', 'REFERENCED_COLUMN_NAME'),
 187         );
 188
 189         $p = $this->adapter->getPlatform();
 190
 191         array_walk($isColumns, function (&$c) use ($p) {
 192             $c = $p->quoteIdentifierChain($c);
 193         });
 194
 195         $sql = 'SELECT ' . implode(', ', $isColumns)
 196              . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLES')) . ' T'
 197
 198              . ' INNER JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLE_CONSTRAINTS')) . ' TC'
 199              . ' ON ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 200              . '  = ' . $p->quoteIdentifierChain(array('TC', 'TABLE_SCHEMA'))
 201              . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 202              . '  = ' . $p->quoteIdentifierChain(array('TC', 'TABLE_NAME'))
 203
 204              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'KEY_COLUMN_USAGE')) . ' KCU'
 205              . ' ON ' . $p->quoteIdentifierChain(array('TC', 'TABLE_SCHEMA'))
 206              . '  = ' . $p->quoteIdentifierChain(array('KCU', 'TABLE_SCHEMA'))
 207              . ' AND ' . $p->quoteIdentifierChain(array('TC', 'TABLE_NAME'))
 208              . '  = ' . $p->quoteIdentifierChain(array('KCU', 'TABLE_NAME'))
 209              . ' AND ' . $p->quoteIdentifierChain(array('TC', 'CONSTRAINT_NAME'))
 210              . '  = ' . $p->quoteIdentifierChain(array('KCU', 'CONSTRAINT_NAME'))
 211
 212              . ' LEFT JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'REFERENTIAL_CONSTRAINTS')) . ' RC'
 213              . ' ON ' . $p->quoteIdentifierChain(array('TC', 'CONSTRAINT_SCHEMA'))
 214              . '  = ' . $p->quoteIdentifierChain(array('RC', 'CONSTRAINT_SCHEMA'))
 215              . ' AND ' . $p->quoteIdentifierChain(array('TC', 'CONSTRAINT_NAME'))
 216              . '  = ' . $p->quoteIdentifierChain(array('RC', 'CONSTRAINT_NAME'))
 217
 218              . ' WHERE ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 219              . ' = ' . $p->quoteTrustedValue($table)
 220              . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_TYPE'))
 221              . ' IN (\'BASE TABLE\', \'VIEW\')';
 222
 223         if ($schema != self::DEFAULT_SCHEMA) {
 224             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 225             . ' = ' . $p->quoteTrustedValue($schema);
 226         } else {
 227             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 228             . ' != \'INFORMATION_SCHEMA\'';
 229         }
 230
 231         $sql .= ' ORDER BY CASE ' . $p->quoteIdentifierChain(array('TC', 'CONSTRAINT_TYPE'))
 232               . " WHEN 'PRIMARY KEY' THEN 1"
 233               . " WHEN 'UNIQUE' THEN 2"
 234               . " WHEN 'FOREIGN KEY' THEN 3"
 235               . " ELSE 4 END"
 236
 237               . ', ' . $p->quoteIdentifierChain(array('TC', 'CONSTRAINT_NAME'))
 238               . ', ' . $p->quoteIdentifierChain(array('KCU', 'ORDINAL_POSITION'));
 239
 240         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 241
 242         $realName = null;
 243         $constraints = array();
 244         foreach ($results->toArray() as $row) {
 245             if ($row['CONSTRAINT_NAME'] !== $realName) {
 246                 $realName = $row['CONSTRAINT_NAME'];
 247                 $isFK = ('FOREIGN KEY' == $row['CONSTRAINT_TYPE']);
 248                 if ($isFK) {
 249                     $name = $realName;
 250                 } else {
 251                     $name = '_zf_' . $row['TABLE_NAME'] . '_' . $realName;
 252                 }
 253                 $constraints[$name] = array(
 254                     'constraint_name' => $name,
 255                     'constraint_type' => $row['CONSTRAINT_TYPE'],
 256                     'table_name'      => $row['TABLE_NAME'],
 257                     'columns'         => array(),
 258                 );
 259                 if ($isFK) {
 260                     $constraints[$name]['referenced_table_schema'] = $row['REFERENCED_TABLE_SCHEMA'];
 261                     $constraints[$name]['referenced_table_name']   = $row['REFERENCED_TABLE_NAME'];
 262                     $constraints[$name]['referenced_columns']      = array();
 263                     $constraints[$name]['match_option']       = $row['MATCH_OPTION'];
 264                     $constraints[$name]['update_rule']        = $row['UPDATE_RULE'];
 265                     $constraints[$name]['delete_rule']        = $row['DELETE_RULE'];
 266                 }
 267             }
 268             $constraints[$name]['columns'][] = $row['COLUMN_NAME'];
 269             if ($isFK) {
 270                 $constraints[$name]['referenced_columns'][] = $row['REFERENCED_COLUMN_NAME'];
 271             }
 272         }
 273
 274         $this->data['constraints'][$schema][$table] = $constraints;
 275     }
 276
 277     protected function loadConstraintDataNames($schema)
 278     {
 279         if (isset($this->data['constraint_names'][$schema])) {
 280             return;
 281         }
 282
 283         $this->prepareDataHierarchy('constraint_names', $schema);
 284
 285         $p = $this->adapter->getPlatform();
 286
 287         $isColumns = array(
 288             array('TC', 'TABLE_NAME'),
 289             array('TC', 'CONSTRAINT_NAME'),
 290             array('TC', 'CONSTRAINT_TYPE'),
 291         );
 292
 293         array_walk($isColumns, function (&$c) use ($p) {
 294             $c = $p->quoteIdentifierChain($c);
 295         });
 296
 297         $sql = 'SELECT ' . implode(', ', $isColumns)
 298         . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLES')) . 'T'
 299         . ' INNER JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLE_CONSTRAINTS')) . 'TC'
 300         . ' ON ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 301         . '  = ' . $p->quoteIdentifierChain(array('TC', 'TABLE_SCHEMA'))
 302         . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 303         . '  = ' . $p->quoteIdentifierChain(array('TC', 'TABLE_NAME'))
 304         . ' WHERE ' . $p->quoteIdentifierChain(array('T', 'TABLE_TYPE'))
 305         . ' IN (\'BASE TABLE\', \'VIEW\')';
 306
 307         if ($schema != self::DEFAULT_SCHEMA) {
 308             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 309             . ' = ' . $p->quoteTrustedValue($schema);
 310         } else {
 311             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 312             . ' != \'INFORMATION_SCHEMA\'';
 313         }
 314
 315         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 316
 317         $data = array();
 318         foreach ($results->toArray() as $row) {
 319             $data[] = array_change_key_case($row, CASE_LOWER);
 320         }
 321
 322         $this->data['constraint_names'][$schema] = $data;
 323     }
 324
 325     protected function loadConstraintDataKeys($schema)
 326     {
 327         if (isset($this->data['constraint_keys'][$schema])) {
 328             return;
 329         }
 330
 331         $this->prepareDataHierarchy('constraint_keys', $schema);
 332
 333         $p = $this->adapter->getPlatform();
 334
 335         $isColumns = array(
 336             array('T', 'TABLE_NAME'),
 337             array('KCU', 'CONSTRAINT_NAME'),
 338             array('KCU', 'COLUMN_NAME'),
 339             array('KCU', 'ORDINAL_POSITION'),
 340         );
 341
 342         array_walk($isColumns, function (&$c) use ($p) {
 343             $c = $p->quoteIdentifierChain($c);
 344         });
 345
 346         $sql = 'SELECT ' . implode(', ', $isColumns)
 347         . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLES')) . 'T'
 348
 349         . ' INNER JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'KEY_COLUMN_USAGE')) . 'KCU'
 350         . ' ON ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 351         . '  = ' . $p->quoteIdentifierChain(array('KCU', 'TABLE_SCHEMA'))
 352         . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 353         . '  = ' . $p->quoteIdentifierChain(array('KCU', 'TABLE_NAME'))
 354
 355         . ' WHERE ' . $p->quoteIdentifierChain(array('T', 'TABLE_TYPE'))
 356         . ' IN (\'BASE TABLE\', \'VIEW\')';
 357
 358         if ($schema != self::DEFAULT_SCHEMA) {
 359             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 360             . ' = ' . $p->quoteTrustedValue($schema);
 361         } else {
 362             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 363             . ' != \'INFORMATION_SCHEMA\'';
 364         }
 365
 366         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 367
 368         $data = array();
 369         foreach ($results->toArray() as $row) {
 370             $data[] = array_change_key_case($row, CASE_LOWER);
 371         }
 372
 373         $this->data['constraint_keys'][$schema] = $data;
 374     }
 375
 376     protected function loadConstraintReferences($table, $schema)
 377     {
 378         parent::loadConstraintReferences($table, $schema);
 379
 380         $p = $this->adapter->getPlatform();
 381
 382         $isColumns = array(
 383             array('RC', 'TABLE_NAME'),
 384             array('RC', 'CONSTRAINT_NAME'),
 385             array('RC', 'UPDATE_RULE'),
 386             array('RC', 'DELETE_RULE'),
 387             array('KCU', 'REFERENCED_TABLE_SCHEMA'),
 388             array('KCU', 'REFERENCED_TABLE_NAME'),
 389             array('KCU', 'REFERENCED_COLUMN_NAME'),
 390         );
 391
 392         array_walk($isColumns, function (&$c) use ($p) {
 393             $c = $p->quoteIdentifierChain($c);
 394         });
 395
 396         $sql = 'SELECT ' . implode(', ', $isColumns)
 397         . 'FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TABLES')) . 'T'
 398
 399         . ' INNER JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'REFERENTIAL_CONSTRAINTS')) . 'RC'
 400         . ' ON ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 401         . '  = ' . $p->quoteIdentifierChain(array('RC', 'CONSTRAINT_SCHEMA'))
 402         . ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_NAME'))
 403         . '  = ' . $p->quoteIdentifierChain(array('RC', 'TABLE_NAME'))
 404
 405         . ' INNER JOIN ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'KEY_COLUMN_USAGE')) . 'KCU'
 406         . ' ON ' . $p->quoteIdentifierChain(array('RC', 'CONSTRAINT_SCHEMA'))
 407         . '  = ' . $p->quoteIdentifierChain(array('KCU', 'TABLE_SCHEMA'))
 408         . ' AND ' . $p->quoteIdentifierChain(array('RC', 'TABLE_NAME'))
 409         . '  = ' . $p->quoteIdentifierChain(array('KCU', 'TABLE_NAME'))
 410         . ' AND ' . $p->quoteIdentifierChain(array('RC', 'CONSTRAINT_NAME'))
 411         . '  = ' . $p->quoteIdentifierChain(array('KCU', 'CONSTRAINT_NAME'))
 412
 413         . 'WHERE ' . $p->quoteIdentifierChain(array('T', 'TABLE_TYPE'))
 414         . ' IN (\'BASE TABLE\', \'VIEW\')';
 415
 416         if ($schema != self::DEFAULT_SCHEMA) {
 417             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 418             . ' = ' . $p->quoteTrustedValue($schema);
 419         } else {
 420             $sql .= ' AND ' . $p->quoteIdentifierChain(array('T', 'TABLE_SCHEMA'))
 421             . ' != \'INFORMATION_SCHEMA\'';
 422         }
 423
 424         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 425
 426         $data = array();
 427         foreach ($results->toArray() as $row) {
 428             $data[] = array_change_key_case($row, CASE_LOWER);
 429         }
 430
 431         $this->data['constraint_references'][$schema] = $data;
 432     }
 433
 434     protected function loadTriggerData($schema)
 435     {
 436         if (isset($this->data['triggers'][$schema])) {
 437             return;
 438         }
 439
 440         $this->prepareDataHierarchy('triggers', $schema);
 441
 442         $p = $this->adapter->getPlatform();
 443
 444         $isColumns = array(
 445 //            'TRIGGER_CATALOG',
 446 //            'TRIGGER_SCHEMA',
 447             'TRIGGER_NAME',
 448             'EVENT_MANIPULATION',
 449             'EVENT_OBJECT_CATALOG',
 450             'EVENT_OBJECT_SCHEMA',
 451             'EVENT_OBJECT_TABLE',
 452             'ACTION_ORDER',
 453             'ACTION_CONDITION',
 454             'ACTION_STATEMENT',
 455             'ACTION_ORIENTATION',
 456             'ACTION_TIMING',
 457             'ACTION_REFERENCE_OLD_TABLE',
 458             'ACTION_REFERENCE_NEW_TABLE',
 459             'ACTION_REFERENCE_OLD_ROW',
 460             'ACTION_REFERENCE_NEW_ROW',
 461             'CREATED',
 462         );
 463
 464         array_walk($isColumns, function (&$c) use ($p) {
 465             $c = $p->quoteIdentifier($c);
 466         });
 467
 468         $sql = 'SELECT ' . implode(', ', $isColumns)
 469         . ' FROM ' . $p->quoteIdentifierChain(array('INFORMATION_SCHEMA', 'TRIGGERS'))
 470         . ' WHERE ';
 471
 472         if ($schema != self::DEFAULT_SCHEMA) {
 473             $sql .= $p->quoteIdentifier('TRIGGER_SCHEMA')
 474             . ' = ' . $p->quoteTrustedValue($schema);
 475         } else {
 476             $sql .= $p->quoteIdentifier('TRIGGER_SCHEMA')
 477             . ' != \'INFORMATION_SCHEMA\'';
 478         }
 479
 480         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 481
 482         $data = array();
 483         foreach ($results->toArray() as $row) {
 484             $row = array_change_key_case($row, CASE_LOWER);
 485             if (null !== $row['created']) {
 486                 $row['created'] = new \DateTime($row['created']);
 487             }
 488             $data[$row['trigger_name']] = $row;
 489         }
 490
 491         $this->data['triggers'][$schema] = $data;
 492     }
 493 }