Extending SQL::Abstract


Most of the perl I write currently has ties into DBIx::Class and hence uses SQL::Abstract.

I also have far too much of a preference for boolean items, which I normally encode in the database as a column of type boolean (or the SQLite vague equivalent).

Its been fairly easy to encode a test for boolean value being true with SQL::Abstract – although the syntax

column => \''

which maps to

WHERE column

is a little esoteric. However its downright close to impossible to produce the opposite test without using literal SQL (actually the first version uses literal SQL – except the SQL statement is NULL and comes after the column reference).

So today I have spent a little time extending SQL::Abstract to support the -bool and -not_bool unary operators which allow both positive and negative boolean tests to be encoded in a way that does not resort to literal SQL.

As part of this I refactored a part of SQL::Abstract so that further extensions of this type can be added more easily – adding a unary_ops extension to the constractor in the same way as the previously existing special_ops.

So now:-

-bool      => this_column,
-not_bool  => that_column

will map to

WHERE this_column AND NOT that_column

The code for this is currently sitting in a svn branch – please have a look and comment on this. My own criticism of it is that the syntax is clunky, but we are restricted to the perl datastructure mapping approach of the existing code (which is the real raison d’etre of SQL::Abstract).

Theatre Management with Perl – 1


First some history – in fact this post is almost all history and setting the scene for what we did later.

When I joined the theatre there was a website – however it had been done by someone’s friend, and was a flash only movie containing information on the shows that were coming in the near future.  This suffered massively from being done in spare time and could take months to get updated, so something had to be done.

The first aim was to get a website that we could get event information onto with as little ongoing maintenance as I could get away with.   Unfortunatly the event information we did have was in Excel spreadsheets – and this was arranged in colour coded tabular form (one column per month, one cell per day) which was likely to be hard to parse automagically.

I decided the easiest way to handle the event information was to use an Excel spreadsheet – this would allow other members of the theatre technical team to update the events too.  Each event (which can cover several performance dates) had a single line, with several fields describing it.

A piece of perl (yup, we have finally got there), parsed out the events (Spreadsheet::ParseExcel) and built a set of per-month event pages as well as a front page with current events on it – using HTML::Template.

The page generation was done off-line and the generated html pushed to the webserver – at that time we had to take this approach as the web server was separate to the machines with the appropriate tools to generate stuff.

As a fairly quick and dirty hack this worked extremely well – so much so that it stayed in place for more than 18 months – you can see examples of this on the way back machine – ie this one from March 2004.  Meanwhile we were starting to wake up to the idea of having an events database – even one as primative as the one we had – and realising we could start to use this for our internal backstage management.

We had started to use a wiki for much of the backstage management – we have half a dozen stage managers plus other crew, all volunteers and organising all of us is not an easy task.  The wiki chosen was MoinMoin – at that time I could not see any perl wikis that I was happy with.  The wiki contained another list of events – this time in a way more focused on periods the theatre was booked (which is different to performance dates – it includes rehearsals and some events that have no performances such as training events).

So we were duplicating information – and I was updating a lot of that information I felt this was very much a bad thing.

So I started looking at producing a proper database and bringing a lot of this information together….

Gadget Drooling


Spent yesterday at PLASA Focus at Leeds Royal Armouries.

We went with a fair shopping list – both Lighting Desk and Sound Desk as well as considering what to do about Radio Microphones (the Government playing silly buggers with frequency allocations makes this one a tricky prospect).

We also ended up considering smoke and haze machines – our own is somewhat long in the tooth, and we are considering keeping this sort of equipment in house but charging hirers a fee for use.  The hirer should get kit cheaper than it would cost to commercially hire, plus we would know the equipment, have a revenue source to pay for it, and the kit is likely to be in much better kit than most of that you get from commercial hire.

Recommendations for a lighting desk would be helpful – we currently have a Strand GSX (ie ancient), and that works quite well for us as its fairly simple for our staff to learn to use, and is good for knocking up lighting states on the fly (we have our basic groups of lights on subs which allows a very fast setup).

So we need something with the following characteristics:-

  • Able to handle 80 dimmers plus misc other DMX kit
  • Able to control a few moving heads (we do not have any, but occaisionally hirers bring them in, and if we have the capability then we are more likely to buy some in future).
  • A set of 20 or so sub faders to allow fast immediate use (or show us another fast way of working)
  • Ideally somewhat similar feel to the Strand to make transition easier
  • Ideally with some degree of remote capability so we do not need to move the whole desk when working in the auditorium
  • We have a fairly restricted space in the stage corner where the desk currently resides.  We could reposition, but ideally it would fit into a similar space to a GSX (not too much bigger).

The ones we looked at (only briefly) yesterday were (in no particular order) the Avolite Pearl, the ETC Ion and Element, the Zero88 Juggler TLXtra (actually I wonder if one of their other desks would have been a better fit).

If anyone has any suggestions then drop me a line or leave a comment.

Enlightened Ironman?


Following the blog post and proposal from Matt Trout, I’m going to aim to push out some posts on my uses and experiences of perl.  I’m extraordinarily unlikely to hit the Ironman rating since a look back at my history shows the occaisional year between posts 🙂

Anyhow, as an ancient perl user (I pulled a perl release from usenet back sometime around 1988/89), I have quite a lot of odds of perl around, although mostly I have been pretty good at retiring the stuff that has outstayed its useful life.  In recent years there have been 2 main threads of work I have been doing with perl:-

  1. Stuff related to my day job – mostly a combination of glue code and some overall product management code.
  2. Theatre back office applications – ie website management and backstage allocation/management.

The 2 sets of code actually share a considerable amount of ideas – I tend to take more risks with the theatre code, and often pull ideas from there into work code.  However in both cases there is core of stuff that is based around DBIx::Class and to a lesser extent Catalyst.

The next few posts are going to outline the theatre code in particular – what the requirements were and how I went about implementing them.  There is a degree of history there – its been an incremental addition of functionality over several years, and if I were starting from scratch I may well not end up with the same system now, but it works and I am reasonably happy with it (although I have plans to improve it further as time allows).



Smoke is becoming very much a theme of this week’s production.

The strange thing about managing these show runs is the way the work pans out.  Typically I am fairly busy during the build period, very very busy during the lighting and technical phases, busy during the rehearsal phase and then relatively lightly loaded during the actual performance run (obviously during the performance itself I have work to do but thats only half the evening, and the time before the performance is quite low impact for most shows).

So for this week, from Tuesday when the show opened, I was expecting the before performance period (ie from 6pm until 7:30pm) to be relatively light.

Wednesday I had to replace 3 auditorium lighting roof bulbs – and 2 of them were under walkways in the roofspace meaning it was a struggle to replace them, and I came down from the roofspace completely covered in dust and worse.

Thursday the fire alarm stopped working.  This show has 4 segments which create significant amounts of smoke.  The alarm would not allow me to do any privaleged commands (ie my key had no effect on it) – ie if it went off I would not be stop it, and I could not isolate any smoke detector zones.  Cue many phone calls.  Cue me looking for the director to tell her we might need to have an electric railway form of the play with no smoke at all.  Finally a couple of minutes before the audience were due in we dismantled the control panel, and manually actuated the switch that my control key should be prodding, which allowed us to switch off the required detector zones.

The show was saved.  Much stress was generated.  Blood pressure up, weight probably down (so one good result).

Then at 10pm we had to dismember the panel again to reset the system.

Very much a night when you wonder quite why you are doing this thing unpaid in your spare time!



I’m stage managing the musical version of The Railway Children this week.  The producing company has seen fit to obtain 4 (really!) smoke machines (and a hazer).

Obviously with all this kit to play with – 2 of the smoke machines were big brutes with DMX interfaces – we needed to test them out.  So I rigged one of the DMX controlled ones at the front of stage, set its address, isolated the fire alarm zones covering auditorium, stage, backstage and roof space.  The initial test went swimmingly – and I then found that if you ran it at 10% the machine gave a puff of smoke every few seconds with a fairly realistic train noise.

Unforunately this smoke was coming off the front of stage and dropping straight into the orchestra pit, and I then watched with sudden horrified realisation as the smoke was pulled straight back into the newly installed air recirculation system which pulls air in at the back of the orchestra pit and then pumps it back through the heating system.  That circuit has another smoke detector in it – on a different zone.  2 seconds later the alarms went off!!!

Lesson learned: now we need to switch off a total of 4 of the 6 zones within the theatre whenever smoke is used on stage.

So what happened?


Somehow I have been absolutely snowed under for the last 2 months…
The good bit is that I have the next 10 days on holiday – which means no blogging whatsoever (no change there then).
Hopefully I should be able to actually write something when I get back, although I am starting off with the get in and running of a musical.