MagicQuery::buildPreparedStatement() - Code Metrics - Inspection of "Performance improvement fix" - thecodingmachine/magic-query - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 1.2 (#45)

by David

created 2019-05-22 10:12 UTC

MagicQuery::buildPreparedStatement() A

↳ Parent: MagicQuery

Complexity

Conditions	3
Paths	4

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	19
rs	9.6333
c	0
b	0
f	0
cc	3
nc	4
nop	2

<?php

namespace Mouf\Database;

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

    /**
     * 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 $sqlNode->toSql($parameters, $this->connection, 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 = new \SplObjectStorage();
        $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 (!$foreignKeysSet->contains($foreignKey)) {
                    $completePath[] = $foreignKey;
                    $foreignKeysSet->attach($foreignKey);
                }
            }
        }

        // 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());
        }

        return $this->schemaAnalyzer;
    }

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

thecodingmachine / magic-query

Pull Request — 1.2 (#45)

MagicQuery::buildPreparedStatement() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like