Using the same generated classes against multiple database schemas

[Espen Strømsnes]

Hi Lukas,

I have been playing a bit with the framework, and I was wondering how do
we support using the same generated classes against multiple database
schemas.

A typical use case (at least for me :-)) is to have the exact same
tables in several database schemas (1 schema for each customer/system/etc).

I want to be able to define the schema at runtime and not be dependent
on having to generate (the exact same classes) for multiple database
schemas.

One use case for this is to have a web application where the user can
choose which database schema to use.

I know of other frameworks where for instance the current schema is
stored in a ThreadLocal object.

Regards,
Espen

[Lukas Eder]

Espen!

I should remove your committing privileges for proposing ThreadLocal as a solution ;-)

Seriously, that's good input. I've had a similar question, where the database is stored on the server using MySQL and on client devices using H2 (I'm glad you already implemented the H2 integration). In that case, some but not all schema objects are the same. That would mean that jOOQ should be able to transform an entity MySQL.A into H2.A, provided they can be considered equivalent. Since they will be transporting data using webservices, I thought XML serialisation/deserialisation might be a good idea: https://sourceforge.net/apps/trac/jooq/ticket/61

Your use case is slightly different, though. I assume that you're installing the same application in the same database several times. You are right, the correct way to do this would be to have the schema configured in the Factory rather than in the generated code. It's not that simple, though. Another use case is an application that runs against several schemas and needs fully qualified elements in the same query from the same Factory.

What other use cases could we think of?

Cheers
Lukas

2010/12/21 Espen Strømsnes <estro...@gmail.com>

[Espen Strømsnes]

After having thought of the ThreadLocal approach I totally agree with you!

When it comes to my use case I have only 1 instance of the application. The database has a number of identical schemas. This is typically done to separate data for clients in their own schema. At runtime the user selects which schema to work with.

Using the same schema-setup another scenario may be as you say to access data from several of these schemas from the same Factory. This can typically be for reporting purposes.

At the moment, I don't have any other use cases, but I'll give it a thought and get back to you.

Regards,
Espen

2010/12/21 Lukas Eder <lukas...@gmail.com>

[Lukas Eder]

OK, let's think about this for a while... :)
We shouldn't introduce workarounds for very concrete use cases. I'm sure a general solution will pop up eventually...

2010/12/21 Espen Strømsnes <estro...@gmail.com>

[Espen Strømsnes]

I totally agree with you in that we should think how to implement this in the best possible way.

Just to clearify on the ThreadLocal issue; I didn't mean that we should use ThreadLocal in jOOQ, but that client code using jOOQ could use ThreadLocal to distinguish which schema to currently use when using the factory.

The use case I have described may be concrete but I think it's a very significant feature to support.

[Lukas Eder]

I was just kidding about ThreadLocal. I know what you mean. :)
Client code can store as many schemata as they need in any storage, including ThreadLocal. It would not change anything to jOOQ.

Let's summarise:

1. Schemata may be DISTRIBUTED. This is the case, when several "database systems", "database instances" or "schemata" are interfaced by a single application / system.
   
2. Schemata may be EQUIVALENT, with exact matches between entities of each schema. This is done, when an database has several "instances", "installations", "configurations".
   
3. Schemata may be IDENTICAL. This is typically the case in a formal release process, where we a database has several "environments", such as "development", "integration", "acceptance test", "production" on different machines. In that case, all entities AND the schema names match, and we don't have a problem.
   

The difference between 1) and 2) is subtle, though. The main focus of these concepts is

1. DISTRIBUTED schemata have logical "parts" implemented as database schemata, which correspond to today's org.jooq.Schema type's generated implementation classes. It is important to be able to distinguish all of the involved schema "parts", as they can be contained in a single query.
2. EQUIVALENT schemata have physical "copies" or "instances", also implemented as database schemata, with no correspondence in today's jOOQ code. For every single query, each implementation of every involved logical "part" should be configured to the actual "instance" value. Several schema "instances" cannot be contained in a single query.

My proposition to implementing support for EQUIVALENT schemata is this:

• You have schemata S_MASTER, S_A and S_B. S_MASTER is the development schema you use for code generation. It is probably not deployed. S_A and S_B are "copies" or "instances" of S_MASTER. Table T is installed in all schemata. As it is generated from S_MASTER, its generated code will reference S_MASTER and render S_MASTER.T by default.
• S_MASTER is optional, you could just have S_A and S_B, in case of which one of them will be chosen as "S_MASTER"
  
• When connecting to S_A (in your case: because a client selected it), then you will have to instanciate a new Factory_S_A with a configured mapping S_MASTER => S_A, where S_A can be provided as java.lang.String or org.jooq.Schema (not generated). From now on, Factory_S_A will create org.jooq.impl objects rendering schema S_A instead of S_MASTER, e.g. S_A.T

I hope this was not too much detail, but I need to get these things formally straight...
What do you think? That should be a good implemenation for all use cases. There are no API changes involved (only enhancements to Factory), such that it could be released in a 1.5.x release. And client code has very little effort to configure selection of a schema "instance"

[Espen Strømsnes]

This is  a very good explanation of the various cases. Nice work.

The EQUIVALENT schemata case (with a master development schema and a number of deployment schemata) matches my use case exact!

I agree that the org.jooq.Schema (or java.lang.String) representing the various schemata must not be generated. When developing the application/generating code we're only aware of the development schema.

[Lukas Eder]

Alright. Consider it done: https://sourceforge.net/apps/trac/jooq/ticket/173. Almost :)
I'll release 1.5.2 this year, probably in the last week of December. I'll give this ticket highest priority

2010/12/22 Espen Strømsnes <estro...@gmail.com>

[Lukas Eder]

It's done! It works (almost) fine for me in MySQL, where I run the integration tests in a second schema "test2".
There is a minor flaw with stored procedures / functions: https://sourceforge.net/apps/trac/jooq/ticket/176
Accessing those in an EQUIVALENT schema cannot be achieved with today's design. I'll fix that for 1.5.2 as well.

So would you mind "beta-testing" that enhancement for me? :)

Cheers
Lukas

2010/12/22 Lukas Eder <lukas...@gmail.com>

[estromsnes]

I have done some initial testing of the EQUIVALENT schemata case and
everything looks good so far.

Espen

On Dec 23, 12:02 am, Lukas Eder <lukas.e...@gmail.com> wrote:
> It's done! It works (almost) fine for me in MySQL, where I run the
> integration tests in a second schema "test2".
> There is a minor flaw with stored procedures / functions:https://sourceforge.net/apps/trac/jooq/ticket/176

> Accessing those in an *EQUIVALENT *schema cannot be achieved with today's

> design. I'll fix that for 1.5.2 as well.
>
> So would you mind "beta-testing" that enhancement for me? :)
>
> Cheers
> Lukas
>

> 2010/12/22 Lukas Eder <lukas.e...@gmail.com>

>
>
>
>
>
>
>
> > Alright. Consider it done:
> >https://sourceforge.net/apps/trac/jooq/ticket/173. Almost :)
> > I'll release 1.5.2 this year, probably in the last week of December. I'll
> > give this ticket highest priority
>

> > 2010/12/22 Espen Strømsnes <estroms...@gmail.com>

>
> > This is  a very good explanation of the various cases. Nice work.
>

> >> The *EQUIVALENT* schemata case (with a master development schema and a

> >> number of deployment schemata) matches my use case exact!
>
> >> I agree that the org.jooq.Schema (or java.lang.String) representing the
> >> various schemata must not be generated. When developing the
> >> application/generating code we're only aware of the development schema.
>
> >> Regards,
>
> >> Espen
>

> >> 2010/12/21 Lukas Eder <lukas.e...@gmail.com>

>
> >>> I was just kidding about ThreadLocal. I know what you mean. :)
> >>> Client code can store as many schemata as they need in any storage,
> >>> including ThreadLocal. It would not change anything to jOOQ.
>
> >>> Let's summarise:
>

> >>>    1. Schemata may be *DISTRIBUTED*. This is the case, when several

> >>>    "database systems", "database instances" or "schemata" are interfaced by a
> >>>    single application / system.

> >>>    2. Schemata may be *EQUIVALENT*, with exact matches between entities

> >>>    of each schema. This is done, when an database has several "instances",
> >>>    "installations", "configurations".

> >>>    3. Schemata may be *IDENTICAL*. This is typically the case in a

> >>>    formal release process, where we a database has several "environments", such
> >>>    as "development", "integration", "acceptance test", "production" on
> >>>    different machines. In that case, all entities AND the schema names match,
> >>>    and we don't have a problem.
>
> >>> The difference between 1) and 2) is subtle, though. The main focus of
> >>> these concepts is
>

> >>>    1. *DISTRIBUTED *schemata have logical "parts" implemented as
> >>>    database schemata, which correspond to today's *org.jooq.Schema*type's generated implementation classes. It is important to be able to

> >>>    distinguish all of the involved schema "parts", as they can be contained in
> >>>    a single query.

> >>>    2. *EQUIVALENT *schemata have physical "copies" or "instances", also

> >>>    implemented as database schemata, with no correspondence in today's jOOQ
> >>>    code. For every single query, each implementation of every involved logical
> >>>    "part" should be configured to the actual "instance" value. Several schema
> >>>    "instances" cannot be contained in a single query.
>

> >>> My proposition to implementing support for *EQUIVALENT *schemata is
> >>> this:
>
> >>>    - You have schemata S_MASTER, S_A and S_B. S_MASTER is the

> >>>    development schema you use for code generation. It is probably not deployed.
> >>>    S_A and S_B are "copies" or "instances" of S_MASTER. Table T is installed in
> >>>    all schemata. As it is generated from S_MASTER, its generated code will
> >>>    reference S_MASTER and render S_MASTER.T by default.

> >>>    - S_MASTER is optional, you could just have S_A and S_B, in case of

> >>>    which one of them will be chosen as "S_MASTER"

> >>>    - When connecting to S_A (in your case: because a client selected

> >>>    it), then you will have to instanciate a new Factory_S_A with a configured

> >>>    mapping S_MASTER => S_A, where S_A can be provided as *
> >>>    java.lang.String* or *org.jooq.Schema* (not generated). From now on,
> >>>    Factory_S_A will create *org.jooq.impl* objects rendering schema S_A

> >>>    instead of S_MASTER, e.g. S_A.T
>
> >>> I hope this was not too much detail, but I need to get these things
> >>> formally straight...
> >>> What do you think? That should be a good implemenation for all use cases.
> >>> There are no API changes involved (only enhancements to Factory), such that
> >>> it could be released in a 1.5.x release. And client code has very little
> >>> effort to configure selection of a schema "instance"
>
> >>> Cheers
> >>> Lukas
>

> >>> 2010/12/21 Espen Strømsnes <estroms...@gmail.com>

>
> >>>> I totally agree with you in that we should think how to implement this
> >>>> in the best possible way.
>
> >>>> Just to clearify on the ThreadLocal issue; I didn't mean that we should
> >>>> use ThreadLocal in jOOQ, but that client code using jOOQ could use
> >>>> ThreadLocal to distinguish which schema to currently use when using the
> >>>> factory.
>
> >>>> The use case I have described may be concrete but I think it's a very
> >>>> significant feature to support.
>
> >>>> Espen
>

> >>>> 2010/12/21 Lukas Eder <lukas.e...@gmail.com>

>
> >>>>> OK, let's think about this for a while... :)
> >>>>> We shouldn't introduce workarounds for very concrete use cases. I'm
> >>>>> sure a general solution will pop up eventually...
>

> >>>>> 2010/12/21 Espen Strømsnes <estroms...@gmail.com>

>
> >>>>>> After having thought of the ThreadLocal approach I totally agree with
> >>>>>> you!
>
> >>>>>> When it comes to my use case I have only 1 instance of the
> >>>>>> application. The database has a number of identical schemas. This is
> >>>>>> typically done to separate data for clients in their own schema. At runtime
> >>>>>> the user selects which schema to work with.
>
> >>>>>> Using the same schema-setup another scenario may be as you say to
> >>>>>> access data from several of these schemas from the same Factory. This can
> >>>>>> typically be for reporting purposes.
>
> >>>>>> At the moment, I don't have any other use cases, but I'll give it a
> >>>>>> thought and get back to you.
>
> >>>>>> Regards,
> >>>>>> Espen
>

> >>>>>> 2010/12/21 Lukas Eder <lukas.e...@gmail.com>

>
> >>>>>> Espen!
>
> >>>>>>> I should remove your committing privileges for proposing ThreadLocal
> >>>>>>> as a solution ;-)
>
> >>>>>>> Seriously, that's good input. I've had a similar question, where the
> >>>>>>> database is stored on the server using MySQL and on client devices using H2
> >>>>>>> (I'm glad you already implemented the H2 integration). In that case, some
> >>>>>>> but not all schema objects are the same. That would mean that jOOQ should be
> >>>>>>> able to transform an entity MySQL.A into H2.A, provided they can be
> >>>>>>> considered equivalent. Since they will be transporting data using
> >>>>>>> webservices, I thought XML serialisation/deserialisation might be a good
> >>>>>>> idea:https://sourceforge.net/apps/trac/jooq/ticket/61
>
> >>>>>>> Your use case is slightly different, though. I assume that you're
> >>>>>>> installing the same application in the same database several times. You are
> >>>>>>> right, the correct way to do this would be to have the schema configured in
> >>>>>>> the Factory rather than in the generated code. It's not that simple, though.
> >>>>>>> Another use case is an application that runs against several schemas and
> >>>>>>> needs fully qualified elements in the same query from the same Factory.
>
> >>>>>>> What other use cases could we think of?
>
> >>>>>>> Cheers
> >>>>>>> Lukas
>

> >>>>>>> 2010/12/21 Espen Strømsnes <estroms...@gmail.com>