AbstractQueryFactory::getSubQueryColumnDescriptors() - Code Metrics - Inspection of "[WIP] Filtering by result iterator" - thecodingmachine/tdbm - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#161)

by David

created 2019-09-02 13:19 UTC

getSubQueryColumnDescriptors() A

↳ Parent: AbstractQueryFactory

Complexity

Conditions	3
Paths	2

Size

Total Lines	15
Code Lines	9

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	9
c	0
b	0
f	0
dl	0
loc	15
rs	9.9666
cc	3
nc	2
nop	0

<?php
declare(strict_types=1);

namespace TheCodingMachine\TDBM\QueryFactory;

use function array_unique;
use Doctrine\DBAL\Platforms\MySqlPlatform;
use Doctrine\DBAL\Schema\Schema;
use function in_array;
use TheCodingMachine\TDBM\OrderByAnalyzer;
use TheCodingMachine\TDBM\TDBMInvalidArgumentException;
use TheCodingMachine\TDBM\TDBMService;
use TheCodingMachine\TDBM\UncheckedOrderBy;

abstract class AbstractQueryFactory implements QueryFactory
{
    /**
     * @var TDBMService
     */
    protected $tdbmService;

    /**
     * @var Schema
     */
    protected $schema;

    /**
     * @var OrderByAnalyzer
     */
    protected $orderByAnalyzer;

    /**
     * @var string|UncheckedOrderBy|null
     */
    protected $orderBy;

    /**
     * @var string|null
     */
    protected $magicSql;
    /**
     * @var string|null
     */
    protected $magicSqlCount;
    /**
     * @var string|null
     */
    protected $magicSqlSubQuery;
    protected $columnDescList;
    protected $subQueryColumnDescList;
    /**
     * @var string
     */
    protected $mainTable;

    /**
     * @param TDBMService $tdbmService
     * @param Schema $schema
     * @param OrderByAnalyzer $orderByAnalyzer
     * @param string|UncheckedOrderBy|null $orderBy
     */
    public function __construct(TDBMService $tdbmService, Schema $schema, OrderByAnalyzer $orderByAnalyzer, string $mainTable, $orderBy)
    {
        $this->tdbmService = $tdbmService;
        $this->schema = $schema;
        $this->orderByAnalyzer = $orderByAnalyzer;
        $this->orderBy = $orderBy;
        $this->mainTable = $mainTable;
    }

    /**
     * Returns the column list that must be fetched for the SQL request.
     *
     * Note: MySQL dictates that ORDER BYed columns should appear in the SELECT clause.
     *
     * @param string $mainTable
     * @param string[] $additionalTablesFetch
     * @param string|UncheckedOrderBy|null $orderBy
     *
     * @param bool $canAddAdditionalTablesFetch Set to true if the function can add additional tables to fetch (so if the factory generates its own FROM clause)
     * @return mixed[] A 3 elements array: [$columnDescList, $columnsList, $reconstructedOrderBy]
     */
    protected function getColumnsList(string $mainTable, array $additionalTablesFetch = array(), $orderBy = null, bool $canAddAdditionalTablesFetch = false): array
    {
        // From the table name and the additional tables we want to fetch, let's build a list of all tables
        // that must be part of the select columns.

        $connection = $this->tdbmService->getConnection();

        $tableGroups = [];
        $allFetchedTables = $this->tdbmService->_getRelatedTablesByInheritance($mainTable);
        $tableGroupName = $this->getTableGroupName($allFetchedTables);
        foreach ($allFetchedTables as $table) {
            $tableGroups[$table] = $tableGroupName;
        }

        $columnsList = [];
        $columnDescList = [];
        $sortColumn = 0;
        $reconstructedOrderBy = null;

        if (is_string($orderBy)) {
            $orderBy = trim($orderBy);
            if ($orderBy === '') {
                $orderBy = null;
            }
        }

        // Now, let's deal with "order by columns"
        if ($orderBy !== null) {
            $securedOrderBy = true;
            $reconstructedOrderBys = [];
            if ($orderBy instanceof UncheckedOrderBy) {
                $securedOrderBy = false;
                $orderBy = $orderBy->getOrderBy();
                $reconstructedOrderBy = $orderBy;
            }
            $orderByColumns = $this->orderByAnalyzer->analyzeOrderBy($orderBy);

            // If we sort by a column, there is a high chance we will fetch the bean containing this column.
            // Hence, we should add the table to the $additionalTablesFetch
            foreach ($orderByColumns as $orderByColumn) {
                if ($orderByColumn['type'] === 'colref') {
                    if ($orderByColumn['table'] !== null) {
                        if ($canAddAdditionalTablesFetch) {
                            $additionalTablesFetch[] = $orderByColumn['table'];
                        } else {
                            $sortColumnName = 'sort_column_'.$sortColumn;
                            $mysqlPlatform = new MySqlPlatform();
                            $columnsList[] = $mysqlPlatform->quoteIdentifier($orderByColumn['table']).'.'.$mysqlPlatform->quoteIdentifier($orderByColumn['column']).' as '.$sortColumnName;
                            $columnDescList[$sortColumnName] = [
                                'tableGroup' => null,
                            ];
                            ++$sortColumn;
                        }
                    }
                    if ($securedOrderBy) {
                        // Let's protect via MySQL since we go through MagicJoin
                        $mysqlPlatform = new MySqlPlatform();
                        $reconstructedOrderBys[] = ($orderByColumn['table'] !== null ? $mysqlPlatform->quoteIdentifier($orderByColumn['table']).'.' : '').$mysqlPlatform->quoteIdentifier($orderByColumn['column']).' '.$orderByColumn['direction'];
                    }
                } elseif ($orderByColumn['type'] === 'expr') {
                    $sortColumnName = 'sort_column_'.$sortColumn;
                    $columnsList[] = $orderByColumn['expr'].' as '.$sortColumnName;
                    $columnDescList[$sortColumnName] = [
                        'tableGroup' => null,
                    ];
                    ++$sortColumn;

                    if ($securedOrderBy) {
                        throw new TDBMInvalidArgumentException('Invalid ORDER BY column: "'.$orderByColumn['expr'].'". If you want to use expression in your ORDER BY clause, you must wrap them in a UncheckedOrderBy object. For instance: new UncheckedOrderBy("col1 + col2 DESC")');
                    }
                }
            }

            if ($reconstructedOrderBy === null) {
                $reconstructedOrderBy = implode(', ', $reconstructedOrderBys);
            }
        }

        foreach ($additionalTablesFetch as $additionalTable) {
            if (in_array($additionalTable, $allFetchedTables, true)) {
                continue;
            }

            $relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($additionalTable);
            $tableGroupName = $this->getTableGroupName($relatedTables);
            foreach ($relatedTables as $table) {
                $tableGroups[$table] = $tableGroupName;
            }
            $allFetchedTables = array_merge($allFetchedTables, $relatedTables);
        }

        // Let's remove any duplicate
        $allFetchedTables = array_unique($allFetchedTables);
        
        // We quote in MySQL because MagicJoin requires MySQL style quotes
        $mysqlPlatform = new MySqlPlatform();

        // Now, let's build the column list
        foreach ($allFetchedTables as $table) {
            foreach ($this->schema->getTable($table)->getColumns() as $column) {
                $columnName = $column->getName();
                $columnDescList[$table.'____'.$columnName] = [
                    'as' => $table.'____'.$columnName,
                    'table' => $table,
                    'column' => $columnName,
                    'type' => $column->getType(),
                    'tableGroup' => $tableGroups[$table],
                ];
                $columnsList[] = $mysqlPlatform->quoteIdentifier($table).'.'.$mysqlPlatform->quoteIdentifier($columnName).' as '.
                    $connection->quoteIdentifier($table.'____'.$columnName);
            }
        }

        return [$columnDescList, $columnsList, $reconstructedOrderBy];
    }

    abstract protected function compute(): void;

    /**
     * Returns an identifier for the group of tables passed in parameter.
     *
     * @param string[] $relatedTables
     *
     * @return string
     */
    protected function getTableGroupName(array $relatedTables): string
    {
        sort($relatedTables);

        return implode('_``_', $relatedTables);
    }

    public function getMagicSql() : string
    {
        if ($this->magicSql === null) {
            $this->compute();
        }

        return $this->magicSql;

    }

    public function getMagicSqlCount() : string
    {
        if ($this->magicSqlCount === null) {
            $this->compute();
        }

        return $this->magicSqlCount;

    }

    public function getMagicSqlSubQuery() : string
    {
        if ($this->magicSqlSubQuery === null) {
            $this->compute();
        }

        return $this->magicSqlSubQuery;

    }

    public function getColumnDescriptors() : array
    {
        if ($this->columnDescList === null) {
            $this->compute();
        }

        return $this->columnDescList;
    }

    /**
     * @return string[][] An array of column descriptors. Value is an array with those keys: table, column
     */
    public function getSubQueryColumnDescriptors() : array
    {
        if ($this->subQueryColumnDescList === null) {
            $columns = $this->tdbmService->getPrimaryKeyColumns($this->mainTable);
            $descriptors = [];
            foreach ($columns as $column) {
                $descriptors[] = [
                    'table' => $this->mainTable,
                    'column' => $column
                ];
            }
            $this->subQueryColumnDescList = $descriptors;
        }

        return $this->subQueryColumnDescList;
    }


    /**
     * Sets the ORDER BY directive executed in SQL.
     *
     * For instance:
     *
     *  $queryFactory->sort('label ASC, status DESC');
     *
     * **Important:** TDBM does its best to protect you from SQL injection. In particular, it will only allow column names in the "ORDER BY" clause. This means you are safe to pass input from the user directly in the ORDER BY parameter.
     * If you want to pass an expression to the ORDER BY clause, you will need to tell TDBM to stop checking for SQL injections. You do this by passing a `UncheckedOrderBy` object as a parameter:
     *
     *  $queryFactory->sort(new UncheckedOrderBy('RAND()'))
     *
     * @param string|UncheckedOrderBy|null $orderBy
     */
    public function sort($orderBy): void
    {
        $this->orderBy = $orderBy;
        $this->magicSql = null;
        $this->magicSqlCount = null;
        $this->columnDescList = null;
    }
}


1			<?php
2			declare(strict_types=1);
3
4			namespace TheCodingMachine\TDBM\QueryFactory;
5
6			use function array_unique;
7			use Doctrine\DBAL\Platforms\MySqlPlatform;
8			use Doctrine\DBAL\Schema\Schema;
9			use function in_array;
10			use TheCodingMachine\TDBM\OrderByAnalyzer;
11			use TheCodingMachine\TDBM\TDBMInvalidArgumentException;
12			use TheCodingMachine\TDBM\TDBMService;
13			use TheCodingMachine\TDBM\UncheckedOrderBy;
14
15			abstract class AbstractQueryFactory implements QueryFactory
16			{
17			/**
18			* @var TDBMService
19			*/
20			protected $tdbmService;
21
22			/**
23			* @var Schema
24			*/
25			protected $schema;
26
27			/**
28			* @var OrderByAnalyzer
29			*/
30			protected $orderByAnalyzer;
31
32			/**
33			* @var string\|UncheckedOrderBy\|null
34			*/
35			protected $orderBy;
36
37			/**
38			* @var string\|null
39			*/
40			protected $magicSql;
41			/**
42			* @var string\|null
43			*/
44			protected $magicSqlCount;
45			/**
46			* @var string\|null
47			*/
48			protected $magicSqlSubQuery;
49			protected $columnDescList;
50			protected $subQueryColumnDescList;
51			/**
52			* @var string
53			*/
54			protected $mainTable;
55
56			/**
57			* @param TDBMService $tdbmService
58			* @param Schema $schema
59			* @param OrderByAnalyzer $orderByAnalyzer
60			* @param string\|UncheckedOrderBy\|null $orderBy
61			*/
62			public function __construct(TDBMService $tdbmService, Schema $schema, OrderByAnalyzer $orderByAnalyzer, string $mainTable, $orderBy)
63			{
64			$this->tdbmService = $tdbmService;
65			$this->schema = $schema;
66			$this->orderByAnalyzer = $orderByAnalyzer;
67			$this->orderBy = $orderBy;
68			$this->mainTable = $mainTable;
69			}
70
71			/**
72			* Returns the column list that must be fetched for the SQL request.
73			*
74			* Note: MySQL dictates that ORDER BYed columns should appear in the SELECT clause.
75			*
76			* @param string $mainTable
77			* @param string[] $additionalTablesFetch
78			* @param string\|UncheckedOrderBy\|null $orderBy
79			*
80			* @param bool $canAddAdditionalTablesFetch Set to true if the function can add additional tables to fetch (so if the factory generates its own FROM clause)
81			* @return mixed[] A 3 elements array: [$columnDescList, $columnsList, $reconstructedOrderBy]
82			*/
83			protected function getColumnsList(string $mainTable, array $additionalTablesFetch = array(), $orderBy = null, bool $canAddAdditionalTablesFetch = false): array
84			{
85			// From the table name and the additional tables we want to fetch, let's build a list of all tables
86			// that must be part of the select columns.
87
88			$connection = $this->tdbmService->getConnection();
89
90			$tableGroups = [];
91			$allFetchedTables = $this->tdbmService->_getRelatedTablesByInheritance($mainTable);
92			$tableGroupName = $this->getTableGroupName($allFetchedTables);
93			foreach ($allFetchedTables as $table) {
94			$tableGroups[$table] = $tableGroupName;
95			}
96
97			$columnsList = [];
98			$columnDescList = [];
99			$sortColumn = 0;
100			$reconstructedOrderBy = null;
101
102			if (is_string($orderBy)) {
103			$orderBy = trim($orderBy);
104			if ($orderBy === '') {
105			$orderBy = null;
106			}
107			}
108
109			// Now, let's deal with "order by columns"
110			if ($orderBy !== null) {
111			$securedOrderBy = true;
112			$reconstructedOrderBys = [];
113			if ($orderBy instanceof UncheckedOrderBy) {
114			$securedOrderBy = false;
115			$orderBy = $orderBy->getOrderBy();
116			$reconstructedOrderBy = $orderBy;
117			}
118			$orderByColumns = $this->orderByAnalyzer->analyzeOrderBy($orderBy);
119
120			// If we sort by a column, there is a high chance we will fetch the bean containing this column.
121			// Hence, we should add the table to the $additionalTablesFetch
122			foreach ($orderByColumns as $orderByColumn) {
123			if ($orderByColumn['type'] === 'colref') {
124			if ($orderByColumn['table'] !== null) {
125			if ($canAddAdditionalTablesFetch) {
126			$additionalTablesFetch[] = $orderByColumn['table'];
127			} else {
128			$sortColumnName = 'sort_column_'.$sortColumn;
129			$mysqlPlatform = new MySqlPlatform();
130			$columnsList[] = $mysqlPlatform->quoteIdentifier($orderByColumn['table']).'.'.$mysqlPlatform->quoteIdentifier($orderByColumn['column']).' as '.$sortColumnName;
131			$columnDescList[$sortColumnName] = [
132			'tableGroup' => null,
133			];
134			++$sortColumn;
135			}
136			}
137			if ($securedOrderBy) {
138			// Let's protect via MySQL since we go through MagicJoin
139			$mysqlPlatform = new MySqlPlatform();
140			$reconstructedOrderBys[] = ($orderByColumn['table'] !== null ? $mysqlPlatform->quoteIdentifier($orderByColumn['table']).'.' : '').$mysqlPlatform->quoteIdentifier($orderByColumn['column']).' '.$orderByColumn['direction'];
141			}
142			} elseif ($orderByColumn['type'] === 'expr') {
143			$sortColumnName = 'sort_column_'.$sortColumn;
144			$columnsList[] = $orderByColumn['expr'].' as '.$sortColumnName;
145			$columnDescList[$sortColumnName] = [
146			'tableGroup' => null,
147			];
148			++$sortColumn;
149
150			if ($securedOrderBy) {
151			throw new TDBMInvalidArgumentException('Invalid ORDER BY column: "'.$orderByColumn['expr'].'". If you want to use expression in your ORDER BY clause, you must wrap them in a UncheckedOrderBy object. For instance: new UncheckedOrderBy("col1 + col2 DESC")');
152			}
153			}
154			}
155
156			if ($reconstructedOrderBy === null) {
157			$reconstructedOrderBy = implode(', ', $reconstructedOrderBys);
158			}
159			}
160
161			foreach ($additionalTablesFetch as $additionalTable) {
162			if (in_array($additionalTable, $allFetchedTables, true)) {
163			continue;
164			}
165
166			$relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($additionalTable);
167			$tableGroupName = $this->getTableGroupName($relatedTables);
168			foreach ($relatedTables as $table) {
169			$tableGroups[$table] = $tableGroupName;
170			}
171			$allFetchedTables = array_merge($allFetchedTables, $relatedTables);
172			}
173
174			// Let's remove any duplicate
175			$allFetchedTables = array_unique($allFetchedTables);
176
177			// We quote in MySQL because MagicJoin requires MySQL style quotes
178			$mysqlPlatform = new MySqlPlatform();
179
180			// Now, let's build the column list
181			foreach ($allFetchedTables as $table) {
182			foreach ($this->schema->getTable($table)->getColumns() as $column) {
183			$columnName = $column->getName();
184			$columnDescList[$table.'____'.$columnName] = [
185			'as' => $table.'____'.$columnName,
186			'table' => $table,
187			'column' => $columnName,
188			'type' => $column->getType(),
189			'tableGroup' => $tableGroups[$table],
190			];
191			$columnsList[] = $mysqlPlatform->quoteIdentifier($table).'.'.$mysqlPlatform->quoteIdentifier($columnName).' as '.
192			$connection->quoteIdentifier($table.'____'.$columnName);
193			}
194			}
195
196			return [$columnDescList, $columnsList, $reconstructedOrderBy];
197			}
198
199			abstract protected function compute(): void;
200
201			/**
202			* Returns an identifier for the group of tables passed in parameter.
203			*
204			* @param string[] $relatedTables
205			*
206			* @return string
207			*/
208			protected function getTableGroupName(array $relatedTables): string
209			{
210			sort($relatedTables);
211
212			return implode('_``_', $relatedTables);
213			}
214
215			public function getMagicSql() : string
216			{
217			if ($this->magicSql === null) {
218			$this->compute();
219			}
220
221			return $this->magicSql;
			0 ignored issues – show Bug Best Practice introduced 2019-08-16 13:58 UTC by Report Bug Copy Issue Report The expression `return $this->magicSql` could return the type `null` which is incompatible with the type-hinted return `string`. Consider adding an additional type-check to rule them out. Loading history...
222			}
223
224			public function getMagicSqlCount() : string
225			{
226			if ($this->magicSqlCount === null) {
227			$this->compute();
228			}
229
230			return $this->magicSqlCount;
			0 ignored issues – show Bug Best Practice introduced 2019-08-16 13:58 UTC by Report Bug Copy Issue Report The expression `return $this->magicSqlCount` could return the type `null` which is incompatible with the type-hinted return `string`. Consider adding an additional type-check to rule them out. Loading history...
231			}
232
233			public function getMagicSqlSubQuery() : string
234			{
235			if ($this->magicSqlSubQuery === null) {
236			$this->compute();
237			}
238
239			return $this->magicSqlSubQuery;
			0 ignored issues – show Bug Best Practice introduced 2019-08-16 13:58 UTC by Report Bug Copy Issue Report The expression `return $this->magicSqlSubQuery` could return the type `null` which is incompatible with the type-hinted return `string`. Consider adding an additional type-check to rule them out. Loading history...
240			}
241
242			public function getColumnDescriptors() : array
243			{
244			if ($this->columnDescList === null) {
245			$this->compute();
246			}
247
248			return $this->columnDescList;
249			}
250
251			/**
252			* @return string[][] An array of column descriptors. Value is an array with those keys: table, column
253			*/
254			public function getSubQueryColumnDescriptors() : array
255			{
256			if ($this->subQueryColumnDescList === null) {
257			$columns = $this->tdbmService->getPrimaryKeyColumns($this->mainTable);
258			$descriptors = [];
259			foreach ($columns as $column) {
260			$descriptors[] = [
261			'table' => $this->mainTable,
262			'column' => $column
263			];
264			}
265			$this->subQueryColumnDescList = $descriptors;
266			}
267
268			return $this->subQueryColumnDescList;
269			}
270
271
272			/**
273			* Sets the ORDER BY directive executed in SQL.
274			*
275			* For instance:
276			*
277			* $queryFactory->sort('label ASC, status DESC');
278			*
279			* Important: TDBM does its best to protect you from SQL injection. In particular, it will only allow column names in the "ORDER BY" clause. This means you are safe to pass input from the user directly in the ORDER BY parameter.
280			* If you want to pass an expression to the ORDER BY clause, you will need to tell TDBM to stop checking for SQL injections. You do this by passing a `UncheckedOrderBy` object as a parameter:
281			*
282			* $queryFactory->sort(new UncheckedOrderBy('RAND()'))
283			*
284			* @param string\|UncheckedOrderBy\|null $orderBy
285			*/
286			public function sort($orderBy): void
287			{
288			$this->orderBy = $orderBy;
289			$this->magicSql = null;
290			$this->magicSqlCount = null;
291			$this->columnDescList = null;
292			}
293			}
294

thecodingmachine / tdbm

Pull Request — master (#161)

getSubQueryColumnDescriptors() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like