>> The only reliable way to automatically derive underlying constraint >> information from view columns seems to be by applying strict conventions. > > I don't think that's going to work for JOOQ.
I wasn't necessarily referring to jOOQ in that paragraph. I was referring to anyone trying to tackle this problem. > I'm siding with the answer given there: Retrieve the view's SQL and parse it. Think about views joining other views. Transitive madness... :-/ Think about views with CTE's. Think about views joining unnested arrays, etc... Parsing these things seems like a very bad idea to me :-) >> If primary keys can be derived reliably from view meta information, >> jooq-codegen could be changed to consider that. > > Sure... but Jooq's mission statement is that it can live with any database > schema. > Imposing naming constraints would be the exact opposite, and detract > massively from Jooq's value. The naming constraints will never be imposed, but always configurable. Already today, you can configure things like - custom data types: http://www.jooq.org/doc/2.6/manual/code-generation/custom-data-types/ - schema mapping: http://www.jooq.org/doc/2.6/manual/code-generation/schema-mapping/ - version and timestamp fields used for optimistic locking: http://www.jooq.org/doc/2.6/manual/code-generation/codegen-advanced/ I don't see a general problem with adding configuration for PK overrides in the code generator. Note that jOOQ's runtime is completely oblivious of the configuration that was applied at code generator time. To jOOQ's runtime, a PK is just a value returned from UpdatableTable.getMainKey(): http://www.jooq.org/javadoc/latest/org/jooq/UpdatableTable.html#getMainKey() Another option to "configure" these things would be to just apply new rules in the GeneratorStrategy... >> We could also discuss some additional code generator configuration to tell >> the generator which columns should be considered as PKs - e.g. by specifying >> a regex. > > You always have the odd table that had a special naming convention, so you'll > need to add a mechanism to specify the PK on a per-view basis. Which means if > something goes wrong, you have to check two places, the per-view config and > the regex. Or you specify a regex: (GENERAL_VIEW_NAME\.ID|ODD_VIEW_NAME\.ODD_ID) I think a regex is powerful enough for most users wanting to override PK definitions in the code-generator. If they have more specific needs, they can patch jooq-meta and implement their own org.jooq.util.Database... > Then people will start to ask for ways to specify different regexes for > different groups of tables [...] A single regex can handle any configuration. Of course, this regex may turn out to be several 100kb long... But if you're used to a nasty database schema, then a nasty regex won't be such a problem, I guess... > That's death by a thousand papercuts, and exactly the kind of slippery slope > that so many DB-related tools go: Adding functionality that kinda-works, but > never can be made to cover all bases. I've had enough of that in Hibernate :-) Well, personally, I feel that there's a difference between adding these features in a code-generator sample implementation or adding it to the core library. I agree that jOOQ should not do such tricks. But the code generator can, and you're free to fork and patch jOOQ's code generator or to write your own with even more interesting features in it. As a matter of fact, jOOQ 3.0's code generator should allow for even better extensibility. > ... well... maybe... here's a really wild idea. > I'm assuming that Oracle is able to tell you whether an arbitrary SELECT has > updatable columns. [...] Good luck with such an approach :-) To me, it's in the same category as parsing the view definition... > And here yet another, entirely different idea: Depending on what you want to > achieve, it might be enough to determine whether a given set of fields is > unique. No matter how you put it, you'll probably not solve the problem of having transitive view dependencies. I'd say, let Oracle do what Oracle does best and let's not try to achieve too many miracles here :-) Cheers Lukas
