0

I'm writing an API in Django to store support tickets in a database. This consists of an Error ID, email address, phone number, image file of the problem, and the date/time the entry was submitted.

Due to the nature of the use case here, I need to poll the API every 10 minutes or so with a request to check on the status of all tickets. What I want to do is automatically delete every ticket that is unresolved for over 10 minutes (not immediately delete on 10 minutes, but when the API is polled and finds that the entry is over 10 minutes old).

I thought that including the date/time column would help me but I can't think of any way of doing the check that isn't super messy. The data type is models.DateTimeField and the database I'm using is MariaDB.

  • 1
    Why not do this as a separate job instead of wrapping it up with the polling? I feel like a separate script that is scheduled every N minutes and does something like `UPDATE yourtable SET deleted=1 WHERE submit_time + INTERVAL 10 MINUTE > Now();` or whatever. In your API you can just ignore tickets older than 10 minutes where this job hasn't touched them yet. – JNevill Nov 13 '19 at 14:17
  • 2
    Because I didn't know I could do that! Thank you. If you add this as an answer I'll accept it. –  Nov 13 '19 at 14:27
  • *"Why not do this as a separate job instead of wrapping it up with the polling? I feel like a separate script that is scheduled every N minutes and does something like UPDATE yourtable SET deleted=1 WHERE submit_time + INTERVAL 10 MINUTE > Now(); or whatever"* @JNevill i assume you and Cm1602 never heard of [MySQL events](https://dev.mysql.com/doc/refman/8.0/en/event-scheduler.html) ? -> *"MySQL Events are tasks that run according to a schedule, Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler. "* – Raymond Nijland Nov 13 '19 at 18:05
  • @RaymondNijland I've heard of them. I didn't want to suggest what stack I would use to solve this though. Personally I like to keep application logic like this on my application server and just keep my database dumb. I've also never been a big fan of triggers, but there's a time and a place for everything. I think my bigger point here was that it seemed wrong to make the API or the Poll to it, do double duty. – JNevill Nov 13 '19 at 18:43
  • [Some good discussion here on "Crontab vs Event Scheduler"](https://stackoverflow.com/questions/14805742/cronjob-or-mysql-event/14805785) Good points made for both sides. – JNevill Nov 13 '19 at 18:48
  • *"I've heard of them"* Fair enough @JNevill *"Personally I like to keep application logic like this on my application server and just keep my database dumb"* Well if i know that i use one RMDS for a project and never change from one RDMS into a other i will use logic in the RMDS but i never do it when i need RDMS portability then i also rather keep the RDMS *"dumb"*, in PostgreSQL for example i wrote complete code in the past to handle imports from locations like ftp or a webservice. Why ? Because it was possible :-) no the real reason to avoid roundtrips between the application and RDMS. – Raymond Nijland Nov 13 '19 at 18:51
  • I'm totally with you. Sometimes you want portability. Sometimes you want your code base all in the same place. Sometimes you want to take advantage of native features to avoid round-trips, credential storage issues, weak links in the chain, etc. I know a developer that HATES stored procedures. He will skin you alive if you mention them around him. But he is an app developer, not a data engineer/etl engineer/etc and he just doesn't have any appreciation for the dark arts of the RDBMS. ;) – JNevill Nov 13 '19 at 18:59
  • @JNevill - I think you wanted `WHERE submit_time < NOW() - INTERVAL 10 MINUTE` – Rick James Nov 14 '19 at 01:27
  • Perhaps the important part of JNevill's Comment is "In your API you can just ignore tickets older than 10 minutes where this job hasn't touched them yet." – Rick James Nov 14 '19 at 01:30
  • 2
    @RickJames Thanks for your help. I have used an event to delete every 10 minutes and it works perfectly. I have one big problem though. My database has an imagefield stored in it. I can cleanup unused files when deleted via my API but when entries are wiped by the DB automatically then I don't get the file cleaned up and it sits there. Do you know a way to approach this? –  Nov 14 '19 at 10:16
  • 2
    Wanted to tag @JNevill in my previous comment too. Thanks for your help. –  Nov 14 '19 at 10:17
  • @Cm1602 - Please start a new Question about the "imagefield". Please provide `SHOW CREATE TABLE` and the "cleanup" SQL being used. Also, is the imagefield referenced by multiple tables? – Rick James Nov 14 '19 at 22:03

0 Answers0