This is v1.0 script:
Spoiler: [ Show ]
Spoiler: [ Hide ]
Code: [Show]
-- 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, '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</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), '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</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), '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</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, '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</code>', '[/code]'),
'<hr />', '[hr]'), '<!--break-->n', ''),
'<!--break-->', '') 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, '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="/', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</code>', '[/code]'),
'<hr />', '[hr]'), '<!--break-->n', ''),
'<!--break-->', '') 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, '<a href', '[url');
-- 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, '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</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), '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</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), '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</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, '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</code>', '[/code]'),
'<hr />', '[hr]'), '<!--break-->n', ''),
'<!--break-->', '') 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, '<p>', ''),
'<strong>', '[b]'), '</strong>', '[/b]'),
'é', 'é'), 'ö', 'ö'),
'á', 'á'), 'í', 'í'),
'Í', 'Í'), 'Ö', 'Ö'),
'ü', 'ü'), 'Ü', 'Ű'),
'<em>', '[i]'), '</em>', '[/i]'),
'<cite>', '[i]'), '</cite>', '[/i]'),
'ú', 'ú'), ' ', ' '),
'<p>', ''), 'Ú', 'Ú'),
'<a href="/', '[url='), '</a>', '[/url]'),
'<a>', '[url]'), 'ó', 'ó'),
'Ó', 'Ó'), 'É', 'É'),
'é', 'é'), '"', '"'),
'</p>', 'n'), '<br />', 'n'), '">', ']'),
'<code>', '[code]'), '</code>', '[/code]'),
'<hr />', '[hr]'), '<!--break-->n', ''),
'<!--break-->', '') 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, '<a href', '[url');
After run the script please edit an forum description: must update include/def_tree.php
This script test in migrating the mandrivalinux.hu
Please send the bugs in this topics.
Aleph