SchemaAnalyzer::isTableReferenced() - Code Metrics - thecodingmachine/schema-analyzer - Measure and Improve Code Quality continuously with Scrutinizer

SchemaAnalyzer::isTableReferenced() A
last analyzed 2020-08-01 21:35 UTC

↳ Parent: SchemaAnalyzer

Complexity

Conditions	4
Paths	4

Size

Total Lines

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	13
rs	9.8333
c	0
b	0
f	0
cc	4
nc	4
nop	1

<?php

namespace Mouf\Database\SchemaAnalyzer;

use Doctrine\Common\Cache\Cache;
use Doctrine\Common\Cache\VoidCache;
use Doctrine\DBAL\Schema\AbstractSchemaManager;
use Doctrine\DBAL\Schema\ForeignKeyConstraint;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\DBAL\Schema\SchemaException;
use Doctrine\DBAL\Schema\Table;
use Fhaculty\Graph\Edge\Base;
use Fhaculty\Graph\Graph;
use Fhaculty\Graph\Vertex;

/**
 * This class can analyze a database model.
 * In this class you will find.
 *
 * - Functions to automatically detect **junction tables**
 * - Functions to compute the shortest path between 2 tables based on the relationships stored in the schema.
 */
class SchemaAnalyzer
{
    private static $WEIGHT_FK = 1;
    private static $WEIGHT_INHERITANCE_FK = 0.1;
    private static $WEIGHT_JOINTURE_TABLE = 1.5;

    const WEIGHT_IMPORTANT = 0.75;
    const WEIGHT_IRRELEVANT = 2;
    const WEIGHT_IGNORE = INF;

    /**
     * @var AbstractSchemaManager
     */
    private $schemaManager;

    /**
     * @var Schema
     */
    private $schema;

    /**
     * @var Cache
     */
    private $cache;

    /**
     * @var string
     */
    private $cachePrefix;

    /**
     * Nested arrays containing table => column => cost.
     *
     * @var float[][]
     */
    private $alteredCosts = [];

    /**
     * Array containing table cost.
     *
     * @var float[]
     */
    private $alteredTableCosts = [];

    /**
     * @param AbstractSchemaManager $schemaManager
     * @param Cache|null            $cache          The Doctrine cache service to use to cache results (optional)
     * @param string|null           $schemaCacheKey The unique identifier for the schema manager. Compulsory if cache is set.
     */
    public function __construct(AbstractSchemaManager $schemaManager, Cache $cache = null, $schemaCacheKey = null)
    {
        $this->schemaManager = $schemaManager;
        if (empty($schemaCacheKey) && $cache) {
            throw new SchemaAnalyzerException('You must provide a schema cache key if you configure SchemaAnalyzer with cache support.');
        }
        if ($cache) {
            $this->cache = $cache;
        } else {
            $this->cache = new VoidCache();
        }
        $this->cachePrefix = $schemaCacheKey;
    }

    /**
     * Detect all junctions tables in the schema.
     * A table is a junction table if:.
     *
     * - it has exactly 2 foreign keys
     * - it has only 2 columns (or 3 columns if the third one is an autoincremented primary key).
     *
     * If $ignoreReferencedTables is true, junctions table that are pointed to by a foreign key of another
     * table are ignored.
     *
     * @param bool $ignoreReferencedTables
     *
     * @return Table[]
     */
    public function detectJunctionTables($ignoreReferencedTables = false)
    {
        $junctionTablesKey = $this->cachePrefix.'_junctiontables_'.($ignoreReferencedTables ? 'true' : 'false');
        $junctionTables = $this->cache->fetch($junctionTablesKey);
        if ($junctionTables === false) {
            $junctionTables = array_filter($this->getSchema()->getTables(), function (Table $table) use ($ignoreReferencedTables) {
                return $this->isJunctionTable($table, $ignoreReferencedTables);
            });
            $this->cache->save($junctionTablesKey, $junctionTables);
        }

        return $junctionTables;
    }

    /**
     * Returns true if $table is a junction table.
     * I.e:.
     *
     * - it must have exactly 2 foreign keys
     * - it must have only 2 columns (or 3 columns if the third one is an autoincremented primary key).
     *
     * If $ignoreReferencedTables is true, junctions table that are pointed to by a foreign key of another
     * table are ignored.
     *
     * @param Table $table
     * @param bool  $ignoreReferencedTables
     *
     * @return bool
     */
    public function isJunctionTable(Table $table, $ignoreReferencedTables = false)
    {
        $foreignKeys = $table->getForeignKeys();
        if (count($foreignKeys) !== 2) {
            return false;
        }

        $columns = $table->getColumns();
        if (count($columns) < 2 || count($columns) > 3) {
            return false;
        }

        if ($table->hasPrimaryKey()) {
            $pkColumns = $table->getPrimaryKey()->getUnquotedColumns();
        } else {
            $pkColumns = [];
        }

        if (count($pkColumns) === 1 && count($columns) === 2) {
            return false;
        }

        if (count($pkColumns) !== 1 && count($columns) === 3) {
            return false;
        }

        $fkColumnNames = [];
        foreach ($foreignKeys as $foreignKey) {
            $fkColumns = $foreignKey->getColumns();
            if (count($fkColumns) !== 1) {
                return false;
            }
            $fkColumnNames[$fkColumns[0]] = true;
        }

        if (count($columns) === 3) {
            // Let's check that the third column (the ID is NOT a foreign key)
            if (isset($fkColumnNames[$pkColumns[0]])) {
                return false;
            }

            // Let's check that the primary key is autoincremented
            $pkColumn = $table->getColumn($pkColumns[0]);
            if (!$pkColumn->getAutoincrement() && strpos($pkColumn->getComment(), '@Autoincrement') === false) {
                return false;
            }
        }

        if ($ignoreReferencedTables && $this->isTableReferenced($table)) {
            return false;
        }

        return true;
    }

    /**
     * Returns true if the table $table is referenced by another table.
     *
     * @param Table $table
     *
     * @return bool
     */
    private function isTableReferenced(Table $table)
    {
        $tableName = $table->getName();
        foreach ($this->getSchema()->getTables() as $tableIter) {
            foreach ($tableIter->getForeignKeys() as $fk) {
                if ($fk->getForeignTableName() === $tableName) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Get the shortest path between 2 tables.
     *
     * @param string $fromTable
     * @param string $toTable
     *
     * @return \Doctrine\DBAL\Schema\ForeignKeyConstraint[]
     *
     * @throws SchemaAnalyzerException
     */
    public function getShortestPath($fromTable, $toTable)
    {
        return $this->fromCache($this->cachePrefix.'_shortest_'.$fromTable.'```'.$toTable, function () use ($fromTable, $toTable) {
            return $this->getShortestPathWithoutCache($fromTable, $toTable);
        });
    }

    /**
     * Get the shortest path between 2 tables.
     *
     * @param string $fromTable
     * @param string $toTable
     *
     * @return \Doctrine\DBAL\Schema\ForeignKeyConstraint[]
     *
     * @throws SchemaAnalyzerException
     */
    private function getShortestPathWithoutCache($fromTable, $toTable)
    {
        $this->checkTableExists($fromTable);
        $this->checkTableExists($toTable);

        $graph = $this->buildSchemaGraph();

        try {
            $predecessors = MultiDijkstra::findShortestPaths($graph->getVertex($fromTable), $graph->getVertex($toTable));
            $edges = MultiDijkstra::getCheapestPathFromPredecesArray($graph->getVertex($fromTable), $graph->getVertex($toTable), $predecessors);
        } catch (MultiDijkstraAmbiguityException $e) {
            // If there is more than 1 short path, let's display this.
            $paths = MultiDijkstra::getAllPossiblePathsFromPredecesArray($graph->getVertex($fromTable), $graph->getVertex($toTable), $predecessors);
            $msg = $this->getAmbiguityExceptionMessage($paths, $graph->getVertex($fromTable), $graph->getVertex($toTable));
            throw new ShortestPathAmbiguityException($msg);
        }

        $foreignKeys = [];

        $currentTable = $fromTable;

        foreach ($edges as $edge) {
            /* @var $edge Base */

            if ($fk = $edge->getAttribute('fk')) {
                /* @var $fk ForeignKeyConstraint */
                $foreignKeys[] = $fk;
                if ($fk->getForeignTableName() == $currentTable) {
                    $currentTable = $fk->getLocalTable()->getName();
                } else {
                    $currentTable = $fk->getForeignTableName();
                }
            } elseif ($junctionTable = $edge->getAttribute('junction')) {
                /* @var $junctionTable Table */
                $junctionFks = array_values($junctionTable->getForeignKeys());
                // We need to order the 2 FKs. The first one is the one that has a common point with the current table.
                $fk = $junctionFks[0];
                if ($fk->getForeignTableName() == $currentTable) {
                    $foreignKeys[] = $fk;
                    $foreignKeys[] = $junctionFks[1];
                    $currentTable = $junctionFks[1]->getForeignTableName();
                } else {
                    $foreignKeys[] = $junctionFks[1];
                    $foreignKeys[] = $fk;
                    $currentTable = $fk->getForeignTableName();
                }
            } else {
                // @codeCoverageIgnoreStart
                throw new SchemaAnalyzerException('Unexpected edge. We should have a fk or a junction attribute.');
                // @codeCoverageIgnoreEnd
            }
        }

        return $foreignKeys;
    }

    private function checkTableExists($tableName)
    {
        try {
            $this->getSchema()->getTable($tableName);
        } catch (SchemaException $e) {
            throw SchemaAnalyzerTableNotFoundException::tableNotFound($tableName, $this->schema, $e);
        }
    }

    private function buildSchemaGraph()
    {
        $graph = new Graph();

        // First, let's create all the vertex
        foreach ($this->getSchema()->getTables() as $table) {
            $graph->createVertex($table->getName());
        }

        // Then, let's create all the edges
        foreach ($this->getSchema()->getTables() as $table) {
            $fks = $this->removeDuplicates($table->getForeignKeys());
            foreach ($fks as $fk) {
                // Create an undirected edge, with weight = 1
                $edge = $graph->getVertex($table->getName())->createEdge($graph->getVertex($fk->getForeignTableName()));
                if (isset($this->alteredCosts[$fk->getLocalTable()->getName()][implode(',', $fk->getLocalColumns())])) {
                    $cost = $this->alteredCosts[$fk->getLocalTable()->getName()][implode(',', $fk->getLocalColumns())];
                } elseif ($this->isInheritanceRelationship($fk)) {
                    $cost = self::$WEIGHT_INHERITANCE_FK;
                } else {
                    $cost = self::$WEIGHT_FK;
                }
                if (isset($this->alteredTableCosts[$fk->getLocalTable()->getName()])) {
                    $cost *= $this->alteredTableCosts[$fk->getLocalTable()->getName()];
                }

                $edge->setWeight($cost);
                $edge->getAttributeBag()->setAttribute('fk', $fk);
            }
        }

        // Finally, let's add virtual edges for the junction tables
        foreach ($this->detectJunctionTables() as $junctionTable) {
            $tables = [];
            foreach ($junctionTable->getForeignKeys() as $fk) {
                $tables[] = $fk->getForeignTableName();
            }

            $edge = $graph->getVertex($tables[0])->createEdge($graph->getVertex($tables[1]));
            $cost = self::$WEIGHT_JOINTURE_TABLE;
            if (isset($this->alteredTableCosts[$junctionTable->getName()])) {
                $cost *= $this->alteredTableCosts[$junctionTable->getName()];
            }
            $edge->setWeight($cost);
            $edge->getAttributeBag()->setAttribute('junction', $junctionTable);
        }

        return $graph;
    }

    /**
     * Remove duplicate foreign keys (assumes that all foreign yes are from the same local table).
     *
     * @param ForeignKeyConstraint[] $foreignKeys
     * @return ForeignKeyConstraint[]
     */
    private function removeDuplicates(array $foreignKeys)
    {
        $fks = [];
        foreach ($foreignKeys as $foreignKey) {
            $fks[implode('__`__', $foreignKey->getLocalColumns())] = $foreignKey;
        }

        return array_values($fks);
    }

    /**
     * Returns the schema (from the schema manager or the cache if needed).
     *
     * @return Schema
     */
    private function getSchema()
    {
        if ($this->schema === null) {
            $schemaKey = $this->cachePrefix.'_schema';
            $this->schema = $this->cache->fetch($schemaKey);
            if (empty($this->schema)) {
                $this->schema = $this->schemaManager->createSchema();
                $this->cache->save($schemaKey, $this->schema);
            }
        }

        return $this->schema;
    }

    /**
     * Returns the full exception message when an ambiguity arises.
     *
     * @param Base[][] $paths
     * @param Vertex   $startVertex
     */
    private function getAmbiguityExceptionMessage(array $paths, Vertex $startVertex, Vertex $endVertex)
    {
        $textPaths = [];
        $i = 1;
        foreach ($paths as $path) {
            $textPaths[] = 'Path '.$i.': '.$this->getTextualPath($path, $startVertex);
            ++$i;
        }

        $msg = sprintf("There are many possible shortest paths between table '%s' and table '%s'\n\n",
            $startVertex->getId(), $endVertex->getId());

        $msg .= implode("\n\n", $textPaths);

        return $msg;
    }

    /**
     * Returns the textual representation of the path.
     *
     * @param Base[] $path
     * @param Vertex $startVertex
     */
    private function getTextualPath(array $path, Vertex $startVertex)
    {
        $currentVertex = $startVertex;
        $currentTable = $currentVertex->getId();

        $textPath = $currentTable;

        foreach ($path as $edge) {
            /* @var $fk ForeignKeyConstraint */
            if ($fk = $edge->getAttribute('fk')) {
                if ($fk->getForeignTableName() == $currentTable) {
                    $currentTable = $fk->getLocalTable()->getName();
                    $isForward = false;
                } else {
                    $currentTable = $fk->getForeignTableName();
                    $isForward = true;
                }

                $columns = implode(',', $fk->getLocalColumns());

                $textPath .= ' '.(!$isForward ? '<' : '');
                $textPath .= '--('.$columns.')--';
                $textPath .= ($isForward ? '>' : '').' ';
                $textPath .= $currentTable;
            } elseif ($junctionTable = $edge->getAttribute('junction')) {
                /* @var $junctionTable Table */
                $junctionFks = array_values($junctionTable->getForeignKeys());
                // We need to order the 2 FKs. The first one is the one that has a common point with the current table.
                $fk = $junctionFks[0];
                if ($fk->getForeignTableName() == $currentTable) {
                    $currentTable = $junctionFks[1]->getForeignTableName();
                } else {
                    $currentTable = $fk->getForeignTableName();
                }
                $textPath .= ' <=('.$junctionTable->getName().')=> '.$currentTable;
            } else {
                // @codeCoverageIgnoreStart
                throw new SchemaAnalyzerException('Unexpected edge. We should have a fk or a junction attribute.');
                // @codeCoverageIgnoreEnd
            }
        }

        return $textPath;
    }

    /**
     * Sets the cost of a foreign key.
     *
     * @param string $tableName
     * @param string $columnName
     * @param float  $cost
     *
     * @return $this
     */
    public function setForeignKeyCost($tableName, $columnName, $cost)
    {
        $this->alteredCosts[$tableName][$columnName] = $cost;
    }

    /**
     * Sets the cost modifier of a table.
     *
     * @param string $tableName
     * @param float  $cost
     *
     * @return $this
     */
    public function setTableCostModifier($tableName, $cost)
    {
        $this->alteredTableCosts[$tableName] = $cost;
    }

    /**
     * Sets the cost modifier of all tables at once.
     *
     * @param array<string, float> $tableCosts The key is the table name, the value is the cost modifier.
     */
    public function setTableCostModifiers(array $tableCosts)
    {
        $this->alteredTableCosts = $tableCosts;
    }

    /**
     * Sets the cost of all foreign keys at once.
     *
     * @param array<string, array<string, float>> $fkCosts First key is the table name, second key is the column name, the value is the cost.
     */
    public function setForeignKeyCosts(array $fkCosts)
    {
        $this->alteredCosts = $fkCosts;
    }

    /**
     * Returns true if this foreign key represents an inheritance relationship,
     * i.e. if this foreign key is based on a primary key.
     *
     * @param ForeignKeyConstraint $fk
     *
     * @return true
     */
    private function isInheritanceRelationship(ForeignKeyConstraint $fk)
    {
        if (!$fk->getLocalTable()->hasPrimaryKey()) {
            return false;
        }
        $fkColumnNames = $fk->getUnquotedLocalColumns();
        $pkColumnNames = $fk->getLocalTable()->getPrimaryKey()->getUnquotedColumns();

        sort($fkColumnNames);
        sort($pkColumnNames);

        return $fkColumnNames == $pkColumnNames;
    }

    /**
     * If this table is pointing to a parent table (if its primary key is a foreign key pointing on another table),
     * this function will return the pointed table.
     * This function will return null if there is no parent table.
     *
     * @param string $tableName
     *
     * @return ForeignKeyConstraint|null
     */
    public function getParentRelationship($tableName)
    {
        return $this->fromCache($this->cachePrefix.'_parent_'.$tableName, function () use ($tableName) {
            return $this->getParentRelationshipWithoutCache($tableName);
        });
    }

    /**
     * If this table is pointing to a parent table (if its primary key is a foreign key pointing on another table),
     * this function will return the pointed table.
     * This function will return null if there is no parent table.
     *
     * @param string $tableName
     *
     * @return ForeignKeyConstraint|null
     */
    private function getParentRelationshipWithoutCache($tableName)
    {
        $table = $this->getSchema()->getTable($tableName);
        foreach ($table->getForeignKeys() as $fk) {
            if ($this->isInheritanceRelationship($fk)) {
                return $fk;
            }
        }

        return;
    }

    /**
     * If this table is pointed by children tables (if other child tables have a primary key that is also a
     * foreign key to this table), this function will return the list of child tables.
     * This function will return an empty array if there are no children tables.
     *
     * @param string $tableName
     *
     * @return ForeignKeyConstraint[]
     */
    public function getChildrenRelationships($tableName)
    {
        return $this->fromCache($this->cachePrefix.'_children_'.$tableName, function () use ($tableName) {
            return $this->getChildrenRelationshipsWithoutCache($tableName);
        });
    }

    /**
     * If this table is pointed by children tables (if other child tables have a primary key that is also a
     * foreign key to this table), this function will return the list of child tables.
     * This function will return an empty array if there are no children tables.
     *
     * @param string $tableName
     *
     * @return ForeignKeyConstraint[]
     */
    private function getChildrenRelationshipsWithoutCache($tableName)
    {
        $schema = $this->getSchema();
        $children = [];
        foreach ($schema->getTables() as $table) {
            if ($table->getName() === $tableName) {
                continue;
            }
            $fks = $this->removeDuplicates($table->getForeignKeys());
            foreach ($fks as $fk) {
                if ($fk->getForeignTableName() === $tableName && $this->isInheritanceRelationship($fk)) {
                    $children[] = $fk;
                }
            }
        }

        return $children;
    }

    /**
     * Returns an item from cache or computes it using $closure and puts it in cache.
     *
     * @param string   $key
     * @param callable $closure
     *
     * @return mixed
     */
    private function fromCache($key, callable $closure)
    {
        $item = $this->cache->fetch($key);
        if ($item === false) {
            $item = $closure();
            $this->cache->save($key, $item);
        }

        return $item;
    }
}


1			<?php
2
3			namespace Mouf\Database\SchemaAnalyzer;
4
5			use Doctrine\Common\Cache\Cache;
6			use Doctrine\Common\Cache\VoidCache;
7			use Doctrine\DBAL\Schema\AbstractSchemaManager;
8			use Doctrine\DBAL\Schema\ForeignKeyConstraint;
9			use Doctrine\DBAL\Schema\Schema;
10			use Doctrine\DBAL\Schema\SchemaException;
11			use Doctrine\DBAL\Schema\Table;
12			use Fhaculty\Graph\Edge\Base;
13			use Fhaculty\Graph\Graph;
14			use Fhaculty\Graph\Vertex;
15
16			/**
17			* This class can analyze a database model.
18			* In this class you will find.
19			*
20			* - Functions to automatically detect junction tables
21			* - Functions to compute the shortest path between 2 tables based on the relationships stored in the schema.
22			*/
23			class SchemaAnalyzer
24			{
25			private static $WEIGHT_FK = 1;
26			private static $WEIGHT_INHERITANCE_FK = 0.1;
27			private static $WEIGHT_JOINTURE_TABLE = 1.5;
28
29			const WEIGHT_IMPORTANT = 0.75;
30			const WEIGHT_IRRELEVANT = 2;
31			const WEIGHT_IGNORE = INF;
32
33			/**
34			* @var AbstractSchemaManager
35			*/
36			private $schemaManager;
37
38			/**
39			* @var Schema
40			*/
41			private $schema;
42
43			/**
44			* @var Cache
45			*/
46			private $cache;
47
48			/**
49			* @var string
50			*/
51			private $cachePrefix;
52
53			/**
54			* Nested arrays containing table => column => cost.
55			*
56			* @var float[][]
57			*/
58			private $alteredCosts = [];
59
60			/**
61			* Array containing table cost.
62			*
63			* @var float[]
64			*/
65			private $alteredTableCosts = [];
66
67			/**
68			* @param AbstractSchemaManager $schemaManager
69			* @param Cache\|null $cache The Doctrine cache service to use to cache results (optional)
70			* @param string\|null $schemaCacheKey The unique identifier for the schema manager. Compulsory if cache is set.
71			*/
72			public function __construct(AbstractSchemaManager $schemaManager, Cache $cache = null, $schemaCacheKey = null)
73			{
74			$this->schemaManager = $schemaManager;
75			if (empty($schemaCacheKey) && $cache) {
76			throw new SchemaAnalyzerException('You must provide a schema cache key if you configure SchemaAnalyzer with cache support.');
77			}
78			if ($cache) {
79			$this->cache = $cache;
80			} else {
81			$this->cache = new VoidCache();
82			}
83			$this->cachePrefix = $schemaCacheKey;
84			}
85
86			/**
87			* Detect all junctions tables in the schema.
88			* A table is a junction table if:.
89			*
90			* - it has exactly 2 foreign keys
91			* - it has only 2 columns (or 3 columns if the third one is an autoincremented primary key).
92			*
93			* If $ignoreReferencedTables is true, junctions table that are pointed to by a foreign key of another
94			* table are ignored.
95			*
96			* @param bool $ignoreReferencedTables
97			*
98			* @return Table[]
99			*/
100			public function detectJunctionTables($ignoreReferencedTables = false)
101			{
102			$junctionTablesKey = $this->cachePrefix.'_junctiontables_'.($ignoreReferencedTables ? 'true' : 'false');
103			$junctionTables = $this->cache->fetch($junctionTablesKey);
104			if ($junctionTables === false) {
105			$junctionTables = array_filter($this->getSchema()->getTables(), function (Table $table) use ($ignoreReferencedTables) {
106			return $this->isJunctionTable($table, $ignoreReferencedTables);
107			});
108			$this->cache->save($junctionTablesKey, $junctionTables);
109			}
110
111			return $junctionTables;
112			}
113
114			/**
115			* Returns true if $table is a junction table.
116			* I.e:.
117			*
118			* - it must have exactly 2 foreign keys
119			* - it must have only 2 columns (or 3 columns if the third one is an autoincremented primary key).
120			*
121			* If $ignoreReferencedTables is true, junctions table that are pointed to by a foreign key of another
122			* table are ignored.
123			*
124			* @param Table $table
125			* @param bool $ignoreReferencedTables
126			*
127			* @return bool
128			*/
129			public function isJunctionTable(Table $table, $ignoreReferencedTables = false)
130			{
131			$foreignKeys = $table->getForeignKeys();
132			if (count($foreignKeys) !== 2) {
133			return false;
134			}
135
136			$columns = $table->getColumns();
137			if (count($columns) < 2 \|\| count($columns) > 3) {
138			return false;
139			}
140
141			if ($table->hasPrimaryKey()) {
142			$pkColumns = $table->getPrimaryKey()->getUnquotedColumns();
143			} else {
144			$pkColumns = [];
145			}
146
147			if (count($pkColumns) === 1 && count($columns) === 2) {
148			return false;
149			}
150
151			if (count($pkColumns) !== 1 && count($columns) === 3) {
152			return false;
153			}
154
155			$fkColumnNames = [];
156			foreach ($foreignKeys as $foreignKey) {
157			$fkColumns = $foreignKey->getColumns();
158			if (count($fkColumns) !== 1) {
159			return false;
160			}
161			$fkColumnNames[$fkColumns[0]] = true;
162			}
163
164			if (count($columns) === 3) {
165			// Let's check that the third column (the ID is NOT a foreign key)
166			if (isset($fkColumnNames[$pkColumns[0]])) {
167			return false;
168			}
169
170			// Let's check that the primary key is autoincremented
171			$pkColumn = $table->getColumn($pkColumns[0]);
172			if (!$pkColumn->getAutoincrement() && strpos($pkColumn->getComment(), '@Autoincrement') === false) {
173			return false;
174			}
175			}
176
177			if ($ignoreReferencedTables && $this->isTableReferenced($table)) {
178			return false;
179			}
180
181			return true;
182			}
183
184			/**
185			* Returns true if the table $table is referenced by another table.
186			*
187			* @param Table $table
188			*
189			* @return bool
190			*/
191			private function isTableReferenced(Table $table)
192			{
193			$tableName = $table->getName();
194			foreach ($this->getSchema()->getTables() as $tableIter) {
195			foreach ($tableIter->getForeignKeys() as $fk) {
196			if ($fk->getForeignTableName() === $tableName) {
197			return true;
198			}
199			}
200			}
201
202			return false;
203			}
204
205			/**
206			* Get the shortest path between 2 tables.
207			*
208			* @param string $fromTable
209			* @param string $toTable
210			*
211			* @return \Doctrine\DBAL\Schema\ForeignKeyConstraint[]
212			*
213			* @throws SchemaAnalyzerException
214			*/
215			public function getShortestPath($fromTable, $toTable)
216			{
217			return $this->fromCache($this->cachePrefix.'_shortest_'.$fromTable.'```'.$toTable, function () use ($fromTable, $toTable) {
218			return $this->getShortestPathWithoutCache($fromTable, $toTable);
219			});
220			}
221
222			/**
223			* Get the shortest path between 2 tables.
224			*
225			* @param string $fromTable
226			* @param string $toTable
227			*
228			* @return \Doctrine\DBAL\Schema\ForeignKeyConstraint[]
229			*
230			* @throws SchemaAnalyzerException
231			*/
232			private function getShortestPathWithoutCache($fromTable, $toTable)
233			{
234			$this->checkTableExists($fromTable);
235			$this->checkTableExists($toTable);
236
237			$graph = $this->buildSchemaGraph();
238
239			try {
240			$predecessors = MultiDijkstra::findShortestPaths($graph->getVertex($fromTable), $graph->getVertex($toTable));
241			$edges = MultiDijkstra::getCheapestPathFromPredecesArray($graph->getVertex($fromTable), $graph->getVertex($toTable), $predecessors);
242			} catch (MultiDijkstraAmbiguityException $e) {
243			// If there is more than 1 short path, let's display this.
244			$paths = MultiDijkstra::getAllPossiblePathsFromPredecesArray($graph->getVertex($fromTable), $graph->getVertex($toTable), $predecessors);
245			$msg = $this->getAmbiguityExceptionMessage($paths, $graph->getVertex($fromTable), $graph->getVertex($toTable));
246			throw new ShortestPathAmbiguityException($msg);
247			}
248
249			$foreignKeys = [];
250
251			$currentTable = $fromTable;
252
253			foreach ($edges as $edge) {
254			/* @var $edge Base */
255
256			if ($fk = $edge->getAttribute('fk')) {
257			/* @var $fk ForeignKeyConstraint */
258			$foreignKeys[] = $fk;
259			if ($fk->getForeignTableName() == $currentTable) {
260			$currentTable = $fk->getLocalTable()->getName();
261			} else {
262			$currentTable = $fk->getForeignTableName();
263			}
264			} elseif ($junctionTable = $edge->getAttribute('junction')) {
265			/* @var $junctionTable Table */
266			$junctionFks = array_values($junctionTable->getForeignKeys());
267			// We need to order the 2 FKs. The first one is the one that has a common point with the current table.
268			$fk = $junctionFks[0];
269			if ($fk->getForeignTableName() == $currentTable) {
270			$foreignKeys[] = $fk;
271			$foreignKeys[] = $junctionFks[1];
272			$currentTable = $junctionFks[1]->getForeignTableName();
273			} else {
274			$foreignKeys[] = $junctionFks[1];
275			$foreignKeys[] = $fk;
276			$currentTable = $fk->getForeignTableName();
277			}
278			} else {
279			// @codeCoverageIgnoreStart
280			throw new SchemaAnalyzerException('Unexpected edge. We should have a fk or a junction attribute.');
281			// @codeCoverageIgnoreEnd
282			}
283			}
284
285			return $foreignKeys;
286			}
287
288			private function checkTableExists($tableName)
289			{
290			try {
291			$this->getSchema()->getTable($tableName);
292			} catch (SchemaException $e) {
293			throw SchemaAnalyzerTableNotFoundException::tableNotFound($tableName, $this->schema, $e);
294			}
295			}
296
297			private function buildSchemaGraph()
298			{
299			$graph = new Graph();
300
301			// First, let's create all the vertex
302			foreach ($this->getSchema()->getTables() as $table) {
303			$graph->createVertex($table->getName());
304			}
305
306			// Then, let's create all the edges
307			foreach ($this->getSchema()->getTables() as $table) {
308			$fks = $this->removeDuplicates($table->getForeignKeys());
309			foreach ($fks as $fk) {
310			// Create an undirected edge, with weight = 1
311			$edge = $graph->getVertex($table->getName())->createEdge($graph->getVertex($fk->getForeignTableName()));
312			if (isset($this->alteredCosts[$fk->getLocalTable()->getName()][implode(',', $fk->getLocalColumns())])) {
313			$cost = $this->alteredCosts[$fk->getLocalTable()->getName()][implode(',', $fk->getLocalColumns())];
314			} elseif ($this->isInheritanceRelationship($fk)) {
315			$cost = self::$WEIGHT_INHERITANCE_FK;
316			} else {
317			$cost = self::$WEIGHT_FK;
318			}
319			if (isset($this->alteredTableCosts[$fk->getLocalTable()->getName()])) {
320			$cost *= $this->alteredTableCosts[$fk->getLocalTable()->getName()];
321			}
322
323			$edge->setWeight($cost);
324			$edge->getAttributeBag()->setAttribute('fk', $fk);
325			}
326			}
327
328			// Finally, let's add virtual edges for the junction tables
329			foreach ($this->detectJunctionTables() as $junctionTable) {
330			$tables = [];
331			foreach ($junctionTable->getForeignKeys() as $fk) {
332			$tables[] = $fk->getForeignTableName();
333			}
334
335			$edge = $graph->getVertex($tables[0])->createEdge($graph->getVertex($tables[1]));
336			$cost = self::$WEIGHT_JOINTURE_TABLE;
337			if (isset($this->alteredTableCosts[$junctionTable->getName()])) {
338			$cost *= $this->alteredTableCosts[$junctionTable->getName()];
339			}
340			$edge->setWeight($cost);
341			$edge->getAttributeBag()->setAttribute('junction', $junctionTable);
342			}
343
344			return $graph;
345			}
346
347			/**
348			* Remove duplicate foreign keys (assumes that all foreign yes are from the same local table).
349			*
350			* @param ForeignKeyConstraint[] $foreignKeys
351			* @return ForeignKeyConstraint[]
352			*/
353			private function removeDuplicates(array $foreignKeys)
354			{
355			$fks = [];
356			foreach ($foreignKeys as $foreignKey) {
357			$fks[implode('__`__', $foreignKey->getLocalColumns())] = $foreignKey;
358			}
359
360			return array_values($fks);
361			}
362
363			/**
364			* Returns the schema (from the schema manager or the cache if needed).
365			*
366			* @return Schema
367			*/
368			private function getSchema()
369			{
370			if ($this->schema === null) {
371			$schemaKey = $this->cachePrefix.'_schema';
372			$this->schema = $this->cache->fetch($schemaKey);
373			if (empty($this->schema)) {
374			$this->schema = $this->schemaManager->createSchema();
375			$this->cache->save($schemaKey, $this->schema);
376			}
377			}
378
379			return $this->schema;
380			}
381
382			/**
383			* Returns the full exception message when an ambiguity arises.
384			*
385			* @param Base[][] $paths
386			* @param Vertex $startVertex
387			*/
388			private function getAmbiguityExceptionMessage(array $paths, Vertex $startVertex, Vertex $endVertex)
389			{
390			$textPaths = [];
391			$i = 1;
392			foreach ($paths as $path) {
393			$textPaths[] = 'Path '.$i.': '.$this->getTextualPath($path, $startVertex);
394			++$i;
395			}
396
397			$msg = sprintf("There are many possible shortest paths between table '%s' and table '%s'\n\n",
398			$startVertex->getId(), $endVertex->getId());
399
400			$msg .= implode("\n\n", $textPaths);
401
402			return $msg;
403			}
404
405			/**
406			* Returns the textual representation of the path.
407			*
408			* @param Base[] $path
409			* @param Vertex $startVertex
410			*/
411			private function getTextualPath(array $path, Vertex $startVertex)
412			{
413			$currentVertex = $startVertex;
414			$currentTable = $currentVertex->getId();
415
416			$textPath = $currentTable;
417
418			foreach ($path as $edge) {
419			/* @var $fk ForeignKeyConstraint */
420			if ($fk = $edge->getAttribute('fk')) {
421			if ($fk->getForeignTableName() == $currentTable) {
422			$currentTable = $fk->getLocalTable()->getName();
423			$isForward = false;
424			} else {
425			$currentTable = $fk->getForeignTableName();
426			$isForward = true;
427			}
428
429			$columns = implode(',', $fk->getLocalColumns());
430
431			$textPath .= ' '.(!$isForward ? '<' : '');
432			$textPath .= '--('.$columns.')--';
433			$textPath .= ($isForward ? '>' : '').' ';
434			$textPath .= $currentTable;
435			} elseif ($junctionTable = $edge->getAttribute('junction')) {
436			/* @var $junctionTable Table */
437			$junctionFks = array_values($junctionTable->getForeignKeys());
438			// We need to order the 2 FKs. The first one is the one that has a common point with the current table.
439			$fk = $junctionFks[0];
440			if ($fk->getForeignTableName() == $currentTable) {
441			$currentTable = $junctionFks[1]->getForeignTableName();
442			} else {
443			$currentTable = $fk->getForeignTableName();
444			}
445			$textPath .= ' <=('.$junctionTable->getName().')=> '.$currentTable;
446			} else {
447			// @codeCoverageIgnoreStart
448			throw new SchemaAnalyzerException('Unexpected edge. We should have a fk or a junction attribute.');
449			// @codeCoverageIgnoreEnd
450			}
451			}
452
453			return $textPath;
454			}
455
456			/**
457			* Sets the cost of a foreign key.
458			*
459			* @param string $tableName
460			* @param string $columnName
461			* @param float $cost
462			*
463			* @return $this
464			*/
465			public function setForeignKeyCost($tableName, $columnName, $cost)
466			{
467			$this->alteredCosts[$tableName][$columnName] = $cost;
468			}
469
470			/**
471			* Sets the cost modifier of a table.
472			*
473			* @param string $tableName
474			* @param float $cost
475			*
476			* @return $this
477			*/
478			public function setTableCostModifier($tableName, $cost)
479			{
480			$this->alteredTableCosts[$tableName] = $cost;
481			}
482
483			/**
484			* Sets the cost modifier of all tables at once.
485			*
486			* @param array<string, float> $tableCosts The key is the table name, the value is the cost modifier.
487			*/
488			public function setTableCostModifiers(array $tableCosts)
489			{
490			$this->alteredTableCosts = $tableCosts;
491			}
492
493			/**
494			* Sets the cost of all foreign keys at once.
495			*
496			* @param array<string, array<string, float>> $fkCosts First key is the table name, second key is the column name, the value is the cost.
497			*/
498			public function setForeignKeyCosts(array $fkCosts)
499			{
500			$this->alteredCosts = $fkCosts;
501			}
502
503			/**
504			* Returns true if this foreign key represents an inheritance relationship,
505			* i.e. if this foreign key is based on a primary key.
506			*
507			* @param ForeignKeyConstraint $fk
508			*
509			* @return true
510			*/
511			private function isInheritanceRelationship(ForeignKeyConstraint $fk)
512			{
513			if (!$fk->getLocalTable()->hasPrimaryKey()) {
514			return false;
515			}
516			$fkColumnNames = $fk->getUnquotedLocalColumns();
517			$pkColumnNames = $fk->getLocalTable()->getPrimaryKey()->getUnquotedColumns();
518
519			sort($fkColumnNames);
520			sort($pkColumnNames);
521
522			return $fkColumnNames == $pkColumnNames;
523			}
524
525			/**
526			* If this table is pointing to a parent table (if its primary key is a foreign key pointing on another table),
527			* this function will return the pointed table.
528			* This function will return null if there is no parent table.
529			*
530			* @param string $tableName
531			*
532			* @return ForeignKeyConstraint\|null
533			*/
534			public function getParentRelationship($tableName)
535			{
536			return $this->fromCache($this->cachePrefix.'_parent_'.$tableName, function () use ($tableName) {
537			return $this->getParentRelationshipWithoutCache($tableName);
538			});
539			}
540
541			/**
542			* If this table is pointing to a parent table (if its primary key is a foreign key pointing on another table),
543			* this function will return the pointed table.
544			* This function will return null if there is no parent table.
545			*
546			* @param string $tableName
547			*
548			* @return ForeignKeyConstraint\|null
549			*/
550			private function getParentRelationshipWithoutCache($tableName)
551			{
552			$table = $this->getSchema()->getTable($tableName);
553			foreach ($table->getForeignKeys() as $fk) {
554			if ($this->isInheritanceRelationship($fk)) {
555			return $fk;
556			}
557			}
558
559			return;
560			}
561
562			/**
563			* If this table is pointed by children tables (if other child tables have a primary key that is also a
564			* foreign key to this table), this function will return the list of child tables.
565			* This function will return an empty array if there are no children tables.
566			*
567			* @param string $tableName
568			*
569			* @return ForeignKeyConstraint[]
570			*/
571			public function getChildrenRelationships($tableName)
572			{
573			return $this->fromCache($this->cachePrefix.'_children_'.$tableName, function () use ($tableName) {
574			return $this->getChildrenRelationshipsWithoutCache($tableName);
575			});
576			}
577
578			/**
579			* If this table is pointed by children tables (if other child tables have a primary key that is also a
580			* foreign key to this table), this function will return the list of child tables.
581			* This function will return an empty array if there are no children tables.
582			*
583			* @param string $tableName
584			*
585			* @return ForeignKeyConstraint[]
586			*/
587			private function getChildrenRelationshipsWithoutCache($tableName)
588			{
589			$schema = $this->getSchema();
590			$children = [];
591			foreach ($schema->getTables() as $table) {
592			if ($table->getName() === $tableName) {
593			continue;
594			}
595			$fks = $this->removeDuplicates($table->getForeignKeys());
596			foreach ($fks as $fk) {
597			if ($fk->getForeignTableName() === $tableName && $this->isInheritanceRelationship($fk)) {
598			$children[] = $fk;
599			}
600			}
601			}
602
603			return $children;
604			}
605
606			/**
607			* Returns an item from cache or computes it using $closure and puts it in cache.
608			*
609			* @param string $key
610			* @param callable $closure
611			*
612			* @return mixed
613			*/
614			private function fromCache($key, callable $closure)
615			{
616			$item = $this->cache->fetch($key);
617			if ($item === false) {
618			$item = $closure();
619			$this->cache->save($key, $item);
620			}
621
622			return $item;
623			}
624			}
625

thecodingmachine / schema-analyzer

SchemaAnalyzer::isTableReferenced() A last analyzed 2020-08-01 21:35 UTC

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like

SchemaAnalyzer::isTableReferenced() A
last analyzed 2020-08-01 21:35 UTC