Completed
Push — master ( 417289...093d86 )
by Sébastien
06:43
created

MysqlInformationSchema::getForeignKeys()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 5
Code Lines 3

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 3
CRAP Score 1
Metric Value
dl 0
loc 5
ccs 3
cts 3
cp 1
rs 9.4286
nc 1
cc 1
eloc 3
nop 1
crap 1
1
<?php
2
namespace Soluble\Schema\Source\Mysql;
3
4
use Soluble\Schema\Exception;
5
use Soluble\Schema\Source;
6
use Soluble\Schema\Db\Wrapper\MysqlConnectionAdapter;
7
use Zend\Config\Config;
8
9
class MysqlInformationSchema extends Source\AbstractSource
10
{
11
    /**
12
     * Schema name
13
     *
14
     * @var string
15
     */
16
    protected $schema;
17
18
    /**
19
     * @var MysqlConnectionAdapter
20
     */
21
    protected $adapter;
22
23
24
25
    /**
26
     * Used to restore innodb stats mysql global variable
27
     * @var string
28
     */
29
    protected $mysql_innodbstats_value;
30
31
    /**
32
     * Whether to include full schema options like comment, collations...
33
     * @var boolean
34
     */
35
    protected $include_options = true;
36
    
37
    /**
38
     *
39
     * @var array
40
     */
41
    protected static $localCache = array();
42
43
44
    /**
45
     *
46
     * @var boolean
47
     */
48
    protected $useLocalCaching = true;
49
50
    /**
51
     *
52
     * @var array
53
     */
54
    protected static $fullyCachedSchemas = array();
55
56
57
    /**
58
     * Constructor
59
     * 
60
     * @param \PDO|\mysqli $connection
61
     * @param string $schema default schema, taken from adapter if not given
62
     * @throws Exception\InvalidArgumentException for invalid connection
63
     * @throws Exception\InvalidUsageException thrown if no schema can be found.
64
     */
65 23
    public function __construct($connection, $schema = null)
66
    {
67
        try {
68 23
            $this->adapter = new MysqlConnectionAdapter($connection);
69 23
        } catch (Exception\InvalidArgumentException $e) {
70
            $msg = "MysqlInformationSchema requires a valid 'mysqli' or 'pdo:mysql' connection object ({$e->getMessage()}).";
71
            throw new Exception\InvalidArgumentException($msg);
72
        }
73
74 23
        if ($schema === null) {
75 23
            $schema = $this->adapter->getCurrentSchema();
76 23
            if ($schema === false || $schema == '') {
77
                $msg = "Database name (schema) parameter missing and no default schema set on connection";
78
                throw new Exception\InvalidUsageException($msg);
79
            }
80 23
        }
81
82 23
        $this->setDefaultSchema($schema);
83 23
    }
84
85
86
    /**
87
     * {@inheritdoc}
88
     */
89 1
    public function getUniqueKeys($table, $include_primary = false)
90
    {
91 1
        $this->loadCacheInformation($table);
92 1
        $uniques = (array) self::$localCache[$this->schema]['tables'][$table]['unique_keys'];
93 1
        if ($include_primary) {
94
            try {
95 1
                $pks = $this->getPrimaryKeys($table);
96 1
                if (count($pks) > 0) {
97 1
                    $uniques = array_merge($uniques, array('PRIMARY' => $pks));
98 1
                }
99 1
            } catch (Exception\NoPrimaryKeyException $e) {
100
                // Ignore exception
101
            }
102 1
        }
103 1
        return $uniques;
104
    }
105
106
107
    /**
108
     * {@inheritdoc}
109
     */
110
    public function getIndexesInformation($table)
111
    {
112
        $this->loadCacheInformation($table);
113
        return self::$localCache[$this->schema]['tables'][$table]['indexes'];
114
    }
115
116
    /**
117
     * {@inheritdoc}
118
     */
119 5
    public function getPrimaryKey($table)
120
    {
121 5
        $pks = $this->getPrimaryKeys($table);
122 2
        if (count($pks) > 1) {
123 1
            $keys = join(',', $pks);
124 1
            throw new Exception\MultiplePrimaryKeyException(__METHOD__ . ". Multiple primary keys found on table '{$this->schema}'.'$table':  $keys");
125
        }
126 1
        return $pks[0];
127
    }
128
129
130
    /**
131
     * {@inheritdoc}
132
     */
133 9
    public function getPrimaryKeys($table)
134
    {
135 9
        $this->loadCacheInformation($table);
136 6
        $pks = self::$localCache[$this->schema]['tables'][$table]['primary_keys'];
137 6
        if (count($pks) == 0) {
138 3
            throw new Exception\NoPrimaryKeyException(__METHOD__ . ". No primary keys found on table  '{$this->schema}'.'$table'.");
139
        }
140 4
        return $pks;
141
    }
142
143
144
    /**
145
     * {@inheritdoc}
146
     */
147 2
    public function getColumnsInformation($table)
148
    {
149 2
        $this->loadCacheInformation($table);
150 2
        return self::$localCache[$this->schema]['tables'][$table]['columns'];
151
    }
152
153
154
    /**
155
     * {@inheritdoc}
156
     */
157 1
    public function getForeignKeys($table)
158
    {
159 1
        $this->loadCacheInformation($table);
160 1
        return self::$localCache[$this->schema]['tables'][$table]['foreign_keys'];
161
    }
162
    
163
    /**
164
     * {@inheritdoc}
165
     */
166 1
    public function getReferences($table)
167
    {
168 1
        $this->loadCacheInformation($table);
169 1
        return self::$localCache[$this->schema]['tables'][$table]['references'];
170
    }
171
172
    /**
173
     * {@inheritdoc}
174
     */
175 3
    public function getTablesInformation()
176
    {
177 3
        $this->loadCacheInformation(null);
178 3
        return self::$localCache[$this->schema]['tables'];
179
    }
180
181
    /**
182
     * Get a table configuration
183
     *
184
     * @throws Exception\ErrorException
185
     * @throws Exception\TableNotFoundException
186
     *
187
     * @param string $table table name
188
     * @param boolean|null $include_options include extended information
189
     * @return array
190
     */
191 13
    protected function getTableConfig($table, $include_options = null)
192
    {
193 13
        if ($include_options === null) {
194 13
            $include_options = $this->include_options;
195 13
        }
196
        
197 13
        $schema = $this->schema;
198
199 13
        if ($this->useLocalCaching &&
200 13
                isset(self::$localCache[$schema]['tables'][$table])) {
201 9
            return self::$localCache[$schema]['tables'][$table];
202
        }
203
204 4
        $config = $this->getObjectConfig($table, $include_options);
205
206 4
        if (!array_key_exists($table, $config['tables'])) {
207 2
            throw new Exception\TableNotFoundException(__METHOD__ . ". Table '$table' in database schema '{$schema}' not found.");
208
        }
209
210 2
        if ($this->useLocalCaching) {
211 2
            if (!array_key_exists($schema, self::$localCache)) {
212 1
                self::$localCache[$schema] = array();
213 1
            }
214 2
            self::$localCache[$schema] = array_merge_recursive(self::$localCache[$schema], $config);
215 2
        }
216
217 2
        return $config['tables'][$table];
218
    }
219
220
221
    /**
222
     * Get schema configuration
223
     *
224
     * @throws Exception\ErrorException
225
     * @throws Exception\SchemaNotFoundException
226
     *
227
     * @param boolean|null $include_options include extended information
228
     * @return array
229
     */
230 2
    protected function getSchemaConfig($include_options = null)
231
    {
232 2
        if ($include_options === null) {
233 2
            $include_options = $this->include_options;
234 2
        }
235 2
        $schema = $this->schema;
236 2
        if ($this->useLocalCaching && in_array($schema, self::$fullyCachedSchemas)) {
237
            return self::$localCache[$schema];
238
        }
239
240 2
        $config = $this->getObjectConfig($table = null, $include_options);
241 2
        if (count($config['tables']) == 0) {
242 1
            throw new Exception\SchemaNotFoundException(__METHOD__ . " Error: schema '{$schema}' not found or without any table or view");
243
        }
244 1
        if ($this->useLocalCaching) {
245 1
            self::$localCache[$schema] = $config;
246 1
            self::$fullyCachedSchemas[] = $schema;
247 1
        }
248 1
        return $config;
249
    }
250
251
    /**
252
     * Return object (table/schema) configuration
253
     *
254
     * @throws Exception\ErrorException
255
     *
256
     * @param string $table
257
     * @param boolean|null $include_options
258
     * @return array
259
     */
260 6
    protected function getObjectConfig($table = null, $include_options = null)
261
    {
262 6
        if ($include_options === null) {
263
            $include_options = $this->include_options;
264
        }
265
        
266 6
        $schema = $this->schema;
267 6
        $qSchema = $this->adapter->quoteValue($schema);
268
269 6
        if ($table !== null) {
270 4
            $qTable = $this->adapter->quoteValue($table);
271 4
            $table_clause = "and (t.TABLE_NAME = $qTable or (kcu.referenced_table_name = $qTable and kcu.constraint_name = 'FOREIGN KEY'))";
272 4
            $table_join_condition = "(t.table_name = kcu.table_name or  kcu.referenced_table_name = t.table_name)";
273 4
        } else {
274 2
            $table_join_condition = "t.table_name = kcu.table_name";
275 2
            $table_clause = '';
276
        }
277
278
        $query = "
279
280
            SELECT
281
                    t.table_name,
282
                    c.column_name,
283
                    c.data_type,
284
                    c.column_type,
285
286
                    c.extra,
287
288
                    tc.constraint_type,
289
                    kcu.constraint_name,
290
                    kcu.referenced_table_name,
291
                    kcu.referenced_column_name,
292
293
                    c.column_default,
294
                    c.is_nullable,
295
                    c.numeric_precision,
296
                    c.numeric_scale,
297
                    c.character_octet_length,
298
                    c.character_maximum_length,
299
                    c.ordinal_position,
300
301
                    c.column_key, -- UNI/MUL/PRI
302
                    c.character_set_name,
303
304
305
                    c.collation_name,
306
307
                    c.column_comment,
308
309
                    t.table_type,
310
                    t.engine,
311
                    t.table_comment,
312
                    t.table_collation
313
314
            FROM `INFORMATION_SCHEMA`.`COLUMNS` c
315
            INNER JOIN `INFORMATION_SCHEMA`.`TABLES` t on c.TABLE_NAME = t.TABLE_NAME
316
            LEFT JOIN `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` kcu
317
               on (
318
                    $table_join_condition
319
                     and kcu.table_schema = t.table_schema
320
                     and kcu.column_name = c.column_name
321
                 )
322
              LEFT JOIN
323
                `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` tc
324
               on (
325
                     t.table_name = tc.table_name
326
                      and tc.table_schema = t.table_schema
327
                      and tc.constraint_name = kcu.constraint_name
328
                  )
329
330
331 6
            where c.TABLE_SCHEMA = $qSchema
332 6
            and t.TABLE_SCHEMA = $qSchema
333 6
            $table_clause
334 6
            and (kcu.table_schema = $qSchema  or kcu.table_schema is null)
335
336
            and (kcu.column_name = c.column_name or kcu.column_name is null)
337
            order by t.table_name, c.ordinal_position
338 6
        ";
339
340 6
        $this->disableInnoDbStats();
341
        try {
342 6
            $results = $this->adapter->query($query);
343 6
        } catch (\Exception $e) {
344
            //@codeCoverageIgnoreStart
345
            $this->restoreInnoDbStats();
346
            throw new Exception\ErrorException(__METHOD__ . ": " . $e->getMessage());
347
            //@codeCoverageIgnoreEnd
348
        }
349 6
        $this->restoreInnoDbStats();
350
351 6
        $references = array();
352 6
        $config = new Config(array('tables' => array()), true);
353 6
        $tables = $config->offsetGet('tables');
354
355
356 6
        foreach ($results as $r) {
357
            // Setting table information
358 3
            $table_name = $r['table_name'];
359 3
            if (!$tables->offsetExists($table_name)) {
360
                $table_def = array(
361 3
                    'name'          => $table_name,
362 3
                    'columns'       => array(),
363 3
                    'primary_keys'  => array(),
364 3
                    'unique_keys'   => array(),
365 3
                    'foreign_keys'  => array(),
366 3
                    'references'    => array(),
367 3
                    'indexes'       => array(),
368 3
                );
369 3
                if ($include_options) {
370 3
                    $table_def['options'] = array(
371 3
                       'comment'   => $r['table_comment'],
372 3
                       'collation' => $r['table_collation'],
373 3
                       'type'      => $r['table_type'],
374 3
                       'engine'    => $r['engine']
375 3
                    );
376 3
                }
377 3
                $tables->offsetSet($table_name, $table_def);
378 3
            }
379 3
            $table   = $tables->offsetGet($table_name);
380 3
            $columns = $table->columns;
381 3
            $column_name = $r['column_name'];
382
383 3
            $data_type = strtolower($r['data_type']);
384
385
            $col_def = array(
386 3
                'type'          => $data_type,
387 3
                'primary'       => ($r['constraint_type'] == 'PRIMARY KEY'),
388 3
                'nullable'      => ($r['is_nullable'] == 'YES'),
389 3
                'default'       => $r['column_default']
390 3
            );
391 3
            if (($r['constraint_type'] == 'PRIMARY KEY')) {
392 2
                $col_def['primary'] = true;
393 2
                $col_def['autoincrement'] = ($r['extra'] == 'auto_increment');
394 2
            }
395
396 3
            $has_charset = false;
397 3
            if (in_array($data_type, array('int', 'tinyint', 'mediumint', 'bigint', 'int', 'smallint', 'year'))) {
398 3
                $col_def['unsigned']  = (bool) preg_match('/unsigned/', strtolower($r['column_type']));
399 3
                $col_def['precision'] = is_numeric($r['numeric_precision']) ? (int) $r['numeric_precision'] : null;
400 3
            } elseif (in_array($data_type, array('real', 'double precision', 'decimal', 'numeric', 'float', 'dec', 'fixed'))) {
401 1
                $col_def['precision'] = is_numeric($r['numeric_precision']) ? (int) $r['numeric_precision'] : null;
402 1
                $col_def['scale']     = is_numeric($r['numeric_scale']) ? (int) $r['numeric_scale'] : null;
403 2
            } elseif (in_array($data_type, array('timestamp', 'date', 'time', 'datetime'))) {
404
                // nothing yet
405 2
            } elseif (in_array($data_type, array('char', 'varchar', 'binary', 'varbinary', 'text', 'tinytext', 'mediumtext', 'longtext'))) {
406 2
                $col_def['octet_length'] = is_numeric($r['character_octet_length']) ? (int) $r['character_octet_length'] : null;
407 2
                $col_def['length'] = is_numeric($r['character_maximum_length']) ? (int) $r['character_maximum_length'] : null;
408 2
                $has_charset = true;
409 2
            } elseif (in_array($data_type, array('blob', 'tinyblob', 'mediumblob', 'longblob'))) {
410 1
                $col_def['octet_length'] = (int) $r['character_octet_length'];
411 1
                $col_def['length'] = (int) $r['character_maximum_length'];
412 1
            } elseif (in_array($data_type, array('enum', 'set'))) {
413 1
                $col_def['octet_length'] = (int) $r['character_octet_length'];
414 1
                $col_def['length'] = (int) $r['character_maximum_length'];
415 1
                $def = $r['column_type'];
416
417 1
                preg_match_all("/'([^']+)'/", $def, $matches);
418 1
                if (is_array($matches[1]) && count($matches) > 0) {
419 1
                    $col_def['values'] = $matches[1];
420 1
                }
421 1
            }
422
423 3
            if ($include_options) {
424 3
                $col_def['options'] = array(
425 3
                        'comment'           => $r['column_comment'],
426 3
                        'definition'        => $r['column_type'],
427 3
                        'column_key'        => $r['column_key'],
428 3
                        'ordinal_position'  => $r['ordinal_position'],
429 3
                        'constraint_type'   => $r['constraint_type'], // 'PRIMARY KEY', 'FOREIGN_KEY', 'UNIQUE'
430
                    );
431 3
                if ($has_charset) {
432 2
                    $col_def['options']['charset']     = $r['character_set_name'];
433 2
                    $col_def['options']['collation']   = $r['collation_name'];
434 2
                }
435 3
            }
436
437 3
            $columns[$column_name] = $col_def;
438
439 3
            $foreign_keys = $table->foreign_keys;
440 3
            $unique_keys  = $table->unique_keys;
441
442 3
            $constraint_name = $r['constraint_name'];
443 3
            $referenced_table_name = $r['referenced_table_name'];
444 3
            $referenced_column_name = $r['referenced_column_name'];
445 3
            switch ($r['constraint_type']) {
446 3
                case 'PRIMARY KEY':
447 2
                    $table->primary_keys = array_merge($table->primary_keys->toArray(), (array) $column_name);
448 2
                    break;
449 3
                case 'UNIQUE':
450 2
                    if (!$unique_keys->offsetExists($constraint_name)) {
451 2
                        $unique_keys[$constraint_name] = array();
452 2
                    }
453 2
                    $unique_keys[$constraint_name] = array_merge($unique_keys[$constraint_name]->toArray(), (array) $column_name);
454 2
                    break;
455 3
                case 'FOREIGN KEY':
456
                    /*
457
                    if (!$foreign_keys->offsetExists($constraint_name)) {
458
                        $foreign_keys[$constraint_name] = array();
459
                    }
460
                     *
461
                     */
462
                    $fk = array(
463 1
                       'referenced_table'  => $referenced_table_name,
464 1
                       'referenced_column' => $referenced_column_name,
465
                       'constraint_name' => $constraint_name
466 1
                    );
467 1
                    $foreign_keys[$column_name] = $fk;
468
                    //$table->references[$referenced_table_name] = array($column_name => $r['referenced_column_name']);
469
470 1
                    if (!array_key_exists($referenced_table_name, $references)) {
471 1
                        $references[$referenced_table_name] = array();
472 1
                    }
473
474 1
                    $k = "$table_name:$referenced_column_name->$column_name";
475 1
                    $references[$referenced_table_name][$k] = array(
476 1
                        'column' => $column_name,
477 1
                        'referencing_table' => $table_name,
478 1
                        'referencing_column' => $referenced_column_name,
479
                        'constraint_name' => $constraint_name
480 1
                    );
481 1
                    break;
482 3
            }
483 6
        }
484
485 6
        foreach ($references as $referenced_table_name => $refs) {
486 1
            if ($tables->offsetExists($referenced_table_name)) {
487 1
                $table = $tables[$referenced_table_name];
488 1
                $table->references = $refs;
489 1
            }
490 6
        }
491
492 6
        $array = $config->toArray();
493 6
        unset($config);
494 6
        return $array;
495
496
    }
497
498
    /**
499
     * Disable innodbstats will increase speed of metadata lookups
500
     *
501
     * @return void
502
     */
503 6
    protected function disableInnoDbStats()
504
    {
505 6
        $sql = "show global variables like 'innodb_stats_on_metadata'";
506
        try {
507 6
            $results = $this->adapter->query($sql);
508 6
            if (count($results) > 0) {
509 6
                $row = $results->offsetGet(0);
510
511 6
                $value = strtoupper($row['Value']);
512
                // if 'on' no need to do anything
513 6
                if ($value != 'OFF') {
514 6
                    $this->mysql_innodbstats_value = $value;
515
                    // disabling innodb_stats
516 6
                    $this->adapter->execute("set global innodb_stats_on_metadata='OFF'");
517 6
                }
518 6
            }
519 6
        } catch (\Exception $e) {
520
            // do nothing, silently fallback
521
        }
522 6
    }
523
524
525
    /**
526
     * Restore old innodbstats variable
527
     * @return void
528
     */
529 6
    protected function restoreInnoDbStats()
530
    {
531 6
        $value = $this->mysql_innodbstats_value;
532 6
        if ($value !== null) {
533
            // restoring old variable
534 6
            $this->adapter->execute("set global innodb_stats_on_metadata='$value'");
535 6
        }
536 6
    }
537
538
539
    /**
540
     *
541
     * @param string $table
542
     * @throws Exception\InvalidArgumentException
543
     * @throws Exception\TableNotFoundException
544
     *
545
     */
546 16
    protected function loadCacheInformation($table = null)
547
    {
548 16
        $schema = $this->schema;
549 16
        $this->checkTableArgument($table);
550
551 14
        if (!in_array($schema, self::$fullyCachedSchemas)) {
552 3
            if ($table !== null) {
553 3
                $this->getTableConfig($table);
554 3
            } else {
555
                $this->getSchemaConfig();
556
            }
557 14
        } elseif ($table !== null) {
558
            // Just in case to check if table exists
559 8
            $this->getTableConfig($table);
560 7
        }
561 13
    }
562
563
    /**
564
     * Clear local cache information for the current schema
565
     *
566
     * @throws Exception\InvalidArgumentException
567
     */
568 1
    public function clearCacheInformation()
569
    {
570 1
        $schema = $this->schema;
571 1
        if (array_key_exists($schema, self::$localCache)) {
572 1
            unset(self::$localCache[$schema]);
573 1
            if (($key = array_search($schema, self::$fullyCachedSchemas)) !== false) {
574 1
                unset(self::$fullyCachedSchemas[$key]);
575 1
            }
576 1
        }
577 1
    }
578
}
579