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