/** * Adds extra indices for reverse foreign keys * This is required for MySQL databases, * and is called from Database::doFinalInitialization() */ public function addExtraIndices() { /** * A collection of indexed columns. The keys is the column name * (concatenated with a comma in the case of multi-col index), the value is * an array with the names of the indexes that index these columns. We use * it to determine which additional indexes must be created for foreign * keys. It could also be used to detect duplicate indexes, but this is not * implemented yet. * @var array */ $_indices = array(); $this->collectIndexedColumns('PRIMARY', $this->getPrimaryKey(), $_indices); $_tableIndices = array_merge($this->getIndices(), $this->getUnices()); foreach ($_tableIndices as $_index) { $this->collectIndexedColumns($_index->getName(), $_index->getColumns(), $_indices); } // we're determining which tables have foreign keys that point to this table, // since MySQL needs an index on any column that is referenced by another table // (yep, MySQL _is_ a PITA) $counter = 0; foreach ($this->getReferrers() as $foreignKey) { $referencedColumns = $foreignKey->getForeignColumnObjects(); $referencedColumnsHash = $this->getColumnList($referencedColumns); if (!array_key_exists($referencedColumnsHash, $_indices)) { // no matching index defined in the schema, so we have to create one $index = new Index(); $index->setName(sprintf('I_referenced_%s_%s', $foreignKey->getName(), ++$counter)); $index->setColumns($referencedColumns); $index->resetColumnSize(); $this->addIndex($index); // Add this new index to our collection, otherwise we might add it again (bug #725) $this->collectIndexedColumns($index->getName(), $referencedColumns, $_indices); } } // we're adding indices for this table foreign keys foreach ($this->getForeignKeys() as $foreignKey) { $localColumns = $foreignKey->getLocalColumnObjects(); $localColumnsHash = $this->getColumnList($localColumns); if (!array_key_exists($localColumnsHash, $_indices)) { // no matching index defined in the schema, so we have to create one. MySQL needs indices on any columns that serve as foreign keys. these are not auto-created prior to 4.1.2 $index = new Index(); $index->setName(substr_replace($foreignKey->getName(), 'FI_', strrpos($foreignKey->getName(), 'FK_'), 3)); $index->setColumns($localColumns); $index->resetColumnSize(); $this->addIndex($index); $this->collectIndexedColumns($index->getName(), $localColumns, $_indices); } } }
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; }