[SOLVED] Generate classes from Postgress database with multiple schemas using Cayenne

Generate classes from Postgress database with multiple schemas using Cayenne

I have PostgreSQL database with multiple schema and I'm using Apache Cayenne to generate Java classes. Problem is that cayenne skips foreign keys on tables in different schema. Example:

Table schema_b.booking that references schema_a.my_user:

create table schema_b.booking
(
    id bigserial not null constraint booking_pkey primary key,
    address_id integer not null constraint cde_fk references schema_b.address
    ...,
    created_by integer not null constraint abc_fk references schema_a.my_user
);

Generated Java class looks like:

class Booking {
     private Long id; 
     private Address addressId; //this is OK
     private Integer createdBy; //NOT OK (Integer instead of MyUser)
}

Console log shows this entry for every FK in different schema:

[INFO] Skip relation: 'null.schema_a.my_user.id <- null.schema_b.booking.created_by # 1' because it related to objects from other catalog/schema
[INFO]      relation primary key: 'null.schema_a'
[INFO]        primary key entity: 'null.schema_a'
[INFO]      relation foreign key: 'null.schema_b'
[INFO]        foreign key entity: 'null.schema_b'

The problem is that Booking#createdBy is not MyUser.

I've searched on SO and official documentation, but without success. Is there any way to achieve this? I know that another option is to move all tables into single schema, but that is almost unfeasible for our project.

Solution

You are correct, Cayenne just skips cross-schema relationships. This looks like an obsolete limitation that should be dropped.

If you are doing this only once, you can just add these missing relationships in Cayenne Modeler. If you need to synchronize Cayenne model with your DB periodically, then it's can be harder to overcome. One option is to skip relationship loading altogether (see docs), and create them manually (or with "Infer relationships" tool in Modeler). In this case all other content (tables and columns) should synchronize just fine. Another option is to wait for 4.1.M2 version of Cayenne that I believe will be ready soon (no exact dates though)