Previous Thread
Next Thread
Print Thread
Hop To
Joined: Jun 2006
Posts: 693
Addict
Addict
Joined: Jun 2006
Posts: 693
With the privacy changes rolling out in Europe, and my forums having probably 10,000 members who no longer use it, I want to run a script to delete all private messages before 2018. That reduces my exposure to privacy issues.

Is there a simple way for me to prune out old private messages? The private_messages_posts backup file is nearly 60mb in size so I've got a few in there.

Can I select from private_messages_topics to get IDs before 2018 then delete those from users, posts, and then topics?

Thanks.


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 58,000 members / 850,000 posts
Joined: Jun 2006
Posts: 16,289
Likes: 115
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,289
Likes: 115
UBB.threads does not have a tool to prune Private Topics older than a specific date; the closest tool to that would be the Prune Topics tool, which doesn't touch PTs.

UBB.threads has three tables for Private Topics, ubbt_PRIVATE_MESSAGE_TOPICS holds the topic information (the topic id, the timestamp it was created, total replies, subject, creation user, last reply time, topic poster id, and last poster name), ubbt_PRIVATE_MESSAGE_POSTS holds each individual post, and ubbt_PRIVATE_MESSAGE_USERS holds information for each user who is involved in the private topic.

With this data we can extrapolate that data will need to be purged from three tables in order to prune all of the data. You would have to create a PHP script, or custom query, to check the timestamp for TOPIC_TIME in the topics table, you'll need to check if that timestamp is equal to or greater than the current time (as a timestamp) minus however many years you wish to prune, to build a list of topics.

Now that we know the Topic IDs of the topics that need to be pruned, we'd take the TOPIC_ID and then purge the data from the other two tables with entries that match the TOPIC_ID.

Another option would be to just truncate all three tables (empty them) and have your users start from 0 PTs.


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 693
Addict
Addict
Joined: Jun 2006
Posts: 693
OK yes that sounds like the plan I had. Thanks!


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 58,000 members / 850,000 posts
Joined: Jun 2006
Posts: 693
Addict
Addict
Joined: Jun 2006
Posts: 693
OK minor issue in handling these queries. The dates in MySQL are long numerical values. They aren't actually dates. It also doesn't seem to be the standard UNIX "seconds since 1900" value since those are in the range of 2 billion and these are in the range of 1.3 billion. What would the numeric value for January 1, 2018 be?

UPDATE OK I found another value that some people use, which is the "seconds since January 1 1970" which seems to be close to these values. So I will calculate based on that.

Last edited by BellaOnline; 05/20/2018 4:12 PM.

Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 58,000 members / 850,000 posts
Joined: Apr 2004
Posts: 1,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145


Current developer of UBB.threads PHP Forum Software
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
Joined: Jun 2006
Posts: 693
Addict
Addict
Joined: Jun 2006
Posts: 693
OK for others who may want to do this, this is what I did.

To figure out dates:
https://www.epochconverter.com/

To find all entries since 2017:
select * from ubbt_private_message_topics where topic_time > 1483251102

To find all entries since 2018:
select * from ubbt_private_message_topics where topic_time > 1514787102;

Turns out all the entries since 2017 were just my welcome messages. So there was no harm in clearing out the entire message library. Still, to play it safe and run controlled queries:

SELECT max(topic_id) from ubbt_private_message_topics;
max ID is 20199

That was the most recent welcome message I'd sent.

Then:

delete from ubbt_private_message_posts where topic_id <= 20199;
66397 rows

delete from ubbt_private_message_topics where topic_id <= 20199;
16133 rows

delete from ubbt_private_message_users where topic_id <= 20199;
20614 rows

My private messages area is now wholly clear. Which makes my preparation for the upcoming GDPR rollout a bit easier. Any website owner who has access to the email address or IP address of any web visitor from Europe has to comply with these.

https://websitesbydiane.com/web-design/gdpr-for-website-owners-small-businesses/

Ask with any questions!


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 58,000 members / 850,000 posts
Joined: Jun 2006
Posts: 693
Addict
Addict
Joined: Jun 2006
Posts: 693
Isaac -

You might have posted in the middle of my postings smile. My initial Google queries all found the 1900 starting point for counting dates which wasn't what these forums use. It was only some subsequent googling that found the second way of handling dates, which is what these forums use. I am now fully set with this project. Thanks!


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 58,000 members / 850,000 posts
Joined: Apr 2004
Posts: 1,945
Likes: 145
UBB.threads Developer
UBB.threads Developer
Joined: Apr 2004
Posts: 1,945
Likes: 145
pro tip: if you clear the "ubbt_PRIVATE_MESSAGE_USERS" database for the single user id who is asking for them to be removed, then when you run the CONTENT REBUILDER ( Prune Orphaned Private Messages ), all their PM will be cleared. And I believe the pruning is also triggered each time any user logs in... along with subscriptions and the post view counter.

I also believe that I posted in another thread, the SQL that would turn off all auto emails for users who have not logged in within 24months. You could just stick that in to a weekly cron task.


Current developer of UBB.threads PHP Forum Software
Current Release: UBBT 7.7.5 // Preview: UBBT 8.0.0
isaac @ id242.com // my forum @ CelicaHobby.com
Joined: Jun 2006
Posts: 16,289
Likes: 115
UBB.threads Developer
UBB.threads Developer
Joined: Jun 2006
Posts: 16,289
Likes: 115
The Unix Epoch is in 1970, not 1900.
Quote
Unix time (also known as POSIX time or Epoch time) is a system for describing instants in time, defined as the number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970, not counting leap seconds. It is used widely in Unix-like and many other operating systems and file formats.

I use the tool at Epoch Converter to convert variables where I can't use PHP date("U")


I am a Web Development Contractor, I do not work for UBBCentral. I have provided free User to User Support since the beginning of these support forums.
Do you need Forum Install or Upgrade Services?
Forums: A Gardeners Forum, Scouters World
UBB.threads: UBBWiki, UBB Styles, UBB.Sitemaps
Longtime Supporter & Resident Post-A-Holic
VNC Web Services: Code Modifications, Upgrades, Styling, Coding Services, Disaster Recovery, and more!
Joined: Jun 2006
Posts: 693
Addict
Addict
Joined: Jun 2006
Posts: 693
My original searches were finding the NTP Prime Epoch results - it was only later searching that had me realize that UBB was using the Unix Prime Epoch smile.

https://www.eecis.udel.edu/~mills/y2k.html

So it's not that Google was wholly wrong in my initial searches. It just gave me the NTP as the default results for my search and I didn't realize until researching that the values didn't match what I was seeing in our system.

I suppose what would help is a database table map that explained how each field is used in UBB - do we have one? I'd find that to be really useful!


Lisa Shea, owner, BellaOnline.com
BellaOnline Website
BellaOnline Forums - UBB since Apr 2002 - 58,000 members / 850,000 posts

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Forum Privacy Policy
by ECNet - 02/26/2024 11:58 AM
Who's Online Now
2 members (Nightcrawler, Ruben), 322 guests, and 160 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)