Lussomo Vanilla import, Whispers not converted to Private Messages Issue Tools
issueid=24827 Sun 9th Mar '08 8:15pm
Senior Member
Lussomo Vanilla import, Whispers not converted to Private Messages
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.
Issue Details
Issue Type Bug
Project ImpEx
Category Suggestion
Status Working as Designed
Priority 5 - Medium
Affected Version 1.86
Fixed Version (none)
Users able to reproduce bug 1
Users unable to reproduce bug 0
Attachments 0
Assigned Users (none)
Tags (none)

Sun 16th Mar '08 3:42pm
Senior Member
  
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.
Code:
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.
Code:
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
Code:
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.
Reply
Sun 16th Mar '08 3:52pm
Senior Member
  
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.
Reply
Wed 3rd Dec '08 9:48pm
ImpEx Developer
  
Indeed, PM's are not supported for the Vanilla system, there isn't the customer demand to update this system at the moment.
Reply
Reply