CODEWITHSUNDEEP
c#.netsqlsemantics
flq
flq

Reputation: 22849

Are there OO models to generate SQL?

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.

Upvotes: 1

Views: 359

Answers (7)

John Fouhy
John Fouhy

Reputation: 42183

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.

Upvotes: 2

TcKs
TcKs

Reputation: 26632

You can try MetaDb. There is some work done. Sample query http://i3.codeplex.com/Project/Download/FileDownload.aspx?ProjectName=metadb&DownloadId=11482

But if you can use .NET 3.5, you can use LINQ.

Upvotes: 1

dkretz
dkretz

Reputation: 37655

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

Upvotes: 2

Andreas Grech
Andreas Grech

Reputation: 107950

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:

Upvotes: 2

Nicholas Mancuso
Nicholas Mancuso

Reputation: 11877

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.

Upvotes: 0

Axelle Ziegler
Axelle Ziegler

Reputation: 2655

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

Upvotes: 0

duffymo
duffymo

Reputation: 308763

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

Upvotes: 5

Related Questions