Leginon

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_forum_id int(11);

ALTER TABLE messages

ADD bb_post_id int(11);

</pre>

h3. Add forums 

<pre>

INSERT INTO ami_redmine.boards (project_id, name, description, topics_count, messages_count, bb_forum_id)

SELECT ami_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 ami_redmine.projects, bb2.notre_forums

WHERE ami_redmine.projects.identifier="leginon" 

</pre>

Inserts 9 rows.

h3. Add Messages

<pre>

INSERT INTO ami_redmine.messages (board_id, subject, content,  locked, sticky, bb_post_id)

SELECT ami_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, ami_redmine.boards

WHERE bb2.notre_posts.forum_id = ami_redmine.boards.bb_forum_id;   

</pre>

inserted 603 rows.

h3. Add parent_id to messages

<pre>

UPDATE ami_redmine.messages AS redMess1, ami_redmine.messages AS redMess2, bb2.notre_topics, bb2.notre_posts

SET redMess1.parent_id=redMess2.id

WHERE redMess1.bb_post_id = bb2.notre_posts.post_id

AND bb2.notre_posts.topic_id = bb2.notre_topics.topic_id

AND bb2.notre_topics.topic_first_post_id = redMess2.bb_post_id

AND redMess1.id != redMess2.id;

</pre>

h3. Add author_id to messages

<pre>

UPDATE ami_redmine.messages, ami_redmine.users, bb2.notre_users, bb2.notre_posts

SET ami_redmine.messages.author_id=redmine.users.id

WHERE ami_redmine.messages.bb_post_id = bb2.notre_posts.post_id

AND bb2.notre_posts.poster_id=bb2.notre_users.user_id

AND bb2.notre_users.user_email = ami_redmine.users.mail;

</pre>

For the messages from people who are not registered in redmine, set the author_id to 2 which is the anonymous user.

<pre>

UPDATE ami_redmine.messages

SET ami_redmine.messages.author_id=2

WHERE ami_redmine.messages.author_id IS NULL;

</pre>

Updates 290 entries.

h3. Add replies_count to messages

<pre>

UPDATE ami_redmine.messages, bb2.notre_topics, bb2.notre_posts

SET ami_redmine.messages.replies_count = bb2.notre_topics.topic_replies

WHERE ami_redmine.messages.parent_id IS NULL

AND ami_redmine.messages.bb_post_id = bb2.notre_posts.post_id

AND bb2.notre_posts.topic_id = bb2.notre_topics.topic_id;

</pre>

h3. Add last_reply_id to messages

<pre>

UPDATE ami_redmine.messages AS redMess1, ami_redmine.messages AS redMess2, bb2.notre_topics, bb2.notre_posts

SET redMess1.last_reply_id = redMess2.id

WHERE redMess1.parent_id IS NULL

AND redMess1.bb_post_id = bb2.notre_posts.post_id

AND bb2.notre_posts.topic_id = bb2.notre_topics.topic_id

AND bb2.notre_topics.topic_last_post_id = redMess2.bb_post_id;

</pre>

h3. Convert Unix timestamps to ISO 8601 and add to messages

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_from-unixtime

http://aruljohn.com/timestamp2date.html

<pre>

UPDATE ami_redmine.messages,  bb2.notre_posts

SET ami_redmine.messages.created_on = FROM_UNIXTIME(bb2.notre_posts.post_time)

WHERE ami_redmine.messages.bb_post_id = bb2.notre_posts.post_id;

</pre>

<pre>

UPDATE ami_redmine.messages,  bb2.notre_posts

SET ami_redmine.messages.updated_on = FROM_UNIXTIME(bb2.notre_posts.post_edit_time)

WHERE ami_redmine.messages.bb_post_id = bb2.notre_posts.post_id

AND bb2.notre_posts.post_edit_time != 0;

</pre>

h3. Add last_reply_id to boards

<pre>

UPDATE ami_redmine.messages AS redMess1, ami_redmine.messages AS redMess2, bb2.notre_topics, bb2.notre_posts

SET redMess1.last_reply_id = redMess2.id

WHERE redMess1.parent_id IS NULL

AND redMess1.bb_post_id = bb2.notre_posts.post_id

AND bb2.notre_posts.topic_id = bb2.notre_topics.topic_id

AND bb2.notre_topics.topic_last_post_id = redMess2.bb_post_id;

</pre>

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_forum_id;

ALTER TABLE messages

DROP COLUMN bb_post_id;

</pre>

h3. For testing content conversion

Get the original content:

<pre>

UPDATE ami_redmine.messages,  bb2.notre_posts

SET ami_redmine.messages.content = bb2.notre_posts.post_text

WHERE ami_redmine.messages.bb_post_id = bb2.notre_posts.post_id;

</pre>