Schema mapping at table level

[Gang Luo]

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

[Lukas Eder]

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

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/be941c4c-71eb-4e23-b8e2-b24675ce43can%40googlegroups.com.

[Gang Luo]

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

[Lukas Eder]

Ah, I see now, thanks for the clarification. Indeed, this currently doesn't work if you have all the tables in the same schema at development time. But why not develop with a SCHEMA_X and a COMMON schema instead? Then you'd translate only the SCHEMA_X. Seems cleaner to me, no?

I'm currently working on the new public query object model for jOOQ 3.16: https://github.com/jOOQ/jOOQ/issues/12425. It will allow for more sophisticated query transformation by users. The existing schema mapping functionality will be re-written to use these transformation capabilities. It will be possible to roll your own logic. I don't think it makes sense to fix (and complicate) the existing schema mapping anymore

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/19ace267-b081-4c86-92e4-78c832e9c4e7n%40googlegroups.com.

[Rob Sargent]

Won't setting the search path on each client connect suffice? common, org_N - and the builder  uses outputSchemaToDefault

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6w043M4h8pjHKtghdZN7MBpnhsCbRE8YBks8nEFjqQkA%40mail.gmail.com.