RunSQLProcess.php

<?php

namespace UnicaenApp\Service\SQL;

use Doctrine\DBAL\Connection;
use Exception;
use Laminas\Log\LoggerAwareTrait;
use Laminas\Stdlib\Glob;
use UnicaenApp\Exception\RuntimeException;

class RunSQLProcess
{
    use LoggerAwareTrait;

    const QUERIES_SPLIT_PATTERN = "#^/$#m"; // oracle !!
    const LOG_FILE_EXT = '.log.sql';
    const LOG_FILE_EXT_PATTERN = '.log.*.sql';
    const LOG_FILE_EXT_TEMPLATE = '.log.%d.sql';

    /**
     * @var string
     */
    private $scriptPath;

    /**
     * @var string
     */
    private $logFilePath;

    /**
     * @var Connection
     */
    private $connection;

    /**
     * @var string
     */
    private $queriesSplitPattern = self::QUERIES_SPLIT_PATTERN;

    /**
     * @var string[]
     */
    private $queries;

    /**
     * @var RunSQLQueryStack
     */
    private $executedQueriesStack;

    /**
     * @param string $scriptPath
     * @return self
     */
    public function setScriptPath(string $scriptPath)
    {
        $this->scriptPath = $scriptPath;

        return $this;
    }

    /**
     * @param Connection $connection
     * @return RunSQLProcess
     */
    public function setConnection(Connection $connection)
    {
        $this->connection = $connection;

        return $this;
    }

    /**
     * @param null|string $logFilePath
     * @return self
     */
    public function setLogFilePath($logFilePath = null)
    {
        $this->logFilePath = $logFilePath;

        return $this;
    }

    /**
     * @param string $queriesSplitPattern
     * @return self
     */
    public function setQueriesSplitPattern($queriesSplitPattern)
    {
        $this->queriesSplitPattern = $queriesSplitPattern;

        return $this;
    }

    /**
     * Exécute dans la transaction courante toutes les instructions d'un script SQL.
     *
     * @return RunSQLResult
     */
    public function executeScript()
    {
        $this->validateScriptPath();
        $this->extractQueriesFromScript();

        $this->logger->info("+ Exécution du script '$this->scriptPath'.");
        $this->logger->info(sprintf("'--> Requêtes trouvées : %d", count($this->queries)));

        $result = $this->executeQueries();
        $this->createLogFile();

        return $result;
    }

    /**
     * Exécute dans la transaction courante toutes les instructions d'un script SQL.
     *
     * @param string $query
     * @return RunSQLResult
     */
    public function executeQuery(string $query)
    {
        $this->logger->info("+ Exécution d'une requête.");

        $this->queries = [$query];
        $result = $this->executeQueries();
        $this->createLogFile();

        return $result;
    }

    private function validateScriptPath()
    {
        if (is_dir($this->scriptPath)) {
            throw new RuntimeException("Le fichier '$this->scriptPath' spécifié est un répertoire");
        }
        if (!is_readable($this->scriptPath)) {
            throw new RuntimeException("Le fichier '$this->scriptPath' n'est pas accessible");
        }
    }

    /**
     * Extrait les requêtes contenues dans le script.
     */
    protected function extractQueriesFromScript()
    {
        $parts = preg_split($this->queriesSplitPattern, file_get_contents($this->scriptPath));
        $queries = array_filter(array_map('trim', $parts));

        if (count($queries) === 0) {
            throw new RuntimeException("Aucune requête trouvée dans le script '$this->scriptPath'");
        }

        $this->queries = $queries;
    }

    /**
     * Exécute dans la transaction courante les requêtes spécifiées.
     *
     * @return RunSQLResult
     */
    private function executeQueries()
    {
        $result = new RunSQLResult();
        $result->setScriptPath($this->scriptPath);
        $result->setLogFilePath($this->logFilePath);
        $result->attachLogger($this->logger);
        $result->setIsSuccess(true);

        $this->executedQueriesStack = new RunSQLQueryStack();

        try {
            foreach ($this->queries as $query) {
                $this->executedQueriesStack->startQuery($query);
                $this->connection->executeQuery($query);
                $this->executedQueriesStack->stopQuery();
            }
        } catch (\Doctrine\DBAL\Exception $e) {
            $result->setIsSuccess(false);
            $result->setException($e);

            $this->executedQueriesStack->stopQueryWithException($e);
        }

        $result->setEndMicrotime();

        $this->logger->info(sprintf("'--> Requêtes exécutées : %d", count($this->executedQueriesStack->getQueries())));

        return $result;
    }

    private function createLogFile()
    {
        $logFilePath = $this->computeLogFilePath();

        $executedQueries = $this->executedQueriesStack->getQueries();
        $remainingQueries = $this->computeRemainingQueries();

        $comment = function($line, $with = '--') {
            return $with . ' ' . str_replace(PHP_EOL, PHP_EOL . $with . ' ', $line);
        };

        $title = $this->scriptPath ?
            "Log d'exécution du script SQL '{$this->scriptPath}'." :
            "Log d'exécution d'une requête SQL.";

        $lines = [];
        $lines[] = "----------------------------------------------------------------------------------------------";
        $lines[] = "--";
        $lines[] = "-- $title";
        $lines[] = "--";
        $lines[] = "-- " . date_create()->format('d/m/Y H:m:s');
        $lines[] = "--";
        $lines[] = "----------------------------------------------------------------------------------------------";
        $lines[] = "";
        $lines[] = "";
        $lines[] = "--------------------- REQUÊTES EXÉCUTÉES ---------------------";
        $lines[] = "";
        foreach ($executedQueries as $query) {
            $hasSucceeded = ! isset($query['exception']);
            if ($hasSucceeded) {
                $lines[] = $comment($query['sql']);
                $lines[] = $comment("/");
                $lines[] = $comment("SUCCÈS");
            } else {
                $exception = $query['exception']; /** @var Exception $exception */
                $lines[] = $comment($query['sql']);
                $lines[] = $comment("/");
                $lines[] = $comment("ÉCHEC", '------');
                $lines[] = $comment("=====", '------');
                $lines[] = $comment($exception->getMessage(), '------');
                $lines[] = $comment("=====", '------');
            }
            $lines[] = $comment($query['executionMS'] . " sec");
            $lines[] = "";
        }
        $lines[] = "";
        $lines[] = "";
        $lines[] = "--------------------- REQUÊTES RESTANTES ---------------------";
        $lines[] = "";
        foreach ($remainingQueries as $query) {
            $lines[] = $query;
            $lines[] = "/";
        }

        file_put_contents($logFilePath, implode(PHP_EOL, $lines));

        $this->logger->info(sprintf("'--> Log script : %s", $logFilePath));
    }

    /**
     * @return string[]
     */
    private function computeRemainingQueries()
    {
        $executedQueries = $this->executedQueriesStack->getQueries();

        $offset = count($executedQueries);
        if ($this->executedQueriesStack->lastQueryHasException()) {
            $offset--; // si la dernière requête a échouée, on la remet quand même dans la liste des requêtes restantes
        }

        return array_slice($this->queries, $offset, null, true);
    }

    /**
     * @return string
     */
    private function computeLogFilePath()
    {
        if ($this->logFilePath !== null) {
            return $this->logFilePath;
        }

        $dir = sys_get_temp_dir();
        $scriptName = $this->scriptPath ? basename($this->scriptPath) : 'run-sql-query';

        $filepathPattern = $dir . '/' . $scriptName . self::LOG_FILE_EXT_PATTERN;
        $filepathTemplate = $dir . '/' . $scriptName . self::LOG_FILE_EXT_TEMPLATE;

        $existingFiles = Glob::glob($filepathPattern);

        return sprintf($filepathTemplate, count($existingFiles) + 1);
    }
}