Forum conversion » History » Version 24
Amber Herold, 07/09/2010 04:54 PM
1 | 1 | Amber Herold | h1. Forum conversion |
---|---|---|---|
2 | |||
3 | Notes on porting the Leginon BB to Redmine |
||
4 | |||
5 | 14 | Amber Herold | # Add Forums |
6 | ## create new colum for bb forum id |
||
7 | ## insert the bb forums |
||
8 | # Add column to redmine message db for bb post id |
||
9 | # Import all posts to current forums (map forum id's), saving bb post id to new column |
||
10 | # enter messages parent ids |
||
11 | 10 | Amber Herold | # edit posts to put into new forum |
12 | # delete unwanted forums |
||
13 | 1 | Amber Herold | |
14 | 10 | Amber Herold | |
15 | 1 | Amber Herold | h2. Redmine DB Tables |
16 | |||
17 | +Messages+ |
||
18 | |||
19 | id board_id parent_id subject content author_id replies_count last_reply_id created_on updated_on locked sticky |
||
20 | |||
21 | +Boards+ |
||
22 | |||
23 | id project_id name description position topics_count messages_count last_message_id |
||
24 | |||
25 | |||
26 | +Users+ |
||
27 | |||
28 | 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 |
||
29 | |||
30 | |||
31 | h2. BB DB Tables |
||
32 | 2 | Amber Herold | |
33 | +Notre_forums+ |
||
34 | |||
35 | 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 |
||
36 | 3 | Amber Herold | |
37 | 20 | Amber Herold | +Notre_posts+ |
38 | 3 | Amber Herold | |
39 | 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 |
||
40 | |||
41 | 4 | Amber Herold | |
42 | |||
43 | +Notre_topics+ |
||
44 | |||
45 | 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 |
||
46 | |||
47 | 3 | Amber Herold | +Notre_users+ |
48 | |||
49 | 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 |
||
50 | 5 | Amber Herold | |
51 | |||
52 | 7 | Amber Herold | h2. Conversion tables |
53 | 1 | Amber Herold | |
54 | 7 | Amber Herold | |
55 | 1 | Amber Herold | h3. Redmine Users |
56 | |||
57 | 14 | Amber Herold | Search for the user to have an existing entry, if not their posts will be assigned to anonymous. |
58 | |||
59 | 1 | Amber Herold | |_.Redmine Entry|_.BB Entry| |
60 | 5 | Amber Herold | |id|| |
61 | 7 | Amber Herold | |login|notre_users->username| |
62 | 1 | Amber Herold | |hashed_password|| |
63 | 5 | Amber Herold | |firstname|| |
64 | |lastname|| |
||
65 | 19 | Amber Herold | |mail|notre_users->user_email| |
66 | 1 | Amber Herold | |mail_notification|| |
67 | 5 | Amber Herold | |admin|| |
68 | |status|| |
||
69 | |last_login_on|| |
||
70 | 19 | Amber Herold | |language|notre_users->user_lang| |
71 | 5 | Amber Herold | |auth_source_id|| |
72 | |created_on|| |
||
73 | |updated_on|| |
||
74 | |type|| |
||
75 | |identity_url|| |
||
76 | 8 | Amber Herold | |
77 | 1 | Amber Herold | |
78 | 10 | Amber Herold | h3. Redmine Boards |
79 | |||
80 | 1 | Amber Herold | We will create these from scratch - Development, Using Leginon, Using Appion, Tomography, Installation, Administration Tools |
81 | |||
82 | |_.Redmine Entry|_.BB Entry| |
||
83 | 9 | Amber Herold | |id|| |
84 | 13 | Amber Herold | |project_id|(the leginon id)| |
85 | 19 | Amber Herold | |name|notre_forums->forum_name| |
86 | |description|notre_forums->forum_desc| |
||
87 | 1 | Amber Herold | |position|| |
88 | 19 | Amber Herold | |topics_count|notre_forums->forum_topics| |
89 | |messages_count|notre_forums->forum_posts| |
||
90 | 20 | Amber Herold | |last_message_id|After all messages are ported add: notre_forums->forum_last_post_id->Redmine Messages->bb_post_id->id| |
91 | 19 | Amber Herold | |bb_forum_id|notre_forums->forum_id| |
92 | 9 | Amber Herold | |
93 | 1 | Amber Herold | |
94 | 9 | Amber Herold | |
95 | 1 | Amber Herold | h3. Redmine Messages |
96 | 9 | Amber Herold | |
97 | 1 | Amber Herold | |_.Redmine Entry|_.BB Entry| |
98 | |id|| |
||
99 | 19 | Amber Herold | |board_id|notre_posts->forum_id->Redmine Boards->bb_forum_id->id| |
100 | |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| |
||
101 | |subject|notre_posts->post_subject| |
||
102 | |content|notre_posts->post_text (need to get BLOB)| |
||
103 | |author_id|notre_posts->poster_id->notre_users->user_email->Users->mail->Users->id, if does not exist assign to anonymous| |
||
104 | 12 | Amber Herold | |replies_count|Set all to 0 then check Notre_topics->topic_first_post_id and update that message with Notre_topics->topic_replies| |
105 | 19 | Amber Herold | |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| |
106 | |created_on|notre_posts->post_time (may need a conversion)| |
||
107 | 20 | Amber Herold | |updated_on|notre_posts->post_edit_time (may need a conversion)| |
108 | |locked|notre_posts->post_edit_locked| |
||
109 | |sticky|0| |
||
110 | 19 | Amber Herold | |bb_post_id|notre_posts->post_id| |
111 | 15 | Amber Herold | |
112 | |||
113 | |||
114 | h2. Commands |
||
115 | |||
116 | |||
117 | h3. Make backups |
||
118 | |||
119 | First backup the tables that we will be modifying: |
||
120 | |||
121 | bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine boards > boards-preport.sql |
||
122 | |||
123 | bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine messages > messages-preport.sql |
||
124 | 16 | Amber Herold | |
125 | |||
126 | h3. Add temporary columns to the boards and messages tables |
||
127 | |||
128 | <pre> |
||
129 | ALTER TABLE boards |
||
130 | ADD bb_post_id int(11); |
||
131 | |||
132 | ALTER TABLE messages |
||
133 | ADD bb_forum_id int(11); |
||
134 | </pre> |
||
135 | 1 | Amber Herold | |
136 | 16 | Amber Herold | |
137 | 1 | Amber Herold | |
138 | 18 | Amber Herold | h3. Add forums |
139 | 1 | Amber Herold | |
140 | 18 | Amber Herold | <pre> |
141 | INSERT INTO redmine.boards (project_id, name, description, topics_count, messages_count, bb_forum_id) |
||
142 | 1 | Amber Herold | 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 |
143 | FROM redmine.projects, bb2.notre_forums |
||
144 | WHERE redmine.projects.identifier="leginon" |
||
145 | </pre> |
||
146 | |||
147 | 20 | Amber Herold | Inserts 9 rows. |
148 | |||
149 | |||
150 | h3. Add Messages |
||
151 | |||
152 | |||
153 | |||
154 | <pre> |
||
155 | INSERT INTO redmine.messages (board_id, subject, content, locked, sticky, bb_post_id) |
||
156 | 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 |
||
157 | FROM bb2.notre_posts, redmine.boards |
||
158 | WHERE bb2.notre_posts.forum_id = redmine.boards.bb_forum_id; |
||
159 | </pre> |
||
160 | |||
161 | inserted 603 rows. |
||
162 | |||
163 | 21 | Amber Herold | |
164 | h3. Add parent_id to messages |
||
165 | |||
166 | <pre> |
||
167 | UPDATE redmine.messages |
||
168 | SET redmine.messages.parent_id=redmine.messages.id |
||
169 | WHERE redmine.messages.id IN |
||
170 | ( |
||
171 | SELECT redmine.messages.id |
||
172 | FROM redmine.messages |
||
173 | WHERE redmine.messages.bb_post_id IN |
||
174 | ( |
||
175 | SELECT bb2.notre_topics.topic_first_post_id |
||
176 | FROM bb2.notre_topics |
||
177 | WHERE bb2.notre_topics.topic_id IN |
||
178 | ( |
||
179 | SELECT bb2.notre_posts.topic_id |
||
180 | FROM bb2.notre_posts, redmine.messages |
||
181 | 23 | Amber Herold | WHERE redmine.messages.bb_post_id = bb2.notre_posts.post_id |
182 | 21 | Amber Herold | ) |
183 | ) |
||
184 | ); |
||
185 | </pre> |
||
186 | |||
187 | 22 | Amber Herold | To avoid a "bug with mySQL":http://emg.nysbc.org/projects/amigroup/wiki/Mysql_Nested_Subqueries_Problem , rewrite the above query: |
188 | |||
189 | <pre> |
||
190 | UPDATE redmine.messages |
||
191 | 24 | Amber Herold | SET redmine.messages.parent_id= |
192 | 22 | Amber Herold | ( |
193 | 24 | Amber Herold | SELECT r_messages.id |
194 | 23 | Amber Herold | FROM (SELECT * FROM redmine.messages) AS r_messages |
195 | 22 | Amber Herold | INNER JOIN |
196 | ( |
||
197 | SELECT bb2.notre_topics.topic_first_post_id AS TopicFirstPostId |
||
198 | FROM bb2.notre_topics |
||
199 | INNER JOIN |
||
200 | ( |
||
201 | SELECT bb2.notre_posts.topic_id AS TopicId |
||
202 | 23 | Amber Herold | FROM bb2.notre_posts, redmine.messages |
203 | WHERE redmine.messages.bb_post_id=bb2.notre_posts.post_id |
||
204 | 22 | Amber Herold | ) AS TopicIds |
205 | 23 | Amber Herold | ON bb2.notre_topics.topic_id=TopicIds.TopicId |
206 | 22 | Amber Herold | ) AS TopicFirstPostIds |
207 | 23 | Amber Herold | ON r_messages.bb_post_id=TopicFirstPostIds.TopicFirstPostId |
208 | 22 | Amber Herold | ); |
209 | </pre> |
||
210 | 21 | Amber Herold | |
211 | 20 | Amber Herold | h3. Convert Unix timestamps to ISO 8601 and add to messages |
212 | |||
213 | bb2.notre_posts.post_time, bb2.notre_posts.post_edit_time, |
||
214 | |||
215 | http://aruljohn.com/timestamp2date.html |
||
216 | |||
217 | h3. Add author_id to messages |
||
218 | |||
219 | h3. Add replies_count to messages |
||
220 | |||
221 | h3. Add last_reply_id to messages |
||
222 | 16 | Amber Herold | |
223 | |||
224 | |||
225 | |||
226 | h3. Create a backup of the tables |
||
227 | |||
228 | bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine boards > boards-postport.sql |
||
229 | |||
230 | bq. mysqldump -u amber -p --skip-lock-tables --extended-insert redmine messages > messages-postport.sql |
||
231 | |||
232 | |||
233 | h3. Remove the temporary columns |
||
234 | |||
235 | <pre> |
||
236 | ALTER TABLE boards |
||
237 | DROP COLUMN bb_post_id; |
||
238 | |||
239 | ALTER TABLE messages |
||
240 | DROP COLUMN bb_forum_id; |
||
241 | </pre> |