PostgreSQL table inheritance
Sander Plas
I accidentally found this in the PostgreSQL docs:
http://www.postgresql.org/docs/current/static/ddl-inherit.html
Never used it, but i think it looks interesting. This seems to allow
inheritance in the database without the need for any "ORM magic" in
the Java domain.
To do anything useful with this in jOOQ, the inheritance hierarchy
would need to be reflected in the generated Table and Record classes
though, and some tricks would probably be required to allow casting
between super and sub types.
Just curious what you think about this..
Sander
Lukas Eder
"Thinking about this" is already on the roadmap :-)
https://sourceforge.net/apps/trac/jooq/ticket/644
Although, I was not aware of Postgres implementing a similar thing as
Oracle. Oracle PL/SQL is actually both a procedural AND an
object-oriented language, although I doubt that the latter is used
very often. You can create OBJECT types, which inherit from other
OBJECT types using the UNDER keyword, similar to Java's "extends"
keyword. All attributes and member procedures/functions are then
inherited. Also, you can create OBJECT VIEWs and OBJECT TABLEs, which
can then be reused in SQL and/or stored procedures. Really powerful
stuff.
I'll have to read into Postgres' implementation of these things.
Interesting also to see a reference to a SQL:1999 standard as far as
type inheritance is concerned. That's certainly something worth
looking into. From what I understand, Postgres just "hides" a foreign
key relationship between cities and capitals behind the notion of one
table inheriting from another?
Cheers
Lukas
2011/6/30 Sander Plas <sande...@gmail.com>:
Lukas Eder
> would need to be reflected in the generated Table and Record classes
> though, and some tricks would probably be required to allow casting
> between super and sub types.
Another thought is to simply let generated tables/table records extend
each other. As in the Postgres example:
------------------------------------------------
class CitiesRecord extends TableRecordImpl<CitiesRecord>;
class CapitalsRecord extends CitiesRecord;
class Cities extends TableImpl<CitiesRecord>;
class Capitals extends Cities;
------------------------------------------------
As can be seen above, the problem will arise when it comes to the
generic <R extends Record> parameter. Today, "covariant generic
inheritance" of table types is not possible. Maybe something like this
should be done instead (only if inheritance is involved):
------------------------------------------------
class CitiesRecord<R extends CitiesRecord> extends
TableRecordImpl<CitiesRecord>;
class CapitalsRecord extends CitiesRecord<CapitalsRecord>;
class Cities<R extends CitiesRecord> extends TableImpl<CitiesRecord>;
class Capitals extends Cities<CapitalsRecord>;
------------------------------------------------
On the other hand, introducing another generic type parameter to
generated classes will be somewhat cumbersome...
Sander Plas
> From what I understand, Postgres just "hides" a foreign
> key relationship between cities and capitals behind the notion of one
> table inheriting from another?
the current implementation do not inherit indexes (see 'Caveats' in
the PostgreSQL page), i guess capitals is implemented as a separate
table with the same fields as cities + a state.
> > To do anything useful with this in jOOQ, the inheritance hierarchy
> > would need to be reflected in the generated Table and Record classes
> > though, and some tricks would probably be required to allow casting
> > between super and sub types.
>
> Another thought is to simply let generated tables/table records extend
> each other.
the most natural way to deal with this.
... I'm not sure about the exact implementation details though :)
I think there are some difficulties too when it comes to type casting.
A "selectFrom(Cities.CITIES)" will return some CitiesRecords that are
in fact CapitalsRecords. There needs to be some way to cast them to
CapitalsRecord. You could probably add the pg_class.relname column
(see PostgreSQL page) to every selectFrom-generated SQL to find out
what the actual subtype of returned 'cities' records is and add a
CapitalsRecord to the Result set instead of a CitiesRecord when
appropriate, but then you're still missing the CapitalsRecord's
'state' value.
Lukas Eder
> A "selectFrom(Cities.CITIES)" will return some CitiesRecords that are
> in fact CapitalsRecords.
But when you select from CITIES, then you will actually only get the
cities records. Even if some records are in fact capitals, their known
type is only Cities, I think? What I mean is this:
---------------------------------------------------------------
-- this will only return cities that are capitals. The state column is included
SELECT * FROM capitals
-- this will return all cities, but not the state column
SELECT * FROM cities
---------------------------------------------------------------
If that's really the behaviour, then I don't think that casting is
going to be needed. On the other hand, I know that Oracle also knows
of abstract / non-instantiable types. If Postgres does, too, then
things may become more complicated.
> There needs to be some way to cast them to
> CapitalsRecord. You could probably add the pg_class.relname column
> (see PostgreSQL page) to every selectFrom-generated SQL to find out
> what the actual subtype of returned 'cities' records is and add a
> CapitalsRecord to the Result set instead of a CitiesRecord when
> appropriate, but then you're still missing the CapitalsRecord's
> 'state' value.
pg_class.relname is probably the correct way to do this. But again,
why do you think we'd need the cast to CapitalsRecord? After all, what
is selected here is a CitiesRecord. And city.store() would correctly
store the "city-part" of a capital, if it is a capital...
Lukas Eder
you'll get a broader audience and lots of good ideas:
http://stackoverflow.com/questions/tagged/jooq
Moodio Moodio
> > I think there are some difficulties too when it comes to type casting.
> > A "selectFrom(Cities.CITIES)" will return some CitiesRecords that are
> > in fact CapitalsRecords.
>
> But when you select from CITIES, then you will actually only get the
> cities records. Even if some records are in fact capitals, their known
> type is only Cities, I think?
> pg_class.relname is probably the correct way to do this. But again,
> why do you think we'd need the cast to CapitalsRecord? After all, what
> is selected here is a CitiesRecord. And city.store() would correctly
> store the "city-part" of a capital, if it is a capital...
But i can imagine situations where you're iterating over cities
records and want to do something "capitals specific", for instance,
show the state, when a record is a capital. Maybe this is the user's
problem: he could just request the full capitals record using the
specific record's primary key when needed. But there still needs to be
a way to find out which cities records are capitalsrecords..
Lukas Eder
There must be something about me being (again) right around here. You
guys talk behind my back? ;-)
> But i can imagine situations where you're iterating over cities
> records and want to do something "capitals specific", for instance,
> show the state, when a record is a capital. Maybe this is the user's
> problem: he could just request the full capitals record using the
> specific record's primary key when needed. But there still needs to be
> a way to find out which cities records are capitalsrecords..
I agree that might be nice. But how can you do that with plain
Postgres, e.g. from pgAdmin III ? Can you somehow select "both" cities
and capitals? As Sander stated, the implementation in Postgres is
probably not done by an implicit join, even if that seems to be the
simplest canonical implementation of inheritance. So when selecting
"both" cities and capitals, with a normalised schema, I'd just outer
join the capitals to the cities, and the resulting "type" would
contain both, including the state column, where applicable.
Another interesting point is this one:
"A table can inherit from more than one parent table, in which
case it has the union of the columns defined by the parent tables."
This simple statement will prevent any type of inheritance-mapping
between SQL and Java, as Java does not support multiple inheritance.
In this case, I'm pretty glad it doesn't, as the power of multiple
inheritance is only exceeded by its mystery, as my coworkers say ;-)
Sander Plas
Lukas Eder
;-)
That sure sounded funny.
Sander Plas
>
> "A table can inherit from more than one parent table, in which
> case it has the union of the columns defined by the parent tables."
>
> This simple statement will prevent any type of inheritance-mapping
> between SQL and Java, as Java does not support multiple inheritance.
> In this case, I'm pretty glad it doesn't, as the power of multiple
> inheritance is only exceeded by its mystery, as my coworkers say ;-)
when only single-inheritance is used in the database.
Wouldn't it be possible to work around this by letting the 'user
facing' types (Cities, CitiesRecord, etc.) be interfaces instead of
classes?
Lukas Eder
> when only single-inheritance is used in the database.
I prefer not to implement (and maintain) such special cases. If there
is support for RDBMS-inheritance in jOOQ, then it should be consistent
for a maximum number of use cases. In my opinion, ignoring inheritance
and treating the two tables as if there were no link is better than
just supporting single inheritance. I feel I could get this very
wrong, if not properly understood.
> Wouldn't it be possible to work around this by letting the 'user
> facing' types (Cities, CitiesRecord, etc.) be interfaces instead of
> classes?
That, in turn, is a very good idea, even if it will cause a major
compatibility break. But it allows for multiple inheritance by
mixin-API design. That's possible both with the "implements" clause,
as well as with the "extends" clause for generics <R extends ... & I1
& I2>. In any case, having "user facing" types being interfaces rather
than classes would allow for many other features that can be hidden
behind those interfaces. To name a few:
- Aliasing could be heavily improved when fields were accessed via
generated methods, instead of static singleton field instances
(https://sourceforge.net/apps/trac/jooq/ticket/117). The
Field.as(String) method could return the table type itself instead of
returning an anonymous table.
- Various jOOQ internals can be hidden behind interfaces. Currently,
there is some potential for API leak
(https://sourceforge.net/apps/trac/jooq/ticket/686). Client code can
call methods from AbstractQueryPart and similar classes.
- A lot more...
I have filed this idea as
https://sourceforge.net/apps/trac/jooq/ticket/688
Lukas Eder
I have just thought of another application of your idea of having
interfaces instead of classes for Table/Record types: In many RDBMS,
stored functions can return cursors/resultsets/tables (I'm currently
implementing support for that for jOOQ 1.6.2). Some RDBMS even expose
the concrete type for those cursors. With interfaces, it might be
easier to hide a stored function behind an org.jooq.Table and to
select from it.
Actually, Postgres has some of the most peculiar implementations for
this. When you select from a function that has OUT parameters, every
OUT parameter will result in a table column... that's quite
interesting
Cheers
Lukas
Sander Plas
There's another (wild) idea related to interfaces that crossed my mind
when i was thinking about inheritance and interfaces. In absence of
real "inheritance" functionality, many databases have multiple tables
that follow the same "base column pattern", for example:
id int primary key,
creation_time timestamp,
modification_time timestamp,
creator_uid int,
mutator_uid int
.. etc. Sometimes it would be nice to be able to write code that is
able to treat these base columns of records of any of these tables the
same way. Currently that is very hard to do with jOOQ because all
generated Record classes directly extend UpdatableRecordImpl, which -
of course- doesn't have any getter or setter methods for these
columns.
If it would be possible to define a "base table" interface (let's say
MyBaseRecord) with getter and setters for these "base" columns, and
instruct the code generator (via some configuration option similar to
the master data table options) to let certain Record classes (or
interfaces) implement MyBaseRecord (just get it to add 'implements
MyBaseRecord' to the class/interface definition), then one could write
methods that work on MyBaseRecord instances.
More complex cases with multiple interfaces extending eachother could
also be possible. Of course, the correct definition of these
interfaces is up to the user. If he defines an interface that doesn't
match the actual record classes, the code just won't compile.
Lukas Eder
I now wonder why I hadn't thought of it from the beginning...
> There's another (wild) idea related to interfaces that crossed my mind
> when i was thinking about inheritance and interfaces. In absence of
> real "inheritance" functionality, many databases have multiple tables
> that follow the same "base column pattern", for example:
>
> id int primary key,
> creation_time timestamp,
> modification_time timestamp,
> creator_uid int,
> mutator_uid int
Yes, that's a very common pattern. I had actually thought about this
recently in an entirely different context. Apart from this pattern,
there is also the change history pattern, audit pattern, and various
other ones that are implemented time and again. Intuitively, I feel
that these features are out of scope for jOOQ, though. Maybe there
should be another artefact called jooq-tools built on top of jOOQ, in
order to extend jOOQ for these frequently used patterns. Such a
jooq-tools project will then also contain DDL statements in order to
create the necessary triggers / columns / change-history tables, etc,
according to best practices for these use-cases. A separate project
would keep the core basic and low-level.
> .. etc. Sometimes it would be nice to be able to write code that is
> able to treat these base columns of records of any of these tables the
> same way. Currently that is very hard to do with jOOQ because all
> generated Record classes directly extend UpdatableRecordImpl, which -
> of course- doesn't have any getter or setter methods for these
> columns.
From my experience, the database is the best place to handle these
items. With identity columns and triggers, you can easily
insert/update creation/modification time/users. But I agree, if the
RDBMS of choice does not support triggers, "injected" interfaces might
become useful. The above jooq-tools project would then heavily rely on
such interface-injection...
In any case, looks like there is some work ahead, in jOOQ :-)