FLUSH TABLES
USE working
CHECK TABLE messages
// get rid of the primary key auto_increment column by dropping and adding it
ALTER TABLE messages ADD old_pkey INT FIRST
UPDATE messages SET old_pkey=pkey
ALTER TABLE messages DROP pkey
ALTER TABLE messages CHANGE old_pkey pkey INT 
// we're back where we started except pkey is not auto-incremented

// do the same thing to create, autonumber and remove autocreate for post_id
ALTER TABLE messages ADD t_post_id MEDIUMINT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, AUTO_INCREMENT=50;
ALTER TABLE messages ADD post_id MEDIUMINT(8) UNSIGNED
UPDATE messages SET post_id=t_post_id
ALTER TABLE messages DROP t_post_id 

// add the topic_id column
ALTER TABLE messages ADD topic_id MEDIUMINT UNSIGNED
/* this section cannot be batch filed */
// create a table to generate the next topic id (start with 20)
CREATE TABLE seq_table (seq INT UNSIGNED NOT NULL);
INSERT INTO seq_table VALUES(19);
// you might to mess around here until 
//SELECT LAST_INSERT_ID() returns 20
// then do the following repeatedly 

UPDATE seq_table SET seq=LAST_INSERT_ID(seq+1)
SELECT LAST_INSERT_ID()
UPDATE messages SET topic_id=LAST_INSERT_ID() where ((parent=0)AND(topic_id IS NULL)) LIMIT 1;
// in practice this becomes <three UP ARROWS, ENTER> 
// until the report says nothing's changed
/* end of repeated section */

// now need to set children to match the topic_id of the parent
// thanks to alan larkin and forums.mysql.com
UPDATE messages m1 INNER JOIN messages m2 ON m1.pkey = m2.parent SET m2.topic_id = m1.topic_id

//setup forum_id set to 17
ALTER TABLE messages ADD forum_id SMALLINT(5) UNSIGNED DEFAULT 17

//setup poster_id to -1 (anonymous)
ALTER TABLE messages ADD poster_id MEDIUMINT(8) DEFAULT -1

// setup post_time
ALTER TABLE messages ADD post_time INT
UPDATE messages SET post_time=stamptime

//setup poster_ip
ALTER TABLE messages ADD poster_ip VARCHAR(8) DEFAULT '7F000001'

//setup post_username
ALTER TABLE messages ADD post_username VARCHAR(25)
UPDATE messages SET post_username = username

// setup enable_bbcode, enable_html, enable_smilies, enable_sig, each is 0
ALTER TABLE messages ADD enable_bbcode TINYINT(1) DEFAULT=0
ALTER TABLE messages ADD enable_html TINYINT(1) DEFAULT=0
ALTER TABLE messages ADD enable_smilies TINYINT(1) DEFAULT=0
ALTER TABLE messages ADD enable_sig TINYINT(1) DEFAULT=0

//setup post_edit_time default NULL
ALTER TABLE messages ADD post_edit_time INT DEFAULT NULL

//setup post_edit_count default 0
ALTER TABLE messages ADD post_edit_count SMALLINT UNSIGNED DEFAULT 0

//all above go into phpbb_posts
//all below go into phpbb_posts_text

ALTER TABLE messages ADD bbcode_uid VARCHAR(10) DEFAULT NULL

// setup post_subject
ALTER TABLE messages ADD post_subject VARCHAR(60)
UPDATE messages SET post_subject=message

//setup post_text
ALTER TABLE messages ADD post_text TEXT
UPDATE messages SET post_text=body

//create the tables to export
// the first two can be populated at this point

CREATE TABLE insert_posts SELECT post_id,topic_id,forum_id,poster_id,post_time,poster_ip,post_username,
enable_bbcode,enable_html,enable_smilies,enable_sig,post_edit_time,
post_edit_count FROM messages

CREATE TABLE insert_posts_text SELECT post_id,bbcode_uid,post_subject,post_text FROM messages


CREATE TABLE insert_topics (
	topic_id MEDIUMINT(8) unsigned,
	forum_id SMALLINT(8) unsigned DEFAULT 17,
	topic_title CHAR(60),
	topic_poster MEDIUMINT(8) DEFAULT -1,
	topic_time INT(11),
	topic_views MEDIUMINT(8) unsigned DEFAULT 0,
	topic_replies MEDIUMINT(8) unsigned,
	topic_status TINYINT(3) DEFAULT 0,
	topic_vote TINYINT(1) DEFAULT 0,
	topic_type TINYINT(3) DEFAULT 0,
	topic_first_post_id MEDIUMINT(8) unsigned,
	topic_last_post_id MEDIUMINT(8) unsigned,
	topic_moved_id MEDIUMINT(8) unsigned DEFAULT 0
)


// populate the topic_id
INSERT INTO insert_topics (topic_id)(SELECT DISTINCT topic_id FROM insert_posts)

// this fills the topic_last_post_id
CREATE TABLE tmp (select MAX(insert_posts.post_id) as max, insert_topics.topic_id AS id  
FROM insert_posts,insert_topics 
WHERE insert_posts.topic_id=insert_topics.topic_id 
GROUP BY insert_topics.topic_id 
ORDER BY insert_posts.topic_id);
UPDATE insert_topics,tmp SET topic_last_post_id=max WHERE topic_id=id
DROP TABLE tmp

// many of the fields we need come from the first post
CREATE TABLE tmp (select MIN(insert_posts.post_id) as min, insert_topics.topic_id AS id, COUNT(insert_posts.post_id) AS count  
FROM insert_posts,insert_topics 
WHERE insert_posts.topic_id=insert_topics.topic_id 
GROUP BY insert_topics.topic_id 
ORDER BY insert_posts.topic_id)
UPDATE insert_topics,tmp SET topic_first_post_id=min,topic_replies=count WHERE topic_id=id
DROP TABLE tmp

// use topic_first_post_id to reference topic_time and 
UPDATE insert_topics,insert_posts
SET insert_topics.topic_time=insert_posts.post_time 
WHERE insert_posts.post_id=insert_topics.topic_first_post_id

UPDATE insert_topics,insert_posts_text
SET insert_topics.topic_title=insert_posts_text.post_subject
WHERE insert_posts_text.post_id=insert_topics.topic_first_post_id



Now insert the stuff into the appropriate phpbb tables,
go to admin page, and resync the forum they got dumped in