1# for .bvfs_lsdirs
2#
3# Let's retrieve the list of the visible dirs in this dir ...
4#
5# First, the empty filename to locate efficiently
6# the dirs in the file table.
7#
8# Then get all the dir entries from File ...
9#
10# parameter:
11#   %s PathId
12#   %s JobIds ("1,2,...")
13#   %s extra filter
14#   %s JobIds ("1,2,...")
15#   %s JobIds ("1,2,...")
16#
17# row                     0    1       2     3      4      5
18(
19SELECT DISTINCT ON (Path) 'D', PathId, Path, JobId, LStat, FileId
20FROM (
21   SELECT
22      Path1.PathId AS PathId,
23      Path1.Path AS Path,
24      lower(Path1.Path) AS lpath,
25      listfile1.JobId AS JobId,
26      listfile1.LStat AS LStat,
27      listfile1.FileId AS FileId
28   FROM (
29      SELECT listpath1.PathId AS PathId
30      FROM (
31         SELECT DISTINCT PathHierarchy1.PathId AS PathId
32         FROM PathHierarchy AS PathHierarchy1
33         INNER JOIN Path AS Path2
34         ON (PathHierarchy1.PathId = Path2.PathId)
35         INNER JOIN PathVisibility AS PathVisibility1
36         ON (PathHierarchy1.PathId = PathVisibility1.PathId)
37         WHERE PathHierarchy1.PPathId = %s
38         AND PathVisibility1.JobId IN (%s)
39         ) AS listpath1
40      LEFT JOIN (
41          SELECT PVD1.PathId AS PathId
42          FROM (
43             SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId
44             FROM PathVisibility AS PV1 WHERE JobId IN (%s) GROUP BY PathId
45             ) AS PVD1
46             INNER JOIN File AS F2
47             ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.Name = '')
48      ) AS listpath2
49      ON (listpath1.PathId = listpath2.PathId)
50      WHERE listpath2.PathId IS NULL
51      %s
52   ) AS listpath3
53   INNER JOIN Path AS Path1 ON (listpath3.PathId = Path1.PathId)
54   LEFT JOIN (
55      SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId
56      FROM File AS File1
57      WHERE File1.Name = ''
58        AND File1.JobId IN (%s)
59   ) AS listfile1
60   ON (listpath3.PathId = listfile1.PathId)
61) AS A
62ORDER BY Path ASC, JobId DESC
63)
64
65
66