<repo_name>sunpig/sunpig-mt-to-wp<file_sep>/copy-entry-keywords.sql
/* 
The quickreviews blog (mt_blog_id=12, wp blog id = 4) used an entry-keywords plugin for storing
key-value data for an entry. In WP, this data is stored in the wp_postmeta table.
There are only three keys: imdb, amazon, and image
*/

insert into wp_4_postmeta (
	post_id,
	meta_key,
	meta_value
)
select 
	entry_id,
	'imdb',
	substring(
		entry_keywords
		FROM
		instr(lower(entry_keywords), 'imdb') + length('imdb') + 1
		FOR
		coalesce(
			nullif(locate(' ', lower(entry_keywords), instr(lower(entry_keywords), 'imdb')), 0),
			nullif(locate("\n", lower(entry_keywords), instr(lower(entry_keywords), 'imdb')), 0),
			length(entry_keywords) + 1
		) - (instr(lower(entry_keywords), 'imdb') + length('imdb') + 1)
	)
from
	mt_entry 
where
	entry_blog_id = 12
	and entry_keywords like '%imdb=%';


insert into wp_4_postmeta (
	post_id,
	meta_key,
	meta_value
)
select 
	entry_id,
	'amazon',
	substring(
		entry_keywords
		FROM
		instr(lower(entry_keywords), 'amazon') + length('amazon') + 1
		FOR
		coalesce(
			nullif(locate(' ', lower(entry_keywords), instr(lower(entry_keywords), 'amazon')), 0),
			nullif(locate("\n", lower(entry_keywords), instr(lower(entry_keywords), 'amazon')), 0),
			length(entry_keywords) + 1
		) - (instr(lower(entry_keywords), 'amazon') + length('amazon') + 1)
	)
from
	mt_entry 
where
	entry_blog_id = 12
	and entry_keywords like '%amazon=%';


insert into wp_4_postmeta (
	post_id,
	meta_key,
	meta_value
)
select 
	entry_id,
	'image',
	substring(
		entry_keywords
		FROM
		instr(lower(entry_keywords), 'image') + length('image') + 1
		FOR
		coalesce(
			nullif(locate(' ', lower(entry_keywords), instr(lower(entry_keywords), 'image')), 0),
			nullif(locate("\n", lower(entry_keywords), instr(lower(entry_keywords), 'image')), 0),
			length(entry_keywords) + 1
		) - (instr(lower(entry_keywords), 'image') + length('image') + 1)
	)
from
	mt_entry 
where
	entry_blog_id = 12
	and entry_keywords like '%image=%';
<file_sep>/database-charset.md
# Database Charsets

The mt_* tables in our database use the latin1 charset, but the wp_* tables use
the utf8 charset. To make things more fun, the data in the mt_* tables is not
consistently encoded: some rows use utf8 stored in the db as latin1, while
other rows genuinely are latin1 stored as latin1. This is visible if I do a
`select entry_text from mt_entry` on the database: sometimes a GBP character
will come back as '£', and sometimes as 'Â£'.

For *most* rows, this conversion will take the utf8 text in the latin1 mt_* tables,
and turn it into utf8 text for the wp_* tables:

```
select 
	convert(cast(convert(entry_text using latin1) as binary) using utf8)
from
	mt_entry;
```

But for some rows it goes horribly wrong, truncating the text at the point where
it encounters an invalid character.

Step 1: Identify problematic rows with badly encoded characters:

```
select
	entry_id, 
	entry_blog_id
from
	mt_entry
where
	convert(cast(convert(entry_text using latin1) as binary) using utf8) <> entry_text
	or convert(cast(convert(entry_text_more using latin1) as binary) using utf8) <> entry_text_more
order by
	entry_id;
```

Step 2: In the MT admin interface, visit each of the 110 problematic entries, and fix them
so that the conversion process will run OK.

It's only 110 entries.

Step 3: Carry on.
<file_sep>/identify-abnormal-characters-in-comments.sql
/* 
The mt_comment table uses the latin1 character set; wp_comments uses utf8.
The copy process uses a latin1 -> utf8 conversion that will handle most
comments, but there are some that cause problems and end up truncated.
To identify which ones these are, and fix them in MT before the migration,
use the following steps:

- Create a temporary table with utf8 charset
- Perform a copy+convert from mt_comment to the temp table
- Join mt_comment with the temp table to find comments that have been truncated
- Note the problem comments, and fix them either in the MT admin or 
  identify the characters that are causing the problem, and run a search/replace
  on the mt_comment table directly.

*/


/*** Drop & create the temp table ***/

drop table if exists tmp_comment_sanitize;

create temporary table if not exists tmp_comment_sanitize (
  `comment_ID` bigint(20) unsigned NOT NULL,
  `comment_post_ID` bigint(20) unsigned NOT NULL,
  `comment_content` text NOT NULL,
  PRIMARY KEY (`comment_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


/*** Copy + convert from mt_comment to temp table ***/

insert into tmp_comment_sanitize
(comment_id, comment_post_id, comment_content)
select 
	comment_id,
	comment_entry_id,
	convert(cast(convert(comment_text using latin1) as binary) using utf8)
from 
	mt_comment 
	where comment_junk_status <> -1
	and comment_visible = 1;


/*** Join mt_comment with the temp table to find comments that need fixing ***/

select 
	mtc.comment_blog_id,
	mtc.comment_entry_id,
	mtc.comment_id,
	mtc.comment_author,
	mtc.comment_text,
	length(mtc.comment_text),
	tcs.comment_content,
	length(tcs.comment_content)
from
	tmp_comment_sanitize tcs
	inner join mt_comment mtc on tcs.comment_id = mtc.comment_id
where
	tcs.comment_content <> mtc.comment_text
	and length(tcs.comment_content) <> length(mtc.comment_text)
order by
	mtc.comment_blog_id,
	mtc.comment_entry_id,
	mtc.comment_id;

<file_sep>/README.md
# sunpig-mt-to-wp

Notes, instructions, and scripts for moving sunpig.com from Movable Type to Wordpress

## Intro

As of 1 January 2014, sunpig.com is using Movable Type Pro version 5.14 with
Community Pack 1.83, and Professional Pack 1.63. The MT installation has two users
(@evilrooster and @sunpig), and five blogs:

* [<NAME>](http://sunpig.com/abi/)
* [Legends of the Sun Pig](http://sunpig.com/martin/)
* defunct [Quick Reviews](http://sunpig.com/martin/quickreviews/2009/)
* defunct [Bunny Tails](http://sunpig.com/bunnytails/)
* defunct, sidebar blog [Something Nice](http://sunpig.com/martin/somethingnice/2010/)

The goal is to move these blogs to self-hosted Wordpress 3.8 on the same server.

Reasons for the migration:

First of all, Movable Type fails two of my three tests for choosing a piece of (open source) software:

* Is it well documented? (yes)
* Is it under active development (no, at least not in its open source version)
* Does it have an active and supportive user community (not any more)

I still like its architectural model of static publishing, and (partly because of that) it has
a great security record, which is important if you're running your own server. I've been using MT
since version 1, and I've clung to it for sentimental and pseudo-practical reasons ("I know the
templating language really well!") for a long time, but the online world is a much different place now,
and the fact is that compared to all other avenues for writing online, MT 5's interface is
poor, and I dislike using it. As a result, I don't. I blogged less in 2013 than in any previous year.

[OpenMelody](http://openmelody.com/) was a fork of the open source version of MT 4, but it seems
to be dead now.

I was considering using [Jekyll](http://jekyllrb.com/), which is a modern static site generator:
write posts in your text editor, run a site generator from the command line, and `rsync` the generated
html files to your server. This has lots of good points: it generates static files, and it plugs
directly into my standard text editor workflow — with version control! This is great if you're a
programmer and always have access to a machine with a command line. Not so great if
`bundle exec jekyll build` makes you twitchy, or if you like the idea of occasionally posting
something from your phone. Also, no matter how you slice it, comments end up as a crazy hack.
I can see myself using jekyll for other projects, just not for our main blogs.

[Ghost](https://ghost.org/) looks interesting and new and shiny, but also: node + sqlite. Really?
And I mistrust an open source project that has a "sign up" link on its home page, but not a
"download".

[Drupal](https://drupal.org/) would probably do the job, but my impression is (perhaps incorrectly)
that it is more geared towards *sites* rather than *blogs*.

So... Wordpress. Big community, well documented, under active development. Used to have a bad rep
for security, but is a lot better than it used to be, and since version 3.7 even features an
automatic update process to apply maintenance and security patches. It also has well-established
guidelines and practices for hardening an installation. It's "the standard" these days. I have a
general preference for "off-piste" solutions, but sometimes I just want to go with something that
"just works". Mostly.


## Factors to consider

I'd prefer as little downtime as possible, but I can live with a few hours. That means I don't have to
script and rehearse the migration in exhaustive detail.

I'm assuming that the new wp_* tables will live side-by-side with the old mt_* tables in the same
MySQL database, at least for the duration of the migration. This makes copying data easy, and means
I can do JOIN statements between the old and the new.

We haven't used the asset management features of MT very much, which means that pretty much all of
our data is stored as text in the database - we don't have to worry about uploaded assets and linkages between
the db and the file system.

Maintaining permalinks for individual posts is a must-have. Maintaining permalinks for comments on entries
is just a nice-to-have.

We only have a few blogs (5) and a few registered authors (4), because we never made much use of MT's
user registration system. With these numbers, it's simple enough to manually create the matching blogs
and users in WP, and then copy data across to them. If we had more registered users, I'd probably
want to script their creation, and spend a lot more time matching up users and entries.

I don't care whether posts appear as duplicates in RSS readers after the migration - this means I can
can just make sure that the GUID for each post in the wp_posts table is unique, but it doesn't have to
match anything in the old system.

The charset the mt_* tables in our database use is `latin1`. The wp_* tables use a utf8 charset.
[Balls](http://codex.wordpress.org/Converting_Database_Character_Sets).

I can cope with a small loss of fidelity in things like categories and tags.

We don't need to copy across spam comments (mt_comment.comment_spam_status)<file_sep>/wp-database.md
# WP database

A multi-site Wordpress installation is considered a "network" of blogs. There is a set of
tables to describe the network as a whole, and features common to all blogs in the network
(e.g. users). Each blog in the network also gets its own set of tables for posts, comments,
links, and tags/categories.

Core/common tables:

* wp_blog_versions
* wp_blogs
* wp_registration_log
* wp_signups
* wp_site
* wp_sitemeta
* wp_usermeta
* wp_users

The first blog in the network gets tables starting just with the standard wp_* prefix.
Subsequent blogs are given a numeric infix that matches their ID in the wp_blogs table,
e.g. wp_2_posts.

Blog-specific tables:

* wp[_2]_commentmeta
* wp[_2]_comments
* wp[_2]_links
* wp[_2]_options
* wp[_2]_postmeta
* wp[_2]_posts
* wp[_2]_term_relationships
* wp[_2]_term_taxonomy
* wp[_2]_terms

This separation of tables is a big difference between a WP installation and a MT installation.
In MT, there is a single table for all blog entries (`mt_entry`), and each row in that table has
an `entry_blog_id` to show what blog it belongs to.

Another difference is that the `mt_entry` table contains many *columns* of data that in WP
are represented as *rows* of data in `wp_postmeta`. (Each row in `wp_postmeta` represents
a piece of data about a single post. Similarly for comments.)

So copying entry/post data from MT tables into the WP tables isn't simply a matter of
doing a single INSERT...SELECT query. Hello cursors?

<file_sep>/migration.md
# The actual migration

## Clean up invalid characters in the original MT database

Manual effort. Can be done any time before the migration.

Use `identify-abnormal-characters-in-entries.sql` and `identify-abnormal-characters-in-comments.sql`
to find enties and comments with characters that will cause problems during migration.


## Clean up duplicate entry tags in the original MT database

Manual effort. Can be done any time before the migration.

The process of copying categories and tags is primitive, and will barf if it tries to copy two
tags that have the same basename (slug). For example, the tags "background image"
and "background-image" can be two separate tags in MT, but they end up with
the same *normalized* (n8d) tagname of "backgroundimage".

Also, it will drop tags that resolve to the same base name as an existing *category*.
(I can live with the loss.)


## Clean up entry_basename values in the mt_entry table in the original MT database

Make URLs consistent: /blogname/yyyy/mm/dd/entry-basename/

For this to happen, all entries should have an appropriate basename. Can be done
any time before the migration.


## Prepare .htaccess redirection rules in the original MT installation

Create relevant MT template to generate HTTP 301 redirect rules for use in .htaccess
for mapping old urls to new ones. Can be done any time before the migration.

Evilrooster:
<mt:Entries lastn="10000">
Redirect 301 <$mt:EntryPermalink$> http://sunpig.com/abi/<$mt:EntryDate format="%Y/%m/%d"$>/<$mt:EntryBasename$>/</mt:Entries>

Quick reviews:
<mt:Entries lastn="10000">
Redirect 301 <$mt:EntryPermalink$> http://sunpig.com/quickreviews/<$mt:EntryDate format="%Y/%m/%d"$>/<$mt:EntryBasename$>/</mt:Entries>

Bunny tails:
<mt:Entries lastn="10000">
Redirect 301 <$mt:EntryPermalink$> http://sunpig.com/bunnytails/<$mt:EntryDate format="%Y/%m/%d"$>/<$mt:EntryBasename$>/</mt:Entries>


## Take backup of MT database

The usual.


## Install Wordpress

Main instructions: [http://codex.wordpress.org/Installing_WordPress](http://codex.wordpress.org/Installing_WordPress)

Multisite install: [http://codex.wordpress.org/Create_A_Network](http://codex.wordpress.org/Create_A_Network)

Install wordpress in its own subdirectory, rather than in the root of the site.

Note that the blog database exists already - use the same db for both MT and WP, which
makes copying data between the two easy.

* Grab the latest version of wordpress and uncompress it to /wordpress under sunpig.com
* Copy wp-config-sample.php to wp-config.php, and edit the file to
	* add database connection details
	* add security keys, populated with details from https://api.wordpress.org/secret-key/1.1/salt/ 
* Run the wp installation script by going to http://sunpig.com/wordpress/wp-admin/install.php
* Create initial user and initial blog on /dummy url (initial blog will later be disabled)


## Prepare Wordpress multisite

Edit wp-config.php to add multisite configuration:

```
/* Multisite */
define( 'WP_ALLOW_MULTISITE', true );
```

This enables network configuration from within wp. In the wp-admin screens, find 
Tools -> Network Setup -> Create a Network of Wordpress Sites

Follow instructions on screen, which will create some code to be added to the wp-config.php
file, and a block to be added to .htaccess


## Add WP users

In the wp-admin interface, create users to match those in the mt installation. The copy scripts
rely on user email address to match up users between mt and wp.


## Add WP sites

In the following order:

1. Legends of the Sun Pig
2. Evilrooster Crows
3. Quick Reviews
4. Bunny Tails

For each blog, edit the settings to make sure that the "Day and name" option is selected

## Copy content

Run the script `procedures.sql` to install the copy procedures.


## Copy the content

In mysql console:

```
-- legends of the sun pig
call copy_mt_categories_and_entry_tags_to_wp_terms(2, 2);
call copy_mt_entries_to_wp_posts(2, 2);
call copy_mt_comments_to_wp_comments(2, 2);

-- evilrooster crows
call copy_mt_categories_and_entry_tags_to_wp_terms(4, 3);
call copy_mt_entries_to_wp_posts(4, 3);
call copy_mt_comments_to_wp_comments(4, 3);

-- quickreviews
call copy_mt_categories_and_entry_tags_to_wp_terms(12, 4);
call copy_mt_entries_to_wp_posts(12, 4);
call copy_mt_comments_to_wp_comments(12, 4);

-- bunny tails
call copy_mt_categories_and_entry_tags_to_wp_terms(3, 5);
call copy_mt_entries_to_wp_posts(3, 5);
call copy_mt_comments_to_wp_comments(3, 5);
```

After that, run the commands in `copy-entry-keywords.sql` to copy the key-value
metadata from the quick reviews blog from mt to wp.

## Create index.php files

For the top-level blog, create an index.php that loads wp:

```
<?php
/**
 * Front to the WordPress application. This file doesn't do anything, but loads
 * wp-blog-header.php which does and tells WordPress to load the theme.
 *
 * @package WordPress
 */

/**
 * Tells WordPress to load the WordPress theme and output it.
 *
 * @var bool
 */
define('WP_USE_THEMES', true);

/** Loads the WordPress Environment and Template */
require( dirname( __FILE__ ) . '/wordpress/wp-blog-header.php' );
```

For each other blog, create an index.php that loads wp (note the difference
in the path on the last line):

```
<?php
/**
 * Front to the WordPress application. This file doesn't do anything, but loads
 * wp-blog-header.php which does and tells WordPress to load the theme.
 *
 * @package WordPress
 */

/**
 * Tells WordPress to load the WordPress theme and output it.
 *
 * @var bool
 */
define('WP_USE_THEMES', true);

/** Loads the WordPress Environment and Template */
require( dirname( __FILE__ ) . '/../wordpress/wp-blog-header.php' );
```


## Modify htaccess for redirects

Go to the generated htaccess redirects files in MT, and add the redirect chunks to the 
main .htaccess file for sunpig so that old links are correctly redirected.


## Harden installation

See [http://codex.wordpress.org/Hardening_WordPress](http://codex.wordpress.org/Hardening_WordPress)


## Mopping up

*Somehow*, Wordpress fails to set the correct category and tag base rewrite rules for sites
beyond the second one. I have no idea what it's doing to make this not work, but 
http://$DOMAIN/$BLOGNAME/category/$CATEGORY_SLUG works for site 2 (/martin), but not 
for subsequent sites created. This appears to be reproducible in my virtual machine test
environment, but I haven't put any effort into debugging.

FIX: Go into the settings/permalinks admin page for each blog, and manually enter values
for category base ("category") and tag base ("tag").
<file_sep>/identify-abnormal-characters-in-entries.sql
/* 
The mt_entry table uses the latin1 character set; wp_posts uses utf8.
The copy process uses a latin1 -> utf8 conversion that will handle most
entries, but there are some that cause problems and end up truncated.
To identify which ones these are, and fix them in MT before the migration,
use the following steps:

- Create a temporary table with utf8 charset
- Perform a copy+convert from mt_entry to the temp table
- Join mt_entry with the temp table to find entries that have been truncated
- Note the problem entries, and fix them either in the MT admin or 
  identify the characters that are causing the problem, and run a search/replace
  on the mt_entry table directly.

*/


/*** Drop & create the temp table ***/

drop table if exists tmp_entry_sanitize;

create temporary table if not exists tmp_entry_sanitize (
  `entry_ID` bigint(20) unsigned NOT NULL,
  `entry_title` text,
  `entry_excerpt` text,
  `entry_text` text,
  `entry_text_more` text,
  `entry_keywords` text,
  `entry_basename` text,
  PRIMARY KEY (`entry_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


/*** Copy + convert from mt_entry to temp table ***/

insert into tmp_entry_sanitize (
	entry_id,
	entry_title,
	entry_excerpt,
	entry_text,
	entry_text_more,
	entry_keywords,
	entry_basename
)
select 
	entry_id,
	convert(cast(convert(entry_title using latin1) as binary) using utf8),
	convert(cast(convert(entry_excerpt using latin1) as binary) using utf8),
	convert(cast(convert(entry_text using latin1) as binary) using utf8),
	convert(cast(convert(entry_text_more using latin1) as binary) using utf8),
	convert(cast(convert(entry_keywords using latin1) as binary) using utf8),
	convert(cast(convert(entry_basename using latin1) as binary) using utf8)
from 
	mt_entry;


/*** Join mt_entry with the temp table to find entries that need fixing ***/

select 
	mte.entry_blog_id,
	mte.entry_id,
	mte.entry_title,
	tes.entry_title,
	length(mte.entry_title),
	length(tes.entry_title),
	mte.entry_excerpt,
	tes.entry_excerpt,
	length(mte.entry_excerpt),
	length(tes.entry_excerpt),
	mte.entry_text,
	tes.entry_text,
	length(mte.entry_text),
	length(tes.entry_text),
	mte.entry_text_more,
	tes.entry_text_more,
	length(mte.entry_text_more),
	length(tes.entry_text_more),
	mte.entry_keywords,
	tes.entry_keywords,
	length(mte.entry_keywords),
	length(tes.entry_keywords),
	mte.entry_basename,
	tes.entry_basename,
	length(mte.entry_basename),
	length(tes.entry_basename)
from
	tmp_entry_sanitize tes
	inner join mt_entry mte on tes.entry_id = mte.entry_id
where
	(tes.entry_title <> mte.entry_title and length(tes.entry_title) <> length(mte.entry_title) )
	or (tes.entry_excerpt <> mte.entry_excerpt and length(tes.entry_excerpt) <> length(mte.entry_excerpt) )
	or (tes.entry_text <> mte.entry_text and length(tes.entry_text) <> length(mte.entry_text) )
	or (tes.entry_text_more <> mte.entry_text_more and length(tes.entry_text_more) <> length(mte.entry_text_more) )
	or (tes.entry_keywords <> mte.entry_keywords and length(tes.entry_keywords) <> length(mte.entry_keywords) )
	or (tes.entry_basename <> mte.entry_basename and length(tes.entry_basename) <> length(mte.entry_basename) )
order by
	mte.entry_blog_id,
	mte.entry_id;
<file_sep>/procedures.sql
delimiter //

DROP PROCEDURE IF EXISTS copy_mt_categories_and_entry_tags_to_wp_terms //

CREATE PROCEDURE copy_mt_categories_and_entry_tags_to_wp_terms(mt_blog_id INT, wp_blog_id INT)
BEGIN
	SET @wp_table_infix = if(wp_blog_id = 1, '', CONCAT(wp_blog_id, '_'));

	SET @str_truncate_wp_table = CONCAT('TRUNCATE TABLE wp_', @wp_table_infix, 'term_relationships');
	PREPARE stmt_truncate_wp_table FROM @str_truncate_wp_table;
	EXECUTE stmt_truncate_wp_table;
	DEALLOCATE PREPARE stmt_truncate_wp_table;

	SET @str_truncate_wp_table = CONCAT('TRUNCATE TABLE wp_', @wp_table_infix, 'term_taxonomy');
	PREPARE stmt_truncate_wp_table FROM @str_truncate_wp_table;
	EXECUTE stmt_truncate_wp_table;
	DEALLOCATE PREPARE stmt_truncate_wp_table;

	SET @str_truncate_wp_table = CONCAT('TRUNCATE TABLE wp_', @wp_table_infix, 'terms');
	PREPARE stmt_truncate_wp_table FROM @str_truncate_wp_table;
	EXECUTE stmt_truncate_wp_table;
	DEALLOCATE PREPARE stmt_truncate_wp_table;

	SET @str_copy_categories = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'terms (',
			'term_id, ',
			'name, ',
			'slug, ',
			'term_group ',
		') ',
		' SELECT ',
			'category_id, ',
			'category_label, ',
			'category_basename, ',
			'0 '
		' FROM mt_category ',
		' WHERE category_blog_id = ', mt_blog_id, ' ORDER BY category_id ASC');
	PREPARE stmt_copy_categories FROM @str_copy_categories;
	EXECUTE stmt_copy_categories;
	DEALLOCATE PREPARE stmt_copy_categories;

	SET @str_create_term_taxonomy = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'term_taxonomy (',
			'term_id, ',
			'taxonomy, ',
			'description, ',
			'parent, ',
			'count',
		') ',
		' SELECT ',
			'term_id, ',
			'\'category\', ',
			'\'\', ',
			'0, '
			'0 '
		' FROM wp_', @wp_table_infix, 'terms');
	PREPARE stmt_create_term_taxonomy FROM @str_create_term_taxonomy;
	EXECUTE stmt_create_term_taxonomy;
	DEALLOCATE PREPARE stmt_create_term_taxonomy;

	SET @str_create_term_relationships = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'term_relationships (',
			'object_id, ',
			'term_taxonomy_id, ',
			'term_order ',
		') ',

		' select ',
			' mt_placement.placement_entry_id, ',
			' wp_', @wp_table_infix, 'term_taxonomy.term_taxonomy_id, ',
			' 1 ',
		' from ',
			' mt_placement ',
			' inner join wp_', @wp_table_infix, 'term_taxonomy on mt_placement.placement_category_id = wp_', @wp_table_infix, 'term_taxonomy.term_id ',
		' where  ',
			' mt_placement.placement_blog_id = ', mt_blog_id, ' ',
			' and wp_', @wp_table_infix, 'term_taxonomy.taxonomy = \'category\' ',
		' order by mt_placement.placement_entry_id; ');
	PREPARE stmt_create_term_relationships FROM @str_create_term_relationships;
	EXECUTE stmt_create_term_relationships;
	DEALLOCATE PREPARE stmt_create_term_relationships;




	SELECT @max_category_id := `max_category_id` FROM (select max(category_id) as max_category_id from mt_category) mtc;

	SET @str_copy_entry_tags = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'terms (',
			'term_id, ',
			'name, ',
			'slug, ',
			'term_group ',
		') ',
		' select distinct ',
			' mtt.tag_id + 1000 + ', @max_category_id, ', ',
			' mtt.tag_name, ',
			' if(mtt.tag_n8d_id = 0, mtt.tag_name, mtt2.tag_name), ',
			' 0 ',
		' from  ',
			' mt_objecttag mto ',
			' inner join mt_tag mtt on mto.objecttag_tag_id = mtt.tag_id ',
			' left outer join mt_tag mtt2 on mtt.tag_n8d_id = mtt2.tag_id ',
		' where ',
			' mto.objecttag_blog_id = ', mt_blog_id,
			' and mto.objecttag_object_datasource = \'entry\'',
			' and if(mtt.tag_n8d_id = 0, mtt.tag_name, mtt2.tag_name) not in (select distinct slug from wp_', @wp_table_infix, 'terms)',
		' order by mtt.tag_id ');
	PREPARE stmt_copy_entry_tags FROM @str_copy_entry_tags;
	EXECUTE stmt_copy_entry_tags;
	DEALLOCATE PREPARE stmt_copy_entry_tags;

	SET @str_create_entry_tags_term_taxonomy = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'term_taxonomy (',
			'term_id, ',
			'taxonomy, ',
			'description, ',
			'parent, ',
			'count',
		') ',
		' SELECT ',
			'term_id, ',
			'\'post_tag\', ',
			'\'\', ',
			'0, '
			'0 '
		' FROM wp_', @wp_table_infix, 'terms',
		' WHERE term_id > ', @max_category_id);
	PREPARE stmt_create_entry_tags_term_taxonomy FROM @str_create_entry_tags_term_taxonomy;
	EXECUTE stmt_create_entry_tags_term_taxonomy;
	DEALLOCATE PREPARE stmt_create_entry_tags_term_taxonomy;

	SET @str_create_entry_tags_term_relationships = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'term_relationships (',
			'object_id, ',
			'term_taxonomy_id, ',
			'term_order ',
		') ',

		' select ',
			' mt_objecttag.objecttag_object_id, ',
			' wp_', @wp_table_infix, 'term_taxonomy.term_taxonomy_id, ',
			' 1 ',
		' from ',
			' mt_objecttag ',
			' inner join wp_', @wp_table_infix, 'term_taxonomy on mt_objecttag.objecttag_tag_id = wp_', @wp_table_infix, 'term_taxonomy.term_id - 1000 - ', @max_category_id, 
		' where  ',
			' mt_objecttag.objecttag_blog_id = ', mt_blog_id, ' and mt_objecttag.objecttag_object_datasource = \'entry\' ',
			' and wp_', @wp_table_infix, 'term_taxonomy.taxonomy = \'post_tag\' ');
	PREPARE stmt_create_entry_tags_term_relationships FROM @str_create_entry_tags_term_relationships;
	EXECUTE stmt_create_entry_tags_term_relationships;
	DEALLOCATE PREPARE stmt_create_entry_tags_term_relationships;


	SET SQL_SAFE_UPDATES=0;

	SET @str_update_counts = CONCAT(
		' update wp_', @wp_table_infix, 'term_taxonomy tt ',
			' inner join ( ',
				' select term_taxonomy_id, count(*) as c ',
				' from wp_', @wp_table_infix, 'term_relationships ',
				' group by term_taxonomy_id ',
			' ) c on tt.term_taxonomy_id = c.term_taxonomy_id ',
		' set tt.count = c.c; ');
	PREPARE stmt_update_counts FROM @str_update_counts;
	EXECUTE stmt_update_counts;
	DEALLOCATE PREPARE stmt_update_counts;

END
//



DROP PROCEDURE IF EXISTS copy_mt_entries_to_wp_posts //

CREATE PROCEDURE copy_mt_entries_to_wp_posts(mt_blog_id INT, wp_blog_id INT)
BEGIN
	SET @wp_table_infix = if(wp_blog_id = 1, '', CONCAT(wp_blog_id, '_'));

	SET @str_truncate_wp_table = CONCAT('TRUNCATE TABLE wp_', @wp_table_infix, 'posts'); 
	PREPARE stmt_truncate_wp_table FROM @str_truncate_wp_table;
	EXECUTE stmt_truncate_wp_table;
	DEALLOCATE PREPARE stmt_truncate_wp_table;

	SET @str_copy_entries = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'posts (',
			'id, ',
			'post_author, ',
			'post_date, ',
			'post_date_gmt, ',
			'post_content, ',
			'post_title, ',
			'post_excerpt, ',
			'post_status, ',
			'comment_status, ',
			'ping_status, ',
			'post_name, ',
			'to_ping, ',
			'pinged, ',
			'post_modified, ',
			'post_modified_gmt, ',
			'post_content_filtered, ',
			'post_parent, ',
			'guid, ',
			'menu_order, ',
			'post_type ',
		') ',
		' SELECT ',
			'entry_id, ',
			'wp_users.id, ',
			'entry_authored_on, ',
			'CONVERT_TZ(entry_authored_on,\'+00:00\',\'+00:00\'),',
			'CAST( ',
				'CAST( ',
					'IF (',
						'TRIM(IFNULL(entry_text_more,\'\'))=\'\', ',
						'IFNULL(entry_text,\'\'), ',
						'CONCAT(IFNULL(entry_text,\'\'), \'<!--more-->\', IFNULL(entry_text_more,\'\'))',
					') AS CHAR CHARACTER SET latin1 ',
				') AS BINARY ',
			'), ',
			'convert(cast(convert(entry_title using latin1) as binary) using utf8), ',
			'convert(cast(convert(IFNULL(entry_excerpt, \'\') using latin1) as binary) using utf8), ',
			'if(entry_status = 2, \'publish\', \'draft\'), ',
			'if(entry_allow_comments = 1, \'open\', \'closed\'), ',
			'if(entry_allow_pings = 1, \'open\', \'closed\'),',
			'convert(cast(convert(replace(entry_basename, \'_\', \'-\') using latin1) as binary) using utf8), ',
			'\'\', ',
			'\'\', ',
			'entry_modified_on, ',
			'CONVERT_TZ(entry_modified_on,\'+00:00\',\'+00:00\'), ',
			'\'\', ',
			'0, ',
			'CONCAT(\'http://sunpig.com/mt-entry-\', entry_id, \'.html\'), ',
			'0, ',
			'\'post\' ',
		' FROM mt_entry ',
		' INNER JOIN mt_author ON mt_entry.entry_author_id = mt_author.author_id ',
		' INNER JOIN wp_users ON mt_author.author_email = wp_users.user_email ',
		' WHERE entry_blog_id = ', mt_blog_id, ' ORDER BY entry_id ASC');
	PREPARE stmt_copy_entries FROM @str_copy_entries;
	EXECUTE stmt_copy_entries;
	DEALLOCATE PREPARE stmt_copy_entries;
END
//


DROP PROCEDURE IF EXISTS copy_mt_comments_to_wp_comments //

CREATE PROCEDURE copy_mt_comments_to_wp_comments(mt_blog_id INT, wp_blog_id INT)
BEGIN
	SET @wp_table_infix = if(wp_blog_id = 1, '', CONCAT(wp_blog_id, '_'));

	SET @str_truncate_wp_table = CONCAT('TRUNCATE TABLE wp_', @wp_table_infix, 'comments'); 
	PREPARE stmt_truncate_wp_table FROM @str_truncate_wp_table;
	EXECUTE stmt_truncate_wp_table;
	DEALLOCATE PREPARE stmt_truncate_wp_table;

	SET @str_copy_comments = CONCAT(
		'INSERT INTO wp_', @wp_table_infix, 'comments (',
			' comment_id, ',
			' comment_post_id, ',
			' comment_author, ',
			' comment_author_email, ',
			' comment_author_url, ',
			' comment_author_IP, ',
			' comment_date, ',
			' comment_date_gmt, ',
			' comment_content, ',
			' comment_karma,  ',
			' comment_approved, ',
			' comment_parent, ',
			' user_id ',
		' ) ',
		' SELECT ',
			' comment_id, ',
			' comment_entry_id, ',
			' comment_author, ',
			' comment_email, ',
			' comment_url, ',
			' comment_ip, ',
			' comment_created_on, ',
			' CONVERT_TZ(comment_created_on,\'+00:00\',\'+00:00\'), ',
			' convert(cast(convert(comment_text using latin1) as binary) using utf8), ',
			' 0, ',
			' comment_visible, ',
			' 0, ',
			' 0 ',
		' FROM ',
			' mt_comment ',
		' WHERE ',
			' comment_blog_id = ', mt_blog_id, 
			' and comment_junk_status <> -1 ',
			' and comment_visible = 1 ',
			' ORDER BY comment_id ASC');
	PREPARE stmt_copy_comments FROM @str_copy_comments;
	EXECUTE stmt_copy_comments;
	DEALLOCATE PREPARE stmt_copy_comments;

	SET @str_update_comment_counts = CONCAT(
		' update wp_', @wp_table_infix, 'posts p ',
		' inner join ( ',
			' select comment_post_id, count(*) as comment_count from wp_', @wp_table_infix, 'comments ',
			' where comment_approved = 1 ',
			' group by comment_post_id ',
		' ) cc on p.id = cc.comment_post_id ',
		' set p.comment_count = cc.comment_count;');
	PREPARE stmt_update_comment_counts FROM @str_update_comment_counts;
	EXECUTE stmt_update_comment_counts;
	DEALLOCATE PREPARE stmt_update_comment_counts;

END
//


delimiter ;
