Are there OO models to generate SQL?

Question

I have been wondering whether there is any code out there that enables representing SQL in the form of some object tree that can be assembled, modified and then finally rendered to valid SQL?

Off the top of my head it could look something like that...

var stmnt = new Statement();
stmnt
  .AddMaster("Customer")
  .Show("Firstname, "Lastname")
  .AddJoin("Address", "ID", "CustomerID")
  .Show("Street", "City");
stmnt.WhereStatement()
  .AddParameter("Address.City", Op.Equal);

string sql = stmnt.Generate();
// select a.FirstName, a.LastName, b.Street, b.City
// from Customer a
// join Address b on b.CustomerID = a.ID
// where b.City = :p1

This is just an example and the thing out there may work totally different, but yes, I'd love to hear what is out tere in that respect.

UPDATE:

I am aware of the numerous possibilities of using ORM technologies to get my results from the DB, but I was after a model for the SQL itself. I know that the level of abstraction is pretty low, but it could possibly allow a situation where multiple collaborators can work on an SQL statement (multiple joins, multiple wheres) which can then be "rendered" at the end of the build-Phase.

Answer 1

Hibernate has its own Hibernate Query Language (HQL) that represents SQL-like constructs as objects.

Answer 2

In .Net, Linq does pretty much what you're talking about.

Answer 3

If you're still using .NET 2.0 and haven't moved onwards to LINQ, then I would create a base statement class, then create classes which allow for a decorator pattern.

That way you can just keep adding what you need to your base statement.

Answer 4

An OR-Mapper, such as Microsoft's LINQ

Here are some examples:

from c in customers
where c.LastName.StartsWith("A")
select c

//

var q = from c in db.Contact
           where c.DateOfBirth.AddYears(35) > DateTime.Now
           orderby c.DateOfBirth descending
           select c;

Some links to get you started:

• http://www.hookedonlinq.com/LINQtoSQL5MinuteOverview.ashx
• http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx
• http://msdn.microsoft.com/en-us/library/bb386976.aspx

Answer 5

See the above, and I've seen more than one programmer head down this road. (And I've told more than one programmer that I've seen more than one programmer ..., but usually they end up finding out on their own how well it works.)

The difficulty I see is that you are adding substantial complexity without offering much in the way of abstraction. You pretty much need to know what SQL you'll be ending up with anyway.

(At least to the degree that the pattern is as represented in your illustration, where you're specifying the clauses directly. ORMs abstract well beyond that.)

Answer 6

The python package SQLAlchemy has an ORM layer, but it also has an SQL generation layer.

[I realise you tagged this post c# and .net, but I thought you might like to see what else is out there]

Here is some example code:

from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy.sql import select

metadata = MetaData()

# Make a basic customer table.
Customer = Table('Customer',
                 metadata,
                 Column('ID', Integer, primary_key=True),
                 Column('FirstName', String),
                 Column('LastName', String))

# Make a basic address table
Address = Table('Address',
                metadata,
                Column('ID', Integer, primary_key=True),
                Column('City', String),
                Column('Street', String),
                Column('CustomerID', None, ForeignKey('Customer.ID')))


# Generate some sql
stmt = select([Customer.c.FirstName,
               Customer.c.LastName,
               Address.c.Street,
               Address.c.City],
              from_obj=Customer.join(Address),
              whereclause=Address.c.City == 'Wellington')

# Display
print stmt
# output:
SELECT "Customer"."FirstName", "Customer"."LastName", "Address"."Street", "Address"."City" 
FROM "Customer" JOIN "Address" ON "Customer"."ID" = "Address"."CustomerID" 
WHERE "Address"."City" = :City_1

# note that SQLAlchemy picked up the join condition from the foreign key.
# you can specify other join conditions if you want.

Typically, you would execute the statement by using SQLAlchemy to connect to a database. Then you can do:

for row in stmt.execute():
    print 'Name:', row.c.FirstName, row.c.LastName, 'City:', row.c.City

Hope this helps.