Skip to:
Content
Pages
Categories
Search
Top
Bottom

Documented import from snitz access database


  • Robin W
    Moderator

    @robin-w

    Converting a Snitz access database forum to BBpress

    This documents how I transferred from a website using Snitz and an access database to a new site with BBress using Mysql.

    This briefing assumes knowledge of
    • FTP programmes and uploading/downloading files
    • A way to access your wordpress database eg myphpadmin
    • A way to see your access database

    Introduction

    BBpress has a conversion tool that lets you import data from several other forum packages. However two things are needed for this to work.
    1. The data in a MySQL database format
    2. A convertor programme that understands the structure of the source (in this case Snitz) forum.

    If you already have a MySQL verison of Snitz (Snitz supports Access, MS SQL, and MySQL), then you’ll still need to fix the date, and run a specific Snitz converter, but don’t need the upload to MySql in step 2.

    Plan

    The plan therefore is to
    1. Fix date format (it is different between the two forums)
    2. Convert the data to sql format
    3. Run a snitz converter
    4. Run repair to fix the links

    Execution

    I can only describe how I achieved this, I suspect there are lots of ways, but this one worked.

    I had an access database on site x with hosting company a, and needed to transfer to a Mysql database on site y with hosting company b (basically I was taking over running someone else’s website management)

    Step 1 fixing the data format

    Snitz stores dates in the format yyyymmddhhmmss
    BBpress stores dates in the format yyyy-mm-dd hh:mm:ss

    You can fix this
    • before you transfer the data to sql,
    • after you have transferred it
    • or during conversion,

    this will depend on where you knowledge lies. But you will need to fix it. If you don’t fix this, all the entries you import will show as posted 2014 years ago (basically year 0)

    I converted before transferring to sql, as I an old and used to working with flat files! If you are more capable in sql, you could no doubt write a small programme to change the data format after you’ve uploaded to sql, and if more capable in php you could do it within the converter programme (see later)

    The Snitz database has two tables with dates in that need changing. These are :

    FORUM_TOPICS
    FORUM_REPLY

    This is how they showed in my Snitz database, but the prefix may be different with yours.

    In FORUM_TOPICS you need to change
    T_DATE
    T_LAST_POST

    In FORUM_REPLY you need to change
    T_REPLY

    I downloaded the access database from the old site to my PC. I then exported the two tables to an excel spreadsheet (in Access go to external data, and look for export to Excel. You’ll need to have created a blank spreadsheet with the name you want to use first) and ran the formula

    =CONCATENATE(MID(N2,1,4),"-",MID(N2,5,2),"-",MID(N2,7,2)," “,MID(N2,9,2),":",MID(N2,11,2),":",MID(N2,13,2))

    Where column N contained the old date.

    I then prefixed the original heading with OLD (eg heading T_DATE became OLDT_DATE) and headed the new column with the heading of the old one (eg T_DATE). I did this to a) preserve the original columns just in case, and b) to use for the clean slug in the conversion (see later).

    I then deleted the old table in access, and imported the two excel ones back into Access (as before external data, then import).

    I then had the same access database, but with the correct date formulas and a couple of extra columns with the old date format in them.

    Step 2 – Convert access database to MySql database

    The first thing you’ll need is a MySql database to upload to.

    You can use the one you presumably already have with WordPress/BBpress. However you’ll just need to check that none of the names co-incide. Since my Snitz database used capitals (I don’t know if they all Snitz databases do), and BBpress uses lower case, I suspect that there is no risk in doing this, but one for you to consider.

    I took the safety of creating a fresh blank database (my hosting package allows the creation of databases and manages them through phpMyadmin).
    There are numerous programmes on the web that do access to Mysql conversion, but for no particular reason I chose Bullzip http://www.bullzip.com/.

    However this needs a 32bit ODBC driver to work, so unless you already have Mysql on your local PC, you’ll need to download the driver from http://www.mysql.com/downloads/connector/odbc/ – choose the 32 bit one.

    Once installed it is a doddle to do the conversion.
    You’ll need :
    The access database on your pc
    The host address of your MySql database
    The database name
    The username
    The password.

    Run the wizard, and it’ll upload in minutes.

    Finally if you’re going to do the date conversion discussed in Step 1 at this stage, now’s the time to do it.

    Step 3 – Importing/converting the data

    This is easy once you’ve got your head around it, but initially it can be very frustrating.

    In essence you need to translate what one forum calls something to what the other needs.

    For Snitz, I used the following translation

    
    Forum section FORUM_FORUM forum
    Forum id	FORUM_ID	_bbp_forum_id
    Forum parent id	CAT_ID	_bbp_parent_id
    Forum title	F_SUBJECT	post_title
    Forum Slug  (see note 1)	FORUM_ID	post_name
    Forum description	F_DESCRIPTION	post_content
    Forum Display Order	F_ORDER	Menu_order
    Forum Date Update		Post_date
    		
    Topic section	FORUM_TOPICS	topic
    Topic id	TOPIC_ID	_bbp_topic_id
    Forum id 	FORUM_ID	_bbp_forum_id
    Topic author	T_AUTHOR	post_author
    Topic title	T_SUBJECT	post_title
    Topic Slug (see note 1)	T_DATE	post_name
    Forum id (if no parent 0) 	FORUM_ID	post_parent
    Topic date update 	T_DATE	post_date
    	T_DATE	post_date_gmt
    	T_LAST_POST_DATE	post_modified
    	T_LAST_POST_DATE	post_modified_gmt
    		
    Tags section		
    Ignored		
    		
    		
    Post section	FORUM_REPLY	reply
    Post id	REPLY_ID	_bbp_post_id
    Forum id	FORUM_ID	_bbp_forum_id
    Topic  id	TOPIC_ID	_bbp_topic_id
    Author ip	R_IP	_bbp_author_ip
    Post author	R_AUTHOR	post_author
    Topic slug	R_DATE	post_name
    Post Content	R_MESSAGE	post_content
    Topic id	TOPIC_ID	post_parent
    Topic date	R_DATE	post_date
    		
    User Section	FORUM_MEMBERS	user
    Store old user id	MEMBER_ID	_bbp_user_id
    	M_NAME	user_login
    	M_PASSWORD	_bbp_password
    	M_EMAIL	user_email
    	M_HOMEPAGE	user_url
    	M_AIM	aim
    	M_YAHOO	yim
    		
    

    [NOTE if you copy this and post in a word doc, you should be able to get a nice tabbed table]
    Note 1

    I used OLDR_DATE & OLDT_DATE from the step 1 as my clean slugs. A slug is what BBpress uses for a URL to reference a post. It needs to be clean – ie not have formatting, odd characters like spaces etc. in it. I did debate using the topic ID, but was unsure if this was unique for BBpress – it may well be fine. So I used the references above as I was pretty sure that with our forum being not that busy, that no two people would have posted in the same second. If you choose to use some other field, such as topic ID, you’ll need to change the field in the converter document – see below.

    This is then used against the example converter that BBpress supplies.

    Whilst initially looking daunting, this is fairly easy to change to get it to work.

    Step 3.1 Creating a Snitz.php

    Firstly go to your site. Using FTP go to web/wp-content/plugins/bbpress/includes/admin/converters and locate example.php. FTP this to your PC and using an editor (notepad ++ is good – download it from http://notepad-plus-plus.org/) you need to change the default “froms” to those above

    But first save it locally as “snitz.php”, and change the first couple of lines to read

    
    /**
     * Implementation of snitz converter.
     */
    class snitz extends BBP_Converter_Base
    

    If you don’t do this, the converter won’t recognise it.

    Then you need to work through each of the lines of code changing the “default” source database tables and names to the correct ones.

    So looking at you new snitz.php, you’ll see for instance the first conversion is :

    
    // Forum id. Stored in postmeta.
    $this->field_map[] = array(
    'from_tablename' => 'forum', 'from_fieldname' => 'forumid',
    'to_type' => 'forum', 'to_fieldname' => '_bbp_forum_id'
    

    So to get this to work for Snitz you need to change the source table from ‘forum’ to “FORUM_FORUM” and the from field needs changing from ‘forumd’ to’ _bbp_forum_id’

    Making the lines now read :

    
    // Forum id. Stored in postmeta.
    $this->field_map[] = array(
    'from_tablename' => 'FORUM_FORUM', 'from_fieldname' => 'FORUM_ID',
    'to_type' => 'forum', 'to_fieldname' => '_bbp_forum_id'
    

    Using the translation table above you work through each of the lines, doing the substitution.

    I commented out the tags section, as I couldn’t see that Snitz had tags. I also commented out the “
    Store old user salt” and “User password verify class” sections as passwords won’t come across – see later on this.

    CRITICALLY I also added the following as the example doesn’t had a bit that brings the actual topic content across !

    
    // Topic content.
    		// Note: We join the posts table because topics do not have content.
    		$this->field_map[] = array(
    			'from_tablename'  => 'FORUM_TOPICS',
    			'from_fieldname'  => 'T_MESSAGE',
    			'join_tablename'  => 'thread',
    			'join_type'       => 'INNER',
    			'join_expression' => 'USING (threadid) WHERE post.parentid = 0',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_content',
    			'callback_method' => 'callback_html'
    

    Once all that is done, save the file locally.

    Then using FTP upload it to the same directory that example came from
    Viz : web/wp-content/plugins/bbpress/includes/admin/converters
    Now you’re ready to run the conversion!

    You’ll need the following info
    Database server (localhost if your using you local Mysql database)
    Database name
    Database User
    Database password

    Then in Dashboard, go to tools>forums>import forums

    Select “snitz” from the converter list, and fill in the database info from above.

    Click to select users, presuming that you want to bring these across.

    Then click start, and sit back and watch. If you’ve got everything right, you’ll see a steady stream of lines such as “converting topics 1-100”

    If Fails :

    If it falls over, then you’ll need to work out which part of the snitz converter it doesn’t like (it’ll give you a line number). Usually you’ve got punctuations wrong, or called something by a wrong name. Remember snitz (in my case anyway) uses capitals, and bbpress needs lowercase, so ensure that’s all ok.

    If nothing comes through (it usually says “starting conversion…conversion complete”), then you have got the connection wrong, as it’s not finding the database and tables.

    If Success :

    Then you’ll need to repair the links (Dashboard>tools>forums>repair forums). Otherwise in my case the forum listings came through, but with no topics listed against them.

    Do as the tool suggests – I ran several at the same time, and wrote most of this document whilst waiting !

    Finally – users

    If you successfully imported your users, they’ll have accounts, but their passwords won’t work – Bbpress can’t bring these across.

    The easiest way is to tell them to click “lost password” and enter their email address and then they can reset their own.

    And that’s how it’s done !

Viewing 23 replies - 1 through 23 (of 23 total)
  • Many thanks for taking the time to write this and share with the community. :)

    I added a link to this post from http://codex.bbpress.org/import-forums/custom-import/


    gianjj
    Participant

    @gianjj

    very helpful article,
    I’m work for migrate a big italian snitz community with 8.200 User and more than 1.2 million replies… in Ms SQL Server 2003

    I Hope everything will work good! :-)

    I suppose the best way to move (huge) data is use OPENQUERY with linked server from MsSQL to MySQL…. during my work I will post information about my experience!

    I hope can help someone in same situation :-)

    By JJ


    gianjj
    Participant

    @gianjj

    Example of DATE formatted with functionaly made for MSSQL Server

    
    ---------------------
    --- 10 TOPIC POST from FORUM_TOPICS
    ---------------------
    SELECT TOP (10) 
    
    	substring(T_LAST_POST, 1,4)+  '-' + substring(T_LAST_POST, 5,2)+ '-'+
    	substring(T_LAST_POST, 7,2)+  ' ' + substring(T_LAST_POST, 9,2)+ ':'+ 
    	substring(T_LAST_POST, 11,2)+ ':' + substring(T_LAST_POST, 13,2)
    	AS T_LAST_POST_FORMATTED, 
    
    	substring(T_DATE, 1,4)+  '-' + substring(T_DATE, 5,2)+ '-'+
    	substring(T_DATE, 7,2)+  ' ' + substring(T_DATE, 9,2)+ ':'+ 
    	substring(T_DATE, 11,2)+ ':' + substring(T_DATE, 13,2)
    	AS T_DATE_FORMATTED,
    	*
    FROM FORUM_TOPICS
    
    ---------------------
    --- 10 TOPIC REPLY from FORUM_REPLY
    ---------------------
    
    SELECT TOP (10) 
    	substring(R_DATE, 1,4)+  '-' + substring(R_DATE, 5,2)+ '-'+
    	substring(R_DATE, 7,2)+  ' ' + substring(R_DATE, 9,2)+ ':'+ 
    	substring(R_DATE, 11,2)+ ':' + substring(R_DATE, 13,2)
    	AS R_DATE_FORMATTED,
    	*
    FROM FORUM_REPLY
    
    ---------------------
    --- 10 USER from FORUM_MEMBERS
    ---------------------
    SELECT TOP (10)  
    	substring(M_DATE, 1,4)+  '-' + substring(M_DATE, 5,2)+ '-'+
    	substring(M_DATE, 7,2)+  ' ' + substring(M_DATE, 9,2)+ ':'+ 
    	substring(M_DATE, 11,2)+ ':' + substring(M_DATE, 13,2)
    	AS M_DATE_FORMATTED,                      
    	*
    FROM FORUM_MEMBERS

    gianjj
    Participant

    @gianjj

    This script launched in QUERY ANALYZER of MsSQL server will generate a list of SQL to import in MySQL ; remember to select “Result in a File”

    Alla this script will not take effect on your database, but will help you to pre-generate all SQL to move your data….
    TEST IT BEFORE MOVE ALL DATA YOU ARE THE ONLY ONE RESPONSABLE OF YOURS WORK :-P

    
    
    --------------------------------
    --- Will Generate a List of INSERT INTO query
    --- Formatted for MySQL table: wp_users
    --- JUST ONE MEMEBR
    --------------------------------
    Select TOP 1
    'INSERT INTO ''wp_users''(''ID'', ''user_login'', ''user_pass'', ''user_nickname'', ''user_email'', 
    ''user_url'', ''user_registred'', ''user_activation_key'', ''user_status'', ''display_name'') 
     VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', '''+M_NAME+''', ''REMEMBER_TO_REST_ALL_PASSWORD'', '''+M_NAME+''','''+M_EMAIL+'''
     , '''+M_HOMEPAGE+''', '''+M_NAME+''', '''+
     	substring(M_DATE, 1,4)+  '-' + substring(M_DATE, 5,2)+ '-'+
    	substring(M_DATE, 7,2)+  ' ' + substring(M_DATE, 9,2)+ ':'+ 
    	substring(M_DATE, 11,2)+ ':' + substring(M_DATE, 13,2)
     +''','''',''0'','''+M_NAME+'''
     );'
    from dbo.FORUM_MEMBERS order by MEMBER_ID asc
    
    --------------------------------
    --- Will Generate a List of INSERT INTO query
    --- Formatted for MySQL table: wp_usermeta
    --- JUST ONE MEMEBR
    --------------------------------
    Select TOP 1
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''first_name'', '''+M_FIRSTNAME+''');',
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''last_name'', '''+M_LASTNAME+''');', 
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''nickname'','''+M_NAME+''');',
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''description'', '''');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''rich_editing'', ''true'');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''comment_shortcuts'', ''false'');', 
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''admin_color'', ''fresh'' );', 
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''use_ssl'', ''0'');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''show_admin_bar_front'', ''true'');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''wp_capabilities'', ''a:1:{s:10:"subscriber";b:1;}'');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''wp_user_level'', ''0'');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''dismissed_wp_pointers'', ''wp330_toolbar,wp330_saving_widgets,wp340_choose_image_from_library,wp340_customize_current_theme_link,wp350_media,wp360_revisions,wp360_locks'');',  
    'INSERT INTO ''wp_usermeta''(''user_id'', ''meta_key'', ''meta_value'') VALUES ('+CAST(MEMBER_ID as nvarchar(10))+', ''default_password_nag'', ''1'');'  
    from dbo.FORUM_MEMBERS order by MEMBER_ID asc
    

    gianjj
    Participant

    @gianjj

    Opss REPLACE the CODE TAG with ‘


    Robin W
    Moderator

    @robin-w

    @gianjj – thanks for adding this – very helpful, and please continue to update us !

    I forget that it was only 13 months ago that I started using bbPress – seems like years now !

    Cool, I just edited a couple of the posts above to wrap some code in ‘code’ tags to make things a little clearer :)

    One more thing, when making a custom importer always base it of the Example.php importer, I keep that updated with examples and any other changes I make for bbPress.

    The latest (updated 3 days ago) is here:

    https://bbpress.trac.wordpress.org/browser/trunk/src/includes/admin/converters/Example.php

    As Robin points out, you just delete/remove the sections or fields that are not applicable to your import e.g. ‘tags’ or if your database doesn’t include the ‘counts’ for topics in forums then remove that part in the forum section.

    And just because I could and it was quick and easy, here is a copy of the Snitz.php based on the latest Example.php with Robin’s fields mappings.

    https://gist.github.com/ntwb/2f057071dd05f9758570

    You can also compare the revisions (Seeing the changes made from the source Example.php)

    https://gist.github.com/ntwb/2f057071dd05f9758570/revisions


    gianjj
    Participant

    @gianjj

    Ok STEPHEN, tomorrow I’ll try to config a new SNITZ.PHP with all information and run it!
    (hope will be my friend hihihihi)

    Actually the only doubt is where store the signature, Snitz stored in
    FORUM_MEMBERS > M_SIG

    And ….I think can be more helpful indicate which fieldname are required in bbp …may be ther is but I didn’t find it

    Tnks
    Bye J


    gianjj
    Participant

    @gianjj

    ok, as if I hadn’t written the last message :-D :-D :-D

    bbPress doesn’t actually support the use of signatures ‘out of the box’.

    What we can do though is still import that data and store it in the wp_usermeta table, then if you do add a plugin that supports signatures in bbPress you can then copy that data (Using phpMyAdmin for example) from your imported database to however that plugin supports signatures.

    Simply add this field map:

    
    // User Signature (Stored in usermeta)
    $this->field_map[] = array(
    	'from_tablename'  => 'FORUM_MEMBERS',
    	'from_fieldname'  => 'M_SIG',
    	'to_type'         => 'user',
    	'to_fieldname'    => '_bbp_snitz_user_sig'
    );
    

    Thus once you (if you do) add a plugin to support signatures to can then query against the _bbp_snitz_user_sig field directly with MySQL and update your user profile fields.

    I just added the above to the Gist also, you can see the changes via the ‘revisions’ link.


    gianjj
    Participant

    @gianjj

    really many thanks for your help….

    On first look I see comments block of topic and reply count not supported in snitz… but is a mistake….. (is it ?)

    		// Forum topic count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename' => 'FORUM_FORUM',
    			'from_fieldname' => 'F_TOPICS',
    			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_topic_count'
    		);
     
    		// Forum reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename' => 'FORUM_FORUM',
    			'from_fieldname' => 'F_COUNT',
    			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_reply_count'
    		);
     
    		// Forum total topic count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename' => 'FORUM_TOTALS',
    			'from_fieldname' => 'T_COUNT',
    			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_total_topic_count'
    		);
     
    		// Forum total reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename' => 'FORUM_TOTALS',
    			'from_fieldname' => 'P_COUNT',
    			'to_type'        => 'forum',
    			'to_fieldname'   => '_bbp_total_reply_count'
    		);

    And I’m Not sure what the difference is from this two information….. but there is a total replies per topic

    		// Topic reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'FORUM_TOPICS',
    			'from_fieldname'  => 'T_REPLIES',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_reply_count',
    			'callback_method' => 'callback_topic_reply_count'
    		);
     
    		// Topic total reply count (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'FORUM_TOPICS',
    			'from_fieldname'  => 'T_REPLIES',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_total_reply_count',
    			'callback_method' => 'callback_topic_reply_count'
    		);

    There is also this :-)

    		// Topic author ip (Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'FORUM_TOPICS',
    			'from_fieldname'  => 'T_IP',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_author_ip'
    		);

    This one is for replies

    FORUM_REPLY > R_IP

    tnks
    J


    gianjj
    Participant

    @gianjj

    Stiky Status
    1 = Stycky
    0 = No Sticky

    		// Sticky status (Stored in postmeta))
    		$this->field_map[] = array(
    			'from_tablename'  => 'FORUM_TOPICS',
    			'from_fieldname'  => 'T_STICKY',
    			'to_type'         => 'topic',
    			'to_fieldname'    => '_bbp_old_sticky_status',
    			'callback_method' => 'callback_sticky_status'
    		);

    gianjj
    Participant

    @gianjj

    Member status
    1 = Active
    0 = No Active

    		// User status.
    		$this->field_map[] = array(
    			'from_tablename' => 'FORUM_MEMBERS',
    			'from_fieldname' => 'M_STATUS',
    			'to_type'        => 'user',
    			'to_fieldname'   => 'user_status'
    		);

    gianjj
    Participant

    @gianjj

    I forgot this, I’m migrating from Snitz Forums 2000 Version 3.4.06 :-)
    Can find this information on Table: FORUM_CONFIG_NEW

    Firstly I only included the fields Robin had supplied, I do not have a copy of Snitz to test either. So I commented out but didn’t remove any of the code so that it would stand out and you could update any of these as needed which you have done.

    The forum ‘counts’ I think you may have incorrect in your post above:
    * _bbp_topic_count – The number of topics that are ‘publicly’ visible
    * _bbp_reply_count – The number of replies that are ‘publicly’ visible
    * _bbp_total_topic_count – The total number of topics (includes topics that are are unapproved or hidden)
    * _bbp_total_reply_count – The total number of replies (includes replies that are are unapproved or hidden)

    A clearer explanation of each of these would be helpful and also are each of these fields in the FORUM_FORUM database table:
    * F_TOPICS
    * F_COUNT
    * T_COUNT
    * P_COUNT

    The topic ‘counts’ is the same:
    * _bbp_reply_count – The number of replies that are ‘publicly’ visible
    * _bbp_total_reply_count – The total number of replies (includes replies that are are unapproved or hidden)

    A clearer explanation of T_REPLIES would be helpful and is this fields in the FORUM_TOPICS database table?

    Whilst you answer those I’ll add Topic Author IP, Stickies and Member Status :)

    There are actually a couple more fields that can be added that I just realized are not in the Example.php file (which I will add)

    Forum type – Is this a forum or category?

    
    		// Forum type (Category = 0 or Forum = 1, Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'forums_table',
    			'from_fieldname'  => 'the_forum_type',
    			'to_type'         => 'forum',
    			'to_fieldname'    => '_bbp_forum_type',
    			'callback_method' => 'callback_forum_type'
    		);
    

    Forum status, can new topics be created in the forum?

    
    		// Forum status (Unlocked = 0 or Locked = 1, Stored in postmeta)
    		$this->field_map[] = array(
    			'from_tablename'  => 'forums_table',
    			'from_fieldname'  => 'the_forum_status',
    			'to_type'         => 'forum',
    			'to_fieldname'    => '_bbp_status',
    			'callback_method' => 'callback_forum_status'
    		);
    

    Topic status – Can new replies be created for this topic?

    
    
    		// Topic status (Open or Closed)
    		$this->field_map[] = array(
    			'from_tablename'  => 'topics_table',
    			'from_fieldname'  => 'the_topic_status',
    			'to_type'         => 'topic',
    			'to_fieldname'    => 'post_status',
    			'callback_method' => 'callback_topic_status'
    		);
    

    gianjj
    Participant

    @gianjj

    dear Stephen don’t be angry …english is not my language :-P
    ..however…

    FORUM_FORUM => F_TOPICS
    This information is refer to all topics (Only Topic) in specific forum ex: “Forum_name => Total Number of topic”

    FORUM_FORUM => F_COUNT
    This information is refer of all replies (Only Reply) in specific forum ex: “Forum_name => Total Number of replis”

    I think a sum of this two information give us a total like here, ex: “Troubleshooting => 30,051″

    ================

    In this Table FORUM_TOTALS, Snitz save information of whole BB, a sum of all replies and all topics
    FORUM_TOTALS => T_COUNT whole topic
    FORUM_TOTALS => P_COUNT whole replies

    ================

    About FORUM_TOPICS => T_REPLIES
    is this field Snitz save number of replies relate to single topic ex this one:
    “Documented import from snitz access database” > “18 replies”

    ================

    with minor importance, also stored in this table FORUM_TOPICS we have

    T_LAST_POST_AUTHOR ID value of last member have posted reply
    ex: Last reply from: Stephen Edgar

    T_LAST_POST data value of last reply added (same format yyyymmddhhmmss)
    ex: Last activity: 5 hours, 57 minutes ago

    T_LAST_POST_REPLY_ID ID value of last reply added actually used for link in email (?)
    ex: Post Link: http://bbpress.org/forums/topic/documented-import-from-snitz-access-database/#post-146492


    gianjj
    Participant

    @gianjj

    To know which Forum or Topic are open and permit to receive new Topic reply, we need this fields

    Forum area
    FORUM_FORUM => F_STATUS 1=open | 0=close

    Single Topic
    FORUM_TOPICS => T_STATUS 1=open | 0=close

    ===========

    To know the categories we need to query the table FORUM_CATEGORYwhere we have

    CAT_ID Unique ID used to join in FORUM_FORUM => CAT_ID table
    CAT_NAME as is …the name
    CAT_STATUS need a mention this field, Category locked = 0 | Category Open = 1

    Ex of query select for all category and related forum

    SELECT FORUM_CATEGORY.CAT_ID, FORUM_CATEGORY.CAT_NAME, FORUM_FORUM.FORUM_ID, FORUM_FORUM.F_SUBJECT
    FROM FORUM_CATEGORY LEFT OUTER JOIN
    FORUM_FORUM ON FORUM_CATEGORY.CAT_ID = FORUM_FORUM.CAT_ID
    GROUP BY FORUM_CATEGORY.CAT_ID, FORUM_CATEGORY.CAT_NAME, FORUM_FORUM.FORUM_ID, FORUM_FORUM.F_SUBJECT

    ===========

    dear Stephen don’t be angry …english is not my language

    Do not worry about that, I understand you fine :)

    Edit: Your MySQL is Excellent ;)

    Forums and Topic status = Done (F_TOPICS & F_COUNT)

    Forum Topics and Replies Counts = Done (T_REPLIES)

    Last Reply Date = Done (T_LAST_POST)

    Due to limitations with the importer configuration we can’t include the FORUM_TOTALS or FORUM_CATEGORY tables. You will need to manually reassign your your forum categories after importing, usually there is only a small number of these so this shouldn’t be much of an issue. The ‘Total’ Counts’ are also not actually needed, we do this slightly different and are recalculated by bbPress after the import has finished with the ‘Repair Tools’.

    Edit: After the import has finished we actually recount all the ‘counts’ so these are not very important to the import, they kind of just help a few things on the way.


    gianjj
    Participant

    @gianjj

    Dear Stephen great work! ;)

    I think the last “basic” information for this great converter is “User status”
    we can find it in a snitz table named FORUM_MEMBERS => M_STATUS
    M_STATUS field have’s two value: 1=unlocked | 0=locked

    NB: also when a user was removed / delete, will stay in the DB, with this two condition:
    M_STATUS => 0
    M_NAME => n/a

    @gianjj Thanks, I will need to think about the best way to add ‘Member Status’ and ‘Deleted Members’, I have some other changes coming for users and will look at adding this at the same time. So for now this will need to be performed manually after importing.

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

You must be logged in to reply to this topic.