/** * Load foreign keys for this table. */ protected function addForeignKeys(Table $table, $oid, $version) { $database = $table->getDatabase(); $stmt = $this->dbh->prepare("SELECT\n\t\t\t\t\t\t\t\t conname,\n\t\t\t\t\t\t\t\t confupdtype,\n\t\t\t\t\t\t\t\t confdeltype,\n\t\t\t\t\t\t\t\t CASE nl.nspname WHEN 'public' THEN cl.relname ELSE nl.nspname||'.'||cl.relname END as fktab,\n\t\t\t\t\t\t\t\t a2.attname as fkcol,\n\t\t\t\t\t\t\t\t CASE nr.nspname WHEN 'public' THEN cr.relname ELSE nr.nspname||'.'||cr.relname END as reftab,\n\t\t\t\t\t\t\t\t a1.attname as refcol\n\t\t\t\t\t\t\t\t FROM pg_constraint ct\n\t\t\t\t\t\t\t\t JOIN pg_class cl ON cl.oid=conrelid\n\t\t\t\t\t\t\t\t JOIN pg_class cr ON cr.oid=confrelid\n\t\t\t\t\t\t\t\t JOIN pg_namespace nl ON nl.oid = cl.relnamespace\n\t\t\t\t\t\t\t\t JOIN pg_namespace nr ON nr.oid = cr.relnamespace\n\t\t\t\t\t\t\t\t LEFT JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = ct.confrelid\n\t\t\t\t\t\t\t\t LEFT JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = ct.conrelid\n\t\t\t\t\t\t\t\t WHERE\n\t\t\t\t\t\t\t\t contype='f'\n\t\t\t\t\t\t\t\t AND conrelid = ?\n\t\t\t\t\t\t\t\t AND a2.attnum = ct.conkey[1]\n\t\t\t\t\t\t\t\t AND a1.attnum = ct.confkey[1]\n\t\t\t\t\t\t\t\t ORDER BY conname"); $stmt->bindValue(1, $oid); $stmt->execute(); $foreignKeys = array(); // local store to avoid duplicates while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $name = $row['conname']; $local_table = $row['fktab']; $local_column = $row['fkcol']; $foreign_table = $row['reftab']; $foreign_column = $row['refcol']; // 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($foreign_table); $foreignColumn = $foreignTable->getColumn($foreign_column); $localTable = $database->getTable($local_table); $localColumn = $localTable->getColumn($local_column); if (!isset($foreignKeys[$name])) { $fk = new ForeignKey($name); $fk->setForeignTableName($foreignTable->getName()); $fk->setOnDelete($ondelete); $fk->setOnUpdate($onupdate); $table->addForeignKey($fk); $foreignKeys[$name] = $fk; } $foreignKeys[$name]->addReference($localColumn, $foreignColumn); } }
/** * Load foreign keys for this table. */ protected function addForeignKeys(Table $table) { $database = $table->getDatabase(); $stmt = $this->dbh->query("SHOW CREATE TABLE `" . $table->getName() . "`"); $row = $stmt->fetch(PDO::FETCH_NUM); $foreignKeys = array(); // local store to avoid duplicates // Get the information on all the foreign keys $regEx = '/CONSTRAINT `([^`]+)` FOREIGN KEY \\((.+)\\) REFERENCES `([^`]*)` \\((.+)\\)(.*)/'; if (preg_match_all($regEx, $row[1], $matches)) { $tmpArray = array_keys($matches[0]); foreach ($tmpArray as $curKey) { $name = $matches[1][$curKey]; $rawlcol = $matches[2][$curKey]; $ftbl = $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]; } } } $localColumns = array(); $foreignColumns = array(); $foreignTable = $database->getTable($ftbl); 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->setForeignTableName($foreignTable->getName()); $fk->setOnDelete($fkactions['ON DELETE']); $fk->setOnUpdate($fkactions['ON UPDATE']); $table->addForeignKey($fk); $foreignKeys[$name] = $fk; } for ($i = 0; $i < count($localColumns); $i++) { $foreignKeys[$name]->addReference($localColumns[$i], $foreignColumns[$i]); } } } }
public function modifyTable() { $table = $this->getTable(); $parentTable = $this->getParentTable(); if ($this->isCopyData()) { // tell the parent table that it has a descendant if (!$parentTable->hasBehavior('concrete_inheritance_parent')) { $parentBehavior = new ConcreteInheritanceParentBehavior(); $parentBehavior->setName('concrete_inheritance_parent'); $parentBehavior->addParameter(array('name' => 'descendant_column', 'value' => $this->getParameter('descendant_column'))); $parentTable->addBehavior($parentBehavior); // The parent table's behavior modifyTable() must be executed before this one $parentBehavior->getTableModifier()->modifyTable(); $parentBehavior->setTableModified(true); } } // Add the columns of the parent table foreach ($parentTable->getColumns() as $column) { if ($column->getName() == $this->getParameter('descendant_column')) { continue; } if ($table->containsColumn($column->getName())) { continue; } $copiedColumn = clone $column; if ($column->isAutoIncrement() && $this->isCopyData()) { $copiedColumn->setAutoIncrement(false); } $table->addColumn($copiedColumn); if ($column->isPrimaryKey() && $this->isCopyData()) { $fk = new ForeignKey(); $fk->setForeignTableName($column->getTable()->getName()); $fk->setOnDelete('CASCADE'); $fk->setOnUpdate(null); $fk->addReference($copiedColumn, $column); $fk->isParentChild = true; $table->addForeignKey($fk); } } // add the foreign keys of the parent table foreach ($parentTable->getForeignKeys() as $fk) { $copiedFk = clone $fk; $copiedFk->setName(''); $copiedFk->setRefPhpName(''); $this->getTable()->addForeignKey($copiedFk); } // add the validators of the parent table foreach ($parentTable->getValidators() as $validator) { $copiedValidator = clone $validator; $this->getTable()->addValidator($copiedValidator); } // add the indices of the parent table foreach ($parentTable->getIndices() as $index) { $copiedIndex = clone $index; $copiedIndex->setName(''); $this->getTable()->addIndex($copiedIndex); } // add the unique indices of the parent table foreach ($parentTable->getUnices() as $unique) { $copiedUnique = clone $unique; $copiedUnique->setName(''); $this->getTable()->addUnique($copiedUnique); } // give name to newly added foreign keys and indices // (this is already done for other elements of the current table) $table->doNaming(); // add the Behaviors of the parent table foreach ($parentTable->getBehaviors() as $behavior) { if ($behavior->getName() == 'concrete_inheritance_parent' || $behavior->getName() == 'concrete_inheritance') { continue; } $copiedBehavior = clone $behavior; $copiedBehavior->setTableModified(false); $this->getTable()->addBehavior($copiedBehavior); } }
/** * Load foreign keys for this table. * * @param Table $table The Table model class to add FKs to */ protected function addForeignKeys(Table $table) { // local store to avoid duplicates $foreignKeys = array(); $stmt = $this->dbh->query("SELECT CONSTRAINT_NAME, DELETE_RULE, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND TABLE_NAME = '" . $table->getName(). "'"); /* @var stmt PDOStatement */ while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { // Local reference $stmt2 = $this->dbh->query("SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = '".$row['CONSTRAINT_NAME']."' AND TABLE_NAME = '" . $table->getName(). "'"); /* @var stmt2 PDOStatement */ $localReferenceInfo = $stmt2->fetch(PDO::FETCH_ASSOC); // Foreign reference $stmt2 = $this->dbh->query("SELECT TABLE_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = '".$row['R_CONSTRAINT_NAME']."'"); $foreignReferenceInfo = $stmt2->fetch(PDO::FETCH_ASSOC); if (!isset($foreignKeys[$row["CONSTRAINT_NAME"]])) { $fk = new ForeignKey($row["CONSTRAINT_NAME"]); $fk->setForeignTableName($foreignReferenceInfo['TABLE_NAME']); $fk->setOnDelete($row["DELETE_RULE"]); $fk->setOnUpdate($row["DELETE_RULE"]); $fk->addReference(array("local" => $localReferenceInfo['COLUMN_NAME'], "foreign" => $foreignReferenceInfo['COLUMN_NAME'])); $table->addForeignKey($fk); $foreignKeys[$row["CONSTRAINT_NAME"]] = $fk; } } }
/** * Load foreign keys for this table. */ 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\n\t\t\t\t\t\t\t\t\tFROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1 INNER JOIN\n\t\t\t\t\t\t\t\t INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1 ON tc1.CONSTRAINT_NAME = ccu1.CONSTRAINT_NAME AND\n\t\t\t\t\t\t\t\t CONSTRAINT_TYPE = 'Foreign Key' INNER JOIN\n\t\t\t\t\t\t\t\t INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON rc1.CONSTRAINT_NAME = tc1.CONSTRAINT_NAME INNER JOIN\n\t\t\t\t\t\t\t\t INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON ccu2.CONSTRAINT_NAME = rc1.UNIQUE_CONSTRAINT_NAME\n\t\t\t\t\t\t\t\t\tWHERE (ccu1.table_name = '" . $table->getName() . "')"); $row = $stmt->fetch(PDO::FETCH_NUM); $foreignKeys = array(); // local store to avoid duplicates while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $lcol = $row['COLUMN_NAME']; $ftbl = $row['FK_TABLE_NAME']; $fcol = $row['FK_COLUMN_NAME']; $foreignTable = $database->getTable($ftbl); $foreignColumn = $foreignTable->getColumn($fcol); $localColumn = $table->getColumn($lcol); if (!isset($foreignKeys[$name])) { $fk = new ForeignKey($name); $fk->setForeignTableName($foreignTable->getName()); //$fk->setOnDelete($fkactions['ON DELETE']); //$fk->setOnUpdate($fkactions['ON UPDATE']); $table->addForeignKey($fk); $foreignKeys[$name] = $fk; } $foreignKeys[$name]->addReference($localColumn, $foreignColumn); } }
/** * Load foreign keys for this table. */ protected function addForeignKeys(Table $table) { $database = $table->getDatabase(); $stmt = $this->dbh->query("SELECT CONSTRAINT_NAME, DELETE_RULE, R_CONSTRAINT_NAME FROM ALL_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND TABLE_NAME = '" . $table->getName() . "'"); $foreignKeys = array(); // local store to avoid duplicates while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $name = $row['CONSTRAINT_NAME']; $stmt2 = $this->dbh->query("SELECT CON.CONSTRAINT_NAME, CON.TABLE_NAME, COL.COLUMN_NAME FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS CON WHERE COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME AND COL.CONSTRAINT_NAME = '" . $row['R_CONSTRAINT_NAME'] . "'"); $foreignKeyCols = $stmt2->fetch(PDO::FETCH_ASSOC); if (!is_array($foreignKeyCols[0])) { $foreignRows[0] = $foreignKeyCols; } else { $foreignRows = $foreignKeyCols; } $stmt2 = $this->dbh->query("SELECT CON.CONSTRAINT_NAME, CON.TABLE_NAME, COL.COLUMN_NAME FROM USER_CONS_COLUMNS COL, USER_CONSTRAINTS CON WHERE COL.CONSTRAINT_NAME = CON.CONSTRAINT_NAME AND COL.CONSTRAINT_NAME = '" . $row['CONSTRAINT_NAME'] . "'"); $localKeyCols = $stmt2->fetch(PDO::FETCH_ASSOC); if (!is_array($localKeyCols[0])) { $localRows[0] = $localKeyCols; } else { $localRows = $localKeyCols; } print_r($foreignRows); $localColumns = array(); $foreignColumns = array(); $foreignTable = $database->getTable($foreignRows[0]["TABLE_NAME"]); foreach ($foreignRows as $foreignCol) { $foreignColumns[] = $foreignTable->getColumn($foreignCol["COLUMN_NAME"]); } foreach ($localRows as $localCol) { $localColumns[] = $table->getColumn($localCol['COLUMN_NAME']); } if (!isset($foreignKeys[$name])) { $fk = new ForeignKey($name); $fk->setForeignTableName($foreignTable->getName()); $fk->setOnDelete($row["DELETE_RULE"]); $fk->setOnUpdate($row["DELETE_RULE"]); $table->addForeignKey($fk); $foreignKeys[$name] = $fk; } for ($i = 0; $i < count($localColumns); $i++) { $foreignKeys[$name]->addReference($localColumns[$i], $foreignColumns[$i]); } } }