I’ve blown a few hours trying to get this important idea to work. I figured my findings and working sql code should serve as a helpful reference. Here’s the code before we dissect it:
SET global event_scheduler = 1;
DROP EVENT IF EXISTS Archive_Old_Posts;
USE teamof4;
CREATE EVENT Archive_Old_Posts
ON SCHEDULE EVERY 1 MINUTE
DO
UPDATE scrims SET scrims.isActive = 0 WHERE scrims.end <= NOW(); INSERT INTO archived_scrims SELECT * FROM scrims WHERE scrims.isActive = 0; DELETE FROM scrims WHERE scrims.isActive = 0;
I have a system where users post time-sensitive data. The posts expire after a user-supplied expiration date, and the system should “remove” these old posts from the system so that only fresh content is available to the users.
My initial idea was to have the posts disabled in the PHP code, however, this method seems crude since the updating of expired data is at the mercy of a page request. Ultimately, mysql’s event construct provides exactly what I was looking for. With the event, I was able to create a recurring procedure that runs in the background with no hassle.
My removal process is actually a three step process, and yes, it could be condensed into fewer mysql statements.
SET global event_scheduler = 1;
This line simply tells the server to turn on the event scheduler.
DROP EVENT IF EXISTS Archive_Old_Posts;
If there already is an event with the name Archive_Old_Posts, then delete that event.
USE teamof4;
Let’s specify which database we’re dealing with.
CREATE EVENT Archive_Old_Posts
ON SCHEDULE EVERY 1 MINUTE
DO
Let’s create a MySQL event with the name Archive_Old_Posts. The procedure defined after the DO keyword should be executed every minute. Of course, there are other scheduling periods that can be found here
UPDATE scrims SET scrims.isActive = 0 WHERE scrims.end <= NOW();
The process starts by updating a flag that indicates whether or not a post is considered active in the system based on the current system time (given by the NOW function) of executing the recurring event. At this point, all expired posts have been labeled for archival and deletion.
INSERT INTO archived_scrims SELECT * FROM scrims WHERE scrims.isActive = 0;
The next step involves copying the expired data into another table (with the same columns as “scrims”). As a side note, you can duplicate a table using the simple sql statement below. Just be sure to delete the copied rows in the new table!
CREATE TABLE archived_scrims LIKE scrims;
I transfer the expired content to clean up the original scrims table so that only “active” scrims are shown to the users. This allows the querying of data to be a simple
SELECT * FROM scrims
Of course, your query could limit the data pulled (to only active posts) with a PHP query utilizing a WHERE clause, but it seems to break my complex data associations with CakePHP. I figured that since I’m already creating a stored procedure to update table elements, I might as well archive the data.
DELETE FROM scrims WHERE scrims.isActive = 0;
The last step involves deleting the newly archived and “inactive” scrims. This is pretty straightforward!
I’m not sure how bad the performance hit is for the mysql server running this event every minute. But the timer is pretty simple to change.
Hope it helps.