protected function addClosureColumn($name, Table $ct_table, Column $column) { $table = $this->getTable(); $id_fieldname = $column->getName(); $domain = $column->getDomain(); if (!$ct_table->hasColumn($name)) { $column = new Column($name); $column->setDomain($domain); $column->setPrimaryKey(true); $ct_table->addColumn($column); } else { $column = $ct_table->getColumn($name); } $ct_tablename_normalized = str_replace('_', '', $ct_table->getName()); $fk_name = $ct_tablename_normalized . '_' . $name . '_fk'; if (!$ct_table->getColumnForeignKeys($name)) { $column_fk = new ForeignKey($fk_name); $column_fk->addReference($name, $table->getColumn($id_fieldname)->getName()); $column_fk->setForeignTableCommonName($table->getName()); $column_fk->setOnUpdate('cascade'); $column_fk->setOnDelete('restrict'); $ct_table->addForeignKey($column_fk); } $column_idx_name = $fk_name . '_idx'; if (!$ct_table->hasIndex($column_idx_name)) { $column_idx = new Index($column_idx_name); $column_idx->addColumn(['name' => $column->getName()]); $ct_table->addIndex($column_idx); } }
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); }
/** * Adds all columns, indexes, constraints and additional tables. */ public function modifyTable() { $table = $this->getTable(); $tableName = $table->getName(); $foreignTableName = $this->getForeignTable(); // enable reload on insert to force the model to load the trigger generated id(s) $table->setReloadOnInsert(true); $foreignIdColumnName = $foreignTableName . '_id'; $compositeKeyColumnName = $foreignTableName . '_' . $tableName . '_id'; if ($table->hasBehavior('concrete_inheritance')) { // we're a child in a concrete inheritance $parentTableName = $table->getBehavior('concrete_inheritance')->getParameter('extends'); $parentTable = $table->getDatabase()->getTable($parentTableName); if ($parentTable->hasBehavior('\\' . __CLASS__)) { //we're a child of a concrete inheritance structure, so we're going to skip this //round here because this behavior has also been attached by the parent table. return; } } if ($table->hasColumn($foreignIdColumnName)) { $foreignIdColumn = $table->getColumn($foreignIdColumnName); } else { $foreignIdColumn = $table->addColumn(array('name' => $foreignIdColumnName, 'type' => 'integer', 'required' => true)); $compositeKeyForeignKeyName = $tableName . '_FK_' . $foreignIdColumnName; $foreignKey = new ForeignKey($compositeKeyForeignKeyName); $foreignKey->addReference($foreignIdColumnName, 'id'); $foreignKey->setForeignTableCommonName($foreignTableName); $foreignKey->setOnUpdate(ForeignKey::CASCADE); $foreignKey->setOnDelete(ForeignKey::CASCADE); $table->addForeignKey($foreignKey); } if ($table->hasColumn($compositeKeyColumnName)) { $compositeKeyColumn = $table->getColumn($compositeKeyColumnName); } else { $compositeKeyColumn = $table->addColumn(array('name' => $compositeKeyColumnName, 'type' => 'integer', 'required' => false)); } $index = new Unique($tableName . '_UQ_' . $foreignIdColumnName . '_' . $compositeKeyColumnName); $index->addColumn($foreignIdColumn); $index->addColumn($compositeKeyColumn); $table->addUnique($index); $database = $table->getDatabase(); $sequenceTableName = sprintf('%s_sequence', $foreignTableName); if (!$database->hasTable($sequenceTableName)) { $sequenceTable = $database->addTable(array('name' => $sequenceTableName, 'package' => $table->getPackage(), 'schema' => $table->getSchema(), 'namespace' => $table->getNamespace() ? '\\' . $table->getNamespace() : null, 'skipSql' => $table->isSkipSql())); $sequenceTable->addColumn(array('name' => 'table_name', 'type' => 'varchar', 'size' => 32, 'required' => true, 'primaryKey' => true)); $sequenceTable->addColumn(array('name' => $foreignIdColumnName, 'type' => 'integer', 'required' => true, 'primaryKey' => true)); $sequenceTable->addColumn(array('name' => $foreignTableName . '_max_sequence_id', 'type' => 'integer', 'required' => false, 'default' => null)); } }
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); }
/** * 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])); } } }
public function testToString() { $tableA = new Table('A'); $tableB = new Table('B'); $diff = new TableDiff($tableA, $tableB); $diff->addAddedColumn('id', new Column('id', 'integer')); $diff->addRemovedColumn('category_id', new Column('category_id', 'integer')); $colFoo = new Column('foo', 'integer'); $colBar = new Column('bar', 'integer'); $tableA->addColumn($colFoo); $tableA->addColumn($colBar); $diff->addRenamedColumn($colFoo, $colBar); $columnDiff = new ColumnDiff($colFoo, $colBar); $diff->addModifiedColumn('foo', $columnDiff); $fk = new ForeignKey('category'); $fk->setTable($tableA); $fk->setForeignTableCommonName('B'); $fk->addReference('category_id', 'id'); $fkChanged = clone $fk; $fkChanged->setForeignTableCommonName('C'); $fkChanged->addReference('bla', 'id2'); $fkChanged->setOnDelete('cascade'); $fkChanged->setOnUpdate('cascade'); $diff->addAddedFk('category', $fk); $diff->addModifiedFk('category', $fk, $fkChanged); $diff->addRemovedFk('category', $fk); $index = new Index('test_index'); $index->setTable($tableA); $index->setColumns([$colFoo]); $indexChanged = clone $index; $indexChanged->setColumns([$colBar]); $diff->addAddedIndex('test_index', $index); $diff->addModifiedIndex('test_index', $index, $indexChanged); $diff->addRemovedIndex('test_index', $index); $string = (string) $diff; $expected = ' A: addedColumns: - id removedColumns: - category_id modifiedColumns: A.FOO: modifiedProperties: renamedColumns: foo: bar addedIndices: - test_index removedIndices: - test_index modifiedIndices: - test_index addedFks: - category removedFks: - category modifiedFks: category: localColumns: from ["category_id"] to ["category_id","bla"] foreignColumns: from ["id"] to ["id","id2"] onUpdate: from to CASCADE onDelete: from to CASCADE '; $this->assertEquals($expected, $string); }
private function addSnapshotTable() { $table = $this->getTable(); $primaryKeyColumn = $table->getFirstPrimaryKeyColumn(); $database = $table->getDatabase(); $snapshotTableName = $this->getParameter(self::PARAMETER_SNAPSHOT_TABLE) ?: $this->getDefaultSnapshotTableName(); if ($database->hasTable($snapshotTableName)) { $this->snapshotTable = $database->getTable($snapshotTableName); return; } $snapshotTable = $database->addTable(['name' => $snapshotTableName, 'phpName' => $this->getParameter(self::PARAMETER_SNAPSHOT_PHPNAME), 'package' => $table->getPackage(), 'schema' => $table->getSchema(), 'namespace' => $table->getNamespace() ? '\\' . $table->getNamespace() : null]); $addSnapshotAt = true; $hasTimestampableBehavior = 0 < count(array_filter($database->getBehaviors(), function (Behavior $behavior) { return 'timestampable' === $behavior->getName(); })); if ($hasTimestampableBehavior) { $addSnapshotAt = false; $timestampableBehavior = clone $database->getBehavior('timestampable'); $timestampableBehavior->setParameters(array_merge($timestampableBehavior->getParameters(), ['create_column' => $this->getParameter(self::PARAMETER_SNAPSHOT_AT_COLUMN), 'disable_updated_at' => 'true'])); $snapshotTable->addBehavior($timestampableBehavior); } $snapshotTable->isSnapshotTable = true; $idColumn = $snapshotTable->addColumn(['name' => 'id', 'type' => 'INTEGER']); $idColumn->setAutoIncrement(true); $idColumn->setPrimaryKey(true); $idColumn->setNotNull(true); $columns = $table->getColumns(); foreach ($columns as $column) { if ($column->isPrimaryKey()) { continue; } $columnInSnapshotTable = clone $column; $columnInSnapshotTable->setNotNull(false); if ($columnInSnapshotTable->hasReferrers()) { $columnInSnapshotTable->clearReferrers(); } if ($columnInSnapshotTable->isAutoincrement()) { $columnInSnapshotTable->setAutoIncrement(false); } $snapshotTable->addColumn($columnInSnapshotTable); } $foreignKeyColumn = $snapshotTable->addColumn(['name' => $this->getParameter(self::PARAMETER_REFERENCE_COLUMN), 'type' => $primaryKeyColumn->getType(), 'size' => $primaryKeyColumn->getSize()]); $index = new Index(); $index->setName($this->getParameter(self::PARAMETER_REFERENCE_COLUMN)); if ($primaryKeyColumn->getSize()) { $index->addColumn(['name' => $this->getParameter(self::PARAMETER_REFERENCE_COLUMN), 'size' => $primaryKeyColumn->getSize()]); } else { $index->addColumn(['name' => $this->getParameter(self::PARAMETER_REFERENCE_COLUMN)]); } $snapshotTable->addIndex($index); $foreignKey = new ForeignKey(); $foreignKey->setName(vsprintf('fk_%s_%s', [$snapshotTable->getOriginCommonName(), $this->getParameter(self::PARAMETER_REFERENCE_COLUMN)])); $foreignKey->setOnUpdate('CASCADE'); $foreignKey->setOnDelete('SET NULL'); $foreignKey->setForeignTableCommonName($this->getTable()->getCommonName()); $foreignKey->addReference($foreignKeyColumn, $primaryKeyColumn); $snapshotTable->addForeignKey($foreignKey); if ($this->getParameter(self::PARAMETER_LOG_SNAPSHOT_AT) == 'true' && $addSnapshotAt) { $snapshotTable->addColumn(['name' => $this->getParameter(self::PARAMETER_SNAPSHOT_AT_COLUMN), 'type' => 'TIMESTAMP']); } $indices = $table->getIndices(); foreach ($indices as $index) { $copiedIndex = clone $index; $snapshotTable->addIndex($copiedIndex); } // copy unique indices to indices // see https://github.com/propelorm/Propel/issues/175 for details $unices = $table->getUnices(); foreach ($unices as $unique) { $index = new Index(); $index->setName($unique->getName()); $columns = $unique->getColumns(); foreach ($columns as $columnName) { if ($size = $unique->getColumnSize($columnName)) { $index->addColumn(['name' => $columnName, 'size' => $size]); } else { $index->addColumn(['name' => $columnName]); } } $snapshotTable->addIndex($index); } $behaviors = $database->getBehaviors(); foreach ($behaviors as $behavior) { $behavior->modifyDatabase(); } $this->snapshotTable = $snapshotTable; }
public function providerForTestGetForeignKeysDDL() { $db = new Database(); $db->setIdentifierQuoting(true); $table1 = new Table('foo'); $db->addTable($table1); $column1 = new Column('bar_id'); $column1->getDomain()->copy(new Domain('FOOTYPE')); $table1->addColumn($column1); $table2 = new Table('bar'); $db->addTable($table2); $column2 = new Column('id'); $column2->getDomain()->copy(new Domain('BARTYPE')); $table2->addColumn($column2); $fk = new ForeignKey('foo_bar_fk'); $fk->setForeignTableCommonName('bar'); $fk->addReference($column1, $column2); $fk->setOnDelete('CASCADE'); $table1->addForeignKey($fk); $column3 = new Column('baz_id'); $column3->getDomain()->copy(new Domain('BAZTYPE')); $table1->addColumn($column3); $table3 = new Table('baz'); $db->addTable($table3); $column4 = new Column('id'); $column4->getDomain()->copy(new Domain('BAZTYPE')); $table3->addColumn($column4); $fk = new ForeignKey('foo_baz_fk'); $fk->setForeignTableCommonName('baz'); $fk->addReference($column3, $column4); $fk->setOnDelete('SETNULL'); $table1->addForeignKey($fk); return array(array($table1)); }
/** * 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("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); } }
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 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; } // we need the reference earlier to build the FK name in Table class to prevent adding FK twice $fk->addReference($row['from'], $row['to']); $fk->setForeignTableCommonName($foreignTable->getCommonName()); $table->addForeignKey($fk); $fk->setForeignTableCommonName($foreignTable->getCommonName()); if ($table->guessSchemaName() != $foreignTable->guessSchemaName()) { $fk->setForeignSchemaName($foreignTable->guessSchemaName()); } $lastId = $row['id']; } else { $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 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 testGetInverseForeignKey() { $database = $this->getDatabaseMock('bookstore'); $platform = $this->getPlatformMock(true); $foreignTable = $this->getTableMock('authors'); $localTable = $this->getTableMock('books', ['platform' => $platform, 'database' => $database]); $database->expects($this->any())->method('getTable')->with($this->equalTo('bookstore.authors'))->will($this->returnValue($foreignTable)); $inversedFk = new ForeignKey(); $inversedFk->addReference('id', 'author_id'); $inversedFk->setTable($localTable); $inversedFk->setForeignSchemaName('bookstore'); $inversedFk->setForeignTableCommonName('authors'); $foreignTable->expects($this->any())->method('getForeignKeys')->will($this->returnValue([$inversedFk])); $fk = new ForeignKey(); $fk->setTable($localTable); $fk->addReference('author_id', 'id'); $fk->setForeignSchemaName('bookstore'); $fk->setForeignTableCommonName('authors'); $this->assertSame('authors', $fk->getForeignTableCommonName()); $this->assertSame('bookstore.authors', $fk->getForeignTableName()); $this->assertInstanceOf('Propel\\Generator\\Model\\Table', $fk->getForeignTable()); $this->assertSame($inversedFk, $fk->getInverseFK()); $this->assertTrue($fk->isMatchedByInverseFK()); }
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']); } }
$fkCategoryPost = new ForeignKey('fk_post_has_category'); $fkCategoryPost->addReference('category_id', 'id'); $fkCategoryPost->setForeignTableCommonName('blog_category'); $fkCategoryPost->setRefPhpName('Posts'); $fkCategoryPost->setPhpName('Category'); $fkCategoryPost->setDefaultJoin('Criteria::INNER_JOIN'); $fkCategoryPost->setOnDelete('SETNULL'); $fkPostTag = new ForeignKey('fk_post_has_tags'); $fkPostTag->addReference('post_id', 'id'); $fkPostTag->setForeignTableCommonName('blog_post'); $fkPostTag->setPhpName('Post'); $fkPostTag->setDefaultJoin('Criteria::LEFT_JOIN'); $fkPostTag->setOnDelete('CASCADE'); $fkTagPost = new ForeignKey('fk_tag_has_posts'); $fkTagPost->addReference('tag_id', 'id'); $fkTagPost->setForeignTableCommonName('blog_tag'); $fkTagPost->setPhpName('Tag'); $fkTagPost->setDefaultJoin('Criteria::LEFT_JOIN'); $fkTagPost->setOnDelete('CASCADE'); /* Regular Indexes */ $pageContentFulltextIdx = new Index('page_content_fulltext_idx'); $pageContentFulltextIdx->setColumns([['name' => 'content']]); $pageContentFulltextIdx->addVendorInfo(new VendorInfo('mysql', ['Index_type' => 'FULLTEXT'])); /* Unique Indexes */ $authorUsernameUnique = new Unique('author_password_unique_idx'); $authorUsernameUnique->setColumns([['name' => 'username', 'size' => '8']]); /* Behaviors */ $timestampableBehavior = new TimestampableBehavior(); $timestampableBehavior->setName('timestampable'); $sluggableBehavior = new SluggableBehavior(); $sluggableBehavior->setName('sluggable');
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) { $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. * * @param Table $table The Table model class to add FKs to */ protected function addForeignKeys(Table $table) { // local store to avoid duplicates $foreignKeys = array(); /* @var StatementInterface $stmt */ $stmt = $this->dbh->query("SELECT CONSTRAINT_NAME, DELETE_RULE, R_CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'R' AND TABLE_NAME = '" . $table->getName() . "'"); while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { // Local reference /* @var StatementInterface $stmt2 */ $stmt2 = $this->dbh->query("SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE CONSTRAINT_NAME = '" . $row['CONSTRAINT_NAME'] . "' AND TABLE_NAME = '" . $table->getName() . "'"); $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->setForeignTableCommonName($foreignReferenceInfo['TABLE_NAME']); $onDelete = 'NO ACTION' === $row['DELETE_RULE'] ? 'NONE' : $row['DELETE_RULE']; $fk->setOnDelete($onDelete); $fk->setOnUpdate($onDelete); $fk->addReference(array('local' => $localReferenceInfo['COLUMN_NAME'], 'foreign' => $foreignReferenceInfo['COLUMN_NAME'])); $table->addForeignKey($fk); $foreignKeys[$row['CONSTRAINT_NAME']] = $fk; } } }
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); }