Skip to:
Content
Pages
Categories
Search
Top
Bottom

Foreign Key Issues after Importing / Converting custom database


  • adressler
    Participant

    @adressler

    Hi everyone, this is my first time posting, and I really dig BBPress so far.

    I’m running WP v3.8.1 with BBPress 2.5.3. I’ve encountered a problem when converting a custom MYSQL database into BBPress. I have successfully exported a Lotus Notes / Domino DB (yes, it still exists…) into a simple MySQL DB containing just 3 tables.

    After a “successful” conversion (of over 35K forums, topics and replies) into BBPress, I can see all of the Forums, Topics, and Replies listed in the BBPress Admin panel. However, there seems to be issues with the Foreign Keys being imported/linked, even though I have double and triple checked the forum_id, topic_id values for the Topics->Forums and Replies->Topics relationships within the Example.php converter included with BBPress. All data has been properly formatted into WordPress friendly values (dates, slugs, etc..) before importing into MySQL.

    In the admin panel, under Forums, it lists all forums in their hierarchical order, as expected. It also lists the topic and reply counts, date, and creator next to each parent / child forum. However, under “Freshness” is says “No Topics”. The Topics Admin page is even more intriguing; it lists all of the Topic Titles, the Forum title which that topic is assigned to (!), reply count, author, creation date, and freshness. Browsing to the Replies Admin page lists all reply titles, author, and creation date, however under Forum it says “No Forum” and under Topic it says “No Topic”.

    When you browse to the forums (mysite.local/forums/forum/the-forum), the forum title and child-forums are displayed, along with topic and reply counts for each child. However, under “Freshness”, it says “No Topics”. When I browse to a child forum, it lists the topic and reply counts, however it says “No Topics were found”.

    When I browse to a topic (mysite.local/forums/topic/the-topic), I see the date, title, content, and reply count. However, there are no replies listed.

    When I browse to a reply (by clicking “View” under the reply listed in the Admin Replies page), it directs me to http://mysite.dev/wp-admin/edit.php?post_type=reply/#post-######, saying “Invalid Post Type”.

    I figured this would be rather straightforward, since I am not importing Users or Tags. Eventually I would like all authors to be “Archived Author”, should I try to tackle this now or after a fully successful import?

    I can supply screen shots, converter output (which really says nothing unexpected), or the full source code for the converter if needed. I’ve included my table structure and simplified converter code for reference.

    I hope that someone with a bit more experience converting forums for BBPress will be able to shed some light on this, my forehead is really starting to hurt from banging my head against the wall. It seems there is very limited information out there on this converter. Any and all help is greatly appreciated!

    My tables to import/convert are laid out as follows:

    forums table {
      forum_id  PRIMARY KEY INT(11)
      title
      slug
      parent_id (Self referencing FK)
      last_mod (DATETIME)
      num_topics
      num_replies
    }
    topics table {
      topic_id PRIMARY KEY INT(11)
      parent_id FK (set to 0 for no parent)
      forum_id FK (matches corresponding <code>forums</code>.forum_id)
      subject (title)
      slug (slug of title)
      body (the post content)
      from
      date
      date_gmt
      num_replies
    }
    replies table {
      reply_id PRIMARY KEY INT(11)
      topic_id FK INT(11) (matches corresponding <code>topics</code>.topic_id)
      forum_id FK INT (11) (matches corresponding <code>forums</code>.forum_id)
      date
      date_gmt
      subject
      body
    }

    And now, here’s the converter I’m using (starting at the Forum section Line#26 of /plugins/bbpress/includes/admin/converters/Example.php). I’ve removed commented lines for brevity.

    
    /** Forum Section *****************************************************/
    
    		// Setup table joins for the forum section at the base of this section
    
    		 // Forum id (Stored in postmeta)
    		$this->field_map[] = array(
    			 'from_tablename'  => 'forums',
    			 'from_fieldname'  => 'forum_id',
    			 'to_type'         => 'forum',
    			 'to_fieldname'    => '_bbp_forum_id'
    		);
    
    		// Forum parent id (If no parent, then 0. Stored in postmeta)
     		$this->field_map[] = array(
    			'from_tablename'  => 'forums',
    			'from_fieldname'  => 'parent_id',
    			'to_type'         => 'forum',
    			'to_fieldname'    => '_bbp_forum_parent_id'
    		 );
    
     		// Forum topic count (Stored in postmeta)
     		$this->field_map[] = array(
    			 'from_tablename' => 'forums',
    			'from_fieldname' => 'num_topics',
    			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_topic_count'
    		 );
    
    		 // Forum reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename' => 'forums',
     			'from_fieldname' => 'num_replies',
     			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_reply_count'
    		 );
    
     		// Forum total topic count (Stored in postmeta)
    		$this->field_map[] = array(
    			' from_tablename' => 'forums',
    			'from_fieldname' => 'num_topics',
    			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_total_topic_count'
    		 );
    
    		 // Forum total reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			 'from_tablename' => 'forums',
    			 'from_fieldname' => 'num_replies',
    			 'to_type'        => 'forum',
    			 'to_fieldname'   => '_bbp_total_reply_count'
    		 );
    
    		 // Forum title.
    		$this->field_map[] = array(
    			'from_tablename'  => 'forums',
    			'from_fieldname'  => 'title',
    			'to_type'         => 'forum',
    			'to_fieldname'    => 'post_title'
    		 );
    
    		 // Forum slug (Clean name to avoid confilcts)
    		$this->field_map[] = array(
    			'from_tablename'  => 'forums',
    			'from_fieldname'  => 'slug',
    			'to_type'         => 'forum',
     			'to_fieldname'    => 'post_name'
     		);
    		
    		 // Forum dates.
    		 $this->field_map[] = array(
    			 'to_type'         => 'forum',
    			'to_fieldname'    => 'post_date',
    			 'default' => date('Y-m-d H:i:s')
     		);
    		 $this->field_map[] = array(
    			 'to_type'         => 'forum',
    			 'to_fieldname'    => 'post_date_gmt',
    			 'default' => date('Y-m-d H:i:s')
    		 );
    		 $this->field_map[] = array(
    			 'to_type'         => 'forum',
    			 'to_fieldname'    => 'post_modified',
    			 'default' => date('Y-m-d H:i:s')
     		);
    		 $this->field_map[] = array(
    			'to_type'         => 'forum',
    			'to_fieldname'    => 'post_modified_gmt',
    			'default' => date('Y-m-d H:i:s')
    		);
    
    		
    /** Topic Section *****************************************************/
    
    		// Setup table joins for the topic section at the base of this section
    
    		// Topic id (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'topic_id',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_topic_id'
    		);
    
    		// Topic reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'num_replies',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_reply_count'
    		);
    
    		// Topic total reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'num_replies',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_total_reply_count'
    		);
    
    		// Topic parent forum id (If no parent, then 0. Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'forum_id',
    			 'to_type'         => 'topic',
    			 'to_fieldname'    => '_bbp_forum_id'
    		);
    
     		// Topic content.
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'body',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_content'
    		);
    
    		// Topic title.
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
     			'from_fieldname'  => 'subject',
     			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_title'
    		);
    
    		// Topic slug (Clean name to avoid conflicts)
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'slug',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_name'
    		);
    		// Topic dates.
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_date'
    		);
    
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'date_gmt',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_date_gmt'
    		);
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_modified'
    		);
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'date_gmt',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_modified_gmt'
    		);
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_last_active_time'
    		);

    And skipping to the Replies section (Tags and Users are not converted)

    	/** Reply Section *****************************************************/
    
    		// Setup table joins for the reply section at the base of this section
    
    		// Reply id (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'reply_id',
    			'to_type'         => 'reply',
    			'to_fieldname'    => '_bbp_post_id'
    		);
    		// Reply parent topic id (If no parent, then 0. Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'topic_id',
    			'to_type'         => 'reply',
    			'to_fieldname'    => '_bbp_topic_id'
    		);
    		// Reply title.
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'subject',
    			'to_type'         => 'reply',
    			'to_fieldname'    => 'post_title'
    		);
    		// Reply content.
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'body',
    			'to_type'         => 'reply',
    			'to_fieldname'    => 'post_content'
    		);
    		// Reply dates.
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'reply',
    			'to_fieldname'    => 'post_date'
    		);
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'reply',
    			'to_fieldname'    => 'post_date_gmt'
    		);
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'reply',
    			'to_fieldname'    => 'post_modified'
    		);
    		$this->field_map[] = array(
    			'from_tablename'  => 'replies',
    			'from_fieldname'  => 'date',
    			'to_type'         => 'reply',
    			'to_fieldname'    => 'post_modified_gmt'
    		);
Viewing 7 replies - 1 through 7 (of 7 total)

  • Robin W
    Moderator

    @robin-w

    ok, before looking at anything else, try running all the forum repairs

    Dashboard>tools>forums>repair and run one at a time


    adressler
    Participant

    @adressler

    Thanks for the reply Robin,

    -”Recalculate the parent topic for each post” did nothing
    -”Recalculate the parent forum for each post” set all of the Topics in Admin panel to “(No Forum)”, where before that was the only text that was actually displaying properly.
    -”Count topics in each forum” reset forum topic counts to 0
    -”Count replies in each forum” reset forum reply counts to 0
    -”Count replies in each topic” reset topic reply counts to 0
    and the rest did nothing.

    It just seems strange that before I tried the tools it was properly mapping topics.forum_id to forums.forum_id to retrieve the forum title that was displayed in the Admin Topics page, and only on the Admin Topics page. When I view the public Topic page, the forum is excluded in the breadcrumb.

    @adressler Nice work and you are sooooo close :)

    As Robin has said run the repair tools and that should fix pretty much everything from what I can see.

    If it doesn’t I’d take a close look at the before and after import relationships between the replies parent topic ID topics.topic_id and replies.topic_id

    Looking at the replies section of your converter scrip, you have this:

    
      // Reply parent topic id (If no parent, then 0. Stored in postmeta)
      $this->field_map[] = array(
      'from_tablename'  => 'replies',
      'from_fieldname'  => 'topic_id',
      'to_type'         => 'reply',
      'to_fieldname'    => '_bbp_topic_id'
    

    I’d suggest adding the following to help the initial import know which forum each reply should be associated with:

    
    // Reply parent forum id (If no parent, then 0. Stored in postmeta)
      $this->field_map[] = array(
      'from_tablename'  => 'replies',
      'from_fieldname'  => 'forum_id',
      'to_type'         => 'reply',
      'to_fieldname'    => '_bbp_forum_id',
      'callback_method' => 'callback_topicid_to_forumid'
    );
    

    Adding this is what you need to fix things as currently you are assigning a topic ID to each reply but this is needed in two places, the _bbp_topic_id and post_parent which the later you are missing. In this case the replies ‘post parent’ is also the topic ID.

    
      // Reply parent topic id (If no parent, then 0)
      $this->field_map[] = array(
      'from_tablename'  => 'replies',
      'from_fieldname'  => 'topic_id',
      'to_type'         => 'reply',
      'to_fieldname'    => 'post_parent',
      'callback_method' => 'callback_topicid'
    );
    

    Also for good measure you should probably do the same for your topic section and add the forum ID as post_parent for each topic:

    
      // Topic parent forum id (If no parent, then 0)
      $this->field_map[] = array(
      'from_tablename'  => 'topics',
      'from_fieldname'  => 'forum_id',
      'to_type'         => 'topic',
      'to_fieldname'    => 'post_parent',
      'callback_method' => 'callback_forumid'
    );
    

    -”Recalculate the parent topic for each post” did nothing

    The 3rd bit of code ‘Reply parent topic id (If no parent, then 0)’ will fix that.

    -”Recalculate the parent forum for each post” set all of the Topics in Admin panel to “(No Forum)”, where before that was the only text that was actually displaying properly.

    The last piece of code I posted above ‘Topic parent forum id’ will fix that.

    It just seems strange that before I tried the tools it was properly mapping topics.forum_id to forums.forum_id to retrieve the forum title that was displayed in the Admin Topics page, and only on the Admin Topics page.

    bbPress stores all sorts of extra ‘meta data’ in WordPress’ wp_postmeta table relating to each of bbPress’ custom post types. Essentially without the additions of the above bbPress only knows a partial set of the data it needs and as such when the repair tools are run it tries to repair all this data but if some key aspects of the data is missing, in this case post_parent it will end up with the results you are now seeing in the dashboard where each topic is not associated with a forum and each reply is not associated with a topic.

    Clear as mud, hey? ;) I’ve been here many times and am right now writing a BuddyPress importer update and am currently here again ;)

    When I view the public Topic page, the forum is excluded in the breadcrumb.


    adressler
    Participant

    @adressler

    Thank you @netweb. I tried your code you provided, unfortunately the conversion results were the same as I described originally. Same results running the tools after the import/conversion as well.

    I am going to run some test queries on the databases just to try to figure out where things are breaking down, probably tomorrow. :) Do you know of a way to echo the SELECT queries to the old Database tables forums, replies, and topics during import?

    Thanks again.

    I tried your code you provided, unfortunately the conversion results were the same as I described originally. Same results running the tools after the import/conversion as well.

    There is a chance I got the code wrong, I based it on the snippets you posted above.

    I think it will be a matter of just working out the correct fields to grab the correct data from in and around the topics table with topic_id, parent_id and forum_id and similarly for the replies table so that the correct value is being added to post_parent.

    If you want to dump all the code so I can take a closer look dump it into a gist here on GitHub.

    https://gist.github.com/

    I am going to run some test queries on the databases just to try to figure out where things are breaking down, probably tomorrow. :) Do you know of a way to echo the SELECT queries to the old Database tables forums, replies, and topics during import?

    Us e Google Chrome/Firefox’s Inspector, simply right click in the yellow window and select ‘Inspect Element’, we have all the MySQL queries that are used during import echo’d out in the page source, just not rendered on the page.


    adressler
    Participant

    @adressler

    Thank you Stephen! Just noticed a typo on my part, fixed it and mostly everything imported perfectly. Just finding a few data anomalies (thanks to Lotus Notes…) but I should be able to fix them within BBPress. Thanks again for your support on these forums, I appreciate the good work you’ve put in.

    Aaron

Viewing 7 replies - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.