MagicQuery::parse() - Code Metrics - Inspection of "Transforms automatically '= null' into 'IS NULL'." - thecodingmachine/magic-query - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 1.1 (#23)

by David

created 2016-04-04 12:30 UTC

MagicQuery::parse() B

↳ Parent: MagicQuery

Complexity

Conditions	3
Paths	3

Size

Total Lines	24
Code Lines	12

Duplication

Lines	0
Ratio	0 %

Importance

Changes	3
Bugs	0	Features	0

Metric	Value
c	3
b	0
f	0
dl	0
loc	24
rs	8.9713
cc	3
eloc	12
nc	3
nop	1

<?php

namespace Mouf\Database;

use Doctrine\Common\Cache\VoidCache;
use Mouf\Database\MagicQuery\Twig\SqlTwigEnvironmentFactory;
use Mouf\Database\SchemaAnalyzer\SchemaAnalyzer;
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);

        return $this->toSql($select, $parameters);
    }

    /**
     * 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();
            $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
     *
     * @return string
     */
    public function toSql(NodeInterface $sqlNode, array $parameters = array())
    {
        return $sqlNode->toSql($parameters, $this->connection, 0, SqlRenderInterface::CONDITION_GUESS);
    }

    /**
     * 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());
            $leftCol->setColumn($foreignKey->getLocalColumns()[0]);

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

thecodingmachine / magic-query

Pull Request — 1.1 (#23)

MagicQuery::parse() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like