MagicQuery::setOutputDialect() - Code Metrics - Inspection of "Adding an option to choose the SQL dialect generat..." - thecodingmachine/magic-query - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 1.3 (#56)

by David

created 2019-08-30 15:33 UTC

MagicQuery::setOutputDialect() A

↳ Parent: MagicQuery

Complexity

Conditions	1
Paths	1

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	6
rs	10
c	0
b	0
f	0
cc	1
nc	1
nop	1

<?php

namespace Mouf\Database;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\MySqlPlatform;
use function array_filter;
use function array_keys;
use Doctrine\Common\Cache\VoidCache;
use function hash;
use function implode;
use Mouf\Database\MagicQuery\Twig\SqlTwigEnvironmentFactory;
use Mouf\Database\SchemaAnalyzer\SchemaAnalyzer;
use PHPSQLParser\PHPSQLParser;
use SQLParser\Node\ColRef;
use SQLParser\Node\Equal;
use SQLParser\Node\NodeInterface;
use SQLParser\Node\Table;
use SQLParser\Node\Traverser\DetectMagicJoinSelectVisitor;
use SQLParser\Node\Traverser\DetectTablesVisitor;
use SQLParser\Node\Traverser\MagicJoinSelect;
use SQLParser\Node\Traverser\NodeTraverser;
use SQLParser\Query\Select;
use SQLParser\Query\StatementFactory;
use SQLParser\SQLParser;
use SQLParser\SqlRenderInterface;

/**
 * The class MagicQuery offers special SQL voodoo methods to automatically strip down unused parameters
 * from parametrized SQL statements.
 */
class MagicQuery
{
    private $connection;
    private $cache;
    private $schemaAnalyzer;
    /**
     * @var AbstractPlatform
     */
    private $platform;
    /**
     * @var \Twig_Environment
     */
    private $twigEnvironment;
    private $enableTwig = false;

    /**
     * @param \Doctrine\DBAL\Connection    $connection
     * @param \Doctrine\Common\Cache\Cache $cache
     * @param SchemaAnalyzer               $schemaAnalyzer (optional). If not set, it is initialized from the connection.
     */
    public function __construct($connection = null, $cache = null, SchemaAnalyzer $schemaAnalyzer = null)
    {
        $this->connection = $connection;
        $this->platform = $connection ? $connection->getDatabasePlatform() : new MySqlPlatform();
        if ($cache) {
            $this->cache = $cache;
        } else {
            $this->cache = new VoidCache();
        }
        if ($schemaAnalyzer) {
            $this->schemaAnalyzer = $schemaAnalyzer;
        }
    }

    /**
     * Whether Twig parsing should be enabled or not.
     * Defaults to false.
     *
     * @param bool $enableTwig
     *
     * @return $this
     */
    public function setEnableTwig($enableTwig = true)
    {
        $this->enableTwig = $enableTwig;

        return $this;
    }

    /**
     * Overrides the output dialect used to generate SQL. By default, the dialect of the connection is used.
     * If no connection is used, MySQL platform is used by default.
     *
     * @param AbstractPlatform $platform
     */
    public function setOutputDialect(AbstractPlatform $platform): self
    {
        $this->platform = $platform;

        return $this;
    }

    /**
     * Returns merged SQL from $sql and $parameters. Any parameters not available will be striped down
     * from the SQL.
     *
     * This is equivalent to calling `parse` and `toSql` successively.
     *
     * @param string $sql
     * @param array  $parameters
     *
     * @return string
     */
    public function build($sql, array $parameters = array())
    {
        if ($this->enableTwig) {
            $sql = $this->getTwigEnvironment()->render($sql, $parameters);
        }

        $select = $this->parse($sql);
        $newSql = $this->toSql($select, $parameters, true);

        return $newSql;
    }

    /**
     * Returns modified SQL from $sql and $parameters. Any parameters not available will be striped down
     * from the SQL. Unlike with the `build` method, the parameters are NOT merged into the SQL.
     * This method is more efficient than `build`  (because result is cached and statements interpolation
     * can be delegated to the database.
     *
     * @param string $sql
     * @param array  $parameters
     *
     * @return string
     */
    public function buildPreparedStatement(string $sql, array $parameters = []): string
    {
        if ($this->enableTwig) {
            $sql = $this->getTwigEnvironment()->render($sql, $parameters);
        }

        $availableParameterKeys = array_keys(array_filter($parameters, static function($param) { return $param !== null;}));
        // We choose md4 because it is fast.
        $cacheKey = 'request_build_'.hash('md4', $sql.'__'.implode('_/_', $availableParameterKeys));
        $newSql = $this->cache->fetch($cacheKey);
        if ($newSql === false) {
            $select = $this->parse($sql);
            $newSql = $this->toSql($select, $parameters, false);

            $this->cache->save($cacheKey, $newSql);
        }

        return $newSql;
    }

    /**
     * Parses the $sql passed in parameter and returns a tree representation of it.
     * This tree representation can be used to manipulate the SQL.
     *
     * @param string $sql
     *
     * @return NodeInterface
     *
     * @throws MagicQueryMissingConnectionException
     * @throws MagicQueryParserException
     */
    public function parse($sql)
    {
        // We choose md4 because it is fast.
        $cacheKey = 'request_'.hash('md4', $sql);
        $select = $this->cache->fetch($cacheKey);

        if ($select === false) {
            //$parser = new SQLParser();
            $parser = new PHPSQLParser();
            $parsed = $parser->parse($sql);

            if ($parsed == false) {
                throw new MagicQueryParserException('Unable to parse query "'.$sql.'"');
            }

            $select = StatementFactory::toObject($parsed);

            $this->magicJoin($select);

            // Let's store the tree
            $this->cache->save($cacheKey, $select);
        }

        return $select;
    }

    /**
     * Transforms back a tree of SQL node into a SQL string.
     *
     * @param NodeInterface $sqlNode
     * @param array $parameters
     * @param bool $extrapolateParameters Whether the parameters should be fed into the returned SQL query

     * @return string
     */
    public function toSql(NodeInterface $sqlNode, array $parameters = array(), bool $extrapolateParameters = true)
    {
        return (string) $sqlNode->toSql($parameters, $this->platform, 0, SqlRenderInterface::CONDITION_GUESS, $extrapolateParameters);
    }

    /**
     * Scans the SQL statement and replaces the "magicjoin" part with the correct joins.
     *
     * @param NodeInterface $select
     *
     * @throws MagicQueryMissingConnectionException
     */
    private function magicJoin(NodeInterface $select)
    {
        // Let's find if this is a MagicJoin query.
        $magicJoinDetector = new DetectMagicJoinSelectVisitor();
        $nodeTraverser = new NodeTraverser();
        $nodeTraverser->addVisitor($magicJoinDetector);

        $nodeTraverser->walk($select);

        $magicJoinSelects = $magicJoinDetector->getMagicJoinSelects();
        foreach ($magicJoinSelects as $magicJoinSelect) {
            // For each select in the query (there can be nested selects!), let's find the list of tables.
            $this->magicJoinOnOneQuery($magicJoinSelect);
        }
    }

    /**
     * For one given MagicJoin select, let's apply MagicJoin.
     *
     * @param MagicJoinSelect $magicJoinSelect
     *
     * @return Select
     */
    private function magicJoinOnOneQuery(MagicJoinSelect $magicJoinSelect)
    {
        $tableSearchNodeTraverser = new NodeTraverser();
        $detectTableVisitor = new DetectTablesVisitor($magicJoinSelect->getMainTable());
        $tableSearchNodeTraverser->addVisitor($detectTableVisitor);

        $select = $magicJoinSelect->getSelect();

        $tableSearchNodeTraverser->walk($select);
        $tables = $detectTableVisitor->getTables();

        $mainTable = $magicJoinSelect->getMainTable();
        // Let's remove the main table from the list of tables to be linked:
        unset($tables[$mainTable]);

        $foreignKeysSet = [];
        $completePath = [];

        foreach ($tables as $table) {
            $path = $this->getSchemaAnalyzer()->getShortestPath($mainTable, $table);
            foreach ($path as $foreignKey) {
                // If the foreign key is not already in our complete path, let's add it.
                if (!isset($foreignKeysSet[$foreignKey->getName()])) {
                    $completePath[] = $foreignKey;
                    $foreignKeysSet[$foreignKey->getName()] = true;
                }
            }
        }

        // At this point, we have a complete path, we now just have to rewrite the FROM section.
        $tableNode = new Table();
        $tableNode->setTable($mainTable);
        $tables = [
            $mainTable => $tableNode,
        ];

        foreach ($completePath as $foreignKey) {
            /* @var $foreignKey \Doctrine\DBAL\Schema\ForeignKeyConstraint */

            $onNode = new Equal();
            $leftCol = new ColRef();
            $leftCol->setTable($foreignKey->getLocalTableName());
            // For some reasons, with Oracle, DBAL returns quoted identifiers. We need to unquote them.
            $leftCol->setColumn($foreignKey->getUnquotedLocalColumns()[0]);

            $rightCol = new ColRef();
            $rightCol->setTable($foreignKey->getForeignTableName());
            $rightCol->setColumn($foreignKey->getUnquotedForeignColumns()[0]);

            $onNode->setLeftOperand($leftCol);
            $onNode->setRightOperand($rightCol);

            $tableNode = new Table();
            $tableNode->setJoinType('LEFT JOIN');
            $tableNode->setRefClause($onNode);

            if (isset($tables[$foreignKey->getLocalTableName()])) {
                $tableNode->setTable($foreignKey->getForeignTableName());
                $tables[$foreignKey->getForeignTableName()] = $tableNode;
            } else {
                $tableNode->setTable($foreignKey->getLocalTableName());
                $tables[$foreignKey->getLocalTableName()] = $tableNode;
            }
        }

        $select->setFrom($tables);
    }

    /**
     * @return SchemaAnalyzer
     */
    private function getSchemaAnalyzer()
    {
        if ($this->schemaAnalyzer === null) {
            if (!$this->connection) {
                throw new MagicQueryMissingConnectionException('In order to use MagicJoin, you need to configure a DBAL connection.');
            }

            $this->schemaAnalyzer = new SchemaAnalyzer($this->connection->getSchemaManager(), $this->cache, $this->getConnectionUniqueId($this->connection));
        }

        return $this->schemaAnalyzer;
    }

    private function getConnectionUniqueId(Connection $connection)
    {
        return hash('md4', $connection->getHost().'-'.$connection->getPort().'-'.$connection->getDatabase().'-'.$connection->getDriver()->getName());
    }

    /**
     * @return \Twig_Environment
     */
    private function getTwigEnvironment()
    {
        if ($this->twigEnvironment === null) {
            $this->twigEnvironment = SqlTwigEnvironmentFactory::getTwigEnvironment();
        }

        return $this->twigEnvironment;
    }
}


1			<?php
2
3			namespace Mouf\Database;
4
5			use Doctrine\DBAL\Connection;
6			use Doctrine\DBAL\Platforms\AbstractPlatform;
7			use Doctrine\DBAL\Platforms\MySqlPlatform;
8			use function array_filter;
9			use function array_keys;
10			use Doctrine\Common\Cache\VoidCache;
11			use function hash;
12			use function implode;
13			use Mouf\Database\MagicQuery\Twig\SqlTwigEnvironmentFactory;
14			use Mouf\Database\SchemaAnalyzer\SchemaAnalyzer;
15			use PHPSQLParser\PHPSQLParser;
16			use SQLParser\Node\ColRef;
17			use SQLParser\Node\Equal;
18			use SQLParser\Node\NodeInterface;
19			use SQLParser\Node\Table;
20			use SQLParser\Node\Traverser\DetectMagicJoinSelectVisitor;
21			use SQLParser\Node\Traverser\DetectTablesVisitor;
22			use SQLParser\Node\Traverser\MagicJoinSelect;
23			use SQLParser\Node\Traverser\NodeTraverser;
24			use SQLParser\Query\Select;
25			use SQLParser\Query\StatementFactory;
26			use SQLParser\SQLParser;
27			use SQLParser\SqlRenderInterface;
28
29			/**
30			* The class MagicQuery offers special SQL voodoo methods to automatically strip down unused parameters
31			* from parametrized SQL statements.
32			*/
33			class MagicQuery
34			{
35			private $connection;
36			private $cache;
37			private $schemaAnalyzer;
38			/**
39			* @var AbstractPlatform
40			*/
41			private $platform;
42			/**
43			* @var \Twig_Environment
44			*/
45			private $twigEnvironment;
46			private $enableTwig = false;
47
48			/**
49			* @param \Doctrine\DBAL\Connection $connection
50			* @param \Doctrine\Common\Cache\Cache $cache
51			* @param SchemaAnalyzer $schemaAnalyzer (optional). If not set, it is initialized from the connection.
52			*/
53			public function __construct($connection = null, $cache = null, SchemaAnalyzer $schemaAnalyzer = null)
54			{
55			$this->connection = $connection;
56			$this->platform = $connection ? $connection->getDatabasePlatform() : new MySqlPlatform();
57			if ($cache) {
58			$this->cache = $cache;
59			} else {
60			$this->cache = new VoidCache();
61			}
62			if ($schemaAnalyzer) {
63			$this->schemaAnalyzer = $schemaAnalyzer;
64			}
65			}
66
67			/**
68			* Whether Twig parsing should be enabled or not.
69			* Defaults to false.
70			*
71			* @param bool $enableTwig
72			*
73			* @return $this
74			*/
75			public function setEnableTwig($enableTwig = true)
76			{
77			$this->enableTwig = $enableTwig;
78
79			return $this;
80			}
81
82			/**
83			* Overrides the output dialect used to generate SQL. By default, the dialect of the connection is used.
84			* If no connection is used, MySQL platform is used by default.
85			*
86			* @param AbstractPlatform $platform
87			*/
88			public function setOutputDialect(AbstractPlatform $platform): self
89			{
90			$this->platform = $platform;
91
92			return $this;
93			}
94
95			/**
96			* Returns merged SQL from $sql and $parameters. Any parameters not available will be striped down
97			* from the SQL.
98			*
99			* This is equivalent to calling `parse` and `toSql` successively.
100			*
101			* @param string $sql
102			* @param array $parameters
103			*
104			* @return string
105			*/
106			public function build($sql, array $parameters = array())
107			{
108			if ($this->enableTwig) {
109			$sql = $this->getTwigEnvironment()->render($sql, $parameters);
110			}
111
112			$select = $this->parse($sql);
113			$newSql = $this->toSql($select, $parameters, true);
114
115			return $newSql;
116			}
117
118			/**
119			* Returns modified SQL from $sql and $parameters. Any parameters not available will be striped down
120			* from the SQL. Unlike with the `build` method, the parameters are NOT merged into the SQL.
121			* This method is more efficient than `build` (because result is cached and statements interpolation
122			* can be delegated to the database.
123			*
124			* @param string $sql
125			* @param array $parameters
126			*
127			* @return string
128			*/
129			public function buildPreparedStatement(string $sql, array $parameters = []): string
130			{
131			if ($this->enableTwig) {
132			$sql = $this->getTwigEnvironment()->render($sql, $parameters);
133			}
134
135			$availableParameterKeys = array_keys(array_filter($parameters, static function($param) { return $param !== null;}));
136			// We choose md4 because it is fast.
137			$cacheKey = 'request_build_'.hash('md4', $sql.'__'.implode('_/_', $availableParameterKeys));
138			$newSql = $this->cache->fetch($cacheKey);
139			if ($newSql === false) {
140			$select = $this->parse($sql);
141			$newSql = $this->toSql($select, $parameters, false);
142
143			$this->cache->save($cacheKey, $newSql);
144			}
145
146			return $newSql;
147			}
148
149			/**
150			* Parses the $sql passed in parameter and returns a tree representation of it.
151			* This tree representation can be used to manipulate the SQL.
152			*
153			* @param string $sql
154			*
155			* @return NodeInterface
156			*
157			* @throws MagicQueryMissingConnectionException
158			* @throws MagicQueryParserException
159			*/
160			public function parse($sql)
161			{
162			// We choose md4 because it is fast.
163			$cacheKey = 'request_'.hash('md4', $sql);
164			$select = $this->cache->fetch($cacheKey);
165
166			if ($select === false) {
167			//$parser = new SQLParser();
168			$parser = new PHPSQLParser();
169			$parsed = $parser->parse($sql);
170
171			if ($parsed == false) {
172			throw new MagicQueryParserException('Unable to parse query "'.$sql.'"');
173			}
174
175			$select = StatementFactory::toObject($parsed);
176
177			$this->magicJoin($select);
178
179			// Let's store the tree
180			$this->cache->save($cacheKey, $select);
181			}
182
183			return $select;
184			}
185
186			/**
187			* Transforms back a tree of SQL node into a SQL string.
188			*
189			* @param NodeInterface $sqlNode
190			* @param array $parameters
191			* @param bool $extrapolateParameters Whether the parameters should be fed into the returned SQL query
192
193			* @return string
194			*/
195			public function toSql(NodeInterface $sqlNode, array $parameters = array(), bool $extrapolateParameters = true)
196			{
197			return (string) $sqlNode->toSql($parameters, $this->platform, 0, SqlRenderInterface::CONDITION_GUESS, $extrapolateParameters);
198			}
199
200			/**
201			* Scans the SQL statement and replaces the "magicjoin" part with the correct joins.
202			*
203			* @param NodeInterface $select
204			*
205			* @throws MagicQueryMissingConnectionException
206			*/
207			private function magicJoin(NodeInterface $select)
208			{
209			// Let's find if this is a MagicJoin query.
210			$magicJoinDetector = new DetectMagicJoinSelectVisitor();
211			$nodeTraverser = new NodeTraverser();
212			$nodeTraverser->addVisitor($magicJoinDetector);
213
214			$nodeTraverser->walk($select);
215
216			$magicJoinSelects = $magicJoinDetector->getMagicJoinSelects();
217			foreach ($magicJoinSelects as $magicJoinSelect) {
218			// For each select in the query (there can be nested selects!), let's find the list of tables.
219			$this->magicJoinOnOneQuery($magicJoinSelect);
220			}
221			}
222
223			/**
224			* For one given MagicJoin select, let's apply MagicJoin.
225			*
226			* @param MagicJoinSelect $magicJoinSelect
227			*
228			* @return Select
229			*/
230			private function magicJoinOnOneQuery(MagicJoinSelect $magicJoinSelect)
231			{
232			$tableSearchNodeTraverser = new NodeTraverser();
233			$detectTableVisitor = new DetectTablesVisitor($magicJoinSelect->getMainTable());
234			$tableSearchNodeTraverser->addVisitor($detectTableVisitor);
235
236			$select = $magicJoinSelect->getSelect();
237
238			$tableSearchNodeTraverser->walk($select);
239			$tables = $detectTableVisitor->getTables();
240
241			$mainTable = $magicJoinSelect->getMainTable();
242			// Let's remove the main table from the list of tables to be linked:
243			unset($tables[$mainTable]);
244
245			$foreignKeysSet = [];
246			$completePath = [];
247
248			foreach ($tables as $table) {
249			$path = $this->getSchemaAnalyzer()->getShortestPath($mainTable, $table);
250			foreach ($path as $foreignKey) {
251			// If the foreign key is not already in our complete path, let's add it.
252			if (!isset($foreignKeysSet[$foreignKey->getName()])) {
253			$completePath[] = $foreignKey;
254			$foreignKeysSet[$foreignKey->getName()] = true;
255			}
256			}
257			}
258
259			// At this point, we have a complete path, we now just have to rewrite the FROM section.
260			$tableNode = new Table();
261			$tableNode->setTable($mainTable);
262			$tables = [
263			$mainTable => $tableNode,
264			];
265
266			foreach ($completePath as $foreignKey) {
267			/* @var $foreignKey \Doctrine\DBAL\Schema\ForeignKeyConstraint */
268
269			$onNode = new Equal();
270			$leftCol = new ColRef();
271			$leftCol->setTable($foreignKey->getLocalTableName());
272			// For some reasons, with Oracle, DBAL returns quoted identifiers. We need to unquote them.
273			$leftCol->setColumn($foreignKey->getUnquotedLocalColumns()[0]);
274
275			$rightCol = new ColRef();
276			$rightCol->setTable($foreignKey->getForeignTableName());
277			$rightCol->setColumn($foreignKey->getUnquotedForeignColumns()[0]);
278
279			$onNode->setLeftOperand($leftCol);
280			$onNode->setRightOperand($rightCol);
281
282			$tableNode = new Table();
283			$tableNode->setJoinType('LEFT JOIN');
284			$tableNode->setRefClause($onNode);
285
286			if (isset($tables[$foreignKey->getLocalTableName()])) {
287			$tableNode->setTable($foreignKey->getForeignTableName());
288			$tables[$foreignKey->getForeignTableName()] = $tableNode;
289			} else {
290			$tableNode->setTable($foreignKey->getLocalTableName());
291			$tables[$foreignKey->getLocalTableName()] = $tableNode;
292			}
293			}
294
295			$select->setFrom($tables);
296			}
297
298			/**
299			* @return SchemaAnalyzer
300			*/
301			private function getSchemaAnalyzer()
302			{
303			if ($this->schemaAnalyzer === null) {
304			if (!$this->connection) {
305			throw new MagicQueryMissingConnectionException('In order to use MagicJoin, you need to configure a DBAL connection.');
306			}
307
308			$this->schemaAnalyzer = new SchemaAnalyzer($this->connection->getSchemaManager(), $this->cache, $this->getConnectionUniqueId($this->connection));
309			}
310
311			return $this->schemaAnalyzer;
312			}
313
314			private function getConnectionUniqueId(Connection $connection)
315			{
316			return hash('md4', $connection->getHost().'-'.$connection->getPort().'-'.$connection->getDatabase().'-'.$connection->getDriver()->getName());
317			}
318
319			/**
320			* @return \Twig_Environment
321			*/
322			private function getTwigEnvironment()
323			{
324			if ($this->twigEnvironment === null) {
325			$this->twigEnvironment = SqlTwigEnvironmentFactory::getTwigEnvironment();
326			}
327
328			return $this->twigEnvironment;
329			}
330			}
331

thecodingmachine / magic-query

Pull Request — 1.3 (#56)

MagicQuery::setOutputDialect() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like