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 columnThis 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 flagsI 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 timestampsIt 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.
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 columnThis 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:
2.) Soft-delete flagsI 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.13.) Insert/Update timestampsIt 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 helpsLukas
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 columnThis 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 flagsI 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.13.) Insert/Update timestampsIt 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 helpsLukasYes, 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?