2011-09-19, 03:32 AM
I'm not sure how common of a problem this is, but I migrated a forum a few weeks ago from phpBB3 to MyBB, and I noticed that none of the URLs were properly migrated. I attempted the migration again only to find the same problem. This was a vanilla installation of the latest version of phpBB3, so I'm not sure why the Merge system didn't convert the links, but at any rate, I wrote this function (well, it's two functions) to fix the issue.
It's far from being the most efficient thing in the world, but it only needs to be run once, so optimization wasn't a big deal to me.
Replaces the following:
The Function(s):
If you take the time to look through the code, you'll see how relatively easy it is to adopt it to suit your needs (if you need to convert some other special HTML tags or whatever).
Anyway...
Example usage:
Optionally run the following to drop the functions after you're done with them:
Notice that I didn't put any limits. I had no need for them. I ran it all locally and then migrated to the production site. The forum that I ran it on only had 5000 posts and 500 topics. So YMMV. I suggest doing a backup first.
On a related note, I also ran the following to fix unordered lists:
Hmm, I just saw the "solved"/"not solved" stuff.
Did I post this in the wrong section?
It's far from being the most efficient thing in the world, but it only needs to be run once, so optimization wasn't a big deal to me.
Replaces the following:
<!-- m --><a class="postlink" href="http://www.google.com">Google!</a><!-- m -->[url=http://www.google.com]Google![/url]The Function(s):
DELIMITER ||
DROP FUNCTION IF EXISTS GET_DATA||
CREATE FUNCTION GET_DATA( _data LONGTEXT, _begin LONGTEXT, _end LONGTEXT) RETURNS LONGTEXT
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE _startPos INT UNSIGNED;
DECLARE _endPos INT UNSIGNED;
SET _startPos = LOCATE(_begin, _data, 1);
IF _startPos < 1 THEN RETURN NULL; END IF;
SET _startPos = _startPos + LENGTH(_begin);
SET _endPos = LOCATE(_end, _data, _startPos);
RETURN SUBSTRING(_data,_startPos,_endPos - _startPos);
END;
||
DELIMITER ;
# SELECT GET_DATA( `message` , '<!-- m --><a class="postlink" href="', '">' )
# FROM `mybb_posts`
# WHERE GET_DATA( `message` , '<!-- m --><a class="postlink" href="', '">' ) IS NOT NULL
# LIMIT 0,5077;
DELIMITER ||
DROP FUNCTION IF EXISTS PHPBB3_TO_MYBB_URLS||
CREATE FUNCTION PHPBB3_TO_MYBB_URLS( x longtext) RETURNS longtext
LANGUAGE SQL NOT DETERMINISTIC READS SQL DATA
BEGIN
DECLARE _linkURL VARCHAR(500);
DECLARE _linkText VARCHAR(500);
DECLARE _begin VARCHAR(500);
DECLARE _middle VARCHAR(500);
DECLARE _end VARCHAR(500);
DECLARE _startPos INT UNSIGNED;
DECLARE _endPos INT UNSIGNED;
SET _begin = '<!-- m --><a class="postlink" href="';
SET _middle = '">';
SET _end = '</a><!-- m -->';
LOOP
SET _linkURL = GET_DATA(x,_begin,_middle);
IF (_linkURL IS NULL) THEN RETURN x; END IF;
SET _linkText = GET_DATA(x,CONCAT(_linkURL,_middle),_end);
SET x = REPLACE(x,CONCAT(_begin,_linkURL,_middle,_linkText,_end),CONCAT('[url=',_linkURL,']',_linkText,'[/url]'));
SET _linkURL = GET_DATA(x,_begin,_middle);
END LOOP;
RETURN x;
END;
||
DELIMITER ;
# UPDATE `mybb_posts` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);If you take the time to look through the code, you'll see how relatively easy it is to adopt it to suit your needs (if you need to convert some other special HTML tags or whatever).
Anyway...
Example usage:
UPDATE `mybb_posts` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);
UPDATE `mybb_privatemessages` SET `message` = PHPBB3_TO_MYBB_URLS(`message`);
UPDATE `mybb_users` SET `signature` = PHPBB3_TO_MYBB_URLS(`signature`);Optionally run the following to drop the functions after you're done with them:
DROP FUNCTION PHPBB3_TO_MYBB_URLS;
DROP FUNCTION GET_DATA;Notice that I didn't put any limits. I had no need for them. I ran it all locally and then migrated to the production site. The forum that I ran it on only had 5000 posts and 500 topics. So YMMV. I suggest doing a backup first.
On a related note, I also ran the following to fix unordered lists:
UPDATE `mybb_posts` SET `message` = REPLACE(`message`,'[/list:u]','[/list]');
UPDATE `mybb_privatemessages` SET `message` = REPLACE(`message`,'[/list:u]','[/list]');
UPDATE `mybb_users` SET `signature` = REPLACE(`signature`,'[/list:u]','[/list]');Hmm, I just saw the "solved"/"not solved" stuff.
Did I post this in the wrong section?