Won't setting the search path on each client connect suffice? common,
org_N - and the builder uses outputSchemaToDefault
On 9/15/21 6:06 AM, Lukas Eder 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] <mailto:[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
<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]
<mailto:[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]
<mailto:[email protected]>.
To view this discussion on the web visit
https://groups.google.com/d/msgid/jooq-user/CAB4ELO6w043M4h8pjHKtghdZN7MBpnhsCbRE8YBks8nEFjqQkA%40mail.gmail.com
<https://groups.google.com/d/msgid/jooq-user/CAB4ELO6w043M4h8pjHKtghdZN7MBpnhsCbRE8YBks8nEFjqQkA%40mail.gmail.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/217c0471-bc88-da56-007a-2c2df5307347%40gmail.com.
