Atomikos Forum

I'm using Hibernate, Spring and Atomikos. My app provides each user with a temporary database, consisting of tables which are mapped in a Hibernate ORM, in a schema in the database. There are multiple copies of this temp schema for multiple users.

There is a temporary data source, distinct from other data sources and transactional resources. Data access is through distinct temp DAOs referencing the temp data source. I need a way of ensuring that all connections from the temp data source obtained for a user use the temp schema allocated for him. I need a way of intervening wherever connections are allocated, and specifying the schema there.

This problem was discussed 6 years ago in Spring, and a thread-based solution was developed. I think the problem is different here because I'm using distributed transactions. The persistence context is no longer thread-based but JTA based, in the Hibernate config. Likewise, all connections on the temp data source for a given transaction must use the same schema.

Somehow the schema has to be set in the Tx Manager or whatever it uses to get connections, possibly at the start of each transaction, from the application's knowledge of the user. This can probably be automated with AOP once I figure out what is needed.

I'm looking at the Atomikos API and have some tentative ideas, but thought I would ask for advice first.

Also, here is the thread-based Spring discussion. Basically they store the schema name in a ThreadLocal variable; there is a TX-aware data source proxy that sets the schema for each connection on a target data source; that is the actual schema-dependent data source. Am I right that thread-centric won't work here? Thanks.

http://forum.springsource.org/showthread.php?t=10728&highlight=schema+datasource

Actually, I fooled myself. I thought the thread-based solution wasn't working, but it is, at least in my small test. But I am grateful for any comments which might refine this scheme. I'm mainly wary that the jta-based persistence context with Hibernate under XA signals some other context issue with this intervention to rename the schema.

Hi,

Not sure if I understood things completely, but we do have a feature request to allow non-query statements for the testQuery - executed before each connection is borrowed from the pool. I suppose that could serve to set the schema? Or not?

Thanks

That sounds promising. I'm not familiar with the testQuery feature but it seems to ensure that the conn works before returning it for use. There would have to be some way of setting that query in the API obviously. Right now I'm adding an extra stmt when getting a connection, setting the schema. Is that happening now because I'm using the Atomikos conn pool under the covers? In which case I would save a stmt. Or is the testQuery  optional?

Hi,

You can set the testQuery as a property on the AtomikosDataSourceBean instance.

Best

Then I don't think it will work. There are multiple schemas in the data source, and a different "test query" (set schema=temp01, for Derby) must be executed for each connection. Your comment suggests that the TQ is associated with the data source. This is finer-grained.

The SchemaAwareDataSourceProxy solution sets the schema as a connection to the temp data source is obtained. The app sets the schema from its knowledge of the user, as the code executes on the user's behalf, using a method on the SchemaAware DataSourceProxy bean. This schema value is thread-local.

Will code outside the user's thread ever get a conn to the temp data source to do work for the user? If so the schema is the config value for the data source...

Hi,

I am not sure I understood your previous post in this thread. Care to specify a bit more?

Thanks

The "data source" is for my purposes multiple data sources, the same set of tables, repeated in multiple schemas. I point my ORM to a different schema for each user session, to give the user a temp database. So the "test query" cannot be the same for the data source. Its purpose is to set the schema. If I change it on the ds for one user and then change it for another use, further connections for the first user will be wrong.

OK,

Then indeed you would need a proxy (adapter) that does this, or you need a different datasource for each user (not realistic I guess?).

HTH