Project

General

Profile

Feature #6507

cross-database joins

Added by Eric Faulhaber almost 2 years ago. Updated over 1 year ago.

Status:
New
Priority:
Normal
Assignee:
Ovidiu Maxiniuc
Target version:
-
Start date:
Due date:
% Done:

0%

billable:
No
vendor_id:
GCD

Related issues

Related to Database - Feature #6348: implement support for MariaDB WIP

History

#1 Updated by Eric Faulhaber almost 2 years ago

Some customers have the need to join tables which exist in separate databases in queries outside of FWD, as in:

select
   db1.table1.field1, db2.tableA.fieldA
from
   db1.table1
join
   db2.tableA
on
   db1.table1.field1 = db2.tableA.fieldA
...

The immediate need is for queries outside of FWD, but a solution for this can be useful within FWD as well, to eliminate some instances of CompoundQuery, if the legacy query meets certain conditions (e.g., PRESELECT).

For PostgreSQL, there is the Foreign Data Wrapper (https://www.crunchydata.com/blog/understanding-postgres_fdw) extension", which probably is the best way to do this.

#2 Updated by Eric Faulhaber almost 2 years ago

#3 Updated by Eric Faulhaber almost 2 years ago

Initially, we need to support this for MariaDB, which essentially just means planning what database instances will look like when using MariaDB as the back end. Joining across databases on the same server essentially just looks like the example in #6507-1. Apparently, there is no additional set up needed, like there is with PostgreSQL.

#4 Updated by Greg Shah almost 2 years ago

Is there a reason to implement this for a specific database backend?

#5 Updated by Eric Faulhaber over 1 year ago

  • Assignee set to Ovidiu Maxiniuc

Greg Shah wrote:

Is there a reason to implement this for a specific database backend?

Yes. Some databases (e.g., MariaDB) support the syntax in #6507-1 "out-of-the-box" if the databases are on the same server. Others (e.g., PostgreSQL) do not; from what I understand, you have to install contributor technology that is not part of the base project (e.g., Foreign Data Wrapper: https://www.crunchydata.com/blog/understanding-postgres_fdw) and add configuration, even for database instances in the same server.

Ovidiu, initially, we just have to make sure we don't do anything to preclude hand-written JDBC from joining across databases, as in #6507-1. Is there anything about the current MariaDB support that might cause a problem with this? As I understand it, we just generate DDL for each database, and it is up to the deployment to create and import databases that need to be joined this way on the same server. Is that correct?

Ultimately, we will want to integrate direct support for this in FWD, though I'm not exactly sure yet what that needs to look like.

Also available in: Atom PDF