Thanks Lukas. I want to map schema, but that mapping is conditional on table. Like if table is "INVOICE" I want to map it to schema "SCHEMA_1" (e.g. "SCHEMA_1.INVOICE"), while for table "USER" I want to map it to schema "COMMON" (i.e. "COMMON.USER").
Thanks -Gang On Wednesday, September 15, 2021 at 8:06:24 AM UTC-4 [email protected] wrote: > Hi Gang, > > I'm not sure if I understood you correctly, but if you want to map tables > only, not schemas, just map your schema to itself... > > I hope this helps, > Lukas > > On Wed, Sep 15, 2021 at 1:51 PM 'Gang Luo' via jOOQ User Group < > [email protected]> wrote: > >> 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 >> >> <https://groups.google.com/d/msgid/jooq-user/be941c4c-71eb-4e23-b8e2-b24675ce43can%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- 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/19ace267-b081-4c86-92e4-78c832e9c4e7n%40googlegroups.com.
