function testUsePre()
{
SqlFormatter::$use_pre = false;
$actual = SqlFormatter::highlight("test");
$expected = '<span style="color: #333;">test</span>';
$this->assertEquals($actual, $expected);
SqlFormatter::$use_pre = true;
$actual = SqlFormatter::highlight("test");
$expected = '<pre style="color: black; background-color: white;"><span style="color: #333;">test</span></pre>';
$this->assertEquals($actual, $expected);
}
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);
}
}
}
/**
* Row::performTypeAnalysis()
*
* @return
*/
public function performTypeAnalysis()
{
if (!$this->cells['type']->v) {
return;
}
if ($this->cells['type']->v == 'ALL') {
$this->cells['type']->setWarning();
}
$infos = array('system' => 'The table has only one row (= system table). This is a special case of the const join type.', 'const' => "<p>The table has at most one matching row, which is read at the start of the query.\n\t\t\t\t\t\t\t\tIn the following queries, <code>{$this->cells['table']->v}</code> can be used as a const table:</p>" . \SqlFormatter::highlight("SELECT * FROM {$this->cells['table']->v} WHERE {$this->getPrimaryKey()->col_name}=1;"), 'eq_ref' => '<p>One row is read from this table for each combination of rows from the previous tables. Example:</p>' . \SqlFormatter::highlight("SELECT * FROM ref_table,{$this->cells['table']->v} WHERE ref_table.key_column={$this->cells['table']->v}.column;"), 'ref' => '<p>All rows with matching index values are read from this table for each combination of rows from the previous tables. Example:</p>' . \SqlFormatter::highlight("SELECT * FROM {$this->cells['table']->v} WHERE {$this->cells['key']->v}=expr;"), 'fulltext' => 'The join is performed using a FULLTEXT index', 'ref_or_null' => 'This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values', 'index_merge' => 'This join type indicates that the Index Merge optimization is used.
In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the
longest key parts for the indexes used. For more information, see Section 8.2.1.4, “Index Merge Optimization”', 'unique_subquery' => 'This type replaces ref for some IN subqueries of the following form:' . \SqlFormatter::highlight("value IN (SELECT primary_key FROM single_table WHERE some_expr)"), 'index_subquery' => 'This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes.', 'range' => "<p>Only rows that are in a given range are retrieved, using an index (in this query <code>{$this->cells['key']->v}</code>)\n\t\t\t\t\t\t\t\tto select the rows.</p>\n\t\t\t\t\t\t\t\t<ul><li>The <code>key</code> column in the output row indicates which index is used.</li>\n\t\t\t\t\t\t\t\t<li>The <code>key_len</code> contains the longest key part that was used</li></ul>", 'index' => 'The index join type is the same as ALL, except that the index tree is scanned', 'ALL' => 'A full table scan is done for each combination of rows from the previous tables.
This is normally <b>not good</b> if the table is the first table not marked const, and usually <b>very bad</b> in all other cases.
Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant
values or column values from earlier tables.');
$this->cells['type']->info = $infos[$this->cells['type']->v];
}
</div>
<table>
<tr>
<th>Original</th>
<th>Comments Removed</th>
</tr>
<?php
foreach ($comment_statements as $sql) {
?>
<tr>
<td>
<pre><?php
echo SqlFormatter::highlight($sql);
?>
</pre>
</td>
<td>
<pre><?php
echo SqlFormatter::highlight(SqlFormatter::removeComments($sql));
?>
</pre>
</td>
</tr>
<?php
}
?>
</table>
</body>
</html>
/**
* Return a query with the parameters replaced
*
* @param string $query
* @param array $parameters
* @param bool $highlight
*
* @return string
*/
public function replaceQueryParameters($query, array $parameters, $highlight = true)
{
$i = 0;
$result = preg_replace_callback('/\\?|((?<!:):[a-z0-9_]+)/i', function ($matches) use($parameters, &$i) {
$key = substr($matches[0], 1);
if (!array_key_exists($i, $parameters) && (false === $key || !array_key_exists($key, $parameters))) {
return $matches[0];
}
$value = array_key_exists($i, $parameters) ? $parameters[$i] : $parameters[$key];
$result = DoctrineExtension::escapeFunction($value);
$i++;
return $result;
}, $query);
if ($highlight) {
$result = \SqlFormatter::highlight($result);
$result = str_replace(array('<pre ', '</pre>'), array('<span ', '</span>'), $result);
}
return $result;
}
public function clusterTest($param)
{
$this->view = false;
foreach ($param as $server) {
$db = $this->di['db']->sql(str_replace('-', '_', $server));
$sql = "CREATE DATABASE IF NOT EXISTS `" . $server . "`;";
$db->sql_query($sql);
echo "[" . date("Y-m-d H:i:s") . "] " . $server . "> " . \SqlFormatter::highlight($sql);
}
foreach ($param as $server) {
$db = $this->di['db']->sql(str_replace('-', '_', $server));
$sql = "SHOW DATABASES";
$databases = $db->sql_fetch_yield($sql);
$i = 0;
foreach ($databases as $database) {
if (in_array($database['Database'], $param)) {
echo "[NOTICE] " . $server . "> " . $database['Database'] . " : Found !" . PHP_EOL;
$i++;
}
}
if ($i != count($param)) {
echo Color::getColoredString("[ERROR] Only {$i} DB found !", "grey", "red") . PHP_EOL;
} else {
echo Color::getColoredString("[NOTICE] {$server}> OK !", "black", "green") . PHP_EOL;
}
}
foreach ($param as $server) {
$db = $this->di['db']->sql(str_replace('-', '_', $server));
$sql = "DROP DATABASE `" . $server . "`;";
$db->sql_query($sql);
echo "[" . date("Y-m-d H:i:s") . "] " . $server . "> " . \SqlFormatter::highlight($sql);
}
foreach ($param as $server) {
$db = $this->di['db']->sql(str_replace('-', '_', $server));
$sql = "SHOW DATABASES";
$databases = $db->sql_fetch_yield($sql);
$i = 0;
foreach ($databases as $database) {
if (in_array($database['Database'], $param)) {
$i++;
}
}
if ($i != 0) {
echo Color::getColoredString("[ERROR] {$i} DB found on {$server} !", "grey", "red") . PHP_EOL;
} else {
echo Color::getColoredString("[NOTICE] {$server}> No databases found !", "black", "green") . PHP_EOL;
}
}
}
/**
* Get the value the MySQL query
*
* @param boolean $highlight Whether HTML should be used to highlight to the query
* @return string
*/
public function getQuery($highlight = false)
{
if ($highlight) {
return \SqlFormatter::highlight($this->query);
} else {
return $this->query;
}
}
public function actionPreviewSQL()
{
$postdata = file_get_contents("php://input");
$post = json_decode($postdata, true);
$criteria = @$post['criteria'] ? $post['criteria'] : [];
$params = @$post['params'] ? $post['params'] : [];
$baseClass = $post['baseclass'];
switch ($baseClass) {
case "DataGrid":
case "DataFilter":
case "RelationField":
case "TextField":
$rel = 'currentModel';
$name = $post['rfname'];
$classPath = $post['rfclass'];
$modelClassPath = $post['rfmodel'];
$modelClass = Helper::explodeLast(".", $modelClassPath);
Yii::import($modelClassPath);
$class = Helper::explodeLast(".", $classPath);
Yii::import($classPath);
$model = new $modelClass();
$builder = $model->commandBuilder;
$fb = FormBuilder::load($classPath);
$field = $fb->findField(['name' => $name]);
$rf = new RelationField();
$rf->builder = $fb;
$rf->attributes = $field;
$rf->relationCriteria = $criteria;
$rf->params = $post['params'];
$criteria = $rf->generateCriteria('', []);
$criteria = new CDbCriteria($criteria);
break;
case "DataSource":
$rel = $post['rel'];
$name = $post['dsname'];
$classPath = $post['dsclass'];
$class = Helper::explodeLast(".", $classPath);
Yii::import($classPath);
$model = new $class();
$builder = $model->commandBuilder;
$fb = FormBuilder::load($classPath);
$fb->model = new $model();
$field = $fb->findField(['name' => $name]);
$ds = new DataSource();
$ds->attributes = $field;
$criteria = DataSource::generateCriteria($params, $criteria, $ds);
$criteria = SqlCriteria::convertPagingCriteria($criteria);
$criteria = new CDbCriteria($criteria);
break;
}
if (!isset($rel)) {
echo json_encode(["sql" => '', "error" => '']);
return false;
}
$isRelated = false;
if ($rel == 'currentModel') {
$tableSchema = $model->tableSchema;
} else {
$parent = $model::model()->find();
$relMeta = $model->getMetadata()->relations[$rel];
$relClass = $relMeta->className;
if (!is_subclass_of($relClass, 'ActiveRecord')) {
throw new CException("Class {$relClass} harus merupakan subclass dari ActiveRecord");
}
$tableSchema = $relClass::model()->tableSchema;
if (!is_null($parent)) {
$parentPrimaryKey = $parent->metadata->tableSchema->primaryKey;
switch (get_class($relMeta)) {
case 'CHasOneRelation':
case 'CBelongsToRelation':
if (is_string($relMeta->foreignKey)) {
$criteria->addColumnCondition([$relMeta->foreignKey => $parent->{$parentPrimaryKey}]);
$isRelated = true;
}
break;
case 'CManyManyRelation':
$parser = new PhpParser\Parser(new PhpParser\Lexer\Emulative());
$stmts = $parser->parse('<?php ' . $relMeta->foreignKey . ';');
$bridgeTable = $stmts[0]->name->parts[0];
$arg0 = $stmts[0]->args[0]->value->name->parts[0];
$arg1 = $stmts[0]->args[1]->value->name->parts[0];
$criteria->join .= " " . $relMeta->joinType . " {$bridgeTable} ON t.{$tableSchema->primaryKey} = {$bridgeTable}.{$arg1} ";
break;
case 'CHasManyRelation':
//without through
if (is_string($relMeta->foreignKey)) {
$criteria->addColumnCondition([$relMeta->foreignKey => $parent->{$parentPrimaryKey}]);
$isRelated = true;
}
//with through
//todo..
break;
}
}
}
$command = $builder->createFindCommand($tableSchema, $criteria);
$commandText = $command->text;
if ($isRelated) {
$commandText = str_replace(":ycp0", "\n" . '"{$model->' . $relMeta->foreignKey . '}"', $commandText);
}
$commandText = SqlFormatter::highlight($commandText);
$errMsg = '';
try {
$command->queryScalar();
} catch (Exception $e) {
$errMsg = $e->getMessage();
$errMsg = str_replace("CDbCommand gagal menjalankan statement", "", $errMsg);
}
echo json_encode(["sql" => $commandText, "error" => $errMsg]);
}
public function log($sql)
{
if ($this->debug) {
$db = $this->di['db']->sql($this->link_to_purge);
$db->sql_select_db($this->schema_to_purge);
if ($this->color) {
echo \SqlFormatter::highlight($sql);
} else {
echo \SqlFormatter::format($sql, false) . PHP_EOL;
}
if ($this->color) {
echo Color::getColoredString("--Row affected : " . end($db->query)['rows'] . " - Time : " . end($db->query)['time'], 'black', 'green', 'bold') . "\n";
} else {
echo "--Row affected : " . end($db->query)['rows'] . PHP_EOL;
}
}
}
/**
* Formats and/or highlights the given SQL statement.
*
* @param string $sql
* @param bool $highlightOnly If true the query is not formatted, just highlighted
*
* @return string
*/
public function formatQuery($sql, $highlightOnly = false)
{
\SqlFormatter::$pre_attributes = 'class="highlight highlight-sql"';
\SqlFormatter::$quote_attributes = 'class="string"';
\SqlFormatter::$backtick_quote_attributes = 'class="string"';
\SqlFormatter::$reserved_attributes = 'class="keyword"';
\SqlFormatter::$boundary_attributes = 'class="symbol"';
\SqlFormatter::$number_attributes = 'class="number"';
\SqlFormatter::$word_attributes = 'class="word"';
\SqlFormatter::$error_attributes = 'class="error"';
\SqlFormatter::$comment_attributes = 'class="comment"';
\SqlFormatter::$variable_attributes = 'class="variable"';
if ($highlightOnly) {
$html = \SqlFormatter::highlight($sql);
$html = preg_replace('/<pre class=".*">([^"]*+)<\\/pre>/Us', '\\1', $html);
} else {
$html = \SqlFormatter::format($sql);
$html = preg_replace('/<pre class="(.*)">([^"]*+)<\\/pre>/Us', '<div class="\\1"><pre>\\2</pre></div>', $html);
}
return $html;
}
public function log($sql)
{
echo \SqlFormatter::highlight($sql);
}
<?php
require 'SqlFormatter.php';
$listeClesPrimaires = array('SESSION' => 'id_session', 'TENTATIVE_CONNEXION' => 'id_tentative', 'BLOCAGE_IP' => 'id_blocage');
try {
$PDO = new PDO('mysql:host=' . Config::HOTE_BDD . ';dbname=' . Config::NOM_BDD, Config::LOGIN_BDD, Config::PASSWORD_BDD, array(1002 => 'SET NAMES UTF8'));
ORM::setDb($PDO);
ORM::configure(array('return_result_sets' => TRUE, 'id_column_overrides' => $listeClesPrimaires, 'logging' => true));
} catch (PDOException $exception) {
exit('Une erreur est survenue.');
}
ORM::configure('logger', function ($requete) {
echo SqlFormatter::highlight($requete);
});
/**
* Highlight SQL syntax.
*
* @param string $sql
* @return string
*/
public function highlightSQL($sql)
{
\SqlFormatter::$pre_attributes = '';
return trim(substr(\SqlFormatter::highlight($sql), 6, -6));
}
/**
* Return a query with the parameters replaced
*
* @param string $query
* @param array $parameters
*
* @return string
*/
public function replaceQueryParameters($query, $parameters)
{
$i = 0;
$result = preg_replace_callback('/\\?|(:[a-z0-9_]+)/i', function ($matches) use($parameters, &$i) {
$key = substr($matches[0], 1);
if (!isset($parameters[$i]) && !isset($parameters[$key])) {
return $matches[0];
}
$value = isset($parameters[$i]) ? $parameters[$i] : $parameters[$key];
$result = DoctrineExtension::escapeFunction($value);
$i++;
return $result;
}, $query);
$result = \SqlFormatter::highlight($result);
$result = str_replace(array("<pre ", "</pre>"), array("<span ", "</span>"), $result);
return $result;
}
/**
* Renders HTML code for custom panel.
* @return string
*/
public function getPanel()
{
$queries = $this->queries;
$html = '<h1>' . self::$title . '</h1>';
$html .= '<div class="tracy-inner tracy-InfoPanel"><table width="300">';
\SqlFormatter::$pre_attributes = 'style="color: black;"';
foreach ($queries as $query) {
$html .= '<tr><td>' . \SqlFormatter::highlight($query) . '</td></tr>';
}
$html .= '</table></div>';
return $html;
}
