Posted on

MySQL 5.0 standardized join syntax

I am sure the revised / SQL:2003 standardized join syntax in MySQL 5.0 is old news to many out there. My guess is they are in the minority, and most haven’t heard a thing about it. Some may have even upgraded only to be frustrated that their queries weren’t working as they should any longer. Here’s the skinny, taken directly from the MySQL manual.

Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. These changes make MySQL more compliant with standard SQL. However, they can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.

I would like to applaud MySQL AB on their latest release. Throughout its history, MySQL has been vastly out-gunned in terms of useful features by many other commercial and free databases. It has also taken a great deal of heat on many occasions due to its poor standards conformance in comparison to the other options on the market. With this release, even if they have not completely closed that gap, they have narrowed it by an impressive margin.

I do have a major gripe, however. Whilst the previously mentioned changes improves standards conformance and portability, it breaks a large enough percentage of MySQL-bound applications to warrant serious scrutiny. Apparently MySQL AB has forgotten that not the entire world is open source, and that many of us must every day maintain databases accessed by scripts and applications controlled by a third party or to which the source code is simply not available. This makes it rather impossible for any of us in such a situation to move those databases to servers running 5.0.

In my particular case, I was looking forward to leveraging triggers, stored procedures and views to reduce my administrative burden and deprecate a number of external scripts (hacks) that we use to transform data for use by other applications. The primary application sitting on this database is commercial and Zend encoded, so “fixing” the broken queries is simply not an option. Yes, we have talked to the vendor. I find it hard to believe that I am the only one in this situation.

Seriously now, how hard would it have been to add an option to enable the legacy behavior?

Update: 2/19/2006, offending commit
http://mysql.bkbits.net:8080/mysql-5.0/patch%401.1886.80.1

Posted on

PHP/AJAX file upload with progress bar

Over the past couple of days I have been pondering adding some file upload functionality to the form classes I have been using for a bit over a year now. History repeats itself, again, time spent pondering instead of just getting on with the nitty gritty means I start thinking about ideal functionality. So, as I pondered how to go about sanely handling file uploads features started coming to mind, and one of them just wouldn’t go away. A semi-realtime inline file upload progress indicator. Well, that doesn’t sound so hard.

I spent some time with Google doing the requisite research to find that there are a number of stumbling blocks. The first being client-side, when a browser window/frame is busy pushing a file or files up the pipe, it seems that it is just that, busy. Which makes it a bit difficult to talk it into displaying updates. This seems to be pretty easily solved by pushing the file upload through a hidden iframe referenced by the target attribute on the form.

That certainly isn’t where the problems end. As luck would have it, not only is the browser happy to be working against us, so is PHP, in more ways than one.

When the execution unit handling the upload gets hit with the POST, it would seem that it likes to make itself busy as well. Ok, so no way to get the status of the file upload from the thread/process actually handling the upload. Apparently there are some patches against PHP to rectify this situation, but until they get committed and see a release they are unusable for most people. I am all for gratuitously hacking my own PHP install, but it seemed like there must be a better way.

I then stumbled across another method. Scan the upload_tmp_dir (PHP INI variable) for files of a known naming scheme, looking for the one with the latest timestamp. The current size of this file could be pushed back to the browser so that it could calculate the upload progress. This method is also not without its glaring faults. The probability of a race condition is too high for any kind of production use. Oh wait, scratch that, I’m starting to sound like a PHP developer, let me rephrase… There is an unavoidable possibility of a race condition, so this method cannot be used. Well… Wait a minute, there is an upload_tmp_dir variable. Why don’t we just generate some kind of unique form id to be passed back to us when we get the POST, then it should be possible to create a directory to have PHP put the file(s) in of a known name, eliminating our race, no? I suppose upload_tmp_dir being read-only is a bit of a stumbling block with that idea, considering we already decided hacks to the PHP source were out. Not to mention PHP probably isn’t going to let us set the variable before it gets busy processing that form data anyway.

Google led me to a couple more resources for accomplishing this throughout the course of my research, but they all involved an external non-PHP script to handle the upload and drop status information somewhere accessible. Unacceptable I say! There must be a way to do it with PHP alone!

I have theorized a method, implementation forthcoming. Here is a brief summary. Have an onSubmit handler frob a PHP script and retrieve a URL to apply to the action property of the form, said PHP script will have just launched a PHP-based very simple webserver. This webserver’s sole purpose in life is to eat POST’s and parse multipart form data. This same PHP script will update an accessible location with the status of the upload. The hidden iframe trick gets used to free up the window with the form in it. This window can now pull upload status via XMLHttpRequest and update a progress bar accordingly. This method also has the benefit of being able to degrade gracefully in the event that JavaScript is unavailable on the client. The default action URL can be implemented as a standard file upload handler.