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: > > http://www.jooq.org/doc/3.1/manual/sql-building/dsl-context/runtime-schema-mapping > > 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: > > http://www.jooq.org/doc/3.1/manual/sql-execution/crud-with-updatablerecords/optimistic-locking > > 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 -- 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 [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
