DBAL 4 - custom data type without requiresSQLCommentHint

[jaroslavlibal]

Hello,

I'm trying to create a custom type for datetime with microseconds, but I can't figure how.

Schema tool always wants me to execute ALTER TABLE foo ALTER occurred_at TYPE IMESTAMP(6) WITHOUT TIME ZONE; because PostgreSQL introspection returns just timestamp without time zone, even if I defined timestamp(6).

Before DBAL 4, this could be easily solved by using a comment with an exact type and requiresSQLCommentHint().

Or in general - what can I do if I have multiple custom types (PHP objects) represented in the database by the same data type?

What should I do with DBAL 4, please? I am using PostgreSQL 17.1.

Here is my custom DateTimeImmutableMicroType:

<?php declare(strict_types = 1);

namespace MyApp\DateTime;

use DateTimeImmutable;
use DateTimeZone;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Types\DateTimeImmutableType as DoctrineDateTimeImmutableTypeAlias;
use Doctrine\DBAL\Types\Exception\InvalidFormat;
use Doctrine\DBAL\Types\Exception\InvalidType;

class DateTimeImmutableMicroType extends DoctrineDateTimeImmutableTypeAlias
{

    private const string DATABASE_TIME_ZONE = 'Europe/Prague';

    /**
     * {@inheritDoc}
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform): string
    {
        if (isset($fieldDeclaration['version']) && $fieldDeclaration['version'] === true) {
            return 'TIMESTAMP';
        }

        if ($platform instanceof PostgreSqlPlatform) {
            return 'TIMESTAMP(6) WITHOUT TIME ZONE';
        }

        return 'DATETIME(6)';
    }

    /**
     * @param T $value
     * @return (T is null ? null : string)
     * @template T
     */
    public function convertToDatabaseValue(mixed $value, AbstractPlatform $platform): ?string
    {
        if ($value === null) {
            return $value;
        }

        if ($value instanceof DateTimeImmutable) {
            $value = $value->setTimezone(new DateTimeZone(self::DATABASE_TIME_ZONE));

            return $value->format('Y-m-d H:i:s.u');
        }

        throw InvalidType::new(
            $value,
            static::class,
            ['null', DateTimeImmutable::class],
        );
    }

    /**
     * @param T $value
     * @return (T is null ? null : DateTimeImmutable)
     * @template T
     */
    public function convertToPHPValue(mixed $value, AbstractPlatform $platform): ?DateTimeImmutable
    {
        if ($value === null || $value instanceof DateTimeImmutable) {
            return $value;
        }

        $dateTime = DateTimeImmutable::createFromFormat('Y-m-d H:i:s.u', $value, new DateTimeZone(self::DATABASE_TIME_ZONE));

        if ($dateTime !== false) {
            return $dateTime;
        }

        try {
            return new DateTimeImmutable($value);
        } catch (\Throwable $e) {
            throw InvalidFormat::new(
                $value,
                static::class,
                $platform->getDateTimeFormatString(),
                $e,
            );
        }
    }
}

[jaroslavlibal]

Had to use the same workaround as in #6743

Is there a more elegant solution for problems like this that were originally solved by helper comments?

I think there will be some similar problems with LongitudeOne/Spatial/DBAL/Types.

[sbuerk]

For TYPO3 we reintroduced the type comment for "custom" types (custom SetType after ENUM type is now in dbal) which gives us some maintenance headache (Replacing SchemaManager classes , maintaining copied method and special required sub methods beeing private etc).

The original takeaway was, as far as I understood it, that the application should be aware of the type and not relying on db types. Which is understandable, but on the otherhand does not really work out special when type option are not correctly parsed back leading to a comparable SQL DDL string beside the "concrete" used type.

Mid/LongTerm we need for TYPO3 also option to remove the need to continusly adopting "internal" but "required" to clone stuff on minor patchlevel changes.

Register in case a discussion will happen again ;)