RFC - Aligning abstract types with cakephp

[markstory]

Currently migrations supports the phinx abstract type set in migration files. The driver adapters in migrations (previously phinx) can translate between the abstract types that phinx uses and the concrete dialect specific types.

I would like to align migrations with the cakephp/database conventions. Currently there are several types that exist in migrations that do not exist in cakephp e.g mediumblob, set, and enum. And other types that exist in cakephp that migrations does not directly support eg. timestampfractional.

These differences could result in some minor breaking changes as the semantics of existing migrations would change.

Types missing in CakePHP

MySQL

• enum
• set
• year
• json

The following types can be shimmed with length:

• tinyblob
• mediumblob
• longblob
• mediuminteger

Postgres

• jsonb
• cidr
• inet
• macaddr
• interval

Sqlserver

• filestream

Handling length based aliases

The blob/integer aliases seem simple enough to shim with a compatibility layer when running migrations. However, during schema reflection these types would be reflected as binary with lengths (as they do in CakePHP today).

Proposed deprecations/removals

• Deprecate support for enum, and set types in migrations. Migrations using these types will emit deprecations in migrations 4.7. We have historically avoided supporting ENUM in cakephp because of the footgun they create for applications down the road. Instead we recommend folks use varchar columns with mapped types.
• Deprecate support for inet, macaddr, interval types in postgres. These types are not portable between database dialects and don't currently have support in cakephp.
• Deprecate support for filestream type in Sqlserver.
• Deprecate the AdapterInterface::PHINX_TYPE_* constants. Instead we can use the cakephp ones.
• Align with cakephp on datetime and timestamp types. This will result in different types being used in migration and dump files.

[dereuromark]

Instead we recommend folks use varchar columns with mapped types.

PHP (backed) enums basically, which can be string (varcharX) or int (tinyint4) based probably.

[markstory]

PHP (backed) enums basically, which can be string (varcharX) or int (tinyint4) based probably.

Yes. What I'm proposing is that Migrations would be unable to create/define columns of with ENUM types in the future. I don't know if there are many applications that are relying on MySQL enum columns in active development, and how much this will be a significant breaking change.

[dereuromark]

The main issue I see is indeed with some legacy apps, e.g. also ones that want to switch to Cake(ORM).
If there was rudimentary support for it, even for only read only, then this would help them migrate away from it probably.

But in the end, if this is too much overhead, we can probably ignore this edge case.

[umer936]

I've just hit the enum no longer working twice 😅. I don't need it and am happy to migrate it to a different way but I have columns (e.g. queue_type) with a limited set of options {'celery', 'rabbitmq', 'dereuromark-queue'} that can be edited by CakePHP or the Python that actually runs it. If there is a better way to do it I'm cool with that but enum seemed the way to do what I wanted when I wrote these.

[dereuromark]

make them varchar and use PHP enums, best way forward probably.

[ADmad]

@umer936 https://book.cakephp.org/5/en/orm/database-basics.html#enum-type

[dereuromark]

Anything left after #910 ?

[markstory]

Nope. I think we can close this one.