View Code - 20260921

schema_import.php | 20260921_175631.php Initial Domain Snapshot

<?php
declare(strict_types=1);

/**
 * Developer Utility: Schema Importer
 * File: schema_import.php
 * 
 * Purpose: Standalone developer tool to import large MySQL .sql schema files 
 * using the credentials defined in a local database.php config file.
 * 
 * WARNING: DO NOT DEPLOY THIS FILE ON PRODUCTION SERVERS.
 */

// Error reporting settings for development utility
error_reporting(E_ALL);
ini_set('display_errors', '1');

// Adjust environment limits for handling very large SQL executions
ini_set('memory_limit', '1024M');
set_time_limit(0);

// Configuration file mapping
const CONFIG_FILE = __DIR__ . '/database.php';
const LOG_DIR = __DIR__ . '/logs';
const LOG_FILE = LOG_DIR . '/import_errors.log';

// Helper function to log errors
function logImportError(string $message): void {
    if (!is_dir(LOG_DIR)) {
        mkdir(LOG_DIR, 0755, true);
    }
    $timestamp = date('Y-m-d H:i:s');
    file_put_contents(LOG_FILE, "[{$timestamp}] {$message}\n", FILE_APPEND);
}

// Load database configurations dynamically
function loadConfig(): array {
    if (!file_exists(CONFIG_FILE)) {
        return [
            'success' => false,
            'message' => 'Configuration file "database.php" not found in the current directory.',
            'data' => []
        ];
    }

try {
        $config = include CONFIG_FILE;
        if (!is_array($config)) {
            return [
                'success' => false,
                'message' => 'Configuration file "database.php" must return an associative array.',
                'data' => []
            ];
        }

$requiredKeys = ['host', 'database', 'username', 'password'];
        foreach ($requiredKeys as $key) {
            if (!isset($config[$key])) {
                return [
                    'success' => false,
                    'message' => "Missing required configuration key: '{$key}' inside database.php.",
                    'data' => []
                ];
            }
        }

return [
            'success' => true,
            'message' => 'Configuration loaded successfully.',
            'data' => $config
        ];
    } catch (\Throwable $e) {
        return [
            'success' => false,
            'message' => 'Error reading configuration: ' . $e->getMessage(),
            'data' => []
        ];
    }
}

// Function to establish PDO Connection
function getPDOConnection(array $config): ?PDO {
    $charset = $config['charset'] ?? 'utf8mb4';
    $dsn = "mysql:host={$config['host']};dbname={$config['database']};charset={$charset}";
    
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

return new PDO($dsn, $config['username'], $config['password'], $options);
}

// Output processing counters and statistics
$importResults = null;
$configResult = loadConfig();
$dbConfig = $configResult['data'];
$connectionError = null;
$dbConnected = false;

if ($configResult['success']) {
    try {
        $pdoTest = getPDOConnection($dbConfig);
        if ($pdoTest) {
            $dbConnected = true;
        }
    } catch (\Throwable $e) {
        $connectionError = $e->getMessage();
    }
}

// Handle Form Submission and Stream Processing
if ($_SERVER['REQUEST_METHOD'] === 'POST' && $dbConnected) {
    $continueOnError = isset($_POST['continue_on_error']) && $_POST['continue_on_error'] === '1';
    
    if (!isset($_FILES['sql_file']) || $_FILES['sql_file']['error'] !== UPLOAD_ERR_OK) {
        $importResults = [
            'success' => false,
            'message' => 'File upload failed. Please make sure the file does not exceed directive limits.'
        ];
    } else {
        $fileTmpPath = $_FILES['sql_file']['tmp_name'];
        $fileName = $_FILES['sql_file']['name'];
        $fileSize = $_FILES['sql_file']['size'];
        $fileExtension = strtolower(pathinfo($fileName, PATHINFO_EXTENSION));

if ($fileExtension !== 'sql') {
            $importResults = [
                'success' => false,
                'message' => 'Invalid file extension. Only .sql files are permitted.'
            ];
        } elseif ($fileSize > 524288000) { // 500 MB
            $importResults = [
                'success' => false,
                'message' => 'File size exceeds maximum permitted threshold of 500 MB.'
            ];
        } else {
            // Execution statistics counters
            $startTime = microtime(true);
            $statementsExecuted = 0;
            $statementsFailed = 0;
            $errorDetails = [];

try {
                $pdo = getPDOConnection($dbConfig);
                
                // Disable Foreign Key Validation Checks for structural consistency during import
                $pdo->exec("SET FOREIGN_KEY_CHECKS = 0;");

// Stream line processing engine avoiding total string payload loads
                $handle = fopen($fileTmpPath, 'r');
                if ($handle) {
                    $currentStatement = '';
                    $inMultilineComment = false;

while (($line = fgets($handle)) !== false) {
                        $trimmedLine = trim($line);

// Skip single-line structural query comments
                        if ($trimmedLine === '' || str_starts_with($trimmedLine, '--') || str_starts_with($trimmedLine, '#')) {
                            continue;
                        }

// Block multiline structural comments logic
                        if (str_starts_with($trimmedLine, '/*')) {
                            if (!str_contains($trimmedLine, '*/')) {
                                $inMultilineComment = true;
                            }
                            continue;
                        }
                        if ($inMultilineComment) {
                            if (str_contains($trimmedLine, '*/')) {
                                $inMultilineComment = false;
                            }
                            continue;
                        }

$currentStatement .= $line;

// Execute buffered statement block when a closing delimiter semicolon is reached
                        if (str_ends_with($trimmedLine, ';')) {
                            $statementToExecute = trim($currentStatement);
                            if ($statementToExecute !== '') {
                                try {
                                    $pdo->exec($statementToExecute);
                                    $statementsExecuted++;
                                } catch (\Throwable $statementException) {
                                    $statementsFailed++;
                                    $errorMessage = $statementException->getMessage();
                                    $statementIndex = $statementsExecuted + $statementsFailed;
                                    
                                    $errorDetails[] = [
                                        'index' => $statementIndex,
                                        'query' => substr($statementToExecute, 0, 250) . (strlen($statementToExecute) > 250 ? '...' : ''),
                                        'error' => $errorMessage
                                    ];

logImportError("Statement #{$statementIndex} failed. Error: {$errorMessage}. Query Segment: {$statementToExecute}");

if (!$continueOnError) {
                                        fclose($handle);
                                        throw new \Exception("Import halted intentionally due to an execution failure on Statement #{$statementIndex}.");
                                    }
                                }
                            }
                            $currentStatement = '';
                        }
                    }

// Process any remaining partial string buffers cleanly
                    $remainingStatement = trim($currentStatement);
                    if ($remainingStatement !== '') {
                        try {
                            $pdo->exec($remainingStatement);
                            $statementsExecuted++;
                        } catch (\Throwable $statementException) {
                            $statementsFailed++;
                            $statementIndex = $statementsExecuted + $statementsFailed;
                            $errorDetails[] = [
                                'index' => $statementIndex,
                                'query' => $remainingStatement,
                                'error' => $statementException->getMessage()
                            ];
                            logImportError("Final Statement #{$statementIndex} failed. Error: " . $statementException->getMessage());
                        }
                    }

fclose($handle);
                }

// Re-enable Foreign Key constraints dynamically
                $pdo->exec("SET FOREIGN_KEY_CHECKS = 1;");

$endTime = microtime(true);
                $executionTime = round($endTime - $startTime, 4);
                $memoryUsed = round(memory_get_peak_usage(true) / (1024 * 1024), 2);

$importResults = [
                    'success' => true,
                    'database' => $dbConfig['database'],
                    'executed' => $statementsExecuted,
                    'failed' => $statementsFailed,
                    'time' => $executionTime,
                    'memory' => $memoryUsed,
                    'errors' => $errorDetails
                ];

} catch (\Throwable $globalException) {
                $endTime = microtime(true);
                $importResults = [
                    'success' => false,
                    'message' => $globalException->getMessage(),
                    'database' => $dbConfig['database'] ?? 'Unknown',
                    'executed' => $statementsExecuted ?? 0,
                    'failed' => $statementsFailed ?? 0,
                    'time' => isset($startTime) ? round($endTime - $startTime, 4) : 0,
                    'memory' => round(memory_get_peak_usage(true) / (1024 * 1024), 2),
                    'errors' => $errorDetails ?? []
                ];
                
                // Keep foreign keys safe even during crash recovery
                try {
                    $pdo = getPDOConnection($dbConfig);
                    $pdo->exec("SET FOREIGN_KEY_CHECKS = 1;");
                } catch (\Throwable $tbl) {
                    // Fail silently inside global exception fallbacks
                }
            }
        }
    }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Developer Utility - Schema Importer</title>
</head>
<body class="bg-light">

<div class="container my-5">
    
    <div class="alert alert-danger border-2 d-flex align-items-center mb-4 shadow-sm" role="alert">
        <div class="me-3 fs-3">⚠️</div>
        <div>
            <strong class="d-block text-uppercase">Developer Utility Warning</strong>
            This file is intended for local setup or migration tasks only. Do not deploy this utility on public production servers.
        </div>
    </div>

<div class="row g-4">
        
        <div class="col-lg-4">
            <div class="card shadow-sm border-0 mb-4">
                <div class="card-header bg-dark text-white py-3">
                    <h5 class="card-title mb-0 fs-6 text-uppercase tracking-wider">Environment Parameters</h5>
                </div>
                <div class="card-body">
                    <?php if (!$configResult['success']): ?>
                        <div class="alert alert-warning mb-0">
                            <strong>Configuration Issue:</strong><br>
                            <?= htmlspecialchars($configResult['message']) ?>
                        </div>
                    <?php else: ?>
                        <div class="mb-3">
                            <label class="text-muted d-block small uppercase-label fw-bold">Target Host</label>
                            <span class="fs-5 text-dark font-monospace"><?= htmlspecialchars($dbConfig['host']) ?></span>
                        </div>
                        <div class="mb-3">
                            <label class="text-muted d-block small uppercase-label fw-bold">Target Database</label>
                            <span class="fs-5 text-primary fw-bold font-monospace"><?= htmlspecialchars($dbConfig['database']) ?></span>
                        </div>
                        <div class="mb-3">
                            <label class="text-muted d-block small uppercase-label fw-bold">Database User</label>
                            <span class="fs-6 text-dark font-monospace"><?= htmlspecialchars($dbConfig['username']) ?></span>
                        </div>
                        <hr>
                        <div class="d-flex align-items-center">
                            <label class="text-muted small fw-bold me-2 mb-0">Status:</label>
                            <?php if ($dbConnected): ?>
                                <span class="badge bg-success px-3 py-2 rounded-pill shadow-sm">Connected Successfully</span>
                            <?php else: ?>
                                <span class="badge bg-danger px-3 py-2 rounded-pill shadow-sm">Connection Failed</span>
                            <?php endif; ?>
                        </div>
                        
                        <?php if ($connectionError): ?>
                            <div class="alert alert-danger mt-3 mb-0 small font-monospace">
                                <strong>PDO Connection Error:</strong><br>
                                <?= htmlspecialchars($connectionError) ?>
                            </div>
                        <?php endif; ?>
                    <?php endif; ?>
                </div>
            </div>

<div class="card shadow-sm border-0">
                <div class="card-body small text-muted">
                    <h6 class="text-dark fw-bold mb-2">Import System Rules</h6>
                    <ul class="ps-3 mb-0">
                        <li>Only valid <code class="text-danger">.sql</code> statements and files are accepted.</li>
                        <li>Maximum upload constraints enforced up to <strong>500 MB</strong>.</li>
                        <li>The system automatically bypasses inline SQL notes, comments, and blocks.</li>
                        <li>Errors details are written locally directly to <code class="text-dark">logs/import_errors.log</code>.</li>
                    </ul>
                </div>
            </div>
        </div>

<div class="col-lg-8">
            
            <?php if ($importResults !== null): ?>
                <?php if (isset($importResults['success']) && $importResults['success'] === true): ?>
                    <div class="card shadow-sm border-0 mb-4 bg-white">
                        <div class="card-body p-4">
                            <div class="d-flex align-items-center mb-3 text-success">
                                <div class="fs-2 me-3">✓</div>
                                <h4 class="mb-0 fw-bold">Schema Import Process Complete</h4>
                            </div>
                            <p class="text-muted">The execution sequence parsed and applied the elements directly inside database target space.</p>
                            
                            <div class="row g-3 my-2">
                                <div class="col-sm-6 col-md-4">
                                    <div class="p-3 bg-light rounded text-center">
                                        <div class="text-muted small fw-bold">Database</div>
                                        <div class="fs-5 fw-bold font-monospace text-dark"><?= htmlspecialchars($importResults['database']) ?></div>
                                    </div>
                                </div>
                                <div class="col-sm-6 col-md-4">
                                    <div class="p-3 bg-light rounded text-center">
                                        <div class="text-muted small fw-bold text-success">Imported Statements</div>
                                        <div class="fs-4 fw-bold font-monospace text-success"><?= $importResults['executed'] ?></div>
                                    </div>
                                </div>
                                <div class="col-sm-6 col-md-4">
                                    <div class="p-3 bg-light rounded text-center">
                                        <div class="text-muted small fw-bold text-danger">Failed Statements</div>
                                        <div class="fs-4 fw-bold font-monospace text-danger"><?= $importResults['failed'] ?></div>
                                    </div>
                                </div>
                                <div class="col-sm-6 col-md-4">
                                    <div class="p-3 bg-light rounded text-center">
                                        <div class="text-muted small fw-bold">Execution Time</div>
                                        <div class="fs-5 fw-bold font-monospace text-dark"><?= $importResults['time'] ?> <span class="fs-6 fw-normal">sec</span></div>
                                    </div>
                                </div>
                                <div class="col-sm-6 col-md-4">
                                    <div class="p-3 bg-light rounded text-center">
                                        <div class="text-muted small fw-bold">Memory Utilized</div>
                                        <div class="fs-5 fw-bold font-monospace text-dark"><?= $importResults['memory'] ?> <span class="fs-6 fw-normal">MB</span></div>
                                    </div>
                                </div>
                            </div>

<?php if (!empty($importResults['errors'])): ?>
                                <h5 class="mt-4 text-warning fw-bold small text-uppercase">Non-Fatal Error Logs Summary</h5>
                                <div class="overflow-auto border rounded bg-light" style="max-height: 250px;">
                                    <table class="table table-sm table-striped font-monospace mb-0 small">
                                        <thead class="table-dark sticky-top">
                                            <tr>
                                                <th class="px-2 py-1"># Line</th>
                                                <th class="px-2 py-1">Statement Segment Context</th>
                                                <th class="px-2 py-1 text-danger">Exception Description</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($importResults['errors'] as $err): ?>
                                                <tr>
                                                    <td class="px-2 py-1 text-nowrap"><?= $err['index'] ?></td>
                                                    <td class="px-2 py-1 text-muted text-break"><?= htmlspecialchars($err['query']) ?></td>
                                                    <td class="px-2 py-1 text-danger text-break"><?= htmlspecialchars($err['error']) ?></td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            <?php endif; ?>
                        </div>
                    </div>
                <?php else: ?>
                    <div class="card shadow-sm border-0 mb-4 border-start border-danger border-4">
                        <div class="card-body p-4">
                            <div class="d-flex align-items-center mb-3 text-danger">
                                <div class="fs-2 me-3">❌</div>
                                <h4 class="mb-0 fw-bold">Import Sequence Terminated Prematurely</h4>
                            </div>
                            <div class="alert alert-danger font-monospace small mb-3">
                                <?= htmlspecialchars($importResults['message'] ?? 'An unknown system exception occurred.') ?>
                            </div>
                            
                            <?php if (isset($importResults['executed']) || isset($importResults['failed'])): ?>
                                <div class="row g-2 font-monospace text-muted small">
                                    <div>Processed Statements Applied: <strong><?= $importResults['executed'] ?? 0 ?></strong></div>
                                    <div>Failed Statement Crashes: <strong><?= $importResults['failed'] ?? 0 ?></strong></div>
                                    <div>Peak System Memory Consumption: <strong><?= $importResults['memory'] ?? 0 ?> MB</strong></div>
                                </div>
                            <?php endif; ?>

<?php if (!empty($importResults['errors'])): ?>
                                <h5 class="mt-4 text-danger fw-bold small text-uppercase">Failure Log Context</h5>
                                <div class="overflow-auto border rounded bg-light" style="max-height: 200px;">
                                    <table class="table table-sm table-striped font-monospace mb-0 small">
                                        <thead class="table-dark">
                                            <tr>
                                                <th class="px-2 py-1">Line</th>
                                                <th class="px-2 py-1">Query Fragment</th>
                                                <th class="px-2 py-1 text-danger">Database Exception Error</th>
                                            </tr>
                                        </thead>
                                        <tbody>
                                            <?php foreach ($importResults['errors'] as $err): ?>
                                                <tr>
                                                    <td class="px-2 py-1"><?= $err['index'] ?></td>
                                                    <td class="px-2 py-1 text-muted text-break"><?= htmlspecialchars($err['query']) ?></td>
                                                    <td class="px-2 py-1 text-danger text-break"><?= htmlspecialchars($err['error']) ?></td>
                                                </tr>
                                            <?php endforeach; ?>
                                        </tbody>
                                    </table>
                                </div>
                            <?php endif; ?>
                        </div>
                    </div>
                <?php endif; ?>
            <?php endif; ?>

<div class="card shadow-sm border-0">
                <div class="card-header bg-primary text-white py-3">
                    <h5 class="card-title mb-0 fs-6 text-uppercase tracking-wider">Execute New SQL Schema Import</h5>
                </div>
                <div class="card-body p-4">
                    <form action="" method="POST" enctype="multipart/form-data" id="uploadForm">
                        <div class="mb-4">
                            <label for="sql_file" class="form-label fw-bold text-dark">Select SQL Target Payload File</label>
                            <input class="form-control form-control-lg font-monospace" type="file" id="sql_file" name="sql_file" accept=".sql" required <?= !$dbConnected ? 'disabled' : '' ?>>
                            <div class="form-text mt-2 text-muted">
                                Ensure file terminates layout structures explicitly using standard semicolons. Multi-line statements are parsed line-by-line using streaming buffers.
                            </div>
                        </div>

<div class="mb-4">
                            <div class="form-check form-switch">
                                <input class="form-check-input" type="checkbox" id="continue_on_error" name="continue_on_error" value="1" checked <?= !$dbConnected ? 'disabled' : '' ?>>
                                <label class="form-check-input-label fw-bold text-dark" for="continue_on_error">Continue importing if a statement execution fails</label>
                            </div>
                            <div class="form-text text-muted ps-4">
                                When enabled, errors will be logged to the repository file, but the engine will proceed to execute the remainder of the schema elements.
                            </div>
                        </div>

<div class="d-grid">
                            <button type="submit" class="btn btn-primary btn-lg fw-bold py-3 text-uppercase tracking-wider" id="submitBtn" <?= !$dbConnected ? 'disabled' : '' ?>>
                                Begin Processing SQL Payload
                            </button>
                        </div>
                    </form>

<div class="mt-4 d-none" id="processingStatus">
                        <div class="d-flex align-items-center p-3 bg-light border rounded">
                            <div class="spinner-border text-primary me-3" role="status"></div>
                            <div>
                                <strong class="d-block text-dark">Processing Schema Import Sequence...</strong>
                                <span class="text-muted small">Streaming file chunks, isolating queries, and writing configuration mappings. Please wait.</span>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

if (uploadForm) {
            uploadForm.addEventListener('submit', function(e) {
                // Perform quick client-side file confirmation checks
                const file = fileInput.files[0];
                if (file) {
                    const extension = file.name.split('.').pop().toLowerCase();
                    if (extension !== 'sql') {
                        e.preventDefault();
                        alert('Error: Please select a valid file ending with the .sql extension.');
                        return;
                    }
                    if (file.size > 524288000) { // 500 MB boundary validation checks
                        e.preventDefault();
                        alert('Error: The selected payload file exceeds the maximum 500 MB execution cap.');
                        return;
                    }
                }

// Show processing indicator
                submitBtn.disabled = true;
                processingStatus.classList.remove('d-none');
            });
        }
    });
</script>

</body>
</html>