function executeSelectQuery(&$sqlQuery)
{
global $g_sqlGroupingFuncs;
global $g_sqlSingleRecFuncs;
$resultSets = array();
// create a copy
$aliases = $sqlQuery->colAliases;
$funcs = $sqlQuery->colFuncs;
// Read all Tables
for ($i = 0; $i < count($sqlQuery->tables); ++$i) {
debug_printb("<br>[executeSelectQuery] Reading table " . $sqlQuery->tables[$i] . "<br>");
if (!($resultSets[$i] = $this->readTable($sqlQuery->tables[$i]))) {
print_error_msg("Reading Table " . $sqlQuery->tables[$i] . " failed");
return false;
}
$resultSets[$i]->setColumnTableForAll($sqlQuery->tables[$i]);
$resultSets[$i]->setColumnTableAliasForAll($sqlQuery->tableAliases[$i]);
// set all functions to the ResultSet of the current table
// if table and column name matches
debug_printb("[executeSelectQuery] Setting functions for the current table:<br>");
for ($j = 0; $j < count($funcs); ++$j) {
if (!$funcs[$j] || !$sqlQuery->colNames[$j]) {
continue;
}
if ($sqlQuery->colTables[$j] == $sqlQuery->tables[$i] || $sqlQuery->colTables[$j] == $sqlQuery->tableAliases[$i]) {
$colNr = $resultSets[$i]->findColNrByAttrs($sqlQuery->colNames[$j], $sqlQuery->colTables[$j], "");
if ($colNr == NOT_FOUND) {
continue;
}
// create a new column for each function
$resultSets[$i]->addColumn($sqlQuery->colNames[$j], $sqlQuery->colAliases[$j], $sqlQuery->colTables[$j], "", "str", "", $funcs[$j], "", true);
$funcs[$j] = "";
}
}
// set all aliases where table, column name and function matches
debug_printb("[executeSelectQuery] Setting aliases for the current table:<br>");
for ($j = 0; $j < count($aliases); ++$j) {
if (!$aliases[$j]) {
continue;
}
if ($sqlQuery->colTables[$j] == $sqlQuery->tables[$i] || $sqlQuery->colTables[$j] == $sqlQuery->tableAliases[$i]) {
if (($colNr = $resultSets[$i]->findColNrByAttrs($sqlQuery->colNames[$j], $sqlQuery->colTables[$j], $sqlQuery->colFuncs[$j])) != NOT_FOUND) {
$resultSets[$i]->setColumnAlias($colNr, $aliases[$j]);
$aliases[$j] = "";
}
}
}
if (TXTDBAPI_DEBUG) {
debug_printb("<br>[executeSelectQuery] Dump of Table {$i} (" . $sqlQuery->tables[$i] . "):<br>");
$resultSets[$i]->dump();
}
}
// set remaining functions to the ResultSet where column name matches
debug_printb("[executeSelectQuery] Setting remaining functions where column name matches:<br>");
for ($i = 0; $i < count($resultSets); ++$i) {
for ($j = 0; $j < count($funcs); ++$j) {
if (!$funcs[$j] || !$sqlQuery->colNames[$j]) {
continue;
}
$colNr = $resultSets[$i]->findColNrByAttrs($sqlQuery->colNames[$j], "", "");
if ($colNr == NOT_FOUND) {
// 'text' or 123 ? => add column
if (!(is_numeric($sqlQuery->colNames[$j]) || has_quotes($sqlQuery->colNames[$j]))) {
continue;
}
debug_print("Adding function with quoted string or number paremeter!<br>");
}
// create a new column for each function
$resultSets[$i]->addColumn($sqlQuery->colNames[$j], $sqlQuery->colAliases[$j], $sqlQuery->colTables[$j], "", "str", "", $funcs[$j], "", true);
$funcs[$j] = "";
}
}
// set remaining aliases where column name and function matches
debug_printb("[executeSelectQuery] Setting remaining aliases where column name and function matches:<br>");
for ($i = 0; $i < count($resultSets); ++$i) {
for ($j = 0; $j < count($aliases); ++$j) {
if (!$aliases[$j]) {
continue;
}
if (($colNr = $resultSets[$i]->findColNrByAttrs($sqlQuery->colNames[$j], "", $sqlQuery->colFuncs[$j])) != NOT_FOUND) {
$resultSets[$i]->setColumnAlias($colNr, $aliases[$j]);
$aliases[$j] = "";
}
}
}
debug_printb("[executeSelectQuery] Executing single-rec functions (on the separate ResultSet's):<br>");
// execute single-rec functions (on the separate ResultSet's)
for ($i = 0; $i < count($resultSets); ++$i) {
$resultSets[$i]->executeSingleRecFuncs();
}
// A query without tables ? => make a dummy ResultSet
$dummyResultSet = false;
if (count($sqlQuery->tables) == 0) {
$dummyResultSet = true;
$rsMaster = new ResultSet();
$rsMaster->addColumn("(dummy)", "(dummy)", "(dummy)", "(dummy)", "str", "(dummy)", "", "", true);
$rsMaster->append();
// else: real ResultSet
} else {
$dummyResultSet = false;
// join the ResultSet's
$rsMaster = $resultSets[0];
for ($i = 1; $i < count($resultSets); ++$i) {
$rsMaster = $rsMaster->joinWithResultSet($resultSets[$i]);
}
}
// from here we only work with $rsMaster and can free the other ResultSet's
unset($resultSets);
$resultSets = "";
// generate additional columns for the remaining functions (functions without params)
for ($i = 0; $i < count($funcs); ++$i) {
if ($funcs[$i]) {
$rsMaster->addColumn($sqlQuery->colNames[$i], $sqlQuery->colAliases[$i], "", "", "str", "", $funcs[$i], execFunc($funcs[$i], ""));
}
}
// generate additional columns from the WHERE-expression
$rsMaster->generateAdditionalColumnsFromWhereExpr($sqlQuery->where_expr);
// generate additional columns from ORDER BY
$rsMaster->generateAdditionalColumnsFromArray($sqlQuery->orderColumns);
// generate additional columns from GROUP BY
$rsMaster->generateAdditionalColumnsFromArray($sqlQuery->groupColumns);
// execute the new single-rec functions (on the Master ResultSet)
$rsMaster->executeSingleRecFuncs();
// set row id's
$rsMaster->reset();
$rId = -1;
while (++$rsMaster->pos < count($rsMaster->rows)) {
$rsMaster->rows[$rsMaster->pos]->id = ++$rId;
}
--$rsMaster->pos;
debug_printb("<br>[executeSelectQuery] Master ResultSet:</b><br>");
if (TXTDBAPI_DEBUG) {
$rsMaster->dump();
}
// apply WHERE expression
if ($sqlQuery->where_expr) {
$ep = new ExpressionParser();
$rsMaster = $ep->getFilteredResultSet($rsMaster, $sqlQuery);
}
// free $ep
unset($ep);
$ep = "";
// stop if the WHERE expression failed
if (txtdbapi_error_occurred()) {
return false;
}
// check if we can use some optimization
// (use the limit in group by, but only if there are no grouping function
// in the groupRows. To be able to do this we must order before grouping)
$optimizedPath = true;
if (!$sqlQuery->limit || !$sqlQuery->orderColumns) {
$optimizedPath = false;
} else {
for ($i = 0; $i < count($sqlQuery->colFuncs); ++$i) {
if (in_array($sqlQuery->colFuncs[$i], $g_sqlGroupingFuncs)) {
$optimizedPath = false;
break;
}
}
}
if ($optimizedPath) {
debug_printb("[executeSelectQuery] Using optimized path!<br>");
} else {
debug_printb("[executeSelectQuery] Using normal path!<br>");
}
// Order ResultSet (if optimizedPath)
if ($optimizedPath) {
debug_printb("[executeSelectQuery] Calling orderRows() (optimized path)..<br>");
if (count($sqlQuery->orderColumns) > 0) {
$rsMaster->orderRows($sqlQuery->orderColumns, $sqlQuery->orderTypes);
}
}
// Group ResultSet (process GROUP BY)
$numGroupingFuncs = 0;
for ($i = 0; $i < count($sqlQuery->colFuncs); ++$i) {
if ($sqlQuery->colFuncs[$i] && in_array($sqlQuery->colFuncs[$i], $g_sqlGroupingFuncs)) {
++$numGroupingFuncs;
break;
}
}
if ($numGroupingFuncs > 0 || count($sqlQuery->groupColumns) > 0) {
debug_printb("[executeSelectQuery] Calling groupRows()..<br>");
$rsMaster = $rsMaster->groupRows($sqlQuery, $optimizedPath);
}
// Order ResultSet (if NOT optimizedPath)
if (!$optimizedPath) {
debug_printb("[executeSelectQuery] Calling orderRows() (normal path)..<br>");
if (count($sqlQuery->orderColumns) > 0) {
$rsMaster->orderRows($sqlQuery->orderColumns, $sqlQuery->orderTypes);
}
}
// add direct value columns
debug_printb("[executeSelectQuery] Adding direct value columns..<br>");
for ($i = 0; $i < count($sqlQuery->colNames); ++$i) {
if ($sqlQuery->colNames[$i] && (is_numeric($sqlQuery->colNames[$i]) || has_quotes($sqlQuery->colNames[$i])) && !$sqlQuery->colTables[$i] && !$sqlQuery->colFuncs[$i] && $rsMaster->findColNrByAttrs($sqlQuery->colNames[$i], "", "") == NOT_FOUND) {
$value = $sqlQuery->colNames[$i];
if (has_quotes($value)) {
remove_quotes($value);
}
$rsMaster->addColumn($sqlQuery->colNames[$i], $sqlQuery->colAliases[$i], "", "", "str", "", "", $value, true);
}
}
// return only the requested columns
debug_printb("[executeSelectQuery] Removing unwanted columns...<br>");
$rsMaster->filterByColumnNamesInSqlQuery($sqlQuery);
// order columns (not their data)
debug_printb("[executeSelectQuery] Ordering columns (amog themself)...<br>");
if (!$rsMaster->orderColumnsBySqlQuery($sqlQuery)) {
print_error_msg("Ordering the Columns (themself) failed");
return false;
}
// process DISTINCT
if ($sqlQuery->distinct == 1) {
$rsMaster = $rsMaster->makeDistinct($sqlQuery->limit);
}
// Apply Limit
$rsMaster->reset();
$rsMaster = $rsMaster->limitResultSet($sqlQuery->limit);
verbose_debug_print("<br>Limited ResultSet:<br>");
if (TXTDBAPI_VERBOSE_DEBUG) {
$rsMaster->dump();
}
$rsMaster->reset();
return $rsMaster;
}
function parseCreateTableQuery()
{
$colNames = array();
$colTypes = array();
$colDefaultValues = array();
$tables = array();
$tables[0] = $this->parseNextElement();
if ($this->parseNextElement() != "(") {
print_error_msg("( expected");
return null;
}
$index = 0;
$arrElements = array();
while ($this->parseNextElements(",", array(";"), $arrElements)) {
$colNames[] = $arrElements[0];
$colTypes[] = $arrElements[1];
if (count($arrElements) > 3 && strtoupper($arrElements[2]) == "DEFAULT") {
if (has_quotes($arrElements[3])) {
remove_quotes($arrElements[3]);
}
$colDefaultValues[] = $arrElements[3];
} else {
$colDefaultValues[] = "";
}
}
$sqlObj = new SqlQuery();
$sqlObj->type = "CREATE TABLE";
$sqlObj->colNames = $colNames;
$sqlObj->colTypes = $colTypes;
$sqlObj->fieldValues = $colDefaultValues;
$sqlObj->tables = $tables;
return $sqlObj;
}
function executeSingleRecFuncs()
{
global $g_sqlSingleRecFuncs;
global $g_sqlMathOps;
debug_printb("[executeSingleRecFuncs] executing singlerec functions...<br>");
for ($i = 0; $i < count($this->colFuncs); ++$i) {
if (!$this->colFuncs[$i] || $this->colFuncsExecuted[$i]) {
continue;
}
if (!in_array($this->colFuncs[$i], $g_sqlSingleRecFuncs)) {
continue;
}
debug_print($this->colFuncs[$i] . "(" . $this->colNames[$i] . "): ");
// EVAL
if ($this->colFuncs[$i] == "EVAL") {
$eval_str = $this->colNames[$i];
$out_str = "";
if (has_quotes($eval_str)) {
remove_quotes($eval_str);
}
debug_print("EVAL function, eval String is {$eval_str}!<br>");
$sp = new StringParser();
$sp->specialElements = $g_sqlMathOps;
$sp->setString($eval_str);
while (!is_empty_str($elem = $sp->parseNextElement())) {
debug_print("ELEM: {$elem}\n");
if (is_numeric($elem) || in_array($elem, $g_sqlMathOps)) {
$out_str .= $elem . " ";
} else {
$origColNr = $this->findColNrByAttrs($elem, "", "");
if ($origColNr == NOT_FOUND) {
print_error_msg("EVAL: Column '" . $elem . "' not found!");
return false;
}
$out_str .= "%{$origColNr}%";
}
}
debug_print("New Eval String: {$out_str}\n");
$val_str = "";
// apply function (use values from the original column as input)
$rowCount = count($this->rows);
$colCount = count($this->colNames);
for ($j = 0; $j < $rowCount; ++$j) {
$val_str = $out_str;
for ($k = 0; $k < $colCount; ++$k) {
if (!is_false(strpos($val_str, "%{$k}%"))) {
$val_str = str_replace("%{$k}%", $this->rows[$j]->fields[$k], $val_str);
}
}
debug_print("VAL_STR={$val_str}\n");
$this->rows[$j]->fields[$i] = execFunc($this->colFuncs[$i], $val_str);
}
$this->colFuncsExecuted[$i] = true;
// function with paramater, but the parameter is not a column
} else {
if ($this->colNames[$i] && !is_empty_str($this->colNames[$i]) && (is_numeric($this->colNames[$i]) || has_quotes($this->colNames[$i]))) {
$param = $this->colNames[$i];
if (has_quotes($param)) {
remove_quotes($param);
}
$result = execFunc($this->colFuncs[$i], $param);
$rowCount = count($this->rows);
debug_print("a function with a non-column parameter! (result={$result})<br>");
for ($j = 0; $j < $rowCount; ++$j) {
$this->rows[$j]->fields[$i] = $result;
}
$this->colFuncsExecuted[$i] = true;
// function with parameter? =>execute function with the values form the original column
} else {
if ($this->colNames[$i]) {
debug_print("a function with a column parameter!<br>");
// find original column (without function)
$origColNr = $this->findColNrByAttrs($this->colNames[$i], $this->colTables[$i], "");
if ($origColNr == NOT_FOUND) {
print_error_msg("Column '" . $this->colNames[$i] . "' not found!");
return false;
}
// copy some column header data from the original
$this->colTables[$i] = $this->colTables[$origColNr];
$this->colTableAliases[$i] = $this->colTableAliases[$origColNr];
// apply function (use values from the original column as input)
$rowCount = count($this->rows);
for ($j = 0; $j < $rowCount; ++$j) {
$this->rows[$j]->fields[$i] = execFunc($this->colFuncs[$i], $this->rows[$j]->fields[$origColNr]);
}
$this->colFuncsExecuted[$i] = true;
// function without parameter: just execute!
} else {
debug_print("a function with no parameters!<br>");
$result = execFunc($this->colFuncs[$i], "");
$rowCount = count($this->rows);
for ($j = 0; $j < $rowCount; ++$j) {
$this->rows[$j]->fields[$i] = $result;
}
$this->colFuncsExecuted[$i] = true;
}
}
}
}
}
function array_walk_remove_quotes(&$value, &$key)
{
if (has_quotes($value)) {
remove_quotes($value);
}
}
