TableHelper.php

<?php

namespace UnicaenDbImport\CodeGenerator\Helper;

use Doctrine\DBAL\DBALException;
use UnicaenApp\Exception\LogicException;
use UnicaenApp\Exception\RuntimeException;
use UnicaenApp\Util;
use UnicaenDbImport\CodeGenerator\Helper;
use UnicaenDbImport\Domain\Operation;

/**
 * Génération de code SQL autour des tables.
 *
 * Version commune à toutes les plateformes de bases de données.
 *
 * @author Unicaen
 */
abstract class TableHelper extends Helper
{
    const V_DIFF_COLUMN_PREFIX = 'S_';

    /**
     * @param string $tableName
     * @return string
     */
    public function generateSQLForSelectFromTable($tableName)
    {
        return "SELECT * FROM " . $tableName;
    }

    /**
     * @param string   $destinationTable
     * @param string   $sourceCodeColumn
     * @param string[] $columns
     * @param string   $intermediateTable
     * @return string
     */
    public function generateSQLForIntermediateTableCreation($destinationTable, $sourceCodeColumn, $columns, $intermediateTable)
    {
        $commaSeparatedColumnNames = implode(', ', array_merge([$sourceCodeColumn], $columns));

        $sqlTemplate = <<<'EOT'
create table {intermediateTable} as select {commaSeparatedColumnNames} from {destinationTable};
delete from {intermediateTable};
EOT;
        return Util::tokenReplacedString($sqlTemplate, compact('intermediateTable', 'destinationTable', 'commaSeparatedColumnNames'));
    }

    /**
     * @param string $tableName
     * @return string
     */
    public function generateSQLForTruncateTable($tableName)
    {
        return $this->platform->getTruncateTableSQL($tableName);
    }

    /**
     * @param string $diffSourceTableName
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array  $columns
     * @return string
     * @throws DBALException
     */
    public function generateSQLForDiffViewCreation($diffSourceTableName, $destinationTable, $sourceCodeColumn, array $columns)
    {
        $res = '';
        $res .= $this->generateDiffViewDeletionSQLSnippet($destinationTable) . ';' . PHP_EOL;
        $res .= $this->generateDiffViewCreationSQLSnippet($diffSourceTableName, $destinationTable, $sourceCodeColumn, $columns) . PHP_EOL;

        return $res;
    }

    /**
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array  $columns
     * @return string
     */
    public function generateSQLForDiffViewSelect($destinationTable, $sourceCodeColumn, array $columns)
    {
        // colonne ID écartée
        $columns = array_diff($columns, ['ID', 'id']);

        $name = $this->generateDiffViewName($destinationTable);

        $updatedColumsSql = implode(
            array_map(function($col) {
                return sprintf('U_%s', $col);
            }, $columns),
            ','
        );

        $srcColumsSql = implode(
            array_map(function($col) {
                return sprintf('S_%s', $col);
            }, $columns),
            ',' . PHP_EOL
        );

        $destColumsSql = implode(
            array_map(function($col) {
                return sprintf('D_%s', $col);
            }, $columns),
            ',' . PHP_EOL
        );

        $sql = <<<EOT
SELECT 
    $sourceCodeColumn, 
    operation, 
    $updatedColumsSql, 
    $srcColumsSql, 
    $destColumsSql 
FROM $name
EOT;

        return $sql;
    }

    /**
     * @param string $destinationTable
     * @return string
     */
    protected function generateDiffViewName($destinationTable)
    {
        return 'V_DIFF_' . $destinationTable;
    }

    /**
     * @param string $destinationTable
     * @return string
     * @throws DBALException
     */
    protected function generateDiffViewDeletionSQLSnippet($destinationTable)
    {
        $name = $this->generateDiffViewName($destinationTable);

        return $this->platform->getDropViewSQL($name);
    }

    /**
     * @param string $diffSourceTableName
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array  $columns
     * @return string
     */
    protected function generateDiffViewCreationSQLSnippet($diffSourceTableName, $destinationTable, $sourceCodeColumn, array $columns)
    {
        $name = $this->generateDiffViewName($destinationTable);

        $res = '';
        $res .= "CREATE VIEW $name AS " . PHP_EOL;
        $res .= $this->indent(4, $this->generateDiffViewBodySelectSQLSnippet($diffSourceTableName, $destinationTable, $sourceCodeColumn, $columns)) . PHP_EOL;

        return $res;
    }

    /**
     * @param string $diffSourceTableName
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array  $columns
     * @return string
     */
    protected function generateDiffViewBodySelectSQLSnippet($diffSourceTableName, $destinationTable, $sourceCodeColumn, array $columns)
    {
        // colonne ID écartée
        $columns = array_diff($columns, ['ID', 'id']);

        $insert = Operation::OPERATION_INSERT;
        $update = Operation::OPERATION_UPDATE;
        $undelete = Operation::OPERATION_UNDELETE;
        $delete = Operation::OPERATION_DELETE;

        $updatedColumsSql = implode(
            array_map(function($col) {
                $tpl = 'CASE WHEN d.%s <> s.%1$s OR (d.%1$s IS NULL AND s.%1$s IS NOT NULL) OR (d.%1$s IS NOT NULL AND s.%1$s IS NULL) THEN 1 ELSE 0 END as U_%1$s';
                return sprintf($tpl, $col);
            }, $columns),
            ',' . PHP_EOL
        );

        $srcColumsSql = implode(
            array_map(function($col) {
                return sprintf('s.%1$s AS S_%1$s', $col);
            }, $columns),
            ',' . PHP_EOL
        );

        $destColumsSql = implode(
            array_map(function($col) {
                return sprintf('d.%1$s AS D_%1$s', $col);
            }, $columns),
            ',' . PHP_EOL
        );

        $udpatedColumnsAddition = implode(
            array_map(function($col) {
                return sprintf('U_%s', $col);
            }, $columns),
            '+'
        );

        $now = $this->platform->getNowExpression();

        $deletedOnColumn = $this->config->getHistoColumnAliasForDeletedOn();

        return <<<EOT
with diff as (
    SELECT
        coalesce(s.$sourceCodeColumn, d.$sourceCodeColumn) $sourceCodeColumn,
        coalesce(s.source_id, d.source_id) source_id,

        case 
        when (s.$sourceCodeColumn IS NOT NULL AND d.$sourceCodeColumn IS NULL) then '$insert'
        when (s.$sourceCodeColumn IS NOT NULL AND d.$sourceCodeColumn IS NOT NULL and (d.$deletedOnColumn IS NULL or d.$deletedOnColumn > $now)) then '$update'
        when (s.$sourceCodeColumn IS NOT NULL AND d.$sourceCodeColumn IS NOT NULL and (d.$deletedOnColumn IS NOT NULL and d.$deletedOnColumn <= $now)) then '$undelete'
        when (s.$sourceCodeColumn IS NULL AND d.$sourceCodeColumn IS NOT NULL and (d.$deletedOnColumn IS NULL or d.$deletedOnColumn > $now)) then '$delete' end as operation,

        $updatedColumsSql,

        $srcColumsSql,

        $destColumsSql

    FROM $destinationTable d
    JOIN source src ON src.id = d.source_id AND src.importable = 1
    FULL OUTER JOIN $diffSourceTableName s ON s.source_id = d.source_id AND s.$sourceCodeColumn = d.$sourceCodeColumn
)
select * from diff
where operation is not null
and (operation = '$undelete' or 0 < $udpatedColumnsAddition)
EOT;
    }

    /**
     * @param array $rows
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param string[] $columns
     * @param string|null|false $idColumnSequence
     * @return string[]
     */
    public function generateSQLForOperationInDestinationTableFromDiffViewRequestResult(
        array $rows,
        $destinationTable,
        $sourceCodeColumn,
        $columns,
        $idColumnSequence = null)
    {
        // colonne ID écartée
        $columns = array_diff($columns, ['ID', 'id']);

        // réorganisation par type d'opération
        $data = [];
        foreach ($rows as $row) {
            $operation = $row['operation'] ?? $row['OPERATION'];
            if (!isset($data[$operation])) {
                $data[$operation] = [];
            }
            $data[$operation][] = $row;
        };

        $sqls = [];

        // une instruction SQL (update ou insert) par type d'opération suffira à mettre à jour la table destination
        $operations = array_keys($data);
        foreach ($operations as $operation) {
            switch ($operation) {
                case Operation::OPERATION_INSERT:
                    $sqls[] = $this->generateSQLForInsertOperationInDestinationTable(
                        $destinationTable,
                        $sourceCodeColumn,
                        $columns,
                        $idColumnSequence) . PHP_EOL;
                    break;
                case Operation::OPERATION_UPDATE:
                    $sqls[] = $this->generateSQLForUpdateOperationInDestinationTable(
                        $destinationTable,
                        $sourceCodeColumn,
                        $columns) . PHP_EOL;
                    break;
                case Operation::OPERATION_UNDELETE:
                    $sqls[] = $this->generateSQLForUndeleteOperationInDestinationTable(
                        $destinationTable,
                        $sourceCodeColumn,
                        $columns) . PHP_EOL;
                    break;
                case Operation::OPERATION_DELETE:
                    $sqls[] = $this->generateSQLForDeleteOperationInDestinationTable(
                        $destinationTable,
                        $sourceCodeColumn,
                        $columns) . PHP_EOL;
                    break;
                default:
                    throw new RuntimeException("Opération inconnue rencontrée : " . $operation);
                    break;
            }
        }

//        return empty($sqls) ? '' : implode(PHP_EOL, $sqls);
        return $sqls;
    }

    /**
     * @param string $destinationTable
     * @param array $columnsValues
     * @param string $sourceCode
     * @param string|null|false $idColumnSequence
     * @return string
     */
    public function generateSQLForInsertIntoTable(
        string $destinationTable,
        array $columnsValues,
        string $sourceCode,
        $idColumnSequence = null)
    {
        $now = $this->platform->getNowExpression();

        $columnsValues = array_map([$this, 'formatValueForInsert'], $columnsValues);

        $useSequenceForId = true;
        if ($idColumnSequence === false) {
            // si false est spécifié comme nom de séquence pour la colonne 'id', le type de cette colonne devra être
            // auto-incrémentable pour que l'INSERT fonctionne.
            $useSequenceForId = false;
        } elseif ($idColumnSequence === null) {
            // si aucun nom de séquence n'a été fourni, on tente celui par défaut.
            $idColumnSequence = $this->generateSQLForIdSequenceDefaultName($destinationTable);
        }
        if ($useSequenceForId) {
            // les éventuels id d'origine sont écrasés
            unset($columnsValues['id']);
            unset($columnsValues['ID']);
            $idExpr = $this->generateSQLForSequenceNextVal($idColumnSequence);
            $columnsValues['id'] = $idExpr;
        }

        $createdOnCol = $this->config->getHistoColumnAliasForCreatedOn();
        $createdByCol = $this->config->getHistoColumnAliasForCreatedBy();
        $createdByValue = $this->config->getHistoColumnValueForCreatedBy();

        // génération des noms de colonnes de la table destination pour l'INSERT.
        $destinationColumns = array_keys($columnsValues);
        array_unshift($destinationColumns,
            $createdOnCol,
            $createdByCol,
            'source_id',
        );
        // génération de la liste des valeurs à INSERTer.
        $selectExpressions = $columnsValues;
        array_unshift($selectExpressions,
            $now,
            $createdByValue !== null ? $createdByValue : 'NULL',
            's.id',
        );

        $commaSeparatedColumnNames = implode(', ', $destinationColumns);
        $commaSeparatedColumnValues = implode(', ', $selectExpressions);

        return <<<EOT
INSERT INTO $destinationTable d ($commaSeparatedColumnNames) 
SELECT $commaSeparatedColumnValues 
FROM source s
WHERE s.code = '$sourceCode';
EOT;
    }

    /**
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array $columns
     * @param string|null|false $idColumnSequence
     * @return string
     */
    public function generateSQLForInsertOperationInDestinationTable(
        $destinationTable,
        $sourceCodeColumn,
        array $columns,
        $idColumnSequence = null)
    {
        $diffViewName = $this->generateDiffViewName($destinationTable);
        $now = $this->platform->getNowExpression();

        $useSequenceForId = true;
        if ($idColumnSequence === false) {
            // si false est spécifié comme nom de séquence pour la colonne 'id', le type de cette colonne devra être
            // auto-incrémentable pour que l'INSERT fonctionne.
            $useSequenceForId = false;
        } elseif ($idColumnSequence === null) {
            // si aucun nom de séquence n'a été fourni, on tente celui par défaut.
            $idColumnSequence = $this->generateSQLForIdSequenceDefaultName($destinationTable);
        }

        $createdOnCol = $this->config->getHistoColumnAliasForCreatedOn();
        $createdByCol = $this->config->getHistoColumnAliasForCreatedBy();
        $createdByValue = $this->config->getHistoColumnValueForCreatedBy();

        // génération des noms de colonnes de la table destination pour l'INSERT.
        $destinationColumns = $columns;
        array_unshift($destinationColumns,
            $createdOnCol,
            $createdByCol,
            $sourceCodeColumn,
            'source_id',
        );
        if ($useSequenceForId) {
            array_unshift($destinationColumns,
                'id',
            );
        }

        // génération de la liste des noms de colonnes provenant de la vue diff pour l'INSERT.
        $selectExpressions = array_map(function (string $columnName) {
            return self::V_DIFF_COLUMN_PREFIX . strtoupper($columnName);
        }, $columns);
        array_unshift($selectExpressions,
            $now,
            $createdByValue !== null ? $createdByValue : 'NULL',
            $sourceCodeColumn,
            'source_id'
        );
        if ($useSequenceForId) {
            $idExpr = $this->generateSQLForSequenceNextVal($idColumnSequence);
            array_unshift($selectExpressions,
                $idExpr,
            );
        }

        $operation = Operation::OPERATION_INSERT;

        // instruction de mise à jour de la table destination
        $markup = '';
        $markup .= "INSERT INTO $destinationTable (" . implode(', ', $destinationColumns) . ")" . PHP_EOL;
        $markup .= "SELECT " . implode(', ', $selectExpressions) . PHP_EOL;
        $markup .= "FROM $diffViewName WHERE operation = '$operation' ";

        return $markup;
    }

    /**
     * @param string $operation
     * @param array $columns
     * @return string
     */
    protected function generateSQLUpdateSetters(string $operation, array $columns)
    {
        $now = $this->platform->getNowExpression();

        switch ($operation) {

            case Operation::OPERATION_UPDATE:
            case Operation::OPERATION_UNDELETE:
                $colUpdates = array_map(function ($col) {
                    return "$col = diff.S_$col";
                }, $columns);

                $updatedOnColumn = $this->config->getHistoColumnAliasForUpdatedOn();
                $updatedByColumn = $this->config->getHistoColumnAliasForUpdatedBy();
                $updatedByValue = $this->config->getHistoColumnValueForUpdatedBy();
                $deletedOnColumn = $this->config->getHistoColumnAliasForDeletedOn();
                $deletedByColumn = $this->config->getHistoColumnAliasForDeletedBy();
                $updatedByValue = $updatedByValue !== null ? $updatedByValue : 'NULL';
                array_unshift($colUpdates,
                    "$updatedOnColumn = $now",
                    "$updatedByColumn = $updatedByValue",
                    "$deletedOnColumn = null",
                    "$deletedByColumn = null"
                );

                $setters = implode(', ', $colUpdates);
                break;

            case Operation::OPERATION_DELETE:
                $deletedOnColumn = $this->config->getHistoColumnAliasForDeletedOn();
                $deletedByColumn = $this->config->getHistoColumnAliasForDeletedBy();
                $deletedByValue = $this->config->getHistoColumnValueForDeletedBy();
                $deletedByValue = $deletedByValue !== null ? $deletedByValue : 'NULL';
                $colUpdates = [
                    "$deletedOnColumn = $now",
                    "$deletedByColumn = $deletedByValue"
                ];

                $setters =  implode(', ', $colUpdates);
                break;

            default:
                throw new LogicException("Opération spécifiée inattendue: " . $operation);
        }

        return $setters;
    }

    /**
     * @param string $tableName
     * @return string
     */
    public function generateSQLForIdSequenceDefaultName(string $tableName)
    {
        return $tableName . '_ID_SEQ';
    }

    /**
     * @param string $sequenceName
     * @return string
     */
    abstract public function generateSQLForSequenceNextVal(string $sequenceName);

    /**
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array $columns
     * @return string
     */
    abstract public function generateSQLForUpdateOperationInDestinationTable($destinationTable, $sourceCodeColumn, array $columns);

    /**
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array $columns
     * @return string
     */
    abstract public function generateSQLForUndeleteOperationInDestinationTable($destinationTable, $sourceCodeColumn, array $columns);

    /**
     * @param string $destinationTable
     * @param string $sourceCodeColumn
     * @param array $columns
     * @return string
     */
    abstract public function generateSQLForDeleteOperationInDestinationTable($destinationTable, $sourceCodeColumn, array $columns);
}