>> One heads-up and data point on views: You're not going to get PK info >> from a view, at least not in Oracle up to and including 11g. > > Yes, that's really a pity. Oracle has all the information, internally, > and makes good use of it in the CBO. It would be really great if Oracle > could disclose some of this referential information in the dictionary > views.
I suspect that Oracle treats views as SQL "macros". So it doesn't have nor need much metadata about a view, besides the definition itself. At least that's how I would implement views if I were to build a database engine and had to work with time-to-market constraints :-) > On the other hand, depending on how you join tables within a view, > the semantics of primary keys / foreign keys change, and it would > probably be hard for the Oracle database to model this generically. Ah, yes, I didn't consider that. I guess that's why there is indeed no good alternative to simply do inline expansion: view don't compose wrt. key uniqueness, you need to expand them into the SQLs that use them and then analyze uniqueness properties anyway. 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 guess one would also need to find out which columns of a view are directly passed through from which column of which base table, and I do not remember anything in that direction. So it could still be a dead end :-( >> Whether that's a minor annoyance or a dealbreaker pretty much depends >> on your workstyle and the overall situation. >> >> Here, we're currently creating and modifying tables and views at the >> drop of a hat, and having to manually provide PK info for views >> turned out to be a hassle. And it was important to do so because the >> Java code uses PK fields to determine whether two Pojos refer to the >> same db record. > > I agree it can be a hassle. You could implement a strict naming > convention for primary key columns and automatically generate "view > primary keys" where applicable. Won't happen for us. Too many design constraints present already (as always...) If I read Jooq's mission statement correctly, such a requirement would be outside Jooq's sope, too.
