Skip to:
Content
Pages
Categories
Search
Top
Bottom

[TUTORIAL] Convert phpBB links after import

  • @schoelje

    Participant

    [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

Viewing 2 replies - 1 through 2 (of 2 total)
  • @schoelje

    Participant

    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

    Moderator

    Thanks for sharing your guide that I just now approved.

Viewing 2 replies - 1 through 2 (of 2 total)
  • You must be logged in to reply to this topic.
Skip to toolbar