MysqlRepository::getTablesCollation() - Code Metrics - Inspection of "check collate" - starker-xp/database-checker - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( d278b5...81f2b1 )

by Guillaume

created 2018-02-26 23:05 UTC

MysqlRepository::getTablesCollation() A

↳ Parent: MysqlRepository

Complexity

Conditions	1
Paths	1

Size

Total Lines	15
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

<?php

namespace Starkerxp\DatabaseChecker\Repository;


class MysqlRepository
{
    /**
     * @var \PDO
     */
    protected $pdo;

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

    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
                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);
    }

}



1			<?php
2
3			namespace Starkerxp\DatabaseChecker\Repository;
4
5
6			class MysqlRepository
7			{
8			/**
9			* @var \PDO
10			*/
11			protected $pdo;
12
13			/**
14			* MysqlRepository constructor.
15			*
16			* @param \PDO $pdo
17			*/
18			public function __construct(\PDO $pdo)
19			{
20			$this->pdo = $pdo;
21			}
22
23			public function getSchemaCollation($database)
24			{
25			$sth = $this->pdo->prepare('SELECT default_collation_name FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=:database');
26			$sth->bindParam(':database', $database);
27			$sth->execute();
28			$results = $sth->fetchAll(\PDO::FETCH_ASSOC);
29
30			return array_column($results, 'default_collation_name')[0];
31			}
32
33			/**
34			* @param $database
35			*
36			* @return array
37			*/
38			public function getTablesStructure($database)
39			{
40			$sth = $this->pdo->prepare('SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=:database GROUP BY TABLE_NAME ORDER BY TABLE_NAME');
41			$sth->bindParam(':database', $database);
42			$sth->execute();
43			$results = $sth->fetchAll(\PDO::FETCH_ASSOC);
44
45			return array_column($results, 'TABLE_NAME');
46			}
47
48			/**
49			* @param $database
50			* @param $table
51			*
52			* @return array
53			*/
54			public function getTablesCollation($database, $table)
55			{
56			$sql =' SELECT c.collation_name
57			FROM information_schema.`TABLES` t
58			INNER JOIN information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` c ON c.collation_name = t.table_collation
59			WHERE
60			t.table_schema = :database
61			AND t.table_name = :table';
62			$sth = $this->pdo->prepare($sql);
63			$sth->bindParam(':database', $database);
64			$sth->bindParam(':table', $table);
65			$sth->execute();
66			$results = $sth->fetchAll(\PDO::FETCH_ASSOC);
67
68			return array_column($results, 'TABLE_NAME');
69			}
70
71			/**
72			* @param $database
73			* @param $table
74			*
75			* @return array
76			*/
77			public function fetchIndexStructure($database, $table)
78			{
79			$sql = 'SELECT
80			INDEX_NAME,
81			GROUP_CONCAT(COLUMN_NAME) AS COLUMN_NAME,
82			NON_UNIQUE
83			FROM
84			information_schema.statistics
85			WHERE
86			TABLE_SCHEMA= :database AND
87			TABLE_NAME= :myTable
88			GROUP BY
89			TABLE_NAME,INDEX_NAME
90			ORDER BY
91			TABLE_NAME,INDEX_NAME,COLUMN_NAME';
92			$sth = $this->pdo->prepare($sql);
93			$sth->bindParam(':database', $database);
94			$sth->bindParam(':myTable', $table);
95			$sth->execute();
96
97			return $sth->fetchAll(\PDO::FETCH_ASSOC);
98			}
99
100			/**
101			* @param $database
102			* @param $table
103			*
104			* @return array
105			*/
106			public function fetchColumnsStructure($database, $table)
107			{
108			$sql = 'SELECT
109			COLUMN_NAME,
110			DATA_TYPE,
111			COLUMN_TYPE,
112			IS_NULLABLE,
113			COLUMN_DEFAULT,
114			EXTRA,
115			COLLATION_NAME
116			FROM INFORMATION_SCHEMA.COLUMNS
117			WHERE
118			TABLE_SCHEMA= :database AND
119			TABLE_NAME= :myTable
120			ORDER BY ORDINAL_POSITION';
121			$sth = $this->pdo->prepare($sql);
122			$sth->bindParam(':database', $database);
123			$sth->bindParam(':myTable', $table);
124			$sth->execute();
125
126			return $sth->fetchAll(\PDO::FETCH_ASSOC);
127			}
128
129			}
			0 ignored issues – show Coding Style introduced 2018-02-22 15:48 UTC by Report Bug Copy Issue Report As per coding style, files should not end with a newline character. This check marks files that end in a newline character, i.e. an empy line. Loading history...
130

starker-xp / database-checker

Push — master ( d278b5...81f2b1 )

MysqlRepository::getTablesCollation() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like