From f56711e4c73eabaccf30358d58eedcfa3f7473af Mon Sep 17 00:00:00 2001
From: Eric Bollengier <eric@baculasystems.com>
Date: Fri, 2 Nov 2012 10:08:06 +0100
Subject: [PATCH] Fix #5346 .bvfs_lsfiles and .bvfs_restore to handle deleted
 files

---
 bacula/src/cats/bvfs.c     |  4 +--
 bacula/src/cats/sql_cmds.c | 65 ++++++++++++++++++++++----------------
 2 files changed, 40 insertions(+), 29 deletions(-)

diff --git a/bacula/src/cats/bvfs.c b/bacula/src/cats/bvfs.c
index 10b283e66d..27f76d54b7 100644
--- a/bacula/src/cats/bvfs.c
+++ b/bacula/src/cats/bvfs.c
@@ -1020,8 +1020,8 @@ bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
       goto bail_out;
    }
 
-   /* TODO: handle basejob and SQLite3 */
-   Mmsg(query, sql_bvfs_select[db_get_type_index(db)], output_table, output_table);
+   Mmsg(query, sql_bvfs_select[db_get_type_index(db)], 
+        output_table, output_table, output_table);
 
    /* TODO: handle jobid filter */
    Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c
index 860def19e3..65354d6a7f 100644
--- a/bacula/src/cats/sql_cmds.c
+++ b/bacula/src/cats/sql_cmds.c
@@ -817,17 +817,27 @@ const char *sql_get_max_connections[] = {
    "SELECT 0"
 };
 
-/* TODO: Check for corner cases with MySQL and SQLite3
+/*
  *  The Group By can return strange numbers when having multiple
  *  version of a file in the same dataset.
  */
+const char *default_sql_bvfs_select = 
+"CREATE TABLE %s AS "
+"SELECT File.JobId, File.FileIndex, File.FileId "
+"FROM Job, File, ( "
+    "SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId "
+       "FROM btemp%s GROUP BY PathId, FilenameId "
+    ") AS T1 JOIN Filename USING (FilenameId) "
+"WHERE T1.JobTDate = Job.JobTDate "
+  "AND Job.JobId = File.JobId "
+  "AND T1.PathId = File.PathId "
+  "AND T1.FilenameId = File.FilenameId "
+  "AND File.FileIndex > 0 "
+  "AND Job.JobId IN (SELECT DISTINCT JobId FROM btemp%s) ";
+
 const char *sql_bvfs_select[] = {
    /* Mysql */
-   "CREATE TABLE %s AS ( "
-      "SELECT JobId, FileIndex, FileId, max(JobTDate) as JobTDate "
-        "FROM btemp%s "
-      "GROUP BY PathId, FilenameId "
-   "HAVING FileIndex > 0)",
+   default_sql_bvfs_select,
 
    /* Postgresql */
    "CREATE TABLE %s AS ( "
@@ -841,12 +851,7 @@ const char *sql_bvfs_select[] = {
           "WHERE FileIndex > 0)",
 
    /* SQLite3 */
-   "CREATE TABLE %s AS "
-      "SELECT JobId, FileIndex, FileId, max(JobTDate) as JobTDate "
-        "FROM btemp%s "
-      "GROUP BY PathId, FilenameId "
-   "HAVING FileIndex > 0",
-
+   default_sql_bvfs_select,
 
    /* Ingres (TODO) */
    "SELECT 0"
@@ -867,13 +872,15 @@ static const char *sql_bvfs_list_files_default =
                "JOIN File USING (FileId) "
                "JOIN Job  ON    (BaseJobId = Job.JobId) "
          "WHERE BaseFiles.JobId IN (%s)   AND PathId = %s "
-       ") AS tmp GROUP BY PathId, FilenameId LIMIT %lld OFFSET %lld"
+       ") AS tmp GROUP BY PathId, FilenameId "
+     "LIMIT %lld OFFSET %lld"
     ") AS T1 JOIN Filename USING (FilenameId) "
 "WHERE T1.JobTDate = Job.JobTDate "
   "AND Job.JobId = File.JobId "
   "AND T1.PathId = File.PathId "
   "AND T1.FilenameId = File.FilenameId "
   "AND Filename.Name != '' "
+  "AND File.FileIndex > 0 "
   " %s "                     /* AND Name LIKE '' */
   "AND (Job.JobId IN ( "
         "SELECT DISTINCT BaseJobId FROM BaseFiles WHERE JobId IN (%s)) "
@@ -886,20 +893,24 @@ const char *sql_bvfs_list_files[] = {
 
    /* JobId PathId JobId PathId WHERE? Filename? Limit Offset*/
    /* Postgresql */
- "SELECT DISTINCT ON (FilenameId) 'F', PathId, T.FilenameId, "
-  "Filename.Name, JobId, LStat, FileId "
-   "FROM "
-       "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 "
-          "FROM File WHERE JobId IN (%s) AND PathId = %s "
-         "UNION ALL "
-        "SELECT File.FileId, File.JobId, PathId, FilenameId, "
-               "File.FileIndex, LStat, MD5 "
-          "FROM BaseFiles JOIN File USING (FileId) "
-         "WHERE BaseFiles.JobId IN (%s) AND File.PathId = %s "
-        ") AS T JOIN Job USING (JobId) JOIN Filename USING (FilenameId) "
-        " WHERE Filename.Name != '' "
-        " %s "               /* AND Name LIKE '' */
-   "ORDER BY FilenameId, StartTime DESC LIMIT %lld OFFSET %lld",
+ "SELECT Type, PathId, FilenameId, Name, JobId, LStat, FileId "
+  "FROM ("
+   "SELECT DISTINCT ON (FilenameId) 'F' as Type, PathId, T.FilenameId, "
+    "Filename.Name, JobId, LStat, FileId, FileIndex "
+     "FROM "
+         "(SELECT FileId, JobId, PathId, FilenameId, FileIndex, LStat, MD5 "
+            "FROM File WHERE JobId IN (%s) AND PathId = %s "
+           "UNION ALL "
+          "SELECT File.FileId, File.JobId, PathId, FilenameId, "
+                 "File.FileIndex, LStat, MD5 "
+            "FROM BaseFiles JOIN File USING (FileId) "
+           "WHERE BaseFiles.JobId IN (%s) AND File.PathId = %s "
+          ") AS T JOIN Job USING (JobId) JOIN Filename USING (FilenameId) "
+          " WHERE Filename.Name != '' "
+          " %s "               /* AND Name LIKE '' */
+     "ORDER BY FilenameId, StartTime DESC "
+   ") AS A WHERE A.FileIndex > 0 "
+   "LIMIT %lld OFFSET %lld ",
 
    /* SQLite */
    sql_bvfs_list_files_default,
-- 
2.39.2