/** * 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)); }
/** * tests return value of getCreateTrigger method * and tries to insert a triggesr */ public function testGetCreateTrigger() { $triggerObj = Trigger::model()->findByPk(array('TRIGGER_SCHEMA' => 'triggertest', 'TRIGGER_NAME' => 'trigger1')); $createTrigger = $triggerObj->getCreateTrigger(); $this->assertType('string', $createTrigger); $this->assertType('string', $triggerObj->delete()); $cmd = Trigger::$db->createCommand($createTrigger); $this->assertEquals(0, $cmd->execute()); $triggerObj = Trigger::model()->findByPk(array('TRIGGER_SCHEMA' => 'triggertest', 'TRIGGER_NAME' => 'trigger1')); $this->assertType('Trigger', $triggerObj); }
private function exportTableStructure($table) { // Get DbConnection object $db = Yii::app()->db; $this->comment('Structure for table ' . $db->quoteTableName($table->TABLE_NAME)); echo "\n\n"; // Structure if ($this->settings['addDropObject']) { echo 'DROP TABLE IF EXISTS ', $db->quoteTableName($table->TABLE_NAME), ";\n"; } $tableStructure = $table->getShowCreateTable(); if ($this->settings['addIfNotExists']) { $tableStructure = 'CREATE TABLE IF NOT EXISTS' . substr($tableStructure, 12); } echo $tableStructure, ";\n\n"; // Triggers if ($this->settings['exportTriggers']) { $triggers = Trigger::model()->findAllByAttributes(array('EVENT_OBJECT_SCHEMA' => $table->TABLE_SCHEMA, 'EVENT_OBJECT_TABLE' => $table->TABLE_NAME)); foreach ($triggers as $trigger) { $this->comment('Trigger ' . $db->quoteTableName($trigger->TRIGGER_NAME) . ' on table ' . $db->quoteTablename($table->TABLE_NAME)); echo "\n\n"; if ($this->settings['addDropObject']) { echo 'DROP TRIGGER IF EXISTS ', $db->quoteTableName($trigger->TRIGGER_NAME), ";\n"; } echo $trigger->getCreateTrigger(), ";\n\n"; } } }
/** * Updates a trigger. */ public function actionUpdate() { $trigger = Trigger::model()->findByPk(array('TRIGGER_SCHEMA' => $this->schema, 'TRIGGER_NAME' => $this->trigger)); if (is_null($trigger)) { $trigger = new Trigger(); } if (isset($_POST['query'])) { $query = $_POST['query']; try { // Split queries $splitter = new SqlSplitter($query); $splitter->delimiter = self::$delimiter; $queries = $splitter->getQueries(); foreach ($queries as $query2) { $cmd = $this->db->createCommand($query2); $cmd->prepare(); $cmd->execute(); } $response = new AjaxResponse(); $response->addNotification('success', Yii::t('core', 'successAlterTrigger'), null, $query); $response->refresh = true; $this->sendJSON($response); } catch (CDbException $ex) { $errorInfo = $cmd->getPdoStatement()->errorInfo(); $trigger->addError(null, Yii::t('core', 'sqlErrorOccured', array('{errno}' => $errorInfo[1], '{errmsg}' => $errorInfo[2]))); } } else { $query = 'DROP TRIGGER ' . $this->db->quoteTableName($trigger->TRIGGER_NAME) . self::$delimiter . "\n" . $trigger->getCreateTrigger(); } CHtml::generateRandomIdPrefix(); $this->render('form', array('trigger' => $trigger, 'query' => $query)); }