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.