interface/modules/zend_modules/library/Zend/Db/Metadata/Source/SqliteMetadata.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 use Zend\Db\ResultSet\ResultSetInterface;
  14
  15 class SqliteMetadata extends AbstractSource
  16 {
  17     protected function loadSchemaData()
  18     {
  19         if (isset($this->data['schemas'])) {
  20             return;
  21         }
  22         $this->prepareDataHierarchy('schemas');
  23
  24         $results = $this->fetchPragma('database_list');
  25         foreach ($results as $row) {
  26             $schemas[] = $row['name'];
  27         }
  28         $this->data['schemas'] = $schemas;
  29     }
  30
  31     protected function loadTableNameData($schema)
  32     {
  33         if (isset($this->data['table_names'][$schema])) {
  34             return;
  35         }
  36         $this->prepareDataHierarchy('table_names', $schema);
  37
  38         // FEATURE: Filename?
  39
  40         $p = $this->adapter->getPlatform();
  41
  42         $sql = 'SELECT "name", "type", "sql" FROM ' . $p->quoteIdentifierChain(array($schema, 'sqlite_master'))
  43              . ' WHERE "type" IN (\'table\',\'view\') AND "name" NOT LIKE \'sqlite_%\'';
  44
  45         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
  46         $tables = array();
  47         foreach ($results->toArray() as $row) {
  48             if ('table' == $row['type']) {
  49                 $table = array(
  50                     'table_type' => 'BASE TABLE',
  51                     'view_definition' => null, // VIEW only
  52                     'check_option' => null,    // VIEW only
  53                     'is_updatable' => null,    // VIEW only
  54                 );
  55             } else {
  56                 $table = array(
  57                     'table_type' => 'VIEW',
  58                     'view_definition' => null,
  59                     'check_option' => 'NONE',
  60                     'is_updatable' => false,
  61                 );
  62
  63                 // Parse out extra data
  64                 if (null !== ($data = $this->parseView($row['sql']))) {
  65                     $table = array_merge($table, $data);
  66                 }
  67             }
  68             $tables[$row['name']] = $table;
  69         }
  70         $this->data['table_names'][$schema] = $tables;
  71     }
  72
  73     protected function loadColumnData($table, $schema)
  74     {
  75         if (isset($this->data['columns'][$schema][$table])) {
  76             return;
  77         }
  78         $this->prepareDataHierarchy('columns', $schema, $table);
  79         $this->prepareDataHierarchy('sqlite_columns', $schema, $table);
  80
  81         $p = $this->adapter->getPlatform();
  82
  83
  84         $results = $this->fetchPragma('table_info', $table, $schema);
  85
  86         $columns = array();
  87
  88         foreach ($results as $row) {
  89             $columns[$row['name']] = array(
  90                 // cid appears to be zero-based, ordinal position needs to be one-based
  91                 'ordinal_position'          => $row['cid'] + 1,
  92                 'column_default'            => $row['dflt_value'],
  93                 'is_nullable'               => !((bool) $row['notnull']),
  94                 'data_type'                 => $row['type'],
  95                 'character_maximum_length'  => null,
  96                 'character_octet_length'    => null,
  97                 'numeric_precision'         => null,
  98                 'numeric_scale'             => null,
  99                 'numeric_unsigned'          => null,
 100                 'erratas'                   => array(),
 101             );
 102             // TODO: populate character_ and numeric_values with correct info
 103         }
 104
 105         $this->data['columns'][$schema][$table] = $columns;
 106         $this->data['sqlite_columns'][$schema][$table] = $results;
 107     }
 108
 109     protected function loadConstraintData($table, $schema)
 110     {
 111         if (isset($this->data['constraints'][$schema][$table])) {
 112             return;
 113         }
 114
 115         $this->prepareDataHierarchy('constraints', $schema, $table);
 116
 117         $this->loadColumnData($table, $schema);
 118         $primaryKey = array();
 119
 120         foreach ($this->data['sqlite_columns'][$schema][$table] as $col) {
 121             if ((bool) $col['pk']) {
 122                 $primaryKey[] = $col['name'];
 123             }
 124         }
 125
 126         if (empty($primaryKey)) {
 127             $primaryKey = null;
 128         }
 129         $constraints = array();
 130         $indexes = $this->fetchPragma('index_list', $table, $schema);
 131         foreach ($indexes as $index) {
 132             if (!((bool) $index['unique'])) {
 133                 continue;
 134             }
 135             $constraint = array(
 136                 'constraint_name' => $index['name'],
 137                 'constraint_type' => 'UNIQUE',
 138                 'table_name'      => $table,
 139                 'columns'         => array(),
 140             );
 141
 142             $info = $this->fetchPragma('index_info', $index['name'], $schema);
 143
 144             foreach ($info as $column) {
 145                 $constraint['columns'][] = $column['name'];
 146             }
 147             if ($primaryKey === $constraint['columns']) {
 148                 $constraint['constraint_type'] = 'PRIMARY KEY';
 149                 $primaryKey = null;
 150             }
 151             $constraints[$constraint['constraint_name']] = $constraint;
 152         }
 153
 154         if (null !== $primaryKey) {
 155             $constraintName = '_zf_' . $table . '_PRIMARY';
 156             $constraints[$constraintName] = array(
 157                 'constraint_name'  => $constraintName,
 158                 'constraint_type'  => 'PRIMARY KEY',
 159                 'table_name'       => $table,
 160                 'columns' => $primaryKey,
 161             );
 162         }
 163
 164         $foreignKeys = $this->fetchPragma('foreign_key_list', $table, $schema);
 165
 166         $id = $name = null;
 167         foreach ($foreignKeys as $fk) {
 168             if ($id !== $fk['id']) {
 169                 $id = $fk['id'];
 170                 $name = '_zf_' . $table . '_FOREIGN_KEY_' . ($id + 1);
 171                 $constraints[$name] = array(
 172                     'constraint_name'  => $name,
 173                     'constraint_type'  => 'FOREIGN KEY',
 174                     'table_name'       => $table,
 175                     'columns'          => array(),
 176                     'referenced_table_schema' => $schema,
 177                     'referenced_table_name'   => $fk['table'],
 178                     'referenced_columns'      => array(),
 179                     // TODO: Verify match, on_update, and on_delete values conform to SQL Standard
 180                     'match_option'     => strtoupper($fk['match']),
 181                     'update_rule'      => strtoupper($fk['on_update']),
 182                     'delete_rule'      => strtoupper($fk['on_delete']),
 183                 );
 184             }
 185             $constraints[$name]['columns'][] = $fk['from'];
 186             $constraints[$name]['referenced_columns'][] = $fk['to'];
 187         }
 188
 189         $this->data['constraints'][$schema][$table] = $constraints;
 190     }
 191
 192     protected function loadTriggerData($schema)
 193     {
 194         if (isset($this->data['triggers'][$schema])) {
 195             return;
 196         }
 197
 198         $this->prepareDataHierarchy('triggers', $schema);
 199
 200         $p = $this->adapter->getPlatform();
 201
 202         $sql = 'SELECT "name", "tbl_name", "sql" FROM '
 203              . $p->quoteIdentifierChain(array($schema, 'sqlite_master'))
 204              . ' WHERE "type" = \'trigger\'';
 205
 206         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 207         $triggers = array();
 208         foreach ($results->toArray() as $row) {
 209             $trigger = array(
 210                 'trigger_name'               => $row['name'],
 211                 'event_manipulation'         => null, // in $row['sql']
 212                 'event_object_catalog'       => null,
 213                 'event_object_schema'        => $schema,
 214                 'event_object_table'         => $row['tbl_name'],
 215                 'action_order'               => 0,
 216                 'action_condition'           => null, // in $row['sql']
 217                 'action_statement'           => null, // in $row['sql']
 218                 'action_orientation'         => 'ROW',
 219                 'action_timing'              => null, // in $row['sql']
 220                 'action_reference_old_table' => null,
 221                 'action_reference_new_table' => null,
 222                 'action_reference_old_row'   => 'OLD',
 223                 'action_reference_new_row'   => 'NEW',
 224                 'created'                    => null,
 225             );
 226
 227             // Parse out extra data
 228             if (null !== ($data = $this->parseTrigger($row['sql']))) {
 229                 $trigger = array_merge($trigger, $data);
 230             }
 231             $triggers[$trigger['trigger_name']] = $trigger;
 232         }
 233
 234         $this->data['triggers'][$schema] = $triggers;
 235     }
 236
 237     protected function fetchPragma($name, $value = null, $schema = null)
 238     {
 239         $p = $this->adapter->getPlatform();
 240
 241         $sql = 'PRAGMA ';
 242
 243         if (null !== $schema) {
 244             $sql .= $p->quoteIdentifier($schema) . '.';
 245         }
 246         $sql .= $name;
 247
 248         if (null !== $value) {
 249             $sql .= '(' . $p->quoteTrustedValue($value) . ')';
 250         }
 251
 252         $results = $this->adapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 253         if ($results instanceof ResultSetInterface) {
 254             return $results->toArray();
 255         }
 256         return array();
 257     }
 258
 259     protected function parseView($sql)
 260     {
 261         static $re = null;
 262         if (null === $re) {
 263             $identifier = $this->getIdentifierRegularExpression();
 264             $identifierList = $this->getIdentifierListRegularExpression();
 265             $identifierChain = $this->getIdentifierChainRegularExpression();
 266             $re = $this->buildRegularExpression(array(
 267                 'CREATE',
 268                 array('TEMP|TEMPORARY'),
 269                 'VIEW',
 270                 array('IF', 'NOT', 'EXISTS'),
 271                 $identifierChain,
 272                 'AS',
 273                 '(?<view_definition>.+)',
 274                 array(';'),
 275             ));
 276         }
 277
 278         if (!preg_match($re, $sql, $matches)) {
 279             return null;
 280         }
 281         return array(
 282             'view_definition' => $matches['view_definition'],
 283         );
 284     }
 285
 286     protected function parseTrigger($sql)
 287     {
 288         static $re = null;
 289         if (null === $re) {
 290             $identifier = $this->getIdentifierRegularExpression();
 291             $identifierList = $this->getIdentifierListRegularExpression();
 292             $identifierChain = $this->getIdentifierChainRegularExpression();
 293             $re = $this->buildRegularExpression(array(
 294                 'CREATE',
 295                 array('TEMP|TEMPORARY'),
 296                 'TRIGGER',
 297                 array('IF', 'NOT', 'EXISTS'),
 298                 $identifierChain,
 299                 array('(?<action_timing>BEFORE|AFTER|INSTEAD\\s+OF)',),
 300                 '(?<event_manipulation>DELETE|INSERT|UPDATE)',
 301                 array('OF', '(?<column_usage>' . $identifierList . ')'),
 302                 'ON',
 303                 '(?<event_object_table>' . $identifier . ')',
 304                 array('FOR', 'EACH', 'ROW'),
 305                 array('WHEN', '(?<action_condition>.+)'),
 306                 '(?<action_statement>BEGIN',
 307                 '.+',
 308                 'END)',
 309                 array(';'),
 310             ));
 311         }
 312
 313         if (!preg_match($re, $sql, $matches)) {
 314             return null;
 315         }
 316         $data = array();
 317
 318         foreach ($matches as $key => $value) {
 319             if (is_string($key)) {
 320                 $data[$key] = $value;
 321             }
 322         }
 323
 324         // Normalize data and populate defaults, if necessary
 325
 326         $data['event_manipulation'] = strtoupper($data['event_manipulation']);
 327         if (empty($data['action_condition'])) {
 328             $data['action_condition'] = null;
 329         }
 330         if (!empty($data['action_timing'])) {
 331             $data['action_timing'] = strtoupper($data['action_timing']);
 332             if ('I' == $data['action_timing'][0]) {
 333                 // normalize the white-space between the two words
 334                 $data['action_timing'] = 'INSTEAD OF';
 335             }
 336         } else {
 337             $data['action_timing'] = 'AFTER';
 338         }
 339         unset($data['column_usage']);
 340
 341         return $data;
 342     }
 343
 344     protected function buildRegularExpression(array $re)
 345     {
 346         foreach ($re as &$value) {
 347             if (is_array($value)) {
 348                 $value = '(?:' . implode('\\s*+', $value) . '\\s*+)?';
 349             } else {
 350                 $value .= '\\s*+';
 351             }
 352         }
 353         unset($value);
 354         $re = '/^' . implode('\\s*+', $re) . '$/';
 355         return $re;
 356     }
 357
 358     protected function getIdentifierRegularExpression()
 359     {
 360         static $re = null;
 361         if (null === $re) {
 362             $re = '(?:' . implode('|', array(
 363                 '"(?:[^"\\\\]++|\\\\.)*+"',
 364                 '`(?:[^`]++|``)*+`',
 365                 '\\[[^\\]]+\\]',
 366                 '[^\\s\\.]+',
 367             )) . ')';
 368         }
 369
 370         return $re;
 371     }
 372
 373     protected function getIdentifierChainRegularExpression()
 374     {
 375         static $re = null;
 376         if (null === $re) {
 377             $identifier = $this->getIdentifierRegularExpression();
 378             $re = $identifier . '(?:\\s*\\.\\s*' . $identifier . ')*+';
 379         }
 380         return $re;
 381     }
 382
 383     protected function getIdentifierListRegularExpression()
 384     {
 385         static $re = null;
 386         if (null === $re) {
 387             $identifier = $this->getIdentifierRegularExpression();
 388             $re = $identifier . '(?:\\s*,\\s*' . $identifier . ')*+';
 389         }
 390         return $re;
 391     }
 392 }