How to Migrate from Textpattern to Ghost CMS

Migrating Articles

The most important thing is to migrate the old posts (articles) from Textpattern to Ghost. To do so, a mapping between the corresponding database table columns needs to be defined. The table containing the Textpattern posts is called textpattern. In Ghost it is simply called posts. We will try to put a reasonable value into every column of posts, as this is the target table. We might not be able to map all informatin that is present in Textpattern.

First, create a copy of your textpattern database. We will constantly make changes to the textpattern table of this database copy until the table is ready for migration. Don’t do this on the original database as you might want to switch back to Textpattern or keep your old site online and working for a while.

Now let’s go through all of the posts columns and try to find or create an equivalent column in the textpattern source table.

Column posts.id

We have to map from column ID of type int(11) to id of type int(10). If you don’t have billions of posts, we only have to rename the column and adjust its length:

ALTER TABLE `textpattern` CHANGE `ID` `id` INT(10) NOT NULL AUTO_INCREMENT;

If you have already published posts in Ghost, make sure their IDs do not conflict with your Textpattern article IDs. You could for example multiply Ghost IDs by 100 or 1000 depending on highest ID in Textpattern. However, these changes need to be reflexted in tables referring to posts, e.g. in posts_tags. In my case, Textpattern IDs are starting at 400 and there were no conflicts.

Column posts.uuid

We have to map from column uid to uuid and from varchar(32) to varchar(36). Example: 1a0673a0f80dd78479b13e8c5de1a190 --> 1a0673a0-f80d-d784-79b1-3e8c5de1a190.

Adjust column name and length:

ALTER TABLE `textpattern` CHANGE `uid` `uuid` VARCHAR(36) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';

Insert hyphens:

UPDATE `textpattern`
SET uuid = CONCAT(
    SUBSTRING(uuid, 1, 8), '-'
    SUBSTRING(uuid, 9, 4), '-'
    SUBSTRING(uuid, 13, 4), '-'
    SUBSTRING(uuid, 17, 4), '-'
    SUBSTRING(uuid, 21, 12)
)

Column posts.title

We have to map from column Title to title and from varchar(255) to varchar(150). First, find titles longer than 150 characters and shorten them manually, so they won’t be truncated:

SELECT * FROM `textpattern` WHERE length(Title) > 150

Afterwards, alter the column:

ALTER TABLE `textpattern` CHANGE `Title` `title` VARCHAR(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';

Column posts.slug

We have to map from column url_title to slug and from varchar(255) to varchar(150). Example: speed-up-your-website --> speed-up-your-website. If you don’t have titles longer than 150 characters, then this shouldn’t be a problem either.

ALTER TABLE `textpattern` CHANGE `url_title` `slug` VARCHAR(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '';

Column posts.markdown

Textile, the markup language used by Textpattern (column textpattern.Body), is not compatible with Markdown, the markup language used by Ghost. However, you can use plain HTML in Markdown documents. Hence we will map from the column textpattern.Body_html to posts.markdown. The column type mediumtext remains the same:

ALTER TABLE `textpattern` CHANGE `Body_html` `markdown` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

In Textpattern you can also write an excerpt, though I hardly ever use this feature. The Problem is that Ghost has no column to store the excerpt. A possible solution could be, to prepend the post body with the excerpt:

UPDATE textpattern
SET markdown = CONCAT(Excerpt_html, markdown)
WHERE Excerpt_html != ''

You might have to tweak your posts manually to make the excerpt look right.

Column posts.html

We will use the HTML contents from the former column Body_html here.

Column posts.image

We have to map from textpattern.Image to posts.image. Image seems to contain an image ID, although it’s defined as varchar(255). Textpattern has a separate table txp_image for storing information about images. The images themselves are stored using their ID as filename in a subfolder called images, e.g. /images/42.jpg. Ghost is storing absolute image URLs (pointing to external websites) and relative URLs (e.g. /content/images/2015/11/moon-landing.gif) in the column image of type text. Relative image URLs refer to images in the subfolder /content/images/<year>/<month>/.

A possible migration approach would be as follows:

Note: To keep it simple, I will not migrate image file names. These will be lost. All images from Textpattern will have names like 42.jpg in Ghost.

The transform the image IDs, run the following:

UPDATE `textpattern` t
SET t.Image = CONCAT('/content/images/2015/11/', t.Image, (SELECT ext FROM txp_image WHERE id = t.Image))
WHERE t.Image != ''

To alter the column name and type, run this:

ALTER TABLE `textpattern` CHANGE `Image` `image` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;

Textpattern doesn’t have featured posts. You can mark posts as featured manually in Ghosts admin interface at a later point. We will use the default value 0 for our migrated posts.

Column posts.page

The value 0 seems to be the default for new posts. If a post is marked as a “static page”, the value becomes 1. We will use 0 for our migrated posts.

Column posts.status

We have to map from Status of type int(2) to status of type varchar(150).

Textpattern seems to know four status values: 4 = Live, 3 = Pending , 2 = Hidden, 1 = Draft and 5 = Sticky. Ghost however only has two: published and draft.

Let’s alter the column structure first:

ALTER TABLE `textpattern` CHANGE `Status` `status` VARCHAR(150) NOT NULL DEFAULT '4';

Map { Live, Sticky } to { published }:

UPDATE `textpattern`
SET status = 'published'
WHERE status = '4' OR status = '5'

Map { Pending, Hidden, Draft} to { draft }:

UPDATE `textpattern`
SET status = 'draft' WHERE status = '3' OR status = '2' OR status = '1'

Column posts.language

We will use the default en_US.

Column posts.meta_title

We will use the default value NULL.

Column posts.meta_description

We will use the default value NULL.

Column posts.author_id

We have to map from AuthorID of type varchar(64) (author name) to author_id of type int(11) (author ID). For every user in Textpattern, register a new user in Ghost. Then perform a mapping from Textpattern user name to Ghost user id:

UPDATE textpattern SET AuthorID = 1 WHERE AuthorID = 'Alice'
UPDATE textpattern SET AuthorID = 2 WHERE AuthorID = 'Bob'

Alter the column structure:

ALTER TABLE `textpattern` CHANGE `AuthorID` `author_id` INT(11) NOT NULL;

Column posts.created_at

We have to map from Posted of type datetime to created_at of type datetime:

ALTER TABLE `textpattern` CHANGE `Posted` `created_at` DATETIME NOT NULL;

Column posts.created_by

We will use the value of author_id for this column.

Column posts.updated_at

We have to map from LastMod of type datetime to updated_at of type datetime:

ALTER TABLE `textpattern` CHANGE `LastMod` `updated_at` DATETIME NOT NULL;

Column posts.updated_by

Same as before:

UPDATE textpattern SET LastModID = 1 WHERE LastModID = 'Alice';
UPDATE textpattern SET LastModID = 2 WHERE LastModID = 'Bob';
ALTER TABLE `textpattern` CHANGE `LastModID` `updated_by` INT(11) NOT NULL;

Column posts.published_at

We will use the value of created_at (formerly known as Posted) for this column.

Column posts.published_by

We will use the value of author_id for this column.

Migration

Finally we should be able to copy over our articles from textpattern to Ghost’s table posts:

INSERT INTO ghost.posts (id, uuid, title, slug, markdown, html, image, featured, page, status, language, meta_title, meta_description, author_id, created_at, created_by, updated_at, updated_by, published_at, published_by)
SELECT t.id, t.uuid, t.title, t.slug, t.markdown, t.markdown, t.image, 0, 0, t.status, 'en_US', NULL, NULL, t.author_id, t.created_at, t.author_id, t.updated_at, t.updated_by, t.created_at, t.author_id
FROM devjack.textpattern t

Migrate Textpattern categories

I just mapped Textpattern categories to Ghost tags:

  1. Make sure that all values of textpattern.Category1 and textpattern.Category2 exist in ghost.tags. Either by doing an SQL insert or by manually picking suitable posts in Ghost and adding tags until every category has been used as a tag at least once. For a few dozens of categories the manual approach might be easier, as Ghost will take care of filling in values for all the other ghost.tags columns such as the UUID and the dates.

  2. Make sure that every Textpattern article corresponds to a post in Ghost. The following query should return an empty result: SELECT * FROM textpattern WHERE id NOT IN (SELECT id FROM ghost.posts)

  3. Copy over the categories:

    INSERT INTO ghost.posts_tags (post_id, tag_id, sort_order)
SELECT posts.id, tags.id, 0
FROM devjack.textpattern posts, ghost.tags tags
WHERE posts.Category1 = tags.name

INSERT INTO ghost.posts_tags (post_id, tag_id, sort_order)
SELECT posts.id, tags.id, 1
FROM devjack.textpattern posts, ghost.tags tags
WHERE posts.Category2 = tags.name

What’s still missing