https://www.icyphoenix.com/viewtopic.php?f=35&t=5238&p=35223#p35223 ----------------------------------- alephlg Tue 30 Dec, 2008 13:15 HOWTO Migrate Drupal 6.4 To Icy Phoenix 1.2.0.27c ----------------------------------- Please replace db_from and db_to to your databases's name, and run correct permissions after backup [b][color=#d80000]ALL[/color][/b] databases. This is v1.0 script: [spoiler][code linenumbers=false] -- EXAMPLE script!! -- Pre-request: -- The blog forums: 32 (replace to your blog forum's number) -- All forum insert into (exist) test forum 1 but this forum is empty. -- Copy the Drupal avatars from sites/default/files/pictures/* to images/avatars/sites/default/files/pictures/ -- Poll will truncate. :-( -- Start: USE db_from; -- users INSERT INTO db_to.ip_users (user_id,user_active,username,user_password, user_lastvisit, user_regdate, user_posts, user_timezone, user_style, user_lang, user_dateformat, user_avatar, user_avatar_type, user_email, user_sig, user_from_flag, user_icq, user_website, user_aim, user_yim, user_msnm, user_occ, user_interests, user_skype, user_selfdes) SELECT users.uid, 1, users.name, users.pass, users.access, users.created, count(c.cid) AS user_posts, '1.00', 3, 'english', 'l, d F Y, H:i', users.picture, '1', users.mail AS users_email, replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(users.signature, '

', ''), '', '[b]'), '', '[/b]'), 'é', 'é'), 'ö', 'ö'), 'á', 'á'), 'í', 'í'), 'Í', 'Í'), 'Ö', 'Ö'), 'ü', 'ü'), 'Ü', 'Ű'), '', '[i]'), '', '[/i]'), '', '[i]'), '', '[/i]'), 'ú', 'ú'), ' ', ' '), '

', ''), 'Ú', 'Ú'), '', 'n'), '
', 'n'), '">', ']'), '', '[code]'), '', '[/code]'), 'english.png', (SELECT value from profile_values WHERE fid = "8" and profile_values.uid = users.uid) as icq, (SELECT value from profile_values WHERE fid = "14" and profile_values.uid = users.uid) as url, (SELECT value from profile_values WHERE fid = "11" and profile_values.uid = users.uid) as aim, (SELECT value from profile_values WHERE fid = "10" and profile_values.uid = users.uid) as yim, (SELECT value from profile_values WHERE fid = "9" and profile_values.uid = users.uid) as msnm, (SELECT value from profile_values WHERE fid = "7" and profile_values.uid = users.uid) as occ, replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace ((SELECT value from profile_values WHERE fid = "15" and profile_values.uid = users.uid), '

', ''), '', '[b]'), '', '[/b]'), 'é', 'é'), 'ö', 'ö'), 'á', 'á'), 'í', 'í'), 'Í', 'Í'), 'Ö', 'Ö'), 'ü', 'ü'), 'Ü', 'Ű'), '', '[i]'), '', '[/i]'), '', '[i]'), '', '[/i]'), 'ú', 'ú'), ' ', ' '), '

', ''), 'Ú', 'Ú'), '', 'n'), '
', 'n'), '">', ']'), '', '[code]'), '', '[/code]') as interests, (SELECT value from profile_values WHERE fid = "13" and profile_values.uid = users.uid) as skype, replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace ((SELECT value from profile_values WHERE fid = "17" and profile_values.uid = users.uid), '

', ''), '', '[b]'), '', '[/b]'), 'é', 'é'), 'ö', 'ö'), 'á', 'á'), 'í', 'í'), 'Í', 'Í'), 'Ö', 'Ö'), 'ü', 'ü'), 'Ü', 'Ű'), '', '[i]'), '', '[/i]'), '', '[i]'), '', '[/i]'), 'ú', 'ú'), ' ', ' '), '

', ''), 'Ú', 'Ú'), '', 'n'), '
', 'n'), '">', ']'), '', '[code]'), '', '[/code]') as qq FROM users left join comments c using (uid) WHERE users.uid != '0' GROUP BY users.uid; -- Exist user update: -- update kozosseg.ip_users -- set user_posts = (select count(cid) -- from from_db.comments -- where uid = 2) where user_id = 2; -- fórumok INSERT INTO db_to.ip_forums SELECT f.tid as forum_id,'1' as cat_id, td.name as name, td.description, 0, 0, 20, COUNT(DISTINCT c.cid) + COUNT(DISTINCT n.nid), COUNT(DISTINCT n.nid), max(c.cid), 1, '', 0, 0, 0, '', 'c','' , 0, 0, 0, 1, 1, 1, 3, 3, 5, 5, 5, 5, 1, 1, 1, 1, 3, 5, 1, -1, '', 0, '', 0, 0, 0, 1 FROM term_node tn left join term_data td using (tid) left join term_hierarchy th using (tid) left join forum f using (tid) left join node n on f.nid = n.nid left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid left join comments c on nr.nid = c.nid left join comment_notify cn using (cid) where td.vid = '1' group by f.tid; -- Start posts per topics INSERT INTO db_to.ip_posts (topic_id, forum_id, poster_id, post_time, poster_ip, post_username) SELECT nr.nid, CASE n.type WHEN 'story' THEN 1 WHEN 'poll' THEN 1 WHEN 'blog' THEN 32 ELSE f.tid END AS forum_id, iu.user_id, n.created, 'f0000001' as hostname, iu.username FROM node n left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid cross join users u on u.uid = nr.uid left join kozosseg.ip_users iu on u.name = iu.username left join forum f on n.nid = f.nid WHERE n.type REGEXP '(forum)|(story)|(poll)|(blog)' group by nr.nid; -- ...and post_text INSERT INTO kozosseg.ip_posts_text (post_id, bbcode_uid, post_subject, post_text) SELECT p.post_id, substring(sha(nr.title),1,10) AS bbcode_uid, nr.title, replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (nr.body, '

', ''), '', '[b]'), '', '[/b]'), 'é', 'é'), 'ö', 'ö'), 'á', 'á'), 'í', 'í'), 'Í', 'Í'), 'Ö', 'Ö'), 'ü', 'ü'), 'Ü', 'Ű'), '', '[i]'), '', '[/i]'), '', '[i]'), '', '[/i]'), 'ú', 'ú'), ' ', ' '), '

', ''), 'Ú', 'Ú'), '', 'n'), '
', 'n'), '">', ']'), '', '[code]'), '', '[/code]'), '

', '[hr]'), 'n', ''), '', '') AS post_text FROM node n left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid cross join users u on u.uid = nr.uid left join forum f on n.nid = f.nid right join kozosseg.ip_posts p on n.created = p.post_time WHERE n.type REGEXP '(forum)|(story)|(poll)|(blog)' group by nr.nid; -- topics INSERT INTO db_to.ip_topics (topic_id, forum_id, topic_title, topic_desc, topic_poster, topic_time, topic_views, topic_replies, topic_first_post_id, topic_last_post_id, news_id) SELECT n.nid, CASE n.type WHEN 'story' THEN '1' WHEN 'poll' THEN '1' WHEN 'blog' THEN '2' ELSE f.tid END AS forum_id, n.title, '', CASE n.uid WHEN 1 THEN 3 WHEN 3 THEN 2 WHEN 7 THEN 4 ELSE n.uid END AS uid, n.created, nc.totalcount, COUNT(DISTINCT c.cid) + COUNT(DISTINCT n.nid), p.post_id, max(c.cid), if(n.type = 'story', 1, 0) FROM node n left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid left join comments c on nr.nid = c.nid left join comment_notify cn using (cid) left join forum f on n.nid = f.nid left join kozosseg.ip_users u on c.name = u.username right join kozosseg.ip_posts p on n.created = p.post_time WHERE n.type REGEXP '(forum)|(story)|(poll)|(blog)' GROUP BY n.nid order by n.nid; INSERT INTO db_to.ip_topic_view SELECT n.nid, u.user_id, nc.timestamp, nc.totalcount FROM node n left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid left join comments c on nr.nid = c.nid left join comment_notify cn using (cid) left join kozosseg.ip_users u on c.name = u.username where n.type REGEXP '(forum)|(story)|(poll)|(blog)' group by n.nid order by n.nid; INSERT INTO db_to.ip_posts (topic_id, forum_id, poster_id, post_time, poster_ip, post_username) SELECT nr.nid, CASE n.type WHEN 'story' THEN '1' WHEN 'poll' THEN '1' WHEN 'blog' THEN '2' ELSE f.tid END AS forum_id, u.user_id, c.timestamp, lower(concat(conv(substring_index(c.hostname,'.',1), 10, 16), conv(substring_index(substring_index(c.hostname,'.',2), '.', -1), 10, 16), conv(substring_index(substring_index(c.hostname,'.',3), '.', -1), 10, 16), conv(substring_index(c.hostname,'.',-1), 10, 16))) as hostname, c.name FROM node n left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid left join comments c on nr.nid = c.nid left join comment_notify cn using (cid) left join forum f on f.nid = n.nid left join kozosseg.ip_users u on c.name = u.username WHERE n.type REGEXP '(forum)|(story)|(poll)|(blog)' group by c.cid order by c.cid; INSERT INTO db_to.ip_posts_text (post_id, bbcode_uid, post_subject, post_text) SELECT p.post_id, substring(cn.notify_hash,1,10) AS bbcode_uid, c.subject, replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace(replace(replace (replace(replace(replace( replace(replace(c.comment, '

', ''), '', '[b]'), '', '[/b]'), 'é', 'é'), 'ö', 'ö'), 'á', 'á'), 'í', 'í'), 'Í', 'Í'), 'Ö', 'Ö'), 'ü', 'ü'), 'Ü', 'Ű'), '', '[i]'), '', '[/i]'), '', '[i]'), '', '[/i]'), 'ú', 'ú'), ' ', ' '), '

', ''), 'Ú', 'Ú'), '', 'n'), '
', 'n'), '">', ']'), '', '[code]'), '', '[/code]'), '

', '[hr]'), 'n', ''), '', '') AS post_text FROM node n left join node_counter nc on n.nid = nc.nid left join node_comment_statistics ncs on n.nid = ncs.nid left join node_revisions nr on ncs.nid = nr.nid left join comments c on nr.nid = c.nid left join comment_notify cn using (cid) left join forum f on f.nid = n.nid right join kozosseg.ip_posts p on c.timestamp = p.post_time WHERE n.type REGEXP '(forum)|(story)|(poll)|(blog)' group by c.cid order by c.cid; UPDATE db_to.ip_forums set forum_posts = (select count(post_id) from kozosseg.ip_posts where forum_id = 1 group by forum_id = 1), forum_topics = (select count(DISTINCT topic_id) from kozosseg.ip_posts where forum_id = 1 group by forum_id = 1), forum_posts = (select poster_id AS forum_last_post_id from kozosseg.ip_posts where forum_id = 1 order by post_time DESC limit 1) where forum_id = 1; UPDATE db_to.ip_forums set forum_posts = (select count(post_id) from kozosseg.ip_posts where forum_id = 2 group by forum_id = 2), forum_topics = (select count(DISTINCT topic_id) from kozosseg.ip_posts where forum_id = 2 group by forum_id = 2), forum_posts = (select poster_id AS forum_last_post_id from kozosseg.ip_posts where forum_id = 2 order by post_time DESC limit 1) where forum_id = 2; -- update user groups (example 3 new users) USE db_to; INSERT ip_user_group (group_id,user_id) VALUES ('7','5'); INSERT ip_user_group (group_id,user_id) VALUES ('5','5'); INSERT ip_user_group (group_id,user_id) VALUES ('8','6'); INSERT ip_user_group (group_id,user_id) VALUES ('5','6'); INSERT ip_user_group (group_id,user_id) VALUES ('9','7'); INSERT ip_user_group (group_id,user_id) VALUES ('5','7'); INSERT ip_groups VALUES ('8', 1, '', 'Personal User', 0, 1, 0, '', 0, 0, 99999999, 99999999, 0, 0, 1, 0, 0, 0); INSERT ip_groups VALUES ('9', 1, '', 'Personal User', 0, 1, 0, '', 0, 0, 99999999, 99999999, 0, 0, 1, 0, 0, 0); INSERT ip_groups VALUES ('10', 1, '', 'Personal User', 0, 1, 0, '', 0, 0, 99999999, 99999999, 0, 0, 1, 0, 0, 0); -- replace in posts_text.post_text from after: -- update db_to.ip_posts_text set post_text = replace(post_text, '