FindObjectsFromRawSqlQueryFactory::generateGroupedSqlCount() - Code Metrics - Inspection of "Feature: raw sql" - thecodingmachine/tdbm - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#12)

by Dorian

created 2017-06-15 14:24 UTC

generateGroupedSqlCount() A

↳ Parent: FindObjectsFromRawSqlQueryFactory

Complexity

Conditions	1
Paths	1

Size

Total Lines	17
Code Lines	14

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	1
dl	0
loc	17
rs	9.4285
c	0
b	0
f	0
eloc	14
nc	1
nop	1

<?php

namespace TheCodingMachine\TDBM\QueryFactory;

use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\Schema;
use TheCodingMachine\TDBM\TDBMException;
use TheCodingMachine\TDBM\TDBMService;
use TheCodingMachine\TDBM\UncheckedOrderBy;
use PHPSQLParser\PHPSQLCreator;
use PHPSQLParser\PHPSQLParser;

/**
 * This class is in charge of formatting the SQL passed to findObjectsFromRawSql method.
 */
class FindObjectsFromRawSqlQueryFactory implements QueryFactory
{
    /**
     * @var array[]
     */
    protected $columnDescriptors;
    /**
     * @var Schema
     */
    private $schema;
    /**
     * @var string
     */
    private $sql;
    /**
     * @var string
     */
    private $sqlCount;
    /**
     * @var string
     */
    private $processedSql;
    /**
     * @var string
     */
    private $processedSqlCount;
    /**
     * @var TDBMService
     */
    private $tdbmService;
    /**
     * @var string
     */
    private $mainTable;

    /**
     * FindObjectsFromRawSqlQueryFactory constructor.
     * @param TDBMService $tdbmService
     * @param Schema $schema
     * @param string $mainTable
     * @param string $sql
     * @param string $sqlCount
     */
    public function __construct(TDBMService $tdbmService, Schema $schema, string $mainTable, string $sql, string $sqlCount = null)
    {
        $this->sql = $sql;
        $this->sqlCount = $sqlCount;
        $this->tdbmService = $tdbmService;
        $this->schema = $schema;
        $this->mainTable = $mainTable;

        $this->compute();
    }

    public function sort($orderBy)
    {
        throw new TDBMException('sort not supported for raw sql queries');
    }

    public function getMagicSql(): string
    {
        return $this->processedSql;
    }

    public function getMagicSqlCount(): string
    {
        return $this->processedSqlCount;
    }

    public function getColumnDescriptors(): array
    {
        return $this->columnDescriptors;
    }

    protected function compute()
    {
        $parser = new PHPSQLParser();
        $generator = new PHPSQLCreator();
        $parsedSql = $parser->parse($this->sql);

        // 1: let's reformat the SELECT and construct our columns
        list($select, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
        $parsedSql['SELECT'] = $select;
        $this->processedSql = $generator->create($parsedSql);
        $this->columnDescriptors = $columnDescriptors;

        // 2: let's compute the count query if needed
        if ($this->sqlCount === null) {
            $parsedSqlCount = $this->generateParsedSqlCount($parsedSql);
            $this->processedSqlCount = $generator->create($parsedSqlCount);
        } else {
            $this->processedSqlCount = $this->sqlCount;
        }
    }

    private function formatSelect($baseSelect)
    {
        $relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable);
        $tableGroup = $this->getTableGroupName($relatedTables);

        $connection = $this->tdbmService->getConnection();
        $formattedSelect = [];
        $columnDescritors = [];
        $fetchedTables = [];

        foreach ($baseSelect as $entry) {
            if ($entry['expr_type'] !== 'colref') {
                $formattedSelect[] = $entry;
                continue;
            }

            $noQuotes = $entry['no_quotes'];
            if ($noQuotes['delim'] != '.' || count($noQuotes['parts']) !== 2) {
                $formattedSelect[] = $entry;
                continue;
            }

            $tableName = $noQuotes['parts'][0];
            if (!in_array($tableName, $relatedTables)) {
                $formattedSelect[] = $entry;
                continue;
            }

            $columnName = $noQuotes['parts'][1];
            if ($columnName !== '*') {
                $formattedSelect[] = $entry;
                continue;
            }

            $table = $this->schema->getTable($tableName);
            foreach ($table->getColumns() as $column) {
                $columnName = $column->getName();
                $alias = "{$tableName}____{$columnName}";
                $formattedSelect[] = [
                    'expr_type' => 'colref',
                    'base_expr' => $connection->quoteIdentifier($tableName).'.'.$connection->quoteIdentifier($columnName),
                    'no_quotes' => [
                        'delim' => '.',
                        'parts' => [
                            $tableName,
                            $columnName
                        ]
                    ],
                    'alias' => [
                        'as' => true,
                        'name' => $alias,
                    ]
                ];

                $columnDescritors[$alias] = [
                    'as' => $alias,
                    'table' => $tableName,
                    'column' => $columnName,
                    'type' => $column->getType(),
                    'tableGroup' => $tableGroup,
                ];
            }
            $fetchedTables[] = $tableName;
        }

        $missingTables = array_diff($relatedTables, $fetchedTables);
        if (!empty($missingTables)) {
            throw new TDBMException('Missing tables '.implode(', ', $missingTables).' in SELECT statement');
        }

        for ($i = 0; $i < count($formattedSelect) - 1; $i++) {
            $formattedSelect[$i]['delim'] = ',';
        }
        return [$formattedSelect, $columnDescritors];
    }

    private function generateParsedSqlCount($parsedSql)
    {
        if (isset($parsedSql['ORDER'])) {
            unset($parsedSql['ORDER']);
        }

        if (!isset($parsedSql['GROUP'])) {
            // most simple case:no GROUP BY in query
            return $this->generateSimpleSqlCount($parsedSql);
        } elseif (!isset($parsedSql['HAVING'])) {
            // GROUP BY without HAVING statement: let's COUNT the DISTINCT grouped columns
            return $this->generateGroupedSqlCount($parsedSql);
        } else {
            // GROUP BY with a HAVING statement: we'll have to wrap the query
            return $this->generateWrappedSqlCount($parsedSql);
        }
    }

    private function generateSimpleSqlCount($parsedSql)
    {
        return [[
            'expr_type' => 'aggregate_function',
            'alias' => false,
            'base_expr' => 'COUNT',
            'sub_tree' => $parsedSql['SELECT'],
            'delim' => false,
        ]];
    }

    private function generateGroupedSqlCount($parsedSql)
    {
        $group = $parsedSql['GROUP'];
        unset($parsedSql['GROUP']);
        $parsedSql['SELECT'] = [[
            'expr_type' => 'aggregate_function',
            'alias' => false,
            'base_expr' => 'COUNT',
            'sub_tree' => array_merge([[
                'expr_type' => 'reserved',
                'base_expr' => 'DISTINCT',
                'delim' => ','
            ]], $group),
            'delim' => false,
        ]];
        return $parsedSql;
    }

    private function generateWrappedSqlCount($parsedSql)
    {
        return [
            'SELECT' => [[
                'expr_type' => 'aggregate_function',
                'alias' => false,
                'base_expr' => 'COUNT',
                'sub_tree' => [
                    [
                        'expr_type' => 'colref',
                        'base_expr' => '*',
                        'sub_tree' => false
                    ]
                ],
                'delim' => false,
            ]],
            'FROM' => [[
                'expr_type' => 'subquery',
                'alias' => [
                    'as' => true,
                    'name' => '____query'
                ],
                'sub_tree' => $parsedSql,
            ]]
        ];
    }

    protected function getTableGroupName(array $relatedTables)
    {
        sort($relatedTables);
        return implode('_``_', $relatedTables);
    }
}


1			<?php
2
3			namespace TheCodingMachine\TDBM\QueryFactory;
4
5			use Doctrine\DBAL\Schema\Column;
6			use Doctrine\DBAL\Schema\Schema;
7			use TheCodingMachine\TDBM\TDBMException;
8			use TheCodingMachine\TDBM\TDBMService;
9			use TheCodingMachine\TDBM\UncheckedOrderBy;
10			use PHPSQLParser\PHPSQLCreator;
11			use PHPSQLParser\PHPSQLParser;
12
13			/**
14			* This class is in charge of formatting the SQL passed to findObjectsFromRawSql method.
15			*/
16			class FindObjectsFromRawSqlQueryFactory implements QueryFactory
17			{
18			/**
19			* @var array[]
20			*/
21			protected $columnDescriptors;
22			/**
23			* @var Schema
24			*/
25			private $schema;
26			/**
27			* @var string
28			*/
29			private $sql;
30			/**
31			* @var string
32			*/
33			private $sqlCount;
34			/**
35			* @var string
36			*/
37			private $processedSql;
38			/**
39			* @var string
40			*/
41			private $processedSqlCount;
42			/**
43			* @var TDBMService
44			*/
45			private $tdbmService;
46			/**
47			* @var string
48			*/
49			private $mainTable;
50
51			/**
52			* FindObjectsFromRawSqlQueryFactory constructor.
53			* @param TDBMService $tdbmService
54			* @param Schema $schema
55			* @param string $mainTable
56			* @param string $sql
57			* @param string $sqlCount
58			*/
59			public function __construct(TDBMService $tdbmService, Schema $schema, string $mainTable, string $sql, string $sqlCount = null)
60			{
61			$this->sql = $sql;
62			$this->sqlCount = $sqlCount;
63			$this->tdbmService = $tdbmService;
64			$this->schema = $schema;
65			$this->mainTable = $mainTable;
66
67			$this->compute();
68			}
69
70			public function sort($orderBy)
71			{
72			throw new TDBMException('sort not supported for raw sql queries');
73			}
74
75			public function getMagicSql(): string
76			{
77			return $this->processedSql;
78			}
79
80			public function getMagicSqlCount(): string
81			{
82			return $this->processedSqlCount;
83			}
84
85			public function getColumnDescriptors(): array
86			{
87			return $this->columnDescriptors;
88			}
89
90			protected function compute()
91			{
92			$parser = new PHPSQLParser();
93			$generator = new PHPSQLCreator();
94			$parsedSql = $parser->parse($this->sql);
95
96			// 1: let's reformat the SELECT and construct our columns
97			list($select, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
98			$parsedSql['SELECT'] = $select;
99			$this->processedSql = $generator->create($parsedSql);
100			$this->columnDescriptors = $columnDescriptors;
101
102			// 2: let's compute the count query if needed
103			if ($this->sqlCount === null) {
104			$parsedSqlCount = $this->generateParsedSqlCount($parsedSql);
105			$this->processedSqlCount = $generator->create($parsedSqlCount);
106			} else {
107			$this->processedSqlCount = $this->sqlCount;
108			}
109			}
110
111			private function formatSelect($baseSelect)
112			{
113			$relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable);
114			$tableGroup = $this->getTableGroupName($relatedTables);
115
116			$connection = $this->tdbmService->getConnection();
117			$formattedSelect = [];
118			$columnDescritors = [];
119			$fetchedTables = [];
120
121			foreach ($baseSelect as $entry) {
122			if ($entry['expr_type'] !== 'colref') {
123			$formattedSelect[] = $entry;
124			continue;
125			}
126
127			$noQuotes = $entry['no_quotes'];
128			if ($noQuotes['delim'] != '.' \|\| count($noQuotes['parts']) !== 2) {
129			$formattedSelect[] = $entry;
130			continue;
131			}
132
133			$tableName = $noQuotes['parts'][0];
134			if (!in_array($tableName, $relatedTables)) {
135			$formattedSelect[] = $entry;
136			continue;
137			}
138
139			$columnName = $noQuotes['parts'][1];
140			if ($columnName !== '*') {
141			$formattedSelect[] = $entry;
142			continue;
143			}
144
145			$table = $this->schema->getTable($tableName);
146			foreach ($table->getColumns() as $column) {
147			$columnName = $column->getName();
148			$alias = "{$tableName}____{$columnName}";
149			$formattedSelect[] = [
150			'expr_type' => 'colref',
151			'base_expr' => $connection->quoteIdentifier($tableName).'.'.$connection->quoteIdentifier($columnName),
152			'no_quotes' => [
153			'delim' => '.',
154			'parts' => [
155			$tableName,
156			$columnName
157			]
158			],
159			'alias' => [
160			'as' => true,
161			'name' => $alias,
162			]
163			];
164
165			$columnDescritors[$alias] = [
166			'as' => $alias,
167			'table' => $tableName,
168			'column' => $columnName,
169			'type' => $column->getType(),
170			'tableGroup' => $tableGroup,
171			];
172			}
173			$fetchedTables[] = $tableName;
174			}
175
176			$missingTables = array_diff($relatedTables, $fetchedTables);
177			if (!empty($missingTables)) {
178			throw new TDBMException('Missing tables '.implode(', ', $missingTables).' in SELECT statement');
179			}
180
181			for ($i = 0; $i < count($formattedSelect) - 1; $i++) {
182			$formattedSelect[$i]['delim'] = ',';
183			}
184			return [$formattedSelect, $columnDescritors];
185			}
186
187			private function generateParsedSqlCount($parsedSql)
188			{
189			if (isset($parsedSql['ORDER'])) {
190			unset($parsedSql['ORDER']);
191			}
192
193			if (!isset($parsedSql['GROUP'])) {
194			// most simple case:no GROUP BY in query
195			return $this->generateSimpleSqlCount($parsedSql);
196			} elseif (!isset($parsedSql['HAVING'])) {
197			// GROUP BY without HAVING statement: let's COUNT the DISTINCT grouped columns
198			return $this->generateGroupedSqlCount($parsedSql);
199			} else {
200			// GROUP BY with a HAVING statement: we'll have to wrap the query
201			return $this->generateWrappedSqlCount($parsedSql);
202			}
203			}
204
205			private function generateSimpleSqlCount($parsedSql)
206			{
207			return [[
208			'expr_type' => 'aggregate_function',
209			'alias' => false,
210			'base_expr' => 'COUNT',
211			'sub_tree' => $parsedSql['SELECT'],
212			'delim' => false,
213			]];
214			}
215
216			private function generateGroupedSqlCount($parsedSql)
217			{
218			$group = $parsedSql['GROUP'];
219			unset($parsedSql['GROUP']);
220			$parsedSql['SELECT'] = [[
221			'expr_type' => 'aggregate_function',
222			'alias' => false,
223			'base_expr' => 'COUNT',
224			'sub_tree' => array_merge([[
225			'expr_type' => 'reserved',
226			'base_expr' => 'DISTINCT',
227			'delim' => ','
228			]], $group),
229			'delim' => false,
230			]];
231			return $parsedSql;
232			}
233
234			private function generateWrappedSqlCount($parsedSql)
235			{
236			return [
237			'SELECT' => [[
238			'expr_type' => 'aggregate_function',
239			'alias' => false,
240			'base_expr' => 'COUNT',
241			'sub_tree' => [
242			[
243			'expr_type' => 'colref',
244			'base_expr' => '*',
245			'sub_tree' => false
246			]
247			],
248			'delim' => false,
249			]],
250			'FROM' => [[
251			'expr_type' => 'subquery',
252			'alias' => [
253			'as' => true,
254			'name' => '____query'
255			],
256			'sub_tree' => $parsedSql,
257			]]
258			];
259			}
260
261			protected function getTableGroupName(array $relatedTables)
262			{
263			sort($relatedTables);
264			return implode('_``_', $relatedTables);
265			}
266			}
267

thecodingmachine / tdbm

Pull Request — master (#12)

generateGroupedSqlCount() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like