private function buildForeignKeys()
{
foreach ($this->foreignKeys as $data) {
$name = $data[0];
$localColumnNames = $data[1];
$localColumns = array();
foreach ($localColumnNames as &$columnName) {
$localColumns[] = $this->columns->{$columnName};
}
$referenceTableName = $data[2];
$referenceColumnNames = $data[3];
$referenceColumns = array();
foreach ($referenceColumnNames as &$columnName) {
$column = new Column();
$column->schemaName = $columnName;
$column->table = new Table(null, null, $referenceTableName);
$referenceColumns[] = $column;
}
$foreignKey = new Database\Definition\ForeignKey($localColumns, $referenceColumns);
// ON UPDATE
if ($data[4]) {
$foreignKey->onUpdate = $data[4];
}
// ON DELETE
if ($data[5]) {
$foreignKey->onDelete = $data[5];
}
$foreignKey->setName($name);
$this->table->addForeignKey($foreignKey);
}
}
private function readForeignKeys(Table $def)
{
$res = $this->database->select()->select('tc.constraint_name, kcu.column_name')->select('ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name')->from('information_schema.table_constraints AS tc')->innerJoin('information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name')->innerJoin('information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name')->where('constraint_type = \'FOREIGN KEY\'')->where('tc.table_name = ?', $def->schemaName)->query()->fetchAll();
$fk = array();
foreach ($res as $r) {
$fk[$r['constraint_name']][$r['column_name']] = array($r['foreign_table_name'], $r['foreign_column_name']);
}
foreach ($fk as $constraintName => $constraintColumns) {
$localColumns = array();
$referenceColumns = array();
foreach ($constraintColumns as $localName => $refData) {
$localColumns[] = $def->columns->{$localName};
$column = new Column();
$column->table = new Table(null, null, $refData[0]);
$column->schemaName = $refData[1];
$referenceColumns[] = $column;
}
$foreignKey = new Database\Definition\ForeignKey($localColumns, $referenceColumns);
$foreignKey->setName($constraintName);
$def->addForeignKey($foreignKey);
}
}