Task::getAllTablesNames() - Code Metrics - webfactory/zauberlehrling - Measure and Improve Code Quality continuously with Scrutinizer

Task::getAllTablesNames() A
last analyzed 2022-10-06 08:15 UTC

↳ Parent: Task

Complexity

Conditions	1
Paths	1

Size

Total Lines	13
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	7
dl	0
loc	13
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	0

<?php

namespace AppBundle\ShowUnusedMySQLTables;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Driver\Statement;
use Doctrine\DBAL\Schema\Table;
use Helper\NullStyle;
use PHPSQLParser\PHPSQLParser;
use Symfony\Component\Console\Style\StyleInterface;

/**
 * Get the names of unused MySQL tables.
 *
 * The idea is analogous to the code coverage. First, enable logging in MySQL, e.g. with
 *
 * SET global general_log = 1;
 * SET global log_output = 'table';
 *
 * You might want to delete old log data:
 *
 * TRUNCATE mysql.general_log;
 *
 * Then execute all use cases of your application, e.g. with behat tests. After that, you can disable MySQl logging with
 *
 * SET global general_log = 0;
 *
 * Then, parse the logged queries and extract the names of the queried tables. Finally, intersect this set with the set
 * of all table names (retrieved via the default Doctrine connection) and you have the names of the unused tables.
 */
final class Task
{
    /**
     * @var Connection
     */
    private $connection;

    /** @var string, e.g 'mysql.' for accessing mysql database tables from the connection  */
    private $dbSystemCatalogPrefix;

    /** @var StyleInterface */
    private $ioStyle;

    /**
     * @param Connection $connection
     * @param string $dbSystemCatalogPrefix
     * @throws \Doctrine\DBAL\DBALException
     */
    public function __construct(Connection $connection, $dbSystemCatalogPrefix)
    {
        $this->connection = $connection;
        $this->connection->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
        $this->dbSystemCatalogPrefix = $dbSystemCatalogPrefix;
    }

    /**
     * @param StyleInterface|null $ioStyle
     */
    public function getUnusedTableNames(StyleInterface $ioStyle = null)
    {
        $this->ioStyle = $ioStyle ?: new NullStyle();
        $this->ioStyle->text('Started.');

        $unusedTableNames = array_diff($this->getAllTablesNames(), $this->getUsedTableNames());

        $this->ioStyle->newLine();
        $this->ioStyle->text('Calculated ' . count($unusedTableNames) . ' potentially unused tables:');
        $this->ioStyle->listing($unusedTableNames);
        $this->ioStyle->success('Finished listing potentially unused tables.');
    }

    /**
     * @return string[]
     */
    private function getAllTablesNames()
    {
        $tables = $this->connection->getSchemaManager()->listTables();
        $tableNames = array_map(
            function (Table $table) {
                return $table->getName();
            },
            $tables
        );

        $this->ioStyle->text('Found ' . count($tableNames) . ' tables in the database "' . $this->connection->getDatabase() . '".');

        return $tableNames;
    }

    /**
     * @return string[]
     */
    private function getUsedTableNames()
    {
        $stmt = $this->getLoggedQueriesStatement();
        $numberOfLoggedQueries = $stmt->rowCount();
        $this->ioStyle->text('Analyzing ' . $numberOfLoggedQueries . ' unique logged queries (among all databases):');

        $this->ioStyle->progressStart($numberOfLoggedQueries);

        $usedTableNames = [];
        while ($loggedQuery = $stmt->fetch(\PDO::FETCH_COLUMN)) {
            $usedTableNames[] = $this->extractTableNamesFromLoggedQuery($loggedQuery);
            $this->ioStyle->progressAdvance();
        }

        if ($usedTableNames === []) {
            return $usedTableNames;

        }

        $usedTableNames = call_user_func_array('array_merge', $usedTableNames);
        $usedTableNames = array_unique($usedTableNames);

        $this->ioStyle->newLine();
        $this->ioStyle->text('Found ' . count($usedTableNames) . ' used tables (among all databases).');

        return $usedTableNames;
    }

    /**
     * @return Statement
     */
    private function getLoggedQueriesStatement()
    {
        return $this->connection->createQueryBuilder()

                                ->select('argument')
                                ->from($this->dbSystemCatalogPrefix . 'general_log')
                                ->where("command_type = 'Query'")
                                ->groupBy('argument')
                                ->execute();
    }

    /**
     * @param string $loggedQuery
     * @return string[]
     */
    private function extractTableNamesFromLoggedQuery($loggedQuery)
    {
        $usedTableNames = [];

        $parser = new PHPSQLParser();
        $parsedQuery = $parser->parse($loggedQuery);

        if (!is_array($parsedQuery) || !array_key_exists('FROM', $parsedQuery)) {

            return [];
        }

        foreach ($parsedQuery['FROM'] as $fromDescription) {
            if ($fromDescription['expr_type'] === 'table') {
                $usedTableNames[] = $fromDescription['table'];
            }
        }

        return $usedTableNames;
    }
}


1			<?php
2
3			namespace AppBundle\ShowUnusedMySQLTables;
4
5			use Doctrine\DBAL\Connection;
6			use Doctrine\DBAL\Driver\Statement;
7			use Doctrine\DBAL\Schema\Table;
8			use Helper\NullStyle;
9			use PHPSQLParser\PHPSQLParser;
10			use Symfony\Component\Console\Style\StyleInterface;
11
12			/**
13			* Get the names of unused MySQL tables.
14			*
15			* The idea is analogous to the code coverage. First, enable logging in MySQL, e.g. with
16			*
17			* SET global general_log = 1;
18			* SET global log_output = 'table';
19			*
20			* You might want to delete old log data:
21			*
22			* TRUNCATE mysql.general_log;
23			*
24			* Then execute all use cases of your application, e.g. with behat tests. After that, you can disable MySQl logging with
25			*
26			* SET global general_log = 0;
27			*
28			* Then, parse the logged queries and extract the names of the queried tables. Finally, intersect this set with the set
29			* of all table names (retrieved via the default Doctrine connection) and you have the names of the unused tables.
30			*/
31			final class Task
32			{
33			/**
34			* @var Connection
35			*/
36			private $connection;
37
38			/** @var string, e.g 'mysql.' for accessing mysql database tables from the connection */
39			private $dbSystemCatalogPrefix;
40
41			/** @var StyleInterface */
42			private $ioStyle;
43
44			/**
45			* @param Connection $connection
46			* @param string $dbSystemCatalogPrefix
47			* @throws \Doctrine\DBAL\DBALException
48			*/
49			public function __construct(Connection $connection, $dbSystemCatalogPrefix)
50			{
51			$this->connection = $connection;
52			$this->connection->getDatabasePlatform()->registerDoctrineTypeMapping('enum', 'string');
53			$this->dbSystemCatalogPrefix = $dbSystemCatalogPrefix;
54			}
55
56			/**
57			* @param StyleInterface\|null $ioStyle
58			*/
59			public function getUnusedTableNames(StyleInterface $ioStyle = null)
60			{
61			$this->ioStyle = $ioStyle ?: new NullStyle();
62			$this->ioStyle->text('Started.');
63
64			$unusedTableNames = array_diff($this->getAllTablesNames(), $this->getUsedTableNames());
65
66			$this->ioStyle->newLine();
67			$this->ioStyle->text('Calculated ' . count($unusedTableNames) . ' potentially unused tables:');
68			$this->ioStyle->listing($unusedTableNames);
69			$this->ioStyle->success('Finished listing potentially unused tables.');
70			}
71
72			/**
73			* @return string[]
74			*/
75			private function getAllTablesNames()
76			{
77			$tables = $this->connection->getSchemaManager()->listTables();
78			$tableNames = array_map(
79			function (Table $table) {
80			return $table->getName();
81			},
82			$tables
83			);
84
85			$this->ioStyle->text('Found ' . count($tableNames) . ' tables in the database "' . $this->connection->getDatabase() . '".');
86
87			return $tableNames;
88			}
89
90			/**
91			* @return string[]
92			*/
93			private function getUsedTableNames()
94			{
95			$stmt = $this->getLoggedQueriesStatement();
96			$numberOfLoggedQueries = $stmt->rowCount();
97			$this->ioStyle->text('Analyzing ' . $numberOfLoggedQueries . ' unique logged queries (among all databases):');
98
99			$this->ioStyle->progressStart($numberOfLoggedQueries);
100
101			$usedTableNames = [];
102			while ($loggedQuery = $stmt->fetch(\PDO::FETCH_COLUMN)) {
103			$usedTableNames[] = $this->extractTableNamesFromLoggedQuery($loggedQuery);
104			$this->ioStyle->progressAdvance();
105			}
106
107			if ($usedTableNames === []) {
108			return $usedTableNames;
			0 ignored issues – show Bug Best Practice introduced 2017-10-27 11:25 UTC by Report Bug Copy Issue Report The expression `return $usedTableNames` returns an array which contains values of type `string[]` which are incompatible with the documented value type `string`. Loading history...
109			}
110
111			$usedTableNames = call_user_func_array('array_merge', $usedTableNames);
112			$usedTableNames = array_unique($usedTableNames);
113
114			$this->ioStyle->newLine();
115			$this->ioStyle->text('Found ' . count($usedTableNames) . ' used tables (among all databases).');
116
117			return $usedTableNames;
118			}
119
120			/**
121			* @return Statement
122			*/
123			private function getLoggedQueriesStatement()
124			{
125			return $this->connection->createQueryBuilder()
			0 ignored issues – show Bug Best Practice introduced 2017-10-27 11:25 UTC by Report Bug Copy Issue Report The expression `return $this->connection...('argument')->execute()` also could return the type `integer` which is incompatible with the documented return type `Doctrine\DBAL\Driver\Statement`. Loading history...
126			->select('argument')
127			->from($this->dbSystemCatalogPrefix . 'general_log')
128			->where("command_type = 'Query'")
129			->groupBy('argument')
130			->execute();
131			}
132
133			/**
134			* @param string $loggedQuery
135			* @return string[]
136			*/
137			private function extractTableNamesFromLoggedQuery($loggedQuery)
138			{
139			$usedTableNames = [];
140
141			$parser = new PHPSQLParser();
142			$parsedQuery = $parser->parse($loggedQuery);
143
144			if (!is_array($parsedQuery) \|\| !array_key_exists('FROM', $parsedQuery)) {
			0 ignored issues – show introduced 2019-01-30 19:17 UTC by Report Bug Copy Issue Report The condition `is_array($parsedQuery)` is always `true`. Loading history...
145			return [];
146			}
147
148			foreach ($parsedQuery['FROM'] as $fromDescription) {
149			if ($fromDescription['expr_type'] === 'table') {
150			$usedTableNames[] = $fromDescription['table'];
151			}
152			}
153
154			return $usedTableNames;
155			}
156			}
157

webfactory / zauberlehrling

Task::getAllTablesNames() A last analyzed 2022-10-06 08:15 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

Task::getAllTablesNames() A
last analyzed 2022-10-06 08:15 UTC