protected function relateI18nTableToMainTable() { $table = $this->getTable(); $i18nTable = $this->i18nTable; $pks = $this->getTable()->getPrimaryKey(); if (count($pks) > 1) { throw new EngineException('The i18n behavior does not support tables with composite primary keys'); } foreach ($pks as $column) { if (!$i18nTable->hasColumn($column->getName())) { $column = clone $column; $column->setAutoIncrement(false); $i18nTable->addColumn($column); } } if (in_array($table->getName(), $i18nTable->getForeignTableNames())) { return; } $fk = new ForeignKey(); $fk->setForeignTableCommonName($table->getCommonName()); $fk->setForeignSchemaName($table->getSchema()); $fk->setDefaultJoin('LEFT JOIN'); $fk->setOnDelete(ForeignKey::CASCADE); $fk->setOnUpdate(ForeignKey::NONE); foreach ($pks as $column) { $fk->addReference($column->getName(), $column->getName()); } $i18nTable->addForeignKey($fk); }
protected function relateDelegateToMainTable($delegateTable, $mainTable) { $pks = $mainTable->getPrimaryKey(); foreach ($pks as $column) { $mainColumnName = $column->getName(); if (!$delegateTable->hasColumn($mainColumnName)) { $column = clone $column; $column->setAutoIncrement(false); $delegateTable->addColumn($column); } } // Add a one-to-one fk $fk = new ForeignKey(); $fk->setForeignTableCommonName($mainTable->getCommonName()); $fk->setForeignSchemaName($mainTable->getSchema()); $fk->setDefaultJoin('LEFT JOIN'); $fk->setOnDelete(ForeignKey::CASCADE); $fk->setOnUpdate(ForeignKey::NONE); foreach ($pks as $column) { $fk->addReference($column->getName(), $column->getName()); } $delegateTable->addForeignKey($fk); }
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->hasColumn($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->setForeignTableCommonName($column->getTable()->getCommonName()); $fk->setForeignSchemaName($column->getTable()->getSchema()); $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 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); } // add the Behaviors of the parent table foreach ($parentTable->getBehaviors() as $behavior) { if ($behavior->getName() == 'concrete_inheritance_parent' || $behavior->getName() == 'concrete_inheritance') { continue; } //validate behavior. If validate behavior already exists, clone only rules from parent if ('validate' === $behavior->getName() && $table->hasBehavior('validate')) { $table->getBehavior('validate')->mergeParameters($behavior->getParameters()); continue; } $copiedBehavior = clone $behavior; $copiedBehavior->setTableModified(false); $this->getTable()->addBehavior($copiedBehavior); } }
protected function addVersionTable() { $table = $this->getTable(); $database = $table->getDatabase(); $versionTableName = $this->getParameter('version_table') ? $this->getParameter('version_table') : $table->getName() . '_version'; if (!$database->hasTable($versionTableName)) { // create the version table $versionTable = $database->addTable(array('name' => $versionTableName, 'phpName' => $this->getVersionTablePhpName(), 'package' => $table->getPackage(), 'schema' => $table->getSchema(), 'namespace' => $table->getNamespace() ? '\\' . $table->getNamespace() : null, 'skipSql' => $table->isSkipSql())); $versionTable->isVersionTable = true; // every behavior adding a table should re-execute database behaviors foreach ($database->getBehaviors() as $behavior) { $behavior->modifyDatabase(); } // copy all the columns foreach ($table->getColumns() as $column) { $columnInVersionTable = clone $column; $columnInVersionTable->clearInheritanceList(); if ($columnInVersionTable->hasReferrers()) { $columnInVersionTable->clearReferrers(); } if ($columnInVersionTable->isAutoincrement()) { $columnInVersionTable->setAutoIncrement(false); } $versionTable->addColumn($columnInVersionTable); } // create the foreign key $fk = new ForeignKey(); $fk->setForeignTableCommonName($table->getCommonName()); $fk->setForeignSchemaName($table->getSchema()); $fk->setOnDelete('CASCADE'); $fk->setOnUpdate(null); $tablePKs = $table->getPrimaryKey(); foreach ($versionTable->getPrimaryKey() as $key => $column) { $fk->addReference($column, $tablePKs[$key]); } $versionTable->addForeignKey($fk); // add the version column to the primary key $versionColumn = $versionTable->getColumn($this->getParameter('version_column')); $versionColumn->setNotNull(true); $versionColumn->setPrimaryKey(true); $this->versionTable = $versionTable; } else { $this->versionTable = $database->getTable($versionTableName); } }
/** * 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])); } } }
/** * Adds a relation from logTable to origin table. * * @param Table $logTable */ protected function addForeignKey(Table $logTable) { $table = $this->getTable(); if ($table->getForeignKeysReferencingTable($table->getName())) { //if already a foreignKey exist to origin table then don't add a second. return; } // create the foreign key $fk = new ForeignKey(); $fk->setForeignTableCommonName($table->getCommonName()); $fk->setForeignSchemaName($table->getSchema()); $fk->setPhpName('Origin'); $fk->setOnDelete('CASCADE'); $fk->setOnUpdate('CASCADE'); foreach ($table->getPrimaryKey() as $column) { $fk->addReference($logTable->getColumn($column->getName()), $column); } $logTable->addForeignKey($fk); }
/** * 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]); } } } }
/** * Load foreign keys for this table. */ protected function addForeignKeys(Table $table) { $database = $table->getDatabase(); $dataFetcher = $this->dbh->query("select fk.name as CONSTRAINT_NAME, lcol.name as COLUMN_NAME, rtab.name as FK_TABLE_NAME, rcol.name as FK_COLUMN_NAME\n from sys.foreign_keys as fk \n inner join sys.foreign_key_columns ref on ref.constraint_object_id = fk.object_id\n inner join sys.columns lcol on lcol.object_id = ref.parent_object_id and lcol.column_id = ref.parent_column_id\n inner join sys.columns rcol on rcol.object_id = ref.referenced_object_id and rcol.column_id = ref.referenced_column_id\n inner join sys.tables rtab on rtab.object_id = ref.referenced_object_id\n where fk.parent_object_id = OBJECT_ID('" . $table->getName() . "')"); $dataFetcher->setStyle(\PDO::FETCH_ASSOC); $foreignKeys = []; // local store to avoid duplicates foreach ($dataFetcher as $row) { $name = $this->cleanDelimitedIdentifiers($row['CONSTRAINT_NAME']); $lcol = $this->cleanDelimitedIdentifiers($row['COLUMN_NAME']); $ftbl = $this->cleanDelimitedIdentifiers($row['FK_TABLE_NAME']); $fcol = $this->cleanDelimitedIdentifiers($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->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); } }
public function testSetForeignSchemaName() { $fk = new ForeignKey(); $fk->setForeignSchemaName('authors'); $this->assertSame('authors', $fk->getForeignSchemaName()); }
protected function addForeignKeys(Table $table) { $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(); $tableName = $row['table']; $tableSchema = ''; if (false !== ($pos = strpos($tableName, '§'))) { $tableName = substr($tableName, $pos + 2); $tableSchema = substr($tableName, 0, $pos); } $fk->setForeignTableCommonName($tableName); if ($table->getDatabase()->getSchema() != $tableSchema) { $fk->setForeignSchemaName($tableSchema); } $fk->setOnDelete($row['on_delete']); $fk->setOnUpdate($row['on_update']); $table->addForeignKey($fk); $lastId = $row['id']; } $fk->addReference($row['from'], $row['to']); } }
/** * Load foreign keys for this table. */ protected function addForeignKeys(Table $table) { $database = $table->getDatabase(); $dataFetcher = $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 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu1 INNER JOIN\n INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc1 ON tc1.CONSTRAINT_NAME = ccu1.CONSTRAINT_NAME AND\n CONSTRAINT_TYPE = 'Foreign Key' INNER JOIN\n INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON rc1.CONSTRAINT_NAME = tc1.CONSTRAINT_NAME INNER JOIN\n INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON ccu2.CONSTRAINT_NAME = rc1.UNIQUE_CONSTRAINT_NAME\n WHERE (ccu1.table_name = '" . $table->getName() . "')"); $foreignKeys = array(); // local store to avoid duplicates foreach ($dataFetcher as $row) { $lcol = $this->cleanDelimitedIdentifiers($row['COLUMN_NAME']); $ftbl = $this->cleanDelimitedIdentifiers($row['FK_TABLE_NAME']); $fcol = $this->cleanDelimitedIdentifiers($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->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); } }
protected function relateI18nTableToMainTable() { $table = $this->getTable(); $i18nTable = $this->i18nTable; $pks = $this->getTable()->getPrimaryKey(); if (count($pks) > 1) { throw new EngineException('The i18n behavior does not support tables with composite primary keys'); } $column = $pks[0]; $i18nColumn = clone $column; if ($this->getParameter('i18n_pk_column')) { // custom i18n table pk name $i18nColumn->setName($this->getParameter('i18n_pk_column')); } else { if (in_array($table->getName(), $i18nTable->getForeignTableNames())) { // custom i18n table pk name not set, but some fk already exists return; } } if (!$i18nTable->hasColumn($i18nColumn->getName())) { $i18nColumn->setAutoIncrement(false); $i18nTable->addColumn($i18nColumn); } $fk = new ForeignKey(); $fk->setForeignTableCommonName($table->getCommonName()); $fk->setForeignSchemaName($table->getSchema()); $fk->setDefaultJoin('LEFT JOIN'); $fk->setOnDelete(ForeignKey::CASCADE); $fk->setOnUpdate(ForeignKey::NONE); $fk->addReference($i18nColumn->getName(), $column->getName()); $i18nTable->addForeignKey($fk); }