schema support for tenant data sources

[Rajesh Mallah]

Hi ,


I felt the need for using schema of existing databases for holding tenant
data.

Currently the command  eg:

./gradlew createTenant -PtenantId=tenant001
-PtenantName="My Tenant 001" -PdomainName=tenant001.example.com
-PtenantReaders=seed,seed-initial,ext
-PdbPlatform=P -PdbIp=127.0.0.1
-PdbUser=ofb_tenant001
-PdbPassword=ofbiz@tenant


creates following data sources in tenant_data_source table.

---------+------------------------------------------------
tenant_id             | tenant001
entity_group_name     | org.apache.ofbiz
jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbiz_tenant001
jdbc_username         | ofb_tenant001
jdbc_password         | ofbiz@tenant
 -[ RECORD 2 ]---------+------------------------------------------------
tenant_id             | tenant001
entity_group_name     | org.apache.ofbiz.olap
jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbizolap_tenant001
jdbc_username         | ofb_tenant001
jdbc_password         | ofbiz@tenant

-----------------------------------------------------------------

I feel IF the command and entity TenantDataSource are extended to
allow specification of *database *and *database-schema * it shall
be very useful in certain use cases.


schemas in databases allow a level of compartmentalization between
database and tables . The advantage of having schema over separate databases
is that it allows joining of tables across schemas whereas cross database
joins are not supported well in many databases.

In current use case I had housed the ofbiz entities in a schema of a database
and utilized the  schema-name attribute of <datasource/> element in
entityengine.xml. In the same database other schema was being used to
store non-OFBiz custom entities.

Since current tenant_data_source does not allows specification of schema

it shall be difficult to use that dataset as a part of multi-tenant setup.

Fortunately PostgreSQL supports a feature that shall be the basis of an
work-around to deal with this situation. It allows to set a config
parameter at per user level.

This feature can be exploited set set the search_path of a given user
so that a user "sees" only that schema in DB.

given the fact that <datasource/> allows specification of schema
and DB in entityengine.xml i feel it should be possible.


regds
mallah.

[Shi Jinghai-3]

Amazing idea.

Unfortunately the lovely MariaDB and TiDB doesn't support the pg style schema.

-----邮件原件-----
发件人: Rajesh Mallah [mailto:[hidden email]]
发送时间: 2018年3月12日 16:16
收件人: [hidden email]
主题: schema support for tenant data sources

Hi ,


I felt the need for using schema of existing databases for holding tenant data.

Currently the command  eg:

./gradlew createTenant -PtenantId=tenant001 -PtenantName="My Tenant 001" -PdomainName=tenant001.example.com
-PtenantReaders=seed,seed-initial,ext
-PdbPlatform=P -PdbIp=127.0.0.1
-PdbUser=ofb_tenant001
-PdbPassword=ofbiz@tenant


creates following data sources in tenant_data_source table.

---------+------------------------------------------------
tenant_id             | tenant001
entity_group_name     | org.apache.ofbiz
jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbiz_tenant001
jdbc_username         | ofb_tenant001
jdbc_password         | ofbiz@tenant
 -[ RECORD 2 ]---------+------------------------------------------------
tenant_id             | tenant001
entity_group_name     | org.apache.ofbiz.olap
jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbizolap_tenant001
jdbc_username         | ofb_tenant001
jdbc_password         | ofbiz@tenant

-----------------------------------------------------------------

I feel IF the command and entity TenantDataSource are extended to allow specification of *database *and *database-schema * it shall be very useful in certain use cases.


schemas in databases allow a level of compartmentalization between database and tables . The advantage of having schema over separate databases is that it allows joining of tables across schemas whereas cross database joins are not supported well in many databases.

In current use case I had housed the ofbiz entities in a schema of a database and utilized the  schema-name attribute of <datasource/> element in entityengine.xml. In the same database other schema was being used to store non-OFBiz custom entities.

Since current tenant_data_source does not allows specification of schema

it shall be difficult to use that dataset as a part of multi-tenant setup.

Fortunately PostgreSQL supports a feature that shall be the basis of an work-around to deal with this situation. It allows to set a config parameter at per user level.

This feature can be exploited set set the search_path of a given user so that a user "sees" only that schema in DB.

given the fact that <datasource/> allows specification of schema and DB in entityengine.xml i feel it should be possible.


regds
mallah.

[taher]

This seems to be a topic more appropriate to the development mailing
list since you're suggesting changes to design.

On Mon, Mar 12, 2018 at 5:09 PM, Shi Jinghai <[hidden email]> wrote:

> Amazing idea.
>
> Unfortunately the lovely MariaDB and TiDB doesn't support the pg style schema.
>
> -----邮件原件-----
> 发件人: Rajesh Mallah [mailto:[hidden email]]
> 发送时间: 2018年3月12日 16:16
> 收件人: [hidden email]
> 主题: schema support for tenant data sources
>
> Hi ,
>
>
> I felt the need for using schema of existing databases for holding tenant data.
>
> Currently the command  eg:
>
> ./gradlew createTenant -PtenantId=tenant001 -PtenantName="My Tenant 001" -PdomainName=tenant001.example.com
> -PtenantReaders=seed,seed-initial,ext
> -PdbPlatform=P -PdbIp=127.0.0.1
> -PdbUser=ofb_tenant001
> -PdbPassword=ofbiz@tenant
>
>
> creates following data sources in tenant_data_source table.
>
> ---------+------------------------------------------------
> tenant_id             | tenant001
> entity_group_name     | org.apache.ofbiz
> jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbiz_tenant001
> jdbc_username         | ofb_tenant001
> jdbc_password         | ofbiz@tenant
>  -[ RECORD 2 ]---------+------------------------------------------------
> tenant_id             | tenant001
> entity_group_name     | org.apache.ofbiz.olap
> jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbizolap_tenant001
> jdbc_username         | ofb_tenant001
> jdbc_password         | ofbiz@tenant
>
> -----------------------------------------------------------------
>
> I feel IF the command and entity TenantDataSource are extended to allow specification of *database *and *database-schema * it shall be very useful in certain use cases.
>
>
> schemas in databases allow a level of compartmentalization between database and tables . The advantage of having schema over separate databases is that it allows joining of tables across schemas whereas cross database joins are not supported well in many databases.
>
> In current use case I had housed the ofbiz entities in a schema of a database and utilized the  schema-name attribute of <datasource/> element in entityengine.xml. In the same database other schema was being used to store non-OFBiz custom entities.
>
> Since current tenant_data_source does not allows specification of schema
>
> it shall be difficult to use that dataset as a part of multi-tenant setup.
>
> Fortunately PostgreSQL supports a feature that shall be the basis of an work-around to deal with this situation. It allows to set a config parameter at per user level.
>
> This feature can be exploited set set the search_path of a given user so that a user "sees" only that schema in DB.
>
> given the fact that <datasource/> allows specification of schema and DB in entityengine.xml i feel it should be possible.
>
>
> regds
> mallah.

[Rajesh Mallah]

Hi Taher, Just subscribed and (re)posted there.  regds mallah.

On Mon, Mar 12, 2018 at 9:17 PM, Taher Alkhateeb <[hidden email]
> wrote:

> This seems to be a topic more appropriate to the development mailing
> list since you're suggesting changes to design.
>
> On Mon, Mar 12, 2018 at 5:09 PM, Shi Jinghai <[hidden email]> wrote:
> > Amazing idea.
> >
> > Unfortunately the lovely MariaDB and TiDB doesn't support the pg style
> schema.
> >
> > -----邮件原件-----
> > 发件人: Rajesh Mallah [mailto:[hidden email]]
> > 发送时间: 2018年3月12日 16:16
> > 收件人: [hidden email]
> > 主题: schema support for tenant data sources
> >
> > Hi ,
> >
> >
> > I felt the need for using schema of existing databases for holding
> tenant data.
> >
> > Currently the command  eg:
> >
> > ./gradlew createTenant -PtenantId=tenant001 -PtenantName="My Tenant 001"
> -PdomainName=tenant001.example.com
> > -PtenantReaders=seed,seed-initial,ext
> > -PdbPlatform=P -PdbIp=127.0.0.1
> > -PdbUser=ofb_tenant001
> > -PdbPassword=ofbiz@tenant
> >
> >
> > creates following data sources in tenant_data_source table.
> >
> > ---------+------------------------------------------------
> > tenant_id             | tenant001
> > entity_group_name     | org.apache.ofbiz
> > jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbiz_tenant001
> > jdbc_username         | ofb_tenant001
> > jdbc_password         | ofbiz@tenant
> >  -[ RECORD 2 ]---------+------------------------------------------------
> > tenant_id             | tenant001
> > entity_group_name     | org.apache.ofbiz.olap
> > jdbc_uri              | jdbc:postgresql://127.0.0.1/ofbizolap_tenant001
> > jdbc_username         | ofb_tenant001
> > jdbc_password         | ofbiz@tenant
> >
> > -----------------------------------------------------------------
> >
> > I feel IF the command and entity TenantDataSource are extended to allow
> specification of *database *and *database-schema * it shall be very useful
> in certain use cases.
> >
> >
> > schemas in databases allow a level of compartmentalization between
> database and tables . The advantage of having schema over separate
> databases is that it allows joining of tables across schemas whereas cross
> database joins are not supported well in many databases.
> >
> > In current use case I had housed the ofbiz entities in a schema of a
> database and utilized the  schema-name attribute of <datasource/> element
> in entityengine.xml. In the same database other schema was being used to
> store non-OFBiz custom entities.
> >
> > Since current tenant_data_source does not allows specification of schema
> >
> > it shall be difficult to use that dataset as a part of multi-tenant
> setup.
> >
> > Fortunately PostgreSQL supports a feature that shall be the basis of an
> work-around to deal with this situation. It allows to set a config
> parameter at per user level.
> >
> > This feature can be exploited set set the search_path of a given user so
> that a user "sees" only that schema in DB.
> >
> > given the fact that <datasource/> allows specification of schema and DB
> in entityengine.xml i feel it should be possible.
> >
> >
> > regds
> > mallah.
>