Terah\FluentPdoModel\Drivers\MysqlPdo

Complexity

Total Complexity

19

Size/Duplication

Total Lines	200
Duplicated Lines	0 %

Coupling/Cohesion

Components	1
Dependencies	6

Importance

Changes	1
Bugs	0	Features	0

6 Methods

Rating	Name	Duplication	Size	Complexity
B	__construct()	0	15	5
A	getTables()	0	12	2
B	getColumns()	0	48	5
B	getForeignKeys()	0	37	3
B	getTableCounts()	0	33	3
A	getFieldComment()	0	13	1

<?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 - terry@terah.com.au
 */
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');
    }
}