>> I'm siding with the answer given there: Retrieve the view's SQL >> and parse it. > > Think about views joining other views. Transitive madness... :-/
That's not so bad. Just analyze the used view for its PK, then treat that view as if it were a table. I think subqueries are more, erm, "interesting" here. I guess one wold need an SQL parser&analyser for that to be viable, and that's a large (and worthy) project in itself. I guess it's also outside the scope of Jooq itself :-) Not sure about CTEs (what's that?) and arrays (never used them, too DB-specific to be comfortable with that). >>> 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/ Hm, yes, I see. That was the point where I started to become wary of Jooq. Not really Jooq's fault, it's just that the code generation in Hibernate Tools were such a time sink and nightmare. > 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() Yeah, that sounds sensible. >>> 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) Well... regexes as they are today (i.e. Perl5-based) are just too write-only to suit my taste. (OT: I sincerely hope that somebody will step up and write a P6CRE library, the Perlistas have designed something really awesome there.) That said, they're still the fastest way to get the job done, so they certainly have their place. >> Then people will start to ask for ways to specify different regexes >> for different groups of tables [...] > > A single regex can handle any configuration. Nah, that's not viable. Regexes don't compose very well (they can't because the problem doesn't - you always get conflicting rules and need to resolve them). > Of course, this regex may turn out to be several 100kb long... Yep, that's one possible symptom. > But if you're used to a nasty database schema, then a nasty regex won't > be such a problem, I guess... I guess differently: if the database schema is already nasty, the last thing you need is yet another nastiness creeping in from elsewhere. It's probably smarter to divide the rule base up: Tables following naming schemes A, B, or C should apply PK identification scheme X, those following naming scheme D should apply PK identification scheme Y, etc. Including schemes that simply use a hardcoded specification. Again, just tossing around ideas. >> 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. Agreed. Provided it's viable to whip up a code generator strategy that simply configures everything statically from a text file or from annotations or anything else hand-built. Maintaining hand-built stuff is a pain, but trying to control an overly smart code generator can be worse. Been there, done that, got the T-shirt, on both roads; neither sight was pretty but the generator route was a mess. >> ... 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... Parsing SQL is algorithmically difficult. Observing database behaviour is find-all-the-incompatibilities difficult. Not the same category. Unless you mean the "too much work to be worth it for Jooq" category, of course :-)
