Jooq XML Database generation

Question

I am manually defining a Database XML schema to use the Jooq capabilities to generate the corresponding code from the definition. I am using Gradle to generate the code with Jooq:

jooq {
    version = '3.13.5'
    edition = nu.studer.gradle.jooq.JooqEdition.OSS
    configurations {
        crate {  
            generationTool {
                logging = org.jooq.meta.jaxb.Logging.INFO
                generator {
                    database {
                        name = 'org.jooq.meta.xml.XMLDatabase'
                        properties {
                            property {
                                key = 'dialect'
                                value = 'POSTGRES'
                            }
                            property {
                                key = 'xmlFile'
                                value = 'src/main/resources/crate_information_schema.xml'
                            }
                        }
                    }
                    target {
                        packageName = 'it.fox.crate'
                        directory = 'src/generated/crate'
                    }
                    strategy.name = "it.fox.generator.CrateGenerationStrategy"
                }
            }
        }
    }
}

and this is the XML file crate_information_schema.xml I am referencing:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<information_schema xmlns="http://www.jooq.org/xsd/jooq-meta-3.14.0.xsd">
    <schemata>
        <schema>
            <catalog_name></catalog_name>
            <schema_name>doc</schema_name>
            <comment></comment>
        </schema>
    </schemata>
    <tables>
        <table>
            <table_catalog></table_catalog>
            <table_schema>doc</table_schema>
            <table_name>events</table_name>
            <table_type>BASE TABLE</table_type>
            <comment></comment>
        </table>
    </tables>
    <columns>
        <column>
            <table_catalog></table_catalog>
            <table_schema>doc</table_schema>
            <table_name>events</table_name>
            <column_name>data_block['angularPositionArray']</column_name>
            <data_type>real_array</data_type>
            <character_maximum_length>0</character_maximum_length>
            <numeric_precision>19</numeric_precision>
            <numeric_scale>0</numeric_scale>
            <ordinal_position>1</ordinal_position>
            <is_nullable>false</is_nullable>
            <comment>angularPositionArray</comment>
        </column>
        <column>
            <table_catalog></table_catalog>
            <table_schema>doc</table_schema>
            <table_name>events</table_name>
            <column_name>data_block['eventId']</column_name>
            <data_type>bigint(20)</data_type>
            <character_maximum_length>0</character_maximum_length>
            <numeric_precision>19</numeric_precision>
            <numeric_scale>0</numeric_scale>
            <ordinal_position>1</ordinal_position>
            <is_nullable>false</is_nullable>
            <comment>eventId</comment>
        </column>
    </columns>
</information_schema>

The code generated is not good, because it indicate the Data Type used is unknown:

 /**
     * @deprecated Unknown data type. Please define an explicit {@link org.jooq.Binding} to specify how this type should be handled. Deprecation can be turned off using {@literal <deprecationOnUnknownTypes/>} in your code generator configuration.
     */
    @java.lang.Deprecated
    public final TableField<EventsRecord, Object> angularPositionArray = createField(DSL.name("data_block['angularPositionArray']"), org.jooq.impl.DefaultDataType.getDefaultDataType("\"real_array\"").nullable(false), this, "angularPositionArray");

I have a couple of questions:

• which is the correct data type for Real Array?
• where is the list of supported data type with the keys to use in the XML?

N.B. CrateDB is an unsupported DataBase but Jooq could talk to the DB using the Postgres driver, the only problem is to create manually the schema.

Answer 1

which is the correct data type for Real Array?

Use <data_type>REAL ARRAY</data_type> (with a whitespace, and upper case, see comments and issue #12611)

where is the list of supported data type with the keys to use in the XML?

It's the same as for any other code generation data source: All the types in SQLDataType are supported. The convention around array types is currently undocumented, but any of HSQLDB's or PostgreSQL's notations should work. The feature request to formally support array types as user defined types via standard SQL INFORMATION_SCHEMA.ELEMENT_TYPES is here: https://github.com/jOOQ/jOOQ/issues/8090

N.B. CrateDB is an unsupported DataBase but Jooq could talk to the DB using the Postgres driver, the only problem is to create manually the schema.

You can obviously use the XMLDatabase for this. I'm guessing you cannot use the JDBCDatabase, because the INFORMATION_SCHEMA is too different, and the PG_CATALOG schema doesn't exist? However, you could easily implement your own org.jooq.meta.Database, too, if that makes more sense.