MysqlInformationSchema::getSchemaConfig() - Code Metrics - Inspection of "Fixed doc" - belgattitude/soluble-schema - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( df6ecc...e437e0 )

by Sébastien

created 2015-12-29 00:15 UTC

MysqlInformationSchema::getSchemaConfig() B

↳ Parent: MysqlInformationSchema

Complexity

Conditions	5
Paths	4

Size

Total Lines	17
Code Lines	11

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	11
CRAP Score	5.0145

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	17
ccs	11
cts	12
cp	0.9167
rs	8.8571
cc	5
eloc	11
nc	4
nop	1
crap	5.0145

<?php
namespace Soluble\Schema\Source\Mysql;

use Soluble\Schema\Exception;
use Soluble\Schema\Source;
use Soluble\Schema\Source\Mysql\MysqlConnectionWrapper;
use Zend\Config\Config;

class MysqlInformationSchema extends Source\AbstractSource
{
    /**
     * Schema name
     *
     * @var string
     */
    protected $schema;

    /**
     * @var MysqlConnectionAdapter
     */
    protected $adapter;



    /**
     * Used to restore innodb stats mysql global variable
     * @var string
     */
    protected $mysql_innodbstats_value;

    /**
     *
     * @var array
     */
    protected static $localCache = array();


    /**
     *
     * @var boolean
     */
    protected $useLocalCaching = true;

    /**
     *
     * @var array
     */
    protected static $fullyCachedSchemas = array();


    /**
     *
     * @param \PDO|\mysqli $connection
     * @param string $schema default schema, taken from adapter if not given
     * @throws Exception\InvalidArgumentException for invalid connection
     * @throws Exception\InvalidUsageException thrown if no schema can be found.
     */
    public function __construct($connection, $schema = null)
    {
        try {
            $this->adapter = new MysqlConnectionAdapter($connection);
        } catch (Exception\InvalidArgumentException $e) {
            $msg = "MysqlInformationSchema requires a valid 'mysqli' or 'pdo:mysql' connection object ({$e->getMessage()}).";
            throw new Exception\InvalidArgumentException($msg);
        }

        if ($schema === null) {
            $schema = $this->adapter->getCurrentSchema();
            if ($schema === false || $schema == '') {
                $msg = "Database name (schema) parameter missing and no default schema set on connection";
                throw new Exception\InvalidUsageException($msg);
            }
        }

        $this->setDefaultSchema($schema);
    }


    /**
     * Return all uniques keys defined for a table.
     *
     * By default it does not include the primary key, simply set
     * the $include_primary parameter to true to get it. In this case
     * the associative key will be 'PRIMARY'.
     *
     * If no unique keys can be found returns an empty array
     *
     *
     * @param string $table table name
     * @param boolean $include_primary include primary keys in the list (indexed as PRIMARY)
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\ExceptionInterface
     * @throws Exception\TableNotFoundException
     * @return array associative array 'index_name' => ['col1', 'col2'], 'index_name_2' => ['col3']
     */
    public function getUniqueKeys($table, $include_primary = false)
    {
        $this->loadCacheInformation($table);
        $uniques = (array) self::$localCache[$this->schema]['tables'][$table]['unique_keys'];
        if ($include_primary) {
            try {
                $pks = $this->getPrimaryKeys($table);
                if (count($pks) > 0) {
                    $uniques = array_merge($uniques, array('PRIMARY' => $pks));
                }
            } catch (Exception\NoPrimaryKeyException $e) {
                // Ignore exception
            }
        }
        return $uniques;
    }


    /**
     * Return indexes information on a table
     *
     * @param string $table table name
     *
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\ExceptionInterface
     * @throws Exception\TableNotFoundException
     *
     * @return array
     */
    public function getIndexesInformation($table)
    {
        $this->loadCacheInformation($table);
        return self::$localCache[$this->schema]['tables'][$table]['indexes'];
    }

    /**
     * Return unique table primary key
     *
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\NoPrimaryKeyException when no pk
     * @throws Exception\MultiplePrimaryKeyException when multiple pk found
     * @throws Exception\ExceptionInterface
     * @throws Exception\TableNotFoundException
     *
     * @param string $table
     *
     * @return string|int primary key
     */
    public function getPrimaryKey($table)
    {
        $pks = $this->getPrimaryKeys($table);
        if (count($pks) > 1) {
            $keys = join(',', $pks);
            throw new Exception\MultiplePrimaryKeyException(__METHOD__ . ". Multiple primary keys found on table '{$this->schema}'.'$table':  $keys");
        }
        return $pks[0];
    }


    /**
     * Return composite primary keys
     *
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\NoPrimaryKeyException
     * @throws Exception\ExceptionInterface
     * @throws Exception\TableNotFoundException
     *
     * @param string $table
     *
     * @return array primary keys
     */
    public function getPrimaryKeys($table)
    {
        $this->loadCacheInformation($table);
        $pks = self::$localCache[$this->schema]['tables'][$table]['primary_keys'];
        if (count($pks) == 0) {
            throw new Exception\NoPrimaryKeyException(__METHOD__ . ". No primary keys found on table  '{$this->schema}'.'$table'.");
        }
        return $pks;
    }


    /**
     * Return column information
     *
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\ExceptionInterface
     * @throws Exception\TableNotFoundException
     *
     * @param string $table
     * @return array associative array [column_name => infos]
     */
    public function getColumnsInformation($table)
    {
        $this->loadCacheInformation($table);
        return self::$localCache[$this->schema]['tables'][$table]['columns'];
    }


    /**
     * Return relations information
     *
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\ExceptionInterface
     * @throws Exception\TableNotFoundException
     *
     * @param string $table
     *
     * @return array
     */
    public function getRelations($table)
    {
        $this->loadCacheInformation($table);
        return self::$localCache[$this->schema]['tables'][$table]['foreign_keys'];
    }

    /**
     * Return table informations
     *
     * @throws Exception\InvalidArgumentException
     * @throws Exception\ErrorException
     * @throws Exception\ExceptionInterface
     *
     * @return array associative array indexed by table_name
     */
    public function getTablesInformation()
    {
        $this->loadCacheInformation(null);
        return self::$localCache[$this->schema]['tables'];
    }



    /**
     * Get a table configuration
     *
     * @throws Exception\ErrorException
     * @throws Exception\TableNotFoundException
     *
     * @param string $table table name
     * @param boolean $include_options include extended information
     * @return array
     */
    public function getTableConfig($table, $include_options = false)
    {
        $schema = $this->schema;

        if ($this->useLocalCaching &&
                isset(self::$localCache[$schema]['tables'][$table])) {
            return self::$localCache[$schema]['tables'][$table];
        }


        $config = $this->getObjectConfig($table, $include_options);

        if (!array_key_exists($table, $config['tables'])) {
            throw new Exception\TableNotFoundException(__METHOD__ . ". Table '$table' in database schema '{$schema}' not found.");
        }

        if ($this->useLocalCaching) {
            if (!array_key_exists($schema, self::$localCache)) {
                self::$localCache[$schema] = array();
            }
            self::$localCache[$schema] = array_merge_recursive(self::$localCache[$schema], $config);
        }

        return $config['tables'][$table];
    }


    /**
     * Get schema configuration
     *
     * @throws Exception\ErrorException
     * @throws Exception\SchemaNotFoundException
     *
     * @param boolean $include_options include extended information
     * @return array
     */
    public function getSchemaConfig($include_options = false)
    {
        $schema = $this->schema;
        if ($this->useLocalCaching && in_array($schema, self::$fullyCachedSchemas)) {
            return self::$localCache[$schema];
        }

        $config = $this->getObjectConfig($table = null, $include_options);
        if (count($config['tables']) == 0) {
            throw new Exception\SchemaNotFoundException(__METHOD__ . " Error: schema '{$schema}' not found or without any table or view");
        }
        if ($this->useLocalCaching) {
            self::$localCache[$schema] = $config;
            self::$fullyCachedSchemas[] = $schema;
        }
        return $config;
    }

    /**
     * Return object (table/schema) configuration
     *
     * @throws Exception\ErrorException
     *
     * @param string $table
     * @param boolean $include_options
     * @return array
     */
    protected function getObjectConfig($table = null, $include_options = false)
    {
        $schema = $this->schema;
        $qSchema = $this->adapter->quoteValue($schema);

        if ($table !== null) {
            $qTable = $this->adapter->quoteValue($table);
            $table_clause = "and (t.TABLE_NAME = $qTable or (kcu.referenced_table_name = $qTable and kcu.constraint_name = 'FOREIGN KEY'))";
            $table_join_condition = "(t.table_name = kcu.table_name or  kcu.referenced_table_name = t.table_name)";
        } else {
            $table_join_condition = "t.table_name = kcu.table_name";
            $table_clause = '';
        }

        $query = "

            SELECT
                    t.table_name,
                    c.column_name,
                    c.data_type,
                    c.column_type,

                    c.extra,

                    tc.constraint_type,
                    kcu.constraint_name,
                    kcu.referenced_table_name,
                    kcu.referenced_column_name,

                    c.column_default,
                    c.is_nullable,
                    c.numeric_precision,
                    c.numeric_scale,
                    c.character_octet_length,
                    c.character_maximum_length,
                    c.ordinal_position,

                    c.column_key, -- UNI/MUL/PRI
                    c.character_set_name,


                    c.collation_name,

                    c.column_comment,

                    t.table_type,
                    t.engine,
                    t.table_comment,
                    t.table_collation

            FROM `INFORMATION_SCHEMA`.`COLUMNS` c
            INNER JOIN `INFORMATION_SCHEMA`.`TABLES` t on c.TABLE_NAME = t.TABLE_NAME
            LEFT JOIN `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` kcu
               on (
                    $table_join_condition
                     and kcu.table_schema = t.table_schema
                     and kcu.column_name = c.column_name
                 )
              LEFT JOIN
                `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` tc
               on (
                     t.table_name = tc.table_name
                      and tc.table_schema = t.table_schema
                      and tc.constraint_name = kcu.constraint_name
                  )


            where c.TABLE_SCHEMA = $qSchema
            and t.TABLE_SCHEMA = $qSchema
            $table_clause
            and (kcu.table_schema = $qSchema  or kcu.table_schema is null)

            and (kcu.column_name = c.column_name or kcu.column_name is null)
            order by t.table_name, c.ordinal_position
        ";

        $this->disableInnoDbStats();
        try {
            $results = $this->adapter->query($query);
        } catch (\Exception $e) {
            //@codeCoverageIgnoreStart
            $this->restoreInnoDbStats();
            throw new Exception\ErrorException(__METHOD__ . ": " . $e->getMessage());
            //@codeCoverageIgnoreEnd
        }
        $this->restoreInnoDbStats();

        $references = array();
        $config = new Config(array('tables' => array()), true);
        $tables = $config->offsetGet('tables');


        foreach ($results as $r) {
            // Setting table information
            $table_name = $r['table_name'];
            if (!$tables->offsetExists($table_name)) {
                $table_def = array(
                    'name'          => $table_name,
                    'columns'       => array(),
                    'primary_keys'  => array(),
                    'unique_keys'   => array(),
                    'foreign_keys'  => array(),
                    'references'    => array(),
                    'indexes'       => array(),
                );
                if ($include_options) {
                    $table_def['options'] = array(
                       'comment'   => $r['table_comment'],
                       'collation' => $r['table_collation'],
                       'type'      => $r['table_type'],
                       'engine'    => $r['engine']
                    );
                }
                $tables->offsetSet($table_name, $table_def);
            }
            $table   = $tables->offsetGet($table_name);
            $columns = $table->columns;
            $column_name = $r['column_name'];

            $data_type = strtolower($r['data_type']);

            $col_def = array(
                'type'          => $data_type,
                'primary'       => ($r['constraint_type'] == 'PRIMARY KEY'),
                'nullable'      => ($r['is_nullable'] == 'YES'),
                'default'       => $r['column_default']
            );
            if (($r['constraint_type'] == 'PRIMARY KEY')) {
                $col_def['primary'] = true;
                $col_def['autoincrement'] = ($r['extra'] == 'auto_increment');
            }

            $has_charset = false;
            if (in_array($data_type, array('int', 'tinyint', 'mediumint', 'bigint', 'int', 'smallint', 'year'))) {
                $col_def['unsigned']  = (bool) preg_match('/unsigned/', strtolower($r['column_type']));
                $col_def['precision'] = $r['numeric_precision'];
            } elseif (in_array($data_type, array('real', 'double precision', 'decimal', 'numeric', 'float', 'dec', 'fixed'))) {
                $col_def['precision'] = $r['numeric_precision'];
                $col_def['scale']     = $r['numeric_scale'];
            } elseif (in_array($data_type, array('timestamp', 'date', 'time', 'datetime'))) {
                // nothing yet
            } elseif (in_array($data_type, array('char', 'varchar', 'binary', 'varbinary', 'text', 'tinytext', 'mediumtext', 'longtext'))) {
                $col_def['octet_length'] = $r['character_octet_length'];
                $col_def['length'] = $r['character_maximum_length'];
                $has_charset = true;
            } elseif (in_array($data_type, array('blob', 'tinyblob', 'mediumblob', 'longblob'))) {
                $col_def['octet_length'] = $r['character_octet_length'];
                $col_def['length'] = $r['character_maximum_length'];
            } elseif (in_array($data_type, array('enum', 'set'))) {
                $col_def['octet_length'] = $r['character_octet_length'];
                $col_def['length'] = $r['character_maximum_length'];
                $def = $r['column_type'];

                preg_match_all("/'([^']+)'/", $def, $matches);
                if (is_array($matches[1]) && count($matches) > 0) {
                    $col_def['values'] = $matches[1];
                }
            }

            if ($include_options) {
                $col_def['options'] = array(
                        'comment'           => $r['column_comment'],
                        'definition'        => $r['column_type'],
                        'column_key'        => $r['column_key'],
                        'ordinal_position'  => $r['ordinal_position'],
                        'constraint_type'   => $r['constraint_type'], // 'PRIMARY KEY', 'FOREIGN_KEY', 'UNIQUE'
                    );
                if ($has_charset) {
                    $col_def['options']['charset']     = $r['character_set_name'];
                    $col_def['options']['collation']   = $r['collation_name'];
                }
            }

            $columns[$column_name] = $col_def;

            $foreign_keys = $table->foreign_keys;
            $unique_keys  = $table->unique_keys;

            $constraint_name = $r['constraint_name'];
            $referenced_table_name = $r['referenced_table_name'];
            $referenced_column_name = $r['referenced_column_name'];
            switch ($r['constraint_type']) {
                case 'PRIMARY KEY':
                    $table->primary_keys = array_merge($table->primary_keys->toArray(), (array) $column_name);
                    break;
                case 'UNIQUE':
                    if (!$unique_keys->offsetExists($constraint_name)) {
                        $unique_keys[$constraint_name] = array();
                    }
                    $unique_keys[$constraint_name] = array_merge($unique_keys[$constraint_name]->toArray(), (array) $column_name);
                    break;
                case 'FOREIGN KEY':
                    /*
                    if (!$foreign_keys->offsetExists($constraint_name)) {
                        $foreign_keys[$constraint_name] = array();
                    }
                     *
                     */
                    $fk = array(
                       'referenced_table'  => $referenced_table_name,
                       'referenced_column' => $referenced_column_name,
                       'constraint_name' => $constraint_name
                    );
                    $foreign_keys[$column_name] = $fk;
                    //$table->references[$referenced_table_name] = array($column_name => $r['referenced_column_name']);

                    if (!array_key_exists($referenced_table_name, $references)) {
                        $references[$referenced_table_name] = array();
                    }

                    $references[$referenced_table_name][] = array(
                        'column' => $column_name,
                        //'referenced_table' => $table_name,
                        'referenced_column' => $referenced_column_name,
                        'constraint_name' => $constraint_name
                    );
                    break;
            }
        }

        foreach ($references as $referenced_table_name => $refs) {
            if ($tables->offsetExists($referenced_table_name)) {
                $table = $tables[$referenced_table_name];
                $references = $table->references;
                $references[$referenced_table_name] = $refs;
            }
        }

        $array = $config->toArray();
        unset($config);
        return $array;

    }

    /**
     * Disable innodbstats will increase speed of metadata lookups
     *
     * @return void
     */
    protected function disableInnoDbStats()
    {
        $sql = "show global variables like 'innodb_stats_on_metadata'";
        try {
            $results = $this->adapter->query($sql);
            if (count($results) > 0) {
                $row = $results->offsetGet(0);

                $value = strtoupper($row['Value']);
                // if 'on' no need to do anything
                if ($value != 'OFF') {
                    $this->mysql_innodbstats_value = $value;
                    // disabling innodb_stats
                    $this->adapter->query("set global innodb_stats_on_metadata='OFF'");
                }
            }
        } catch (\Exception $e) {
            // do nothing, silently fallback
        }
    }


    /**
     * Restore old innodbstats variable
     * @return void
     */
    protected function restoreInnoDbStats()
    {
        $value = $this->mysql_innodbstats_value;
        if ($value !== null) {
            // restoring old variable
            $this->adapter->query("set global innodb_stats_on_metadata='$value'");
        }
    }


    /**
     *
     * @param string $table
     * @throws Exception\InvalidArgumentException
     * @throws Exception\TableNotFoundException
     *
     */
    protected function loadCacheInformation($table = null)
    {
        $schema = $this->schema;
        $this->checkTableArgument($table);

        if (!in_array($schema, self::$fullyCachedSchemas)) {
            if ($table !== null) {
                $this->getTableConfig($table);
            } else {
                $this->getSchemaConfig();
            }
        } elseif ($table !== null) {
            // Just in case to check if table exists
            $this->getTableConfig($table);
        }
    }

    /**
     * Clear local cache information for the current schema
     *
     * @throws Exception\InvalidArgumentException
     */
    public function clearCacheInformation()
    {
        $schema = $this->schema;
        if (array_key_exists($schema, self::$localCache)) {
            unset(self::$localCache[$schema]);
            if (($key = array_search($schema, self::$fullyCachedSchemas)) !== false) {
                unset(self::$fullyCachedSchemas[$key]);
            }
        }
    }
}


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

belgattitude / soluble-schema

Push — master ( df6ecc...e437e0 )

MysqlInformationSchema::getSchemaConfig() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like