SQL Domains

I’ve not come across SQL domains before. But this blog post, “Or you could use a domain,” nicely shows how you can use them in a database to encapsulate a data type. It seems a bit like using typedef in C—you can’t create new types, but you can create similiar ones based on preexisting ones. Although domains allow you to have much more interesting constraints, looking at the documentation for CREATE DOMAIN.


Bloody Software

Over Christmas, my server has mostly been turned off. This is because it’s in one of the guest bedrooms and my mother who was staying in there didn’t appreciate the noise.

On the day after boxing day, we travelled up to the in-law’s for a few days. And so I switched the server on and ran out of the door. Unfortunately PostgreSQL failed to start up correctly (a recent change to the FreeBSD ports system, to which PostgreSQL had not been adjusted). Thanks to the marvels of PHP, this blog just displayed a database connect error. Including the database username and password in the clear. How screwed is that?

What’s worse is that I kept getting “connection refused” when attempting to connect from the in-law’s broadband connection. I have no idea why, but I suspect that some filtering is occuring in their router or at BT Internet. Grrr. So no way to fix it until I got back, just now.

So, the sooner I can get Typo up and running instead, the better. It won’t work without a database, but I very much doubt that it will default to spamming my username and password to the world… Good job I don’t use that one elsewhere.



After getting fed up with FastCGI, I baked some cookies instead. Gingerbread is lovely.

Cookies ready to go in the oven

The baked cookies



I spent a little while yesterday attempting to work out how to get FastCGI installed. Unfortunately, because I’m using Apache 2.2, this wasn’t straightforward. In fact, I’d pretty much given up. Later on, I found this patch on the fastcgi-devel list, which purports to work (and looks like it will, given my experiences yesterday).

All this is just so that I can sensibly run Typo, a rails app. There are other ways to do it, but they’re less preferrable. I could use plain CGI, but that’s dog-slow. I could set up lighttpd, but I’d rather not, seeing as I’m already running Apache for so many other services. I suppose that I could run the standalone script/server tool and use mod_proxy to connect to it. But I that means that I then have to ensure that the process stays up the whole time and gets started correctly. Overall, FastCGI seems like the best choice.

So, today I’ll have a go at plumbing that patch into the FreeBSD port.


Blogging Software

It started innocently enough, but as usual turned into yak shaving rather quickly. I wanted to try out some graphical editors for posting to my blog. Sounds simple enough. Except that a while back, I chose wordpress-pg. Its a version of wordpress, but it talks to PostgreSQL instead. Except that for some reason, my copy doesn’t have the xmlrpc.php file needed. So heading over to the wordpress-pg site, I am greeted by this lovely message.

WARNING! This version is probably subject to flaws in xmlrpc handling. Delete xmlrpc.php if present in your install. This flaw can allows outside attackers to execute arbitrary commands as the user your web server runs as.

Oh dear. So I won’t be doing that then. But part of the problem is that it’s already fixed in wordpress 1.5 (MySQL edition). Sadly, wordpress-pg is a very inactive project. I know, it’s open source, fix it yourself. But right now, I more or less just want something to work. And I don’t want to delve into scary php if I can help it (I know, that’s rich coming from a Perl programmer). So what are the choices?

I could look at getting MySQL installed and moving over to wordpress proper. Well, I’ve finally gotten around to doing some MySQL at work and I now understand it a little bit, even if I don’t like it that much. But I still have to get the actual database ported from PostgreSQL to MySQL somehow, which seems jolly arduous.

The alternative is to take a look at Typo, a Rails powered blogging engine. The selling points are that it appears to be actively maintained, it’s written in Rails (which I wish to play with) and also that it has a wordpress import script. So I’ve just spent an hour attempting to convert my wordpress-pg install over to typo. And I have to say that it works pretty well. Here’s what I found.

Firstly, I followed the basic install to get typo up and running. I saw Piers complaining about it, but it’s reasonable for me. But I’ve used rails before, so I’m probably not the target market. Anyway, I got to the admin screens and set up a test account pretty quickly. I then ran the import script giving it my old wordpress database name. Needless to say, it didn’t work first time. I had to make these changes to the script:

  1. Get rid of all backquotes, that’s some weird MySQLism.
  2. I had to change all entry['ID'] to entry['id']. PostgreSQL downcases things.
  3. I had to rework the trackback support slightly. Because wordpress-pg is based on wordpress 1.2, trackbacks are stored as a comment that starts with <trackback /> at the beginning. So I had to change comment_type = 'trackback' into comment_content LIKE '<trackback />%'
  4. The import script runs in the dev environment by default, but I had been testing in the production environment. This meant I had no users set up in my dev environment. Consequently, I had to patch up the relationships between the users and the articles afterwards (update articles set user_id=1;). Actually, looking at the script, it doesn’t seem to make those links anyway…

The biggest problem was actually getting the script to access the original data. The script assumes that you can do the MySQLish thing of accessing another database by specifying dbname.table. This doesn’t work in PostgreSQL. But, you can take advantage of the fact that PostgreSQL does support schemas, so instead, you can create a schema called wordpress and load a dump of the main database into there. I had to make a few edits to the dumpfile before loading it in though.

  1. Get rid of the stuff about plpgsql at the top. I don’t use it.
  2. Add CREATE SCHEMA wordpress and SET search_path = wordpress, public, pg_catalog.
  3. Comment out all the ALTER foo SET OWNER TO wordpress lines. You have to import it as the typo user and don’t want that changing behind your back.

After the import you can just run DROP SCHEMA wordpress CASCADE to clean up.

After that, I now have a working blog with all my old content. Hurrah! Not simple, but not too difficult either.

Now before I get this main blog converted over, I just need to work out how to run FastCGI scripts from Apache, something I haven’t done before. And then I’ll be away, and I can uninstall wordpress-pg.


Komedia Night Out

I’ve just been to Komedia as part of my partner’s work’s night out. Two of the acts were OK, but Raymond & Mr Timpkins Revue were superb. A couple of guys putting words to music. Very little spoken stuff, exceedingly silly and still requiring an amount of thinking. They kept your mind racing trying to keep up with all the gags playing along, while your mouth simply carried on laughing.

In short, well worth seeing if you’re anywhere near them.


Mud + Karts

For work’s Christmas do, we all went off to Mudmania this afternoon. What an excellent way to spend the day, getting very very wet and muddy inside 400cc engines. Lovely. And then it’s off to Indian Summer for some nosh later on. Yum. Happy Dom.



TRAMP is a tool for Emacs to let you edit files on another computer, using only an ssh connection. It rocks, as it lets me edit files at work from my laptop at home. Behind the scenes, there’s some serious voodoo going on, encoding and decoding files on the fly, transferring them to the appropriate place and so on. But it’s all 100% transparent.

But what’s really made my jaw drop is that it integrates correctly with vc-mode. I have a file that’s checked in to RCS on the remote machine. Without thinking I hit the checkout button to start editing the file. And it worked! I totally wasn’t expecting that. But it’s damned useful.

In summary, if you use Emacs, you need TRAMP.