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 <[email protected]> > Hello Lukas, > > You mentioned in this GitHub > comment<https://github.com/jOOQ/jOOQ/issues/2665#issuecomment-22241049>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: http://www.jooq.org/doc/3.1/manual/sql-building/dsl-context/runtime-schema-mapping An example was provided in this thread by Peter Cooner: - https://groups.google.com/d/msg/jooq-user/RRKU3pUHz-s/Fd-dK0p-ciQJ The original requirements were given here: - https://groups.google.com/d/msg/jooq-user/K_tTB4-prDQ/WrDZuqwcRfIJ 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: 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 -- 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.
