Designing a SQL-like encapsulation object for programmatic use

Question

In the last few weeks, I have been working on a Data Mapping Library, which has involved lots of research, experimentation, crying, blaming the whiteboard for not being big enough, and more research. But now I have a full idea of what I'm trying to do.

I have decided that before working on any data source adapters, I need to build a SQL expression library. The queries are not assembled with a string of text, but in the form of an instantiated object, containing multiple other objects associated with which part of the query it pertains to.

As such, a SELECT query object will have objects for the columns, table from, joins, where clause, group bys, having clause, order bys, and limit. These objects are then passed to the data source adapter, which will take the input and turn it into a query (or use it in other ways) so that it can fetch the data in a common fashion.

Although this is good enough for simple queries, when it comes to more complex queries, where the join reference, or a condition within the where clause is a nested select query (i.e. SELECT * FROM tblA INNER JOIN (SELECT * FROM tblB WHERE foo = 'bar'), or SELECT * FROM tblA WHERE alice IN (SELECT * FROM tblB WHERE foo = 'bar')), I am having trouble designing an interface where such a thing could be defined, which would later be passed to the adapter that could use it in whatever way it needs to.

So, can anyone propose a design that would allow for such queries to be defined in abstract which could be interpreted into a sql query, or into a nosql programmatic interface function call?

EDIT

The design of the system is such that all the tables and columns are defined as objects (separately, but with named associations for linking). Here is an example of the coding I am hoping to be able to execute:

tblA::select()
    ->where(new inCondition(
                           'tblAfooCol',
                            tblB::select()->where(new isNullCondition('tblBbarCol'))
                           )
           )
    ->fetch();

Now, instead of this being directly translated into SQL (which is very easy), I am trying to plan a way that this can be returned to the data source adapter (internally through the fetch function). So far, the system I have built can handle the commands for a single SELECT query being parsed, but it is when sub queries are used in conjunction with the query that will be passed to the adapter. I am having problems determining how to encapsulate the sub query so the adapter can process it itself. As said before, converting it directly into SQL works great, but if I want to use it with a special adapter that does not use SQL (i.e. an XML or MongoDB adapter), without a function to convert the SQL into commands (or if it is SQL, converting some of the syntax to a valid specification for that rmdbs) that can be used may become very processor intensive (only for certain queries) and cost quite a lot of performance.

Answer 1

If you're doing this because you need it, you should really consider using ActiveRecord, because it does everything that you're trying to do, and more:

http://www.phpactiverecord.org/

(don't reinvent the wheel).

If you're doing this for academic exercise, you could still benefit a ton from looking at the files in ActiveRecord and then using the concepts that you learn from code review.

Answer 2

If you insist of doing it yourself

1. don't
2. look at Hibernate criteria API or JPA criteria API for inspiration.

I don't know PHP, but i would be surprised if there wasn't a lot of libraries for this for PHP

Answer 3

What you currently have looks very much like an ORM. You might look at how ORM's in the established frameworks are implemented.

On the other hand, the existing ORM implementations all (to my knowledge) target databases that support SQL interfaces. If it is your intention to target also NoSQL databases, it might be better to re-think your approach and not start from an SQL-like syntax.

You could start with having a DataCollection (table or join), on which you apply Filters (where), Ordering (order by) and an OutputSpecification (select columns), which then results in a new DataCollection.

This approach can still be converted to SQL without too much trouble (although it will be harder than with your current approach), but it might lend itself better for processing in contexts that don't use SQL (directly).