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....

Jeff Atwood had a really great blog entry on PHP the other day. I think the title pretty much sums up the way I feel about it: PHP Sucks, But It Doesn't Matter.

I've been working with PHP for about 3 or 4 years now. For the last 9 months, writing PHP code has been my day job. And I've got to tell you, PHP really is kind of a crappy language. It's so bad you can't even complain that PHP was poorly designed, because it quite clearly wasn't designed. In fact, I'm not even so sure it "evolved." Sometimes it seems like it just sort of mutated.

So, as Jeff said, nobody with an ounce of programming talent could thing that PHP is a "good" language in any objective sense. It's just too hacked-up and thrown together. It's the Visual Basic 6 of the web. God knows much of the PHP code you find on the net is every bit as terrible as the VB6 code you find. In fact, when you consider ill-conceived "features" like safe mode and magic quotes, it even starts to make VB6 look good.

But all that is really beside the point. At the end of the day, PHP does the job. It might not have the orgasm-inspiring elegance of Ruby, but does it really need that? These days, PHP has all the big features - decent object-orietation, a rich standard library including decent XML handling and database access layers, and a number of good MVC frameworks. In short, it has what it needs to allow decent developers to write good, solid, maintainable code. PHP doesn't lend itself to elegance, but in the right hands, it can be elegant enough. And really, that's all that matters.

I learned something cool yesterday - F# runs on Mono! That means I can mess with it without having to use VMware!

I got interested in F# from hearing about it on Hanselmintes and .NET Rocks. For those who haven't heard of it, F# is a functional programming language, similar to OCaml, built on .NET. It's actually not an "official Microsoft product," but rather a project out of Microsoft Research, which is pretty cool.

Incidentally, working Microsoft Research is on my list dream jobs. I mean, how many organizations can boast of having had two Turing Award winners on staff? How could you not want to work someplace like that?

Anyway, the thing that excites me about F# is the combination of functional programming and its status as a first-class .NET language. I've been meaning to get up to speed on functional programming for a few years now, but I've just never gotten around to it. Learning LISP or ML always seemed on par with refreshing my Prolog and Ada skills - an interesting exercise, but not profitable in terms of marketability. I mean, how many Standard ML listing have you ever seen on Monster?

However, it looks like functional programming may start pushing more into the main stream. The current trend in hardware is that CPU speeds are flattening out and performance gains are being made by adding more processors or cores. However, most code today is not written to use more than one core/proc at a time. So we're going to have to start parallelizing our code to fully take advantage of the hardware. That's where functional programming comes in. Pure functions, by definition, have no side effects. So if you're writing pure functional programs, parallelism becomes much easier, as you have no worries about thread safety and whatnot.

So with F# I can now learn functional programming while using .NET. This means that I can leverage some of my existing knowledge while learning the new language, which always makes things go faster and smoother. It also means that this learning experience has some vague marketability, i.e. I can at least count it as .NET experience. In other words, it's not one of those "off in left-field" learning ventures like if I took up Intercal or APL. I'm not going to feel (as much) like I could be making better use of my time.

Anyway, it turns out that installing F# on Ubuntu 8.04 wasn't quite as painless as I had hoped. On the up side, the F# site does supply a ZIP archive with generic Mono-compatibile binaries and full source (under the MS shared-source license). However, it seems the binaries don't quite work right with Mono 1.2.6. That's fixable, though, thanks to Laurent Le Brun's article on using F$ 1.9.4.17 on Mono. Basically, the important thing is to remember to pass mono the --runtime=v2.0.50727 option when running the F# compiler or F# binaries.

I haven't been blogging much lately, but hopefully I'll be posting back in the coming months with tid-bits on F#. It's been a while since I tried to learn a new language, especially a non-procedural one, so I'm looking forward to it.