Proposal of a future plan for a replacement for SQLDialect

[fge]

Right now, SQLDialect is an enum. This means that it is nigh impossible to implement one's own SQL dialect over JooQ.

My idea is that a new interface called SqlDialect be introduced (note the difference in casing), with whatever relevant methods there are, and which work in a similar manner to Java 7's OpenOption.

For instance, if we take Files.newOutputStream(), it accepts as an argument a vararg of OpenOptions, and the JDK implements StandardOpenOption as an enum, which is why you can write:

Files.newOuputStream(somePath, StandardOpenOption.CREATE, StandardOpenOption.APPEND)

This means that anyone could then implement its own dialect, and the existing SQLDialect could be superseded by, say, two enums: SqlDialects (open source), CommercialSqlDialects (non open source).

Of course, that would also mean that this interface would have a crapload of method since I surmise that the real SQL generation is done elsewhere.

Comments?

[fge]

Hmwell, now that I think of it, SqlDialects should really be a "utility class" with constants DEFAULT, etc public static final members...

[lukaseder]

Thank you very much for your suggestion. This is essentially the same as #2337, which we've rejected a while ago.

From our experience, the currently 21 supported SQL dialects are much too different from one another in very subtle ways. We do not want to formally expose those differences in any public API, which we would then have to maintain.

[lukaseder]

Closing this as duplicate of #2337

[fge]

Too bad... Well, I do understand the concern about the licensing model and this is a legitimate one.

Meh, that's kind of frustrating but there's nothing to be done about it :p FWIW, my intention was to implement a dialect for InterSystems Caché, which has its fair share of pecularities...

[lukaseder]

Interesting, thanks for pointing this out. I've read about Caché some time ago, but we haven't pursued adding support for it. I've created a feature request for this to keep track of this database: #4723

Are you currently using Caché in production? What are those peculiarities that you have encountered?

[fge]

Yes, at my job I'm using it. I implement a SonarQube plugin for Caché ObjectScript (therefore a language plugin), and I fetch the sources from the database directly.

InterSystems provides jars for both the JDBC layer and a wrapper over this same layer; I use the wrapper. As to its pecularities I guess I can only just point to the documentation:

http://docs.intersystems.com/cache20152/csp/docbook/DocBook.UI.Page.cls?KEY=BGJD

But even the wrapper is a pain to use and I'd much rather use JooQ, although I haven't really tried to use it to generate the code for the tables yet.

If you are interested, I may give it a shot...

[lukaseder]

You can most certainly try to give it a shot, but a thorough implementation of dialect support takes a lot of time. Licensing isn't the only reason for #2337, the other reason is that we've found users to be quite frustrated after they tried implementing their own dialects as it really isn't easy. To prevent that bad experience, we generally recommend not to try it.

For jOOQ 3.8, there is support for DB2 for System z and VoltDB as our highest priority. Do you know anything about Caché's adoption in the industry? The db-engines.com ranking doesn't rank it very high. What are the reasons why you're using this particular database at your job?

[fge]

About its adoption, I can only tell that InterSystems is a big name in the medical industry, in US in particular and also in Europe with Roche Diagnostics; as such it is not really a "general purpose" RDBMS like Oracle (to quote The Big Commercial One(tm)) or PostgreSQL.

The reason I use it is because the source files I analyze in my language plugin are extracted from it ;)

Bah, I've yet to try using the default dialect, who knows, it may be enough in a first place.

[lukaseder]

Thank you for the additional insight. We'll think about it. Perhaps, there really is a niche business opportunity when supporting this database.

Let us know how your attempts to use the default dialect work for you.