Converting Vanilla forum posts to PHPBB2

CLEANUP
This was just code I used in the prototyping. Probably wouldn't be needed for you.

Code:

delete from phpbb_topics where topic_id > 2 ;
delete from phpbb_posts where post_id > 2;
delete from phpbb_users where user_id > 2;

]

[h1]User Transfer[/h1]

The password hashing for PHPBB2, and Vanilla is the same.

Code:

INSERT INTO `phpbb_users` (`user_id`,`username` , `user_password` , `user_lastvisit` , `user_regdate` , `user_level` , `user_posts`, `user_email`)
select (`userid`+1) ,`Name`,`Password`,unix_timestamp(`DateLastActive`),unix_timestamp(`DateFirstVisit`),0,`CountComments`,`email`
from `LUM_User`
where `userid` > 1

[h1]Bring over the topics from the Vanilla Discussions table.[/h1]
Storing the "Discussion ID" in the "replies" field for future reference.

Code:

insert into `phpbb_topics` (`forum_id`, `topic_title`, `topic_poster`, `topic_time`, `topic_last_post_uid`,`topic_replies`)
SELECT `CategoryID`,`Name`,(`AuthUserID`+1),unix_timestamp(`DateCreated`),(`LastUserID`+1),`DiscussionID`
FROM `LUM_Discussion`
where `WhisperUserID` = 0
--topic replies = original post id!!!!

[h1]Convert the Categories to Forums.[/h1]
Here I set the forum ID's, you will need to set these up manually.
You can see the syntax here.

It would be much easier just to bring Vanilla's categories over, but I chose to change the structure for the new forum.

Vanilla and PHPBB differ to some degree here.

Code:

Update phpbb_topics set forum_id = '2' where forum_id = '3'; -- General
Update phpbb_topics set forum_id = '16' where forum_id = '24'; -- Site Info
Update phpbb_topics set forum_id = '13' where forum_id = '11'; -- Admin Archive
Update phpbb_topics set forum_id = '4' where forum_id = '23'; -- ASM Info
Update phpbb_topics set forum_id = '15' where forum_id = '17'; -- Files and Tools
Update phpbb_topics set forum_id = '3' where forum_id = '18'; -- Spam
Update phpbb_topics set forum_id = '14' where forum_id = '20'; -- Archive
Update phpbb_topics set forum_id = '9' where forum_id = '25'; -- Archive

[h1]Post Headers:[/h1]
Here I connect Vanilla's comments table with the PHPBB Topics table. I join it to the phpbb Topics table
to get the new topic ID, and forum ID.

Code:

insert into `phpbb_posts` (`topic_id`,`forum_id`,`poster_id`,`post_time`,`enable_html`,`post_username`)
select p.topic_id, p.forum_id, (l.authuserid+1),unix_timestamp(`datecreated`),'1', l.commentid
from `LUM_Comment` l,`phpbb_topics` p
where l.discussionid = p.topic_replies

[h1]Post Bodies[/h1]

Here I have to join 3 tables to populate the posts_text table. One column from each table is used to populate the required information.

You can see the joins, LUM_Discussion->LUM_Comment->phpbb_posts .

Code:

insert into phpbb_posts_text (`post_id`,`post_subject`,`post_text` )
select pos.post_id, dis.name, com.body
from `LUM_Comment` com, `phpbb_posts` pos, `LUM_Discussion` dis
where com.CommentID = pos.post_username
and dis.DiscussionID = com.DiscussionID

[h1]Topic First Post / Last Post [/h1]

Code:

update phpbb_topics t
set topic_first_post_id = (select min(post_id) from phpbb_posts p where p.topic_id = t.topic_id),
topic_last_post_id = (select max(post_id) from phpbb_posts p where p.topic_id = t.topic_id),
topic_time = (select max(post_time) from phpbb_posts p where p.topic_id = t.topic_id)

[h1]Topic Replies[/h1]
Update the topic replies, by counting posts from the phpbb_posts table.

Code:

update `phpbb_topics` t
set `topic_replies` = (select count(*) from `phpbb_posts` where topic_id = t.topic_id);

Since we don't count the original post as a reply, we will quickly subtract 1 from each count.

Code:

update `phpbb_topics` t
set `topic_replies` = (`topic_replies`-1);