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