Jooq: DDLDatabase should sort SQL files

[Diagoras]

DDLDatabase on master looks like it can handle being pointed to a directory containing SQL files, which is great for all of us Flyway users who have been eager to use JOOQ's codegen directly with our Flyway migration scripts.

However, it looks like the directory has its files parsed and applied to the in-memory H2 database in the order returned by File.listFiles(). Per the Javadoc, that method offers no guarantees about the order the files are returned in - meaning that Flyway scripts might get retrieved and applied entirely out of order.

I believe resolving this should be as simple as calling Arrays.sort(file.listFiles()), which will give us the files in Flyway-compatible lexicographic order.

Let me know if you need any more details, or see any issue with this request.

[lukaseder]

Thanks for your suggestion. You're absolutely right. This is a simple oversight. However, I think that lexicographic order might be insufficient in this case. I'd expect files to be ordered semantically, e.g.

version-2
version-10

Rather than

version-10
version-2

This is what Windows does by default. Does Flyway do that?

[lukaseder]

Blog post about the ordering approach here, in case anyone is interested: https://blog.jooq.org/2018/02/23/how-to-order-file-names-semantically-in-java/

[Diagoras]

Thanks, you're completely right about semantic ordering. That blog post was an interesting read, as well.

Flyway has a specific naming pattern it expects, but what you've implemented should work well enough for the most common usecase involving only "V" prefixed version files. Handling undo and repeatable migrations (and all the other complexities of Flyway's ordering of updates) should probably be handled in a different ticket, and may even end up requiring its own FlywayDatabase extends DDLDatabase. Should I open an issue for that now? Or do you think grappling with the specifics of Flyway's migrations is out of scope for JOOQ?

[lukaseder]

Interesting, thanks for the pointer. I was going to ask @axelfontaine about the ordering but then I forgot. Surely, I can copy it, too.

hould probably be handled in a different ticket, and may even end up requiring its own FlywayDatabase extends DDLDatabase

Please, no, don't. No inheritance where composition would suffice. I will totally agree with a configuration flag that specifies the ordering behaviour:

• Natural
• Semantic
• Flyway

I'll reopen this issue to implement things this way.

[Diagoras]

Interesting, thanks for the pointer.

Yeah, no worries. Thanks for the awesome library!

Please, no, don't. No inheritance where composition would suffice.

Fair enough. I tend to work in Scala, so I was trying to Java-ize the proposed solution. It's all about the AbstractSingletonProxyBeanFactory's, right? :p

[lukaseder]

Fair enough. I tend to work in Scala, so I was trying to Java-ize the proposed solution. It's all about the AbstractSingletonProxyBeanFactory's, right?

I'm not sure what you're trying to imply but extending is usually a bad idea, even in Scala.

[galvinograd]

+1
This feature is necessary for me to make my app work with Flyway without building a complicated build system with docker-compose. I don't mind implementing that, what is the process? Would you mind me submitting a pull request?

[lukaseder]

@galvinograd : Thanks for your offer. Here's the process: https://github.com/jOOQ/jOOQ/blob/master/CONTRIBUTING.md

[lukaseder]

Hmm, Flyway doesn't seem to implement a very sophisticated sort order:

    /**
     * @return The complete location descriptor.
     */
    public String getDescriptor() {
        return prefix + path;
    }

    @SuppressWarnings("NullableProblems")
    public int compareTo(Location o) {
        return getDescriptor().compareTo(o.getDescriptor());
    }

It's essentially just alphanumeric. So, we won't explicitly support Flyway, but just these three sort modes:

• Semantic sort (what I've just implemented. This is the default)
• Alphanumeric sort
• No sort (order as produced by the file system)