Da dieser Artikel nur ein sehr kleines Zielpublikum haben wird, verfasse ich diesen auf Englisch.
During the last weeks I migrated three old (I should rather say very old) installations of Movable Type to WordPress. For the younger generation: Movable Type is one of the first blogging systems which came out long bevor WordPress was published.
For this process I created some SQL code which migrates more information between the systems than a regular export and import would do. It still covers not everything but you can easily extend it for your needs. For newer versions of Movable Type you might also need some changes.
The method will transfer posts, comments, categories and tags from Movable Type to WordPress. It is based on the assumption, that you only use one author. It works for German special characters when it comes to the slug. You can run this SQL code multiple times, because it will delete all existing database entries.
Important: Movable Type allows more than one weblog in a single installation. The SQL code requires the ID of the blog to be 1. If you have a different ID check all places with “blog_id” and replace the following 1 with the ID from your weblog.
This is how it works:
- Copy everything from your webspace to a new webspace. This will keep the image files in place. This webspace will be our testing and development environment. Later you can copy it back to the original place. (Or you create a backup and work within the existing webspace. Which I do not recommend. Trust me, I know the pain…)
- Install WordPress as you would normally do. You can use a separate database or the same one that Movable Type does. (See next step.)
- If you use a separate database: Create an SQL dump of the Movable Type installation and insert it into the WordPress database.
- First you will have to check if there are duplicate base names in your Movable Type system. In this case you will run into trouble later. So you have to fix this first. You can find duplicate base names using this SQL statement. If it returns no results you are ready to go. If it returns results, change the base name directly in the database and set redirects in your .htaccess file if you want.
SELECT entry_basename, COUNT(*) FROM mt_entry GROUP BY entry_basename HAVING COUNT(*) > 1; - I needed to define the charset:
SET NAMES 'utf8'; - First we have to delete all existing categories and tags:
delete from wp_terms;
delete from wp_term_taxonomy where taxonomy = 'category' OR taxonomy = 'post_tag'; - Now we create the categories:
insert into wp_terms (term_id, name, slug, term_group) select category_id, category_label, category_label, 0 as term_group from mt_category where category_blog_id = 1 AND (category_description not like '<!--tag-->%' OR category_description IS NULL) AND category_label != 'tags';
insert into wp_term_taxonomy (term_id, taxonomy, parent, count) select category_id, 'category', 0, 0 from mt_category where category_blog_id = 1 AND (category_description not like '<!--tag-->%' OR category_description IS NULL) AND category_label != 'tags'; - If you use subcategories this hierarchy will not be transferred. For me it was quicker to change the structure manually in WordPress than figuring out the SQL code. So please do this manually in your WordPress backend too.
- Next we create all the tags:
insert into wp_terms (term_id, name, slug, term_group) select category_id, category_label, category_label, 0 as term_group from mt_category where category_blog_id = 1 AND category_description = '<!--tag-->';
insert into wp_term_taxonomy (term_id, taxonomy, parent, count) select category_id, 'post_tag', 0, 0 from mt_category where category_blog_id = 1 AND category_description = '<!--tag-->'; - Now we fix the slugs. This cleans up a little bit with the spaces and it gives a better representation of German special characters. Add others if needed.
update wp_terms set slug = replace(trim(lower(slug)), ' ', '-');
update wp_terms set slug = replace(slug, '+', '');
update wp_terms set slug = replace(slug, '.', '');
update wp_terms set slug = replace(slug, '_', '-');
update wp_terms set slug = replace(slug, ':', '-');
-- German only
update wp_terms set slug = replace(slug, 'ä', 'ue');
update wp_terms set slug = replace(slug, 'Ä', 'ue');
update wp_terms set slug = replace(slug, 'ö', 'oe');
update wp_terms set slug = replace(slug, 'Ö', 'ue');
update wp_terms set slug = replace(slug, 'ü', 'ue');
update wp_terms set slug = replace(slug, 'Ü', 'ue');
update wp_terms set slug = replace(slug, 'ß', 'ss'); - The most important part follows: Now we transfer the posts. Note that this statement supports both: Posts with and without “continue reading”.
delete from wp_posts where post_type = 'post';
insert into wp_posts (post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count) SELECT 1, entry_created_on, entry_created_on, CASE WHEN entry_text_more IS NULL OR entry_text_more = '' THEN entry_text ELSE CONCAT(entry_text, '<!--more-->', entry_text_more) END, entry_title, entry_excerpt, 'publish', 'open', 'open', NULL, entry_basename, NULL, NULL, entry_modified_on, entry_modified_on, NULL, 0, 'http://', 0, 'post', NULL, 0 FROM mt_entry WHERE entry_blog_id = 1; - We’ve got what you have written so far. Let’s add the comments from your readers now:
DELETE FROM wp_comments;
INSERT INTO wp_comments (comment_post_ID, comment_author, comment_author_email, comment_author_url, comment_date, comment_date_gmt, comment_content, comment_approved) SELECT (SELECT ID FROM wp_posts where post_name = (SELECT entry_basename FROM mt_entry WHERE entry_id = comment_entry_id)), comment_author, comment_email, comment_url, comment_created_on, comment_created_on, comment_text, 1 FROM mt_comment WHERE comment_blog_id = 1 AND comment_visible = 1;
UPDATE wp_posts SET comment_count = (SELECT COUNT(*) FROM wp_comments WHERE comment_post_ID = ID); - Finally, we assign the categories and tags to the posts:
DELETE FROM wp_term_relationships;
CREATE TABLE tmp (i1 INT, i2 INT, i3 INT, i4 INT);
INSERT INTO tmp (i1, i2, i3, i4) SELECT placement_entry_id, placement_category_id, (SELECT ID FROM wp_posts where post_name = (SELECT entry_basename FROM mt_entry WHERE entry_id = placement_entry_id)) AS object_id, (SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE term_id = placement_category_id) as term_taxonomy_id FROM mt_placement WHERE placement_blog_id = 1;
DELETE FROM tmp WHERE i3 is null OR i4 IS NULL;
INSERT INTO wp_term_relationships (object_id, term_taxonomy_id, term_order) SELECT i3, i4, 0 FROM tmp GROUP BY i3, i4;
DROP TABLE tmp;
UPDATE wp_term_taxonomy SET count = (SELECT COUNT(*) FROM wp_term_relationships WHERE wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id); - Sometimes you need more changes to the content of the old posts. In my case I move to another domain so I had to remove the domain from all internal links. My solution is a simple Perl script which uses some regular expressions. You will need SSH access or run this script as Perl CGI. (In fact I love Perl. This is the reason why I stayed for such a long time with Movable Type.)
#!/usr/bin/perl
use strict
use warnings;
use DBI;
# database settings
my $dsn = "DBI:mysql:xxx;
my $user = "xxx";
my $password = 'xxx';
my $dbh = DBI->connect($dsn,$user,$password);
my $sql = "select id, post_content from wp_posts order by id;";
my $sth = $dbh->prepare($sql);
$sth->execute();
while(my @row = $sth->fetchrow_array()){
my $id = $row[0];
my $content = $row[1];
# do some crazy manipulation of $content using regular expressions
# example: remove the own URL from all internal link
$content =~ s/http...www.theofel.de//sg;
my $sth = $dbh->prepare("UPDATE wp_posts SET post_content = ? WHERE id = ?");
$sth->execute($content, $id);
$sth->finish();
}
$sth->finish();
$dbh->disconnect(); - Now it is time for testing! You might need some changes if you use special features. Especially if SEO is important for you, you will need some extra work.
- Check if you need redirects in your .htaccess file. Maybe the URL structure has changed.
- Remove the CGI files from Movable Type from your installation for security reasons.