LinLog

So this week, .NET rocks had an interview with John Lam of Iron Ruby fame. It was a good show, but for me, the single best part was a comment John made in the last 5 minutes of the show. He said:

Rails is the Visual Foxpro of the 21st century.

I just thought that was great. It really appealed to the contrarian in me. With people touting Ruby on Rails as the future of web development, it's nice to see it compared to the has-been languages of yesterday.

Of course, I'm taking that quote out of context. John wasn't actually trying to put down Rails, but was simply making the point that it's a tool that appeals to pragmatists, in that it allows you to quickly and easily create simple applications that interact with databases. Which is exactly what Visual Foxpro did too.

For the record, I'm not a Ruby on Rails fan. I don't dislike it, though - I haven't used it enough to form a strong opinion. However, the few times I've played with it, I found it nice, but not compelling. I didn't hate it, but I didn't like it enough to put in the effort of learning both Rails and Ruby. Though, truth be told, from what I've seen, I like Ruby a lot better than I like Rails.

Likewise, I am not a fan of Visual FoxPro either. Of course, I've never actually used Visual Foxpro, but at my last job I did have to maintain and rewrite some old FoxPro 2.6 (for DOS) applications. Maybe my opinion was influenced by the apps I was working on (they were written by a clerk who "knew FoxPro" and was trying to help out), but I found it to have all the elegance and sophistication of VBA in Microsoft Access. And for those who aren't familiar with Access VBA, it has all the grace and subtlety of a sledgehammer.

The thing is, FoxPro actually worked pretty well. Granted, it was ugly and promoted antipatterns, but it was pretty easy to create a simple desktop database application. I don't know if it was simpler than Rails, but it was probably in the same league. Same thing with Visual Basic - just drag and drop a few controls on a form and, voila! Working database app!

I think that's one of the things that turns me off about Rails a bit - the examples and hype around it smack of VB6 demos from the 1990's. Whenever I see a demo that says something like "Create <impressive sounding thing> in 15 mintes," I'm automatically skeptical. I'm skeptical because I've seen the same thing done in VB6. It's same reason I get turned off whenever I see someone extol the virtues of programming language X over language Y by pointing to how much shorter a "Hello, World," program is in X, or some other such inane metric. It's just not a useful or meaningful comparison.

Getting something up and running fast is all well and good. Nobody wants to spend 3 months on infrastructure just to spend a week building the actual application. But the name of the game is maintainability. It doesn't matter how fast you get up and running if you have to go back and tear out half the application when your requirements change. Likewise, your productivity gains start to evaporate when you need to spend 3 days coming up with a work-around because your environment forces you to do things in a particular way that doesn't really work in your case.

Bottom line: there's always a catch. And if you think there isn't a catch, then you just don't have enough experience to know what the catch is yet. No technology is perfect, whether it's ActiveRecord, data-bound controls, or whatever other new miracle library came up in your RSS reader today. There's always something that will get you if you're not careful. The trick is to know what it is before it's too late to account for it.

Last time, I was bitching about doing an update based on a select from the same table in MySQL. Well, after reading the chapter on "Views, Derived Tables, Materialized Tables, and Temporary Tables" in SQL for Smarties, followed by a search of the MySQL documentation, I figured out a way around that annoying limitation.

If you recall from last time, the query that wasn't working looked something like this:

mysql> UPDATE test_tbl SET val2 = (SELECT val2 FROM test_tbl WHERE val1 = 1) WHERE val1 = 3;
ERROR 1093 (HY000): You can't specify target table 'test_tbl' for update in FROM clause

Basically, this is doing an update and setting a column to the value of a scalar subquery. The catch is that, in MySQL, this doesn't work when the select and update are on the same table.

Well, the trick is that you have to make make MySQL materialize the subquery. I actually decided to try that after reading Joe Celko's story about views in the early days of DB2. At the time, it was using inline-expansion for views, which would sometimes generate invalid SQL. Thus it was sometimes necessary to make the database materialize the view.

Well, I figured the same thing might apply here. And so, after a little inspiration from the MySQL manual, here's a working version of the same query:

UPDATE test_tbl SET val2 = (SELECT val2 FROM (SELECT val2 FROM test_tbl AS tmp1 WHERE val1=1) AS tmp2) WHERE val1 = 3;

I stole the sub-subquery idea from the MySQL manual. It is, of course, completely gratuitous from a logical standpoint, but it forces MySQL to materialize the subquery. That, in turn, allows us to bypass the "no updating the same table" restriction, since we're technically selecting from a temporary table.

So there you have it! I was wrong about not being able to do that. It just takes a little finagling. Of course, I still think MySQL should be smart enough to make a temporary table on its own, but at least the end result is still possible.

I'm currently about half way through Joe Celko's SQL for Smarties and I've got to tell you - it's spoiling me as a LAMP developer.

Let me start out by saying that this book is a must-read for any developer who works with databases. Even if you've been writing SQL for years, you need to read this book. In fact, unless you sat on the ANSI SQL-92 committee with Joe Celko, you can benefit from reading this book. I've been "doing SQL" for years, and I've learned a bunch of new tricks and rediscovered things I had forgoten from reading this. And not only is it informative on SQL, but it's also entertaining and informative on other subjects (e.g., in one chapter, Joe describes the history of the soundex algorithm).

The problem with getting more SQL-literate is that I have to work with MySQL. Now, I don't want to rag on MySQL too much - it's good for what it is. And what it is is an engine for storing data and getting it back out in an efficient manner. The problem is that it's really not focused on implementing standard SQL in any deep way.

My particular annoyance today came when I was writing code to do a simple update. The situation is that I was working on a new variation on the picture galleries for our site, and realized that I wasn't associating a thumbnail image with the galleries I was creating. By virtue of the way our codebase works (all of which pre-dates my involvement), our picture galleries consist of n+1 rows in our main media table, where n is the number of pictures in the gallery - one row per picture and another row for the gallery meta-item. Because of the way I had written my creation methods in PHP and added my foreign key constraints in the database, the path of least resistance was to just set the gallery thumbnail to the same value as one of the pictures. Here's a simplified example of the basic query I used:

-- Create a sample test and populate it with data.
CREATE TABLE test_tbl (id INT, val2 INT);
INSERT INTO test_tbl VALUES (1, 2);
INSERT INTO test_tbl VALUES (3, NULL);
-- Here's the query I was running.
UPDATE test_tbl SET val2 = (SELECT val2 FROM test_tbl WHERE id = 3) WHERE id=2;

In the routine as I had written it, I had the IDs of the gallery and all the pictures, but that was it. With a query like this, I could just clone the thumbnail of one picture to the gallery in a single query - no need to clutter the code with any lookups or suchlike.

Of course, if you know MySQL, you may already know that this didn't work. When I tried running this, I got:
ERROR 1093 (HY000): You can't specify target table 'test_tbl' for update in FROM clause
I was actually kind of surprised by this. I mean, this query isn't all that exotic. It's perfectly valid in standard SQL and it works in PostgreSQL. Why not MySQL?

Well, the answer seems to be that MySQL disregards the conceptual execution model of subqueries. I say this because, if you do the subquery on a different table than the one being updated, then MySQL doesn't have a problem with it. Example:
-- Create a second table and add a row.
CREATE TABLE extra_tbl (id INT, val2 INT);
INSERT INTO test_tbl VALUES (3, 9);
-- This one works just find in MySQL.
UPDATE test_tbl SET val2 = (SELECT val2 FROM extra_tbl WHERE id = 3) WHERE id=2;

You see, according to Joe Celko (read the book!), the execution model of a subquery is that it creates a temporary table. So, in the case of my first sample query, it would create a copy of test_tbl, find the target row in that, and then update test_tbl. If you didn't use that model, then it would make some sense that you can't update the same table you're searching in. (Note: Of course, other databases don't actually create temporary tables for every subquery - they just produce the same result as if they did.)

I guess my problem is that I'm just starting to expect too much from MySQL. I've reached the point that I find it annoying that the default storage engine (MyISAM) doesn't support foreign key constraints. I find it even more annoying that MySQL doesn't support the enormously useful CHECK constraints at all. And, really, is it absolutely necessary to invent new versions of standard functions, mangle standard functions (what the hell did they do to CAST()?), or to disable the ANSI string concatenation operator by default? It all just just seems superfluous. After all, PostgreSQL didn't have much trouble with the ANSI standard. Why can't MySQL get it?

Oh, well. I guess it could be worse. At least now I have the wisdom to know what I'm missing. In days gone by, I thought MS Access seemed just fine as a database. Back then, I didn't declare primary keys and didn't even know what indexes were! By comparison to that, MySQL looked like freakin' Oracle! If only I hadn't been so young and stupid then, think of where I'd be now....