More Useful SQL Commands

These are examples of SQL statements you might find handy. Don't forget to substitute real values for the example values given. For example, if the query says "upload_fk=456", you need to replace 456 with your upload value.

Delete copyright records for an upload

delete from copyright where pfile_fk in 
    (select distinct(pfile_fk) as pf from uploadtree where upload_fk=454);

Warning copyright results are on pfiles (the physical file reference in the database). So if you delete a copyright result for pfile 123 in upload 456, AND another upload (789) refers to pfile 123, then if you delete the results for 456, and do a copyright report on upload 789 you will see all the 789 copyrights with the results for pfile 123 omitted. This is a known problem that is solved in newer agents (nomos, buckets at this v 1.2 time).

Delete nomos license records for an upload

delete from license_file where pfile_fk in 
    (select distinct(pfile_fk) as pf from uploadtree where upload_fk=454);

The same warning above for copyrights also apply to nomos results. However, this is still a useful thing to know how to do if you understand the above warning on how pfiles are reused.

Sometimes you don't actually want to delete the records, you just want to rescan without breaking any previous reports. To do this, you need to add a new nomos record in the agent table, remember to update the version. Then reschedule the nomos agent. In v 1.2 the user interface to reschedule an agent was not updated to account for agents like nomos that can keep multiple revisions of results. So you can't simply hit the reset link in Show Job Queue. You have to delete the job, then use Jobs > Agents to schedule a new job.

Delete bucket records for an upload

delete from bucket_ars where upload_fk=97; 

delete from bucket_file where pfile_fk in (select distinct(pfile_fk) as pf from uploadtree where upload_fk=97); 

delete from bucket_container where uploadtree_fk in (select uploadtree_pk from uploadtree where upload_fk=97);