1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

XF 1.5 Delete vb pms before import...

Discussion in 'Installation, Upgrade, and Import Support' started by Joeychgo, Mar 30, 2016.

  1. Joeychgo

    Joeychgo Well-Known Member

    I have a larger vb board I am about to convert to XF.

    Before I do, I want to clear some PMs. I actually want to do 2 things.

    1. Delete all PMs from users who haven't visited the forum in 6 months or more;
    2. Of the remaining users, I want to delete all pms in excess of 10 from each user.

    The idea being, no user will have more then 10 PMs in their box, and if they haven't been to the site in 6 months, they will have zero.

    So I'm looking for queries that will accomplish this. Could be vb queries or xf queries --- either works for me.
     
  2. wang

    wang Well-Known Member

    The queries must be executed on the vbulletin database if you want to delete the conversations before converting to xenforo.
     
    Last edited: Mar 30, 2016
  3. Joeychgo

    Joeychgo Well-Known Member

    Thanks -- still need to know what queries to run
     
  4. Slavik

    Slavik XenForo Moderator Staff Member

    Tf you use phpmyadmin empty the pm tables labeled pm, pmtext etc.

    If via command line, its the truncate command.
     
  5. Chris D

    Chris D XenForo Developer Staff Member

    I think Joey wants it conditional:
    Don't empty or truncate the tables or you'll lose all PMs for all users.
     
  6. Joeychgo

    Joeychgo Well-Known Member

    I actually want to do 2 things.

    1. Delete all PMs from users who haven't visited the forum in 6 months or more;
    2. Of the remaining users, I want to delete all pms in excess of 10 from each user.
     
  7. AndyB

    AndyB Well-Known Member

  8. Joeychgo

    Joeychgo Well-Known Member

    that doesn't do anything I asked for.....

    Am I missing something? This is what I want to do...

    1. Delete all PMs from users who haven't visited the forum in 6 months or more;
    2. Of the remaining users, I want to delete all pms in excess of 10 from each user.

    Am I unclear somehow or haven't explained it properly?
     
  9. Chris D

    Chris D XenForo Developer Staff Member

    The main problem is you're asking a bunch of XF experts how to do something in VB which is something that we've either never done, or haven't done for many years :)
     
    wang likes this.
  10. wang

    wang Well-Known Member

    You are being very clear sir, however this is something that must be asked at the vbulletin support forums, rather than here. Because you are asking how to do things on a vbulletin forum and not on a xenforo forum.
     
  11. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    PMs are in vB's pm and pmtext tables. You would need to prune the PMs in vB before running the import, or prune them in XF after the import.

    vB's schema for PMs is more simple so it would be easier to do this in vB. I will restore an old vB backup and work on implementing your criteria. Probably tomorrow.
     
  12. Joeychgo

    Joeychgo Well-Known Member

    Thank you Jake
     
  13. Alfa1

    Alfa1 Well-Known Member

    Have you considered to import all PMs between the same users as one conversation?
     
  14. Joeychgo

    Joeychgo Well-Known Member

    I'm not sure what you mean?
     
  15. Alfa1

    Alfa1 Well-Known Member

    If you import PMs to XF conversations you will have a large number of conversations. Mainly because each vb message is imported as a separate conversation. You could opt to reduce the number of conversations by merging all conversations between the same members.

    Lets say that member A and B have sent each other 200 PMs. Imported to XF that will be 200 conversations. If you merge them to 1 conversation with 200 messages then that reduces the number of conversations a lot. IIRC this has been done before.
     
  16. Joeychgo

    Joeychgo Well-Known Member

    I don't have that many members who just pm each other constantly. Nice idea but it wont work for me.
     
    Alfa1 likes this.
  17. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    vB queries:

    Code:
    /* PRUNE PMS FOR INACTIVE USERS */
    DELETE pm.*
    FROM pm AS pm
    WHERE pm.userid IN (
        SELECT user.userid
        FROM user AS user
        WHERE user.lastvisit < UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
    );
    
    /* DELETE PMS IN EXCESS OF 10 FOR EACH USER */
    DELETE pm.*
    FROM pm AS pm
    INNER JOIN (
        SELECT pm1.userid, (
            SELECT pm2.pmid
            FROM pm AS pm2
            WHERE pm2.userid = pm1.userid
            ORDER BY pm2.pmid DESC
            LIMIT 9, 1
        ) AS 'oldestPM'
        FROM pm AS pm1
        GROUP BY pm1.userid
    ) AS bound ON (bound.userid = pm.userid)
    WHERE 1=1
    -- older than 10 most recent pms for each userid
    AND pm.pmid < bound.oldestPM;
    
    /* DELETE TEXT RECORDS THAT WERE ORPHANED FROM PRUNES */
    DELETE pmtext.*
    FROM pmtext AS pmtext
    LEFT JOIN pm AS pm ON (pm.pmtextid = pmtext.pmtextid)
    WHERE pm.pmid IS NULL;
    
    Of course you should test this on a copy of your vB database and then do test imports from that. I haven't tested this with a vB frontend or with an import to XF.
     
    wang and MattW like this.

Share This Page