Jooq: The Diff tool does not support column type changes

[JanecekPetr]

Expected behavior and actual behavior:

The new Diff tool does not detect type changes, e.g. going from `varchar(100) to text in Postgres, or from integer to float8. The Diff tool currently completely ignores those, it should error out and/or correctly produce the diff.

Steps to reproduce the problem:

1. go to https://www.jooq.org/diff/
2. version Before:

CREATE TABLE aaa (
    abc integer,
    def varchar(100)
);

1. version After:

CREATE TABLE aaa (
    abc float8 not null,
    def varchar(100) not null
);

The output is

alter table aaa
  alter abc set not null;
alter table aaa
  alter def set not null;

but I expected something similar to:

alter table aaa
  alter column abc set not null,
  alter column abc type float8;
alter table aaa
  alter column def set not null,
  alter column def type text;

or an error message. Still much better that silently ignoring any differences.

Versions:

• jOOQ: 3.13-SNAPSHOT as of 2019/12/05

[lukaseder]

Thanks a lot for your report. Yes, we know :-) Check out issue https://github.com/jOOQ/jOOQ/issues/9425 with a roadmap of features to support in this area.

I'll look into this right away.

[JanecekPetr]

Check out issue #9425

I looked there, did not find anything related to changing types, so I created a new ticket. I also tested adding values to an enum and renaming columns, both are already documented in #9425, so I left them out.

Thanks, looks like a step in an exciting direction!

[lukaseder]

Notice, there's still a ton of improvements that we have to implement. In this particular example, we'll soon emit those set data type commands. However, that doesn't mean any given database knows how to run this for each data type combination. We'll have to emulate quite a few such statements to make sure they work, e.g. by adding a new column, updating all data into it, dropping the old one, and renaming the new one to the old name.

[lukaseder]

I looked there, did not find anything related to changing types, so I created a new ticket.

I had noticed this as well, and added the bullet now. It was meant implicitly as the parent bullet, but this wasn't clear.

I also tested adding values to an enum and renaming columns, both are already documented in #9425, so I left them out.

Thanks a lot! Yes, that one would be great. We have a customer that would love that feature. We'll first need to support enum types in org.jooq.Meta though, and for that, we might need a new enum type runtime meta model first.

Some things just open cans of worms :-) But we'll get there!

[JanecekPetr]

"It was meant implicitly as the parent bullet, but this wasn't clear."

Ohhh, there's a "Column data type changes". Somehow I missed that :). Thanks, I'm closing this now as the requirements is covered elsewhere.

[lukaseder]

Fixed now. We'll soon auto-publish the latest snapshots to those websites, so the column type alteration should soon be available.

Thanks again. If you find anything else, please let us know! :)