A Schema Manager instance helps you with the abstraction of the generation of SQL assets such as Tables, Sequences, Foreign Keys and Indexes.
To retrieve the SchemaManager
for your connection you can use
the getSchemaManager()
method:
<?php
$sm = $conn->getSchemaManager();
Now with the SchemaManager
instance in $em
you can use the
available methods to learn about your database schema:
Note
Parameters containing identifiers passed to the SchemaManager methods are NOT quoted automatically! Identifier quoting is really difficult to do manually in a consistent way across different databases. You have to manually quote the identifiers when you accept data from user- or other sources not under your control.
Retrieve an array of databases on the configured connection:
<?php
$databases = $sm->listDatabases();
Retrieve an array of Doctrine\DBAL\Schema\Sequence
instances
that exist for a database:
<?php
$sequences = $sm->listSequences();
Or if you want to manually specify a database name:
<?php
$sequences = $sm->listSequences('dbname');
Now you can loop over the array inspecting each sequence object:
<?php
foreach ($sequences as $sequence) {
echo $sequence->getName() . "\n";
}
Retrieve an array of Doctrine\DBAL\Schema\Column
instances that
exist for the given table:
<?php
$columns = $sm->listTableColumns('user');
Now you can loop over the array inspecting each column object:
<?php
foreach ($columns as $column) {
echo $column->getName() . ': ' . $column->getType() . "\n";
}
Retrieve a single Doctrine\DBAL\Schema\Table
instance that
encapsulates all the details of the given table:
<?php
$table = $sm->listTableDetails('user');
Now you can call methods on the table to manipulate the in memory schema for that table. For example we can add a new column:
<?php
$table->addColumn('email_address', 'string');
Retrieve an array of Doctrine\DBAL\Schema\ForeignKeyConstraint
instances that exist for the given table:
<?php
$foreignKeys = $sm->listTableForeignKeys('user');
Now you can loop over the array inspecting each foreign key object:
<?php
foreach ($foreignKeys as $foreignKey) {
echo $foreignKey->getName() . ': ' . $foreignKey->getLocalTableName() ."\n";
}
Retrieve an array of Doctrine\DBAL\Schema\Index
instances that
exist for the given table:
<?php
$indexes = $sm->listTableIndexes('user');
Now you can loop over the array inspecting each index object:
<?php
foreach ($indexes as $index) {
echo $index->getName() . ': ' . ($index->isUnique() ? 'unique' : 'not unique') . "\n";
}
Retrieve an array of Doctrine\DBAL\Schema\Table
instances that
exist in the connections database:
<?php
$tables = $sm->listTables();
Each Doctrine\DBAl\Schema\Table
instance is populated with
information provided by all the above methods. So it encapsulates
an array of Doctrine\DBAL\Schema\Column
instances that can be
retrieved with the getColumns()
method:
<?php
foreach ($tables as $table) {
echo $table->getName() . " columns:\n\n";
foreach ($table->getColumns() as $column) {
echo ' - ' . $column->getName() . "\n";
}
}
Retrieve an array of Doctrine\DBAL\Schema\View
instances that
exist in the connections database:
<?php
$views = $sm->listViews();
Now you can loop over the array inspecting each view object:
<?php
foreach ($views as $view) {
echo $view->getName() . ': ' . $view->getSql() . "\n";
}
For a complete representation of the current database you can use
the createSchema()
method which returns an instance of
Doctrine\DBAL\Schema\Schema
, which you can use in conjunction
with the SchemaTool or Schema Comparator.
<?php
$fromSchema = $sm->createSchema();
Now we can clone the $fromSchema
to $toSchema
and drop a
table:
<?php
$toSchema = clone $fromSchema;
$toSchema->dropTable('user');
Now we can compare the two schema instances in order to calculate the differences between them and return the SQL required to make the changes on the database:
<?php
$sql = $fromSchema->getMigrateToSql($toSchema, $conn->getDatabasePlatform());
The $sql
array should give you a SQL query to drop the user
table:
<?php
print_r($sql);
/*
array(
0 => 'DROP TABLE user'
)
*/