Our previously used framework has a mechanism that automatically extends the SQL to filter out the logically deleted rows. Is there a way to implement this also with jOOQ?
The idea would be:
- Find out all the involved tables via the
FROMandJOINparts (a more complex logic isn't needed). - Determine if they are a logical deletable table (we already have our own meta data for this).
- Extend the
JOIN ... ON ...part withdeleted = 0if deleted wasn't already given. - Add/extend the
WHEREpart withdeleted = 0if deleted wasn't already given for the main table.
The initial promising way seemed to be QOM (https://blog.jooq.org/traversing-jooq-expression-trees-with-the-new-traverser-api/).
But if I understand correctly it isn't ready yet for this requirement as it doesn't traverse into the JOIN parts.
Also as it is experimental it might be risky (?). Is there a rough time/version schedule planned for it or is it too unforeseeable in the future?
Extending the WHERE part (without an existing check) is very easy with addConditions.
But doing the same for the JOIN ... ON ... part?
As jOOQ doesn't allow extending certain classes as they are marked as final (not meant as a criticism as there are understandable reasons for it) and Java doesn't support extension methods like C# the only solution I could think of up until now is that we have our own top-level select-method that does it all.
Example: selectFromWhereNotDeleted(table1, table2, table3) -> rest of the select statement can be added via fluent API
It adds the correct join conditions based on our meta data. Also it determines if the table is logical deletable and adds deleted = 0 everywhere accordingly. This is implemented and it works. But it totally breaks with the standard known SQL syntax. Especially if you want to only select certain columns and/or extend the JOIN ... ON ... part. I've made that work also but again a special syntax per table parameter is needed (wrapper class/methods). Meaning you have to jump between the SQL parts when writing the code.
The alternative would have been to implemented all the involved jOOQ classes ourselves as a wrapper to be able to extend them with the needed methods. I scratched that as I find it too messy (too many layers, too much code, too brittle if jOOQ changes in the future).
There seems to have been the idea to implement a soft-deleting feature in jOOQ at some time (https://github.com/jOOQ/jOOQ/issues/2683).
But am I missing something? Is there already a better/easier way?
CodePudding user response：
Is there a way to implement this also with jOOQ?
Even before all these features are available, you could implement it yourself using the existing VisitListener SPI. An example is given in this blog post, showing how to implement row level security with jOOQ using a VisitListener. The implementation details are too complex for a Stack Overflow answer, though the blog post will provide you with sufficient ideas.
The initial promising way seemed to be QOM. But if I understand correctly it isn't ready yet for this requirement as it doesn't traverse into the JOIN parts.
It will traverse into JOIN trees starting from jOOQ 3.18, see also #13640 for a list of changes. You can access 3.18.0-SNAPSHOT builds already today: https://www.jooq.org/download/versions
Also as it is experimental it might be risky (?).
As of jOOQ 3.18, the QOM API is still subject to (incompatible) change between minor releases. E.g. there might be
- Method name changes
- Type hierarchy changes (e.g. it's unclear if a
Selectshould have set operations (internal status quo), or if a new, higher level type will be needed)
The risk on your end is probably acceptable. The API won't go away anymore. Numerous customers already rely on it to implement custom SQL transformations like you do.
Is there a rough time/version schedule planned for it or is it too unforeseeable in the future?
No promises, of course, but I have strong hopes for this API to finalise by the end of 2023.
There seems to have been the idea to implement a soft-deleting feature in jOOQ at some time.
Indeed, soft-deleting will come to jOOQ soon in one of the next releases (3.19 or so?): #2683. To implement it properly, jOOQ first needed a robust:
- Public query object model (
QOM) API QOMtraversal and replacement capabilities- SQL transformation capabilities
I.e. all the things you've found yourself. However, it's likely that soft-deleting can be published before all the above will leave experimental status.