public function testCompositeForeignKeys()
{
$this->conn->expects($this->once())->method('fetchAll')->will($this->returnValue($this->getFKDefinition()));
$fkeys = $this->manager->listTableForeignKeys('dummy');
$this->assertEquals(1, count($fkeys), "Table has to have one foreign key.");
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\ForeignKeyConstraint', $fkeys[0]);
$this->assertEquals(array('column_1', 'column_2', 'column_3'), array_map('strtolower', $fkeys[0]->getLocalColumns()));
$this->assertEquals(array('column_1', 'column_2', 'column_3'), array_map('strtolower', $fkeys[0]->getForeignColumns()));
}
/**
* @param string $table
* @param string $column
* @return string
* @throws \Exception
*/
private function getForeignKeyConstraint($table, $column)
{
$keys = $this->schemaManager->listTableForeignKeys($table);
foreach ($keys as $key) {
if (in_array($column, $key->getLocalColumns(), false)) {
return $key->getName();
}
}
throw new \Exception('Foreign key constraint not found.');
}
/**
* Get data based on foreign keys information from table.
*
* @param string $table
* @param string|null $database
* @return array
*/
public function getForeign($table, $database = null)
{
$foreignData = [];
foreach ($this->schema->listTableForeignKeys($table, $database) as $foreign) {
$localColumns = $foreign->getLocalColumns();
$referencesColumns = $foreign->getForeignColumns();
$indexName = $foreign->getName();
$defaultIndexName = implode('_', [$table, reset($localColumns), 'foreign']);
$foreignData[reset($localColumns)] = ['dropForeignKey' => $indexName, 'name' => $indexName != $defaultIndexName ? $indexName : null, 'references' => reset($referencesColumns), 'on' => $foreign->getForeignTableName(), 'onDelete' => $foreign->onDelete(), 'onUpdate' => $foreign->onUpdate()];
}
return $foreignData;
}
public function testListForeignKeys()
{
if(!$this->_conn->getDatabasePlatform()->supportsForeignKeyConstraints()) {
$this->markTestSkipped('Does not support foreign key constraints.');
}
$this->createTestTable('test_create_fk1');
$this->createTestTable('test_create_fk2');
$foreignKey = new \Doctrine\DBAL\Schema\ForeignKeyConstraint(
array('foreign_key_test'), 'test_create_fk2', array('id'), 'foreign_key_test_fk', array('onDelete' => 'CASCADE')
);
$this->_sm->createForeignKey($foreignKey, 'test_create_fk1');
$fkeys = $this->_sm->listTableForeignKeys('test_create_fk1');
$this->assertEquals(1, count($fkeys), "Table 'test_create_fk1' has to have one foreign key.");
$this->assertType('Doctrine\DBAL\Schema\ForeignKeyConstraint', $fkeys[0]);
$this->assertEquals(array('foreign_key_test'), array_map('strtolower', $fkeys[0]->getLocalColumns()));
$this->assertEquals(array('id'), array_map('strtolower', $fkeys[0]->getForeignColumns()));
$this->assertEquals('test_create_fk2', strtolower($fkeys[0]->getForeignTableName()));
if($fkeys[0]->hasOption('onDelete')) {
$this->assertEquals('CASCADE', $fkeys[0]->getOption('onDelete'));
}
}
public function checkForForeignKeySourceTable($columnName)
{
// @var Doctrine\DBAL\Schema\ForeignKeyConstraint[]
$fks = $this->schema->listTableForeignKeys($this->table->getName());
foreach ($fks as $f) {
if (in_array($columnName, $f->getColumns())) {
return $f->getForeignTableName();
}
}
return '';
}
/**
* @param $name
* @return \Doctrine\DBAL\Schema\ForeignKeyConstraint|null
*/
protected function getForeignKey($name)
{
$foreignKeys = $this->schemaManager->listTableForeignKeys($this->table);
foreach ($foreignKeys as $foreignKey) {
foreach ($foreignKey->getLocalColumns() as $localColumn) {
if ($localColumn === $name) {
return $foreignKey;
}
}
}
return null;
}
/**
* Get array of foreign keys
*
* @param string $table Table Name
* @param \Doctrine\DBAL\Schema\AbstractSchemaManager $schema
*
* @return array
*/
public function generate($table, $schema)
{
$this->table = $table;
$fields = [];
$foreignKeys = $schema->listTableForeignKeys($table);
if (empty($foreignKeys)) {
return array();
}
foreach ($foreignKeys as $foreignKey) {
$fields[] = ['name' => $this->getName($foreignKey), 'field' => $foreignKey->getLocalColumns()[0], 'references' => $foreignKey->getForeignColumns()[0], 'on' => $foreignKey->getForeignTableName(), 'onUpdate' => $foreignKey->hasOption('onUpdate') ? $foreignKey->getOption('onUpdate') : 'RESTRICT', 'onDelete' => $foreignKey->hasOption('onDelete') ? $foreignKey->getOption('onDelete') : 'RESTRICT'];
}
return $fields;
}
public function testListForeignKeysComposite()
{
if (!$this->_conn->getDatabasePlatform()->supportsForeignKeyConstraints()) {
$this->markTestSkipped('Does not support foreign key constraints.');
}
$this->_sm->createTable($this->getTestTable('test_create_fk3'));
$this->_sm->createTable($this->getTestCompositeTable('test_create_fk4'));
$foreignKey = new \Doctrine\DBAL\Schema\ForeignKeyConstraint(array('id', 'foreign_key_test'), 'test_create_fk4', array('id', 'other_id'), 'foreign_key_test_fk');
$this->_sm->createForeignKey($foreignKey, 'test_create_fk3');
$fkeys = $this->_sm->listTableForeignKeys('test_create_fk3');
$this->assertEquals(1, count($fkeys), "Table 'test_create_fk3' has to have one foreign key.");
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\ForeignKeyConstraint', $fkeys[0]);
$this->assertEquals(array('id', 'foreign_key_test'), array_map('strtolower', $fkeys[0]->getLocalColumns()));
$this->assertEquals(array('id', 'other_id'), array_map('strtolower', $fkeys[0]->getForeignColumns()));
}
/**
* return the the interesect on primary index and foreign key.
* if we have 2 primary key that are also foreign key without a type field it should be a pivot table !
*
* @param $table
* @return array
*/
public function checkPivots($table)
{
$foreignKeys = $this->schema->listTableForeignKeys($table);
$indexes = $this->schema->listTableIndexes($table);
$toto = [];
foreach ($foreignKeys as $key) {
$toto[] = $key->getLocalColumns();
}
$tata = [];
foreach ($indexes as $key) {
if ($key->isPrimary()) {
$tata[] = $key->getColumns();
}
}
return array_intersect(array_flatten($toto), array_flatten($tata));
}
/**
* @param EloquentModel $model
* @return $this
*/
protected function setRelations(EloquentModel $model)
{
$foreignKeys = $this->manager->listTableForeignKeys($this->addPrefix($model->getTableName()));
foreach ($foreignKeys as $tableForeignKey) {
$tableForeignColumns = $tableForeignKey->getForeignColumns();
if (count($tableForeignColumns) !== 1) {
continue;
}
$relation = new BelongsTo($this->cropPrefix($tableForeignKey->getForeignTableName()), $tableForeignKey->getLocalColumns()[0], $tableForeignColumns[0]);
$model->addRelation($relation);
}
$tables = $this->manager->listTables();
foreach ($tables as $table) {
if ($table->getName() === $this->addPrefix($model->getTableName())) {
continue;
}
$foreignKeys = $table->getForeignKeys();
foreach ($foreignKeys as $name => $foreignKey) {
if ($foreignKey->getForeignTableName() === $this->addPrefix($model->getTableName())) {
$localColumns = $foreignKey->getLocalColumns();
if (count($localColumns) !== 1) {
continue;
}
if (count($foreignKeys) === 2 && count($table->getColumns()) >= 2) {
$keys = array_keys($foreignKeys);
$key = array_search($name, $keys) === 0 ? 1 : 0;
$secondForeignKey = $foreignKeys[$keys[$key]];
$secondForeignTable = $secondForeignKey->getForeignTableName();
$relation = new BelongsToMany($this->cropPrefix($secondForeignTable), $table->getName(), $localColumns[0], $secondForeignKey->getLocalColumns()[0]);
$model->addRelation($relation);
break;
} else {
$tableName = $this->cropPrefix($foreignKey->getLocalTableName());
$foreignColumn = $localColumns[0];
$localColumn = $foreignKey->getForeignColumns()[0];
if ($this->isColumnUnique($table, $foreignColumn)) {
$relation = new HasOne($tableName, $foreignColumn, $localColumn);
} else {
$relation = new HasMany($tableName, $foreignColumn, $localColumn);
}
$model->addRelation($relation);
}
}
}
}
return $this;
}
public function testListTableDetailsWithFullQualifiedTableName()
{
if (!$this->_sm->getDatabasePlatform()->supportsSchemas()) {
$this->markTestSkipped('Test only works on platforms that support schemas.');
}
$defaultSchemaName = $this->_sm->getDatabasePlatform()->getDefaultSchemaName();
$primaryTableName = 'primary_table';
$foreignTableName = 'foreign_table';
$table = new Table($foreignTableName);
$table->addColumn('id', 'integer', array('autoincrement' => true));
$table->setPrimaryKey(array('id'));
$this->_sm->dropAndCreateTable($table);
$table = new Table($primaryTableName);
$table->addColumn('id', 'integer', array('autoincrement' => true));
$table->addColumn('foo', 'integer');
$table->addColumn('bar', 'string');
$table->addForeignKeyConstraint($foreignTableName, array('foo'), array('id'));
$table->addIndex(array('bar'));
$table->setPrimaryKey(array('id'));
$this->_sm->dropAndCreateTable($table);
$this->assertEquals($this->_sm->listTableColumns($primaryTableName), $this->_sm->listTableColumns($defaultSchemaName . '.' . $primaryTableName));
$this->assertEquals($this->_sm->listTableIndexes($primaryTableName), $this->_sm->listTableIndexes($defaultSchemaName . '.' . $primaryTableName));
$this->assertEquals($this->_sm->listTableForeignKeys($primaryTableName), $this->_sm->listTableForeignKeys($defaultSchemaName . '.' . $primaryTableName));
}
/**
* {@see AbstractSchemaManager::listTableForeignKeys}
*/
public function listTableForeignKeys($table, $database = null)
{
return $this->manager->listTableForeignKeys($this->replacePrefix($table), $database);
}
