# this script is modified after the recipe provided at
# http://spindrop.us/2006/05/19/migrating-from-drupal-47-to-wordpress
#
# updated 2007/05/28 with the comment count updater script from
# http://www.brendanloy.com/2007/02/wordpress-21-upgrade-problems.html
#
# this assumes that both wordpress and drupal are in separate databases. The wordpress database is called "wordpress" and the Drupal database is called "mtdotnetM
igration"

# first, nuke previous content in wordpress database
use mtdotnet;
delete from mt_categories;
delete from mt_posts;     
delete from mt_post2cat;
delete from mt_comments;

# categories
INSERT INTO 
    mt_categories (cat_ID, cat_name, category_nicename, category_description, category_parent)
SELECT term_data.tid, name, name, description, parent 
FROM mtdotnetMigration.term_data, mtdotnetMigration.term_hierarchy 
WHERE term_data.tid=term_hierarchy.tid;

# posts
INSERT INTO 
    mt_posts (id, post_date, post_content, post_title, 
    post_excerpt, post_name, post_modified)
SELECT DISTINCT
    n.nid, FROM_UNIXTIME(created), body, n.title, 
    teaser, 
    REPLACE(REPLACE(REPLACE(REPLACE(LOWER(n.title),' ', '_'),'.', '_'),',', '_'),'+', '_'),
    FROM_UNIXTIME(changed) 
FROM mtdotnetMigration.node n, mtdotnetMigration.node_revisions r
WHERE n.vid = r.vid;

# category --> post relationships
INSERT INTO mt_post2cat (post_id,category_id) SELECT nid,tid FROM mtdotnetMigration.term_node ;

# category count updating
UPDATE `mt_categories` SET `category_count` = (SELECT COUNT(`post_id`) FROM `mt_post2cat` WHERE `mt_categories`.`cat_ID` = `mt_post2cat`.`category_id`);

# comments
INSERT INTO 
    mt_comments 
    (comment_post_ID, comment_date, comment_content, comment_parent, comment_author, comment_author_email, comment_author_url)
SELECT 
    nid, FROM_UNIXTIME(timestamp), 
    comment, thread, name, mail, homepage
FROM mtdotnetMigration.comments ;

# update comments count on mt_posts table
UPDATE `mt_posts` SET `comment_count` = (SELECT COUNT(`comment_post_id`) FROM `mt_comments` WHERE `mt_posts`.`id` = `mt_comments`.`comment_post_id`);

# fix post slugs. first we have to remove the duplicate _____ chars, then replace that with a single - char
UPDATE mt_posts set post_name = REPLACE(post_name, '__', '_');
UPDATE mt_posts set post_name = REPLACE(post_name, '__', '_');
UPDATE mt_posts set post_name = REPLACE(post_name, '__', '_');
UPDATE mt_posts set post_name = REPLACE(post_name, '__', '_');
UPDATE mt_posts set post_name = REPLACE(post_name, '_', '-');

# migrate users
INSERT INTO 
   mt_users 
   (ID, user_login, user_pass, user_nickname, user_nicename, user_email, user_registered)
SELECT
   uid, name, pass, name, name, mail, FROM_UNIXTIME(created)
FROM mtdotnetMigration.users
   WHERE uid > 1;

# turn all --break-- tags into --more-- tags
UPDATE mt_posts set post_content = REPLACE (post_content, '--break--','--more--');

# turn all example.com?q=node/1234 links to example.com?p=1234 links
UPDATE mt_posts set post_content = REPLACE (post_content, 'www.markturner.net/?q=node/','www.markturner.net/?p=');
UPDATE mt_posts set post_content = REPLACE (post_content, 'www.markturner.net/node/','www.markturner.net/?p=');
UPDATE mt_posts set post_excerpt = REPLACE (post_excerpt, 'www.markturner.net/node/','www.markturner.net/?p=');
UPDATE mt_posts set post_excerpt = REPLACE (post_excerpt, 'www.markturner.net/?q=node/','www.markturner.net/?p=');