Lussomo Vanilla import, Whispers not converted to Private Messages

[buro9]

Lussomo Vanilla import, Whispers not converted to Private Messages

In Vanilla the concept of Private Messaging is to Whisper.

Whispers can either be within a discussion (so Private Messages appear inline) or a whole discussion is a whisper (in which every comment in the discussion is actually a Private Message in vBulletin).

Whispers are always 1 to 1, only 1 sender and 1 recipient.

Any entries in the Comments table that have a WhisperUserId != 0 is actually a Private Message that is inline. AuthUserId = From User, and WhisperUserId = To User.

Any entries in the Discussions table that have a WhisperUserId != 0 is actually a whole conversation that is a stream of Private Messages. Every Comment that belongs to this discussion should be a Private Message between those two users. AuthUserId = 1st user involved, WhisperUserId = 2nd user involved... whichever user of these two isn't the AuthUserId (From User) of any Comments in the Discussion is implicitly the WhisperUserId (To User) of the Private Message.

[buro9]

Here are the three queries that you'll be needing (for a default install of Vanilla):

Fetch Threads

I exclude threads which are private message conversations by checking that the discussion itself isn't a whisper to someone else.


SELECT *
FROM {$tableprefix}Discussion
WHERE WhisperUserId = 0
ORDER BY UserID LIMIT {$start_at}, {$per_page}


Fetch Posts

I exclude all posts that belong to a discussion that is being whispered to someone, and all posts within a thread that is a whisper.


SELECT C.*
FROM {$tableprefix}Comment C
,{$tableprefix}Discussion D
WHERE D.DiscussionID = C.DiscussionID
AND D.WhisperUserID = 0
AND C.WhisperUserID = 0
ORDER BY CommentID
LIMIT {$start_at}, {$per_page}


Fetch Private Messages

Three parts to this:
1) Fetch all inline whispers and give them a subject based on the first 80 characters of the body.
2) Fetch all of the discussion whispers where the author of the discussion is talking
3) Fetch all of the discussion whispers where a person is responding to the author


SELECT LEFT(C.Body,80) Subject
,C.CommentID
,C.DiscussionID
,C.AuthUserID
,C.DateCreated
,C.EditUserID
,C.DateEdited
,C.WhisperUserID
,C.Body
,C.FormatType
,C.Deleted
,C.DateDeleted
,C.DeleteUserID
,C.RemoteIp
FROM {$tableprefix}Comment C
,{$tableprefix}Discussion D
WHERE D.DiscussionID = C.DiscussionID
AND C.WhisperUserID != 0
UNION ALL
SELECT D.Name Subject
,C.CommentID
,C.DiscussionID
,C.AuthUserID
,C.DateCreated
,C.EditUserID
,C.DateEdited
,D.WhisperUserID
,C.Body
,C.FormatType
,C.Deleted
,C.DateDeleted
,C.DeleteUserID
,C.RemoteIp
FROM {$tableprefix}Comment C
,{$tableprefix}Discussion D
WHERE D.DiscussionID = C.DiscussionID
AND D.WhisperUserID != 0
AND C.AuthUserID = D.AuthUserID
UNION ALL
SELECT D.Name Subject
,C.CommentID
,C.DiscussionID
,D.WhisperUserID AuthUserID
,C.DateCreated
,C.EditUserID
,C.DateEdited
,D.AuthUserId WhisperUserID
,C.Body
,C.FormatType
,C.Deleted
,C.DateDeleted
,C.DeleteUserID
,C.RemoteIp
FROM {$tableprefix}Comment C
,{$tableprefix}Discussion D
WHERE D.DiscussionID = C.DiscussionID
AND D.WhisperUserID != 0
AND C.AuthUserID != D.AuthUserID
ORDER BY CommentID
LIMIT {$start_at}, {$per_page}


Using those to source the data load would solve it all.

[buro9]

I don't see how to wire those pieces of SQL into ImpEx otherwise I'd do it myself. It doesn't look like private messages are being handled anywhere in the code yet. Adjusting the Discussions/Threads bit of SQL isn't a problem, but where would the Posts bit go?

Guess I'm stuck now as knowing the SQL isn't really enough to solve it for me, plugging the SQL in is needed.

Happy to help test when the next iteration is ready. Or I can offer a database for testing and point to example rows to verify that the import was successful.

[Jerry]

Indeed, PM's are not supported for the Vanilla system, there isn't the customer demand to update this system at the moment.