+ # TODO: the UPDATE part is the same as in check_binaries. Merge?
+
+ query = """
+ WITH usage AS (
+ SELECT
+ af.archive_id AS archive_id,
+ af.file_id AS file_id,
+ af.component_id AS component_id,
+ BOOL_OR(EXISTS (SELECT 1 FROM src_associations sa
+ JOIN suite s ON sa.suite = s.id
+ WHERE sa.source = df.source
+ AND s.archive_id = af.archive_id)
+ OR EXISTS (SELECT 1 FROM files_archive_map af_bin
+ JOIN binaries b ON af_bin.file_id = b.file
+ WHERE b.source = df.source
+ AND af_bin.archive_id = af.archive_id
+ AND af_bin.last_used > ad.delete_date)
+ OR EXISTS (SELECT 1 FROM extra_src_references esr
+ JOIN bin_associations ba ON esr.bin_id = ba.bin
+ JOIN binaries b ON ba.bin = b.id
+ JOIN suite s ON ba.suite = s.id
+ WHERE esr.src_id = df.source
+ AND s.archive_id = af.archive_id))
+ AS in_use
+ FROM files_archive_map af
+ JOIN dsc_files df ON af.file_id = df.file
+ JOIN archive_delete_date ad ON af.archive_id = ad.archive_id
+ GROUP BY af.archive_id, af.file_id, af.component_id
+ )