/**
* @param string $schema
* @param Table $table
* @param string $name
* @param FieldDefinition $definition
*
* @throws DoctrineStorageException
*/
public function map(string $schema, Table $table, string $name, FieldDefinition $definition)
{
if (!$definition instanceof AssociationFieldDefinition) {
throw DoctrineStorageException::invalidDefinition(AssociationFieldDefinition::class, $definition);
}
$table->addColumn($name, 'guid', ['notnull' => !$definition->isNullable(), 'default' => $definition->defaultValue(), 'length' => $definition->options()['length'] ?? null, 'unique' => $definition->options()['unique'] ?? false]);
$table->addForeignKeyConstraint($this->tableName($schema, $definition->typeSchema()->name()), [$name], ['id']);
}
/**
* @param DBALTable $KeyTarget Foreign Key (Column: KeySource Name)
* @param DBALTable $KeySource Foreign Data (Column: Id)
*/
public function addForeignKey(DBALTable &$KeyTarget, DBALTable $KeySource)
{
if (!$this->Database->hasColumn($KeyTarget->getName(), $KeySource->getName())) {
$KeyTarget->addColumn($KeySource->getName(), 'bigint');
if ($this->Database->getPlatform()->supportsForeignKeyConstraints()) {
$KeyTarget->addForeignKeyConstraint($KeySource, array($KeySource->getName()), array('Id'));
}
}
}
public function doIt(Table $table, Schema $schema = null)
{
$table->addColumn("title", "string", ['notnull' => false]);
$table->addColumn("sort", "integer", ['notnull' => false]);
$table->addColumn("path", "string", ['notnull' => false]);
$table->addColumn("level", "integer", ['notnull' => false]);
$table->addColumn("parent_id", "integer", ['notnull' => false]);
$table->addForeignKeyConstraint($table, array('parent_id'), array("id"), array("onUpdate" => "CASCADE"));
}
/**
* Add a foreign key constraint to the table being built.
*
* @param string[] $localColumnNames
* @param \TYPO3\CMS\Core\Database\Schema\Parser\AST\ReferenceDefinition $referenceDefinition
* @param string $indexName
*/
protected function addForeignKeyConstraint(array $localColumnNames, ReferenceDefinition $referenceDefinition, string $indexName = null)
{
$foreignTableName = $referenceDefinition->tableName->getQuotedName();
$foreignColumNames = array_map(function (IndexColumnName $columnName) {
return $columnName->columnName->getQuotedName();
}, $referenceDefinition->columnNames);
$options = ['onDelete' => $referenceDefinition->onDelete, 'onUpdate' => $referenceDefinition->onUpdate];
$this->table->addForeignKeyConstraint($foreignTableName, $localColumnNames, $foreignColumNames, $options, $indexName);
}
/**
* @param Schema $schema
* @param Table $table
* @param string $keyName
*/
protected function setOwnerCascadeDelete(Schema $schema, Table $table, $keyName)
{
foreach ($table->getForeignKeys() as $foreignKey) {
if ($foreignKey->getLocalColumns() == ['owner_id']) {
$table->removeForeignKey($foreignKey->getName());
$table->addForeignKeyConstraint($schema->getTable('orocrm_contact'), ['owner_id'], ['id'], ['onDelete' => 'CASCADE', 'onUpdate' => null], $keyName);
break;
}
}
}
public function testEncodedForeignKeyConstraintNameIsTheSameAsDoctrineDefault()
{
$tableName1 = 'tbl123456789012345';
$columnName1 = 'clmn1234567890';
$tableName2 = 'tbl1234567890';
$columnName2 = 'clmn12345';
$table1 = new Table($tableName1, [new Column($columnName1, Type::getType('integer'))]);
$table2 = new Table($tableName2, [new Column($columnName2, Type::getType('integer'))]);
$table2->setPrimaryKey([$columnName2]);
$table1->addForeignKeyConstraint($table2, [$columnName1], [$columnName2]);
$foreignKeys = $table1->getForeignKeys();
$doctrineResult = array_pop($foreignKeys)->getName();
$generator = new DbIdentifierNameGenerator();
$result = $generator->generateForeignKeyConstraintName($tableName1, [$columnName1]);
$this->assertEquals($doctrineResult, $result);
}
public function resetDatabase()
{
$dbPath = $this->app['sqlite_path'];
$dbDir = dirname($dbPath);
$filesystem = new Filesystem();
$filesystem->mkdir($dbDir);
$filesystem->chmod($dbDir, 0777, 00, true);
if (!is_writable($dbDir)) {
throw new \Exception('Unable to write to ' . $dbPath);
}
$schemaManager = $this->getConnection()->getSchemaManager();
$userTable = new Table('user');
$userTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$userTable->setPrimaryKey(array('id'));
$userTable->addColumn('name', 'string', array('length' => 255));
$userTable->addUniqueIndex(array('name'));
$userTable->addColumn('age', 'integer');
$schemaManager->dropAndCreateTable($userTable);
$bookTable = new Table('book');
$bookTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$bookTable->setPrimaryKey(array('id'));
$bookTable->addColumn('name', 'string', array('length' => 255));
$bookTable->addUniqueIndex(array('name'));
$bookTable->addColumn('userID', 'integer');
$bookTable->addColumn('ISBN', 'integer');
$bookTable->addForeignKeyConstraint($userTable, array('userID'), array('id'));
$schemaManager->dropAndCreateTable($bookTable);
$bannedTable = new Table('banned');
$bannedTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$bannedTable->setPrimaryKey(array('id'));
$bannedTable->addColumn('name', 'string', array('length' => 255));
$bannedTable->addUniqueIndex(array('name'));
$bannedTable->addColumn('userID', 'integer');
$bannedTable->addColumn('ISBN', 'integer');
$schemaManager->dropAndCreateTable($bannedTable);
}
public function addForeign(Table $table, Table $foreignTable)
{
$columnName = $foreignTable->getName() . '_id';
$table->addColumn($columnName, "integer", ['notnull' => false]);
$table->addForeignKeyConstraint($foreignTable, array($columnName), array("id"), array("onUpdate" => "CASCADE", "onDelete" => "SET NULL"));
}
/**
* @group DBAL-1095
*/
public function testDoesNotListIndexesImplicitlyCreatedByForeignKeys()
{
if (!$this->_sm->getDatabasePlatform()->supportsForeignKeyConstraints()) {
$this->markTestSkipped('This test is only supported on platforms that have foreign keys.');
}
$primaryTable = new Table('test_list_index_implicit_primary');
$primaryTable->addColumn('id', 'integer');
$primaryTable->setPrimaryKey(array('id'));
$foreignTable = new Table('test_list_index_implicit_foreign');
$foreignTable->addColumn('fk1', 'integer');
$foreignTable->addColumn('fk2', 'integer');
$foreignTable->addIndex(array('fk1'), 'explicit_fk1_idx');
$foreignTable->addForeignKeyConstraint('test_list_index_implicit_primary', array('fk1'), array('id'));
$foreignTable->addForeignKeyConstraint('test_list_index_implicit_primary', array('fk2'), array('id'));
$this->_sm->dropAndCreateTable($primaryTable);
$this->_sm->dropAndCreateTable($foreignTable);
$indexes = $this->_sm->listTableIndexes('test_list_index_implicit_foreign');
$this->assertCount(2, $indexes);
$this->assertArrayHasKey('explicit_fk1_idx', $indexes);
$this->assertArrayHasKey('idx_6d88c7b4fdc58d6c', $indexes);
}
/**
* Specify a foreign key for the table.
*
* @param string $table
* @param array|string $localColumnNames
* @param array|string $foreignColumnNames
* @param array $options
* @param null $constraintName
*
* @return Blueprint
*/
public function foreign($table, $localColumnNames, $foreignColumnNames = 'id', $options = [], $constraintName = null)
{
$localColumnNames = is_array($localColumnNames) ? $localColumnNames : [$localColumnNames];
$foreignColumnNames = is_array($foreignColumnNames) ? $foreignColumnNames : [$foreignColumnNames];
return $this->table->addForeignKeyConstraint($table, $localColumnNames, $foreignColumnNames, $options, $constraintName);
}
public function resetDatabase()
{
// 1) check DB permissions
$dbPath = $this->app['sqlite_path'];
$dbDir = dirname($dbPath);
// make sure the directory is available and writeable
$filesystem = new Filesystem();
$filesystem->mkdir($dbDir);
$filesystem->chmod($dbDir, 0777, 00, true);
if (!is_writable($dbDir)) {
throw new \Exception('Unable to write to ' . $dbPath);
}
// 2) Add some tables bro!
$schemaManager = $this->getConnection()->getSchemaManager();
$userTable = new Table('user');
$userTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$userTable->setPrimaryKey(array('id'));
$userTable->addColumn('email', 'string', array('length' => 255));
$userTable->addUniqueIndex(array('email'));
$userTable->addColumn('username', 'string', array('length' => 50));
$userTable->addUniqueIndex(array('username'));
$userTable->addColumn('password', 'string', array('length' => 255));
$schemaManager->dropAndCreateTable($userTable);
$tokenTable = new Table(ApiTokenRepository::TABLE_NAME);
$tokenTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$tokenTable->setPrimaryKey(array('id'));
$tokenTable->addColumn('token', 'string', array('length' => 32));
$tokenTable->addColumn('userId', 'integer');
$tokenTable->addColumn('notes', 'string', array('length' => 255));
$tokenTable->addColumn('createdAt', 'datetime');
$tokenTable->addUniqueIndex(array('token'), 'token_idx');
$tokenTable->addForeignKeyConstraint($userTable, array('userId'), array('id'));
$schemaManager->dropAndCreateTable($tokenTable);
$programmerTable = new Table('programmer');
$programmerTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$programmerTable->setPrimaryKey(array('id'));
$programmerTable->addColumn('nickname', 'string', array('length' => 255));
$programmerTable->addUniqueIndex(array('nickname'));
$programmerTable->addColumn('avatarNumber', 'integer');
$programmerTable->addColumn('tagLine', 'integer', array('notnull' => false));
$programmerTable->addColumn('userId', 'integer');
$programmerTable->addColumn('powerLevel', 'integer');
$programmerTable->addForeignKeyConstraint($userTable, array('userId'), array('id'));
$schemaManager->dropAndCreateTable($programmerTable);
$projectTable = new Table('project');
$projectTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$projectTable->setPrimaryKey(array('id'));
$projectTable->addColumn('name', 'string', array('length' => 255));
$projectTable->addColumn('difficultyLevel', 'integer');
$schemaManager->dropAndCreateTable($projectTable);
$battleTable = new Table('battle');
$battleTable->addColumn('id', 'integer', array('unsigned' => true, 'autoincrement' => true));
$battleTable->setPrimaryKey(array('id'));
$battleTable->addColumn('programmerId', 'integer');
$battleTable->addColumn('projectId', 'integer');
$battleTable->addColumn('didProgrammerWin', 'integer');
$battleTable->addColumn('foughtAt', 'datetime');
$battleTable->addColumn('notes', 'text');
$battleTable->addForeignKeyConstraint($programmerTable, array('programmerId'), array('id'));
$battleTable->addForeignKeyConstraint($projectTable, array('projectId'), array('id'));
$schemaManager->dropAndCreateTable($battleTable);
}
public function testTableUpdateForeignKey()
{
$tableForeign = new Table("bar");
$tableForeign->addColumn('id', 'integer');
$table1 = new Table("foo");
$table1->addColumn('fk', 'integer');
$table1->addForeignKeyConstraint($tableForeign, array('fk'), array('id'));
$table2 = new Table("foo");
$table2->addColumn('fk', 'integer');
$table2->addForeignKeyConstraint($tableForeign, array('fk'), array('id'), array('onUpdate' => 'CASCADE'));
$c = new Comparator();
$tableDiff = $c->diffTable($table1, $table2);
$this->assertType('Doctrine\\DBAL\\Schema\\TableDiff', $tableDiff);
$this->assertEquals(1, count($tableDiff->changedForeignKeys));
}
public function testAddForeignKeyConstraint()
{
$table = new Table("foo");
$table->addColumn("id", 'integer');
$foreignTable = new Table("bar");
$foreignTable->addColumn("id", 'integer');
$table->addForeignKeyConstraint($foreignTable, array("id"), array("id"), array("foo" => "bar"));
$constraints = $table->getForeignKeys();
$this->assertEquals(1, count($constraints));
$this->assertType('Doctrine\\DBAL\\Schema\\ForeignKeyConstraint', $constraints["foo_id_fk"]);
$this->assertEquals("foo_id_fk", $constraints["foo_id_fk"]->getName());
$this->assertTrue($constraints["foo_id_fk"]->hasOption("foo"));
$this->assertEquals("bar", $constraints["foo_id_fk"]->getOption("foo"));
}
protected static function parseForeignKey(Schema $schema, Table $table, SimpleXMLElement $xForeignKey, AbstractPlatform $platform)
{
$foreignTable = (string) $xForeignKey['table'];
$localColumnNames = array();
$foreignColumnNames = array();
foreach ($xForeignKey->column as $xColumn) {
$localColumnNames[] = (string) $xColumn['local'];
$foreignColumnNames[] = (string) $xColumn['foreign'];
}
$constraintName = isset($xForeignKey['name']) ? (string) $xForeignKey['name'] : null;
$options = array();
if (isset($xForeignKey['onupdate'])) {
$options['onUpdate'] = (string) $xForeignKey['onupdate'];
}
if (isset($xForeignKey['ondelete'])) {
$options['onDelete'] = (string) $xForeignKey['ondelete'];
}
$table->addForeignKeyConstraint($foreignTable, $localColumnNames, $foreignColumnNames, $options, $constraintName);
}
private function setUpForeignKeyConstraintViolationExceptionTest()
{
$schemaManager = $this->_conn->getSchemaManager();
$table = new Table("constraint_error_table");
$table->addColumn('id', 'integer', array());
$table->setPrimaryKey(array('id'));
$owningTable = new Table("owning_table");
$owningTable->addColumn('id', 'integer', array());
$owningTable->addColumn('constraint_id', 'integer', array());
$owningTable->setPrimaryKey(array('id'));
$owningTable->addForeignKeyConstraint($table, array('constraint_id'), array('id'));
$schemaManager->createTable($table);
$schemaManager->createTable($owningTable);
}
/**
* @group DBAL-374
*/
public function testQuotedColumnInForeignKeyPropagation()
{
$table = new Table('`quoted`');
$table->addColumn('create', 'string');
$table->addColumn('foo', 'string');
$table->addColumn('`bar`', 'string');
// Foreign table with reserved keyword as name (needs quotation).
$foreignTable = new Table('foreign');
$foreignTable->addColumn('create', 'string');
// Foreign column with reserved keyword as name (needs quotation).
$foreignTable->addColumn('bar', 'string');
// Foreign column with non-reserved keyword as name (does not need quotation).
$foreignTable->addColumn('`foo-bar`', 'string');
// Foreign table with special character in name (needs quotation on some platforms, e.g. Sqlite).
$table->addForeignKeyConstraint($foreignTable, array('create', 'foo', '`bar`'), array('create', 'bar', '`foo-bar`'), array(), 'FK_WITH_RESERVED_KEYWORD');
// Foreign table with non-reserved keyword as name (does not need quotation).
$foreignTable = new Table('foo');
$foreignTable->addColumn('create', 'string');
// Foreign column with reserved keyword as name (needs quotation).
$foreignTable->addColumn('bar', 'string');
// Foreign column with non-reserved keyword as name (does not need quotation).
$foreignTable->addColumn('`foo-bar`', 'string');
// Foreign table with special character in name (needs quotation on some platforms, e.g. Sqlite).
$table->addForeignKeyConstraint($foreignTable, array('create', 'foo', '`bar`'), array('create', 'bar', '`foo-bar`'), array(), 'FK_WITH_NON_RESERVED_KEYWORD');
// Foreign table with special character in name (needs quotation on some platforms, e.g. Sqlite).
$foreignTable = new Table('`foo-bar`');
$foreignTable->addColumn('create', 'string');
// Foreign column with reserved keyword as name (needs quotation).
$foreignTable->addColumn('bar', 'string');
// Foreign column with non-reserved keyword as name (does not need quotation).
$foreignTable->addColumn('`foo-bar`', 'string');
// Foreign table with special character in name (needs quotation on some platforms, e.g. Sqlite).
$table->addForeignKeyConstraint($foreignTable, array('create', 'foo', '`bar`'), array('create', 'bar', '`foo-bar`'), array(), 'FK_WITH_INTENDED_QUOTATION');
$sql = $this->_platform->getCreateTableSQL($table, AbstractPlatform::CREATE_FOREIGNKEYS);
$this->assertEquals($this->getQuotedColumnInForeignKeySQL(), $sql);
}
/**
* Gather columns and fk constraints that are required for one part of relationship.
*
* @param array $joinColumns
* @param \Doctrine\DBAL\Schema\Table $theJoinTable
* @param ClassMetadata $class
* @param array $mapping
* @param array $primaryKeyColumns
* @param array $uniqueConstraints
*/
private function _gatherRelationJoinColumns($joinColumns, $theJoinTable, $class, $mapping, &$primaryKeyColumns, &$uniqueConstraints)
{
$localColumns = array();
$foreignColumns = array();
$fkOptions = array();
$foreignTableName = $this->quoteStrategy->getTableName($class, $this->platform);
foreach ($joinColumns as $joinColumn) {
list($definingClass, $referencedFieldName) = $this->getDefiningClass($class, $joinColumn['referencedColumnName']);
if (!$definingClass) {
throw new \Doctrine\ORM\ORMException("Column name `" . $joinColumn['referencedColumnName'] . "` referenced for relation from " . $mapping['sourceEntity'] . " towards " . $mapping['targetEntity'] . " does not exist.");
}
$quotedColumnName = $this->quoteStrategy->getJoinColumnName($joinColumn, $class, $this->platform);
$quotedRefColumnName = $this->quoteStrategy->getReferencedJoinColumnName($joinColumn, $class, $this->platform);
$primaryKeyColumns[] = $quotedColumnName;
$localColumns[] = $quotedColumnName;
$foreignColumns[] = $quotedRefColumnName;
if (!$theJoinTable->hasColumn($quotedColumnName)) {
// Only add the column to the table if it does not exist already.
// It might exist already if the foreign key is mapped into a regular
// property as well.
$fieldMapping = $definingClass->getFieldMapping($referencedFieldName);
$columnDef = null;
if (isset($joinColumn['columnDefinition'])) {
$columnDef = $joinColumn['columnDefinition'];
} elseif (isset($fieldMapping['columnDefinition'])) {
$columnDef = $fieldMapping['columnDefinition'];
}
$columnOptions = array('notnull' => false, 'columnDefinition' => $columnDef);
if (isset($joinColumn['nullable'])) {
$columnOptions['notnull'] = !$joinColumn['nullable'];
}
if ($fieldMapping['type'] == "string" && isset($fieldMapping['length'])) {
$columnOptions['length'] = $fieldMapping['length'];
} elseif ($fieldMapping['type'] == "decimal") {
$columnOptions['scale'] = $fieldMapping['scale'];
$columnOptions['precision'] = $fieldMapping['precision'];
}
$theJoinTable->addColumn($quotedColumnName, $fieldMapping['type'], $columnOptions);
}
if (isset($joinColumn['unique']) && $joinColumn['unique'] == true) {
$uniqueConstraints[] = array('columns' => array($quotedColumnName));
}
if (isset($joinColumn['onDelete'])) {
$fkOptions['onDelete'] = $joinColumn['onDelete'];
}
}
$theJoinTable->addForeignKeyConstraint($foreignTableName, $localColumns, $foreignColumns, $fkOptions);
}
public function testAlterTable()
{
$table = new Table('user');
$table->addColumn('id', 'integer');
$table->addColumn('article', 'integer');
$table->addColumn('post', 'integer');
$table->addColumn('parent', 'integer');
$table->setPrimaryKey(array('id'));
$table->addForeignKeyConstraint('article', array('article'), array('id'), array('deferrable' => true));
$table->addForeignKeyConstraint('post', array('post'), array('id'), array('deferred' => true));
$table->addForeignKeyConstraint('user', array('parent'), array('id'), array('deferrable' => true, 'deferred' => true));
$table->addIndex(array('article', 'post'), 'index1');
$diff = new TableDiff('user');
$diff->fromTable = $table;
$diff->newName = 'client';
$diff->renamedColumns['id'] = new \Doctrine\DBAL\Schema\Column('key', \Doctrine\DBAL\Types\Type::getType('integer'), array());
$diff->renamedColumns['post'] = new \Doctrine\DBAL\Schema\Column('comment', \Doctrine\DBAL\Types\Type::getType('integer'), array());
$diff->removedColumns['parent'] = new \Doctrine\DBAL\Schema\Column('comment', \Doctrine\DBAL\Types\Type::getType('integer'), array());
$diff->removedIndexes['index1'] = $table->getIndex('index1');
$sql = array('DROP INDEX IDX_8D93D64923A0E66', 'DROP INDEX IDX_8D93D6495A8A6C8D', 'DROP INDEX IDX_8D93D6493D8E604F', 'DROP INDEX index1', 'CREATE TEMPORARY TABLE __temp__user AS SELECT id, article, post FROM user', 'DROP TABLE user', 'CREATE TABLE user (' . '"key" INTEGER NOT NULL, article INTEGER NOT NULL, comment INTEGER NOT NULL' . ', PRIMARY KEY("key")' . ', CONSTRAINT FK_8D93D64923A0E66 FOREIGN KEY (article) REFERENCES article (id) DEFERRABLE INITIALLY IMMEDIATE' . ', CONSTRAINT FK_8D93D6495A8A6C8D FOREIGN KEY (comment) REFERENCES post (id) NOT DEFERRABLE INITIALLY DEFERRED' . ')', 'INSERT INTO user ("key", article, comment) SELECT id, article, post FROM __temp__user', 'DROP TABLE __temp__user', 'ALTER TABLE user RENAME TO client', 'CREATE INDEX IDX_8D93D64923A0E66 ON client (article)', 'CREATE INDEX IDX_8D93D6495A8A6C8D ON client (comment)');
$this->assertEquals($sql, $this->_platform->getAlterTableSQL($diff));
}
/**
* @group DBAL-50
*/
public function testAddForeignKeyIndexImplicitly()
{
$table = new Table("foo");
$table->addColumn("id", 'integer');
$foreignTable = new Table("bar");
$foreignTable->addColumn("id", 'integer');
$table->addForeignKeyConstraint($foreignTable, array("id"), array("id"), array("foo" => "bar"));
$this->assertEquals(1, count($table->getIndexes()));
$this->assertTrue($table->hasIndex("foo_id_idx"));
$this->assertEquals(array('id'), $table->getIndex('foo_id_idx')->getColumns());
}
/**
* Add foreign keys from BelongsTo relations to the table schema
* @param Table $table
* @return Table
*/
protected function addForeignKeys(Table $table)
{
$entityName = $this->mapper->entity();
$entity = new $entityName();
$relations = $entityName::relations($this->mapper, $entity);
$fields = $this->mapper->entityManager()->fields();
foreach ($relations as $relationName => $relation) {
if ($relation instanceof BelongsTo) {
$fieldInfo = $fields[$relation->localKey()];
if ($fieldInfo['foreignkey'] === false) {
continue;
}
$foreignTableMapper = $relation->mapper()->getMapper($relation->entityName());
$foreignTable = $foreignTableMapper->table();
$foreignSchemaManager = $foreignTableMapper->connection()->getSchemaManager();
$foreignTableObject = $foreignSchemaManager->listTableDetails($foreignTable);
$foreignTableColumns = $foreignTableObject->getColumns();
$foreignTableNotExists = empty($foreignTableColumns);
$foreignKeyNotExists = !array_key_exists($relation->foreignKey(), $foreignTableColumns);
// We need to use the is_a() function because the there is some inconsistency in entity names (leading slash)
$notRecursiveForeignKey = !is_a($entity, $relation->entityName());
/* Migrate foreign table if:
* - the foreign table not exists
* - the foreign key not exists
* - the foreign table is not the same as the current table (recursion check)
* This migration eliminates the 'Integrity constraint violation' error
*/
if (($foreignTableNotExists || $foreignKeyNotExists) && $notRecursiveForeignKey) {
$foreignTableMapper->migrate();
}
$onUpdate = !is_null($fieldInfo['onUpdate']) ? $fieldInfo['onUpdate'] : "CASCADE";
if (!is_null($fieldInfo['onDelete'])) {
$onDelete = $fieldInfo['onDelete'];
} else {
if ($fieldInfo['notnull']) {
$onDelete = "CASCADE";
} else {
$onDelete = "SET NULL";
}
}
// Field alias support
$fieldAliasMappings = $this->mapper->entityManager()->fieldAliasMappings();
if (isset($fieldAliasMappings[$relation->localKey()])) {
$localKey = $fieldAliasMappings[$relation->localKey()];
} else {
$localKey = $relation->localKey();
}
$fkName = $this->mapper->table() . '_fk_' . $relationName;
$table->addForeignKeyConstraint($foreignTable, [$localKey], [$relation->foreignKey()], ["onDelete" => $onDelete, "onUpdate" => $onUpdate], $fkName);
}
}
return $table;
}
public function testGeneratesCreateTableSQLWithForeignKeyConstraints()
{
$table = new Table('test');
$table->addColumn('id', 'integer');
$table->addColumn('fk_1', 'integer');
$table->addColumn('fk_2', 'integer');
$table->setPrimaryKey(array('id'));
$table->addForeignKeyConstraint('foreign_table', array('fk_1', 'fk_2'), array('pk_1', 'pk_2'));
$table->addForeignKeyConstraint('foreign_table2', array('fk_1', 'fk_2'), array('pk_1', 'pk_2'), array(), 'named_fk');
$this->assertEquals(array('CREATE TABLE test (id INT NOT NULL, fk_1 INT NOT NULL, fk_2 INT NOT NULL, ' . 'CONSTRAINT FK_D87F7E0C177612A38E7F4319 FOREIGN KEY (fk_1, fk_2) REFERENCES foreign_table (pk_1, pk_2), ' . 'CONSTRAINT named_fk FOREIGN KEY (fk_1, fk_2) REFERENCES foreign_table2 (pk_1, pk_2))'), $this->_platform->getCreateTableSQL($table, AbstractPlatform::CREATE_FOREIGNKEYS));
}
/**
* @group DBAL-1062
*/
public function testGeneratesAlterTableRenameIndexUsedByForeignKeySQL()
{
$foreignTable = new Table('foreign_table');
$foreignTable->addColumn('id', 'integer');
$foreignTable->setPrimaryKey(array('id'));
$primaryTable = new Table('mytable');
$primaryTable->addColumn('foo', 'integer');
$primaryTable->addColumn('bar', 'integer');
$primaryTable->addColumn('baz', 'integer');
$primaryTable->addIndex(array('foo'), 'idx_foo');
$primaryTable->addIndex(array('bar'), 'idx_bar');
$primaryTable->addForeignKeyConstraint($foreignTable, array('foo'), array('id'), array(), 'fk_foo');
$primaryTable->addForeignKeyConstraint($foreignTable, array('bar'), array('id'), array(), 'fk_bar');
$tableDiff = new TableDiff('mytable');
$tableDiff->fromTable = $primaryTable;
$tableDiff->renamedIndexes['idx_foo'] = new Index('idx_foo_renamed', array('foo'));
$this->assertSame($this->getGeneratesAlterTableRenameIndexUsedByForeignKeySQL(), $this->_platform->getAlterTableSQL($tableDiff));
}
/**
* @param Metadata $metadata
* @return Table
*/
public function metadataToTable(Metadata $metadata)
{
$tblName = $metadata->getDbTableName();
if (isset($this->md2tableCache[$tblName])) {
return $this->md2tableCache[$tblName];
}
$cols = [];
foreach ($metadata->getLocalFields() as $fieldObj) {
$col = $fieldObj->getDoctrineColumn();
$col->setLength($fieldObj->max_length);
$col->setNotnull(!$fieldObj->null);
$col->setComment($fieldObj->help_text);
$col->setAutoincrement($fieldObj->auto_increment);
$cols[] = $col;
}
$table = new Table($tblName, $cols);
$this->md2tableCache[$tblName] = $table;
foreach ($metadata->getLocalFields() as $fieldObj) {
if ($fieldObj->unique) {
$table->addUniqueIndex([$fieldObj->db_column]);
} elseif ($fieldObj->db_index) {
$table->addIndex([$fieldObj->db_column]);
}
if ($fieldObj->primary_key) {
$table->setPrimaryKey([$fieldObj->db_column]);
}
if ($this->followRelations === true && $fieldObj instanceof ForeignKey) {
$relationClass = $fieldObj->relationClass;
$relationTable = $this->metadataToTable($relationClass::metadata());
$table->addForeignKeyConstraint($relationTable, [$fieldObj->db_column], [$fieldObj->to_field]);
$this->generateQueue[] = $relationClass;
}
}
if ($this->followRelations === true) {
foreach ($metadata->getRelationFields() as $fieldObj) {
if ($fieldObj instanceof ManyToMany) {
if ($fieldObj->throughClass) {
$throughClass = $fieldObj->throughClass;
//$this->metadataToTable($throughClass::metadata());
$this->generateQueue[] = $throughClass;
}
}
}
}
return $table;
}
/**
* @group DBAL-831
*/
public function testListTableDetailsWithDifferentIdentifierQuotingRequirements()
{
$primaryTableName = '"Primary_Table"';
$offlinePrimaryTable = new Schema\Table($primaryTableName);
$offlinePrimaryTable->addColumn('"Id"', 'integer', array('autoincrement' => true, 'comment' => 'Explicit casing.'));
$offlinePrimaryTable->addColumn('select', 'integer', array('comment' => 'Reserved keyword.'));
$offlinePrimaryTable->addColumn('foo', 'integer', array('comment' => 'Implicit uppercasing.'));
$offlinePrimaryTable->addColumn('BAR', 'integer');
$offlinePrimaryTable->addColumn('"BAZ"', 'integer');
$offlinePrimaryTable->addIndex(array('select'), 'from');
$offlinePrimaryTable->addIndex(array('foo'), 'foo_index');
$offlinePrimaryTable->addIndex(array('BAR'), 'BAR_INDEX');
$offlinePrimaryTable->addIndex(array('"BAZ"'), 'BAZ_INDEX');
$offlinePrimaryTable->setPrimaryKey(array('"Id"'));
$foreignTableName = 'foreign';
$offlineForeignTable = new Schema\Table($foreignTableName);
$offlineForeignTable->addColumn('id', 'integer', array('autoincrement' => true));
$offlineForeignTable->addColumn('"Fk"', 'integer');
$offlineForeignTable->addIndex(array('"Fk"'), '"Fk_index"');
$offlineForeignTable->addForeignKeyConstraint($primaryTableName, array('"Fk"'), array('"Id"'), array(), '"Primary_Table_Fk"');
$offlineForeignTable->setPrimaryKey(array('id'));
$this->_sm->tryMethod('dropTable', $foreignTableName);
$this->_sm->tryMethod('dropTable', $primaryTableName);
$this->_sm->createTable($offlinePrimaryTable);
$this->_sm->createTable($offlineForeignTable);
$onlinePrimaryTable = $this->_sm->listTableDetails($primaryTableName);
$onlineForeignTable = $this->_sm->listTableDetails($foreignTableName);
$platform = $this->_sm->getDatabasePlatform();
// Primary table assertions
$this->assertSame($primaryTableName, $onlinePrimaryTable->getQuotedName($platform));
$this->assertTrue($onlinePrimaryTable->hasColumn('"Id"'));
$this->assertSame('"Id"', $onlinePrimaryTable->getColumn('"Id"')->getQuotedName($platform));
$this->assertTrue($onlinePrimaryTable->hasPrimaryKey());
$this->assertSame(array('"Id"'), $onlinePrimaryTable->getPrimaryKey()->getQuotedColumns($platform));
$this->assertTrue($onlinePrimaryTable->hasColumn('select'));
$this->assertSame('"select"', $onlinePrimaryTable->getColumn('select')->getQuotedName($platform));
$this->assertTrue($onlinePrimaryTable->hasColumn('foo'));
$this->assertSame('FOO', $onlinePrimaryTable->getColumn('foo')->getQuotedName($platform));
$this->assertTrue($onlinePrimaryTable->hasColumn('BAR'));
$this->assertSame('BAR', $onlinePrimaryTable->getColumn('BAR')->getQuotedName($platform));
$this->assertTrue($onlinePrimaryTable->hasColumn('"BAZ"'));
$this->assertSame('BAZ', $onlinePrimaryTable->getColumn('"BAZ"')->getQuotedName($platform));
$this->assertTrue($onlinePrimaryTable->hasIndex('from'));
$this->assertTrue($onlinePrimaryTable->getIndex('from')->hasColumnAtPosition('"select"'));
$this->assertSame(array('"select"'), $onlinePrimaryTable->getIndex('from')->getQuotedColumns($platform));
$this->assertTrue($onlinePrimaryTable->hasIndex('foo_index'));
$this->assertTrue($onlinePrimaryTable->getIndex('foo_index')->hasColumnAtPosition('foo'));
$this->assertSame(array('FOO'), $onlinePrimaryTable->getIndex('foo_index')->getQuotedColumns($platform));
$this->assertTrue($onlinePrimaryTable->hasIndex('BAR_INDEX'));
$this->assertTrue($onlinePrimaryTable->getIndex('BAR_INDEX')->hasColumnAtPosition('BAR'));
$this->assertSame(array('BAR'), $onlinePrimaryTable->getIndex('BAR_INDEX')->getQuotedColumns($platform));
$this->assertTrue($onlinePrimaryTable->hasIndex('BAZ_INDEX'));
$this->assertTrue($onlinePrimaryTable->getIndex('BAZ_INDEX')->hasColumnAtPosition('"BAZ"'));
$this->assertSame(array('BAZ'), $onlinePrimaryTable->getIndex('BAZ_INDEX')->getQuotedColumns($platform));
// Foreign table assertions
$this->assertTrue($onlineForeignTable->hasColumn('id'));
$this->assertSame('ID', $onlineForeignTable->getColumn('id')->getQuotedName($platform));
$this->assertTrue($onlineForeignTable->hasPrimaryKey());
$this->assertSame(array('ID'), $onlineForeignTable->getPrimaryKey()->getQuotedColumns($platform));
$this->assertTrue($onlineForeignTable->hasColumn('"Fk"'));
$this->assertSame('"Fk"', $onlineForeignTable->getColumn('"Fk"')->getQuotedName($platform));
$this->assertTrue($onlineForeignTable->hasIndex('"Fk_index"'));
$this->assertTrue($onlineForeignTable->getIndex('"Fk_index"')->hasColumnAtPosition('"Fk"'));
$this->assertSame(array('"Fk"'), $onlineForeignTable->getIndex('"Fk_index"')->getQuotedColumns($platform));
$this->assertTrue($onlineForeignTable->hasForeignKey('"Primary_Table_Fk"'));
$this->assertSame($primaryTableName, $onlineForeignTable->getForeignKey('"Primary_Table_Fk"')->getQuotedForeignTableName($platform));
$this->assertSame(array('"Fk"'), $onlineForeignTable->getForeignKey('"Primary_Table_Fk"')->getQuotedLocalColumns($platform));
$this->assertSame(array('"Id"'), $onlineForeignTable->getForeignKey('"Primary_Table_Fk"')->getQuotedForeignColumns($platform));
}
public function testListQuotedTable()
{
$offlineTable = new Schema\Table('user');
$offlineTable->addColumn('id', 'integer');
$offlineTable->addColumn('username', 'string', array('unique' => true));
$offlineTable->addColumn('fk', 'integer');
$offlineTable->setPrimaryKey(array('id'));
$offlineTable->addForeignKeyConstraint($offlineTable, array('fk'), array('id'));
$this->_sm->dropAndCreateTable($offlineTable);
$onlineTable = $this->_sm->listTableDetails('"user"');
$comparator = new Schema\Comparator();
$this->assertFalse($comparator->diffTable($offlineTable, $onlineTable));
}
public function testDoesNotPropagateForeignKeyAlterationForNonSupportingEngines()
{
$table = new Table("foreign_table");
$table->addColumn('id', 'integer');
$table->addColumn('fk_id', 'integer');
$table->addForeignKeyConstraint('foreign_table', array('fk_id'), array('id'));
$table->setPrimaryKey(array('id'));
$table->addOption('engine', 'MyISAM');
$addedForeignKeys = array(new ForeignKeyConstraint(array('fk_id'), 'foo', array('id'), 'fk_add'));
$changedForeignKeys = array(new ForeignKeyConstraint(array('fk_id'), 'bar', array('id'), 'fk_change'));
$removedForeignKeys = array(new ForeignKeyConstraint(array('fk_id'), 'baz', array('id'), 'fk_remove'));
$tableDiff = new TableDiff('foreign_table');
$tableDiff->fromTable = $table;
$tableDiff->addedForeignKeys = $addedForeignKeys;
$tableDiff->changedForeignKeys = $changedForeignKeys;
$tableDiff->removedForeignKeys = $removedForeignKeys;
$this->assertEmpty($this->_platform->getAlterTableSQL($tableDiff));
$table->addOption('engine', 'InnoDB');
$tableDiff = new TableDiff('foreign_table');
$tableDiff->fromTable = $table;
$tableDiff->addedForeignKeys = $addedForeignKeys;
$tableDiff->changedForeignKeys = $changedForeignKeys;
$tableDiff->removedForeignKeys = $removedForeignKeys;
$this->assertSame(array('ALTER TABLE foreign_table DROP FOREIGN KEY fk_remove', 'ALTER TABLE foreign_table DROP FOREIGN KEY fk_change', 'ALTER TABLE foreign_table ADD CONSTRAINT fk_add FOREIGN KEY (fk_id) REFERENCES foo (id)', 'ALTER TABLE foreign_table ADD CONSTRAINT fk_change FOREIGN KEY (fk_id) REFERENCES bar (id)'), $this->_platform->getAlterTableSQL($tableDiff));
}
/**
* @group DBAL-91
*/
public function testAddForeignKeyWithQuotedColumnsAndTable()
{
$table = new Table("test");
$table->addColumn('"foo"', 'integer');
$table->addColumn('bar', 'integer');
$table->addForeignKeyConstraint('"boing"', array('"foo"', '"bar"'), array("id"));
}
/**
* @group DDC-887
*/
public function testUpdateSchemaWithForeignKeyRenaming()
{
if (!$this->_sm->getDatabasePlatform()->supportsForeignKeyConstraints()) {
$this->markTestSkipped('This test is only supported on platforms that have foreign keys.');
}
$table = new \Doctrine\DBAL\Schema\Table('test_fk_base');
$table->addColumn('id', 'integer');
$table->setPrimaryKey(array('id'));
$tableFK = new \Doctrine\DBAL\Schema\Table('test_fk_rename');
$tableFK->setSchemaConfig($this->_sm->createSchemaConfig());
$tableFK->addColumn('id', 'integer');
$tableFK->addColumn('fk_id', 'integer');
$tableFK->setPrimaryKey(array('id'));
$tableFK->addIndex(array('fk_id'), 'fk_idx');
$tableFK->addForeignKeyConstraint('test_fk_base', array('fk_id'), array('id'));
$this->_sm->createTable($table);
$this->_sm->createTable($tableFK);
$tableFKNew = new \Doctrine\DBAL\Schema\Table('test_fk_rename');
$tableFKNew->setSchemaConfig($this->_sm->createSchemaConfig());
$tableFKNew->addColumn('id', 'integer');
$tableFKNew->addColumn('rename_fk_id', 'integer');
$tableFKNew->setPrimaryKey(array('id'));
$tableFKNew->addIndex(array('rename_fk_id'), 'fk_idx');
$tableFKNew->addForeignKeyConstraint('test_fk_base', array('rename_fk_id'), array('id'));
$c = new \Doctrine\DBAL\Schema\Comparator();
$tableDiff = $c->diffTable($tableFK, $tableFKNew);
$this->_sm->alterTable($tableDiff);
}
public function testMovedForeignKeyForeignTable()
{
$tableForeign = new Table("bar");
$tableForeign->addColumn('id', 'integer');
$tableForeign2 = new Table("bar2");
$tableForeign2->addColumn('id', 'integer');
$table1 = new Table("foo");
$table1->addColumn('fk', 'integer');
$table1->addForeignKeyConstraint($tableForeign, array('fk'), array('id'));
$table2 = new Table("foo");
$table2->addColumn('fk', 'integer');
$table2->addForeignKeyConstraint($tableForeign2, array('fk'), array('id'));
$c = new Comparator();
$tableDiff = $c->diffTable($table1, $table2);
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\TableDiff', $tableDiff);
$this->assertEquals(1, count($tableDiff->changedForeignKeys));
}
/**
* @dataProvider getNormalizesAssetNames
* @group DBAL-831
*/
public function testNormalizesColumnNames($assetName)
{
$table = new Table('test');
$table->addColumn($assetName, 'integer');
$table->addIndex(array($assetName), $assetName);
$table->addForeignKeyConstraint('test', array($assetName), array($assetName), array(), $assetName);
$this->assertTrue($table->hasColumn($assetName));
$this->assertTrue($table->hasColumn('foo'));
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\Column', $table->getColumn($assetName));
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\Column', $table->getColumn('foo'));
$this->assertTrue($table->hasIndex($assetName));
$this->assertTrue($table->hasIndex('foo'));
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\Index', $table->getIndex($assetName));
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\Index', $table->getIndex('foo'));
$this->assertTrue($table->hasForeignKey($assetName));
$this->assertTrue($table->hasForeignKey('foo'));
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\ForeignKeyConstraint', $table->getForeignKey($assetName));
$this->assertInstanceOf('Doctrine\\DBAL\\Schema\\ForeignKeyConstraint', $table->getForeignKey('foo'));
$table->renameIndex($assetName, $assetName);
$this->assertTrue($table->hasIndex($assetName));
$this->assertTrue($table->hasIndex('foo'));
$table->renameIndex($assetName, 'foo');
$this->assertTrue($table->hasIndex($assetName));
$this->assertTrue($table->hasIndex('foo'));
$table->renameIndex('foo', $assetName);
$this->assertTrue($table->hasIndex($assetName));
$this->assertTrue($table->hasIndex('foo'));
$table->renameIndex($assetName, 'bar');
$this->assertFalse($table->hasIndex($assetName));
$this->assertFalse($table->hasIndex('foo'));
$this->assertTrue($table->hasIndex('bar'));
$table->renameIndex('bar', $assetName);
$table->dropColumn($assetName);
$table->dropIndex($assetName);
$table->removeForeignKey($assetName);
$this->assertFalse($table->hasColumn($assetName));
$this->assertFalse($table->hasColumn('foo'));
$this->assertFalse($table->hasIndex($assetName));
$this->assertFalse($table->hasIndex('foo'));
$this->assertFalse($table->hasForeignKey($assetName));
$this->assertFalse($table->hasForeignKey('foo'));
}