Final note on SQlite and MT
Wednesday April 19, 2006; part of: Nerdery

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.

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 mt.cgi.off, 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 somwehre 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.

Posted by andrewb at April 19, 2006 03:25 PM
Comments
Post a comment
Textile formatting works here. Double hyphens are automatically converted to en dashes, quotes are automatically smartened. You can put dashes and asterisks around text to make italics bold and other silly effects easily.
  • Text wrapped in Asterisks which * will be bold. The asterisks must touch each end of the bold text. There must a space before the first and after the last.
  • Text wrapped in underscores - _ - will be italicised. The underscores must touch each end of the italics. There must a space before the first and after the last.
  • Paragraphs starting bq. will be block quoted. There must be no space before the "b" and one space after the full stop.
  • A hyperlink is made by wrapping the link text in double quotes, followed immediately by a colon, then the URL. If there is a question mark in the URL, wrap the whole lot in square brackets.
  • I use two classes to mark up text that deserves it. sane text looks like this. loony text looks like that. The syntax for those is %(sane)[space] sane text %; loony is left as an exercise to the reader.
Name:



Email Address:



URL:



Comments:



Remember info?