Has anyone written a higher level query langage (than sql) that generates sql for common tasks, on limited schemas.

Question

Sql is the standard in query languages, however it is sometime a bit verbose. I am currently writing limited query language that will make my common queries quicker to write and with a bit less mental overhead.

If you write a query over a good database schema, essentially you will be always joining over the primary key, foreign key fields so I think it should be unnecessary to have to state them each time.

So a query could look like.

select s.name, region.description from shop s
where monthly_sales.amount > 4000 and s.staff < 10

The relations would be

shop -- many to one -- region,

shop -- one to many -- monthly_sales

The sql that would be eqivilent to would be

select distinct s.name, r.description 
from shop s 
join region r on shop.region_id = region.region_id 
join monthly_sales ms on ms.shop_id = s.shop_id  
where ms.sales.amount > 4000 and s.staff < 10

(the distinct is there as you are joining to a one to many table (monthly_sales) and you are not selecting off fields from that table)

I understand that original query above may be ambiguous for certain schemas i.e if there the two relationship routes between two of the tables. However there are ways around (most) of these especially if you limit the schema allowed. Most possible schema's are not worth considering anyway.

I was just wondering if there any attempts to do something like this? (I have seen most orm solutions to making some queries easier)

EDIT: I actually really like sql. I have used orm solutions and looked at linq. The best I have seen so far is SQLalchemy (for python). However, as far as I have seen they do not offer what I am after.

Answer 1

Hibernate and LinqToSQL do exactly what you want

Answer 2

I believe that any (decent) ORM would be of help here..

Answer 3

Entity SQL is slightly higher level (in places) than Transact SQL. Other than that, HQL, etc. For object-model approaches, LINQ (IQueryable<T>) is much higher level, allowing simple navigation:

var qry = from cust in db.Customers
          select cust.Orders.Sum(o => o.OrderValue);

etc

Answer 4

I think you'd be better off spending your time just writing more SQL and becoming more comfortable with it. Most developers I know have gone through just this progression, where their initial exposure to SQL inspires them to bypass it entirely by writing their own ORM or set of helper classes that auto-generates the SQL for them. Usually they continue adding to it and refining it until it's just as complex (if not more so) than SQL. The results are sometimes fairly comical - I inherited one application that had classes named "And.cs" and "Or.cs", whose main functions were to add the words " AND " and " OR ", respectively, to a string.

SQL is designed to handle a wide variety of complexity. If your application's data design is simple, then the SQL to manipulate that data will be simple as well. It doesn't make much sense to use a different sort of query language for simple things, and then use SQL for the complex things, when SQL can handle both kinds of thing well.

Answer 5

Martin Fowler plumbed a whole load of energy into this and produced the Active Record pattern. I think this is what you're looking for?

Answer 6

Not sure if this falls in what you are looking for but I've been generating SQL dynamically from the definition of the Data Access Objects; the idea is to reflect on the class and by default assume that its name is the table name and all properties are columns. I also have search criteria objects to build the where part. The DAOs may contain lists of other DAO classes and that directs the joins.

Since you asked for something to take care of most of the repetitive SQL, this approach does it. And when it doesn't, I just fall back on handwritten SQL or stored procedures.