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 <mallah.raj...@gmail.com> 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: +61 3 9585 6788 Web: http://www.coherentsoftware.com.au/ Email: i...@coherentsoftware.com.au
