support for schema , ofbiz database , ofbiz olap database specification in tenant_data_source

[Rajesh Mallah]

Hi ,

I felt the need for using a specific schema of an existing databases for
holding tenant data.
I eventually achieved the objective by updating the jdb_uri column of the
tenant_data_source

Currently the command  for creating new Tenant is :

./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


This 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 i used to
work-around and deal with this situation. PgSQL 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.

[Paul Foxworthy]

Hi Rajesh,

Schemas are not available in all databases. In MySQL/MariaDB, schemas and
databases are the same thing, or if you like, a database has exactly one
schema. So any work to take advantage of schemas in other DBMSes should not
break in those that don't support them.

Even if you did use schemas where possible, e.g. in Postgres, in a very
large situation with many tenants, might you want to partition so a given
database has some maximum number of tenants? So you might not escape
cross-database joins altogether.

Cheers

Paul Foxworthy


On 13 March 2018 at 03:41, Rajesh Mallah <[hidden email]> wrote:

> Hi ,
>
> I felt the need for using a specific schema of an existing databases for
> holding tenant data.
> I eventually achieved the objective by updating the jdb_uri column of the
> tenant_data_source
>
> Currently the command  for creating new Tenant is :
>
> ./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
>
>
> This 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 i used to
> work-around and deal with this situation. PgSQL 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.
>

--
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: [phone]
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]

[Rajesh Mallah]

Hi Paul ,

Thanks for the reply.

I was not suggesting to use schemas  in general for for accommodating
tenants.
Each tenant can reside in its own  dedicated DB.

When we use OFBiz as a part of some application that has other relational
data
as well, then schema partitioning comes handy.

Eg, The OFBiz data lies in its own schema say 'ofbiz'  and the other
application
data lies in another schema say 'general' or 'app' etc. it makes utilizing
ofbiz.*  and
general.* tables easier. It allows close integration of applications with
OFBiz.

As far as other DBs are concerned that do not have same notion of schema,
the feature can be done on an optional basis (ie controlled by the args) ,
 i guess the current form of entityengine.xml is already doing it i.e,
handling
diverse DBs and supporting (Pg) schema at the same time.

Also the DBname specification is currently embedded in the jdbc_uri , if we
can have
an option for specifying / overriding the 2 kinds of DBs (org.apache.ofbiz
and
org.apache.ofbiz.olap) while creating a tenant it shall allow to use
existing DBs.

This shall be very handy for migrations /  consolidation of DBs in
enterprise
environments.

regds
mallah.


On Tue, Mar 13, 2018 at 6:25 AM, Paul Foxworthy <[hidden email]> wrote:

> Hi Rajesh,
>
> Schemas are not available in all databases. In MySQL/MariaDB, schemas and
> databases are the same thing, or if you like, a database has exactly one
> schema. So any work to take advantage of schemas in other DBMSes should not
> break in those that don't support them.
>
> Even if you did use schemas where possible, e.g. in Postgres, in a very
> large situation with many tenants, might you want to partition so a given
> database has some maximum number of tenants? So you might not escape
> cross-database joins altogether.
>
> Cheers
>
> Paul Foxworthy
>
>
> On 13 March 2018 at 03:41, Rajesh Mallah <[hidden email]> wrote:
>
> > Hi ,
> >
> > I felt the need for using a specific schema of an existing databases for
> > holding tenant data.
> > I eventually achieved the objective by updating the jdb_uri column of the
> > tenant_data_source
> >
> > Currently the command  for creating new Tenant is :
> >
> > ./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
> >
> >
> > This 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 i used to
> > work-around and deal with this situation. PgSQL 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.
> >
>
>
>
> --
> Coherent Software Australia Pty Ltd
> PO Box 2773
> Cheltenham Vic 3192
> Australia
>
> Phone: [phone]
> Web: http://www.coherentsoftware.com.au/
> Email: [hidden email]
>

[Paul Foxworthy]

On 13 March 2018 at 13:12, Rajesh Mallah <[hidden email]> wrote:

> I was not suggesting to use schemas  in general for for accommodating
> tenants.
> Each tenant can reside in its own dedicated DB.
>
> When we use OFBiz as a part of some application that has other relational
> data as well, then schema partitioning comes handy.
>
> Eg, The OFBiz data lies in its own schema say 'ofbiz'  and the other
> application data lies in another schema say 'general' or 'app' etc. it
> makes utilizing
> ofbiz.*  and general.* tables easier. It allows close integration of
> applications with
> OFBiz.
>
> As far as other DBs are concerned that do not have same notion of schema,
> the feature can be done on an optional basis (ie controlled by the args) ,
>  i guess the current form of entityengine.xml is already doing it i.e,
> handling diverse DBs and supporting (Pg) schema at the same time.
>
> Also the DBname specification is currently embedded in the jdbc_uri , if we
> can have an option for specifying / overriding the 2 kinds of DBs
> (org.apache.ofbiz
> and org.apache.ofbiz.olap) while creating a tenant it shall allow to use
> existing DBs.
>
> This shall be very handy for migrations /  consolidation of DBs in
> enterprise environments.
>

Hi Rajesh,

OK, thanks.

I agree it would be useful to have the database name as a separate logical
thing rather than embedded in the URI.

Integrating two applications and two schemas within one database might be
the best. But managing permissions gets tricky - each application would
have its own security management, and yet requires some access to the other
application's data, even if read-only. It would be difficult to scale one
application independently of the other using a cluster or some other
technique. Have you considered using XML-RPC services instead?

Cheers

Paul Foxworthy

--
Coherent Software Australia Pty Ltd
PO Box 2773
Cheltenham Vic 3192
Australia

Phone: [phone]
Web: http://www.coherentsoftware.com.au/
Email: [hidden email]

[Rajesh Mallah]

Hi Paul ,

Thanks for attention and considering the proposal of having feature for
database name specification for tenantCreation.

Yes I use XML-RPC thoroughly as i mostly use OFBiz as a framework
for generic data models rather than the OOTB. In such a development model
I feel joining tables and getting data from DB is much more efficient than
implementing DB like activities in application code. (ie. utilising xml-rpc)

Sparingly i require to access the ofbiz entities directly and that is where
having ofbiz entities in a schema of existing database comes handy.

Its true that scaling requirements of the two applications can be different
and tying them into same DB may lead to redundant allocations.

But i still feel having flexibility shall be a boon as we never know how
creative people get with their setups!. OFBiz is already a very flexible
architecture for that matter.

I guess the default behavior of the command can be left like that and
for those who need more flexibility there can be fine grained options
for overriding the default values.


regds
mallah.

On Tue, Mar 13, 2018 at 7:59 AM, Paul Foxworthy <[hidden email]> wrote:

> On 13 March 2018 at 13:12, Rajesh Mallah <[hidden email]> wrote:
>
>
> > I was not suggesting to use schemas  in general for for accommodating
> > tenants.
> > Each tenant can reside in its own dedicated DB.
> >
> > When we use OFBiz as a part of some application that has other relational
> > data as well, then schema partitioning comes handy.
> >
> > Eg, The OFBiz data lies in its own schema say 'ofbiz'  and the other
> > application data lies in another schema say 'general' or 'app' etc. it
> > makes utilizing
> > ofbiz.*  and general.* tables easier. It allows close integration of
> > applications with
> > OFBiz.
> >
> > As far as other DBs are concerned that do not have same notion of schema,
> > the feature can be done on an optional basis (ie controlled by the args)
> ,
> >  i guess the current form of entityengine.xml is already doing it i.e,
> > handling diverse DBs and supporting (Pg) schema at the same time.
> >
> > Also the DBname specification is currently embedded in the jdbc_uri , if
> we
> > can have an option for specifying / overriding the 2 kinds of DBs
> > (org.apache.ofbiz
> > and org.apache.ofbiz.olap) while creating a tenant it shall allow to use
> > existing DBs.
> >
> > This shall be very handy for migrations /  consolidation of DBs in
> > enterprise environments.
> >
>
> Hi Rajesh,
>
> OK, thanks.
>
> I agree it would be useful to have the database name as a separate logical
> thing rather than embedded in the URI.
>
> Integrating two applications and two schemas within one database might be
> the best. But managing permissions gets tricky - each application would
> have its own security management, and yet requires some access to the other
> application's data, even if read-only. It would be difficult to scale one
> application independently of the other using a cluster or some other
> technique. Have you considered using XML-RPC services instead?
>
> Cheers
>
> Paul Foxworthy
>
> --
> Coherent Software Australia Pty Ltd
> PO Box 2773
> Cheltenham Vic 3192
> Australia
>
> Phone: [phone]
> Web: http://www.coherentsoftware.com.au/
> Email: [hidden email]
>

[Shi Jinghai-3]

Cannot agree more.

And distributed DB such as CockroachDB can be used to get PB level storage.


-----邮件原件-----
发件人: Rajesh Mallah [mailto:[hidden email]]
发送时间: 2018年3月13日 11:33
收件人: [hidden email]
主题: Re: support for schema , ofbiz database , ofbiz olap database specification in tenant_data_source

Hi Paul ,

Thanks for attention and considering the proposal of having feature for database name specification for tenantCreation.

Yes I use XML-RPC thoroughly as i mostly use OFBiz as a framework for generic data models rather than the OOTB. In such a development model I feel joining tables and getting data from DB is much more efficient than implementing DB like activities in application code. (ie. utilising xml-rpc)

Sparingly i require to access the ofbiz entities directly and that is where having ofbiz entities in a schema of existing database comes handy.

Its true that scaling requirements of the two applications can be different and tying them into same DB may lead to redundant allocations.

But i still feel having flexibility shall be a boon as we never know how creative people get with their setups!. OFBiz is already a very flexible architecture for that matter.

I guess the default behavior of the command can be left like that and for those who need more flexibility there can be fine grained options for overriding the default values.


regds
mallah.

On Tue, Mar 13, 2018 at 7:59 AM, Paul Foxworthy <[hidden email]> wrote:

> On 13 March 2018 at 13:12, Rajesh Mallah <[hidden email]> wrote:
>
>
> > I was not suggesting to use schemas  in general for for
> > accommodating tenants.
> > Each tenant can reside in its own dedicated DB.
> >
> > When we use OFBiz as a part of some application that has other
> > relational data as well, then schema partitioning comes handy.
> >
> > Eg, The OFBiz data lies in its own schema say 'ofbiz'  and the other
> > application data lies in another schema say 'general' or 'app' etc.
> > it makes utilizing
> > ofbiz.*  and general.* tables easier. It allows close integration of
> > applications with OFBiz.
> >
> > As far as other DBs are concerned that do not have same notion of
> > schema, the feature can be done on an optional basis (ie controlled
> > by the args)
> ,
> >  i guess the current form of entityengine.xml is already doing it
> > i.e, handling diverse DBs and supporting (Pg) schema at the same time.
> >
> > Also the DBname specification is currently embedded in the jdbc_uri
> > , if
> we
> > can have an option for specifying / overriding the 2 kinds of DBs
> > (org.apache.ofbiz and org.apache.ofbiz.olap) while creating a tenant
> > it shall allow to use existing DBs.
> >
> > This shall be very handy for migrations /  consolidation of DBs in
> > enterprise environments.
> >
>
> Hi Rajesh,
>
> OK, thanks.
>
> I agree it would be useful to have the database name as a separate
> logical thing rather than embedded in the URI.
>
> Integrating two applications and two schemas within one database might
> be the best. But managing permissions gets tricky - each application
> would have its own security management, and yet requires some access
> to the other application's data, even if read-only. It would be
> difficult to scale one application independently of the other using a
> cluster or some other technique. Have you considered using XML-RPC services instead?
>
> Cheers
>
> Paul Foxworthy
>
> --
> Coherent Software Australia Pty Ltd
> PO Box 2773
> Cheltenham Vic 3192
> Australia
>
> Phone: [phone]
> Web: http://www.coherentsoftware.com.au/
> Email: [hidden email]
>