MysqlPdo::getTables() - Code Metrics - terah/fluent-pdo-model - Measure and Improve Code Quality continuously with Scrutinizer

MysqlPdo::getTables() A
last analyzed 2018-05-04 04:46 UTC

↳ Parent: MysqlPdo

Complexity

Conditions	2
Paths	2

Size

Total Lines	12
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
cc	2
eloc	9
nc	2
nop	1
dl	0
loc	12
rs	9.4285
c	1
b	0
f	0

<?php declare(strict_types=1);
/**
 * @package Terah\FluentPdoModel
 *
 * Licensed under The MIT License
 * For full copyright and license information, please see the LICENSE.txt
 * Redistributions of files must retain the above copyright notice.
 *
 * @license       http://www.opensource.org/licenses/mit-license.php MIT License
 */

namespace Terah\FluentPdoModel\Drivers;

use \PDO;
use Psr\Log\LoggerInterface;
use Psr\Log\NullLogger;
use Terah\FluentPdoModel\Column;
use Terah\FluentPdoModel\FluentPdoModel;
use Terah\FluentPdoModel\ForeignKey;
use Terah\RedisCache\CacheInterface;
use Terah\RedisCache\NullCache;

/**
 * Class MysqlPdo
 *
 * @package Terah\FluentPdoModel\Drivers
 * @author  Terry Cullen - [email protected]
 */
class MysqlPdo extends AbstractPdo implements DriverInterface
{
    /** @var bool  */
    protected $_supportsColumnMeta = true;

    /**
     * @param string          $dsn
     * @param string          $username
     * @param string          $password
     * @param array           $options
     * @param LoggerInterface $logger
     * @param CacheInterface   $cache
     */
    public function __construct(string $dsn, string $username='', string $password='', array $options=[], LoggerInterface $logger=null, CacheInterface $cache=null)
    {
        parent::__construct($dsn, $username, $password, $options);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        $this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
        if ( array_key_exists('timeout', $options) && $options['timeout'] )
        {
            $this->setAttribute(PDO::ATTR_TIMEOUT, (int)$options['timeout']);
        }
        $this->exec("SET NAMES utf8 COLLATE utf8_unicode_ci");
        $this->setConfig($options, $dsn);
        $this->setLogger($logger ? $logger : new NullLogger());
        $this->setCache($cache ? $cache : new NullCache());
    }

    /**
     * @param bool $include_views
     * @return string[]
     */
    public function getTables(bool $include_views=false) : array
    {
        $this->exec('FLUSH TABLES');
        $types      = $include_views ? ['VIEW', 'BASE TABLE'] : ['BASE TABLE'];
        return (new FluentPdoModel($this))
            ->table('information_schema.tables', 't')
           // ->select('t.TABLE_NAME AS table_name')
            ->where('t.table_schema', $this->getConfig('dbname'))
            ->whereIn('t.table_type', $types)
            ->orderBy('t.table_type')
            ->fetchColumn('table_name');
    }

    /**
     * @param bool $include_views
     * @param string $table
     * @return Column[][]
     */
    public function getColumns(bool $include_views=false, string $table='') : array
    {
        $this->exec('FLUSH TABLES');
        $types      = $include_views ? ['VIEW', 'BASE TABLE'] : ['BASE TABLE'];
        $query      = (new FluentPdoModel($this))
            ->table('information_schema.columns', 'c')
            ->select([
                'c.TABLE_NAME               as table_name',
                'c.COLUMN_NAME              as column_name',
                'c.IS_NULLABLE              as is_nullable',
                'c.DATA_TYPE                as data_type',
                'c.CHARACTER_MAXIMUM_LENGTH as character_maximum_length',
                'c.NUMERIC_PRECISION        as numeric_precision',
                'c.COLUMN_TYPE              as column_type',
                'c.COLUMN_COMMENT           as comment',
            ])
            ->leftJoin('information_schema.tables', 'c.table_name = t.table_name AND c.table_schema = t.table_schema', 't')
            ->where('t.table_schema', $this->getConfig('dbname'))
            ->whereIn('t.table_type', $types);
        if ( $table )
        {
            $query->where('t.table_name', $table);
        }
        $columns = [];
        $query->fetchCallback(function(\stdClass $record) use (&$columns) {

            $column                                             = new Column();
            $column->tableName                                  = $record->table_name;
            $column->columnName                                 = $record->column_name;
            $column->isNullable                                 = $record->is_nullable;
            $column->dataType                                   = $record->data_type;
            $column->maxLength                                  = $record->character_maximum_length;
            $column->precision                                  = $record->numeric_precision;
            $column->columnType                                 = $record->column_type;
            $column->comment                                    = $record->comment;
            $columns[$record->table_name][$record->column_name] = $column;

            return true;
        });
        ksort($columns);
        if ( $table )
        {
            return !empty( $columns[$table] ) ? [$table => $columns[$table]] : [];
        }

        return $columns;

    }

    /**
     * @param string $table
     * @return ForeignKey[][]
     */
    public function getForeignKeys(string $table='') : array
    {
        $this->exec('FLUSH TABLES');
        $query      = (new FluentPdoModel($this))
            ->table('information_schema.table_constraints', 'i')
            ->select([
                'i.TABLE_NAME              as table_name',
                'i.CONSTRAINT_NAME         as constraint_name',
                'k.REFERENCED_TABLE_NAME   as referenced_table_name',
                'k.REFERENCED_COLUMN_NAME  as referenced_column_name',
                'k.COLUMN_NAME             as column_name'
            ])
            ->join('information_schema.KEY_COLUMN_USAGE', 'i.CONSTRAINT_NAME = k.CONSTRAINT_NAME', 'k')
            ->where('i.TABLE_SCHEMA', $this->getConfig('dbname'))
            ->where('i.CONSTRAINT_TYPE', 'FOREIGN KEY');
        $foreign_keys = [];
        $query->fetchCallback(function(\stdClass $record) use (&$foreign_keys) {

            $foreignKey                     = new ForeignKey();
            $foreignKey->localTableName     = $record->table_name;
            $foreignKey->localColumnName    = $record->column_name;
            $foreignKey->constraintName     = $record->constraint_name;
            $foreignKey->foreignTableName   = $record->referenced_table_name;
            $foreignKey->foreignColumnName  = $record->referenced_column_name;

            $foreign_keys[$record->table_name][$record->column_name] = $foreignKey;

            return true;
        });
        ksort($foreign_keys);
        if ( $table )
        {
            return ! empty( $foreign_keys[$table] ) ? [$table => $foreign_keys[$table]] : [];
        }

        return $foreign_keys;
    }

    /**
     * @param bool|false $include_views
     * @param string $table
     * @return array
     */
    public function getTableCounts(bool $include_views=false, string $table='') : array
    {
        $this->exec('FLUSH TABLES');
        $types      = $include_views ? ['VIEW', 'BASE TABLE'] : ['BASE TABLE'];
        $query      = (new FluentPdoModel($this))
            ->table('information_schema.tables', 't')
            ->selectRaw("CONCAT('SELECT ''',table_name,''' as tbl, COUNT(*) as cnt FROM ', table_name)")
            ->where('t.TABLE_SCHEMA', $this->getConfig('dbname'))
            ->whereIn('t.table_type', $types);
        if ( $table )
        {
            $query->where('t.table_name', $table);
        }
        $sqls = [];
        $query->fetchCallback(function(\stdClass $record) use (&$sqls){

            $sql    = (array)$record;
            $sql    = array_values($sql);
            $sqls[] = $sql[0];

            return true;
        });
        $tableCounts    = [];
        $sql            = implode(' UNION ALL ', $sqls);
        (new FluentPdoModel($this))->query($sql)->fetchCallback(function(\stdClass $record) use (&$tableCounts){

            $tableCounts[$record->tbl] = $record->cnt;

            return true;
        });

        return $tableCounts;
    }

    /**
     * @param string $table
     * @param string $column
     * @return string
     */
    public function getFieldComment(string $table, string $column) : string
    {
        $this->exec('FLUSH TABLES');
        $query      = (new FluentPdoModel($this))
            ->table('information_schema.columns', 'c')
            ->select('c.COLUMN_COMMENT', 'comment')
            ->leftJoin('information_schema.tables', 'c.table_name = t.table_name AND c.table_schema = t.table_schema', 't')
            ->where('t.table_schema', $this->getConfig('dbname'))
            ->where('c.table_name', $table)
            ->where('c.column_name', $column);

        return $query->fetchStr('comment');
    }
}

1			<?php declare(strict_types=1);
2			/**
3			* @package Terah\FluentPdoModel
4			*
5			* Licensed under The MIT License
6			* For full copyright and license information, please see the LICENSE.txt
7			* Redistributions of files must retain the above copyright notice.
8			*
9			* @license http://www.opensource.org/licenses/mit-license.php MIT License
10			*/
11
12			namespace Terah\FluentPdoModel\Drivers;
13
14			use \PDO;
15			use Psr\Log\LoggerInterface;
16			use Psr\Log\NullLogger;
17			use Terah\FluentPdoModel\Column;
18			use Terah\FluentPdoModel\FluentPdoModel;
19			use Terah\FluentPdoModel\ForeignKey;
20			use Terah\RedisCache\CacheInterface;
21			use Terah\RedisCache\NullCache;
22
23			/**
24			* Class MysqlPdo
25			*
26			* @package Terah\FluentPdoModel\Drivers
27			* @author Terry Cullen - [email protected]
28			*/
29			class MysqlPdo extends AbstractPdo implements DriverInterface
30			{
31			/** @var bool */
32			protected $_supportsColumnMeta = true;
33
34			/**
35			* @param string $dsn
36			* @param string $username
37			* @param string $password
38			* @param array $options
39			* @param LoggerInterface $logger
40			* @param CacheInterface $cache
41			*/
42			public function __construct(string $dsn, string $username='', string $password='', array $options=[], LoggerInterface $logger=null, CacheInterface $cache=null)
43			{
44			parent::__construct($dsn, $username, $password, $options);
45			$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
46			$this->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
47			$this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
48			if ( array_key_exists('timeout', $options) && $options['timeout'] )
49			{
50			$this->setAttribute(PDO::ATTR_TIMEOUT, (int)$options['timeout']);
51			}
52			$this->exec("SET NAMES utf8 COLLATE utf8_unicode_ci");
53			$this->setConfig($options, $dsn);
54			$this->setLogger($logger ? $logger : new NullLogger());
55			$this->setCache($cache ? $cache : new NullCache());
56			}
57
58			/**
59			* @param bool $include_views
60			* @return string[]
61			*/
62			public function getTables(bool $include_views=false) : array
63			{
64			$this->exec('FLUSH TABLES');
65			$types = $include_views ? ['VIEW', 'BASE TABLE'] : ['BASE TABLE'];
66			return (new FluentPdoModel($this))
67			->table('information_schema.tables', 't')
68			// ->select('t.TABLE_NAME AS table_name')
69			->where('t.table_schema', $this->getConfig('dbname'))
70			->whereIn('t.table_type', $types)
71			->orderBy('t.table_type')
72			->fetchColumn('table_name');
73			}
74
75			/**
76			* @param bool $include_views
77			* @param string $table
78			* @return Column[][]
79			*/
80			public function getColumns(bool $include_views=false, string $table='') : array
81			{
82			$this->exec('FLUSH TABLES');
83			$types = $include_views ? ['VIEW', 'BASE TABLE'] : ['BASE TABLE'];
84			$query = (new FluentPdoModel($this))
85			->table('information_schema.columns', 'c')
86			->select([
87			'c.TABLE_NAME as table_name',
88			'c.COLUMN_NAME as column_name',
89			'c.IS_NULLABLE as is_nullable',
90			'c.DATA_TYPE as data_type',
91			'c.CHARACTER_MAXIMUM_LENGTH as character_maximum_length',
92			'c.NUMERIC_PRECISION as numeric_precision',
93			'c.COLUMN_TYPE as column_type',
94			'c.COLUMN_COMMENT as comment',
95			])
96			->leftJoin('information_schema.tables', 'c.table_name = t.table_name AND c.table_schema = t.table_schema', 't')
97			->where('t.table_schema', $this->getConfig('dbname'))
98			->whereIn('t.table_type', $types);
99			if ( $table )
100			{
101			$query->where('t.table_name', $table);
102			}
103			$columns = [];
104			$query->fetchCallback(function(\stdClass $record) use (&$columns) {
105
106			$column = new Column();
107			$column->tableName = $record->table_name;
108			$column->columnName = $record->column_name;
109			$column->isNullable = $record->is_nullable;
110			$column->dataType = $record->data_type;
111			$column->maxLength = $record->character_maximum_length;
112			$column->precision = $record->numeric_precision;
113			$column->columnType = $record->column_type;
114			$column->comment = $record->comment;
115			$columns[$record->table_name][$record->column_name] = $column;
116
117			return true;
118			});
119			ksort($columns);
120			if ( $table )
121			{
122			return !empty( $columns[$table] ) ? [$table => $columns[$table]] : [];
123			}
124
125			return $columns;
126
127			}
128
129			/**
130			* @param string $table
131			* @return ForeignKey[][]
132			*/
133			public function getForeignKeys(string $table='') : array
134			{
135			$this->exec('FLUSH TABLES');
136			$query = (new FluentPdoModel($this))
137			->table('information_schema.table_constraints', 'i')
138			->select([
139			'i.TABLE_NAME as table_name',
140			'i.CONSTRAINT_NAME as constraint_name',
141			'k.REFERENCED_TABLE_NAME as referenced_table_name',
142			'k.REFERENCED_COLUMN_NAME as referenced_column_name',
143			'k.COLUMN_NAME as column_name'
144			])
145			->join('information_schema.KEY_COLUMN_USAGE', 'i.CONSTRAINT_NAME = k.CONSTRAINT_NAME', 'k')
146			->where('i.TABLE_SCHEMA', $this->getConfig('dbname'))
147			->where('i.CONSTRAINT_TYPE', 'FOREIGN KEY');
148			$foreign_keys = [];
149			$query->fetchCallback(function(\stdClass $record) use (&$foreign_keys) {
150
151			$foreignKey = new ForeignKey();
152			$foreignKey->localTableName = $record->table_name;
153			$foreignKey->localColumnName = $record->column_name;
154			$foreignKey->constraintName = $record->constraint_name;
155			$foreignKey->foreignTableName = $record->referenced_table_name;
156			$foreignKey->foreignColumnName = $record->referenced_column_name;
157
158			$foreign_keys[$record->table_name][$record->column_name] = $foreignKey;
159
160			return true;
161			});
162			ksort($foreign_keys);
163			if ( $table )
164			{
165			return ! empty( $foreign_keys[$table] ) ? [$table => $foreign_keys[$table]] : [];
166			}
167
168			return $foreign_keys;
169			}
170
171			/**
172			* @param bool\|false $include_views
173			* @param string $table
174			* @return array
175			*/
176			public function getTableCounts(bool $include_views=false, string $table='') : array
177			{
178			$this->exec('FLUSH TABLES');
179			$types = $include_views ? ['VIEW', 'BASE TABLE'] : ['BASE TABLE'];
180			$query = (new FluentPdoModel($this))
181			->table('information_schema.tables', 't')
182			->selectRaw("CONCAT('SELECT ''',table_name,''' as tbl, COUNT(*) as cnt FROM ', table_name)")
183			->where('t.TABLE_SCHEMA', $this->getConfig('dbname'))
184			->whereIn('t.table_type', $types);
185			if ( $table )
186			{
187			$query->where('t.table_name', $table);
188			}
189			$sqls = [];
190			$query->fetchCallback(function(\stdClass $record) use (&$sqls){
191
192			$sql = (array)$record;
193			$sql = array_values($sql);
194			$sqls[] = $sql[0];
195
196			return true;
197			});
198			$tableCounts = [];
199			$sql = implode(' UNION ALL ', $sqls);
200			(new FluentPdoModel($this))->query($sql)->fetchCallback(function(\stdClass $record) use (&$tableCounts){
201
202			$tableCounts[$record->tbl] = $record->cnt;
203
204			return true;
205			});
206
207			return $tableCounts;
208			}
209
210			/**
211			* @param string $table
212			* @param string $column
213			* @return string
214			*/
215			public function getFieldComment(string $table, string $column) : string
216			{
217			$this->exec('FLUSH TABLES');
218			$query = (new FluentPdoModel($this))
219			->table('information_schema.columns', 'c')
220			->select('c.COLUMN_COMMENT', 'comment')
221			->leftJoin('information_schema.tables', 'c.table_name = t.table_name AND c.table_schema = t.table_schema', 't')
222			->where('t.table_schema', $this->getConfig('dbname'))
223			->where('c.table_name', $table)
224			->where('c.column_name', $column);
225
226			return $query->fetchStr('comment');
227			}
228			}

terah / fluent-pdo-model

MysqlPdo::getTables() A last analyzed 2018-05-04 04:46 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

MysqlPdo::getTables() A
last analyzed 2018-05-04 04:46 UTC