PHP SqlFormatter::splitQuery方法代碼示例

 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();
 }