function testSplitQueryEmpty()
{
$sql = "SELECT 1;SELECT 2;\n-- This is a comment\n;SELECT 3";
$expected = array("SELECT 1;", "SELECT 2;", "SELECT 3");
$actual = SqlFormatter::splitQuery($sql);
$this->assertEquals($expected, $actual);
}
/**
* @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;
}
/**
* @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;
}
<tr>
<th>Original</th>
<th>Split</th>
</tr>
<?php
foreach ($split_statements as $sql) {
?>
<tr>
<td>
<pre><?php
echo SqlFormatter::highlight($sql);
?>
</pre>
</td>
<td><?php
$queries = SqlFormatter::splitQuery($sql);
echo "<ol>";
foreach ($queries as $query) {
echo "<li><pre>" . SqlFormatter::highlight($query) . "</pre></li>";
}
echo "</ol>";
?>
</td>
</tr>
<?php
}
?>
</table>
<h1>Removing Comments</h1>
public static function run(&$report)
{
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
//expand macros in query
$sql = PhpReports::render($report->raw_query, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
$datasets = array();
$explicit_datasets = preg_match('/--\\s+@dataset(\\s*=\\s*|\\s+)true/', $sql);
foreach ($queries as $i => $query) {
$is_last = $i === count($queries) - 1;
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = mysql_query($query, $report->conn);
if (!$result) {
throw new Exception("Query failed: " . mysql_error($report->conn));
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if (mysql_fetch_assoc($result)) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
// If this query should be included as a dataset
if (!$explicit_datasets && $is_last || preg_match('/--\\s+@dataset(\\s*=\\s*|\\s+)true/', $query)) {
$dataset = array('rows' => array());
while ($row = mysql_fetch_assoc($result)) {
$dataset['rows'][] = $row;
}
// Get dataset title if it has one
if (preg_match('/--\\s+@title(\\s*=\\s*|\\s+)(.*)/', $query, $matches)) {
$dataset['title'] = $matches[2];
}
$datasets[] = $dataset;
}
}
return $datasets;
}
public static function run(&$report)
{
$report->conn->SetFetchMode(ADODB_FETCH_ASSOC);
$rows = array();
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
//expand macros in query
$sql = PhpReports::render($report->raw_query, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
foreach ($queries as $query) {
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = $report->conn->Execute($query);
if (!$result) {
throw new Exception("Query failed: " . $report->conn->ErrorMsg());
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if ($result->GetAssoc()) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
}
return $result->GetArray();
}
public static function run(&$report)
{
$report->conn->SetFetchMode(ADODB_FETCH_ASSOC);
$rows = array();
$macros = $report->macros;
foreach ($macros as $key => $value) {
if (is_array($value)) {
$first = true;
foreach ($value as $key2 => $value2) {
$value[$key2] = mysql_real_escape_string(trim($value2));
$first = false;
}
$macros[$key] = $value;
} else {
$macros[$key] = mysql_real_escape_string($value);
}
if ($value === 'ALL') {
$macros[$key . '_all'] = true;
}
}
//add the config and environment settings as macros
$macros['config'] = PhpReports::$config;
$macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];
$raw_sql = "";
foreach ($report->raw_query as $qry) {
if (is_array($qry)) {
foreach ($qry as $key => $value) {
// TODO handle arrays better
if (!is_bool($value) && !is_array($value)) {
$qry[$key] = PhpReports::renderString($value, $macros);
}
}
//TODO This sux - need a class or something :-)
$raw_sql .= PivotTableSQL($report->conn, $qry['tables'], $qry['rows'], $qry['columns'], $qry['where'], $qry['orderBy'], $qry['limit'], $qry['agg_field'], $qry['agg_label'], $qry['agg_fun'], $qry['include_agg_field'], $qry['show_count']);
} else {
$raw_sql .= $qry;
}
}
//expand macros in query
$sql = PhpReports::render($raw_sql, $macros);
$report->options['Query'] = $sql;
$report->options['Query_Formatted'] = SqlFormatter::format($sql);
//split into individual queries and run each one, saving the last result
$queries = SqlFormatter::splitQuery($sql);
foreach ($queries as $query) {
if (!is_array($query)) {
//skip empty queries
$query = trim($query);
if (!$query) {
continue;
}
$result = $report->conn->Execute($query);
if (!$result) {
throw new Exception("Query failed: " . $report->conn->ErrorMsg());
}
//if this query had an assert=empty flag and returned results, throw error
if (preg_match('/^--[\\s+]assert[\\s]*=[\\s]*empty[\\s]*\\n/', $query)) {
if ($result->GetAssoc()) {
throw new Exception("Assert failed. Query did not return empty results.");
}
}
}
}
return $result->GetArray();
}
