#!/bin/sh
mysql --user=mydbuser --password=mypassword --database=mydb -vvv --execute="DELETE FROM ost_file_chunk WHERE ost_file_chunk.file_id IN( SELECT DISTINCT ostt.file_id FROM ost_ticket ost, ost_thread osth, ost_ticket_status ots, ost_thread_entry oste, ost_attachment ostt, ost_file ostf WHERE ost.ticket_id = osth.object_id AND oste.thread_id = osth.id AND oste.id = ostt.object_id AND ostf.id = ostt.file_id AND ots.id = ost.status_id and ots.state not like '%open%' and ost.closed < NOW() - INTERVAL 30 DAY GROUP BY ostt.file_id)" > /var/www/backup/del_from_chunk.log;
mysql --user=mydbuser --password=mypassword --database=mydb -vvv --execute="DELETE FROM ost_file WHERE ost_file.id IN( SELECT DISTINCT ostt.file_id FROM ost_ticket ost, ost_thread osth, ost_ticket_status ots, ost_thread_entry oste, ost_attachment ostt WHERE ost.ticket_id = osth.object_id AND oste.thread_id = osth.id AND oste.id = ostt.object_id AND ots.id = ost.status_id and ots.state not like '%open%' and ost.closed < NOW() - INTERVAL 30 DAY GROUP BY ostt.file_id)" > /var/www/backup/del_from_file.log;
mysql --user=mydbuser --password=mypassword --database=mydb -vvv --execute="DELETE FROM ost_attachment WHERE ost_attachment.object_id IN( SELECT DISTINCT oste.id FROM ost_ticket ost, ost_thread osth, ost_ticket_status ots, ost_thread_entry oste WHERE ost.ticket_id = osth.object_id AND oste.thread_id = osth.id AND ots.id = ost.status_id and ots.state not like '%open%' and ost.closed < NOW() - INTERVAL 30 DAY GROUP BY oste.id)" > /var/www/backup/del_from_attachment.log;