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})";
}
}
