Re: Views and aliasing in jOOQ

[Lukas Eder]

Hello Henning,

Unfortunately, this isn't possible out of the box, right now. It
sounds like a very nice idea, though. I have registered feature
request #1969 for this.
https://github.com/jOOQ/jOOQ/issues/1969

Of course, you can always write actual database views and let the code
generator generate meta-data for those.

Also, you could manually implement some org.jooq.Table objects, but
that might be quite a bit of work. There are CustomTable types, which
unfortunately aren't as powerful as you would like to see them:
http://www.jooq.org/doc/2.6/manual/sql-building/queryparts/custom-queryparts/

This is due to the methods toSQL() and bind() being final in
CustomTable's class hierarchy, which is probably a design flaw. It
should be fixed in jOOQ 3.0 as of #1970:
https://github.com/jOOQ/jOOQ/issues/1970

Cheers
Lukas

2012/11/16 Henning Blohm <hennin...@gmail.com>:
> Hi,
>
> I am trying to achieve the construction of a (logical) view using jOOQ. As a
> simple case, assume there is a table students with name and age and I want
> to turn this into
>
> minors = select s.name as minor_name from students s where s.age<21;
>
> Some other piece of code can then filter those further as in
>
> select m.minor_name from minors where m.minor_name='smith';
>
> In Java terms, "minors" would be a typed "queryable" with a custom POJO type
> (with field "minorName").
>
> Real world examples would be more complex of course
>
> I spent some time looking for a suitable construct in jOOQ but was not
> succesful.
>
> Any helpful directions would be great!
>
> Thanks,
> Henning

[Henning Blohm]

Hi Lukas,

thanks for that extremely quick and detailed reply!

Henning

[Schönfisch, Jörg]

Hi,

 

what we‘re doing in our project sounds similar and might be helpful for Henning’s request. SelectQuery provides asTable() so it can be used anywhere or normal table can be used. To get the correct field out of the view we use this method (with some sanity checks whether the field exists or not):

    private Field<?> getSelectField(SelectQuery query, String name) {

        List<Field<?>> fields = query.getFields();

        int index = fields.indexOf(field);

        return query.getSelect().get(index);

    }

So something like this can be done:

Factory create = …

SelectQuery minorView = …

SelectQuery queryName = create.selectQuery(minorView.asTable()).where(getSelectField(minorView, “name”).eq(“smith”));

SelectQuery queryAge = create.selectQuery(minorView.asTable()).where(getSelectField(minorView, “age”).greaterThan(16));

 

getSelectField was used because otherwise the aliasing of the field somehow got screwed up. AFAIR it used the tableAlias inside the SelectQuery instead of the alias of the select itself, which results in errors if you use the view in joins or where clauses.

Maybe wrap the query for the view and the getSelectField calls in their own class with getTable(), getMinorName, etc and it should be very close to what you want.

 

I hope this approach is somehow useful or did I totally misunderstand the question?

 

Cheers,

Jörg

[Lukas Eder]

Hi Jörg,

> what we‘re doing in our project sounds similar and might be helpful for
> Henning’s request. SelectQuery provides asTable() so it can be used anywhere

> or normal table can be used. [...]

Thanks for sharing these ideas. That is of course another valid way to
tackle the problem discussed in this thread.

Cheers
Lukas

2012/11/16 Schönfisch, Jörg <joerg.sc...@softplant.de>:

[Durchholz, Joachim]

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.

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.

[Lukas Eder]

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

> 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. But as I said, as soon as you join
several tables, these primary keys will have a new semantics...

[Durchholz, Joachim]

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

[Lukas Eder]

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

That's my understanding, too. Interestingly, though, because there is
a lot of meta information about materialized views, and also about
common table expressions. The latter are not the same as regular
views, which can be seen from execution plans. It seems that they're
formally more expressive than ordinary views, which is a shame.

> 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 :-)

Well, if the CBO could collect statistics / histograms, etc about
views, that would be quite a feature... 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. E.g:

SELECT DISTINCT v.a
FROM (
SELECT x.a, y.b
FROM x
LEFT OUTER JOIN y on a = b
) v

The CBO can completely strip and ignore the LEFT OUTER JOIN and select
only distinct values from x.a

> 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:
http://docs.oracle.com/cd/E11882_01/server.112/e10595/views001.htm#i1006232

Typically, when adding many-to-one relationships, column
"updatability" disappears from the view.

Anyway, among the updatable columns, you still don't know which ones
are part of a/the primary key.

I have tried to find a solution to this problem before, without any luck:
http://stackoverflow.com/q/5500738/521799

The only reliable way to automatically derive underlying constraint
information from view columns seems to be by applying strict
conventions. If I'm wrong, I'll happily attribute a 250k bounty on
Stack Overflow to a better answer :-), as this would solve a couple of
non-jOOQ problems for me, too.

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

[Durchholz, Joachim]

> 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 :-)

[Lukas Eder]

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

[Durchholz, Joachim]

>> 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 :-)

[Lukas Eder]

> I guess it's also outside the scope of Jooq itself :-)

It clearly is

> Not sure about CTEs (what's that?) and arrays (never used them, too
> DB-specific to be comfortable with that).

Common Table Expressions, e.g.:

WITH
x AS (SELECT 1 a FROM DUAL),
y AS (SELECT 2 b FROM DUAL)
SELECT x.a, y.b FROM x, y

You can have CTE's in views, too.

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

Yes, too many not-well-designed features are a bad thing. I'm aware of
this. This is one reason why I'm removing master data tables from jOOQ
3.0...

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

I agree. I wouldn't want to deal with a "not-well-designed" database
schema, in the first place. The idea we're discussing here is mainly
targeted at people dealing with well-designed databases. The "others"
are used to finding creative solutions anyway, so I won't take a guess
at what the best solution for *them* would be.

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

((A|B|C)\.PK_X|D\.PK_Y)

... works for me ;-)

>> Good luck with such an approach :-)
>> To me, it's in the same category as parsing the view definition...
>

> [...]

> Unless you mean the "too much work to be worth it for Jooq" category,
> of course :-)

Yes. It's even too much work to seriously think about it :-)

Cheers
Lukas