Using same connection pool for multiple schemas

[Amit]

The current implementation of BoneCP creates multiple connection pools and ultimately those many partitions for
each schema the user wants to connect to. For e.g. Assuming I have two schemas restored on an Oracle server, I
would have two bonecp connection pools instead of one. This is done in the getConnection(String username, String
password) method implementation of the BoneCPDataSource class.

Having a common connection pool for multiple schemas would be beneficial in a multitenant environment where a
common application deployment model is used to server multiple tenants. For e.g. If I were to configure a bonecp
pool with 3 partitions for one tenant, I would need 5 pools with 3 partitions each if I had 5 schemas restored
on the same oracle server instead of just one pool with 3 partitions. In scenarios where only one of the tenants
is active at a time, it would save the application server from managing all the pools and in turn save some
resources (i.e. connections) on the app server and database server.

The implementation of this feature would need modifications in the way new connections are added to the pool and
how free connections are retrieved from the pool.
1. Adding new connections
 a. During physical connection creation, the username/password would either be taken from the
BoneCPDataSource configuration (if specified) or from the getConnection(String username, String password)
method.
 b. While adding this connection to the pool, the pool needs to maintain a Map<ConnectionRetrievalInfo,
Collection<ConnectionHandle>). Here ConnectionRetrievalInfo is just a wrapper over two String properties -
username & password. It should implement equals & hashcode using these properties.

2. Retrieving connections from the pool
 a. While getting free connections from the pool, we should retrieve it by comparing
ConnectionRetrievalInfo objects.

For each request with distinct username/password a new connection would be created but the pool would be the
same.

Oracle's Universal Connection Pool (UCP) implements this feature and that's where this idea comes from. Please
let me know your suggestions on this approach.

Thanks.

[Amit (amits-84)]

This is more of a feature request then a bug in bonecp

[Wallace Wadge (wwadge)]

This is already implemented in 0.8.0-snapshot

[Alexey Panteleev (alexey-0)]

FYI:

  In MySQL we work with multiple catalogs/databases. Are those the same as schemas in Oracle?
The simple thing we do is conn.setCatalog(db) right after getting a new connection from BoneCP.
This way we can use one datasource but still work with multiple catalogs.