Exemplo n.º 1
0
 /**
  * Method install database structure from sql file
  * @param array $dbinfo Array with database settings to be checked
  * @return mixed true on success, error message on fault
  */
 private function _setupDatabase($dbinfo)
 {
     $adapter = array('params' => $dbinfo);
     if (extension_loaded('pdo_mysql')) {
         $adapter['adapter'] = 'pdo_mysql';
         $adapter['params']['driver_options'] = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES UTF8;');
     } else {
         return "You should have pdo_mysql extension installed";
     }
     try {
         $db = Zend_Db::factory(new Zend_Config($adapter));
         $this->_session->dbinfo = $adapter;
         Zend_Db_Table::setDefaultAdapter($db);
         $file = file_get_contents(APPLICATION_PATH . '/resourses/seotoaster.sql');
         if ($file === false) {
             return false;
         }
         $queries = SqlSplitter::split($file);
         $db->beginTransaction();
         try {
             foreach ($queries as $sql) {
                 $db->query($sql);
             }
             $db->commit();
             return true;
         } catch (Exception $ex) {
             $db->rollBack();
             return $ex->getMessage();
         }
     } catch (Exception $e) {
         return $e->getMessage();
     }
 }
Exemplo n.º 2
0
    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;
        }
    }
Exemplo n.º 3
0
 /**
  * Updates a routine.
  */
 public function actionUpdate()
 {
     $routine = Routine::model()->findByPk(array('ROUTINE_SCHEMA' => $this->schema, 'ROUTINE_NAME' => $this->routine));
     if (is_null($routine)) {
         $routine = new Routine();
         $routine->ROUTINE_TYPE = $_POST['type'];
     }
     $type = strtolower($routine->ROUTINE_TYPE);
     if (isset($_POST['query'])) {
         $currentRoutine = $routine->getCreateRoutine();
         $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', 'successAlterRoutine', array('{routine}' => $routine->ROUTINE_NAME)), null, $query);
             $response->refresh = true;
             $this->sendJSON($response);
         } catch (CDbException $ex) {
             $errorInfo = $cmd->getPdoStatement()->errorInfo();
             $routine->addError(null, Yii::t('core', 'sqlErrorOccured', array('{errno}' => $errorInfo[1], '{errmsg}' => $errorInfo[2])));
             $this->restoreCurrentRoutine($currentRoutine);
         }
     } else {
         $query = 'DROP ' . strtoupper($routine->ROUTINE_TYPE) . ' ' . $this->db->quoteTableName($routine->ROUTINE_NAME) . self::$delimiter . "\n" . $routine->getCreateRoutine();
     }
     CHtml::generateRandomIdPrefix();
     $this->render('form', array('routine' => $routine, 'type' => $type, 'query' => $query));
 }
Exemplo n.º 4
0
 public function runImport()
 {
     $response = new AjaxResponse();
     $response->refresh = true;
     $response->executeJavaScript('sideBar.loadTables("' . $this->schema . '")');
     $this->mimeType = CFileHelper::getMimeType($this->file);
     $filesize = filesize($this->file);
     // Open file and set position to last position
     switch ($this->mimeType) {
         // GZip - Files
         case 'application/x-gzip':
             $handle = gzopen($this->file, 'r');
             $content = gzread($handle, $filesize);
             gzclose($handle);
             break;
             // BZip - Files
         // BZip - Files
         case 'application/x-bzip2':
             $handle = bzopen($this->file, 'r');
             $content = bzread($handle, $filesize);
             bzclose($handle);
             break;
             // All other files (plain text)
         // All other files (plain text)
         default:
             $content = file_get_contents($this->file);
             break;
     }
     $sqlSplitter = new SqlSplitter($content);
     $queries = $sqlSplitter->getQueries();
     foreach ($queries as $query) {
         try {
             $cmd = $this->db->createCommand($query);
             # Do NOT prepare the statement, because of double quoting
             $cmd->execute();
         } catch (CDbException $ex) {
             $dbException = new DbException($cmd);
             if (!in_array(@$dbException->getNumber(), $this->ignoreErrorNumbers)) {
                 $dbException = new DbException($cmd);
                 $response->addNotification('error', Yii::t('core', 'errorExecuteQuery'), $dbException->getText() . '  ' . $dbException->getNumber(), StringUtil::cutText($dbException->getSql(), 100));
                 $response->addData('error', true);
                 $response->refresh = true;
                 @unlink($this->file);
                 return $response;
             }
         }
     }
     $response->addNotification('success', Yii::t('core', 'successImportFile'), Yii::t('core', 'executedQueries') . ":" . count($queries));
     // We cannot output json here, see: http://jquery.malsup.com/form/#file-upload
     Yii::app()->end($response);
 }
Exemplo n.º 5
0
 /**
  * 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));
 }