SQL Hell

The contents of this post hardly comes as a surprise to anyone who has done any extensive web developing (that would include me), but venting ensues:

One would imagine that standards are made to, say, standardize stuff. Well, HTML4 and CSS2 are standards, but look at how much of a consolidation it is to the plethora of web developers that have to juggle code so that layouts look similar between IE and, um, all other browsers.

Would you be surprised that SQL's first standardized version is years older than HTML's first one? Would you be even more surprised that SQL's most recent version is years younger than (X)HTML's latest one? Seems like SQL has a longer and more rigorous history, but still it seems like no two SQL implementations are compatible with each other. If you feed a SQL statement that's over five words long, the chances are that no two RDBMS work identically (should they not return errors, which is a feat on its own). At least HTML works with incompatible browsers - SQL just spits out errors and pouts. And I'm getting the impression, that MySQL is the IE of RDBMS:es, when it comes to standard behavior...

Having done smaller scripts previously, with the Hack, Test, Look What's Happened Once The Smoke Has Set Aside -way of working, I have never got into the vast and exciting (not!) depths of per-engine work-arounds. I would have no idea what performance differences there are between MyISAM and InnoDB. I've written bits of SQL, seen that they work as expected, and moved on. But now, with this, there's not really anything concrete to test the models on. Models could be thought of abstract data types that the end user defines into something intuitive. Therefore, Models have to work for anything, with anything, anyplace, anywhere, anytime.

I've planned to have support for MySQL, PostgreSQL and SQLite (to start with). I have to generalize whatever table constructions into something that would work with all and any SQL engine. Oh, how convenient it would've been, if I could just write one standard SQL query, and never think of it again. But no! Now I'm practically forced to write one SQL statement for each supported engine. The modifications required for the query depends really on the context. Sometimes a simple rewording is enough. Other times, one keyword must be replaced by separate queries. Unnecessary to say, the SQL queries are, and will be, far from optimal on a per-engine basis.

Wouldn't it be nice, if standards were holy scriptures, where developers could take a look and see how stuff would work, instead of being forced to read each implementations' documentations of "how we do stuff around here"?


Obviously, LightFrame would do good with a SQL guru mucking about... *nudge*