I recently had a similar problem that required setting a default connection
*catalog* (this was on MySQL that does not support schemas). I solved that with
a custom DataSource wrapper per stack [1]. However JDBC connection class does
not have a notion of default schema, so this will not work with schemas,
however maybe you can replace ‘setCatalog’ call in [1] with a quick schema
change statement execution (“set search_path to 'schema’", per [2])?
Having said that, we also have an open Jira to handle it explicitly [3]. It is
on the list of things for 3.2.
Andrus
[1]
class TenantDataSource implements DataSource {
private DataSource delegate;
private String defaultCatalog;
TenantDataSource(DataSource delegate, String defaultCatalog) {
this.delegate = delegate;
this.defaultCatalog = defaultCatalog;
}
@Override
public Connection getConnection() throws SQLException {
Connection c = delegate.getConnection();
c.setCatalog(defaultCatalog);
return c;
}
@Override
public Connection getConnection(String username, String password)
throws SQLException {
throw new UnsupportedOperationException("Unsupported");
}
// other DataSource methods, delegated to the underlying DS
…
}
[2]
http://stackoverflow.com/questions/4168689/is-it-possible-to-specify-schema-when-connecting-to-postgres-with-jdbc
[3] https://issues.apache.org/jira/browse/CAY-1824
On Nov 11, 2013, at 4:25 AM, Emanuele Maiarelli <[email protected]>
wrote:
> I was doing some tests on multitenancy implementations using cayenee 3.1
> beta 2 and postgres , using 1 schema per tenant.
>
> I instancend one ServerRuntime per tennant this way:
>
> public static ServerRuntime createServerRuntime(final String
> tenantSchema) {
>
> ServerRuntime runtime = new ServerRuntime("/cayenne-...xml", new
> Module() {
>
> public void configure(Binder binder) {
>
> binder.bind(DataMapLoader.class).toInstance(new
> TenantMapLoader(tenantSchema));
>
> }
>
> // in a custom module override the schema of all loaded DataMaps
> });
>
> return runtime;
> }
>
> static class TenantMapLoader extends XMLDataMapLoader {
>
> private String tenantSchema;
>
> TenantMapLoader(String tenantSchema) {
> this.tenantSchema = tenantSchema;
> }
>
> public DataMap load(Resource configurationResource) {
> DataMap map = super.load(configurationResource);
> map.setDefaultSchema(tenantSchema);
>
> for (DbEntity e : map.getDbEntities()) {
> e.setSchema(tenantSchema);
>
> // workaround else cayenne would search for postgress
> sequences
> // in public schema.
>
> String
> sequence=tenantSchema.concat(".").concat(e.getPrimaryKeyGenerator().getGeneratorName());
> e.getPrimaryKeyGenerator().setGeneratorName(sequence);
>
> }
>
> return map;
> }
> }
>
> I had to put a workaround for handling sequences in the right schema, else
> cayenne would look in public schema (as performig a query in postgres
> omitting the schema name will end peforming it in public schema).
>
> Is there another way to specify to cayenne which schema must be used for
> sequences?
