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