Column precision is not specified 2

[cavemanlu]

Bug Report

Q	A
Version	4.*

Summary

Given:

• An old projects without framework.
• Historically, more than 100 columns (several TB of data) in different tables in Postgres have type NUMBER without scale/precision (which is absolutely correct for this RDBMS)
• There is doctrine-migrations library
• There is NO doctrine-orm

Current behaviour

It is impossible to update doctrine-migrations and doctrine-dbal (3.8 -> 4) because dbal broke support for type NUMBER without scale/precision.

The 'correct' way to fix it - by updating all the columns with scale/precision cannot be applied easily (it will take days on the amount of data).

How to reproduce

Create a simple project with Postgres with dependency on doctrine-migrations.
Create a table with a column with type NUMBER without scale/precision.
Create any migration on the schema.
Try to apply the migration.
Get 'Column' precision is not specified' exception.

Expected behaviour

No errors.

A workaround with the possibility to configure default scale/precision project-wise/per-column would work.

[berkut1]

Have you tried creating your own custom type to override the method

dbal/src/Platforms/AbstractPlatform.php

Line 1388 in 9042447

public function getDecimalTypeDeclarationSQL(array $column): string

and bypass your issue?

[derrabus]

We've had a similar discussion in #6455: Postgres also supports VARCHAR without a limit which DBAL 4 "broke" as well. The problem is that those unconstrained types as Postgres calls them are not portable at all which makes them a bit out of scope for a database abstraction layer. A custom type as @berkut1 suggested is probably your best option for a Postgres-only type.

[morozov]

Postgres also supports VARCHAR without a limit which DBAL 4 "broke" as well.

I don't think this is accurate. If you use DBAL only with Postgres and/or SQLite, you shouldn't have to specify the length. The validation happens during rendering the type DDL by the platform.

For example (also note ?int in the signature):

dbal/src/Platforms/PostgreSQLPlatform.php

Lines 637 to 646 in 44635a2

	protected function getVarcharTypeDeclarationSQLSnippet(?int $length): string
	{
	$sql = 'VARCHAR';
	if ($length !== null) {
	$sql .= sprintf('(%d)', $length);
	}
	return $sql;
	}

[berkut1]

@morozov
It's "broken" in quotes :)

I tried to describe the problem here. In DBAL3, all platforms have this similar code:

dbal/src/Platforms/PostgreSQLPlatform.php

Lines 1129 to 1133 in b35648d

	protected function getVarcharTypeDeclarationSQLSnippet($length, $fixed)
	{
	return $fixed ? ($length > 0 ? 'CHAR(' . $length . ')' : 'CHAR(255)')
	: ($length > 0 ? 'VARCHAR(' . $length . ')' : 'VARCHAR(255)');
	}

As you can see, if no value is specified, it returns VARCHAR(255). The problem is that ORM forcibly does this too here:

https://github.com/doctrine/orm/blob/9d4f54b9a476f13479c3845350b12c466873fc42/src/Tools/SchemaTool.php#L463-L465

So, ORM expects to always get VARCHAR(255) by default and uses tricks around this to fool DBAL without creating custom types. I tried to describe the issue using the INET example. #6466

This is also how in ORM with DBAL4 it "breaks" ENUM, which was based on fooling/tricking DBAL doctrine/migrations#1441 (comment) (no one has checked my theory yet, but I’m sure the problem is related) . The problem is not in DBAL4 (partially, because the initial code contains examples of tricking), but in ORM and its hardcoded value of 255 for all strings.

But of course, this all relates only to VARCHAR and strings.

[cavemanlu]

Have you tried creating your own custom type to override the method

dbal/src/Platforms/AbstractPlatform.php

Line 1388 in 9042447

public function getDecimalTypeDeclarationSQL(array $column): string

and bypass your issue?

Thanks, this workaround worked for me.
I replaced the default Decimal type with the new one that checks default values for precision/scale.