public function actionUpdate()
{
$isSubmitted = false;
$sql = false;
$foreignKey = ForeignKey::model()->findBySql('SELECT * FROM KEY_COLUMN_USAGE ' . 'WHERE TABLE_SCHEMA = :tableSchema ' . 'AND TABLE_NAME = :tableName ' . 'AND COLUMN_NAME = :columnName ' . 'AND REFERENCED_TABLE_SCHEMA IS NOT NULL', array('tableSchema' => $this->schema, 'tableName' => $this->table, 'columnName' => $this->column));
if (!$foreignKey) {
$foreignKey = new ForeignKey();
$foreignKey->TABLE_SCHEMA = $this->schema;
$foreignKey->TABLE_NAME = $this->table;
$foreignKey->COLUMN_NAME = $this->column;
}
if (isset($_POST['ForeignKey'])) {
$foreignKey->attributes = $_POST['ForeignKey'];
if ($foreignKey->getReferences() && ($sql = $foreignKey->save())) {
$isSubmitted = true;
} elseif (!$foreignKey->getReferences() && ($sql = $foreignKey->delete())) {
$isSubmitted = true;
}
}
CHtml::generateRandomIdPrefix();
// Column data
$columns = array('' => '');
$tables = Table::model()->findAllByAttributes(array('TABLE_SCHEMA' => $this->schema));
foreach ($tables as $table) {
if (StorageEngine::check($table->ENGINE, StorageEngine::SUPPORTS_FOREIGN_KEYS)) {
$columns[$table->TABLE_NAME] = array();
foreach ($table->columns as $column) {
$columns[$table->TABLE_NAME][$this->schema . '.' . $table->TABLE_NAME . '.' . $column->COLUMN_NAME] = $column->COLUMN_NAME;
}
}
}
// "On-Actions"
$onActions = array('' => '', 'CASCADE' => 'CASCADE', 'SET NULL' => 'SET NULL', 'NO ACTION' => 'NO ACTION', 'RESTRICT' => 'RESTRICT');
$this->render('form', array('foreignKey' => $foreignKey, 'columns' => $columns, 'onActions' => $onActions, 'sql' => $sql, 'isSubmitted' => $isSubmitted));
}
/**
* Adds the method that returns the referrer fkey collection.
* @param string &$script The script will be modified in this method.
*/
protected function addRefFKGet(&$script, ForeignKey $refFK)
{
$table = $this->getTable();
$tblFK = $refFK->getTable();
$peerClassname = $this->getStubPeerBuilder()->getClassname();
$fkPeerBuilder = $this->getNewPeerBuilder($refFK->getTable());
$relCol = $this->getRefFKPhpNameAffix($refFK, $plural = true);
$collName = $this->getRefFKCollVarName($refFK);
$lastCriteriaName = $this->getRefFKLastCriteriaVarName($refFK);
$className = $fkPeerBuilder->getObjectClassname();
$script .= "\n\t/**\n\t * Gets an array of {$className} objects which contain a foreign key that references this object.\n\t *\n\t * If this collection has already been initialized with an identical Criteria, it returns the collection.\n\t * Otherwise if this " . $this->getObjectClassname() . " has previously been saved, it will retrieve\n\t * related {$relCol} from storage. If this " . $this->getObjectClassname() . " is new, it will return\n\t * an empty collection or the current collection, the criteria is ignored on a new object.\n\t *\n\t * @param PropelPDO \$con\n\t * @param Criteria \$criteria\n\t * @return array {$className}[]\n\t * @throws PropelException\n\t */\n\tpublic function get{$relCol}(\$criteria = null, PropelPDO \$con = null, array \$selectedColumns = null)\n\t{";
$script .= "\n\t\tif (\$criteria === null) {\n\t\t\t\$criteria = new Criteria({$peerClassname}::DATABASE_NAME);\n\t\t}\n\t\telseif (\$criteria instanceof Criteria)\n\t\t{\n\t\t\t\$criteria = clone \$criteria;\n\t\t}\n\n\t\tif (\$this->{$collName} === null) {\n\t\t\tif (\$this->isNew()) {\n\t\t\t \$this->{$collName} = array();\n\t\t\t} else {\n";
foreach ($refFK->getLocalColumns() as $colFKName) {
// $colFKName is local to the referring table (i.e. foreign to this table)
$lfmap = $refFK->getLocalForeignMapping();
$localColumn = $this->getTable()->getColumn($lfmap[$colFKName]);
$colFK = $refFK->getTable()->getColumn($colFKName);
$clo = strtolower($localColumn->getName());
$script .= "\n\t\t\t\t\$criteria->add(" . $fkPeerBuilder->getColumnConstant($colFK) . ", \$this->{$clo});\n";
}
// end foreach ($fk->getForeignColumns()
$script .= "\n if (null !== \$selectedColumns) {\n " . $fkPeerBuilder->getPeerClassname() . "::setSelectedColumns(\$selectedColumns);\n }\n\t\t\t\t" . $fkPeerBuilder->getPeerClassname() . "::addSelectColumns(\$criteria);\n\t\t\t\t\$this->{$collName} = " . $fkPeerBuilder->getPeerClassname() . "::doSelect(\$criteria, \$con);\n\t\t\t}\n\t\t} else {\n\t\t\t// criteria has no effect for a new object\n\t\t\tif (!\$this->isNew()) {\n\t\t\t\t// the following code is to determine if a new query is\n\t\t\t\t// called for. If the criteria is the same as the last\n\t\t\t\t// one, just return the collection.\n";
foreach ($refFK->getLocalColumns() as $colFKName) {
// $colFKName is local to the referring table (i.e. foreign to this table)
$lfmap = $refFK->getLocalForeignMapping();
$localColumn = $this->getTable()->getColumn($lfmap[$colFKName]);
$colFK = $refFK->getTable()->getColumn($colFKName);
$clo = strtolower($localColumn->getName());
$script .= "\n\n\t\t\t\t\$criteria->add(" . $fkPeerBuilder->getColumnConstant($colFK) . ", \$this->{$clo});\n";
}
// foreach ($fk->getForeignColumns()
$script .= "\n if (null !== \$selectedColumns) {\n " . $fkPeerBuilder->getPeerClassname() . "::setSelectedColumns(\$selectedColumns);\n }\n\t\t\t\t" . $fkPeerBuilder->getPeerClassname() . "::addSelectColumns(\$criteria);\n\t\t\t\tif (!isset(\$this->{$lastCriteriaName}) || !\$this->" . $lastCriteriaName . "->equals(\$criteria)) {\n\t\t\t\t\t\$this->{$collName} = " . $fkPeerBuilder->getPeerClassname() . "::doSelect(\$criteria, \$con);\n\t\t\t\t}\n\t\t\t}\n\t\t}\n\t\t\$this->{$lastCriteriaName} = \$criteria;\n\t\treturn \$this->{$collName};\n\t}\n";
}
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);
}
public function init()
{
if (empty($this->related_name)) {
$this->setOption('related_name', $this->metadata->getDbTableName());
}
parent::init();
}
/**
* Shows the table structure
*/
public function actionStructure()
{
$table = $this->loadTable();
if (!$table instanceof Table) {
$response = new AjaxResponse();
$response->addNotification("error", Yii::t("core", "tableLoadErrorTitle", array("{table}" => $this->table)), Yii::t("core", "tableLoadErrorMessage", array("{table}" => $this->table)));
$response->executeJavaScript("sideBar.loadTables(schema)");
$this->sendJSON($response);
}
// Constraints
if (StorageEngine::check($table->ENGINE, StorageEngine::SUPPORTS_FOREIGN_KEYS)) {
$foreignKeys = array();
$sql = 'SELECT * FROM KEY_COLUMN_USAGE ' . 'WHERE TABLE_SCHEMA = :tableSchema ' . 'AND TABLE_NAME = :tableName ' . 'AND REFERENCED_TABLE_SCHEMA IS NOT NULL';
$table->foreignKeys = ForeignKey::model()->findAllBySql($sql, array('tableSchema' => $table->TABLE_SCHEMA, 'tableName' => $table->TABLE_NAME));
foreach ($table->foreignKeys as $key) {
$foreignKeys[] = $key->COLUMN_NAME;
}
} else {
$foreignKeys = false;
}
// Indices
$sql = 'SELECT * FROM STATISTICS ' . 'WHERE TABLE_SCHEMA = :tableSchema ' . 'AND TABLE_NAME = :tableName ' . 'GROUP BY INDEX_NAME ' . 'ORDER BY INDEX_NAME = \'PRIMARY\' DESC, INDEX_NAME';
$table->indices = Index::model()->findAllBySql($sql, array('tableSchema' => $table->TABLE_SCHEMA, 'tableName' => $table->TABLE_NAME));
foreach ($table->indices as $index) {
$index->columns = IndexColumn::model()->findAllByAttributes(array('TABLE_SCHEMA' => $table->TABLE_SCHEMA, 'TABLE_NAME' => $table->TABLE_NAME, 'INDEX_NAME' => $index->INDEX_NAME));
}
// Indices (seperate for each column)
$indicesRaw = Index::model()->findAllByAttributes(array('TABLE_SCHEMA' => $table->TABLE_SCHEMA, 'TABLE_NAME' => $table->TABLE_NAME));
// Triggers
$table->triggers = Trigger::model()->findAllByAttributes(array('EVENT_OBJECT_SCHEMA' => $table->TABLE_SCHEMA, 'EVENT_OBJECT_TABLE' => $table->TABLE_NAME));
$this->render('structure', array('table' => $table, 'canAlter' => Yii::app()->user->privileges->checkTable($table->TABLE_SCHEMA, $table->TABLE_NAME, 'ALTER'), 'foreignKeys' => $foreignKeys, 'indicesRaw' => $indicesRaw));
}
public function modifyTable()
{
$parentTable = $this->getParentTable();
if (count($parentTable->getPrimaryKey()) > 1) {
throw new RuntimeException('Equal nest works only with a single primary key for the parent table');
}
$parentTablePrimaryKey = $parentTable->getPrimaryKey();
if (!$this->getTable()->hasColumn($this->getReferenceColumn1Name())) {
$this->getTable()->addColumn(array('name' => $this->getReferenceColumn1Name(), 'primaryKey' => 'true', 'type' => $parentTablePrimaryKey[0]->getType()));
$fk = new ForeignKey();
$fk->setName($this->getReferenceColumn1Name());
$fk->setForeignTableCommonName($this->getParentTable()->getCommonName());
$fk->setOnDelete(ForeignKey::CASCADE);
$fk->setOnUpdate(null);
$fk->addReference($this->getReferenceColumn1Name(), $parentTablePrimaryKey[0]->getName());
$this->getTable()->addForeignKey($fk);
}
if (!$this->getTable()->hasColumn($this->getReferenceColumn2Name())) {
$this->getTable()->addColumn(array('name' => $this->getReferenceColumn2Name(), 'primaryKey' => 'true', 'type' => $parentTablePrimaryKey[0]->getType()));
$fk = new ForeignKey();
$fk->setName($this->getReferenceColumn2Name());
$fk->setForeignTableCommonName($this->getParentTable()->getCommonName());
$fk->setOnDelete(ForeignKey::CASCADE);
$fk->setOnUpdate(null);
$fk->addReference($this->getReferenceColumn2Name(), $parentTablePrimaryKey[0]->getName());
$this->getTable()->addForeignKey($fk);
}
if (!$parentTable->hasBehavior('equal_nest_parent')) {
$parentBehavior = new EqualNestParentBehavior();
$parentBehavior->setName('equal_nest_parent');
$parentBehavior->addParameter(array('name' => 'middle_table', 'value' => $this->getTable()->getName()));
$parentTable->addBehavior($parentBehavior);
}
$this->parentBehavior = $parentTable->getBehavior('equal_nest_parent');
}
private function addRelation($oLocalColumn, $oUsersTable, $oUsersId)
{
$oFk = new ForeignKey();
$oFk->setTable($this->getTable());
$oFk->setForeignTableCommonName($oUsersTable->getName());
if (!$oFk->isMatchedByInverseFK()) {
$oFk->setOnDelete(ForeignKey::SETNULL);
$oFk->setOnUpdate(null);
$oFk->addReference($oLocalColumn, $oUsersId);
$this->getTable()->addForeignKey($oFk);
}
}
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);
}
private function __construct($sql)
{
preg_match('/^CREATE TABLE (?<name>[^\\s\\(]+) \\((?<defs>.*)\\)\\s*$/is', $sql, $matches);
$this->m_name = $matches['name'];
$defs = array_map('trim', preg_split('/,[\\t ]*[\\r\\n]/', $matches['defs']));
$this->m_columns = [];
$this->m_foreign = [];
$this->m_unique = [];
$constraints = [];
foreach ($defs as $def) {
if (count($constraints) || ($matched = self::match('(?:CONSTRAINT|PRIMARY|UNIQUE|FOREIGN|CHECK)\\b', $def, $m))) {
if (isset($matched) && !$matched) {
throw new \Exception('Failed to parse start of constraint');
}
// table constraint
// check for optional constraint name
$constraintName = NULL;
if (self::match('CONSTRAINT (?<name>' . Column::R_NAME . ')', $def, $m)) {
$constraintName = $m['name'];
$def = ltrim(substr($def, strlen($m[0])));
}
if ($uniqueKey = UniqueKey::parse($constraintName, $def)) {
$this->m_unique[] = $uniqueKey;
} else {
if ($foreignKey = ForeignKey::parse($constraintName, $def)) {
$this->m_foreign[$foreignKey->localField()] = $foreignKey;
} else {
throw new \Exception('Failed to parse constraint');
}
}
} else {
$column = Column::parse($def);
$this->m_columns[$column->name()] = $column;
}
}
}
/**
* Gets the "RelatedBy*" suffix (if needed) that is attached to method and variable names.
*
* The related by suffix is based on the local columns of the foreign key. If there is more than
* one column in a table that points to the same foreign table, then a 'RelatedByLocalColName' suffix
* will be appended.
*
* @return string
*/
protected function getRelatedBySuffix(ForeignKey $fk, $columnCheck = false)
{
$relCol = "";
foreach ($fk->getLocalColumns() as $columnName) {
$column = $fk->getTable()->getColumn($columnName);
if (!$column) {
throw new Exception("Could not fetch column: {$columnName} in table " . $fk->getTable()->getName());
}
if (count($column->getTable()->getForeignKeysReferencingTable($fk->getForeignTableName())) > 1 || $fk->getForeignTableName() == $fk->getTable()->getName()) {
// if there are seeral foreign keys that point to the same table
// then we need to generate methods like getAuthorRelatedByColName()
// instead of just getAuthor(). Currently we are doing the same
// for self-referential foreign keys, to avoid confusion.
$relCol .= $column->getPhpName();
}
}
#var_dump($fk->getForeignTableName() . ' - ' .$fk->getTableName() . ' - ' . $this->getTable()->getName());
#$fk->getForeignTableName() != $this->getTable()->getName() &&
// @todo comment on it
if ($columnCheck && !$relCol && $fk->getTable()->getColumn($fk->getForeignTableName())) {
foreach ($fk->getLocalColumns() as $columnName) {
$column = $fk->getTable()->getColumn($columnName);
$relCol .= $column->getPhpName();
}
}
if ($relCol != "") {
$relCol = "RelatedBy" . $relCol;
}
return $relCol;
}
/**
* 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->setForeignTableCommonName($foreignReferenceInfo['TABLE_NAME']);
$onDelete = $row["DELETE_RULE"] == 'NO ACTION' ? '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;
}
}
}
public function getReferrerVersionsColumn(ForeignKey $fk)
{
$fkTableName = $fk->getTable()->getName();
$fkIdsColumnName = $fkTableName . '_versions';
return $this->versionTable->getColumn($fkIdsColumnName);
}
public function getForeignKeyDDL(ForeignKey $fk)
{
$pattern = "\n-- SQLite does not support foreign keys; this is just for reference\n-- FOREIGN KEY (%s) REFERENCES %s (%s)\n";
return sprintf($pattern, $this->getColumnListDDL($fk->getLocalColumns()), $fk->getForeignTableName(), $this->getColumnListDDL($fk->getForeignColumns()));
}
/**
* Criar a ForeignKey da Associação.
* @param $column
* @param $other_table
* @param null $name
* @param null $other_key
* @return \Illuminate\Support\Fluent
*/
public function foreignAssociation($column, $other_table, $name = null, $other_key = null)
{
$name = is_null($name) ? ForeignKey::makeName($this->table, $column) : $name;
$other_key = is_null($other_key) ? self::keyAttr() : $other_key;
$fk = $this->foreign($column, $name);
$fk->on($other_table);
$fk->references($other_key);
return $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 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
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->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->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->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 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);
}
// 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);
}
}
public function getForeignKeyDDL(ForeignKey $fk)
{
if ($fk->isSkipSql()) {
return;
}
$pattern = 'CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)';
$script = sprintf($pattern, $this->quoteIdentifier($fk->getName()), $this->getColumnListDDL($fk->getLocalColumns()), $this->quoteIdentifier($fk->getForeignTableName()), $this->getColumnListDDL($fk->getForeignColumns()));
if ($fk->hasOnUpdate() && $fk->getOnUpdate() != ForeignKey::SETNULL) {
$script .= ' ON UPDATE ' . $fk->getOnUpdate();
}
if ($fk->hasOnDelete() && $fk->getOnDelete() != ForeignKey::SETNULL) {
$script .= ' ON DELETE ' . $fk->getOnDelete();
}
return $script;
}
/**
* Adds the method that adds an object into the referrer fkey collection.
* @param string &$script The script will be modified in this method.
*/
protected function addCrossFKAdd(&$script, ForeignKey $refFK, ForeignKey $crossFK)
{
$relCol = $this->getFKPhpNameAffix($crossFK, $plural = true);
$collName = $this->getCrossFKVarName($crossFK);
$tblFK = $refFK->getTable();
$joinedTableObjectBuilder = $this->getNewObjectBuilder($refFK->getTable());
$className = $joinedTableObjectBuilder->getObjectClassname();
$foreignObjectName = '$' . $tblFK->getStudlyPhpName();
$crossObjectName = '$' . $crossFK->getForeignTable()->getStudlyPhpName();
$crossObjectClassName = $this->getNewObjectBuilder($crossFK->getForeignTable())->getObjectClassname();
$script .= "\n\t/**\n\t * Associate a " . $crossObjectClassName . " object to this object\n\t * through the " . $tblFK->getName() . " cross reference table.\n\t *\n\t * @param " . $crossObjectClassName . " " . $crossObjectName . " The {$className} object to relate\n\t * @return void\n\t */\n\tpublic function add" . $this->getFKPhpNameAffix($crossFK, $plural = false) . "(" . $crossObjectName . ")\n\t{\n\t\tif (\$this->" . $collName . " === null) {\n\t\t\t\$this->init" . $relCol . "();\n\t\t}\n\t\tif (!\$this->" . $collName . "->contains(" . $crossObjectName . ")) { // only add it if the **same** object is not already associated\n\t\t\t" . $foreignObjectName . " = new " . $className . "();\n\t\t\t" . $foreignObjectName . "->set" . $this->getFKPhpNameAffix($crossFK, $plural = false) . "(" . $crossObjectName . ");\n\t\t\t\$this->add" . $this->getRefFKPhpNameAffix($refFK, $plural = false) . "(" . $foreignObjectName . ");\n\n\t\t\t\$this->" . $collName . "[]= " . $crossObjectName . ";\n\t\t}\n\t}\n";
}
public function testColumnIsFKAndPK()
{
$column = new Column();
$column->setName('id');
$column->setPrimaryKey(true);
$column->setAutoIncrement(true);
$column->setType('integer');
$table = new Table();
$table->setCommonName('table_one');
$table->addColumn($column);
$db = new Database();
$db->setName('MultipleTables');
$db->addTable($table);
$column = new Column();
$column->setName('id');
$column->setPrimaryKey(true);
$column->setAutoIncrement(true);
$column->setType('integer');
$c2 = new Column();
$c2->setPrimaryKey(true);
$c2->setName('foreign_id');
$c2->setType('integer');
$table = new Table();
$table->setCommonName('table_two');
$table->addColumn($column);
$table->addColumn($c2);
$fk = new ForeignKey();
$fk->setName('FK_1');
$fk->addReference('foreign_id', 'id');
$fk->setForeignTableCommonName('table_one');
$table->addForeignKey($fk);
$db->addTable($table);
$expected = implode("\n", array('digraph G {', 'nodetable_one [label="{<table>table_one|<cols>id (integer) [PK]\\l}", shape=record];', 'nodetable_two [label="{<table>table_two|<cols>id (integer) [PK]\\lforeign_id (integer) [FK] [PK]\\l}", shape=record];', 'nodetable_two:cols -> nodetable_one:table [label="foreign_id=id"];', '}', ''));
$this->assertEquals($expected, PropelDotGenerator::create($db));
}
/**
* 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]);
}
}
}
}
/**
* @param $columns
* @param $referencedTable
* @param array $referencedColumns
* @param array $options
* @return $this
*/
public function addForeignKey($columns, $referencedTable, $referencedColumns = ['id'], $options = [])
{
$foreignKey = new ForeignKey();
if (!is_array($referencedColumns)) {
$referencedColumns = array($referencedColumns);
}
$foreignKey->setReferenceColumns($referencedColumns);
if ($referencedTable instanceof Table) {
$foreignKey->setReferenceTable($referencedTable);
} else {
$foreignKey->setReferenceTable(new Table($referencedTable, [], $this->mapper));
}
$foreignKey->setColumns($columns);
$foreignKey->setOptions($options);
$this->foreignKeys[] = $foreignKey;
return $this;
}
/**
* A utility function to create a new foreign key
* from attrib and add it to this table.
*/
public function addForeignKey($fkdata)
{
if ($fkdata instanceof ForeignKey) {
$fk = $fkdata;
$fk->setTable($this);
$this->foreignKeys[] = $fk;
if ($this->foreignTableNames === null) {
$this->foreignTableNames = array();
}
if (!in_array($fk->getForeignTableName(), $this->foreignTableNames)) {
$this->foreignTableNames[] = $fk->getForeignTableName();
}
return $fk;
} else {
$fk = new ForeignKey();
$fk->setTable($this);
$fk->loadFromXML($fkdata);
return $this->addForeignKey($fk);
}
}
/**
* @param string $stmt
* @return $this
*/
public function parse($stmt)
{
$lines = array_map('trim', explode(PHP_EOL, $stmt));
$this->first = array_shift($lines);
$last = array_pop($lines);
//remove AUTO_INCREMENT bit from raw statement
$this->last = preg_replace('/AUTO_INCREMENT=\\d+ ?/', '', $last);
$this->statement = str_replace($last, $this->last, $stmt);
if ($this->name === null) {
if (!preg_match('/`([^`]+)/', $this->first, $match)) {
throw new \RuntimeException(sprintf('Unable to extract name from %s (looked at line %s)', $stmt, $lines[0]));
}
$this->name = $match[1];
}
foreach ($lines as $ln) {
if (mb_substr($ln, -1) == ',') {
$ln = mb_substr($ln, 0, -1);
}
//field lines start with back-tick
switch ($ln[0]) {
case '`':
$field = new Field($ln);
$this->fields[$field->getName()] = $field;
break;
case 'P':
$this->primary = new Primary($ln);
break;
case 'S':
//spatial
//spatial
case 'U':
//unique
//unique
case 'F':
//fulltext
//fulltext
case 'I':
//index
//index
case 'K':
//Key
$idx = new Index($ln);
$this->indexes[$idx->getName()] = $idx;
break;
case 'C':
$constraint = new ForeignKey($ln);
$this->constraints[$constraint->getName()] = $constraint;
break;
default:
throw new \LogicException(sprintf('Unable to parse line %s in %s', $ln, $stmt));
}
}
return $this;
}
public function getForeignKeyDDL(ForeignKey $fk)
{
if ($fk->isSkipSql()) {
return;
}
$pattern = "CONSTRAINT %s\r\n FOREIGN KEY (%s) REFERENCES %s (%s)";
$script = sprintf($pattern, $this->quoteIdentifier($fk->getName()), $this->getColumnListDDL($fk->getLocalColumns()), $this->quoteIdentifier($fk->getForeignTableName()), $this->getColumnListDDL($fk->getForeignColumns()));
if ($fk->hasOnDelete()) {
$script .= "\r\n ON DELETE " . $fk->getOnDelete();
}
return $script;
}
/**
* Load foreign keys for this table.
*/
protected function addForeignKeys(Table $table, $oid, $version)
{
$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();
// local store to avoid duplicates
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$name = $row['conname'];
$local_table = $row['fktab'];
$local_columns = explode(',', trim($row['fkcols'], '{}'));
$foreign_table = $row['reftab'];
$foreign_columns = 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($foreign_table);
$localTable = $database->getTable($local_table);
if (!isset($foreignKeys[$name])) {
$fk = new ForeignKey($name);
$fk->setForeignTableCommonName($foreignTable->getCommonName());
$fk->setForeignSchemaName($foreignTable->getSchema());
$fk->setOnDelete($ondelete);
$fk->setOnUpdate($onupdate);
$table->addForeignKey($fk);
$foreignKeys[$name] = $fk;
}
for ($i = 0; $i < count($local_columns); $i++) {
$foreignKeys[$name]->addReference($localTable->getColumn($local_columns[$i]), $foreignTable->getColumn($foreign_columns[$i]));
}
}
}
public function testCompareModifiedFks()
{
$db1 = new Database();
$db1->setPlatform($this->platform);
$c1 = new Column('Foo');
$c2 = new Column('Bar');
$fk1 = new ForeignKey();
$fk1->addReference($c1, $c2);
$t1 = new Table('Baz');
$t1->addForeignKey($fk1);
$db1->addTable($t1);
$t1->doNaming();
$db2 = new Database();
$db2->setPlatform($this->platform);
$c3 = new Column('Foo');
$c4 = new Column('Bar2');
$fk2 = new ForeignKey();
$fk2->addReference($c3, $c4);
$t2 = new Table('Baz');
$t2->addForeignKey($fk2);
$db2->addTable($t2);
$t2->doNaming();
$tc = new PropelTableComparator();
$tc->setFromTable($t1);
$tc->setToTable($t2);
$nbDiffs = $tc->compareForeignKeys();
$tableDiff = $tc->getTableDiff();
$this->assertEquals(1, $nbDiffs);
$this->assertEquals(1, count($tableDiff->getModifiedFks()));
$this->assertEquals(array('Baz_FK_1' => array($fk1, $fk2)), $tableDiff->getModifiedFks());
}
public function getDropForeignKeyDDL(ForeignKey $fk)
{
if ($fk->isSkipSql()) {
return;
}
$pattern = "\nALTER TABLE %s DROP FOREIGN KEY %s;\n";
return sprintf($pattern, $this->quoteIdentifier($fk->getTable()->getName()), $this->quoteIdentifier($fk->getName()));
}
/**
* @param string &$script The script will be modified in this method.
* @param ForeignKey $refFK
* @param ForeignKey $crossFK
*/
protected function addCrossFKDoAdd(&$script, ForeignKey $refFK, ForeignKey $crossFK)
{
$relatedObjectClassName = $this->getFKPhpNameAffix($crossFK, $plural = false);
// lcfirst() doesn't exist in PHP < 5.3
$lowerRelatedObjectClassName = $relatedObjectClassName;
$lowerRelatedObjectClassName[0] = strtolower($lowerRelatedObjectClassName[0]);
$joinedTableObjectBuilder = $this->getNewObjectBuilder($refFK->getTable());
$className = $joinedTableObjectBuilder->getObjectClassname();
$tblFK = $refFK->getTable();
$foreignObjectName = '$' . $tblFK->getStudlyPhpName();
$script .= "\n\t/**\n\t * @param\t{$relatedObjectClassName} \${$lowerRelatedObjectClassName} The {$lowerRelatedObjectClassName} object to add.\n\t */\n\tprotected function doAdd{$relatedObjectClassName}(\${$lowerRelatedObjectClassName})\n\t{\n\t\t{$foreignObjectName} = new {$className}();\n\t\t{$foreignObjectName}->set{$relatedObjectClassName}(\${$lowerRelatedObjectClassName});\n\t\t\$this->add{$className}({$foreignObjectName});\n\t}\n";
}
/**
* 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);
}
}
/**
* Adds the method that remove an object from the referrer fkey collection.
* @param string $script The script will be modified in this method.
*/
protected function addCrossFKRemove(&$script, ForeignKey $refFK, ForeignKey $crossFK)
{
$relCol = $this->getFKPhpNameAffix($crossFK, $plural = true);
$collName = 'coll' . $relCol;
$tblFK = $refFK->getTable();
$joinedTableObjectBuilder = $this->getNewObjectBuilder($refFK->getTable());
$className = $joinedTableObjectBuilder->getObjectClassname();
$lowerRelCol = $relCol;
$lowerRelCol[0] = strtolower($lowerRelCol[0]);
$M2MScheduledForDeletion = $lowerRelCol . "ScheduledForDeletion";
$crossObjectName = '$' . $crossFK->getForeignTable()->getStudlyPhpName();
$crossObjectClassName = $this->getNewObjectBuilder($crossFK->getForeignTable())->getObjectClassname();
$relatedObjectClassName = $this->getFKPhpNameAffix($crossFK, $plural = false);
$script .= "\n /**\n * Remove a {$crossObjectClassName} object to this object\n * through the {$tblFK->getName()} cross reference table.\n *\n * @param {$crossObjectClassName} {$crossObjectName} The {$className} object to relate\n * @return void\n */\n public function remove{$relatedObjectClassName}({$crossObjectClassName} {$crossObjectName})\n {\n if (\$this->get{$relCol}()->contains({$crossObjectName})) {\n \$this->{$collName}->remove(\$this->{$collName}->search({$crossObjectName}));\n if (null === \$this->{$M2MScheduledForDeletion}) {\n \$this->{$M2MScheduledForDeletion} = clone \$this->{$collName};\n \$this->{$M2MScheduledForDeletion}->clear();\n }\n \$this->{$M2MScheduledForDeletion}[]= {$crossObjectName};\n }\n }\n";
}
