public function run() { $response = new AjaxResponse(); $profiling = Yii::app()->user->settings->get('profiling'); try { $sqlQuery = new SqlQuery($this->query); } catch (SQPException $ex) { $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $ex->getMessage()); $this->response = $response; return; } if (!$this->query) { $this->query = $this->getDefaultQuery(); $queries = (array) $this->query; } else { if ($profiling) { $cmd = $this->db->createCommand('FLUSH STATUS'); $cmd->execute(); $cmd = $this->db->createCommand('SET PROFILING = 1'); $cmd->execute(); } $splitter = new SqlSplitter($this->query); $queries = $splitter->getQueries(); } if ($this->execute) { $queryCount = count($queries); $i = 1; foreach ($queries as $query) { try { $sqlQuery = new SqlQuery($query); } catch (SQPException $ex) { $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $ex->getMessage()); break; } $type = $sqlQuery->getType(); $this->table = $sqlQuery->getTable(); $this->tables = $sqlQuery->getTables(); $this->singleTableSelect = count($this->tables) == 1; // SELECT if ($type == "select") { // Pagination $pages = new Pagination(); $pages->route = $this->route; $pageSize = $pages->setupPageSize('pageSize', 'schema.table.browse'); // Sorting $sort = new Sort($this->db); $sort->multiSort = false; $sort->route = $this->route; $sqlQuery->applyCalculateFoundRows(); $limit = $sqlQuery->getLimit(); $order = $sqlQuery->getOrder(); // Apply sort if ($sort->getOrder()) { $sqlQuery->applySort($sort->getOrder(), true); } if (isset($_REQUEST['page'])) { $offset = $_REQUEST['page'] * $pageSize - $pageSize; $sqlQuery->applyLimit($pageSize, $offset, true); } // Set pagesize from query limit if ($limit && !isset($_REQUEST[$pages->pageSizeVar])) { $_REQUEST[$pages->pageSizeVar] = $limit['length']; $pageSize = $pages->setupPageSize('pageSize', 'schema.table.browse'); $offset = $limit['start']; } elseif (!$limit) { $offset = 0; $sqlQuery->applyLimit($pageSize, $offset, true); } elseif (isset($_REQUEST[$pages->pageSizeVar])) { $pageSize = $pages->setupPageSize('pageSize', 'schema.table.browse'); $offset = 0; $sqlQuery->applyLimit($pageSize, $offset, true); } $this->start = (int) $offset; } elseif ($type == "insert" || $type == "update" || $type == "delete") { #predie("insert / update / delete statement"); $response->refresh = true; } elseif ($type == "show") { // show create table etc. } elseif ($type == "explain") { } elseif ($type == "analyze" || $type == "optimize" || $type == "repair" || $type == "check") { // Table functions } elseif ($type == "use") { $name = $sqlQuery->getDatabase(); if ($queryCount == 1 && $name && $this->schema != $name) { $response->redirectUrl = Yii::app()->baseUrl . '/schema/' . $name . '#sql'; $response->addNotification('success', Yii::t('core', 'successChangeDatabase', array('{name}' => $name))); } } elseif ($type == "create") { $response->reload = true; } elseif ($type == "drop") { $response->reload = true; } $this->executedQueries[] = $sqlQuery->getQuery(); $this->originalQueries[] = $sqlQuery->getOriginalQuery(); if ($type == "select") { $pages->postVars = $sort->postVars = array('query' => $sqlQuery->getOriginalQuery()); } // Prepare query for execution $cmd = $this->db->createCommand($sqlQuery->getQuery()); $cmd->prepare(); if ($this->hasResultSet = $sqlQuery->returnsResultSet() !== false) { try { // Fetch data $start = microtime(true); $data = $cmd->queryAll(); $time = round(microtime(true) - $start, 6); SqlUtil::FixTable($data); if ($type == 'select') { $total = (int) $this->db->createCommand('SELECT FOUND_ROWS()')->queryScalar(); $pages->setItemCount($total); $this->total = $total; $keyData = array(); } $columns = array(); // Fetch column headers if (isset($data[0])) { $columns = array_keys($data[0]); } $isSent = true; $this->lastResultSetQuery = $sqlQuery->getOriginalQuery(); } catch (CDbException $ex) { $ex = new DbException($cmd); $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $ex->getText(), $ex->getSql()); } } else { try { // Measure time $start = microtime(true); $result = $cmd->execute(); $time = round(microtime(true) - $start, 6); $response->addNotification('success', Yii::t('core', 'successExecuteQuery'), Yii::t('core', 'affectedRowsQueryTime', array($result, '{rows}' => $result, '{time}' => $time)), $sqlQuery->getQuery()); } catch (CDbException $ex) { $dbException = new DbException($cmd); $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), Yii::t('core', 'sqlErrorOccured', array('{errno}' => $dbException->getNumber(), '{errmsg}' => $dbException->getText()))); } } $i++; } if ($profiling) { $cmd = $this->db->createCommand('select state, SUM(duration) as total, COUNT(*) AS count FROM information_schema.profiling GROUP BY state ORDER by total desc'); $cmd->prepare(); $profileData = $cmd->queryAll(); if (count($profileData)) { $results = '<table class="profiling">'; foreach ($profileData as $item) { $results .= '<tr>'; $results .= '<td class="state">' . ucfirst($item['state']) . '</td>'; $results .= '<td class="time">' . $item['total'] . '</td>'; $results .= '<td class="count">(' . $item['count'] . ')</td>'; $results .= '</tr>'; } $results .= '</table>'; $response->addNotification('info', Yii::t('core', 'profilingResultsSortedByExecutionTime'), $results, null); } } else { if (isset($total) && isset($time)) { $response->addNotification('success', Yii::t('core', 'successExecuteQuery'), Yii::t('core', 'foundRowsQueryTime', array(null, '{rows}' => $total, '{time}' => $time)), $sqlQuery->getQuery()); } } } // Assign local variables to class properties if (isset($pages)) { $this->pagination = $pages; } if (isset($sort)) { $this->sort = $sort; } if (isset($type)) { $this->queryType = $type; } if (isset($columns)) { $this->columns = $columns; foreach ($this->columns as $column) { if ($this->getColumn($column) == null) { $this->singleTableSelect = false; break; } } } if (isset($data)) { $this->data = $data; } if (isset($response)) { $this->response = $response; } }
/** * Lists all schemata. */ public function actionList() { // Create list for sideBar usage if ($this->request->getParam('sideBar')) { $schemata = array(); foreach (Schema::model()->findAll() as $schema) { $schemata[] = $schema->SCHEMA_NAME; } $this->sendJSON($schemata); } else { $criteria = new CDbCriteria(); // Pagination $pages = new Pagination(Schema::model()->count($criteria)); $pages->setupPageSize('pageSize', 'schemata'); $pages->applyLimit($criteria); $pages->route = '#schemata'; // Sort $sort = new CSort('Schema'); $sort->attributes = array('name' => 'SCHEMA_NAME', 'tableCount' => 'tableCount', 'collation' => 'DEFAULT_COLLATION_NAME'); $sort->defaultOrder = 'SCHEMA_NAME ASC'; $sort->route = '#schemata'; $sort->applyOrder($criteria); $criteria->group = 'SCHEMA_NAME'; $criteria->select = 'SCHEMA_NAME, DEFAULT_COLLATION_NAME, COUNT(*) AS tableCount'; $schemaList = Schema::model()->with(array('tables' => array('select' => 'COUNT(tables.TABLE_NAME) AS tableCount')))->together()->findAll($criteria); $this->render('list', array('schemaList' => $schemaList, 'schemaCount' => $pages->getItemCount(), 'schemaCountThisPage' => min($pages->getPageSize(), $pages->getItemCount() - $pages->getCurrentPage() * $pages->getPageSize()), 'pages' => $pages, 'sort' => $sort)); } }
public function actionSchemata() { // Create criteria $criteria = new CDbCriteria(); $criteria->condition = 'Host = :host AND User = :user'; $criteria->params = array(':host' => $this->host, ':user' => $this->user); // Pagination $pages = new Pagination(User::model()->count($criteria)); $pages->setupPageSize('pageSize', 'privileges.userSchemata'); $pages->applyLimit($criteria); $pages->route = '#privileges/users/' . base64_encode($this->user . '@' . $this->host) . '/schemata'; // Sort $sort = new CSort('User'); $sort->attributes = array('User' => 'username', 'Host' => 'host', 'Password = \'\'' => 'password'); $sort->defaultOrder = 'User ASC'; $sort->route = '#privileges/users/' . base64_encode($this->user . '@' . $this->host) . '/schemata'; $sort->applyOrder($criteria); // Fetch schemata $schemata = SchemaPrivilege::model()->findAll($criteria); // Render $this->render('userSchemata', array('schemata' => $schemata, 'user' => $this->user, 'host' => $this->host, 'pages' => $pages, 'sort' => $sort)); }