DDLDatabase issue with array type

[marceloverdijk]

Expected behavior

Having a column definition in a DDLDatabase script like previous_names VARCHAR(255)[] DEFAULT NULL, I would expect a VARCHAR array to be generated.

Actual behavior

It generates:

public final TableField<HotelRecord, Object[]> PREVIOUS_NAMES = 
    createField(DSL.name("previous_names"), SQLDataType.OTHER.getArrayDataType(), this, "");

Probably this is because H2 does not support it?
Although that seem to be added: h2database/h2database#1390, but I'm not sure that is released yet, and probably won't be soon.

Would there be any workarounds for this?

Steps to reproduce the problem

• If the problem relates to code generation, please post your code generation configuration

I'm using org.jooq.meta.extensions.ddl.DDLDatabase.

Versions

• jOOQ:
• Java:
• Database (include vendor):
• OS:
• JDBC Driver (include name if inofficial driver):

[lukaseder]

Thanks for your suggestion.

Yes, this limitation is due to H2 historically not knowing typed arrays, so you're going to lose the array types when using the DDLDatabase. A lot of limitations of the DDLDatabase are derived from our using H2 behind the scenes to simulate your actual database migration. A better approach will be:

• To interpret the migration ourselves, rather than simulating it on H2: Add configuration in DDLDatabase, LiquibaseDatabase, JPADatabase matching Settings.interpreterDialect #7034
• To integrate more tightly with test containers in order to run your migration on the actual traget database product: Allow for DDLDatabase to connect to a testcontainers managed database #6551

Even so, numerous improvements in jOOQ would profit from a new H2 release, but we'll have to wait. It seems this cannot be accelerated: h2database/h2database#2491

So, I'm closing this as a duplicate of the above three issues.

[lukaseder]

(Note that #6551 contains some comments related to workaround to use jOOQ's code generator with testcontainers already today!)

[marceloverdijk]

Thx for mentioning that issue.

I was looking at a different workaround to use forced types (but it is not working yet).

forcedTypes {
	forcedType {
		name = "VARCHAR(255)[]"
		includeExpression = "hotel\\.previous_names"
	}
}

The includeExpression is correct as when I change the name to e.g. "VARCHAR" the type is forced.
But with "VARCHAR(255)[]" it is not being forced unfortunately.

For the forced type name the jOOQ docs state:

Specify any data type that is supported in your database, or if unsupported, a type from org.jooq.impl.SQLDataType.

I guess name "VARCHAR(255)[]" is not working as it is not supported in the H2 database.
Would it be possible to specify a org.jooq.impl.SQLDataType array type here in a way?

Another more brute "force type" way would be just doing an automated search / replace in the generated Java file. ;-)

[marceloverdijk]

I've moved to testcontainers and it works well!.

[lukaseder]

Thanks for the feedback. If you don't mind then, I won't investigate how to apply a forced type for a string array in H2? Because that, too, will resolve itself eventually, when the next version of H2 is released...