public
Last active

Propel's Reverse engineering FKs fail on MSSQL issue

  • Download Gist
MssqlSchemaParser.php
PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
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);
}
 
}

Very good! Thanks! I was stucked with this issue for a while ;)

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.