Dbal: Symfony 3 - Postgresql JSON type always appear in diff

[PapsOu]

With Symfony 3.3.9, doctrine/common v2.8.1, doctrine/dbal v2.6.2 and doctrine/orm v2.5.11, bin/console doctrine:schema:update --dump-sql always display changes related to JSON types even if applying them manually:

ALTER TABLE sylius_product_attribute_value ALTER json_value TYPE JSON;
ALTER TABLE sylius_product_attribute_value ALTER json_value DROP DEFAULT;
ALTER TABLE sylius_payment ALTER details TYPE JSON;
ALTER TABLE sylius_payment ALTER details DROP DEFAULT;
ALTER TABLE sylius_gateway_config ALTER config TYPE JSON;
ALTER TABLE sylius_gateway_config ALTER config DROP DEFAULT;

Here's gist for more details about versions https://gist.github.com/PapsOu/12805469b21bdeac6fe757174b8743f8

[lcobucci]

Being fixed on https://github.com/doctrine/dbal/pull/2855

[PapsOu]

Do you have any news about this PR ?

[lcobucci]

@PapsOu no, quite busy with paid work that I haven't been able to finish stuff 😢

[bordeux]

also that bug is for JSONB colum :) I

[PapsOu]

Hi @lcobucci, do you have any news about the PR ?

[lcobucci]

@PapsOu I'm waiting for reviews now. The fix is failing for Oracle but I didn't have time to look into that yet.

[bordeux]

@lcobucci what is your branch/repo name ? Where can i make some fixes ?

[PapsOu]

@bordeux Everything is here... https://github.com/doctrine/dbal/pull/2855

[jbdelhommeau]

Do you say if this bug is resolved ? I still have the problem with jsonb.

[PapsOu]

@lcobucci (or any maintainer) : I'm experiencing again this bug.

A bin/console do:sc:up --dump-sql displays those lines :

ALTER TABLE pia_processing_data_type ALTER data TYPE TEXT;
ALTER TABLE pia_processing_data_type ALTER data DROP DEFAULT;

Those lines are always displayed after a bin/console do:sc:up --force && bin/console do:sc:up --dump-sql, even after executing those raw SQL manually.

As a little hack, I have manually executed this SQL query :

COMMENT ON COLUMN pia_processing_data_type.data IS NULL;

So I suppose that the schema diff tool checks the database comment to check if a column holds a specific doctrine type (DCType:array, DCType:json). Is it right ?

Hope this will help to fix this problem.

PS: Could you re-open this issue ? Please find, eventually, the composer info doctrine/* result :

doctrine/annotations                v1.6.0 Docblock Annotations Parser
doctrine/cache                      v1.7.1 Caching library offering an object-oriented API for many cache backends
doctrine/collections                v1.5.0 Collections Abstraction library
doctrine/common                     v2.9.0 Common Library for Doctrine projects
doctrine/dbal                       v2.8.0 Database Abstraction Layer
doctrine/doctrine-bundle            1.9.1  Symfony DoctrineBundle
doctrine/doctrine-cache-bundle      1.3.3  Symfony Bundle for Doctrine Cache
doctrine/doctrine-migrations-bundle v1.3.1 Symfony DoctrineMigrationsBundle
doctrine/event-manager              v1.0.0 Doctrine Event Manager component
doctrine/inflector                  v1.3.0 Common String Manipulations with regard to casing and singular/plural rules.
doctrine/instantiator               1.1.0  A small, lightweight utility to instantiate objects in PHP without invoking their constructors
doctrine/lexer                      v1.0.1 Base library for a lexer that can be used in Top-Down, Recursive Descent Parsers.
doctrine/migrations                 v1.8.1 Database Schema migrations using Doctrine DBAL
doctrine/orm                        v2.6.2 Object-Relational-Mapper for PHP
doctrine/persistence                v1.0.0 Doctrine Persistence abstractions.
doctrine/reflection                 v1.0.0 Doctrine Reflection component

[lcobucci]

@jbdelhommeau @PapsOu we need some test cases that could help us reproduce the issue you're describing... could you try to give it a shot?

[PapsOu]

Please find the complete test case :

git clone https://github.com/PapsOu/DoctrineDbalTestCase.git
cd DoctrineDbalTestCase
composer install
# Adapt your .env
bin/console do:mi:ex 20180810122330
bin/console do:sc:va ~ Should print OK
# edit file src/Entity/ExampleEntity.php at line 25 and replace `@ORM\Column(type="json", nullable=true)` by `@ORM\Column(type="string", nullable=true)`
bin/console do:mi:ex 20180810122406 # or bin/console do:sc:up --force
bin/console do:sc:up --dump-sql # Still displays :

     ALTER TABLE example_entity ALTER an_array TYPE VARCHAR(255);
     ALTER TABLE example_entity ALTER an_array DROP DEFAULT;

[lcobucci]

@PapsOu sorry but an example project is not a test case, we might have other reasons affecting the comparison (like metadata cache). We actually need tests like: https://github.com/doctrine/dbal/pull/2855/files#diff-3341e657ce7db44717715314a5e80dceR1210

[PapsOu]

@lcobucci : Does this test case match the expected one ? I'm not really sure about how to change the column type using the class Doctrine\DBAL\Schema\Table.

[jbdelhommeau]

For me it's an other problem.
I have a new type json_document from this bundle. https://github.com/dunglas/doctrine-json-odm/blob/master/src/Type/JsonDocumentType.php

Diff always show:

ALTER TABLE mytable ALTER col1 TYPE JSONB;
ALTER TABLE mytable ALTER col1 DROP DEFAULT;

It's normal because Doctrine\DBAL\Types\JsonType and Dunglas\DoctrineJsonOdm\Type\JsonDocumentType are two differente type. But one extend other.

A tip to solve it ?