/**
* Parse SQL query WHERE and ORDER BY clauses and validate that nothing bad is happening there
* @param string $where
* @param string $order_by
* @return bool
*/
public function validateQueryClauses($where, $order_by = '')
{
if (empty($where) && empty($order_by)) {
return true;
}
if (empty($where) && !empty($order_by)) {
$where = "deleted=0";
}
$parser = new PHPSQLParser\PHPSQLParser();
$testquery = "SELECT dummy FROM dummytable WHERE {$where}";
$clauses = 3;
if (!empty($order_by)) {
$testquery .= " ORDER BY {$order_by}";
$clauses++;
}
$parsed = $parser->parse($testquery);
if (count($parsed) != $clauses) {
// we assume: SELECT, FROM, WHERE, maybe ORDER
return false;
}
$parts = array_keys($parsed);
if ($parts[0] != "SELECT" || $parts[1] != "FROM" || $parts[2] != "WHERE") {
// check the keys to be SELECT, FROM, WHERE
return false;
}
if (!empty($order_by) && $parts[3] != "ORDER") {
// extra key is ORDER
return false;
}
// verify SELECT didn't change
if (count($parsed["SELECT"]) != 1 || $parsed["SELECT"][0] !== array('expr_type' => 'colref', 'alias' => '`dummy`', 'base_expr' => 'dummy', 'sub_tree' => false)) {
Log::debug("validation failed SELECT");
return false;
}
// verify FROM didn't change
if (count($parsed["FROM"]) != 1 || $parsed["FROM"][0] !== array('table' => 'dummytable', 'alias' => 'dummytable', 'join_type' => 'JOIN', 'ref_type' => '', 'ref_clause' => '', 'base_expr' => false, 'sub_tree' => false)) {
Log::debug("validation failed FROM");
return false;
}
// check WHERE
if (!$this->validateExpression($parsed["WHERE"], true)) {
Log::debug("validation failed WHERE");
return false;
}
// check ORDER
if (!empty($order_by) && !$this->validateExpression($parsed["ORDER"])) {
Log::debug("validation failed ORDER");
return false;
}
return true;
}
/**
* @REST\Get("/query/analyze")
*/
public function analyzeQueryTest(Request $request)
{
$query = $request->query->get('query');
$parser = new \PHPSQLParser\PHPSQLParser();
$result = $parser->parse($query);
if (false) {
return new Response(print_r($result, true), 200);
ob_start();
var_dump($result);
$dump = ob_get_clean();
return new Response($dump, 200);
} else {
$parser = new \PHPSQLParser\PHPSQLParser();
$creator = new \PHPSQLParser\PHPSQLCreator();
$tree = $parser->parse($query);
$column = 'foo';
$direction = 'asc';
$tree['ORDER'] = array(array('expr_type' => 'colref', 'base_expr' => $column, 'no_quotes' => array('delim' => false, 'parts' => array($column)), 'sub_tree' => false, 'direction' => strtoupper($direction)));
return array('query' => $creator->create($tree));
}
}
#!/usr/bin/php
<?php
$config = ['git_urls' => ['https://github.com/yfix/php-sql-parser.git' => 'php_sql_parser/'], 'autoload_config' => ['php_sql_parser/src/PHPSQLParser/' => 'PHPSQLParser'], 'example' => function () {
$parser = new \PHPSQLParser\PHPSQLParser();
$sql = '
`id` int(6) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL DEFAULT \'\',
`active` enum(\'0\',\'1\') NOT NULL DEFAULT \'0\',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
';
$parsed = $parser->parse($sql);
var_export($parsed);
}];
if ($return_config) {
return $config;
}
require_once __DIR__ . '/_yf_autoloader.php';
new yf_autoloader($config);
function process_sql($sql, $recLevel = 0, $whereSubquery = false)
{
#only useful for the fetch worker for debugging
$this->shown_temp_table_create = true;
$this->sql = $sql;
$parser = null;
$straight_join = false;
$conn = false;
$this->shard_sql = "";
#identical SQL which will be broadcast to all shards
$this->coord_sql = "";
#the summary table sql
$this->in_lists = array();
$error = array();
$select = null;
if (!is_array($sql)) {
#TODO: support parser re-use
#$this->parsed = $this->client->do('sql_parse',$sql);
$parser = new \PHPSQLParser\PHPSQLParser($sql);
$this->parsed = PHPSQLbuildShardQuery($parser->parsed, $this->headNodeTables);
$this->parsedCopy = $this->parsed;
} else {
$this->parsed = $sql;
}
if (!empty($this->parsed['UNION ALL'])) {
$queries = array();
foreach ($this->parsed['UNION ALL'] as $sub_tree) {
$this->process_sql($sub_tree, $recLevel++);
$queries = array_merge($queries, $this->shard_sql);
}
$this->table_name = "aggregation_tmp_" . mt_rand(1, 100000000);
$coord_sql = "SELECT * from " . $this->table_name;
} elseif (!empty($this->parsed['UNION'])) {
$queries = array();
foreach ($this->parsed['UNION'] as $sub_tree) {
$this->process_sql($sub_tree, $recLevel++);
$queries = array_merge($queries, $this->shard_sql);
}
$this->table_name = "aggregation_tmp_" . mt_rand(1, 100000000);
#UNION operation requires deduplication of the temporary table
$coord_sql = "SELECT DISTINCT * from " . $this->table_name;
} elseif (!empty($this->parsed['SELECT'])) {
#reset the important variables
$select = $from = $where = $group = $order_by = $order_by_coord = "";
$this->errors = array();
#we only support SQL_MODE=ONLY_FULL_GROUP_BY, and we build the GROUP BY from the SELECT expression
//unset($this->parsed['GROUP']);
#The SELECT clause is processed first.
$distinct = false;
if (!empty($this->parsed['OPTIONS'])) {
if (in_array('STRAIGHT_JOIN', $this->parsed['OPTIONS'])) {
$straight_join = true;
}
unset($this->parsed['OPTIONS']);
}
$select = $this->process_select($this->parsed['SELECT'], $recLevel, $straight_join, $whereSubquery);
if (!empty($select['error'])) {
$this->errors = $select['error'];
return false;
}
unset($this->parsed['SELECT']);
if (empty($this->parsed['FROM'])) {
// $this->errors = array('Unsupported query', 'Missing FROM clause');
// return false;
$this->table_name = "aggregation_tmp_" . mt_rand(1, 100000000);
$select['coord_sql'] .= "\nFROM `{$this->table_name}`";
} else {
$select['shard_sql'] .= "\n" . $this->process_from($this->parsed['FROM'], $recLevel);
$this->table_name = "aggregation_tmp_" . mt_rand(1, 100000000);
#we only select from a single table here
$select['coord_sql'] .= "\nFROM `{$this->table_name}`";
if (array_key_exists("USE INDEX", $this->parsed) || array_key_exists("IGNORE INDEX", $this->parsed)) {
$index = "";
if (array_key_exists("USE INDEX", $this->parsed)) {
$index .= " USE INDEX ";
$tree = $this->parsed['USE INDEX'];
unset($this->parsed['USE INDEX']);
}
if (array_key_exists("IGNORE INDEX", $this->parsed)) {
$index .= " IGNORE INDEX ";
$tree = $this->parsed['IGNORE INDEX'];
unset($this->parsed['IGNORE INDEX']);
}
if ($tree !== false) {
foreach ($tree as $node) {
$index .= $node['base_expr'] . " ";
}
}
$select['shard_sql'] .= $index;
$select['coord_sql'] .= $index;
}
unset($this->parsed['FROM']);
}
if ($this->push_where !== false && $this->push_where) {
if (!empty($this->parsed['WHERE'])) {
$this->parsed['WHERE'][] = array('expr_type' => 'operator', 'base_expr' => 'and', 'sub_tree' => "");
}
if (!$parser) {
$parser = new \PHPSQLParser\PHPSQLParser();
}
$this->messages[] = "Where clause push detected. Pushing additional WHERE condition:'" . $this->push_where . "' to each storage node.\n";
if ($this->push_where) {
foreach ($parser->process_expr_list($parser->split_sql($this->push_where)) as $item) {
$this->parsed['WHERE'][] = $item;
}
}
}
#note that this will extract inlists and store them in $this->in_lists (if inlist optimization is on)
if (!empty($this->parsed['WHERE'])) {
$where_clauses = $this->process_where($this->parsed['WHERE']);
unset($this->parsed['WHERE']);
}
if (!empty($this->parsed['ORDER'])) {
$order_by = "";
$order_by_coord = "";
foreach ($this->parsed['ORDER'] as $o) {
if ($order_by) {
$order_by .= ",";
}
if ($order_by_coord) {
$order_by_coord .= ",";
}
#check if order by arguemnt is just a number. if yes, we dont need to quote this
if (isset($o['alias']['name']) && is_numeric(trim($o['alias']['name'], "`"))) {
$o['alias']['name'] = trim($o['alias']['name'], "`");
}
if (isset($o['alias']['name']) && !empty($o['alias']['name'])) {
$order_by .= "`" . trim($o['alias']['name'], "`") . "`" . ' ' . $o['direction'];
$order_by_coord .= "`" . trim($o['alias']['name'], "`") . "`" . ' ' . $o['direction'];
} else {
$order_by .= "`" . trim($o['base_expr'], "`") . "`" . ' ' . $o['direction'];
if (isset($o['origParse']['sub_tree']) && $o['origParse']['sub_tree'] !== false) {
$base = buildEscapedString(array($o['origParse']));
} else {
$base = str_replace("`", "", getBaseExpr($o));
}
$order_by_coord .= "`" . $base . "`" . ' ' . $o['direction'];
}
}
//only do order by on shards if LIMIT statement is present - otherwise sorting has
//to be done on coordination node
if (!empty($this->parsed['LIMIT'])) {
$order_by = "ORDER BY {$order_by}";
} else {
$order_by = "";
}
$order_by_coord = "ORDER BY {$order_by_coord}";
unset($this->parsed['ORDER']);
}
#ADDED GROUP BY SUPPORT HERE
$group_by = "";
$group_by_coord = "";
if (!empty($this->parsed['GROUP'])) {
foreach ($this->parsed['GROUP'] as $g) {
if ($group_by) {
$group_by .= ",";
}
$group_by = str_replace("`", "", getBaseExpr($g));
if ($group_by_coord) {
$group_by_coord .= ",";
}
if (isset($g['origParse']['sub_tree']) && $g['origParse']['sub_tree'] !== false) {
$base = buildEscapedString(array($g['origParse']));
} else {
$base = $g['base_expr'];
}
$group_by_coord .= "`" . $base . "`";
}
$group_by = " GROUP BY {$group_by}";
$group_by_coord = " GROUP BY {$group_by_coord}";
unset($this->parsed['GROUP']);
}
$limit = "";
$limit_coord = "";
if (!empty($this->parsed['LIMIT'])) {
if ($this->parsed['LIMIT']['offset'] == "") {
$this->parsed['LIMIT']['offset'] = 0;
}
$limit .= " LIMIT {$this->parsed['LIMIT']['offset']},{$this->parsed['LIMIT']['rowcount']}";
$limit_coord .= " LIMIT {$this->parsed['LIMIT']['offset']},{$this->parsed['LIMIT']['rowcount']}";
unset($this->parsed['LIMIT']);
}
foreach ($this->parsed as $key => $clauses) {
$this->errors[] = array('Unsupported query', $key . ' clause is not supported');
}
if ($this->errors) {
return false;
}
$queries = array();
if (!empty($where_clauses)) {
foreach ($where_clauses as $where) {
$queries[] = $select['shard_sql'] . ' ' . $where . ' ' . $group_by . ' ' . $order_by . ' ' . $limit;
}
} else {
if ($order_by === "") {
$queries[] = $select['shard_sql'] . $group_by . ' ' . $limit;
} else {
$queries[] = $select['shard_sql'] . $group_by . ' ' . $order_by . ' ' . $limit;
}
}
} else {
$this->errors = array('Unsupported query', 'Missing expected clause:SELECT');
return false;
}
$this->coord_sql = $select['coord_sql'] . ' ' . $group_by_coord . ' ' . $order_by_coord . ' ' . $limit_coord;
$this->coord_odku = $select['coord_odku'];
$this->shard_sql = $queries;
$this->agg_key_cols = $select['group_aliases'];
if ($this->verbose) {
echo "-- INPUT SQL:\n{$sql}\n";
echo "\n--PARALLEL OPTIMIZATIONS:\n";
if ($this->agg_key_cols) {
echo "\n* The following projections were selected for a UNIQUE CHECK on the storage node operation:\n{$this->agg_key_cols}\n";
if ($this->coord_odku) {
echo "\n* storage node result set merge optimization enabled:\nON DUPLICATE KEY UPDATE\n" . join(",\n", $this->coord_odku) . "\n";
}
}
echo "\n";
foreach ($this->messages as $msg) {
echo "-- {$msg}\n";
}
echo "\n-- SQL TO SEND TO SHARDS:\n";
print_r($this->shard_sql);
echo "\n-- AGGREGATION SQL:\n{$this->coord_sql}" . ($this->agg_key_cols && $this->coord_odku ? "\nON DUPLICATE KEY UPDATE\n" . join(",\n", $this->coord_odku) . "\n" : "\n");
} else {
if (is_array($sql)) {
$this->outputString .= "-- INPUT SQL:\nArray\n";
} else {
$this->outputString .= "-- INPUT SQL:\n{$sql}\n";
}
$this->outputString .= "\n--PARALLEL OPTIMIZATIONS:\n";
if ($this->agg_key_cols) {
$this->outputString .= "\n* The following projections were selected for a UNIQUE CHECK on the storage node operation:\n{$this->agg_key_cols}\n";
if ($this->coord_odku) {
$this->outputString .= "\n* storage node result set merge optimization enabled:\nON DUPLICATE KEY UPDATE\n" . join(",\n", $this->coord_odku) . "\n";
}
}
$this->outputString .= "\n";
foreach ($this->messages as $msg) {
$this->outputString .= "-- {$msg}\n";
}
$this->outputString .= "\n-- SQL TO SEND TO SHARDS:\n";
$tmpReturn = print_r($this->shard_sql, true);
//foreach($tmpReturn as $line)
$this->outputString .= $tmpReturn . "\n";
$this->outputString .= "\n-- AGGREGATION SQL:\n{$this->coord_sql}" . ($this->agg_key_cols && $this->coord_odku ? "\nON DUPLICATE KEY UPDATE\n" . join(",\n", $this->coord_odku) . "\n" : "\n");
}
return true;
}
