PHP SqlFormatter类代码示例

 private static function runQueryWithView($query, $fields, $printArray)
 {
     $_SESSION['tableData'] = array();
     $exec_time_row = array();
     $records = '';
     try {
         // turn on query profiling
         Flight::get('db')->query('SET profiling = 1;');
         $stmt = Flight::get('db')->query($query);
         // find out time above query was ran for
         $exec_time_result = Flight::get('db')->query('SELECT query_id, SUM(duration) FROM information_schema.profiling GROUP BY query_id ORDER BY query_id DESC LIMIT 1;');
         $exec_time_row = $exec_time_result->fetchAll(PDO::FETCH_NUM);
         // run query and fetch array
         $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
         // store table fields/columns + data rows in session for exporting later
         $_SESSION['tableData'] = array_merge($fields, $data);
         $records = Presenter::listTableData($data);
     } catch (PDOException $e) {
         setFlashMessage('Error: ' . $e->getMessage());
     }
     Flight::render('table', array('title' => Flight::get('lastSegment'), 'icon' => self::$icon, 'table_data' => $records, 'fields' => getOptions($fields), 'query' => SqlFormatter::format($query), 'printArray' => $printArray, 'timetaken' => $exec_time_row[0][1]));
 }

 /**
  * @test
  */
 public function leftJoinStartsOnNewLine()
 {
     $sql = "SELECT a\nFROM t LEFT JOIN b ON t.a  = b.c WHERE b = c";
     $expected = "SELECT a\nFROM t\nLEFT JOIN b ON t.a  = b.c\nWHERE b = c";
     $o = new SqlFormatter();
     $actual = $o->format($sql);
     $this->assertEquals($expected, $actual);
 }

 public function collect(Request $request, Response $response, \Exception $exception = null)
 {
     $this->data = array('queries' => $this->database->getQueries(), 'queryCount' => count($this->database->getQueries()));
     foreach ($this->data['queries'] as &$query) {
         $query['query_formatted'] = \SqlFormatter::format($query['query']);
     }
 }

 /**
  * @param IQuery $query
  * @param bool $highlight
  * @return String
  * @throws Exception
  */
 public function format(IQuery $query, $highlight = false)
 {
     if (!class_exists('\\SqlFormatter')) {
         throw new Exception('Coult not find the SqlFormatter class by jdorn');
     }
     return \SqlFormatter::format($this->render($query), $highlight);
 }

 function setupTextArea($sql)
 {
     if ($sql == 'HIDE') {
         $this->sugar_smarty->assign("HIGHLIGHTED_SQL", "");
         $this->sugar_smarty->assign("SHOWTEXTAREA", 'none');
         $this->sugar_smarty->assign("HIDEEDITBUTTON", true);
         $parser = new PHPSQLParser($sql);
         $finalParser = $this->finalSQLParse($parser->parsed, "", 0);
     } elseif (!empty($sql)) {
         require_once 'custom/modules/Administration/SweetDBAdmin/sql-formatter/lib/SqlFormatter.php';
         $this->sugar_smarty->assign("HIGHLIGHTED_SQL", SqlFormatter::format($sql));
         $this->sugar_smarty->assign("SHOWTEXTAREA", 'none');
         $parser = new PHPSQLParser($sql);
         $finalParser = $this->finalSQLParse($parser->parsed, "", 0);
     } else {
         $this->sugar_smarty->assign("SHOWTEXTAREA", 'block');
     }
     if (isset($finalParser['TABLE']) || isset($finalParser['FROM'])) {
         if (isset($finalParser['TABLE'])) {
             $this->sugar_smarty->assign("TABLE", $finalParser['TABLE']);
         } else {
             if (count($finalParser['FROM']) == 1) {
                 $this->sugar_smarty->assign("TABLE", $finalParser['FROM'][0]['table']);
             }
         }
     }
 }

/**
 * @var \Zend\Db\Adapter\Driver\Pdo\Result $results
 * @var \Zend\Db\Adapter\Driver\StatementInterface $statement
 */
function renderResults(Result $results, StatementInterface $statement = null)
{
    $headers = [];
    $queryInformation = null;
    $outputData = null;
    $resultContents = null;
    if ($statement) {
        $queryInformation = SqlFormatter::format($statement->getSql());
        if ($statement->getParameterContainer()->count()) {
            $queryInformation .= createTable(array_keys($statement->getParameterContainer()->getNamedArray()), [array_values($statement->getParameterContainer()->getNamedArray())]);
        }
    }
    if ($results->count()) {
        foreach ($results as $result) {
            $headers = array_keys($result);
            $outputData[] = $result;
        }
    }
    // Results
    if ($outputData) {
        $resultContents = createTable([$headers], $outputData);
    }
    // Wrapper Table
    $table = new Table(new ConsoleOutput());
    $table->setHeaders([['Query Results', 'Generated SQL']])->setRows([[$resultContents, $queryInformation]])->render();
}

 public function view()
 {
     /* @var $view \Doctrine\DBAL\Schema\View */
     $view = $this->__view;
     $this->view_name = $view->getName();
     $this->set('view_sql', \SqlFormatter::format($view->getSql()), false);
 }

 public function disp_val()
 {
     if (Rend::$sql_pretty) {
         return \SqlFormatter::format($this->raw, false);
     } else {
         return parent::disp_val();
     }
 }

 /**
  * @return bool
  * @throws ValidationFailedException
  */
 public function validate()
 {
     $queryArray = \SqlFormatter::splitQuery($this->queryString);
     $pattern = '/\\*/';
     if ($this->stringHasRegexInstance($queryArray, $pattern)) {
         throw new ValidationFailedException("Query has to have all wished columns defined, no '*' is allowed", 500);
     }
     return true;
 }

 /**
  * @param OutputInterface $output
  */
 protected function showCreates($output)
 {
     $creates = $this->app['schema.comparator']->getCreates();
     if ($creates) {
         $output->writeln('<info>Tables to be created:</info>');
         foreach ($creates as $tableName => $sql) {
             $output->writeln("\n");
             $output->writeln(\SqlFormatter::format($sql[0]));
             $output->writeln("\n");
         }
     }
 }

 /**
  * Internal helper function which returns all traced doctrine queries
  * which executed over the Shopware()->Models() manager.
  *
  * @return array
  */
 public function getDoctrineQueries()
 {
     $queries = array();
     /**@var $logger \Doctrine\DBAL\Logging\DebugStack */
     $logger = Shopware()->Models()->getConfiguration()->getSQLLogger();
     foreach ($logger->queries as $query) {
         $explain = $this->getSqlExplain($query['sql'], $query['params']);
         $sql = $this->getQuerySql($query['sql']);
         $this->sqlTime += $query['executionMS'];
         $queries[] = array('sql' => SqlFormatter::format($sql), 'short' => $this->getShortSql($sql), 'explain' => $explain, 'status' => $this->getQueryStatus($explain), 'params' => $query['params'], 'time' => number_format($query['executionMS'], 5));
     }
     return $queries;
 }

 /**
  * {@inheritdoc}
  */
 public function startQuery($sql, array $params = null, array $types = null)
 {
     $this->start = microtime(true);
     $replacedSql = $sql;
     if ($params) {
         foreach ($params as $param => $value) {
             $value = is_string($value) ? "'" . $value . "'" : $value;
             $replacedSql = str_replace($param, $value, $replacedSql);
         }
     }
     echo SqlFormatter::format($replacedSql) . '</br></br>';
     parent::startQuery($sql, $params, $types);
 }

function print_sql($query)
{
    if ($GLOBALS['cli']) {
        print_vars($query);
    } else {
        if (class_exists('SqlFormatter')) {
            // Hide it under a "database icon" popup.
            #echo overlib_link('#', '<i class="oicon-databases"> </i>', SqlFormatter::highlight($query));
            echo '<p>', SqlFormatter::highlight($query), '</p>';
        } else {
            print_vars($query);
        }
    }
}

 /**
  * execute
  *
  * Action attached to the query listener.
  * It attaches each new query in a query stack.
  *
  * @access public
  * @param  name     event name
  * @param  array    $data
  * @param  Session  $session
  * @return null
  */
 public function execute($name, $data, Session $session)
 {
     if (!in_array($name, array('query:pre', 'query:post'))) {
         return;
     }
     if ('query:post' === $name) {
         end($this->queries);
         $key = key($this->queries);
         reset($this->queries);
         $this->queries[$key] += $data;
         return;
     }
     $this->queries[] = ['sql' => \SqlFormatter::format($data['sql'], true), 'parameters' => $data['parameters']];
 }

 /**
  * @return bool
  * @throws ValidationFailedException
  */
 public function validate()
 {
     $arrayOfQueries = \SqlFormatter::splitQuery($this->queryString);
     $queryString = array_map("strtolower", $arrayOfQueries);
     $tableNames = array_map("strtolower", $this->limits["table_names"]);
     $tableNamesString = $this->getTableNamesStringForRegex($tableNames);
     $regex = "/(from" . ValidTableNameValidator::ANY_SPACE_META_SEQUENCE_PATTERN . "({$tableNamesString}))/";
     $hasJoinCommand = "/join/";
     if ($this->stringHasRegexInstance($queryString, $hasJoinCommand)) {
         $regexJoinCommand = "/(join" . ValidTableNameValidator::ANY_SPACE_META_SEQUENCE_PATTERN . "({$tableNamesString}))/";
         $result = $this->stringHasRegexInstance($queryString, $regex) && $this->stringHasRegexInstance($queryString, $regexJoinCommand);
         if (!$result) {
             throw new ValidationFailedException("The given query does not contain a valid table in the Join statement.", 500);
         }
         return $result;
     }
     if (!$this->stringHasRegexInstance($queryString, $regex)) {
         $helper = "<em>Check that the tables specify the schema</em>";
         throw new ValidationFailedException("<p>The given query does not contain a valid table.</p>{$helper}", 500);
     }
     return true;
 }