Lambda the Ultimate

Thanks for the compliment...

I must be missing something here...

database access layer

Perhaps we need to be more explicit about what we mean by this expression. It is commonly overloaded to mean both a general framework for database access, and an application-specific library for handling the app's data needs.

Perhaps you even mean something else.

In my experience, the frameworks (e.g. Hibernate) simply pass the complexities off to a "configuration" level, and often hobble what you can do with your DB to a common denominator of functionality.

The best thing we could do to help with this problem would be to define a truly standard data language that all DBs used, though history hasn't been kind to this idea.

I am very thin skinned when I think I am being accused of "Spurious Generality". It just happens so often it gets on my nerves...

I apologize if my post seemed hostile. It was not intended that way, nor was I directing my comments at you individually.

I suppose "Spurious Generality" IS a bit grumpy sounding . I guess it is just my (not-so) inner curmudgeon shining through. ;-)

In my experience, the frameworks (e.g. Hibernate) simply pass the complexities off to a "configuration" level, and often hobble what you can do with your DB to a common denominator of functionality.

I was going to raise Hibernate as a counter-example to Zawodny's claims.

Although it requires a configuration level to define the mapping between language objects and the database schema, the point is that the mapping is defined in one place, and then doesn't need to be dealt with all over the application.

This provides the opposite of what Zawodny is claiming: it reduces complexity and clutter at the application level, it's roughly neutral on application performance, and a net win for developer performance. It generally does improve things. In addition, contrary to Zawodny's claim, it does provide independence from the underlying database, and the degree of independence can be controlled by the developers - it can be a tradeoff, but it's a controllable one.

However, it appears Zawodny is talking about PHP, and no-one is solving the sorts of problems using PHP that are being solved with Java and Hibernate. I can well imagine that the limitations of PHP could make developing a credible database abstraction layer problematic.

The best thing we could do to help with this problem would be to define a truly standard data language that all DBs used, though history hasn't been kind to this idea.

Tools like Hibernate ultimately can't completely eliminate the semantic gap between the programming language and the data query language. Having a better standard data query language won't really help here.

From the perspective of a developer in a given language, a much better solution is to have the data query language be more tightly integrated into the programming language. SchemeQL provides an example of this, but it's still hobbled by having to deal with SQL on the back end, and as a result it isn't really able to rise above SQL in terms of the abstraction power that can be achieved in database queries. So you're still stuck with the basic model of doing a query within the pragmatic limits of the query language, then further processing the results in the programming language, with a sharp line between what can be done easily in each environment.

The problem with the integrated language solution is that it would have to be different for each language, and it would, as Ehud points out, require significant improvements in the the programming languages themselves. Which means short of an amazing stroke of genius and luck, we won't see any movement in this area for a long time to come.

HaskellDB is type safe, flexible, and reasonably efficient. It's not really industrial strength yet (you have to recompile any time you change the database, and error messages aren't terribly clear), but it does do away with the "impedence" mismatch.

I thought about this for a while; my first answer was code generation, and my second was an embeddable query language. That's quite a fork in the road.

One of the problems with code generation (at least the naive solution I had in mind) is that would result in a "flat" API, whereas with that many tables it's likely that there are all sorts of module boundaries that could be introduced; probably multiple layers of modularity, in fact. The way it breaks down is unlikely to be obvious, and there may not be an unequivocally "right" way to do it, so there's (hard) design work to be done.

Even if you can use code generation to magic up a huge array of type-safe data access methods, you still have the problem of how to express the larger-scale design. So code generation moves the problem into the domain of the PL (you've "hidden" the connections, cursors, queries and so on), but doesn't make any of the complexity go away, or even make it significantly more tractable. What's more, there are issues of policy (what kind of cursor or locking to use, transaction boundaries, etc.) that may not be able to be decided on globally and once-and-for-all, so the "client" of the API may still have to deal with the low-level specifics of data access. It starts to look like generating all those methods hasn't really bought you all that much.

I, too, wouldn't choose code generation for this problem.

How would you write this data access library so that changes in the db and/or application logic will be easiest to handle?

That's the tricky part, and partly why I'm not so hot on Hibernate and friends; things are always changing. ;-)

The pattern I'm using these days with good success looks like this (in Java):

There is a family of classes that encapsulate statements in the database language. These know how to set any variables in the statement, including any necessary conversions. At this level you "speak the databases language", i.e. programmer simply uses their understanding of their specific DBMS and schema to get the result you want. A DBA can help here if need be.

Another family of classes knows how to convert the results of queries into application domain objects. These are often surprisingly simple and quick to write.

The actual use of these statements and converters is hidden from the rest of the app by "loaders" and "savers". The rest of the app doesn't have to know anything about the database (even whether it exists or not). This helps to reduce the effects of change both ways across the barrier.

Yes, there are a lot of these classes with a large database, but each class is quite simple, and our team can use existing Java knowledge and database knowledge to quickly change them as needed. We routinely have major schema and app changes and can generally synch them up quite quickly.

There is no need to encode the metadata from the database in some 3rd configuration language or format. I don't have to make any assumptions about how tables and columns map to app data structures or types; I just write simple converters as needed.

Success is a lousy teacher (to paraphrase Bill Gates), and
therefore we should not conclude that the database
system, as the unit of engineering, deploying, and
operating packaged database technology, is in good
shape.

Rethinking Database System Architecture:
Towards a Self-tuning RISC-style Database System paper claims the RDBMSes (I mean marketed implementations) themselves are becoming a choking morass for developers.

I think that there is a general misunderstanding in the software industry of the purpose of a DBMS, or at least a RDBMS. As you say there were specific features that they have by definition (though finding a good one right now eludes me. Anyone?) One such is application independence, which among other things enables reporting applications to do their job.

Many applications have simpler requirements that do not call for a DBMS, and in such cases a DBMS's extra features become a source of complications. For example, if an application simply needs to persist objects then a database will always be a poor choice no matter how good a framework there is. I think it's a lack of recognition about this that leads to the ridiculous claims that Prevayler made (makes?), for example. It may or may not be an excellent persistance engine, but comparisons to databases are apples and oranges. That it exists at all I see as proof of a common misuse of databases.

To come back to Ehud's original question, I think even the common commercially used languages have sufficient power of abstraction to deal with databases or other lesser (in terms of features) persistance engines. I'm not sold on code generation, though. I also think there is a lot more confusion and misunderstanding about data and data storage right now than there is about programming languages and language design (the cynic in me points to the far greater role of industry in the database field then in PL's.)

Safety is a key reason for the separation. Moving the db to its own process isolates it from all the wacky things your app may be doing with threads and memory.

True, and definitely an important consideration. But this safety can also be provided at the language-level, as with the Mnesia database in Erlang, to keep the benefits of integration.

So why d'yall use these SQL servers, anyway?

- Because there is already significant investement in them in many organizations
- Because someone wants to use standard reporting tools against them.
- Because the organization wants to store data that is used across applications and organizational divisions.
- Because in many organizations applications come and go but data must live forever.
- Because your boss/client/grand-high-poobah said so. ;-)

Indeed I was wondering whether open-source code like Firebird, PostgreSQL, and MySQL might not permit that sort of thing, heretofore restricted by closed-source distribution.

you may even point out the Criteria library. Criteria allows you to write things like:

idx1 = SQLTable.new("orders")
q1 = (idx1.price > idx1.paid) &
(idx1.duedate

q1.limit = 5
q1.order = :ASC
q1.order_by = idx1.name, idx1.age

q1.select()

and spit out SQL92 like:
SELECT * FROM orders WHERE ((orders.price > orders.paid) AND (orders.duedate

note that ruby does not have macros, and that Criteria does not allow you do everything (but I may be wrong about this)