- q = session.execute("""
-SELECT b.file, f.filename FROM binaries b, files f
- WHERE f.last_used IS NOT NULL AND f.id = b.file
- AND EXISTS (SELECT 1 FROM bin_associations ba WHERE ba.bin = b.id)""")
-
- for i in q.fetchall():
- Logger.log(["unset lastused", i[1]])
- session.execute("UPDATE files SET last_used = NULL WHERE id = :fileid", {'fileid': i[0]})
- session.commit()
+ 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 bin_associations ba
+ JOIN suite s ON ba.suite = s.id
+ WHERE ba.bin = b.id
+ AND s.archive_id = af.archive_id))
+ AS in_use
+ FROM files_archive_map af
+ JOIN binaries b ON af.file_id = b.file
+ 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]])