(Sorry, this may be a bit off-topic. But I think it’s related to PL design.)

Something has been bugging me for quite some time.

SQL is powerful. It’s declarative. Complex data-access algorithms can be expressed very concisely. But its syntax is monolithic. A SQL statement cuts across multiple tables (or domain objects as in OOA&D). When I examine many SQL statements at work, many of them share similar patterns: Certain inner joins or where clauses are repeated in many places to achieve the same filtering rules. I can’t see an easy way to abstract these patterns out, to reuse snipplets of the statements. (like mixins or traits?) I can’t see an easy way to discompose complex SQLs or to compose complex SQL statements out of smaller building blocks in manageable ways.

From the OOP point of view, data access should be hidden in an OR layer and everything should be done via methods on the domain objects. But that’s not feasible in many applications. Many of the business processes implemented by the software my company develops are batch-oriented and they already take hours to run. If I insist on having everything going through the OR layer and take away the power of optimized SQLs, the software is simply not usable.

What other alternatives are there? And, is there any research on modularizing SQL or on a modularizable data-acc