AbstractQueryFactory::getColumnAlias() - Code Metrics - Inspection of "Oracle: Fix `ORA-00972: identifier is too long` (3..." - thecodingmachine/tdbm - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — fix/oracle-identifier-too-long ( 53568b )

unknown

created 2020-06-30 09:11 UTC

AbstractQueryFactory::getColumnAlias() A

↳ Parent: AbstractQueryFactory

Complexity

Conditions	2
Paths	2

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

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

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

    protected $magicSql;
    protected $magicSqlCount;
    protected $columnDescList;

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

    /**
     * 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();
                $alias = self::getColumnAlias($table, $columnName);
                $columnDescList[$alias] = [
                    'as' => $alias,
                    'table' => $table,
                    'column' => $columnName,
                    'type' => $column->getType(),
                    'tableGroup' => $tableGroups[$table],
                ];
                $columnsList[] = sprintf(
                    '%s.%s as %s',
                    $mysqlPlatform->quoteIdentifier($table),
                    $mysqlPlatform->quoteIdentifier($columnName),
                    $connection->quoteIdentifier($alias)
                );
            }
        }

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

    public static function getColumnAlias(string $tableName, string $columnName): string
    {
        $alias = $tableName.'____'.$columnName;
        if (strlen($alias) <= 30) { // Older oracle version had a limit of 30 characters for identifiers
            return $alias;
        }
        return substr($columnName, 0, 20) . crc32($tableName.'____'.$columnName);
    }

    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 getColumnDescriptors() : array
    {
        if ($this->columnDescList === null) {
            $this->compute();
        }

        return $this->columnDescList;
    }

    /**
     * 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			protected $magicSql;
38			protected $magicSqlCount;
39			protected $columnDescList;
40
41			/**
42			* @param TDBMService $tdbmService
43			* @param Schema $schema
44			* @param OrderByAnalyzer $orderByAnalyzer
45			* @param string\|UncheckedOrderBy\|null $orderBy
46			*/
47			public function __construct(TDBMService $tdbmService, Schema $schema, OrderByAnalyzer $orderByAnalyzer, $orderBy)
48			{
49			$this->tdbmService = $tdbmService;
50			$this->schema = $schema;
51			$this->orderByAnalyzer = $orderByAnalyzer;
52			$this->orderBy = $orderBy;
53			}
54
55			/**
56			* Returns the column list that must be fetched for the SQL request.
57			*
58			* Note: MySQL dictates that ORDER BYed columns should appear in the SELECT clause.
59			*
60			* @param string $mainTable
61			* @param string[] $additionalTablesFetch
62			* @param string\|UncheckedOrderBy\|null $orderBy
63			*
64			* @param bool $canAddAdditionalTablesFetch Set to true if the function can add additional tables to fetch (so if the factory generates its own FROM clause)
65			* @return mixed[] A 3 elements array: [$columnDescList, $columnsList, $reconstructedOrderBy]
66			*/
67			protected function getColumnsList(string $mainTable, array $additionalTablesFetch = array(), $orderBy = null, bool $canAddAdditionalTablesFetch = false): array
68			{
69			// From the table name and the additional tables we want to fetch, let's build a list of all tables
70			// that must be part of the select columns.
71
72			$connection = $this->tdbmService->getConnection();
73
74			$tableGroups = [];
75			$allFetchedTables = $this->tdbmService->_getRelatedTablesByInheritance($mainTable);
76			$tableGroupName = $this->getTableGroupName($allFetchedTables);
77			foreach ($allFetchedTables as $table) {
78			$tableGroups[$table] = $tableGroupName;
79			}
80
81			$columnsList = [];
82			$columnDescList = [];
83			$sortColumn = 0;
84			$reconstructedOrderBy = null;
85
86			if (is_string($orderBy)) {
87			$orderBy = trim($orderBy);
88			if ($orderBy === '') {
89			$orderBy = null;
90			}
91			}
92
93			// Now, let's deal with "order by columns"
94			if ($orderBy !== null) {
95			$securedOrderBy = true;
96			$reconstructedOrderBys = [];
97			if ($orderBy instanceof UncheckedOrderBy) {
98			$securedOrderBy = false;
99			$orderBy = $orderBy->getOrderBy();
100			$reconstructedOrderBy = $orderBy;
101			}
102			$orderByColumns = $this->orderByAnalyzer->analyzeOrderBy($orderBy);
103
104			// If we sort by a column, there is a high chance we will fetch the bean containing this column.
105			// Hence, we should add the table to the $additionalTablesFetch
106			foreach ($orderByColumns as $orderByColumn) {
107			if ($orderByColumn['type'] === 'colref') {
108			if ($orderByColumn['table'] !== null) {
109			if ($canAddAdditionalTablesFetch) {
110			$additionalTablesFetch[] = $orderByColumn['table'];
111			} else {
112			$sortColumnName = 'sort_column_'.$sortColumn;
113			$mysqlPlatform = new MySqlPlatform();
114			$columnsList[] = $mysqlPlatform->quoteIdentifier($orderByColumn['table']).'.'.$mysqlPlatform->quoteIdentifier($orderByColumn['column']).' as '.$sortColumnName;
115			$columnDescList[$sortColumnName] = [
116			'tableGroup' => null,
117			];
118			++$sortColumn;
119			}
120			}
121			if ($securedOrderBy) {
122			// Let's protect via MySQL since we go through MagicJoin
123			$mysqlPlatform = new MySqlPlatform();
124			$reconstructedOrderBys[] = ($orderByColumn['table'] !== null ? $mysqlPlatform->quoteIdentifier($orderByColumn['table']).'.' : '').$mysqlPlatform->quoteIdentifier($orderByColumn['column']).' '.$orderByColumn['direction'];
125			}
126			} elseif ($orderByColumn['type'] === 'expr') {
127			$sortColumnName = 'sort_column_'.$sortColumn;
128			$columnsList[] = $orderByColumn['expr'].' as '.$sortColumnName;
129			$columnDescList[$sortColumnName] = [
130			'tableGroup' => null,
131			];
132			++$sortColumn;
133
134			if ($securedOrderBy) {
135			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")');
136			}
137			}
138			}
139
140			if ($reconstructedOrderBy === null) {
141			$reconstructedOrderBy = implode(', ', $reconstructedOrderBys);
142			}
143			}
144
145			foreach ($additionalTablesFetch as $additionalTable) {
146			if (in_array($additionalTable, $allFetchedTables, true)) {
147			continue;
148			}
149
150			$relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($additionalTable);
151			$tableGroupName = $this->getTableGroupName($relatedTables);
152			foreach ($relatedTables as $table) {
153			$tableGroups[$table] = $tableGroupName;
154			}
155			$allFetchedTables = array_merge($allFetchedTables, $relatedTables);
156			}
157
158			// Let's remove any duplicate
159			$allFetchedTables = array_unique($allFetchedTables);
160
161			// We quote in MySQL because MagicJoin requires MySQL style quotes
162			$mysqlPlatform = new MySqlPlatform();
163
164			// Now, let's build the column list
165			foreach ($allFetchedTables as $table) {
166			foreach ($this->schema->getTable($table)->getColumns() as $column) {
167			$columnName = $column->getName();
168			$alias = self::getColumnAlias($table, $columnName);
169			$columnDescList[$alias] = [
170			'as' => $alias,
171			'table' => $table,
172			'column' => $columnName,
173			'type' => $column->getType(),
174			'tableGroup' => $tableGroups[$table],
175			];
176			$columnsList[] = sprintf(
177			'%s.%s as %s',
178			$mysqlPlatform->quoteIdentifier($table),
179			$mysqlPlatform->quoteIdentifier($columnName),
180			$connection->quoteIdentifier($alias)
181			);
182			}
183			}
184
185			return [$columnDescList, $columnsList, $reconstructedOrderBy];
186			}
187
188			public static function getColumnAlias(string $tableName, string $columnName): string
189			{
190			$alias = $tableName.'____'.$columnName;
191			if (strlen($alias) <= 30) { // Older oracle version had a limit of 30 characters for identifiers
192			return $alias;
193			}
194			return substr($columnName, 0, 20) . crc32($tableName.'____'.$columnName);
195			}
196
197			abstract protected function compute(): void;
198
199			/**
200			* Returns an identifier for the group of tables passed in parameter.
201			*
202			* @param string[] $relatedTables
203			*
204			* @return string
205			*/
206			protected function getTableGroupName(array $relatedTables): string
207			{
208			sort($relatedTables);
209
210			return implode('_``_', $relatedTables);
211			}
212
213			public function getMagicSql() : string
214			{
215			if ($this->magicSql === null) {
216			$this->compute();
217			}
218
219			return $this->magicSql;
220			}
221
222			public function getMagicSqlCount() : string
223			{
224			if ($this->magicSqlCount === null) {
225			$this->compute();
226			}
227
228			return $this->magicSqlCount;
229			}
230
231			public function getColumnDescriptors() : array
232			{
233			if ($this->columnDescList === null) {
234			$this->compute();
235			}
236
237			return $this->columnDescList;
238			}
239
240			/**
241			* Sets the ORDER BY directive executed in SQL.
242			*
243			* For instance:
244			*
245			* $queryFactory->sort('label ASC, status DESC');
246			*
247			* 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.
248			* 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:
249			*
250			* $queryFactory->sort(new UncheckedOrderBy('RAND()'))
251			*
252			* @param string\|UncheckedOrderBy\|null $orderBy
253			*/
254			public function sort($orderBy): void
255			{
256			$this->orderBy = $orderBy;
257			$this->magicSql = null;
258			$this->magicSqlCount = null;
259			$this->columnDescList = null;
260			}
261			}
262

thecodingmachine / tdbm

Push — fix/oracle-identifier-too-long ( 53568b )

AbstractQueryFactory::getColumnAlias() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like