> On the other hand, once a view is expanded and transformed into > the surrounding query, depending on the predicates / projections > applied, there may not be much left of the original view.
Exactly. >> I dimly remember that Oracle has a way to tell which columns of a view >> are updatable. Maybe this information can somehow be creatively abused >> to infer which view columns consistute a PK. > > I think, "updatable" means that if you reduce a view to its "updatable" > columns, you can INSERT / UPDATE / DELETE into that part of the view. > Oracle mentions the term "key-preserved table" in this context: Yes, that's their terminology for the condition that makes a column updatable. Not so sure about insert and delete, that's hairy stuff. > Typically, when adding many-to-one relationships, column "updatability" > disappears from the view. Updatability is a per-column property in Oracle. You can still update the columns that came from the to-many side. (Oracle quickly loses track of uniqueness, even in circumstances where a human would say it's obvious that a column should be updatable.) > Anyway, among the updatable columns, you still don't know which ones are part > of a/the primary key. Yeah, I feared it would be that way. > 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'm siding with the answer given there: Retrieve the view's SQL and parse it. >> If I read Jooq's mission statement correctly, such a requirement would >> be outside Jooq's sope, too. > > 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. > 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. Then people will start to ask for ways to specify different regexes for different groups of tables (because they were built by different teams, or in different epochs, or in different companies before the companies merged, or by different contractors). Which means you'll have to look into even more places if the PKs aren't correctly determined. 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 :-) That said, I don't have anything to offer how to tackle the problem, and it would indeed be nice to have something that works. ... 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. (Maybe defining a temporary view is needed for that.) Start with SELECT field1, field2, ..., fieldN FROM view and remove fields from that SELECT until it stops being updatable. The remaining fields form the view's PK. Lots of ramifications here: - This could probably be sped up via divide-and-conquer strategies. - Multi-field PKs. Divide-and-conquer is still possible but more complicated. - You'll actually get any unique key. Might not be the PK. - If the database is really smart about uniqueness, you might end up with a set of fields that's just inferrable as unique from other constraints. I don't know whether that's a problem or not. 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. Just tossing around ideas :-)
