1 Topic by Jones (edited by Jones 2010-04-11 09:36)

  • Registered: 2010-04-09
  • Posts: 17

Topic: bbPress to PunBB

Good Day,

I want to move my forum from bbPress to PunBB. I would like to see a converter or someone has maybe a sql script to convert the bbPRess Database to a PunBB Database.
The bbPress Forum ist about 1000 user and over 6000 posts. Would like to see a solution for please!

Please help wink

2 Reply by Jones (edited by Jones 2010-04-13 14:58)

  • Registered: 2010-04-09
  • Posts: 17

Re: bbPress to PunBB

I am really sad, that nobody answered since this with his options or opinions...
Well after an hour of work today I finished my small script to convert a bbPress Database to a PunBB Database.
This scripts based on a Converter from PunBB to bbPress which I found in the internet.

Of course I will post it here for you. But I need to tell you, these is maybe not a elegant solution and it it's still not ready because the switching from HTML to PLAIN is complicated. Please read the instruction at the top of the Script first!! Very important!

EDIT: Removed
EDIT - 13.04: Fixed some HTML to PLAIN thingies
EDIT - 13.04 - Second: Fixed correct UTF-8 transport to MySQL
EDIT - 13.04 - Third: Added very important thing to do before starting the script, see at the head of the script

<?php
set_time_limit(0);
/*
 * Import bbPress forum into PunBB forum
 *
 * ORIGINALLY DEVELOPED BY: Bruno Torres <http://brunotorres.net>
 * MODIFIED FOR OTHER WAY SOLUTION BY: J. Hapke <http://www.jh-scripting.de>
 *
 * USE WITH FRESH PUNBB INSTALL. BACKUP YOUR DATA.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2 of the License, or (at
 * your option) any later version.  This program is distributed in the
 * hope that it will be useful, but WITHOUT ANY WARRANTY; without even
 * the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
 * PURPOSE.  See the GNU General Public License at
 * http://www.gnu.org/copyleft/gpl.html for more details.
*
*
* We don't take any charge for destroyed database, destroyed PunBB and / or BBPress softwares.
* You Do this on your OWN Risk.
*
* Additions:
* You need to DOWNLOAD the following page from http://www.chuggnutt.com/html2text.php
* All Rights for this Programm goes to them!
*
* Put this file into the Folder where the Script will run.
*
* BACKUP YOUR DATABASE
*
* IMPORTANT: You need to DELETE all marked as DELETED posts in your bbPress database, otherwise you will all have them back.
* You can do this like it has been shown in this guide
* http://www.g-loaded.eu/2010/04/07/permanently-delete-posts-topics-bbpress/
* THIS IS VERY IMPORTANT TO DO BEFORE!
*
* MODIFY the settings of this script, read below.
* It's IMPORTANT that your wordpress database and the bbPress database are the same database!
*
* Run the script and be Happy!
 */

/* The database connection for existing punbb */
$punbb_db['host'] = 'localhost';
$punbb_db['username'] = 'username';
$punbb_db['password'] = 'password';
$punbb_db['database'] = 'databasename';
$punbb_db['tableprefix'] = 'pun_';

/* The database connection for new bbpress */
$bbpress_db['host'] = 'localhost';
$bbpress_db['username'] = 'username';
$bbpress_db['password'] = 'password';
$bbpress_db['database'] = 'databasename';
$bbpress_db['tableprefix'] = 'bb_';

$save_to_file = TRUE; //Change to FALSE to suppress writing queries to file.
$filename = '/bbPress_imported.sql'; //Ensure the file is writable by PHP.

$do_import = FALSE; //Change to TRUE if you want the script to perform the import. 
//If FALSE, you will need to use the generated SQL file to do the import.


//You don't need to edit the rest of this file

$punbb_tables['forums'] = $punbb_db['tableprefix'] . 'forums';
$punbb_tables['categories'] = $punbb_db['tableprefix'] . 'categories';
$punbb_tables['users'] = $punbb_db['tableprefix'] . 'users';
$punbb_tables['topics'] = $punbb_db['tableprefix'] . 'topics';
$punbb_tables['posts'] = $punbb_db['tableprefix'] . 'posts';

$bbpress_tables['forums'] = $bbpress_db['tableprefix'] . 'forums';
$bbpress_tables['users'] = 'wp_users';
$bbpress_tables['usermeta'] = 'wp_usermeta';
$bbpress_tables['topics'] = $bbpress_db['tableprefix'] . 'topics';
$bbpress_tables['posts'] = $bbpress_db['tableprefix'] . 'posts';

?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>Import punbb to bbPress</title>
<style>
  body{ font:normal 1em verdana, arial, "bitstream vera sans", helvetica, sans-serif; }
</style>
<div id="content">
<?php

//Connecting to the old forum database
@mysql_connect($bbpress_db['host'], $bbpress_db['username'], $bbpress_db['password']) or die("Connect to bbPress database failed.<br>\nMySQL output:<br>\n<pre>" . mysql_error() . "</pre>");

echo "Connected to punbb database host.<br>\n"; flush();

@mysql_select_db($bbpress_db['database']) or die("Cannot select bbPress database. MySQL output:<br>\n<pre>" . mysql_error() . "</pre>");

echo "Selected bbPress database.<br><br>\n"; flush();


//Next, let's import the forums themselves

$export_sql = "SELECT forum_id, forum_name, forum_desc, forum_order, topics, posts FROM " . $bbpress_tables['forums'];
$export_result = mysql_query($export_sql) or die("Can't retrieve data from the bbpress forums table. The query used was:<br>\n<pre>" . $export_sql . "</pre><br>\n and the server threw the following error:<br>\n<pre>" . mysql_error() . "</pre>");

$import_sql .= "#Exporting Forums data: \n";
while($export_row = mysql_fetch_object($export_result)){
  $import_sql .= "INSERT INTO " . $punbb_tables['forums'] . " (id, forum_name, forum_desc, disp_position, num_topics, num_posts, cat_id, sort_by) VALUES (
  " . $export_row->forum_id . ",
  '" . addslashes($export_row->forum_name) . "',
  '" . addslashes($export_row->forum_desc) . "',
  " . $export_row->forum_order . ",
  " . $export_row->topics . ",
  " . $export_row->posts . ",
  1,
  0
  );\n";
}
echo "Forums exported - OK.<br>\n"; flush();

//Now, to the users

$export_sql = "SELECT * FROM " . $bbpress_tables['users'];
$export_result = mysql_query($export_sql) or die("Can't retrieve data from the punbb users table. The query used was:<br>\n<pre>" . $export_sql . "</pre><br>\n MySQL output:<br>\n<pre>" . mysql_error() . "</pre>");

$import_sql .= "#Exporting Users data: \n";
while($export_row = mysql_fetch_object($export_result)){
  $user_id = ($export_row->ID +1);
  $posts_count_first = mysql_query("SELECT user_id, meta_key, meta_value FROM `wp_usermeta` WHERE user_id = " .$export_row->ID . " AND meta_key = \"bb_topics_replied\"");
  $posts_count = mysql_fetch_object($posts_count_first);
  $regdate = strtotime($export_row->user_registered);
  $import_sql .= "INSERT INTO " . $punbb_tables['users'] . " (id, username, password, email, url, registered, num_posts) VALUES (
  " . $user_id . ",
  '" . $export_row->user_login . "',
  '" . $export_row->user_pass . "',
  '" . $export_row->user_email . "',
  '" . $export_row->user_url . "',
  '" . $regdate . "',
  '" . $posts_count->meta_value . "'
   );\n";
}
$import_sql .= "INSERT INTO " . $punbb_tables['users'] . " (id, username, password, registered) VALUES (
  1,
  'Guest',
  'Guest',
  '" . time() . "'
   );\n";
echo "Users exported - OK.<br>\n"; flush();


//Importing topics

$export_sql = "SELECT * FROM " . $bbpress_tables['topics'];
$export_result = mysql_query($export_sql) or die("Can't retrieve data from the bbpress topics table. The query used was:<br>\n<pre>" . $export_sql . "</pre><br>\n MySQL output:<br>\n<pre>" . mysql_error() . "</pre>");

$import_sql .= "#Exporting Topics: \n";
while($export_row = mysql_fetch_object($export_result)){
  $poster = $export_row->topic_poster_name;
  $last_poster = $export_row->topic_last_poster_name;
  $postdate = strtotime($export_row->topic_start_time);
  $lastpost = strtotime($export_row->topic_time);
  $import_sql .= "INSERT INTO " . $punbb_tables['topics'] . " (id, subject, poster, last_poster, posted, forum_id, closed, last_post_id, sticky, num_replies, num_views, last_post) VALUES (
  " . $export_row->topic_id . ",
  '" . addslashes($export_row->topic_title) . "',
  '" . $poster . "',
  '" . $last_poster . "',
  '" . $postdate . "',
  " . $export_row->forum_id . ",
  0,
  " . $export_row->topic_last_post_id . ",
  " . $export_row->topic_sticky . ",
  " . ($export_row->topic_posts - 1) . ",
  0,
  '" . $lastpost . "'
  );\n";
}

echo "Topics exported - OK.<br>\n"; flush();


//Importing posts

$export_sql = "SELECT * FROM " . $bbpress_tables['posts'];    
             $export_result = mysql_query($export_sql) or die("Can't retrieve data from bbpress poste table. The query used was:<br>\n<pre>" . $export_sql . "</pre><br>\n MySQL output:<br>\n<pre>" . mysql_error() . "</pre>");
// Include the class definition file.
require_once('class.html2text.inc');
$import_sql .= "#Exporting Posts: \n";
while($export_row = mysql_fetch_object($export_result)){
  $post_time = strtotime($export_row->post_time);
  $poster_name_first = mysql_query("SELECT ID, user_login FROM `wp_users` WHERE ID = " .$export_row->poster_id);
  $poster_name = mysql_fetch_object($poster_name_first);
  $post_text_first =& new html2text($export_row->post_text);
  $post_text_second = $post_text_first->get_text();
  $post_text = substr($post_text_second, 2, -1);
  $import_sql .= "INSERT INTO " . $punbb_tables['posts'] . " (id, topic_id, poster, poster_id, message, posted, poster_ip, hide_smilies) VALUES (
  " . $export_row->post_id . ",
  " . $export_row->topic_id . ",
  '" . $poster_name->user_login . "',
  " . ($export_row->poster_id +1) . ",
  '" . addslashes($post_text) . "',
  '" . $post_time . "',
  '" . $export_row->poster_ip . "',
  0
  );\n";
}

echo "Posts exported.<br><br>\n"; flush();

$sql_file = $_SERVER['DOCUMENT_ROOT'].$filename;

if ($save_to_file){
  if (file_put_contents($sql_file, utf8_encode($import_sql))){
    echo "The SQL queries were successfully written to the file: ".$sql_file."<br><br>\n"; flush();
  } else {
    echo "Could not write file. Check file/directory permissions."; flush();
  }
}

if ($do_import){
    @mysql_connect($punbb_db['host'], $punbb_db['username'], $punbb_db['password']) or die("Can't connect to bbpress database.<br>\nMySQL output:<br>\n<pre>" . mysql_error() . "</pre>");

  @mysql_query("SET NAMES 'utf8'");
  echo "UTF-8 selected<br>\n"; flush();

    @mysql_select_db($punbb_db['database']) or die("Can't select the bbpress database. MySQL output:<br>\n<pre>" . mysql_error() . "</pre>");

  /* Cleanup; admin user is given high ID to avoid the ID already issued to punbb users */
  @mysql_query("TRUNCATE TABLE " . $punbb_tables['forums']);
  @mysql_query("TRUNCATE TABLE " . $punbb_tables['topics']);
  @mysql_query("TRUNCATE TABLE " . $punbb_tables['posts']);
  @mysql_query("TRUNCATE TABLE " . $punbb_tables['users']);

  /* Break SQL into an array of single INSERTs and apply each */
  $import_sql = utf8_encode($import_sql);
  $import_sql_array = explode(";\n", $import_sql);

  echo "Starting the import...<br>\n"; flush();
  $ok = 0;
  $error = 0;
  foreach ($import_sql_array as $s){
    if (!@mysql_query($s)){
      echo "There was an error trying to execute the query:<br>\n<pre>$s</pre><br>\nMySQLoutput:<br>\n<pre>" . mysql_error() . "</pre>\n"; flush();
      $error++;
    } else {
      $ok++;
    }
  }
  echo "Import complete.<br>\nStatistics:<br>\n$ok successful, $error errors"; flush();
}

?>
</div>

Be careful and good day!

  • From: Russia
  • Registered: 2008-07-22
  • Posts: 713

Re: bbPress to PunBB

Jones wrote:

this only works with the Integration of PunBB with Wordpress, because of the passwords.

Is it because of md5 hashes? Actually, earlier PunBB versions also had md5 hashes. And PunBB 1.3 is able to convert md5 hashes to sha1 hashes on the fly, transparently for users. Take a look at this piece of code.

PunBB team intentions, personal tasks.

Parpalak's Website

4 Reply by Jones (edited by Jones 2010-04-13 14:59)

  • Registered: 2010-04-09
  • Posts: 17

Re: bbPress to PunBB

I see, then it's of course possible to use it without the wordpress bridge, thanks for this add.

Added some fixes, for my propose it transport html to plain very well now, just 6 errors with 6000 posts.

Forgot to add, that you need to delete all bbPress posts that are marked as delete before otherwise they will pop up again.
-> http://www.g-loaded.eu/2010/04/07/perma … s-bbpress/

Ah before I forget, can you tell me the function of the the mysql field pun_topics->first_post_id ?

5 Reply by Jones

  • Registered: 2010-04-09
  • Posts: 17

Re: bbPress to PunBB

The Script Dialog can be found at
http://www.jh-scripting.de/convert-bbpress-to-punbb/
now

  • From: Russia
  • Registered: 2008-07-22
  • Posts: 713

Re: bbPress to PunBB

Jones wrote:

The Script Dialog can be found at
http://www.jh-scripting.de/convert-bbpress-to-punbb/
now

Thanks. I've added the link to the Wiki migration page. Feel free to add a description or upload the file there.

Jones wrote:

can you tell me the function of the mysql field pun_topics->first_post_id ?

I didn't get what exactly you want. Do you solve this issue?

PunBB team intentions, personal tasks.

Parpalak's Website

7 Reply by Jones

  • Registered: 2010-04-09
  • Posts: 17

Re: bbPress to PunBB

Unfortunaly not,
look here in the pun_topics database
http://punbb.informer.com/wiki/punbb13/ … nce#topics

There is a field called first_post_id, description is the first post that was made for this topic, does this thingy has any function in getting the topic? I mean I didn't entered anything while migrate it from bbPress to PunBB and the topics are looking quite well. SO I don't see any function of this field right now.

  • From: Russia
  • Registered: 2008-07-22
  • Posts: 713

Re: bbPress to PunBB

Ok, I've got it. Actually first_post_id is used in some places, for example, in edit.php. You can find the other entries using search in files.

Take a look at the db_update.php, line 1147. You can find there how to set first_post_id.

PunBB team intentions, personal tasks.

Parpalak's Website