It’s far too long since I’ve posted anything here. I’ve no excuse really. Following the end of my contract in Canary Wharf, I was off work for seven weeks. OK, I was on holiday for two of those weeks, but that still leaves five weeks when I could have been doing something constructive, but actually just spent a lot of time watching Netflix.
But there were a couple of things I did. Neither of them took more than a few hours, but I thought it was worth writing them up – if only to give an example of a couple of really useful (to me, at least) things that I was able to build really quickly with Perl.
Cooking Vinyl
If you were a music fan in the 1990s, then there’s a good chance that you own at least one album released on Cooking Vinyl Records. At times, it seemed like pretty much every album I bought was released by them. Back in 2005, I wrote a blog post where I tried to explain how much they meant to me.
In particular, they produced a series of compilation albums that introduced me to so many of my favourite acts. Ten years ago, I tried to find a definitive list of all of the songs and artists which appeared on those compilation albums. As I failed to find one, I created it myself. At the time, it was a static list of albums which listed the tracks and artists on each of the albums. For ten years I’ve had it in the back of my head to do something more interesting with the data. A few weeks ago, I finally got round to it.
As I said, the original page just had a list of albums with artists and song titles. That’s useful, but it would be more interesting to be able to cross-reference the data in various ways – list all of the albums that an artist appeared on, for example. And for that, we need a database.
If you’ve come on any of my database training course over the last ten years, you’ll know that I use a CD database example. The model that I use is pretty simple and, in particular, it assumes that all tracks on a given CD are by the same artist. As I say in the class “various artists compilations don’t exist in this simplified universe”. Obviously, that’s not going to work in this example. So I needed to come up with another database model.
Here’s the data model I designed. You’ll see that it all hinges on the track table. A track is an instance of a particular song, recorded by a particular album appearing on a particular album. The only extra data on the track table is the “number” column which allows us to declare the order in which tracks appear on an album.
Advanced students will have spotted an omission from the data model. An artist might well have different versions of a song. There could be the original version, an edited single version and many live or remixed versions. So actually, we could add a “recording” table and it’s the recording that appears on an album. That’s, perhaps, an enhancement for the future.
Having designed the database the rest of the code just falls out really. I already had a data file so it was just a case of parsing that and inserting the data into an SQLite database. DBIx:Class (and, particularly the find_or_create method) makes this trivial. I then wrote another program that generated the web site using the Template Toolkit. Nothing complex there at all.
The site is at http://cookingvinyl.dave.org.uk/. And all of the code is on Github. It could do with being made a bit prettier – perhaps I can add some pictures.
Why not have a look. And check out some Cooking Vinyl recordings.
Tower Bridge
I’ve lived in London for thirty-five years. And in all that time I have never seen Tower Bridge opening. Oh, I’ve seen it when it’s open, but I’ve never been in the right place at the right time to see it actually opening. As a Londoner, that’s a matter of supreme embarrassment to me.
But the office I’m working in currently is three minutes walk from Tower Bridge. All I need is a way to get a notification a few minutes before the bridge lifts. Surely, there must be a way to get that?
Sadly, no. The Tower Bridge web site has a page listing the upcoming lifts, but no service that would send any kind of notification. So, once again, it was up to me to provide one. I asked the London Perl Mongers on IRC what would be a good way to get notifications of upcoming events on an Android phone and Ilmari pointed out that the obvious method was to create a calendar that could be read by the calendar app on my phone.
So that’s what I’ve done. I use Web::Query to scrape the data from the Tower Bridge web site (doing some over-complicated madness to account for the fact that they are missing the year from their dates) and then create a .ics file using Date::ICal and Data::ICal. I also create a JSON version of the data in case it’s useful to anyone (if it is, please let me know).
The site is at http://towerbridge.dave.org.uk/ and (of course) the code is on Github.
So, there you are. Two (hopefully) little projects that I threw togther in a very small amount of time using the power of Perl. Please let me know if you find either of them useful.
