Reputation bait edit:
Me and another developer at my work had a whiteboard with a 'Technologies that should die' section we'd add to. Mime email was on there, as was SQL. The problem with entrenched technologies such as these is it's hard to see a world without them - it's easy to see a world without them, but it's hard to visualise a world with something genuinely better.
SQL is one such relic. It was originally designed to be hand typed into a database using queries designed by a human - hence the pseudo-english structure of it. Machines were not meant to make SQL commands, it's just that now that programming, especially with the web, has a high reliance on databases and SQL is what is the standard interface for databases is.
One thing I am sure of is that getting a programming language to make a human-language string which is passed to a database which then has to decode it back into a computer-language format to understand it is both inefficient and hazardous. Mixing data and commands is something you don't want to do as it is asking for issues. Even modern CPU's contain an NX bit to flag areas of memory as data so that they will not be accidentally run - causing either a crash or an exploit. SQL injection issues are rife, and are symptom not of (only) bad programmers, but of a bad technology. We have many high level languages (above C) so that buffer overflows are no longer an issue, but are stuck with a C level database language designed in the early 70's.
You just need to look at any semi-experienced PHP (or any language) programmer's code. No doubt they all use some form of SQL abstraction library to take the perils and complication away from using it. For example, if I want to pull the contents of a row I'll use something like the following:
$row = CMS_SQL::get_row ('pets_table', 'bob', 'name');
Which will pull the row from pets_table with the name of 'bob' - assuming name is unique in the schema. Using true SQL you'd have to do...
$name = mysql_real_escape_string ($name);
$result = mysql_query ("SELECT * FROM `pets_table` WHERE `name`='{$name}' LIMIT 1");
$row = mysql_fetch_assoc ($result);
Which is pretty horrible, especially if you have to repeat the same thing a lot. Also let's take updating rows. The code below uses my abstracted SQL class to change the cat name from bob to bobby.
$row['name'] = 'bobby'; # Change pet name to bobby
CMS_SQL::update_array ($row, 'pets_table');
Note: update_array uses the primary key id if it's available to track the row, which is why I don't seem to specify it. The SQL for the above is much worse.
And finally, get a single value from a database:
$cat_colour = CMS_SQL::get_element ('pets_table', 'colour', 'bob', 'name');
And in (unsafe) SQL:
$sql = "SELECT `colour` FROM `pets_table` WHERE `name`='bob' LIMIT 1";
$result = mysql_query ($sql);
if (mysql_num_rows ($result) > 0)
{
list ($cat_colour) = mysql_fetch_array ($result);
}
else
{
$cat_colour = false;
}
As I said it's very hard to envisage a replacement technology for anything - anything new usually has some good ideas that just don't work, and obvious ideas that are somehow missed, but I really do see an eventual abandonment of SQL and a move towards treating databases as if they were actually physical resources - with php for example possibly treating the whole thing as a native associative array. I also see references being treated as pointers transparently: e.g. if you're making a comment system and have a table called 'posts' and 'users' and you have a poster column on the table posts which references an unique entry in users then the the column posts.poster would be the correct row on users. Updating it would update the row in users. No joins, sub-selects or any nonsense.
With the whole thing being treated as a native data structure it would let you use either a special language to be run the database server, or even native code if the compiler was especially smart, which would let you use native conditionals rather than WHERE, as the whole WHERE var='val' AND var2='val2' is programming conditionals (such as if's) just represented in human-readable SQL.
It'll probably be an open source project such as PostgreSQL or MySQL or some other database which will provide a native class for dealing with data in a non-SQL format for a web-language such as Ruby, Python, PHP etc. which will bring the ease of dealing with native data and the power of a dedicated SQL server into one. Once there is a standardized, stable, native alternative I can forsee it replacing SQL for 99% of the low-end data storage platforms, with SQL relegated to high-performance ultra-huge databases and legacy systems (before eventually being entirely supplanted).
The reason SQL is so pervasive is that people think in terms of SQL when doing things - as soon as something comes along which allows people to mentally visualise data storage and queries in other terms I think it's days are numbered.
Edit: wow, I didn't realise how much I hated SQL until this point :)
