/**
* @brief Adds the SELECT clause to a given node from the SQL tree
* @param sqlTree the SQL tree which is rewritten
* @param table currently treated table
* @param toThisNode the node where the SELECT clause is added to
* @param tableList the sorted table list
* @param recLevel current recursion level
*
* Takes the SELECT clause from sqlTree and adds it to the current node.
*/
function PHPSQLaddOuterQuerySelect(&$sqlTree, &$table, &$toThisNode, $tableList, $recLevel)
{
if (!array_key_exists('SELECT', $sqlTree)) {
return;
}
#if the SELECT key does not exist in the target tree, create it
if (!array_key_exists('SELECT', $toThisNode)) {
$toThisNode['SELECT'] = array();
}
if (hasAlias($table)) {
$tblAlias = extractTableAlias($table['node']);
} else {
$tblAlias = false;
}
$tblDb = extractDbName($table['node']);
$tblName = extractTableName($table['node']);
#collect all columns from the sql tree
$partList = array();
PHPSQLcollectColumns($sqlTree, $tblDb, $tblName, trim($tblAlias, '`'), $partList, $tableList, $recLevel);
foreach ($partList as $node) {
$nodeCopy = $node;
$nodeCopy['base_expr'] = getBaseExpr($nodeCopy);
if (!hasAlias($nodeCopy) && isColref($nodeCopy) && $nodeCopy['base_expr'] !== "*") {
$nodeCopy['alias'] = createAliasNode(array(implodeNoQuotes($nodeCopy['no_quotes'])));
}
array_push($toThisNode['SELECT'], $nodeCopy);
$key = array_search($node, $sqlTree['SELECT']);
unset($sqlTree['SELECT'][$key]);
}
//if this is recLevel = 0, add all remaining columns as well
if ($recLevel == 0) {
foreach ($sqlTree['SELECT'] as $node) {
//only colrefs are not yet considered here, remaining stuff has been caputred by collectColumns
if (isColref($node)) {
array_push($toThisNode['SELECT'], $node);
}
}
$sqlTree['SELECT'] = array();
}
#in order for the joins to work properly, collect all the variables that
#somehow participate in the where
if (empty($sqlTree['WHERE'])) {
return;
}
$tmpList1 = PHPSQLgetAllColsFromWhere($sqlTree['WHERE'], $tblAlias, false);
$tmpList2 = PHPSQLgetAllColsFromWhere($sqlTree['WHERE'], $tblName, true);
//this handles (crudely) the case, when no alias is given to the DB
if ($table['name'] === extractTableAlias($table['node'])) {
$tmpList3 = PHPSQLgetAllColsFromWhere($sqlTree['WHERE'], "", true);
$tmpList2 = array_merge($tmpList2, $tmpList3);
}
if ($tblAlias === $tblName) {
$listOfWhereCols = $tmpList1;
} else {
$listOfWhereCols = array_merge($tmpList1, $tmpList2);
}
foreach ($listOfWhereCols as $cols) {
$find = false;
foreach ($toThisNode['SELECT'] as &$node) {
if (columnIsEqual($node, $cols)) {
$find = true;
break;
}
}
if ($find === false) {
#mark this select as comming from a where
$cols['where_col'] = true;
$cols['base_expr'] = getBaseExpr($cols);
if (!hasAlias($cols) && isColref($cols) && $cols['base_expr'] !== "*") {
$cols['alias'] = createAliasNode(array(implodeNoQuotes($cols['no_quotes'])));
}
array_push($toThisNode['SELECT'], $cols);
}
}
}
/**
* @brief Add all columns to the SELECT tree
* @param sqlTree SQL parser tree node of complete query/subquery
* @param mysqlConn a properly initialised MySQLI/MySQLII connection to the DB
* @param zendAdapter a valid ZEND DB adapter
*
* This function will evaluate the all the tables that need SQL * attribute substitution.
* The database is queried to retrieve a complete list of columns of each table and the
* approperiate SELECT colref nodes are added to the SQL parser tree. The SQL * attribute
* is removed from the sqlTree SELECT node.
*/
function _parseSqlAll_SELECT(&$sqlTree, $mysqlConn = false, $zendAdapter = false)
{
if (!is_array($sqlTree) || !array_key_exists('SELECT', $sqlTree)) {
return;
}
$table = false;
$selectCpy = $sqlTree['SELECT'];
$sqlTree['SELECT'] = array();
foreach ($selectCpy as &$node) {
if (strpos($node['base_expr'], "*") !== false && $node['sub_tree'] === false) {
//we have found an all operator and need to find the corresponding
//table to look things up
$tableFullName = false;
$dbName = extractDbName($node);
$tableName = extractTableName($node);
$colName = extractColumnName($node);
if ($dbName !== false) {
$tableFullName = "`" . $dbName . "`.`" . $tableName . "`";
} else {
if ($tableName !== false) {
$tableFullName = "`" . $tableName . "`";
}
}
$table = array();
$alias = array();
if ($tableFullName === false) {
//add everything *ed from all tables to this query
foreach ($sqlTree['FROM'] as $fromNode) {
if (isTable($fromNode)) {
$table[] = $fromNode['table'];
if (!hasAlias($fromNode)) {
$alias[] = $fromNode['table'];
} else {
$alias[] = $fromNode['alias']['name'];
}
} else {
if (isSubquery($fromNode)) {
//handle subqueries...
_parseSqlAll_linkSubquerySELECT($fromNode['sub_tree'], $sqlTree, $fromNode['alias']['name']);
}
}
}
} else {
foreach ($sqlTree['FROM'] as $fromNode) {
//it could be, that the table here is actually another aliased table (which should
//have been processed here already, since SELECT is called last) -> link to tree
if (isTable($fromNode)) {
if (hasAlias($fromNode)) {
if (trim($fromNode['alias']['name'], "`") === $tableName) {
$table[] = $fromNode['table'];
break;
}
} else {
if ($fromNode['table'] === $tableFullName) {
$table[] = $fromNode['table'];
break;
}
}
} else {
if (isSubquery($fromNode)) {
if (trim($fromNode['alias']['name'], "`") === $tableName) {
_parseSqlAll_linkSubquerySELECT($fromNode['sub_tree'], $sqlTree, $tableName);
continue 2;
}
}
}
}
$alias[] = $tableFullName;
}
if (empty($table)) {
continue;
}
//now that we know the table, we need to look up what is in there
foreach (array_keys($table) as $key) {
if ($mysqlConn !== false) {
_parseSqlAll_getColsMysqlii($sqlTree, $node, $mysqlConn, $table[$key], $alias[$key]);
}
if ($zendAdapter !== false) {
_parseSqlAll_getColsZend($sqlTree, $node, $zendAdapter, $table[$key], $alias[$key]);
}
}
} else {
array_push($sqlTree['SELECT'], $node);
}
}
}
