test123
myTest
echo "TEST";
$csv1 = << $csv1,
"csv2" => "id,city\n1,Berlin\n2,Hamburg"
];
$sql3 = <<";
queryCsvEngine($sql3, $dataArray);
function queryCsvEngine(string $sql, array $csvSources): string {
/* ---------------------------------------------------------
1. CSV-Quellen einlesen
--------------------------------------------------------- */
$tables = [];
foreach ($csvSources as $name => $csv) {
$lines = array_map('trim', explode("\n", trim($csv)));
$header = str_getcsv(array_shift($lines));
$rows = array_map('str_getcsv', $lines);
$tables[$name] = [
"header" => $header,
"rows" => $rows,
"index" => array_flip($header)
];
}
/* ---------------------------------------------------------
2. SELECT + FROM parsen
--------------------------------------------------------- */
if (!preg_match('/^select\s+(.*?)\s+from\s+(.+?)(?:\s+|$)(.*)$/i', trim($sql), $m)) {
throw new Exception("Ungültige SELECT/FROM-Syntax.");
}
$selectPart = trim($m[1]);
$fromPart = trim($m[2]);
$rest = trim($m[3]);
/* Tabellenalias */
$tableAliases = [];
if (preg_match('/^([a-zA-Z0-9_]+)\s+as\s+([a-zA-Z0-9_]+)$/i', $fromPart, $fa)) {
$real = $fa[1];
$alias = $fa[2];
$tableAliases[$alias] = $real;
$mainTable = $alias;
} else {
$tableAliases[$fromPart] = $fromPart;
$mainTable = $fromPart;
}
if (!isset($tables[$tableAliases[$mainTable]])) {
throw new Exception("Tabelle '$mainTable' existiert nicht.");
}
/* ---------------------------------------------------------
3. JOINs parsen (INNER, LEFT, RIGHT)
--------------------------------------------------------- */
$joins = [];
while (preg_match('/^(left|right|inner)?\s*join\s+([a-zA-Z0-9_]+)(?:\s+as\s+([a-zA-Z0-9_]+))?\s+on\s+([a-zA-Z0-9_.]+)\s*=\s*([a-zA-Z0-9_.]+)(.*)$/i', $rest, $j)) {
$type = strtolower($j[1] ?: "inner");
$table = $j[2];
$alias = $j[3] ?: $table;
$left = $j[4];
$right = $j[5];
$rest = trim($j[6]);
$tableAliases[$alias] = $table;
$joins[] = [
"type" => $type,
"alias" => $alias,
"table" => $table,
"left" => $left,
"right" => $right
];
}
/* ---------------------------------------------------------
4. WHERE
--------------------------------------------------------- */
$wherePart = null;
if (preg_match('/^where\s+(.*?)(?:\s+group\s+by\s+|$)/i', $rest, $w)) {
$wherePart = trim($w[1]);
$rest = trim(substr($rest, strlen($w[0])));
}
/* ---------------------------------------------------------
5. GROUP BY
--------------------------------------------------------- */
$groupBy = null;
if (preg_match('/^group\s+by\s+(.*?)(?:\s+having\s+|$)/i', $rest, $g)) {
$groupBy = array_map('trim', explode(',', $g[1]));
$rest = trim(substr($rest, strlen($g[0])));
}
/* ---------------------------------------------------------
6. HAVING
--------------------------------------------------------- */
$havingPart = null;
if (preg_match('/^having\s+(.*?)(?:\s+order\s+by\s+|$)/i', $rest, $h)) {
$havingPart = trim($h[1]);
$rest = trim(substr($rest, strlen($h[0])));
}
/* ---------------------------------------------------------
7. ORDER BY (mehrere Spalten)
--------------------------------------------------------- */
$orderBy = [];
if (preg_match('/^order\s+by\s+(.+)$/i', $rest, $o)) {
foreach (explode(',', $o[1]) as $p) {
if (preg_match('/([a-zA-Z0-9_.]+)(?:\s+(asc|desc))?/i', trim($p), $m2)) {
$orderBy[] = [
"col" => $m2[1],
"dir" => isset($m2[2]) ? strtolower($m2[2]) : "asc"
];
}
}
}
/* ---------------------------------------------------------
8. JOINs anwenden
--------------------------------------------------------- */
$data = [];
foreach ($tables[$tableAliases[$mainTable]]["rows"] as $row) {
$data[] = [$mainTable => $row];
}
foreach ($joins as $j) {
$alias = $j["alias"];
$real = $j["table"];
$type = $j["type"];
[$lt, $lc] = explode('.', $j["left"]);
[$rt, $rc] = explode('.', $j["right"]);
$leftIdx = $tables[$tableAliases[$lt]]["index"][$lc];
$rightIdx = $tables[$real]["index"][$rc];
$newData = [];
foreach ($data as $rowSet) {
$leftVal = $rowSet[$lt][$leftIdx];
$matched = false;
foreach ($tables[$real]["rows"] as $r2) {
if ($r2[$rightIdx] == $leftVal) {
$matched = true;
$rowSet2 = $rowSet;
$rowSet2[$alias] = $r2;
$newData[] = $rowSet2;
}
}
if (!$matched && $type === "left") {
$rowSet2 = $rowSet;
$rowSet2[$alias] = array_fill(0, count($tables[$real]["header"]), "NULL");
$newData[] = $rowSet2;
}
}
if ($type === "right") {
$newData = [];
foreach ($tables[$real]["rows"] as $r2) {
$matched = false;
foreach ($data as $rowSet) {
$leftVal = $rowSet[$lt][$leftIdx];
if ($r2[$rightIdx] == $leftVal) {
$matched = true;
$rowSet2 = $rowSet;
$rowSet2[$alias] = $r2;
$newData[] = $rowSet2;
}
}
if (!$matched) {
$rowSet2 = [];
foreach ($rowSet as $k => $v) {
$rowSet2[$k] = array_fill(0, count($v), "NULL");
}
$rowSet2[$alias] = $r2;
$newData[] = $rowSet2;
}
}
}
$data = $newData;
}
/* ---------------------------------------------------------
9. WHERE anwenden
--------------------------------------------------------- */
if ($wherePart) {
$data = array_filter($data, function($rowSet) use ($wherePart, $tables, $tableAliases) {
if (!preg_match('/^([a-zA-Z0-9_]+)\.([a-zA-Z0-9_]+)\s*(=|>|<)\s*(.+)$/', $wherePart, $w)) {
throw new Exception("WHERE unterstützt nur einfache Vergleiche.");
}
$full = $w[0]; // kompletter Ausdruck
$t = $w[1]; // Tabellenalias
$col = $w[2]; // Spaltenname
$op = $w[3]; // Operator (=, >, <)
$val = $w[4]; // Vergleichswert
$val = trim($val, " '\"");
// temp
//$idx = 1;
$idx = $tables[$tableAliases[$t]]["index"][$col];
$v = $rowSet[$t][$idx];
if ($v === null) return false;
switch ($op) {
case '=':
return $v == $val;
case '>':
return $v > $val;
case '<':
return $v < $val;
default:
throw new Exception("Unbekannter Operator: $op");
}
});
}
/* ---------------------------------------------------------
10. GROUP BY anwenden
--------------------------------------------------------- */
$groups = [];
if ($groupBy) {
foreach ($data as $rowSet) {
$keyParts = [];
foreach ($groupBy as $gcol) {
[$t, $c] = explode('.', $gcol);
$idx = $tables[$tableAliases[$t]]["index"][$c];
$keyParts[] = $rowSet[$t][$idx];
}
$key = implode('|', $keyParts);
$groups[$key][] = $rowSet;
}
} else {
$groups["__all__"] = $data;
}
/* ---------------------------------------------------------
11. SELECT parsen (mit Aliassen)
--------------------------------------------------------- */
$selects = array_map('trim', explode(',', $selectPart));
$selectParsed = [];
foreach ($selects as $sel) {
if (preg_match('/^(.*?)\s+as\s+([a-zA-Z0-9_]+)$/i', $sel, $m2)) {
$expr = trim($m2[1]);
$alias = trim($m2[2]);
} else {
$expr = $sel;
$alias = null;
}
$selectParsed[] = [
"expr" => $expr,
"alias" => $alias
];
}
/* ---------------------------------------------------------
12. Header erzeugen
--------------------------------------------------------- */
$headerParts = [];
foreach ($selectParsed as $sp) {
$headerParts[] = $sp["alias"] ?: $sp["expr"];
}
$result = [];
$result[] = implode(', ', $headerParts);
/* ---------------------------------------------------------
13. Datenzeilen erzeugen
--------------------------------------------------------- */
foreach ($groups as $grows) {
/* Ohne GROUP BY → jede Zeile einzeln */
/* ---------------------------------------------------------
PATCH: Aggregatfunktionen → nur eine Ausgabe pro Gruppe
--------------------------------------------------------- */
$selectContainsAggregate = false;
foreach ($selectParsed as $sp) {
if (preg_match('/^(sum|min|max|avg)\(/i', $sp["expr"])) {
$selectContainsAggregate = true;
break;
}
}
/* Wenn Aggregatfunktionen vorhanden sind → nur eine Zeile */
if ($selectContainsAggregate) {
$rowOut = [];
foreach ($selectParsed as $sp) {
$expr = $sp["expr"];
if (preg_match('/^(sum|min|max|avg)\(([a-zA-Z0-9_]+)\.([a-zA-Z0-9_]+)\)$/i', $expr, $a)) {
$func = strtolower($a[1]);
$t = $a[2];
$c = $a[3];
$idx = $tables[$tableAliases[$t]]["index"][$c];
/* Werte extrahieren */
$vals = array_map(function($rs) use ($t, $idx) {
return $rs[$t][$idx];
}, $grows);
$vals = array_filter($vals, function($v) {
return $v !== "NULL";
});
$vals = array_map('floatval', $vals);
/* Aggregatfunktion ausführen (PHP‑7‑kompatibel) */
switch ($func) {
case 'sum':
$val = array_sum($vals);
break;
case 'min':
$val = min($vals);
break;
case 'max':
$val = max($vals);
break;
case 'avg':
$val = count($vals) ? array_sum($vals) / count($vals) : 0;
break;
default:
throw new Exception("Unbekannte Aggregatfunktion: $func");
}
$rowOut[] = $val;
} else {
list($t, $c) = explode('.', $expr);
$idx = $tables[$tableAliases[$t]]["index"][$c];
$rowOut[] = isset($grows[0][$t][$idx]) ? $grows[0][$t][$idx] : "NULL";
}
}
/* Strings in Hochkommas setzen */
foreach ($rowOut as &$v) {
if ($v !== "NULL" && !preg_match('/^-?[0-9]+(\.[0-9]+)?$/', $v)) {
$v = '"' . $v . '"';
}
}
unset($v);
$result[] = implode(',', $rowOut);
continue; // ← WICHTIG: keine weiteren Zeilen erzeugen
}
/* Mit GROUP BY → eine Zeile pro Gruppe */
else {
$rowOut = [];
foreach ($selectParsed as $sp) {
$expr = $sp["expr"];
if (preg_match('/^(sum|min|max|avg)\(([a-zA-Z0-9_]+)\.([a-zA-Z0-9_]+)\)$/i', $expr, $a)) {
$func = strtolower($a[1]);
$t = $a[2];
$c = $a[3];
$idx = $tables[$tableAliases[$t]]["index"][$c];
$vals = array_map(function($rs) use ($t, $idx) {
return $rs[$t][$idx];
}, $grows);
$vals = array_filter($vals, function($v) {
return $v !== "NULL";
});
$vals = array_map('floatval', $vals);
switch ($func) {
case 'sum':
$val = array_sum($vals);
break;
case 'min':
$val = min($vals);
break;
case 'max':
$val = max($vals);
break;
case 'avg':
$val = count($vals) ? array_sum($vals) / count($vals) : 0;
break;
default:
throw new Exception("Unbekannte Aggregatfunktion: $func");
}
$rowOut[] = $val;
} else {
list($t, $c) = explode('.', $expr);
$idx = $tables[$tableAliases[$t]]["index"][$c];
$rowOut[] = isset($grows[0][$t][$idx]) ? $grows[0][$t][$idx] : "NULL";
}
}
foreach ($rowOut as &$v) {
if ($v === "NULL") continue;
if (!preg_match('/^-?[0-9]+(\.[0-9]+)?$/', $v)) {
$v = '"' . $v . '"';
}
}
unset($v);
$result[] = implode(',', $rowOut);
}
}
/* ---------------------------------------------------------
14. ORDER BY anwenden
--------------------------------------------------------- */
if ($orderBy) {
// Erste Zeile (Header) entfernen und separat speichern
$headerRow = array_shift($result);
// CSV-Zeilen in Arrays umwandeln
$rows = array_map('str_getcsv', $result);
// Sortieren nach ORDER BY
usort($rows, function($a, $b) use ($orderBy, $headerParts) {
foreach ($orderBy as $ob) {
$col = $ob["col"];
$dir = $ob["dir"]; // "asc" oder "desc"
// Spaltenindex im Header finden
$colIndex = array_search($col, $headerParts);
if ($colIndex === false) {
continue; // unbekannte Spalte → ignorieren
}
// Vergleich durchführen
$cmp = $a[$colIndex] <=> $b[$colIndex];
if ($cmp !== 0) {
return ($dir === "asc") ? $cmp : -$cmp;
}
}
return 0; // alle ORDER-BY-Spalten gleich
});
// Zeilen wieder in CSV umwandeln
$rowsCsv = array_map(function($r) {
return implode(',', $r);
}, $rows);
// Header wieder einfügen
$result = array_merge([$headerRow], $rowsCsv);
}
/* ---------------------------------------------------------
15. Ausgabe mit
--------------------------------------------------------- */
return implode("
", $result) . "
";
}