Hi folks, I am working on a multi-tenant system that requires splitting schemas for some types of data and be able to map dynamically to the right schema based on the current context using JOOQ.
For example, there is a table "USER" that stored all the user profiles, and a "INVOICE" storing the invoice data. This is a multi-tenant system so users from different clients/organizations will share the same database. For management purpose, we want all users from any clients/organizations to access to "USER" table in the same schema, while have one schema for each client/organization for the "INVOICE" table, such that User1 from Org1 will have the user info in "COMMON.USER" and invoice data in "ORG_1.INVOICE", while User2 from Org2 will have the user info also in "COMMON.USER" but invoice data in "ORG_2.INVOICE". We are looking for a way to dynamically map to the right schema based on 1. what client/org it is and 2. what table is being access. The schema mapping capability offered by JOOQ right now doesn't take into account table such that I cannot seem to do such schema mapping conditional on table. Does anyone know a quick workarounds for this use case? I would also like to suggest a small feature to make this case easier. The relevant mapping is done today at https://github.com/jOOQ/jOOQ/blob/main/jOOQ/src/main/java/org/jooq/impl/TableImpl.java#L344-L358. That can be summarized into following pseudo code *Schema mappedSchema = mapSchema(schema) * *ctx.visit(mappedSchema.getName()) * *ctx.visit(".") * *Table mappedTable = mapTable(table) * *ctx.visit(mappedTable.getUnqualifiedName())* I would like to suggest adding additional config to indicate if we want to do the schema mapping at the table level, and if so, we skip the schema (only) mapping and using the fully qualified name from the mapped table so to include the (mapped) schema from it. *if ( ! configuration.mapSchemaAtTableLevel()) { * * Schema mappedSchema = mapSchema(schema) * * ctx.visit(mappedSchema.getName()) * * ctx.visit(".") * *} * *Table mappedTable = mapTable(table) * *if (configuration.mapSchemaAtTableLevel()) { * * ctx.visit(mappedTable.getSchema().getName()) * * ctx.visit(".") * *} * *ctx.visit(mappedTable.getUnqualifiedName())* Would appreciate any help or comments on any immediate worlaround to support our use case, and the suggested feature above. Thanks. -Gang -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/be941c4c-71eb-4e23-b8e2-b24675ce43can%40googlegroups.com.
