function collectNodes($node, $expr_type)
{
$return = array();
//is this a leave, wrap it in an array, so that the code below
//can be kept simple
if (array_key_exists("expr_type", $node)) {
$node = array($node);
}
foreach ($node as $subNode) {
if (!is_array($subNode)) {
continue;
}
if (!isset($subNode['expr_type'])) {
$return = array_merge($return, collectNodes($subNode, $expr_type));
continue;
}
if (!empty($subNode['sub_tree'])) {
$return = array_merge($return, collectNodes($subNode['sub_tree'], $expr_type));
}
if ($subNode['expr_type'] === $expr_type) {
$return[] = $subNode;
}
}
return $return;
}
/**
* @brief Find all the columns in a SQL query and save them in the tableList with the according table
* @param sqlTree SQL query tree
* @param listOfTables list of tables to save the columns to
*
* Find all the columns in a SQL query and save them in the tableList with the according table.
*/
function PHPSQLGroupTablesAndCols($sqlTree, &$listOfTables)
{
$selectTree = $sqlTree['SELECT'];
if (empty($sqlTree['FROM'])) {
return;
}
$fromTree = $sqlTree['FROM'];
foreach ($fromTree as $currTable) {
$table = array();
if (isTable($currTable)) {
$table['name'] = $currTable['table'];
$table['no_quotes'] = $currTable['no_quotes'];
} else {
if (isSubquery($currTable)) {
$table['name'] = "DEPENDENT-SUBQUERY";
$table['expr_type'] = "subquery";
$table['no_quotes'] = false;
} else {
throw new Exception("Unsupported clause in FROM");
}
}
$table['alias'] = $currTable['alias'];
$table['node'] = $currTable;
$table['sel_columns'] = array();
array_push($listOfTables, $table);
}
//put dependant queries at the end of the list
$currIndex = count($listOfTables) - 1;
foreach ($listOfTables as $key => $node) {
if ($node['name'] == 'DEPENDENT-SUBQUERY' && $key < $currIndex) {
$tmpNode = $listOfTables[$currIndex];
$listOfTables[$currIndex] = $node;
$listOfTables[$key] = $tmpNode;
$currIndex--;
}
}
//link the columns with the tables
foreach ($selectTree as $node) {
$columnsInNode = collectNodes($node, "colref");
foreach ($columnsInNode as $column) {
foreach ($listOfTables as &$table) {
if (isColumnInTable($column, $table)) {
array_push($table['sel_columns'], $column);
break;
}
}
}
}
}
