7862450178

Committed 11 Feb 2024 01:55PM UTC coverage: 70.298%. First build

Build # 7862450178

Build Type

Pull #87

github

Committed by

web-flow

Commit Message

Merge 3adfbf0ce into 0c0ab130b

Pull Request Pull Request #87: Update deps + test on dbal v3

Run Details

2 of 11 new or added lines in 5 files covered. (18.18%)

1084 of 1542 relevant lines covered (70.3%)

4.76 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

95.88

/src/Mouf/Database/MagicQuery.php

<?php

namespace Mouf\Database;

use Doctrine\Common\Cache\Cache;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\MySqlPlatform;
use Twig\Environment;
use function array_filter;
use function array_keys;
use Doctrine\Common\Cache\VoidCache;
use Doctrine\DBAL\Platforms\MySQL80Platform;
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;
    /** @var Cache */
    private $cache;
    /** @var SchemaAnalyzer */
    private $schemaAnalyzer;
    /** @var AbstractPlatform */
    private $platform;
    /** @var Environment */
    private $twigEnvironment;
    /** @var bool */
    private $enableTwig = false;

    /**
     * @param \Doctrine\DBAL\Connection    $connection
     * @param 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 MySQL80Platform();
        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 (or is you set this explicitly to null), 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
    {
        if ($platform !== null) {
            $this->platform = $platform;
        } else {
            $this->platform = $this->connection ? $this->connection->getDatabasePlatform() : new MySQL80Platform();
        }

        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 && $param !== [];}));
        // We choose md4 because it is fast.
        $cacheKey = 'request_build_'.hash('md4', get_class($this->platform).'__'.$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.
     *
     * @throws MagicQueryMissingConnectionException
     */
    private function magicJoin(NodeInterface $select): void
    {
        // 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.
     */
    private function magicJoinOnOneQuery(MagicJoinSelect $magicJoinSelect): void
    {
        $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->createSchemaManager(), $this->cache, $this->getConnectionUniqueId($this->connection));
        }

        return $this->schemaAnalyzer;
    }

    private function getConnectionUniqueId(Connection $connection): string
    {
        $connectionParams = $connection->getParams();
        \assert(\array_key_exists('host', $connectionParams));
        \assert(\array_key_exists('port', $connectionParams));
        return hash('md4', $connectionParams['host'].'-'.$connectionParams['port'].'-'.$connection->getDatabase().'-'.$connection->getDriver()->getDatabasePlatform()->getName());
    }

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

thecodingmachine / magic-query / 7862450178

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous