FindObjectsFromRawSqlQueryFactory::processParsedUnionQuery() - Code Metrics - Inspection of "findFromRawSql does not support UNION queries" - thecodingmachine/tdbm - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#52)

by David

created 2017-11-22 10:32 UTC

processParsedUnionQuery() B

↳ Parent: FindObjectsFromRawSqlQueryFactory

Complexity

Conditions	2
Paths	2

Size

Total Lines	30
Code Lines	15

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	15
nc	2
nop	2
dl	0
loc	30
rs	8.8571
c	0
b	0
f	0

<?php

namespace TheCodingMachine\TDBM\QueryFactory;

use Doctrine\DBAL\Schema\Schema;
use TheCodingMachine\TDBM\TDBMException;
use TheCodingMachine\TDBM\TDBMService;
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 $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->tdbmService = $tdbmService;
        $this->schema = $schema;
        $this->mainTable = $mainTable;

        [$this->processedSql, $this->processedSqlCount, $this->columnDescriptors] = $this->compute($sql, $sqlCount);
    }

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

    private function compute(string $sql, ?string $sqlCount)
    {
        $parser = new PHPSQLParser();
        $parsedSql = $parser->parse($sql);

        if (isset($parsedSql['SELECT'])) {
            [$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedSelectQuery($parsedSql, $sqlCount);

        } elseif (isset($parsedSql['UNION'])) {
            [$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedUnionQuery($parsedSql, $sqlCount);

        } else {
            throw new TDBMException('Unable to analyze query "'.$sql.'"');
        }

        return [$processedSql, $processedSqlCount, $columnDescriptors];

    }

    private function processParsedUnionQuery(array $parsedSql, ?string $sqlCount): array
    {
        $selects = $parsedSql['UNION'];

        $parsedSqlList = [];
        $columnDescriptors = [];

        foreach ($selects as $select) {
            [$selectProcessedSql, $selectProcessedCountSql, $columnDescriptors] = $this->processParsedSelectQuery($select, '');


            // Let's reparse the returned SQL (not the most efficient way of doing things)
            $parser = new PHPSQLParser();
            $parsedSql = $parser->parse($selectProcessedSql);


            $parsedSqlList[] = $parsedSql;
        }

        // Let's rebuild the UNION query
        $query = ['UNION' => $parsedSqlList];

        // The count is the SUM of the count of the UNIONs
        $countQuery = $this->generateWrappedSqlCount($query);

        $generator = new PHPSQLCreator();

        $processedSql = $generator->create($query);
        $processedSqlCount = $generator->create($countQuery);

        return [$processedSql, $sqlCount ?? $processedSqlCount, $columnDescriptors];
    }

    /**
     * @param array $parsedSql
     * @param null|string $sqlCount
     * @return mixed[]
     */
    private function processParsedSelectQuery(array $parsedSql, ?string $sqlCount): array
    {
        // 1: let's reformat the SELECT and construct our columns
        list($select, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
        $generator = new PHPSQLCreator();
        $parsedSql['SELECT'] = $select;
        $processedSql = $generator->create($parsedSql);

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

        return [$processedSql, $processedSqlCount, $columnDescriptors];
    }

    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)
    {
        $parsedSql['SELECT'] =                 [[
            'expr_type' => 'aggregate_function',
            'alias' => [
                'as' => true,
                'name' => 'cnt',
            ],
            'base_expr' => 'COUNT',
            'sub_tree' => $parsedSql['SELECT'],
            'delim' => false,
        ]];

        return $parsedSql;
    }

    private function generateGroupedSqlCount($parsedSql)
    {
        $group = $parsedSql['GROUP'];
        unset($parsedSql['GROUP']);
        $parsedSql['SELECT'] = [[
            'expr_type' => 'aggregate_function',
            'alias' => [
                'as' => true,
                'name' => 'cnt',
            ],
            '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' => [
                    'as' => true,
                    'name' => 'cnt',
                ],
                '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\Schema;
6			use TheCodingMachine\TDBM\TDBMException;
7			use TheCodingMachine\TDBM\TDBMService;
8			use PHPSQLParser\PHPSQLCreator;
9			use PHPSQLParser\PHPSQLParser;
10
11			/**
12			* This class is in charge of formatting the SQL passed to findObjectsFromRawSql method.
13			*/
14			class FindObjectsFromRawSqlQueryFactory implements QueryFactory
15			{
16			/**
17			* @var array[]
18			*/
19			protected $columnDescriptors;
20			/**
21			* @var Schema
22			*/
23			private $schema;
24			/**
25			* @var string
26			*/
27			private $processedSql;
28			/**
29			* @var string
30			*/
31			private $processedSqlCount;
32			/**
33			* @var TDBMService
34			*/
35			private $tdbmService;
36			/**
37			* @var string
38			*/
39			private $mainTable;
40
41			/**
42			* FindObjectsFromRawSqlQueryFactory constructor.
43			* @param TDBMService $tdbmService
44			* @param Schema $schema
45			* @param string $mainTable
46			* @param string $sql
47			* @param string $sqlCount
48			*/
49			public function __construct(TDBMService $tdbmService, Schema $schema, string $mainTable, string $sql, string $sqlCount = null)
50			{
51			$this->tdbmService = $tdbmService;
52			$this->schema = $schema;
53			$this->mainTable = $mainTable;
54
55			[$this->processedSql, $this->processedSqlCount, $this->columnDescriptors] = $this->compute($sql, $sqlCount);
56			}
57
58			public function sort($orderBy)
59			{
60			throw new TDBMException('sort not supported for raw sql queries');
61			}
62
63			public function getMagicSql(): string
64			{
65			return $this->processedSql;
66			}
67
68			public function getMagicSqlCount(): string
69			{
70			return $this->processedSqlCount;
71			}
72
73			public function getColumnDescriptors(): array
74			{
75			return $this->columnDescriptors;
76			}
77
78			private function compute(string $sql, ?string $sqlCount)
79			{
80			$parser = new PHPSQLParser();
81			$parsedSql = $parser->parse($sql);
82
83			if (isset($parsedSql['SELECT'])) {
84			[$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedSelectQuery($parsedSql, $sqlCount);
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSql` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history... Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSqlCount` does not exist. Did you mean `$sqlCount`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history... Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$columnDescriptors` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history...
85			} elseif (isset($parsedSql['UNION'])) {
86			[$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedUnionQuery($parsedSql, $sqlCount);
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSqlCount` does not exist. Did you mean `$sqlCount`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
87			} else {
88			throw new TDBMException('Unable to analyze query "'.$sql.'"');
89			}
90
91			return [$processedSql, $processedSqlCount, $columnDescriptors];
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSqlCount` does not exist. Did you mean `$sqlCount`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
92			}
93
94			private function processParsedUnionQuery(array $parsedSql, ?string $sqlCount): array
95			{
96			$selects = $parsedSql['UNION'];
97
98			$parsedSqlList = [];
99			$columnDescriptors = [];
100
101			foreach ($selects as $select) {
102			[$selectProcessedSql, $selectProcessedCountSql, $columnDescriptors] = $this->processParsedSelectQuery($select, '');
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$selectProcessedSql` does not exist. Did you mean `$select`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history... Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$selectProcessedCountSql` does not exist. Did you mean `$select`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
103
104			// Let's reparse the returned SQL (not the most efficient way of doing things)
105			$parser = new PHPSQLParser();
106			$parsedSql = $parser->parse($selectProcessedSql);
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$selectProcessedSql` does not exist. Did you mean `$select`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
107
108			$parsedSqlList[] = $parsedSql;
109			}
110
111			// Let's rebuild the UNION query
112			$query = ['UNION' => $parsedSqlList];
113
114			// The count is the SUM of the count of the UNIONs
115			$countQuery = $this->generateWrappedSqlCount($query);
116
117			$generator = new PHPSQLCreator();
118
119			$processedSql = $generator->create($query);
120			$processedSqlCount = $generator->create($countQuery);
121
122			return [$processedSql, $sqlCount ?? $processedSqlCount, $columnDescriptors];
123			}
124
125			/**
126			* @param array $parsedSql
127			* @param null\|string $sqlCount
128			* @return mixed[]
129			*/
130			private function processParsedSelectQuery(array $parsedSql, ?string $sqlCount): array
131			{
132			// 1: let's reformat the SELECT and construct our columns
133			list($select, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
134			$generator = new PHPSQLCreator();
135			$parsedSql['SELECT'] = $select;
136			$processedSql = $generator->create($parsedSql);
137
138			// 2: let's compute the count query if needed
139			if ($sqlCount === null) {
140			$parsedSqlCount = $this->generateParsedSqlCount($parsedSql);
141			$processedSqlCount = $generator->create($parsedSqlCount);
142			} else {
143			$processedSqlCount = $sqlCount;
144			}
145
146			return [$processedSql, $processedSqlCount, $columnDescriptors];
147			}
148
149			private function formatSelect($baseSelect)
150			{
151			$relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable);
152			$tableGroup = $this->getTableGroupName($relatedTables);
153
154			$connection = $this->tdbmService->getConnection();
155			$formattedSelect = [];
156			$columnDescritors = [];
157			$fetchedTables = [];
158
159			foreach ($baseSelect as $entry) {
160			if ($entry['expr_type'] !== 'colref') {
161			$formattedSelect[] = $entry;
162			continue;
163			}
164
165			$noQuotes = $entry['no_quotes'];
166			if ($noQuotes['delim'] !== '.' \|\| count($noQuotes['parts']) !== 2) {
167			$formattedSelect[] = $entry;
168			continue;
169			}
170
171			$tableName = $noQuotes['parts'][0];
172			if (!in_array($tableName, $relatedTables)) {
173			$formattedSelect[] = $entry;
174			continue;
175			}
176
177			$columnName = $noQuotes['parts'][1];
178			if ($columnName !== '*') {
179			$formattedSelect[] = $entry;
180			continue;
181			}
182
183			$table = $this->schema->getTable($tableName);
184			foreach ($table->getColumns() as $column) {
185			$columnName = $column->getName();
186			$alias = "{$tableName}____{$columnName}";
187			$formattedSelect[] = [
188			'expr_type' => 'colref',
189			'base_expr' => $connection->quoteIdentifier($tableName).'.'.$connection->quoteIdentifier($columnName),
190			'no_quotes' => [
191			'delim' => '.',
192			'parts' => [
193			$tableName,
194			$columnName
195			]
196			],
197			'alias' => [
198			'as' => true,
199			'name' => $alias,
200			]
201			];
202
203			$columnDescritors[$alias] = [
204			'as' => $alias,
205			'table' => $tableName,
206			'column' => $columnName,
207			'type' => $column->getType(),
208			'tableGroup' => $tableGroup,
209			];
210			}
211			$fetchedTables[] = $tableName;
212			}
213
214			$missingTables = array_diff($relatedTables, $fetchedTables);
215			if (!empty($missingTables)) {
216			throw new TDBMException('Missing tables '.implode(', ', $missingTables).' in SELECT statement');
217			}
218
219			for ($i = 0; $i < count($formattedSelect) - 1; $i++) {
220			$formattedSelect[$i]['delim'] = ',';
221			}
222			return [$formattedSelect, $columnDescritors];
223			}
224
225			private function generateParsedSqlCount($parsedSql)
226			{
227			if (isset($parsedSql['ORDER'])) {
228			unset($parsedSql['ORDER']);
229			}
230
231			if (!isset($parsedSql['GROUP'])) {
232			// most simple case:no GROUP BY in query
233			return $this->generateSimpleSqlCount($parsedSql);
234			} elseif (!isset($parsedSql['HAVING'])) {
235			// GROUP BY without HAVING statement: let's COUNT the DISTINCT grouped columns
236			return $this->generateGroupedSqlCount($parsedSql);
237			} else {
238			// GROUP BY with a HAVING statement: we'll have to wrap the query
239			return $this->generateWrappedSqlCount($parsedSql);
240			}
241			}
242
243			private function generateSimpleSqlCount($parsedSql)
244			{
245			$parsedSql['SELECT'] = [[
246			'expr_type' => 'aggregate_function',
247			'alias' => [
248			'as' => true,
249			'name' => 'cnt',
250			],
251			'base_expr' => 'COUNT',
252			'sub_tree' => $parsedSql['SELECT'],
253			'delim' => false,
254			]];
255
256			return $parsedSql;
257			}
258
259			private function generateGroupedSqlCount($parsedSql)
260			{
261			$group = $parsedSql['GROUP'];
262			unset($parsedSql['GROUP']);
263			$parsedSql['SELECT'] = [[
264			'expr_type' => 'aggregate_function',
265			'alias' => [
266			'as' => true,
267			'name' => 'cnt',
268			],
269			'base_expr' => 'COUNT',
270			'sub_tree' => array_merge([[
271			'expr_type' => 'reserved',
272			'base_expr' => 'DISTINCT',
273			'delim' => ','
274			]], $group),
275			'delim' => false,
276			]];
277			return $parsedSql;
278			}
279
280			private function generateWrappedSqlCount($parsedSql)
281			{
282			return [
283			'SELECT' => [[
284			'expr_type' => 'aggregate_function',
285			'alias' => [
286			'as' => true,
287			'name' => 'cnt',
288			],
289			'base_expr' => 'COUNT',
290			'sub_tree' => [
291			[
292			'expr_type' => 'colref',
293			'base_expr' => '*',
294			'sub_tree' => false
295			]
296			],
297			'delim' => false,
298			]],
299			'FROM' => [[
300			'expr_type' => 'subquery',
301			'alias' => [
302			'as' => true,
303			'name' => '____query'
304			],
305			'sub_tree' => $parsedSql,
306			]]
307			];
308			}
309
310			protected function getTableGroupName(array $relatedTables)
311			{
312			sort($relatedTables);
313			return implode('_``_', $relatedTables);
314			}
315			}
316

thecodingmachine / tdbm

Pull Request — master (#52)

processParsedUnionQuery() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like