Suitable algorithm for joining records from multiple databases

Question

I am in the process of writing a library that is used to be able to access data from a database. The library requires the initial definition of all the tables that the application will be using in the form of classes that extend an abstract class within the library.

These classes contain the database connection the table is stored in, the table name, the columns, the indices, and the relationships between tables. The abstract class contains static functions that allow a user to select or insert rows into that particular database.

The select function returns an object that allows a query to be assembled, that provides options for the fields, joins, where, group, having, order, and limit, which culminates in a fetch function that returns an object that contains the resultant records along with save and delete functions.

Now, this works fine when it is working between tables within the same database, when being combined with tables in other database, but I am trying to get it so that I can do something like this:

class tabA extends tableBase {
    public static $database  = 'mySqlDB';
    public static $tablename = 'tableA';
    public static $columns   = array(
        'tableAID'       => 'int(11)',
        'tableAKey'      => 'varchar(128)',
        'tableBRefID'    => 'int(11)',
    );
}

class tabB extends tableBase {
    public static $database  = 'oracleDB';
    public static $tablename = 'tableB';
    public static $columns   = array(
        'tableBID'       => 'int(11)',
        'tableBSpecial'  => 'boolean',
        'tableBValue'    => 'text',
    );
}

tabA::select()->join('tabB', 'LEFT')->where(andCondition(eqCondition(tabA::tableAKey, 'foo'), eqCondition(tabB::tableBSpecial, true)))->fetch()

Where the return of fetch would comprise of rows containing the found columns from the two databases.

The drivers for the databases use a common interface, of which the select function looks like:

function select ($columns, $from, $joins, $where, $group, $having, $order, $limit, $offset)

I am aware that the tableBase class will have to break up the query it will be performing into two separate queries, one to query from tabA, and one from tabB, then join the results in the defined type (in this case, LEFT join). But I am having problems coming up with a suitable algorithm for dividing the query into two separate query blocks, then how to combine the results (satisfying the where condition) in an efficient manor.

My thoughts so far would entail stripping out the columns (excluding fields that are derived from operations between data over the two databases) and conditions that are relevant to each database, using those searches on each database, passing through the results of the main database table results and joining data (discarding rows that don't match the join), (at this point, if there are any derived fields as stated above, they are computed), then finally passing through all the resultant rows to ensure that any remaining where or having conditions are matched, and applying any final orderings.

But to my mind, this doesn't seem the most efficient manor, and creates issues when using limits, unless fetches all the rows, then applies the limit operation on the final result.

So, are there any resources, or information on how such a thing could be accomplished in a better/more efficient/neater way?

ADDITIONAL

Although these are SQL databases, a NoSQL database could work as long as the driver built operates within the confines of the database driver interface. At the moment, there is only one driver that uses PDO, but I am working on building drivers for XML and MongoDB datastores.

Answer 1

Bear with me if this is a simplistic answer, but I don't think I see anything specifically forbidding this in your question.

If you added database specifiers to the table specifiers in your SELECT, then you'd pass the burden of cross-database handling to the database servers. Several RDBMS allow this.

E.g. instead of

USE a; SELECT x FROM test;     
USE b; SELECT x, y FROM test; 
(emulation of JOIN between the two cursors)

you would use

SELECT db2.test.y FROM db2.test JOIN db1.test ON db1.test.x = db2.test.x;

Of course your driver would then be less than "universal", and less (or not at all) parallelizable, and you would not be able to split tables between two different servers, or two different RDBMSes (MySQL and Postgres), or even two different access levels to the same server. But maybe the advantages in development time and debugging garnered this way would be worth these limitations?