function PHPSQLbuildShardQuery($sqlTree, $headNodeTables = array())
{
#check for subqueries
#this handles nested subqueries that the user already provided. no idea how to handle these
#together with the automatic joins found below...
//before we start with anything, we are going to rewrite any possitional argument in order by
//with the corresponding given column
$sqlTree = PHPSQLresolvePositionalArguments($sqlTree);
$subQueries = array();
if (!empty($sqlTree['FROM'])) {
$subQueries = collectNodes($sqlTree['FROM'], "subquery");
}
if (!empty($sqlTree['WHERE'])) {
$subQueries = array_merge($subQueries, collectNodes($sqlTree['WHERE'], "subquery"));
}
foreach ($subQueries as &$subQuery) {
$subQuery['table'] = "DEPENDENT-SUBQUERY";
$subQuery['sub_tree'] = PHPSQLbuildShardQuery($subQuery['sub_tree']);
}
$newSqlTree = PHPSQLGroupWhereTerms($sqlTree);
$listOfTables = array();
PHPSQLGroupTablesAndCols($newSqlTree, $listOfTables);
$dependantList = PHPSQLGroupWhereCond($newSqlTree, $listOfTables);
PHPSQLCountWhereConditions($listOfTables);
$listOfTables = PHPSQLdetStartTable($listOfTables, $headNodeTables, $dependantList);
$nestedQuery = PHPSQLbuildNestedQuery($newSqlTree, $listOfTables, $dependantList, 0);
#link subqueries
linkSubqueriesToTree($nestedQuery, $subQueries);
linkNestedWheresToTree($nestedQuery, $subQueries);
return $nestedQuery;
}
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;
}
