LinLog

Ruby on the Cross

pageer@skepticats.com (Peter Geer)

Author's note: This is a piece I wrote back in 2006, but didn't publish because I was afraid people might find it offensive. I found it while looking through the backlog of unpublished/unfinished entries in my drafts folder and thought it was funny enough to share. It's a satire of "magic" frameworks like Ruby on Rails (which was the big thing at the time), which are sold as the solution to all your problems. Thinking about "Rails evangelist", my mind naturally jumped to the new testament evangelists, so this is sort of a merger of the two. No offense intended to any Christians in the audience - I just thought software and eternal salvation made for a funny juxtaposition. But maybe I just have a weird sense of humor. Enjoy!

That's it - the "programming problem" will soon be solved. Next week is the official first, last, and only ever release of the ultimate web development framework - Ruby on the Cross.

What is it

What is RC? It's the ultimate web development tool based on the Christian faith. Think of it as a cross between Ubuntu Christian Edition and Ruby on Rails, but infinitely more productive. It was inspired by the Book of Matthew, chapter 17, verse 20:

And Jesus said unto them, Because of your unbelief: for verily I say unto you, If ye have faith as a grain of mustard seed, ye shall say unto this mountain, Remove hence to yonder place; and it shall remove; and nothing shall be impossible unto you.

Using this principle, RC is able to reach untold levels of productivity.

What does it do?

As you probably already know, Ruby on Rails takes all the pain out of developing CRUD applications by taking your database schema and automatically generating models, controllers, and default views for it, giving you a working, if simple, web application just by running a few commands. RC takes this one step farther: it generates your domain logic for you.

That's right, you heard me: Ruby on the Cross is capable of doing your business analysis for you. In fact, not only can it generate your domain object, but it generates fully functional controllers; rich, AJAX-driven views (which are fully backward-compatible with Mosaic 1.0, by the way); and even writes your database schema. Best of all, it is 100% guaranteed to be future-proof and to be maximally performant. Thus there will never be any need to maintain the generated program!

How does it work?

At this point, you're probably thinking that this is too good to be true. How could anyone possibly promise those things?

Well, remember the quote from Mat.17:20? By drawing on the faith of the programmer, RC is able to miraculously harness the power of God to create genuinely perfect software. There are no algorithms to debug, no trade-offs or design compromises to make. Just the pure invokation of divine power, which not even Murphy's Law can resist.

Hard to believe? It shouldn't be. Just think about all the things people thank God for. If He can get someone a parking space or help the high school basketball team win their big game, why can't He write software?

How do you use it?

Using Ruby on the Cross is quite simple. You simply need to run a few shell commands. You start with:
ruby cross praise jesus
This command initializes Cross and prepares your system for an infilling of the Holy Spirit.

The next command is:
ruby cross supplicate <app_name>
This command forms a prayer, asking the Almighty to design your application. Note that no configuration, or even knowledge of the problem domain, is necessary for this step. Being omniscient, the Lord is able to determine exactly what your customer needs, and will need in the future, with perfect precision.

Lastly, you simply need to ask God to put the project files on your PC so that you can deploy them. To do this, you simply run:
ruby cross create ex nihilo <app_name>
This will cause the completed project source code to appear on your hard drive. Note that you do not need to specify a path on the filesystem - the Lord knows the most appropriate place and will put the code there. And don't worry about source control: God has already added the code to your RCS - not that you're ever going to need to change it.

Pathological PHP

pageer@skepticats.com (Peter Geer)

You know what annoys me? People with crazy ideas. Especially when they pimp them like crazy.

That's why this tutorial on "code separation" from a web forum I occasionally visit annoys me so much. The author links to this thing like his life depends on it. Whenever somebody has the nerve to post a code snippet that has both HTML and PHP in it, he brings it up. Even if the code is just echoing a variable inside an HTML block. It's ridiculous.

Don't get the wrong idea - separation of concerns is obviously a good thing. If you're outputting HTML and querying the database in the same file, you're doing things wrong. But this guy takes it to absurd lengths and insists that you should never have any PHP code mixed in with your HTML. Not even echo statements.

The real kicker is the content of this "tutorial". It's basically a half-baked template system that does nothing but string replacement of pre-defined placeholders. At best, it grossly over simplifies the problem. I suppose it does demonstrate that it's possible to output a page without having HTML and PHP in the same file (as if anyone really doubted that), but that's about it.

The thing that really bothers me about this approach is that the author promotes it results in code that's easier to understand than code that has both PHP and HTML in it. Except that it's not. The guy apparently just has a pathological fear of having two different languages in the same file. it's completely irrational.

The problem with his approach is that it doesn't actually solve the problem, but just moves it. Sure, basic replacement like that it's fine for simple cases, but as soon as the requirements for your markup get more complicated, things blow up. For example, how do you do conditionals? Well, you have another template file and you do a check in your controller for which one to inject into the page. What about loops? Well, you have a template file for the loop content and you run the actual loop in your controller, build up the output, and inject that into the page.

The net result? What would normally be a fairly simple page consisting of one template with a loop and two conditionals is now spread across six template (one main template, one for the loop body, and two for each if statement) and pushes all the display logic into the controller. So instead of one "messy" template to sort through, you now have a seven-file maze that accomplishes the same thing.

I find it difficult to see how this is any sort of improvement. At best, it's just trading one type of complexity for another in the name of some abstract principle that mixing code and markup is evil. Of course, if you want to follow that principle, you could always go the sane route and just use something like Smarty instead. But let's be honest - that's just using PHP code with a slightly different syntax. It may be useful in some cases, but it's not really fundamentally different from just writing your template files in PHP.

Personally, I've come to be a believer in keeping things simple. PHP is a template system. It was originally designed as a template system. It's good at it. So there's no need to introduce additional layers of template abstraction - just stick with PHP. There may be cases where things like Smarty are useful, but they're far from necessary. And the half-baked templating systems like those advocated in that tutorial are just intellectual abortions. There's no need to reinvent a less functional version of the wheel when you can just use a working, tested wheel.

Good intentions, bad idea

pageer@skepticats.com (Peter Geer)

Today I'm going to discuss a comedy of errors. This starts out with a nasty bug that surfaced in my company's product a couple of months ago, and finally became clear when I was doing some prep work for implementing a CDN. It's a tale of good intentions, bad ideas, and code I didn't write, but have to fix.

First, the bug.

To explain the bug, I have to tell you a little about how my company's new product works. Basically, it's a drag-and-drop UI builder for Flash ads. The user designs their ad on a design surface in a FLEX app, saves it, than can then publish the result. However, rather than actually compile a SWF for the ad, we're currently assembling all the assets at run-time on the client-side. Our ad tags serve up a shell SWF file and inject a few parameters into it, including a URL to a "recipe" file that we use to cook up the ad. This is just an XML file contains the information for the ad's constituent elements. The SWF file parses it and pulls/creates all the needed objects to build the ad. There were various reasons for doing it this way, but I won't get into those.

Now, this bug was a real head-scratcher. The actual problem was simple - the shell SWF just wasn't rendering the ad. No error, no message - just didn't work. However, it only happened in IE - Firefox, Chrome, Opera, and Safari worked just fine. It also only happened in our production and test environments - our dev servers and demo server worked fine in IE. The SWF files were identical in every environment - I know because I triple checked. What's more, I could see the XML file being requested in the server logs, so the SWF wasn't totally crapping out. And, again, it worked in other browsers, so it didn't seem like there could be an issue with the SWF.

Well, after researching and messing around with this for the better part of a day, our QA person found a link that put us on the right track. In fact, there are a bunch of such links. It turned out to be an issue with the HTTP headers on the XML file. The file was being served over SSL with the "no-cache" header set. Turns out IE doesn't like this. Apparently "no-cache" keeps the file completely out of cache when used on SSL, which means not even long enough for the browser to pass it off to the Flash plugin. Apparently we would have seen an error for this if we did the SWF file in ActionScript 3, but we used ActionScript 2 (apparently most of our customers require ads to be in AS 2 - don't ask me why), which has a penchant for failing silently. And the reason it didn't happen in all four environments is because the Apache configurations were actually not all the same. Go figure.

Fast forward two months to the discovery of the cause.

I'm looking to implement a CDN. We've got one that does origin pull, so it shouldn't be a big deal, right? Well, yeah, but we still have to make some changes, because those "recipe" files are on the same path as rest of our media and it won't do to have the CDN caching them when users are editing them and trying to preview the output. So I need to fix it so that, at least for the previews, we can serve the recipes from our own server instead of the CDN.

A few months ago, when we implemented user uploads, we (and by "we" I mean "another guy on my team") added the concept of a "media URL" to our system. The idea is that we could just change this media URL to switch to a CDN without having to change any URLs in code. This was implemented as a method on one of our back-end classes. It would return the base domain and path, if applicable, from which media is served. So building a URL would look like this:
$image_url = ObfuscatedAdClassModel::getMediaURL(); $image_url .= '/path/to/naughty_pic.jpg';
The getMediaURL() method just checks the database/Memcache for a saved URL and returns the saved value or a static default if nothing is found. Easy-peasy.

Or not. You see, I exaggerated in that last sentence - what I meant was, that's what getMediaURL() should do. In actuality, it does a bit more. In fact, as an object lesson in over complication, I'm posting the redacted code below.
static public function getMediaURL(){ global $cache_enabled;

$db = self::getDB(); if (!is_object($db)) { throw new Exception('Error getting database connection'); }

$settings_key='media_url'; if( $ft_query_cache_enabled ) { $settings_value = $db->getCache($settings_key); }

if (!$settings_value){ $settings_value=DEFAULT_MEDIA_URL; $query = "SELECT value FROM settings WHERE key='$settings_key'"; $ret = $db->query($query); if (is_array($ret[0])) { $settings_value = $ret[0]["settings_value"]; } elseif ($ret === true){ try{ $db->startTrans(); $have_lock = $db->query( "LOCK TABLE settings IN ACCESS EXCLUSIVE MODE;" ); if ($have_lock){ $query = "SELECT value FROM settings WHERE key='$settings_key'"; $ret = $db->query($query); if (!is_array($ret[0])) { $query = "INSERT INTO settings (key, value) VALUES ('$settings_key','$settings_value')"; $ret = $db->query($query); if($ret !== true){ $err_msg = "Unable to insert setting: $settings_key"; Log::error($err_msg); throw new Exception($err_msg); } } } else { $err_msg = "Could not acquire table lock or table does not exist: settings ".$db->getLastError(); Log::error($err_msg); throw new Exception($err_msg); } $db->commitTrans(); } catch (Exception $e){ $db->rollbackTrans(); $err_msg = 'DB error while attempting to update settings.'; Log::error($err_msg); throw new Exception($err_msg); } } else { $err_msg = 'DB error while attempting to load settings.'; Log::error($err_msg); throw new Exception($err_msg); } if( $cache_enabled ) { $db->setCache($settings_key, $settings_value, 3600); } } if ( $_SERVER["SERVER_PORT"] == 443 ){ return "https://" . $settings_value . "/"; } else { return "http://" . $settings_value . "/"; } }
This should be just a simple look-up in a generic settings table. However, when the desired setting is not found, this method tries to insert it into the database. This is completely unnecessary and leads us to five levels of nested "if" blocks and a table lock. We already have the default value value, so why not just return that? And the table lock is just paranoid. This is not a setting that's likely to change more than once every couple of months. And if we do get a minor inconsistency, so what? You think users are going to complain that an ad didn't render properly?

Note also that the returned URL is set to straight HTTP or SSL based on the current server port. Hint: this will be important later.

Now, the reason I'm looking at this is because I need to adjust how the URLs to the recipe files are handled in our system. Given our media URL scheme, if we set our static contents to serve from the CDN, the recipes will go with them. For previewing in-progress ads, this won't work. So I need to change the recipe URLs to use something other than the media URL. So I find where the recipe URL is injected into the client-side code and start tracing it backward.

To my surprise, I find the that recipe URL is actually not set dynamically using getMediaURL(). It turns out it's coming from our back-end ad object, via a getMetadata() getter method, as a full, absolute URL. And what is this metadata? Well, it's an associative array of seemingly random data that we serialize and cram in the database. And by "we", I mean the same guy who wrote getMediaURL(). For the record, I told him it was a bad idea.

So if the recipe URL is coming out of this metadata, what does that mean? That it's stored in the database. So I start grepping for the key for the metadata of the recipe URL. And I find it in the web service method in our management dashboard that saves the XML files.

Let's pause here. Now, if you're very sharp, you may have asked yourself earlier why we were serving out this XML file over SSL. We're serving ads right? They don't need to go over SSL. And this Flash problem was specific to SSL, so if we just served them over straight HTTP, we should have been good. So why didn't we do that?

That's a good question. It had occurred to me after I fixed that bug (by changing the Apache configuration), but when I looked, I couldn't find where the URL was set to HTTPS. Plus I didn't know why we were serving them over HTTPS, so I assumed there must be some reason for it. And besides, the bug was "fixed" and I had lots of other work to do at the time, so it wasn't a priority.

Again, if you're sharp, you can probably see that there was no good reason. The recipes were being served over SSL by accident! You see, our management dashboard, which is where the recipe file is saved, runs over HTTPS, and getMediaURL() selects the protocol based on the current one. So when we called getMediaURL() to build the recipe URL to save in the database, it came back as an HTTPS URL.

So there you have it. A crazy, hard to diagnose bug, caused by a method that's too smart for its own good, and hidden by an ill-conceived half-abstraction. I hate to speak negatively of a friend and former colleague, but this was really a lesson in poor system design. He needed to separate things out a bit more. He should have done less in getMediaURL() and factored the generic "metadata" out into separate properties rather than lumping them all together.

Situations like this are why we have guidelines in programming. Things like "don't put serialized strings in the database", "a method should do one thing", and "don't use global variables" can seem arbitrary to the inexperienced. After all, it's so much quicker an easier to do all those things. But those of us who've been around the block a few times get that queasy feeling. We know there's a reason for those guidelines - those things can easily come back and bite you hard later on. Sure, they might not become a problem, but if they do, it's going to be much harder to fix them later than to "do it right" the first time. The hard part of software development is not "making things work" - a trained chimp can do that. The real art is in keeping things working over the long haul. That's what separates the real pros from the ones who are just faking it.

Batch script goodness

pageer@skepticats.com (Peter Geer)

Ah, Raymond Chen. You can always count on him for a good tip stated in a way that makes it seem blindingly obvious.

The other day I got a couple of good tips from his blog. While writing a simple batch script to wrap up executing some Selenium RC test cases, I had the need to change to another directory and then change back. A little Googling led me to this page, where Raymond expounds on the eminently useful %CD% environment variable, which stores the current directory. (Of course, I ended up just using pushd and popd, which I'd forgotten Windows had, but that's not the point.)

On the same page, Raymond links to a handy little 90-byte batch script that does the equivalent of the UNIX which command. I thought that was really great. In fact, that's something I've always wanted. I've got the UnxUtils, which includes a Windows port of which, but it's just not the same, because it requires you to specify the file extension. I'm not much of a batch scripter, so I never would have thought to even try doing that in a batch file. Go Raymond!

Up 79 million in 20 seconds

pageer@skepticats.com (Peter Geer)

We had one of those weird experiences at work today. The kind where something strange happens, and nobody knows how or why, and when you look into it, it doesn't even make sense.

Basically, the auto-numbered ID field on the table we use for media items (videos, pictures, etc.) jump up noticably this morning. And by "noticably" I mean it went from about 1 million to about 80 million. And when we looked at the timestamps, the jump happened in about 20 seconds. The IDs created before 8:38:12 AM were in teh 1 million range, and the ones createdafter 8:38:32 AM were in the 80 million range.

So the obvious question is: how did this happen? It doesn't look like it was caused by actually adding 79 million records to the table. There were only about 800,000 records total, and no indication in our moderation logs of any mass deletions. We didn't get any indication of increased server load either. In fact, the only reason we even noticed it is because the media IDs are in our URLs. I kind of doubt a bot could have created 79 million new media items in 20 seconds without at least generating a Nagios warning. In fact, I doubt our master database server could handle 79 million writes in 20 seconds.

So what does that leave? User error? Nobody with access to the database server was even working at 8:30 in the morning. Random MySQL screw up? Maybe, though that's a really wierd random error. Something else? Who knows...?

After sniffing around the server and tossing out ideas for 30 or 40 minutes, we ultimately gave up. It's a little disquieting that we don't know what happened, but we really can't justify spending all that much time on this. It's a very weird problem, but nothing is broken and we all have more important things to worry about at the moment.

Edit: Found the cause. Apparently we were hacked.

PHP is developed by morons

pageer@skepticats.com (Peter Geer)

Well, it's official: the people who develop PHP are morons. Or, rather, the people responsible for adding namespaces to PHP 5.3 are.

Why do I say this? Because I just read an annoucnement on Slashdot that they've decided on the operator to use for separating namespace in PHP 5.3: the backslash (\).

Seriously? The friggin' backslash? What kind of choice is that? Last I knew they'd pretty much decided to go with the double colon (::), like C++, which at least makes sense. But the backslash?

What's worse, just look at the RFC listing the operators they were considering. In addition to the backslash, they had the double star (**), double caret (^^), double percent (%%), a shell prompt (:>), a smiley face (:)), and a triple colon (:::). For God's sake, it looks like they picked this list out of a hat. They might as well have just used the string NAMESPACESEPARATOR. It's no less absurd than any of those.

Now, let's be realistic for a minute. In terms of syntax, PHP is a highly derivative language. It's an amalgamation of Perl, C++, and Java, with a dash of a few other things thrown in.

Given that heritage, there's really only a handful of choices for namespace separators that even make sense. The first, and most natural, is the double colon (::). This is what C++ uses and it's already used for static methods and class members in PHP. So the semantics of this can naturally be extended to the generic "scope resolution operator." Keeps things clean and simple.

The second choice is the dot (.), which is what's used in Java, C#, Python, and many others. This is a bit unnatural in PHP, as dot is the string concatenation operator, but it at least offers consistency with other related languages.

Third is...actually, that's it. There are only 2 valid choices of namespace separator. And the PHP namespace team didn't pick either one. Nice work guys.

The Slashdot article also linked to an interesting consequence of the choice of backslash: it has the potential to mess up referencing classes in strings. So if your class starts with, say, the letter "t" or "n", you're going to have to be very careful about using namespaces in conjunction with functions that accept a class name as a string. Just what we needed. As if PHP isn't messed up enough, now the behaviour of a function is going to depend on the names of your classes and the type of quotes you use.

I guess I'm going to have to bone up on my C#, because PHP seems to be going even farther off the deep end that before. It was always a thrown-together language, but this is just silly. The backslash is just a stupid choice for this operator and there's just no excuse for it.

The impressiveness of ugly hacks

pageer@skepticats.com (Peter Geer)

If you've been a programmer for any length of time, you've probably seen lots of hacks designed to work around limitations of a particular language or toolkit. You may have even come up with some yourself. They're a mainstay of programming.

Thing thing about hacks is that we all know we're not really supposed to use them. At least, those of us who are moderately competent know that. Of course, sometimes we use them anyway, either because we have no choice or because the alternatives are just as bad (like all the nasty CSS hacks for Internet Explorer), but we're generally not happy about it. Coming up with hacks is more like a game. It's an excuse to push the technology to its limits, to see how far we can bend it to get what we want. And if you have enough knowledge and, just as importantly, imagination, you can do some pretty impressive things.

I started thinking about this last night when I came across what I considered a very impressive hack for PHP. The limitation it addresses, is that PHP binds the self keyword at compile-time, rather than run-time. The up-shot of that is that if you have a static method foo() in a base class Fizz and override it in a child class Buzz, any methods in your base class that call self::foo() will always use the implementation in Fizz, even if they're called through Buzz.

There was a work-around for this limitation in the comments on the get_class() page on php.net. This particular hack used a trick I never would have thought to consider - using the results of the debug_backtrace() function to loop though the call-stack and determine the class of the calling method.

Just stop and think about that - using a debugging function to control how calls to class methods are resolved. It's both brilliant and completely insane. It just feels wrong - like you're going way too far for a piece of functionality that's easily achieved by using a different approach (i.e. instance methods). If I ever saw one of my co-workers put that in our codebase, I'd beat him in the head with a keyboard until he apologized. And yet, at the same time, it makes me smile, because this is one of those things you really shouldn't be able to do, and yet you can.

John Lam rocks

pageer@skepticats.com (Peter Geer)

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.

Updating from the same table

pageer@skepticats.com (Peter Geer)

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.

Another reason to hate MySQL

pageer@skepticats.com (Peter Geer)

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