Loading MySQL data for phpunit testing

Posted on February 9, 2011 by james

I’d been bugged for a while because when I was writing phpunit tests for some of my code, I couldn’t find a tidy way of pre-loading data into a MySQL database for testing against. Everything I came up with seemed really kludgy and the documentation hadn’t really enlightened me much.

Eventually I resorted to reading the code for phpunit and discovered that it can load a database from an XML dump, which can be created directly from mysqldump. This means that if I have a database containing a large amount of static data that I need to test against I can first do:

mysqldump --xml ... > test-data.xml

and as long as the database is created my phpunit test classes just start as follows:
<?php

require_once 'PHPUnit/Extensions/Database/TestCase.php';

class ExampleTest extends PHPUnit_Extensions_Database_TestCase
{
  protected function
  getConnection()
  {
    try {
      $pdo = new PDO('mysql:host='.DBHOST.';dbname='.DBNAME, DBUSER,
          DBPASS );
    } catch ( PDOException $e ) {
      throw ( new Exception ( $e->getMessage()));
    }
    return $this->createDefaultDBConnection( $pdo, DBNAME );
  }

  protected function getDataSet()
  {
    return $this->createMySQLXMLDataSet(
        dirname(__FILE__).'/test-data.xml' );
  }

  // test case functions follow

which is far neater than the previous mess I was using to initialise the database.

Posted in Computing | Tagged , , , | Leave a comment

Searching for the longest match with MySQL

Posted on February 9, 2011 by james

I’ve been involved with Carbon Guerrilla, a project to allow organisations to manage and track their CO2 emissions for some time now. Yesterday I was working on some code to allow people to compare emissions across locations by re-basing emissions from heating by degree day values for the locations in question. The degree day data is based on splitting the UK into eighteen different regions and I had a table of mappings from postcodes (actually prefixes of postcodes) to those regions, so to find the correct region for any given location I wanted to find the best match in the postcode table, where "best" means longest in this case.

Google came up with some very complex ways to find the longest match, apparently mostly relating to finding telephone number prefixes for billing (probably from asterisk), but I couldn’t believe there wouldn’t be a relatively simple way to do it. In the end I came up with this:

SELECT regionId FROM regionByPostcode WHERE <location-postcode>
LIKE CONCAT( postcodePrefix, '%' ) ORDER BY LENGTH( postcodePrefix)
LIMIT 1

I’ve not managed to break that in testing yet and I think I’m happy with it. I’m only matching against a few hundred postcodes so it’s never going to be too slow. I’m not sure about performance if it had to match against many millions though.

Posted in Computing | Tagged , | Leave a comment

Sortable list of Messier Objects

Posted on February 8, 2011 by james

Although I’ve been interested in the stars for years, I’ve only recently become a telescope owner.  Despite the lack of clear nights since receiving my new toy, I’ve spent some time looking at the obvious things like the moon and whichever planets happen to be in the right place at the right time.  So far I’ve managed to find Jupiter and Venus (both fairly obvious because they’re so bright), Saturn and Uranus (more by luck  than judgement if I’m honest: it was very close in the sky to Jupiter at the time and easy to “star-hop” to from there).

That done, I’ve decided that it would help “get my eye in” and I’d get some useful practice at using the scope if I started hunting for some of the easier Messier objects; a list of 110 deep space objects catalogued by Charles Messier getting on for 250 years ago.  He was actually interested in finding comets (and did find quite a few), but this list was all the things he’d found that he was sure weren’t comets.  Some are other galaxies such as the Andromeda Galaxy, some are nebulae where stars are dying and others forming (like the one in Orion’s sword) and others are just dense groups of stars (the Beehive, in Cancer, for example).

Hunting specific deep space objects requires a little planning, so it was going to be useful to know which DSOs were in which constellations and how bright they might be.  My telescope isn’t huge, and unless the air is exceptionally clear it just wouldn’t be possible to find some of the fainter ones.  Some of the constellations aren’t visible at all times of the year, either, so there’s no point looking for objects in Orion, for example, in the middle of summer.  Wikipedia has a full list of the Messier objects, but it doesn’t sort very well, so I created my own.  I’m sure it’s possible to do in Open Office or Excel, but I’m not particularly competent at driving either of those, so I stuck the data in a database and built a web-based sortable table of Messier Objects instead using PHP and Javascript. When I have another few minutes spare, I’ll add more data for each object such as the common names and Right Ascension/Declination.

Posted in Astronomy, Computing | Tagged , , | Leave a comment