コード例 #1
0
 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;
 }
コード例 #2
0
 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;
                 }
             }
         }
     }
 }