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

Completed

Pull Request — 4.3 (#149)

by Dorian

created 2017-06-15 13:44 UTC

getMagicSqlCount() A

↳ Parent: FindObjectsFromRawSqlQueryFactory

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	4
rs	10
c	0
b	0
f	0
cc	1
eloc	2
nc	1
nop	0

<?php

namespace Mouf\Database\TDBM\QueryFactory;


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

thecodingmachine / database.tdbm

Pull Request — 4.3 (#149)

getMagicSqlCount() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like