Generating same catalog and schema classes for different database types

[Marcus Gattinger]

Hi,

out software product targets different DBMS (e. g. SQL Server and MySQL), hence I'm facing the problem that some DBMS support schemas (like SQL Server) and others (MySQL) don't.
So my goal is to let jOOQ generate identical classes for the catalog and the schema implementation despite of the concrete database name (and schema if available) or DBMS. I do not want to create different source code branches for each DBMS.

I have partly achieved this by creating my own naming strategy (i. e. a class that derives from the DefaultGeneratorClass) and use it during the generation process:

<generator>
    <strategy>
        <name>MyGeneratorStrategy</name>
    </strategy>
    ...
<generator>

The names of the generated catalog and schema classes are now identical for SQL Server and MySQL (e. g. DefaultCatalog and DefaultSchema respectively).
But the generated catalog class contains the output catalog name (defined by the <outputCatalog> element) in its private constructor and the generated schema class contains the output schema name (defined by the <outputSchema> element) in its private constructor, respectively.

So how can I create a private constructor that do not use a string literal but rather a map of properties (a static map that is filled at application start time)?

Kind regards,
Marcus

[Marcus Gattinger]

The solution I come up with is to omit the schema and catalog notion during SQL generation:
DSL.using(<data source>, <dialect>, new Settings().withRenderSchema(false).withRenderCatalog(false)

[Lukas Eder]

That's a very interesting problem. For historic reasons (jOOQ didn't always support catalogs), jOOQ treats MySQL's databases (which are really catalogs) as schemas - in practice they actually work in that way in everyday SQL usage. But of course, this is conceptually wrong and should be fixed in a future version, where jOOQ would generate catalogs for MySQL rather than schemas (guarded with a flag for backwards compatibility). I've created a feature request for this:

https://github.com/jOOQ/jOOQ/issues/5963

Your approach certainly works. Do note that you can also turn off schema generation at code generation time. That will slightly speed up things at runtime, as these checks won't need to be performed every time a table / column is generated. On this page, look for "outputCatalogToDefault" and "outputSchemaToDefault"

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-catalog-and-schema-mapping/

With the generator strategy, you can only re-define how jOOQ's code generator outputs Java elements, such as class names, member names, method names, etc. You cannot re-define actual object names. That's what the catalog / schema mapping feature is for, but it is, unfortunately, not sophisticated enough for what you intend to do.

Let me know if turning off catalog/schema rendering or generating is sufficient for you, or if you need more help

Best Regards,

Lukas

2017-03-10 16:14 GMT+01:00 Marcus Gattinger <gatt...@gmx.de>:

The solution I come up with is to omit the schema and catalog notion during SQL generation:
DSL.using(<data source>, <dialect>, new Settings().withRenderSchema(false).withRenderCatalog(false)

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marcus Gattinger]

Hi Lukas,

thank you for answering to my post. Actually I already have tried "outputCatalogToDefault" and "outputSchemaToDefault". But the result is not want I expected.

For MySQL I use this configuration:

<database>
 <name>org.jooq.util.mysql.MySQLDatabase</name>
 <inputCatalog>my_database</inputCatalog>
 <inputSchema>my_database</inputSchema>
 <outputCatalogToDefault>true</outputCatalogToDefault>
 <outputSchemaToDefault>true</outputSchemaToDefault>
 <includes>.*</includes>
 <excludes>schema_version_.*</excludes>
</database>

I have played around with "inputCatalog", "outputCatalog", "outputCatalogToDefault" (omit them, use non-existing names, using true/false for the latter). The behaviour of the elements is quite weird to me:

1. Omitting the element "inputCatalog" but using the "outputCatalog" element always creates a catalog class named "_" using the value of the "outputCatalog" element for the catalog name in the constructor.
2. Omitting the elements "inputCatalog" and "outputCatalog" always creates a catalog class named "DefaultCatalog" using an empty string for the catalog name in the constructor. In doesn't matter if you use the value "true" or "false" for "outputCatalogToDefault" element or omit this element completely.
3. Using the "inputCatalog" element behaves exactly like case no 2, i. e. the elements "outputCatalog" and "outputCatalogToDefault" are ignored.

The element "inputSchema" is mandatory, however (and the value must match the database name). Otherwise no classes will be generated.

1. Omitting the elements "outputSchema" and "outputSchemaToDefault" creates a schema class named like the value of "inputSchema" element using the value of the "inputSchema" element for the schema name in the constructor.
2. Using the element "outputSchema" but omitting the element "outputSchemaToDefault" has the same effect than case no 1.
3. Using the elements "outputSchema" and "outputSchemaToDefault" has also the same effect than case no 1 (no matter whether the value "true" or "false" is used).

The desired result will be generated only, if I use the elements "inputSchema" and "outputSchemaToDefault". Now the two classes "DefaultCatalog" and "DefaultSchema" are generated using an empty string for the catalog / schema name in the constructor.

To make the code generation work as expected for SQL Server both elements "inputCatalog" and "inputSchema" are mandatory. The value for "inputCatalog" must match the database name, the value for "inputSchema" must match the schema name (the genernal default is "dbo").

So in the end I was not able to find exaclty one combination of elements "inputCatalog", "inputSchema", "outputCatalogToDefault", "outputSchemaToDefault" that work for both DBMS types. Hence I was forced to use the solution of my previous post.

As site note I also configured the following "generator" elements:

<generate>
 <deprecated>false</deprecated>
 <fluentSetters>true</fluentSetters>
 <globalCatalogReferences>false</globalCatalogReferences>
 <globalRoutineReferences>true</globalRoutineReferences>
 <globalSchemaReferences>false</globalSchemaReferences>
 <globalTableReferences>true</globalTableReferences>
 <pojos>true</pojos>
 <records>true</records>
 <relations>true</relations>
</generate>

Cheers,
Marcus

[Lukas Eder]

Hi Marcus,

Sorry for the delay. And sorry for the confusion. 

Indeed, as I mentioned in the previous email, for historic reasons, jOOQ treats the MySQL "catalog" (database) like a schema, because from a practical perspective, those databases look like schemata. So, without actual catalog support in MySQL, it probably makes no sense to specify any catalog at all (on MySQL). But as you've noticed, the catalog will be required on SQL Server.

But the main conclusion is this one:

2017-03-11 13:58 GMT+01:00 Marcus Gattinger <gatt...@gmx.de>:

So in the end I was not able to find exaclty one combination of elements "inputCatalog", "inputSchema", "outputCatalogToDefault", "outputSchemaToDefault" that work for both DBMS types. Hence I was forced to use the solution of my previous post.

Yes, that's not easily possible right now. Some databases support:

- Catalogs and schemas (e.g. SQL Server)

- Catalogs only (e.g. MySQL)

- Schemas only (e.g. Oracle)

- None (e.g. SQLite)

So, I suspect that this kind of confusion might be inevitable at this stage.

Note that an alternative approach is to use the XMLGenerator or the JPAGenerator to generate jOOQ code from database independent resources (XML files or JPA annotations). Perhaps that's a better solution? The drawback here is that not all features are available yet (e.g. stored procedures).

Lukas