Handling file storage on disk.

pstojanovic

Hello everyone,
I would like to ask about any suggestions regarding the handling of attachments stored in the file system itself.
My core issue is that I want to keep tickets but remove attachments after X period of time in order to optimize storage.
Currently I am running a shell script that sends a query which maps all attachments that belong to department X older than the given time period, and then I rm them. The script itself is simple but I feel like getting the actual data is a bit convoluted.
Query snippet

SELECT
    f.id        AS file_id,
    f.key       AS file_key,
    f.name      AS original_name,
    f.type      AS mime_type,
    f.size,
    f.created   AS file_created,
    'H'         AS attachment_type,
    t.ticket_id,
    t.number    AS ticket_number,
    t.closed    AS ticket_closed
FROM ost_file f
JOIN ost_attachment a   ON a.file_id       = f.id
JOIN ost_thread_entry e ON e.id            = a.object_id
JOIN ost_thread th      ON th.id           = e.thread_id
JOIN ost_ticket t       ON t.ticket_id     = th.object_id
WHERE
    a.type         = 'H'
    AND th.object_type = 'T'
    AND f.bk       = 'F'
    AND t.dept_id  = 14
    AND f.created  < NOW() - INTERVAL 6 MONTH

Is there a better way to approach this ?
TYIA for any suggestions.

KevinTheJedi

pstojanovic

You are doing this completely out of the ordinary and outside its intended use. So of course it’ll be convoluted and complicated; especially if you don’t know the codebase/database. We have ERDs here that will help you relate tables, etc. Key names are file names in the FS setup.

You should instead make a php script that calls actual methods from our code to lookup, get files, delete, and whatever else you need.

Cheers.

KevinTheJedi

pstojanovic

Also keep in mind that this will not unlink inline images from all objects. You would need to manually scan every thread entry, every note from every object that supports it, etc. Otherwise the system will try to load a missing image and throw errors constantly.

You really aren’t supposed to manually delete attachments/files for this very reason. You should delete the related object which orphans the file (if not attached to something else) and queues for deletion on cleanup.

Cheers.

pstojanovic

KevinTheJedi Key names are file names in the FS setup.

This much I have gathered and the cursed query is a product of reviewing the ERDs.

KevinTheJedi Otherwise the system will try to load a missing image and throw errors constantly.

Jesus a major oversight on my part...

Well back to the drawing board and back to php.

Thanks for the notes.