JOINs across different DBs

[Jad El Omeiri]

Hi all,

We are facing a problem at the company where I work. let me describe, and please any help would be highly appreciated.

Requirement:

The company has an old really complicated Database (MS) that has all the data needed for running the company. As well as it has a new Database (postgres) that is for Ofbiz which is still being worked on.
Now what is needed is to be able to do JOINs across these two different DBs, from within Ofbiz using the Entity Engine.

Problem:

When choosing Ofbiz, they actually thought that OOTB Ofbiz could deal with JOINing different DBs.
And I think I just found out from this old post: http://ofbiz.135035.n4.nabble.com/ViewEntity-across-multiple-databases-td152683.html  that "All member-entities in a view-entity must be in the same database, but not necessarily in the same entity group."
So What I have been telling is that OOTB with Ofbiz it is not possible to make JOINS across multiple DBs. Am I totally correct?

Solution:

Now the solutions we were thinking about at first and that were refused:
a) moving the data which is in MS DB into Ofbiz (postgres)   ==> not doable because of the complexity of the DB & and all the references being made...
b) copy the data from MS to postgres (which means keeping 2 copies of MS DB)  ==> not to be considered because of impracticality and bad for the business flow (sync problems...)

Now how I am currently thinking to solve the problem:
1) edit some of the Entity Engine code so that it can do the JOINS as needed. Here I am also having this http://ofbiz.135035.n4.nabble.com/Use-of-external-database-for-generate-product-pages-td4637770.html  post as a reference. Jacques Le Roux is actually advising to:
"Actually if you look at the entityengine.xml file which defines delegators (access to DB) you will see that OFBiz uses already 3 different DB OOTB
You can follow this way and add your own group-map, of course you will need to add custom code. Have a look into the bi component for instance... "
But I actually think the problem posted there is not the same as ours (difference is in the need to have JOINs)
2) write separate java code that can be integrated into Ofbiz as a service for example. In that code actually do the queries as you would normally do in java. I think of this post as a reference for this solution:
http://ofbiz.135035.n4.nabble.com/Help-Connecting-to-2nd-External-Database-td149600.html#a149601
3) use UnityJDBC http://www.unityjdbc.com/ that could make the 2 DBs look as only one. But the problems here is that 1st of all solution would not be free. 2nd I am still not sure, if just having UnityJDBC and using it instead of the jdbc driver, would make it actually work correctly or would result in any problems with Ofbiz.

Please help me or send me in the best direction, as my company is considering to migrate from Ofbiz if no real solution is found.

Thanks,

Jad



 

[Jad El Omeiri]

Note that how our DBA is dealing with the problem is by LINKing the 2 DBs with odbc... But that obviously is being done outside Ofbiz!

[UnityJDBC]

UnityJDBC is transparent to your Java application including Ofbiz. Queries are done using standard SQL including joins across databases.

The setup involves configuring JDBC connections to your databases (in this case MS and Postgres). This setup produces an XML file storing the connection information. Then, the UnityJDBC URL is jdbc:unity://sources.xml where sources.xml is the path to the configuration file. The UnityJDBC installation package has a SourceBuilder application to make this configuration easy.

Once the configuration is done, a join across two databases is as simple as:

SELECT *
FROM database1.table1 INNER JOIN database2.table2 ON ...

I would start by downloading UnityJDBC and testing connections to both databases. The support team can also help you get started with setting up UnityJDBC with Ofbiz.

--
UnityJDBC Support

[Jad El Omeiri]

Thank you UnityJDBC for your reply.
looks like this is the solution I will be opting for.
I will be soon giving my feedback.

note: did anyone have any experience with Ofbiz/UnityJDBC before? it would probably help.

[Christian Carlow-OFBizzer]

Thanks Jad,

I'm looking for any open-source equivalent  to UnityJDBC but there
doesn't seem to be much out there.  Regardless of using such a tool, I
think the OFBiz entity engine will issues joining the external db due to
incompatible primary keys in the external db. I think view-entities
require joins to be on primary keys.  In order for the external db to be
joined in my case it would have to be done on non-primary key fields.  
So I think the entity engine would have to be changed to allow such a
tool to be employed within OFBiz, but the design seems deliberate and
correct and unlikely to change from requiring primary key joins.  
Importing or using the external db tables directly seem to be the main
alternatives.

On 01/14/2014 10:35 AM, Jad El Omeiri wrote:

> Thank you UnityJDBC for your reply.
> looks like this is the solution I will be opting for.
> I will be soon giving my feedback.
>
> note: did anyone have any experience with Ofbiz/UnityJDBC before? it would
> probably help.
>
>
>
> -----
> Jad El Omeiri
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647184.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.

[Jad El Omeiri]

Hi Christian,

As you are saying, there's no real open source equivalent to UnityJDBC. Although it would have been nice to have one, in order to integrate it maybe with Ofbiz in the future.

Concerning view-entities, to be honest, I don't really know why they can't be used on different DBs.

BTW, someone has just now mentioned to me that:
"Postgres now supports Foreign Data Wrappers. You can use regular database queries to access these data sources like regular tables. Might be worth a look."

So as he's saying, you could probably take a look.

Christian Carlow-OFBizzer wrote

Thanks Jad,

I'm looking for any open-source equivalent  to UnityJDBC but there
doesn't seem to be much out there.  Regardless of using such a tool, I
think the OFBiz entity engine will issues joining the external db due to
incompatible primary keys in the external db. I think view-entities
require joins to be on primary keys.  In order for the external db to be
joined in my case it would have to be done on non-primary key fields.  
So I think the entity engine would have to be changed to allow such a
tool to be employed within OFBiz, but the design seems deliberate and
correct and unlikely to change from requiring primary key joins.  
Importing or using the external db tables directly seem to be the main
alternatives.

On 01/14/2014 10:35 AM, Jad El Omeiri wrote:
> Thank you UnityJDBC for your reply.
> looks like this is the solution I will be opting for.
> I will be soon giving my feedback.
>
> note: did anyone have any experience with Ofbiz/UnityJDBC before? it would
> probably help.
>
>
>
> -----
> Jad El Omeiri
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647184.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.

[Christian Carlow-OFBizzer]

Hey Jad,

Concerning view-entities, I mean that in my case I would need the
ability to join the Product.internalName to
externalDbProduct.internalName (just for example).  I don't thing the
entity engine allows this period so it defeats the purpose of attempting
to join the external entity in the first place.  This is the case for
the majority of external entities so it's a viable migration method in
my case.

On 01/16/2014 11:03 AM, Jad El Omeiri wrote:

> Hi Christian,
>
> As you are saying, there's no real open source equivalent to UnityJDBC.
> Although it would have been nice to have one, in order to integrate it maybe
> with Ofbiz in the future.
>
> Concerning view-entities, to be honest, I don't really know why they can't
> be used on different DBs.
>
> BTW, someone has just now mentioned to me that:
> /"Postgres now supports Foreign Data Wrappers. You can use regular database
> queries to access these data sources like regular tables. Might be worth a
> look."/
>
> So as he's saying, you could probably take a look.
>
>
> Christian Carlow-OFBizzer wrote
>> Thanks Jad,
>>
>> I'm looking for any open-source equivalent  to UnityJDBC but there
>> doesn't seem to be much out there.  Regardless of using such a tool, I
>> think the OFBiz entity engine will issues joining the external db due to
>> incompatible primary keys in the external db. I think view-entities
>> require joins to be on primary keys.  In order for the external db to be
>> joined in my case it would have to be done on non-primary key fields.
>> So I think the entity engine would have to be changed to allow such a
>> tool to be employed within OFBiz, but the design seems deliberate and
>> correct and unlikely to change from requiring primary key joins.
>> Importing or using the external db tables directly seem to be the main
>> alternatives.
>>
>> On 01/14/2014 10:35 AM, Jad El Omeiri wrote:
>>> Thank you UnityJDBC for your reply.
>>> looks like this is the solution I will be opting for.
>>> I will be soon giving my feedback.
>>>
>>> note: did anyone have any experience with Ofbiz/UnityJDBC before? it
>>> would
>>> probably help.
>>>
>>>
>>>
>>> -----
>>> Jad El Omeiri
>>> --
>>> View this message in context:
>>> http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647184.html
>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>
>
>
>
> -----
> Jad El Omeiri
> --
> View this message in context: http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647314.html
> Sent from the OFBiz - User mailing list archive at Nabble.com.

[Christian Carlow-OFBizzer]

Sorry,

it's *NOT a viable migration method in my case

On 01/16/2014 11:42 AM, Christian Carlow wrote:

> Hey Jad,
>
> Concerning view-entities, I mean that in my case I would need the
> ability to join the Product.internalName to
> externalDbProduct.internalName (just for example).  I don't thing the
> entity engine allows this period so it defeats the purpose of
> attempting to join the external entity in the first place.  This is
> the case for the majority of external entities so it's a viable
> migration method in my case.
>
> On 01/16/2014 11:03 AM, Jad El Omeiri wrote:
>> Hi Christian,
>>
>> As you are saying, there's no real open source equivalent to UnityJDBC.
>> Although it would have been nice to have one, in order to integrate
>> it maybe
>> with Ofbiz in the future.
>>
>> Concerning view-entities, to be honest, I don't really know why they
>> can't
>> be used on different DBs.
>>
>> BTW, someone has just now mentioned to me that:
>> /"Postgres now supports Foreign Data Wrappers. You can use regular
>> database
>> queries to access these data sources like regular tables. Might be
>> worth a
>> look."/
>>
>> So as he's saying, you could probably take a look.
>>
>>
>> Christian Carlow-OFBizzer wrote
>>> Thanks Jad,
>>>
>>> I'm looking for any open-source equivalent  to UnityJDBC but there
>>> doesn't seem to be much out there.  Regardless of using such a tool, I
>>> think the OFBiz entity engine will issues joining the external db
>>> due to
>>> incompatible primary keys in the external db. I think view-entities
>>> require joins to be on primary keys.  In order for the external db
>>> to be
>>> joined in my case it would have to be done on non-primary key fields.
>>> So I think the entity engine would have to be changed to allow such a
>>> tool to be employed within OFBiz, but the design seems deliberate and
>>> correct and unlikely to change from requiring primary key joins.
>>> Importing or using the external db tables directly seem to be the main
>>> alternatives.
>>>
>>> On 01/14/2014 10:35 AM, Jad El Omeiri wrote:
>>>> Thank you UnityJDBC for your reply.
>>>> looks like this is the solution I will be opting for.
>>>> I will be soon giving my feedback.
>>>>
>>>> note: did anyone have any experience with Ofbiz/UnityJDBC before? it
>>>> would
>>>> probably help.
>>>>
>>>>
>>>>
>>>> -----
>>>> Jad El Omeiri
>>>> --
>>>> View this message in context:
>>>> http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647184.html 
>>>>
>>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>>
>>
>>
>> -----
>> Jad El Omeiri
>> --
>> View this message in context:
>> http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647314.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>

[Jad El Omeiri]

alright now I get what you mean!

Christian Carlow-OFBizzer wrote

Sorry,

it's *NOT a viable migration method in my case

On 01/16/2014 11:42 AM, Christian Carlow wrote:
> Hey Jad,
>
> Concerning view-entities, I mean that in my case I would need the
> ability to join the Product.internalName to
> externalDbProduct.internalName (just for example).  I don't thing the
> entity engine allows this period so it defeats the purpose of
> attempting to join the external entity in the first place.  This is
> the case for the majority of external entities so it's a viable
> migration method in my case.
>
> On 01/16/2014 11:03 AM, Jad El Omeiri wrote:
>> Hi Christian,
>>
>> As you are saying, there's no real open source equivalent to UnityJDBC.
>> Although it would have been nice to have one, in order to integrate
>> it maybe
>> with Ofbiz in the future.
>>
>> Concerning view-entities, to be honest, I don't really know why they
>> can't
>> be used on different DBs.
>>
>> BTW, someone has just now mentioned to me that:
>> /"Postgres now supports Foreign Data Wrappers. You can use regular
>> database
>> queries to access these data sources like regular tables. Might be
>> worth a
>> look."/
>>
>> So as he's saying, you could probably take a look.
>>
>>
>> Christian Carlow-OFBizzer wrote
>>> Thanks Jad,
>>>
>>> I'm looking for any open-source equivalent  to UnityJDBC but there
>>> doesn't seem to be much out there.  Regardless of using such a tool, I
>>> think the OFBiz entity engine will issues joining the external db
>>> due to
>>> incompatible primary keys in the external db. I think view-entities
>>> require joins to be on primary keys.  In order for the external db
>>> to be
>>> joined in my case it would have to be done on non-primary key fields.
>>> So I think the entity engine would have to be changed to allow such a
>>> tool to be employed within OFBiz, but the design seems deliberate and
>>> correct and unlikely to change from requiring primary key joins.
>>> Importing or using the external db tables directly seem to be the main
>>> alternatives.
>>>
>>> On 01/14/2014 10:35 AM, Jad El Omeiri wrote:
>>>> Thank you UnityJDBC for your reply.
>>>> looks like this is the solution I will be opting for.
>>>> I will be soon giving my feedback.
>>>>
>>>> note: did anyone have any experience with Ofbiz/UnityJDBC before? it
>>>> would
>>>> probably help.
>>>>
>>>>
>>>>
>>>> -----
>>>> Jad El Omeiri
>>>> --
>>>> View this message in context:
>>>> http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647184.html 
>>>>
>>>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>>
>>
>>
>>
>> -----
>> Jad El Omeiri
>> --
>> View this message in context:
>> http://ofbiz.135035.n4.nabble.com/JOINs-across-different-DBs-tp4647103p4647314.html
>> Sent from the OFBiz - User mailing list archive at Nabble.com.
>