Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question
1  
I think you're on the right track with NoSQL. After all, without SQL, one must implement joins, etc. manually. Blending databases might be a similar problem. –  Randall Cook May 9 '13 at 18:47
    
Another idea: can a database abstraction layer like ODBC help here? I am guessing not, as it might merely provide a consistent interface to different databases, but cannot unify them. –  Randall Cook May 9 '13 at 18:49

1 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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.