1# Get the list of the last recent version per Delta with a given jobid list
2# This is a tricky part because with SQL the result of
3#
4# SELECT MAX(A), B, C, D FROM... GROUP BY (B,C)
5#
6# doesn't give the good result (for D).
7#
8# With PostgreSQL, we can use DISTINCT ON(), but with Mysql or Sqlite,
9# we need an extra join using JobTDate.
10SELECT FileId,
11       Job.JobId AS JobId,
12       FileIndex,
13       File.PathId AS PathId,
14       File.Name AS Name,
15       LStat,
16       MD5,
17       DeltaSeq,
18       Fhinfo,
19       Fhnode,
20       Job.JobTDate AS JobTDate
21FROM Job,
22     File,
23
24  (SELECT MAX(JobTDate) AS JobTDate,
25          PathId,
26          FileName
27   FROM
28     (SELECT JobTDate,
29             PathId,
30             File.Name AS FileName
31      FROM File
32      JOIN Job USING (JobId)
33      WHERE File.JobId IN (%s)
34        UNION ALL
35        SELECT JobTDate,
36               PathId,
37               File.Name AS FileName
38        FROM BaseFiles
39        JOIN File USING (FileId)
40        JOIN Job ON (BaseJobId = Job.JobId) WHERE BaseFiles.JobId IN (%s) ) AS tmp
41   GROUP BY PathId,
42            FileName) AS T1
43WHERE (Job.JobId IN
44         (SELECT DISTINCT BaseJobId
45          FROM BaseFiles
46          WHERE JobId IN (%s))
47       OR Job.JobId IN (%s))
48  AND T1.JobTDate = Job.JobTDate
49  AND Job.JobId = File.JobId
50  AND T1.PathId = File.PathId
51  AND T1.FileName = File.Name
52