Skip to content

Instantly share code, notes, and snippets.

@clasense4
Created April 29, 2014 14:30
Show Gist options
  • Save clasense4/11402101 to your computer and use it in GitHub Desktop.
Save clasense4/11402101 to your computer and use it in GitHub Desktop.
protected function addForeignKeys(Table $table)
{
$database = $table->getDatabase();
$stmt = $this->dbh->query("SELECT ccu1.TABLE_NAME, ccu1.COLUMN_NAME, ccu2.TABLE_NAME AS FK_TABLE_NAME, ccu2.COLUMN_NAME AS FK_COLUMN_NAME, ccu1.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1 INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1 ON tc1.CONSTRAINT_NAME = ccu1.CONSTRAINT_NAME AND
CONSTRAINT_TYPE = 'Foreign Key' INNER JOIN
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON rc1.CONSTRAINT_NAME = tc1.CONSTRAINT_NAME INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON ccu2.CONSTRAINT_NAME = rc1.UNIQUE_CONSTRAINT_NAME
WHERE (ccu1.table_name = '".$table->getName()."')");
$foreignKeys = array(); // local store to avoid duplicates
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$lcol = $this->cleanDelimitedIdentifiers($row['COLUMN_NAME']);
$ftbl = $this->cleanDelimitedIdentifiers($row['FK_TABLE_NAME']);
$fcol = $this->cleanDelimitedIdentifiers($row['FK_COLUMN_NAME']);
$name = $this->cleanDelimitedIdentifiers($row['CONSTRAINT_NAME']);
$foreignTable = $database->getTable($ftbl);
$foreignColumn = $foreignTable->getColumn($fcol);
$localColumn = $table->getColumn($lcol);
if (!isset($foreignKeys[$name])) {
$fk = new ForeignKey($name);
$fk->setForeignTableCommonName($foreignTable->getCommonName());
$fk->setForeignSchemaName($foreignTable->getSchema());
//$fk->setOnDelete($fkactions['ON DELETE']);
//$fk->setOnUpdate($fkactions['ON UPDATE']);
$table->addForeignKey($fk);
$foreignKeys[$name] = $fk;
}
$foreignKeys[$name]->addReference($localColumn, $foreignColumn);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment