Dotrine (MySQL) SchemaToool Resolver (Support Custom Relation Name & Invalid Default 'NULL' detected as string - Timestamp Object vs Default current_timestamp())

SchemaToolListener.php

<?php
declare(strict_types=1);

namespace App\EventListener;

use Doctrine\Bundle\DoctrineBundle\Attribute\AsDoctrineListener;
use Doctrine\DBAL\Platforms\AbstractMySQLPlatform;
use Doctrine\DBAL\Schema\AbstractSchemaManager;
use Doctrine\DBAL\Schema\Column;
use Doctrine\DBAL\Schema\ComparatorConfig;
use Doctrine\DBAL\Schema\DefaultExpression;
use Doctrine\DBAL\Schema\DefaultSchemaManagerFactory;
use Doctrine\DBAL\Schema\ForeignKeyConstraint\Deferrability;
use Doctrine\DBAL\Schema\ForeignKeyConstraint\ReferentialAction;
use Doctrine\DBAL\Types\BigIntType;
use Doctrine\DBAL\Types\BinaryType;
use Doctrine\DBAL\Types\BlobType;
use Doctrine\DBAL\Types\DateImmutableType;
use Doctrine\DBAL\Types\DateIntervalType;
use Doctrine\DBAL\Types\DateTimeImmutableType;
use Doctrine\DBAL\Types\DateTimeType;
use Doctrine\DBAL\Types\DateTimeTzImmutableType;
use Doctrine\DBAL\Types\DateTimeTzType;
use Doctrine\DBAL\Types\DateType;
use Doctrine\DBAL\Types\DecimalType;
use Doctrine\DBAL\Types\FloatType;
use Doctrine\DBAL\Types\IntegerType;
use Doctrine\DBAL\Types\SmallIntType;
use Doctrine\DBAL\Types\StringType;
use Doctrine\DBAL\Types\TimeImmutableType;
use Doctrine\DBAL\Types\TimeType;
use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Tools\Event\GenerateSchemaTableEventArgs;
use Doctrine\ORM\Tools\ToolEvents;
use Throwable;
use function array_change_key_case;
use function array_combine;
use function array_key_exists;
use function array_map;
use function in_array;
use function is_a;
use function is_object;
use function is_string;
use function method_exists;
use function preg_replace;
use function str_replace;
use function strtolower;
use function strtoupper;
use function trim;
use function ucfirst;

#[AsDoctrineListener(event: ToolEvents::postGenerateSchemaTable, priority: 500, connection: 'default')]
final class SchemaToolListener
{
    private AbstractSchemaManager $schemaManager;

    private bool $is_mysql;

    public const array RESERVED_UPDATE = [
        'onUpdate',
        'onupdate',
        'on_update',
    ];

    public function __construct(protected EntityManagerInterface $entityManager)
    {
    }

    public function isMySql(): bool
    {
        try {
            return $this->is_mysql ??= $this->entityManager
                    ->getConnection()
                    ->getDatabasePlatform() instanceof AbstractMySQLPlatform;
        } catch (Throwable) {
            return $this->is_mysql = false;
        }
    }

    /**
     * @throws Throwable
     */
    public function postGenerateSchemaTable(GenerateSchemaTableEventArgs $eventArgs): void
    {
        if (!$this->isMySql()) {
            return;
        }
        $classTable = $eventArgs->getClassTable();
        $platform = $this->entityManager->getConnection()->getDatabasePlatform();
        $schema = $eventArgs->getSchema();
        $foreignKeys = $classTable->getForeignKeys();
        $metadata = $eventArgs->getClassMetadata();
        $associations = $metadata->getAssociationMappings();
        $tableName = $classTable->getObjectName()->toString();
        $this->schemaManager ??= ($this->entityManager
            ->getConfiguration()
            ->getSchemaManagerFactory() ?? new DefaultSchemaManagerFactory())
            ->createSchemaManager($this->entityManager->getConnection());
        $original_schema = $this->schemaManager->introspectSchema();
        foreach ($classTable->getColumns() as $column) {
            try {
                $definition = $this->getColumnDeclarationSQLOnUpdate(
                    $column
                );
                if ($definition) {
                    $column->setColumnDefinition($definition);
                }
            } catch (Throwable) {
            }
        }

        $comparatorConfig = new ComparatorConfig()
            ->withReportModifiedIndexes(false);
        $diff = $this
            ->schemaManager
            ->createComparator($comparatorConfig)
            ->compareSchemas($original_schema, $schema);
        foreach ($diff->getAlteredTables() as $alteredTable) {
            if ($alteredTable->getOldTable()->getObjectName()->toString() !== $tableName) {
                continue;
            }
            foreach ($alteredTable->getChangedColumns() as $column) {
                $old_column = $column->getOldColumn();
                $new_column = $column->getNewColumn();
                $old_type = $old_column->getType();
                $new_type = $new_column->getType();
                $changed = Type::lookupName($old_type) !== Type::lookupName($new_type);

                if (!$changed) {
                    $old_arr = $old_column->toArray();
                    $new_arr = $new_column->toArray();
                    unset($old_arr['type'], $new_arr['type']);
                    if (($old_arr['default'] ?? null) === 'NULL') {
                        $new_arr['default'] = $old_arr['default'];
                    }
                    // fix timestamp default value, as it can be represented as string or expression,
                    // so compare them and if they are the same, use the old value to avoid diff
                    if (is_string($old_arr['default'] ?? null)
                        && ($default_ts = ($new_arr['default'] ?? null)) instanceof DefaultExpression
                    ) {
                        /**
                         * @var DefaultExpression $default_ts
                         */
                        $exp = strtoupper($default_ts->toSQL($platform));
                        $default = strtoupper($old_arr['default']);
                        if ($exp === $default || "$exp()" === $default) {
                            $new_arr['default'] = $old_arr['default'];
                        }
                    }
                    foreach ($old_arr as $key => $value) {
                        if (!($exists = array_key_exists($key, $new_arr))
                            && $value !== null || ($exists && $value !== $new_arr[$key])
                        ) {
                            $changed = true;
                            break;
                        }
                    }
                }
                if (!$changed) {
                    try {
                        $copy = $old_arr;
                        foreach ($old_arr as $key => $value) {
                            if (array_key_exists($key, $new_arr)) {
                                $copy[$key] = $value;
                            }
                        }
                        foreach ($new_arr as $key => $value) {
                            if (!array_key_exists($key, $copy)) {
                                $copy[$key] = $value;
                            }
                        }
                        // NO DIFF, but some attributes changed, so update the column with new attributes
                        unset($copy['name']);
                        $col = $classTable->getColumn($new_column->getObjectName()->toString());
                        foreach ($copy as $method => $value) {
                            if (is_string($method)) {
                                $m = 'set' . ucfirst($method);
                                if (method_exists($old_column, $m)) {
                                    $col->$m($value);
                                }
                            }
                        }
                    } catch (Throwable $e) {
                    }
                }
            }
            break;
        }
        $allowedTrigger = ['CASCADE', 'NO ACTION', 'RESTRICT', 'SET NULL'];
        foreach ($associations as $association) {
            if (empty($association->joinColumns)
                || empty($association->targetToSourceKeyColumns)
            ) {
                continue;
            }
            $columns = array_change_key_case(
                array_map('strtolower', $association->targetToSourceKeyColumns)
            );
            $onUpdate = null;
            $onDelete = null;
            $relationName = null;
            foreach ($association->joinColumns as $item) {
                $options = $item->options ?? null;
                if (empty($options)) {
                    continue;
                }
                $options = array_change_key_case($options);
                $relationName = $relationName ?? $options['relationName'] ?? null;
                $relationName = $relationName && is_string($relationName) ? trim($relationName) : null;
                if (!$relationName
                    && isset($options['relation_name'])
                    && is_string($options['relation_name'])
                ) {
                    $relation = trim($options['relation_name']);
                    if ($relation !== '') {
                        $relationName = $relation;
                    }
                }

                if (!$onUpdate) {
                    $update = !empty($options['onUpdate'])
                        ? $options['onUpdate']
                        : (
                        !empty($options['onupdate']) ? $options['onupdate'] : ($options['on_update'] ?? null)
                        );
                    /** @noinspection DuplicatedCode */
                    $update = is_string($update)
                        ? strtoupper(str_replace('  ', '', $update))
                        : null;
                    if ($update === 'SETNULL'
                        || $update === 'SET-NULL'
                        || $update === 'SET_NULL'
                        || $update === 'NULL'
                    ) {
                        $update = 'SET NULL';
                    }
                    if ($update === 'NOACTION'
                        || $update === 'NO-ACTION'
                        || $update === 'NO_ACTION'
                    ) {
                        $update = 'NO ACTION';
                    }
                    if ($update && in_array($update, $allowedTrigger)) {
                        $onUpdate = $update;
                    }
                }
                if (!$onDelete) {
                    $delete = !empty($options['onDelete'])
                        ? $options['onDelete']
                        : (
                        !empty($options['ondelete']) ? $options['ondelete'] : ($options['on_delete'] ?? null)
                        );
                    /** @noinspection DuplicatedCode */
                    $delete = is_string($delete)
                        ? strtoupper(str_replace('  ', '', $delete))
                        : null;
                    // resolve
                    if ($delete === 'SETNULL'
                        || $delete === 'SET-NULL'
                        || $delete === 'SET_NULL'
                        || $delete === 'NULL'
                    ) {
                        $delete = 'SET NULL';
                    }
                    if ($delete === 'NOACTION'
                        || $delete === 'NO-ACTION'
                        || $delete === 'NO_ACTION'
                    ) {
                        $delete = 'NO ACTION';
                    }
                    if ($delete && in_array($delete, $allowedTrigger)) {
                        $onDelete = $delete;
                    }
                }
            }

            $validName = is_string($relationName) && $relationName !== '';
            $targetTable = $this
                ->entityManager
                ->getClassMetadata($association['targetEntity'])
                ->getTableName();
            foreach ($foreignKeys as $foreignName => $foreignKey) {
                $name = is_string($foreignName) ? $foreignName : $foreignKey->getObjectName()?->toString() ?? '';
                if (!str_starts_with(strtolower($name), 'fk_')
                    || $foreignKey->getReferencedTableName()->toString() !== ($targetTable)
                ) {
                    continue;
                }
                try {
                    $onDelete ??= $foreignKey->getOnDeleteAction()->name;
                } catch (Throwable) {
                }
                try {
                    $onUpdate ??= $foreignKey->getOnUpdateAction()->name;
                } catch (Throwable) {
                }
                $foreign = array_map(fn($e) => ($e->toString()), $foreignKey->getReferencedColumnNames());
                $local = array_map(fn($e) => ($e->toString()), $foreignKey->getReferencingColumnNames());
                $dataColumns = array_combine($foreign, $local);
                foreach ($columns as $key => $v) {
                    if (!isset($dataColumns[$key]) || $dataColumns[$key] !== $v) {
                        continue 2;
                    }
                }
                $validName = $validName && $relationName ? $relationName : $foreignName;
                $onUpdate = match ($onUpdate) {
                    'CASCADE' => ReferentialAction::CASCADE,
                    'NO ACTION',
                    'NO_ACTION' => ReferentialAction::NO_ACTION,
                    'RESTRICT' => ReferentialAction::RESTRICT,
                    'SET NULL',
                    'SET_NULL' => ReferentialAction::SET_NULL,
                    default => null
                };
                $onDelete = match ($onDelete) {
                    'CASCADE' => ReferentialAction::CASCADE,
                    'NO ACTION',
                    'NO_ACTION' => ReferentialAction::NO_ACTION,
                    'RESTRICT' => ReferentialAction::RESTRICT,
                    'SET NULL',
                    'SET_NULL' => ReferentialAction::SET_NULL,
                    default => null
                };
                $options = [];
                if ($onUpdate) {
                    $options['onUpdate'] = $onUpdate->value;
                }
                if ($onDelete) {
                    $options['onDelete'] = $onDelete->value;
                }
                try {
                    $defer = $foreignKey->getDeferrability();
                    $options['deferrable'] = $defer->value === Deferrability::DEFERRABLE->value;
                } catch (Throwable) {
                }
                try {
                    $match_type = $foreignKey->getMatchType();
                    $options['match'] = $match_type->value;
                } catch (Throwable) {
                }
                $classTable->dropForeignKey($foreignName);
                $classTable->addForeignKeyConstraint(
                    $foreignKey->getReferencedTableName()->toString(),
                    $local,
                    $foreign,
                    $options,
                    $validName,
                );
            }
        }
    }

    /**
     * @throws Throwable
     */
    protected function getColumnDeclarationSQLOnUpdate(Column $column): ?string
    {
        if (!$this->isMySql() || $column->getColumnDefinition() !== null) {
            return null;
        }
        $columnAttributes = $column->toArray();
        $platform = $this->entityManager->getConnection()->getDatabasePlatform();
        $attribute = '';
        if (!isset($columnAttributes['type'])) {
            return '';
        }
        $type = $columnAttributes['type'];
        if (!($type instanceof Type)) {
            return '';
        }
        $supportedType = [
            Types::DATE_MUTABLE => DateType::class,
            Types::DATE_IMMUTABLE => DateImmutableType::class,
            Types::DATEINTERVAL => DateIntervalType::class,
            Types::DATETIME_MUTABLE => DateTimeType::class,
            Types::DATETIME_IMMUTABLE => DateTimeImmutableType::class,
            Types::DATETIMETZ_MUTABLE => DateTimeTzType::class,
            Types::DATETIMETZ_IMMUTABLE => DateTimeTzImmutableType::class,
            Types::TIME_MUTABLE => TimeType::class,
            Types::TIME_IMMUTABLE => TimeImmutableType::class,
        ];
        $attributeName = Type::lookupName($type);
        if (!isset($supportedType[$attributeName])) {
            return '';
        }
        if (!empty($columnAttributes['attribute'])) {
            $attr = $this->getColumnDeclarationAttributeOnUpdate(
                $columnAttributes['attribute'],
                $columnAttributes
            );
            $attribute = $attr ? " $attr" : '';
        } else {
            $onUpdate = null;
            foreach (self::RESERVED_UPDATE as $item) {
                if (!isset($columnAttributes[$item])) {
                    continue;
                }
                if (is_string($columnAttributes[$item])
                    && trim($columnAttributes[$item]) !== ''
                    || $columnAttributes[$item] instanceof DefaultExpression
                ) {
                    $onUpdate = $columnAttributes[$item];
                    break;
                }
            }
            if ($onUpdate) {
                $attr = $this->getColumnDeclarationAttributeOnUpdate(
                    $onUpdate,
                    $columnAttributes
                );
                $attribute = $attr ? " $attr" : '';
            }
        }
        /*if ($attribute === '') {
            return null;
        }*/
        $default = $platform->getDefaultValueDeclarationSQL($columnAttributes);
        $charset = !empty($columnAttributes['charset']) ?
            ' ' . $platform->getColumnCharsetDeclarationSQL($columnAttributes['charset']) : '';

        $collation = !empty($columnAttributes['collation']) ?
            ' ' . $platform->getColumnCollationDeclarationSQL($columnAttributes['collation']) : '';
        $notnull = $column->getNotnull() ? ' NOT NULL' : '';
        $unique = '';
        $check = '';
        $type = $column->getType();
        $typeDecl = $type->getSQLDeclaration($columnAttributes, $platform);
        $declaration = $typeDecl . $attribute . $charset . $default . $notnull . $unique . $check . $collation;
        if ($platform->supportsInlineColumnComments()) {
            $comment = $column->getComment();
            /*if ($type instanceof DateTimeType
                || $type instanceof TimeType
                || $type instanceof JsonType
                || $type instanceof SimpleArrayType
            ) {
                $type = Type::lookupName($type);
                $comment = sprintf(
                    '%s(%s:%s)',
                    $comment,
                    'DC4Type',
                    $type
                );
            }*/
            if ($comment) {
                $declaration .= ' ' . $platform->getInlineColumnCommentSQL($comment);
            }
        }

        return $declaration;
    }

    /**
     * @throws Throwable
     */
    private function getColumnDeclarationAttributeOnUpdate(
        $attribute,
        array $attributes
    ): string {
        if (is_object($attribute)) {
            if ($attribute instanceof DefaultExpression) {
                $sql = strtoupper($attribute->toSQL($this->entityManager->getConnection()->getDatabasePlatform()));
                return "ON UPDATE $sql";
            }
            return '';
        } elseif (!is_string($attribute) || trim($attribute) === '') {
            return '';
        }
        if (str_starts_with(
            str_replace(' ', '', $attribute),
            'ON_UPDATE_CURRENT_TIMESTAMP'
        )
        ) {
            $attribute = 'ON UPDATE CURRENT_TIMESTAMP';
        } elseif (str_starts_with(
            str_replace(' ', '', $attribute),
            'UNSIGNED_ZEROFILL'
        )
        ) {
            $attribute = 'UNSIGNED ZEROFILL';
        }
        $attribute = preg_replace('~\s+~', ' ', $attribute);
        $allowedAttributes = [
            'ON UPDATE CURRENT_TIMESTAMP' => [
                DateTimeType::class,
                TimeType::class
            ],
            'COMPRESSED=ZLIB' => [
                BlobType::class,
                StringType::class,
                BinaryType::class,
            ],
            'UNSIGNED ZEROFILL' => [
                BigIntType::class,
                SmallIntType::class,
                IntegerType::class,
                DecimalType::class,
                FloatType::class,
            ],
            'UNSIGNED' => [
                BigIntType::class,
                SmallIntType::class,
                IntegerType::class,
                DecimalType::class,
                FloatType::class
            ],
        ];
        $current = $allowedAttributes[$attribute] ?? null;
        if (!$current) {
            return '';
        }

        if (array_any($current, fn($className) => is_a($attributes['type'], $className))) {
            return $attribute === 'COMPRESSED=ZLIB'
                ? 'COMPRESSED=zlib'
                : $attribute;
        }
        return '';
    }
}