Code: Select all
delete from phpbb_topics where topic_id > 2 ;
delete from phpbb_posts where post_id > 2;
delete from phpbb_users where user_id > 2;
User Transfer
The password hashing for PHPBB2, and Vanilla is the same.
Code: Select all
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
Bring over the topics from the Vanilla Discussions table.
Storing the "Discussion ID" in the "replies" field for future reference.
Code: Select all
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!!!!
Convert the Categories to Forums.
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: Select all
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
Post Headers:
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: Select all
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
Post Bodies
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: Select all
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
Topic First Post / Last Post
Code: Select all
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)
Topic Replies
Update the topic replies, by counting posts from the phpbb_posts table.
Code: Select all
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: Select all
update `phpbb_topics` t
set `topic_replies` = (`topic_replies`-1);