Hello Lukas, yes, the FkExecuteListener i wrote is a "poor-mans-solution" :)
thanks for mentioning all the points that Policies are covering. I will keep this in mind to mention in my team as soon as jooq is used in our product for some time (if i can convince my colleagues to use jooq instead of hibernate that is / in my old company we already used jooq), and make sure it is clear, that my current solution does not cover those cases. I checked the generated Schema / Catalog instances and found no problem in getting all generated tables, and searching for the field in them, so i'm conviced that the Policy Feature would be good to use as it is for me. If you want you can close your ticket, as it seems everything i need would work with the Policies. While saying this, i take into account that in a multi-module setup, there would be separated project for the Codegen, Core, Subprojects ,... - codegen-project (generates code for database1, database, ...) - core-project (defines a common DSLFactory that creates the Jooq Configuration and from that, the Jooq DSLContext object) - project1 (uses the core-project and only database1, and would need to write a Project1DSLFactory, that creates Policies specific for database1 / project1) - project2 (uses the core-project and only database2, and would need to write a Project2DSLFactory, that creates Policies specific for database2 / project2) - server1 (uses project1 and provides rest-endpoints and defines evolutions with liquibase for database1) - server2 (uses project2 and provides rest-endpoints and defines evolutions with liquibase for database2) with outlining such (example) dependencies between projects i want to say, that the codegen-project containing the Schema / Catalog ist often defined separately from the projects that are depending on its generated Schema / Catalog instances, and that we normally strive to keep database-specific generated classes out of the database-independent core-projects, but its often not possible and also not really necessary. lukas...@gmail.com schrieb am Dienstag, 26. März 2024 um 07:31:12 UTC: > Hi Bernd, > > On Mon, Mar 25, 2024 at 6:04 PM 'Bernd Huber' via jOOQ User Group < > jooq...@googlegroups.com> wrote: > >> Hello Lukas, >> >> the generic Map<Object, Object> is indeed a good solution. >> I have changed my code accordingly, and the result looks better! >> I can use the default nested-transactions the way they are described in >> the jooq documentation >> and with help of "dsl.data(x,y)" i still have my context-specific data >> available in my dao-layer. >> >> --- >> i implemented the tenantId-check for all SELECT-Statements with a custom >> ExecuteListener, >> that checks for the table if it contains a column "clientId" and will >> append the clientId from the request. >> see: >> https://github.com/funkrusher/fk-framework-quarkus-jooq/blob/main/fk_core/src/main/java/org/fk/core/jooq/FkExecuteListener.java >> > > Thanks for sharing that FkExecuteListener. > > This will work for simple cases indeed. But it doesn't take into account: > > - DML (INSERT, UPDATE, DELETE, MERGE), both read / write use-cases (you > mustn't be able to write a record that you won't be able to read, i.e. the > SQL view WITH CHECK OPTION semantics) > - Subqueries (scalar, derived tables, set operations like UNION, etc.) > - CTE > - Queries where your clientId isn't projected > - Queries where your clientId is aliased > - Implicit join > - Policies on parent tables should affect child tables as well > - And probably a lot more, all of which the policies will do for you > > > jOOQ has around 80 integration tests for policies covering this > functionality > > >> I will keep the Policy Feature in my mind, for a reason in my company to >> get a commercial license someday! >> > > It'll pay off in no time compared to the time spent maintaining your > ExecuteListener ;-). All commercial features like this are available > already in the jOOQ Express Edition. > > >> one thing would be helpful for the policy feature in my opinion: >> - having all Tables generated by the Codegen present when the Policy is >> set, to automatically generate the list of all tables that contain a >> "tenantId", to not need to write them manually >> - criterias depending on the existence of columns in a table (add >> criteria x, if table contains field "y") >> > > These are interesting ideas to keep in mind in the long run: > https://github.com/jOOQ/jOOQ/issues/16514 > > I won't rush a solution here, waiting for more requirements. I'm not > convinced it's necessary. You have a generated Schema or even Catalog > instance, which you can query for all its tables via Schema::getTables, and > then Table::fields to look up all the qualified TENANT_ID fields in your > schema (including cases where the naming convention isn't followed, or > whatever other kind of policy pattern emerges in your case). So, your > generic PolicyProvider seems only take 3-4 lines of code, IMO? > > -- 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 jooq-user+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f0ddec30-1876-4944-88b3-1cd61b0352d2n%40googlegroups.com.
