Thoughts in the Wilderness

I started using Castle ActiveRecord last week, in an effort to refactor the data access layer of a medium-size application that I have to re-write (or at least rinse, DRY and repeat until it is maintainable).

Firstly, kudos to the Castle team. For the most part, they have made the simple things simple. In case you’re not familiar, Castle ActiveRecord is an API that supports the ActiveRecord data access pattern. Rather than re-invent the wheel, they use the robust (but somewhat complex and under-documented) NHibernate O/R Mapper for their data access.

Anyway, on to the subject at hand – HQL = Hibernate Query Language. I was hesitant to use this at first, because it is after all *text*. I dislike using text-sql in my applications because there is no type-safety, and that limits my ability to change the code. Nevertheless, as part of the refactoring process, I decided to leave some of the SQL there, for now.

I did not want to learn HQL. It seemed a waste – I don’t need to work with multiple database flavors – just with Sql Server. But after many struggles, I eventually did learn it. And I am glad, because I quickly (ok, slowly) realized that HQL addressed my biggest pet peeve regarding normal SQL – it treats relations as first class citizens. This leads to wonderful, easy to read and write queries, almost exactly like I thought they should look (see my previous post for my thoughts on that).

HQL – if you have been avoiding it, consider taking a second look. Its much more than just a way of abstracting the database implementation. It is SQL, the way it should have been.

I’ve been using relational databases for some time now – in fact, its the only type of database I’ve used professionally. I’ve even had a go at writing my own.

Over the years, relational databases have not substantially changed. Sure, they manage themselves a bit better, and there are a few more data types (xml), but basically the way they expose the data is unchanged.

I would like to suggest that this model (exposed via the SQL language) is missing a major piece. Adding this piece would make the SQL language much more approachable, and make databases more self-documenting.

The missing piece is “relationships”. Its hard to believe, but relational database do not treat relationships as first-order objects. How would doing so change things? Well consider the following valid SQL query:

SELECT oi.*
FROM orders o
INNER JOIN orderItems oi ON o.Id = oi.OrderId
WHERE o.Number = 123

Notice how I had to specify the details of the relationship (the INNER JOIN line). Now consider what would happen if SQL had named the the relationship “Items”. To be clear, the following SQL is not valid today:

SELECT orders.Items.*
WHERE Number = 123

The above version is far more approachable. Add some intellisense, and even a non-developer could write it.

The simple fact is that the SQL is in need of a major overhaul, but no-one cares. Developers don’t care, because we have been ignored by the database vendors for so long that we have built whole mini-industries around abstracting the database. Database vendors don’t care, because…well who knows…they live in their own little world. Report tool vendors should probably care, but in my experience, most have a serious lack of imagination.