XF 1.1 Help importing data from VB using raw sql query

[puppi]

I think it would be brilliant to be able to import data to XenForo using raw sql instead of the migration tool.
My particular concern here to migrate from clunky VB 4 to slick XF 1.1


The reasons being that:
1) Migration tool is inevitably much slower than raw sql query
2) By using raw sql, one may avoids many php and webserver-specific pitfalls that might cause migration tool to fail (something that actually bugging me).

So I looked at the schemas and quickly put together these queries to migrate posts and threads:

Migrate posts:

INSERT INTO xenforodb.xf_post (post_id, thread_id, user_id, username, post_date, message) SELECT
postid, threadid, userid, username, dateline, pagetext FROM vbdb.post;

migrate threads:

INSERT INTO xenforodb.xf_thread (thread_id, title, reply_count, view_count, user_id, username, post_date, sticky,
first_post_id, last_post_date, last_post_id, last_post_user_id, last_post_username) SELECT
threadid, title, replycount, views, postuserid, postusername, dateline, sticky, firstpostid, lastpost,
lastpostid,lastposterid, lastposter FROM vbdb.thread;

However, as for forums migration, I'm having difficulty because the table structure of the VB and XF diverge here and I'm by no means an expert in data migration.

Here are my (failed) attempted queries:

migrate forums:

INSERT INTO xenforodb.xf_node (node_id, title, description) SELECT forumid, title_clean, description_clean
FROM vbdb.forum;

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

INSERT INTO xenforodb.xf_forum (node_id) SELECT forumid FROM vbdb.forum;

I just need basic migration of forum->thread->post . Not fancy about thanks, profile etc. So If you have and share the queries or know how to fixed mine, I'd be greatful.

 

[Jake Bunce]

http://xenforo.com/community/resources/a-r-f-i-a-really-fast-vb4-importer.1397/

This importer is not ready-to-go. It requires programming.

The web-based importer would be better for you. The only reason to trouble yourself with the CLI-based importer is if your forum is enormous, but 500k posts is not enormous.

 

[puppi]

Thanks for the reference. However I think, a basic CLI importer is not far reaching and should be available as a free community solution. I continue trying to make correct queries while the migration tools is striving

 

[dbembibre]

I take your queries to migrate my 12 million of posts, the thread query is incorrect because you miss the node_id->forumid relation. This is the correct query.


INSERT IGNORE INTO xf_thread (nodeid, thread_id, title, reply_count, view_count, user_id, username, post_date, sticky,
first_post_id, last_post_date, last_post_id, last_post_user_id, last_post_username) SELECT
forumid, threadid, title, replycount, views, postuserid, postusername, dateline, sticky, firstpostid, lastpost,
lastpostid,postuserid, lastposter FROM forum3_6.thread;

As i run the incorrect query execute the update query to relationate threads and nodes.

mysql> select count(node_id) from xf_thread where node_id not in (select forumid from forum3_6.thread);
+----------------+
| count(node_id) |
+----------------+
| 524452 |
+----------------+
1 row in set (6.48 sec)

mysql> UPDATE xf_thread SET xf_thread.node_id = (SELECT forum3_6.thread.forumid FROM forum3_6.thread
-> WHERE forum3_6.thread.threadid = xf_thread.thread_id);