24.4 Multitenancy

Separate-schema multitenancy is supported by default. That is, a separate database schema will be created for each DataAccessUnit (instanceType or instance). To support shared-table multitenancy:

Enable entity multitenancy in persistence units.
Enable InstanceType multitenancy in system-config.xml
Provide seed.sql files to add multitenant discriminator columns into shared-tables.

Enable Entity Multitenancy

In ORM xml, add <multitenant> element under <mapped-superclass> or <entity> and use "enabled" attribute to enable or disable entity hierarchy multitenancy.

For example, The ORM xml of the System module contains:


<entity-mappings>
	<mapped-superclass class="com.cmobilecom.af.entity.PersistenceEntityBase" >
		<multitenant enabled="#{multitenant.enabled}">
			<!-- Support application-managed multitenancy. -->
			<discriminator-column name="instanceId" type="java.lang.Long"
				value-mapping-property="instance.id"
				tenant-entity="com.cmobilecom.af.module.system.entity.InstanceImpl"
				attribute="instance"/>
		</multitenant>
	</mapped-superclass>
</entity-mappings>

All entity types extends PersistenceEntityBase, and entity multitenancy is inheritable by class hierarchy. So the multitenancy of all entities are controlled by the expression #{multitenant.enabled} that can be resolved by persistence-unit property multitenant.enabled in META-INF/persistence.xml. For example,


	<persistence-unit name="hr" transaction-type="RESOURCE_LOCAL">
		<mapping-file>System/conf/orm.xml</mapping-file>
		<mapping-file>Website/conf/orm.xml</mapping-file>
		<mapping-file>HR/conf/orm.xml</mapping-file>

		<properties>
			<!-- properties to resolve expressions in ORM mappings -->
			<property name="multitenant.enabled" value="true"/>
			<property name="user.multitenant.enabled" value="false"/>
		</properties>
	</persistence-unit>

To disable multitenancy for an entity type when its class hierarchy is enabled for multitenancy, add <multitenant> element for the entity type and set its attribute "enabled" to "false". For example, to disable Employee multitenancy:


	<entity class="Employee" >
		<multitenant enabled="false"/>
	</entity>

The multitenancy of the entity type and all its subclass types will be disabled.

See Cmobilecom JPA developer guide for more details on Multitenancy.

Enable InstanceType Multitenancy

To configure multitenancy for an instance type, add <multitenant> element for the instance type in conf/system-config.xml. For example,


<system-config>

	<instance-type id="hr" name="HR">
       
		<moduleNode module="HR"/>
		<moduleNode module="System"/>
		<persistenceUnit>hr</persistenceUnit>
	
		<multitenant>
			<type>SHARED_TABLE</type>
			<schema>#{db.schemaName}_#{instanceType.id}</schema>
			
			<!-- users: single identity (shared by system and all instances) -->
			<shared-entity>
				<class>com.cmobilecom.af.entity.security.User</class>
				<access>
					<create/>
					<edit/>
					<view/>
					<owner-access/>
					<privacy/>
				</access>
				<shared-with>system</shared-with>
				<param name="paramName" value="paramValue"/>
			</shared-entity>
		</multitenant>	 
	</instance-type>
</system-config>

Each instanceType has its own separate database schema, and instances of an instanceType can share the same schema or have its own separate schema.

Multitenant Type

Multitenant Type can be separate schema or shared table.

SHARED_TABLE: all the instances of the InstanceType share tables in one schema.
SEPARATE_SCHEMA: default. Separate schemas for different instances.

Schema

The schema specifies schema name that supports the following variables:

#{db.schemaName}: system schema name
#{instanceType.id}: instanceType id
#{instance.id}: instance id

Example 1: The system instance uses system schema

	#{db.schemaName}

Example 2: shared table, use one schema for all the instances of an InstanceType

	#{db.schemaName}_#{instanceType.id}

Example 3: separate schemas for different instances of an InstanceType

	#{db.schemaName}_#{instanceType.id}_#{instance.id}

For the system InstanceType, it must be #{db.schemaName}. For a non-system InstanceType, it can contain #{instanceType.id} and/or #{instance.id}.

Shared Entities

The shared-entity for User type in the example above specifies that User entities in the system instance are shared by all the instances of the InstanceType and the InstanceType itself. This achieves the single-user-account to access all DataAccessUnit(s).

If User entities is not shared, each DataAccessUnit will have its own users. In this case, set property "user.multitenant.enabled" to "true" to enable User multitenancy (i.e., disable user entity sharing) in META-INF/persistence.xml.

		<properties>
			<property name="user.multitenant.enabled" value="true"/>
		</properties>

For the system InstanceType, if the User entity is configured as shared-table multitenancy (i.e., the User table is shared with instances of other instanceTypes and each instance has its own users), the installation seed/seed.xml must include the following seed sql file to add "instanceId" column to User table.

	<seedSql>System/db/@DBMS_TYPE@/multitenant/seed-user.sql</seedSql>

Seed Sql For Multitenancy

The discriminator column is instanceId, and its type is the same as entity id.

For example, multitenant seed sql files in the example HR module for MySql and Oracle databases:

     db/mysql/multitenant/seed.sql
     db/oracle/multitenant/seed.sql

MySql database: alter Address and Employee tables:


ALTER TABLE `Address` add `instanceId` bigint(20) NOT NULL;
ALTER TABLE `Address` add INDEX `instanceId` (`instanceId`);

ALTER TABLE `Employee` add `instanceId` bigint(20) NOT NULL;
ALTER TABLE `Employee` drop KEY `nid`;
ALTER TABLE `Employee` add UNIQUE KEY `nid` (`instanceId`, `nid`);

Oracle database: alter Address and Employee tables:


ALTER TABLE Address add instanceId NUMBER(19,0) NOT NULL;
CREATE INDEX IX_HR_ADDR_instanceId on Address (instanceId);

ALTER TABLE Employee add instanceId NUMBER(19,0) NOT NULL;
ALTER TABLE Employee drop CONSTRAINT UK_HR_EMP_nid;
ALTER TABLE Employee add CONSTRAINT UK_HR_EMP_nid UNIQUE (instanceId, nid);

The discriminator column should have index for performance, and needs to be added to unique keys if they are unique in the scope of a tenant. If the discriminator column is in a unique key, it will have index automatically.

← System Configuration→ User AgentFrames / No Frames