/**
* Returns the foreign table name of the FK.
*
* @return string
*/
public function getForeignTableName()
{
$platform = $this->getPlatform();
if ($this->foreignSchemaName && $platform->supportsSchemas()) {
return $this->foreignSchemaName . $platform->getSchemaDelimiter() . $this->foreignTableCommonName;
}
$database = $this->getDatabase();
if ($database && ($schema = $this->parentTable->guessSchemaName()) && $platform->supportsSchemas()) {
return $schema . $platform->getSchemaDelimiter() . $this->foreignTableCommonName;
}
return $this->foreignTableCommonName;
}
protected function addForeignKeys(Table $table)
{
$database = $table->getDatabase();
$stmt = $this->dbh->query('PRAGMA foreign_key_list("' . $table->getName() . '")');
$lastId = null;
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
if ($lastId !== $row['id']) {
$fk = new ForeignKey();
$onDelete = $row['on_delete'];
if ($onDelete && 'NO ACTION' !== $onDelete) {
$fk->setOnDelete($onDelete);
}
$onUpdate = $row['on_update'];
if ($onUpdate && 'NO ACTION' !== $onUpdate) {
$fk->setOnUpdate($onUpdate);
}
$foreignTable = $database->getTable($row['table'], true);
if (!$foreignTable) {
continue;
}
$table->addForeignKey($fk);
$fk->setForeignTableCommonName($foreignTable->getCommonName());
if ($table->guessSchemaName() != $foreignTable->guessSchemaName()) {
$fk->setForeignSchemaName($foreignTable->guessSchemaName());
}
$lastId = $row['id'];
}
$fk->addReference($row['from'], $row['to']);
}
}
/**
* Load foreign keys for this table.
*/
protected function addForeignKeys(Table $table, $oid)
{
$database = $table->getDatabase();
$stmt = $this->dbh->prepare("SELECT\n conname,\n confupdtype,\n confdeltype,\n CASE nl.nspname WHEN 'public' THEN cl.relname ELSE nl.nspname||'.'||cl.relname END as fktab,\n array_agg(DISTINCT a2.attname) AS fkcols,\n CASE nr.nspname WHEN 'public' THEN cr.relname ELSE nr.nspname||'.'||cr.relname END as reftab,\n array_agg(DISTINCT a1.attname) AS refcols\n FROM pg_constraint ct\n JOIN pg_class cl ON cl.oid=conrelid\n JOIN pg_class cr ON cr.oid=confrelid\n JOIN pg_namespace nl ON nl.oid = cl.relnamespace\n JOIN pg_namespace nr ON nr.oid = cr.relnamespace\n LEFT JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = ct.confrelid\n LEFT JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = ct.conrelid\n WHERE\n contype='f'\n AND conrelid = ?\n AND a2.attnum = ANY (ct.conkey)\n AND a1.attnum = ANY (ct.confkey)\n GROUP BY conname, confupdtype, confdeltype, fktab, reftab\n ORDER BY conname");
$stmt->bindValue(1, $oid);
$stmt->execute();
$foreignKeys = array();
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$name = $row['conname'];
$localTable = $row['fktab'];
$localColumns = explode(',', trim($row['fkcols'], '{}'));
$foreignTableName = $row['reftab'];
$foreignColumns = explode(',', trim($row['refcols'], '{}'));
// On Update
switch ($row['confupdtype']) {
case 'c':
$onupdate = ForeignKey::CASCADE;
break;
case 'd':
$onupdate = ForeignKey::SETDEFAULT;
break;
case 'n':
$onupdate = ForeignKey::SETNULL;
break;
case 'r':
$onupdate = ForeignKey::RESTRICT;
break;
default:
case 'a':
// NOACTION is the postgresql default
$onupdate = ForeignKey::NONE;
break;
}
// On Delete
switch ($row['confdeltype']) {
case 'c':
$ondelete = ForeignKey::CASCADE;
break;
case 'd':
$ondelete = ForeignKey::SETDEFAULT;
break;
case 'n':
$ondelete = ForeignKey::SETNULL;
break;
case 'r':
$ondelete = ForeignKey::RESTRICT;
break;
default:
case 'a':
// NOACTION is the postgresql default
$ondelete = ForeignKey::NONE;
break;
}
$foreignTable = $database->getTable($foreignTableName);
$localTable = $database->getTable($localTable);
if (!$foreignTable) {
continue;
}
if (!isset($foreignKeys[$name])) {
$fk = new ForeignKey($name);
$fk->setForeignTableCommonName($foreignTable->getCommonName());
if ($table->guessSchemaName() != $foreignTable->guessSchemaName()) {
$fk->setForeignSchemaName($foreignTable->getSchema());
}
$fk->setOnDelete($ondelete);
$fk->setOnUpdate($onupdate);
$table->addForeignKey($fk);
$foreignKeys[$name] = $fk;
}
$max = count($localColumns);
for ($i = 0; $i < $max; $i++) {
$foreignKeys[$name]->addReference($localTable->getColumn($localColumns[$i]), $foreignTable->getColumn($foreignColumns[$i]));
}
}
}
/**
* Load foreign keys for this table.
*/
protected function addForeignKeys(Table $table)
{
$database = $table->getDatabase();
$dataFetcher = $this->dbh->query(sprintf('SHOW CREATE TABLE %s', $this->getPlatform()->doQuoting($table->getName())));
$row = $dataFetcher->fetch();
$foreignKeys = array();
// local store to avoid duplicates
// Get the information on all the foreign keys
$pattern = '/CONSTRAINT `([^`]+)` FOREIGN KEY \\((.+)\\) REFERENCES `([^\\s]+)` \\((.+)\\)(.*)/';
if (preg_match_all($pattern, $row[1], $matches)) {
$tmpArray = array_keys($matches[0]);
foreach ($tmpArray as $curKey) {
$name = $matches[1][$curKey];
$rawlcol = $matches[2][$curKey];
$ftbl = str_replace('`', '', $matches[3][$curKey]);
$rawfcol = $matches[4][$curKey];
$fkey = $matches[5][$curKey];
$lcols = array();
foreach (preg_split('/`, `/', $rawlcol) as $piece) {
$lcols[] = trim($piece, '` ');
}
$fcols = array();
foreach (preg_split('/`, `/', $rawfcol) as $piece) {
$fcols[] = trim($piece, '` ');
}
// typical for mysql is RESTRICT
$fkactions = array('ON DELETE' => ForeignKey::RESTRICT, 'ON UPDATE' => ForeignKey::RESTRICT);
if ($fkey) {
// split foreign key information -> search for ON DELETE and afterwords for ON UPDATE action
foreach (array_keys($fkactions) as $fkaction) {
$result = null;
preg_match('/' . $fkaction . ' (' . ForeignKey::CASCADE . '|' . ForeignKey::SETNULL . ')/', $fkey, $result);
if ($result && is_array($result) && isset($result[1])) {
$fkactions[$fkaction] = $result[1];
}
}
}
// restrict is the default
foreach ($fkactions as $key => $action) {
if (ForeignKey::RESTRICT === $action) {
$fkactions[$key] = null;
}
}
$localColumns = array();
$foreignColumns = array();
if ($table->guessSchemaName() != $database->getSchema() && false == strpos($ftbl, $database->getPlatform()->getSchemaDelimiter())) {
$ftbl = $table->guessSchemaName() . $database->getPlatform()->getSchemaDelimiter() . $ftbl;
}
$foreignTable = $database->getTable($ftbl, true);
if (!$foreignTable) {
continue;
}
foreach ($fcols as $fcol) {
$foreignColumns[] = $foreignTable->getColumn($fcol);
}
foreach ($lcols as $lcol) {
$localColumns[] = $table->getColumn($lcol);
}
if (!isset($foreignKeys[$name])) {
$fk = new ForeignKey($name);
$fk->setForeignTableCommonName($foreignTable->getCommonName());
if ($table->guessSchemaName() != $foreignTable->guessSchemaName()) {
$fk->setForeignSchemaName($foreignTable->guessSchemaName());
}
$fk->setOnDelete($fkactions['ON DELETE']);
$fk->setOnUpdate($fkactions['ON UPDATE']);
$table->addForeignKey($fk);
$foreignKeys[$name] = $fk;
}
$max = count($localColumns);
for ($i = 0; $i < $max; $i++) {
$foreignKeys[$name]->addReference($localColumns[$i], $foreignColumns[$i]);
}
}
}
}
