/** * 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]); } } } }