emailtemplate.it/public/tools/schema-dump.php

<?php
declare(strict_types=1);

header('Content-Type: text/plain; charset=utf-8');

function loadProjectConfig(): ?array
{
    $paths = [
        dirname(__DIR__, 2) . '/inc/config.php',
        dirname(__DIR__, 2) . '/config/emailtemplate.conf.php',
        dirname(__DIR__)     . '/inc/config.php',
    ];
    foreach ($paths as $path) {
        if (!is_file($path)) continue;
        try {
            $cfg = include $path;
            if (is_array($cfg) && isset($cfg['projectdb']) && is_array($cfg['projectdb'])) {
                return $cfg;
            }
        } catch (Throwable $e) {
            continue;
        }
    }
    $env = [
        'db_host'    => getenv('DB_HOST') ?: getenv('DB_TPL_HOST'),
        'db_name'    => getenv('DB_NAME') ?: getenv('DB_TPL_NAME'),
        'db_user'    => getenv('DB_USER') ?: getenv('DB_TPL_USER'),
        'db_pass'    => getenv('DB_PASS') ?: getenv('DB_TPL_PASS'),
        'db_charset' => getenv('DB_CHARSET') ?: 'utf8mb4',
        'db_port'    => (int)(getenv('DB_PORT') ?: 3306),
        'db_socket'  => getenv('DB_SOCKET') ?: null,
    ];
    if (!empty($env['db_name']) && !empty($env['db_user'])) {
        return ['projectdb' => $env];
    }
    return null;
}

$conf = loadProjectConfig();
if (!$conf || !is_array($conf['projectdb'])) {
    echo "-- Fehler: config.php konnte nicht geladen werden oder enthält keine projectdb-Einträge.\n";
    echo "-- Hinweis: Stelle sicher, dass inc/config.php oder config/emailtemplate.conf.php auf dem Server vorhanden und lesbar ist.\n";
    exit(1);
}

$cfg = $conf['projectdb'];
$dbName = (string)($cfg['db_name'] ?? '');
if ($dbName === '') {
    echo "-- Fehler: Kein Datenbankname in der Konfiguration gefunden.\n";
    exit(1);
}

$pdo = null;
$error = null;
try {
    $host    = $cfg['db_host'] ?? '127.0.0.1';
    $port    = (int)($cfg['db_port'] ?? 3306);
    $charset = $cfg['db_charset'] ?? 'utf8mb4';
    $user    = $cfg['db_user'] ?? '';
    $pass    = $cfg['db_pass'] ?? '';
    $socket  = $cfg['db_socket'] ?? null;

    $dsn = $socket
        ? sprintf('mysql:unix_socket=%s;dbname=%s;charset=%s', $socket, $dbName, $charset)
        : sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s', $host ?: '127.0.0.1', $port, $dbName, $charset);

    $pdo = new PDO(
        $dsn,
        $user,
        $pass,
        [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        ]
    );
} catch (Throwable $e) {
    $error = $e->getMessage();
}

if (!$pdo) {
    echo "-- Fehler: Datenbankverbindung fehlgeschlagen.\n";
    echo '-- Detail: ' . ($error ?? 'Unbekannt') . "\n";
    exit(1);
}

$schemaInfo = [
    'charset'   => null,
    'collation' => null,
];
$stmt = $pdo->prepare('SELECT DEFAULT_CHARACTER_SET_NAME AS charset, DEFAULT_COLLATION_NAME AS collate_name FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = :schema');
$stmt->execute([':schema' => $dbName]);
if ($row = $stmt->fetch()) {
    $schemaInfo['charset'] = $row['charset'] ?? null;
    $schemaInfo['collation'] = $row['collate_name'] ?? null;
}

$quote = static function (string $identifier): string {
    return '`' . str_replace('`', '``', $identifier) . '`';
};

$now = gmdate('Y-m-d H:i:s') . ' UTC';
$lines = [];
$lines[] = sprintf('-- Schema-Dump für `%s` (erstellt am %s)', $dbName, $now);
$lines[] = '-- Hinweis: Es werden nur CREATE-Anweisungen ausgegeben, bestehende Tabellen bleiben unangetastet.';
$lines[] = '';

$charset = $schemaInfo['charset'] ?: ($cfg['db_charset'] ?? 'utf8mb4');
$collation = $schemaInfo['collation'] ?? null;
$createDb = sprintf(
    'CREATE DATABASE IF NOT EXISTS %s CHARACTER SET %s%s;',
    $quote($dbName),
    $charset,
    $collation ? ' COLLATE ' . $collation : ''
);
$lines[] = $createDb;
$lines[] = sprintf('USE %s;', $quote($dbName));
$lines[] = 'SET NAMES utf8mb4;';
$lines[] = 'SET FOREIGN_KEY_CHECKS = 0;';
$lines[] = '';

$tableStmt = $pdo->prepare(
    'SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA = :schema ORDER BY TABLE_NAME'
);
$tableStmt->execute([':schema' => $dbName]);
$tables = $tableStmt->fetchAll();

foreach ($tables as $tbl) {
    $tableName = (string)$tbl['TABLE_NAME'];
    $type = strtoupper((string)$tbl['TABLE_TYPE']);
    $lines[] = sprintf('-- %s: %s', $type === 'VIEW' ? 'View' : 'Tabelle', $tableName);
    try {
        if ($type === 'VIEW') {
            $sql = sprintf('SHOW CREATE VIEW %s', $quote($tableName));
            $res = $pdo->query($sql)->fetch();
            $create = $res['Create View'] ?? '';
            if ($create !== '') {
                $lines[] = $create . ';';
            } else {
                $lines[] = sprintf('-- WARNUNG: Keine CREATE VIEW Ausgabe für %s verfügbar.', $tableName);
            }
        } else {
            $sql = sprintf('SHOW CREATE TABLE %s', $quote($tableName));
            $res = $pdo->query($sql)->fetch();
            $create = $res['Create Table'] ?? '';
            if ($create !== '') {
                if (stripos($create, 'CREATE TABLE IF NOT EXISTS') === false) {
                    $create = preg_replace('/^CREATE TABLE/i', 'CREATE TABLE IF NOT EXISTS', $create, 1);
                }
                $lines[] = $create . ';';
            } else {
                $lines[] = sprintf('-- WARNUNG: Keine CREATE TABLE Ausgabe für %s verfügbar.', $tableName);
            }
        }
    } catch (Throwable $e) {
        $lines[] = sprintf('-- FEHLER beim Auslesen von %s: %s', $tableName, $e->getMessage());
    }
    $lines[] = '';
}

$lines[] = 'SET FOREIGN_KEY_CHECKS = 1;';
$lines[] = '-- Ende des Schema-Dumps';

echo implode("\n", $lines);