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.

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.

share|improve this question
2  
If you're using .NET, hasn't this problem already been solved by linq and entity framework? If you're not using .NET, check out how c# does things using either Linq or just lambda expressions. –  LachlanB May 23 '13 at 23:37
    
I'm doing this in PHP, also, know how to do it will allow me to enact the same concepts in other languages should I need it. Besides, I would really like to know, as this problem has been going in circles in my head for ages, so I would really like a solution :) –  cgoddard May 23 '13 at 23:38
    
Unless I am missing something, it sounds like you are describing an ORM.. en.wikipedia.org/wiki/Object-relational_mapping What language are you writing this in? –  Zach Leighton May 24 '13 at 0:06
    
Please don't reinvent the wheel. It ts already done. –  user61852 May 24 '13 at 19:49
    
@user61852 not reinventing anything, its for learning, if people dont knows how any of the libraries they use work, there will be no progression, and the libraries will stagnate and never become good. p.s. the wheel is the most reinvented item of all time... –  cgoddard May 25 '13 at 11:09
add comment

3 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
add comment

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).

share|improve this answer
add comment

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.