Rubénerd!

I've been blogging for long enough to have suffered a couple of disastrous data losses, but only once have I irreversibly lost stuff. Consequently I backup my blogs daily, but on a hunch early this morning I decided to test my backups on a local installation of WordPress. I choked!

Every evening my server is configured with a couple of cron jobs to do these backups which then get the bzip2 treatment:

• An SQL query
• A WordPress RSS/WXR file
• A grilled cheese sandwich with avocado and gherkins

The SQL backup is fairly vanilla stuff, and always works, as one would expect. WordPress's automatically generated RSS/WXR files are much easier to work with, but from repeated painful experience over many years they're unreliable as heck. Perhaps I should phrase that to say "easier to work with... when they work"!

You're telling us this... why?

I belabor all this because when I tested the WXR files my server has been exporting lately, they don't include categories when you reimport them into vanilla WordPress installs. None. Nada. Zippo. This despite setting the PHP memory ceiling higher as I talked about before and splitting up the WXR files as recommended by various folks.

Despite being introduced years ago, WXR is still horribly broken. As far as I can tell from trudging through the source WordPress doesn't even use a XML parser when importing them. I suppose that's another reason why I don't use sites like WordPress.com, when things like this mess up I can always access the database directly, and why on smaller projects I use SQLite3 databases which you can cheat on and backup by just copying over one file! Ah and I'm nostalgic for sqlplus already ;).

What do you guys do for data backups for your online stuff?

It seems as with many technologies, regardless of your own abilities or competency if you’re using the "wrong" implementation you’re perceived as an amateur, and vice versa.

For example, I've read so many disparaging remarks by rude people on forums saying that people who run Ubuntu and Fedora are amateurs, and while I admit to being a FreeBSD and Debian guy I still replied by asking them to prove the automatic connection they were alleging. The inventor of the Linux kernel uses Fedora, so clearly their blanket assumption was false!

Anyway I'm getting sidetracked, in this case I wanted to talk about databases. I was implementing a simple website for a client that only involved one editor and a limited number of pages, so I opted to use SQLite instead of MySQL (or PostgreSQL) because its far simpler to implement, doesn't require a client/server connection and under the limited loads they'd be experiencing it could be faster.

I was so impressed by how well it performed and how easy it was to use I've started looking at other situations where it may be useful instead of just assuming everything should use MySQL. My blog here for example only has one editor and a limited number of tables with very simple foreign key relationships and a few thousand tuples. WordPress doesn't support SQLite natively, but I've been writing some basic blogging software of my own in Django and Ruby for a long time and when I started using SQLite with them I was really impressed.

But back to the client I was talking about before, no sooner had I proposed the solution and had a working prototype than someone working on another part of the project informed everyone I wasn't taking the task seriously because I was using SQLite which isn't a proper database. I didn't want to start a fight so I heaved a heavy sigh and rewrote the system in MySQL which I still maintain is far too heavy and has features that will never be needed for a database with only three tables and less than a hundred tuples each, but at least now I've been told I've corrected my childish approach.

The person did have a point, SQLite does implement only a subset of the features and SQL commands of MySQL; for example it doesn't use static typing though it can be worked around by using constraints. I also concede the possibility that in the future their needs may necessitate a more feature complete SQL implementation. To me though that's akin to Microsoft claiming Vista was better than Mac OS X Leopard because it was more efficient when using nine trillion cores, or that a Hummer is better than a bicycle because you can haul drums of nuclear waste in it more easily.

I guess I just like elegant systems that have a comfortable ceiling for growth but that at the same time are simpler and therefore easier to maintain and use. Einstein said "Make everything as simple as possible, but not simpler", and then there's the adage of the "right tool for the job.". Perhaps I just need to get used to eating humble pie more often and accepting that I'm working with other people with their own needs and expectations. Still, it'd be nice if people would stop assuming things just because of the tools you use.

Aside: I've hacked together a way to post blog entries from the computer pools at the university, but it's such a long and convoluted procedure I still won't be doing many of them until I get our home internet connection fixed.

Update: It's been brought to my attention that not defining primary keys as not null does have an impact after all, refer to Dale McGowan's comment below. Suffice to say, I will continue to define them as not null from now on!

Much like a musician who despite getting the right sound realises they've been playing their instrument wrong throughout their career, I often find I'm not using computer software properly or I've been wasting effort. I guess I get myself into a predictable routine that I'm used to.

In this case I found out something about Oracle databases. I've been using them for years, but it took a practical class at university yesterday to realise I've been wasting time with a simple line of code that was completely unnecessary.

If you do any sort of work with databases you'd probably want to smack me on the head for this, but I didn't know that primary keys are automatically assigned as not null; presumably because it doesn't make logical sense and it would break a table to have a primary key that has null values!

Up until Friday this is the schema I would have created and entered into an Oracle database:

create table Jackarse (
  refID char(6) not null,
  name varchar(32),
  grilledCheeseSandwich boolean,
  primary key (refID)
);

And here's what I've since learned I can do instead:

create table Jackarse (
  refID char(6),
  name varchar(32),
  grilledCheeseSandwich boolean,
  primary key (refID)
);

That's right, both these schemas will result in the same table being created in Oracle. I guess it wasn't harming anything having the not null command there, it was just unnecessary.