Mark's Blog

Yesterday I have been asked to help a good friend of mine with migrating her Serendipity and CuteNews blogs to Wordpress. As it were rather trivial installations, with only one author and no tweaks in the installations themselves, the whole work was limited to transform database tables and, in case of CuteNews, moving text-based data collections to MySQL. Here is how I did it all:

I did start with Serendipity. Of course, convertres already exist, but I have managed it by two SQL commands:

INSERT INTO wp_posts
SELECT s.id, 1 'post_author',
       FROM_UNIXTIME(s.timestamp) 'post_date', FROM_UNIXTIME(s.timestamp-2*3600) 'post_date_gmt',
       s.body 'post_content', s.title 'post_title', 0 'post_category', '' AS 'post_excerpt',
       IF(s.isdraft = 'false', 'publish', 'draft') AS 'post_status', 'closed' AS 'comment_status', 'closed' AS 'ping_status',
       '' AS 'post_password', CONCAT('old_s9y_', s.id) 'post_name',
       '' AS 'to_ping', '' AS 'pinged',
       FROM_UNIXTIME(s.last_modified) 'post_modified', FROM_UNIXTIME(s.last_modified-2*3600) 'post_modified_gmt',
       '' AS 'post_content_filtered', 0 'post_parent', CONCAT('http://example.de/diary/?p=', s.id) AS 'guid',
       0 AS 'menu_order', 'post' AS 'post_type', '' AS 'post_mime_type', s.comments AS 'comment_count'
FROM serendipity_entries s
ORDER BY s.id ASC;

INSERT INTO wp_comments
SELECT s.id 'comment_ID', s.entry_id 'comment_post_ID',
       s.author 'comment_author', s.email 'comment_author_email', s.url 'comment_author_url', s.ip 'comment_author_IP',
       FROM_UNIXTIME(s.timestamp) 'comment_date', FROM_UNIXTIME(s.timestamp-2*3600) 'comment_date_gmt',
       s.body 'comment_content',
       0 'comment_karma', IF(s.status = 'approved' , 1, 0) 'comment_approved',
       '' AS 'comment_agent', '' AS 'comment_type', s.parent_id 'comment_parent', 0 'user_id'
FROM serendipity_comments s
WHERE s.id < 188
ORDER BY s.id ASC;

Comments above 188 were spam, hence the WHERE clause. Then, I needed to update the comment count by:

UPDATE wp_posts p SET p.comment_count = (SELECT COUNT(*) FROM wp_comments c WHERE c.comment_post_ID = p.ID AND c.comment_approved = 1)

CuteNews stores its data in textfiles, with (roughtly) one blog entry (“news”) by line. Using MySQL’s Query Browser and its Regex Text Importer, you can obtain all entries as matchgroups by these regexp along with the following SQL commands to import them to previously created MySQL tables:

/* news: (\d+)\|([^\|]+)\|([^\|]+)\|([^\|]+)\|\|\|\|\| */
INSERT INTO cutenews_news(created, author, title, body)
VALUES(FROM_UNIXTIME($RegEx1.1), '$RegEx1.2', '$RegEx1.3', REPLACE('$RegEx1.4', '{nl}', "\n"));
/* comments: (\d+)\|([^\|\>]+)\|([^\|]+)\|([^\|]+)\|([^\|]+)\|\| */
INSERT INTO cutenews_comments(commented, author, email, IP, comment_text)
VALUES(FROM_UNIXTIME($RegEx1.1), '$RegEx1.2', '$RegEx1.3', '$RegEx1.4', '$RegEx1.5');

cutenews_news has to receive a primary id “news”, cutenews_comments has to have an additional column “comment” (INT PRIMARY AUTO_INCREMET) as well as “news”. By the latter we link comments and news as follows:

UPDATE cutenews_comments c SET c.news = (SELECT MAX(p.news) FROM cutenews_news p WHERE (p.created <= c.created))

Copying the Serendipity to Wordpress instructions for transforming tables we can finish migration from Cutenews:

INSERT INTO wp_posts
SELECT n.news + 96 'ID', 1 'post_author',
       n.created 'post_date', DATE_SUB(n.created,INTERVAL 2 HOUR) 'post_date_gmt',
       n.body 'post_content', n.title 'post_title', 0 'post_category', '' AS 'post_excerpt',
       'publish' AS 'post_status', 'closed' AS 'comment_status', 'closed' AS 'ping_status',
       '' AS 'post_password', CONCAT('old_cn_', n.news) 'post_name',
       '' AS 'to_ping', '' AS 'pinged',
       n.created 'post_modified', DATE_SUB(n.created,INTERVAL 2 HOUR) 'post_modified_gmt',
       '' AS 'post_content_filtered', 0 'post_parent', CONCAT('http://example.de/diary/?p=', n.news+96) AS 'guid',
       0 AS 'menu_order', 'post' AS 'post_type', '' AS 'post_mime_type',
       (SELECT COUNT(*) FROM cutenews_comments c WHERE c.news = n.news) AS 'comment_count'
FROM cutenews_news n
ORDER BY n.news ASC;

INSERT INTO wp_comments
SELECT NULL 'comment_ID', c.news + 96 'comment_post_ID',
       c.author 'comment_author', c.email 'comment_author_email', '' AS 'comment_author_url', c.ip 'comment_author_IP',
       c.commented 'comment_date', DATE_SUB(c.commented,INTERVAL 2 HOUR) 'comment_date_gmt',
       c.comment_text 'comment_content',
       0 'comment_karma', 1 'comment_approved',
       '' AS 'comment_agent', '' AS 'comment_type', 0 'comment_parent', 0 'user_id'
FROM cutenews_comments c
ORDER BY c.news ASC;

Voila!

Bookmark to:

Tags: blog, convert, MySQL, regexp, SQL