XF 1.5 Delete vb pms before import...

[Joeychgo]

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.

 

[wang]

The queries must be executed on the vbulletin database if you want to delete the conversations before converting to xenforo.

 

[Joeychgo]

The queries must be executed on the vbulletin database if you want to delete the conversations before converting to xenforo.

Thanks -- still need to know what queries to run

 

[Slavik]

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

If via command line, its the truncate command.

 

[Chris D]

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

If via command line, its the truncate command.

I think Joey wants it conditional:

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.

Don't empty or truncate the tables or you'll lose all PMs for all users.

 

[Joeychgo]

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.

 

[AndyB]

I suggest running the import without any changes to the PM tables. Then if you like use this add-on to prune old Conversations:

https://xenforo.com/community/resources/conversations-prune.4084/

 

[Joeychgo]

I suggest running the import without any changes to the PM tables. Then if you like use this add-on to prune old Conversations:

https://xenforo.com/community/resources/conversations-prune.4084/

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?

 

[Chris D]

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]

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?

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.

 

[Jake Bunce]

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.

 

[Joeychgo]

Thank you Jake

 

[Alpha1]

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

 

[Joeychgo]

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

I'm not sure what you mean?

 

[Alpha1]

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.

 

[Joeychgo]

Lets say that member A and B have sent each other 200 PMs

I don't have that many members who just pm each other constantly. Nice idea but it wont work for me.

 

[Jake Bunce]

vB queries:

/* 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.