[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]

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

===========

[gianjj]

dear Stephen don’t be angry …english is not my language 😛
..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: https://bbpress.org/forums/topic/documented-import-from-snitz-access-database/#post-146492

[gianjj]

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

[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]

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]

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]

ok, as if I hadn’t written the last message 😀 😀 😀

[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]

Opss REPLACE the CODE TAG with ‘

[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 😛

--------------------------------
--- 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]

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]

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