HOWTO Migrate Drupal 6.4 To Icy Phoenix 1.2.0.27c

Posts: 28

Tue 30 Dec, 2008 13:15

Subject: 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 ALL databases.
This is v1.0 script:

Spoiler: [ Show ]

Code: [Download] [Hide] [Select]

-- 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]'), 

			'&eacute;', 'é'), '&ouml;', 'ö'), 

			'&aacute;', 'á'), '&iacute;', 'í'), 

			'&Iacute;', 'Í'), '&Ouml;', 'Ö'), 

			'&uuml;', 'ü'), '&Uuml;', 'Ű'), 

			'<em>', '[i]'), '</em>', '[/i]'),

			'<cite>', '[i]'), '</cite>', '[/i]'), 

			'&uacute;', 'ú'), '&nbsp;', ' '), 

			'<p>', ''), '&Uacute;', 'Ú'), 

			'<a href="', '[url='), '</a>', '[/url]'), 

			'<a>', '[url]'), '&oacute;', 'ó'), 

			'&Oacute;', 'Ó'), '&Eacute;', 'É'), 

			'&eacute;', 'é'), '&quot;', '"'), 

			'</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]'), 

			'&eacute;', 'é'), '&ouml;', 'ö'), 

			'&aacute;', 'á'), '&iacute;', 'í'), 

			'&Iacute;', 'Í'), '&Ouml;', 'Ö'), 

			'&uuml;', 'ü'), '&Uuml;', 'Ű'), 

			'<em>', '[i]'), '</em>', '[/i]'),

			'<cite>', '[i]'), '</cite>', '[/i]'), 

			'&uacute;', 'ú'), '&nbsp;', ' '), 

			'<p>', ''), '&Uacute;', 'Ú'), 

			'<a href="', '[url='), '</a>', '[/url]'), 

			'<a>', '[url]'), '&oacute;', 'ó'), 

			'&Oacute;', 'Ó'), '&Eacute;', 'É'), 

			'&eacute;', 'é'), '&quot;', '"'), 

			'</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]'), 

			'&eacute;', 'é'), '&ouml;', 'ö'), 

			'&aacute;', 'á'), '&iacute;', 'í'), 

			'&Iacute;', 'Í'), '&Ouml;', 'Ö'), 

			'&uuml;', 'ü'), '&Uuml;', 'Ű'), 

			'<em>', '[i]'), '</em>', '[/i]'),

			'<cite>', '[i]'), '</cite>', '[/i]'),

			'&uacute;', 'ú'), '&nbsp;', ' '), 

			'<p>', ''), '&Uacute;', 'Ú'), 

			'<a href="', '[url='), '</a>', '[/url]'), 

			'<a>', '[url]'), '&oacute;', 'ó'), 

			'&Oacute;', 'Ó'), '&Eacute;', 'É'), 

			'&eacute;', 'é'), '&quot;', '"'), 

			'</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]'), 

			'&eacute;', 'é'), '&ouml;', 'ö'), 

			'&aacute;', 'á'), '&iacute;', 'í'), 

			'&Iacute;', 'Í'), '&Ouml;', 'Ö'), 

			'&uuml;', 'ü'), '&Uuml;', 'Ű'), 

			'<em>', '[i]'), '</em>', '[/i]'),

			'<cite>', '[i]'), '</cite>', '[/i]'),

			'&uacute;', 'ú'), '&nbsp;', ' '), 

			'<p>', ''), '&Uacute;', 'Ú'), 

			'<a href="', '[url='), '</a>', '[/url]'), 

			'<a>', '[url]'), '&oacute;', 'ó'), 

			'&Oacute;', 'Ó'), '&Eacute;', 'É'), 

			'&eacute;', 'é'), '&quot;', '"'), 

			'</p>', 'n'), '<br />', 'n'), '">', ']'),

			'<code>', '[code]'), '</code>', '[/code]'),

			'<hr />', '[hr]'), '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]'), 

			'&eacute;', 'é'), '&ouml;', 'ö'), 

			'&aacute;', 'á'), '&iacute;', 'í'), 

			'&Iacute;', 'Í'), '&Ouml;', 'Ö'), 

			'&uuml;', 'ü'), '&Uuml;', 'Ű'), 

			'<em>', '[i]'), '</em>', '[/i]'),

			'<cite>', '[i]'), '</cite>', '[/i]'),

			'&uacute;', 'ú'), '&nbsp;', ' '), 

			'<p>', ''), '&Uacute;', 'Ú'), 

			'<a href="/', '[url='), '</a>', '[/url]'), 

			'<a>', '[url]'), '&oacute;', 'ó'), 

			'&Oacute;', 'Ó'), '&Eacute;', 'É'), 

			'&eacute;', 'é'), '&quot;', '"'), 

			'</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

Profile PM Website

alephlg

Quote

Posts: 28

Thu 08 Jan, 2009 23:59

Subject: Re: HOWTO Migrate Drupal 6.4 To Icy Phoenix 1.2.0.27c

Convert Drupal's SEO links to Icy Phoenix's links:

Code: [Download] [Hide] [Select]

SELECT u1.dst, 

CASE substring_index(u1.src, '/', -1) WHEN 'feed' THEN

	CASE substring_index(u2.src, '/', 1) 

		WHEN 'node' THEN 'rss.php'

		WHEN 'user' THEN 'rss.php'

		WHEN 'blog' THEN 'rss.php'

		WHEN 'forum' THEN concat('rss.php?f=', substring_index(u2.src, '/', -1))

		WHEN 'taxonomy' THEN 'rss.php'

		WHEN 'tagadelic' THEN 'rss.php'

		WHEN 'contact' THEN 'rss.php'

		ELSE 'rss.php' END

WHEN 'track' THEN concat('search.php?search_author=',

					substring_index(u2.dst, '/', 1))

ELSE

	CASE substring_index(u2.src, '/', 1) 

		WHEN 'node' THEN concat(u1.dst, '-vt', 

			substring_index(u2.src, '/', -1), '.html')

		WHEN 'user' THEN concat('profile.php?mode=viewprofile&u=',

					substring_index(u2.src, '/', -1))

		WHEN 'blog' THEN concat(u1.dst, '-vt', 

					substring_index(u2.src, '/', -1), '.html')

		WHEN 'forum' THEN concat(u1.dst, '-vf', 

			substring_index(u2.src, '/', -1), '.html')

		WHEN 'taxonomy' THEN 'index.php'

		WHEN 'tagadelic' THEN 'kb.php'

		WHEN 'contact' THEN 'contact_us.php'

		ELSE 'index.php'

	END 

END AS url

 FROM www_mandrivalinux_hu.url_alias u1 

    left join www_mandrivalinux_hu.url_alias u2 

           ON u1.src = concat(u2.src, '/feed') 

               OR u1.src = u2.src where  substring_index(u1.src, '/', -1) != 'feed';

Export the resolution to csv file and edit like stream:

Code: [Download] [Hide] [Select]

cat resolution.csv | sed 's/^"/RewriteRule ^/g' | sed 's/","/$ /g' | sed 's/"//g' > old-drupal-link.include

...and include this file to vhost file or insert the .htaccess file after RewriteEngine On line.

Profile PM Website

Reply to topic

Page 1 of 1

You cannot post new topics
You cannot reply to topics
You cannot edit your posts
You cannot delete your posts
You cannot vote in polls
You cannot attach files
You can download files
You cannot post calendar events

This is a "Lo-Fi" version of our main content. To view the full version with more information, formatting and images, please click here.

Powered by Icy Phoenix based on phpBB
Generation Time: 0.1511s (PHP: 12% SQL: 88%)
SQL queries: 11 - Debug Off - GZIP Enabled

Icy Phoenix

Icy Phoenix - Powering Communities

HOWTO Migrate Drupal 6.4 To Icy Phoenix 1.2.0.27c