public function parseForeignKeyReferences($createSql)
{
// This regex has the following restrictions: A column name can not contain a ")" or a " ".
$pattern = '|FOREIGN KEY[^(]*\\(([^)]+)\\)[ ]*REFERENCES[ ]*([^ ]+)[ ]+\\(([^)]+)\\)|';
$matches = array();
$count = preg_match_all($pattern, $createSql, $matches, PREG_SET_ORDER);
foreach ($matches as $match) {
$fKeyString = $match[1];
$refTable = $this->trimBackticks($match[2]);
$refKeyString = $match[3];
$fKey = array();
foreach (explode(',', $fKeyString) as $quotedKey) {
$fKey[] = $this->trimBackticks($quotedKey);
}
$refKey = array();
foreach (explode(',', $refKeyString) as $quotedKey) {
$refKey[] = $this->trimBackticks($quotedKey);
}
$dbName = $this->getDatabase()->getDatabaseName();
$schemaFk = new SchemaForeignKey();
$schemaFk->setLocalDatabaseName($dbName);
$schemaFk->setLocalTableName($this->getTableName());
$schemaFk->setLocalKeyName($fKey);
$schemaFk->setRefDatabaseName($dbName);
$schemaFk->setRefTableName($refTable);
$schemaFk->setRefKeyName($refKey);
$this->addForeignKey($schemaFk);
// echo 'Table ' . $this->getTableName() . ' has FK ' . implode(',', $fKey) . ' which points to table ' . $refTable . ' (' . implode(',', $refKey) . ')' . "\n";
}
}
/**
* Returns the existing foreign key (with same local key name) if one
* exists, otherwise returns null.
*
* @return mixed - SchemaForeignKey if found, null if not.
* @author Anthony Bush
**/
public function getForeignKey(SchemaForeignKey $foreignKeyNeedle)
{
foreach ($this->foreignKeys as $foreignKey) {
if ($foreignKey->getLocalKeyName() == $foreignKeyNeedle->getLocalKeyName()) {
return $foreignKey;
}
}
return null;
}
/**
* Scans the schema and manipulates it by detecting FKs adding them to
* tables.
*
* It uses the id_to_table_regex config setting to find columns that might
* be FKs, and uses match_table_name_prefixes to assist in locating a table
* that matches the FK column name.
*
* @return void
* @author Anthony Bush
**/
public function manipulateSchema($schema)
{
$this->schema = $schema;
// add any missed FKs that we can detect via naming A.S. conventions
foreach ($schema->getDatabases() as $localDatabaseName => $database) {
foreach ($database->getTables() as $localTableName => $table) {
// Get the per database/table setting for id_to_table_regex
$idToTableRegexes = $this->config->getIdToTableRegex($table);
// Loop through the table's columns and setup an FK for any ID matches.
foreach ($table->getColumns() as $column) {
// Skip primary keys b/c we are looking for FKs
// TODO: can you have both PK and FK? if so, we need to allow this and check for it on the other end to make sure we don't link the table to itself.
if ($column->isPrimaryKey()) {
continue;
}
// If the ID matches, scan the table suggested by the parsed value
// from the id_to_table_regex and the other config settings
$matches = array();
foreach ($idToTableRegexes as $idToTableRegex) {
if (!preg_match($idToTableRegex, $column->getColumnName(), $matches)) {
if ($this->debug) {
echo "\tit_to_table_regex '{$idToTableRegex}' did not match " . $column->getColumnName() . "\n";
}
continue;
}
if ($this->debug) {
echo "\tit_to_table_regex '{$idToTableRegex}' matched " . $column->getColumnName() . "\n";
}
if (isset($matches[1])) {
$refTable = $this->findTable($matches[1], $table);
// Add the foreign key
if (!is_null($refTable) && count($refTable->getPrimaryKey()) == 1) {
$refDatabaseName = $refTable->getDatabase()->getDatabaseName();
$fk = new SchemaForeignKey();
$fk->setLocalDatabaseName($localDatabaseName);
$fk->setLocalTableName($localTableName);
$fk->setLocalKeyName(array($column->getColumnName()));
$fk->setRefDatabaseName($refDatabaseName);
$fk->setRefTableName($refTable->getTableName());
// Downgrade the ref primary key to an array of column names (no references to the objects)
$refKeyName = array();
foreach ($refTable->getPrimaryKey() as $pkColumn) {
$refKeyName[] = $pkColumn->getColumnName();
}
$fk->setRefKeyName($refKeyName);
if ($this->verbose) {
echo 'Detected FK by name';
if ($table->hasForeignKey($fk)) {
echo ' (which is already set up)';
}
$dbName = $table->getDatabase()->getDatabaseName();
$refDbName = $refTable->getDatabase()->getDatabaseName();
echo ': ' . $dbName . '.' . $table->getTableName() . ' (' . $column->getColumnName() . ') => ' . $refDbName . '.' . $refTable->getTableName() . ' (' . implode(',', $refKeyName) . ')' . "\n";
}
$table->addForeignKey($fk);
// Stop looping through idToTableRegexes because we found a match.
break;
}
}
}
}
}
}
// (re)-link relationships
$schema->linkRelationships();
}
