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.