Same entity on different database

Support
#1

Good morning,
I have the same entity on different databases. If this entity is in priority database I have to use this one, otherwise I have to use that one in the secondary database. How can do it by using the Entity?

Thank you in advance.

1 Like
#3

Hi @infonuvola2015,

Must say that what you explained looks as very custom behavior; I have never faced such problem before. Hence, easy solution can hardly be found. There are a couple of ways that might work:

  1. Create one MappedSuperClass and subclass to different entities for different data stores. Then manager all operations manually through services returning instances of the MappedSuperClass.
  2. Integrate on the database level, so all entity instances from both DBs present in a single table.

It would be great to understand what problem you are trying to solve. Do you need to enable all CRUD operations over the mentioned entity or Read would be just enough? Are these different databases of the same type? Would logical replication solve the problem?

P.S. I would suggest to avoid such complications, of course if it is possible.

Regards,
Aleksey

#4

Hi Aleksey,
thank you for response.
I try to explain my problem.
Our application is a multi company web application.
In our database schemas there are a lot of tables that may be shared with all company or private.
For example customer table will may be present in main database or in secondary database of single company.
When I try to init entity I must verify if the table exist in the company database or in primary database before to create connection.
I need to enable all CRUD operations on entity.
All Databases are of the same type.
Replication dosn’t resolve my problem.

I think that this is the only way to manage this database schemas.
Have you an axample with MappedSuperClass and how to custom java.sql.DataSource implementation for select connection ?
Thank you for all
Best regards
Ripalta

#5

Hi @infonuvola2015,
I’m not sure if I understood your requirements, but I’ll share some thoughts that may help you find a solution.

Have you considered implementing a single database multitenancy? There is an official addon in the platform. But the standard implementation is that an entity is completely global (tenant independent) or completely tenant (users will only be able to access the records belonging to his own tenant). Given what you said, that is not the case.

This sentence somewhat invalidates the standard multitenancy usage. A possible solution is to use Access Groups/Constraints. Something like that:

({E}.companyId == userSession.user.companyId OR {E}.companyId is null)

Then each user should be able to work with global records (where companyId is null) and with records belonging to his own company.

Hope it helps.

Regards.

1 Like
#6

image

Hi Peterson,
thank you for response, I think that our needs is not implementable with multi-tenancy addon entity.
I attach an schemas of an example of our database.
User 1 and User 2 are be able to read entity of primary db for general table, customer, item, instead store movments is secondary db entity.
User 3 of company 3 has all entity tables on secondary db and read only general tables on primary db.
I hope that I explain correctly my need.
If you have another solution is good for me.
I think that I need to customize connection with implementation of java.sql.DataSource
Thank you for help
Best regards
Ripalta

#7

@infonuvola2015,

Still don’t understand. What is the need for such overcomplicated architecture? Why replication doesn’t solve the problem? Why not replicate common data over separate databases for each company?

You may take the way of hacking JDBC DataSource in some way, but from my point of view, it is a risky way I won’t recommend to follow. There is no standard and widely used (hence, well-tested) way to have instances of the same entity in different databases, moreover have different CRUD permissions depending on data origination.

I would strongly recommend to re-think the architecture because what you explain looks quite weird. Based on my experience and gut feeling it doesn’t sound like something that could work well. Following the KISS principle I would recommend evaluating the following options:

  1. Provide separate isolated installations for each of your users and replicate common data over all installations.
  2. Think about having a single database for all customers, as it was noted by @peterson.br.
  3. Look towards DB sharding solutions, like Citus - this one can help if you need to isolate arbitrary data of different customers.

Regards,
Aleksey

#8

Hi stukalov,
unfortunately our DBMS doesn’t implements the replica action.
we have many customers with little installations with three/four db, so can you say me how to customized the JDBC DataSource. From what I understand this one is unique solution of my problem.
Thank you in advance.
Ripalta.

#9

What is your DBMS?

As for “unique solution” - not sure. What I’m sure about is that generally unique solutions are very expensive to implement in support, so normally the wisest solution is to review your architecture.

Regarding “say me how to customized the JDBC DataSource”, this question goes way beyond CUBA. Just go for a google search.

#10

Hi stukalov,

I can’t do replication because my DBMS is Sql Express, and this operation is not supported.
Thank you for your suggestion about my issue.

R

#11

@infonuvola2015,

Did you google? I can find quite a few solutions.

  1. Replication from one SQL Server Express to another - Stack Overflow
  2. Logical SQL Server data replication 101
  3. SQL Server Replication - SQL Server | Microsoft Learn

Finally, why SQL Express?! Because it is free? There are quite a few Enterprise Grade DBs with no limitations introduced by Express. Moving to a normal enterprise database seems to be better solution in the end. Have a look at PostgreSQL.

Regards,
Aleksey

#13

Hi Aleksey,
I have resolved the problem of replication redesigning the database infrastructure, but I already have the problem about the customers that have n-companies in the n different databases. So can I have to solve this issue without creating different n entities (with a same structure), one for each company.
Thank you in advance for you suggestions.
R