Auto purge uploads from old deleted posts

feature
3

I wonder why this isnt more widely discussed? This is really a big problem.

I have written a PHP script, which uses a CSV file generated from the following SQL query which lists all uploads and their references:
(increase the limit if you have lots of uploads)

SELECT 
    uploads.original_filename,
    ROUND(uploads.filesize / 1000000.0, 2) AS size_in_mb,
    uploads.extension,
    uploads.created_at,
    uploads.url,
    upload_references.upload_id,
    upload_references.target_id,
    upload_references.target_type,
    upload_references.created_at,
    upload_references.updated_at
FROM upload_references
JOIN uploads ON uploads.id = upload_references.upload_id
ORDER BY upload_references.target_type
LIMIT 90000

Now what the script does is, it filters uploads, which remain only as drafts (which falsely remains in the database as I explained here). The script outputs space seperated string with all the file names. You can also modify the script to output the full path (remove the function basename() ).

Then login to your discourse SSH server and execute rm command for all the files.

  • One downside to this is that all images, which remains in active drafts will be deleted too (but this can be limited by lowering delete drafts older than n days).
  • Second downside - the incorrect database entries still remains, for that I should ask to the devs for a fix.

If the incorrect entries are deleted, the issue should be properly fixed.

<?php
 
if (($open = fopen("test.csv", "r")) !== false) {
    while (($data = fgetcsv($open, 100000, ",")) !== false) {
        $array[] = $data;
    }
 
    fclose($open);
}
$final = array();
$i=0;
foreach ($array as $item){
	if($item[7]=="Draft"){
			foreach ($array as $item_inside){
				if(($item_inside[4]==$item[4]) && ($item_inside[7]!="Draft")) $i++; //taisa i++, kad nav tikai drafts
				}
			if($i==0)array_push($final, $item[4]); //bija tikai drafti, var likt masiivaa
			$i=0;
		}
	}
$final_unique= array_unique($final);
//print_r($final_unique);

foreach($final_unique as $single){
	echo basename($single)." ";
	}
?>

File test.csv containing the query should be placed in the same directory as the script.
If you have any problems, ask me!

2 Likes