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)); }
/** * 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)); }
/** * tries to insert a foreignkey which is not new * @expectedException CDbException */ public function testInsertNotNew() { $fk = 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' => 'tabletest', 'tableName' => 'product_order', 'columnName' => 'customer_id')); $fk->insert(); }