[Schoelje]

[Warning]: DO NOT DO THIS ON A PRODUCTION DATABASE!
My apologies for the caps, but this is really important 😉

[Assumptions]: Debian/Ubuntu developmachine with phpMyAdmin.

[Note]: Obviously I haven’t tested all Apache/MySql versions. This was done on SolydK (Debian Jessie). It is also not perfect. So, if somebody can improve on the code. Please, let me know.

After you’ve setup a development machine, imported the phpBB data and checked that everything is working fine, you still need to convert the phpBB internal links like “viewtopic.php?p=####” or “viewtopic.php?t=####” to the slugs that are being used by bbPress.

Let’s start!

1) First you’ll need to install and compile lib_mysqludf_preg.
Create a bash file install_preg.sh with this content:

#!/bin/bash
apt-get update
apt-get install libpcre3-dev libmysqlclient-dev build-essential libmysqld-dev libpcre3-dev
wget https://github.com/mysqludf/lib_mysqludf_preg/archive/testing.zip
unzip testing.zip
cd lib_mysqludf_preg-testing
./configure
make  install
make MYSQL="mysql -p" installdb
service mysql restart

Make it executable:
chmod +x install_preg.sh

And run it:
./install_preg.sh

2) Install the stored procedures.
In phpMyAdmin, select the database and the SQL tab.
Paste the following codes separately, creating three stored procedures on your database:

DELIMITER $$
CREATE PROCEDURE sp_cleanup_replies()
BEGIN
  -- declare cursor for reply url change
  DECLARE reply_cursor CURSOR FOR 
  SELECT DISTINCT old_post_id, REPLACE(<code>xkcom_posts</code>.<code>guid</code>, 'http://yoursite.com', '') AS new_url
  FROM (SELECT CAST(CONVERT(PREG_CAPTURE('/#p([0-9]+)/i', <code>post_content</code>, 1) USING UTF8) AS UNSIGNED) AS old_post_id FROM <code>xkcom_posts</code>) AS t1
  INNER JOIN <code>xkcom_postmeta</code> ON <code>xkcom_postmeta</code>.<code>meta_value</code> = old_post_id
  INNER JOIN <code>xkcom_posts</code> ON <code>xkcom_posts</code>.<code>ID</code> = <code>xkcom_postmeta</code>.<code>post_id</code>
  WHERE <code>xkcom_postmeta</code>.<code>meta_key</code> = '_bbp_old_reply_id'
  AND old_post_id IS NOT NULL
  ORDER BY old_post_id DESC;
  
  SELECT 'sp_cleanup_replies: START';
  
  -- Change posts
  OPEN reply_cursor;
  BEGIN
    DECLARE old_reply_id MEDIUMINT;
    DECLARE new_url VARCHAR(255);
    DECLARE search_string VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    read_loop: LOOP
      FETCH reply_cursor INTO old_reply_id, new_url;
      IF done THEN
	LEAVE read_loop;
      END IF;
      
      SET search_string = CONCAT('|[a-z0-9\.\-:\/&=\?\+]+#p', old_reply_id, '|i');
      SELECT CONCAT('sp_cleanup_replies: replace ', search_string, CONCAT(' with ', new_url));
      
      -- Uncomment the following line if you want to test the regexp
      -- SELECT CONVERT(PREG_REPLACE(search_string, new_url, <code>post_content</code>) USING UTF8) FROM <code>xkcom_posts</code> WHERE INSTR(<code>post_content</code>, CONCAT('#p', old_reply_id)) > 0; LEAVE read_loop;
      
      UPDATE <code>xkcom_posts</code> SET <code>post_content</code>= CONVERT(PREG_REPLACE(search_string, new_url, <code>post_content</code>) USING UTF8) WHERE INSTR(<code>post_content</code>, CONCAT('#p', old_reply_id)) > 0;
    
    END LOOP;
  END;
  CLOSE reply_cursor;
  
  SELECT 'sp_cleanup_replies: DONE';
  
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_cleanup_topics()
BEGIN 
  -- declare cursor for topic url change
  DECLARE topic_cursor CURSOR FOR 
  SELECT DISTINCT old_topic_id, REPLACE(<code>xkcom_posts</code>.<code>guid</code>, 'http://yoursite.com', '') AS new_url
  FROM (SELECT CAST(CONVERT(PREG_CAPTURE('/t=([0-9]+)/i', <code>post_content</code>, 1) USING UTF8) AS UNSIGNED) AS old_topic_id FROM <code>xkcom_posts</code>) AS t1
  INNER JOIN <code>xkcom_postmeta</code> ON <code>xkcom_postmeta</code>.<code>meta_value</code> = old_topic_id
  INNER JOIN <code>xkcom_posts</code> ON <code>xkcom_posts</code>.<code>ID</code> = <code>xkcom_postmeta</code>.<code>post_id</code>
  WHERE <code>xkcom_postmeta</code>.<code>meta_key</code> = '_bbp_old_topic_id'
  AND old_topic_id IS NOT NULL
  ORDER BY old_topic_id DESC;
  
  SELECT 'sp_cleanup_topics: START';
  
  -- Change topics
  OPEN topic_cursor;
  BEGIN
    DECLARE old_topic_id MEDIUMINT;
    DECLARE new_url VARCHAR(255);
    DECLARE search_string VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    read_loop: LOOP
      FETCH topic_cursor INTO old_topic_id, new_url;
      IF done THEN
	LEAVE read_loop;
      END IF;
      
      SET search_string = CONCAT('|[a-z0-9\.\-:\/&=\?\+]+t=', old_topic_id, '|i');
      SELECT CONCAT('sp_cleanup_topics: replace ', search_string, CONCAT(' with ', new_url));
      
      -- Uncomment the following line if you want to test the regexp
      -- SELECT CONVERT(PREG_REPLACE(search_string, new_url, <code>post_content</code>) USING UTF8) FROM <code>xkcom_posts</code> WHERE INSTR(<code>post_content</code>, CONCAT('t=', old_topic_id)) > 0; LEAVE read_loop;
      
      UPDATE <code>xkcom_posts</code> SET <code>post_content</code>= CONVERT(PREG_REPLACE(search_string, new_url, <code>post_content</code>) USING UTF8) WHERE INSTR(<code>post_content</code>, CONCAT('t=', old_topic_id)) > 0;

    END LOOP;
  END;
  CLOSE topic_cursor;
  
  SELECT 'sp_cleanup_topics: DONE';
  
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE sp_cleanup_missing()
BEGIN
  -- declare cursor for reply url change
  DECLARE missing_cursor CURSOR FOR 
  SELECT DISTINCT old_post_id
  FROM (SELECT CAST(CONVERT(PREG_CAPTURE('|/&[a-z0-9=\+]+#p([0-9]+)|i', <code>post_content</code>, 1) USING UTF8) AS UNSIGNED) AS old_post_id FROM <code>xkcom_posts</code>) AS t1
  WHERE old_post_id IS NOT NULL
  ORDER BY old_post_id DESC;
  
  SELECT 'sp_cleanup_missing: START';
  
  -- Change posts
  OPEN missing_cursor;
  BEGIN
    DECLARE missing_reply_id MEDIUMINT;
    DECLARE search_string VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    read_loop: LOOP
      FETCH missing_cursor INTO missing_reply_id;
      IF done THEN
	LEAVE read_loop;
      END IF;
      
      SET search_string = CONCAT('|[a-z0-9&=\+]+#p', missing_reply_id, '|i');
      SELECT CONCAT('sp_cleanup_missing: remove ', search_string);
      
      -- Uncomment the following line if you want to test the regexp
      -- SELECT CONVERT(PREG_REPLACE(search_string, '#', <code>post_content</code>) USING UTF8) FROM <code>xkcom_posts</code> WHERE INSTR(<code>post_content</code>, CONCAT('#p', missing_reply_id)) > 0; LEAVE read_loop;
      
      UPDATE <code>xkcom_posts</code> SET <code>post_content</code>= CONVERT(PREG_REPLACE(search_string, '#', <code>post_content</code>) USING UTF8) WHERE INSTR(<code>post_content</code>, CONCAT('#p', missing_reply_id)) > 0;
    
    END LOOP;
  END;
  CLOSE missing_cursor;
  
  SELECT 'sp_cleanup_missing: DONE';
  
END$$
DELIMITER ;

3) Run the stored procedures from terminal.
It is important that you check some example posts with links that need to change. First the post links like “viewtopic.php?some_parameters#p#####”, then topic links like “viewtopic.php?some_parameters&t=#####” and lastly the links to missing posts are removed.

mysql -u root -p -e 'CALL sp_cleanup_replies()' my_database

mysql -u root -p -e 'CALL sp_cleanup_topics()' my_database

mysql -u root -p -e 'CALL sp_cleanup_missing()' my_database

Now, check your posts and when you’re happy you can drop the stored procedures:

DROP PROCEDURE IF EXISTS sp_cleanup_replies;
DROP PROCEDURE IF EXISTS sp_cleanup_topics;
DROP PROCEDURE IF EXISTS sp_cleanup_missing;

Mod note: edited and changed dev site from contributer to yoursite.com

[Schoelje]

In the sp_cleanup_replies and sp_cleanup_topics stored procedures you’ll find “dev.solydxk.com”.
Obviously, you need to change that to the development domain you’re working on.

It seems I cannot edit my own OPs 🙁

[Robkk]

Thanks for sharing your guide that I just now approved.