code-gen :: skip views?

[ern...@returnly.com]

Hi,

I'm looking for an advice on skipping views during code gen. I believe ATM JOOQ cannot distinguish between regular tables and views.

This is creating problems in my case, where I have two schemas, let's say A and B. B has views for all tables in A as well as its own tables unique to B. (Don't ask me why -- legacy setup).

So when I generate code from the two schemas, for each table T in A I get table/record/pojo objects in duplicate. While they technically don't clash due to different package names, referring to the right object in the code becomes error-prone.

Why can't JOOQ distinguish between tables and views? This is supported by JDBC's DatabaseMetaData, isn't it?

Any alternative piece of advice?

Thanks!

--Ernest

[Samir Faci]

Though this isn't a perfect solution you could white list/ black list the set of tables that are generated.

https://www.jooq.org/doc/3.9/manual/code-generation/codegen-advanced/codegen-config-includes-excludes/

We have a ton of tables generated in legacy and would rather not generate code for in our DB, so instead we explicitly list every table we want to auto-gen.

--

Samir Faci

--
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.

--

Thank you
Samir Faci

https://keybase.io/csgeek

[ern...@returnly.com]

Thanks, Samir, I'm aware of that; it is indeed far from perfect as that list will get stale quickly. I guess I'll live with extra generated objects.

Core committers -- any word re: supporting views exclusion akin to routines, UDTs, etc.?

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hi Ernest,

Thanks for your message and for your suggestion.

Currently, jOOQ doesn't really distinguish between tables, views, materialised views, temporary tables, table-valued functions, and many other object types that behave like tables. There's a pending feature request for adding such distinctions in the runtime API:

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

... but it's not a priority, because it will not be easy to correctly model this distinction across the entirety of the jOOQ API, including: generated tables (those are easy), plain SQL tables, named tables, meta tables (from information schema: easy, from JDBC DatabaseMetaData: tricky), parsed tables, and much more. 

Nevertheless, it would definitely be useful to be able to distinguish between the types, both in the runtime API as well as in the code generator.

Note: Even if JDBC's DatabaseMetaData claims that it can handle the distinction between tables and views, it's generally not a good idea to trust this claim, or any claim made by DatabaseMetaData, as many JDBC drivers implement the API poorly.

However, there are options for workaround!

If you're using only one database (e.g. PostgreSQL), you can use the programmatic code generation configuration:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-programmatic

That way, you can easily produce a dynamically generated "excludes" regular expression from a query against the dictionary views. E.g. in PostgreSQL:

SELECT string_agg(table_schema || '.' || table_name, '|')

FROM information_schema.tables

WHERE table_type = 'VIEW';

In fact, I wonder if we should add a new feature to the code generator. Everywhere where users can put regular expressions, they can also put a SQL string that produces that regular expression, dynamically, through the XML configuration, rather than programmatically. I've created a feature request for this:

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

I hope this helps,
Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[ern...@returnly.com]

Thanks, Lucas.

Supplying regex via SQL - great idea!

For now, I'm using gradle to configure codegen, but I think I can put together a custom gradle task to fetch the views relatively easily.

Thanks again for the valuable feedback.

[Lukas Eder]

True, Gradle *is* programmatic configuration, so that would make the task a bit easier.

Cheers,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.