Starkerxp\DatabaseChecker\Repository\MysqlRepository

Complexity

Total Complexity

6

Size/Duplication

Total Lines	128
Duplicated Lines	0 %

Importance

Changes

0

6 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	3	1
A	getTablesCollation()	0	15	1
A	getSchemaCollation()	0	8	1
A	fetchColumnsStructure()	0	21	1
A	getTablesStructure()	0	8	1
A	fetchIndexStructure()	0	22	1

<?php

namespace Starkerxp\DatabaseChecker\Repository;


use Starkerxp\DatabaseChecker\LoggerTrait;

class MysqlRepository implements StructureInterface
{
    use LoggerTrait;
    /**
     * @var \PDO
     */
    protected $pdo;

    /**
     * MysqlRepository constructor.
     *
     * @param \PDO $pdo
     */
    public function __construct(\PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * @param $database
     *
     * @return array
     */
    public function getSchemaCollation($database)
    {
        $sth = $this->pdo->prepare('SELECT default_collation_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=:database');
        $sth->bindParam(':database', $database);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);

        return array_column($results, 'default_collation_name')[0];
    }

    /**
     * @param $database
     *
     * @return array
     */
    public function getTablesStructure($database)
    {
        $sth = $this->pdo->prepare('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=:database GROUP BY TABLE_NAME ORDER BY TABLE_NAME');
        $sth->bindParam(':database', $database);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);

        return array_column($results, 'TABLE_NAME');
    }

    /**
     * @param $database
     * @param $table
     *
     * @return array
     */
    public function getTablesCollation($database, $table)
    {
        $sql = ' SELECT c.collation_name
                FROM information_schema.`TABLES` t
                INNER JOIN information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` c ON c.collation_name = t.table_collation
                WHERE 
                     t.table_schema = :database
                   AND t.table_name = :table';
        $sth = $this->pdo->prepare($sql);
        $sth->bindParam(':database', $database);
        $sth->bindParam(':table', $table);
        $sth->execute();
        $results = $sth->fetchAll(\PDO::FETCH_ASSOC);

        return array_column($results, 'TABLE_NAME');
    }

    /**
     * @param $database
     * @param $table
     *
     * @return array
     */
    public function fetchIndexStructure($database, $table)
    {
        $sql = 'SELECT 
                  INDEX_NAME,
                  GROUP_CONCAT(COLUMN_NAME) AS COLUMN_NAME,
                  NON_UNIQUE,
                  IF(INDEX_TYPE="FULLTEXT",0,1) as NON_FULLTEXT
                FROM 
                  information_schema.statistics 
                WHERE 
                  TABLE_SCHEMA= :database AND 
                  TABLE_NAME= :myTable  
                GROUP BY
                  TABLE_NAME,INDEX_NAME
                ORDER BY
                  TABLE_NAME,INDEX_NAME,COLUMN_NAME';
        $sth = $this->pdo->prepare($sql);
        $sth->bindParam(':database', $database);
        $sth->bindParam(':myTable', $table);
        $sth->execute();

        return $sth->fetchAll(\PDO::FETCH_ASSOC);
    }

    /**
     * @param $database
     * @param $table
     *
     * @return array
     */
    public function fetchColumnsStructure($database, $table)
    {
        $sql = 'SELECT 
                  COLUMN_NAME, 
                  DATA_TYPE, 
                  COLUMN_TYPE, 
                  IS_NULLABLE, 
                  COLUMN_DEFAULT, 
                  EXTRA,
                  COLLATION_NAME
                FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE 
                  TABLE_SCHEMA= :database AND 
                  TABLE_NAME= :myTable 
                ORDER BY ORDINAL_POSITION';
        $sth = $this->pdo->prepare($sql);
        $sth->bindParam(':database', $database);
        $sth->bindParam(':myTable', $table);
        $sth->execute();

        return $sth->fetchAll(\PDO::FETCH_ASSOC);
    }

}