Forum conversion » History » Revision 24
Revision 23 (Amber Herold, 07/09/2010 04:19 PM) → Revision 24/41 (Amber Herold, 07/09/2010 04:54 PM)
h1. Forum conversion
Notes on porting the Leginon BB to Redmine
# Add Forums
## create new colum for bb forum id
## insert the bb forums
# Add column to redmine message db for bb post id
# Import all posts to current forums (map forum id's), saving bb post id to new column
# enter messages parent ids
# edit posts to put into new forum
# delete unwanted forums
h2. Redmine DB Tables
+Messages+
id board_id parent_id subject content author_id replies_count last_reply_id created_on updated_on locked sticky
+Boards+
id project_id name description position topics_count messages_count last_message_id
+Users+
id login hashed_password firstname lastname mail mail_notification admin status last_login_on language auth_source_id created_on updated_on type identity_url
h2. BB DB Tables
+Notre_forums+
forum_id parent_id left_id right_id forum_parents forum_name forum_desc forum_desc_bitfield forum_desc_options forum_desc_uid forum_link forum_password forum_style forum_image forum_rules forum_rules_link forum_rules_bitfield forum_rules_options forum_rules_uid forum_topics_per_page forum_type forum_status forum_posts forum_topics forum_topics_real forum_last_post_id forum_last_poster_id forum_last_post_subject forum_last_post_time forum_last_poster_name forum_last_poster_colour forum_flags display_subforum_list display_on_index enable_indexing enable_icons enable_prune prune_next prune_days prune_viewed prune_freq
+Notre_posts+
post_id topic_id forum_id poster_id icon_id poster_ip post_time post_approved post_reported enable_bbcode enable_smilies enable_magic_url enable_sig post_username post_subject post_text post_checksum post_attachment bbcode_bitfield bbcode_uid post_postcount post_edit_time post_edit_reason post_edit_user post_edit_count post_edit_locked
+Notre_topics+
topic_id forum_id icon_id topic_attachment topic_approved topic_reported topic_title topic_poster topic_time topic_time_limit topic_views topic_replies topic_replies_real topic_status topic_type topic_first_post_id topic_first_poster_name topic_first_poster_colour topic_last_post_id topic_last_poster_id topic_last_poster_name topic_last_poster_colour topic_last_post_subject topic_last_post_time topic_last_view_time topic_moved_id topic_bumped topic_bumper poll_title poll_start poll_length poll_max_options poll_last_vote poll_vote_change
+Notre_users+
user_id user_type group_id user_permissions user_perm_from user_ip user_regdate username username_clean user_password user_passchg user_pass_convert user_email user_email_hash user_birthday user_lastvisit user_lastmark user_lastpost_time user_lastpage user_last_confirm_key user_last_search user_warnings user_last_warning user_login_attempts user_inactive_reason user_inactive_time user_posts user_lang user_timezone user_dst user_dateformat user_style user_rank user_colour user_new_privmsg user_unread_privmsg user_last_privmsg user_message_rules user_full_folder user_emailtime user_topic_show_days user_topic_sortby_type user_topic_sortby_dir user_post_show_days user_post_sortby_type user_post_sortby_dir user_notify user_notify_pm user_notify_type user_allow_pm user_allow_viewonline user_allow_viewemail user_allow_massemail user_options user_avatar user_avatar_type user_avatar_width user_avatar_height user_sig user_sig_bbcode_uid user_sig_bbcode_bitfield user_from user_icq user_aim user_yim user_msnm user_jabber user_website user_occ user_interests user_actkey user_newpasswd user_form_salt
h2. Conversion tables
h3. Redmine Users
Search for the user to have an existing entry, if not their posts will be assigned to anonymous.
|_.Redmine Entry|_.BB Entry|
|id||
|login|notre_users->username|
|hashed_password||
|firstname||
|lastname||
|mail|notre_users->user_email|
|mail_notification||
|admin||
|status||
|last_login_on||
|language|notre_users->user_lang|
|auth_source_id||
|created_on||
|updated_on||
|type||
|identity_url||
h3. Redmine Boards
We will create these from scratch - Development, Using Leginon, Using Appion, Tomography, Installation, Administration Tools
|_.Redmine Entry|_.BB Entry|
|id||
|project_id|(the leginon id)|
|name|notre_forums->forum_name|
|description|notre_forums->forum_desc|
|position||
|topics_count|notre_forums->forum_topics|
|messages_count|notre_forums->forum_posts|
|last_message_id|After all messages are ported add: notre_forums->forum_last_post_id->Redmine Messages->bb_post_id->id|
|bb_forum_id|notre_forums->forum_id|
h3. Redmine Messages
|_.Redmine Entry|_.BB Entry|
|id||
|board_id|notre_posts->forum_id->Redmine Boards->bb_forum_id->id|
|parent_id|Add these as second step after all posts moved over, notre_posts->topic_id->notre_topics->topic_first_post_id->Redmine Messages->bb_post_id->id|
|subject|notre_posts->post_subject|
|content|notre_posts->post_text (need to get BLOB)|
|author_id|notre_posts->poster_id->notre_users->user_email->Users->mail->Users->id, if does not exist assign to anonymous|
|replies_count|Set all to 0 then check Notre_topics->topic_first_post_id and update that message with Notre_topics->topic_replies|
|last_reply_id|Set all to NULL then check notre_topics->topic_first_post_id->Redmine Messages->bb_post_id->id and update that message with notre_topics->topic_last_post_id->Redmine Messages->bb_post_id->id|
|created_on|notre_posts->post_time (may need a conversion)|
|updated_on|notre_posts->post_edit_time (may need a conversion)|
|locked|notre_posts->post_edit_locked|
|sticky|0|
|bb_post_id|notre_posts->post_id|
h2. Commands
h3. Make backups
First backup the tables that we will be modifying:
bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine boards > boards-preport.sql
bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine messages > messages-preport.sql
h3. Add temporary columns to the boards and messages tables
<pre>
ALTER TABLE boards
ADD bb_post_id int(11);
ALTER TABLE messages
ADD bb_forum_id int(11);
</pre>
h3. Add forums
<pre>
INSERT INTO redmine.boards (project_id, name, description, topics_count, messages_count, bb_forum_id)
SELECT redmine.projects.id, bb2.notre_forums.forum_name, bb2.notre_forums.forum_desc, bb2.notre_forums.forum_topics, bb2.notre_forums.forum_posts, bb2.notre_forums.forum_id
FROM redmine.projects, bb2.notre_forums
WHERE redmine.projects.identifier="leginon"
</pre>
Inserts 9 rows.
h3. Add Messages
<pre>
INSERT INTO redmine.messages (board_id, subject, content, locked, sticky, bb_post_id)
SELECT redmine.boards.id, bb2.notre_posts.post_subject, bb2.notre_posts.post_text, bb2.notre_posts.post_edit_locked, 0, bb2.notre_posts.post_id
FROM bb2.notre_posts, redmine.boards
WHERE bb2.notre_posts.forum_id = redmine.boards.bb_forum_id;
</pre>
inserted 603 rows.
h3. Add parent_id to messages
<pre>
UPDATE redmine.messages
SET redmine.messages.parent_id=redmine.messages.id
WHERE redmine.messages.id IN
(
SELECT redmine.messages.id
FROM redmine.messages
WHERE redmine.messages.bb_post_id IN
(
SELECT bb2.notre_topics.topic_first_post_id
FROM bb2.notre_topics
WHERE bb2.notre_topics.topic_id IN
(
SELECT bb2.notre_posts.topic_id
FROM bb2.notre_posts, redmine.messages
WHERE redmine.messages.bb_post_id = bb2.notre_posts.post_id
)
)
);
</pre>
To avoid a "bug with mySQL":http://emg.nysbc.org/projects/amigroup/wiki/Mysql_Nested_Subqueries_Problem , rewrite the above query:
<pre>
UPDATE redmine.messages
SET redmine.messages.parent_id= redmine.messages.parent_id=redmine.messages.id
WHERE redmine.messages.id IN
(
SELECT r_messages.id redmine.messages.id
FROM (SELECT * FROM redmine.messages) AS r_messages
INNER JOIN
(
SELECT bb2.notre_topics.topic_first_post_id AS TopicFirstPostId
FROM bb2.notre_topics
INNER JOIN
(
SELECT bb2.notre_posts.topic_id AS TopicId
FROM bb2.notre_posts, redmine.messages
WHERE redmine.messages.bb_post_id=bb2.notre_posts.post_id
) AS TopicIds
ON bb2.notre_topics.topic_id=TopicIds.TopicId
) AS TopicFirstPostIds
ON r_messages.bb_post_id=TopicFirstPostIds.TopicFirstPostId
);
</pre>
h3. Convert Unix timestamps to ISO 8601 and add to messages
bb2.notre_posts.post_time, bb2.notre_posts.post_edit_time,
http://aruljohn.com/timestamp2date.html
h3. Add author_id to messages
h3. Add replies_count to messages
h3. Add last_reply_id to messages
h3. Create a backup of the tables
bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine boards > boards-postport.sql
bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine messages > messages-postport.sql
h3. Remove the temporary columns
<pre>
ALTER TABLE boards
DROP COLUMN bb_post_id;
ALTER TABLE messages
DROP COLUMN bb_forum_id;
</pre>