protected function fixForeignKeyProblem(DataSourceHandler $handler, DatasetMetaData $dataset, $columnName, $recreateForeignKey, $sql) { // we implement the following logic because of restrictions in MySQL which are the following: // - unique key constraint cannot be deleted if there is a foreign key for the column // - to delete the unique constraint we need to alter table and delete the foreign key, the unique key and then recreate the foreign key // - the above approach does not work if name of deleted foreign key is the same as of created one // - to support it we need to re-create foreign key with different name // - Example: delete fk_a and recreate with f2_a name. Next time delete f2_a and create fk_a $foreignKeyName = $this->generateForeignKeyConstraintName($dataset, $columnName); // preparing an alternative name for the foreign key $foreignKeyName2 = $foreignKeyName; $foreignKeyName2[1] = '2'; $availableForeignKeyNames = array($foreignKeyName, $foreignKeyName2); $availableForeignKeyPrefixes = array('fk_', 'f2_'); $schemaName = $handler->getDataSourceOwner($dataset->datasourceName); // looking for existing foreign key for the column $constraintQuery = db_select('INFORMATION_SCHEMA.key_column_usage', 'c'); $constraintQuery->fields('c', array('constraint_name', 'referenced_table_name', 'referenced_column_name')); $constraintQuery->condition('c.constraint_schema', $schemaName); $constraintQuery->condition('c.table_schema', $schemaName); $constraintQuery->condition('c.table_name', $dataset->source); $constraintQuery->condition('c.column_name', $columnName); // preparing pattern to support both fk_* and f2_* $foreignKeyPattern = $foreignKeyName; $foreignKeyPattern[1] = '_'; $constraintQuery->condition('c.constraint_name', $foreignKeyPattern, 'LIKE'); $statement = $constraintQuery->execute(); $existingForeignKeyName = $referencedTableName = $referencedColumnName = NULL; foreach ($statement as $record) { if (isset($existingForeignKeyName)) { $column = $dataset->getColumn($columnName); throw new UnsupportedOperationException(t('Found several foreign key constraint for the %datasetName dataset %columnName column: [%foreignKey1, %foreignKey2]', array('%datasetName' => $dataset->publicName, '%columnName' => $column->publicName, '%foreignKey1' => $existingForeignKeyName, '%foreignKey2' => $record->constraint_name))); } $existingForeignKeyName = $record->constraint_name; $referencedTableName = $record->referenced_table_name; $referencedColumnName = $record->referenced_column_name; } $fixedSQL = ''; // removing foreign key constraint $recreatedForeignKeyPrefix = NULL; if (isset($existingForeignKeyName)) { $index = array_search($existingForeignKeyName, $availableForeignKeyNames); if ($index === FALSE) { $column = $dataset->getColumn($columnName); throw new UnsupportedOperationException(t('%foundForeignKey foreign key constraint name is not supported for the %datasetName dataset %columnName column. Supported names are %foreignKey1 and %foreignKey2', array('%foundForeignKey' => $existingForeignKeyName, '%datasetName' => $dataset->publicName, '%columnName' => $column->publicName, '%foreignKey1' => $foreignKeyName, '%foreignKey2' => $foreignKeyName2))); } $fixedSQL = $this->prepareForeignKeyDeleteStatement($handler, $dataset, $columnName, $availableForeignKeyPrefixes[$index]); $recreatedForeignKeyIndex = $index == 0 ? 1 : 0; $recreatedForeignKeyPrefix = $availableForeignKeyPrefixes[$recreatedForeignKeyIndex]; } // registering original SQL statement if (isset($sql)) { if ($fixedSQL != '') { $fixedSQL .= $this->getUpdateClauseDelimiter() . ' '; } $fixedSQL .= $sql; } // adding foreign key constraint if ($recreateForeignKey && isset($existingForeignKeyName)) { $fixedSQL .= $this->getUpdateClauseDelimiter() . ' ADD ' . $this->prepareForeignKeyCreateStatement($handler, $dataset, $columnName, $referencedTableName, $referencedColumnName, $recreatedForeignKeyPrefix); } return $fixedSQL; }
protected function assembleForeignKeyConstraints(DataSourceHandler $handler, DatasetMetaData $dataset, $indent, &$sql) { $metamodel = data_controller_get_metamodel(); foreach ($dataset->getColumns() as $column) { $columnName = $column->name; if (!isset($column->type->sourceApplicationType)) { continue; } // the column has to contain a reference to another dataset $dimensionLookupHandler = DimensionLookupFactory::getInstance()->getHandler($column->type->sourceApplicationType); list($referencedDatasetName) = $dimensionLookupHandler->adjustReferencePointColumn($metamodel, $dataset->name, $column->name); if ($dataset->name == $referencedDatasetName) { continue; } $referencedDataset = $metamodel->getDataset($referencedDatasetName); // we can create a foreign key constraint referenced to a table only $referencedDatasetSourceType = DatasetTypeHelper::detectDatasetSourceType($referencedDataset); if ($referencedDatasetSourceType != DatasetTypeHelper::DATASET_SOURCE_TYPE__TABLE) { continue; } $referencedOwner = NULL; if ($dataset->datasourceName != $referencedDataset->datasourceName) { // if we cannot join datasets we cannot create a foreign key constraint $datasourceQueryHandler = DataSourceQueryFactory::getInstance()->getHandler($handler->getDataSourceType()); if (!$datasourceQueryHandler->isJoinSupported($dataset->datasourceName, $referencedDataset->datasourceName)) { continue; } $referencedOwner = $handler->getDataSourceOwner($referencedDataset->datasourceName); } $referencedTableName = $referencedDataset->source; $referencedColumnName = $referencedDataset->getKeyColumn()->name; $sql .= ",\n{$indent}CONSTRAINT fk_{$dataset->source}_{$columnName} FOREIGN KEY ({$columnName}) REFERENCES " . (isset($referencedOwner) ? $referencedOwner . '.' : '') . "{$referencedTableName} ({$referencedColumnName})"; } }