Weblog

PHPTuring

A few years ago, as an exercise in Test-Driven Development, I wrote a Turing machine simulator in PHP and imaginatively named it PHPTuring.

I had completely forgotten about it until today, when I dug it out for another look. Truth be told, I still haven't seen a Turing machine done any better in PHP, and apart from a few syntactical niceties (removing closing PHP tags as per the Zend way, neatening up the PHPDoc blocks) I'm actually pretty comfortable with the code.

Using it is a breeze. It reads pipe-separated tapes and newline plus pipe-separated instruction sets like so:

It should work with other formats, so long as someone writes parsers for them. Similarly, the debugger is just an Observer that dumps the state and tape to the screen at each step, but it could easily do something more subtle some day.

The code ships with full tests, and is available for download on PHPTuring's Sourceforge download page.

So why am I banging on about it here? I don't know. Maybe just because I like it, because it was the first afternoon's coding that really got me test-infected, and because I'd be interested in any feedback.

The Get Up Kids are The Greatest Band That Ever Existed

The Get Up Kids are the greatest band that ever existed. To prove this, I would like to draw your attention to some very early live footage. Firstly, here is a magnificent basement performance of "Off the Wagon":

It's like drinking when you're walking in a circle. And here are those pimply teenagers once again, covering "Disconnected", by Face to Face:

Five years later, Adrianne Verhoeven joins TGUK on stage midway through "Anne Arbour". Look how far they had come:

Stay tuned for further proof that The Get Up Kids are the greatest band that ever existed.

MySQL versus PostgreSQL: Adding a 'Last Modified Time' Column to a Table

This is the second post here detailing my ongoing adventures with PostgreSQL. This time I had a requirement to add a 'timestamp' column to a table. The point of this being to allow us to track the 'last modified' time of a row, without requiring that the application code manage the timestamp itself.

There's a lot of reasons why you might wish to do this. In this case it was to simplify syncing the data into a data warehouse. More specifically, to allow the DBA to easily identify rows which have changed since the last import.

Having done this a couple of times in MySQL, I assumed that the process would be straightforward. I should know better by now!

MySQL

MySQL provides a TIMESTAMP column type, for exactly this purpose. It's formatted as a standard DATETIME, but can optionally be configured to automatically set itself to the current time when the row is inserted (on by default) and/or updated (off by default). It's a breeze to add a column of this type to a table, like so:

ALTER TABLE mytable
    ADD lastmodified TIMESTAMP 
        DEFAULT CURRENT_TIMESTAMP 
        ON UPDATE CURRENT_TIMESTAMP;

Notice the crucial ON UPDATE clause, which tells MySQL to update the timestamp when the row is modified.

All rows in the lastmodified column are initially set to the 'zero' date, i.e. 0000-00-00 00:00:00. If we want to backfill the column with another value we can do that in a further step. This example sets each row's timestamp to the current date and time:

UPDATE mytable SET lastmodified=CURRENT_TIMESTAMP

Pretty straightforward, all in all.

PostgreSQL

Let's dive right in and view the Postgres solution in all its glory:

ALTER TABLE mytable 
    ADD lastmodified TIMESTAMP;

ALTER TABLE mytable 
    ALTER COLUMN lastmodified 
        SET DEFAULT CURRENT_TIMESTAMP;

UPDATE mytable SET lastmodified=CURRENT_TIMESTAMP;

CREATE OR REPLACE FUNCTION update_lastmodified_column()
        RETURNS TRIGGER AS '
  BEGIN
    NEW.lastmodified = NOW();
    RETURN NEW;
  END;
' language 'plpgsql';

CREATE TRIGGER update_lastmodified_modtime BEFORE UPDATE
  ON mytable FOR EACH ROW EXECUTE PROCEDURE
  update_lastmodified_column();

That's a fair deal more complex. The big obstacle here is that Postgres doesn't have the equivalent of MySQL's TIMESTAMP type. It does a have type named TIMESTAMP, but this is analogous to MySQL's DATETIME. We'll have to reverse engineer the behaviour we want.

We'll step though the process and have a closer look at what's involved. We'll start by creating the column and specifying that inserted rows should automatically default to the current date and time. As we've seen before, PostgreSQL won't allow us to do that in one step.

ALTER TABLE mytable 
    ADD lastmodified TIMESTAMP;

ALTER TABLE mytable 
    ALTER COLUMN lastmodified 
        SET DEFAULT CURRENT_TIMESTAMP;

Again, we may wish to backfill the column:

UPDATE mytable SET lastmodified=CURRENT_TIMESTAMP;

As an aside, I like that Postgres has some convenient shorthand values for commonly used dates. Alongside CURRENT_TIMESTAMP, there's such options as "yesterday", "epoch" and the cosmologically questionable "-infinity". They're documented here.

So far we've created the column, backfilled it with sane values, and any newly inserted rows will be timestamped appropriately. But what about updates to rows? This is where the fun really begins.

Lacking MySQL's ON UPDATE clause, which we noted earlier, we need to create a TRIGGER. A trigger is a kind of "event handler" which is fired off (well, triggered) in response to some action, in this case UPDATEs. To make things even more interesting, Postgres deviates from the SQL99 standard by not allowing us to run SQL directly within the trigger: any functionality we require must be defined in a stored proc:

CREATE OR REPLACE FUNCTION update_lastmodified_column() 
        RETURNS TRIGGER AS '
  BEGIN
    NEW.lastmodified = NOW();
    RETURN NEW;
  END;
' language 'plpgsql';

NEW is a special keyword which refers to the new version of the row. Similarly, OLD is available to us, should we need to access the previous values within the row.

Finally, we have to attach that proc to the relevant table by use of the trigger:

CREATE TRIGGER update_lastmodified_modtime BEFORE UPDATE
  ON mytable FOR EACH ROW EXECUTE PROCEDURE
  update_lastmodified_column();

MySQL's single command has succesfully been implemented as a dozen or so lines of Postgres code. I'll give round two to MySQL, but I'll give points to PostgreSQL for making my day a little more interesting than it might otherwise have been!

An Introduction to Tera-WURFL

I recently added a post about Wurfl, a comprehensive open-source XML database of mobile device capabilities. I noted that actually querying Wurfl in a performant manner:

is going to be a non-trivial task, and is perhaps a topic for a further article.

Well, I guess this is that article. It's time to have a look at Tera-WURFL, which is perhaps the most popular tool for querying Wurfl programmatically - from PHP, at least.

Tera-WURFL

Tera-WURFL is a PHP library written by Steve Kamerman, and made freely available to the public. The developers claim querying Tera-WURFL to be five to ten times faster than querying Wurfl directly with PHP, but in practice the performance benefits tend to be much higher, not to mention the greatly improved convenience of having a PHP library already written for you.

The key features of Tera-WURFL can be summarised as follows:

• A MySQL database containing data parsed from Wurfl itself

• A small PHP library which encapsulates querying the database, and provides a simple object interface to the data
• A web interface which makes it a breeze to retrieve the latest version of Wurfl from Sourceforge, and to import it into your local database

Installation and Configuration

Installing Tera-WURFL is pretty painless. It comes with full installation instructions so I won't go into too much detail here. Suffice to say that you'll need to download the latest version (currently 1.5.2) from the site, and either unzip it into a directory which is accessible via the web, or unzip it elsewhere, and create a symlink to it from a web directory. This is so that you can later browse to Tera-WURFL's admin interface in order to import or update WURFL.

You will also need to create an empty MySQL database for Tera-WURFL, and make sure that you have a MySQL user account which has full permissions on that database. Place the details of the database and the user account into the relevant slots in tera_wurfl_config.php, which lives in the root of the unzipped folders, and you're ready to go.

Querying Tera-WURFL

Once everything is installed and configured, accessing Tera-WURFL from within a PHP application is trivially easy:

That's really all there is to it, from a user's perspective. $device is now a large object with comprehensive information regarding the device and its capabilities.

Let's try a concrete example, that of Nokia's popular N95 handset, which identifies itself with the HTTP User-Agent string:

Mozilla/5.0 (SymbianOS/9.2; U; Series60/3.1 NokiaN95/11.0.026; 
        Profile MIDP-2.0 Configuration/CLDC-1.1) 
        AppleWebKit/413 (KHTML, like Gecko) Safari/413

Passing that string into the getDeviceCapabilitiesFromAgent() method, and calling print_r() on the resulting object provides us with output similar to the following:

array (
  'id' => 'nokia_n95_ver1_sub_mozilla_b',
  'user_agent' => 'Mozilla/5.0 (SymbianOS/9.2; U;
    Series60/3.1 NokiaN95/11.0.026; Profile/MIDP-2.0
    Configuration/CLDC-1.1 )
    AppleWebKit/413 (KHTML, like Gecko) Safari/413',
  'fall_back' => 'nokia_n95_ver1',
  'product_info' => 
  array (
    'brand_name' => 'Nokia',
    'model_name' => 'N95',
    'unique' => true,
    'ununiqueness_handler' => '',
    'is_wireless_device' => true,
    'device_claims_web_support' => true,
    'has_pointing_device' => false,
    'has_qwerty_keyboard' => false,
    'can_skip_aligned_link_row' => true,
    'uaprof' =>
        'http://nds1.nds.nokia.com/uaprof/NN95-1r100.xml',
    'uaprof2' => '',
    'uaprof3' => '',
    'nokia_series' => 60,
    'nokia_edition' => 3,
    'device_os' => 'Symbian OS',
    'mobile_browser' => 'Nokia',
    'mobile_browser_version' => '',
  ),

  // snip
  
  'xhtml_ui' => 
  array (
    'xhtml_honors_bgcolor' => true,
    'xhtml_supports_forms_in_table' => false,
    'xhtml_support_wml2_namespace' => false,
    'xhtml_autoexpand_select' => false,
    'xhtml_select_as_dropdown' => false,
    'xhtml_select_as_radiobutton' => false,
    'xhtml_select_as_popup' => false,
    'xhtml_display_accesskey' => false,
    'xhtml_supports_invisible_text' => false,
    'xhtml_supports_inline_input' => false,
    'xhtml_supports_monospace_font' => false,
    'xhtml_supports_table_for_layout' => true,
    'xhtml_supports_css_cell_table_coloring' => false,
    'xhtml_format_as_css_property' => true,
    'xhtml_format_as_attribute' => false,
    'xhtml_nowrap_mode' => false,
    'xhtml_marquee_as_css_property' => false,
    'xhtml_readable_background_color1' => '#FFFFFF',
    'xhtml_readable_background_color2' => '#FFFFFF',
    'xhtml_allows_disabled_form_elements' => false,
    'xhtml_document_title_support' => true,
    'xhtml_preferred_charset' => 'utf8',
    'opwv_xhtml_extensions_support' => false,
    'xhtml_make_phone_call_string' => 'wtai://wp/mc;',
    'xhtmlmp_preferred_mime_type' => 'application/xhtml+xml',
    'xhtml_table_support' => true,
    'xhtml_send_sms_string' => 'none',
    'xhtml_send_mms_string' => 'none',
    'xhtml_supports_file_upload' => true,
    'xhtml_file_upload' => 'supported',
  ),
  'ajax' => 
  array (
    'ajax_support_javascript' => true,
    'ajax_manipulate_css' => true,
    'ajax_support_getelementbyid' => true,
    'ajax_support_inner_html' => true,
    'ajax_xhr_type' => 'standard',
    'ajax_support_full_dom' => true,
  ),

  // snip
  
  'display' => 
  array (
    'resolution_width' => 240,
    'resolution_height' => 320,
    'columns' => 15,
    'max_image_width' => 229,
    'max_image_height' => 300,
    'rows' => 6,
  ),
  'image_format' => 
  array (
    'wbmp' => true,
    'bmp' => true,
    'epoc_bmp' => true,
    'gif_animated' => true,
    'jpg' => true,
    'png' => true,
    'tiff' => true,
    'transparent_png_alpha' => false,
    'transparent_png_index' => false,
    'svgt_1_1' => false,
    'svgt_1_1_plus' => false,
    'greyscale' => false,
    'gif' => true,
    'colors' => 262144,
  ),

  // snip
 
  'sound_format' => 
  array (
    'wav' => true,
    'mmf' => false,
    'smf' => false,
    'mld' => false,
    'midi_monophonic' => true,
    'midi_polyphonic' => true,
    'sp_midi' => true,
    'rmf' => true,
    'xmf' => true,
    'compactmidi' => false,
    'digiplug' => false,
    'nokia_ringtone' => true,
    'imelody' => false,
    'au' => true,
    'amr' => true,
    'awb' => true,
    'aac' => true,
    'mp3' => true,
    'voices' => 64,
    'qcelp' => false,
    'evrc' => false,
  ),
  
  // snip

 )

We can immediately see a lot of useful information there, such as the exact make and model of the handset, the screen dimensions, and the various sound and image formats which the device supports. Note that I've snipped the output considerably there, as the real object contains a great deal of data. Some notable items I've left out include:

• Level of J2ME support
• Details of MMS, SMS and Wap Push capabilities
• DRM support
• Known bugs

To see the object in its entirety, feel free to query the database using the form I have hosted here. This is a very slightly modified version of a tool which ships with Tera-WURFL, and should give you a feel for the level of detail you can expect.

Performance

I mentioned earlier that actually querying Tera-WURFL is pretty quick and efficient. To see why, we'll need to look at what happens behind the scenes.

Here's some ad hoc performance stats for the Nokia N95 we looked at just now:

Time to load tera_wurfl_class.php:0.004951000213623
Time to initialize class:0.00052809715270996
Time to find the user agent:0.5135498046875
Total:0.51902890205383

Total Queries: 95

I know what you're thinking: half a second is a little sluggish. No wonder, when we're making ninety-five queries! But let's hit 'refresh' and try once more. The output for a subsequent query is as follows:

Time to load tera_wurfl_class.php:0.0053188800811768
Time to initialize class:0.00048208236694336
Time to find the user agent:0.00093793869018555
Total:0.0067389011383057

Total Queries: 1 (Found in cache)

That's more like it. As the initial generation of the Tera_Wurfl object is so query-intensive, Tera-WURFL (since version 1.5) caches it in a dedicated table as a serialised string. That means that subsequent requests for the same user-agent are reduced to one single-table query against a primary key, which is about the swiftest thing you can do with a database[1]. Combine that with MySQL's built-in query caching and we're really flying.

Applications

Of course, how you actually put Tera-WURFL to work for you is up to you. You may choose to use it to automatically tailor wallpapers and other graphics to screen sizes, to determine whether or not a user can support your J2ME app, or adapt markup to specific mobile browsers. In fact, a future post may look at Wall4PHP, a tag library which, handily enough, comes bundled with Tera-WURFL, and can be used to automatically adapt mobile web pages to the browser on which they are being viewed.

Still, for the time being, I hope this has given a reasonable introduction to what Tera-WURFL can offer the mobile developer.

[1] It will be even faster if we change the table storage engine to InnoDB, as opposed to the default MyISAM. This is because InnoDB's use of clustered indexes makes lookups against primary keys extremely efficient.

Herding Cats

For as long as anyone can remember, the term "herding cats" has been used as an analogy for the challenges involved in managing developers. The implication being, of course, that developers tend to be smart, wilful, single-minded folks. Personal experience suggests this is often the case.

The analogy was reflected in the title of a book named "Herding Cats: A Primer for Programmers who Lead Programmers", written by the impressively named J. Hank Rainwater. I mention it because this is a decent read for anyone who develops, or who works with developers - whether in a management capacity or not. It's not in the same league as "The Pragmatic Programmer", but I'm getting off the point now.

So anyway, I came across this video via Yahoo MySQL guru Jeremy Zawodny's blog. It's so slickly made that you're not surprised when it turns out to be an ad for a big expensive professional services company. But I liked it.

MySQL versus PostgreSQL: Adding an Auto-Increment Column to a Table

The bulk of my database experience (almost eight years now) has been with the popular open-source MySQL database management system. MySQL has progressed significantly over the years, and has grown into a remarkable product. It finally has all the must-have features such as views, stored procs and referential integrity, coupled with the blistering performance for which MySQL has always been known. In short, it rocks.

But I digress. I've recently been having to get to grips with PostgreSQL (an old version of course - 7.1 or so - just to make life really interesting). It's largely intuitive, but there are quirks around most corners. This is my favourite so far: I recently needed to add an auto-incrementing integer "id" column to a table.

MySQL

This sort of thing will be second nature to MySQL developers:

One SQL command - not bad.

PostgreSQL

It turned out not to be so easy with our Postgres installation. For a start, there are no auto_increment columns. So, as with several other major RDBMSs, the solution is to create a "sequence", which is kind of like a pseudo-table which acts as a counter:

Next, we have to add our new column to the table, and specify that for each new row it should take its value from the sequence, using the NEXTVAL() function. For reasons best known to the Postgres guys, you can't do this in one step:

And then:

We're getting there. We now have an auto increment column. The problem is that Postgres won't backfill this with values automatically: all pre-existing rows are currently null for this column. Let's change that:

Job done. Well, some time later, the job will be done. That final step is one of the slowest things you can possibly ask Postgres to do. For a mid-sized table (around 5,000,000 rows, with a handful of small numeric and text columns), that took about 2.5 hours on powerful hardware - so you'll want to leave this for a quiet time. Fortunately Postgres treats the UPDATE as an atomic transaction: nothing is committed until the command completes, so it will be difficult for you to leave the data in an inconsistent state.