What are the ways to modify or hack the jOOQ-generated DDL?

I am using the schema exporting feature of jOOQ extensively. Unfortunately, in some cases, jOOQ generates incorrect or inaccurate DDL for database objects. Are there any standard ways to slightly modify the generated DDL, besides using replace/regex commands on the generated DDL string?

Let's take a look at an example.

1. Create a table (disabling NO_ZERO_DATE mode):

set sql_mode='';
create table test_date_types_table (
  timestamp_column_nullable timestamp null,
  timestamp_column_not_null timestamp not null,
  timestamp_column_default timestamp
);

2. Run this Java code to generate DDL for the table:

        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:6000/sakila", "root", "admin");
        Configuration configuration = new DefaultConfiguration().set(conn).set(SQLDialect.MYSQL.family());
        Meta currentMeta = using(configuration).meta();


        Query[] queries = currentMeta
                .filterSchemas(v -> v.getName().equalsIgnoreCase("sakila"))
                .ddl()
                .queries();

        for (Query query : queries) {
            System.out.println(query);
        }

3. As a result, I received this DDL from jOOQ:

create table `sakila`.`test_date_types_table` (
  `timestamp_column_nullable` timestamp,
  `timestamp_column_not_null` timestamp not null default null,
  `timestamp_column_default` timestamp not null default null
)

4. That DDL doesn't work, because we have not null columns with null value by default:

Invalid default value for 'timestamp_column_not_null'

5. And our nullable column timestamp_column_default is no longer nullable

6. I believe the valid DDL script should look something like our source DDL:

create table `sakila`.`test_date_types_table` (
  `timestamp_column_nullable` timestamp,
  `timestamp_column_not_null` timestamp not null,
  `timestamp_column_default` timestamp
)

How could I fix this and similar cases on my end without changing anything in the jOOQ core?

The non-regular expression way of patching the jOOQ expression tree would be to use the experimental (as of jOOQ 3.18) query object model API, for example from an ExecuteListener. You can either pattern match the entire query until you find the right place to replace, or use the built-in query object model replacement API. This allows for replacing the query parts before rendering it.

A simple example for using the replacement API is this:

System.out.println(ctx
    .parser()
    .parseQuery("create table t (i int not null)")
    .$replace(q -> {
        if (q instanceof QOM.CreateTable ct) {
            UnmodifiableCollection<? extends TableElement> te1 = 
                ct.$tableElements();
            UnmodifiableCollection<? extends TableElement> te2 = 
                (UnmodifiableCollection<? extends TableElement>)
                te1.$replace(x -> x instanceof Field<?> f 
                      && !f.getDataType().nullable()
                    ? DSL.field(
                          f.getQualifiedName(), 
                          f.getDataType().nullable(true)
                      )
                    : x
                );

            if (te1 != te2)
                return ct.$tableElements(te2);
        }
        return q;
    })
);

It will replace all NOT NULL constraints on CREATE TABLE statements, resulting in:

create table T (
  I int
)

An alternative, which is more complicated to do, would be to use a VisitListener, which allows for replacing QueryPart types while they're being rendered to output SQL.