[SMF Converter] Vanilla 2.1.x -> Help needed!

[MasterOne]

I have been playing around with the [SMF Converter] Vanilla 2 the whole day, but it just does not work with Vanilla v2.1.x as it was originally written for Vanilla 1 and then updated to Vanilla 2.0.x, which is not 100% compatible with v2.1.x.

I have made some changes to vanilla_to_smf.sql to make the conversion script run till the end without errors, and the result even looks good, except that the initial posting of each thread is missing. That's because the initial posting of a thread is not a comment, but to be found in the "Body" row in the Discussions table, and I have absolutely no idea on how to convert that entry into the initial comment of newly generated SMF threads.

Here is what I did so far and the information I can provide for any assistance, that is highly appreciated:

At first I performed a fresh installation of SMF 2.0.9, I then uploaded the Vanilla 2 converter files convert.php and vanilla_to_smf.sql, created a folder named "conf" and put the following config.php into that folder:

Code Select Expand

<?php
$Configuration['DATABASE_NAME'] = 'Name_of_SMF_Database';
$Configuration['DATABASE_TABLE_PREFIX'] = 'GDN_';
?>

That was necessary because the name and content of the Vanilla config file has changed. As can be seen, the default prefix for the Vanilla v2.1.x tables is GDN_ and to simplify the process I have imported the Vanilla tables into the SMF database (would not have been possible otherwise because I had both databases on two different database servers).

The table structures have changed quite a bit, I have attached the structural overview of the relevant tables. What's completely missing in the old Vanilla 2 converter is the conversion of PMs, which are stored in GDN_Conversation and GDN_ConversationMessage (for completeness I have attached the structural overview of these two as well). It would be nice to preserve PMs as well, but I could live without them if necessary.

Here is my modified vanilla_to_smf.sql (it's a hack, some things like the use of "$ignore = true;" surely is wrong, but it kept complaining about duplicate entry for key PRIMARY without it; same with the workarounds for several "Wrong value type sent to the database. Integer expected."):

Code Select Expand

/* ATTENTION: You don't need to run or use this file!  The convert.php script does everything for you! */

/******************************************************************************/
---~ name: "Vanilla"
/******************************************************************************/
---~ version: "SMF 2.0"
---~ settings: "/conf/config.php"
---~ from_prefix: "`$Configuration[DATABASE_NAME]`.$Configuration[DATABASE_TABLE_PREFIX]"
---~ table_test: "{$from_prefix}User"

/******************************************************************************/
--- Converting members...
/******************************************************************************/

TRUNCATE {$to_prefix}members;

---* {$to_prefix}members
---{
$ignore = true;
$row['date_registered'] = strtotime($row['date_registered']);
if(empty($row['date_registered']))
    $row['date_registered'] = mktime();
$row['last_login'] = strtotime($row['last_login']);
if(empty($row['last_login']))
    $row['last_login'] = mktime();
if(empty($row['posts']))
    $row['posts'] = 0;
$row['real_name'] = trim($row['real_name']) == '' ? $row['member_name'] : $row['real_name'];
---}
SELECT
m.UserID AS id_member, m.Name as member_name, m.DateFirstVisit AS date_registered,
(m.CountDiscussions + m.CountComments) AS posts, m.DateLastActive AS last_login,
CASE mr.RoleID WHEN 4 THEN 1 ELSE 0 END AS id_group, m.Password AS passwd,
'' AS real_name, m.Email AS email_address,
CASE m.ShowEmail WHEN 1 THEN 0 ELSE 1 END as hide_email, m.Photo AS avatar,
m.LastIPAddress AS member_ip, m.LastIPAddress AS member_ip2, '' AS password_salt,
'' AS lngfile, '' AS buddy_list, '' AS pm_ignore_list, '' AS message_labels,
'' AS personal_text, '' AS time_format, '' AS usertitle, '' AS secret_question,
'' AS secret_answer, '' AS validation_code, '' AS additional_groups, '' AS smiley_set
FROM {$from_prefix}User AS m
LEFT JOIN {$from_prefix}UserRole AS mr ON (m.UserID = mr.UserID);
---*

/******************************************************************************/
--- Converting boards...
/******************************************************************************/

TRUNCATE {$to_prefix}boards;

---* {$to_prefix}boards
SELECT
c.CategoryID AS id_board, 1 AS id_cat, 0 AS id_parent, 0 AS num_posts,
0 AS num_topics, '-1,0' AS member_groups, SUBSTRING(c.Name, 1, 255) AS name,
SUBSTRING(c.Description, 1, 65534) AS description, c.Sort AS board_order
FROM {$from_prefix}Category AS c;
---*

/******************************************************************************/
--- Converting topics...
/******************************************************************************/

TRUNCATE {$to_prefix}topics;
TRUNCATE {$to_prefix}log_topics;
TRUNCATE {$to_prefix}log_boards;
TRUNCATE {$to_prefix}log_mark_read;

---* {$to_prefix}topics
---{
if(empty($row['id_first_msg']))
    $row['id_first_msg'] = 0;
if(empty($row['id_last_msg']))
    $row['id_last_msg'] = 0;
if(empty($row['id_member_updated']))
    $row['id_member_updated'] = 0;
---}
SELECT
t.DiscussionID AS id_topic, t.Announce AS is_sticky, t.CategoryID AS id_board,
MIN(p.CommentID) AS id_first_msg, MAX(p.CommentID) AS id_last_msg,
t.InsertUserID AS id_member_started, t.UpdateUserID AS id_member_updated,
t.CountComments AS num_replies, t.Closed AS locked,
t.CountViews AS num_views
FROM {$from_prefix}Discussion AS t
LEFT JOIN {$from_prefix}Comment AS p ON (p.DiscussionID = t.DiscussionID)
GROUP BY p.DiscussionID;
---*

/******************************************************************************/
--- Converting posts...
/******************************************************************************/

TRUNCATE {$to_prefix}messages;

---* {$to_prefix}messages 200
---{
$ignore = true;
$row['poster_time'] = strtotime($row['poster_time']);
$row['modified_time'] = is_null($row['modified_time']) ? 0 : strtotime($row['modified_time']);
$row['modified_name'] = is_null($row['modified_name']) ? '' : $row['modified_name'];
if(empty($row['id_board']))
    $row['id_board'] = 0;
---}
SELECT
p.CommentID AS id_msg, p.DiscussionID AS id_topic, t.CategoryID AS id_board,
p.DateInserted AS poster_time, p.InsertUserID AS id_member, t.Name AS subject,
m.Name AS poster_name, m.Email AS poster_email, p.InsertIPAddress AS poster_ip,
p.DateUpdated AS modified_time, m2.Name AS modified_name, p.Body AS body
FROM {$from_prefix}Comment AS p
LEFT JOIN {$from_prefix}Discussion AS t ON (t.DiscussionID = p.DiscussionID)
LEFT JOIN {$from_prefix}User AS m ON (m.UserID = p.InsertUserID)
LEFT JOIN {$from_prefix}User AS m2 ON (m2.UserID = p.UpdateUserID)
WHERE p.CommentID > 0;
---*

/******************************************************************************/
--- Converting settings...
/******************************************************************************/

---{
$settings = array();

//SMTP?
if (isset($Configuration['SMTP_HOST']))
{
$settings['smtp_host'] = $Configuration['SMTP_HOST'];
$settings['mail_type'] = '1';
}
if (isset($Configuration['SMTP_USER']))
$settings['smtp_username'] = $Configuration['SMTP_USER'];
if (isset($Configuration['SMTP_PASSWORD']))
$settings['smtp_password'] = $Configuration['SMTP_PASSWORD'];

// Direct registration?
if (empty($Configuration['ALLOW_IMMEDIATE_ACCESS']))
$settings['registration_method'] = '2';
// Post length?
if (isset($Configuration['MAX_COMMENT_LENGTH']))
$settings['max_messageLength'] = $Configuration['MAX_COMMENT_LENGTH'];
// Topics per page?
if (isset($Configuration['DISCUSSIONS_PER_PAGE']))
$settings['defaultMaxTopics'] = $Configuration['DISCUSSIONS_PER_PAGE'];
// Messages per page?
if (isset($Configuration['COMMENTS_PER_PAGE']))
$settings['defaultMaxMessages'] = $Configuration['COMMENTS_PER_PAGE'];
// Search results per page?
if (isset($Configuration['SEARCH_RESULTS_PER_PAGE']))
$settings['search_results_per_page'] = $Configuration['SEARCH_RESULTS_PER_PAGE'];

$inserts = array();
foreach ($settings as $variable => $value)
$inserts[] = "('$variable', '$value')";

if (!empty($inserts))
convert_query("
REPLACE INTO {$to_prefix}settings
(variable, value)
VALUES " . implode(',
', $inserts));
---}

As said, with my modifications the conversion runs through without errors, but the major problem is now the missing initial posting of each thread. If at least that could be fixed, the result would be usable, but of course the whole conversion is pointless with the initial posting of every thread missing. If only someone with the proper knowledge could rework the whole vanilla_to_smf.sql to make it properly work with Vanilla 2.1.x, that would be really great!

Please let me know if any additional info would be of help.

P.S. I've just discovered that this hackish conversion also has overwritten my administrator account, that's what someone else mentioned for the Vanilla 2.0.x converter that he could not log in any more after the conversion. There surely is a quick fix by manipulating the database with phpMyAdmin directly, but it's Friday evening and I've really had enough for today.

Gary: changed message icon to prevent confusion on what contains actual converters