Hi Sam, 2013/8/7 Sam Braam <[email protected]>
> 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<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. > 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: https://github.com/jOOQ/jOOQ/issues/2682 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: https://github.com/jOOQ/jOOQ/issues/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: >> http://www.jooq.org/doc/3.1/**manual/sql-execution/crud-** >> with-updatablerecords/**optimistic-locking<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? > An indicative, non-authoritative release date is late September 2013. Infrequent SNAPSHOT releases are available from the Sonatype repository: https://oss.sonatype.org/content/repositories/snapshots/org/jooq Best Regards, Lukas -- 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.
