portal-api/app/Services/Imports/Concerns/ParsesSqlDump.php

<?php

namespace App\Services\Imports\Concerns;

use RuntimeException;

trait ParsesSqlDump
{
    private function parseSqlDump(string $path, array $tables): array
    {
        $result = [];
        foreach ($tables as $table) {
            $result[$table] = [];
        }

        $handle = fopen($path, 'rb');
        if (! $handle) {
            throw new RuntimeException("Unable to open SQL file: {$path}");
        }

        $activeTable = null;
        $activeColumns = [];
        $buffer = '';
        $state = ['in_string' => false, 'escaped' => false];

        while (($line = fgets($handle)) !== false) {
            if ($activeTable === null) {
                $trimmed = trim($line);
                if (
                    preg_match('/^INSERT INTO `([^`]+)` \((.+)\) VALUES$/', $trimmed, $matches)
                    && in_array($matches[1], $tables, true)
                ) {
                    $activeTable = $matches[1];
                    $activeColumns = $this->parseColumns($matches[2]);
                    $buffer = '';
                    $state = ['in_string' => false, 'escaped' => false];
                }

                continue;
            }

            $buffer .= $line;

            if (! $this->lineEndsStatement($line, $state)) {
                continue;
            }

            $valueBlock = preg_replace('/;\s*$/', '', trim($buffer));
            $result[$activeTable] = array_merge(
                $result[$activeTable],
                $this->parseValueBlock($valueBlock, $activeColumns)
            );

            $activeTable = null;
            $activeColumns = [];
            $buffer = '';
            $state = ['in_string' => false, 'escaped' => false];
        }

        fclose($handle);

        return $result;
    }

    private function parseColumns(string $columnSegment): array
    {
        preg_match_all('/`([^`]+)`/', $columnSegment, $matches);
        return $matches[1] ?? [];
    }

    private function lineEndsStatement(string $line, array &$state): bool
    {
        $length = strlen($line);

        for ($i = 0; $i < $length; $i++) {
            $char = $line[$i];

            if ($state['in_string']) {
                if ($state['escaped']) {
                    $state['escaped'] = false;
                    continue;
                }

                if ($char === '\\') {
                    $state['escaped'] = true;
                    continue;
                }

                if ($char === "'") {
                    $state['in_string'] = false;
                }

                continue;
            }

            if ($char === "'") {
                $state['in_string'] = true;
                continue;
            }

            if ($char === ';') {
                return true;
            }
        }

        return false;
    }

    private function parseValueBlock(string $valueBlock, array $columns): array
    {
        $rows = [];
        $fields = [];
        $field = '';
        $depth = 0;
        $inString = false;
        $escaped = false;
        $length = strlen($valueBlock);

        for ($i = 0; $i < $length; $i++) {
            $char = $valueBlock[$i];

            if ($inString) {
                $field .= $char;

                if ($escaped) {
                    $escaped = false;
                    continue;
                }

                if ($char === '\\') {
                    $escaped = true;
                    continue;
                }

                if ($char === "'") {
                    $inString = false;
                }

                continue;
            }

            if ($char === "'") {
                $inString = true;
                $field .= $char;
                continue;
            }

            if ($char === '(') {
                if ($depth > 0) {
                    $field .= $char;
                }
                $depth++;
                continue;
            }

            if ($char === ')') {
                if ($depth > 1) {
                    $field .= $char;
                    $depth--;
                    continue;
                }

                $fields[] = $this->normalizeSqlValue($field);
                $rows[] = array_combine($columns, $fields);
                $fields = [];
                $field = '';
                $depth = 0;
                continue;
            }

            if ($char === ',' && $depth === 1) {
                $fields[] = $this->normalizeSqlValue($field);
                $field = '';
                continue;
            }

            if ($depth >= 1) {
                $field .= $char;
            }
        }

        return $rows;
    }

    private function normalizeSqlValue(string $value): mixed
    {
        $value = trim($value);

        if (strcasecmp($value, 'NULL') === 0) {
            return null;
        }

        if ($value === '') {
            return '';
        }

        if ($value[0] === "'" && substr($value, -1) === "'") {
            return stripcslashes(substr($value, 1, -1));
        }

        if (is_numeric($value)) {
            return str_contains($value, '.') ? (float) $value : (int) $value;
        }

        return $value;
    }
}