- q = projectB.query("""
-SELECT f.id FROM source s, files f, dsc_files df
- WHERE f.last_used IS NOT NULL AND s.id = df.source AND df.file = f.id
- AND ((EXISTS (SELECT 1 FROM src_associations sa WHERE sa.source = s.id))
- OR (EXISTS (SELECT 1 FROM binaries b WHERE b.source = s.id)))""")
-
- #### XXX: this should also handle deleted binaries specially (ie, not
- #### reinstate sources because of them
-
- ql = q.getresult()
- # Could be done in SQL; but left this way for hysterical raisins
- # [and freedom to innovate don'cha know?]
- projectB.query("BEGIN WORK")
- for i in ql:
- file_id = i[0]
- projectB.query("UPDATE files SET last_used = NULL WHERE id = %s" % (file_id))
- projectB.query("COMMIT WORK")
+ # 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 IS NULL OR 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
+ )
+
+ UPDATE files_archive_map af
+ SET last_used = CASE WHEN usage.in_use THEN NULL ELSE :last_used END
+ FROM usage, files f, archive
+ WHERE af.archive_id = usage.archive_id AND af.file_id = usage.file_id AND af.component_id = usage.component_id
+ AND ((af.last_used IS NULL AND NOT usage.in_use) OR (af.last_used IS NOT NULL AND usage.in_use))
+ AND af.file_id = f.id
+ AND af.archive_id = archive.id
+
+ RETURNING archive.name, f.filename, af.last_used IS NULL
+ """
+
+ res = session.execute(query, {'last_used': now_date})
+ for i in res:
+ op = "set lastused"
+ if i[2]:
+ op = "unset lastused"
+ Logger.log([op, i[0], i[1]])