0075_bvfs_ls_sub_dirs_5

# for .bvfs_lsdirs
#
# Let's retrieve the list of the visible dirs in this dir ...
#
# First, the empty filename to locate efficiently
# the dirs in the file table.
#
# Then get all the dir entries from File ...
#
# parameter:
#   %s PathId
#   %s JobIds ("1,2,...")
#   %s extra filter
#   %s JobIds ("1,2,...")
#   %s JobIds ("1,2,...")
#
# row  0    1       2     3      4      5
SELECT 'D', PathId, Path, JobId, LStat, FileId
FROM (
   SELECT
      Path1.PathId AS PathId,
      Path1.Path AS Path,
      lower(Path1.Path) AS lpath,
      listfile1.JobId AS JobId,
      listfile1.LStat AS LStat,
      listfile1.FileId AS FileId
   FROM (
      SELECT listpath1.PathId AS PathId
      FROM (
         SELECT DISTINCT PathHierarchy1.PathId AS PathId
         FROM PathHierarchy AS PathHierarchy1
         INNER JOIN Path AS Path2
         ON (PathHierarchy1.PathId = Path2.PathId)
         INNER JOIN PathVisibility AS PathVisibility1
         ON (PathHierarchy1.PathId = PathVisibility1.PathId)
         WHERE PathHierarchy1.PPathId = %s
         AND PathVisibility1.JobId IN (%s)
         ) AS listpath1
      LEFT JOIN (
          SELECT PVD1.PathId AS PathId
          FROM (
             SELECT PV1.PathId AS PathId, MAX(JobId) AS MaxJobId
             FROM PathVisibility AS PV1 WHERE JobId IN (%s) GROUP BY PathId
             ) AS PVD1
             INNER JOIN File AS F2
             ON (F2.PathId = PVD1.PathId AND F2.JobId = PVD1.MaxJobId AND F2.FileIndex = 0 AND F2.Name = '')
      ) AS listpath2
      ON (listpath1.PathId = listpath2.PathId)
      WHERE listpath2.PathId IS NULL
      %s
   ) AS listpath3
   INNER JOIN Path AS Path1 ON (listpath3.PathId = Path1.PathId)
   LEFT JOIN (
      SELECT File1.PathId AS PathId, File1.JobId AS JobId, File1.LStat AS LStat, File1.FileId AS FileId
      FROM File AS File1
      WHERE File1.Name = ''
        AND File1.JobId IN (%s)
   ) AS listfile1
   ON (listpath3.PathId = listfile1.PathId)
) AS A