manager/client-package/manage-client/lib/mysql.php

<?php

declare(strict_types=1);

// Optional MySQL/MariaDB dump for the backup archive.
//
// PDO only: no exec(), no mysqldump binary, because shared hosting frequently
// blocks shell execution. The dump is streamed to a temp file, so table size is
// bounded by disk rather than memory_limit.
//
// Active only when MANAGE_BACKUP_DATABASE is configured. Projects without a
// database (like the PSA order system) leave it at null and never touch this.

function manageDatabaseConfigured(): bool
{
    $config = MANAGE_BACKUP_DATABASE;

    return is_array($config) && trim((string) ($config["dsn"] ?? "")) !== "";
}

function manageDatabaseConnect(): PDO
{
    $config = MANAGE_BACKUP_DATABASE;
    if (!is_array($config)) {
        throw new RuntimeException("MANAGE_BACKUP_DATABASE ist nicht konfiguriert.");
    }

    $dsn = trim((string) ($config["dsn"] ?? ""));
    if ($dsn === "") {
        throw new RuntimeException("MANAGE_BACKUP_DATABASE benötigt einen DSN.");
    }

    if (!class_exists("PDO")) {
        throw new RuntimeException("Die PHP-PDO-Erweiterung ist nicht verfügbar.");
    }

    try {
        $pdo = new PDO(
            $dsn,
            (string) ($config["user"] ?? ""),
            (string) ($config["password"] ?? ""),
            [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ],
        );
    } catch (PDOException $exception) {
        // The DSN may contain a host name but never a password, so it is safe
        // to keep out of the message entirely.
        throw new RuntimeException("Datenbankverbindung fehlgeschlagen: " . $exception->getMessage());
    }

    return $pdo;
}

function manageDatabaseName(PDO $pdo): string
{
    $config = MANAGE_BACKUP_DATABASE;
    $name = is_array($config) ? trim((string) ($config["name"] ?? "")) : "";
    if ($name !== "") {
        return $name;
    }

    try {
        $value = $pdo->query("SELECT DATABASE()")->fetchColumn();
        if (is_string($value) && $value !== "") {
            return $value;
        }
    } catch (Throwable $exception) {
        // Fall through to the generic name below.
    }

    return "database";
}

function manageDatabaseQuoteIdentifier(string $identifier): string
{
    return "`" . str_replace("`", "``", $identifier) . "`";
}

function manageDatabaseTables(PDO $pdo): array
{
    $tables = [];
    foreach ($pdo->query("SHOW FULL TABLES") as $row) {
        $values = array_values($row);
        $name = (string) ($values[0] ?? "");
        $type = strtoupper((string) ($values[1] ?? "BASE TABLE"));
        if ($name === "") {
            continue;
        }
        $tables[] = ["name" => $name, "type" => $type];
    }

    usort($tables, static function (array $left, array $right): int {
        return strcmp($left["name"], $right["name"]);
    });

    return $tables;
}

// Formats one value for the INSERT statement. Binary content is written as a
// hex literal so the dump stays valid ASCII and survives any transport.
function manageDatabaseQuoteValue(PDO $pdo, $value): string
{
    if ($value === null) {
        return "NULL";
    }
    if (is_int($value) || is_float($value)) {
        return (string) $value;
    }
    if (is_bool($value)) {
        return $value ? "1" : "0";
    }

    $value = (string) $value;
    if ($value !== "" && preg_match('//u', $value) !== 1) {
        return "0x" . bin2hex($value);
    }

    return $pdo->quote($value);
}

/**
 * Writes a SQL dump of the configured database to $targetFile.
 *
 * @return array{tables: int, rows: int, bytes: int, database: string}
 */
function manageDatabaseDump(string $targetFile): array
{
    $pdo = manageDatabaseConnect();
    $config = MANAGE_BACKUP_DATABASE;
    $skipDataTables = is_array($config) && is_array($config["skip_data_tables"] ?? null)
        ? array_map("strval", $config["skip_data_tables"])
        : [];

    manageEnsureDir(dirname($targetFile));
    $handle = fopen($targetFile, "wb");
    if ($handle === false) {
        throw new RuntimeException("SQL-Dump konnte nicht erstellt werden.");
    }

    $database = manageDatabaseName($pdo);
    $tableCount = 0;
    $rowCount = 0;

    try {
        fwrite($handle, "-- Manage client database dump\n");
        fwrite($handle, "-- Database: " . $database . "\n");
        fwrite($handle, "-- Created: " . date(DATE_ATOM) . "\n\n");
        fwrite($handle, "SET NAMES utf8mb4;\n");
        fwrite($handle, "SET FOREIGN_KEY_CHECKS=0;\n\n");

        foreach (manageDatabaseTables($pdo) as $table) {
            $name = $table["name"];
            $quoted = manageDatabaseQuoteIdentifier($name);

            // Views must be recreated after the tables they read from, but a
            // single-pass dump with FOREIGN_KEY_CHECKS=0 is enough in practice
            // and keeps this readable.
            $createRow = $pdo->query("SHOW CREATE TABLE " . $quoted)->fetch();
            $create = "";
            foreach ((array) $createRow as $key => $value) {
                if (stripos((string) $key, "create") === 0) {
                    $create = (string) $value;
                    break;
                }
            }
            if ($create === "") {
                continue;
            }

            $tableCount++;
            fwrite($handle, "--\n-- Table: " . $name . "\n--\n");
            fwrite($handle, "DROP TABLE IF EXISTS " . $quoted . ";\n");
            fwrite($handle, "DROP VIEW IF EXISTS " . $quoted . ";\n");
            fwrite($handle, $create . ";\n\n");

            if ($table["type"] === "VIEW" || in_array($name, $skipDataTables, true)) {
                continue;
            }

            // Chunked reads keep a large table from being buffered as a whole.
            $offset = 0;
            $chunkSize = 500;
            while (true) {
                $statement = $pdo->prepare(
                    "SELECT * FROM " . $quoted . " LIMIT " . $chunkSize . " OFFSET " . $offset,
                );
                $statement->execute();
                $rows = $statement->fetchAll();
                if ($rows === []) {
                    break;
                }

                foreach ($rows as $row) {
                    $columns = [];
                    $values = [];
                    foreach ($row as $column => $value) {
                        $columns[] = manageDatabaseQuoteIdentifier((string) $column);
                        $values[] = manageDatabaseQuoteValue($pdo, $value);
                    }
                    fwrite(
                        $handle,
                        "INSERT INTO " . $quoted . " (" . implode(", ", $columns) . ") VALUES (" .
                        implode(", ", $values) . ");\n",
                    );
                    $rowCount++;
                }

                if (count($rows) < $chunkSize) {
                    break;
                }
                $offset += $chunkSize;
            }

            fwrite($handle, "\n");
        }

        fwrite($handle, "SET FOREIGN_KEY_CHECKS=1;\n");
    } catch (Throwable $exception) {
        fclose($handle);
        @unlink($targetFile);
        throw new RuntimeException("Datenbank-Dump fehlgeschlagen: " . $exception->getMessage());
    }

    fclose($handle);
    @chmod($targetFile, 0660);

    return [
        "tables" => $tableCount,
        "rows" => $rowCount,
        "bytes" => (int) (filesize($targetFile) ?: 0),
        "database" => $database,
    ];
}