Use-cases and possible workarounds regarding #2665 - SPI for RenderContext and BindContext listening to allow for custom SQL transformation

232 views
Skip to first unread message

Sam Braam

unread,
Aug 7, 2013, 8:34:01 AM8/7/13
to jooq...@googlegroups.com
Hello Lukas,

You mentioned in this GitHub comment that I should post my use cases here for discussion regarding #2665.

I am in the early phases of replacing an in-house DB framework sitting atop a legacy schema which cannot be modified.  JOOQ fits well here providing modern mechanisms for type safe interaction with the DB without trying to shoehorn the entire schema into an ORM (or the ORM onto the schema).  The replacement framework should encapsulate some of the redundant details of our DML profile, while maintaining the expressiveness of your DSL.  The features included in #2665 would (as far as I can tell) allow me to accomplish this end.  I will list a couple of my use cases, perhaps there are other ways of dealing with this until 3.2 is released?

1.) Multi-tenant partitioning via discriminator column

This is the big one.  DML statements run against partitioned tables must include the discriminator in the WHERE clause.  Failure to do so is nothing short of catastrophic, so I would like to enforce it for all DML generated through the DSL.  Unfortunately not all tables are partitioned, so a global implementation would need to inspect the statement to determine if any of the tables affected contain a discriminator column.  The ExecuteListener Interface and the ExecuteContext provided Query does not expose enough information to adequately make this determination and append the condition.  Perhaps you see an alternative?


2.) Soft-delete flags

I can't imagine that I am the only one facing this dilemma: How can I provide DRY style consideration of soft-delete flags while using the DSL?   It would be great if I could implement a Listener interface and check if the queried tables have a soft delete flag, and when yes, append a condition regarding it.  Again, it seems the current API does not provide enough query introspection to accomplish this in a listener.


3.) Insert/Update timestamps

It would be nice if this could also be centrally enforced for tables containing the supporting columns.


Obviously some of the above mentioned points could be solved at the RDBMS level, but unfortunately my options are quite limited in that area. Perhaps the "Listener" approach is the wrong one for my cases, although at first glance it appears correct.  I look forward to hearing any thoughts/ideas you may have.

Sam




Lukas Eder

unread,
Aug 7, 2013, 9:50:08 AM8/7/13
to jooq...@googlegroups.com
Hello Sam,

Thank you for sharing your use-cases with the community. I think there are a couple of interesting problems to be evaluated here.

2013/8/7 Sam Braam <s...@bra.am>

Hello Lukas,

You mentioned in this GitHub comment that I should post my use cases here for discussion regarding #2665.

I am in the early phases of replacing an in-house DB framework sitting atop a legacy schema which cannot be modified.  JOOQ fits well here providing modern mechanisms for type safe interaction with the DB without trying to shoehorn the entire schema into an ORM (or the ORM onto the schema).  The replacement framework should encapsulate some of the redundant details of our DML profile, while maintaining the expressiveness of your DSL.  The features included in #2665 would (as far as I can tell) allow me to accomplish this end.  I will list a couple of my use cases, perhaps there are other ways of dealing with this until 3.2 is released?

1.) Multi-tenant partitioning via discriminator column

This is the big one.  DML statements run against partitioned tables must include the discriminator in the WHERE clause.  Failure to do so is nothing short of catastrophic, so I would like to enforce it for all DML generated through the DSL.  Unfortunately not all tables are partitioned, so a global implementation would need to inspect the statement to determine if any of the tables affected contain a discriminator column.  The ExecuteListener Interface and the ExecuteContext provided Query does not expose enough information to adequately make this determination and append the condition.  Perhaps you see an alternative?

I'm aware of a jOOQ users having implemented table partitioning using jOOQ's runtime table mapping feature:

An example was provided in this thread by Peter Cooner:

The original requirements were given here:

Unfortunately, I'm not aware if it worked out well for Peter.

In any case, I think that #2665 might offer a more thorough solution for your, once it is released in jOOQ 3.2.
 
2.) Soft-delete flags

I can't imagine that I am the only one facing this dilemma: How can I provide DRY style consideration of soft-delete flags while using the DSL?   It would be great if I could implement a Listener interface and check if the queried tables have a soft delete flag, and when yes, append a condition regarding it.  Again, it seems the current API does not provide enough query introspection to accomplish this in a listener.

To be sure I got this right: Some of your tables have a T.DELETED column, which is set to true to indicate that a record is deleted. Right?

So here are a couple of use-cases that I would see deriving from the above:
- By default, not "DELETED" records should be fetched from the database
- The above may be overridden by adding an explicit predicate on the "DELETED" column.
- Potentially, even jOOQ's DELETE statement would be overridden, producing an UPDATE statement to change the "DELETED" flag.

Again, #2665 would be the most thorough means of implementing all of this, although a rewrite from DELETE to UPDATE is probably not supported in the first version. I could imagine a VisitListener (intercepting DELETE on appropriate tables) communicating with an ExecuteListener in order to replace the query being executed.

Other than that, I don't think there's an easy way of implementing the above in jOOQ 3.1
 
3.) Insert/Update timestamps

It would be nice if this could also be centrally enforced for tables containing the supporting columns.

This is implemented along the lines of optimistic locking, which allows to enforce record versions or timestamps:

Note that optimistic locking was implemented around jOOQ's CRUD API (i.e. UpdatableRecord). These version or timestamp value updates are currently not enforced, if you bypass the CRUD API through explicit UPDATEs or through plain SQL.
 
Obviously some of the above mentioned points could be solved at the RDBMS level, but unfortunately my options are quite limited in that area. Perhaps the "Listener" approach is the wrong one for my cases, although at first glance it appears correct.  I look forward to hearing any thoughts/ideas you may have.

In my opinion, the jOOQ 3.2 VisitListener form #2665 will be the best choice for injecting the type of custom behaviour that you're planning to inject (short of database triggers, of course).

Hope this helps
Lukas 

Sam Braam

unread,
Aug 7, 2013, 11:37:37 AM8/7/13
to jooq...@googlegroups.com
Hello Lukas,

thank you for the quick reply.  Just a quick clarification between the lines...


On Wednesday, August 7, 2013 3:50:08 PM UTC+2, Lukas Eder wrote:
Hello Sam,

Thank you for sharing your use-cases with the community. I think there are a couple of interesting problems to be evaluated here.
1.) Multi-tenant partitioning via discriminator column

This is the big one.  DML statements run against partitioned tables must include the discriminator in the WHERE clause.  Failure to do so is nothing short of catastrophic, so I would like to enforce it for all DML generated through the DSL.  Unfortunately not all tables are partitioned, so a global implementation would need to inspect the statement to determine if any of the tables affected contain a discriminator column.  The ExecuteListener Interface and the ExecuteContext provided Query does not expose enough information to adequately make this determination and append the condition.  Perhaps you see an alternative?

I'm aware of a jOOQ users having implemented table partitioning using jOOQ's runtime table mapping feature:


Schema-based multi-tenancy would be a dream if it were possible... But alas, the partioning I was refering to was not physical partioning per se, but logical partitioning of rows in a table using a key (usually a composite primary) to retrieve them.  This is used in a SaaS platform where more than one "Tenant" or hosted customer uses not only the same database and schema, but also the same tables.   This requires that one consistently use the "discriminator" key when accessing the database, for example:


select * from PRODUCTS where PRODUCT_ID = 4711 and TENANT_ID = 41;

...where TENANT_ID is the discriminator key and the PRODUCT_ID potentially exists more than once in the table. You can imagine the consequence if one were to forget the TENANT_ID in the where clause  See http://msdn.microsoft.com/en-us/library/aa479086.aspx for more info about the topic.  It looks like I'll need to implement this verbosely for now.
 
 
2.) Soft-delete flags

I can't imagine that I am the only one facing this dilemma: How can I provide DRY style consideration of soft-delete flags while using the DSL?   It would be great if I could implement a Listener interface and check if the queried tables have a soft delete flag, and when yes, append a condition regarding it.  Again, it seems the current API does not provide enough query introspection to accomplish this in a listener.

To be sure I got this right: Some of your tables have a T.DELETED column, which is set to true to indicate that a record is deleted. Right?

Exactly.
 

So here are a couple of use-cases that I would see deriving from the above:
- By default, not "DELETED" records should be fetched from the database
- The above may be overridden by adding an explicit predicate on the "DELETED" column.
- Potentially, even jOOQ's DELETE statement would be overridden, producing an UPDATE statement to change the "DELETED" flag.

Again, #2665 would be the most thorough means of implementing all of this, although a rewrite from DELETE to UPDATE is probably not supported in the first version. I could imagine a VisitListener (intercepting DELETE on appropriate tables) communicating with an ExecuteListener in order to replace the query being executed.


Although a "nice to have" feature,  Completely rewriting the query is not really necessary.  I was thinking more about your first and second use-cases to eliminate the boilerplate code.
 
Other than that, I don't think there's an easy way of implementing the above in jOOQ 3.1
 
3.) Insert/Update timestamps

It would be nice if this could also be centrally enforced for tables containing the supporting columns.

This is implemented along the lines of optimistic locking, which allows to enforce record versions or timestamps:

Note that optimistic locking was implemented around jOOQ's CRUD API (i.e. UpdatableRecord). These version or timestamp value updates are currently not enforced, if you bypass the CRUD API through explicit UPDATEs or through plain SQL.
 
Obviously some of the above mentioned points could be solved at the RDBMS level, but unfortunately my options are quite limited in that area. Perhaps the "Listener" approach is the wrong one for my cases, although at first glance it appears correct.  I look forward to hearing any thoughts/ideas you may have.

In my opinion, the jOOQ 3.2 VisitListener form #2665 will be the best choice for injecting the type of custom behaviour that you're planning to inject (short of database triggers, of course).

Hope this helps
Lukas 

Yes, your info did indeed help, thank you for taking the time to write. It looks like I'll need to wait for 3.2.  What is your estimated timeline for the release?

Sam 
 

Lukas Eder

unread,
Aug 8, 2013, 6:09:13 AM8/8/13
to jooq...@googlegroups.com
Hi Sam,

2013/8/7 Sam Braam <s...@bra.am>

Hello Lukas,

thank you for the quick reply.  Just a quick clarification between the lines...


On Wednesday, August 7, 2013 3:50:08 PM UTC+2, Lukas Eder wrote:
Hello Sam,

Thank you for sharing your use-cases with the community. I think there are a couple of interesting problems to be evaluated here.


1.) Multi-tenant partitioning via discriminator column

This is the big one.  DML statements run against partitioned tables must include the discriminator in the WHERE clause.  Failure to do so is nothing short of catastrophic, so I would like to enforce it for all DML generated through the DSL.  Unfortunately not all tables are partitioned, so a global implementation would need to inspect the statement to determine if any of the tables affected contain a discriminator column.  The ExecuteListener Interface and the ExecuteContext provided Query does not expose enough information to adequately make this determination and append the condition.  Perhaps you see an alternative?

I'm aware of a jOOQ users having implemented table partitioning using jOOQ's runtime table mapping feature:


Schema-based multi-tenancy would be a dream if it were possible... But alas, the partioning I was refering to was not physical partioning per se, but logical partitioning of rows in a table using a key (usually a composite primary) to retrieve them.  This is used in a SaaS platform where more than one "Tenant" or hosted customer uses not only the same database and schema, but also the same tables.   This requires that one consistently use the "discriminator" key when accessing the database, for example:


select * from PRODUCTS where PRODUCT_ID = 4711 and TENANT_ID = 41;

...where TENANT_ID is the discriminator key and the PRODUCT_ID potentially exists more than once in the table. You can imagine the consequence if one were to forget the TENANT_ID in the where clause  See http://msdn.microsoft.com/en-us/library/aa479086.aspx for more info about the topic.  It looks like I'll need to implement this verbosely for now.

Thanks for the clarifications! I have never thought of multi-tenancy the way MSDN describes it. In previous projects I was working on, we implemented the same kind of multi-tenancy using Oracle's session variables. We initialised every "session" with appropriate SYS_CONTEXT variables, which would be used in all multi-tenant capable database views. Access layers (e.g. Java) had read/write grants only to those views that restricted the data sets according to the context's tenant (and even user), not to the physical tables.

This seemed to be the most thorough way to implement multi-tenancy using "discriminator" keys. I'll have to remember blogging about this solution.

But this solution was Oracle-dependent, and built into the legacy system from the beginning. For older legacy systems, it would be very nice if jOOQ had such capabilities.

Apart from implementing #2665 for SQL rewriting, I'll also register a new feature request that will implement this kind of shared-schema multi-tenancy out of the box in jOOQ, built upon #2665:

It might not be ready for jOOQ 3.2, though.

2.) Soft-delete flags

I can't imagine that I am the only one facing this dilemma: How can I provide DRY style consideration of soft-delete flags while using the DSL?   It would be great if I could implement a Listener interface and check if the queried tables have a soft delete flag, and when yes, append a condition regarding it.  Again, it seems the current API does not provide enough query introspection to accomplish this in a listener.

To be sure I got this right: Some of your tables have a T.DELETED column, which is set to true to indicate that a record is deleted. Right?

Exactly.
 

So here are a couple of use-cases that I would see deriving from the above:
- By default, not "DELETED" records should be fetched from the database
- The above may be overridden by adding an explicit predicate on the "DELETED" column.
- Potentially, even jOOQ's DELETE statement would be overridden, producing an UPDATE statement to change the "DELETED" flag.

Again, #2665 would be the most thorough means of implementing all of this, although a rewrite from DELETE to UPDATE is probably not supported in the first version. I could imagine a VisitListener (intercepting DELETE on appropriate tables) communicating with an ExecuteListener in order to replace the query being executed.


Although a "nice to have" feature,  Completely rewriting the query is not really necessary.  I was thinking more about your first and second use-cases to eliminate the boilerplate code.

I see. Well, the first two use-cases will certainly help removing much of the boilerplate. Nonetheless, in the long run, we can think about adding out-of-the-box support for this common functionality in jOOQ, again built upon #2665. I'll register #2683:

Of course, if the DB architecture can be adapted, the most reliable way of implementing soft-deletes is again by abstracting table access through database views that hide deleted records from the access layer, as well as using INSTEAD OF triggers, executing an UPDATE instead of a DELETE. Another one for my blog-post-to-come.

Other than that, I don't think there's an easy way of implementing the above in jOOQ 3.1
 
3.) Insert/Update timestamps

It would be nice if this could also be centrally enforced for tables containing the supporting columns.

This is implemented along the lines of optimistic locking, which allows to enforce record versions or timestamps:

Note that optimistic locking was implemented around jOOQ's CRUD API (i.e. UpdatableRecord). These version or timestamp value updates are currently not enforced, if you bypass the CRUD API through explicit UPDATEs or through plain SQL.
 
Obviously some of the above mentioned points could be solved at the RDBMS level, but unfortunately my options are quite limited in that area. Perhaps the "Listener" approach is the wrong one for my cases, although at first glance it appears correct.  I look forward to hearing any thoughts/ideas you may have.

In my opinion, the jOOQ 3.2 VisitListener form #2665 will be the best choice for injecting the type of custom behaviour that you're planning to inject (short of database triggers, of course).

Hope this helps
Lukas 

Yes, your info did indeed help, thank you for taking the time to write. It looks like I'll need to wait for 3.2.  What is your estimated timeline for the release?

An indicative, non-authoritative release date is late September 2013. Infrequent SNAPSHOT releases are available from the Sonatype repository:

Best Regards,
Lukas
Reply all
Reply to author
Forward
0 new messages