Dots

Last week Dan and I were sitting down at lunch and we somehow got onto the topic of creating a website that just printed dots to the screen. As usual we got a bit carried away with the idea and decided that each dot could be an object and could be stored in a database and accessed through an API so that all created dots were stored for the future.

What began as a silly idea at the lunch table soon turned into a quick fun little project. Dan wrote most of the front-end code, I wrote most of the back-end API stuff, created the database, and registered a domain pointing to my VPS. We posted about it a fair bit over the weekend and it turned out to be quite popular among a couple of hundred people.

We hit our target of a million dots in a little over 2 days, and were both amazed it happened so quickly. Especially when you consider there is little-to-no reason for anyone to visit the site at all.

Anyway, we’re making changes to bits and bobs all the time at the moment. You can see the stats for your browser as well as the total on a new page, and there might be other little bits of analysis coming up in the future if we don’t get completely bored and do something else instead.

Create some dots.

New job

Since Cheryl was looking like getting a job in London I’ve been looking for one myself. I sent an application to LoveFilm after thinking about how much of a good fit it would be for my interests. They’re a Perl house, so I know the language. They’re a company that deals with films, which I spend a lot of time and money on. The Playstation 3 app kind of made my mind up because I thought that it would be something I would like to work on.

I had to complete a programming test before anything else. After doing that I thought that I hadn’t done very well, but apparently I was worrying over nothing. I was offered an interview last Friday and so I went out and bought a new suit, got a haircut, had a shave, and went down there.

The interview itself went pretty well. I had a technical test/interview with two of the developers there, and then I had an interview with the head of the digital distribution team. I felt fairly comfortable during most of this process. The only issue was the pressure of being tested on programming stuff without a computer nearby. I don’t like writing SQL on paper. I much prefer to try a query once, then adjust it if it’s wrong, but oh well.

After the interview I was told that I should hear back fairly soon, so I went away not knowing what would happen, or how long it would really take.

I met up with JonP and we went for some drinks before my train at 20:30. At around 20:15 we realised that there was no chance of me making the train to Nottingham, so JonP offered to let me stay at his. We left the Bull in Shepherd’s Bush and went to the London Cocktail Club for a couple of drinks. Then we headed to Soho to a weird bar that serves drinks infused with garlic and stuff. We each had a shot of tequila infused with chilli. It was disgusting, so we chased it down with a beer.

On the way out of that bad JonP realised that he no longer had his bag. We headed back to the London Cocktail Club, which was closed. JonP rang their phone and left a message while I banged on the shutter door. Eventually someone came to the door to clean the steps with a mop, and thankfully JonP’s bag was still in there.

We caught a bus back to JonP and Emma’s apartment and I fell asleep fully suited up. I woke up a few times the next morning, then eventually noticed that I had a missed call from my contact at the agency. He said he’d got some feedback for me, and asked me to call him when possible.

I called him back and he told me that the interview was positive and that I had been offered the job. 😀

I rang Cheryl and my immediate family to let them know how it went, and started to feel pretty happy.

We decided to go and get some Mexican breakfast at Wahaca. JonP got as far as the DLR station before he decided to go back home to sleep. Emma and I powered on through. We decided to begin the drinking straight away. We ordered a tequila and a Corona, and during the course of eating our lunch we also ordered another tequila.

At about 2:30 Emma had to go to meet some of her friends so I decided to wander around London on my own for a while.

Cheryl called just as Emma left and said that she was thinking of coming down to London for the night. We discovered that the coach would be much cheaper, and Emma and JonP were fine with it, so we made plans.

I decided that I wanted to read a book and enjoy the day. I bought A Room with a View by E. M. Forster and made myself comfortable in Costa Coffee. I was reading in there for about 4 hours while Cheryl made her way to London. JonP’s band were playing a gig in Camden so we arranged to meet over there. I set off for Camden and met Cheryl at the tube station. She’d just seen JonP walk by with his guitar, so we headed off looking for the pub.

The venue was pretty small and incredibly warm, but the gig itself was good. 🙂

After the gig we went back to JonP’s apartment and Emma was asleep on the couch. She woke up after a little while and went to bed instead. We stayed up and had some pizza and stuff from Pizza Planet, and then called it a night.

We left fairly early on Sunday so that we could have some of the weekend chilling out at home. It was a really good weekend. 😀

MySQL annoyances

I’m working on creating a table in MySQL for keeping track of some votes. I want to limit the data so that someone on a specific IP, with a specific user agent (browser) cannot vote more than once for a specific item per month. The constraint at the table level is just to stop it filling up with false entries submitted by script. There will be client-side constraints too, for regular users.

To set this up I created a table for the votes with the following fields: vote, ip, ua, item_id, created_at.

I decided I could probably do it all with a simple UNIQUE KEY, but half way through writing it I realised that I only wanted part of the created_at field to be taken into account because people should be able to vote for the same item in another month, but not another day in the same month. I wondered if I could include a function in the unique key like this:

UNIQUE KEY `vote_constraint` (`item_id`, `ip`, `ua`, YEAR(`created_at`), MONTH(`created_at`))

Sadly that’s not allowed.

I decided to include year and month columns in the table so that I could stick them in the unique key without using a function. Since the month and year will always be the current month and year I figured I could set up a clever default based on the created_at value, but that didn’t work either. MySQL only allows you to use constants as defaults (barring a couple of half-constant keywords like AUTO_INCREMENT and CURRENT_TIMESTAMP).

I looked into triggers, which I have never used before. The theory is that you can create a trigger that is called when certain events happen. In my case I would want to create a trigger that is called BEFORE INSERT on the table that sets the year and month values to the right thing. This would allow me to insert a row and get values inserted by default. Frustratingly MySQL doesn’t really give a crap about the name of the trigger. BEFORE INSERT actually gets called AFTER checking whether the data you’ve passed is valid or not. So I’d have to pass in a valid value for each field before the trigger (that would create valid data) would even be considered, rendering the entire trigger pointless.

There are many reports of this being an issue over the last 7 years or so (yeah, honestly), and there have been patches submitted to fix this since May last year, but it’s not out there in the current version, and it might not appear for quite a while in the stable MySQL we use at work.

So despite everything it seems like I might as well just create the values in my Perl script and pass them through to MySQL to save it having to think.

Love SQL

Something I’m working on at the moment requires a rather large collection of data to be moved from several places into one clever little table that I’ve created. It’s going to consolidate functionality and make things much easier to script in a few different environments.

Gathering all of this data together has been made pretty damn easy by using a few choice SQL queries. A few months ago I would probably have written a Perl script to do this instead, but that’s really not necessary.

This first query performs a SELECT on one table and uses the values to populate the INSERT statement values. It’s quite simple because all of the required data is in one table on the same database as the table it’s being copied to. All field, table, and database names have been changed to protect the innocent.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, email_address_1, email_type FROM prefs WHERE email_address_1 IS NOT NULL;

The second query is a bit more complicated. It performs a SELECT on a table in one database that does an INNER JOIN on a table in another database and then an INSERT to get all of the collated data into a different table in the first database.

INSERT INTO clever_table (id, person_id, email_address, email_type) SELECT NULL, person_id, people.email, email_type FROM prefs INNER JOIN other_db.people ON prefs.person_id=people.id WHERE prefs.password_reminder=1;

I was quite pleased to find this worked first time when I tested. It doesn’t seem complicated to me now, but I know it would have done before I started working where I do now.

First day with Perl 6 – UK postcode grammar

Today I decided to install and play around with Perl 6. It’s the developing version of Perl that is meant to replace Perl 5 in the future. It’s still under very heavy development, and is not ready for general use, but I thought I’d give it a quick look. Perl 6 is not backwards compatible, which means that if you’re used to writing things in Perl 5 you’re in for a shock.

I decided a good first test of something to write would be a UK postcode parser/validator in order to try out something new to Perl 6, or at least to me: grammars.

Grammars are basically a way of relating several rules or regexes to each other in specific ways. By the way, you can’t call Perl 6 regexes “regular expressions” any more because they’re apparently not regular enough.

After a bit of playing around I actually managed to get the following code working:

grammar postcode {
  regex TOP {
    <outcode> ' ' <incode>
  }

  regex outcode {
    'GIR'|<[A..PR..UWYZ]>(\d**{1..2}|(<[A..HK..Y]>\d|<[A..HK..Y]>\d(\d|<[ABEHMNPRV..Y]>))|\d<[A..HJKS..UW]>)
  }

  regex incode {
    \d<[ABD..HJLNP..UW..Z]>**{2}
  }
}

my @postcodes = 'WC1N 2PL', 'NG18 3JL', 'L1 4DJ', 'GIR 0AA';

for @postcodes -> $postcode {
  print "$postcode: ";
  if postcode.parse($postcode) {
    say 'Yes!';
  } else {
    say 'No!';
  }
}

I think that the few examples I’ve put in there represent the vast majority of postcodes out there, including certain London-style postcodes and the GiroBank postcode (GIR 0AA). If you want to play around with this then you can just add extra postcodes to the array and they’ll be iterated over in the for loop.

There are a couple of interesting differences in the syntax of the Perl above compared to Perl 5. The if and for statements have no parentheses around the condition. This is because parentheses directly after a word represent a subroutine. Also, to use a different variable as the iterator in a for loop you now use the -> construct after the array name (or range, or whatever you’re looping over).

There are loads of other differences, but I haven’t really had much of a chance to play with them properly.

So far I’m liking Perl 6 a lot. 🙂