Schema analyzer for DBAL
This package offer utility functions to analyze database schemas. It is built on top of Doctrine DBAL.
In this package, 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.
Installation
You can install this package through Composer:
{
"require": {
"mouf/schema-analyzer": "~1.0"
}
}
The packages adheres to the SemVer specification, and there will be full backward compatibility between minor versions.
Detecting junction tables
The starting point is always a DBAL Schema. Pass the schema to SchemaAnalyzer, and then, simply call the functions.
// $conn is the DBAL connection.
$schema = $conn->getSchemaManager()->createSchema();
// Let's detect all junctions tables
$tables = $schema->detectJunctionTables();
// This will return an array of Doctrine\DBAL\Schema\Table objects
A junction table is a table:
- that has exactly 2 foreign keys
- that has only 2 columns (or 3 columns if the one of those is an autoincremented primary key).
Computing the shortest path between 2 tables
Following foreign keys, the getShortestPath
function will try to find the shortest path between 2 tables.
It will return the list of foreign keys it used to link the 2 tables.
Internals:
- Each foreign key has a cost of 1
- Junction tables have a cost of 1.5, instead of 2 (one for each foreign key)
// $conn is the DBAL connection.
$schema = $conn->getSchemaManager()->createSchema();
// Let's detect the shortest path between 2 tables:
$fks = $schema->getShortestPath("users", "rights");
// This will return an array of Doctrine\DBAL\Schema\ForeignKeyConstraint objects
// TODO: Ambiguity exception!
Found a typo? Something is wrong in this documentation? Just fork and edit it!