function createTableSection($data, $exclusions)
{
$description = "";
$return = "";
if (empty($data)) {
return '<p>Cannot display this information</p>';
}
$return .= '<table>';
foreach ($data as $column => $content) {
if (!in_array($column, $exclusions, TRUE) && $content) {
$column = extractColumnName($column);
if ($column === 'Description') {
$description .= '<p class="table_desc">' . $content . '</p>';
} else {
if ($column === 'Hidden property') {
$description .= '<p class="table_desc"><b>Hidden property:</b> ' . $content . '</p>';
} else {
$return .= '<tr><td class="title">' . $column . '</td><td>' . $content . '</td></tr>';
}
}
}
}
$return .= '</table>';
$return .= $description;
return $return;
}
/**
* @brief Collects all participating nodes from the SQL tree for a given table name or alias
* @param sqlTree the SQL tree which is rewritten
* @param tblDb database name
* @param tblName table name
* @param tblAlias table alias name
* @param returnArray return all the found columns into this array
* @param tableList the sorted table list
* @param recLevel current recursion level
* @param startBrach true if this is the first call to this function, false if it is in a recursion
* @return returns the number of columns that lie in a different table than the one given, -1 if none are found
*
* Counts all the columns corresponding to a given table name or alias. The columns that correspond to the table are placed
* into the returnArray. The function returns the number of columns that are different to the specified table.
*/
function PHPSQLcollectColumns($sqlSelect, $tblDb, $tblName, $tblAlias, &$returnArray, &$tableList, $recLevel, $startBranch = true)
{
$countDiffTables = 0;
$workload = array();
if (array_key_exists('SELECT', $sqlSelect)) {
$workload = $sqlSelect['SELECT'];
} else {
$workload = $sqlSelect;
}
foreach ($workload as $node) {
if (!isColref($node) && !isOperator($node)) {
$currCountDiffTables = false;
$currColArray = array();
if (hasSubtree($node)) {
$currCountDiffTables = PHPSQLcollectColumns($node['sub_tree'], $tblDb, $tblName, $tblAlias, $currColArray, $tableList, $recLevel, false);
$countDiffTables += $currCountDiffTables;
} else {
if ($recLevel === 0) {
//if this has no subtree (as do functions without parameters like foo()), then add it at the outermost possible
//place (i.e. recLeve = 0)
$currCountDiffTables = 0;
}
}
if ($startBranch === true && $currCountDiffTables === 0) {
if (!hasAlias($node)) {
$node['alias'] = createAliasNode(array(buildEscapedString(array($node))));
}
//add the correct table names to all columns that are not in the current column
if (hasSubtree($node) && count($tableList) > 0 && $recLevel !== max(array_keys($tableList))) {
$toThisTableName = extractTableAlias($tableList[$recLevel + 1]['node']);
if ($toThisTableName === false) {
$toThisTableName = extractTableName($tableList[$recLevel + 1]['node']);
}
if (hasAlias($tableList[$recLevel])) {
rewriteTableNameInSubqueries($node['sub_tree'], $toThisTableName, extractTableAlias($tableList[$recLevel]['node']));
} else {
rewriteTableNameInSubqueries($node['sub_tree'], $toThisTableName, extractTableName($tableList[$recLevel]['node']));
}
if ($node['expr_type'] !== "aggregate_function") {
$node['base_expr'] = getBaseExpr($node);
}
}
//if this is a function/aggregate that applies to a '*' "column", then only apply this at the
//outermost level (i.e. reclevel=0) and not before (see Test40 for case where this applies)
$canAddThisNode = true;
foreach ($currColArray as $column) {
if ($column['base_expr'] === "*" && $recLevel != 0) {
$canAddThisNode = false;
break;
}
}
if ($canAddThisNode === true) {
array_push($returnArray, $node);
}
}
//add columns if not yet added, but only if there are dependant columns evolved
if ($currCountDiffTables !== false && $currCountDiffTables > 0) {
foreach ($currColArray as $column) {
$found = false;
//go through the already selected columns
foreach ($returnArray as $returnColumn) {
if (columnIsEqual($returnColumn, $column)) {
$found = true;
break;
}
}
if ($found === false) {
$column['alias'] = createAliasNode(array(implodeNoQuotes($column['no_quotes'])));
array_push($returnArray, $column);
}
}
}
} else {
if (isColref($node) || $node['base_expr'] === '*' && $startBranch === true) {
$currCol = extractColumnName($node);
$currTable = extractTableName($node);
$currDB = extractDbName($node);
if ($currTable === $tblAlias || $currTable === false || $currTable === $tblName || $tblAlias === $currDB . "." . $currTable) {
array_push($returnArray, $node);
} else {
#check if this table has already been selected and processed. if yes, we can already process it and
#donot need to wait
$found = false;
foreach ($tableList as $key => $tblListNode) {
#skip everything not yet processed...
if ($key < $recLevel) {
continue;
}
if ($currTable === extractTableAlias($tblListNode['node'])) {
$found = true;
break;
}
}
if ($found === false) {
$countDiffTables += 1;
}
}
}
}
}
return $countDiffTables;
}
/**
* @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);
}
}
}
