Hi Ray,
To find the uploads over a year old:
select upload_pk from upload where upload_ts (now() - interval '1 year')
order by upload_ts
You can then take that list and schedule the delete agent on each one of the
upload_pk's with the fossjobs command (man fossjobs).
Unfortunately, you can't simply delete the upload from the database because
that won't delete the files from the repository, and because cascade isn't
turned on for most of the foreign keys. So, at least for now, you have to use
the delete agent.
Bob Gobeille
On Jun 14, 2011, at 6:29 AM, Westphal, Raymond W wrote:
Hello Everyone.
I'm trying to find a way to automatically cleanup the repository. The users
will allow me to delete uploads older than 1 year.
I created the following query from watching the database while I selected the
Organize/Uploads/Delete Uploaded File menu option.
SELECT upload_pk, upload_desc, upload_ts, ufile_name
FROM foldercontents,uploadtree,upload
WHERE foldercontents.parent_fk = '4'
AND upload_ts (select current_date -365)
AND foldercontents.foldercontents_mode = 2
AND foldercontents.child_id = upload.upload_pk
AND uploadtree.upload_fk = upload.upload_pk
AND uploadtree.parent IS NULL;
ORDER BY upload_ts;
Can I simply delete from the upload table where upload_ts (select
current_date -365) ?
Thanks,
Ray W.
CONFIDENTIALITY NOTICE: This e-mail and any files transmitted with it are
intended solely for the use of the individual or entity to whom they are
addressed and may contain confidential and privileged information protected
by law. If you received this e-mail in error, any review, use, dissemination,
distribution, or copying of the e-mail is strictly prohibited. Please notify
the sender immediately by return e-mail and delete all copies from your
system.
___
fossology mailing list
fossology@fossology.org
http://fossology.org/mailman/listinfo/fossology
___
fossology mailing list
fossology@fossology.org
http://fossology.org/mailman/listinfo/fossology
