Reviving jOOQ + Inheritance

[Josh Padnick]

Hello,

First, been really enjoying jOOQ. What a pleasure to use and have the full power of SQL available.  Ok, so my question is about modeling inheritance with jOOQ.

I've reviewed the history of posts on this and come across:

• https://groups.google.com/d/msg/jooq-user/9dfbLtnR77c/_0GL1H7f2ZYJ
• https://groups.google.com/d/msg/jooq-user/FLrkH_u2bbE/zJS8DQeFu2wJ
• https://groups.google.com/d/msg/jooq-user/9X_0gROVIf8/XZc2OXqitJQJ
• https://groups.google.com/d/msg/jooq-user/YuTp9-5K9fs/_d5siyYQn3EJ 
  

I understand (and agree) with Lukas's position that since SQL does not directly model inheritance, jOOQ should not either.  I'd like to describe my approach to working around this and would appreciate any feedback for better ways of handling this.

SQL

We have the following tables:

CREATE TABLE notify_event (
    notify_event_id SERIAL  NOT NULL,
    PRIMARY KEY (notify_event_id),
);


CREATE TABLE notify_event_text_message (
    notify_event_id INTEGER  NOT NULL,
    message_body TEXT  NOT NULL,
    CONSTRAINT PK_notify_event_text_message PRIMARY KEY (notify_event_id)
);

Basically, a notify_event is the parent type, and notify_event_text_message is the subtype.

Object Model

In Java, we have modeled this as follows:

• class NotifyEvent extends generated.tables.pojos.NotifyEvent
• class NotifyEventTextMessage extends NotifyEvent

We're using a POJO pattern where we define our own POJOs, but just extend from the jOOQ auto-generated POJO in most cases and then add in any customizations we want.  This has worked very nicely so far, particularly for converting to/from JSON with the opportunity to customize the JSON serialization.

It even works well with patterns like:

notifyEventTextMessageRecord.into( models.NotifyEventTextMessage.class )

And then I manually manage the POJO member property links.  

My Questions

Does anyone else use this pattern with success?  Are there unforeseen issues we may run into down the line?  

So far, it's worked very well, with the sole exception that we have to copy & paste the jOOQ auto-generated POJO into any subtypes (e.g. NotifyEventTextMessage above) because Java doesn't like multiple inheritance.  This is somewhat problematic because if we auto-generate the POJO with changed properties, we no longer get the compiler error.  Has anyone else used this pattern and found a more elegant workaround?

Josh

[Lukas Eder]

Hello Josh,

2014-09-02 21:18 GMT+02:00 Josh Padnick <josh.p...@gmail.com>:

Hello,

First, been really enjoying jOOQ. What a pleasure to use and have the full power of SQL available.  Ok, so my question is about modeling inheritance with jOOQ.

I've reviewed the history of posts on this and come across:

• https://groups.google.com/d/msg/jooq-user/9dfbLtnR77c/_0GL1H7f2ZYJ
• https://groups.google.com/d/msg/jooq-user/FLrkH_u2bbE/zJS8DQeFu2wJ
• https://groups.google.com/d/msg/jooq-user/9X_0gROVIf8/XZc2OXqitJQJ
• https://groups.google.com/d/msg/jooq-user/YuTp9-5K9fs/_d5siyYQn3EJ 
  

I understand (and agree) with Lukas's position that since SQL does not directly model inheritance, jOOQ should not either.  I'd like to describe my approach to working around this and would appreciate any feedback for better ways of handling this.

Hmm, that's not 100% my position. SQL (or at least ORDBMS like Oracle and PostgreSQL) does support inheritance. So far, this kind of true ORDBMS table inheritance has been a low priority for jOOQ, though, as it is hardly ever used.

As far as various models of mapping Java inheritance to SQL through discriminators, joins, etc. is concerned, I'm indeed a bit reluctant as jOOQ does not implement this kind of ORM.

 

SQL

We have the following tables:

CREATE TABLE notify_event (
    notify_event_id SERIAL  NOT NULL,
    PRIMARY KEY (notify_event_id),
);


CREATE TABLE notify_event_text_message (
    notify_event_id INTEGER  NOT NULL,
    message_body TEXT  NOT NULL,
    CONSTRAINT PK_notify_event_text_message PRIMARY KEY (notify_event_id)
);

Basically, a notify_event is the parent type, and notify_event_text_message is the subtype.

Object Model

In Java, we have modeled this as follows:

• class NotifyEvent extends generated.tables.pojos.NotifyEvent
• class NotifyEventTextMessage extends NotifyEvent

We're using a POJO pattern where we define our own POJOs, but just extend from the jOOQ auto-generated POJO in most cases and then add in any customizations we want.  This has worked very nicely so far, particularly for converting to/from JSON with the opportunity to customize the JSON serialization.

It even works well with patterns like:

notifyEventTextMessageRecord.into( models.NotifyEventTextMessage.class )

And then I manually manage the POJO member property links.  

My Questions

Does anyone else use this pattern with success?  Are there unforeseen issues we may run into down the line?  

So far, it's worked very well, with the sole exception that we have to copy & paste the jOOQ auto-generated POJO into any subtypes (e.g. NotifyEventTextMessage above) because Java doesn't like multiple inheritance.

You might be able to implement that copy & pasting also when overriding the code generator behaviour... Have you tried that?

Apart from that, given that you're using PostgreSQL, you may in fact try to use PostgreSQL's inheritance mechanism. This will be transparent to jOOQ, and jOOQ will generate NotifyEventTextMessage POJOs with all inherited columns as well.

More information here:

http://www.postgresql.org/docs/9.3/static/tutorial-inheritance.html

Cheers

Lukas

[Josh Padnick]

Thanks for your reply, Lukas!  See comments below:

On Wednesday, September 3, 2014 7:37:15 AM UTC-7, Lukas Eder wrote:

Hello Josh,

2014-09-02 21:18 GMT+02:00 Josh Padnick <josh.p...@gmail.com>:

Hello,

First, been really enjoying jOOQ. What a pleasure to use and have the full power of SQL available.  Ok, so my question is about modeling inheritance with jOOQ.

I've reviewed the history of posts on this and come across:

• https://groups.google.com/d/msg/jooq-user/9dfbLtnR77c/_0GL1H7f2ZYJ
• https://groups.google.com/d/msg/jooq-user/FLrkH_u2bbE/zJS8DQeFu2wJ
• https://groups.google.com/d/msg/jooq-user/9X_0gROVIf8/XZc2OXqitJQJ
• https://groups.google.com/d/msg/jooq-user/YuTp9-5K9fs/_d5siyYQn3EJ 
  

I understand (and agree) with Lukas's position that since SQL does not directly model inheritance, jOOQ should not either.  I'd like to describe my approach to working around this and would appreciate any feedback for better ways of handling this.

Hmm, that's not 100% my position. SQL (or at least ORDBMS like Oracle and PostgreSQL) does support inheritance. So far, this kind of true ORDBMS table inheritance has been a low priority for jOOQ, though, as it is hardly ever used.

As far as various models of mapping Java inheritance to SQL through discriminators, joins, etc. is concerned, I'm indeed a bit reluctant as jOOQ does not implement this kind of ORM.

Thanks for clarifying.  I would say that jOOQ's support of inheritance works great for us, with the exception of the limitation imposed by java's lack of support for multiple inheritance.

 

 

SQL

We have the following tables:

CREATE TABLE notify_event (
    notify_event_id SERIAL  NOT NULL,
    PRIMARY KEY (notify_event_id),
);


CREATE TABLE notify_event_text_message (
    notify_event_id INTEGER  NOT NULL,
    message_body TEXT  NOT NULL,
    CONSTRAINT PK_notify_event_text_message PRIMARY KEY (notify_event_id)
);

Basically, a notify_event is the parent type, and notify_event_text_message is the subtype.

Object Model

In Java, we have modeled this as follows:

• class NotifyEvent extends generated.tables.pojos.NotifyEvent
• class NotifyEventTextMessage extends NotifyEvent

We're using a POJO pattern where we define our own POJOs, but just extend from the jOOQ auto-generated POJO in most cases and then add in any customizations we want.  This has worked very nicely so far, particularly for converting to/from JSON with the opportunity to customize the JSON serialization.

It even works well with patterns like:

notifyEventTextMessageRecord.into( models.NotifyEventTextMessage.class )

And then I manually manage the POJO member property links.  

My Questions

Does anyone else use this pattern with success?  Are there unforeseen issues we may run into down the line?  

So far, it's worked very well, with the sole exception that we have to copy & paste the jOOQ auto-generated POJO into any subtypes (e.g. NotifyEventTextMessage above) because Java doesn't like multiple inheritance.

You might be able to implement that copy & pasting also when overriding the code generator behaviour... Have you tried that?

I have not, and hadn't considered that!  So, I guess I would explicitly declare child tables somewhere and then auto-generate the corresponding POJOs.  The only issue with this is that we then manually customize these POJOs using the Jackson library to facilitate automatic conversion to JSON.  That's nothing more than adding annotations, though, so I could probably handle that as well.

Looks like the best docs for this are at http://www.jooq.org/doc/3.4/manual/code-generation/codegen-generatorstrategy/.

 

Apart from that, given that you're using PostgreSQL, you may in fact try to use PostgreSQL's inheritance mechanism. This will be transparent to jOOQ, and jOOQ will generate NotifyEventTextMessage POJOs with all inherited columns as well.

I had considered this, but was a little freaked out about the limitations outlined at http://www.postgresql.org/docs/9.3/static/ddl-inherit.html.  It seemed like we were trading one problem (the inherent mismatch between Java inheritance and SQL) for another problem (the particular quirks Postgres inheritance has for constraints).  Do you know of anyone successfully using this in production?  Did they find it to be worth the overhead?

Thanks as always for your prompt and insightful responses.

[Josh Padnick]

Sigh, I had a whole set of inline responses written out, but Google didn't post them and I've lost the message.  Anyway, summary of what I just wrote:

• Lukas, thanks as always for your response
• I hadn't considered writing a custom generator.  The only snag here is that we add annotations to certain methods depending on how we want these POJOs concerted to JSON.  Will look into this; a cool idea toward automating!
• Regarding PostgreSQL inheritance, I was initially excited about this, but I was scared off by many of the limitations and edge cases that we will inevitably run into.  For example, foreign key relationships get inherited but the relationship itself doesn't appear to be enforced in a child table.  Do you have jOOQ customers using Oracle or Postgres inheritance with success?  Do you see this as a strong option for supporting inheritance in jOOQ or just one option?

Thanks again for your input!

Josh

On Wednesday, September 3, 2014 7:37:15 AM UTC-7, Lukas Eder wrote:

[Lukas Eder]

Hi Josh,

That's a bummer with the lost message. Don't you hate when that happens? Thanks for trying again and summarising your thoughts.

2014-09-03 22:17 GMT+02:00 Josh Padnick <josh.p...@gmail.com>:

Sigh, I had a whole set of inline responses written out, but Google didn't post them and I've lost the message.  Anyway, summary of what I just wrote:

• Lukas, thanks as always for your response

You're welcome, of course!

• I hadn't considered writing a custom generator.  The only snag here is that we add annotations to certain methods depending on how we want these POJOs concerted to JSON.  Will look into this; a cool idea toward automating!

Can you show a couple of examples? Maybe, we'll be able to factor out a new feature request. 

• Regarding PostgreSQL inheritance, I was initially excited about this, but I was scared off by many of the limitations and edge cases that we will inevitably run into.  For example, foreign key relationships get inherited but the relationship itself doesn't appear to be enforced in a child table.  Do you have jOOQ customers using Oracle or Postgres inheritance with success?  Do you see this as a strong option for supporting inheritance in jOOQ or just one option?

I agree that relational inheritance is a door that, once opened, will give way to a myriad of edge cases. I wasn't aware of the ones you've mentioned. 

Personally, I don't have any experience with PostgreSQL ORDBMS features. Our integration partner UWS is using it to implement partitioning in their projects:

http://blog.jooq.org/2014/08/26/integrating-jooq-with-postgresql-partitioning

That might be a different use-case from yours, though.

I do have some experience with Oracle's object-oriented features, though and I would not recommend using that, as DDL will be set in stone forever. There's virtually no way to run any ALTER TABLE statements once you make your tables depend on OBJECT types. That may be fine for temporary tables (like queues, etc.) but certainly not for regular data.

Our Oracle customers thus far have not been asking for support of ORDBMS features in Oracle, although some of them are very happy with object-oriented PL/SQL support, where this makes much more sense.

Best,

Lukas

[Lukas Eder]

... I think this was the "missing" E-Mail with inline comments that you referred to. It was stuck in Google Groups' Spam filter, which has been filtering much more aggressively, lately. I'm sorry about that.

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Josh Padnick]

No problem at all :) It is nice to know Google Groups isn't accepting posts intermittently, and what actually happened.  Regarding your original question...

• I hadn't considered writing a custom generator.  The only snag here is that we add annotations to certain methods depending on how we want these POJOs concerted to JSON.  Will look into this; a cool idea toward automating!

Can you show a couple of examples? Maybe, we'll be able to factor out a new feature request. 

So, we're using EmberJS on the frontend, and Postgres on the backend.  Therefore, a single row of data makes a journey from "row in table" to "POJO" to "JSON" and back in reverse when we get data from our front-end.

jOOQ gets us from "row in table" to POJO using the awesome into() method.  We don't even overwrite the default behavior here, and for POJOs with complex relationships, we just manually read the select results "into" the appropriate POJO and then manually assemble the POJO.  Maybe this could be more automated, but it's nice to have explicit code about how our POJOs are constructed.

So, in the POJOs, we do things like the following:

NotifyEvent.java

// We start by inheriting from the auto-generated POJO
public class NotifyEvent extends db.jooq.generated...tables.pojos.NotifyEvent {

    private Integer notifyEventId;
    private Integer notifyConversationId;
   
    // This it the code that jOOQ auto-generates, but Ember Data in EmberJS will need our JSON to return a property named       // "id", so we use Jackson JSON Annotations so that Json.toJson( thisPojo ) will automatically produce this.
    // Note that "Json.toJson" is specific to Play Framework, but it is just a util method that wraps native Jackson
    // functionality
    @JsonProperty("id")
    public java.lang.Integer getNotifyEventId() {
      return this.notifyEventId;
    }

    // Likewise, when Ember sends us data, we need to tell our POJO that "id" in the JSON is really this property
    @JsonProperty("id")
    public void setNotifyEventId(java.lang.Integer notifyEventId) {
       this.notifyEventId = notifyEventId;
    }

    // To handle foreign key relationships, sometimes we want to embed the full JSON of the related object,
    // but sometimes (like in this case), we just want to report the id.  This automates that, too.
    @JsonProperty("notifyConversation")
    public java.lang.Integer getNotifyConversationId() {
        return this.notifyConversationId;
    }


    @JsonProperty("notifyConversation")
    public void setNotifyConversationId(java.lang.Integer notifyConversationId) {
        this.notifyConversationId = notifyConversationId;
    }

So, in an ideal world, jOOQ's auto-generator could let us:

• Explicitly handle inheritance relationships in the POJOs that are generated, perhaps with some kind of separate XML file (do you see this as viable today by overriding the generator behavior?)
• Explicitly declare annotations, perhaps in a separate XML file

Then we could have jOOQ read our SQL, read our XML annotation declarations, and read our XML inheritance declarations and then just auto-generate everything.

Hope that answers your question,

Josh

[Lukas Eder]

Hi Josh,

If I understood you correctly, this should be quite easy to do already today by extending the JavaGenerator from jooq-codegen. You could override org.jooq.util.JavaGenerator.generatePojo(TableDefinition table) and call the super method (which generates db.jooq.generated...NotifyEvent) before you generate your own subtype (NotifyEvent).

Since you're choosing extension, you'll have full control over the contents of your own subtype. Also, such a solution would give you complete liberty with respect to how you'd like to structure and interpret that XML file.

Hope that answers your question,

Yep. :-)

I think this is a solution quite tightly coupled to your requirements, so I'm not sure if this could go into jOOQ at the current stage. But probably, with what we have today and a bit of extension code at your side, you can already achieve that automation.

Cheers,

Lukas