openxava / documentation / Multischema applications

This document explains how to create multi-schema applications using OpenXava.
Multi-schema applications work using Hibernate and EJB3 JPA as persistence engines. EJB2 CMP does not support multischema.
If you want a ready-to-use multitenancy application without programming visit the multitenancy section.

Multi-schema applications

A multi-schema application allows you to replicate all table structure of your database in several database schemas. Then each user (or in each session) can work with a different schema, that is with different data.
For example, you can have the data of 3 different companies in the same database server. Your OpenXava application, that is deployed only once in an application sever, can be executed for employees of these 3 companies, but each employee can only access the data of his company. A multi-schema application allows you to implement this. Using several schema in the database is not only for security, but for avoiding having huge database tables; because you can divide the data by companies, years, departments, etc.

Multi-database applications (new in v4.2.2)

Instead of several schemas in the same application you can choose to use several completely different databases. In order to do it follow the instructions below changing DefaultSchema controller by PersistenceUnit and SetDefaultSchemaAction by SetPersistenceUnitAction. Moreover, you have to define several persistence units in persistence.xml, one for each database.

An example

Let's see an example of an OpenXava module that uses multi-schema.
First, we need to have an entity class, as this one:

@Entity
public class Issue {
 
    @Id @Column(length=5) @Required
    private String id;
 
    @Column(length=40) @Required
    private String description;
 
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
 
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
 
}

Or, if you are using the classic OpenXava XML components:

<component name="Issue">
    <entity>
        <property name="id" type="String" key="true"
            size="5" required="true"/>
        <property name="description" type="String"
            size="40" required="true"/>
    </entity>
    <entity-mapping table="ISSUE">
        <property-mapping
            property="id" column="ID"/>
        <property-mapping
            property="description" column="DESCRIPTION"/>
    </entity-mapping>
</component>
 

You can see how we map the component against the table ISSUE, but we don't specify the schema.
Now, we can define the module in application.xml, as following:

<module name="Issues">
    <model name="Issue"/>
    <controller name="Typical"/>
    <controller name="Issues"/>
</module>

Then, we define our custom controller Issues, in the controllers.xml, in this way:

<controller name="Issues">
    <extends controller="DefaultSchema"/>
    <action name="changeToCompanyA" on-init="true"
        class="org.openxava.actions.SetDefaultSchemaAction">
        <set property="newDefaultSchema" value="COMPANYA"/>
        <use-object name="xava_defaultSchema"/>  <!-- Not needed since v4m2 -->
    </action>
    <action name="changeToCompanyB"
        class="org.openxava.actions.SetDefaultSchemaAction">
        <set property="newDefaultSchema" value="COMPANYB"/>
        <use-object name="xava_defaultSchema"/>  <!-- Not needed since v4m2 -->
    </action>
</controller>

And now we have a module that can work against the schema 'COMPANYA' or against the schema 'COMPANYB'. The user only have to click in the corresponding button in order to change in hot the source of the data.

That's all.

How it works

You can use these ready-to-use controllers and actions, but if you know how it works, you can do it yourself, and adapting it to your special requeriments if needed. The key is the class XPersistence, using this class it's possible to change the default schema in runtime:

XPersistence.setDefaultSchema("COMPANYA");

This changes the default schema to 'COMPANYA', but only for the current execution thread.
Now, if you use a session object (see section 7.2 of Reference Guide) and use an action with on-each-request="true" to set the schema associated to the current user as the default schema for the thread of the request, you will have the problem solved.

Let's try to do it.
Define a session object to store the current schema by user:

<object name="xava_defaultSchema" class="java.lang.String" scope="global"/>

This is defined in OpenXava/xava/default-controllers.xml, therefore it's available for you; although you can create your own session object in your own controllers.xml if you prefer so.

Define an action (in your own controller) to be executed before each request, in controllers.xml:

<controller ... >
    <action name="setDefaultSchema" before-each-request="true" hidden="true"
        class="org.openxava.actions.SetDefaultSchemaAction">
        <use-object name="xava_defaultSchema"/>  <!-- Not needed since v4m2 -->
    </action>
    ...
</controller>

(The DefaultSchema controller of OpenXava has this action included)
In this action you only need to have the session object (in this case xava_defaultSchema) and put it as default schema using XPersistence:

public class SetDefaultSchemaAction extends BaseAction {
 
    @Inject  // Since v4m2
    private String defaultSchema;
    private String newDefaultSchema;
 
    public void execute() throws Exception {
        if (newDefaultSchema != null)    defaultSchema = newDefaultSchema;
        XPersistence.setDefaultSchema(defaultSchema);
    }
 
    /**
     * The current default schema used by OpenXava and JPA.
     */
    public String getDefaultSchema() {
        return defaultSchema;
    }
 
    /**
     * The current default schema used by OpenXava and JPA.
     */
    public void setDefaultSchema(String company) {
        this.defaultSchema = company;
    }
 
    /**
     * The new default schema for OpenXava and JPA. <P>
     *
     * This value update the property 'defaultSchema'.
     */
    public String getNewDefaultSchema() {
        return newDefaultSchema;
    }
 
    /**
     * The new default schema for OpenXava and JPA. <P>
     *
     * This value update the property 'defaultSchema'.
     */
    public void setNewDefaultSchema(String newCompany) {
        this.newDefaultSchema = newCompany;
    }
}

Because defaultSchema is injected using <use-object /> (in all OX versions) or @Inject (since v4m2) when we change defaultSchema property we also change the session object xava_defaultSchema.
This action is part of OpenXava core (in org.openxava.actions), you can use it as is, or create your own action using a similar technique.

This techique can be used with XHibernate too (XHibernate was removed in v7.0).

Now you can call to this action (or other alike) when you want to change the current schema for the user.

Switching schema through URL (new in v4m4)

You can also change schema by using its URL, see the details in the How to section.