Automatically delete URLS after some time

[icsy7867]

I think the best way to do this would be to simply create a cron/systemd timer and run something like:

DELETE FROM yourls_url WHERE timestamp <= DATE_SUB(NOW(), INTERVAL 365 DAY);
If I wanted to delete URLS older than a year.

However, I wasnt sure if there was a better or recommended way (I didnt see one). I was looking into writing a simple plugin, but I didnt see a way to trigger a plugin using some sort of schedule.

I could write a pluggin that simply fired off that SQL query on every page load, but that feels inefficient, but maybe the preferred way?

[ozh]

Your cron based system would totally work

My plugin way would be : instead of running the SQL query on every page load, run it once every 24 hours. How ? In the option, store the last time the query was run (add option "last-delete-old-links" => "2021-07-26 01:03:37" for instance) and if the time is 23.5 or 24 hours later than this date, run the query and update the option.

I'm sure there are plugins already for this (I don't know how they work, though). Check https://github.com/YOURLS/awesome-yourls

[icsy7867]

I have been thinking about writting a plugin and I think it wouldnt be too hard to create a new table and track a name and expiration and add a field to capture the time from a dropdown menu from the sample public front page. I am going to look into this if I have more time.

For anyone else trying to do the same thing (I like to post my solution just in case it helps others), assuming you have admin rights to the DB, as a root/superuser you need can enable events on the MySQL DB:

SET GLOBAL event_scheduler = ON;

And then you can create a mysql event to handle this for you.
Something like this should work:

USE yourls;

CREATE 
    EVENT clearyourls
       ON SCHEDULE EVERY 12 HOUR
    DO
       DELETE FROM yourls_url WHERE timestamp <= DATE_SUB(NOW(), INTERVAL 365 DAY);

if you dont have admin rights on the MySQL server, you can create a cron/systemd timer using something like:

mysql -u yourlsadmin -pSecurepassword --ssl -h db.host.com -e 'SELECT * FROM yourls_url WHERE timestamp <= DATE_SUB(NOW(), INTERVAL 365 DAY)' yourls

[joshp23]

https://github.com/joshp23/YOURLS-Expiry