Final note on SQlite and MT

They are two wholly loathsome bits of software. I’m sure it’s possible to run a Movable Type blog on sqlite if you start from scratch, but once you have a couple of thousand entries, it’s impossible to copy everything over. Entries and comments can be exported and imported without to many problems; but all the templates, all the settings, and all the author details are also held in the database, and they are much harder to export and import. That’s MT’s fault.

UPDATED below the fold is the particular contortion I had to go through to get it working with Pair. But in practice the simplest way is to install on your own machine MT, mySQL, and the conversion script.. Since the conversion can take some time (the blogs here make a 7.4 MB file) and will certainly go wrong,1 it’s silly to do them on someone else’s machine where the process may be at any moment interrupted.

SQLite files (*.db3) can be pushed around with FTP programs. For transferring MySQL, the easiest way is to back up on one machine and restore to the other.

1 In particular, you will need to unlink linked template files before trying to convert the database; I also had repeated problems with log entries and had to purge them all.

The rest, however, is the fault of bloody loathsome SQLIte. It has been praised. Ignore this praise. It is a tool for programmers, not people. It is meant to be wrapped up in other programs which no one has actually got round to writing. The worst consequence of this is that there are three or four versions in circulation, which use different database formats. That’s right. A database made with version 2 can’t be read by version 3, and vice versa — and there is no easy way of telling from the outside which version you are using, or which version made the database. This matters particularly with blogs because the handling of dates and times changed between the two versions, and only the newest works with the conversion script for MT. Since there is — get this no way to read in CSV files into SQLIte, you will be utterly dependent on the converter script and you must get the latest version.

Which is this? Typically for open-source projects, there is a page listing all the wonderful database managers you can use with it, and most are either dead or obsolete. In fact the only one that works on Windows with the latest version is SQLitespy.

The way that MT works, the sqlite program that it uses is built into a perl module (DBD::SQLite). Version 1.11 works, since it has sqlite 3 built in; version 0.25 uses sqlite 2 — that is of course the one that Pair has set up.

So, to run the conversion, you need to install the latest version of ActiveState Perl at home, and then the latest version of DBD::SQLite, then MT and the conversion script, which goes into the mt directory. At this point, you get clever.

Rename mt.cgi on pair to, thus disabling the installation there. Do not use or even install Mysql on the home machine. Instead, set up an SSH tunnel to pair, using port 3306. In the mt-config.cfg file, tell MT that you are running a MySQL server on localhost (with the pair username and password) and point your browser at the mt-db-convert.cgi on localhost.

It’s terribly slow, of course, but that doesn’t matter. If it barfs when converting templates that are linked to files, go to the local mt.cgi and unlink all of those templates (you can always put them back later). But after about fifteen minutes, you will have the whole lot converted into a single, possibly very large, file.

change the home mt.cfg to point at this sqlite database, and test it. It should be identical to the blog on pair. Upload it to pair and —

discover that you have to install the updated perl modules yourself and they won’t. But it might work somewhere else; and, in any case the trick of connecting to a remote MT database is worth recording, I think, as is the general warning about sqlite.

This entry was posted in nördig. Bookmark the permalink.