Recommended setting for RenderNameCase and RenderQuotedNames

[Marcus Fihlon]

Hi

I want to configure my application to work as OS (filesystem) and DB independent as possible. It should run on Linux, Mac, Windows, support MariaDB, PostgreSQL, H2, etc. Some filesystems are case sensitive, others not. Same with the DBs.

Which combination of RenderNameCase and RenderQuotedNames should I use and how should I format my SQL files for DB initialization? With or without quoted table names, lowercase or uppercase?

I tried several combinations. Some work with MariaDB on Linux and Windows fine, but then H2 does not work anymore. Or vice versa. To handle this, I started getting OS- and DB-information programmatically from the system environment and use if-cascades to determine which setting to use. Ugly. Or I implement a configuration option and "outsurce" the trial and error to my users. Ugly, too.

Do you have some advice?

[Lukas Eder]

The only real problem here is MySQL, whose case sensitivity depends:

- On the OS

- On the identifier type

- On a system or session flag

- On the identifier style (quoted or not)

With most others, case sensitivity is mostly tied to whether an identifier is quoted, or it's never sensitive at all. But if your're in control of your DDL, you can make sure (with tests and diligence) that even MySQL behaves.

So, you mostly have 2 options for your DDL:

- Make your identifiers mostly case sensitive. That will simplify using the same style across RDBMS. Though it might force some users to quote identifiers when running ad-hoc queries against your schema. In short, you'll either make the PostgreSQL folks unhappy (default lower case), or almost everyone else (default upper case)

- Make your identifiers mostly case insensitive. That will simplify ad-hoc queries by users. Within jOOQ, you can either turn off quoting, or turn it on and override upper/lower case, in case you work with reserved words as identifiers, or have special characters in them.

A note about SQL Server, it is customary to use [QuotedPascalCase] identifiers, though I think if you want to support all RDBMS, then I'd ignore that and use the same as everywhere else.

In short:

- Be very diligent with your DDL and add tons of tests (you can use org.jooq.Meta to check if all reported identifiers have the correct upper or lower case)

- Specify quoting and case depending on your configured SQLDialect in jOOQ

That should do the trick.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/78d20cc0-ba08-4f00-bbaa-88ead57c8741n%40googlegroups.com.